Dad can in B, or Simple and neat code generation for SQLite

image

Very soon, on November 4 and 5, we will start new courses SQL for data analysis and C ++ developer, especially for their start, we 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 a popular library SQLite, which allows developers to write stored procedures in Transact-SQL (T-SQL) 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 attempting to assign nullable columns to non-nullable output variables, and otherwise ensures 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 validate 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 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. 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 to insert into any table (here it is your_table), whose arguments are exactly the table columns. 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 a discount promo code HABR – no less useful, it will give you an additional 10% to the discount indicated on the banner.

image

More courses

Recommended articles

  • How to Become a Data Scientist Without Online Courses
  • 450 free courses from the Ivy League
  • How to learn Machine Learning 5 days a week for 9 months in a row
  • How much does a data analyst earn: an overview of salaries and vacancies in Russia and abroad in 2020
  • Machine Learning and Computer Vision in the Mining Industry

Similar Posts

Leave a Reply

Your email address will not be published. Required fields are marked *