Very soon, on November 4 and 5, we will start new streams of SQL courses for data analysis and a C ++ developer , especially for their start, we have prepared this translation from the Facebook Engineering blog with an overview of a useful tool.
SQLite is widely used, but writing well tested and supported data access layers can be challenging at best. Many commands use some kind of code generation to avoid having to change dozens of sequence numbers every time a column is added, but this approach leads to errors. The CQL to CG / SQL compiler allows you to create complex stored procedures with large queries, and combinations of syntactic helpers and strong typing make these procedures much easier to obtain and store. The combination of strong typing in a language and a tool for good unit testing can provide confidence that even very complex logic is correct.
What it is
CG / SQL is a code generation system for the popular SQLite library that allows developers to write stored procedures in a Transact-SQL (T-SQL) variant and compile them to C code that uses the SQLite API to perform C operations. CG / SQL allows engineers to create complex stored procedures with large queries without the manual code review required by existing methods.
The entire system also includes functions for managing and updating the schema, generating test code for stored procedures, obtaining query plans for procedures, and interacting with stored procedures from other languages such as Java and Objective-C. JSON output allows parsing or front-end code. The package contains extensive language and system documentation.
What the tool does
The CQL compiler does most of the dirty work. It reads schema and procedures, providing a strongly typed language with hundreds of compilation errors designed to prevent SQLite runtime problems. The compiler carefully monitors variable data types and schema types, reporting inconsistencies, such as trying to assign nullable columns to non-nullable output variables, and otherwise ensuring that the SQLite APIs are used consistently and correctly.
The generated code always checks for return codes, and it always uses the correct ordinal and column types when binding or reading data to or from SQLite. This is exactly what is difficult to obtain and keep correctly. In addition, schema annotations allow the system to automatically create stored procedures that update the database from any previous version of the schema to the current version. To make this possible, dozens of checks are carried out.
Procedure annotations can also be used to indicate that you would like to maintain the test code for creating schema fragments and inserting data into that schema. This approach allows you to test procedures with almost no fuss, and also not depend on the system being deployed. Similarly, these tools can create schemas that check query plans at compile time.
Why is this needed?
SQLite is widely used, but writing well tested and supported data access layers can be challenging at best. Many commands use some kind of code generation to avoid having to change dozens of sequence numbers every time a column is added, but this approach leads to errors. The CQL to CG / SQL compiler allows you to create complex stored procedures with large queries, and combinations of syntax helpers and strong typing make these procedures much easier to obtain and store. The combination of strong typing in a language and a tool for good unit testing can provide confidence that even very complex logic is correct. Syntactic helpers convert safe code to canonical SQL, so engineers write less code, but the code is more correct and it runs everywhere.Let's look at an example:
create procedure insert_a_row(like your_table)
begin
insert into your_table from arguments;
end;
This code creates a procedure for inserting into any table (here it is
your_table), the arguments of which are exactly the columns of the table. You won't forget about the columns, you won't put dozens of arguments in the wrong order. The constructs are concise and robust, which makes it easier for engineers to generate code without having to manually check every bit of it.
CG / SQL on Github
CG / SQL is certainly a useful thing, but the HABR discount promo code is no less useful, it will give you an additional 10% to the discount indicated on the banner.
- SQL for data analysis
- C ++ developer
- Machine Learning Course
- Data Science profession training
- Data Analyst training
- Data Analytics Online Bootcamp
- Python for Web Development Course
More courses