@@ -52,6 +52,8 @@ QgsSpatiaLiteProvider::QgsSpatiaLiteProvider( QString const &uri )
52
52
mGeometryColumn = anUri.geometryColumn ();
53
53
mSqlitePath = anUri.database ();
54
54
mSubsetString = anUri.sql ();
55
+ mPrimaryKey = anUri.keyColumn ();
56
+ mQuery = mTableName ;
55
57
56
58
// trying to open the SQLite DB
57
59
spatialite_init ( 0 );
@@ -131,65 +133,137 @@ void QgsSpatiaLiteProvider::loadFields()
131
133
{
132
134
int ret;
133
135
int i;
136
+ sqlite3_stmt *stmt = NULL ;
134
137
char **results;
135
138
int rows;
136
139
int columns;
137
140
char *errMsg = NULL ;
138
141
QString pkName;
139
142
int pkCount = 0 ;
140
143
int fldNo = 0 ;
144
+ QString sql;
141
145
142
146
attributeFields.clear ();
143
- mPrimaryKey .clear ();
144
147
145
- QString sql = QString ( " PRAGMA table_info(\" %1\" )" ).arg ( mTableName );
146
-
147
- ret = sqlite3_get_table ( sqliteHandle, sql.toUtf8 ().constData (), &results, &rows, &columns, &errMsg );
148
- if ( ret != SQLITE_OK )
149
- goto error;
150
- if ( rows < 1 )
151
- ;
152
- else
148
+ if ( !isQuery )
153
149
{
154
- for ( i = 1 ; i <= rows; i++ )
155
- {
156
- QString name = QString::fromUtf8 ( results[( i * columns ) + 1 ] );
157
- const char *type = results[( i * columns ) + 2 ];
158
- QString pk = results[( i * columns ) + 5 ];
159
- if ( pk.toInt () != 0 )
160
- {
161
- // found a Primary Key column
162
- pkCount++;
163
- pkName = name;
164
- }
150
+ mPrimaryKey .clear ();
151
+
152
+ sql = QString ( " PRAGMA table_info(%1)" ).arg ( quotedIdentifier ( mTableName ) );
165
153
166
- if ( name != mGeometryColumn )
154
+ ret = sqlite3_get_table ( sqliteHandle, sql.toUtf8 ().constData (), &results, &rows, &columns, &errMsg );
155
+ if ( ret != SQLITE_OK )
156
+ goto error;
157
+ if ( rows < 1 )
158
+ ;
159
+ else
160
+ {
161
+ for ( i = 1 ; i <= rows; i++ )
167
162
{
168
- // for sure any SQLite value can be represented as SQLITE_TEXT
169
- QVariant::Type fieldType = QVariant::String;
170
-
171
- // making some assumptions in order to guess a more realistic type
172
- if ( strcasecmp ( type, " int" ) == 0 ||
173
- strcasecmp ( type, " integer" ) == 0 ||
174
- strcasecmp ( type, " bigint" ) == 0 ||
175
- strcasecmp ( type, " smallint" ) == 0 ||
176
- strcasecmp ( type, " tinyint" ) == 0 ||
177
- strcasecmp ( type, " boolean" ) == 0 )
163
+ QString name = QString::fromUtf8 ( results[( i * columns ) + 1 ] );
164
+ const char *type = results[( i * columns ) + 2 ];
165
+ QString pk = results[( i * columns ) + 5 ];
166
+ if ( pk.toInt () != 0 )
178
167
{
179
- fieldType = QVariant::Int;
168
+ // found a Primary Key column
169
+ pkCount++;
170
+ pkName = name;
180
171
}
181
- else if ( strcasecmp ( type, " real" ) == 0 ||
182
- strcasecmp ( type, " double" ) == 0 ||
183
- strcasecmp ( type, " double precision" ) == 0 || strcasecmp ( type, " float" ) == 0 )
172
+
173
+ if ( name != mGeometryColumn )
184
174
{
185
- fieldType = QVariant::Double;
186
- }
175
+ // for sure any SQLite value can be represented as SQLITE_TEXT
176
+ QVariant::Type fieldType = QVariant::String;
177
+
178
+ // making some assumptions in order to guess a more realistic type
179
+ if ( strcasecmp ( type, " int" ) == 0 ||
180
+ strcasecmp ( type, " integer" ) == 0 ||
181
+ strcasecmp ( type, " bigint" ) == 0 ||
182
+ strcasecmp ( type, " smallint" ) == 0 ||
183
+ strcasecmp ( type, " tinyint" ) == 0 ||
184
+ strcasecmp ( type, " boolean" ) == 0 )
185
+ {
186
+ fieldType = QVariant::Int;
187
+ }
188
+ else if ( strcasecmp ( type, " real" ) == 0 ||
189
+ strcasecmp ( type, " double" ) == 0 ||
190
+ strcasecmp ( type, " double precision" ) == 0 || strcasecmp ( type, " float" ) == 0 )
191
+ {
192
+ fieldType = QVariant::Double;
193
+ }
187
194
188
- attributeFields.insert ( fldNo++, QgsField ( name, fieldType, type, 0 , 0 , " " ) );
195
+ attributeFields.insert ( fldNo++, QgsField ( name, fieldType, type, 0 , 0 , " " ) );
196
+ }
189
197
}
190
198
}
191
- }
192
- sqlite3_free_table ( results );
199
+ sqlite3_free_table ( results );
200
+ }
201
+ else
202
+ {
203
+ sql = QString ( " select * from %1 limit 1" ).arg ( mQuery );
204
+
205
+ if ( sqlite3_prepare_v2 ( sqliteHandle, sql.toUtf8 ().constData (), -1 , &stmt, NULL ) != SQLITE_OK )
206
+ {
207
+ // some error occurred
208
+ QgsDebugMsg ( QString ( " SQLite error: %1\n\n SQL: %2" )
209
+ .arg ( sql )
210
+ .arg ( QString::fromUtf8 ( sqlite3_errmsg ( sqliteHandle ) ) ) );
211
+ return ;
212
+ }
213
+
214
+ ret = sqlite3_step ( stmt );
215
+ if ( ret == SQLITE_DONE )
216
+ {
217
+ // there are no rows to fetch
218
+ sqlite3_finalize ( stmt );
219
+ return ;
220
+ }
221
+
222
+ if ( ret == SQLITE_ROW )
223
+ {
224
+ // one valid row has been fetched from the result set
225
+ columns = sqlite3_column_count ( stmt );
226
+ for ( i = 0 ; i < columns; i++ )
227
+ {
228
+ QString name = QString::fromUtf8 ( sqlite3_column_name ( stmt, i ) );
229
+ const char *type = sqlite3_column_decltype ( stmt, i );
230
+ if ( type == NULL )
231
+ type = " TEXT" ;
232
+
233
+ if ( name != mPrimaryKey )
234
+ {
235
+ pkCount++;
236
+ pkName = name;
237
+ }
238
+
239
+ if ( name != mGeometryColumn )
240
+ {
241
+ // for sure any SQLite value can be represented as SQLITE_TEXT
242
+ QVariant::Type fieldType = QVariant::String;
243
+
244
+ // making some assumptions in order to guess a more realistic type
245
+ if ( strcasecmp ( type, " int" ) == 0 ||
246
+ strcasecmp ( type, " integer" ) == 0 ||
247
+ strcasecmp ( type, " bigint" ) == 0 ||
248
+ strcasecmp ( type, " smallint" ) == 0 ||
249
+ strcasecmp ( type, " tinyint" ) == 0 ||
250
+ strcasecmp ( type, " boolean" ) == 0 )
251
+ {
252
+ fieldType = QVariant::Int;
253
+ }
254
+ else if ( strcasecmp ( type, " real" ) == 0 ||
255
+ strcasecmp ( type, " double" ) == 0 ||
256
+ strcasecmp ( type, " double precision" ) == 0 || strcasecmp ( type, " float" ) == 0 )
257
+ {
258
+ fieldType = QVariant::Double;
259
+ }
260
+
261
+ attributeFields.insert ( fldNo++, QgsField ( name, fieldType, type, 0 , 0 , " " ) );
262
+ }
263
+ }
264
+ }
265
+ sqlite3_finalize ( stmt );
266
+ }
193
267
194
268
if ( pkCount == 1 )
195
269
{
@@ -221,7 +295,9 @@ bool QgsSpatiaLiteProvider::featureAtId( int featureId, QgsFeature & feature, bo
221
295
222
296
feature.setValid ( false );
223
297
224
- QString sql = " SELECT ROWID" ;
298
+ QString primaryKey = !isQuery ? " ROWID" : quotedIdentifier ( mPrimaryKey );
299
+
300
+ QString sql = QString ( " SELECT %1" ).arg ( primaryKey );
225
301
for ( QgsAttributeList::const_iterator it = fetchAttributes.constBegin (); it != fetchAttributes.constEnd (); ++it )
226
302
{
227
303
const QgsField & fld = field ( *it );
@@ -232,7 +308,10 @@ bool QgsSpatiaLiteProvider::featureAtId( int featureId, QgsFeature & feature, bo
232
308
{
233
309
sql += QString ( " , AsBinary(%1)" ).arg ( quotedIdentifier ( mGeometryColumn ) );
234
310
}
235
- sql += QString ( " FROM %1 WHERE ROWID = %2" ).arg ( quotedIdentifier ( mTableName ) ).arg ( featureId );
311
+ sql += QString ( " FROM %1 WHERE %2 = %3" )
312
+ .arg ( mQuery )
313
+ .arg ( primaryKey )
314
+ .arg ( featureId );
236
315
237
316
if ( sqlite3_prepare_v2 ( sqliteHandle, sql.toUtf8 ().constData (), -1 , &stmt, NULL ) != SQLITE_OK )
238
317
{
@@ -267,7 +346,7 @@ bool QgsSpatiaLiteProvider::featureAtId( int featureId, QgsFeature & feature, bo
267
346
{
268
347
if ( ic == 0 )
269
348
{
270
- // first column always contains the ROWID
349
+ // first column always contains the ROWID (or the primary key)
271
350
feature.setFeatureId ( sqlite3_column_int ( stmt, ic ) );
272
351
}
273
352
else
@@ -391,7 +470,7 @@ bool QgsSpatiaLiteProvider::nextFeature( QgsFeature & feature )
391
470
{
392
471
if ( ic == 0 )
393
472
{
394
- // first column always contains the ROWID
473
+ // first column always contains the ROWID (or the primary key)
395
474
feature.setFeatureId ( sqlite3_column_int ( sqliteStatement, ic ) );
396
475
}
397
476
else
@@ -522,7 +601,9 @@ void QgsSpatiaLiteProvider::select( QgsAttributeList fetchAttributes, QgsRectang
522
601
sqliteStatement = NULL ;
523
602
}
524
603
525
- QString sql = " SELECT ROWID" ;
604
+ QString primaryKey = !isQuery ? " ROWID" : quotedIdentifier ( mPrimaryKey );
605
+
606
+ QString sql = QString ( " SELECT %1" ).arg ( primaryKey );
526
607
for ( QgsAttributeList::const_iterator it = fetchAttributes.constBegin (); it != fetchAttributes.constEnd (); ++it )
527
608
{
528
609
const QgsField & fld = field ( *it );
@@ -533,7 +614,7 @@ void QgsSpatiaLiteProvider::select( QgsAttributeList fetchAttributes, QgsRectang
533
614
{
534
615
sql += QString ( " , AsBinary(%1)" ).arg ( quotedIdentifier ( mGeometryColumn ) );
535
616
}
536
- sql += QString ( " FROM %1" ).arg ( quotedIdentifier ( mTableName ) );
617
+ sql += QString ( " FROM %1" ).arg ( mQuery );
537
618
538
619
QString whereClause;
539
620
@@ -569,7 +650,10 @@ void QgsSpatiaLiteProvider::select( QgsAttributeList fetchAttributes, QgsRectang
569
650
mbrFilter += QString ( " ymin <= %1 AND " ).arg ( QString::number ( rect.yMaximum (), ' f' , 6 ) );
570
651
mbrFilter += QString ( " ymax >= %1" ).arg ( QString::number ( rect.yMinimum (), ' f' , 6 ) );
571
652
QString idxName = QString ( " idx_%1_%2" ).arg ( mIndexTable ).arg ( mIndexGeometry );
572
- whereClause += QString ( " ROWID IN (SELECT pkid FROM %1 WHERE %2)" ).arg ( quotedIdentifier ( idxName ) ).arg ( mbrFilter );
653
+ whereClause += QString ( " %1 IN (SELECT pkid FROM %2 WHERE %3)" )
654
+ .arg ( quotedIdentifier ( primaryKey ) )
655
+ .arg ( quotedIdentifier ( idxName ) )
656
+ .arg ( mbrFilter );
573
657
}
574
658
else if ( spatialIndexMbrCache )
575
659
{
@@ -579,7 +663,10 @@ void QgsSpatiaLiteProvider::select( QgsAttributeList fetchAttributes, QgsRectang
579
663
arg ( QString::number ( rect.yMinimum (), ' f' , 6 ) ).
580
664
arg ( QString::number ( rect.xMaximum (), ' f' , 6 ) ).arg ( QString::number ( rect.yMaximum (), ' f' , 6 ) );
581
665
QString idxName = QString ( " cache_%1_%2" ).arg ( mIndexTable ).arg ( mIndexGeometry );
582
- whereClause += QString ( " ROWID IN (SELECT rowid FROM %1 WHERE mbr = FilterMbrIntersects(%2))" ).arg ( quotedIdentifier ( idxName ) ).arg ( mbr );
666
+ whereClause += QString ( " %1 IN (SELECT rowid FROM %2 WHERE mbr = FilterMbrIntersects(%3))" )
667
+ .arg ( quotedIdentifier ( primaryKey ) )
668
+ .arg ( quotedIdentifier ( idxName ) )
669
+ .arg ( mbr );
583
670
}
584
671
else
585
672
{
@@ -711,7 +798,7 @@ QVariant QgsSpatiaLiteProvider::minimumValue( int index )
711
798
// get the field name
712
799
const QgsField & fld = field ( index );
713
800
714
- QString sql = QString ( " SELECT Min(\" %1 \" ) FROM \" %2 \" " ).arg ( fld.name () ).arg ( mTableName );
801
+ QString sql = QString ( " SELECT Min(%1 ) FROM %2 " ).arg ( quotedIdentifier ( fld.name () ) ) .arg ( mQuery );
715
802
716
803
if ( !mSubsetString .isEmpty () )
717
804
{
@@ -766,7 +853,7 @@ QVariant QgsSpatiaLiteProvider::maximumValue( int index )
766
853
// get the field name
767
854
const QgsField & fld = field ( index );
768
855
769
- QString sql = QString ( " SELECT Max(\" %1 \" ) FROM \" %2 \" " ).arg ( fld.name () ).arg ( mTableName );
856
+ QString sql = QString ( " SELECT Max(%1 ) FROM %2 " ).arg ( quotedIdentifier ( fld.name () ) ) .arg ( mQuery );
770
857
771
858
if ( !mSubsetString .isEmpty () )
772
859
{
@@ -819,7 +906,7 @@ void QgsSpatiaLiteProvider::uniqueValues( int index, QList < QVariant > &uniqueV
819
906
// get the field name
820
907
const QgsField & fld = field ( index );
821
908
822
- sql = QString ( " SELECT DISTINCT \" %1 \" FROM \" %2 \" ORDER BY \" %1 \" " ).arg ( fld.name () ).arg ( mTableName );
909
+ sql = QString ( " SELECT DISTINCT %1 FROM %2 ORDER BY %1 " ).arg ( quotedIdentifier ( fld.name () ) ) .arg ( mQuery );
823
910
824
911
if ( !mSubsetString .isEmpty () )
825
912
{
@@ -1511,6 +1598,7 @@ bool QgsSpatiaLiteProvider::checkLayerType()
1511
1598
mTableBased = false ;
1512
1599
mViewBased = false ;
1513
1600
mVShapeBased = false ;
1601
+ isQuery = false ;
1514
1602
1515
1603
// checking if this one is a Table-based layer
1516
1604
QString sql = QString ( " SELECT read_only FROM geometry_columns "
@@ -1594,6 +1682,49 @@ bool QgsSpatiaLiteProvider::checkLayerType()
1594
1682
}
1595
1683
sqlite3_free_table ( results );
1596
1684
1685
+ // checking if this one is a select query
1686
+ if ( mQuery .startsWith ( " (select" , Qt::CaseInsensitive ) &&
1687
+ mQuery .endsWith ( " )" ) )
1688
+ {
1689
+ // get a new alias for the subquery
1690
+ int index = 0 ;
1691
+ QString alias;
1692
+ QRegExp regex;
1693
+ do
1694
+ {
1695
+ alias = QString ( " subQuery_%1" ).arg ( QString::number ( index++ ) );
1696
+ QString pattern = QString ( " (\\\" ?)%1\\ 1" ).arg ( QRegExp::escape ( alias ) );
1697
+ regex.setPattern ( pattern );
1698
+ regex.setCaseSensitivity ( Qt::CaseInsensitive );
1699
+ }
1700
+ while ( mQuery .contains ( regex ) );
1701
+
1702
+ // convert the custom query into a subquery
1703
+ mQuery = QString ( " %1 as %2" )
1704
+ .arg ( mQuery )
1705
+ .arg ( quotedIdentifier ( alias ) );
1706
+
1707
+ sql = QString ( " SELECT 0 FROM %1 LIMIT 1" ).arg ( mQuery );
1708
+ ret = sqlite3_get_table ( sqliteHandle, sql.toUtf8 ().constData (), &results, &rows, &columns, &errMsg );
1709
+ if ( ret == SQLITE_OK && rows == 1 )
1710
+ {
1711
+ isQuery = true ;
1712
+ mReadOnly = true ;
1713
+ count++;
1714
+ }
1715
+ if ( errMsg )
1716
+ {
1717
+ QgsDebugMsg ( QString ( " sqlite error %1 [%2]" ).arg ( sql ).arg ( errMsg ) );
1718
+ sqlite3_free ( errMsg );
1719
+ errMsg = 0 ;
1720
+ }
1721
+ sqlite3_free_table ( results );
1722
+ }
1723
+ else
1724
+ {
1725
+ mQuery = quotedIdentifier ( mTableName );
1726
+ }
1727
+
1597
1728
// checking for validity
1598
1729
return count == 1 ;
1599
1730
}
@@ -1607,6 +1738,8 @@ bool QgsSpatiaLiteProvider::getGeometryDetails()
1607
1738
ret = getViewGeometryDetails ();
1608
1739
if ( mVShapeBased )
1609
1740
ret = getVShapeGeometryDetails ();
1741
+ if ( isQuery )
1742
+ ret = getQueryGeometryDetails ();
1610
1743
return ret;
1611
1744
}
1612
1745
@@ -1845,6 +1978,122 @@ bool QgsSpatiaLiteProvider::getVShapeGeometryDetails()
1845
1978
return false ;
1846
1979
}
1847
1980
1981
+ bool QgsSpatiaLiteProvider::getQueryGeometryDetails ()
1982
+ {
1983
+ int ret;
1984
+ int i;
1985
+ char **results;
1986
+ int rows;
1987
+ int columns;
1988
+ char *errMsg = NULL ;
1989
+
1990
+ QString fType ( " " );
1991
+ QString xSrid ( " " );
1992
+
1993
+ // get stuff from the relevant column instead. This may (will?)
1994
+ // fail if there is no data in the relevant table.
1995
+ QString sql = QString ( " select srid(%1), geometrytype(%1) from %2" )
1996
+ .arg ( quotedIdentifier ( mGeometryColumn ) )
1997
+ .arg ( mQuery );
1998
+
1999
+ // it is possible that the where clause restricts the feature type
2000
+ if ( !mSubsetString .isEmpty () )
2001
+ {
2002
+ sql += " WHERE " + mSubsetString ;
2003
+ }
2004
+
2005
+ sql += " limit 1" ;
2006
+
2007
+ ret = sqlite3_get_table ( sqliteHandle, sql.toUtf8 ().constData (), &results, &rows, &columns, &errMsg );
2008
+ if ( ret != SQLITE_OK )
2009
+ goto error;
2010
+ if ( rows < 1 )
2011
+ ;
2012
+ else
2013
+ {
2014
+ for ( i = 1 ; i <= rows; i++ )
2015
+ {
2016
+ xSrid = results[( i * columns ) + 0 ];
2017
+ fType = results[( i * columns ) + 1 ];
2018
+ }
2019
+ }
2020
+ sqlite3_free_table ( results );
2021
+
2022
+ if ( !xSrid.isEmpty () && !fType .isEmpty () )
2023
+ {
2024
+ if ( fType == " GEOMETRY" )
2025
+ {
2026
+ // check to see if there is a unique geometry type
2027
+ sql = QString ( " select distinct "
2028
+ " case"
2029
+ " when geometrytype(%1) IN ('POINT','MULTIPOINT') THEN 'POINT'"
2030
+ " when geometrytype(%1) IN ('LINESTRING','MULTILINESTRING') THEN 'LINESTRING'"
2031
+ " when geometrytype(%1) IN ('POLYGON','MULTIPOLYGON') THEN 'POLYGON'"
2032
+ " end "
2033
+ " from %2" )
2034
+ .arg ( quotedIdentifier ( mGeometryColumn ) )
2035
+ .arg ( mQuery );
2036
+
2037
+ if ( !mSubsetString .isEmpty () )
2038
+ sql += " where " + mSubsetString ;
2039
+
2040
+ ret = sqlite3_get_table ( sqliteHandle, sql.toUtf8 ().constData (), &results, &rows, &columns, &errMsg );
2041
+ if ( ret != SQLITE_OK )
2042
+ goto error;
2043
+ if ( rows != 1 )
2044
+ ;
2045
+ else
2046
+ {
2047
+ for ( i = 1 ; i <= rows; i++ )
2048
+ {
2049
+ fType = results[( 1 * columns ) + 0 ];
2050
+ }
2051
+ }
2052
+ sqlite3_free_table ( results );
2053
+ }
2054
+
2055
+ if ( fType == " POINT" )
2056
+ {
2057
+ geomType = QGis::WKBPoint;
2058
+ }
2059
+ else if ( fType == " MULTIPOINT" )
2060
+ {
2061
+ geomType = QGis::WKBMultiPoint;
2062
+ }
2063
+ else if ( fType == " LINESTRING" )
2064
+ {
2065
+ geomType = QGis::WKBLineString;
2066
+ }
2067
+ else if ( fType == " MULTILINESTRING" )
2068
+ {
2069
+ geomType = QGis::WKBMultiLineString;
2070
+ }
2071
+ else if ( fType == " POLYGON" )
2072
+ {
2073
+ geomType = QGis::WKBPolygon;
2074
+ }
2075
+ else if ( fType == " MULTIPOLYGON" )
2076
+ {
2077
+ geomType = QGis::WKBMultiPolygon;
2078
+ }
2079
+ mSrid = xSrid.toInt ();
2080
+ }
2081
+
2082
+ if ( geomType == QGis::WKBUnknown || mSrid < 0 )
2083
+ goto error;
2084
+
2085
+ return getSridDetails ();
2086
+
2087
+ error:
2088
+ // unexpected error
2089
+ if ( errMsg != NULL )
2090
+ {
2091
+ QgsDebugMsg ( QString ( " SQL error: %1\n\n %2" ).arg ( sql ).arg ( errMsg ? QString::fromUtf8 ( errMsg ) : " unknown cause" ) );
2092
+ sqlite3_free ( errMsg );
2093
+ }
2094
+ return false ;
2095
+ }
2096
+
1848
2097
bool QgsSpatiaLiteProvider::getSridDetails ()
1849
2098
{
1850
2099
int ret;
@@ -1891,8 +2140,10 @@ bool QgsSpatiaLiteProvider::getTableSummary()
1891
2140
int columns;
1892
2141
char *errMsg = NULL ;
1893
2142
1894
- QString sql = QString ( " SELECT Min(MbrMinX(\" %1\" )), Min(MbrMinY(\" %1\" )), "
1895
- " Max(MbrMaxX(\" %1\" )), Max(MbrMaxY(\" %1\" )), Count(*) " " FROM \" %2\" " ).arg ( mGeometryColumn ).arg ( mTableName );
2143
+ QString sql = QString ( " SELECT Min(MbrMinX(%1)), Min(MbrMinY(%1)), "
2144
+ " Max(MbrMaxX(%1)), Max(MbrMaxY(%1)), Count(*) " " FROM %2" )
2145
+ .arg ( quotedIdentifier ( mGeometryColumn ) )
2146
+ .arg ( mQuery );
1896
2147
1897
2148
if ( !mSubsetString .isEmpty () )
1898
2149
{
0 commit comments