The README.md will be updated, so please check our project repository for the latest information.
The project website is available at https://github.com/haiqiang-zhang/AST-SQL-Dialects-Testing-Between-DBMSs
This study addresses the challenges of SQL dialect compatibility across various database management systems (DBMSs), including MySQL, PostgreSQL, SQLite, DuckDB, ClickHouse. Despite SQL’s standardization, differ- ences in dialects complicate interoperability and testing. This project introduces a comprehensive methodology and toolkit to evaluate SQL dialect compatibility by developing an extensive test suite. The test results are categorized into three classes: SAME, DIFFERENT, and ERROR, providing a granular analysis of compatibility. The study includes statistical examinations to identify specific areas of discrepancies and explores the causes at the code and DBMS architecture levels. Ultimately, this research aims to enhance testing efficiency and foster SQL dialect compatibility, contributing to the broader understanding of SQL interoperability in database technology
You should download the original test results from the following link: https://drive.google.com/file/d/1KuigPPFbBXgtShQpnQdS-ozRxswmJfZn/view?usp=sharing
The test results are available in the test_result
folder.
sql_classification_compatibility.csv
shows success, different and error rate of each SQL category for all the DBMSs involved in the test.sql_classification_rate_in_dbms.csv
shows the sql dialect classification of other DBMS that each dbms is most compatible with.dbms_compatibility_rates.csv
shows the compatibility rate of each DBMS with other DBMSs.
- put the test case in the
test_case
folder, the test case structure should be same as the currenttest_case
folder. - install the required packages by running the
pip install -r requirements.txt
command. - install and configure the DBMSs that you want to test.
- MySQL
- if you use MacOS, you can install the DBMSs using the
brew install
command. And the test toolkits will automatically detect the installed DBMSs and auto config. - if you use other OS, you should rewrite init_dbms method in the
adapter/MySQLAdapter.py
file to configure the DBMSs.
- if you use MacOS, you can install the DBMSs using the
- PostgreSQL
- Please install the PostgreSQL and start the service.
- add the superuser configuration in the
adapter/PostgreSQLAdapter.py
file.
- SQLite
- No need to install, the test toolkits will automatically create a SQLite database in the
test_db
folder.
- No need to install, the test toolkits will automatically create a SQLite database in the
- DuckDB
- Please install the DuckDB and start the service.
- ClickHouse
- Please install the ClickHouse and start the service.
- MySQL
- configure the test process in the start of
test_main.py
. - run the
test_main.py
script to test the compatibility of the SQL dialects.
There are three mandatory folders for each DBMS:
setup
: the setup SQL script for the DBMS.test
: the test SQL script for the DBMS.result
: the expected result of the test SQL script. (each test sql script should have a corresponding result file) Some of folders we provide for MySQL, PostgreSQL, SQLite, DuckDB, ClickHouse test cases:unsplit
: the sql script that combines setup and test cases together.test_raw
: the raw test sql script extracted from official test suites.