Skip to content

Commit

Permalink
Browse files Browse the repository at this point in the history
Merge pull request #8630 from elpaso/bugfix-20674-db-manager-sqlite-a…
…liased-queries

sqlite accept aliased queries from db manager
  • Loading branch information
elpaso committed Dec 10, 2018
2 parents 87d2da1 + a44b13b commit cf0442d
Show file tree
Hide file tree
Showing 4 changed files with 32 additions and 8 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 @@ -602,7 +602,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
18 changes: 15 additions & 3 deletions tests/src/python/test_provider_spatialite.py
Expand Up @@ -654,14 +654,14 @@ def testVectorLayerUtilsCreateFeatureWithProviderDefaultLiteral(self):
f = QgsVectorLayerUtils.createFeature(vl)
self.assertEqual(f.attributes(), [None, "qgis 'is good", 5, 5.7, None])

# check that provider default literals take precedence over passed attribute values
# check that provider default literals do not take precedence over passed attribute values
f = QgsVectorLayerUtils.createFeature(vl, attributes={1: 'qgis is great', 0: 3})
self.assertEqual(f.attributes(), [3, "qgis 'is good", 5, 5.7, None])
self.assertEqual(f.attributes(), [3, "qgis is great", 5, 5.7, None])

# test that vector layer default value expression overrides provider default literal
vl.setDefaultValueDefinition(3, QgsDefaultValue("4*3"))
f = QgsVectorLayerUtils.createFeature(vl, attributes={1: 'qgis is great', 0: 3})
self.assertEqual(f.attributes(), [3, "qgis 'is good", 5, 12, None])
self.assertEqual(f.attributes(), [3, "qgis is great", 5, 12, None])

def testCreateAttributeIndex(self):
vl = QgsVectorLayer("dbname=%s table='test_defaults' key='id'" % self.dbname, "test_defaults", "spatialite")
Expand Down Expand Up @@ -848,6 +848,18 @@ def _check_features(vl, offset):
self.assertTrue(vl_no_pk.isValid())
_check_features(vl_no_pk, 10)

def testAliasedQueries(self):
"""Test regression when sending queries with aliased tables from DB manager"""

def _test(sql):
vl = QgsVectorLayer('dbname=\'{}/provider/spatialite.db\' table="{}" (geom) sql='.format(TEST_DATA_DIR, sql), 'test', 'spatialite')
self.assertTrue(vl.isValid())

_test("(SELECT * FROM somedata as my_alias\n)")
_test("(SELECT * FROM somedata as my_alias)")
_test("(SELECT * FROM somedata AS my_alias)")
_test('(SELECT * FROM \\"somedata\\" as my_alias\n)')


if __name__ == '__main__':
unittest.main()

0 comments on commit cf0442d

Please sign in to comment.