Skip to content

Commit

Permalink
Browse files Browse the repository at this point in the history
sqlite accept aliased queries from db manager
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"
  • Loading branch information
elpaso committed Dec 13, 2018
1 parent 527448d commit 6f725b8
Show file tree
Hide file tree
Showing 3 changed files with 17 additions and 5 deletions.
6 changes: 3 additions & 3 deletions python/plugins/db_manager/dlg_sql_layer_window.py
Expand Up @@ -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
Expand Down
2 changes: 1 addition & 1 deletion python/plugins/db_manager/dlg_sql_window.py
Expand Up @@ -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):
Expand Down
14 changes: 13 additions & 1 deletion src/providers/spatialite/qgsspatialiteprovider.cpp
Expand Up @@ -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 );
Expand Down

0 comments on commit 6f725b8

Please sign in to comment.