SQLite Programming

Learning how to use SQLite with a given language is hard to do. One of the best ways is to pick an open-source database administration tool written in the language of your choice and then to study it. Here are some tools in various languages. Enjoy.

.Net: Dot Net SQLite Administration Program
DotNetSQLiteAdmin is a tool written in ASP.NET intended to handle the administration of SQLite databases over the Web. Currently it can create/drop databases and tables, vacuum databases/tables, execute SQL statement, a automated data entry form.

PHP: phpSQLiteAdmin phpSQLiteAdmin is a Web interface for the administration of SQLite databases.

Python: sqlxpress sqlxpress is front-end tool for Sqlite database (www.sqlite.org). Developed using python, wxPython & pysqlite. This tool is designed for Linux users who uses Sqlite Database.
Ruby: RailsDB RailsDB is a web application written in Ruby using the Ruby on Rails web framework to provide a generic interface to popular open source databases such as MySQL, PostgreSQL, and SQLite.

Ruby: Gemma - the GTK/Ruby/DBI ERP Gemma is a gtk ruby DBI sqlite ERP software, the most important features will be: - Easy to estend with new tables - Multiplatform and database indipendent - OSCommerce database compatible

C++: Smart DB SmartDB is an object oriented approach towards integrating SQLite database, like ms ado object, for C++/MFC projects. It acts as a wrapper for SQLite. You can open SQLite database into your program with just 3 lines of code. full source code available.

C/C++: SQLite Database browser is a light GUI editor for SQLite databases, built on top of QT. The main goal of the project is to allow non-technical users to create, modify and edit SQLite databases using a set of wizards and a spreadsheet-like interface.

Lua: Doris - OpenGL Scripting Doris is a Lua script driven OpenGL viewer with GUI widget extensions. Lua is a fast, powerful, portable scripting language. Lua bindings are provided to OpenGL, GLUT, GLUI (a GL widget library) and Luasocket (networking).

Lua: Duh, email client Email client, most work done in Lua, SQLite message storage, lua plugins, can use OpenSSL and iconv, but not required. Delphi GUI. Idea is to stripe GUI code down to minimum, making it portable to any platform were Lua, Sqlite and C compiler exist.

Perl: KeepInTouch - Newsletter Tool For Unix This newsletter tool started as a unix-geeks-only tool made in Perl with a SQLite DB. But it is fast evolving into a multi-purpose newsletter management tool usable within large corporations. For all features visit http://keepintouch.sourceforge.net/

Perl: mwForum mwForum is a Web-based discussion forum system. It is based on Perl CGI scripts, uses a MySQL, PostgreSQL or SQLite database and is compatible with mod_perl 1.0 and 2.0 for optimal performance.

Programming Language Interfaces to SQLite
Python
ANSI C
Perl
Ruby
Java
JDBC driver for SQLite with Java
ODBC driver for SQLite with Visual Basic (VB6)
Lua
C# or Visual Basic .Net
PHP
REXX
Lazarus Free Pascal IDE
C++ IOStream.
Windows ANSI Forth
Ada
JavaScript or ECMAScript wrapper.
The BlitzBasic Programming Language
PLT Scheme (based on Dr. Scheme)
(Google) Gears Database API
Delphi

This example is trite, and canned, has no header files, weak code structure, but it gets the point across.

You have a program composed of subsystems A,B, and C, each of which does something when told to by the main program. Each program has different levels of tracing. Some trace messages are present at all levels of tracing (level 1). Other trace messages are at some different level. Yet other trace messages are present at a different level.

Each of these subsystems has a method X_setEnabling() which sets whether or not the subsystem is activated. It also has the method X_set_traceLevel() to set the trace level.

When the system initializes, the main routine (in configExampleMain.c) show how to configure the behaviour of these subsystems at run-time.

The code is as follows:

/* a.c - There are two more files b.c and c.c that look like a.c but a_ is replaced with b_ or c_. They are not
here in the Squidoo Lens, but you can construct them by
using a.c as a reference */

extern void trace(char* inText, int inTraceLevel, int inModuleTrace);
static int a_traceLevel; /* The trace level of A */
static int a_enabled; /* The enabling of A */

void a_setTraceLevel(int inLevel) { a_traceLevel=inLevel;}

void a_setEnabling(int inEnabled) { a_enabled=inEnabled;} /* 0 to disable, 1 to enable*/

void a_doIt() {
int i,j,k;
if (a_enabled!=0) {
for ( i=0; i<2; i++) {
trace("a outer", 1, a_traceLevel);
for ( j=0; j<2; j++) {
trace("\ta middle", 2, a_traceLevel);
for ( k=0; k<2; k++) trace("\t\ta inner", 3, a_traceLevel);
}
}
} else printf ("a_doIt disabled\n");
}
/* end of a.c */

/* trace.c */
#include "stdio.h"
void trace(char* inText, int inTraceLevel, int inModuleTrace) {
if (inTraceLevel<=inModuleTrace) printf ("trace %s\n", inText);
}
/* end of trace.c */

/* configExampleMain.c */
#include "stdio.h"
#include "/home/jgodse/squidoo/configExample/sqlite3.h"

sqlite3 *db; /* global SQLite database handle */

extern void a_setTraceLevel(int inLevel);
extern void a_setEnabling(int inEnabled);

extern void a_doIt();

int main () {
printf ("configExampleMain.c\n");

int nrows, ncols, rc, i,j;
char *zErr;
char **startupConfig;
char* sql="select * from subsystems;";

rc = sqlite3_open("./configExample.db", &db); /* open the config database */

rc = sqlite3_get_table(db, "select * from subsystems;",
&startupConfig, &nrows, &ncols, &zErr); /* get the config data*/
if (rc==1) printf("zErr=%s\n", zErr);

/* For each row, pull out the name (offset 0), enabled (1), and traceLevel(2) off the stack. */
for (i=0; i=5; i++) {
/* Run the main functions of a,b,c. That will show the tracing and enabling */
printf ("Main: a_doIt\n");a_doIt();
printf ("Main: b_doIt\n");b_doIt();
printf ("Main: c_doIt\n");c_doIt();
}
return 0;
}

/* end of configExampleMain.c */

#Makefile - don't forget the tabs!
all:
gcc -c a.c
gcc -c b.c
gcc -c c.c
gcc -c trace.c
gcc -c configExampleMain.c
gcc a.o b.o c.o trace.o configExampleMain.o -lsqlite3 -o runTest
# end of Makefile

First you build the code (this example is on Linux), by running "make" on the command line.

>make

Then populate the database with the following from the command line:

> sqlite3 configExample.db "CREATE TABLE subsystems (name string, enabled int, traceLevel int);"
> sqlite3 configExample.db "INSERT INTO subsystems VALUES('a',1,2);"
> sqlite3 configExample.db "INSERT INTO subsystems VALUES('b',1,3);"
> sqlite3 configExample.db "INSERT INTO subsystems VALUES('c',1,1);"

Note that the file configExample.db is the database file holding the configuration data. It is accessible from the SQLite command line program "sqlite3".

Then you fire up the program:

>./runTest

For each of the scenarios below, change the data and see how the logs appear through the application, but running the application

>./runTest.

See which trace messages come out, and see which subsystems are enabled.

If you want to disable all systems, do

> sqlite3 configExampledb "update subsystems set enabled=0;"

To enable system a, do
>sqlite3 configExampledb "update subsystems set enabled=1 where name='a'; "

To also turn tracing level 3 in module C do this:

>sqlite3 configExample.db "update subsystems set enabled=1, traceLevel=3 where name='a';"

What does this all mean?

1. You can activate tracing in your application with some simple canned SQL commands that are scriptable from the command line.
2. You can enable and disable software modules from running during a debug session by setting the "enabled" property to 0.

Tracing and activation are key infrastructure features that can enable efficient software development. SQLite makes it easy.

SQLite provides application designers the ability to store blobs in a database. A blob is any file, text, binary, or whatever. The method is simple. First create a database with blob fields. In our example, it is

create table images (name string, image blob);

Accessing blobs uses the parameterized binding features of SQLite. You define a query string with placeholders for some parameters, and then you bind data to those parameters with a separate API call. In our example one of the query strings is:

insert into images (name, image) values (?,?);

The ?'s are the placeholders for the parameters to be bound. You then call sqlite3_prepare() to prepare the SQL, and associate it with a sqlite3_stmt.
You then call sqlite3_bind_xxx() to bind data to the prepared sqlite3_stmt. XXX is the t ype of the data (text and blob in our case).
For selects, get the data and call sqlite3_column_bytes() to get the size of the blob, and sqlite3_column_blob() to extract the column data.
Then call sqlite3_step() to execute the statement, and sqlite3_finalize().

This example reads a file and saves it into the database. It then reads the file out of the database and makes a copy with a related but different name and saves it.
Run "ls -al" on the directory and see the new files, and how they are the same size as the old files. Also note that the sqlite3 database (blobExample.db) has grown by approximately the size of the blob.

The code below should get you started in writing blob access code with SQLite. I didn't do any error checking for all of the SQLite API calls because this example is for instruction only. Always check error codes and have your code act appropriately if the sqlite3_XXXXX error codes indicate a problem.

/* Copyright 2008 Jay Godse

Licensed under the Apache License, Version 2.0 (the "License")
http://www.apache.org/licenses/LICENSE-2.0
*/

/* File name - blobExampleMain.c */
/* Purpose - The main file to show how SQLite can be used */
/* to store blobs */
/* Assumes blobTest.db already exists with a table: */
/* CREATE TABLE images (name string, image blob); */
/* Build this file (linux/gcc with the following command - */
/* gcc blobExampleMain.c -lsqlite3 -o blobExample */

#include "stdio.h"
#include "stdlib.h"
#include "string.h"
#include "sqlite3.h"

sqlite3 *db; /* global SQLite database handle */

int main (int argc, char **argv) {
int nrows, ncols, rc, i,j;
char *zErr;
char **startupConfig;

/* query strings*/
char* sqlinsert="insert into images (name, image) values (?,?);";
char* sqlselect="select image from images where name=? limit 1;";
int f1Size;
FILE * f1;
FILE * f2;
char fileToSave[200]; /* input file to save*/
char copiedFile[208]; /* file that is output*/

if (argc==2) {
strncpy(fileToSave, (char*)argv[1], 199);
printf("saving %s to database\n", fileToSave);
}
else {
printf("Usage: blobExample argc=%d\n", argc);
return 0;
}

printf("Open the file %s to copy into the database\n", fileToSave);

f1 = fopen(fileToSave, "rb");

if (f1==NULL) {
printf("%s the file does not exist\n", fileToSave);
return 0;
}

/* get the size f1Size of the input file*/
fseek(f1, 0, SEEK_END);
f1Size=ftell(f1);
fseek(f1, 0, SEEK_SET);

char *copyBuf = (char*)malloc(f1Size+1);

printf("The size of %s is %d\n", fileToSave, f1Size);

if (f1Size != fread(copyBuf, sizeof(char), f1Size, f1)) {
free (copyBuf);
return -2;
}

fclose (f1);

printf("Opening the database to copy the file into it\n");
rc = sqlite3_open("./blobExample.db", &db);
sqlite3_stmt *insertstmt;
sqlite3_stmt *selectstmt;

printf ("Now doing the image insert by binding the file to the blob\n");
rc = sqlite3_prepare(db, sqlinsert, strlen(sqlinsert), &insertstmt, NULL);

sqlite3_bind_text(insertstmt, 1, fileToSave,
strlen(fileToSave), SQLITE_STATIC);
sqlite3_bind_blob(insertstmt, 2, (const void*)copyBuf, f1Size, SQLITE_STATIC);

sqlite3_step(insertstmt);

sqlite3_finalize(insertstmt);
free (copyBuf);

printf("Now doing the select and image extraction\n");
rc=sqlite3_prepare(db, sqlselect, strlen(sqlselect), &selectstmt, NULL);
sqlite3_bind_text(selectstmt, 1, fileToSave, strlen(fileToSave), SQLITE_STATIC);

ncols=sqlite3_column_count(selectstmt);
sqlite3_step(selectstmt);

sprintf (copiedFile, "copyOf__%s", fileToSave);

f2 = fopen(copiedFile, "wb");
fwrite (sqlite3_column_blob(selectstmt, 0), sqlite3_column_bytes(selectstmt, 0), 1, f2);
fclose (f2);

sqlite3_finalize(selectstmt);
return 0;

}

In November 2007, Microsoft released version 3.5 of the .Net framework. Buried among the features is a technology called Language Integrated Query or LINQ. What is it?

The Wikipedia article on LINQ says that it adds a kind of query language to the .Net framework. This allows the programmer to access data storage in SQL databases, XML formats, and program variables with a query language. The Wikipedia article describes the article quite well, including the main primitives of the query language. It looks and smells like standard SQL. Microsoft has also released some very illustrative sample code that shows how to use LINQ. The following example in C# shows a typical use of LINQ. It takes a list of products, called "products" and asks for all information, ordered by the units in stock.

public void Linq33() {
List products = GetProductList();

var sortedProducts =
from p in products
orderby p.UnitsInStock descending
select p;

ObjectDumper.Write(sortedProducts);
}

This is a very simple thing to do with a SQL database, or even an XML document. However, the code to sort a list by one of its fields is considerably more complex. It is also more error-prone.

Why is this a better way? Several reasons.

1. Using LINQ focuses designers on specifying exactly which data they want, and not on how to acquire that data. The query engine figures out how to manipulate the data to get the desired result, and then executes the query plan.
2. Using LINQ reduces the code volume, probably by a factor of 10 per query. The cheapest line of code to debug is the one you don't have to write.
3. LINQ lets you use the single language on any data storage, not just SQL databases or XML documents.

This is a great technology...unless you happen to work on:

* A software platform that is not Microsoft .Net 3.5.
* A platform with limited resources (can't afford a CLR).
* A code base written with an "old" language such as C, C++, or even Java. These platforms make up a large number of users and enterprises. (For example the TIOBE Programming Communicty Index suggests that about 22% of programmers still use C/C++).

This is where SQLite "comes to the rescue". If you define your internal data (including global variables) in an "in-memory" SQLite database, you can get the same effect as having LINQ. The following code examples implement the C# example from above using C and SQLite.

//static global database handle
static sqlite3* db; //database with product list
//somewhere in the initialization code you did the following:
db=sqlite3_open(":memory:", &db); //creates an in-memory database

// The SQLite equivalent function of Linq33
void SQLite_Linq33() {
List products = GetProductList();
int nrows, ncols, rc, i,j;
char *zErr;
char *sortedProducts[];
char* sql="select * from products as p order by p.UnitsInStock desc;"

rc = sqlite3_get_table(db, sql, &sortedProducts, &nrows, &ncols, &zErr);
for (i=0; lessThan(i,nrows);i++) {
for(j=0; lessThan(j,ncols); j++) {
printf("%s |", sortedProducts[(i+1)*ncols + j]
}
printf ("\n");
}
sqlite3_free_table(sortedProducts);
}
//note that lessThan(a,b) returns TRUE if a is less than b.
//I needed this function because you can't use the actual
//less than sign in Squidoo without it getting rejected,
// possibly as a HTML injection attack.

There you go. You can get the benefits of Language Integrated Query without using .Net 3.5. So even if you are trapped working in a legacy ANSI C or C++ software platform, SQLite can help you get the benefits of Language Integrated Query without switching platforms.

There is a great tutorial at dblog.com.au which shows how to build an iPhone application from start to finish. It includes using an on-phone SQLite database for data storage.

Enjoy.

One great way to learn SQLite is to study projects that have used SQLite. The following open source projects use SQLite. Download and study the code.
The Horse's Mouth
SQLite web site

Apache Plug-ins
Apache plug-in for SQLite logging
Apache log file analyzer
Apache authentication module

Content Management and Blogging
Mojoportal - a .Net based content management system. SQLite is one of many databases supported.
Blogging and CMS platform.
PHP content management system
Content management system.
LiteBlog Blogging platform
Web site on CD.

Code Generators and Development Frameworks
Ruby-based web development framework. SQLite is one of many databases supported.
Android - Google et al wireless develoment platform.
C++ code generator from database schema
Convert SQL query results to XML without XSLT
Database application development framework
Tool for convertng Palm OS databases to XML. XML <--> SQLite conversion function too.
Rich Internet Application development kit.
Embedded system development tools
TCL/TK executalble generator
Web services development framework for grid computing
Web application server.
C++ MUD Game framework
Automatic Testing Framework in Perl. Not open source, but it is a tutorial on automated testing, with Perl, and using SQLite.

Unusual and Cool
Epidemic simulator
EDI translator, formats include edifact, x12, xml, SAP idoc
Storing & searching linguistically annotated text
Management of genome-scale biological databases.
Genetic pedigree analysis tool.
Python astronomy software library.
Open source project metatadata gatherer.
Home theatre platform (not unlike TiVo)
Open Source Windows clipboard extension
Full-text search engine.
Predictive text entry system
Scientific Word Processor for the blind
Flexible paste bin
Network monitor changes tool.
Media player for Sharp Zaurus
Print quota management for CUPS

Programming Language Interfaces to SQLite
REXX interface to SQLite
Lazarus Free Pascal IDE
SQL connection is represented as a C++ stream.
Gambas - A Basic-like development language
Windows ANSI Forth
Ada database wrapper
JavaScript or ECMAScript wrapper.

Information Management
Bibliographic reference
Mail classification tool
Movie database management
Code review management
Manage running & races
Information and contact manager.
Reference & bibliography database for SGML, XML, and LaTeX/BibTeX docs
CD collection manager
Issue Tracking System
Mail filter
GoGear (Phillips MP3 player) for Linux Boxes - a MP3 searcher and list builder
BibWork - bibliographic and research management application
Media Server to use with Netgear MP101 Digital Music Player
IMDb movie database management tool
RSS/Atom newsreader for Mac OS X.
Bible editor
Email grey listing software
Photo-image archive
Create and manage digital books
Manhattan virtual classroom course management system

Communication
Streamline Media Server
Mumble group voice chat system. murmur.sqlite is the database
Secure peer-to-peer networking
Open source WAP/SMS gateway
Enterprise Secure Shell (SSH)

Math Stuff, Statistics & Data Analysis
Statistical library for working with data sets and statistical models.
Cubulus OLAP engine
Equational Programming Language
Wrapper for J Programming Language - strong on statistics & mathematical analysis.
Logic Proof language

Money Management
Home accounting system
GNU Cash for embedded devices
Personal Finance Manager for KDE