sqlite accept aliased queries from db manager

elpaso · elpaso · commit 6f725b82a40b · 2018-12-13T09:08:48.000+01:00
Fixes #20674 - DB Manager - load sql query as layer with geom column Well, not sure it really fixes that particular issue because it is not really well described, but for sure this fixes the general case of "SELECT * FROM my_table AS my_alias"
diff --git a/python/plugins/db_manager/dlg_sql_layer_window.py b/python/plugins/db_manager/dlg_sql_layer_window.py
@@ -147,13 +147,13 @@ def __init__(self, iface, layer, parent=None):
 
         # Update from layer
         # First the SQL from QgsDataSourceUri table
-        sql = uri.table()
+        sql = uri.table().replace('\n', ' ').strip()
         if uri.keyColumn() == '_uid_':
-            match = re.search(r'^\(SELECT .+ AS _uid_,\* FROM \((.*)\) AS _subq_.+_\s*\)$', sql, re.S | re.X)
+            match = re.search(r'^\(SELECT .+ AS _uid_,\* FROM \((.*)\) AS _subq_.+_\s*\)$', sql, re.S | re.X | re.IGNORECASE)
             if match:
                 sql = match.group(1)
         else:
-            match = re.search(r'^\((SELECT .+ FROM .+)\)$', sql, re.S | re.X)
+            match = re.search(r'^\((SELECT .+ FROM .+)\)$', sql, re.S | re.X | re.IGNORECASE)
             if match:
                 sql = match.group(1)
         # Need to check on table() since the parentheses were removed by the regexp
diff --git a/python/plugins/db_manager/dlg_sql_window.py b/python/plugins/db_manager/dlg_sql_window.py
@@ -561,7 +561,7 @@ def createView(self):
     def _getSqlQuery(self):
         sql = self.editSql.selectedText()
         if len(sql) == 0:
-            sql = self.editSql.text()
+            sql = self.editSql.text().replace('\n', ' ').strip()
         return sql
 
     def uniqueChanged(self):
diff --git a/src/providers/spatialite/qgsspatialiteprovider.cpp b/src/providers/spatialite/qgsspatialiteprovider.cpp
@@ -4603,9 +4603,21 @@ bool QgsSpatiaLiteProvider::checkLayerType()
       // 3. check if ROWID injection works
       if ( ! queryGeomTableName.isEmpty() )
       {
+        // Check if the whole sql is aliased (I couldn't find a sqlite API call to get this information)
+        QRegularExpression re { R"re(\s+AS\s+(\w+)\n?\)?$)re" };
+        re.setPatternOptions( QRegularExpression::PatternOption::MultilineOption |
+                              QRegularExpression::PatternOption::CaseInsensitiveOption );
+        QRegularExpressionMatch match { re.match( mTableName ) };
+        regex.setPattern( QStringLiteral( R"re(\s+AS\s+(\w+)\n?\)?$)re" ) );
+        QString tableAlias;
+        if ( match.hasMatch() )
+        {
+          tableAlias = match.captured( 1 );
+        }
         QString newSql( mQuery.replace( QStringLiteral( "SELECT " ),
                                         QStringLiteral( "SELECT %1.%2, " )
-                                        .arg( quotedIdentifier( queryGeomTableName ), QStringLiteral( "ROWID" ) ),
+                                        .arg( quotedIdentifier( tableAlias.isEmpty() ? queryGeomTableName : tableAlias ),
+                                              QStringLiteral( "ROWID" ) ),
                                         Qt::CaseInsensitive ) );
         sql = QStringLiteral( "SELECT ROWID FROM %1 WHERE ROWID IS NOT NULL LIMIT 1" ).arg( newSql );
         ret = sqlite3_get_table( mSqliteHandle, sql.toUtf8().constData(), &results, &rows, &columns, &errMsg );
