|
| 1 | +# -*- coding: utf-8 -*- |
| 2 | +"""QGIS Unit tests for QgsSQLStatement. |
| 3 | +
|
| 4 | +.. note:: This program is free software; you can redistribute it and/or modify |
| 5 | +it under the terms of the GNU General Public License as published by |
| 6 | +the Free Software Foundation; either version 2 of the License, or |
| 7 | +(at your option) any later version. |
| 8 | +""" |
| 9 | +__author__ = 'Even Rouault' |
| 10 | +__date__ = '4/4/2016' |
| 11 | +__copyright__ = 'Copyright 2016, The QGIS Project' |
| 12 | +# This will get replaced with a git SHA1 when you do a git archive |
| 13 | +__revision__ = '$Format:%H$' |
| 14 | + |
| 15 | +from qgis.testing import unittest |
| 16 | +from qgis.core import QgsSQLStatement |
| 17 | + |
| 18 | + |
| 19 | +class TestQgsSQLStatementCustomFunctions(unittest.TestCase): |
| 20 | + |
| 21 | + def checkNominal(self, statement, expected_dump=None): |
| 22 | + exp = QgsSQLStatement(statement) |
| 23 | + self.assertEqual(exp.hasParserError(), False) |
| 24 | + self.assertEqual(exp.parserErrorString(), "") |
| 25 | + if expected_dump is None: |
| 26 | + expected_dump = statement |
| 27 | + self.assertEqual(exp.dump(), expected_dump) |
| 28 | + self.assertEqual(exp.dump(), exp.rootNode().clone().dump()) |
| 29 | + |
| 30 | + def testNominalSimple(self): |
| 31 | + statement = "SELECT a FROM t" |
| 32 | + self.checkNominal(statement) |
| 33 | + exp = QgsSQLStatement(statement) |
| 34 | + statement_node = exp.rootNode() |
| 35 | + self.assertEqual(statement_node.nodeType(), QgsSQLStatement.ntSelect) |
| 36 | + tables = statement_node.tables() |
| 37 | + self.assertEqual(len(tables), 1) |
| 38 | + table = tables[0] |
| 39 | + self.assertEqual(table.nodeType(), QgsSQLStatement.ntTableDef) |
| 40 | + self.assertEqual(table.name(), 't') |
| 41 | + self.assertEqual(table.alias(), '') |
| 42 | + columns = statement_node.columns() |
| 43 | + self.assertEqual(len(columns), 1) |
| 44 | + column = columns[0] |
| 45 | + self.assertEqual(column.nodeType(), QgsSQLStatement.ntSelectedColumn) |
| 46 | + column_ref = column.column() |
| 47 | + self.assertEqual(column.alias(), '') |
| 48 | + self.assertEqual(column_ref.nodeType(), QgsSQLStatement.ntColumnRef) |
| 49 | + self.assertEqual(column_ref.name(), 'a') |
| 50 | + self.assertEqual(column_ref.tableName(), '') |
| 51 | + |
| 52 | + def testNominalSelectDistinct(self): |
| 53 | + statement = "SELECT DISTINCT a FROM t" |
| 54 | + self.checkNominal(statement) |
| 55 | + |
| 56 | + def testNominalColumns(self): |
| 57 | + statement = "SELECT null, 1234567890123456789, a, b b_alias, 'literal', CAST(1 AS varchar), " |
| 58 | + statement += "\"1c\", *, \"*\", a.*, foo(), bar(baz, baw), t.c AS \"1quoted\", " |
| 59 | + statement += "COUNT(*), COUNT(*) a, COUNT(DISTINCT x), COUNT(DISTINCT x) AS a FROM t" |
| 60 | + expected_dump = "SELECT NULL, 1234567890123456789, a, b AS b_alias, 'literal', CAST(1 AS varchar), " |
| 61 | + expected_dump += "\"1c\", *, \"*\", a.*, foo(), bar(baz, baw), t.c AS \"1quoted\", " |
| 62 | + expected_dump += "COUNT(*), COUNT(*) AS a, COUNT(DISTINCT x), COUNT(DISTINCT x) AS a FROM t" |
| 63 | + self.checkNominal(statement, expected_dump) |
| 64 | + |
| 65 | + def testNominalFrom(self): |
| 66 | + statement = "SELECT a FROM t1, t2 at2, t3 AS at3, \"1quoted\", t4 AS \"2quoted\"" |
| 67 | + expected_dump = "SELECT a FROM t1, t2 AS at2, t3 AS at3, \"1quoted\", t4 AS \"2quoted\"" |
| 68 | + self.checkNominal(statement, expected_dump) |
| 69 | + |
| 70 | + def testNominalWhere(self): |
| 71 | + statement = "SELECT a FROM t WHERE 1.5 <= 'a' OR TRUE OR FALSE OR a IS NULL AND b IS NOT NULL " + \ |
| 72 | + "OR NOT d OR 1 + (2 - 3) * 4 / 5 ^ 6 <> 0 OR a IN (1, 2) OR b NOT IN (5) " + \ |
| 73 | + "OR x BETWEEN 5 AND 6 OR x NOT BETWEEN 5 AND 6 OR c = d OR c > d OR c < d OR c >= d OR c <= d" |
| 74 | + self.checkNominal(statement) |
| 75 | + |
| 76 | + def checkJoinType(self, joinType): |
| 77 | + statement = "SELECT a FROM t " + joinType + " j1 ON TRUE" |
| 78 | + self.checkNominal(statement) |
| 79 | + |
| 80 | + def testJoinTypes(self): |
| 81 | + self.checkJoinType('JOIN') |
| 82 | + self.checkJoinType('LEFT JOIN') |
| 83 | + self.checkJoinType('LEFT OUTER JOIN') |
| 84 | + self.checkJoinType('RIGHT JOIN') |
| 85 | + self.checkJoinType('RIGHT OUTER JOIN') |
| 86 | + self.checkJoinType('CROSS JOIN') |
| 87 | + self.checkJoinType('FULL JOIN') |
| 88 | + self.checkJoinType('INNER JOIN') |
| 89 | + |
| 90 | + def testJoin(self): |
| 91 | + statement = "SELECT a FROM t JOIN j1 ON TRUE JOIN j2 USING (a) JOIN j3 USING (\"1a\", b)" |
| 92 | + self.checkNominal(statement) |
| 93 | + |
| 94 | + def testNominalOrderBy(self): |
| 95 | + statement = "SELECT a FROM t ORDER BY a, b ASC, c DESC" |
| 96 | + expected_dump = "SELECT a FROM t ORDER BY a, b, c DESC" |
| 97 | + self.checkNominal(statement, expected_dump) |
| 98 | + |
| 99 | + def testNominalFull(self): |
| 100 | + statement = \ |
| 101 | + "SELECT a FROM t JOIN j1 ON cond1 JOIN j2 ON cond2 WHERE TRUE ORDER BY c" |
| 102 | + self.checkNominal(statement) |
| 103 | + |
| 104 | + def checkError(self, statement): |
| 105 | + exp = QgsSQLStatement(statement) |
| 106 | + self.assertEqual(exp.hasParserError(), True) |
| 107 | + self.assertNotEqual(exp.parserErrorString(), '') |
| 108 | + self.assertEqual(exp.dump(), "(no root)") |
| 109 | + self.assertEqual(exp.rootNode(), None) |
| 110 | + |
| 111 | + def testError(self): |
| 112 | + self.checkError("1") |
| 113 | + self.checkError("SELECT") |
| 114 | + self.checkError("SELECT a") |
| 115 | + self.checkError("SELECT a, FROM b") |
| 116 | + self.checkError("SELECT 1a FROM b") |
| 117 | + self.checkError("SELECT a AS FROM b") |
| 118 | + self.checkError("SELECT a,. FROM b") |
| 119 | + self.checkError("SELECT f(*) FROM b") |
| 120 | + self.checkError("SELECT f(*) a FROM b") |
| 121 | + self.checkError("SELECT .") |
| 122 | + self.checkError("SELECT a FROM") |
| 123 | + self.checkError("SELECT a FROM b WHERE") |
| 124 | + self.checkError("SELECT a FROM b WHERE .") |
| 125 | + self.checkError("SELECT a FROM b,") |
| 126 | + self.checkError("SELECT a FROM b,.") |
| 127 | + self.checkError("SELECT a FROM b JOIN") |
| 128 | + self.checkError("SELECT a FROM b JOIN c") |
| 129 | + self.checkError("SELECT a FROM b JOIN c ON") |
| 130 | + self.checkError("SELECT a FROM b JOIN c USING") |
| 131 | + self.checkError("SELECT a FROM b JOIN c ON d JOIN") |
| 132 | + self.checkError("SELECT a FROM b ORDER BY") |
| 133 | + self.checkError("SELECT a FROM b JOIN c ON d ORDER BY e unexpected") |
| 134 | + |
| 135 | + def testBasicValidationCheck(self): |
| 136 | + exp = QgsSQLStatement("error") |
| 137 | + (b, errorMsg) = exp.doBasicValidationChecks() |
| 138 | + self.assertFalse(b) |
| 139 | + self.assertEqual(errorMsg, 'No root node') |
| 140 | + |
| 141 | + exp = QgsSQLStatement("SELECT c FROM t") |
| 142 | + (b, errorMsg) = exp.doBasicValidationChecks() |
| 143 | + self.assertTrue(b) |
| 144 | + self.assertEqual(errorMsg, '') |
| 145 | + |
| 146 | + exp = QgsSQLStatement("SELECT t.c FROM t ORDER BY t.c") |
| 147 | + (b, errorMsg) = exp.doBasicValidationChecks() |
| 148 | + self.assertTrue(b) |
| 149 | + self.assertEqual(errorMsg, '') |
| 150 | + |
| 151 | + exp = QgsSQLStatement("SELECT t.c FROM t t_alias") |
| 152 | + (b, errorMsg) = exp.doBasicValidationChecks() |
| 153 | + self.assertFalse(b) |
| 154 | + self.assertEqual( |
| 155 | + errorMsg, 'Table t is referenced by column c, but not selected in FROM / JOIN.') |
| 156 | + |
| 157 | + exp = QgsSQLStatement( |
| 158 | + "SELECT CAST(1 + foo(t_unknown.a) AS varchar) FROM t") |
| 159 | + (b, errorMsg) = exp.doBasicValidationChecks() |
| 160 | + self.assertFalse(b) |
| 161 | + self.assertEqual( |
| 162 | + errorMsg, 'Table t_unknown is referenced by column a, but not selected in FROM / JOIN.') |
| 163 | + |
| 164 | + exp = QgsSQLStatement("SELECT c FROM t WHERE t_unknown.a = 1") |
| 165 | + (b, errorMsg) = exp.doBasicValidationChecks() |
| 166 | + self.assertFalse(b) |
| 167 | + self.assertEqual( |
| 168 | + errorMsg, 'Table t_unknown is referenced by column a, but not selected in FROM / JOIN.') |
| 169 | + |
| 170 | + exp = QgsSQLStatement( |
| 171 | + "SELECT c FROM t JOIN t2 ON t.c1 = t2.c2 AND t3.c3 IS NOT NULL") |
| 172 | + (b, errorMsg) = exp.doBasicValidationChecks() |
| 173 | + self.assertFalse(b) |
| 174 | + self.assertEqual( |
| 175 | + errorMsg, 'Table t3 is referenced by column c3, but not selected in FROM / JOIN.') |
| 176 | + |
| 177 | + exp = QgsSQLStatement("SELECT c FROM t ORDER BY t_unknown.c") |
| 178 | + (b, errorMsg) = exp.doBasicValidationChecks() |
| 179 | + self.assertFalse(b) |
| 180 | + self.assertEqual( |
| 181 | + errorMsg, 'Table t_unknown is referenced by column c, but not selected in FROM / JOIN.') |
| 182 | + |
| 183 | +if __name__ == "__main__": |
| 184 | + unittest.main() |
0 commit comments