Chapter 1: Introduction

CQL was designed as a compiler for the SQLite runtime system. SQLite lacks stored procedures but has a rich C runtime interface that allows you to create any kind of control flow mixed with any SQL operations that you might need. However, SQLite’s programming interface is both verbose and error-prone in that small changes in SQL statements can require significant swizzling of the C code that calls them. Additionally, many of the SQLite runtime functions have error codes which must be strictly checked to ensure correct behavior. In practice, it’s easy to get some or all of this wrong.

CQL simplifies this situation by providing a high-level SQL language not unlike the stored procedure forms that are available in client/server SQL solutions and lowering that language to “The C you could have written to do that job using the normal SQLite interfaces.”

As a result, the C generated is generally very approachable but now the source language does not suffer from brittleness due to query or table changes, and CQL always generates correct column indices, nullability checks, error checks, and the other miscellany needed to use SQLite correctly.

CQL is also strongly typed, whereas SQLite is very forgiving with regard to what operations are allowed on which data. Strict type checking is much more reasonable given CQL’s compiled programming model.

NOTE: CQL was created to help solve problems in the building of Meta Platforms’s Messenger application, but this content is free from references to Messenger. The CQL code generation here is done in the simplest mode with the fewest runtime dependencies allowed for illustration.

Getting Started

Before starting this tutorial, make sure you have built the cql executable first in Building CG/SQL

The “Hello World” program rendered in CQL looks like this:

-- needed to allow vararg calls to C functions
declare procedure printf no check;

create proc hello()
begin
  call printf("Hello, world\n");
end;

This very nearly works exactly as written but we’ll need a little bit of glue to wire it all up.

First, assuming you have built cql, you should have the power to do this:

$ cql --in hello.sql --cg hello.h hello.c

This will produce the C output files hello.c and hello.h which can be readily compiled.

However, hello.c will not have a main – rather it will have a function like this:

...
void hello(void);
...

The declaration of this function can be found in hello.h.

NOTE: hello.h attempts to include cqlrt.h. To avoid configuring include paths for the compiler, you might keep cqlrt.h in the same directory as the examples and avoid that complication. Otherwise, you must make arrangements for the compiler to locate cqlrt.h, either by adding it to an INCLUDE path or by incorporating some -I options to aid the compiler in finding the source.

That hello function is not quite adequate to get a running program, which brings us to the next step in getting things running. Typically, you have some kind of client program that will execute the procedures you create in CQL. Let’s create a simple one in a file we’ll creatively name main.c.

A very simple CQL main might look like this:

#include <stdlib.h>
#include "hello.h"
int main(int argc, char **argv)
{
   hello();
   return 0;
}

Now we should be able to do the following:

$ cc -o hello main.c hello.c
$ ./hello
Hello, world

Congratulations, you’ve printed "Hello, world" with CG/SQL!

Why did this work?

A number of things are going on even in this simple program that are worth discussing:

• the procedure hello had no arguments, and did not use the database
  • therefore its type signature when compiled will be simply void hello(void); so we know how to call it
  • you can see the declaration for yourself by examining the hello.c or hello.h
• since nobody used a database, we didn’t need to initialize one
• since there are no actual uses of SQLite, we didn’t need to provide that library
• for the same reason, we didn’t need to include a reference to the CQL runtime
• the function printf was declared “no check”, so calling it creates a regular C call using whatever arguments are provided, in this case a string
• the printf function is declared in stdio.h which is pulled in by cqlrt.h, which appears in hello.c, so it will be available to call in the generated C code
• CQL allows string literals with double quotes, and those literals may have most C escape sequences in them, so the “” bit works
  • Normal SQL string literals (also supported) use single quotes and do not allow, or need escape characters other than '' to mean one single quote

All of these facts put together mean that the normal, simple linkage rules result in an executable that prints the string “Hello, world” and then a newline.

Variables and Arithmetic

Borrowing once again from examples in “The C Programming Language”, it’s possible to do significant control flow in CQL without reference to databases. The following program illustrates a variety of concepts:

-- needed to allow vararg calls to C functions
declare procedure printf no check;

-- print a conversion table  for temperatures from 0 to 300
create proc conversions()
begin
  -- not null can be abbreviated with '!'
  declare fahr, celsius int!;

  -- variable type can be implied
  -- these are all int not null  (or int!)
  let lower := 0;   /* lower limit of range */
  let upper := 300; /* upper limit of range */
  let step := 20;   /* step size */

  -- this is the canonical SQL assignment syntax
  -- but there are shorthand versions available in CQL
  set fahr := lower;
  while fahr <= upper
  begin
    -- top level assignment without 'set' is ok
    celsius := 5 * (fahr - 32) / 9;
    call printf("%d\t%d\n", fahr, celsius);

    -- the usual assignment ops are supported
    fahr += step;
  end;
end;

You may notice that both the SQL style -- line prefix comments and the C style /* */ forms are acceptable comment forms.

Like C, in CQL all variables must be declared before they are used. They remain in scope until the end of the procedure in which they are declared, or they are global scoped if they are declared outside of any procedure. The declarations announce the names and types of the local variables. Importantly, variables stay in scope for the whole procedure even if they are declared within a nested begin and end block.

The most basic types are the scalar or “unitary” types (as they are referred to in the compiler)

NOTE: SQLite makes no distinction between integer storage and long integer storage, but the declarations tell CQL whether it should use the SQLite methods for binding and reading 64-bit or 32-bit quantities when using the variable or column so declared.

There will be more notes on these types later, but importantly, all keywords and names in CQL are case insensitive just like in the underlying SQL language. Additionally all of the above may be combined with not null to indicate that a null value may not be stored in that variable (as in the example). When generating the C code, the case used in the declaration becomes the canonical case of the variable and all other cases are converted to that in the emitted code. As a result, the C remains case sensitively correct.

The size of the reference types is machine dependent, whatever the local pointer size is. The non-reference types use machine independent declarations like int32_t to get exactly the desired sizes in a portable fashion.

All variables of a reference type are set to NULL when they are declared, including those that are declared NOT NULL. For this reason, all nonnull reference variables must be initialized (i.e., assigned a value) before anything is allowed to read from them. This is not the case for nonnull variables of a non-reference type, however: They are automatically assigned an initial value of 0, and thus may be read from at any point.

The programs execution begins with three assignments:

let lower := 0;
let upper := 300;
let step := 20;

This initializes the variables just like in the isomorphic C code. Statements are separated by semicolons, just like in C. In the above, the data type of the variable was inferred because the let keyword was used.

The table is then printed using a while loop.

while fahr <= upper
begin
  ...
end;

The above has the usual meaning, with the statements in the begin/end block being executed repeatedly until the condition becomes false.

The body of a begin/end block such as the one in the while statement can contain one or more statements.

The typical computation of Celsius temperature ensues with this code:

celsius := 5 * (fahr - 32) / 9;
call printf("%d\t%d\n", fahr, celsius);
fahr += step;

This computes the Celsius temperature and then prints it out, moving on to the next entry in the table. Note that we have started using some shorthand. SET can be omitted. And the += assignment operators are also supported. Top-level procedure calls can also be made without the call keyword; that, too, could have been omitted.

Importantly, the CQL compiler uses the normal SQLite order of operations, which is NOT the same as the C order of operations. As a result, the compiler may need to add parentheses in the C output to get the correct order; or it may remove some parentheses because they are not needed in the C order even though they were in the SQL order.

The printf call operates as before, with the fahr and celsius variables being passed on to the C runtime library for formatting, unchanged.

NOTE: When calling “unchecked” native functions like printf, string literals are simply passed through unchanged as C string literals. No CQL string object is created.

Basic Conversion Rules

As a rule, CQL does not perform its own conversions, leaving that instead to the C compiler. An exception to this is that boolean expressions are normalized to a 0 or 1 result before they are stored.

However, even with no explicit conversions, there are compatibility checks to ensure that letting the C compiler do the conversions will result in something sensible. The following list summarizes the essential facts/rules as they might be applied when performing a + operation.

• the numeric types are bool, int, long, real
• non-numeric types cannot be combined with numerics, e.g. 1 + ‘x’ always yields an error
• any numeric type combined with itself yields the same type
• bool combined with int yields int
• bool or int combined with long yields long
• bool, int, or long combined with real yields real

Preprocessing Features

CQL includes its own pre-processor, it is described in Chapter 18. While it is still possible to use the C pre-processor in front of CQL as was once necessary this practice is now deprecated. The usual pre-processor features are supported:

• macros (@macro)
• including files (@include)
• conditional compilation (@ifdef, @ifndef)
• token pasting (@text and @id)

Chapter 2: Using Data

The point of using CQL is to facilitate access to a SQLite database, so we’ll switch gears to a slightly more complicated setup. We’ll still keep things fairly simple, but let’s start to use some database features.

NOTE: It is not the intent of this tutorial to also be a primer for the SQLite programming language, which is ably documented on sqlite.org. Please refer to that site for details on the meaning of the SQL statements used here if you are new to SQL.

A Sample Program

Suppose we have the following program:

-- needed to allow vararg calls to C functions
declare procedure printf no check;

create table my_data(t text not null);

create proc hello()
begin
  insert into my_data(t) values("Hello, world\n");
  declare t text not null;
  set t := (select * from my_data);
  call printf('%s', t);
end;

The above is an interesting little baby program, and it appears as though it would once again print that most famous of salutations, “Hello, world”.

Well, it doesn’t. At least, not yet. Let’s walk through the various things that are going to go wrong as this will teach us everything we need to know about activating CQL from some environment of your choice.

Providing a Suitable Database

CQL is just a compiler; it doesn’t know how the code it creates will be provisioned any more than, say, clang does. It creates functions with predictable signatures so that they can be called from C just as easily as the SQLite API itself, and using the same currency. Our new version of hello now requires a database handle because it performs database operations. Also, there are now opportunities for the database operations to fail, and so hello now provides a return code.

A new minimal main program might look something like this:

#include <stdlib.h>
#include <sqlite3.h>

#include "hello.h"

int main(int argc, char **argv)
{
  sqlite3 *db;
  int rc = sqlite3_open(":memory:", &db);
  if (rc != SQLITE_OK) {
    exit(1); /* not exactly world class error handling but that isn't the point */
  }
  rc = hello(db);
  if (rc != SQLITE_OK) {
    exit(2);
  }

  sqlite3_close(db);
  return 0;
}

If we re-run CQL and look in the hello.h output file we’ll see that the declaration of the hello function is now:

...
extern CQL_WARN_UNUSED cql_code hello(sqlite3 *_Nonnull _db_);
...

This indicates that the database is used and a SQLite return code is provided. We’re nearly there. If you attempt to build the program as before, there will be several link-time errors due to missing functions. Typically, these are resolved by providing the SQLite library to the command line and also adding the CQL runtime. The new command line looks something like this:

$ cc -o hello main.c hello.c cqlrt.c -lsqlite3
$ ./hello
Hello, world

The CQL runtime can be placed anywhere you want it to be, and of course the usual C separate compilation methods can be applied. More on that later.

But actually, that program doesn’t quite work yet. If you run it, you’ll get an error result code, not the message “Hello, world”.

Let’s talk about the final missing bit.

Declaring Schema

In CQL, a loose piece of Data Definition Language (henceforth DDL) does not actually create or drop anything. In most CQL programs, the normal situation is that “something” has already created the database and put some data in it. You need to tell the CQL compiler about the schema so that it knows what the tables are and what to expect to find in those tables. This is because typically you’re reconnecting to some sort of existing database. So, in CQL, loose DDL simply declares schema, it does not create it. To create schema you have to put the DDL into a procedure you can run. If you do that, then the DDL still serves a declaration, but also the schema will be created when the procedure is executed.

We need to change our program a tiny bit.

-- needed to allow vararg calls to C functions
declare procedure printf no check;

create proc hello()
begin
  create table my_data(t text not null);
  insert into my_data(t) values("Hello, world\n");

  declare t text not null;
  set t := (select * from my_data);
  call printf('%s', t);
  drop table my_data;
end;

If we rebuild the program, it will now behave as expected.

Explaining The New Hello World

Let’s go over every important line of the new program, starting from main:

int rc = sqlite3_open(":memory:", &db);

This statement gives us an empty, private, in-memory only database to work with. This is the simplest case and it’s still very useful. The sqlite_open and sqlite_open_v2 functions can be used to create a variety of databases per the SQLite documentation.

We’ll need such a database to use our procedure, and we use it in the call here:

rc = hello(db);

This provides a valid database handle to our procedure. Note that the procedure doesn’t know what database it is supposed to operate on; it expects to be handed a suitable database on a silver platter. In fact, any given procedure could be used with various databases at various times. Just like SQLite, CQL does not enforce any particular database setup; it just uses the provided database.

When hello runs, we begin with:

create table my_data(t text not null);

This will create the my_data table with a single column t, of type text not null. That will work because we know we’re going to call this with a fresh/empty database. More typically you might do create table if not exists... or otherwise have a general attach/create phase or something to that effect. We’ll dispense with that here.

Next, we’ll run the insert statement:

insert into my_data(t) values("Hello, world\n");

This will add a single row to the table. Note that we have again used double quotes, meaning that this is a C string literal. This is highly convenient given the escape sequences. Normally SQLite text has the newlines directly embedded in it; that practice isn’t very compiler friendly, hence the alternative.

Next, we declare a local variable to hold our data:

declare t text not null;

Then, we can read back our data:

set t := (select * from my_data);

This form of database reading has very limited usability but it does work for this case and it is illustrative. The presence of (select ...) indicates to the CQL compiler that the parenthesized expression should be given to SQLite for evaluation according to the SQLite rules. The expression is statically checked at compile time to ensure that it has exactly one result column. In this case the * is just column t, and actually it would have been clearer to use t directly here but then there wouldn’t be a reason to talk about * and multiple columns. At run time, the select query must return exactly one row or an error code will be returned. It’s not uncommon to see (select ... limit 1) to force the issue. But that still leaves the possibility of zero rows, which would be an error. We’ll talk about more flexible ways to read from the database later.

You can declare a variable and assign it in one step with the LET keyword, e.g.

let t := (select * from my_data);

The code would normally be written in this way but for discussion purposes, these examples continue to avoid LET.

At this point it seems wise to bring up the unusual expression evaluation properties of CQL. CQL is by necessity a two-headed beast. On the one side there is a rich expression evaluation language for working with local variables. Those expressions are compiled into C (or Lua) logic that emulates the behavior of SQLite on the data. It provides complex expression constructs such as IN and CASE but it is ultimately evaluated by C execution. Alternately, anything that is inside of a piece of SQL is necessarily evaluated by SQLite itself. To make this clearer let’s change the example a little bit before we move on.

set t := (select "__"||t||' '||1.234 from my_data);

This is a somewhat silly example but it illustrates some important things:

• even though SQLite doesn’t use double quotes for string literals, that’s no problem because CQL will convert the string into a single quoted version with the correct escape values as a matter of course during compilation
• the || concatenation operator is evaluated by SQLite
• you can mix and match both kinds of string literals, they will all be the single quote variety by the time SQLite sees them
• the || operator has lots of complex formatting conversions (such as converting real values to strings) * in fact the conversions are so subtle as to be impossible to emulate in loose C code with any economy, so, like a few other operators, || is only supported in the SQLite context

Returning now to our code as written, we see something very familiar:

call printf('%s', t);

Note that we’ve used the single quote syntax here for no particular reason other than illustration. There are no escape sequences here, so either form would suffice. Importantly, the string literal will not create a string object as before, but the text variable t is, of course, a string reference. Before it can be used in a call to an undeclared function, it must be converted into a temporary C string. This might require allocation in general; that allocation is automatically managed.

Also, note that CQL assumes that calls to “no check” functions should be emitted as written. In this way, you can use printf even though CQL knows nothing about it.

Lastly, we have:

drop table my_data;

This is not strictly necessary because the database is in memory anyway and the program is about to exit but there it is for illustration.

Now Data Manipulation Language (i.e. insert and select here; and henceforth DML) and DDL might fail for various reasons. If that happens the procedure will goto a cleanup handler and return the failed return code instead of running the rest of the code. Any temporary memory allocations will be freed and any pending SQLite statements will be finalized. More on that later when we discuss error handling.

With that we have a much more complicated program that prints “Hello, world”

Introducing Cursors

In order to read data with reasonable flexibility, we need a more powerful construction. Let’s change our example again and start using some database features.

declare procedure printf no check;

-- for previty the `create` in `create proc` can be elided
proc hello()
begin
  -- this time we use the ! short hand for not null
  create table my_data(
    pos int! primary key,
    txt text!
  );

  -- you can supply more than one set of values in a single insert
  -- but we didn't here.
  insert into my_data values(2, 'World');
  insert into my_data values(0, 'Hello');
  insert into my_data values(1, 'There');

  declare C cursor for select * from my_data order by pos;

  loop fetch C
  begin
    -- we elided the 'call' here to show the briefer syntax
    printf("%d: %s\n", C.pos, C.txt);
  end;

  close C;

  drop table my_data;
end;

Reviewing the essential parts of the above.

create table my_data(
  pos integer not null primary key,
  txt text not null
);

The table now includes a position column to give us some ordering. That is the primary key.

insert into my_data values(2, 'World');

The insert statements provide both columns, not in the printed order. The insert form where the columns are not specified indicates that all the columns will be present, in order; this is more economical to type. CQL will generate errors at compile time if there are any missing columns or if any of the values are not type compatible with the indicated column.

The most important change is here:

declare C cursor for select * from my_data order by pos;

We’ve created a non-scalar variable C, a cursor over the indicated result set. The results will be ordered by pos.

loop fetch C
begin
  ...
end;

This loop will run until there are no results left (it might not run at all if there are zero rows, that is not an error). The FETCH construct allows you to specify target variables, but if you do not do so, then a synthetic structure is automatically created to capture the projection of the select. In this case the columns are pos and txt. The automatically created storage exactly matches the type of the columns in the select list (which could itself be a tricky calculation). In this case the select is quite simple and the columns of the result directly match the schema for my_data. An integer and a string reference. Both not null.

printf("%d: %s\n", C.pos, C.txt);

The storage for the cursor is given the same names as the columns of the projection of the select, in this case the columns were not renamed in the select clause so pos and txt are the fields in the cursor. Double quotes were used in the format string to get the newline in there easily.

close C;

The cursor is automatically released at the end of the procedure, but in this case, we’d like to release it before the drop table operation occurs. Therefore, an explicit close statement is included. However, this sort of close is frequently omitted since automatic cleanup takes care of it.

If you compile and run this program, you’ll get this output:

$ cc -x c -E hello.sql | cql --cg hello.h hello.c
$ cc -o hello main.c hello.c cqlrt.c -lsqlite3
$ ./hello
0: Hello
1: There
2: World

So the data was inserted and then sorted.

Going Crazy

We’ve only scratched the surface of what SQLite can do, and almost all DML constructs are supported by CQL. This includes common table expressions, and even recursive versions of the same. But remember, when it comes to DML, the CQL compiler only has to validate the types and figure out what the result shape will be – SQLite always does all the heavy lifting of evaluation. All of this means with remarkably little additional code, the example below from the SQLite documentation can be turned into a CQL stored proc using the constructs we have defined above.

-- needed to allow vararg calls to C functions
declare proc printf no check;

-- proc and procedure can be used interchangeably
procedure mandelbrot()
begin
  -- this is basically one giant select statement
  declare C cursor for
    with recursive
      -- x from -2.0 to +1.2
      xaxis(x) as (select -2.0 union all select x + 0.05 from xaxis where x < 1.2),

      -- y from -1.0 to +1.0
      yaxis(y) as (select -1.0 union all select y + 0.1 from yaxis where y < 1.0),

      m(iter, cx, cy, x, y) as (
        -- initial seed iteration count 0, at each of the points in the above grid
        select 0 iter, x cx, y cy, 0.0 x, 0.0 y from xaxis, yaxis
        union all
        -- the next point is always iter +1, same (x,y) and the next iteration of z^2 + c
        select iter+1 iter, cx, cy, x*x-y*y + cx x, 2.0*x*y + cy y from m
        -- stop condition, the point has escaped OR iteration count > 28
        where (m.x*m.x + m.y*m.y) < 4.0 and m.iter < 28
      ),
      m2(iter, cx, cy) as (
       -- find the last iteration for any given point to get that count
       select max(iter), cx, cy from m group by cx, cy
      ),
      a(t) as (
        -- convert the iteration count to a printable character, grouping by line
        select group_concat(substr(" .+*#", 1 + min(iter/7,4), 1), '')
        from m2 group by cy
      )
    -- group all the lines together
    select rtrim(t) line from a;

  -- slurp out the data
  loop fetch C
  begin
    call printf("%s\n", C.line);
  end;
end;

The above code uses various SQLite features to generate this text:

$
                                     ....#
                                    ..#*..
                                  ..+####+.
                             .......+####....   +
                            ..##+*##########+.++++
                           .+.##################+.
               .............+###################+.+
               ..++..#.....*#####################+.
              ...+#######++#######################.
           ....+*################################.
  #############################################...
           ....+*################################.
              ...+#######++#######################.
               ..++..#.....*#####################+.
               .............+###################+.+
                           .+.##################+.
                            ..##+*##########+.++++
                             .......+####....   +
                                  ..+####+.
                                    ..#*..
                                     ....#
                                      +.

The above won’t come up very often, but it does illustrate several things:

• WITH RECURSIVE actually provides a full lambda calculus so arbitrary computation is possible
• You can use WITH RECURSIVE to create table expressions that are sequences of numbers easily, with no reference to any real data

Chapter 3: Expression Fundamentals

Until this point we’ve only discussed simple kinds of expressions as well as variables and table columns marked with NOT NULL. These are indeed the easiest types for CQL to work with as they tend to correspond most directly to the types known to C. However, SQL provides for many more types of expressions as well as nullable types, and these require handling in any language that purports to be like SQL.

Expression Examples

The usual arithmetic operators apply in CQL:

Example expressions (these are all true)

(1 + 2) * 3 == 9
1 + 2 * 3 == 7
6 / 3 == 2
7 - 5 == 2
6 % 5 == 1
5 / 2.5 == 2
7 & 3 == 2 | 1
1 << 2 == 4

However, before going any further, it’s important to note that CQL is inherently a two-headed beast. Expressions are either evaluated by transpiling to C (like the predicate of an IF statement or a variable assignment) or by sending them to SQLite for evaluation (like expressions inside a SELECT statement or the WHERE part of a DELETE).

CQL evaluation rules are designed to be as similar as possible, but some variance is inevitable because evaluation is done in two fundamentally different ways.

Operator Precedence

The operator precedence rules in CQL are as follows; the top-most rule binds the most loosely, and the bottom-most rule binds the most tightly:

ASSIGNMENT:     := += -= /= *= %= &= |= <<= >>=
LOGICAL_OR:     OR
LOGICAL_AND:    AND
LOGICAL_NOT:    NOT
EQUALITY:       = == != <>  IS [NOT], [NOT] IN, [NOT] LIKE,
                [NOT] MATCH, [NOT] GLOB, [NOT] BETWEEN
INEQUALITY:     <  <=  >  >=
BINARY:         << >> & |
ADDITION:       + -
MULTIPLICATION: * / %
CONCAT:         || -> ->>
COLLATE:        COLLATE
UNARY:          ~  -
SPECIAL:        x[] x:y x::y x:::y x.y f(y) CAST CASE ~type~

The above rules are not the same as C’s operator precedence rules! Instead, CQL follows SQLite’s rules. Parentheses are emitted in the C output as needed to force that order.

NOTE: CQL emits minimal parentheses in all outputs. Different parentheses are often needed for SQL output as opposed to C output.

Order of Evaluation

In contrast to C, CQL guarantees a left-to-right order of evaluation for arguments. This applies both to arguments provided to the operators mentioned in the previous section as well as arguments provided to procedures.

Variables, Columns, Basic Types, and Nullability

CQL needs type information for both variables in the code and columns in the database. Like SQL, CQL allows variables to hold a NULL value, and just as in SQL, the absence of NOT NULL implies that NULL is a legal value. Consider these examples:

-- real code should use better names than this :)
create table all_the_nullables(
  i1 integer,
  b1 bool,
  l1 long,
  r1 real,
  t1 text,
  bl1 blob
);

declare i2 integer;
declare b2 bool;
declare l2 long;
declare r2 real;
declare t2 text;
declare bl2 blob;

All of i1, i2, b1, b2, l1, l2, r1, r2, t1, t2, and bl1, bl2 are nullable. In some sense, variables and columns declared nullable (by virtue of the missing NOT NULL) are the root sources of nullability in the SQL language. That and the NULL literal. Though there are other sources as we will see.

NOT NULL could be added to any of these, e.g.

-- real code should use better names than this :)
declare i_nn integer not null;

In fact, NOT NULL is so common in CQL code that it can be abbreviated with a single ! character. Some languages use ? to make a type nullable, but since nullable is the default in SQL, CQL opts for the reverse. Hence, the following code is equivalent to the above:

-- "int" is equivalent to "integer" and "!" is equivalent to "not null"
declare i_nn int!;

In the context of computing the types of expressions, CQL is statically typed, and so it must make a decision about the type of any expression based on the type information at hand at compile time. As a result, it handles the static type of an expression conservatively. If the result might be null, then the expression is of a nullable type, and the compiled code will include an affordance for the possibility of a null value at runtime.

The generated code for nullable types is considerably less efficient, and so it should be avoided if that is reasonably possible.

Quoted Identifiers

In places where a SQL name is allowed, such as a table name, column name, index name, trigger name, or constraint name, a back-quoted identifier may be used. This allows for the more flexible names supported by SQLite to appear anywhere they might be needed.

Example:

  create table `my table` (
    `a column` integer
  );

Since SQL names “leak” into the language via cursors, other places a SQL name might appear have similar flexibility. For instance, names of variables, and columns in cursors can have exotic names.

When rendered to SQL, the name will be emitted like so:

  [my table]

If the name goes to C or Lua, it has to be escaped and rendered like so:

  X_aX20table

In this form, non-identifier characters are escaped into hex. This is invisible to users of CQL, but the C or Lua interface to such columns necessarily uses the escaped names. While this is less than perfect, it is the only way to allow access to any legal SQL name.

LET Statement

You can declare and initialize a variable in one step using the LET form, e.g.

LET x := 1;

The named variable is declared to be the exact type of the expression on the right. More on expressions in the coming sections. The right side is often a constant in these cases but does not need to be.

LET i := 1;  -- integer not null
LET l := 1L;  -- long not null
LET t := "x";  -- text not null
LET b := x IS y; -- bool not null
LET b := x = y;  -- bool (maybe not null depending on x/y)

The pseudo-function “nullable” removes not null from the type of its argument but otherwise does no computation. This can be useful to initialize nullable types.

LET n_i := nullable(1);  -- nullable integer variable initialized to 1
LET n_l := nullable(1L);  -- nullable long variable initialized to 1

The pseudo-function “sensitive” adds @sensitive to the type of its argument but otherwise does no computation. This also can be useful to initialize nullable types.

LET s_i := sensitive(1);  -- sensitive nullable integer variable initialized to 1
LET s_l := sensitive(1L);  -- sensitive nullable long variable initialized to 1

The @RC special variable

CQL also has the special built-in variable @RC, which refers to the most recent error code returned by a SQLite operation, e.g., 0 == SQLITE_OK, 1 == SQLITE_ERROR. @RC is of type integer not null. Specifically:

• Each catch block captures the error code when it is entered into its own local variable.
• The hidden variable is created lazily, so it only exists if it is used.
  • The variable is called _rc_thrown_n where n is the catch block number in the procedure.
• Any reference to @RC refers to the above error variable of the innermost catch block the @RC reference is in.
• If the @RC reference happens outside of any catch block, its value is SQLITE_OK (i.e., zero).

Types of Literals

There are a number of literal objects that may be expressed in CQL. These are as follows:

String Literals

• A double quoted string is a C style string literal.
  • The usual simple C escape sequences are supported.
  • The form for embedded hex characters is supported, however.
  • The \0NNN octal form is not supported, and.
  • Embedded nulls in string literals (\0 or \0x00) are not supported (you must use blobs in such cases).
• A single quoted string is a SQL style string literal.
  • No escape sequences are supported other than '' to indicate a single quote character (this is just like normal SQLite).
• A sequence of single or double quoted strings separated by whitespace such as “xx” ‘yy’ “zz” which are concatenated to make one literal.
• The sequence @FILE(“some_string”) is a special string literal.
  • The value of this literal is the path of the current compiland starting at the letters in some_string, or, the entire path of the current compiland if some_string does not occur in the path.
  • The purpose of the @FILE construct is to provide a partial path to a file for diagnostics that is consistent even if the file is built in various different root paths on different build machines.

Blob Literals

• SQLite Blob literals are supported in SQL contexts (i.e., where they will be processed by SQLite). CQL produces an error if you attempt to use a blob literal in a loose expression.

Numeric Literals

• All numeric literals are considered to be positive; negative numbers are actually a positive literal combined with unary minus (the negation operator).
• Base 10 and hexadecimal literals are supported.
• Literals with a decimal point are of type REAL and stored as the C type double.
• Literals that can fit in a signed integer without loss and do not end in the letter L are integer literals.
• Larger literals, or those ending with the letter L, are long integer literals.
• Literals that begin with 0x are interpreted as hex.

Examples:

  1.3            -- real
  2L             -- long
  123456789123   -- long
  123            -- integer
  0x10           -- hex integer
  0x10L          -- hex long integer

The NULL literal

The use of NULL always results in a nullable result; however, this literal is special in that it has no storage class. NULL is neither a numeric nor a string itself but rather mutates into whatever it is first combined with. For instance, NULL + 1 results in a nullable integer. Because NULL has no primitive type, in some cases where type knowledge is required, you might have to use the CAST() function to cast NULL to a specific type, such as CAST(NULL as TEXT). This construct guarantees type consistency in cases like SELECT from different sources combined with UNION ALL.

NOTE: Constructs like CAST(NULL as TEXT) are always rewritten to just NULL before going to SQLite as the cast is uninteresting except for the type information, which SQLite doesn’t need/use anyway.

NOTE: The trailing cast notation is often helpful for economy here, e.g. NULL :TEXT: is a lot shorter than CAST(TEXT AS NULL) and is identical.

Boolean Literals

The boolean literals TRUE and FALSE (case insensitive) may be used freely. These are the same as the normal literals 0 and 1 except they have type BOOL. They mix with numbers in the usual ways with the usual promotion rules.

NOTE: Even if the target language is Lua, you can mix and match bools and integers in CQL. The compiler will emit casts if needed.

Other Considerations

The C pre-processor can still be combined with CQL, in which case the __FILE__ and __LINE__ directives of the pre-processor may be used to create literals; they will be preprocessed into normal literals.

The use of __FILE__ can give surprising results in the presence of build systems; hence, the existence of @FILE(...).

Use of the C-pre-processor is deprecated.

Const and Enumerations

It’s possible to use named constants in CQL with nothing more than the pre-processor features that have already appeared; however, the use of macros in such a way is not entirely satisfactory. For one thing, macros are expanded before semantic analysis, which means CQL can’t provide macro constant values for you in the JSON output, for instance.

To help with this problem, CQL includes specific support for constants. They can be in the form of enumerations of a fixed type or general-purpose ad hoc constants. We’ll see both in the sections to follow.

declare enum business_type integer (
  restaurant,
  laundromat,
  corner_store = 11+3  /* math added for demo purposes only */
);

After this enum is declared, this:

select business_type.corner_store;

is the same as this:

select 14;

And that is exactly what SQLite will see: the literal 14.

You can also use an enum type to define columns whose type is more specific than just integer, like so:

CREATE TABLE businesses (
  name TEXT,
  type business_type
);

CQL will then enforce that you use the correct enum to access those columns. For example, this is valid:

SELECT * FROM businesses WHERE type = business_type.laundromat;

While this does not type check, even if state.delaware is a numeric code for the state:

SELECT * FROM businesses WHERE type = state.delaware;

Enumerations follow these rules:

• The enumeration can be any numeric type (bool, integer, long integer, real).
• The values of the enumeration start at 1 (i.e., if there is no = expression, the first item will be 1, not 0).
• If you don’t specify a value, the next value is the previous value plus one.
• If you do specify a value, it can be any constant expression, and it will be cast to the type of the enumeration (even if that is lossy).
• The enumeration can refer to previous values in itself with no qualification (big = 100.0, medium = big/2, small = medium/2).
• The enumeration can refer to previously defined enumerations as usual (code = business_type.restaurant).
• Once the enumeration is defined, you refer to its members in a fully qualified fashion enum_name.member_name elsewhere.

With these forms, you get some additional useful output: * The JSON includes the enumerations and their values in their own section. * You can use the @emit_enums directive to put declarations like this into the .h file that corresponds to the current compiland.

enum business_type {
  business_type__restaurant = 1,
  business_type__laundromat = 2,
  business_type__corner_store = 14
};

Note that C does not allow for floating-point enumerations, so in case of floating-point values such as:

declare enum floating real (
  one = 1.0,
  two = 2.0,
  e = 2.71828,
  pi = 3.14159
);

you get:

// enum floating (floating point values)
#define floating__one 1.000000e+00
#define floating__two 2.000000e+00
#define floating__e 2.718280e+00
#define floating__pi 3.141590e+00

In Lua output the compiler generates initialized global variables with names like the above.

In order to get useful expressions in enumeration values, constant folding and general evaluation were added to the compiler; these expressions work on any numeric type and the literal NULL. The supported operations include:

+, -, *, /, %, |, &, <<, >>, ~, and, or, not, ==, <=, >=, !=, <, >, the CAST operator, and the CASE forms (including the IIF function). These operations are enough to make a lot of very interesting expressions, all of which are evaluated at compile time.

Constant folding was added to allow for rich enum expressions, but there is also the const() primitive in the language which can appear anywhere a literal could appear. This allows you to do things like:

create table something(
  x integer default const((1<<16)|0xf) /* again the math is just for illustration */
);

The const form is also very useful in macros to force arguments to be constants and so forth.

This const form ensures that the constant will be evaluated at compile time. The const pseudo-function can also nest so you can build these kinds of macros from other macros or you can build enum values this way. Anywhere you might need literals, you can use const.

Named Types

A common source of errors in stored procedures is incorrect typing in variables and arguments. For instance, a particular key for an entity might need to be LONG, LONG NOT NULL or even LONG NOT NULL @SENSITIVE. This requires you to diligently get the type right in all the places it appears, and should it ever change, you have to revisit all the places.

To help with this situation, and to make the code a little more self-describing, we added named types to the language. This is a lot like typedef in the C language. These defintiions do not create different incompatible types, but they do let you name types for reuse.

You can now write these sorts of forms:

declare foo_id type long not null;

create table foo(
  id foo_id primary key autoincrement,
  name text
);

create proc inserter(name_ text, out id foo_id)
begin
  insert into foo(id, name) values(NULL, name_);
  set id := last_insert_rowid();
end;

declare function func_return_foo_id() foo_id;

declare var foo_id;

Additionally any enumerated type can be used as a type name. e.g.

declare enum thing integer (
  thing1,
  thing2
);

declare thing_type type thing;

Enumerations always include “not null” in addition to their base type. Enumerations also have a unique “kind” associated; specifically, the above enum has the type integer<thing> not null. The rules for type kinds are described below.

Type Kinds

Any CQL type can be tagged with a “kind”; for instance, real can become real<meters>, integer can become integer<job_id>. The idea here is that the additional tag, the “kind”, can help prevent type mistakes in arguments, in columns, and in procedure calls. For instance:

create table things(
  size real<meters>,
  duration real<seconds>
);

create proc do_something(size_ real<meters>, duration_ real<seconds>)
begin
  insert into things(size, duration) values(size_, duration_);
end;

In this situation, you couldn’t accidentally switch the columns in do_something even though both are real, and indeed SQLite will only see the type real for both. If you have your own variables typed real<size> and real<duration>, you can’t accidentally do:

  call do_something(duration, size);

even though both are real. The type kind won’t match.

Importantly, an expression with no type kind is compatible with any type kind (or none). Hence all of the below are legal.

declare generic real;
set generic := size;        -- no kind may accept <meters>
set generic := duration;    -- no kind may accept <seconds>
set duration := generic;    -- no kind may be stored in <seconds>

Only mixing types where both have a kind, and the kind is different, generates errors. This choice allows you to write procedures that (for instance) log any integer or any real, or that return an integer out of a collection.

These rules are applied to comparisons, assignments, column updates, anywhere and everywhere types are checked for compatibility.

To get the most value out of these constructs, the authors recommend that type kinds be used universally except when the extra compatibility described above is needed (like low-level helper functions).

Importantly, type kind can be applied to object types as well, allowing object<dict> to be distinct from object<list>.

At runtime, the kind information is lost. But it does find its way into the JSON output so external tools also get to see the kinds.

Nullability

Nullability Rules

Nullability is tracked via CQL’s type system. To understand whether or not an expression will be assigned a nullable type, you can follow these rules; they will hopefully be intuitive if you are familiar with SQL:

The literal NULL is, of course, always assigned a nullable type. All other literals are nonnull.

In general, the type of an expression involving an operator (e.g., +, ==, !=, ~, LIKE, et cetera) is nullable if any of its arguments are nullable. For example, 1 + NULL is assigned the type INTEGER, implying nullability. 1 + 2, however, is assigned the type INTEGER NOT NULL.

IN and NOT IN expressions are assigned a nullable type if and only if their left argument is nullable: The nullability of the right side is irrelevant. For example, "foo" IN (a, b) will always have the type BOOL NOT NULL, whereas some_nullable IN (a, b) will have the type BOOL.

NOTE: In CQL, the IN operator behaves like a series of equality tests (i.e., == tests, not IS tests), and NOT IN behaves symmetrically. SQLite has slightly different nullability rules for IN and NOT IN. This is the one place where CQL has different evaluation rules from SQLite, by design.

The result of IS and IS NOT is always of type BOOL NOT NULL, regardless of the nullability of either argument.

For CASE expressions, the result is always of a nullable type if no ELSE clause is given. If an ELSE is given, the result is nullable if any of the THEN or ELSE expressions are nullable.

NOTE: The SQL CASE construct is quite powerful: Unlike the C switch statement, it is actually an expression. In this sense, it is rather more like a highly generalized ternary a ? b : c operator than a C switch statement. There can be arbitrarily many conditions specified, each with their own result, and the conditions need not be constants; typically, they are not.

IFNULL and COALESCE are assigned a NOT NULL type if one or more of their arguments are of a NOT NULL type.

In most join operations, the nullability of each column participating in the join is preserved. However, in a LEFT OUTER join, the columns on the right side of the join are always considered nullable; in a RIGHT OUTER join, the columns on the left side of the join are considered nullable.

As in most other languages, CQL does not perform evaluation of value-level expressions during type checking. There is one exception to this rule: An expression within a const is evaluated at compilation time, and if its result is then known to be nonnull, it will be given a NOT NULL type. For example, const(NULL or 1) is given the type BOOL NOT NULL, whereas merely NULL or 1 has the type BOOL.

Nullability Improvements

CQL is able to “improve” the type of some expressions from a nullable type to a NOT NULL type via occurrence typing, also known as flow typing. There are three kinds of improvements that are possible:

Positive improvements, i.e., improvements resulting from the knowledge that some condition containing one or more AND-linked IS NOT NULL checks must have been true:

IF statements:

IF a IS NOT NULL AND c.x IS NOT NULL THEN
  -- `a` and `c.x` are not null here
ELSE IF b IS NOT NULL THEN
  -- `b` is not null here
END IF;

CASE expressions:

CASE
  WHEN a IS NOT NULL AND c.x IS NOT NULL THEN
    -- `a` and `c.x` are not null here
  WHEN b IS NOT NULL THEN
    -- `b` is not null here
  ELSE
    ...
END;

IIF expressions:

IIF(a IS NOT NULL AND c.x IS NOT NULL,
  ..., -- `a` and `c.x` are not null here
  ...
)

SELECT expressions:

SELECT
  -- `t.x` and `t.y` are not null here
FROM t
WHERE x IS NOT NULL AND y IS NOT NULL

Negative improvements, i.e., improvements resulting from the knowledge that some condition containing one or more OR-linked IS NULL checks must have been false:

IF statements:

IF a IS NULL THEN
  ...
ELSE IF c.x IS NULL THEN
  -- `a` is not null here
ELSE
  -- `a` and `c.x` are not null here
END IF;

IF statements, guard pattern:

IF a IS NULL RETURN;
-- `a` is not null here

IF c.x IS NULL THEN
  ...
  THROW;
END IF;
-- `a` and `c.x` are not null here

CASE expressions:

CASE
  WHEN a IS NULL THEN
    ...
  WHEN c.x IS NULL THEN
    -- `a` is not null here
  ELSE
    -- `a` and `c.x` are not null here
END;

IIF expressions:

IIF(a IS NULL OR c.x IS NULL,
  ...,
  ... -- `a` and `c.x` are not null here
)

Assignment improvements, i.e., improvements resulting from the knowledge that the right side of a statement (or a portion therein) cannot be NULL:

SET statements:

SET a := 42;
-- `a` is not null here

NOTE: Assignment improvements from FETCH statements are not currently supported. This may change in a future version of CQL.

There are several ways in which improvements can cease to be in effect:

The scope of the improved variable or cursor field has ended:

IF a IS NOT NULL AND c.x IS NOT NULL THEN
  -- `a` and `c.x` are not null here
END IF;
-- `a` and `c.x` are nullable here

An improved variable was SET to a nullable value:

IF a IS NOT NULL THEN
  -- `a` is not null here
  SET a := some_nullable;
  -- `a` is nullable here
END IF;

An improved variable was used as an OUT (or INOUT) argument:

IF a IS NOT NULL THEN
  -- `a` is not null here
  CALL some_procedure_that_requires_an_out_argument(a);
  -- `a` is nullable here
END IF;

An improved variable was used as a target for a FETCH statement:

IF a IS NOT NULL THEN
  -- `a` is not null here
  FETCH c INTO a;
  -- `a` is nullable here
END IF;

An improved cursor field was re-fetched:

IF c.x IS NOT NULL THEN
  -- `c.x` is not null here
  FETCH c;
  -- `c.x` is nullable here
END IF;

A procedure call was made (which removes improvements from all globals because the procedure may have mutated any of them; locals are unaffected):

IF a IS NOT NULL AND some_global IS NOT NULL THEN
  -- `a` and `some_global` are not null here
  CALL some_procedure();
  -- `a` is still not null here
  -- `some_global` is nullable here
END IF;

CQL is generally smart enough to understand the control flow of your program and infer nullability appropriately; here are a handful of examples:

IF some_condition THEN
  SET a := 42;
ELSE
  THROW;
END IF;
-- `a` is not null here because it must have been set to 42
-- if we've made it this far

IF some_condition THEN
  SET a := 42;
ELSE
  SET a := 100;
END IF;
-- `a` is not null here because it was set to a value of a
-- `NOT NULL` type in all branches and the branches cover
-- all of the possible cases

IF a IS NOT NULL THEN
  IF some_condition THEN
    SET a := NULL;
  ELSE
    -- `a` is not null here despite the above `SET` because
    -- CQL understands that, if we're here, the previous
    -- branch must not have been taken
  END IF;
END IF;

IF a IS NOT NULL THEN
  WHILE some_condition
  BEGIN
    -- `x` is nullable here despite `a IS NOT NULL` because
    -- `a` was set to `NULL` later in the loop and thus `x`
    -- will be `NULL` when the loop repeats
    LET x := a;
    SET a := NULL;
    ...
  END;
END IF;

Regarding Conditions

For positive improvements, the check must be exactly of the form IS NOT NULL; other checks that imply a variable or cursor field must not be null when true have no effect:

IF a > 42 THEN
  -- `a` is nullable here
END IF;

NOTE: This may change in a future version of CQL.

For multiple positive improvements to be applied from a single condition, they must be linked by AND expressions along the outer spine of the condition; uses of IS NOT NULL checks that occur as subexpressions within anything other than AND have no effect:

IF
  (a IS NOT NULL AND b IS NOT NULL)
  OR c IS NOT NULL
THEN
  -- `a`, `b`, and `c` are all nullable here
END IF;

For negative improvements, the check must be exactly of the form IS NULL; other checks that imply a variable or cursor field must not be null when false have no effect:

DECLARE equal_to_null INT;
IF a IS equal_to_null THEN
  ...
ELSE
  -- `a` is nullable here
END IF;

For multiple negative improvements to be applied from a single condition, they must be linked by OR expressions along the outer spine of the condition; uses of IS NULL checks that occur as subexpressions within anything other than OR have no effect:

IF
  (a IS NULL OR b IS NULL)
  AND c IS NULL
THEN
  ...
ELSE
  -- `a`, `b`, and `c` are all nullable here
END IF;

Forcing Nonnull Types

If possible, it is best to use the techniques described in “Nullability Improvements” to verify that the value of a nullable type is nonnull before using it as such.

Sometimes, however, you may know that a value with a nullable type cannot be null and simply wish to use it as though it were nonnull. The ifnull_crash and ifnull_throw “attesting” functions convert the type of an expression to be nonnull and ensure that the value is nonnull with a runtime check. They cannot be used in SQLite contexts because the functions are not known to SQLite, but they can be used in loose expressions. For example:

CREATE PROC square_if_odd(a INT NOT NULL, OUT result INT)
BEGIN
  IF a % 2 = 0 THEN
    SET result := NULL;
  ELSE
    SET result := a * a;
  END IF;
END;

-- `x` has type `INT`, but we know it can't be `NULL`
let x := square_if_odd(3);

-- `y` has type `INT NOT NULL`
let y := ifnull_crash(x);

Above, the ifnull_crash attesting function is used to coerce the expression x to be of type INT NOT NULL. If our assumptions were somehow wrong, however—and x were, in fact, NULL—our program would crash.

As an alternative to crashing, you can use ifnull_throw. The following two pieces of code are equivalent:

CREATE PROC y_is_not_null(x INT)
BEGIN
  let y := ifnull_throw(x);
END;

CREATE PROC y_is_not_null(x INT)
BEGIN
  DECLARE y INT NOT NULL;
  IF x IS NOT NULL THEN
    SET y := x;
  ELSE
    THROW;
  END IF;
END;

Expression Types

CQL supports a variety of expressions, nearly everything from the SQLite world. The following are the various supported operators; they are presented in order from the weakest binding strength to the strongest. Note that the binding order is NOT the same as C, and in some cases, it is radically different (e.g., boolean math)

UNION and UNION ALL

These appear only in the context of SELECT statements. The arms of a compound select may include FROM, WHERE, GROUP BY, HAVING, and WINDOW. If ORDER BY or LIMIT ... OFFSET are present, these apply to the entire UNION.

Example:

select A.x x from A inner join B using(z)
union all
select C.x x from C
where x = 1;

The WHERE applies only to the second select in the union. And each SELECT is evaluated before the the UNION ALL

select A.x x from A inner join B using(z)
where x = 3
union all
select C.x x from C
where x = 1
order by x;

The ORDER BY applies to the result of the union, so any results from the 2nd branch will sort before any results from the first branch (because x is constrained in both).

Assignment

Assignment occurs in the UPDATE statement, in the SET and LET statements, and in various implied cases like the += operator. In these cases, the left side is a simple target and the right side is a general expression. The expression is evaluated before the assignment.

NOTE: In cases where the left-hand side is an array or property, the assignment is actually rewritten to call a suitable setter function, so it isn’t actually an assignment at all.

Example:

SET x := 1 + 3 AND 4;  -- + before AND then :=

Logical OR

The logical OR operator performs shortcut evaluation, much like the C || operator (not to be confused with SQL’s concatenation operator with the same lexeme). If the left side is true, the result is true, and the right side is not evaluated.

The truth table for logical OR is as follows:

Logical AND

The logical AND operator performs shortcut evaluation, much like the C && operator. If the left side is false, the result is false, and the right side is not evaluated.

The truth table for logical AND is as follows:

BETWEEN and NOT BETWEEN

These are ternary operators. The general forms are:

  expr1 BETWEEN expr2 AND expr3
  expr1 NOT BETWEEN expr2 AND expr3

Importantly, there is an inherent ambiguity in the language because expr2 or expr3 above could be logical expressions that include AND. CQL resolves this ambiguity by insisting that expr2 and expr3 be “math expressions” as defined in the CQL grammar. These expressions may not have ungrouped AND or OR operators.

Examples:

-- oh hell no (syntax error)
a between 1 and 2 and 3;

-- all ok
a between (1 and 2) and 3;
a between 1 and (2 and 3);
a between 1 and b between c and d; -- binds left to right
a between 1 + 2 and 12 / 2;

Logical NOT

The one operand of logical NOT must be a numeric. NOT 'x' is illegal.

Non-ordering tests !=, <>, =, ==, LIKE, GLOB, MATCH, REGEXP, IN, IS, IS NOT

These operations do some non-ordered comparison of their two operands: * IS and IS NOT never return NULL, So for instance X IS NOT NULL gives the natural answer. x IS y is true if and only if: 1. both x and y are NULL or 2. if they are equal. * The other operators return NULL if either operand is NULL and otherwise perform their usual test to produce a boolean. * != and <> are equivalent as are = and ==. * Strings and blobs compare equal based on their value, not their identity (i.e., not the string/blob pointer). * Objects compare equal based on their address, not their content (i.e., reference equality). * MATCH, GLOB, and REGEXP are only valid in SQL contexts, LIKE can be used in any context (a helper method to do LIKE in C is provided by SQLite, but not the others). * MATCH, GLOB, REGEXP, LIKE, and IN may be prefixed with NOT which reverses their value.

 NULL IS NULL  -- this is true
(NULL == NULL) IS NULL  -- this is also true because NULL == NULL is not 1, it's NULL.
(NULL != NULL) IS NULL  -- this is also true because NULL != NULL is not 0, it's also NULL.
'xy' NOT LIKE 'z%'` -- this is true

Ordering comparisons <, >, <=, >=

These operators perform the usual order comparison of their two operands:

• If either operand is NULL, the result is NULL.
• Objects and blobs may not be compared with these operands.
• Strings are compared based on their value (as with other comparisons), not their address.
• Numerics are compared as usual with the usual promotion rules.

NOTE: CQL uses strcmp for string comparison. In SQL expressions, the comparison happens in whatever way SQLite has been configured. Typically, general-purpose string comparison should be done with helper functions that deal with collation and other considerations. This is a very complex topic and CQL is largely silent on it.

Bitwise operators <<, >>, &, |

These are the bit-manipulation operations. Their binding strength is very different from C, so beware. Notably, the & operator has the same binding strength as the | operator, so they bind left to right, which is utterly unlike most systems. Many parentheses are likely to be needed to correctly codify the usual “or of ands” patterns.

Likewise, the shift operators << and >> have the same strength as & and |, which is very atypical. Consider:

x & 1 << 7;    -- not ambiguous but unusual meaning, not like C or Lua
(x & 1) << 7;  -- means the same as the above
x & (1 << 7)   -- probably what you intended

Note that these operators only work on integer and long integer data. If any operand is NULL, the result is NULL.

Addition and Subtraction +, -

These operators perform the typical arithmetic operations. Note that there are no unsigned numeric types, so it’s always signed arithmetic that is performed.

• Operands are promoted to the “biggest” type involved as previously described (bool -> int -> long -> real).
• Only numeric operands are legal (no adding strings).
• If any operand is NULL, the result is NULL.

Multiplication, Division, Modulus *, /, %

These operators also perform typical arithmetic operations. Note that there are no unsigned numeric types, so it’s always signed arithmetic that is performed.

• Operands are promoted to the “biggest” type as previously described (bool -> int -> long -> real).
• Only numeric operands are legal (no multiplying strings).
• If any operand is NULL, the result is NULL.
• In a native context, division by zero will product a fault.

EXCEPTION: The % operator doesn’t make sense on real values, so real values produce an error.

Concatenation ||

This operator is only valid in a SQL context, it concatenates the text representation of its left and right arguments into text. The arguments can be of any type.

JSON Operators -> and ->>

The JSON extraction operator -> accepts a JSON text value or JSON blob value as its left argument and a valid JSON path as its right argument. The indicated path is extracted and the result is a new, smaller, piece of JSON text.

The extended extraction operator ->> will return the selected item as a value rather than as JSON. The SQLite documentation can be helpful here:

Both the -> and ->> operators select the same subcomponent of the JSON to their left. The difference is that -> always returns a JSON representation of that subcomponent and the ->> operator always returns an SQL representation of that subcomponent.

Importantly, the resulting type of the ->> operator cannot be known at compile time because it will depend on the path value which could be, and often is, a non-constant expression. Dynamic typing is not a concept available in CQL, as a consequence, in CQL, you must declare the extraction type when using the ->> operator with syntax:

  json_arg ->> ~type_spec~ path_arg

The type_spec can be any valid type like int, int<foo>, or a type alias. All the normal type expressions are valid.

NOTE: this form is not a CAST operation, it’s a declaration. The type of the expression will be assumed to be the indicated type. SQLite will not see the ~type~ notation when the expression is evaluated. If a cast is desired simply write one as usual, the trailing cast syntax can be specially convenient when working with JSON.

Unary operators -, ~

Unary negation (-) and bitwise invert (~) are the strongest binding operators.

• The ~ operator only works on integer types (not text, not real).
• The usual promotion rules otherwise apply.
• If the operand is NULL, the result is NULL.

CAST Expressions

The CAST expression can be written in two ways, the standard form:

  CAST( expr AS type)

And equivalently using pipeline notation

  expr ~type~

The trailing ~type~ notation has very strong binding, As shown in the order of operations table, it is even stronger than the unary ~ operator. It is indended to be used in function pipelines in combination with other pipeline operations (the : family) rather than as part of arithmetic and so forth, hence it has a fairly strong binding (equal to :).

The ~type~ form is immediatley converted to the standard CAST form and so SQLite will never see this alternate notation. This form is purely syntatic sugar.

CASE Expressions

The CASE expression has two major forms and provides a great deal of flexibility in an expression. You can think of it as an enhanced version of the C ?: operator.

let x := 'y';
let y := case x
  when 'y' then 1
  when 'z' then 2
  else 3
end;

In this form, the CASE expression (x here) is evaluated exactly once and then compared against each WHEN clause. Every WHEN clause must be type-compatible with the CASE expression. The THEN expression corresponding to the matching WHEN is evaluated and becomes the result. If no WHEN matches, then the ELSE expression is used. If there is no ELSE and no matching WHEN, then the result is NULL.

If that’s not general enough, there is an alternate form:

let y := 'yy';
let z := 'z';
let x := case
  when y = 'y' then 1
  when z = 'z' then 2
  else 3
end;

In the second form, where there is no value before the first WHEN keyword, each WHEN expression is a separate independent boolean expression. The first one that evaluates to true causes the corresponding THEN to be evaluated, and that becomes the result. As before, if there is no matching WHEN clause, then the result is the ELSE expression if present, or NULL if there is no ELSE.

The result types must be compatible, and the best type to hold the answer is selected with the usual promotion rules.

SELECT Expressions

Single values can be extracted from SQLite using an inline SELECT expression. For instance:

set x_ := (select x from somewhere where id = 1);

The SELECT statement in question must extract exactly one column, and the type of the expression becomes the type of the column. This form can appear anywhere an expression can appear, though it is most commonly used in assignments. Something like this would also be valid:

if (select x from somewhere where id = 1) == 3 then
  ...
end if;

The SELECT statement can, of course, be arbitrarily complex.

Importantly, if the SELECT statement returns no rows, this will result in the normal error flow. In that case, the error code will be SQLITE_DONE, which is treated like an error because in this context SQLITE_ROW is expected as a result of the SELECT. This is not a typical error code and can be quite surprising to callers. If you’re seeing this failure mode, it usually means the code had no affordance for the case where there were no rows, and probably that situation should have been handled. This is an easy mistake to make, so to avoid it, CQL also supports these more tolerant forms:

set x_ := (select x from somewhere where id = 1 if nothing then -1);

And even more generally, if the schema allows for null values and those are not desired:

set x_ := (select x from somewhere where id = 1 if nothing or null then -1);

Both of these are much safer to use, as only genuine errors (e.g., the table was dropped and no longer exists) will result in the error control flow.

Again, note that:

set x_ := (select ifnull(x, -1) from somewhere where id = 1);

Would not avoid the SQLITE_DONE error code because “no rows returned” is not at all the same as “null value returned.”

The if nothing or null form above is equivalent to the following, but it is more economical and probably clearer:

set x_ := (select ifnull(x, -1) from somewhere where id = 1 if nothing then -1);

To compute the type of the overall expression, the rules are almost the same as normal binary operators. In particular:

• If the default expression is present, it must be type compatible with the select result. The result type is the smallest type that holds both the select value and the default expression (see normal promotion rules above).
• Object types are not allowed (SQLite cannot return an object).
• In (select ...), the result type is not null if and only if the select result type is not null (see select statement, many cases).
• In (select ... if nothing), the result type is not null if and only if both the select result and the default expression types are not null (normal binary rules).
• In (select ... if nothing or null), the result type is not null if and only if the default expression type is not null.

Finally, the form (select ... if nothing then throw) is allowed; this form is exactly the same as normal (select ...), but it makes explicit that the error control flow will happen if there is no row. Consequently, this form is allowed even if @enforce_strict select if nothing is in force.

Marking Data as Sensitive

CQL supports the notion of ‘sensitive’ data in a first-class way. You can think of it as very much like nullability; it largely begins by tagging data columns with @sensitive.

Rather than go through the whole calculus, it’s easier to understand by a series of examples. So let’s start with a table with some sensitive data.

create table with_sensitive(
 id integer,
 name text @sensitive,
 sens integer @sensitive
);

The most obvious thing you might do at this point is create a stored procedure that would read data out of that table. Maybe something like this:

create proc get_sensitive()
begin
  select id as not_sensitive_1,
        sens + 1 sensitive_1,
        name as sensitive_2,
        'x' as not_sensitive_2,
        -sens as sensitive_3,
        sens between 1 and 3 as sensitive_4
  from with_sensitive;
end;

So looking at that procedure, we can see that it’s reading sensitive data, so the result will have some sensitive columns in it.

• id is not sensitive (at least not in this example)
• sens + 1 is sensitive, math on a sensitive field leaves it sensitive
• name is sensitive, it began that way and is unchanged
• x is just a string literal, it’s not sensitive
• -sens is sensitive, that’s more math
• and the between expression is also sensitive

Generally, sensitivity is “radioactive” - anything it touches becomes sensitive. This is very important because even a simple-looking expression like sens IS NOT NULL must lead to a sensitive result or the whole process would be largely useless. It has to be basically impossible to wash away sensitivity.

These rules apply to normal expressions as well as expressions in the context of SQL. Accordingly:

Sensitive variables can be declared:

declare sens integer @sensitive;

Simple operations on the variables are sensitive:

-- this is sensitive (and the same would be true for any other math)
sens + 1;

The IN expression gives a sensitive result if anything about it is sensitive:

-- all of these are sensitive
sens in (1, 2);
1 in (1, sens);
(select id in (select sens from with_sensitive));

Similarly, sensitive constructs in CASE expressions result in a sensitive output:

-- not sensitive
case 0 when 1 then 2 else 3 end;

-- all of these are sensitive
case sens when 1 then 2 else 3 end;
case 0 when sens then 2 else 3 end;
case 0 when 1 then sens else 3 end;
case 0 when 1 then 2 else sens end;

Cast operations preserve sensitivity:

-- sensitive result
select cast(sens as INT);

Aggregate functions likewise preserve sensitivity:

-- all of these are sensitive
select AVG(T1.sens) from with_sensitive T1;
select MIN(T1.sens) from with_sensitive T1;
select MAX(T1.sens) from with_sensitive T1;
select SUM(T1.sens) from with_sensitive T1;
select COUNT(T1.sens) from with_sensitive T1;

There are many operators that get similar treatment such as COALESCE, IFNULL, IS and IS NOT.

Things get more interesting when we come to the EXISTS operator:

-- sensitive if and only if any selected column is sensitive
exists(select * from with_sensitive)

-- sensitive because "info" is sensitive
exists(select info from with_sensitive)

-- not sensitive because "id" is not sensitive
exists(select id from with_sensitive)

If this is making you nervous, it probably should. We need a little more protection because of the way EXISTS is typically used. The predicates matter. Consider the following:

-- id is now sensitive because the predicate of the where clause was sensitive
select id from with_sensitive where sens = 1;

-- this expression is now sensitive because id is sensitive in this context
exists(select id from with_sensitive where sens = 1)

In general, if the predicate of a WHERE or HAVING clause is sensitive, then all columns in the result become sensitive.

Similarly, when performing joins, if the column specified in the USING clause is sensitive or the predicate of the ON clause is sensitive, then the result of the join is considered to be all sensitive columns, even if the columns were not sensitive in the schema.

Likewise, a sensitive expression in LIMIT or OFFSET will result in 100% sensitive columns, as these can be used in a WHERE-ish way.

-- join with ON
select T1.id from with_sensitive T1 inner join with_sensitive T2 on T1.sens = T2.sens

-- join with USING
select T1.id from with_sensitive T1 inner join with_sensitive T2 using(sens);

All of these expressions and join propagations are designed to make it impossible to simply wash away sensitivity with a little bit of math.

Now we come to enforcement, which boils down to what assignments or “assignment-like” operations we allow.

If we have these:

declare sens integer @sensitive;
declare not_sens integer;

We can use those as stand-ins for lots of expressions, but the essential calculus goes like this:

-- assigning a sensitive to a sensitive is ok
set sens := sens + 1;

-- assigning not sensitive data to a sensitive is ok
-- this is needed so you can (e.g.) initialize to zero
set sens := not_sens;

-- not ok
set not_sens := sens;

Now these “assignments” can happen in a variety of ways:

• you can set an out parameter of your procedure
• when calling a function or procedure, we require:
  • any IN parameters of the target be “assignable” from the value of the argument expression
  • any OUT parameters of the target be “assignable” from the procedures type to the argument variable
  • any IN/OUT parameters require both the above

Now it’s possible to write a procedure that accepts sensitive things and returns non-sensitive things. This is fundamentally necessary because the proc must be able return (e.g.) a success code, or encrypted data, that is not sensitive. However, if you write the procedure in CQL it, too, will have to follow the assignment rules and so cheating will be quite hard. The idea here is to make it easy to handle sensitive data well and make typical mistakes trigger errors.

With these rules it’s possible to compute the the type of procedure result sets and also to enforce IN/OUT parameters. Since the signature of procedures is conveniently generated with –generate_exports good practices are fairly easy to follow and sensitivity checks flow well into your programs.

This is a brief summary of CQL semantics for reference types – those types that are ref counted by the runtime.

The three reference types are:

• TEXT
• OBJECT
• BLOB

Each of these has their own macro for retain and release though all three actually turn into the exact same code in all the current CQL runtime implementations. In all cases the object is expected to be promptly freed when the reference count falls to zero.

Reference Semantics

Stored Procedure Arguments

• in and inout arguments are not retained on entry to a stored proc
• out arguments are assumed to contain garbage and are nulled without retaining on entry
• if your out argument doesn’t have garbage in it, then it is up to you to release it before you make a call
• When calling a proc with an out argument, CQL will release the argument variable before the call site, obeying its own contract

Local Variables

• assigning to a local variable retains the object, and then does a release on the previous object
• this order is important; all assignments are done in this way in case of aliasing (release first might accidentally free too soon)
• CQL calls release on all local variables when the method exits

Assigning to an out parameter or a global variable

• out, inout parameters, and global variables work just like local variables except that CQL does not call release at the end of the procedure

Function Return Values

Stored procedures do not return values, they only have out arguments and those are well defined as above. Functions however are also supported and they can have either get or create semantics

Get Semantics

If you declare a function like so:

declare function Getter() object;

Then CQL assumes that the returned object should follow the normal rules above, retain/release will balance by the end of the procedure for locals and globals or out arguments could retain the object.

Create Semantics

If you declare a function like so:

declare function Getter() create text;

then CQL assumes that the function created a new result which it is now responsible for releasing. In short, the returned object is assumed to arrive with a retain count of 1 already on it. When CQL stores this return value it will:

• release the object that was present at the storage location (if any)
• copy the returned pointer without further retaining it this one time

As a result, if you store the returned value in a local variable it will be released when the procedure exits (as usual) or if you instead store the result in a global or an out parameter the result will survive to be used later.

Comparison

CQL tries to adhere to normal SQL comparison rules but with a C twist.

OBJECT

The object type has no value-based comparison, so there is no <, > and so forth.

The following table is useful. Let’s suppose there are exactly two distinct objects ‘X’ and ‘Y’:

null = null resulting in NULL is particularly surprising but consistent with the usual SQL rules. And again, as in SQL, the IS operator returns true for X IS Y even if both are NULL.

NOTE: null-valued variables evaluate as above; however, the NULL literal generally yields errors if it is used strangely. For example, in if x == NULL, you get an error. The result is always going to be NULL, hence falsey. This was almost certainly intended to be if x IS NULL. Likewise, comparing expressions that are known to be NOT NULL against NULL yields errors. This is also true where an expression has been inferred to be NOT NULL by control flow analysis.

TEXT

Text has value comparison semantics, but normal string comparison is done only with strcmp, which is of limited value. Typically, you’ll want to either delegate the comparison to SQLite (with (select x < y)) or else use a helper function with a suitable comparison mechanism.

For text comparisons, including equality:

Example:

'x' < 'y' == true -- because strcmp("x", "y") < 0

As with type object, the IS and IS NOT operators behave similarly to equality and inequality, but never produce a NULL result. Strings have the same null semantics as object. In fact strings have all the same semantics as object except that they get a value comparison with strcmp rather than an identity comparison. With object x == y implies that x and y point to the very same object. With strings, it only means x and y hold the same text.

The IN and NOT IN operators also work for text using the same value comparisons as above.

Additionally there are special text comparison operators such as LIKE, MATCH and GLOB. These comparisons are defined by SQLite.

BLOB

Blobs are compared by value (equivalent to memcmp) but have no well-defined ordering. The memcmp order is deemed not helpful as blobs usually have internal structure hence the valid comparisons are only equality and inequality.

You can use user defined functions to do better comparisons of your particular blobs if needed.

The net comparison behavior is otherwise just like strings.

Sample Code

Out Argument Semantics

DECLARE FUNCTION foo() OBJECT;

CREATE PROC foo_user (OUT baz OBJECT)
BEGIN
  SET baz := foo();
END;

void foo_user(cql_object_ref _Nullable *_Nonnull baz) {
  *(void **)baz = NULL; // set out arg to non-garbage
  cql_set_object_ref(baz, foo());
}

Function with Create Semantics

DECLARE FUNCTION foo() CREATE OBJECT;

CREATE PROCEDURE foo_user (INOUT baz OBJECT)
BEGIN
  DECLARE x OBJECT;
  SET x := foo();
  SET baz := foo();
END;

void foo_user(cql_object_ref _Nullable *_Nonnull baz) {
  cql_object_ref x = NULL;

  cql_object_release(x);
  x = foo();
  cql_object_release(*baz);
  *baz = foo();

cql_cleanup:
  cql_object_release(x);
}

Function with Get Semantics

DECLARE FUNCTION foo() OBJECT;

CREATE PROCEDURE foo_user (INOUT baz OBJECT)
BEGIN
  DECLARE x OBJECT;
  SET x := foo();
  SET baz := foo();
END;

void foo_user(cql_object_ref _Nullable *_Nonnull baz) {
  cql_object_ref x = NULL;

  cql_set_object_ref(&x, foo());
  cql_set_object_ref(baz, foo());

cql_cleanup:
  cql_object_release(x);
}

Pipeline Function Notation

Function calls can be made using a pipeline notation to chain function results together in a way that is clearer. The general syntax is:

   expr : func(...args...)

Which is exactly equivalent to

   func(expr, ...args...)

This can be generalized with the @op statement, which in general might change the function name to something more specific. For instance.

@op real : call foo as foo_real;
@op real<joules> : call foo as foo_real_joules;

Now has more specific mappings

declare x real<joules>;

-- These are the functions that you might call with pipeline notation

declare function foo(x text) real;
declare function foo_real(x real) real;
declare function foo_real_joules(x real) real;

The mappings and declarations are both required now allow this:

"test":foo()  -->  foo("test")

5.0:foo()     --> foo_real(5.0)

x:foo()       --> foo_real_joules(x)

Note that in each case foo could have included additional arguments which are placed normally.

x:foo(1,2)    --> foo_real_joules(x, 1, 2) (this function is not declared)

If there are no additional arguments the () can be elided. This is a good way to end a pipeline.

x:dump        --> dump(x)

The old :: and ::: operators are no longer supported. The combination of naming conventions proved impossible to remember. With @op you can decide how to resolve the naming yourself.

Pipeline Polymorphic Overloads

This is a functor like form and it can be enabled using @op like the other cases. For instance

@op object<list_builder> : functor all as list_builder_add;

The invocation syntax is like the : operator but with no function name, so it looks kind of like the left argument has become a functor (something you can call like a function). In reality the call is converted using the base name in the @op directive and the base types of the arguments.

expr:(arg1, arg2, ...)

To enable:

• the left argument (here expr) must have a type kind (e.g. object<container>)
• the type kind must match an @op directive specifying functor and all
• the rewritten function name is the @op name plus the types of all the arguments (if any)

for instance, with these declarations:

declare function new_builder() create object<list_builder>;
declare function list_builder_add_int(arg1 object<list_builder>, arg2 int!) object<list_builder>;
declare function list_builder_add_int_int(arg1 object<list_builder>, arg2 int!, arg3 int!) object<list_builder>;
declare function list_builder_add_real(arg1 object<list_builder>, arg2 real!) object<list_builder>;
declare function list_builder_to_list(arg1 object<list_builder>) create object<list>;

@op object<list_builder> : functor all as list_builder_add;
@op object<list_builder> : call to_list as list_builder_to_list;

You could write:

let list := new_builder():(5):(7.0):(1,2):to_list();

This expands to the much less readable:

LET list :=
  list_builder_to_list(
   list_builder_add_int_int(
      list_builder_add_real(
        list_builder_add_int(
          new_builder(), 5), 7.0), 1, 2));

You can use this form to build helper functions that assemble text, arrays, JSON and many other uses. Anywhere the “fluent” coding pattern is helpful this syntax gives you a very flexible pattern. In the end it’s just rewritten function calls.

The appended type names look like this:

• the null type applies only to the null literal, other instances are typed such as a nullable int that is null

++ the CURSOR type applies to functions with a CURSOR argument, these are the so called dynamic-cursor arguments

Pipeline Cast Operations

Cast operations also have a pipeline notation:

-- This is the same as let i := cast(foo(x) as int);
let i := x :foo() ~int~ ;

These operations are particularly useful when working with json data. For instance:

select foo.json : json_extract('x') ~int~ :ifnull(0) as X from foo;

This is much easier to read than the equivalent:

select ifnull(cast(json_extract(foo.json 'x') as int), 0) as X from foo;

In all the cases the expression is rewritten into the normal form so SQLite will never see the pipeline form. No special SQLite support is needed.

This form of the ~ operator has the same binding strength as the : operator family.

Other operators useful in construction: -> << >> ||

The @op form allows the definition of many possible overload combos. For instance the -> is now mappable to a function call of your choice, this is very useful in pipeline forms.

@op text<xml> : arrow text<xml_path> as extract_xml_path;

Allows this mapping

  xml -> path   --->   extract_xml_path(xml, path)

The type forms for arrow should use types in their simplest form, like so:

These are the same forms that are used when adding argument types for the polymorpic pipeline form.

@op can be used with lshift rshift and concat to remap the <<, >>, and || operators respectively.

Properties and Arrays

CQL offers structured types in the form of cursors, but applications often need other structured types. In the interest of not encumbering the language and runtime with whatever types given application might need, CQL offers a generic solution for properties and arrays where it will rewrite property and array syntax into function calls.

Specific Properties on objects

The rewrite depends on the object type, and in particular the “kind” designation. So for instance consider:

declare function create_container() create object<container> not null;
let cont := create_container();
cont.x := cont.x + 1;

For this to work we need a function that makes the container:

function create_container() create object<container>;

Now cont.x might mean field access in a cursor or table access in a select expression. So, when it is seen, cont.x will be resolved in the usual ways. If these fail then CQL will look for a suitable mapping using the @op directive, like so:

@op object<container> : get x as container_get_x;
@op object<container> : set x as container_set_x;

container_set_x to do setting and container_get_x to do getting.

Like these maybe:

declare function container_get_x(self object<container> not null) int not null;
declare proc container_set_x(self object<container> not null, value int not null);

With those in place cont.x := cont.x + 1 will be converted into this:

CALL set_object_container_x(cont, get_object_container_x(cont) + 1);

Importantly with this pattern you can control exactly which properties are available. Missing properties will always give errors. For instance cont.y := 1; results in name not found 'y'.

This example uses object as the base type but it can be any type. For instance, if you have a type integer<handle> that identifies some storage based on the handle value, you can use the property pattern on this. CQL does not care what the types are, so if property access is meaningful on your type then it can be supported.

Additionally, the rewrite can happen in a SQL context or a native context. The only difference is that in a SQL context you would need to create the appropriate SQLite UDF and declare it with declare select function rather than declare function.

Open Ended Properties

There is a second choice for properties, where the properties are open-ended. That is where the property name can be anything. If instead of the property specific functions above, we had created these more general functions:

declare function container_get(self object<container>! , field text!) int!;
declare proc container_set(self object<container>!, field text!, value int!);

These functions have the desired property name as a parameter (field) and so they can work with any field.

@op object<container> : get all as container_get;
@op object<container> : set all as container_set;

With these in place, cont.y is no longer an error. We get these transforms:

cont.x += 1;
cont.y += 1;
-- specific functions for 'x'
CALL container_set_x(cont, container_get_x(cont) + 1);

-- generic functions for 'y'
CALL container_set(cont, 'y', container_get(cont, 'y') + 1);

Nearly the same, but more generic. This is great if the properties are open-ended. Perhaps for a dictionary or something representing JSON. Anything like that.

In fact, the property doesn’t have to be a compile-time constant. Let’s look at array syntax next.

Using Arrays in CQL

Array access is just like the open-ended property form with two differences:

• the type of the index can be anything you want it to be, not just a property name
• there can be as many indices as you like

cont['x'] += 1;

You get exactly the same transform if you set up the array mapping:

@op object<container> : array get container_get;
@op object<container> : array set container_set;

To yield:

CALL container_set(cont, 'x', container_get(cont, 'x') + 1);

The index type is not contrained so you can make several mappings with different signatures. For instance:

cont[1,2] := cont[3,4] + cont[5,6];

Could be supported by these functions:

declare function cont_get_ij(self object<container>!, i int!, j int!) int!;
declare proc cont_set_ij(self object<container>!, i int!, j int!, value int!);

@op object<container> : array get as cont_get_ij;
@op object<container> : array set as cont_set_ij;

This results in:

CALL cont_set_ij(cont, 1, 2, cont_get_ij(cont, 3, 4) + cont_get_ij(cont, 5, 6));

This is a very simple transform that allows for the creation of any array-like behavior you might want.

Notes On Implementation

In addition to the function pattern shown above, you can use the “proc as func” form to get values, like so:

create proc container(self object<container>!, field text!, out value int!)
begin
  value := something_using_container_and_field;
end;

And with this form, you could write all of the property management or array management in CQL directly.

However, it’s often the case that you want to use native data structures to hold your things-with-properties or your arrays. If that’s the case, you can write them in C as usual, using the normal interface types to CQL that are defined in cqlrt.h. The exact functions you need to implement will be emitted into the header file output for C. For Lua, things are even easier; just write matching Lua functions in Lua. It’s all primitives or dictionaries in Lua.

In C, you could also implement some or all of the property reading functions as macros. You could add these macros to your copy of cqlrt.h (it’s designed to be changed) or you could emit them with @echo c, "#define stuff\n";

Finally, the no check version of the functions or procedures can also be used. This will let you use a var-arg list, for instance, in your arrays, which might be interesting. Variable indices can be used in very flexible array builder forms.

Another interesting aspect of the no check version of the APIs is that the calling convention for such functions is a little different in C (in Lua it’s the same). In C, the no check forms most common target is the printf function. But printf accepts C strings not CQL text objects. This means any text argument must be converted to a normal C string before making the call. But it also means that string literals pass through unchanged into the C!

For instance:

  call printf("Hello world\n");

becomes:

  printf("Hello world\n");

So likewise, if your array or property getters are no check, then cont.x := 1; becomes maybe container_set(cont, "x", 1). Importantly, the C string literal "x" will fold across any number of uses in your whole program, so there will only be one copy of the literal. This gives great economy for the flexible type case and it is actually why no check functions were added to the language, rounding out all the no check flavors.

Chapter 4: Procedures, Functions, and Control Flow

Though we’ve already introduced examples of procedures, let’s now go over some of the additional aspects we have not yet illustrated, like arguments and control-flow.

Out Parameters

Consider this procedure:

create procedure copy_integer(in arg1 integer not null, out arg2 integer not null)
begin
  set arg2 := arg1;
end;

arg1 has been declared as in. This is the default: in arg1 integer not null, and arg1 integer not null mean the exact same thing.

arg2, however, has been declared as out. When a parameter is declared using out, arguments for it are passed by reference. This is similar to by-reference arguments in other languages; indeed, they compile into a simple pointer reference in the generated C code.

Given that arg2 is passed by reference, the statement set arg2 := arg1; actually updates a variable in the caller. For example:

declare x int not null;
call copy_integer(42, x);
-- `x` is now 42

It is important to note that values cannot be passed into a procedure via an out parameter. In fact, out parameters are immediately assigned a new value as soon as the procedure is called:

• All nullable out parameters are set to null.
• Nonnull out parameters of a non-reference type (e.g., integer, long, bool, etc.) are set to their default values (0, 0.0, false, etc.).
• Nonnull out parameters of a reference type (e.g., blob, object, and text) are set to null as there are no default values for reference types. They must, therefore, be assigned a value within the procedure so that they will not be null when the procedure returns. CQL enforces this.

In addition to in and out parameters, there are also inout parameters. inout parameters are, as one might expect, a combination of in and out parameters: The caller passes in a value as with in parameters, but the value is passed by reference as with out parameters.

inout parameters allow for code such as the following:

proc times_two(inout arg integer not null)
begin
  -- note that a variable in the caller is both read from and written to
   
  arg += arg; -- this is the same as set arg := arg + arg; 
end;

let x := 2;
times_two(x); -- this is the same as call times_two(x)
-- `x` is now 4

Procedure Calls

The usual call syntax is used to invoke a procedure. It returns no value, but it can have any number of out arguments.

  declare scratch integer not null;
  call copy_integer(12, scratch);
  scratch == 12; -- true

Let’s go over the most essential bits of control flow.

The IF statement

The CQL IF statement has no syntactic ambiguities at the expense of being somewhat more verbose than many other languages. In CQL, the ELSE IF portion is baked into the IF statement, so what you see below is logically a single statement.

create proc checker(foo integer, out result integer not null)
begin
  if foo = 1 then
    result := 1;
  else if foo = 2 then
    result := 3;
  else
    result := 5;
  end if;
end;

The WHILE statement

What follows is a simple procedure that counts down its input argument.

declare procedure printf no check;

create proc looper(x integer not null)
begin
  while x > 0
  begin
   call printf('%d\n', x);
   x -= 1;
  end;
end;

The WHILE loop has additional keywords that can be used within it to better control the loop. A more general loop might look like this:

declare procedure printf no check;

create proc looper(x integer not null)
begin
  while 1
  begin
   x -= 1;
   if x < 0 then
     leave;
   else if x % 100 = 0 then
     continue;
   else if x % 10 = 0 then
     call printf('%d\n', x);
   end if;
  end;
end;

Let’s go over this peculiar loop:

  while 1
  begin
    ...
  end;

This is an immediate sign that there will be an unusual exit condition. The loop will never end without one because 1 will never be false.

   if x < 0 then
     leave;

Now here we’ve encoded our exit condition a bit strangely: we might have done the equivalent job with a normal condition in the predicate part of the while statement but for illustration anyway, when x becomes negative leave will cause us to exit the loop. This is like break in C.

   else if x % 100 = 0 then
     continue;

This bit says that on every 100th iteration, we go back to the start of the loop. So the next bit will not run, which is the printing.

   else if x % 10 = 0 then
     call printf('%d\n', x);
   end if;

Finishing up the control flow, on every 10th iteration we print the value of the loop variable.

The SWITCH Statement

The CQL SWITCH is designed to map to the C switch statement for better code generation and also to give us the opportunity to do better error checking. SWITCH is a statement like IF, not an expression like CASE..WHEN..END, so it combines with other statements. The general form looks like this:

SWITCH switch-expression [optional ALL VALUES]
WHEN expr1, expr2, ... THEN
  [statement_list]
WHEN expr3, ... THEN
  [statement_list]
WHEN expr4 THEN
  NOTHING
ELSE
  [statement_list]
END;

• the switch-expression must be a not-null integral type (integer not null or long integer not null)
• the WHEN expressions [expr1, expr2, etc.] are made from constant integer expressions (e.g. 5, 1+7, 1<<2, or my_enum.thing)
• the WHEN expressions must be compatible with the switch expression (long constants cannot be used if the switch expression is an integer)
• the values in the WHEN clauses must be unique (after evaluation)
• within one of the interior statement lists the LEAVE keyword exits the SWITCH prematurely, just like break in C
  • a LEAVE is not required before the next WHEN
  • there are no fall-through semantics as you can find in C, if fall-through ever comes to SWITCH it will be explicit
• if the keyword NOTHING is used after THEN it means there is no code for that case, which is useful with ALL VALUES (see below)
• the ELSE clause is optional and works just like default in C, covering any cases not otherwise explicitly listed
• if you add ALL VALUES then:
  • the expression must be an from an enum type
  • the WHEN values must cover every value of the enum
    • enum members that start with a leading _ are by convention considered pseudo values and do not need to be covered
  • there can be no extra WHEN values not in the enum
  • there can be no ELSE clause (it would defeat the point of listing ALL VALUES which is to get an error if new values come along)

Some more complete examples:

let x := get_something();
switch x
  when 1,1+1 then -- constant expressions ok
    set y := 'small';
    -- other stuff
  when 3,4,5 then
    set y := 'medium';
    -- other stuff
  when 6,7,8 then
    set y := 'large';
    -- other stuff
  else
    set y := 'zomg enormous';
end;

declare enum item integer (
  pen = 0, pencil, brush,
  paper, canvas,
  _count
);

let x := get_item(); -- returns one of the above

switch x all values
  when item.pen, item.pencil then
     call write_something();
  when item.brush then nothing
     -- itemize brush but it needs no code
  when item.paper, item.canvas then
    call setup_writing();
end;

Using THEN NOTHING allows the compiler to avoid emitting a useless break in the C code. Hence, that choice is better/clearer than when brush then leave;.

Note that the presence of _count in the enum will not cause an error in the above because it starts with _.

The C output for this statement will be a C switch statement.

The TRY, CATCH, and THROW Statements

This example illustrates catching an error from some DML, and recovering rather than letting the error cascade up. This is the common “upsert” pattern (insert or update)

declare procedure printf no check;

create procedure upsert_foo(id_ integer, t_ text)
begin
  try
    insert into foo(id, t) values(id_, t_);
  catch
    try
      update foo set t = t_ where id = id_;   
    catch
      call printf("Error code %d!\n", @rc);
      throw;
    end;
  end;
end;

Once again, let’s go over this section by section:

  try
    insert into foo(id, t) values(id_, t_);
  catch

Normally if the insert statement fails, the procedure will exit with a failure result code. Here, instead, we prepare to catch that error.

  catch
    try
      update foo set t = t_ where id = id_;
    catch

Now, having failed to insert, presumably because a row with the provided id already exists, we try to update that row instead. However that might also fail, so we wrap it in another try. If the update fails, then there is a final catch block:

    catch
      call printf("Error code %d!\n", @rc);
      throw;
    end;

Here we see a usage of the @rc variable to observe the failed error code. In this case, we simply print a diagnostic message and then use the throw keyword to rethrow the previous failure (exactly what is stored in @rc). In general, throw will create a failure in the current block using the most recent failed result code from SQLite (@rc) if it is an error, or else the general SQLITE_ERROR result code if there is no such error. In this case, the failure code for the update statement will become the result code of the current procedure.

This leaves only the closing markers:

  end;
end;

If control flow reaches the normal end of the procedure it will return SQLITE_OK.

Procedures as Functions: Motivation and Example

The calling convention for CQL stored procedures often requires that the procedure return a result code from SQLite. This makes it impossible to write a procedure that returns a result like a C function, as the result position is already used for the error code. You can get around this problem by using out arguments as your return results. So, for instance, this version of the Fibonacci function is possible.

-- this works, but it is awkward
create procedure fib (in arg integer not null, out result integer not null)
begin
  if (arg <= 2) then
    set result := 1;
  else
    declare t integer not null;
    call fib(arg - 1, result);
    call fib(arg - 2, t);
    set result := t + result;
  end if;
end;

The above works, but the notation is very awkward.

CQL has a “procedures as functions” feature that tries to make this more pleasant by making it possible to use function call notation on a procedure whose last argument is an out variable. You simply call the procedure like it was a function and omit the last argument in the call. A temporary variable is automatically created to hold the result and that temporary becomes the logical return of the function. For semantic analysis, the result type of the function becomes the type of the out argument.

-- rewritten with function call syntax
create procedure fib (in arg integer not null, out result integer not null)
begin
  if (arg <= 2) then
    set result := 1;
  else
    set result := fib(arg - 1) + fib(arg - 2);
  end if;
end;

This form is allowed when:

• all but the last argument of the procedure was specified
• the formal parameter for that last argument was marked with out (neither in nor inout are acceptable)
• the procedure does not return a result set using a select statement or out statement (more on these later)

Chapter 5: Types of Cursors, Shapes, OUT and OUT UNION, and FETCH

In the previous chapters we have used cursor variables without fully discussing them. Most of the uses are fairly self-evident but a more exhaustive discussion is also useful.

First there are three types of cursors, as we will see below.

Statement Cursors

A statement cursor is based on a SQL SELECT statement. A full example might look like this:

-- elsewhere
create table xy_table(x integer, y integer);

declare C cursor for select x, y from xy_table;

When compiled, this will result in creating a SQLite statement object (type sqlite_stmt *) and storing it in a variable called C_stmt. This statement can then be used later in various ways.

Here’s perhaps the simplest way to use the cursor above:

declare x, y integer;
fetch C into x, y;

This will have the effect of reading one row from the results of the query into the local variables x and y.

These variables might then be used to create some output such as:

/* note use of double quotes so that \n is legal */
call printf("x:%d y:%d\n", ifnull(x, 0), ifnull(y,0));

More generally, there the cursor may or may not be holding fetched values. The cursor variable C can be used by itself as a boolean indicating the presence of a row. So a more complete example might be

if C then
  call printf("x:%d y:%d\n", ifnull(x, 0), ifnull(y,0));
else
  call printf("nada\n");
end if

And even more generally

loop fetch C into x, y
begin
  call printf("x:%d y:%d\n", ifnull(x, 0), ifnull(y,0));
end;

The last example above reads all the rows and prints them.

Now if the table xy_table had instead had dozens of columns, those declarations would be very verbose and error prone, and frankly annoying, especially if the table definition was changing over time.

To make this a little easier, there are so-called ‘automatic’ cursors. These happen implicitly and include all the necessary storage to exactly match the rows in their statement. Using the automatic syntax for the above looks like so:

declare C cursor for select * from xy_table;
fetch C;
if C then
  call printf("x:%d y:%d\n", ifnull(C.x, 0), ifnull(C.y,0));
end if;

or the equivalent loop form:

declare C cursor for select * from xy_table;
loop fetch C
begin
  call printf("x:%d y:%d\n", ifnull(C.x, 0), ifnull(C.y,0));
end;

All the necessary local state is automatically created, hence “automatic” cursor. This pattern is generally preferred, but the loose variables pattern is in some sense more general.

In all the cases if the number or type of variables do not match the select statement, semantic errors are produced.

Value Cursors

The purpose of value cursors is to make it possible for a stored procedure to work with structures as a unit rather than only field by field. SQL doesn’t have the notion of structure types, but structures actually appear pretty directly in many places. Generally we call these things “Shapes” and there are a variety of source for shapes including:

• the columns of a table
• the projection of a SELECT statement
• the columns of a cursor
• the result type of a procedure that returns a select
• the arguments of a procedure
• other things derived from the above

Let’s first start with how you declare a value cursor. It is providing one of the shape sources above.

So:

declare C cursor like xy_table;
declare C cursor like select 1 a, 'x' b;
declare C cursor like (a integer not null, b text not null);
declare C cursor like my_view;
declare C cursor like my_other_cursor;
declare C cursor like my_previously_declared_stored_proc;
declare C cursor like my_previously_declared_stored_proc arguments;

Any of those forms define a valid set of columns – a shape. Note that the select example in no way causes the query provided to run. Instead, the select statement is analyzed and the column names and types are computed. The cursor gets the same field names and types. Nothing happens at run time.

The last two examples assume that there is a stored procedure defined somewhere earlier in the same translation unit and that the procedure returns a result set or has arguments, respectively.

In all cases the cursor declaration makes a cursor that could hold the indicated result. That result can then be loaded with FETCH or emitted with OUT or OUT UNION which will be discussed below.

Once we have declared a value cursor we can load it with values using FETCH in its value form. Here are some examples:

Fetch from compatible values:

fetch C from values(1,2);

Fetch from a call to a procedure that returns a single row:

fetch C from call my_previously_declared_stored_proc();

Fetch from another cursor:

fetch C from D;

In this last case if D is a statement cursor it must also be “automatic” (i.e. it has the storage). This form lets you copy a row and save it for later. For instance, in a loop you could copy the current max-value row into a value cursor and use it after the loop, like so:

declare C cursor for select * from somewhere;
declare D cursor like C;

loop fetch C
begin
  if (not D or D.something < C.something) then
    fetch D from C;
  end if;
end;

After the loop, D either empty because there were no rows (thus if D would fail) or else it has the row with the maximum value of something, whatever that is.

Value cursors are always have their own storage, so you could say all value cursors are “automatic”.

And as we saw above, value cursors may or may not be holding a row.

declare C cursor like xy_table;
if not C then
  call printf("this will always print because C starts empty\n");
end if;

When you call a procedure you may or may not get a row as we’ll see below.

The third type of cursor is a “result set” cursor but that won’t make any sense until we’ve discussed result sets a little which requires OUT and/or OUT UNION and so we’ll go on to those statements next. As it happens, we are recapitulating the history of cursor features in the CQL language by exploring the system in this way.

Benefits of using named typed to declare a cursor

This form allows any kind of declaration, for instance:

declare C cursor like ( id integer not null, val real, flag boolean );

This wouldn’t really give us much more than the other forms, however typed name lists can include LIKE in them again, as part of the list. Which means you can do this kind of thing:

declare C cursor like (like D, extra1 real, extra2 bool)

You could then load that cursor like so:

fetch C from values (from D, 2.5, false);

and now you have D plus 2 more fields which maybe you want to output.

Importantly this way of doing it means that C always includes D, even if D changes over time. As long as the extra1 and extra2 fields don’t conflict names it will always work.

OUT Statement

Value cursors were initially designed to create a convenient way for a procedure to return a single row from a complex query without having a crazy number of OUT parameters. It’s easiest to illustrate this with an example.

Suppose you want to return several variables, the “classic” way to do so would be a procedure like this:

create proc get_a_row(
  id_ integer not null,
  out got_row bool not null,
  out w integer not null,
  out x integer,
  out y text not null,
  out z real)
begin
  declare C cursor for
    select w, x, y, z from somewhere where id = id_;
  fetch C into w, x, y, z;
  set got_row := C;
end;

This is already verbose, but you can imagine the situation gets very annoying if get_a_row has to produce a couple dozen column values. And of course you have to get the types exactly right. And they might evolve over time. Joy.

On the receiving side you get to do something just as annoying:

declare w integer not null
declare x integer;
declare y text;
declare z real;
declare got_row bool not null;
call get_a_row(id, got_row, w, x, y, z);

Using the out statement we get the equivalent functionality with a much simplified pattern. It looks like this:

create proc get_a_row(id_ integer not null)
begin
  declare C cursor for
    select w, x, y, z from somewhere where id = id_;
  fetch C;
  out C;
end;

To use the new procedure you simply do this:

declare C cursor like get_a_row;
fetch C from call get_a_row(id);

In fact, originally you did the two steps above in one statement and that was the only way to load a value cursor. Later, the calculus was generalized. The original form still works:

declare C cursor fetch from call get_a_row(id);

The OUT statement lets you return a single row economically and lets you then test if there actually was a row and if so, read the columns. It infers all the various column names and types so it is resilient to schema change and generally a lot less error prone than having a large number of out arguments to your procedure.

Once you have the result in a value cursor you can do the usual cursor operations to move it around or otherwise work with it.

The use of the LIKE keyword to refer to groups of columns spread to other places in CQL as a very useful construct, but it began here with the need to describe a cursor shape economically, by reference.

OUT UNION Statement

The semantics of the OUT statement are that it always produces one row of output (a procedure can produce no row if an out never actually rans but the procedure does use OUT).

If an OUT statement runs more than once, the most recent row becomes the result. So the OUT statement really does mirror having one out variable for each output column. This was its intent and procedures that return at most, or exactly, one row are very common so it works well enough.

However, in general, one row results do not suffice; you might want to produce a result set from various sources, possibly with some computation as part of the row creation process. To make general results, you need to be able to emit multiple rows from a computed source. This is exactly what OUT UNION provides.

Here’s a (somewhat contrived) example of the kind of thing you can do with this form:

create proc foo(n integer not null)
begin
  declare C cursor like select 1 value;
  let i := 0;
  while i < n
  begin
     -- emit one row for every integer
     fetch C from values(i);
     out union C;
     set i := i + 1;
  end;
end;

In foo above, we make an entire result set out of thin air. It isn’t a very interesting result, but of course any computation would have been possible.

This pattern is very flexible as we see below in bar where we merge two different data streams.

create table t1(id integer, stuff text, [other things too]);
create table t2(id integer, stuff text, [other things too]);

create proc bar()
begin
  declare C cursor for select * from t1 order by id;
  declare D cursor for select * from t2 order by id;

  fetch C;
  fetch D;

  -- we're going to merge these two queries
  while C or D
  begin
    -- if both have a row pick the smaller id
    if C and D then
       if C.id < D.id then
         out union C;
         fetch C;
       else
         out union D;
         fetch D;
       end if;
    else if C then
      -- only C has a row, emit that
      out union C;
      fetch C;
    else
      -- only D has a row, emit that
      out union D;
      fetch D;
    end if;
  end;
end;

Just like foo, in bar, each time OUT UNION runs a new row is accumulated.

Now, if you build a procedure that ends with a SELECT statement CQL automatically creates a fetcher function that does something like an OUT UNION loop – it loops over the SQLite statement for the SELECT and fetches each row, materializing a result.

With OUT UNION you take manual control of this process, allowing you to build arbitrary result sets. Note that either of C or D above could have been modified, replaced, skipped, normalized, etc. with any kind of computation. Even entirely synthetic rows can be computed and inserted into the output as we saw in foo.

Result Set Cursors

Now that we have OUT UNION it makes sense to talk about the final type of cursor.

OUT UNION makes it possible to create arbitrary result sets using a mix of sources and filtering. Unfortunately this result type is not a simple row, nor is it a SQLite statement. This meant that neither of the existing types of cursors could hold the result of a procedure that used OUT UNION. – CQL could not itself consume its own results.

To address this hole, we need an additional cursor type. The syntax is exactly the same as the statement cursor cases described above but, instead of holding a SQLite statement, the cursor holds a result set pointer and the current and maximum row numbers. Stepping through the cursor simply increments the row number and fetches the next row out of the rowset instead of from SQLite.

Example:

-- reading the above
create proc reader()
begin
  declare C cursor for call bar();
  loop fetch C
  begin
    call printf("%d %s\n", C.id, C.stuff);  -- or whatever fields you need
  end;
end;

If bar had been created with a SELECT, UNION ALL, and ORDER BY to merge the results, the above would have worked with C being a standard statement cursor, iterating over the union. Since foo produces a result set, CQL transparently produces a suitable cursor implementation behind the scenes, but otherwise the usage is the same.

Note this is a lousy way to simply iterate over rows; you have to materialize the entire result set so that you can just step over it. Re-consuming like this is not recommended at all for production code, but it is ideal for testing result sets that were made with OUT UNION which otherwise would require C/C++ to test. Testing CQL with CQL is generally a lot easier.

Reshaping Data, Cursor LIKE forms

There are lots of cases where you have big rows with many columns, and there are various manipulations you might need to do.

What follows is a set of useful syntactic sugar constructs that simplify handling complex rows. The idea is that pretty much anywhere you can specify a list of columns you can instead use the LIKE x construct to get the columns as they appear in the shape x – which is usually a table or a cursor.

It’s a lot easier to illustrate with examples, even though these are, again, a bit contrived.

First we need some table with lots of columns – usually the column names are much bigger which makes it all the more important to not have to type them over and over, but in the interest of some brevity, here is a big table:

create table big (
  id integer primary key,
  id2 integer unique,
  a integer,
  b integer,
  c integer,
  d integer,
  e integer,
  f integer
);

This example showcases several of the cursor and shape slicing features by emitting two related rows:

create proc foo(id_ integer not null)
begin
  -- this is the shape of the result we want -- it's some of the columns of "big"
  -- note this query doesn't run, we just use its shape to create a cursor
  -- with those columns.
  declare result cursor like select id, b, c, d from big;

  -- fetch the main row, specified by id_
  -- main row has all the fields, including id2
  declare main_row cursor for select * from big where id = id_;
  fetch main_row;

  -- now fetch the result columns out of the main row
  -- `like result` here means to use the names of the result cursor
  -- to index into the columns of the main_row cursor, and then
  -- and store them in `result`
  fetch result from cursor main_row(like result);

  -- this is our first result row
  out union result;

  -- now we want the related row, but we only need two columns
  -- from the related row, 'b' and 'c'
  declare alt_row cursor for select b, c from big where big.id2 = main_row.id2;
  fetch alt_row;

  -- update some of the fields in 'result' from the `alt_row`
  update cursor result(like alt_row) from cursor alt_row;

  -- and emit the modified result, so we've generated two rows
  out union result;
end;

Now let’s briefly discuss what is above. The two essential parts are:

fetch result from cursor main_row(like result);

and

update cursor result(like alt_row) from cursor alt_row;

In the first case what we’re saying is that we want to load the columns of result from main_row but we only want to take the columns that are actually present in result. So this is a narrowing of a wide row into a smaller row. In this case, the smaller row, result, is what we want to emit. We needed the other columns to compute alt_row.

The second case, what we’re saying is that we want to update result by replacing the columns found in alt_row with the values in alt_row. So in this case we’re writing a smaller cursor into part of a wider cursor. Note that we used the update cursor form here because it preserves all other columns. If we used fetch we would be rewriting the entire row contents, using NULL if necessary, and that is not desired here.

Here is the rewritten version of the above procedure; this is what ultimately gets compiled into C.

CREATE PROC foo (id_ INTEGER NOT NULL)
BEGIN
  DECLARE result CURSOR LIKE SELECT id, b, c, d FROM big;
  DECLARE main_row CURSOR FOR SELECT * FROM big WHERE id = id_;
  FETCH main_row;

  FETCH result(id, b, c, d)
    FROM VALUES(main_row.id, main_row.b, main_row.c, main_row.d);
  OUT UNION result;

  DECLARE alt_row CURSOR FOR SELECT b, c FROM big WHERE big.id2 = main_row.id2;
  FETCH alt_row;

  UPDATE CURSOR result(b, c) FROM VALUES(alt_row.b, alt_row.c);
  OUT UNION result;
END;

Of course you could have typed the above directly but if there are 50 odd columns it gets old fast and is very error prone. The sugar form is going to be 100% correct and will require much less typing and maintenance.

Finally, while I’ve shown both LIKE forms separately, they can also be used together. For instance:

  update cursor C(like X) from cursor D(like X);

The above would mean, “move the columns that are found in X from cursor D to cursor C”, presuming X has columns common to both.

Fetch Statement Specifics

Many of the examples used the FETCH statement in a sort of demonstrative way that is hopefully self-evident but the statement has many forms and so it’s worth going over them specifically. Below we’ll use the letters C and D for the names of cursors. Usually C;

Fetch with Statement or Result Set Cursors

A cursor declared in one of these forms:

• declare C cursor for select * from foo;
• declare C cursor for call foo(); (foo might end with a select or use out union)

is either a statement cursor or a result set cursor. In either case the cursor moves through the results. You load the next row with:

• FETCH C, or
• FETCH C into x, y, z;

In the first form C is said to be automatic in that it automatically declares the storage needed to hold all its columns. As mentioned above, automatic cursors have storage for their row.

Having done this fetch you can use C as a scalar variable to see if it holds a row, e.g.

declare C cursor for select * from foo limit 1;
fetch C;
if C then
  -- bingo we have a row
  call printf("%s\n", C.whatever);
end if

You can easily iterate, e.g.

declare C cursor for select * from foo;
loop fetch C
begin
  -- one time for every row
  call printf("%s\n", C.whatever);
end;

Automatic cursors are so much easier to use than explicit storage that explicit storage is rarely seen. Storing to out parameters is one case where explicit storage actually is the right choice, as the out parameters have to be declared anyway.

Fetch with Value Cursors

A value cursor is declared in one of these ways:

• declare C cursor fetch from call foo(args)
  • foo must be a procedure that returns one row with OUT
• declare C cursor like select 1 id, "x" name;
• declare C cursor like X;
  • where X is the name of a table, a view, another cursor, or a procedure that returns a structured result

A value cursor is always automatic; it’s purpose is to hold a row. It doesn’t iterate over anything but it can be re-loaded in a loop.

• fetch C or fetch C into ... is not valid on such a cursor, because it doesn’t have a source to step through.

The canonical way to load such a cursor is:

• fetch C from call foo(args);
  • foo must be a procedure that returns one row with OUT
• fetch C(a,b,c...) from values(x, y, z);

The first form is in some sense the origin of the value cursor. Value cursors were added to the language initially to provide a way to capture the single row OUT statement results, much like result set cursors were added to capture procedure results from OUT UNION. In the first form, the cursor storage (a C struct) is provided by reference as a hidden out parameter to the procedure and the procedure fills it in. The procedure may or may not use the OUT statement in its control flow, as the cursor might not hold a row. You can use if C then ... as before to test for a row.

The second form is more interesting as it allows the cursor to be loaded from arbitrary expressions subject to some rules:

• you should think of the cursor as a logical row: it’s either fully loaded or it’s not, therefore you must specify enough columns in the column list to ensure that all NOT NULL columns will get a value
• if not mentioned in the list, NULL will be loaded where possible
• if insufficient columns are named, an error is generated
• if the value types specified are not compatible with the column types mentioned, an error is generated
• later in this chapter, we’ll show that columns can also be filled with dummy data using a seed value

With this form, any possible valid cursor values could be set, but many forms of updates that are common would be awkward. So there are various forms of syntactic sugar that are automatically rewritten into the canonical form. See the examples below:

• fetch C from values(x, y, z)
  • if no columns are specified this is the same as naming all the columns, in declared order
• fetch C from arguments
  • the arguments to the procedure in which this statement appears are used as the values, in order
  • in this case C was also rewritten into C(a,b,c,..) etc.
• fetch C from arguments like C
  • the arguments to the procedure in which this statement appears are used, by name, as the values, using the names of of the indicated shape
  • the order in which the arguments appeared no longer matters, the names that match the columns of C are used if present
  • the formal parameter name may have a single trailing underscore (this is what like C would generate)
  • e.g. if C has columns a and b then there must exist formals named a or a_ and b or b_, in any position
• fetch C(a,b) from cursor D(a,b)
  • the named columns of D are used as the values
  • in this case the statement becomes: fetch C(a,b) from values(D.a, D.b);

That most recent form doesn’t seem like it saves much, but recall the first rewrite:

• fetch C from cursor D
  • both cursors are expanded into all their columns, creating a copy from one to the other
  • fetch C from D can be used if the cursors have the exact same column names and types; it also generates slightly better code and is a common case

It is very normal to want to use only some of the columns of a cursor; these LIKE forms do that job. We saw some of these forms in an earlier example.

• fetch C from cursor D(like C)
  • here D is presumed to be “bigger” than C, in that it has all of the C columns and maybe more. The like C expands into the names of the C columns so C is loaded from the C part of D
  • the expansion might be fetch C(a, b, g) from values (D.a, D.b, D.g)
  • D might have had fields c, d, e, f which were not used because they are not in C.

The symmetric operation, loading some of the columns of a wider cursor can be expressed neatly:

• fetch C(like D) from cursor D
  • the like D expands into the columns of D causing the cursor to be loaded with what’s in D and NULL (if needed)
  • when expanded, this might look like fetch C(x, y) from values(D.x, D.y)

LIKE can be used in both places, for instance suppose E is a shape that has a subset of the rows of both C and D. You can write a form like this:

• fetch C(like E) from cursor D(like E)
  • this means take the column names found in E and copy them from D to C.
  • the usual type checking is done

As is mentioned above, the fetch form means “load an entire row into the cursor”. This is important because “half loaded” cursors would be semantically problematic. However there are many cases where you might like to amend the values of an already loaded cursor. You can do this with the update form.

• update cursor C(a,b,..) from values(1,2,..);
  • the update form is a no-op if the cursor is not already loaded with values (!!)
  • the columns and values are type checked so a valid row is ensured (or no row)
  • all the re-writes above are legal so update cursor C(like D) from D is possible; it is in fact the use-case for which this was designed.

Calling Procedures with Argument Bundles

It’s often desirable to treat bundles of arguments as a unit, or cursors as a unit, especially when calling other procedures. The shape patterns above are very helpful for moving data between cursors, and the database. These can be rounded out with similar constructs for procedure definitions and procedure calls as follows.

First we’ll define some shapes to use in the examples. Note that we made U using T.

create table T(x integer not null, y integer not null,  z integer not null);
create table U(like T, a integer not null, b integer not null);

We haven’t mentioned this before but the implication of the above is that you can use the LIKE construct inside a table definition to add columns from a shape.

We can also use the LIKE construct to create procedure arguments. To avoid conflicts with column names, when used this way the procedure arguments all get a trailing underscore appended to them. The arguments will be x_, y_, and z_ as we can see if the following:

create proc p1(like T)
begin
  call printf("%d %d %d\n", x_, y_, z_);
end;

Shapes can also be used in a procedure call, as showed below. This next example is obviously contrived, but of course it generalizes. It is exactly equivalent to the above.

create proc p2(like T)
begin
  call printf("%d %d %d\n", from arguments);
end;

Now we might want to chain these things together. This next example uses a cursor to call p1.

create proc q1()
begin
 declare C cursor for select * from T;
 loop fetch C
 begin
   /* this is the same as call p(C.x, C.y, C.z) */
   call p1(from C);
 end;
end;

The LIKE construct allows you to select some of the arguments, or some of a cursor to use as arguments. This next procedure has more arguments than just T. The arguments will be x_, y_, z_, a_, b_. But the call will still have the T arguments x_, y_, and z_.

create proc q2(like U)
begin
  /* just the args that match T: so this is still call p(x_, y_, z_) */
  call p1(from arguments like T);
end;

Or similarly, using a cursor.

create proc q3(like U)
begin
 declare C cursor for select * from U;
 loop fetch C
 begin
  /* just the columns that match T so this is still call p(C.x, C.y, C.z) */
  call p1(from C like T);
 end;
end;

Note that the from argument forms do not have to be all the arguments. For instance you can get columns from two cursors like so:

  call something(from C, from D)

All the varieties can be combined but of course the procedure signature must match. And all these forms work in function expressions as well as procedure calls.

e.g.

  set x := a_function(from C);

Since these forms are simply syntatic sugar, they can also appear inside of function calls that are in SQL statements. The variables mentioned will be expanded and become bound variables just like any other variable that appears in a SQL statement.

Note the form x IN (from arguments) is not supported at this time, though this would be a relatively easy addition.

Using Named Argument Bundles

There are many cases where stored procedures require complex arguments using data shapes that come from the schema, or from other procedures. As we have seen the LIKE construct for arguments can help with this, but it has some limitations. Let’s consider a specific example to study:

create table Person (
  id text primary key,
  name text not null,
  address text not null,
  birthday real
);

To manage this table we might need something like this:

create proc insert_person(like Person)
begin
  insert into Person from arguments;
end;

As we have seen, the above expands into:

create proc insert_person(
  id_ text not null,
  name_ text not null,
  address_ text not null,
  birthday_ real)
begin
  insert into Person(id, name, address, birthday)
    values(id_, name_, address_, birthday_);
end;

It’s clear that the sugared version is a lot easier to reason about than the fully expanded version, and much less prone to errors as well.

This much is already helpful, but just those forms aren’t general enough to handle the usual mix of situations. For instance, what if we need a procedure that works with two people? A hypothetical insert_two_people procedure cannot be written with the forms we have so far.

To generalize this the language adds the notion of named argument bundles. The idea here is to name the bundles which provides a useful scoping. Example:

create proc insert_two_people(p1 like Person, p2 like Person)
begin
  -- using a procedure that takes a Person args
  call insert_person(from p1);
  call insert_person(from p2);
end;

or alternatively

create proc insert_two_people(p1 like Person, p2 like Person)
begin
  -- inserting a Person directly
  insert into Person from p1;
  insert into Person from p2;
end;

The above expands into:

create proc insert_two_people(
  p1_id text not null,
  p1_name text not null,
  p1_address text not null,
  p1_birthday real,
  p2_id text not null,
  p2_name text not null,
  p2_address text not null,
  p2_birthday real)
begin
  insert into Person(id, name, address, birthday)
    values(p1_id, p1_name, p1_address, p1_birthday);
  insert into Person(id, name, address, birthday)
    values(p2_id, p2_name, p2_address, p2_birthday);
end;

Or course different named bundles can have different types – you can create and name shapes of your choice. The language allows you to use an argument bundle name in all the places that a cursor was previously a valid source. That includes insert, fetch, update cursor, and procedure calls. You can refer to the arguments by their expanded name such as p1_address or alternatively p1.address – they mean the same thing.

Here’s another example showing a silly but illustrative thing you could do:

create proc insert_lotsa_people(P like Person)
begin
  -- make a cursor to hold the arguments
  declare C cursor like P;

  -- convert arguments to a cursor
  fetch C from P;

  -- set up to patch the cursor and use it 20 times
  let i := 0;
  while i < 20
  begin
    update cursor C(id) from values(printf("id_%d", i));
    insert into Person from C;
    set i := i + 1;
  end;
end;

The above shows that you can use a bundle as the source of a shape, and you can use a bundle as a source of data to load a cursor. After which you can do all the usual value cursor things. Of course in this case the value cursor was redundant, we could just as easily have done something like this:

  set P_id := printf("id_%d", i);
  insert into Person from P;
  set i := i + 1;

NOTE: the CQL JSON output includes extra information about procedure arguments if they originated as part of a shape bundle do identify the shape source for tools that might need that information.

The @COLUMNS construct in the SELECT statement

The select list of a SELECT statement already has complex syntax and functionality, but it is a very interesting place to use shapes. To make it possible to use shape notations and not confuse that notation with standard SQL the @COLUMNS construct was added to the language. This allows for a sugared syntax for extracting columns in bulk.

The @COLUMNS clause is like of a generalization of the select T.* with shape slicing and type-checking. The forms are discussed below:

Columns from a join table or tables

This is the simplest form, it’s just like T.*:

-- same as A.*
select @columns(A) from ...;

-- same as A.*, B.*
select @columns(A, B) from ...;

Columns from a particular joined table that match a shape

This allows you to choose some of the columns of one table of the FROM clause.

-- the columns of A that match the shape Foo
select @columns(A like Foo) from ...;

-- get the Foo shape from A and the Bar shape from B
select @columns(A like Foo, B like Bar) from ...;

Columns from any that match a shape, from anywhere in the FROM

Here we do not specify a particular table that contains the columns, the could come from any of the tables in the FROM clause.

--- get the Foo shape from anywhere in the join
select @columns(like Foo) from ...;

-- get the Foo and Bar shapes, from anywhere in the join
select @columns(like Foo, like Bar) from ...;

Subsets of Columns from shapes

This pattern can be helpful for getting part of a shape.

-- get the a and b from the Foo shape only
select @columns(like Foo(a,b))

This pattern is great for getting almost all of a shape (e.g. everything but the pk).

-- get the Foo shape except the a and b columns
select @columns(like Foo(-a, -b))

Specific columns

This form allows you to slice out a few columns without defining a shape, you simply list the exact columns you want.

-- T1.x and T2.y plus the Foo shape
select @columns(T1.x, T2.y, like Foo) from ...;

Distinct columns

Its often the case that there are duplicate column names in the FROM clause. For instance, you could join A to B with both having a column pk. The final result set can only have one column named pk, the distinct clause helps you to get distinct column names. In this context distinct is about column names, not values.

-- removes duplicate column names
-- e.g. there will be one copy of 'pk'
select @columns(distinct A, B) from A join B using(pk);

-- if both Foo and Bar have an (e.g.) 'id' field you only get one copy
select @columns(distinct like Foo, like Bar) from ...;

If a specific column is mentioned it is always included, but later expressions that are not a specific column will avoid that column name.

-- if F or B has an x it won't appear again, just T.x
select @columns(distinct T.x, F like Foo, B like Bar) from F, B ..;

Of course this is all just sugar, so it all compiles to a column list with table qualifications – but the syntax is very powerful. You can easily narrow a wide table, or fuse joins that share common keys without creating conflicts.

-- just the Foo columns
select @columns(like Foo) from Superset_Of_Foo_From_Many_Joins_Even;

-- only one copy of 'pk'
select @columns(distinct A,B,C) from
  A join B using (pk) join C using (pk);

And of course you can define shapes however you like and then use them to slice off column chucks of your choice. There are many ways to build up shapes from other shapes. For instance, you can declare procedures that return the shape you want and never actually create the procedure – a pattern is very much like a shape “typedef”. E.g.

declare proc shape1() (x integer, y real, z text);
declare proc shape2() (like shape1, u bool, v bool);

With this combination you can easily define common column shapes and slice them out of complex queries without having to type the columns names over and over.

Missing Data Columns, Nulls and Dummy Data

What follows are the rules for columns that are missing in an INSERT, or FETCH statement. As with many of the other things discussed here, the forms result in automatic rewriting of the code to include the specified dummy data. So SQLite will never see these forms.

Two things to note: First, the dummy data options described below are really only interesting in test code, it’s hard to imagine them being useful in production code. Second, none of what follows applies to the update cursor statement because its purpose is to do partial updates on exactly the specified columns and we’re about to talk about what happens with the columns that were not specified.