The cloud-native time-series database CnosDB has introduced the sqllogictest integration framework. By integrating sqllogictest into CnosDB, developers can more easily test and verify the correctness of the database, and quickly discover and solve potential issues. This new integration has significant implications for CnosDB, providing developers with more efficient and reliable testing tools. CnosDB also becomes the first time-series database to integrate sqllogictest, further improving stability and reliability.
About Sqllogictest
sqllogictest is an integration testing framework used to verify whether SQL database engines calculate the correct results. It can perform various SQL queries, including testing support for basic syntax, functions, operators, and data types. sqllogictest only concerns the correctness of query results, not database performance, indexing, transactions, concurrency, memory, or disk.
The main function of sqllogictest is to execute SQL queries and compare the expected results with the actual results. The framework can test various SQL queries, such as SELECT statements, INSERT statements, UPDATE statements, DELETE statements, and CREATE TABLE statements, among others. sqllogictest can also verify whether the SQL engine supports various data types, operators, and functions, and test whether different query statements have the expected effects.
It is worth noting that sqllogictest only concerns the correctness of query results, not database performance or other aspects. This means that it is primarily used to ensure that the SQL engine can calculate results correctly, rather than evaluating the overall performance of the database.
Reasons For Introducing Sqllogictest
The CnosDB technical team has always been committed to providing users with high stability and reliability products. To achieve this goal, we focus on logical testing of query engines, which is crucial to improving stability and reliability. By introducing sqllogictest, the CnosDB team found that they could gain the following benefits:
Firstly, sqllogictest can help with regression testing to ensure the stability of the CnosDB product. Regression testing refers to checking whether the original software functions remain intact after modification. When community developers modify the code, it is important to ensure that new bugs are not introduced, and that bugs that have been fixed do not reappear. By using sqllogictest, the CnosDB team can quickly and effectively test modified code to ensure that the CnosDB product remains stable.
Secondly, sqllogictest can help developers validate the correctness of SQL statements and find logical errors in the database. During the development process, developers need to write SQL queries to ensure that query results are correct. By using sqllogictest, developers can perform logical testing on SQL queries, identify potential errors and issues, and fix them in the early stages.
Thirdly, sqllogictest makes it very convenient and easy to write integration tests. Integration testing refers to testing whether all components of the entire system or application program are working properly. By using sqllogictest, the CnosDB team can easily write integration tests to ensure that all components of CnosDB can work together, ensuring product integrity and consistency.
Fourthly, sqllogictest is written in the Rust programming language, which is the same as CnosDB, which helps to develop the Rust language ecosystem. Rust is a fast, secure, and reliable programming language that, like C++ and C, can be used to write high-performance infrastructure programs. The reason why the CnosDB team uses Rust to write CnosDB is that it provides better performance, security, and reliability.
Finally, sqllogictest is a mature open-source project that can be freely used and modified. Many SQL-based databases or frameworks, such as DataFusion, are already using it, and its stability and ease of use have been proven. By using sqllogictest, the CnosDB team can gain support and help from the open-source community, improving product quality and reliability.
Using sqllogictest to Write SQL Tests
Below are the specific methods for writing SQL tests using sqllogictest, with the test file using the .slt extension.
- Verify successful statement execution
statement ok
CREATE TABLE foo(a BIGINT, b BIGINT, TAGS(c));
statement ok
INSERT INTO TABLE foo(time, a, b, c) VALUES (1, 1, 1, '1'), (2, 2, 2, '2') ;
2. Verify successful query statement
query T
SELECT * FROM foo ORDER BY time;
----
1970-01-01T00:00:00.000000001 1 1 1
1970-01-01T00:00:00.000000002 2 2 2
T represents the text of the comparison result set.
3. Verify statement execution failure
statement error Database .* forbid drop
DROP DATABASE public;
The error is followed by a regular expression used to match various error messages.
By simply configuring sqllogictest, you can quickly use it to help check for logical errors in SQL. For more usage methods, please visit https://github.com/cnosdb/cnosdb/tree/main/query_server/sqllogicaltests and https://github.com/risinglightdb/sqllogictest-rs.
The above is all the content of CnosDB supporting sqllogictest. For the CnosDB technical team, the pursuit of stability and reliability is endless. We welcome database and database testing enthusiasts to follow our WeChat public account, Bilibili account, and join our community. We will update the latest technical trends and technical insights in a timely manner and look forward to in-depth communication with everyone.
References: GitHub — risinglightdb/sqllogictest-rs: Sqllogictest parser and runner in Rust.