Appendix 10: CQL Working Example

This is a working example that shows all of the basic DML statements and the call patterns to access them. The code also includes the various helpers you can use to convert C types to CQL types.

todo.sql

-- This is a simple schema for keep track of tasks and whether they are done
-- this serves to both declare the table and create the schema
create proc todo_create_tables()
begin
create table if not exists tasks(
description text not null,
done bool default false not null
);
end;
-- adds a new not-done task
create proc todo_add(task TEXT NOT null)
begin
insert into tasks values(task, false);
end;
-- gets the tasks in inserted order
create proc todo_tasks()
begin
select rowid, description, done from tasks order by rowid;
end;
-- updates a given task by rowid
create proc todo_setdone_(rowid_ integer not null, done_ bool not null)
begin
update tasks set done = done_ where rowid == rowid_;
end;
-- deletes a given task by rowid
create proc todo_delete(rowid_ integer not null)
begin
delete from tasks where rowid == rowid_;
end;

main.c

#include <stdlib.h>
#include <sqlite3.h>
#include "todo.h"
int main(int argc, char **argv)
{
/* Note: not exactly world class error handling but that isn't the point */
// create a db
sqlite3 *db;
int rc = sqlite3_open(":memory:", &db);
if (rc != SQLITE_OK) {
exit(1);
}
// make schema if needed (it always will be here because memory databases begin empty
rc = todo_create_tables(db);
if (rc != SQLITE_OK) {
exit(2);
}
// add some tasks
const char * const default_tasks[] = {
"Buy milk",
"Walk dog",
"Write code"
};
for (int i = 0; i < 3; i++) {
// note we make a string reference from a c string here
cql_string_ref dtask = cql_string_ref_new(default_tasks[i]);
rc = todo_add(db, dtask);
cql_string_release(dtask); // and then dispose of the reference
if (rc != SQLITE_OK) {
exit(3);
}
}
// mark a task as done
rc = todo_setdone_(db, 1, true);
if (rc != SQLITE_OK) {
exit(4);
}
// delete a row in the middle, rowid = 2
rc = todo_delete(db, 2);
if (rc != SQLITE_OK) {
exit(5);
}
// select out some results
todo_tasks_result_set_ref result_set;
rc = todo_tasks_fetch_results(db, &result_set);
if (rc != SQLITE_OK) {
printf("error: %d\n", rc);
exit(2);
}
// get result count
cql_int32 result_count = todo_tasks_result_count(result_set);
// loop to print
for (cql_int32 row = 0; row < result_count; row++) {
// note "get" semantics mean that a ref count is not added
// if you want to keep the string you must "retain" it
cql_string_ref text = todo_tasks_get_description(result_set, row);
cql_bool done = todo_tasks_get_done(result_set, row);
cql_int32 rowid = todo_tasks_get_rowid(result_set, row);
// convert to c string format
cql_alloc_cstr(ctext, text);
printf("%d: rowid:%d %s (%s)\n",
row, rowid, ctext, done ? "done" : "not done");
cql_free_cstr(ctext, text);
}
// done with results, free the lot
cql_result_set_release(result_set);
// and close the database
sqlite3_close(db);
}

Build Steps

# ${cgsql} refers to the root of the CG/SQL repo
% cql --in todo.sql --cg todo.h todo.c
% cc -o todo -I${cqsql}/sources main.c todo.c ${cgsql}/sources/cqlrt.c -lsqlite3

Results

Note that rowid 2 has been deleted, the leading number is the index in the result set. The rowid is of course the database rowid.

% ./todo
0: rowid:1 Buy milk (done)
1: rowid:3 Write code (not done)
Last updated on by Rico Mariani