1 /*************************************************************************** 2 qgsvirtuallayerqueryparser.cpp : SQL query parser utility functions 3 begin : Jan 2016 4 copyright : (C) 2016 Hugo Mercier, Oslandia 5 email : hugo dot mercier at oslandia dot com 6 ***************************************************************************/ 7 8 /*************************************************************************** 9 * * 10 * This program is free software; you can redistribute it and/or modify * 11 * it under the terms of the GNU General Public License as published by * 12 * the Free Software Foundation; either version 2 of the License, or * 13 * (at your option) any later version. * 14 * * 15 ***************************************************************************/ 16 17 #include "qgsvirtuallayerqueryparser.h" 18 #include "qgsvirtuallayersqlitehelper.h" 19 #include "qgsvirtuallayerblob.h" 20 21 #include "sqlite3.h" 22 23 #include <QRegExp> 24 #include <QtDebug> 25 26 namespace QgsVirtualLayerQueryParser 27 { 28 referencedTables(const QString & query)29 QStringList referencedTables( const QString &query ) 30 { 31 QStringList tables; 32 33 // 34 // open an empty in-memory sqlite database and execute the query 35 // sqlite will return an error for each missing table 36 // this way we know the list of tables referenced by the query 37 QgsScopedSqlite db( QStringLiteral( ":memory:" ), /*withExtension=*/ false ); 38 39 const QString noSuchError = QStringLiteral( "no such table: " ); 40 41 while ( true ) 42 { 43 char *errMsg = nullptr; 44 int r = sqlite3_exec( db.get(), query.toUtf8().constData(), nullptr, nullptr, &errMsg ); 45 QString err; 46 if ( r != SQLITE_OK ) 47 { 48 err = QString::fromUtf8( errMsg ); 49 sqlite3_free( errMsg ); 50 } 51 if ( r && err.startsWith( noSuchError ) ) 52 { 53 QString tableName = err.mid( noSuchError.size() ); 54 tables << tableName; 55 56 // create a dummy table to skip this error 57 QString createStr = QStringLiteral( "CREATE TABLE \"%1\" (id int)" ).arg( tableName.replace( QLatin1String( "\"" ), QLatin1String( "\"\"" ) ) ); 58 ( void )sqlite3_exec( db.get(), createStr.toUtf8().constData(), nullptr, nullptr, nullptr ); 59 } 60 else 61 { 62 // no error, or another error 63 break; 64 } 65 } 66 return tables; 67 } 68 columnCommentDefinitions(const QString & query)69 QMap<QString, ColumnDef> columnCommentDefinitions( const QString &query ) 70 { 71 QMap<QString, ColumnDef> defs; 72 73 // look for special comments in SQL 74 // a column name followed by /*:type*/ 75 QRegExp rx( "([a-zA-Z_\x80-\xFF][a-zA-Z0-9_\x80-\xFF]*)\\s*/\\*:(int|real|text|((?:multi)?(?:point|linestring|polygon)):(\\d+))\\s*\\*/", Qt::CaseInsensitive ); 76 int pos = 0; 77 78 while ( ( pos = rx.indexIn( query, pos ) ) != -1 ) 79 { 80 QString column = rx.cap( 1 ); 81 QString type = rx.cap( 2 ); 82 ColumnDef def; 83 def.setName( column ); 84 if ( type == QLatin1String( "int" ) ) 85 def.setScalarType( QVariant::Int ); 86 else if ( type == QLatin1String( "real" ) ) 87 def.setScalarType( QVariant::Double ); 88 else if ( type == QLatin1String( "text" ) ) 89 def.setScalarType( QVariant::String ); 90 else 91 { 92 // there should be 2 more captures 93 def.setGeometry( QgsWkbTypes::parseType( rx.cap( 3 ) ) ); 94 def.setSrid( static_cast<QgsWkbTypes::Type>( rx.cap( 4 ).toLong() ) ); 95 } 96 defs[column] = def; 97 98 pos += rx.matchedLength(); 99 } 100 return defs; 101 } 102 103 // set the type of the column type, given its text representation setColumnDefType(const QString & columnType,ColumnDef & d)104 void setColumnDefType( const QString &columnType, ColumnDef &d ) 105 { 106 // geometry type 107 QRegExp geometryTypeRx( "\\(([0-9]+),([0-9]+)\\)" ); 108 109 // see qgsvirtuallayersqlitemodule for possible declared types 110 // the type returned by PRAGMA table_info will be either 111 // the type declared by one of the virtual tables 112 // or null 113 if ( columnType == QLatin1String( "int" ) ) 114 d.setScalarType( QVariant::Int ); 115 else if ( columnType == QLatin1String( "real" ) ) 116 d.setScalarType( QVariant::Double ); 117 else if ( columnType == QLatin1String( "text" ) ) 118 d.setScalarType( QVariant::String ); 119 else if ( columnType.startsWith( QLatin1String( "geometry" ) ) ) 120 { 121 // parse the geometry type and srid 122 // geometry(type,srid) 123 int pos = geometryTypeRx.indexIn( columnType, 0 ); 124 if ( pos != -1 ) 125 { 126 QgsWkbTypes::Type type = static_cast<QgsWkbTypes::Type>( geometryTypeRx.cap( 1 ).toLong() ); 127 long srid = geometryTypeRx.cap( 2 ).toLong(); 128 d.setGeometry( type ); 129 d.setSrid( srid ); 130 } 131 } 132 } 133 geometryDefinitionFromVirtualTable(sqlite3 * db,const QString & tableName)134 ColumnDef geometryDefinitionFromVirtualTable( sqlite3 *db, const QString &tableName ) 135 { 136 ColumnDef d; 137 Sqlite::Query q( db, QStringLiteral( "PRAGMA table_info(%1)" ).arg( tableName ) ); 138 while ( q.step() == SQLITE_ROW ) 139 { 140 QString columnName = q.columnText( 1 ); 141 QString columnType = q.columnText( 2 ); 142 if ( ! columnType.startsWith( QLatin1String( "geometry" ) ) ) 143 continue; 144 145 d.setName( columnName ); 146 147 setColumnDefType( columnType, d ); 148 149 break; 150 } 151 return d; 152 } 153 columnDefinitionsFromQuery(sqlite3 * db,const QString & query)154 TableDef columnDefinitionsFromQuery( sqlite3 *db, const QString &query ) 155 { 156 // get column types defined by comments 157 QMap<QString, ColumnDef> definedColumns = columnCommentDefinitions( query ); 158 159 // create a view to detect column names and types, using PRAGMA table_info 160 QString viewStr = "CREATE TEMP VIEW _tview AS " + query; 161 Sqlite::Query::exec( db, viewStr ); 162 163 QStringList columns; 164 QVector<int> undefinedColumns; 165 TableDef tableDef; 166 { 167 Sqlite::Query q( db, QStringLiteral( "PRAGMA table_info(_tview)" ) ); 168 int columnNumber = 0; 169 while ( q.step() == SQLITE_ROW ) 170 { 171 QString columnName = q.columnText( 1 ); 172 173 columns << columnName; 174 175 QString columnType = q.columnText( 2 ); 176 177 // column type defined by comments 178 if ( definedColumns.contains( columnName ) ) 179 { 180 tableDef << definedColumns[columnName]; 181 } 182 else 183 { 184 ColumnDef d; 185 d.setName( columnName ); 186 187 setColumnDefType( columnType, d ); 188 189 if ( d.scalarType() == QVariant::Invalid ) 190 { 191 // else no type is defined 192 undefinedColumns << columnNumber; 193 } 194 195 tableDef << d; 196 } 197 198 columnNumber++; 199 } 200 } 201 202 if ( undefinedColumns.size() == 0 ) 203 return tableDef; 204 205 // get the first row to introspect types 206 { 207 QString qs = QStringLiteral( "SELECT " ); 208 for ( int i = 0; i < undefinedColumns.size(); i++ ) 209 { 210 qs += "\"" + columns[undefinedColumns[i]] + "\""; 211 if ( i != undefinedColumns.size() - 1 ) 212 qs += QLatin1String( ", " ); 213 } 214 qs += QLatin1String( " FROM _tview LIMIT 1" ); 215 216 Sqlite::Query q( db, qs ); 217 if ( q.step() == SQLITE_ROW ) 218 { 219 for ( int i = 0; i < undefinedColumns.size(); i++ ) 220 { 221 int colIdx = undefinedColumns[i]; 222 int type = q.columnType( i ); 223 switch ( type ) 224 { 225 case SQLITE_INTEGER: 226 tableDef[colIdx].setScalarType( QVariant::Int ); 227 break; 228 case SQLITE_FLOAT: 229 tableDef[colIdx].setScalarType( QVariant::Double ); 230 break; 231 case SQLITE_BLOB: 232 { 233 // might be a geometry, parse the type 234 QByteArray ba( q.columnBlob( i ) ); 235 QPair<QgsWkbTypes::Type, long> p( spatialiteBlobGeometryType( ba.constData(), ba.size() ) ); 236 if ( p.first != QgsWkbTypes::NoGeometry ) 237 { 238 tableDef[colIdx].setGeometry( p.first ); 239 tableDef[colIdx].setSrid( p.second ); 240 } 241 else 242 { 243 // interpret it as a string 244 tableDef[colIdx].setScalarType( QVariant::String ); 245 } 246 } 247 break; 248 case SQLITE_TEXT: 249 default: 250 tableDef[colIdx].setScalarType( QVariant::String ); 251 break; 252 }; 253 } 254 } 255 } 256 return tableDef; 257 } 258 tableDefinitionFromVirtualTable(sqlite3 * db,const QString & tableName)259 TableDef tableDefinitionFromVirtualTable( sqlite3 *db, const QString &tableName ) 260 { 261 TableDef td; 262 Sqlite::Query q( db, QStringLiteral( "PRAGMA table_info(%1)" ).arg( tableName ) ); 263 while ( q.step() == SQLITE_ROW ) 264 { 265 ColumnDef d; 266 QString columnName = q.columnText( 1 ); 267 QString columnType = q.columnText( 2 ); 268 269 d.setName( columnName ); 270 setColumnDefType( columnType, d ); 271 272 td << d; 273 } 274 return td; 275 } 276 277 } // namespace 278