About SQLite

The Cardinal 825 comes fully equipped with an open source relational database known as SQLite. SQLite is a software library that implements a ​self-contained, ​serverless, ​zero-configuration, ​transactional SQL database engine. SQLite is the ​most widely deployed SQL database engine in the world. The source code for SQLite is in the ​public domain. SQLite is used by companies such as Adobe, Bloomberg, mozilla, symbian, Skype, and Google (Android OS).

SQLite Library (libsql)

Cardinal supplies a library that allows usage of the SQLite database as well as a wrapper class to make generating queries and using the database as easy as possible. You can get the latest library from this wiki or by checking out the latest source from the repository.

Using libsql

Once you have the latest source you will need to include it in your project by modifying your project settings. Follow the steps listed below to get up and running.

1. Right-click on your project and choose properties from the drop-down menu.

2. Choose C/C++ Build and then Settings from the next screen. Click on Directories under Sourcery G++ C++ Compiler.

3. Click on the add button and choose Workspace and the libsql project from the popup.

4. Click OK, OK and then observed the Include paths. It should have ${workspace_loc/libsql}.

5. Next, click on Libraries under Sourcery G++ C++ Linker.

6. In the Libraries (-l) pane choose the add button and enter pthread. Click the add button again and enter sql. If you're using the lib825 (and you probably are) that pane should look like the screenshot below.

7. In the Library search path (-L) pane click the green add and choose Workspace. Pick libsql/Debug (or Release for release build) from the dropdown. If you do not have Debug you can either build the debug release of the libsql or manually enter /Debug after the libsql path. The final result should be identical to the image shown below.

8. The Library search path (-L) should be identical to the image below (Release may say Debug if you're building a Debug build).

9. Click OK to save your settings.

SqlBuilder Class

A query builder class has been included with the SQLite library to facilitate generation of queries. Using the class is recommended to eliminate query bugs (and debugging time). Below you can find examples of using the class.

// LibSQL Headers
#include "SqlBuilder.h"

int main(int ac, char **av) {
    SqlBuilder Sql;
        
    // Example of a simple query
    Sql.Select("name");
    Sql.From("customers");
    Sql.Print();
    Sql.Reset();

    // Example of using sorting
    Sql.Select("id");
    Sql.From("trucks");
    Sql.Sort(SQL_ORDER_ASC,"name");
    Sql.Print();
    Sql.Reset();
        
    // Example of using multiple arguments with a where clause
    Sql.Select("name","value");
    Sql.From("configuration");
    Sql.Where("name LIKE 'Thomas'", "id > 0");
    Sql.Print();
    Sql.Reset();
        
    // Most complicated example, multiple arguments, multiple sorts, and multiple tables with aliases
    // This also does a simple inner join and then sorts by customer name, truck name, in descending order.
    Sql.Select("c.id,t.id,c.name,t.name");
    Sql.From("trucks", "t"); // trucks table aliased as t, so the id field would be t.id
    Sql.From("customers", "c");
    Sql.Where("c.id > 0", "t.id > 0", "t.name LIKE 'temp'", "c.name LIKE 'kyle'");
    Sql.Sort(SQL_ORDER_DESC,"c.name","t.name");
    Sql.Print();
    Sql.Reset();

    // Using values to bind to the where clause.
    int nTmp = 5;
    Sql.Select("test");
    Sql.From("trucks");
    Sql.Where("id =", nTmp);
    Sql.Print();
        
    return 0;
}

Output:

# ./sqltest
SqlBuilder :: Query Generated = [SELECT name FROM customers]
SqlBuilder :: Query Generated = [SELECT id FROM trucks ORDER BY name ASC]
SqlBuilder :: Query Generated = [SELECT name, value FROM configuration WHERE name LIKE 'Thomas' id > 0]
SqlBuilder :: Query Generated = [SELECT c.id,t.id,c.name,t.name FROM trucks AS t, customers AS c WHERE c.id > 0 AND t.id > 0 AND t.name LIKE 'temp' AND c.name LIKE 'kyle' ORDER BY c.name, t.name DESC]
SqlBuilder :: Query Generated = [SELECT test FROM trucks WHERE id = 5]

SQLite++ Class

A C++ wrapper has been provided that sits on top of the SQLite C functions and aims to make implementation easier. See below for examples.

database db;

// Headers
#include "sq3.hpp"

int main(int ac,char **av) {
    database db;
        
    // Executing a simple query
    db.execute("SELECT * FROM trucks");
        
    // Iterating through rows of a result set
    statement st(db, "SELECT * FROM trucks");
    cursor cur(st.get_cursor());
    
    while(cur.step() == SQLITE_ROW) {
        string name;
        float accum;
        
        // Do stuff with data
        cur.get(0,name);
        cur.get(1,accum);
    }
    
    // Doing multiple inserts using bind and transactions for speed
    bool bError = false;
    
    transaction tr(db);
    st.prepare("INSERT INTO trucks (name,accum) VALUES (?,?);");
    
    for(int i = 0; i < 10; i++) {
        st.bind(1,"truck!");
        st.bind(2,i*1000.0);
        
        if (st.execute() == SQLITE_ERROR) {
                bError = true;
        }
    }
    
    if (bError) {
        tr.rollback();
    } else {
        tr.commit();
    }
}

Using SQLite++ with SqlBuilder

You can easily combine SQLite++ and SqlBuilder to make database access easy.

// LibSQL Headers
#include "sq3.hpp"
#include "SqlBuilder.h"

int main(int ac, char **av) {
    database db;
    SqlBuilder sql;

    // Identical to the select above
    sql.Select("*");
    sql.From("trucks");
    db.execute(sql.GetQuery());

    // Grab a specific row instead of all results
    // Gets the name of truck id 10
    sql.Select("name");
    sql.From("trucks");
    sql.Where("id =",10);
    db.execute(sql.GetQuery());
}