SQLite

# The library that programs using readline() must link against.
#
LIBREADLINE = @TARGET_READLINE_LIBS@

# Object files for the SQLite library.
#
LIBOBJ = build.o dbbe.o delete.o expr.o insert.o \
         main.o parse.o select.o table.o tokenize.o update.o \
         util.o vdbe.o where.o tclsqlite.o

# All of the source code files.
#
SRC = \
  $(TOP)/src/build.c \
  $(TOP)/src/dbbe.c \
  $(TOP)/src/dbbe.h \
  $(TOP)/src/delete.c \
  $(TOP)/src/expr.c \
  $(TOP)/src/insert.c \
  $(TOP)/src/main.c \
  $(TOP)/src/parse.y \
  $(TOP)/src/select.c \
  $(TOP)/src/shell.c \
  $(TOP)/src/sqlite.h.in \
  $(TOP)/src/sqliteInt.h \
  $(TOP)/src/table.c \
  $(TOP)/src/tclsqlite.c \
  $(TOP)/src/tokenize.c \
  $(TOP)/src/update.c \
  $(TOP)/src/util.c \
  $(TOP)/src/vdbe.c \
  $(TOP)/src/vdbe.h \
  $(TOP)/src/where.c

insert.o:	$(TOP)/src/insert.c $(HDR)
	$(TCC) $(GDBM_FLAGS) -c $(TOP)/src/insert.c

select.o:	$(TOP)/src/select.c $(HDR)
	$(TCC) $(GDBM_FLAGS) -c $(TOP)/src/select.c

table.o:	$(TOP)/src/table.c $(HDR)
	$(TCC) $(GDBM_FLAGS) -c $(TOP)/src/table.c

update.o:	$(TOP)/src/update.c $(HDR)
	$(TCC) $(GDBM_FLAGS) -c $(TOP)/src/update.c

tclsqlite.o:	$(TOP)/src/tclsqlite.c $(HDR)
	$(TCC) $(GDBM_FLAGS) $(TCL_FLAGS) -c $(TOP)/src/tclsqlite.c

gdbmdump:	$(TOP)/tool/gdbmdump.c

1.0.7

**   drh@hwaci.com
**   http://www.hwaci.com/drh/
**
*************************************************************************
** This file contains code to implement the "sqlite" command line
** utility for accessing SQLite databases.
**
** $Id: shell.c,v 1.25 2000/09/29 13:30:55 drh Exp $
*/
#include <stdlib.h>
#include <string.h>
#include <stdio.h>
#include "sqlite.h"
#include <unistd.h>
#include <ctype.h>

/*
** These are the allowed modes.
*/
#define MODE_Line     0  /* One column per line.  Blank line between records */
#define MODE_Column   1  /* One record per line in neat columns */
#define MODE_List     2  /* One record per line with a separator */
#define MODE_Semi     3  /* Same as MODE_List but append ";" to each line */
#define MODE_Html     4  /* Generate an XHTML table */
#define MODE_Insert   5  /* Generate SQL "insert" statements */

/*
** Number of elements in an array
*/
#define ArraySize(X)  (sizeof(X)/sizeof(X[0]))

/*

** invokes for each row of a query result.
*/
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;
      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);
      }
      break;
    }
    case MODE_Column: {
      if( p->cnt++==0 ){
        for(i=0; i<nArg; i++){
          int w, n;

           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);
        }
      }
      for(i=0; i<nArg; i++){

          }
          if( z[j] ){
            fprintf(p->out, "\\%c", z[j]);
            z++;
          }
          z += j;
        }
        if( i<nArg-1 ){
          fprintf(p->out, "%s", p->separator);
        }else if( p->mode==MODE_Semi ){
          fprintf(p->out, ";\n");
        }else{
          fprintf(p->out, "\n");
        }
      }
      break;
    }
    case MODE_Html: {
      if( p->cnt++==0 && p->showHeader ){
        fprintf(p->out,"<TR>");
        for(i=0; i<nArg; i++){

static char zHelp[] = 
  ".dump ?TABLE? ...      Dump the database in an text format\n"
  ".exit                  Exit this program\n"
  ".explain               Set output mode suitable for EXPLAIN\n"
  ".header ON|OFF         Turn display of headers on or off\n"
  ".help                  Show this message\n"
  ".indices TABLE         Show names of all indices on TABLE\n"
  ".mode MODE             Set mode to one of \"line\", \"column\", \n"
  "                       \"insert\", \"list\", or \"html\"\n"
  ".mode insert TABLE     Generate SQL insert statements for TABLE\n"
  ".output FILENAME       Send output to FILENAME\n"
  ".output stdout         Send output to the screen\n"
  ".schema ?TABLE?        Show the CREATE statements\n"
  ".separator STRING      Change separator string for \"list\" mode\n"
  ".tables ?PATTERN?      List names of tables matching a pattern\n"
  ".timeout MS            Try opening locked tables for MS milliseconds\n"

  if( c=='s' && strncmp(azArg[0], "schema", n)==0 ){
    struct callback_data data;
    char *zErrMsg = 0;
    char zSql[1000];
    memcpy(&data, p, sizeof(data));
    data.showHeader = 0;
    data.mode = MODE_Semi;
    if( nArg>1 ){
      sprintf(zSql, "SELECT sql FROM sqlite_master "
                    "WHERE tbl_name LIKE '%.800s' AND type!='meta'"
                    "ORDER BY type DESC, name",
         azArg[1]);
    }else{
      sprintf(zSql, "SELECT sql FROM sqlite_master "

  }else

  if( c=='s' && strncmp(azArg[0], "separator", n)==0 && nArg==2 ){
    sprintf(p->separator, "%.*s", (int)ArraySize(p->separator)-1, azArg[1]);
  }else

  if( c=='t' && n>1 && strncmp(azArg[0], "tables", n)==0 ){
    char **azResult;
    int nRow, rc;
    char *zErrMsg;
    char zSql[1000];



    if( nArg==1 ){
      sprintf(zSql,
        "SELECT name FROM sqlite_master "
        "WHERE type='table' "
        "ORDER BY name");
    }else{
      sprintf(zSql,
        "SELECT name FROM sqlite_master "
        "WHERE type='table' AND name LIKE '%%%.100s%%' "
        "ORDER BY name", azArg[1]);
    }
    rc = sqlite_get_table(db, zSql, &azResult, &nRow, 0, &zErrMsg);
    if( zErrMsg ){
      fprintf(stderr,"Error: %s\n", zErrMsg);
      free(zErrMsg);
    }
    if( rc==SQLITE_OK ){
      int len, maxlen = 0;
      int i, j;
      int nPrintCol, nPrintRow;
      for(i=1; i<=nRow; i++){
        if( azResult[i]==0 ) continue;
        len = strlen(azResult[i]);
        if( len>maxlen ) maxlen = len;
      }
      nPrintCol = 80/(maxlen+2);
      if( nPrintCol<1 ) nPrintCol = 1;
      nPrintRow = (nRow + nPrintCol - 1)/nPrintCol;
      for(i=0; i<nPrintRow; i++){
        for(j=i+1; j<=nRow; j+=nPrintRow){
          char *zSp = j<=nPrintRow ? "" : "  ";
          printf("%s%-*s", zSp, maxlen, azResult[j] ? azResult[j] : "");
        }
        printf("\n");
      }
    }
    sqlite_free_table(azResult);
  }else

  if( c=='t' && n>1 && strncmp(azArg[0], "timeout", n)==0 && nArg>=2 ){
    sqlite_busy_timeout(db, atoi(azArg[1]));
  }else

  if( c=='w' && strncmp(azArg[0], "width", n)==0 ){

**   drh@hwaci.com
**   http://www.hwaci.com/drh/
**
*************************************************************************
** This header file defines the interface that the sqlite library
** presents to client programs.
**
** @(#) $Id: sqlite.h.in,v 1.3 2000/09/29 13:30:55 drh Exp $
*/
#ifndef _SQLITE_H_
#define _SQLITE_H_

/*
** The version of the SQLite library.
*/

** "ms" milleseconds of sleeping, the handler returns 0 which
** causes sqlite_exec() to return SQLITE_BUSY.
**
** Calling this routine with an argument less than or equal to zero
** turns off all busy handlers.
*/
void sqlite_busy_timeout(sqlite*, int ms);

/*
** This next routine is really just a wrapper around sqlite_exec().
** Instead of invoking a user-supplied callback for each row of the
** result, this routine remembers each row of the result in memory
** obtained from malloc(), then returns all of the result after the
** query has finished.  After the calling function has finished using
** the result, it should pass the result data pointer to 
** sqlite_free_table() in order to release the memory that was malloc-ed.
** Because of the way the malloc() happens, the calling function must
** not try to call malloc() directly.  Only sqlite_free_table() is able
** to release the memory properly and safely.
**
** The return value of this routine is the same as from sqlite_exec().
*/
int sqlite_get_table(
  sqlite*,               /* An open database */
  char *sql,             /* SQL to be executed */
  char ***resultp,       /* Result written to a char *[]  that this points to */
  int *nrow,             /* Number of result rows written here */
  int *ncolumn,          /* Number of result columns written here */
  char **errmsg          /* Error msg written here */
);

/*
** Call this routine to free the memory that sqlite_get_table() allocated.
*/
void sqlite_free_table(char **result);


#endif /* _SQLITE_H_ */

/*
** This file contains the sqlite_get_table() and sqlite_free_table()
** interface routines.  These are just wrappers around the main
** interface routine of sqlite_exec().
**
** This routines are in a separate files to that they will not be linked
** if they are not used.
*/
#include <stdlib.h>
#include "sqlite.h"

/*
** This structure is used to pass data from sqlite_get_table() through
** to the callback function is uses to build the result.
*/
typedef struct TabResult {
  char **azResult;
  int nResult;
  int nAlloc;
  int nRow;
  int nColumn;
  int nData;
  int rc;
} TabResult;

/*
** This routine is called once for each row in the result table.  Its job
** is to fill in the TabResult structure appropriately, allocating new
** memory as necessary.
*/
static int sqlite_get_table_cb(void *pArg, int nCol, char **argv, char **colv){
  TabResult *p = (TabResult*)pArg;
  int need;
  int i, len;
  char *z, *zVal;

  /* 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 ){
    p->nColumn = nCol;
    need = nCol*2;
  }else{
    need = nCol;
  }
  if( p->nData + need >= p->nAlloc ){
    p->nAlloc = p->nAlloc*2 + need + 1;
    p->azResult = realloc( p->azResult, sizeof(char*)*p->nAlloc );
    if( p->azResult==0 ){
      p->rc = SQLITE_NOMEM;
      return 1;
    }
  }

  /* If this is the first row, then generate an extra row containing
  ** the names of all columns.
  */
  if( p->nRow==0 ){
    for(i=0; i<nCol; i++){
      if( colv[i]==0 ){
        z = 0;
      }else{
        z = malloc( strlen(colv[i])+1 );
        if( z==0 ){
          p->rc = SQLITE_NOMEM;
          return 1;
        }
        strcpy(z, colv[i]);
      }
      p->azResult[p->nData++] = z;
    }
  }

  /* Copy over the row data
  */
  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.
**
** The result that is written to ***pazResult is held in memory obtained
** from malloc().  But the caller cannot free this memory directly.  
** Instead, the entire table should be passed to sqlite_free_table() when
** the calling procedure is finished using it.
*/
int sqlite_get_table(
  sqlite *db,                 /* The database on which the SQL executes */
  char *zSql,                 /* The SQL to be executed */
  char ***pazResult,          /* Write the result table here */
  int *pnRow,                 /* Write the number of rows in the result here */
  int *pnColumn,              /* Write the number of columns of result here */
  char **pzErrMsg             /* Write error messages here */
){
  int rc;
  TabResult res;
  if( pazResult==0 ){ return SQLITE_ERROR; }
  *pazResult = 0;
  if( pnColumn ) *pnColumn = 0;
  if( pnRow ) *pnRow = 0;
  res.nResult = 0;
  res.nRow = 0;
  res.nColumn = 0;
  res.nData = 1;
  res.nAlloc = 200;
  res.rc = SQLITE_OK;
  res.azResult = malloc( sizeof(char*)*res.nAlloc );
  if( res.azResult==0 ){
    return SQLITE_NOMEM;
  }
  res.azResult[0] = 0;
  rc = sqlite_exec(db, zSql, sqlite_get_table_cb, &res, pzErrMsg);
  if( res.azResult ){
    res.azResult[0] = (char*)res.nData;
  }
  if( rc==SQLITE_ABORT ){
    sqlite_free_table(&res.azResult[1]);
    return res.rc;
  }
  if( rc!=SQLITE_OK ){
    sqlite_free_table(&res.azResult[1]);
    return rc;
  }
  if( res.nAlloc>res.nData ){
    res.azResult = realloc( res.azResult, sizeof(char*)*(res.nData+1) );
    if( res.azResult==0 ) return SQLITE_NOMEM;
  }
  *pazResult = &res.azResult[1];
  if( pnColumn ) *pnColumn = res.nColumn;
  if( pnRow ) *pnRow = res.nRow;
  return rc;
}

/*
** This routine frees the space the sqlite_get_table() malloced.
*/
void sqlite_free_table(
  char **azResult             /* Result returned from from sqlite_get_table() */
){
  if( azResult ){
    int i, n;
    azResult--;
    n = (int)azResult[0];
    for(i=1; i<n; i++){ if( azResult[i] ) free(azResult[i]); }
    free(azResult);
  }
}

	 3675 8925 3675 9450
4 1 0 100 0 0 20 0.0000 4 195 1920 3675 8025 Virtual Machine\001
4 1 0 100 0 0 20 0.0000 4 195 1830 3675 6225 Code Generator\001
4 1 0 100 0 0 20 0.0000 4 195 735 3675 4350 Parser\001
4 1 0 100 0 0 20 0.0000 4 195 1140 3675 2550 Tokenizer\001
4 1 0 100 0 0 20 0.0000 4 195 1020 3675 750 Interface\001
4 1 0 100 0 0 20 0.0000 4 195 990 3675 9825 Backend\001

4 1 0 100 0 0 14 0.0000 4 195 1860 3675 6525 build.c delete.c expr.c\001
4 1 0 100 0 0 14 0.0000 4 195 2115 3675 6750 insert.c select.c update.c\001
4 1 0 100 0 0 14 0.0000 4 150 705 3675 6975 where.c\001
4 1 0 100 0 0 14 0.0000 4 150 630 3675 5175 parse.y\001
4 1 0 100 0 0 14 0.0000 4 150 870 3675 3375 tokenize.c\001
4 1 0 100 0 0 14 0.0000 4 150 570 3675 8775 vdbe.c\001
4 1 0 100 0 0 14 0.0000 4 150 570 3675 10500 dbbe.c\001
4 1 0 100 0 0 14 0.0000 4 195 2040 3675 1575 main.c table.c tclsqlite.c\001

#
# Run this Tcl script to generate the sqlite.html file.
#
set rcsid {$Id: arch.tcl,v 1.3 2000/09/29 13:30:55 drh Exp $}

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

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>

<p>Most of the public interface to the SQLite library is implemented by
four functions found in the <b>main.c</b> source file.  The
<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

#
# Run this Tcl script to generate the sqlite.html file.
#
set rcsid {$Id: c_interface.tcl,v 1.8 2000/09/29 13:30:55 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 API</h2>

<p>The interface to the SQLite library consists of eight functions
(only the first three of which are required),
one opaque data structure, and some constants used as return
values from sqlite_exec():</p>

<blockquote><pre>
typedef struct sqlite sqlite;

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

void sqlite_close(sqlite*);

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

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

void sqlite_free_table(char**);

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[];

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

<p>The main result from <b>sqlite_get_table()</b> is an array of pointers
to strings.  There is one element in this array for each column of
each row in the result.  NULL results are represented by a NULL
pointer. In addition to the regular data, there is an added row at the 
beginning of the array that contains the names of each column of the
result.</p>

<p>As an example, consider the following query:</p>

<blockquote>
SELECT employee_name, login, host FROM users WHERE logic LIKE 'd%';
</blockquote>

<p>This query will return the name, login and host computer name
for every employee whose login begins with the letter "d".  If this
query is submitted to <b>sqlite_get_table()</b> the result might
look like this:</p>

<blockquote>
nrow = 2<br>
ncolumn = 3<br>
result[0] = "employee_name"<br>
result[1] = "login"<br>
result[2] = "host"<br>
result[3] = "dummy"<br>
result[4] = "No such user"<br>
result[5] = 0<br>
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>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.</p>

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

<h2>Testing for a complete SQL statement</h2>

<p>The last interface routine to SQLite is a convenience function used
to test whether or not a string forms a complete SQL statement.
If the <b>sqlite_complete()</b> function returns true when its input
is a string, then the argument forms a complete SQL statement.

}


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

chng {2000 Sep 29 (Not Released)} {
<li>Added the <b>sqlite_get_table()</b> API</li>
<li>Updated the documtation for due to the above change.</li>
<li>Modified the <b>sqlite</b> shell to make use of the new
    sqlite_get_table() API in order to print a list of tables
    in multiple columns, similar to the way "ls" prints filenames.</li>
<li>Modified the <b>sqlite</b> shell to print a semicolon at the
    end of each CREATE statement in the output of the ".schema" command.</li>
}

chng {2000 Sep 21 (Not Released)} {
<li>Change the tclsqlite "eval" method to return a list of results if
    no callback script is specified.</li>
<li>Change tclsqlite.c to use the Tcl_Obj interface</li>
<li>Add tclsqlite.c to the libsqlite.a library</li>
}