test:	testfixture sqlite
	./testfixture $(TOP)/test/quick.test

sqlite.tar.gz:	
	pwd=`pwd`; cd $(TOP)/..; tar czf $$pwd/sqlite.tar.gz sqlite

index.html:	$(TOP)/www/index.tcl sqlite.tar.gz last_change

	tclsh $(TOP)/www/index.tcl `cat $(TOP)/VERSION` >index.html

sqlite.html:	$(TOP)/www/sqlite.tcl
	tclsh $(TOP)/www/sqlite.tcl >sqlite.html

c_interface.html:	$(TOP)/www/c_interface.tcl
	tclsh $(TOP)/www/c_interface.tcl >c_interface.html

changes.html:	$(TOP)/www/changes.tcl
	tclsh $(TOP)/www/changes.tcl >changes.html

fileformat.html:	$(TOP)/www/fileformat.tcl
	tclsh $(TOP)/www/fileformat.tcl >fileformat.html

lang.html:	$(TOP)/www/lang.tcl
	tclsh $(TOP)/www/lang.tcl >lang.html

vdbe.html:	$(TOP)/www/vdbe.tcl
	tclsh $(TOP)/www/vdbe.tcl >vdbe.html

arch.html:	$(TOP)/www/arch.tcl
................................................................................
# Files to be published on the website.
#
PUBLISH = \
  sqlite.tar.gz \
  index.html \
  sqlite.html \
  changes.html \
  fileformat.html \
  lang.html \
  opcode.html \
  arch.html \
  arch.png \
  vdbe.html \
  c_interface.html \
  crosscompile.html \
  mingw.html \
  tclsqlite.html

website:	$(PUBLISH)

publish:	$(PUBLISH)
	chmod 0644 $(PUBLISH)
	scp $(PUBLISH) hwaci@oak.he.net:public_html/sw/sqlite

install:	sqlite libsqlite.a sqlite.h
	mv sqlite /usr/bin
	mv libsqlite.a /usr/lib
	mv sqlite.h /usr/include

clean:	
	rm -f *.o sqlite libsqlite.a sqlite.h
	rm -f lemon lempar.c parse.* sqlite.tar.gz
	rm -f $(PUBLISH)
	rm -f *.da *.bb *.bbg gmon.out

test:	testfixture sqlite
	./testfixture $(TOP)/test/quick.test

sqlite.tar.gz:	
	pwd=`pwd`; cd $(TOP)/..; tar czf $$pwd/sqlite.tar.gz sqlite

index.html:	$(TOP)/www/index.tcl sqlite.tar.gz last_change
	cp ../sqlite-*.tar.gz .
	tclsh $(TOP)/www/index.tcl `cat $(TOP)/VERSION` >index.html

sqlite.html:	$(TOP)/www/sqlite.tcl
	tclsh $(TOP)/www/sqlite.tcl >sqlite.html

c_interface.html:	$(TOP)/www/c_interface.tcl
	tclsh $(TOP)/www/c_interface.tcl >c_interface.html

changes.html:	$(TOP)/www/changes.tcl
	tclsh $(TOP)/www/changes.tcl >changes.html




lang.html:	$(TOP)/www/lang.tcl
	tclsh $(TOP)/www/lang.tcl >lang.html

vdbe.html:	$(TOP)/www/vdbe.tcl
	tclsh $(TOP)/www/vdbe.tcl >vdbe.html

arch.html:	$(TOP)/www/arch.tcl
................................................................................
# Files to be published on the website.
#
PUBLISH = \
  sqlite.tar.gz \
  index.html \
  sqlite.html \
  changes.html \

  lang.html \
  opcode.html \
  arch.html \
  arch.png \
  vdbe.html \
  c_interface.html \
  crosscompile.html \
  mingw.html \
  tclsqlite.html

website:	$(PUBLISH)

publish:	$(PUBLISH)
	chmod 0644 $(PUBLISH) sqlite-*.tar.gz
	scp $(PUBLISH) sqlite-*.tar.gz hwaci@oak.he.net:public_html/sw/sqlite

install:	sqlite libsqlite.a sqlite.h
	mv sqlite /usr/bin
	mv libsqlite.a /usr/lib
	mv sqlite.h /usr/include

clean:	
	rm -f *.o sqlite libsqlite.a sqlite.h
	rm -f lemon lempar.c parse.* sqlite.tar.gz
	rm -f $(PUBLISH)
	rm -f *.da *.bb *.bbg gmon.out

2.0.0

2.0-alpha-2

*/
int sqliteOsFileExists(const char *zFilename){
#if OS_UNIX
  return access(zFilename, 0)==0;
#endif
#if OS_WIN
  HANDLE h;
  h = CreateFile(zBuf,
    GENERIC_READ,
    0,
    NULL,
    OPEN_EXISTING,
    FILE_ATTRIBUTE_NORMAL | FILE_FLAG_RANDOM_ACCESS,
    NULL
  );
  if( h!=INVALID_FILE_HANDLE ){
    CloseHandle(h);
    return 1;
  }
  return 0;
#endif
}

................................................................................
      int n = sqliteRandomByte() % sizeof(zChars);
      zBuf[j++] = zChars[n];
    }
    zBuf[j] = 0;
  }while( access(zBuf,0)==0 );
#endif
#if OS_WIN




  int i, j;
  HANDLE h;
  char zTempPath[SQLITE_TEMPNAME_SIZE];
  GetTempPath(SQLITE_TEMPNAME_SIZE-30, zTempPath);
  for(;;){
    sprintf(zBuf, "%s/sqlite_", zTempPath);
    j = strlen(zBuf);
    for(i=0; i<15; i++){
      int n = sqliteRandomByte() % sizeof(zChars);
................................................................................
#if OS_UNIX
  int got;
  got = read(id, pBuf, amt);
  if( got<0 ) got = 0;
  return got==amt ? SQLITE_OK : SQLITE_IOERR;
#endif
#if OS_WIN
  int got;
  if( !ReadFile(id, pBuf, amt, &got, 0) ){
    got = 0;
  }
  return got==amt ? SQLITE_OK : SQLITE_IOERR;
#endif
}

................................................................................
#if OS_UNIX
  int wrote;
  wrote = write(id, pBuf, amt);
  if( wrote<amt ) return SQLITE_FULL;
  return SQLITE_OK;
#endif
#if OS_WIN
  int wrote;
  if( !WriteFile(id, pBuf, amt, &wrote, 0) || wrote<amt ){
    return SQLITE_FULL;
  }
  return SQLITE_OK;
#endif
}

*/
int sqliteOsFileExists(const char *zFilename){
#if OS_UNIX
  return access(zFilename, 0)==0;
#endif
#if OS_WIN
  HANDLE h;
  h = CreateFile(zFilename,
    GENERIC_READ,
    0,
    NULL,
    OPEN_EXISTING,
    FILE_ATTRIBUTE_NORMAL | FILE_FLAG_RANDOM_ACCESS,
    NULL
  );
  if( h!=INVALID_HANDLE_VALUE ){
    CloseHandle(h);
    return 1;
  }
  return 0;
#endif
}

................................................................................
      int n = sqliteRandomByte() % sizeof(zChars);
      zBuf[j++] = zChars[n];
    }
    zBuf[j] = 0;
  }while( access(zBuf,0)==0 );
#endif
#if OS_WIN
  static char zChars[] =
    "abcdefghijklmnopqrstuvwxyz"
    "ABCDEFGHIJKLMNOPQRSTUVWXYZ"
    "0123456789";
  int i, j;

  char zTempPath[SQLITE_TEMPNAME_SIZE];
  GetTempPath(SQLITE_TEMPNAME_SIZE-30, zTempPath);
  for(;;){
    sprintf(zBuf, "%s/sqlite_", zTempPath);
    j = strlen(zBuf);
    for(i=0; i<15; i++){
      int n = sqliteRandomByte() % sizeof(zChars);
................................................................................
#if OS_UNIX
  int got;
  got = read(id, pBuf, amt);
  if( got<0 ) got = 0;
  return got==amt ? SQLITE_OK : SQLITE_IOERR;
#endif
#if OS_WIN
  DWORD got;
  if( !ReadFile(id, pBuf, amt, &got, 0) ){
    got = 0;
  }
  return got==amt ? SQLITE_OK : SQLITE_IOERR;
#endif
}

................................................................................
#if OS_UNIX
  int wrote;
  wrote = write(id, pBuf, amt);
  if( wrote<amt ) return SQLITE_FULL;
  return SQLITE_OK;
#endif
#if OS_WIN
  DWORD wrote;
  if( !WriteFile(id, pBuf, amt, &wrote, 0) || wrote<amt ){
    return SQLITE_FULL;
  }
  return SQLITE_OK;
#endif
}

#  define SQLITE_MIN_SLEEP_MS 1
# else
#  define SQLITE_MIN_SLEEP_MS 1000
# endif
#endif

#if OS_WIN


  typedef HANDLE OsFile;
# define SQLITE_TEMPNAME_SIZE (MAX_PATH+50)
# deifne SQLITE_MIN_SLEEP_MS 1
#endif

int sqliteOsDelete(const char*);
int sqliteOsFileExists(const char*);
int sqliteOsOpenReadWrite(const char*, OsFile*, int*);
int sqliteOsOpenExclusive(const char*, OsFile*);
int sqliteOsOpenReadOnly(const char*, OsFile*);

#  define SQLITE_MIN_SLEEP_MS 1
# else
#  define SQLITE_MIN_SLEEP_MS 1000
# endif
#endif

#if OS_WIN
#include <windows.h>
#include <winbase.h>
  typedef HANDLE OsFile;
# define SQLITE_TEMPNAME_SIZE (MAX_PATH+50)
# define SQLITE_MIN_SLEEP_MS 1
#endif

int sqliteOsDelete(const char*);
int sqliteOsFileExists(const char*);
int sqliteOsOpenReadWrite(const char*, OsFile*, int*);
int sqliteOsOpenExclusive(const char*, OsFile*);
int sqliteOsOpenReadOnly(const char*, OsFile*);

** The pager is used to access a database disk file.  It implements
** atomic commit and rollback through the use of a journal file that
** is separate from the database file.  The pager also implements file
** locking to prevent two processes from writing the same database
** file simultaneously, or one process from reading the database while
** another is writing.
**
** @(#) $Id: pager.c,v 1.23 2001/09/19 13:58:44 drh Exp $
*/
#include "sqliteInt.h"
#include "pager.h"
#include "os.h"
#include <assert.h>
#include <string.h>

................................................................................
  nRec = (nRec - (sizeof(aMagic)+sizeof(Pgno))) / sizeof(PageRecord);

  /* Process segments beginning with the last and working backwards
  ** to the first.
  */
  for(i=nRec-1; i>=0; i--){
    /* Seek to the beginning of the segment */
    off_t ofst;
    ofst = i*sizeof(PageRecord) + sizeof(aMagic) + sizeof(Pgno);
    rc = sqliteOsSeek(pPager->jfd, ofst);
    if( rc!=SQLITE_OK ) break;
    rc = sqliteOsRead(pPager->jfd, &pgRec, sizeof(pgRec));
    if( rc!=SQLITE_OK ) break;

    /* Sanity checking on the page */

** The pager is used to access a database disk file.  It implements
** atomic commit and rollback through the use of a journal file that
** is separate from the database file.  The pager also implements file
** locking to prevent two processes from writing the same database
** file simultaneously, or one process from reading the database while
** another is writing.
**
** @(#) $Id: pager.c,v 1.24 2001/09/20 01:44:43 drh Exp $
*/
#include "sqliteInt.h"
#include "pager.h"
#include "os.h"
#include <assert.h>
#include <string.h>

................................................................................
  nRec = (nRec - (sizeof(aMagic)+sizeof(Pgno))) / sizeof(PageRecord);

  /* Process segments beginning with the last and working backwards
  ** to the first.
  */
  for(i=nRec-1; i>=0; i--){
    /* Seek to the beginning of the segment */
    int ofst;
    ofst = i*sizeof(PageRecord) + sizeof(aMagic) + sizeof(Pgno);
    rc = sqliteOsSeek(pPager->jfd, ofst);
    if( rc!=SQLITE_OK ) break;
    rc = sqliteOsRead(pPager->jfd, &pgRec, sizeof(pgRec));
    if( rc!=SQLITE_OK ) break;

    /* Sanity checking on the page */

**    May you find forgiveness for yourself and forgive others.
**    May you share freely, never taking more than you give.
**
*************************************************************************
** This header file defines the interface that the SQLite library
** presents to client programs.
**
** @(#) $Id: sqlite.h.in,v 1.17 2001/09/16 00:13:27 drh Exp $
*/
#ifndef _SQLITE_H_
#define _SQLITE_H_
#include <stdarg.h>     /* Needed for the definition of va_list */

/*
** The version of the SQLite library.
................................................................................
#define SQLITE_ABORT     4    /* Callback routine requested an abort */
#define SQLITE_BUSY      5    /* One or more database files are locked */
#define SQLITE_NOMEM     6    /* A malloc() failed */
#define SQLITE_READONLY  7    /* Attempt to write a readonly database */
#define SQLITE_INTERRUPT 8    /* Operation terminated by sqlite_interrupt() */
#define SQLITE_IOERR     9    /* Some kind of disk I/O error occurred */
#define SQLITE_CORRUPT   10   /* The database disk image is malformed */
#define SQLITE_NOTFOUND  11   /* Table or record not found */
#define SQLITE_FULL      12   /* Insertion failed because database is full */
#define SQLITE_CANTOPEN  13   /* Unable to open the database file */
#define SQLITE_PROTOCOL  14   /* Database lock protocol error */
#define SQLITE_EMPTY     15   /* Database table is empty */
#define SQLITE_SCHEMA    16   /* The database schema changed */
#define SQLITE_TOOBIG    17   /* Too much data for one row of a table */

/* This function causes any pending database operation to abort and
** return at its earliest opportunity.  This routine is typically
** called in response to a user action such as pressing "Cancel"
** or Ctrl-C where the user wants a long query operation to halt

**    May you find forgiveness for yourself and forgive others.
**    May you share freely, never taking more than you give.
**
*************************************************************************
** This header file defines the interface that the SQLite library
** presents to client programs.
**
** @(#) $Id: sqlite.h.in,v 1.18 2001/09/20 01:44:43 drh Exp $
*/
#ifndef _SQLITE_H_
#define _SQLITE_H_
#include <stdarg.h>     /* Needed for the definition of va_list */

/*
** The version of the SQLite library.
................................................................................
#define SQLITE_ABORT     4    /* Callback routine requested an abort */
#define SQLITE_BUSY      5    /* One or more database files are locked */
#define SQLITE_NOMEM     6    /* A malloc() failed */
#define SQLITE_READONLY  7    /* Attempt to write a readonly database */
#define SQLITE_INTERRUPT 8    /* Operation terminated by sqlite_interrupt() */
#define SQLITE_IOERR     9    /* Some kind of disk I/O error occurred */
#define SQLITE_CORRUPT   10   /* The database disk image is malformed */
#define SQLITE_NOTFOUND  11   /* (Internal Only) Table or record not found */
#define SQLITE_FULL      12   /* Insertion failed because database is full */
#define SQLITE_CANTOPEN  13   /* Unable to open the database file */
#define SQLITE_PROTOCOL  14   /* Database lock protocol error */
#define SQLITE_EMPTY     15   /* (Internal Only) Database table is empty */
#define SQLITE_SCHEMA    16   /* The database schema changed */
#define SQLITE_TOOBIG    17   /* Too much data for one row of a table */

/* This function causes any pending database operation to abort and
** return at its earliest opportunity.  This routine is typically
** called in response to a user action such as pressing "Cancel"
** or Ctrl-C where the user wants a long query operation to halt

#    May you find forgiveness for yourself and forgive others.
#    May you share freely, never taking more than you give.
#
#***********************************************************************
# This file implements regression tests for SQLite library.  The
# focus of this file is testing the sqlite_*_printf() interface.
#
# $Id: printf.test,v 1.3 2001/09/16 00:13:28 drh Exp $

set testdir [file dirname $argv0]
source $testdir/tester.tcl

set n 1
foreach v {1 2 5 10 99 100 1000000 999999999 0 -1 -2 -5 -10 -99 -100 -9999999} {
  do_test printf-1.$n.1 [subst {
................................................................................
  }] [format {Three integers: (%06d) (%06x) (%06o)} $v $v $v]
  do_test printf-1.$n.6 [subst {
    sqlite_mprintf_int {Three integers: (% 6d) (% 6x) (% 6o)} $v $v $v
  }] [format {Three integers: (% 6d) (% 6x) (% 6o)} $v $v $v]
  incr n
}




set m 1
foreach {a b} {1 1 5 5 10 10 10 5} {
  set n 1
  foreach x {0.001 1.0e-20 1.0 0.0 100.0 9.99999 -0.00543 -1.0 -99.99999} {
    do_test printf-2.$m.$n.1 [subst {
      sqlite_mprintf_double {A double: %*.*f} $a $b $x
    }] [format {A double: %*.*f} $a $b $x]
................................................................................
    }] [format {A double: %d %d %g} $a $b $x]
    do_test printf-2.$m.$n.5 [subst {
      sqlite_mprintf_double {A double: %d %d %#g} $a $b $x
    }] [format {A double: %d %d %#g} $a $b $x]
    incr n
  }
  incr m


}

do_test printf-3.1 {
  sqlite_mprintf_str {A String: (%*.*s)} 10 10 {This is the string}
} [format {A String: (%*.*s)} 10 10 {This is the string}]
do_test printf-3.2 {
  sqlite_mprintf_str {A String: (%*.*s)} 10 5 {This is the string}

#    May you find forgiveness for yourself and forgive others.
#    May you share freely, never taking more than you give.
#
#***********************************************************************
# This file implements regression tests for SQLite library.  The
# focus of this file is testing the sqlite_*_printf() interface.
#
# $Id: printf.test,v 1.4 2001/09/20 01:44:43 drh Exp $

set testdir [file dirname $argv0]
source $testdir/tester.tcl

set n 1
foreach v {1 2 5 10 99 100 1000000 999999999 0 -1 -2 -5 -10 -99 -100 -9999999} {
  do_test printf-1.$n.1 [subst {
................................................................................
  }] [format {Three integers: (%06d) (%06x) (%06o)} $v $v $v]
  do_test printf-1.$n.6 [subst {
    sqlite_mprintf_int {Three integers: (% 6d) (% 6x) (% 6o)} $v $v $v
  }] [format {Three integers: (% 6d) (% 6x) (% 6o)} $v $v $v]
  incr n
}


if {$::tcl_platform(platform)!="windows"} {

set m 1
foreach {a b} {1 1 5 5 10 10 10 5} {
  set n 1
  foreach x {0.001 1.0e-20 1.0 0.0 100.0 9.99999 -0.00543 -1.0 -99.99999} {
    do_test printf-2.$m.$n.1 [subst {
      sqlite_mprintf_double {A double: %*.*f} $a $b $x
    }] [format {A double: %*.*f} $a $b $x]
................................................................................
    }] [format {A double: %d %d %g} $a $b $x]
    do_test printf-2.$m.$n.5 [subst {
      sqlite_mprintf_double {A double: %d %d %#g} $a $b $x
    }] [format {A double: %d %d %#g} $a $b $x]
    incr n
  }
  incr m
}

}

do_test printf-3.1 {
  sqlite_mprintf_str {A String: (%*.*s)} 10 10 {This is the string}
} [format {A String: (%*.*s)} 10 10 {This is the string}]
do_test printf-3.2 {
  sqlite_mprintf_str {A String: (%*.*s)} 10 5 {This is the string}

#    May you find forgiveness for yourself and forgive others.
#    May you share freely, never taking more than you give.
#
#***********************************************************************
# This file implements some common TCL routines used for regression
# testing the SQLite library
#
# $Id: tester.tcl,v 1.18 2001/09/16 00:13:28 drh Exp $

# Make sure tclsqlite was compiled correctly.  Abort now with an
# error message if not.
#
if {[sqlite -tcl-uses-utf]} {
  if {"\u1234"=="u1234"} {
    puts stderr "***** BUILD PROBLEM *****"
................................................................................
    puts stderr "and try again.\n**************************"
    exit 1
  }
}

# Create a test database
#

file delete -force ./test.db
file delete -force ./test.db-journal
sqlite db ./test.db

# Abort early if this script has been run before.
#
if {[info exists nTest]} return

# Set the test counters to zero

#    May you find forgiveness for yourself and forgive others.
#    May you share freely, never taking more than you give.
#
#***********************************************************************
# This file implements some common TCL routines used for regression
# testing the SQLite library
#
# $Id: tester.tcl,v 1.19 2001/09/20 01:44:43 drh Exp $

# Make sure tclsqlite was compiled correctly.  Abort now with an
# error message if not.
#
if {[sqlite -tcl-uses-utf]} {
  if {"\u1234"=="u1234"} {
    puts stderr "***** BUILD PROBLEM *****"
................................................................................
    puts stderr "and try again.\n**************************"
    exit 1
  }
}

# Create a test database
#
catch {db close}
file delete -force test.db
file delete -force test.db-journal
sqlite db ./test.db

# Abort early if this script has been run before.
#
if {[info exists nTest]} return

# Set the test counters to zero

Center
Inches
Letter  
100.00
Single
-2
1200 2
2 2 0 1 0 7 100 0 -1 0.000 0 0 -1 0 0 5
	 2550 450 4875 450 4875 1275 2550 1275 2550 450
2 1 0 3 0 7 100 0 -1 0.000 0 0 -1 1 0 2
	1 1 3.00 75.00 135.00
	 3675 8550 3675 9075
2 2 0 1 0 7 100 0 -1 0.000 0 0 -1 0 0 5
	 2550 9075 4875 9075 4875 9900 2550 9900 2550 9075
2 1 0 3 0 7 100 0 -1 0.000 0 0 -1 1 0 2
	1 1 3.00 75.00 135.00
	 3675 7200 3675 7725
2 2 0 1 0 7 100 0 -1 0.000 0 0 -1 0 0 5
	 2550 7725 4875 7725 4875 8550 2550 8550 2550 7725
2 1 0 3 0 7 100 0 -1 0.000 0 0 -1 1 0 2
	1 1 3.00 75.00 135.00
	 3675 5775 3675 6300
2 2 0 1 0 7 100 0 -1 0.000 0 0 -1 0 0 5
	 2550 6300 4875 6300 4875 7200 2550 7200 2550 6300
2 2 0 1 0 7 100 0 -1 0.000 0 0 -1 0 0 5
	 2550 4500 4875 4500 4875 5775 2550 5775 2550 4500
2 1 0 3 0 7 100 0 -1 0.000 0 0 -1 1 0 2
	1 1 3.00 75.00 135.00
	 3675 3975 3675 4500
2 1 0 3 0 7 100 0 -1 0.000 0 0 -1 1 0 2
	1 1 3.00 75.00 135.00
	 3675 2625 3675 3150
2 2 0 1 0 7 100 0 -1 0.000 0 0 -1 0 0 5
	 2550 3150 4875 3150 4875 3975 2550 3975 2550 3150
2 1 0 3 0 7 100 0 -1 0.000 0 0 -1 1 0 2
	1 1 3.00 75.00 135.00
	 3675 1275 3675 1800
2 2 0 1 0 7 100 0 -1 0.000 0 0 -1 0 0 5















	 2550 1800 4875 1800 4875 2625 2550 2625 2550 1800


4 1 0 100 0 0 20 0.0000 4 195 1020 3675 750 Interface\001
4 1 0 100 0 0 14 0.0000 4 195 2040 3675 1125 main.c table.c tclsqlite.c\001
4 1 0 100 0 0 20 0.0000 4 195 1920 3675 6675 Virtual Machine\001
4 1 0 100 0 0 14 0.0000 4 150 570 3675 7050 vdbe.c\001
4 1 0 100 0 0 20 0.0000 4 195 1830 3675 4875 Code Generator\001
4 1 0 100 0 0 14 0.0000 4 195 1860 3675 5175 build.c delete.c expr.c\001
4 1 0 100 0 0 14 0.0000 4 195 2115 3675 5400 insert.c select.c update.c\001
................................................................................
4 1 0 100 0 0 20 0.0000 4 195 1140 3675 2100 Tokenizer\001
4 1 0 100 0 0 14 0.0000 4 150 870 3675 2475 tokenize.c\001
4 1 0 100 0 0 20 0.0000 4 255 1350 3675 9375 Page Cache\001
4 1 0 100 0 0 14 0.0000 4 150 630 3675 3825 parse.y\001
4 1 0 100 0 0 14 0.0000 4 150 600 3675 8400 btree.c\001
4 1 0 100 0 0 14 0.0000 4 150 645 3675 9750 pager.c\001
4 1 0 100 0 0 20 0.0000 4 195 1620 3675 8025 B-tree Driver\001

Center
Inches
Letter  
100.00
Single
-2
1200 2


2 1 0 3 0 7 100 0 -1 0.000 0 0 -1 1 0 2
	1 1 3.00 75.00 135.00
	 3675 8550 3675 9075


2 1 0 3 0 7 100 0 -1 0.000 0 0 -1 1 0 2
	1 1 3.00 75.00 135.00
	 3675 7200 3675 7725


2 1 0 3 0 7 100 0 -1 0.000 0 0 -1 1 0 2
	1 1 3.00 75.00 135.00
	 3675 5775 3675 6300




2 1 0 3 0 7 100 0 -1 0.000 0 0 -1 1 0 2
	1 1 3.00 75.00 135.00
	 3675 3975 3675 4500
2 1 0 3 0 7 100 0 -1 0.000 0 0 -1 1 0 2
	1 1 3.00 75.00 135.00
	 3675 2625 3675 3150


2 1 0 3 0 7 100 0 -1 0.000 0 0 -1 1 0 2
	1 1 3.00 75.00 135.00
	 3675 1275 3675 1800
2 1 0 3 0 7 100 0 -1 0.000 0 0 -1 1 0 2
	1 1 3.00 75.00 135.00
	 3675 9900 3675 10425
2 2 0 1 0 11 100 0 20 0.000 0 0 7 0 0 5
	 2550 10425 4875 10425 4875 11250 2550 11250 2550 10425
2 2 0 1 0 11 100 0 20 0.000 0 0 7 0 0 5
	 2550 9075 4875 9075 4875 9900 2550 9900 2550 9075
2 2 0 1 0 11 100 0 20 0.000 0 0 7 0 0 5
	 2550 7725 4875 7725 4875 8550 2550 8550 2550 7725
2 2 0 1 0 11 100 0 20 0.000 0 0 7 0 0 5
	 2550 6300 4875 6300 4875 7200 2550 7200 2550 6300
2 2 0 1 0 11 100 0 20 0.000 0 0 7 0 0 5
	 2550 4500 4875 4500 4875 5775 2550 5775 2550 4500
2 2 0 1 0 11 100 0 20 0.000 0 0 7 0 0 5
	 2550 3150 4875 3150 4875 3975 2550 3975 2550 3150
2 2 0 1 0 11 100 0 20 0.000 0 0 7 0 0 5
	 2550 1800 4875 1800 4875 2625 2550 2625 2550 1800
2 2 0 1 0 11 100 0 20 0.000 0 0 7 0 0 5
	 2550 450 4875 450 4875 1275 2550 1275 2550 450
4 1 0 100 0 0 20 0.0000 4 195 1020 3675 750 Interface\001
4 1 0 100 0 0 14 0.0000 4 195 2040 3675 1125 main.c table.c tclsqlite.c\001
4 1 0 100 0 0 20 0.0000 4 195 1920 3675 6675 Virtual Machine\001
4 1 0 100 0 0 14 0.0000 4 150 570 3675 7050 vdbe.c\001
4 1 0 100 0 0 20 0.0000 4 195 1830 3675 4875 Code Generator\001
4 1 0 100 0 0 14 0.0000 4 195 1860 3675 5175 build.c delete.c expr.c\001
4 1 0 100 0 0 14 0.0000 4 195 2115 3675 5400 insert.c select.c update.c\001
................................................................................
4 1 0 100 0 0 20 0.0000 4 195 1140 3675 2100 Tokenizer\001
4 1 0 100 0 0 14 0.0000 4 150 870 3675 2475 tokenize.c\001
4 1 0 100 0 0 20 0.0000 4 255 1350 3675 9375 Page Cache\001
4 1 0 100 0 0 14 0.0000 4 150 630 3675 3825 parse.y\001
4 1 0 100 0 0 14 0.0000 4 150 600 3675 8400 btree.c\001
4 1 0 100 0 0 14 0.0000 4 150 645 3675 9750 pager.c\001
4 1 0 100 0 0 20 0.0000 4 195 1620 3675 8025 B-tree Driver\001
4 1 0 100 0 0 14 0.0000 4 105 345 3675 11100 os.c\001
4 1 0 100 0 0 20 0.0000 4 195 1470 3675 10725 OS Interface\001

#
# Run this Tcl script to generate the sqlite.html file.
#
set rcsid {$Id: arch.tcl,v 1.4 2001/09/16 00:13:29 drh Exp $}

puts {<html>
<head>
  <title>Architecture of SQLite</title>
</head>
<body bgcolor=white>
<h1 align=center>
................................................................................
puts {
<h2>Introduction</h2>

<table align="right" border="1" cellpadding="15" cellspacing="1">
<tr><th>Block Diagram Of SQLite</th></tr>
<tr><td><img src="arch.png"></td></tr>
</table>
<p>This file describes the architecture of the SQLite library.





A block diagram showing the main components of SQLite
and how they interrelate is shown at the right.  The text that
follows will provide a quick overview of each of these components.
</p>

<h2>Interface</h2>

................................................................................
<b>sqlite_get_table()</b> routine is implemented in <b>table.c</b>.
The Tcl interface is implemented by <b>tclsqlite.c</b>.  More
information on the C interface to SQLite is
<a href="c_interface.html">available separately</a>.<p>

<p>To avoid name collisions with other software, all external
symbols in the SQLite library begin with the prefix <b>sqlite</b>.
Those symbols that are intended for external use (as oppose to
those which are for internal use only but which have to be exported
do to limitations of the C linker's scoping mechanism) begin
with <b>sqlite_</b>.</p>

<h2>Tokenizer</h2>

<p>When a string containing SQL statements is to be executed, the
interface passes that string to the tokenizer.  The job of the tokenizer
is to break the original string up into tokens and pass those tokens
one by one to the parser.  The tokenizer is hand-coded in C.
(There is no "lex" code here.)  All of the code for the tokenizer
is contained in the <b>tokenize.c</b> source file.</p>

<p>Note that in this design, the tokenizer calls the parser.  People
who are familiar with YACC and BISON may be used to doing things the
other way around -- having the parser call the tokenizer.  This author

as done it both ways, and finds things generally work out nicer for
the tokenizer to call the parser.  YACC has it backwards.</p>

<h2>Parser</h2>

<p>The parser is the piece that assigns meaning to tokens based on
their context.  The parser for SQLite is generated using the
<a href="http://www.hwaci.com/sw/lemon/">Lemon</a> LALR(1) parser
................................................................................
<p>An SQLite database is maintained on disk using a B-tree implementation
found in the <b>btree.c</b> source file.  A separate B-tree is used for
each table and index in the database but all B-trees are stored in the
same disk file.  Each page of a B-tree is 1024 bytes in size.  The data
is stored with the key in an area called "payload".  Up to 236 bytes of
payload can be stored with each B-tree entry.  Any additional payload
is stored in a chain of overflow pages.</p>





<h2>Page Cache</h2>




<p>The page cache provides the rollback and atomic commit abstraction
and takes care of reader/writer locking of the database file.  The
B-tree driver requests particular pages from the page cache and notifies
the page cache when it wants to modify pages and commit or rollback its
changes and the page cache handles all the messy details of making sure
the requests are handled quickly, safely, and efficiently.</p>


















}

puts {
<br clear="both" />
<p><hr /></p>
<p><a href="index.html"><img src="/goback.jpg" border=0 />
Back to the SQLite Home Page</a>
</p>

</body></html>}

#
# Run this Tcl script to generate the sqlite.html file.
#
set rcsid {$Id: arch.tcl,v 1.5 2001/09/20 01:44:43 drh Exp $}

puts {<html>
<head>
  <title>Architecture of SQLite</title>
</head>
<body bgcolor=white>
<h1 align=center>
................................................................................
puts {
<h2>Introduction</h2>

<table align="right" border="1" cellpadding="15" cellspacing="1">
<tr><th>Block Diagram Of SQLite</th></tr>
<tr><td><img src="arch.png"></td></tr>
</table>
<p>This document describes the architecture of the SQLite library.
The information here is useful to those who want to understand or
modify the inner workings of SQLite.
</p>

<p>
A block diagram showing the main components of SQLite
and how they interrelate is shown at the right.  The text that
follows will provide a quick overview of each of these components.
</p>

<h2>Interface</h2>

................................................................................
<b>sqlite_get_table()</b> routine is implemented in <b>table.c</b>.
The Tcl interface is implemented by <b>tclsqlite.c</b>.  More
information on the C interface to SQLite is
<a href="c_interface.html">available separately</a>.<p>

<p>To avoid name collisions with other software, all external
symbols in the SQLite library begin with the prefix <b>sqlite</b>.
Those symbols that are intended for external use (in other words,
those symbols which form the API for SQLite) begin

with <b>sqlite_</b>.</p>

<h2>Tokenizer</h2>

<p>When a string containing SQL statements is to be executed, the
interface passes that string to the tokenizer.  The job of the tokenizer
is to break the original string up into tokens and pass those tokens
one by one to the parser.  The tokenizer is hand-coded in C.
All of the code for the tokenizer
is contained in the <b>tokenize.c</b> source file.</p>

<p>Note that in this design, the tokenizer calls the parser.  People
who are familiar with YACC and BISON may be used to doing things the
other way around -- having the parser call the tokenizer.  The author
of SQLite 
has done it both ways and finds things generally work out nicer for
the tokenizer to call the parser.  YACC has it backwards.</p>

<h2>Parser</h2>

<p>The parser is the piece that assigns meaning to tokens based on
their context.  The parser for SQLite is generated using the
<a href="http://www.hwaci.com/sw/lemon/">Lemon</a> LALR(1) parser
................................................................................
<p>An SQLite database is maintained on disk using a B-tree implementation
found in the <b>btree.c</b> source file.  A separate B-tree is used for
each table and index in the database but all B-trees are stored in the
same disk file.  Each page of a B-tree is 1024 bytes in size.  The data
is stored with the key in an area called "payload".  Up to 236 bytes of
payload can be stored with each B-tree entry.  Any additional payload
is stored in a chain of overflow pages.</p>

<p>The interface to the B-tree subsystem is defined by the header file
<b>btree.h</b>.
</p>

<h2>Page Cache</h2>

<p>The B-tree module requests information from the disk in 1024 byte
chunks.  The page cache is reponsible for reading, writing, and
caching these chunks for the B-tree module.
The page cache also provides the rollback and atomic commit abstraction
and takes care of reader/writer locking of the database file.  The
B-tree driver requests particular pages from the page cache and notifies
the page cache when it wants to modify pages and commit or rollback its
changes and the page cache handles all the messy details of making sure
the requests are handled quickly, safely, and efficiently.</p>

<p>The code to implement the page cache is contained in the single C
source file <b>pager.c</b>.  The interface to the page cache subsystem
is defined by the header file <b>pager.h</b>.
</p>

<h2>OS Interface</h2>

<p>
In order to provide portability between POSIX and Win32 operating systems,
SQLite uses an abstraction layer to interace with the operating system.
The <b>os.c</b> file contains about 20 routines used for opening and
closing files, deleting files, creating and deleting locks on files,
flushing the disk cache, and so forth.  Each of these functions contains
two implementations separated by #ifdefs: one for POSIX and the other
for Win32.  The interface to the OS abstraction layer is defined by
the <b>os.h</b> header file.
</p>
}

puts {
<br clear="both" />
<p><hr /></p>
<p><a href="index.html"><img src="/goback.jpg" border=0 />
Back to the SQLite Home Page</a>
</p>

</body></html>}

#
# Run this Tcl script to generate the sqlite.html file.
#
set rcsid {$Id: c_interface.tcl,v 1.14 2001/04/05 16:25:53 drh Exp $}

puts {<html>
<head>
  <title>The C language interface to the SQLite library</title>
</head>
<body bgcolor=white>
<h1 align=center>
................................................................................
  char *sql,
  int (*)(void*,int,char**,char**),
  void*,
  char **errmsg
);

#define SQLITE_OK        0    /* Successful result */
#define SQLITE_INTERNAL  1    /* An internal logic error in SQLite */
#define SQLITE_ERROR     2    /* SQL error or missing database */
#define SQLITE_PERM      3    /* Access permission denied */
#define SQLITE_ABORT     4    /* Callback routine requested an abort */
#define SQLITE_BUSY      5    /* One or more database files are locked */
#define SQLITE_NOMEM     6    /* A malloc() failed */
#define SQLITE_READONLY  7    /* Attempt to write a readonly database */








</pre></blockquote>

<p>Only the three core routines shown above are required to use
SQLite.  But there are many other functions that provide 
useful interfaces.  These extended routines are as follows:
</p>

................................................................................
<p>All of the above definitions are included in the "sqlite.h"
header file that comes in the source tree.</p>

<h2>Opening a database</h2>

<p>Use the <b>sqlite_open()</b> function to open an existing SQLite
database or to create a new SQLite database.  The first argument
is the database name.  The second argument is a constant 0666 to
open the database for reading and writing and 0444 to open the


database read only.  The third argument is a pointer to a string
pointer.  If the third argument is not NULL and an error occurs
while trying to open the database, then an error message will be
written to memory obtained from malloc() and *errmsg will be made
to point to this error message.  The calling function is responsible
for freeing the memory when it has finished with it.</p>

<p>The name of an SQLite database is normally the name of a directory
that contains a collection of GDBM files that comprise the database.
There is one GDBM file for each table and index in the
database.  All GDBM files end with the ".tbl" suffix.  Every SQLite
database also contains a special database table named <b>sqlite_master</b>
stored in its own GDBM file.  This special table records the database
schema.</p>


<p>To create a new SQLite database, all you have to do is call
<b>sqlite_open()</b> with the first parameter set to the name of
an empty directory and the second parameter set to 0666.  The
directory is created automatically if it does not already exist.</p>

<p>Beginning with SQLite version 1.0.14, SQLite supports database
backends other than GDBM.  The only backends currently supported 
are the default GDBM driver and an in-memory hash table database.
You may anticipate additional backends in future versions of SQLite.</p>

<p>An alternative database backend is specified by prepending the
backend name and a colon to the database name argument of the
<b>sqlite_open()</b> function.  For the GDBM backend, you can
prepend "<b>gdbm:</b>" to the directory name.  To select the in-memory
hash table backend, prepend "<b>memory:</b>" to the database name.
Future database drivers will be selected by a similar mechanism.</p>

<p>The return value of the <b>sqlite_open()</b> function is a
pointer to an opaque <b>sqlite</b> structure.  This pointer will
be the first argument to all subsequent SQLite function calls that
deal with the same database.  NULL is returned if the open fails
for any reason.</p>

<h2>Closing the database</h2>

<p>To close an SQLite database, call the <b>sqlite_close()</b>
function passing it the sqlite structure pointer that was obtained
from a prior call to <b>sqlite_open</b>.



<h2>Executing SQL statements</h2>

<p>The <b>sqlite_exec()</b> function is used to process SQL statements
and queries.  This function requires 5 parameters as follows:</p>

<ol>
................................................................................
mailing list.
</p></dd>
<dt>SQLITE_ERROR</dt>
<dd><p>This return value indicates that there was an error in the SQL
that was passed into the <b>sqlite_exec()</b>.
</p></dd>
<dt>SQLITE_PERM</dt>
<dd><p>This return value says that the access permissions on one of the
GDBM files is such that the file cannot be opened.
</p></dd>
<dt>SQLITE_ABORT</dt>
<dd><p>This value is returned if the callback function returns non-zero.
</p></dd>
<dt>SQLITE_BUSY</dt>
<dd><p>This return code indicates that one of the underlying GDBM files
is locked because it is currently being accessed by another thread or
process.  GDBM allows mutiple readers of the same file, but only one
writer.  So multiple processes can query an SQLite database at once.
But only a single process can write to an SQLite database at one time.
If an attempt is made to write to an SQLite database that another
process is currently reading, the write is not performed and 
<b>sqlite_exec()</b> returns SQLITE_BUSY.  Similarly, an attempt to read
an SQLite database that is currently being written by another process
will return SQLITE_BUSY.  In both cases, the write or query attempt
can be retried after the other process finishes.</p>
<p>Note that locking is done at the file level.  One process can
write to table ABC (for example) while another process simultaneously
reads from a different table XYZ.  But you cannot have two processes reading
and writing table ABC at the same time.
</p></dd>
<dt>SQLITE_NOMEM</dt>
<dd><p>This value is returned if a call to <b>malloc()</b> fails.
</p></dd>
<dt>SQLITE_READONLY</dt>
<dd><p>This return code indicates that an attempt was made to write to
a database file that was originally opened for reading only.  This can
happen if the callback from a query attempts to update the table
being queried.
</p></dd>
<dt>SQLITE_INTERRUPT</dt>
<dd><p>This value is returned if a call to <b>sqlite_interrupt()</b>
interrupts a database operation in progress.
</p></dd>







































</dl>
</blockquote>

<h2>Querying without using a callback function</h2>

<p>The <b>sqlite_get_table()</b> function is a wrapper around
<b>sqlite_exec()</b> that collects all the information from successive
................................................................................
<p>Note that the character encoding mechanism used by SQLite cannot
be changed at run-time.  This is a compile-time option only.  The
<b>sqlite_encoding</b> character string just tells you how the library
was compiled.</p>

<h2>Changing the libraries response to locked files</h2>

<p>The GDBM library supports database locks at the file level.
If a GDBM database file is opened for reading, then that same
file cannot be reopened for writing until all readers have closed
the file.  If a GDBM file is open for writing, then the file cannot
be reopened for reading or writing until it is closed.</p>

<p>If the SQLite library attempts to open a GDBM file and finds that
the file is locked, the default action is to abort the current
operation and return SQLITE_BUSY.  But this is not always the most
convenient behavior, so a mechanism exists to change it.</p>

<p>The <b>sqlite_busy_handler()</b> procedure can be used to register
a busy callback with an open SQLite database.  The busy callback will
be invoked whenever SQLite tries to open a GDBM file that is locked.
The callback will typically do some other useful work, or perhaps sleep,
in order to give the lock a chance to clear.  If the callback returns
non-zero, then SQLite tries again to open the GDBM file and the cycle
repeats.  If the callback returns zero, then SQLite aborts the current
operation and returns SQLITE_BUSY.</p>

<p>The arguments to <b>sqlite_busy_handler()</b> are the opaque
structure returned from <b>sqlite_open()</b>, a pointer to the busy
callback function, and a generic pointer that will be passed as
the first argument to the busy callback.  When SQLite invokes the

#
# Run this Tcl script to generate the sqlite.html file.
#
set rcsid {$Id: c_interface.tcl,v 1.15 2001/09/20 01:44:43 drh Exp $}

puts {<html>
<head>
  <title>The C language interface to the SQLite library</title>
</head>
<body bgcolor=white>
<h1 align=center>
................................................................................
  char *sql,
  int (*)(void*,int,char**,char**),
  void*,
  char **errmsg
);

#define SQLITE_OK        0    /* Successful result */
#define SQLITE_ERROR     1    /* SQL error or missing database */
#define SQLITE_INTERNAL  2    /* An internal logic error in SQLite */
#define SQLITE_PERM      3    /* Access permission denied */
#define SQLITE_ABORT     4    /* Callback routine requested an abort */
#define SQLITE_BUSY      5    /* One or more database files are locked */
#define SQLITE_NOMEM     6    /* A malloc() failed */
#define SQLITE_READONLY  7    /* Attempt to write a readonly database */
#define SQLITE_INTERRUPT 8    /* Operation terminated by sqlite_interrupt() */
#define SQLITE_IOERR     9    /* Some kind of disk I/O error occurred */
#define SQLITE_CORRUPT   10   /* The database disk image is malformed */
#define SQLITE_FULL      12   /* Insertion failed because database is full */
#define SQLITE_CANTOPEN  13   /* Unable to open the database file */
#define SQLITE_PROTOCOL  14   /* Database lock protocol error */
#define SQLITE_SCHEMA    16   /* The database schema changed */
#define SQLITE_TOOBIG    17   /* Too much data for one row of a table */
</pre></blockquote>

<p>Only the three core routines shown above are required to use
SQLite.  But there are many other functions that provide 
useful interfaces.  These extended routines are as follows:
</p>

................................................................................
<p>All of the above definitions are included in the "sqlite.h"
header file that comes in the source tree.</p>

<h2>Opening a database</h2>

<p>Use the <b>sqlite_open()</b> function to open an existing SQLite
database or to create a new SQLite database.  The first argument
is the database name.  The second argument is intended to signal
whether the database is going to be used for reading and writing
or just for reading.  But in the current implementation, the
second argument to <b>sqlite_open</b> is ignored.
The third argument is a pointer to a string pointer.
If the third argument is not NULL and an error occurs
while trying to open the database, then an error message will be
written to memory obtained from malloc() and *errmsg will be made
to point to this error message.  The calling function is responsible
for freeing the memory when it has finished with it.</p>

<p>The name of an SQLite database is the name of a file that will


contain the database.  If the file does not exist, SQLite attempts
to create and initialize it.  If the file is read-only (due to
permission bits or because it is located on read-only media like
a CD-ROM) then SQLite opens the database for reading only.  The

entire SQL database is stored in a single file on the disk.  But
additional temporary files may be created during the execution of
an SQL command in order to store the database rollback journal or
temporary and intermediate results of a query.</p>














<p>The return value of the <b>sqlite_open()</b> function is a
pointer to an opaque <b>sqlite</b> structure.  This pointer will
be the first argument to all subsequent SQLite function calls that
deal with the same database.  NULL is returned if the open fails
for any reason.</p>

<h2>Closing the database</h2>

<p>To close an SQLite database, call the <b>sqlite_close()</b>
function passing it the sqlite structure pointer that was obtained
from a prior call to <b>sqlite_open</b>.
If a transaction is active when the database is closed, the transaction
is rolled back.</p>

<h2>Executing SQL statements</h2>

<p>The <b>sqlite_exec()</b> function is used to process SQL statements
and queries.  This function requires 5 parameters as follows:</p>

<ol>
................................................................................
mailing list.
</p></dd>
<dt>SQLITE_ERROR</dt>
<dd><p>This return value indicates that there was an error in the SQL
that was passed into the <b>sqlite_exec()</b>.
</p></dd>
<dt>SQLITE_PERM</dt>
<dd><p>This return value says that the access permissions on the database
file are such that the file cannot be opened.
</p></dd>
<dt>SQLITE_ABORT</dt>
<dd><p>This value is returned if the callback function returns non-zero.
</p></dd>
<dt>SQLITE_BUSY</dt>
<dd><p>This return code indicates that another program or thread has
the database locked.  SQLite allows two or more threads to read the
database at the same time, but only one thread can have the database
open for writing at the same time.  Locking in SQLite is on the
entire database.</p>










</p></dd>
<dt>SQLITE_NOMEM</dt>
<dd><p>This value is returned if a call to <b>malloc()</b> fails.
</p></dd>
<dt>SQLITE_READONLY</dt>
<dd><p>This return code indicates that an attempt was made to write to
a database file that is opened for reading only.


</p></dd>
<dt>SQLITE_INTERRUPT</dt>
<dd><p>This value is returned if a call to <b>sqlite_interrupt()</b>
interrupts a database operation in progress.
</p></dd>
<dt>SQLITE_IOERR</dt>
<dd><p>This value is returned if the operating system informs SQLite
that it is unable to perform some disk I/O operation.  This could mean
that there is no more space left on the disk.
</p></dd>
<dt>SQLITE_CORRUPT</dt>
<dd><p>This value is returned if SQLite detects that the database it is
working on has become corrupted.  Corruption might occur due to a rogue
process writing to the database file or it might happen due to an 
perviously undetected logic error in of SQLite. 
</p></dd>
<dt>SQLITE_FULL</dt>
<dd><p>This value is returned if an insertion failed because there is
no space left on the disk, or the database is too big to hold any
more information.  The latter case should only occur for databases
that are larger than 2GB in size.
</p></dd>
<dt>SQLITE_CANTOPEN</dt>
<dd><p>This value is returned if the database file could not be opened
for some reason.
</p></dd>
<dt>SQLITE_PROTOCOL</dt>
<dd><p>This value is returned if some other process is messing with
file locks and has violated the file locking protocol that SQLite uses
on its rollback journal files.
</p></dd>
<dt>SQLITE_SCHEMA</dt>
<dd><p>When the database first opened, SQLite reads the database schema
into memory and uses that schema to parse new SQL statements.  If another
process changes the schema, the command currently being processed will
abort because the virtual machine code generated assumed the old
schema.  This is the return code for such cases.  Retrying the
command usually will clear the problem.
</p></dd>
<dt>SQLITE_TOOBIG</dt>
<dd><p>SQLite cannot store more than about 64K of data in a single row
of a single table.  If you attempt to store more than 64K in a single
row, this is the return code you get.
</p></dd>
</dl>
</blockquote>

<h2>Querying without using a callback function</h2>

<p>The <b>sqlite_get_table()</b> function is a wrapper around
<b>sqlite_exec()</b> that collects all the information from successive
................................................................................
<p>Note that the character encoding mechanism used by SQLite cannot
be changed at run-time.  This is a compile-time option only.  The
<b>sqlite_encoding</b> character string just tells you how the library
was compiled.</p>

<h2>Changing the libraries response to locked files</h2>












<p>The <b>sqlite_busy_handler()</b> procedure can be used to register
a busy callback with an open SQLite database.  The busy callback will
be invoked whenever SQLite tries to open a locked that is locked.
The callback will typically do some other useful work, or perhaps sleep,
in order to give the lock a chance to clear.  If the callback returns
non-zero, then SQLite tries again to open the database and the cycle
repeats.  If the callback returns zero, then SQLite aborts the current
operation and returns SQLITE_BUSY.</p>

<p>The arguments to <b>sqlite_busy_handler()</b> are the opaque
structure returned from <b>sqlite_open()</b>, a pointer to the busy
callback function, and a generic pointer that will be passed as
the first argument to the busy callback.  When SQLite invokes the

#
# Run this Tcl script to generate the fileformat.html file.
#
set rcsid {$Id: fileformat.tcl,v 1.4 2000/08/04 13:49:03 drh Exp $}

puts {<html>
<head>
  <title>The SQLite file format</title>
</head>
<body bgcolor=white>
<h1 align=center>
The SQLite File Format
</h1>}
puts "<p align=center>
(This page was last modified on [lrange $rcsid 3 4] GMT)
</p>"

puts {
<p>SQLite stores each SQL table and index in a separate GDBM file.
The name of the GDBM file used to store a particular table is usually
just the table name with "<b>.tbl</b>" appended.
Consider an example:</p>
}

proc Code {body} {
  puts {<blockquote><pre>}
  regsub -all {&} [string trim $body] {\&amp;} body
  regsub -all {>} $body {\&gt;} body
  regsub -all {<} $body {\&lt;} body
  regsub -all {\(\(\(} $body {<font color="#00671f"><u>} body
  regsub -all {\)\)\)} $body {</u></font>} body
  puts $body
  puts {</pre></blockquote>}
}

Code {
$ (((rm -rf ex1)))
$ (((sqlite ex1)))
Enter ".help" for instructions
sqlite> (((create table tbl1(one varchar(10), two smallint);)))
sqlite> (((create index idx1 on tbl1(one);)))
sqlite> (((insert into tbl1 values('hello!',10);)))
sqlite> (((.exit)))
$ ls ex1
idx1.tbl  sqlite_master.tbl  tbl1.tbl
$
}

puts {
<p>The example above creates a new SQL database with a single
table named <b>tbl1</b> and a single index named <b>idx1</b>.
Three files were created for this database.  <b>tbl1.tbl</b> stores
all the data for the <b>tbl1</b> table and <b>idx1.tbl</b> stores
all the information needed by the index <b>idx1</b>.  The remaining file
<b>sqlite_master.tbl</b> holds the data for the special
built-in table called <b>sqlite_master</b>.  Every SQLite database
has an <b>sqlite_master</b> table.  This table contains the schema
for the database.  You can query the <b>sqlite_master</b> table
using ordinary SQL commands, but you cannot write to the
<b>sqlite_master</b> table.</p>

<p>The GDBM file used to store an SQL table is <em>usually</em>
just the name of the table with <b>.tbl</b> appended.  But there
are exceptions.  First, the name of the table is converted to
all lower case letters before being used to construct the filename.
This is because SQL table names are not case sensitive but Unix filenames are.
Second, if the table name contains any characters other than 
alphanumerics and underscores, the exceptional characters are encoded
as a single '+' sign.  For example:</p>
}

Code {
$ (((sqlite ex1)))
sqlite> (((create table 'Strange Table Name!'(a int, b char(30));)))
sqlite> .exit
$ (((ls ex1)))
idx1.tbl sqlite_master.tbl strange+table+name+.tbl tbl1.tbl
$
}

puts {
<h2>SQL Table File Format</h2>

<p>Each record of a GDBM file contains a key and a data.
Both key and data are arbitary bytes of any length.  The information
from an SQL table is mapped into a GDBM file as follows:</p>

<p>The GDBM key for each record of an SQL table file is a
randomly chosen integer.  The key size thus depends on the size
of an integer on the host computer.  (Typically this means "4 bytes".)
</p>

<p>If the SQL table contains N columns, then the data entry
for each record begins with N integers.  Each integer is the
offset in bytes from the beginning of the GDBM data to the 
start of the data for the corresponding column.  If the column
contains a NULL value, then its corresponding integer will
be zero.  All column data is stored as null-terminated ASCII
text strings.</p>

<p>Consider a simple example:</p>
}

Code {
$ (((rm -rf ex1)))
$ (((sqlite ex1)))
sqlite> (((create table t1(a int, b text, c text);)))
sqlite> (((insert into t1 values(10,NULL,'hello!');)))
sqlite> (((insert into t1 values(-11,'this is','a test');)))
sqlite> (((.exit)))
$ (((gdbmdump ex1/t1.tbl)))
key  : 6d1a6e03                                      m.n.
data : 0c000000 10000000 18000000 2d313100 74686973  ............-11.this
       20697300 61207465 737400                       is.a test.

key  : 6d3f90e2                                      m?..
data : 0c000000 00000000 0f000000 31300068 656c6c6f  ............10.hello
       2100                                          !.

$
}

puts {
<p>In the example above, we have created a new table named <b>t1</b>
that contains two records. The <b>gdbmdump</b> program is used to
dump the contents of the <b>t1</b> GDBM file
in a human readable format.  The source code to <b>gdbmdump</b>
is included with the SQLite distribution.  Just type "make gdbmdump"
to build it.</p>

<p>We can see in the dump of <b>t1</b> that each record
is a separate GDBM entry with a 4-byte random key.  The keys
shown are for a single sample run. If you try
this experiment yourself, you will probably get completely different
keys.<p>

<p>Because the <b>t1</b> table contains 3 columns, the data part of
each record begins with 3 integers.  In both records of the example,
the first integer
has the value 12 since the beginning of the data for the first column
begins on the 13th byte of the record.  You can see how each column's
data is stored as a null-terminated string.  For the second record,
observe that the offset integer is zero for the second column.  This
indicates that the second column contains NULL data.</p>

<h2>SQL Index File Format</h2>

<p>Each SQL index is also represented using a single GDBM file.
There is one entry in the GDBM file for each unique SQL key in the
table that is being indexed.  The GDBM key is an
arbitrary length null-terminated string which is SQL key that
is used by the index.  The data is a list of integers that correspond
to GDBM keys of entries in data table that have the corresponding
SQL key.  If the data record of the index is exactly 4 bytes in size,
then the data represents a single integer key.  If the data is greater
than 4 bytes in size, then the first 4 bytes form an integer that
tells us how many keys are in the data.  The index data record is
always sized to be a power of 2.  Unused slots at the end of the
index data record are filled with zero.</p>

<p>To illustrate, we will create an index on the example table
shown above, and add a new entry to this table that has a duplicate
SQL key.</p>
}

Code {
$ (((sqlite ex1)))
sqlite> (((create index i1 on t1(a);)))
sqlite> (((insert into t1 values(10,'another','record');)))
sqlite> (((.exit)))
$ (((gdbmdump ex1/t1.tbl)))
key  : 223100ae                                      "1..
data : 0c000000 10000000 18000000 2d313100 74686973  ............-11.this
       20697300 61207465 737400                       is.a test.

key  : a840e996                                      .@..
data : 0c000000 00000000 0f000000 31300068 656c6c6f  ............10.hello
       2100                                          !.

key  : c19e3119                                      ..1.
data : 0c000000 0f000000 17000000 31300061 6e6f7468  ............10.anoth
       65720072 65636f72 6400                        er.record.
$
}

puts {
<p>We added the new record to the <b>t1</b> table because we wanted to
have two records with the same value on column <b>a</b> since that
column is used by the <b>i1</b> index.  You can see from the dump
above that the new <b>t1</b> record is assigned another random
GDBM key.</p>

<p>Now let's look at a dump of the index file.</p>
}

Code {
$ (((gdbmdump ex1/i1.tbl)))
key  : 313000                                        10.
data : 02000000 45b4f724 6d3f90e2 00000000           ....E..$m?......

key  : 2d313100                                      -11.
data : 6d1a6e03                                      m.n.

$
}

puts {
<p>The GDBM file for the index contains only two records because
the <b>t1</b> table contains only two distinct values for
column <b>a</b>.  You can see that the GDBM keys for each record
are just the text values for <b>a</b> columns of table <b>t1</b>.
The data for each record of the index is a list of integers
where each integer is the GDBM key for an entry in the <b>t1</b>
table that has the corresponding value for the <b>a</b> column.</p>
The index entry for -11 contains only a single entry and is 4
bytes in size.  The index entry for 10 is 16 bytes in size but
contains only 2 entries.  The first integer is the number of
entires.  The two integer keys follow.  The last 4 bytes are unused.
}

puts {
<p><hr /></p>
<p><a href="index.html"><img src="/goback.jpg" border=0 />
Back to the SQLite Home Page</a>
</p>

</body></html>}

#
# Run this TCL script to generate HTML for the index.html file.
#
set rcsid {$Id: index.tcl,v 1.38 2001/09/16 00:13:29 drh Exp $}

puts {<html>
<head><title>SQLite: An SQL Database Library Built Atop GDBM</title></head>
<body bgcolor=white>
<h1 align=center>SQLite: An SQL Database Library Built Atop
<a href="http://www.gnu.org/software/gdbm/gdbm.html">GDBM</a></h1>
<p align=center>}
puts "This page was last modified on [lrange $rcsid 3 4] GMT<br>"
set vers [lindex $argv 0]
puts "The latest SQLite version is <b>$vers</b>"
puts " created on [exec cat last_change] GMT"
puts {</p>}

................................................................................
Programs that link with the SQLite library can have SQL database
access without running a separate RDBMS process.
The distribution comes with a standalone command-line
access program (<a href="sqlite.html">sqlite</a>) that can
be used to administer an SQLite database and which serves as
an example of how to use the SQLite library.</p>






<h2>Features</h2>

<p><ul>
<li>Implements a large subset of SQL92.</li>
<li>A complete SQL database (with multiple tables and indices) is
    stored in a single disk file.</li>
................................................................................
<li>Small memory footprint: about 12000 lines of C code.</li>
<li>Very simple 
<a href="c_interface.html">C/C++ interface</a> requires the use of only
three functions and one opaque structure.</li>
<li>A TCL interface to the library is included.</li>
<li>A TCL-based test suite provides near 100% code coverage.</li>
<li>Self-contained: no external dependencies.</li>
<li>Built and tested under Linux, HPUX, and WinNT.</li>

</ul>
</p>
































<h2>Current Status</h2>

<p>A <a href="changes.html">change history</a> is available online.
There are currently no <em>known</em> memory leaks or debilitating bugs
in the library.  <a href="http://gcc.gnu.org/onlinedocs/gcov_1.html">Gcov</a>
is used to verify test coverage.  The test suite currently exercises
all code except for a few areas which are unreachable or which are
only reached when <tt>malloc()</tt> fails.</p>

<p>Known bugs:</p>

<ul>
<li><p>
  The LIKE operator is suppose to ignore case. 
  But it only ignores case for 7-bit Latin characters.
................................................................................

<p><ul>
<li>Information on the <a href="sqlite.html">sqlite</a>
    command-line utility.</li>
<li>The <a href="lang.html">SQL Language</a> subset understood by SQLite.</li>
<li>The <a href="c_interface.html">C/C++ Interface</a>.</li>
<li>The <a href="tclsqlite.html">Tcl Interface</a>.</li>
<li>The <a href="fileformat.html">file format</a> used by SQLite databases.</li>
<li>The <a href="arch.html">Architecture of the SQLite Library</a> describes
    how the library is put together.</li>
<li>A description of the <a href="opcode.html">virtual machine</a> that
    SQLite uses to access the database.</li>
<li>Instructions for building 
    <a href="crosscompile.html">SQLite for Win98/NT</a> using the
    MinGW cross-compiler.  There are also instructions on
    <a href="mingw.html">building MinGW</a> in case you don't already have
    a copy.</li>
</ul>
</p>

<p>The SQLite source code is 35% comment.  These comments are
another important source of information. </p>
}

puts {







<a name="mailinglist" />
<h2>Mailing List</h2>
<p>A mailing list has been set up on eGroups for discussion of
SQLite design issues or for asking questions about SQLite.</p>
<center>
<a href="http://www.egroups.com/subscribe/sqlite">
<img src="http://www.egroups.com/img/ui/join.gif" border=0 /><br />
Click to subscribe to sqlite</a>
</center>}

puts {<h2>Download</h2>

<p>You can download a tarball containing all source
code for SQLite
}
puts "version $vers"
puts {
(including the TCL scripts that generate the
HTML files for this website) at <a href="sqlite.tar.gz">sqlite.tar.gz</a>.}
puts "This is a [file size sqlite.tar.gz] byte download."
puts {</p>

<p>To build sqlite under Unix, just unwrap the tarball, create a separate
build directory, run configure from the build directory and then
type "make".  For example:</p>

<blockquote><pre>
$ tar xzf sqlite.tar.gz      <i> Unpacks into directory named "sqlite" </i>
$ mkdir bld                  <i> Create a separate build directory </i>
$ cd bld
$ ../sqlite/configure
$ make                       <i> Builds "sqlite" and "libsqlite.a" </i>
$ make test                  <i> Optional: run regression tests </i>
</pre></blockquote>

<p>Instructions for building SQLite for WindowsNT are
found <a href="crosscompile.html">here</a>.





}

puts {<h2>Command-line Usage Example</h2>

<p>Download the source archive and compile the <b>sqlite</b>
program as described above.  Then type:</p>

................................................................................
sqlite> .exit
base$
</pre></blockquote>
}
puts {<h2>Related Sites</h2>

<ul>
<li><p>The canonical site for GDBM is
       <a href="http://www.gnu.org/software/gdbm/gdbm.html">
       http://www.gnu.org/software/gdbm/gdbm.html</a></p></li>

<li><p>Someday, we would like to port SQLite to work with
       the Berkeley DB library in addition to GDBM.  For information
       about the Berkeley DB library, see
       <a href="http://www.sleepycat.com/">http://www.sleepycat.com/</a>
       </p></li>

<li><p>Here is a good <a href="http://w3.one.net/~jhoffman/sqltut.htm">
       tutorial on SQL</a>.</p></li>

<li><p><a href="http://www.postgresql.org/">PostgreSQL</a> is a
       full-blown SQL RDBMS that is also open source.</p></li>

<li><p><a href="http://www.chordate.com/gadfly.html">Gadfly</a> is another
       SQL library, similar to SQLite, except that Gadfly is written
       in Python.</p></li>

<li><p><a href="http://www.vogel-nest.de/tcl/qgdbm.html">Qgdbm</a> is
       a wrapper around 
       <a href="http://www.vogel-nest.de/tcl/tclgdbm.html">tclgdbm</a>
       that provides SQL-like access to GDBM files.</p></li>
</ul>}

puts {
<p><hr /></p>
<p>
<a href="../index.html"><img src="/goback.jpg" border=0 />
More Open Source Software</a> from Hwaci.
</p>

</body></html>}

#
# Run this TCL script to generate HTML for the index.html file.
#
set rcsid {$Id: index.tcl,v 1.39 2001/09/20 01:44:44 drh Exp $}

puts {<html>
<head><title>SQLite: An SQL Database Engine In A C Library</title></head>
<body bgcolor=white>
<h1 align=center>SQLite: An SQL Database Engine In A C Library</h1>

<p align=center>}
puts "This page was last modified on [lrange $rcsid 3 4] GMT<br>"
set vers [lindex $argv 0]
puts "The latest SQLite version is <b>$vers</b>"
puts " created on [exec cat last_change] GMT"
puts {</p>}

................................................................................
Programs that link with the SQLite library can have SQL database
access without running a separate RDBMS process.
The distribution comes with a standalone command-line
access program (<a href="sqlite.html">sqlite</a>) that can
be used to administer an SQLite database and which serves as
an example of how to use the SQLite library.</p>

<p>SQLite is <b>not</b> a client library used to connect to a
big database server.  SQLite <b>is</b> the server.  The SQLite
library reads and writes directly to and from the database files
on disk.</p>

<h2>Features</h2>

<p><ul>
<li>Implements a large subset of SQL92.</li>
<li>A complete SQL database (with multiple tables and indices) is
    stored in a single disk file.</li>
................................................................................
<li>Small memory footprint: about 12000 lines of C code.</li>
<li>Very simple 
<a href="c_interface.html">C/C++ interface</a> requires the use of only
three functions and one opaque structure.</li>
<li>A TCL interface to the library is included.</li>
<li>A TCL-based test suite provides near 100% code coverage.</li>
<li>Self-contained: no external dependencies.</li>
<li>Built and tested under Linux and Win2K.</li>
<li>No copyright on the source code.  Use for any purpose.</li>
</ul>
</p>
}

puts {<h2>Download</h2>}

puts {<table align="right"hspace="10">
<tr><td align="center" bgcolor="#8ee5ee">
<table border="2"><tr><td align="center">
<a href="sqlite.tar.gz"><big><b>Download SQLite<br>}
puts "version $vers<br>"
puts {Now!
</td></tr></table>
</td></tr>
</table>}


puts {<p>You can download a tarball containing all source
code for SQLite
}
puts "version $vers"
puts {
(including the TCL scripts that generate the
HTML files for this website) at <a href="sqlite.tar.gz">sqlite.tar.gz</a>.}
puts "This is a [file size sqlite.tar.gz] byte download."
set historical [lsort -dict [glob -nocomplain sqlite-*.tar.gz]]
if {$historical!=""} {
  puts {The following historical versions of SQLite are also available:}
  foreach x $historical {
     puts "<a href=\"$x\">$x</a> ([file size $x] bytes)"
  }
}
puts {</p>}

puts {<h2>Current Status</h2>

<p>A <a href="changes.html">change history</a> is available online.
There are currently no <em>known</em> memory leaks or debilitating bugs
in the library.  <a href="http://gcc.gnu.org/onlinedocs/gcov_1.html">Gcov</a>
is used to verify test coverage.</p>



<p>Known bugs:</p>

<ul>
<li><p>
  The LIKE operator is suppose to ignore case. 
  But it only ignores case for 7-bit Latin characters.
................................................................................

<p><ul>
<li>Information on the <a href="sqlite.html">sqlite</a>
    command-line utility.</li>
<li>The <a href="lang.html">SQL Language</a> subset understood by SQLite.</li>
<li>The <a href="c_interface.html">C/C++ Interface</a>.</li>
<li>The <a href="tclsqlite.html">Tcl Interface</a>.</li>

<li>The <a href="arch.html">Architecture of the SQLite Library</a> describes
    how the library is put together.</li>
<li>A description of the <a href="opcode.html">virtual machine</a> that
    SQLite uses to access the database.</li>





</ul>
</p>

<p>The SQLite source code is 35% comment.  These comments are
another important source of information. </p>
}

puts {
<table align="right">
<tr><td align="center">
<a href="http://www.yahoogroups.com/subscribe/sqlite">
<img src="http://www.egroups.com/img/ui/join.gif" border=0 /><br />
Click to subscribe to sqlite</a>
</td></tr>
</table>
<a name="mailinglist" />
<h2>Mailing List</h2>
<p>A mailing list has been set up on yahooGroups for discussion of
SQLite design issues or for asking questions about SQLite.</p>





}



puts {<h2>Building From Source</h2>}


puts {





<p>To build sqlite under Unix, just unwrap the tarball, create a separate
build directory, run configure from the build directory and then
type "make".  For example:</p>

<blockquote><pre>
$ tar xzf sqlite.tar.gz      <i> Unpacks into directory named "sqlite" </i>
$ mkdir bld                  <i> Create a separate build directory </i>
$ cd bld
$ ../sqlite/configure
$ make                       <i> Builds "sqlite" and "libsqlite.a" </i>
$ make test                  <i> Optional: run regression tests </i>
</pre></blockquote>

<p>The Win2K version of SQLite was built using the MingW32 cross-compiler
running under Linux.  You have to give the configure script hints to make
this work.  Read the comments at the beginning of the file
<b>configure.in</b> for additional information.  The source code is
general enough that it should be possible to compile SQLite using VC++,
though the author has no desire or motivation to try.
</p>
}

puts {<h2>Command-line Usage Example</h2>

<p>Download the source archive and compile the <b>sqlite</b>
program as described above.  Then type:</p>

................................................................................
sqlite> .exit
base$
</pre></blockquote>
}
puts {<h2>Related Sites</h2>

<ul>










<li><p>Here is a good <a href="http://w3.one.net/~jhoffman/sqltut.htm">
       tutorial on SQL</a>.</p></li>

<li><p><a href="http://www.postgresql.org/">PostgreSQL</a> is a
       full-blown SQL RDBMS that is also open source.</p></li>

<li><p><a href="http://www.chordate.com/gadfly.html">Gadfly</a> is another
       SQL library, similar to SQLite, except that Gadfly is written
       in Python.</p></li>





</ul>}

puts {
<p><hr /></p>
<p>
<a href="../index.html"><img src="/goback.jpg" border=0 />
More Open Source Software</a> from Hwaci.
</p>

</body></html>}

#
# Run this Tcl script to generate the sqlite.html file.
#
set rcsid {$Id: lang.tcl,v 1.8 2001/05/21 13:45:10 drh Exp $}

puts {<html>
<head>
  <title>Query Language Understood By SQLite</title>
</head>
<body bgcolor=white>
<h1 align=center>
................................................................................
  {DELETE delete}
  {UPDATE update}
  {SELECT select}
  {COPY copy}
  {EXPLAIN explain}
  {expression expr}
  {{BEGIN TRANSACTION} transaction}

}] {
  puts "<li><a href=\"#[lindex $section 1]\">[lindex $section 0]</a></li>"
}
puts {</ul></p>

<p>Details on the implementation of each command are provided in
the sequel.</p>
................................................................................
COMMIT [TRANSACTION [<name>]]
}
Syntax {sql-statement} {
ROLLBACK [TRANSACTION [<name>]]
}

puts {
<p>Support for transactions in SQLite is thin.  Transactions
may not be nested.  The GDBM backend does not support an atomic
commit or rollback, but it does support locking.  (Note, however,
that the compilation instructions on this website for using GDBM under 
Windows will disable locking.)  The MEM backend has no transaction
support and silently ignores all requests to begin or end
transactions.  A new backend is currently under
development for SQLite 2.0 that will support both atomic commits and rollback,
but that driver is not yet available.</p>

<p>Under GDBM, starting a transaction just locks all
tables that are either read or written during the course of the
transaction.  The locks are removed when the transaction is ended.
Thus, transactions can be used to make changes to multiple tables
with the assurance that other threads or processes will not touch
the same tables at the same time. For example:</p>

<blockquote>
<b>SELECT data1, data2, ... FROM table1 WHERE ...;</b><br>
... Make a decision to update the table ...<br>
<b>BEGIN TRANSACTION;<br>
SELECT data1, data2, ... FROM table1 WHERE ...;</b><br>
... Make sure no other process changed the table in between
the first SELECT and the BEGIN TRANSACTION. ...<br>
<b>UPDATE table1 SET data1=... WHERE ...;<br>
END TRANSACTION;</b>
</blockquote>

<p>In the code above, the <b>table1</b> table is locked by
the second SELECT because of the transaction.  Thus we know that
no other process has modified <b>table1</b> when the UPDATE
occurs.  The END TRANSACTION releases the lock.</p>
}


Section COPY copy

Syntax {sql-statement} {
COPY <table-name> FROM <filename>
}

................................................................................

puts {
<p>The CREATE INDEX command consists of the keywords "CREATE INDEX" followed
by the name of the new index, the keyword "ON", the name of a previously
created table that is to be indexed, and a parenthesized list of names of
columns in the table that are used for the index key.
Each column name can be followed by one of the "ASC" or "DESC" keywords
to indicate sort order, but since GDBM does not implement ordered keys,
these keywords are ignored.</p>

<p>There are no arbitrary limits on the number of indices that can be
attached to a single table, nor on the number of columns in an index.</p>

<p>The exact text
of each CREATE INDEX statement is stored in the <b>sqlite_master</b>
table.  Everytime the database is opened, all CREATE INDEX statements
................................................................................
<p>Each column definition is the name of the column followed by the
datatype for that column, then one or more optional column constraints.
The datatype for the column is ignored.  All information
is stored as null-terminated strings.  The constraints are also ignored,
except that the PRIMARY KEY constraint will cause an index to be automatically
created that implements the primary key and the DEFAULT constraint
which specifies a default value to use when doing an INSERT.
The name of the primary
key index will be the table name
with "<b>__primary_key</b>" appended.  The index used for a primary key
does not show up in the <b>sqlite_master</b> table, but a GDBM file is
created for that index.</p>

<p>There are no arbitrary limits on the size of columns, on the number
of columns, or on the number of constraints in a table.</p>


<p>The exact text
of each CREATE TABLE statement is stored in the <b>sqlite_master</b>
table.  Everytime the database is opened, all CREATE TABLE statements
are read from the <b>sqlite_master</b> table and used to regenerate
SQLite's internal representation of the table layout.</p>
}
................................................................................
puts {</p>

<p>The query is executed again one or more tables specified after
the FROM keyword.  If more than one table is specified, then the
query is against the join of the various tables.</p>

<p>The WHERE clause can be used to limit the number of rows over
which the query operates.  Note that because of limitations of
GDBM (it uses hashing not b-trees) indices will only be used to
optimize the query if WHERE expression contains equality comparisons
connected by the AND operator.</p>

<p>The GROUP BY clauses causes one or more rows of the result to
be combined into a single row of output.  This is especially useful
when the result contains aggregate functions.  The expressions in
the GROUP BY clause do <em>not</em> have to be expressions that
................................................................................
Syntax {sql-statement} {
VACUUM [<index-or-table-name>]
}

puts {
<p>The VACUUM command is an SQLite extension modelled after a similar
command found in PostgreSQL.  If VACUUM is invoked with the name of a
table or index, then the <b>gdbm_reorganize()</b> function is called
on the corresponding GDBM file.  If VACUUM is invoked with no arguments,
then <b>gdbm_reorganize()</b> is called for every GDBM file in the database.</p>

<p>It is a good idea to run VACUUM after creating large indices,

especially indices where a single index value refers to many
entries in the data table.  Reorganizing these indices will make

the underlying GDBM file much smaller and will help queries to
run much faster.</p>






}

puts {
<p></p>
}

puts {
<p><hr /></p>
<p><a href="index.html"><img src="/goback.jpg" border=0 />
Back to the SQLite Home Page</a>
</p>

</body></html>}

#
# Run this Tcl script to generate the sqlite.html file.
#
set rcsid {$Id: lang.tcl,v 1.9 2001/09/20 01:44:44 drh Exp $}

puts {<html>
<head>
  <title>Query Language Understood By SQLite</title>
</head>
<body bgcolor=white>
<h1 align=center>
................................................................................
  {DELETE delete}
  {UPDATE update}
  {SELECT select}
  {COPY copy}
  {EXPLAIN explain}
  {expression expr}
  {{BEGIN TRANSACTION} transaction}
  {PRAGMA pragma}
}] {
  puts "<li><a href=\"#[lindex $section 1]\">[lindex $section 0]</a></li>"
}
puts {</ul></p>

<p>Details on the implementation of each command are provided in
the sequel.</p>
................................................................................
COMMIT [TRANSACTION [<name>]]
}
Syntax {sql-statement} {
ROLLBACK [TRANSACTION [<name>]]
}

puts {
<p>Beginning in version 2.0, SQLite supports transactions with
rollback and atomic commit.  However, only a single level of
transaction is required.  In other words, transactions
may not be nested.
</p>

<p>
No changes can be made to the database except within a transaction.
Any command that changes the database (basically, any SQL command
other than SELECT) will automatically starts a transaction if
when is not already in effect.  Automatically stared transactions
are committed at the conclusion of the command.
</p>

<p>
Transactions can be started manually using the BEGIN TRANSACTION
command. Such transactions persist until a COMMIT or ROLLBACK
or until an error occurs or the database is closed.  If an
error is encountered or the database is closed, the transaction
is automatically rolled back.  The END TRANSACTION command is
a alias for COMMIT.
</p>
}












Section COPY copy

Syntax {sql-statement} {
COPY <table-name> FROM <filename>
}

................................................................................

puts {
<p>The CREATE INDEX command consists of the keywords "CREATE INDEX" followed
by the name of the new index, the keyword "ON", the name of a previously
created table that is to be indexed, and a parenthesized list of names of
columns in the table that are used for the index key.
Each column name can be followed by one of the "ASC" or "DESC" keywords
to indicate sort order, but the sort order is ignored in the current
implementation.</p>

<p>There are no arbitrary limits on the number of indices that can be
attached to a single table, nor on the number of columns in an index.</p>

<p>The exact text
of each CREATE INDEX statement is stored in the <b>sqlite_master</b>
table.  Everytime the database is opened, all CREATE INDEX statements
................................................................................
<p>Each column definition is the name of the column followed by the
datatype for that column, then one or more optional column constraints.
The datatype for the column is ignored.  All information
is stored as null-terminated strings.  The constraints are also ignored,
except that the PRIMARY KEY constraint will cause an index to be automatically
created that implements the primary key and the DEFAULT constraint
which specifies a default value to use when doing an INSERT.
</p>





<p>There are no arbitrary limits on the number
of columns or on the number of constraints in a table.
The total amount of data in a single row is limited to 65535 bytes.</p>

<p>The exact text
of each CREATE TABLE statement is stored in the <b>sqlite_master</b>
table.  Everytime the database is opened, all CREATE TABLE statements
are read from the <b>sqlite_master</b> table and used to regenerate
SQLite's internal representation of the table layout.</p>
}
................................................................................
puts {</p>

<p>The query is executed again one or more tables specified after
the FROM keyword.  If more than one table is specified, then the
query is against the join of the various tables.</p>

<p>The WHERE clause can be used to limit the number of rows over
which the query operates.  In the current implementation,
indices will only be used to
optimize the query if WHERE expression contains equality comparisons
connected by the AND operator.</p>

<p>The GROUP BY clauses causes one or more rows of the result to
be combined into a single row of output.  This is especially useful
when the result contains aggregate functions.  The expressions in
the GROUP BY clause do <em>not</em> have to be expressions that
................................................................................
Syntax {sql-statement} {
VACUUM [<index-or-table-name>]
}

puts {
<p>The VACUUM command is an SQLite extension modelled after a similar
command found in PostgreSQL.  If VACUUM is invoked with the name of a
table or index then it is suppose to clean up the named table or index.
In the current implementation, VACUUM is a no-op.
</p>
}


Section PRAGMA pragma


Syntax {sql-statement} {
PRAGMA <name> = <value>
}

puts {
<p>The PRAGMA command is used to modify the operation of the SQLite library.
Additional documentation on the PRAMGA statement is forthcoming.
</p>
}

puts {
<p></p>
}

puts {
<p><hr /></p>
<p><a href="index.html"><img src="/goback.jpg" border=0 />
Back to the SQLite Home Page</a>
</p>

</body></html>}

#
# Run this Tcl script to generate the sqlite.html file.
#
set rcsid {$Id: opcode.tcl,v 1.4 2000/07/30 20:04:43 drh Exp $}

puts {<html>
<head>
  <title>SQLite Virtual Machine Opcodes</title>
</head>
<body bgcolor=white>
<h1 align=center>
................................................................................

<p>This document is intended as a reference, not a tutorial.
A separate <a href="vdbe.html">Virtual Machine Tutorial</a> is 
available.  If you are looking for a narrative description
of how the virtual machine works, you should read the tutorial
and not this document.  Once you have a basic idea of what the
virtual machine does, you can refer back to this document for
the details on a particular opcode.</p>





<p>The source code to the virtual machine is in the <b>vdbe.c</b> source
file.  All of the opcode definitions further down in this document are
contained in comments in the source file.  In fact, the opcode table
in this document
was generated by scanning the <b>vdbe.c</b> source file 
and extracting the necessary information from comments.  So the 
................................................................................
one or two.  Some operators use none of the operands.<p>

<p>The virtual machine begins execution on instruction number 0.
Execution continues until (1) a Halt instruction is seen, or 
(2) the program counter becomes one greater than the address of
last instruction, or (3) there is an execution error.
When the virtual machine halts, all memory
that it allocated is released and all database files it may
have had open are closed.</p>



<p>The virtual machine also contains an operand stack of unlimited
depth.  Many of the opcodes use operands from the stack.  See the
individual opcode descriptions for details.</p>

<p>The virtual machine can have zero or more cursors.  Each cursor
is a pointer into a single GDBM file.  There can be multiple
cursors pointing at the same file.
All cursors operate independently, even cursors pointing to the same file.

The only way for the virtual machine to interact with a GDBM
file is through a cursor.
Instructions in the virtual
machine can create a new cursor (Open), read data from a cursor
(Field), advance the cursor to the next entry in the GDBM file

(Next), and many other operations.  All cursors are automatically
closed when the virtual machine terminates.</p>

<p>The virtual machine contains an arbitrary number of fixed memory
locations with addresses beginning at zero and growing upward.
Each memory location can hold an arbitrary string.  The memory
cells are typically used to hold the result of a scalar SELECT
that is part of a larger expression.</p>
................................................................................
to implement the ORDER BY clause of a SELECT statement.  The
fact that the virtual machine allows multiple sorters is an
historical accident.  In practice no more than one sorter
(sorter number 0) ever gets used.</p>

<p>The virtual machine may contain an arbitrary number of "Lists".
Each list stores a list of integers.  Lists are used to hold the
GDBM keys for records of a GDBM file that needs to be modified.
(See the <a href="fileformat.html">file format</a> description for
more information on GDBM keys in SQLite table files.)
The WHERE clause of an UPDATE or DELETE statement scans through
the table and writes the GDBM key of every record to be modified
into a list.  Then the list is played back and the table is modified
in a separate step.  It is necessary to do this in two steps since
making a change to a GDBM file can alter the scan order.</p>

<p>The virtual machine can contain an arbitrary number of "Sets".
Each set holds an arbitrary number of strings.  Sets are used to
implement the IN operator with a constant right-hand side.</p>

<p>The virtual machine can open a single external file for reading.
This external read file is used to implement the COPY command.</p>
................................................................................
  puts {</pre></blockquote>}
}

Code {
$ (((sqlite ex1)))
sqlite> (((.explain)))
sqlite> (((explain delete from tbl1 where two<20;)))
addr  opcode        p1     p2     p3          
----  ------------  -----  -----  -------------------------------------   


0     ListOpen      0      0                  
1     Open          0      1      tbl1        


2     Next          0      9                  
3     Field         0      1                  

4     Integer       20     0                  
5     Ge            0      2                  
6     Key           0      0                  

7     ListWrite     0      0                  
8     Goto          0      2                  
9     Noop          0      0                  

10    ListRewind    0      0                  

11    ListRead      0      14                 

12    Delete        0      0                  
13    Goto          0      11                 
14    ListClose     0      0                  

}

puts {
<p>All you have to do is add the "EXPLAIN" keyword to the front of the
SQL statement.  But if you use the ".explain" command to <b>sqlite</b>
first, it will set up the output mode to make the program more easily
viewable.</p>

<p>If <b>sqlite</b> has been compiled without the "-DNDEBUG=1" option
(that is, with the NDEBUG preprocessor macro not defined) then you
can put the SQLite virtual machine in a mode where it will trace its
execution by writing messages to standard output.  There are special
comments to turn tracing on and off.  Use the <b>--vdbe-trace-on--</b>
comment to turn tracing on and the <b>--vdbe-trace-off--</b> comment







to turn tracing back off.</p>


<h2>The Opcodes</h2>
}

puts "<p>There are currently [llength $OpcodeList] opcodes defined by
the virtual machine."
puts {All currently defined opcodes are described in the table below.

#
# Run this Tcl script to generate the sqlite.html file.
#
set rcsid {$Id: opcode.tcl,v 1.5 2001/09/20 01:44:44 drh Exp $}

puts {<html>
<head>
  <title>SQLite Virtual Machine Opcodes</title>
</head>
<body bgcolor=white>
<h1 align=center>
................................................................................

<p>This document is intended as a reference, not a tutorial.
A separate <a href="vdbe.html">Virtual Machine Tutorial</a> is 
available.  If you are looking for a narrative description
of how the virtual machine works, you should read the tutorial
and not this document.  Once you have a basic idea of what the
virtual machine does, you can refer back to this document for
the details on a particular opcode.
Unfortunately, the virtual machine tutorial was written for
SQLite version 1.0.  There are substantial changes in the virtual
machine for version 2.0 and the document has not been updated.
</p>

<p>The source code to the virtual machine is in the <b>vdbe.c</b> source
file.  All of the opcode definitions further down in this document are
contained in comments in the source file.  In fact, the opcode table
in this document
was generated by scanning the <b>vdbe.c</b> source file 
and extracting the necessary information from comments.  So the 
................................................................................
one or two.  Some operators use none of the operands.<p>

<p>The virtual machine begins execution on instruction number 0.
Execution continues until (1) a Halt instruction is seen, or 
(2) the program counter becomes one greater than the address of
last instruction, or (3) there is an execution error.
When the virtual machine halts, all memory
that it allocated is released and all database cursors it may
have had open are closed.  If the execution stopped due to an
error, any pending transactions are terminated and changes made
to the database are rollback.</p>

<p>The virtual machine also contains an operand stack of unlimited
depth.  Many of the opcodes use operands from the stack.  See the
individual opcode descriptions for details.</p>

<p>The virtual machine can have zero or more cursors.  Each cursor
is a pointer into a single table or index within the database.
There can be multiple cursors pointing at the same index or table.
All cursors operate independently, even cursors pointing to the same
indices or tables.
The only way for the virtual machine to interact with a database
file is through a cursor.
Instructions in the virtual
machine can create a new cursor (Open), read data from a cursor
(Column), advance the cursor to the next entry in the table
(Next) or index (NextIdx), and many other operations.
All cursors are automatically
closed when the virtual machine terminates.</p>

<p>The virtual machine contains an arbitrary number of fixed memory
locations with addresses beginning at zero and growing upward.
Each memory location can hold an arbitrary string.  The memory
cells are typically used to hold the result of a scalar SELECT
that is part of a larger expression.</p>
................................................................................
to implement the ORDER BY clause of a SELECT statement.  The
fact that the virtual machine allows multiple sorters is an
historical accident.  In practice no more than one sorter
(sorter number 0) ever gets used.</p>

<p>The virtual machine may contain an arbitrary number of "Lists".
Each list stores a list of integers.  Lists are used to hold the
rowids for records of a database table that needs to be modified.


The WHERE clause of an UPDATE or DELETE statement scans through
the table and writes the rowid of every record to be modified
into a list.  Then the list is played back and the table is modified
in a separate step.  It is necessary to do this in two steps since
making a change to a database table can alter the scan order.</p>

<p>The virtual machine can contain an arbitrary number of "Sets".
Each set holds an arbitrary number of strings.  Sets are used to
implement the IN operator with a constant right-hand side.</p>

<p>The virtual machine can open a single external file for reading.
This external read file is used to implement the COPY command.</p>
................................................................................
  puts {</pre></blockquote>}
}

Code {
$ (((sqlite ex1)))
sqlite> (((.explain)))
sqlite> (((explain delete from tbl1 where two<20;)))
addr  opcode        p1     p2     p3                                      
----  ------------  -----  -----  ----------------------------------------
0     Transaction   0      0                                              
1     VerifyCookie  990    0                                              
2     ListOpen      0      0                                              
3     Open          0      31     tbl1                                    
4     VerifyCookie  990    0                                              
5     Rewind        0      0                                              
6     Next          0      13                                             

7     Column        0      1                                              
8     Integer       20     0                                              
9     Ge            0      6                                              

10    Recno         0      0                                              
11    ListWrite     0      0                                              
12    Goto          0      6                                              

13    Close         0      0                                              
14    ListRewind    0      0                                              
15    Open          0      31                                             
16    ListRead      0      20                                             
17    MoveTo        0      0                                              
18    Delete        0      0                                              
19    Goto          0      16                                             
20    ListClose     0      0                                              
21    Commit        0      0                                              
}

puts {
<p>All you have to do is add the "EXPLAIN" keyword to the front of the
SQL statement.  But if you use the ".explain" command to <b>sqlite</b>
first, it will set up the output mode to make the program more easily
viewable.</p>

<p>If <b>sqlite</b> has been compiled without the "-DNDEBUG=1" option
(that is, with the NDEBUG preprocessor macro not defined) then you
can put the SQLite virtual machine in a mode where it will trace its
execution by writing messages to standard output.  The non-standard
SQL "PRAGMA" comments can be used to turn tracing on and off.  To
turn tracing on, enter:
</p>

<blockquote><pre>
PRAGMA vdbe_trace=on;
</pre></blockquote>

<p>
You can turn tracing back off by entering a similar statement but
changing the value "on" to "off".</p>

<h2>The Opcodes</h2>
}

puts "<p>There are currently [llength $OpcodeList] opcodes defined by
the virtual machine."
puts {All currently defined opcodes are described in the table below.

#
# Run this Tcl script to generate the tclsqlite.html file.
#
set rcsid {$Id: tclsqlite.tcl,v 1.3 2000/10/23 13:16:33 drh Exp $}

puts {<html>
<head>
  <title>The Tcl interface to the SQLite library</title>
</head>
<body bgcolor=white>
<h1 align=center>
................................................................................
The <b>sqlite</b> command also creates a new Tcl
command to control the database.  The name of the new Tcl command
is given by the first argument.  This approach is similar to the
way widgets are created in Tk.
</p>

<p>
The name of the database is usually either the name of a directory
that will contain the GDBM files that comprise the database, or it is the
name of the directory prefaced by "<b>gdbm:</b>".  The second form
of the name is a new feature beginning in SQLite version 1.0.14 that
allows you to select alternative database backends.  The default
backend is GDBM.  But you can also select to store the database in
a hash table in memory by using the prefix "<b>memory:</b>". 
Other backends may be added in the future.
</p>

<p>
Every time you open an SQLite database with the <b>memory:</b> prefix
on the database name, you get a new in-memory database.  This is true
even if you open two databases with the same name.  Furthermore,
an in-memory database is automatically deleted when the database is
closed and so is not useful for persistant storage like a normal
database.  But the use of an in-memory SQL database does give Tcl/Tk
a powerful new data storage mechanism that can do things that are
difficult to do with only Tcl array variables.  In fact, the
hash-table backend for SQLite was created for the sole purpose of
providing better data structure support to the Tcl language.
</p>

<p>
Once an SQLite database is open, it can be controlled using 
methods of the <i>dbcmd</i>.  There are currently 5 methods
defined:</p>

................................................................................
<h2>The "timeout" method</h2>

<p>The "timeout" method is used to control how long the SQLite library
will wait for locks to clear before giving up on a database transaction.
The default timeout is 0 millisecond.  (In other words, the default behavior
is not to wait at all.)</p>

<p>The GDBM backend allows multiple simultaneous
readers or a single writer but not both.  If any process is writing to
the database no other process is allows to read or write.  If any process
is reading the database other processes are allowed to read but not write.
Each GDBM file is locked separately.  Because each SQL table is stored as
a separate file, it is possible for different processes to write to different
database tables at the same time, just not the same table.</p>

<p>When SQLite tries to open a GDBM file and finds that it is locked, it
can optionally delay for a short while and try to open the file again.
This process repeats until the query times out and SQLite returns a
failure.  The timeout is adjustable.  It is set to 0 by default so that
if a GDBM file is locked, the SQL statement fails immediately.  But you
can use the "timeout" method to change the timeout value to a positive
number.  For example:</p>

<blockquote><b>db1 timeout 2000</b></blockquote>

<p>The argument to the timeout method is the maximum number of milliseconds
to wait for the lock to clear.  So in the example above, the maximum delay
would be 2 seconds.</p>

<h2>The "busy" method</h2>

<p>The "busy" method, like "timeout", only comes into play when a GDBM
file is locked.  But the "busy" method gives the programmer much more
control over what action to take.  The "busy" method specifies a callback
Tcl procedure that is invoked whenever SQLite tries to open a locked
GDBM file.  This callback can do whatever is desired.  Presumably, the
callback will do some other useful work for a short while then return
so that the lock can be tried again.  The callback procedure should
return "0" if it wants SQLite to try again to open the GDBM file and
should return "1" if it wants SQLite to abandon the current operation.

}

puts {
<p><hr /></p>
<p><a href="index.html"><img src="/goback.jpg" border=0 />
Back to the SQLite Home Page</a>
</p>

</body></html>}

#
# Run this Tcl script to generate the tclsqlite.html file.
#
set rcsid {$Id: tclsqlite.tcl,v 1.4 2001/09/20 01:44:44 drh Exp $}

puts {<html>
<head>
  <title>The Tcl interface to the SQLite library</title>
</head>
<body bgcolor=white>
<h1 align=center>
................................................................................
The <b>sqlite</b> command also creates a new Tcl
command to control the database.  The name of the new Tcl command
is given by the first argument.  This approach is similar to the
way widgets are created in Tk.
</p>

<p>
The name of the database is just the name of a disk file in which
the database is stored.



















</p>

<p>
Once an SQLite database is open, it can be controlled using 
methods of the <i>dbcmd</i>.  There are currently 5 methods
defined:</p>

................................................................................
<h2>The "timeout" method</h2>

<p>The "timeout" method is used to control how long the SQLite library
will wait for locks to clear before giving up on a database transaction.
The default timeout is 0 millisecond.  (In other words, the default behavior
is not to wait at all.)</p>

<p>The SQlite database allows multiple simultaneous
readers or a single writer but not both.  If any process is writing to
the database no other process is allows to read or write.  If any process
is reading the database other processes are allowed to read but not write.
The entire database shared a single lock.</p>



<p>When SQLite tries to open a database and finds that it is locked, it
can optionally delay for a short while and try to open the file again.
This process repeats until the query times out and SQLite returns a
failure.  The timeout is adjustable.  It is set to 0 by default so that
if the database is locked, the SQL statement fails immediately.  But you
can use the "timeout" method to change the timeout value to a positive
number.  For example:</p>

<blockquote><b>db1 timeout 2000</b></blockquote>

<p>The argument to the timeout method is the maximum number of milliseconds
to wait for the lock to clear.  So in the example above, the maximum delay
would be 2 seconds.</p>

<h2>The "busy" method</h2>

<p>The "busy" method, like "timeout", only comes into play when the
database is locked.  But the "busy" method gives the programmer much more
control over what action to take.  The "busy" method specifies a callback
Tcl procedure that is invoked whenever SQLite tries to open a locked
database.  This callback can do whatever is desired.  Presumably, the
callback will do some other useful work for a short while then return
so that the lock can be tried again.  The callback procedure should
return "0" if it wants SQLite to try again to open the database and
should return "1" if it wants SQLite to abandon the current operation.

}

puts {
<p><hr /></p>
<p><a href="index.html"><img src="/goback.jpg" border=0 />
Back to the SQLite Home Page</a>
</p>

</body></html>}