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