Examples

This is an archive of interesting examples that can be helpful.

Information for contributors is at the end of the article.

The Basic Hello World Sample

• This is the Hello World sample in the docs
• It goes over the bootstrap code you need as well
• You can see a similar thing in the sources/repl directory, look at go.sql, go.sh, and go_query_plan.sh

A Parent and Child Result Set Example

• See Parent Child Result Set for the source code
• The lua output for this is very interesting and fairly readable, this gives you a lot of insight as to how this works under the hood. The C code is isomorphic.
• In this example we create an overall result set (which we could return) and then iterate it
• This presumes that you want the overall result set materialized

A Parent and Child Setup with No Result Set

• See Parent Child With No Result Set for the source code
• Result sets are useful if you want to return the results of entire query as a unit and use them durably, maybe even after the database contents have changed
• If you don't need durable results, you can simply iterate results from a cursor, this uses much less memory

Using Nested Result Sets Like Records

• See Using nested result sets to return records in a column for the source code
• Generates a single row result with either a SELECT or an OUT statement to give you a flexible record
• You can emit this record and then access it as usual
• Note that the record might be empty (no row is legal!)

Example from Appendix 10 of The Guide

• Appendex 10 contains a complete working example
• This shows interop between C and CQL as well as assorted SQL operations and rowset access

The Lua Demo and Test Suite

• A simple demo for Lua codegen along with various tests is in the sources/lua_demo directory
• There are various samples in that directory the demo script runs them all
• It also executes the run_test.sql test cases
• Naturally, you need to install Lua and the Lua SQLite library on your system for this to work
• demo.sh will run it

The Java Demo and Test Suite

• A simple demo for Java codegen and JNI bindings to result sets can be found in the sources/java_demo directory
• Note that an open opportunity here is to generate the java and JNI to call the procedure to get results sets automatically; presently the java generation only creates wrappers to help you read data out of results sets, you have to do the JNI to get those result sets yourself
• The example code shows you how to do this in one case, but it isn't generalized (it could be)
• demo.sh will run it

The CQLRT_CF (Core Foundation Runtime) Demo and Test Suite

• A simple demo that uses cqlrt_cf the CoreFoundation version of the runtime is in the sources/cqlrt_cf directory
• This code really only works on MacOS as it requires CoreFoundation
• I have made attempts to use CFLite open source as an alternative to get the code to run on Linux but that went badly
• If you want to see how to do ObjectiveC wrappers with --rt objc and a little glue, this is the source code for you
• make.sh will build and run it

The CQL Tester Application

• The code that verifies the AST and code-gen output of the compiler is itself written in CQL and is interesting
• Source can be found in sources/tester
• Build the tester using the regen.sh command
• This code runs on every execution of test.sh and cov.sh as the primary verification tool
• It includes a main and database helpers in dbhelp.sql
• Note that it is not auto-updated so that we do not try to use a potentially broken CQL compiler to create its own tester
• Many rowset access concepts are demonstrated by this bit of code

Complex Shared Fragment and-Rowsets with Many Options

• This rather more complicated demo uses a variety of techniques to build a complex result set
• It uses conditional fragments in two different forms, normal fragments, as well as parent/child result sets
• It tries to share some of the core helpers as a good pattern example for query assembly from fragments

CQL Output Verifiers

• These are the tool that test.sh uses to test the compiler, they are written in CQL!
• General Output Verifier
• Line Directive Checker
• Both of these tools demonstrate some interesting database techniques as well as control flow and interfacing with new native interfaces

Contributors

It's helpful to create a complete example that you could drop into the playground examples directory and run with play.sh. A command like play.sh run c examples/your_example.sql should do the trick.

Error Tracing

When developing samples, more error tracing might be helpful. Use the following at the start of your sample:

@echo c, '
#undef cql_error_trace
#define cql_error_trace() \
   fprintf(stderr, "SQL Failure %d %s: %s %d\n", _rc_, sqlite3_errmsg(_db_), __FILE__, __LINE__);
';

If you're targeting Lua (e.g., ./play.sh run lua examples/your_example.sql) then tracing is present by default.