sql-testing-tools 0.3.0

A library that normalizes simple SQL queries and compares them first by equality of the normalized string and then using the cosette API.

[Beta in Development!]

• Submit bug reports and features requests at: https://github.com/ValentinHerrmann/sql_testing_tools
• PyPi-Package available at: https://pypi.org/project/sql-testing-tools/

Getting started

Imports

# ensure to always use the latest version of sql_testing_tools
import os
os.system('pip install -U sql_testing_tools')

# import sql_testing_tools
import sql_testing_tools.BaseAccess as Ba
import sql_testing_tools.Helper as He

# import unittest and create TestClass
import unittest 
class TestClass(unittest.TestCase):
    # Ba.setDBName(...)
    # ...

Choosing a database

On top level of your test class, set the SQLite database you want to use.

Option 1: Use the predefined ones from dbiu_databases which are based on datenbanken-im-unterricht.de

    # with DB name (preferred)
    # --> only available if current dbiu_databases version has been imported to sql_testing_tools
    Ba.setDBName("dbiu.bayern")

    # or with dbiu_databases version number
    # --> available as soon as a dbiu_databases version has been published on PyPi
    Ba.setDBName(2) 

Currently supported to use with DB name are:

1. dbiu.bahn: https://www.dbiu.de/bahn
2. dbiu.bayern: https://www.dbiu.de/bayern
3. dbiu.bundestag: https://www.datenbanken-im-unterricht.de/downloads/bundestag.zip
4. dbiu.bundestag_einfach: https://www.dbiu.de/bundestagsmitglieder
5. dbiu.film_fernsehen: https://www.dbiu.de/filmundfernsehen
6. dbiu.haushaltsausstattung: https://www.dbiu.de/haushaltsausstattung
7. dbiu.straftaten: https://www.datenbanken-im-unterricht.de/downloads/kriminalstatistik-erweitert.zip
8. dbiu.straftaten_einfach: https://www.dbiu.de/kriminalstatistik
9. dbiu.kunstsammlung: https://www.dbiu.de/kunstsammlung
10. dbiu.ladepunkte: https://www.dbiu.de/ladepunkte
11. dbiu.laenderspiele: https://www.dbiu.de/laenderspiele
12. dbiu.lebensmittel: https://www.dbiu.de/lebensmittel
13. dbiu.schulstatistik: https://www.dbiu.de/schulstatistik
14. dbiu.studierende: https://www.dbiu.de/studierende
15. dbiu.unfallstatistik: https://www.dbiu.de/unfallstatistik
16. dbiu.videospiele_einfach: https://www.dbiu.de/videospiele
17. dbiu.videospiele: https://www.dbiu.de/videospiele2
18. dbiu.wetterdaten: https://www.dbiu.de/wetterdaten
19. dbiu.terra: https://terra.sachsen.schule/

Option 2: Use your own SQLite database with relative path starting at the working dir of your test repository.

    Ba.setDBName("databases/bayern.db")

Available testing features

The following methods are available for use in test methods:

Run the query to find out if syntax/database errors occur.

try:
    Ba.runAndGetStringTable_fromFile("sqlfile.sql")
except Exception as e:
    # the execution failed (usually due to syntax or database errors)
    self.fail(e)

Set sql files to be compared (optional). The sql strings will be normalized and used or all following methods (to improve performance) until new files are specified. All check...() functions call setup(...) as first step. If one argument is "" or None the corresponding file is not changed. Therefore calling the check...() functions without arguments does not change the sql files that are compared.

# Raises an Exception if one of the files is empty. 
setup("sqlfile.sql","solution.sql")

Compared single parts of a sql query Call without arguments to keep the last normalized sql strings (and improve performance) and with one or two sql file paths to load and normalize new queries. Each check was successfull if "" is returned. Returns a German error message if not. Each method compares the normalized string between the start keyword and the next keyword or ;

res = He.checkColumns() # starts at "SELECT"
res = He.checkTables() # starts at "FROM"
res = He.checkCondition() # starts at "WHERE"
res = He.checkOrder() # starts at "ORDER BY"
res = He.checkGroup() # starts at "GROUP BY"

In special cases where you'd like to start and stop at individual keywords for comparison use this directly:

# Can not be called with new sql files. If needed call setup(...) before.
res = checkKeywords("startKeyword",["end","keywords"]) 

Compares equality of the full normalized sql strings and if not equal uses the Cosette API (cosette.cs.washington.edu) for comparison. A file cosette_apikey.txt with only the apikey in it on root level of the test repository is required to use this feature. If not existant, only the string comparison is performed. Warning: Cosette is currently not maintained!

res = checkEquality()

Changelog

Version 0.3 (since 0.2)

SQL Features

• Support LIMIT clause
• Support LIKE operator
• Support <= and >= (leq and geq)
• Support ASC/DESC in ORDER BY (also with multiple columns and order directions), no direction treated as ASC
• Support NOT
• Accept COUNT(colname) in addition to COUNT(*)
• Normalize string inequality: <>, !=, NOT LIKE → NOT...LIKE...
• Ignore case in = string comparison and therefore convert to LIKE (if no %, _ in string)

Test Features

• Added support for dbiu.de databases 1-18 (loaded via dbiu_databases) with option to still load DBs from local repo
• Added more check methods for single parts of queries: checkColumns, checkTables, checkCondition, checkOrder, checkGroup, checkKeywords
• Improved documentation
• Verified that ;, whitespaces (at the ende of the query), and linebreaks are ignored

Bug Fixes

• Fixed bug when having only one column selected
• Fixed handling of nested conditions with parantheses
• Fixed memory problem with huge cross products (used to lead to build failed)
• Fixed bug that forced irrelevant equality order if both sides aren't literals
• Fixed part checks if keywords are in wrong order
• Fixed bug in checkKeywords: if keyword was present in one query and not in the other, the comparison was not performed correctly

Other

• added dbiu.terra database support

Version 0.2

SQL Features

• Support SELECT: columns with our without table prefix
• Support FROM: one or more table from DB; no queries as tables!
• Support WHERE: single conditions; no Paranthesises!
• Support GROUP BY one or more columns
• Support Support ORDER BY
• Support chained conditions (with AND,OR and Paranthesises) in WHERE statement
• Support aggregate functions

Bug Fixes

• Fixed import error to ensure imports working in different environments
• Fixed linebreak problems: Linebreaks are now converted into whitespaces before parsing where tokens