Appendix 6: CQL In 20 Minutes

What follows is a series of examples intended to illustrate the most important features of the CQL language. This appendix was significantly influenced by a similar article on Python at

Also of interest:

And with no further delay, CQL in 20 minutes...

-- Single line comments start with two dashes
/* C style comments also work
* C pre-processor features like #include and #define are generally available
* CQL is typically run through the C pre-processor before it is compile.
* 1. Primitive Datatypes and Operators
-- You have numbers
3 -- an integer
3L -- a long integer
3.5 -- a real literal
0x10 -- 16 in hex
-- Math is what you would expect
1 + 1 --> 2
8 - 1 --> 7
10 * 2 --> 20
35.0 / 5 --> 7.0
-- Modulo operation, same as C and SQLite
7 % 3 --> 1
-7 % 3 --> -1
7 % -3 --> 1
-7 % 3 --> -1
-- Bitwise operators bind left to right like in SQLite
1 | 4 & 3 --> 1 (not 0)
-- Enforce precedence with parentheses
1 + 3 * 2 --> 7
(1 + 3) * 2 --> 8
-- use 0 and 1 for bools
0 --> how to true
1 --> how to false
null --> null means "unknown" in CQL like SQLite
-- negate with not
not 0 --> 1
not 1 --> 0
not null --> null (not unknown is unknown)
-- Logical Operators
1 and 0 --> 0
0 or 1 --> 1
0 and x --> 0 and x not evaluated
1 or x --> 1 and x not evaluated
-- remember null is "unknown"
null or 0 --> null
null or 1 --> 1
null and 0 --> 0
null and 1 --> null
-- Non-zero values are true
0 --> false
4 --> true
-6 --> true
0 and 2 --> 0 (false)
-5 or 0 --> 1 (true)
-- Equality is ==, but note unknown is not == to anything
1 == 1 --> true
1 = 1 --> true (= and == are the same thing)
2 == 1 --> false
null == 1 --> null (hence not true)
null == null --> null (hence not true)
"x" == "x" --> true
-- IS lets you compare against null
1 IS 1 --> true
2 IS 1 --> false
null IS 1 --> false
null IS null --> true (Unknown is Unknown? Yes it is!)
"x" IS "x" --> true
-- x IS NOT y is the same as NOT (x IS y)
1 IS NOT 1 --> false
2 IS NOT 1 --> true
null IS NOT 1 --> true
null IS NOT null --> false
"x" IS NOT "x" --> false
-- Inequality is != or <>
1 != 1 --> false
2 <> 1 --> true
null != 1 --> null
null <> null --> null
-- More comparisons
1 < 10 --> true
1 > 10 --> false
2 <= 2 --> true
2 >= 2 --> true
10 < null --> null
-- Seeing whether a value is in a range
1 < 2 and 2 < 3 --> true
2 < 3 and 3 < 2 --> false
-- BETWEEN makes this look nicer
2 BETWEEN 1 AND 3 --> true
3 BETWEEN 2 AND 2 --> false
-- Strings are created with "x" or 'x'
"This is a string.\n" -- can have C style escapes (no embedded nulls)
"Th\x69s is a string.\n" -- even hex literals
'This isn''t a C style string' -- use '' to escape single quote ONLY
* 2. Simple Variables
-- CQL can call simple libc methods with no declaration
-- we'll need this for later examples so we can do something
-- with our expressions (i.e. print them)
call printf("I'm CQL. Nice to meet you!\n");
-- variables are declared with DECLARE
-- keywords and identifiers are not case sensitive
declare x integer not null;
-- you can call it X, it is the same thing.
set X := 0;
-- all variables begin with a null value if allowed, else a zero value.
declare y integer not null;
if y == 0 then
call printf("Yes, this will run.\n");
end if;
-- a nullable variable (i.e. not marked with not null) initialized to null
declare z real;
if z is null then
call printf("Yes, this will run.\n");
end if;
-- the various types
declare a_blob blob;
declare a_string text;
declare a_real real;
declare an_int integer;
declare a_long long;
declare an_object object;
-- there are some typical SQL synonyms
declare an_int int;
declare a_long long integer;
declare a_long long int;
declare a_long long_int;
-- the basic types can be tagged to make them less miscible
declare m real<meters>;
declare kg real<kilos>;
set m := kg; -- error!
-- object variables can be tagged so that they are not mixed-up easily
declare dict object<dict> not null;
declare list object<list> not null;
set dict := create_dict(); -- an external function that creates a dict
set dict := create_list(); -- error
set list := create_list(); -- ok
set list := dict; -- error
-- implied type initialization
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 psuedo function "nullable" converts the type of its arg to the nullable
-- version of the same thing.
LET n_i := nullable(1); -- nullable integer variable initialized to 1
LET l_i := nullable(1L); -- nullable long variable initialized to 1
* 3. Control Flow
-- Just make a variable
declare some_var integer not null;
set some_var := 5
-- Here is an if statement
if some_var > 10 then
call printf("some_var is totally bigger than 10.\n")
else if some_var < 10 then -- else if is optional
call printf("some_var is smaller than 10.\n")
else -- else is optional
call printf("some_var is indeed 10.\n")
end if;
-- while loops iterate as usual
declare i integer not null;
set i := 0;
while i < 5
call printf("%d\n", i);
set i := i + 1;
-- use "leave" to end a loop early
declare i integer not null;
set i := 0;
while i < 500
if i >= 5 then
-- we are not going to get anywhere near 500
end if;
call printf("%d\n", i);
set i := i + 1;
-- use "continue" to go back to the loop test
declare i integer not null;
set i := 0;
while i < 500
set i := i + 1;
if i % 2 then
continue; -- note we had to do this after "i" was incremented!
end if;
-- odd numbers will not be printed because of continue above
call printf("%d\n", i);
* 4. Complex Expression Forms
-- case is an expression, so it is more like the C ?: operator
-- than a switch statement. It is ?: on steroids.
case i -- a switch expression is optional
when 1 then "one" -- one or more cases
when 2 then "two"
else "other" -- else is optional
-- case with no common expression, is a series of booleans
when i == 1 then "i = one" -- booleans could be completely unrelated
when j == 2 then "j = two" -- first match wins
else "other"
-- if nothing matches the result is null
case 7
when 1 then "one"
--> result null
-- case is just an expression so it can nest
case X
when 1
case y when 1 "x:1 y:1"
else "x:1 y:other"
case when z == 1 "x:other z:1"
else "x:other z:other"
-- IN is used to test for membership
5 IN (1, 2, 3, 4, 5) --> true
7 IN (1, 2) --> false
null in (1, 2, 3) --> null
null in (1, null, 3) --> null (null == null is not true)
7 NOT IN (1, 2) --> true
null not in (null, 3) --> null
* 4. Working and "getting rid of" null
-- null can be annoying, you might need a not null value
-- in most operations null is radioactive
null + x --> null
null * x --> null
null == null --> null
-- IS and IS NOT always return 0 or 1
null is 1 -> 0
1 is not null -> 1
-- COALESCE returns the first non null arg, or the last arg if all were null
-- if the last arg is not null, you get a non null result for sure
COALESCE(x==y, 0) --> if x or y is null, you get 0 not null, not quite "is"
-- IFNULL is coalesce with 2 args only (COALESCE is more general)
IFNULL(x, -1) --> use -1 if x is null
-- the reverse, convert a sentinel value to unknown, more exotic
NULLIF(x, -1) --> if x is -1 then use null
-- the else part of a case can get rid of nulls
CASE when x == y then 1 else 0 end; --> true iff x = y and neither is null
-- case can be used to give you a default value after various tests
-- this expression is never null, "other" is returned if x is null
CASE when x > 0 then "pos" when x < 0 then "neg" else "other" end;
-- you can "throw" out of the current procedure (see exceptions below)
declare x integer not null;
set x := ifnull_throw(nullable_int_expr); -- returns non null, throws if null
-- if you have already tested the expression you can use assert-like form
if nullable_int_expr is not null then
-- I am very sure nullable_int_expression is not null
set x := ifnull_crash(nullable_int_expr);
end if;
* 5. Tables, Views, Indices, Triggers
-- most forms of data definition language DDL are supported
-- "loose" DDL (outside of any procedure) simply declares
-- schema, it does not actually create it, it is assumed to
-- exist as you specified.
create table T1(
id integer primary key,
t text,
r real
create table T2(
id integer primary key references T1(id),
l long,
b blob
-- CQL can take a series of declarations and automatically
-- create a procedure that will materialize the declarations
-- you made. This will not be discussed here. But you will get
-- procedures that have things like.
create proc make_tables()
create table T1 if not exists (
id integer primary key,
t text,
r real
-- views are supported
create view V1 as (select * from T1);
-- triggers are supported
create trigger if not exists trigger1
before delete on T1
delete from T2 where id =;
-- indices are supported
create index I1 on T1(t);
create index I2 on T1(r);
-- the various drop forms are supported
drop index I1;
drop index I2;
drop view V1;
drop table T2;
drop table T1;
-- a complete discussion of DDL is out of scope, refer to
* 6. Selecting Data
-- we will use this scratch variable in examples
declare rr real;
-- first observe CQL is a two-headed language
set rr := 1+1; -- this is evaluated in generated C code
set rr := (select 1+1); -- this select statement is sent to SQLite, it does the add
-- CQL tries to do most things the same as SQLite in the C context
-- but some things are exceedingly hard to emulate correctly.
-- Even simple looking things:
set rr := (select cast("1.23" as real)); --> rr := 1.23
set rr := cast("1.23" as real); --> error
-- In general, numeric/text conversions have to happen in SQLite context
-- because the specific library that does the conversion could be and usually
-- is different. It would not do to give different answers in one context or
-- another so those conversions are simply not supported.
-- Loose concatenation is not supported because of the implied conversions
-- Loose means "not in the context of a SQL statement"
set r := 1.23;
set r := (select cast("100"||r as real)); --> 1001.23 (a number)
set r := cast("100"||r as real); --> error, concat not supported in loose expr
-- illustrate a simple insertion
insert into T1 values (1, "foo", 3.14);
-- finally, reading from the database
set r := (select r from T1 where id = 1); --> r = 3.14
-- the (select ...) form requires the result to have at least one row
-- you can use "if nothing" forms to handle other cases
set r := (select r from T1
where id = 2
if nothing -1); --> r = -1
-- if the select statement might return a null result you can handle that as well
set r := (select r from T1
where id = 2
if nothing or null -1); --> r = -1
-- with no if nothing clause, lack of a row will cause the select expression to throw
-- an exception. "If nothing throw" merely makes this explicit.
set r := (select r from T1 where id = 2 if nothing throw); --> will throw
* 6. Procedures, Results, Exceptions
-- procedures are a list of statements that can be executed with arguments
create proc hello()
call printf("Hello, world\n");
-- in, out, and in/out parameters are possible
create proc swizzle(x integer, inout y integer, out z real not null)
set y := x + y; -- any computation you like
-- bizarre way to compute an id but this is just an illustration
set z := (select r from T1 where id = x if nothing or null -1);
-- procedures like "hello" have a void signature, they return nothing
-- as nothing can go wrong but those that use the database like "swizzle"
-- can return an error code if there is a problem.
-- will_fail will always return SQLITE_CONSTRAINT, the second insert
-- is said to "throw"
create proc will_fail()
insert into T1 values (1, "x", 1);
insert into T1 values (1, "x", 1); --> duplicate key
-- exceptions can be caught, here is an examples
create proc upsert_t1(
id_ integer primary key,
t_ text,
r_ real
begin try
insert into T1(id, t, r) values (id_, t_, r_);
end try;
begin catch
update T1 set t = t_, r = r_ where id = id_;
end catch;
-- shapes can be very useful in avoiding boilerplate code
-- the following is equivalent, more on shapes later
create proc upsert_t1(LIKE t1) -- my args are the same as the columns of T1
begin try
insert into T1 from arguments
end try;
begin catch
update T1 set t = t_, r = r_ where id = id_;
end catch;
-- you can (re)throw an error explicitly
-- if there is no current error you get SQLITE_ERROR
create proc upsert_wrapper(LIKE t1) -- my args are the same as the columns of T1
if r_ > 10 then throw end if;
call upsert_t1(from arguments);
-- procedures can also produce a result set
-- the compiler generates the code to create this result set
-- and helper functions to read rows out of it
create proc get_low_r(r_ real)
-- optionally insert some rows or do other things
select * from T1 where T1.r <= r_;
-- a procedure can choose between various results, they must be compatible
-- the last "select" to run controls the ultimate result
create proc get_hi_or_low(r_ real, hi_not_low bool not null)
if hi_not_ low then
select * from T1 where T1.r >= r_; -- economical easy query
select * from T1 where T1.r <= r_; -- economical easy query
end if;
-- using IF to create to nice selects above is a powerful thing.
-- SQLite has no IF, if we tried to create a shared query we get
-- something that does not use indices at all. As in the below.
-- The two-headed beast has its advantages.
select * from T1 where case hi_not_low then T1.r >= r_ else T1.r <= r_ end;
-- you can get the current return code to help you manage your catch things
-- this upsert is a bit better than the first
create proc upsert_t1(LIKE t1) -- my args are the same as the columns of T1
begin try
insert into T1 from arguments
end try;
begin catch;
if @rc == 19 /* SQLITE_CONSTRAINT */ then
update T1 set t = t_, r = r_ where id = id_;
throw; -- rethrow, something bad happened.
end if;
end catch;
-- By convention you can call a proc with an out parameter as its last arg
-- using function notation. The out variable is the return value
-- If the proc uses the database it could throw which causes the caller to throw
create proc fib(n integer not null, out result integer not null)
result := case n <= 2 then 1 else fib(n-1) + fib(n-2) end;
* 7. Statement Cursors
-- statement cursors let you iterate over a select result
-- here we introduce cursors loop and fetch
create proc count_t1(r_ real, out rows_ integer not null)
declare rows integer not null; -- starts at zero guaranteed
declare C cursor for select * from T1 where r < r_;
loop fetch C -- iterate until fetch returns no row
if C.r < 5 then
rows := rows + 1;
end if;
set rows_ := rows;
-- more elementary forms are possible
create proc peek_t1(r_ real, out rows_ integer not null)
/* rows_ is set to zero for sure! */
declare C cursor for select * from T1 where r < r_ limit 2;
open C; -- this is no-op, present because other systems have it
fetch C; -- fetch might find a row or not
if C then -- cursor name as bool indicates presence of a row
set rows_ = rows_ + (C.r < 5);
fetch C;
set rows_ = rows_ + (C and C.r < 5);
end if;
close C; -- cursors auto-closed at end of method but early close possible
-- the fetch form can also fetch directly into variables
fetch C into id_, t_, r_; --> loads named locals instead of, C.t, C.r
-- a procedure can be the source of a cursor
declare C cursor for call get_low_r(3.2); -- valid cursor source
-- "out" can be used to create a result set that is just one row
create proc one_t1(r_ real)
declare C cursor for select * from T1 where r < r_ limit 1;
fetch C;
out C; -- emits a row if we have one, no row is ok too, empty result set.
* 8. Value Cursors, Out, and Out Union
-- to consume a procedure that uses "out" you can declare a value cursor
-- by itself this does not imply use of the database, but often the source
-- of the cursor uses the database.
create proc consume_one_t1()
-- a cursor whose shape matches the one_t1 "out" statement
declare C cursor like one_t1;
-- load it from the call
fetch C from call one_t1(7);
if C.r > 10 then
-- use values as you see fit
call printf("woohoo");
end if;
-- you can do the above in one go with the compound form
declare C cursor fetch from call one_t1(7); -- same net code
-- value cursors can come from anywhere and can be a result
create proc use_t1_a_lot()
-- T1 is the same shape as one_t1, this will work, too
declare C cursor like T1;
fetch C from call one_t1(7); -- load it from the call
-- do something, then maybe load it again with different args
fetch C from call one_t1(12); -- load it again
-- do something, then maybe load it again with explicit args
fetch C using
1 id,
"foo" t,
8.2 r;
-- now return it
out C;
-- make a complex result set one row at a time
create proc out_union_example()
-- T1 is the same shape as one_t1, this will work, too
declare C cursor like T1;
-- load it from the call
fetch C from call one_t1(7);
-- note out UNION rather than just out, indicating potentially many rows
out union C;
-- load it again with different args
fetch C from call one_t1(12);
out union C;
-- do something, then maybe load it again with explicit args
fetch C using
1 id,
"foo" t,
8.2 r;
out union C;
-- we have generated a 3 row result set
-- consume the above
create proc consume_result()
declare C cursor for call out_union_example();
loop fetch C
-- use builtin cql_cursor_format to make the cursor into a string
call printf("%s\n", cql_cursor_format(C)); --> prints every column and value
* 9. Named Types and Enumerations
-- create a simple named types
declare my_type type integer not null; -- make an alias for integer not null
declare i my_type; -- use it, "i" is an integer
-- mixing in type kinds is helpful
declare distance type real<meters>; -- e.g., distances to be measured in meters
declare time type real<seconds>; -- e.g., time to be measured in seconds
declare job_id type long<job_id>;
declare person_id type long<person_id>;
-- with the above done
-- * vars/cols of type "distance" are incompatible with those of type "time"
-- * vars/cols of types job_id are incompatible with person_id
-- this is true even though the underlying type is the same for both!
-- enums can have any numeric type as their base type
declare enum implement integer (
pencil, -- values start at 1 unless you use = to choose something
pen, -- the next enum gets previous + 1 as its value (2)
brush = 7 -- with = expression you get the indicated value
-- the above also implicitly does this
declare implement type integer<implement> not null;
-- using the enum, simply use dot notation
declare impl implement;
set impl := implement.pen; -- value "2"
-- if you want this enum to be owned by the current compiland you can
-- emit it into the .h file we are going to generate.
-- do not put this in an include file, you want it to go to one place
@emit_enums implement;
* 10. Shapes and Their Uses
-- shapes first appeared to help define value cursors like so:
-- a table or view name defines a shape
declare C cursor like T1;
-- the result of a proc defines a shape
declare D cursor like one_t1;
-- a dummy select statement defines a shape (the select does not run)
-- this one is the same as (x integer not null, y text not null)
declare E cursor like select 1 x, "2" y;
-- another cursor defines a shape
declare F cursor like C;
-- the arguments of a procedure define a shape
-- create proc count_t1(r_ real, out rows_ integer not null) ...
-- the shape will be:
-- (r_ real, rows_ integer not null)
declare G cursor like count_t1 arguments;
-- a loaded cursor can be used to make a call
call count_t1(from G); -- the args become G.r_, G.rows_
-- a shape can be used to define a functions args, or some of the args
-- p will have args id_, t_, and r_ with types matching table T1
-- note that an _ was added
create proc p(like T1)
-- do something
-- the arguments of the current procedure are a synthetic shape
-- called "arguments" and can used where other shapes can appeared
-- for instance you can have q shim to p using this form:
create proc q(like T1, print bool not null)
-- maybe pre-process, silly example
set id_ := id_ + 1;
call p(from arguments);
-- maybe post-process, silly example
set r_ := r_ - 1;
if print then
-- convert args to cursor
declare C like q arguments;
fetch C from arguments;
call printf("%s\n", cql_cursor_format(C)); --> prints every column and value
end if;
-- insert a row based on the args
insert into T1 from arguments;
-- you an use a given shape more than once if you name them
-- more exciting if T1 was like a "person" or something
create proc r(a like T1, b like T1)
call p(from a);
call p(from b);
-- you can refer to, etc.
declare C like a;
fetch C from a;
call printf("%s\n", cql_cursor_format(C));
fetch C from b;
call printf("%s\n", cql_cursor_format(C));
-- shapes can be subsetted for instance
-- here not just the arguments that match C are copied, there could be more
fetch C from arguments(like C);
-- use the D shape to load C, defaults for other arguments
fetch C(like D) from D;
-- use the D shape to load C, dummy values for the others
-- dummy seed here means use "11" for any numerics
-- and use "col_name_11" for any strings/blobs
fetch C(like D) from D @dummy_seed(11);
-- use the Z shape to control which fields are copied
-- use the dummy value even if the field is nullable and null woud have be ok
fetch C(like Z) from D(like Z) @dummy_seed(11) @dummy_nullables;
-- same works for insert statements
insert into T1(like Z) from D(like Z) @dummy_seed(11) @dummy_nullables;
-- you can make a helper to create test args that are mostly constant
-- or computable
create get_foo_args(X like some_shape, seed_ integer not null)
declare C cursor like foo arguments;
-- any way of loading C could work this is one
fetch C(like X) from X @dummy_seed(seed_);
out C;
-- then get the full arg set and call it
-- some_shape is the part of the args that needs to manually vary in each
-- test iteration, the rest will be dummy values. There could be zillions.
-- some_shape is going to get the values 1, 2, 3 and 100 will be the seed
declare foo_args cursor fetch from call get_foo_args(1,2,3, 100);
call foo(from foo_args);
-- this kind of thing is a pain
insert into foo(a, b, c, d, e, f, g)
values(1, 2, 3, null, null, 5, null);
-- instead write this form:
insert into foo USING
1 a, 2 b, 3 c, null d, null e, 5 f, null g;
-- the FETCH statement can also be "fetch using"
declare C cursor like foo;
fetch C USING
1 a, 2 b, 3 c, null d, null e, 5 f, null g;

If you've read this far you know more than most now. :)

Last updated on by Raoul Foaleng