Skip to content

Commit

Permalink
Browse files Browse the repository at this point in the history
Fixing db_manager so that it can run sql with inline comments
Fixes #29089
  • Loading branch information
stev-0 authored and nyalldawson committed Sep 6, 2019
1 parent 6d55c78 commit a39a413
Show file tree
Hide file tree
Showing 3 changed files with 83 additions and 7 deletions.
40 changes: 33 additions & 7 deletions python/plugins/db_manager/dlg_sql_window.py
Expand Up @@ -60,6 +60,37 @@
import re


def check_comments_in_sql(raw_sql_input):
lines = []
for line in raw_sql_input.split('\n'):
if not line.strip().startswith('--'):
if '--' in line:
comment_positions = []
comments = re.finditer(r'--', line)
for match in comments:
comment_positions.append(match.start())
quote_positions = []
identifiers = re.finditer(r'"(?:[^"]|"")*"', line)
quotes = re.finditer(r"'(?:[^']|'')*'", line)
for match in identifiers:
quote_positions.append((match.start(), match.end()))
for match in quotes:
quote_positions.append((match.start(), match.end()))
unquoted_comments = comment_positions.copy()
for comment in comment_positions:
for quote_position in quote_positions:
if comment >= quote_position[0] and comment < quote_position[1]:
unquoted_comments.remove(comment)
if len(unquoted_comments) > 0:
lines.append(line[:unquoted_comments[0]])
else:
lines.append(line)
else:
lines.append(line)
sql = ' '.join(lines)
return sql.strip()


class DlgSqlWindow(QWidget, Ui_Dialog):
nameChanged = pyqtSignal(str)
QUERY_HISTORY_LIMIT = 20
Expand Down Expand Up @@ -605,13 +636,8 @@ def _getSqlQuery(self):
def _getExecutableSqlQuery(self):
sql = self._getSqlQuery()

# Clean it up!
lines = []
for line in sql.split('\n'):
if not line.strip().startswith('--'):
lines.append(line)
sql = ' '.join(lines)
return sql.strip()
uncommented_sql = check_comments_in_sql(sql)
return uncommented_sql

def uniqueChanged(self):
# when an item is (un)checked, simply trigger an update of the combobox text
Expand Down
1 change: 1 addition & 0 deletions tests/src/python/CMakeLists.txt
Expand Up @@ -257,6 +257,7 @@ ADD_PYTHON_TEST(PyQgsAuxiliaryStorage test_qgsauxiliarystorage.py)
ADD_PYTHON_TEST(PyQgsAuthManagerOgr test_authmanager_ogr.py)
ADD_PYTHON_TEST(PyQgsFieldValidator test_qgsfieldvalidator.py)
ADD_PYTHON_TEST(PyQgsPluginDependencies test_plugindependencies.py)
ADD_PYTHON_TEST(PyQgsDBManagerSQLWindow test_db_manager_sql_window.py)

IF (NOT WIN32)
ADD_PYTHON_TEST(PyQgsLogger test_qgslogger.py)
Expand Down
49 changes: 49 additions & 0 deletions tests/src/python/test_db_manager_sql_window.py
@@ -0,0 +1,49 @@
# -*- coding: utf-8 -*-
"""QGIS Unit tests for the DBManager SQL Window
.. note:: This program is free software; you can redistribute it and/or modify
it under the terms of the GNU General Public License as published by
the Free Software Foundation; either version 2 of the License, or
(at your option) any later version.
"""
__author__ = 'Stephen Knox'
__date__ = '2019-08-27'
__copyright__ = 'Copyright 2019, Stephen Knox'

from qgis.testing import unittest
from plugins.db_manager.dlg_sql_window import check_comments_in_sql


class TestPyQgsDBManagerSQLWindow(unittest.TestCase):

def test_no_comment_parsing(self):
query = "SELECT * FROM test"
self.assertEqual(check_comments_in_sql(query), query)

def test_comment_parsing(self):
query = "SELECT * FROM test -- WHERE a = 1 "
self.assertEqual(check_comments_in_sql(query), "SELECT * FROM test")

def test_comment_parsing_newline(self):
query = "SELECT * FROM test -- WHERE a = 1 \n ORDER BY b"
self.assertEqual(check_comments_in_sql(query), "SELECT * FROM test ORDER BY b")

def test_comment_parsing_newline2(self):
query = "SELECT * FROM test \n-- WHERE a = 1 \n ORDER BY b"
self.assertEqual(check_comments_in_sql(query), "SELECT * FROM test ORDER BY b")

def test_comment_parsing_nothing(self):
query = "--SELECT * FROM test"
self.assertEqual(check_comments_in_sql(query), "")

def test_comment_parsing_quote(self):
query = "SELECT * FROM test WHERE a = '--sdf'"
self.assertEqual(check_comments_in_sql(query), query)

def test_comment_parsing_identifier(self):
query = 'SELECT * FROM "test--1" WHERE a = 1'
self.assertEqual(check_comments_in_sql(query), query)


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

0 comments on commit a39a413

Please sign in to comment.