Skip to main content

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 keeping track of tasks and whether or not they have been completed

-- 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 (in memory databases always 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(6);
}

// 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)