SQLite Embedded Database

SQLite:

SQLite is a software library that implements a self-contained, serverless, zero-configuration, transactional SQL relational database management system (RDBMS). This tutorial covers the command line interface and the SQLite C/C++ database programming API.

SQLite comes with Linux: (example of package list on RHEL)

sqlite
sqlite-devel
python-sqlite

SQLite Command Line Interface:

Basic SQLite: Create database, create a database table and insert a database record.

[prompt]$ sqlite3 /tmp/bedrock.db
sqlite> .help
sqlite> CREATE TABLE employee (Name varchar(20),Dept varchar(20),jobTitle varchar(20));
sqlite> .schema employee
CREATE TABLE employee (Name varchar(20),Dept varchar(20),jobTitle varchar(20));
sqlite> INSERT INTO employee VALUES ('Fred Flinstone','Quarry Worker','Rock Digger');
sqlite> .exit

Populate a database from a SQL command file:

File: employeeFile.sql

INSERT INTO employee VALUES ('Wilma Flinstone','Finance','Analyst');
INSERT into employee values ('Barney Rubble','Sales','Neighbor');
INSERT INTO employee VALUES ('Betty Rubble','IT','Neighbor');

Load SQL file, execute a select and delete a record:

[prompt]$ sqlite3 /tmp/bedrock.db
sqlite> .tables
employee
sqlite> .read employeeFile.sql
sqlite> SELECT Name FROM employee WHERE dept='Sales';
Barney Rubble
sqlite> SELECT * FROM employee;
Fred Flinstone|Quarry Worker|Rock Digger
Wilma Flinstone|Finance|Analyst
Barney Rubble|Sales|Neighbor
Betty Rubble|IT|Neighbor
sqlite> DELETE FROM employee WHERE dept='Sales';
sqlite> .exit

Generate an ASCII file dump of the database:

[prompt]$ sqlite3 /tmp/bedrock.db
sqlite> .show
     echo: off
  explain: off
  headers: off
     mode: list
nullvalue: ""
   output: stdout
separator: "|"
    width:
sqlite> .output /tmp/bedrock.sql
sqlite> .dump
sqlite> .exit

This generates the following file: /tmp/bedrock.sql

BEGIN TRANSACTION;
CREATE TABLE employee (Name varchar(20),Dept varchar(20),jobTitle varchar(20));
INSERT INTO "employee" VALUES('Fred Flinstone','Quarry Worker','Rock Digger');
INSERT INTO "employee" VALUES('Wilma Flinstone','Finance','Analyst');
INSERT INTO "employee" VALUES('Barney Rubble','Sales','Neighbor');
INSERT INTO "employee" VALUES('Betty Rubble','IT','Neighbor');
COMMIT;

Upgrading the SQLite database:

sqlite test.db .dump | sqlite3 testV3.db

SQLite Information:

C/C++ SQLite Programming API:

Accessing SQLite with C/C++ API:

Function	Description
sqlite3_open()	Opens specified database file. If the database file does not already exist, it is created.
sqlite3_close()	Closes a previously opened database file.
sqlite3_prepare_v2()	Prepares a SQL statement ready for execution.
sqlite3_step()	Executes a SQL statement previously prepared by the sqlite3_prepare_v2() function.
sqlite3_column_<type>()	Returns a data field from the results of a SQL retrieval operation where <type> is replaced by the data type of the data to be extracted (text, blob, bytes, int, int16 etc).
sqlite3_finalize()	Deletes a previously prepared SQL statement from memory.
sqlite3_exec()	Combines the functionality of sqlite3_prepare_v2(), sqlite3_step() and sqlite3_finalize() into a single function call.

Database access example:

File: accessTableEmployee.cpp

#include <iostream>
#include <sqlite3.h>
#include <stdlib.h>

// g++ AccessTableEmployee.cpp -lsqlite3

using namespace std;

static int callback(void *NotUsed, int argc, char **argv, char **azColName)
{
  int i;
  cout << "Number of args= " << argc << endl;

  for(i=0; i<argc; i++)
  {
     cout << azColName[i] << " = " << (argv[i] ? argv[i] : "NULL") << endl;
  }
  cout << endl;
  return 0;
}

int main(int argc, char **argv)
{
  sqlite3 *db;       // Declare pointer to sqlite database structure
  char *zErrMsg = 0;

  // Open Database 

  int rc = sqlite3_open("/tmp/bedrock.db", &db);
  if( rc )
  {
    cerr << "Can't open database: " << sqlite3_errmsg(db) << endl;
    sqlite3_close(db);
    exit(1);
  }

  // Insert data into database

  const char *zSql = "INSERT INTO employee(Name, Dept, jobTitle) VALUES('Barney Rubble','Sales','Neighbor')";

  sqlite3_stmt *ppStmt;
  const char **pzTail;

  if( sqlite3_prepare_v2(db, zSql, strlen(zSql)+1, &ppStmt, pzTail) != SQLITE_OK )
  {
      cerr << "db error: " << sqlite3_errmsg(db) << endl;
  }

  if(ppStmt)
  {
      sqlite3_step(ppStmt);
      sqlite3_finalize(ppStmt);
      sqlite3_exec(db, "COMMIT", NULL, NULL, NULL);
  }
  else
  {
      cerr << "Error: ppStmt is NULL" << endl;
  }

  // Select from database 

  rc = sqlite3_exec(db,"select * from employee", callback, 0, &zErrMsg);
  if( rc!=SQLITE_OK )
  {
      cerr << "SQL error: " << zErrMsg << endl;
      sqlite3_free(zErrMsg);
  }

  // Close

  sqlite3_close(db);
  return 0;
}

Compile: g++ AccessTableEmployee.cpp -lsqlite3

Run: a.out

Number of args= 3
Name = Wilma Flinstone
Dept = Finance
jobTitle = Analyst

Number of args= 3
Name = Betty Rubble
Dept = IT
jobTitle = Neighbor

Number of args= 3
Name = Barney Rubble
Dept = Sales
jobTitle = Neighbor

Creating and Accessing SQLite BLOBS with C/C++:

Blobs: (binary objects eg images or C data structures)

Create a database table "btest" with one BLOB collumn called "MyData":

[prompt]$ sqlite3 /tmp/bedrock.db
sqlite> create table btest(ID INTEGER, MyData BLOB);

Insert a blob into the database. Select the blob using the sqlite3_exec() API call.

File: sqliteBlobExampleExec.cpp

#include <iostream>
#include <stdlib.h>
#include <string.h>
#include <sqlite3.h>
#include <time.h>

using namespace std;

// Store gmtime data structure as a blob
// See man pages for information on gmtime data structure: man gmtime

static int callback(void *NotUsed, int argc, char **argv, char **azColName)
{
  struct tm blob;

  for(int i=0; i<argc; i++)
  {
    if(!strcmp(azColName[i],"MyData"))
    {  // Handle Blob data
       memcpy(&blob, argv[i], sizeof(struct tm));
       cout << "Year retrieved from blob: " << blob.tm_year+1900 << endl;
    }
    else
    {  // All other database collumns
       cout << azColName[i] << " = " << (argv[i] ? argv[i] : "NULL") << endl;
    }
  }
  cout << endl;
  return 0;
}


int main(int argc, char **argv)
{
  sqlite3 *db;
  char *zErrMsg = 0;

  time_t tt = 0;
  time_t now = time(&tt);          // seconds since the Epoch
  struct tm *blob = gmtime(&now);  // Create the blob to store in the database

  cout << "Year stored: " << blob->tm_year+1900 << endl;
  cout << endl;

  int rc = sqlite3_open("/tmp/bedrock.db", &db);
  if( rc )
  {
    cerr << "Can't open database: " << sqlite3_errmsg(db) << endl;
    exit(1);
  }

  // Insert blob data into database

  const char   *zSql = "INSERT INTO btest(ID, MyData) VALUES('1',?)";
  sqlite3_stmt *ppStmt;
  const char  **pzTail;

  if( sqlite3_prepare_v2(db, zSql, strlen(zSql)+1, &ppStmt, pzTail) != SQLITE_OK )
  {
      cerr << "db error: " << sqlite3_errmsg(db) << endl;
      sqlite3_close(db);
      exit(1);
  }

  if(ppStmt)
  {
      // For Blob collumn bind 1
      sqlite3_bind_blob(ppStmt, 1, blob, sizeof(struct tm), SQLITE_TRANSIENT);
      sqlite3_step(ppStmt);
      sqlite3_finalize(ppStmt);
      sqlite3_exec(db, "COMMIT", NULL, NULL, NULL);
  }
  else
  {
      cerr << "Error: ppStmt is NULL" << endl;
      sqlite3_close(db);
      exit(1);
  }

  // Select rows from database

  // Note: sqlite3_exec() does sqlite3_prepare_v2(), sqlite3_step() and sqlite3_finalize() into a single function call

  rc = sqlite3_exec(db,"SELECT * FROM btest", callback, 0, &zErrMsg);
  if( rc!=SQLITE_OK )
  {
      cerr << "SQL error: " << zErrMsg << endl;
      sqlite3_free(zErrMsg);
      sqlite3_close(db);
      exit(1);
  }

  sqlite3_exec(db, "END", NULL, NULL, NULL);

  // Close database

  sqlite3_close(db);
  return 0;
}

Compile: g++ sqliteBlobExampleExec.cpp -lsqlite3
Run: ./a.out
Result:

Year stored: 2010

ID = 1
Year retrieved from blob: 2010

Insert a blob into the database. Select the blob using the sqlite3_step() API call.

File: sqliteBlobExample.cpp

#include <iostream>
#include <stdlib.h>
#include <string.h>
#include <sqlite3.h>
#include <time.h>

using namespace std;

// Store gmtime data structure as a blob
// See man pages for information on gmtime data structure: man gmtime

int main(int argc, char **argv)
{
  sqlite3 *db;
  sqlite3_stmt *ppStmt;
  char *zErrMsg = 0;
  const char   *zSql = "INSERT INTO btest(ID, MyData) VALUES('1',?)";

  time_t tt = 0;
  time_t now = time(&tt);          // seconds since the Epoch
  struct tm *blob = gmtime(&now);  // Create the blob to store in the database

  cout << "Year stored: " << blob->tm_year+1900 << endl;
  cout << endl;

  int rc = sqlite3_open("/tmp/bedrock.db", &db);
  if( rc )
  {
    cerr << "Can't open database: " << sqlite3_errmsg(db) << endl;
    exit(1);
  }

  // Insert blob data into database

  if( sqlite3_prepare_v2(db, zSql, -1, &ppStmt, NULL) != SQLITE_OK )
  {
      cerr << "db error: " << sqlite3_errmsg(db) << endl;
      sqlite3_close(db);
      exit(1);
  }

  if(ppStmt)
  {
      // For Blob collumn bind 1
      sqlite3_bind_blob(ppStmt, 1, blob, sizeof(struct tm), SQLITE_TRANSIENT);
      sqlite3_step(ppStmt);
      sqlite3_finalize(ppStmt);
      sqlite3_exec(db, "COMMIT", NULL, NULL, NULL);
  }
  else
  {
      cerr << "Error: ppStmt is NULL" << endl;
      sqlite3_close(db);
      exit(1);
  }

  // Select rows from database

  const char *zSqlSelect = "select * from btest";
  if( sqlite3_prepare_v2(db, zSqlSelect, -1, &ppStmt, NULL) != SQLITE_OK )
  {
      cerr << "db error: " << sqlite3_errmsg(db) << endl;
      sqlite3_close(db);
      exit(1);
  }

  // Name of DB table
  cout << sqlite3_column_table_name(ppStmt,0) << endl;

  // For each row returned
  while (sqlite3_step(ppStmt) == SQLITE_ROW)
  {
      // For each collumn
      for(int jj=0; jj < sqlite3_column_count(ppStmt); jj++)
      {
          // Print collumn name
          cout << sqlite3_column_name(ppStmt,jj) << " = ";

          // Print collumn data
          switch(sqlite3_column_type(ppStmt, jj))
          {
             case SQLITE_INTEGER: cout << sqlite3_column_int(ppStmt, jj) << endl;
                  break;
             case SQLITE_FLOAT:   cout << sqlite3_column_double(ppStmt, jj) << endl;
                  break;
             case SQLITE_TEXT:    cout << sqlite3_column_text(ppStmt, jj) << endl;
                  break;
             case SQLITE_BLOB:    cout << "BLOB " << endl;
                  cout << "Size of blob: " << sqlite3_column_bytes(ppStmt, jj) << endl;
                  struct tm *blobRetreived;
                  blobRetreived = (struct tm *) sqlite3_column_blob(ppStmt, jj);
                  cout << "Year retrieved from blob: " << blobRetreived->tm_year+1900 << endl;
                  break;
             case SQLITE_NULL:    cout << "NULL " << endl;
                  break;
             default: cout << "default " << endl;
                  break;
          }
      }
  }
  sqlite3_finalize(ppStmt);

  sqlite3_exec(db, "END", NULL, NULL, NULL);

  // Close database

  sqlite3_close(db);
  return 0;
}

Compile: g++ sqliteBlobExample.cpp -lsqlite3
Run: ./a.out
Result:

Year stored: 2010

btest
ID = 1
MyData = BLOB
Size of blob: 44
Year retrieved from blob: 2010

Notes:

Calls to sqlite3_step() and sqlite3_finalize() clear out allocated memory returned by sqlite3_column_<type>().
Thus do not free memory returned by sqlite3_column_blob() or for any of the calls to sqlite3_column_<type>().

Links:

SQLite Database Admin Tools:

SQLiteman - Qt, Cross platform
SQLiteGui - SQLite browser GUI (C++/Qt)
sqlite-manager - UIL Firefox extension
SQLite Studio - cross platform, multi-language support, Tcl/Tk
TkSQLite - Tcl/Tk
SQL Db Manager - developed in Delphi-7
wxSQLite - C+/wxWidgets (fr)
SQLite Db Browser
Web Admin Tools:
- SQLite Manager - PHP4/5
- Bazdig - PHP
- SQLiteWeb.sh - shell script CGI [cache]
- SQLite web

SQLite Software Development Links:

sqlite-sdbc-driver - Open Office
DB_Sqlite_Tools - PHP Pear package
SQLiteDB - PHP class
Perl Parse-Dia-SQL - Perl CPAN module
Javascript API for SQLite
Ruby API for SQLite
knoda KDE driver library

Related SQLite Links:

Books:

	Using SQLite by Jay A. Kreibich ISBN #0596521189, O'Reilly First edition.
	The Definitive Guide to SQLite by Mike Owens ISBN #1590596730, Apress First edition.
	C++ How to Program by Harvey M. Deitel, Paul J. Deitel ISBN #0131857576, Prentice Hall Fifth edition. The first edition of this book (and Professor Sheely at UTA) taught me to program C++. It is complete and covers all the nuances of the C++ language. It also has good code examples. Good for both learning and reference.