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 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.
- 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
- Data Analytics Course
- DevOps course
- Profession Web developer
- The profession of iOS developer from scratch
- The profession of Android developer from scratch
- The profession of Java developer from scratch
- Course “Mathematics and Machine Learning for Data Science”
- Advanced Course “Machine Learning Pro + Deep Learning”
- 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