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

1INSERT INTO employee VALUES ('Wilma Flinstone','Finance','Analyst');
2INSERT into employee values ('Barney Rubble','Sales','Neighbor');
3INSERT 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

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

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

01#include <iostream>
02#include <sqlite3.h>
03#include <stdlib.h>
04 
05// g++ AccessTableEmployee.cpp -lsqlite3
06 
07using namespace std;
08 
09static int callback(void *NotUsed, int argc, char **argv, char **azColName)
10{
11  int i;
12  cout << "Number of args= " << argc << endl;
13 
14  for(i=0; i<argc; i++)
15  {
16     cout << azColName[i] << " = " << (argv[i] ? argv[i] : "NULL") << endl;
17  }
18  cout << endl;
19  return 0;
20}
21 
22int main(int argc, char **argv)
23{
24  sqlite3 *db;       // Declare pointer to sqlite database structure
25  char *zErrMsg = 0;
26 
27  // Open Database 
28 
29  int rc = sqlite3_open("/tmp/bedrock.db", &db);
30  if( rc )
31  {
32    cerr << "Can't open database: " << sqlite3_errmsg(db) << endl;
33    sqlite3_close(db);
34    exit(1);
35  }
36 
37  // Insert data into database
38 
39  const char *zSql = "INSERT INTO employee(Name, Dept, jobTitle) VALUES('Barney Rubble','Sales','Neighbor')";
40 
41  sqlite3_stmt *ppStmt;
42  const char **pzTail;
43 
44  if( sqlite3_prepare_v2(db, zSql, strlen(zSql)+1, &ppStmt, pzTail) != SQLITE_OK )
45  {
46      cerr << "db error: " << sqlite3_errmsg(db) << endl;
47  }
48 
49  if(ppStmt)
50  {
51      sqlite3_step(ppStmt);
52      sqlite3_finalize(ppStmt);
53      sqlite3_exec(db, "COMMIT", NULL, NULL, NULL);
54  }
55  else
56  {
57      cerr << "Error: ppStmt is NULL" << endl;
58  }
59 
60  // Select from database 
61 
62  rc = sqlite3_exec(db,"select * from employee", callback, 0, &zErrMsg);
63  if( rc!=SQLITE_OK )
64  {
65      cerr << "SQL error: " << zErrMsg << endl;
66      sqlite3_free(zErrMsg);
67  }
68 
69  // Close
70 
71  sqlite3_close(db);
72  return 0;
73}

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

01#include <iostream>
02#include <stdlib.h>
03#include <string.h>
04#include <sqlite3.h>
05#include <time.h>
06 
07using namespace std;
08 
09// Store gmtime data structure as a blob
10// See man pages for information on gmtime data structure: man gmtime
11 
12static int callback(void *NotUsed, int argc, char **argv, char **azColName)
13{
14  struct tm blob;
15 
16  for(int i=0; i<argc; i++)
17  {
18    if(!strcmp(azColName[i],"MyData"))
19    {  // Handle Blob data
20       memcpy(&blob, argv[i], sizeof(struct tm));
21       cout << "Year retrieved from blob: " << blob.tm_year+1900 << endl;
22    }
23    else
24    {  // All other database collumns
25       cout << azColName[i] << " = " << (argv[i] ? argv[i] : "NULL") << endl;
26    }
27  }
28  cout << endl;
29  return 0;
30}
31 
32 
33int main(int argc, char **argv)
34{
35  sqlite3 *db;
36  char *zErrMsg = 0;
37 
38  time_t tt = 0;
39  time_t now = time(&tt);          // seconds since the Epoch
40  struct tm *blob = gmtime(&now);  // Create the blob to store in the database
41 
42  cout << "Year stored: " << blob->tm_year+1900 << endl;
43  cout << endl;
44 
45  int rc = sqlite3_open("/tmp/bedrock.db", &db);
46  if( rc )
47  {
48    cerr << "Can't open database: " << sqlite3_errmsg(db) << endl;
49    exit(1);
50  }
51 
52  // Insert blob data into database
53 
54  const char   *zSql = "INSERT INTO btest(ID, MyData) VALUES('1',?)";
55  sqlite3_stmt *ppStmt;
56  const char  **pzTail;
57 
58  if( sqlite3_prepare_v2(db, zSql, strlen(zSql)+1, &ppStmt, pzTail) != SQLITE_OK )
59  {
60      cerr << "db error: " << sqlite3_errmsg(db) << endl;
61      sqlite3_close(db);
62      exit(1);
63  }
64 
65  if(ppStmt)
66  {
67      // For Blob collumn bind 1
68      sqlite3_bind_blob(ppStmt, 1, blob, sizeof(struct tm), SQLITE_TRANSIENT);
69      sqlite3_step(ppStmt);
70      sqlite3_finalize(ppStmt);
71      sqlite3_exec(db, "COMMIT", NULL, NULL, NULL);
72  }
73  else
74  {
75      cerr << "Error: ppStmt is NULL" << endl;
76      sqlite3_close(db);
77      exit(1);
78  }
79 
80  // Select rows from database
81 
82  // Note: sqlite3_exec() does sqlite3_prepare_v2(), sqlite3_step() and sqlite3_finalize() into a single function call
83 
84  rc = sqlite3_exec(db,"SELECT * FROM btest", callback, 0, &zErrMsg);
85  if( rc!=SQLITE_OK )
86  {
87      cerr << "SQL error: " << zErrMsg << endl;
88      sqlite3_free(zErrMsg);
89      sqlite3_close(db);
90      exit(1);
91  }
92 
93  sqlite3_exec(db, "END", NULL, NULL, NULL);
94 
95  // Close database
96 
97  sqlite3_close(db);
98  return 0;
99}

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

001#include <iostream>
002#include <stdlib.h>
003#include <string.h>
004#include <sqlite3.h>
005#include <time.h>
006 
007using namespace std;
008 
009// Store gmtime data structure as a blob
010// See man pages for information on gmtime data structure: man gmtime
011 
012int main(int argc, char **argv)
013{
014  sqlite3 *db;
015  sqlite3_stmt *ppStmt;
016  char *zErrMsg = 0;
017  const char   *zSql = "INSERT INTO btest(ID, MyData) VALUES('1',?)";
018 
019  time_t tt = 0;
020  time_t now = time(&tt);          // seconds since the Epoch
021  struct tm *blob = gmtime(&now);  // Create the blob to store in the database
022 
023  cout << "Year stored: " << blob->tm_year+1900 << endl;
024  cout << endl;
025 
026  int rc = sqlite3_open("/tmp/bedrock.db", &db);
027  if( rc )
028  {
029    cerr << "Can't open database: " << sqlite3_errmsg(db) << endl;
030    exit(1);
031  }
032 
033  // Insert blob data into database
034 
035  if( sqlite3_prepare_v2(db, zSql, -1, &ppStmt, NULL) != SQLITE_OK )
036  {
037      cerr << "db error: " << sqlite3_errmsg(db) << endl;
038      sqlite3_close(db);
039      exit(1);
040  }
041 
042  if(ppStmt)
043  {
044      // For Blob collumn bind 1
045      sqlite3_bind_blob(ppStmt, 1, blob, sizeof(struct tm), SQLITE_TRANSIENT);
046      sqlite3_step(ppStmt);
047      sqlite3_finalize(ppStmt);
048      sqlite3_exec(db, "COMMIT", NULL, NULL, NULL);
049  }
050  else
051  {
052      cerr << "Error: ppStmt is NULL" << endl;
053      sqlite3_close(db);
054      exit(1);
055  }
056 
057  // Select rows from database
058 
059  const char *zSqlSelect = "select * from btest";
060  if( sqlite3_prepare_v2(db, zSqlSelect, -1, &ppStmt, NULL) != SQLITE_OK )
061  {
062      cerr << "db error: " << sqlite3_errmsg(db) << endl;
063      sqlite3_close(db);
064      exit(1);
065  }
066 
067  // Name of DB table
068  cout << sqlite3_column_table_name(ppStmt,0) << endl;
069 
070  // For each row returned
071  while (sqlite3_step(ppStmt) == SQLITE_ROW)
072  {
073      // For each collumn
074      for(int jj=0; jj < sqlite3_column_count(ppStmt); jj++)
075      {
076          // Print collumn name
077          cout << sqlite3_column_name(ppStmt,jj) << " = ";
078 
079          // Print collumn data
080          switch(sqlite3_column_type(ppStmt, jj))
081          {
082             case SQLITE_INTEGER: cout << sqlite3_column_int(ppStmt, jj) << endl;
083                  break;
084             case SQLITE_FLOAT:   cout << sqlite3_column_double(ppStmt, jj) << endl;
085                  break;
086             case SQLITE_TEXT:    cout << sqlite3_column_text(ppStmt, jj) << endl;
087                  break;
088             case SQLITE_BLOB:    cout << "BLOB " << endl;
089                  cout << "Size of blob: " << sqlite3_column_bytes(ppStmt, jj) << endl;
090                  struct tm *blobRetreived;
091                  blobRetreived = (struct tm *) sqlite3_column_blob(ppStmt, jj);
092                  cout << "Year retrieved from blob: " << blobRetreived->tm_year+1900 << endl;
093                  break;
094             case SQLITE_NULL:    cout << "NULL " << endl;
095                  break;
096             default: cout << "default " << endl;
097                  break;
098          }
099      }
100  }
101  sqlite3_finalize(ppStmt);
102 
103  sqlite3_exec(db, "END", NULL, NULL, NULL);
104 
105  // Close database
106 
107  sqlite3_close(db);
108  return 0;
109}

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.