DOCSEARCHbyAlgolia
Submit Docs

CG/SQL Documentation

website·Indexed 33 days ago

https://cgsql.dev

A code generation system for SQLite that enables developers to write stored procedures in a variant of T-SQL. The documentation covers the CQL language syntax, compiler internals (lexical analysis, semantic analysis, and C code generation), schema management, and production considerations.

Tokens
108.6K
Snippets
472
Records
768
Agent score
67%

What's inside CG/SQL

  1. Overview of CG/SQL and the CQL Compiler

    CG/SQL is a code generation system for SQLite that enables developers to write stored procedures using a variant of Transact-SQL (T-SQL). These procedures are compiled into C code that interacts with SQLite's C API.

    Key components include:

    • CQL Compiler: The core engine that reads schemas and procedures. It provides a strongly typed language that catches hundreds of potential runtime SQL issues at compile time, such as data type inconsistencies (e.g., assigning nullable columns to non-nullable variables).
    • Generated C Code: Automatically handles return code checks and ensures correct column ordinals and types during data binding and reading.
    • Schema Management: Uses schema annotations to automatically generate stored procedures for upgrading databases between versions.
    • Testing & Analysis: Supports the creation of unit test code and the generation of query plans at compile time via procedure annotations.

  2. Overview of CQL C Code Generation

    The C code generation pass, implemented in cg_c.c, translates the AST into C code. It is designed to run in a single pass over the AST and assumes the program is error-free (semantic analysis must report zero errors before codegen begins). Key responsibilities include:

    • Compiling SQL expressions (including nullable variables and complex constructs like CASE or IN) into C.
    • Generating control flow for IF, WHILE, and SWITCH statements.
    • Implementing economic result set creation by slurping SQL rows into arrays.
    • Generating SQLite statement text and binding variables.
    • Implementing consistent error checking for SQLite APIs and exception handling (e.g., TRY/CATCH).
    • Managing reference types with consistent retain/release pairs.
    • Producing .h and .c files, allowing contributions to different sections of the output files from various points in the compilation process.

  3. Overview of CQL Test Helper Generation

    The Test Helper generator in CQL focuses exclusively on procedures that have been annotated with specific attributes. It ignores the bodies of procedures and focuses only on their signatures to generate helpers. The generator identifies these procedures by scanning the AST for CREATE PROC statements and looking for attributes of the form @attribute(cql:autotest=xxx).

  4. Overview of CQL Compiler Testing Framework

    The CQL compiler uses a multi-stage testing suite launched via test.sh, which builds the compiler and executes over 3,000 tests. The framework maintains nearly 100% line coverage and high branch coverage (over 80% when asserts are disabled).

  5. Overview of CG/SQL (CQL)

    CG/SQL (CQL) is a strongly-typed, high-level SQL language designed as a precompiled addition to the SQLite runtime system. It allows developers to create control flow mixed with SQL operations, which the CQL compiler then lowers into C code. This process automates the generation of correct column indices, nullability checks, and error checks, reducing the brittleness associated with manual SQLite C API usage.

  6. Overview of CQL Semantic Analysis

    The semantic analysis pass verifies the correctness of a program by 'decorating' the AST with semantic information, primarily focusing on types. The process relies heavily on name resolution to determine the meaning of identifiers in context, which then allows the compiler to compute types and check for errors. Code generators only execute if semantic analysis completes without reporting errors.

  7. Overview of CQL Query Fragments

    CQL Query fragments allow developers to represent large queries as a set of smaller, manageable pieces. A 'core' base query fetches essential columns, and extension fragments add additional columns or rows. This modular approach ensures that errors are easier to isolate and that extensions only depend on the core query and their own specific requirements, rather than every other extension in the system.

  8. Overview of CG/SQL Key Features

    CG/SQL provides a set of tools to simplify database development with SQLite, specifically focusing on three core areas:

    1. Stored Procedures: Enables the creation of highly complex stored procedures with large queries while eliminating the need for manual code checking.
    2. Schema Management: Uses an annotation system to automatically manage and upgrade the database schema.
    3. Code Quality: Ensures consistency between the SQLite APIs and the schema through the use of annotated procedures.

  9. Organize schema using Schema Regions

    Schema Regions allow developers to divide a schema into logical, independent islands. This is used to:

    • Deploy different parts of the schema to different databases.
    • Upgrade specific regions on different schedules.
    • Omit irrelevant schema parts from certain distributions.
    • Enforce dependency rules to prevent unrelated regions from depending on each other (e.g., preventing an 'in-memory' region from referring to an 'on-disk' region).

  10. CQL Compiler Architecture Overview

    The CQL compiler is implemented using a standard flex (lexical analyzer) and bison (parser) pipeline. The grammar is based on a large subset of the SQLite dialect of SQL, augmented with control flow and compiler directives. The compiler processes input as plain text (ASCII), though UTF-8 characters are supported within string literals. All tokens are case-insensitive (ASCII only).

  11. Infer non-nullability using control flow analysis

    CQL uses control flow analysis to automatically infer when a variable of a nullable type must contain a non-null value based on the program's execution path. This reduces the need for explicit coercion functions like ifnull_crash or ifnull_throw.
    CREATE PROC some_proc(t0 TEXT, t1 TEXT)
BEGIN
  IF t0 IS NULL RETURN;
  -- `t0` is inferred as non-null here

  IF t1 IS NOT NULL THEN
    -- Both `t0` and `t1` are inferred as non-null here
    CALL another_proc(t0, t1);
  ELSE
    -- `t0` is still non-null here
    CALL another_proc(t0, "default");
  END IF;
END;

  12. Define CQL value types

    The CQL runtime allows the definition of basic value types to match the target system's architecture. These are typically mapped to standard C types or SQLite types.
    typedef unsigned char cql_bool;
#define cql_true (cql_bool)1
#define cql_false (cql_bool)0

typedef unsigned long cql_hash_code;
typedef int32_t cql_int32;
typedef uint32_t cql_uint32;
typedef uint16_t cql_uint16;
typedef sqlite3_int64 cql_int64;
typedef double cql_double;
typedef int cql_code;