The SQLite Database Engine
by Michael Owens

Listing 1: VDBE Program for a Simple Query

SQLite version 2.8.0
Enter ".help" for instructions

sqlite> explain select projectname_short from project
   ...> order by rating desc limit 10;
0          ColumnName   0          0        projectname_short
1          Integer      -10        0
2          MemStore     0          1
3          Integer      0          0
4          OpenRead     0          3        project
5          VerifyCook   0          1476
6          Rewind       0          13
7          Column       0          3
8          SortMakeRe   1          0
9          Column       0          11
10         SortMakeKe   1          0 
11         SortPut      0          0
12         Next         0          7
13         Close        0          0
14         Sort         0          0
15         SortNext     0          19
16         MemIncr      0          19
17         SortCallba   1          0
18         Goto         0          15
19         SortReset    0          0
20         Halt         0          0


Listing 2: C API example

#include <stdlib.h>
#include <stdio.h>
#include <sqlite.h>
typedef struct sqlite_vm sqlite_vm;
int main()
{
   const char* db_name = "db";
   sqlite *db; /* The database handle */
   char *sql = "select projectname_full as name, rating, license "
               "from project order by rating desc limit 10";
   const char *tail; /* Points to next SQL statement to process, if any. */
   char *err_msg; /* Last error message, if any. */

   sqlite_vm *pvm; /* Virtual machine for executing query. */
   db = sqlite_open(db_name, 0, &err_msg);
   if(db==0)
   {
      fprintf(stderr, "Can't open database: %s\n", err_msg);
      exit(1);
   }
   /* Compile SQL, allocate a virtual machine for processing. */
   int ret = sqlite_compile(db,sql,&tail,&pvm,&err_msg);
   if(ret != SQLITE_OK)
   {
      fprintf(stderr, "Compile failed: %s\n", err_msg);
      sqlite_freemem(err_msg);
      exit(1);
   }
   int i, ncols;
   const char** fields;
   const char** col_defs;
   ret = sqlite_step(pvm, &ncols, &fields, &col_defs);
   /* Print Column Names */
   printf("%35s %5s %40s\n\n", col_defs[0], col_defs[1], col_defs[2]);
   /* Print Column Datatypes */
   printf("%35s %5s %40s\n\n", col_defs[3], col_defs[4], col_defs[5]);
   /* Print Result Set */
   while(ret == SQLITE_ROW)
   {
      printf("%35s %5s %40s\n", fields[0], fields[1], fields[2]);
      ret = sqlite_step(pvm, &ncols, &fields, &col_defs);
   }
   ret = sqlite_finalize(pvm, &err_msg);
   if(ret != SQLITE_OK)
   {
      fprintf(stderr, "Finalize failed: %s\n", err_msg);
      sqlite_freemem(err_msg);
      exit(1);
   }
   sqlite_close(db);
}

Listing 3: Creating A Custom Function

void normal_curve_area(sqlite_func* context, int argc, const char **argv)
{
   char** endptr;
   char result[65];
   double x1, x2, mu, sigma;
   if(argc != 4)
   {
      return;
   }
   x1 = strtod((char*)argv[0], endptr);
   if((x1==0) && (argv[0]==*endptr))
      return;
   x2 = strtod((char*)argv[1], endptr);
   if((x2==0) && (argv[1]==*endptr))

      return;
   mu = strtod((char*)argv[2], endptr);
   if((x1==0) && (argv[2]==*endptr))
      return;
   sigma = strtod((char*)argv[3], endptr);
   if((x1==0) && (argv[3]==*endptr))
      return;
   sprintf(result, "%f", GetNormalCurveArea(x1,x2,mu,sigma));
   sqlite_set_result_string(context, result, -1);
}
double GetNormalCurveArea(double x1, double x2, double mu, double sigma)
{
   /* Maclaurin Series Expansion for exp(-x2/2)
   Michael Owens
   Description: This function takes two random variables, a lower 
   limit (x1) and an upper limit (x2), on a Gaussian distribution and 
   computes the total area between them.
   User Input Parameters: 
   x2: upper limit
   x1: lower limit
   mu: population mean
   sigma: variance
   Nomenclature:
   sz: dummy variable for series expansion
   z = (x-mu)/sig
   cum: the cumulative value of z, or integral
   cum1 is the area from -r1 to 0 while
   cum2 is the area from 0 to r2.
   Limitations:
   The Limiting Values of z: A value greater than z=5 will give exactly 50% of
   the normal curve to four decimal places, and larger values will only
   encumber series convergence, therefore any values greater than 4 will be
   reset to 4.
   */
   double j = 10; // Initialized for priming the while() block.
   double bound = 4.2;
   double z1 = (x1 - mu) / sigma;
   double z2 = (x2 - mu) / sigma;
   if (z1 < -bound)
      z1 = (double)-bound;
   if (z1 > bound)
      z1 = (double)bound;
   if (z2 < -bound)
      z2 = (double)-bound;
   if (z2 > bound)
      z2 = (double)bound;
   double cum1 = fabs(z1);
   double cum2 = fabs(z2);
   // Use absolute values for computing terms
   x1 = fabs(z1);
   x2 = fabs(z2);
   // Computations
   // Maclaurin Series: term by term addition
   // Area of lower limit
   if(cum1)

      SeriesExpansion(x1,cum1);
   else
      cum1 = 0;
   // Area of upper limit
   if(cum2)
      SeriesExpansion(x2,cum2);
   else
      cum2 = 0;
   // Determine the total area:
   double Area;
   if ((z2 + z2) < (fabs(z2 + z2))) // if z2 is negative
      Area = cum1 - cum2; // then z1 must be negative too.
   else
      if ((z1 + z1) < (fabs(z1 + z1))) // z2 is positve and if z1 negative
         Area = cum1 + cum2;
      else
         Area = fabs(cum2 - cum1); // if z1 is positive
   // Limiting area from origin to +infinity
   double CA;
   CA = pow(2*3.1415926535, 0.5);
   // Normalized area
   Area = Area/CA; // Area from origin to lower limit.
   return Area;
}
short SeriesExpansion(double &x, double &cum)
{
   double SeriesTerm;
   double j = 10;
   for (int i = 1; j > 0.0001; i++)
   {
      int f = i;
      double factorial = f;
      if(f-1)
      {
      while(f-1)
         factorial *= --f;
      }
      if(!factorial)
      return 0;
      SeriesTerm = (pow(-1,i));
      SeriesTerm *= (double)1/((2*i)+1);
      SeriesTerm *= (double)pow(x,(2*i+1));
      SeriesTerm *= (double)1/((pow(2,i)*factorial));
      cum += SeriesTerm;
      j = fabs(SeriesTerm);
   }
   return 1;
}


Listing 4: Using A Custom Function

int main(int argc, char **argv)
{
   sqlite *db;

   const char *tail;
   sqlite_vm *pvm;
   char *err_msg;
   int ncols;
   const char** fields;
   const char** col_defs;
   db = sqlite_open("db", 0, &err_msg);
   sqlite_create_function(db, "normal_curve_area", 4, normal_curve_area, NULL);
   const char* sql = "select normal_curve_area(-2.35, 2.35, 0, 1)";
   sqlite_compile(db, sql, &tail, &pvm, &err_msg);
   sqlite_step(pvm, &ncols, &fields, &col_defs);
   printf("Area=%s\n", fields[0]);
   sqlite_finalize(pvm, &err_msg);
   sqlite_close(db);
   return 0;
}


Listing 5: Controlling Conflict Resolution

SQLite version 2.8.2
Enter ".help" for instructions
sqlite> -- Create table;
sqlite> create table emp(name text UNIQUE ON CONFLICT ROLLBACK);
sqlite> -- Populate;
sqlite> insert into emp values('Larry');
sqlite> insert into emp values('Moe');
sqlite> insert into emp values('Curly');
sqlite> -- generate a UNIQUE constraint violation;
sqlite> insert into emp values('Curly');
SQL error: uniqueness constraint failed
sqlite> -- try to commit, won't work as previous resolution rolled back transaction.
sqlite> commit;
SQL error: cannot commit - no transaction is active
sqlite> -- Set REPLACE at transaction scope.
sqlite> begin on conflict replace;
sqlite> -- try again: this time it will work
sqlite> insert into emp values('Curly');
sqlite> commit;
sqlite> -- Play around with statement level resolution;
sqlite> begin on conflict replace;
sqlite> -- ABORT will stop us, but leave transaction running.
sqlite> insert or ABORT into emp values('Curly');
SQL error: uniqueness constraint failed
sqlite> -- FAIL will stop us, but leave transaction running.
sqlite> insert or FAIL into emp values('Curly');
SQL error: uniqueness constraint failed
sqlite> -- IGNORE will silently fail, but leave transaction running.
sqlite> insert or IGNORE into emp values('Curly');
sqlite> -- default transaction scope is REPLACE, will push it through.
sqlite> insert into emp values('Curly');
sqlite> commit;
sqlite>



Listing 6: Trigger Examples

-- Log deleted projects
CREATE TRIGGER on_delete_proj BEFORE DELETE ON project
FOR EACH ROW
BEGIN
   insert into removed values(old.project_id,old.projectname_full);
END
-- Track version changes. Set conflict to overwrite matching records.
CREATE TRIGGER on_update_proj BEFORE UPDATE OF latest_version ON project
FOR EACH ROW
BEGIN
   insert OR REPLACE into versions
   values( new.project_id, old.latest_version, new.latest_version )
END


Listing 7: Views with Triggers

CREATE VIEW above_average AS SELECT projectname_full, rating
FROM project
WHERE rating > (SELECT AVG(rating) FROM project)
ORDER BY rating DESC;
-- Make the view updatable according to following trigger
CREATE TRIGGER on_update_above_average
INSTEAD OF UPDATE ON above_average
FOR EACH ROW
BEGIN
   UPDATE project SET rating=new.rating
   WHERE projectname_full=new.projectname_full;
END
-- Now the following update to the view will work:
UPDATE above_average SET rating=10 WHERE projectname_full='Gentoo Linux';


Listing 8: SQLite from Perl

use DBI;
my $dbh = DBI->connect("dbi:SQLite:dbname=db","","");
my $cursor;
my @rec;
my $SQL = "select projectname_full as name, rating "
           . "from project order by rating desc";
$cursor = $dbh->prepare($SQL);
$cursor->execute();
while(@rec = $cursor->fetchrow_array)
{
   print "$rec[0], $rec[1]\n";
}
$cursor->finish;
$dbh->disconnect;


Listing 9: SQLite from Python
import sqlite

conn = sqlite.connect(db="db", mode=077)
cursor = conn.cursor()
SQL = """select projectname_full as name,
         rating from project
         order by rating desc"""
cursor.execute(SQL)
row = cursor.fetchone()
while row != None:
   print "%14s, %15s" % (row['name'], row['rating'])
   row = cursor.fetchone()
conn.close()





4