SQLite

2.0.6

**     COPY
**     VACUUM
**     BEGIN TRANSACTION
**     COMMIT
**     ROLLBACK
**     PRAGMA
**
** $Id: build.c,v 1.51 2001/10/19 16:44:57 drh Exp $
*/
#include "sqliteInt.h"
#include <ctype.h>

/*
** This routine is called after a single SQL statement has been
** parsed and we want to execute the VDBE code to implement 

  if( sqliteStrICmp(zLeft, "count_changes")==0 ){
    if( getBoolean(zRight) ){
      db->flags |= SQLITE_CountRows;
    }else{
      db->flags &= ~SQLITE_CountRows;
    }
  }else

  if( sqliteStrICmp(zLeft, "empty_result_callbacks")==0 ){
    if( getBoolean(zRight) ){
      db->flags |= SQLITE_NullCallback;
    }else{
      db->flags &= ~SQLITE_NullCallback;
    }
  }else

  if( sqliteStrICmp(zLeft, "table_info")==0 ){
    Table *pTab;
    Vdbe *v;
    pTab = sqliteFindTable(db, zRight);
    if( pTab ) v = sqliteGetVdbe(pParse);
    if( pTab && v ){

**    May you find forgiveness for yourself and forgive others.
**    May you share freely, never taking more than you give.
**
*************************************************************************
** This file contains C code routines that are called by the parser
** to handle SELECT statements in SQLite.
**
** $Id: select.c,v 1.42 2001/10/19 16:44:57 drh Exp $
*/
#include "sqliteInt.h"

/*
** Allocate a new Select structure and return a pointer to that
** structure.
*/

  /* If there is an ORDER BY clause, then we need to sort the results
  ** and send them to the callback one by one.
  */
  if( pOrderBy ){
    generateSortTail(v, pEList->nExpr);
  }
  pParse->nTab = base;


  /* Issue a null callback if that is what the user wants.
  */
  if( (pParse->db->flags & SQLITE_NullCallback)!=0 && eDest==SRT_Callback ){
    sqliteVdbeAddOp(v, OP_NullCallback, pEList->nExpr, 0);
  }

  return 0;
}

**    May you find forgiveness for yourself and forgive others.
**    May you share freely, never taking more than you give.
**
*************************************************************************
** This file contains code to implement the "sqlite" command line
** utility for accessing SQLite databases.
**
** $Id: shell.c,v 1.37 2001/10/19 16:44:57 drh Exp $
*/
#include <stdlib.h>
#include <string.h>
#include <stdio.h>
#include "sqlite.h"
#include <ctype.h>
#ifdef OS_UNIX

*/
static int callback(void *pArg, int nArg, char **azArg, char **azCol){
  int i;
  struct callback_data *p = (struct callback_data*)pArg;
  switch( p->mode ){
    case MODE_Line: {
      int w = 5;
      if( azArg==0 ) break;
      for(i=0; i<nArg; i++){
        int len = strlen(azCol[i]);
        if( len>w ) w = len;
      }
      if( p->cnt++>0 ) fprintf(p->out,"\n");
      for(i=0; i<nArg; i++){
        fprintf(p->out,"%*s = %s\n", w, azCol[i], azArg[i] ? azArg[i] : 0);

             w = p->colWidth[i];
          }else{
             w = 0;
          }
          if( w<=0 ){
            w = strlen(azCol[i] ? azCol[i] : "");
            if( w<10 ) w = 10;
            n = strlen(azArg && azArg[i] ? azArg[i] : "");
            if( w<n ) w = n;
          }
          if( i<ArraySize(p->actualWidth) ){
            p->actualWidth[i] = w; 
          }
          if( p->showHeader ){
            fprintf(p->out,"%-*.*s%s",w,w,azCol[i], i==nArg-1 ? "\n": "  ");

            }
            fprintf(p->out,"%-*.*s%s",w,w,"-----------------------------------"
                   "----------------------------------------------------------",
                    i==nArg-1 ? "\n": "  ");
          }
        }
      }
      if( azArg==0 ) break;
      for(i=0; i<nArg; i++){
        int w;
        if( i<ArraySize(p->actualWidth) ){
           w = p->actualWidth[i];
        }else{
           w = 10;
        }
        fprintf(p->out,"%-*.*s%s",w,w,
            azArg[i] ? azArg[i] : "", i==nArg-1 ? "\n": "  ");
      }
      break;
    }
    case MODE_Semi:
    case MODE_List: {
      if( p->cnt++==0 && p->showHeader ){
        for(i=0; i<nArg; i++){
          fprintf(p->out,"%s%s",azCol[i], i==nArg-1 ? "\n" : p->separator);
        }
      }
      if( azArg==0 ) break;
      for(i=0; i<nArg; i++){
        char *z = azArg[i];
        if( z==0 ) z = "";
        while( *z ){
          int j;
          for(j=0; z[j] && z[j]!=p->escape && z[j]!='\\'; j++){}
          if( j>0 ){

      if( p->cnt++==0 && p->showHeader ){
        fprintf(p->out,"<TR>");
        for(i=0; i<nArg; i++){
          fprintf(p->out,"<TH>%s</TH>",azCol[i]);
        }
        fprintf(p->out,"</TR>\n");
      }
      if( azArg==0 ) break;
      fprintf(p->out,"<TR>");
      for(i=0; i<nArg; i++){
        fprintf(p->out,"<TD>");
        output_html_string(p->out, azArg[i] ? azArg[i] : "");
        fprintf(p->out,"</TD>\n");
      }
      fprintf(p->out,"</TD></TR>\n");
      break;
    }
    case MODE_Insert: {
      if( azArg==0 ) break;
      fprintf(p->out,"INSERT INTO %s VALUES(",p->zDestTable);
      for(i=0; i<nArg; i++){
        char *zSep = i>0 ? ",": "";
        if( azArg[i]==0 ){
          fprintf(p->out,"%sNULL",zSep);
        }else if( is_numeric(azArg[i]) ){
          fprintf(p->out,"%s%s",zSep, azArg[i]);
        }else{
          if( zSep[0] ) fprintf(p->out,"%s",zSep);
          output_quoted_string(p->out, azArg[i]);
        }
      }
      fprintf(p->out,");\n");
      break;
    }
  }      
  return 0;
}

/*
** Set the destination table field of the callback_data structure to

/*
** 2001 September 15
**
** The author disclaims copyright to this source code.  In place of
** a legal notice, here is a blessing:
**
**    May you do good and not evil.
**    May you find forgiveness for yourself and forgive others.
**    May you share freely, never taking more than you give.
**
*************************************************************************
** Internal interface definitions for SQLite.
**
** @(#) $Id: sqliteInt.h,v 1.64 2001/10/19 16:44:57 drh Exp $
*/
#include "sqlite.h"
#include "hash.h"
#include "vdbe.h"
#include "parse.h"
#include "btree.h"
#include <stdio.h>

#define SQLITE_Interrupt      0x00000004  /* Cancel current operation */
#define SQLITE_InTrans        0x00000008  /* True if in a transaction */
#define SQLITE_InternChanges  0x00000010  /* Uncommitted Hash table changes */
#define SQLITE_FullColNames   0x00000020  /* Show full column names on SELECT */
#define SQLITE_CountRows      0x00000040  /* Count rows changed by INSERT, */
                                          /*   DELETE, or UPDATE and return */
                                          /*   the count using a callback. */
#define SQLITE_NullCallback   0x00000080  /* Invoke the callback once if the */
                                          /*   result set is empty */

/*
** Current file format version
*/
#define SQLITE_FileFormat 2

/*

  int need;
  int i;
  char *z;

  /* Make sure there is enough space in p->azResult to hold everything
  ** we need to remember from this invocation of the callback.
  */
  if( p->nRow==0 && argv!=0 ){
    p->nColumn = nCol;
    need = nCol*2;
  }else{
    need = nCol;
  }
  if( p->nData + need >= p->nAlloc ){
    p->nAlloc = p->nAlloc*2 + need + 1;

      }
      p->azResult[p->nData++] = z;
    }
  }

  /* Copy over the row data
  */
  if( argv!=0 ){
    for(i=0; i<nCol; i++){
      if( argv[i]==0 ){
        z = 0;
      }else{
        z = malloc( strlen(argv[i])+1 );
        if( z==0 ){
          p->rc = SQLITE_NOMEM;
          return 1;
        }
        strcpy(z, argv[i]);
      }
      p->azResult[p->nData++] = z;
    }
    p->nRow++;
  }
  return 0;
}

/*
** Query the database.  But instead of invoking a callback for each row,
** malloc() for space to hold the result and return the entire results
** at the conclusion of the call.

/*
** 2001 September 15
**
** The author disclaims copyright to this source code.  In place of
** a legal notice, here is a blessing:
**
**    May you do good and not evil.
**    May you find forgiveness for yourself and forgive others.
**    May you share freely, never taking more than you give.
**
*************************************************************************
** A TCL Interface to SQLite
**
** $Id: tclsqlite.c,v 1.26 2001/10/19 16:44:57 drh Exp $
*/
#ifndef NO_TCL     /* Omit this whole file if TCL is unavailable */

#include "sqlite.h"
#include "tcl.h"
#include <stdlib.h>
#include <string.h>

  char ** azN            /* Name for each column */
){
  CallbackData *cbData = (CallbackData*)clientData;
  int i, rc;
#ifdef UTF_TRANSLATION_NEEDED
  Tcl_DString dCol;
#endif

  if( azCol==0 || (cbData->once && cbData->zArray[0]) ){
    Tcl_SetVar2(cbData->interp, cbData->zArray, "*", "", 0);
    for(i=0; i<nCol; i++){
      Tcl_SetVar2(cbData->interp, cbData->zArray, "*", azN[i],
         TCL_LIST_ELEMENT|TCL_APPEND_VALUE);
    }
    cbData->once = 0;
  }
  if( azCol!=0 ){
    if( cbData->zArray[0] ){
      for(i=0; i<nCol; i++){
        char *z = azCol[i];
        if( z==0 ) z = "";
#ifdef UTF_TRANSLATION_NEEDED
        Tcl_DStringInit(&dCol);
        Tcl_ExternalToUtfDString(NULL, z, -1, &dCol);
        Tcl_SetVar2(cbData->interp, cbData->zArray, azN[i], 
              Tcl_DStringValue(&dCol), 0);
        Tcl_DStringFree(&dCol);
#else
        Tcl_SetVar2(cbData->interp, cbData->zArray, azN[i], z, 0);
#endif
      }
    }else{
      for(i=0; i<nCol; i++){
        char *z = azCol[i];
        if( z==0 ) z = "";
#ifdef UTF_TRANSLATION_NEEDED
        Tcl_DStringInit(&dCol);
        Tcl_ExternalToUtfDString(NULL, z, -1, &dCol);
        Tcl_SetVar(cbData->interp, azN[i], Tcl_DStringValue(&dCol), 0);
        Tcl_DStringFree(&dCol);
#else
        Tcl_SetVar(cbData->interp, azN[i], z, 0);
#endif
      }
    }

  }
  rc = Tcl_EvalObj(cbData->interp, cbData->pCode);
  if( rc==TCL_CONTINUE ) rc = TCL_OK;
  cbData->tcl_rc = rc;
  return rc!=TCL_OK;
}

/*
** This is an alternative callback for database queries.  Instead
** of invoking a TCL script to handle the result, this callback just
** appends each column of the result to a list.  After the query
** is complete, the list is returned.
*/
static int DbEvalCallback2(
  void *clientData,      /* An instance of CallbackData */
  int nCol,              /* Number of columns in the result */
  char ** azCol,         /* Data for each column */
  char ** azN            /* Name for each column */
){
  Tcl_Obj *pList = (Tcl_Obj*)clientData;
  int i;
  if( azCol==0 ) return 0;
  for(i=0; i<nCol; i++){
    Tcl_Obj *pElem;
    if( azCol[i] && *azCol[i] ){
#ifdef UTF_TRANSLATION_NEEDED
      Tcl_DString dCol;
      Tcl_DStringInit(&dCol);
      Tcl_ExternalToUtfDString(NULL, azCol[i], -1, &dCol);

*************************************************************************
** An tokenizer for SQL
**
** This file contains C code that splits an SQL input string up into
** individual tokens and sends those tokens one-by-one over to the
** parser for analysis.
**
** $Id: tokenize.c,v 1.29 2001/10/19 16:44:57 drh Exp $
*/
#include "sqliteInt.h"
#include "os.h"
#include <ctype.h>
#include <stdlib.h>

/*

      return i;
    }
    default: {
      if( !isIdChar[*z] ){
        break;
      }
      for(i=1; isIdChar[z[i]]; i++){}
      *tokenType = sqliteKeywordCode((char*)z, i);
      return i;
    }
  }
  *tokenType = TK_ILLEGAL;
  return 1;
}

** type to the other occurs as necessary.
** 
** Most of the code in this file is taken up by the sqliteVdbeExec()
** function which does the work of interpreting a VDBE program.
** But other routines are also provided to help in building up
** a program instruction by instruction.
**
** $Id: vdbe.c,v 1.87 2001/10/19 16:44:57 drh Exp $
*/
#include "sqliteInt.h"
#include <ctype.h>

/*
** SQL is translated into a sequence of instructions to be
** executed by a virtual machine.  Each instruction is an instance

  int nLineAlloc;     /* Number of spaces allocated for zLine */
  int nMem;           /* Number of memory locations currently allocated */
  Mem *aMem;          /* The memory locations */
  Agg agg;            /* Aggregate information */
  int nSet;           /* Number of sets allocated */
  Set *aSet;          /* An array of sets */
  int nFetch;         /* Number of OP_Fetch instructions executed */
  int nCallback;      /* Number of callbacks invoked so far */
};

/*
** Create a new virtual database engine.
*/
Vdbe *sqliteVdbeCreate(sqlite *db){
  Vdbe *p;

  "SortMakeRec",       "SortMakeKey",       "Sort",              "SortNext",
  "SortKey",           "SortCallback",      "SortClose",         "FileOpen",
  "FileRead",          "FileColumn",        "FileClose",         "AggReset",
  "AggFocus",          "AggIncr",           "AggNext",           "AggSet",
  "AggGet",            "SetInsert",         "SetFound",          "SetNotFound",
  "SetClear",          "MakeRecord",        "MakeKey",           "MakeIdxKey",
  "Goto",              "If",                "Halt",              "ColumnCount",
  "ColumnName",        "Callback",          "NullCallback",      "Integer",
  "String",            "Null",              "Pop",               "Dup",
  "Pull",              "Add",               "AddImm",            "Subtract",
  "Multiply",          "Divide",            "Remainder",         "BitAnd",
  "BitOr",             "BitNot",            "ShiftLeft",         "ShiftRight",
  "AbsValue",          "Precision",         "Min",               "Max",
  "Like",              "Glob",              "Eq",                "Ne",
  "Lt",                "Le",                "Gt",                "Ge",
  "IsNull",            "NotNull",           "Negative",          "And",
  "Or",                "Not",               "Concat",            "Noop",
  "Strlen",            "Substr",          
};

/*
** Given the name of an opcode, return its number.  Return 0 if
** there is no match.
**
** This routine is used for testing and debugging.

** array passed as the 4th parameter to the callback.  No checking
** is done.  If this value is wrong, a coredump can result.
*/
case OP_ColumnCount: {
  p->azColName = sqliteRealloc(p->azColName, (pOp->p1+1)*sizeof(char*));
  if( p->azColName==0 ) goto no_mem;
  p->azColName[pOp->p1] = 0;
  p->nCallback = 0;
  break;
}

/* Opcode: ColumnName P1 * P3
**
** P3 becomes the P1-th column name (first is 0).  An array of pointers
** to all column names is passed as the 4th parameter to the callback.
** The ColumnCount opcode must be executed first to allocate space to
** hold the column names.  Failure to do this will likely result in
** a coredump.
*/
case OP_ColumnName: {
  p->azColName[pOp->p1] = pOp->p3 ? pOp->p3 : "";
  p->nCallback = 0;
  break;
}

/* Opcode: Callback P1 * *
**
** Pop P1 values off the stack and form them into an array.  Then
** invoke the callback function using the newly formed array as the

    }
  }
  zStack[p->tos+1] = 0;
  if( xCallback!=0 ){
    if( xCallback(pArg, pOp->p1, &zStack[i], p->azColName)!=0 ){
      rc = SQLITE_ABORT;
    }
    p->nCallback++;
  }
  PopStack(p, pOp->p1);
  break;
}

/* Opcode: NullCallback P1 * *
**
** Invoke the callback function once with the 2nd argument (the
** number of columns) equal to P1 and with the 4th argument (the
** names of the columns) set according to prior OP_ColumnName and
** OP_ColumnCount instructions.  This is all like the regular
** OP_Callback or OP_SortCallback opcodes.  But the 3rd argument
** which normally contains a pointer to an array of pointers to
** data is NULL.
**
** The callback is only invoked if there have been no prior calls
** to OP_Callback or OP_SortCallback.
**
** This opcode is used to report the number and names of columns
** in cases where the result set is empty.
*/
case OP_NullCallback: {
  if( xCallback!=0 && p->nCallback==0 ){
    if( xCallback(pArg, pOp->p1, 0, p->azColName)!=0 ){
      rc = SQLITE_ABORT;
    }
    p->nCallback++;
  }
  break;
}

/* Opcode: Concat P1 P2 P3
**
** Look at the first P1 elements of the stack.  Append them all 
** together with the lowest element first.  Use P3 as a separator.  
** Put the result on the top of the stack.  The original P1 elements
** are popped from the stack if P2==0 and retained if P2==1.

case OP_SortCallback: {
  int i = p->tos;
  VERIFY( if( i<0 ) goto not_enough_stack; )
  if( xCallback!=0 ){
    if( xCallback(pArg, pOp->p1, (char**)zStack[i], p->azColName) ){
      rc = SQLITE_ABORT;
    }
    p->nCallback++;
  }
  POPSTACK;
  break;
}

/* Opcode: SortClose P1 * *
**

*************************************************************************
** Header file for the Virtual DataBase Engine (VDBE)
**
** This header defines the interface to the virtual database engine
** or VDBE.  The VDBE implements an abstract machine that runs a
** simple program to access and modify the underlying database.
**
** $Id: vdbe.h,v 1.30 2001/10/19 16:44:57 drh Exp $
*/
#ifndef _SQLITE_VDBE_H_
#define _SQLITE_VDBE_H_
#include <stdio.h>

/*
** A single VDBE is an opaque structure named "Vdbe".  Only routines

#define OP_Goto               69
#define OP_If                 70
#define OP_Halt               71

#define OP_ColumnCount        72
#define OP_ColumnName         73
#define OP_Callback           74
#define OP_NullCallback       75

#define OP_Integer            76
#define OP_String             77
#define OP_Null               78
#define OP_Pop                79
#define OP_Dup                80
#define OP_Pull               81

#define OP_Add                82
#define OP_AddImm             83
#define OP_Subtract           84
#define OP_Multiply           85
#define OP_Divide             86
#define OP_Remainder          87
#define OP_BitAnd             88
#define OP_BitOr              89
#define OP_BitNot             90
#define OP_ShiftLeft          91
#define OP_ShiftRight         92
#define OP_AbsValue           93
#define OP_Precision          94
#define OP_Min                95
#define OP_Max                96
#define OP_Like               97
#define OP_Glob               98
#define OP_Eq                 99
#define OP_Ne                100
#define OP_Lt                101
#define OP_Le                102
#define OP_Gt                103
#define OP_Ge                104
#define OP_IsNull            105
#define OP_NotNull           106
#define OP_Negative          107
#define OP_And               108
#define OP_Or                109
#define OP_Not               110
#define OP_Concat            111
#define OP_Noop              112

#define OP_Strlen            113
#define OP_Substr            114

#define OP_MAX               114

/*
** Prototypes for the VDBE interface.  See comments on the implementation
** for a description of what each of these routines does.
*/
Vdbe *sqliteVdbeCreate(sqlite*);
void sqliteVdbeCreateCallback(Vdbe*, int*);

# 2001 September 15
#
# The author disclaims copyright to this source code.  In place of
# a legal notice, here is a blessing:
#
#    May you do good and not evil.
#    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 SELECT statement.
#
# $Id: select1.test,v 1.14 2001/10/19 16:44:58 drh Exp $

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

# Try to select on a non-existant table.
#
do_test select1-1.1 {

do_test select1-8.5 {
  execsql {
    SELECT min(1,2,3), -max(1,2,3)
    FROM test1 ORDER BY f1
  }
} {1 -3 1 -3}


# Check the behavior when the result set is empty
#
do_test select1-9.1 {
  catch {unset r}
  set r(*) {}
  db eval {SELECT * FROM test1 WHERE f1<0} r {}
  set r(*)
} {}
do_test select1-9.2 {
  execsql {PRAGMA empty_result_callbacks=on}
  set r(*) {}
  db eval {SELECT * FROM test1 WHERE f1<0} r {}
  set r(*)
} {f1 f2}
do_test select1-9.3 {
  set r(*) {}
  db eval {SELECT * FROM test1 WHERE f1<(select count(*) from test2)} r {}
  set r(*)
} {f1 f2}
do_test select1-9.4 {
  set r(*) {}
  db eval {SELECT * FROM test1 ORDER BY f1} r {}
  set r(*)
} {f1 f2}
do_test select1-9.5 {
  set r(*) {}
  db eval {SELECT * FROM test1 WHERE f1<0 ORDER BY f1} r {}
  set r(*)
} {f1 f2}
unset r

finish_test

#
# Run this Tcl script to generate the sqlite.html file.
#
set rcsid {$Id: c_interface.tcl,v 1.18 2001/10/19 16:44:58 drh Exp $}

puts {<html>
<head>
  <title>The C language interface to the SQLite library</title>
</head>
<body bgcolor=white>
<h1 align=center>
The C language interface to the SQLite library
</h1>}
puts "<p align=center>
(This page was last modified on [lrange $rcsid 3 4] GMT)
</p>"

puts {
<p>The SQLite library is designed to be very easy to use from
a C or C++ program.  This document gives an overview of the C/C++
programming interface.</p>

<h2>The Core API</h2>

<p>The interface to the SQLite library consists of three core functions,
one opaque data structure, and some constants used as return values.
The core interface is as follows:</p>

<blockquote><pre>
typedef struct sqlite sqlite;
#define SQLITE_OK           0   /* Successful result */

sqlite *sqlite_open(const char *dbname, int mode, char **errmsg);

void sqlite_close(sqlite*);

int sqlite_exec(
  sqlite*,
  char *sql,
  int (*)(void*,int,char**,char**),
  void*,
  char **errmsg
);





















</pre></blockquote>




<p>
The above is all you really need to know in order to use SQLite









in your C or C++ programs.  There are other convenience functions

available (and described below) but we will begin by describing

the core functions shown above.

</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

argv[i][0] == 0
</pre></blockquote>
<p>But if the i-th parameter is NULL we will get:</p>
<blockquote><pre>
argv[i] == 0
</pre></blockquote>
<p>The names of the columns are contained in the fourth argument.</p>

<p>If the EMPTY_RESULT_CALLBACKS pragma is set to ON and the result of
a query is an empty set, then the callback is invoked once with the
third parameter (argv) set to 0.  In other words
<blockquote><pre>
argv == 0
</pre></blockquote>
The second parameter (argc)
and the fourth parameter (columnNames) are still valid
and can be used to determine the number and names of the result
columns if there had been a result.
The default behavior is not to invoke the callback at all if the
result set is empty.</p>

<p>The callback function should normally return 0.  If the callback
function returns non-zero, the query is immediately aborted and 
<b>sqlite_exec()</b> will return SQLITE_ABORT.</p>

<h2>Error Codes</h2>

<p>
The <b>sqlite_exec()</b> function normally returns SQLITE_OK.  But
if something goes wrong it can return a different value to indicate
the type of error.  Here is a complete list of the return codes:
</p>

<blockquote><pre>
#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   /* The database file is locked */
#define SQLITE_LOCKED       6   /* A table in the database is locked */
#define SQLITE_NOMEM        7   /* A malloc() failed */
#define SQLITE_READONLY     8   /* Attempt to write a readonly database */
#define SQLITE_INTERRUPT    9   /* Operation terminated by sqlite_interrupt() */
#define SQLITE_IOERR       10   /* Some kind of disk I/O error occurred */
#define SQLITE_CORRUPT     11   /* The database disk image is malformed */
#define SQLITE_NOTFOUND    12   /* (Internal Only) Table or record not found */
#define SQLITE_FULL        13   /* Insertion failed because database is full */
#define SQLITE_CANTOPEN    14   /* Unable to open the database file */
#define SQLITE_PROTOCOL    15   /* Database lock protocol error */
#define SQLITE_EMPTY       16   /* (Internal Only) Database table is empty */
#define SQLITE_SCHEMA      17   /* The database schema changed */
#define SQLITE_TOOBIG      18   /* Too much data for one row of a table */
#define SQLITE_CONSTRAINT  19   /* Abort due to contraint violation */
</pre></blockquote>

<p>
The meanings of these various return values are as follows:
</p>

<blockquote>
<dl>
<dt>SQLITE_OK</dt>
<dd><p>This value is returned if everything worked and there were no errors.
</p></dd>
<dt>SQLITE_INTERNAL</dt>

<dt>SQLITE_CONSTRAINT</dt>
<dd><p>This constant is returned if the SQL statement would have violated
a database constraint.
</p></dd>
</dl>
</blockquote>

<h2>The Extended API</h2>

<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>

<blockquote><pre>
int sqlite_get_table(
  sqlite*,
  char *sql,
  char ***result,
  int *nrow,
  int *ncolumn,
  char **errmsg
);

void sqlite_free_table(char**);

void sqlite_interrupt(sqlite*);

int sqlite_complete(const char *sql);

void sqlite_busy_handler(sqlite*, int (*)(void*,const char*,int), void*);

void sqlite_busy_timeout(sqlite*, int ms);

const char sqlite_version[];

const char sqlite_encoding[];

int sqlite_exec_printf(
  sqlite*,
  char *sql,
  int (*)(void*,int,char**,char**),
  void*,
  char **errmsg,
  ...
);

int sqlite_exec_vprintf(
  sqlite*,
  char *sql,
  int (*)(void*,int,char**,char**),
  void*,
  char **errmsg,
  va_list
);

int sqlite_get_table_printf(
  sqlite*,
  char *sql,
  char ***result,
  int *nrow,
  int *ncolumn,
  char **errmsg,
  ...
);

int sqlite_get_table_vprintf(
  sqlite*,
  char *sql,
  char ***result,
  int *nrow,
  int *ncolumn,
  char **errmsg,
  va_list
);

</pre></blockquote>

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

<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
callbacks and write it into memory obtained from malloc().  This
is a convenience function that allows the application to get the
entire result of a database query with a single function call.</p>

result[6] = "D. Richard Hipp"<br>
result[7] = "drh"<br>
result[8] = "zadok"
</blockquote>

<p>Notice that the "host" value for the "dummy" record is NULL so
the result[] array contains a NULL pointer at that slot.</p>

<p>If the result set of a query is empty, then by default
<b>sqlite_get_table()</b> will set nrow to 0 and leave its
result parameter is set to NULL.  But if the EMPTY_RESULT_CALLBACKS
pragma is ON then the result parameter is initialized to the names
of the columns only.  For example, consider this query which has
an empty result set:</p>

<blockquote>
SELECT employee_name, login, host FROM users WHERE employee_name IS NULL;
</blockquote>

<p>
The default behavior gives this results:
</p>

<blockquote>
nrow = 0<br>
ncolumn = 0<br>
result = 0<br>
</blockquote>

<p>
But if the EMPTY_RESULT_CALLBACKS pragma is ON, then the following
is returned:
</p>

<blockquote>
nrow = 0<br>
ncolumn = 3<br>
result[0] = "employee_name"<br>
result[1] = "login"<br>
result[2] = "host"<br>
</blockquote>

<p>Memory to hold the information returned by <b>sqlite_get_table()</b>
is obtained from malloc().  But the calling function should not try
to free this information directly.  Instead, pass the complete table
to <b>sqlite_free_table()</b> when the table is no longer needed.
It is safe to call <b>sqlite_free_table()</b> will a NULL pointer such
as would be returned if the result set is empty.</p>

<p>The <b>sqlite_get_table()</b> routine returns the same integer
result code as <b>sqlite_exec()</b>.</p>

<h2>Interrupting an SQLite operation</h2>

<p>The <b>sqlite_interrupt()</b> function can be called from a

proc chng {date desc} {
  puts "<DT><B>$date</B></DT>"
  puts "<DD><P><UL>$desc</UL></P></DD>"
}

chng {2001 Oct 19 (2.0.6)} {
<li>Added the EMPTY_RESULT_CALLBACKS pragma</li>
<li>Support for UTF-8 and ISO8859 characters in column and table names.</li>
<li>Bug fix: Compute correct table names with the FULL_COLUMN_NAMES pragma
    is turned on.</li>
}

chng {2001 Oct 14 (2.0.5)} {
<li>Added the COUNT_CHANGES pragma.</li>

#
# Run this Tcl script to generate the sqlite.html file.
#
set rcsid {$Id: lang.tcl,v 1.14 2001/10/19 16:44:58 drh Exp $}

puts {<html>
<head>
  <title>Query Language Understood By SQLite</title>
</head>
<body bgcolor=white>
<h1 align=center>

<li><p><b>PRAGMA count_changes = ON;
       <br>PRAGMA count_changes = OFF;</b></p>
    <p>When on, the COUNT_CHANGES pragma causes the callback function to
    be invoked once for each DELETE, INSERT, or UPDATE operation.  The
    argument is the number of rows that were changed.</p>

<li><p><b>PRAGMA empty_result_callbacks = ON;
       <br>PRAGMA empty_result_callbacks = OFF;</b></p>
    <p>When on, the EMPTY_RESULT_CALLBACKS pragma causes the callback
    function to be invoked once for each query that has an empty result
    set.  The third "<b>argv</b>" parameter to the callback is set to NULL
    because there is no data to report.  But the second "<b>argc</b>" and
    fourth "<b>columnNames</b>" parameters are valid and can be used to
    determine the number and names of the columns that would have been in
    the result set had the set not been empty.</p>

<li><p><b>PRAGMA full_column_names = ON;
       <br>PRAGMA full_column_names = OFF;</b></p>
    <p>The column names reported in an SQLite callback are normally just
    the name of the column itself, except for joins when "TABLE.COLUMN"
    is used.  But when full_column_names is turned on, column names are
    always reported as "TABLE.COLUMN" even for simple queries.</p></li>