The sqlite3cc library is a C++ wrapper around the excellent SQLite 3 library. The SQLite Library has a C-style interface, and sqlite3cc is intended to make the use of SQLite more comfortable in a C++ programming environment.

The goals of sqlite3cc are:

  • to be elegantly written, following principles of good C++ design and those laid out by the boost project
  • to be fast, adding an insignificant execution overhead
  • where feasible, to keep the API similar (if only in name) to the SQLite3 library

Getting sqlite3cc

The sqlite3cc library is free/libre open-source software, available under the terms of the GNU Lesser General Public License version 3 or later.

Download

You can download the latest version here:

Download sqlite3ccversion 0.1.1234K

changes | old versions

Installation

To install, run the usual three commands:

$ ./configure
$ make
$ make install

You may need to be root to run the final command.

Documentation

A complete reference (courtesy of doxygen) will be available shortly. In the meantime, the rest of this documentation is an informal (and incomplete) guide only.

Note that sqlite3cc makes pretty regular use of RAII.

Connections

The sqlite::connection class essentially wraps sqlite3’s sqlite3 struct.

// connecting to, and work on, a database
sqlite::connection conn( "example.db" );
conn.busy_timeout( 1000 );
conn.exec( "PRAGMA user_version = 1" );

Commands and Queries

The sqlite::query and sqlite::command classes represent SQL statements that, respectively, do and do not return results. They make use of RAII to clean-up their handles and, as such, neither of these classes is copyable (this may change in the next release).

Statements can be prepared and executed in a few different ways, but sqlite3’s API is also preserved.

// the sqlite3 way
sqlite::command c1( conn,
    "CREATE TABLE pets ( name TEXT PRIMARY KEY, type TEXT, age INTEGER )" );
c1.step();

In addition to simply wrapping sqlite3’s structs and providing methods, sqlite3cc also tries to provide a more c++ way of doing things.

// another way
sqlite::command c2 = conn.make_command( "INSERT INTO pets VALUES( ?, :foo, :88 )" );
c2.bind( 1, "billy" );
c2.bind( ":foo", "cat" );
c2.bind( 88, 12 );
c2.exec();

// or, for brevity
*conn.make_command( "INSERT INTO pets VALUES( ?, ?, ? )" )
    << "tessa" << "dog" << 16 << sqlite::exec;

There are some things that you can only do with commands, or only with queries.

// only possible with commands
assert( c2.changes() == 1 );

sqlite::query q1( conn, "SELECT * FROM pets" );

// only possible with queries
assert( q1.column_count() == 3 );
assert( q1.column_name( 2 ) == "age" );

Results

For queries, results are returned in the form of sqlite::row objects. There are, again, a number of ways to retrieve these.

std::string name;
std::string type;
unsigned int age;

// the sqlite3 way
sqlite::row r1 = q1.step();
r1.column( 0, name );
r1.column( 1, type );
r1.column( 2, age );

// another way
sqlite::row r2 = q1.step();
name = r2.column< std::string >( 0 );
type = r2.column< std::string >( 1 );
age = r2.column< unsigned int >( 2 );

// or, for brevity
q1.step() >> name >> type >> age;
q1.step() >> name >> sqlite::null >> age >> sqlite::set_index( 1 ) >> type;

In addition, sqlite::query::iterator can also be used to fetch results.

for( sqlite::query::iterator i = q1.begin(); i != q1.end(); i++ )
    if( i->row_number() == 3 )
        std::cout << "name: " << i->column< std::string >( 0 ) << std::endl;

When there are no more results, the row object that is returned by step() evaluates to false when converted to a bool.

// make sure we have no more results
assert( !q1.step() );

Transactions

There are four types of transaction in sqlite3cc. They are:

  • sqlite::deferred_transaction
  • sqlite::immediate_transaction
  • sqlite::exclusive_transaction
  • sqlite::recursive_transaction

The first three are parallels to those defined by SQLite. The final type of transaction uses SQLite’s SAVEPOINT and RELEASE mechanism to allow for nesting transactions.

The use of transactions is facilitated by a scope guard/sentinel class. Note the use of curly-brackets to introduce a scope for the transaction.

{
    // deferred_transaction is used by default
    sqlite::transaction_guard< > t( conn );
    conn.exec( "DELETE from pets" );

    // commit the transaction (or it will rollback)
    t.commit();
}

Error Handling

As a rule of thumb, where the sqlite3 API has been preserved, sqlite3 error codes are returned. Where the interface has been extended for use with c++, exceptions are thrown. But, as should be the case, exceptions are not thrown for expectable (unexceptional) errors.

The exception thrown is an instance of the sqlite::sqlite_error class.

try {
    sqlite::row q1.step()
}
catch( sqlite::sqlite_error &e ) {
    std::cerr << "error: " << e.what() << ", code " << e.get_code() << std::endl;
}

SQLite API

The sqlite3cc library is based on the SQLite C interface, so you will also need to be familiar with that. You can find documentation on the SQLite API on their website.

Development

You can always drop me an email. Feedback and suggestions are always welcome.

Bugs

Please report bugs at the project’s development site:

dev.ed.am/sqlite3cc

Repository

You can obtain the latest development code from the bazaar repository at:

http://bzr.ed.am/sqlite3cc

But you will need to run the following to set up the project’s configure script:

$ autoreconf -i

Comments

  • J. Landercy

    I found your interface very usefull. I just compiled into a dynamic library, it works like a charm.

    Notice that I linked it to a test program and I executed the present example. In the current version that I downloaded (2013/11) connection class does not exhibit a command method. Therefore the following line will not compile:

    sqlite::command c2 = conn.command( “INSERT INTO pets VALUES( ?, :foo, :88 )” );

    It might be an relief form a previous version.

    Anyway, thank you very much for sharing your work. I really appreciated it.

    • Thanks! I’ve fixed that. And I’m about to do a 0.1.1 release with a few minor improvements…

  • This wrapper is well-designed and helps a lot to integrate SQLite fast. I’m using it in my current project and I’m happy to have it: https://github.com/monsdar/Brofiler

Leave a Reply