SQLite

** sqlite and the code that does the actually reading and writing
** of information to the disk.
**
** This file uses GDBM as the database backend.  It should be
** relatively simple to convert to a different database such
** as NDBM, SDBM, or BerkeleyDB.
**
** $Id: dbbe.c,v 1.12 2000/06/07 14:42:26 drh Exp $
*/
#include "sqliteInt.h"
#include <gdbm.h>
#include <sys/stat.h>
#include <unistd.h>
#include <ctype.h>
#include <time.h>

    return 0;
  }
  if( access(zName, writeFlag ? (X_OK|W_OK|R_OK) : (X_OK|R_OK)) ){
    sqliteSetString(pzErrMsg, "access permission denied", 0);
    return 0;
  }
  zMaster = 0;
  sqliteSetString(&zMaster, zName, "/" MASTER_NAME ".tbl", 0);
  if( stat(zMaster, &statbuf)==0
   && access(zMaster, writeFlag ? (W_OK|R_OK) : R_OK)!=0 ){
    sqliteSetString(pzErrMsg, "access permission denied for ", zMaster, 0);
    sqliteFree(zMaster);
    return 0;
  }
  sqliteFree(zMaster);

**   drh@hwaci.com
**   http://www.hwaci.com/drh/
**
*************************************************************************
** This file contains C code routines that are called by the parser
** to handle INSERT statements.
**
** $Id: insert.c,v 1.7 2000/06/07 14:42:27 drh Exp $
*/
#include "sqliteInt.h"

/*
** This routine is call to handle SQL of the following form:
**
**    insert into TABLE (IDLIST) values(EXPRLIST)
**
** The parameters are the table name and the expression list.
*/
void sqliteInsert(
  Parse *pParse,        /* Parser context */
  Token *pTableName,    /* Name of table into which we are inserting */
  ExprList *pList,      /* List of values to be inserted */
  Select *pSelect,      /* A SELECT statement to use as the data source */
  IdList *pField        /* Field name corresponding to pList.  Might be NULL */
){
  Table *pTab;          /* The table to insert into */
  char *zTab;           /* Name of the table into which we are inserting */
  int i, j, idx;        /* Loop counters */
  Vdbe *v;              /* Generate code into this virtual machine */
  Index *pIdx;          /* For looping over indices of the table */
  int srcTab;           /* Date comes from this temporary cursor if >=0 */
  int nField;           /* Number of columns in the data */
  int base;             /* First available cursor */
  int iCont, iBreak;    /* Beginning and end of the loop over srcTab */

  zTab = sqliteTableNameFromToken(pTableName);
  pTab = sqliteFindTable(pParse->db, zTab);
  sqliteFree(zTab);
  if( pTab==0 ){
    sqliteSetNString(&pParse->zErrMsg, "no such table: ", 0, 
        pTableName->z, pTableName->n, 0);
    pParse->nErr++;
    goto insert_cleanup;
  }
  if( pTab->readOnly ){
    sqliteSetString(&pParse->zErrMsg, "table ", pTab->zName,
        " may not be modified", 0);
    pParse->nErr++;
    goto insert_cleanup;
  }
  v = pParse->pVdbe;
  if( v==0 ){
    v = pParse->pVdbe = sqliteVdbeCreate(pParse->db->pBe);
  }
  if( v==0 ) goto insert_cleanup;
  if( pSelect ){
    int rc;
    srcTab = pParse->nTab++;
    sqliteVdbeAddOp(v, OP_Open, srcTab, 1, 0, 0);
    rc = sqliteSelect(pParse, pSelect, SRT_Table, srcTab);
    if( rc ) goto insert_cleanup;
    assert( pSelect->pEList );
    nField = pSelect->pEList->nExpr;
  }else{
    srcTab = -1;
    assert( pList );
    nField = pList->nExpr;
  }
  if( pField==0 && nField!=pTab->nCol ){
    char zNum1[30];
    char zNum2[30];
    sprintf(zNum1,"%d", nField);
    sprintf(zNum2,"%d", pTab->nCol);
    sqliteSetString(&pParse->zErrMsg, "table ", pTab->zName,
       " has ", zNum2, " columns but ",
       zNum1, " values were supplied", 0);
    pParse->nErr++;
    goto insert_cleanup;
  }
  if( pField!=0 && nField!=pField->nId ){
    char zNum1[30];
    char zNum2[30];
    sprintf(zNum1,"%d", nField);
    sprintf(zNum2,"%d", pField->nId);
    sqliteSetString(&pParse->zErrMsg, zNum1, " values for ",
       zNum2, " columns", 0);
    pParse->nErr++;
    goto insert_cleanup;
  }
  if( pField ){

        sqliteSetString(&pParse->zErrMsg, "table ", pTab->zName,
           " has no column named ", pField->a[i].zName, 0);
        pParse->nErr++;
        goto insert_cleanup;
      }
    }
  }
  base = pParse->nTab;





  sqliteVdbeAddOp(v, OP_Open, base, 1, pTab->zName, 0);
  for(idx=1, pIdx=pTab->pIndex; pIdx; pIdx=pIdx->pNext, idx++){
    sqliteVdbeAddOp(v, OP_Open, idx+base, 1, pIdx->zName, 0);
  }
  if( srcTab>=0 ){
    sqliteVdbeAddOp(v, OP_Rewind, srcTab, 0, 0, 0);
    iBreak = sqliteVdbeMakeLabel(v);
    iCont = sqliteVdbeAddOp(v, OP_Next, srcTab, iBreak, 0, 0);
  }
  sqliteVdbeAddOp(v, OP_New, 0, 0, 0, 0);
  if( pTab->pIndex ){
    sqliteVdbeAddOp(v, OP_Dup, 0, 0, 0, 0);
  }
  for(i=0; i<pTab->nCol; i++){
    if( pField==0 ){
      j = i;
    }else{
      for(j=0; j<pField->nId; j++){
        if( pField->a[j].idx==i ) break;
      }
    }
    if( pField && j>=pField->nId ){
      char *zDflt = pTab->aCol[i].zDflt;
      if( zDflt==0 ){
        sqliteVdbeAddOp(v, OP_Null, 0, 0, 0, 0);
      }else{
        sqliteVdbeAddOp(v, OP_String, 0, 0, zDflt, 0);
      }
    }else if( srcTab>=0 ){
      sqliteVdbeAddOp(v, OP_Field, srcTab, i, 0, 0); 
    }else{
      sqliteExprCode(pParse, pList->a[j].pExpr);
    }
  }
  sqliteVdbeAddOp(v, OP_MakeRecord, pTab->nCol, 0, 0, 0);
  sqliteVdbeAddOp(v, OP_Put, base, 0, 0, 0);
  /* sqliteVdbeAddOp(v, OP_Close, 0, 0, 0, 0); */
  for(idx=1, pIdx=pTab->pIndex; pIdx; pIdx=pIdx->pNext, idx++){
    if( pIdx->pNext ){
      sqliteVdbeAddOp(v, OP_Dup, 0, 0, 0, 0);
    }
    for(i=0; i<pIdx->nField; i++){
      int idx = pIdx->aiField[i];
      if( pField==0 ){
        j = idx;
      }else{
        for(j=0; j<pField->nId; j++){
          if( pField->a[j].idx==idx ) break;
        }
      }
      if( pField && j>=pField->nId ){
        char *zDflt = pTab->aCol[idx].zDflt;
        if( zDflt==0 ){
          sqliteVdbeAddOp(v, OP_Null, 0, 0, 0, 0);
        }else{
          sqliteVdbeAddOp(v, OP_String, 0, 0, zDflt, 0);
        }
      }else if( srcTab>=0 ){
        sqliteVdbeAddOp(v, OP_Field, srcTab, i, 0, 0); 
      }else{
        sqliteExprCode(pParse, pList->a[j].pExpr);
      }
    }
    sqliteVdbeAddOp(v, OP_MakeKey, pIdx->nField, 0, 0, 0);
    sqliteVdbeAddOp(v, OP_PutIdx, idx+base, 0, 0, 0);
    /* sqliteVdbeAddOp(v, OP_Close, idx, 0, 0, 0); */
  }
  if( srcTab>=0 ){
    sqliteVdbeAddOp(v, OP_Goto, 0, iCont, 0, 0);
    sqliteVdbeAddOp(v, OP_Noop, 0, 0, 0, iBreak);
  }

insert_cleanup:
  if( pList ) sqliteExprListDelete(pList);
  if( pSelect ) sqliteSelectDelete(pSelect);
  sqliteIdListDelete(pField);
}

**
*************************************************************************
** Main file for the SQLite library.  The routines in this file
** implement the programmer interface to the library.  Routines in
** other files are for internal use by SQLite and should not be
** accessed by users of the library.
**
** $Id: main.c,v 1.10 2000/06/07 14:42:27 drh Exp $
*/
#include "sqliteInt.h"

/*
** This is the callback routine for the code that initializes the
** database.  Each callback contains text of a CREATE TABLE or
** CREATE INDEX statement that must be parsed to yield the internal

    sqliteStrRealloc(pzErrMsg);
    return 0;
  }
  
  /* Open the backend database driver */
  db->pBe = sqliteDbbeOpen(zFilename, (mode&0222)!=0, mode!=0, pzErrMsg);
  if( db->pBe==0 ){
    sqliteStrRealloc(pzErrMsg);
    sqliteFree(db);
    return 0;
  }

  /* Attempt to read the schema */
  rc = sqliteInit(db, pzErrMsg);
  if( rc!=SQLITE_OK && rc!=SQLITE_BUSY ){

**
*************************************************************************
** This file contains SQLite's grammar for SQL.  Process this file
** using the lemon parser generator to generate C code that runs
** the parser.  Lemon will also generate a header file containing
** numeric codes for all of the tokens.
**
** @(#) $Id: parse.y,v 1.15 2000/06/07 14:42:27 drh Exp $
*/
%token_prefix TK_
%token_type {Token}
%extra_argument {Parse *pParse}
%syntax_error {
  sqliteSetNString(&pParse->zErrMsg,"syntax error near \"",0,TOKEN.z,TOKEN.n,
                   "\"", 1, 0);

    {sqliteUpdate(pParse,&X,Y,Z);}

setlist(A) ::= ID(X) EQ expr(Y) COMMA setlist(Z).
    {A = sqliteExprListAppend(Z,Y,&X);}
setlist(A) ::= ID(X) EQ expr(Y).   {A = sqliteExprListAppend(0,Y,&X);}

cmd ::= INSERT INTO ID(X) fieldlist_opt(F) VALUES LP itemlist(Y) RP.
               {sqliteInsert(pParse, &X, Y, 0, F);}
cmd ::= INSERT INTO ID(X) fieldlist_opt(F) select(S).
               {sqliteInsert(pParse, &X, 0, S, F);}


%type itemlist {ExprList*}
%destructor itemlist {sqliteExprListDelete($$);}
%type item {Expr*}
%destructor item {sqliteExprDelete($$);}

**   drh@hwaci.com
**   http://www.hwaci.com/drh/
**
*************************************************************************
** This file contains C code routines that are called by the parser
** to handle SELECT statements.
**
** $Id: select.c,v 1.15 2000/06/07 14:42:27 drh Exp $
*/
#include "sqliteInt.h"

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

  ** table iParm.
  */
  if( eDest==SRT_Union ){
    sqliteVdbeAddOp(v, OP_MakeRecord, nField, 0, 0, 0);
    sqliteVdbeAddOp(v, OP_String, iParm, 0, "", 0);
    sqliteVdbeAddOp(v, OP_Put, iParm, 0, 0, 0);
  }else 

  /* Store the result as data using a unique key.
  */
  if( eDest==SRT_Table ){
    sqliteVdbeAddOp(v, OP_MakeRecord, nField, 0, 0, 0);
    sqliteVdbeAddOp(v, OP_New, iParm, 0, 0, 0);
    sqliteVdbeAddOp(v, OP_Pull, 1, 0, 0, 0);
    sqliteVdbeAddOp(v, OP_Put, iParm, 0, 0, 0);
  }else 

  /* Construct a record from the query result, but instead of
  ** saving that record, use it as a key to delete elements from
  ** the temporary table iParm.
  */
  if( eDest==SRT_Except ){
    sqliteVdbeAddOp(v, OP_MakeRecord, nField, 0, 0, 0);

        sqliteVdbeDequoteP3(v, addr);
      }
      continue;
    }
    p = pEList->a[i].pExpr;
    if( p->op!=TK_FIELD || pTabList==0 ){
      char zName[30];
      sprintf(zName, "column%d", i+1);
      sqliteVdbeAddOp(v, OP_ColumnName, i, 0, zName, 0);
    }else{
      if( pTabList->nId>1 ){
        char *zName = 0;
        Table *pTab = pTabList->a[p->iTable].pTab;
        char *zTab;
 

** Author contact information:
**   drh@hwaci.com
**   http://www.hwaci.com/drh/
**
*************************************************************************
** Internal interface definitions for SQLite.
**
** @(#) $Id: sqliteInt.h,v 1.20 2000/06/07 14:42:27 drh Exp $
*/
#include "sqlite.h"
#include "dbbe.h"
#include "vdbe.h"
#include "parse.h"
#include <gdbm.h>
#include <stdio.h>

** The results of a select can be distributed in several ways.
*/
#define SRT_Callback     1  /* Invoke a callback with each row of result */
#define SRT_Mem          2  /* Store result in a memory cell */
#define SRT_Set          3  /* Store result as unique keys in a table */
#define SRT_Union        5  /* Store result as keys in a table */
#define SRT_Except       6  /* Remove result from a UNION table */
#define SRT_Table        7  /* Store result as data with a unique key */

/*
** When a SELECT uses aggregate functions (like "count(*)" or "avg(f1)")
** we have to do some additional analysis of expressions.  An instance
** of the following structure holds information about a single subexpression
** somewhere in the SELECT statement.  An array of these structures holds
** all the information we need to generate code for aggregate

void sqliteExprListDelete(ExprList*);
void sqliteStartTable(Parse*,Token*,Token*);
void sqliteAddColumn(Parse*,Token*);
void sqliteAddDefaultValue(Parse*,Token*,int);
void sqliteEndTable(Parse*,Token*);
void sqliteDropTable(Parse*, Token*);
void sqliteDeleteTable(sqlite*, Table*);
void sqliteInsert(Parse*, Token*, ExprList*, Select*, IdList*);
IdList *sqliteIdListAppend(IdList*, Token*);
void sqliteIdListAddAlias(IdList*, Token*);
void sqliteIdListDelete(IdList*);
void sqliteCreateIndex(Parse*, Token*, Token*, IdList*, Token*, Token*);
void sqliteDropIndex(Parse*, Token*);
int sqliteSelect(Parse*, Select*, int, int);
Select *sqliteSelectNew(ExprList*,IdList*,Expr*,ExprList*,Expr*,ExprList*,int);

# Copyright (c) 1999, 2000 D. Richard Hipp
#
# This program is free software; you can redistribute it and/or
# modify it under the terms of the GNU General Public
# License as published by the Free Software Foundation; either
# version 2 of the License, or (at your option) any later version.
#
# This program is distributed in the hope that it will be useful,
# but WITHOUT ANY WARRANTY; without even the implied warranty of
# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the GNU
# General Public License for more details.
# 
# You should have received a copy of the GNU General Public
# License along with this library; if not, write to the
# Free Software Foundation, Inc., 59 Temple Place - Suite 330,
# Boston, MA  02111-1307, USA.
#
# Author contact information:
#   drh@hwaci.com
#   http://www.hwaci.com/drh/
#
#***********************************************************************
# This file implements regression tests for SQLite library.  The
# focus of this file is exercising the code in dbbe.c.
#
# $Id: dbbe.test,v 1.1 2000/06/07 14:42:27 drh Exp $

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

# Try to open a database that does not exist.
#
do_test dbbe-1.1 {
  catch {db close}
  file delete -force testdb
  set v [catch {sqlite db testdb 0444} msg]
  lappend v $msg
} {1 {can't find directory "testdb"}}
do_test dbbe-1.2 {
  catch {db close}
  file delete -force testdb
  set v [catch {sqlite db testdb/dummy 0666} msg]
  lappend v $msg
} {1 {can't find or create directory "testdb/dummy"}}

# Try to open a database for writing in a directory that
# doesn't exist but for which the parent directory does
# exist.  This should work!
#
do_test dbbe-1.3 {
  catch {db close}
  file delete -force testdb
  set v [catch {sqlite db testdb 0666} msg]
  lappend v $msg
} {0 {}}

# Try to open a file instead of a directory.
#
do_test dbbe-1.4 {
  catch {db close}
  file delete -force testdb
  set fd [open testdb w]
  puts $fd hi!
  close $fd
  set v [catch {sqlite db testdb 0666} msg]
  lappend v $msg
} {1 {not a directory: "testdb"}}

# Access permission denied on the directory.
#
do_test dbbe-1.5 {
  catch {db close}
  file delete -force testdb
  file mkdir testdb
  file attributes testdb -permissions 0
  set v [catch {sqlite db testdb 0666} msg]
  lappend v $msg
} {1 {access permission denied}}

# Access permission denied on the master file
#
do_test dbbe-1.6 {
  catch {db close}
  file delete -force testdb
  sqlite db testdb 0666
  execsql {CREATE TABLE t1(x int)}
  db close
  file attributes testdb/sqlite_master.tbl -permission 0444
  set v [catch {sqlite db testdb 0666} msg]
  lappend v $msg
} {1 {access permission denied for testdb/sqlite_master.tbl}}
do_test dbbe-1.6b {
  catch {db close}
  file delete -force testdb
  sqlite db testdb 0666
  execsql {CREATE TABLE t1(x int)}
  db close
  file attributes testdb/sqlite_master.tbl -permission 0444
  set v [catch {sqlite db testdb 0444} msg]
  lappend v $msg
} {0 {}}

# Make sure a table can be accessed by either uppercase or lowercase
# names
#
do_test dbbe-2.1 {
  catch {db close}
  file delete -force testdb
  sqlite db testdb 0666
  execsql {
    CREATE TABLE t1(x int);
    INSERT INTO t1 VALUES(1);
  }
  db close
  sqlite db testdb 0444
  set r [execsql {SELECT * FROM T1}]
  db close
  sqlite db testdb 0666
  lappend r [execsql {SELECT * FROM t1}]
} {1 1}

finish_test

#   drh@hwaci.com
#   http://www.hwaci.com/drh/
#
#***********************************************************************
# This file implements regression tests for SQLite library.  The
# focus of this file is testing the INSERT statement.
#
# $Id: insert.test,v 1.4 2000/06/07 14:42:27 drh Exp $

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

# Try to insert into a non-existant table.
#
do_test insert-1.1 {

  execsql {SELECT * FROM test2 ORDER BY f1}
} {1 -4.32 hi abc-123 xyzzy 111 -2.22 hi hi! {}}

# Do additional inserts with default values, but this time
# on a table that has indices.  In particular we want to verify
# that the correct default values are inserted into the indices.
#
do_test insert-3.1 {
  execsql {
    DELETE FROM test2;
    CREATE INDEX index9 ON test2(f1,f2);
    CREATE INDEX indext ON test2(f4,f5);
    SELECT * from test2;
  }


} {}
do_test insert-3.2 {
  execsql {INSERT INTO test2(f2,f4) VALUES(-3.33,'hum')}
  execsql {SELECT * FROM test2 WHERE f1=111 AND f2=-3.33}
} {111 -3.33 hi hum {}}
do_test insert-3.3 {
  execsql {INSERT INTO test2(f1,f2,f5) VALUES(22,-4.44,'wham')}

# Copyright (c) 1999, 2000 D. Richard Hipp
#
# This program is free software; you can redistribute it and/or
# modify it under the terms of the GNU General Public
# License as published by the Free Software Foundation; either
# version 2 of the License, or (at your option) any later version.
#
# This program is distributed in the hope that it will be useful,
# but WITHOUT ANY WARRANTY; without even the implied warranty of
# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the GNU
# General Public License for more details.
# 
# You should have received a copy of the GNU General Public
# License along with this library; if not, write to the
# Free Software Foundation, Inc., 59 Temple Place - Suite 330,
# Boston, MA  02111-1307, USA.
#
# Author contact information:
#   drh@hwaci.com
#   http://www.hwaci.com/drh/
#
#***********************************************************************
# This file implements regression tests for SQLite library.  The
# focus of this file is testing the INSERT statement that takes is
# result from a SELECT.
#
# $Id: insert2.test,v 1.1 2000/06/07 14:42:27 drh Exp $

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

# Create some tables with data that we can select against
#
do_test insert2-1.0 {
  execsql {CREATE TABLE d1(n int, log int);}
  for {set i 1} {$i<=20} {incr i} {
    for {set j 0} {pow(2,$j)<$i} {incr j} {}
    execsql "INSERT INTO d1 VALUES($i,$j)"
  }
  execsql {SELECT * FROM d1 ORDER BY n}
} {1 0 2 1 3 2 4 2 5 3 6 3 7 3 8 3 9 4 10 4 11 4 12 4 13 4 14 4 15 4 16 4 17 5 18 5 19 5 20 5}

# Insert into a new table from the old one.
#
do_test insert2-1.1 {
  execsql {
    CREATE TABLE t1(log int, cnt int);
    INSERT INTO t1 SELECT log, count(*) FROM d1 GROUP BY log;
  }
  execsql {SELECT * FROM t1 ORDER BY log}
} {0 1 1 1 2 2 3 4 4 8 5 4}
do_test insert2-1.2 {
  catch {execsql {DROP TABLE t1}}
  execsql {
    CREATE TABLE t1(log int, cnt int);
    INSERT INTO t1 
       SELECT log, count(*) FROM d1 GROUP BY log
       EXCEPT SELECT n-1,log FROM d1;
    SELECT * FROM t1 ORDER BY log;
  }
} {0 1 3 4 4 8 5 4}
do_test insert2-1.3 {
  catch {execsql {DROP TABLE t1}}
  execsql {
    CREATE TABLE t1(log int, cnt int);
    INSERT INTO t1 
       SELECT log, count(*) FROM d1 GROUP BY log
       INTERSECT SELECT n-1,log FROM d1;
    SELECT * FROM t1 ORDER BY log;
  }
} {1 1 2 2}

finish_test

#   drh@hwaci.com
#   http://www.hwaci.com/drh/
#
#***********************************************************************
# This file implements regression tests for SQLite library.  The
# focus of this file is testing the SELECT statement.
#
# $Id: select1.test,v 1.4 2000/06/07 14:42:27 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-6.4 {
  set v [catch {execsql2 {SELECT f1+F2 as xyzzy FROM test1 ORDER BY f2}} msg]
  lappend v $msg
} {0 {xyzzy 33 xyzzy 77}}
do_test select1-6.5 {
  set v [catch {execsql2 {SELECT test1.f1+F2 FROM test1 ORDER BY f2}} msg]
  lappend v $msg
} {0 {column1 33 column1 77}}
do_test select1-6.6 {
  set v [catch {execsql2 {SELECT test1.f1+F2, t1 FROM test1, test2 
         ORDER BY f2}} msg]
  lappend v $msg
} {0 {column1 33 test2.t1 abc column1 77 test2.t1 abc}}
do_test select1-6.7 {
  set v [catch {execsql2 {SELECT A.f1, t1 FROM test1 as A, test2 
         ORDER BY f2}} msg]
  lappend v $msg
} {0 {A.f1 11 test2.t1 abc A.f1 33 test2.t1 abc}}
do_test select1-6.8 {
  set v [catch {execsql2 {SELECT A.f1, f1 FROM test1 as A, test1 as B