SQLite

/*
** 2004 April 6
**
** 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.
**
*************************************************************************
** $Id: btree.c,v 1.147 2004/05/26 00:01:54 drh Exp $
**
** This file implements a external (disk-based) database using BTrees.
** For a detailed discussion of BTrees, refer to
**
**     Donald E. Knuth, THE ART OF COMPUTER PROGRAMMING, Volume 3:
**     "Sorting And Searching", pages 473-480. Addison-Wesley
**     Publishing Company, Reading, Massachusetts.

}

/*
** Copy the complete content of pBtFrom into pBtTo.  A transaction
** must be active for both files.
**
** The size of file pBtFrom may be reduced by this operation.
** If anything goes wrong, the transaction on pBtFrom is rolled back.
*/
int sqlite3BtreeCopyFile(Btree *pBtTo, Btree *pBtFrom){
  int rc = SQLITE_OK;
  Pgno i, nPage, nToPage;

  if( !pBtTo->inTrans || !pBtFrom->inTrans ) return SQLITE_ERROR;
  if( pBtTo->pCursor ) return SQLITE_BUSY;

**     DROP INDEX
**     creating ID lists
**     BEGIN TRANSACTION
**     COMMIT
**     ROLLBACK
**     PRAGMA
**
** $Id: build.c,v 1.198 2004/05/29 02:37:19 danielk1977 Exp $
*/
#include "sqliteInt.h"
#include <ctype.h>

/*
** This routine is called when a new SQL statement is beginning to
** be parsed.  Check to see if the schema for the database needs

          "the temporary database file");
        return;
      }
    }
  }

  /* Make sure the new table name does not collide with an existing
  ** index or table name.  Issue an error message if it does.
  **
  ** If we are re-reading the sqlite_master table because of a schema
  ** change and a new permanent table is found whose name collides with
  ** an existing temporary table, that is not an error.
  */
  pTable = sqlite3FindTable(db, zName, 0);
  if( pTable!=0 && (pTable->iDb==iDb || !db->init.busy) ){
    sqlite3ErrorMsg(pParse, "table %T already exists", pName);
    sqliteFree(zName);
    return;
  }
  if( (pIdx = sqlite3FindIndex(db, zName, 0))!=0 &&
          (pIdx->iDb==0 || !db->init.busy) ){
    sqlite3ErrorMsg(pParse, "there is already an index named %s", zName);

  ** own name.
  */
  if( pName && !db->init.busy ){
    Index *pISameName;    /* Another index with the same name */
    Table *pTSameName;    /* A table with same name as the index */
    zName = sqliteStrNDup(pName->z, pName->n);
    if( zName==0 ) goto exit_create_index;
    if( (pISameName = sqlite3FindIndex(db, zName, 0))!=0 ){
      sqlite3ErrorMsg(pParse, "index %s already exists", zName);
      goto exit_create_index;
    }
    if( (pTSameName = sqlite3FindTable(db, zName, 0))!=0 ){
      sqlite3ErrorMsg(pParse, "there is already a table named %s", zName);
      goto exit_create_index;
    }

**
*************************************************************************
** 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.195 2004/05/27 23:56:16 danielk1977 Exp $
*/
#include "sqliteInt.h"
#include "os.h"
#include <ctype.h>

/*
** A pointer to this structure is used to communicate information

      sqlite3BtreeCloseCursor(curMain);
      return rc;
    }
  }else{
    memset(meta, 0, sizeof(meta));
  }
  db->aDb[iDb].schema_cookie = meta[0];
  if( iDb==0 ){
    db->next_cookie = meta[0];
    db->file_format = meta[1];
    if( meta[4] ){
      /* If meta[4] is still zero, then we are opening a previously empty
      ** file. Leave db->enc to the default value set by the sqlite3_open()

      ** call in this case.
      */



      db->enc = (u8)meta[4];







    }




    size = meta[2];
    if( size==0 ){ size = MAX_PAGES; }
    db->cache_size = size;
    db->safety_level = meta[3];
    if( meta[5]>0 && meta[5]<=2 && db->temp_store==0 ){
      db->temp_store = meta[5];
    }
    if( db->safety_level==0 ) db->safety_level = 2;

    /*

    **  file_format==1    Version 3.0.0.
    */
    if( db->file_format==0 ){
      /* This happens if the database was initially empty */
      db->file_format = 1;


    }else if( db->file_format>1 ){




      sqlite3BtreeCloseCursor(curMain);
      sqlite3SetString(pzErrMsg, "unsupported file format", (char*)0);
      return SQLITE_ERROR;
    }
  }else if( db->file_format!=meta[1] ){
    if( meta[1]==0 ){
      sqlite3SetString(pzErrMsg, "cannot attach empty database: ",
         db->aDb[iDb].zName, (char*)0);
    }else{
      sqlite3SetString(pzErrMsg, "incompatible file format in auxiliary "
         "database: ", db->aDb[iDb].zName, (char*)0);
    }
    sqlite3BtreeClose(db->aDb[iDb].pBt);
    db->aDb[iDb].pBt = 0;
    return SQLITE_FORMAT;
  }
  sqlite3BtreeSetCacheSize(db->aDb[iDb].pBt, db->cache_size);
  sqlite3BtreeSetSafetyLevel(db->aDb[iDb].pBt, meta[3]==0 ? 2 : meta[3]);

  /* Read the schema information out of the schema tables
  */
  assert( db->init.busy );
  sqlite3SafetyOff(db);
  if( rc==SQLITE_EMPTY ){
    /* For an empty database, there is nothing to read */
    rc = SQLITE_OK;
  }else{

    if( iDb==0 ){
      /* This SQL statement tries to read the temp.* schema from the
      ** sqlite_temp_master table. It might return SQLITE_EMPTY. 
      */
      rc = sqlite3_exec(db, init_script1, sqlite3InitCallback, &initData, 0);
      if( rc==SQLITE_OK || rc==SQLITE_EMPTY ){
        rc = sqlite3_exec(db, init_script2, sqlite3InitCallback, &initData, 0);

**
*************************************************************************
** 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.123 2004/05/29 02:37:19 danielk1977 Exp $
*/
%token_prefix TK_
%token_type {Token}
%default_type {Token}
%extra_argument {Parse *pParse}
%syntax_error {
  if( pParse->zErrMsg==0 ){

number(A) ::= INTEGER(X).  {A = X;}
number(A) ::= FLOAT(X).    {A = X;}
plus_opt ::= PLUS.
plus_opt ::= .

//////////////////////////// The CREATE TRIGGER command /////////////////////

cmd ::= CREATE(A) trigger_decl BEGIN trigger_cmd_list(S) END(Z). {
  Token all;
  all.z = A.z;
  all.n = (Z.z - A.z) + Z.n;
  sqlite3FinishTrigger(pParse, S, &all);
}

trigger_decl ::= temp(T) TRIGGER nm(B) dbnm(Z) trigger_time(C) trigger_event(D)
                 ON nm(E) dbnm(DB) foreach_clause(F) when_clause(G). {
  SrcList *pTab = sqlite3SrcListAppend(0, &E, &DB);
  sqlite3BeginTrigger(pParse, &B, &Z, C, D.a, D.b, pTab, F, G, T);

}

%type trigger_time  {int}
trigger_time(A) ::= BEFORE.      { A = TK_BEFORE; }
trigger_time(A) ::= AFTER.       { A = TK_AFTER;  }
trigger_time(A) ::= INSTEAD OF.  { A = TK_INSTEAD;}
trigger_time(A) ::= .            { A = TK_BEFORE; }

  if( !db->init.busy ){
    static VdbeOpList insertTrig[] = {
      { OP_NewRecno,   0, 0,  0          },
      { OP_String,     0, 0,  "trigger"  },
      { OP_String,     0, 0,  0          },  /* 2: trigger name */
      { OP_String,     0, 0,  0          },  /* 3: table name */
      { OP_Integer,    0, 0,  0          },

      { OP_String,     0, 0,  0          },  /* 5: SQL */

      { OP_MakeRecord, 5, 0,  "tttit"    },
      { OP_PutIntKey,  0, 0,  0          },
    };
    int addr;
    Vdbe *v;

    /* Make an entry in the sqlite_master table */
    v = sqlite3GetVdbe(pParse);
    if( v==0 ) goto triggerfinish_cleanup;
    sqlite3BeginWriteOperation(pParse, 0, nt->iDb);
    sqlite3OpenMasterTable(v, nt->iDb);
    addr = sqlite3VdbeAddOpList(v, ArraySize(insertTrig), insertTrig);
    sqlite3VdbeChangeP3(v, addr+2, nt->name, 0); 
    sqlite3VdbeChangeP3(v, addr+3, nt->table, 0); 
    sqlite3VdbeChangeP3(v, addr+5, pAll->z, pAll->n);
    if( nt->iDb==0 ){
      sqlite3ChangeCookie(db, v, 0);
    }
    sqlite3VdbeAddOp(v, OP_Close, 0, 0);
    sqlite3EndWriteOperation(pParse);
  }

**
*************************************************************************
** This file contains code used to implement the VACUUM command.
**
** Most of the code in this file may be omitted by defining the
** SQLITE_OMIT_VACUUM macro.
**
** $Id: vacuum.c,v 1.16 2004/05/22 09:21:21 danielk1977 Exp $
*/
#include "sqliteInt.h"
#include "os.h"

/*
** A structure for holding a dynamic string - a string that can grow
** without bound. 
*/
typedef struct dynStr dynStr;
struct dynStr {
  char *z;        /* Text of the string in space obtained from sqliteMalloc() */
  int nAlloc;     /* Amount of space allocated to z[] */
  int nUsed;      /* Next unused slot in z[] */
};

/*
** A structure that holds the vacuum context
*/
typedef struct vacuumStruct vacuumStruct;
struct vacuumStruct {
  sqlite *dbOld;       /* Original database */
  sqlite *dbNew;       /* New database */
  char **pzErrMsg;     /* Write errors here */
  int rc;              /* Set to non-zero on an error */
  const char *zTable;  /* Name of a table being copied */
  const char *zPragma; /* Pragma to execute with results */
  dynStr s1, s2;       /* Two dynamic strings */
};

#if !defined(SQLITE_OMIT_VACUUM) || SQLITE_OMIT_VACUUM
/*
** Append text to a dynamic string
*/
static void appendText(dynStr *p, const char *zText, int nText){
  if( nText<0 ) nText = strlen(zText);
  if( p->z==0 || p->nUsed + nText + 1 >= p->nAlloc ){
    char *zNew;
    p->nAlloc = p->nUsed + nText + 1000;
    zNew = sqliteRealloc(p->z, p->nAlloc);
    if( zNew==0 ){
      sqliteFree(p->z);
      memset(p, 0, sizeof(*p));
      return;
    }
    p->z = zNew;
  }
  memcpy(&p->z[p->nUsed], zText, nText+1);
  p->nUsed += nText;
}

/*
** Append text to a dynamic string, having first put the text in quotes.
*/
static void appendQuoted(dynStr *p, const char *zText){
  int i, j;
  appendText(p, "'", 1);
  for(i=j=0; zText[i]; i++){
    if( zText[i]=='\'' ){
      appendText(p, &zText[j], i-j+1);
      j = i + 1;
      appendText(p, "'", 1);
    }
  }
  if( j<i ){
    appendText(p, &zText[j], i-j);
  }
  appendText(p, "'", 1);
}

/*
** Execute statements of SQL.  If an error occurs, write the error
** message into *pzErrMsg and return non-zero.
*/
static int execsql(char **pzErrMsg, sqlite *db, const char *zSql){ 
  char *zErrMsg = 0;
  int rc;

  /* printf("***** executing *****\n%s\n", zSql); */
  rc = sqlite3_exec(db, zSql, 0, 0, &zErrMsg);
  if( zErrMsg ){
    sqlite3SetString(pzErrMsg, zErrMsg, (char*)0);
    sqlite3_freemem(zErrMsg);
  }
  return rc;
}

/*
** This is the second stage callback.  Each invocation contains all the
** data for a single row of a single table in the original database.  This
** routine must write that information into the new database.
*/
static int vacuumCallback2(void *pArg, int argc, char **argv, char **NotUsed){
  vacuumStruct *p = (vacuumStruct*)pArg;
  const char *zSep = "(";
  int i;

  if( argv==0 ) return 0;
  p->s2.nUsed = 0;
  appendText(&p->s2, "INSERT INTO ", -1);
  appendQuoted(&p->s2, p->zTable);
  appendText(&p->s2, " VALUES", -1);
  for(i=0; i<argc; i++){
    appendText(&p->s2, zSep, 1);
    zSep = ",";
    if( argv[i]==0 ){
      appendText(&p->s2, "NULL", 4);
    }else{
      appendQuoted(&p->s2, argv[i]);
    }
  }
  appendText(&p->s2,")", 1);
  p->rc = execsql(p->pzErrMsg, p->dbNew, p->s2.z);
  return p->rc;
}

/*
** This is the first stage callback.  Each invocation contains three
** arguments where are taken from the SQLITE_MASTER table of the original
** database:  (1) the entry type, (2) the entry name, and (3) the SQL for
** the entry.  In all cases, execute the SQL of the third argument.
** For tables, run a query to select all entries in that table and 
** transfer them to the second-stage callback.
*/
static int vacuumCallback1(void *pArg, int argc, char **argv, char **NotUsed){
  vacuumStruct *p = (vacuumStruct*)pArg;
  int rc = 0;
  assert( argc==3 );
  if( argv==0 ) return 0;
  assert( argv[0]!=0 );
  assert( argv[1]!=0 );
  assert( argv[2]!=0 );
  rc = execsql(p->pzErrMsg, p->dbNew, argv[2]);
  if( rc==SQLITE_OK && strcmp(argv[0],"table")==0 ){
    char *zErrMsg = 0;
    p->s1.nUsed = 0;
    appendText(&p->s1, "SELECT * FROM ", -1);
    appendQuoted(&p->s1, argv[1]);
    p->zTable = argv[1];
    rc = sqlite3_exec(p->dbOld, p->s1.z, vacuumCallback2, p, &zErrMsg);
    if( zErrMsg ){
      sqlite3SetString(p->pzErrMsg, zErrMsg, (char*)0);
      sqlite3_freemem(zErrMsg);
    }
  }
  if( rc!=SQLITE_ABORT ) p->rc = rc;
  return rc;
}

/*
** This callback is used to transfer PRAGMA settings from one database
** to the other.  The value in argv[0] should be passed to a pragma
** identified by ((vacuumStruct*)pArg)->zPragma.
*/
static int vacuumCallback3(void *pArg, int argc, char **argv, char **NotUsed){
  vacuumStruct *p = (vacuumStruct*)pArg;
  char zBuf[200];
  assert( argc==1 );
  if( argv==0 ) return 0;
  assert( argv[0]!=0 );
  assert( strlen(p->zPragma)<100 );
  assert( strlen(argv[0])<30 );
  sprintf(zBuf,"PRAGMA %s=%s;", p->zPragma, argv[0]);
  p->rc = execsql(p->pzErrMsg, p->dbNew, zBuf);
  return p->rc;
}

/*
** Generate a random name of 20 character in length.
*/
static void randomName(unsigned char *zBuf){
  static const unsigned char zChars[] =
    "abcdefghijklmnopqrstuvwxyz"
    "0123456789";
  int i;
  sqlite3Randomness(20, zBuf);
  for(i=0; i<20; i++){
    zBuf[i] = zChars[ zBuf[i]%(sizeof(zChars)-1) ];
  }
}



































#endif

/*
** The non-standard VACUUM command is used to clean up the database,
** collapse free space, etc.  It is modelled after the VACUUM command
** in PostgreSQL.
**

  return;
}

/*
** This routine implements the OP_Vacuum opcode of the VDBE.
*/
int sqlite3RunVacuum(char **pzErrMsg, sqlite *db){

#if !defined(SQLITE_OMIT_VACUUM) || SQLITE_OMIT_VACUUM
  const char *zFilename;  /* full pathname of the database file */
  int nFilename;          /* number of characters  in zFilename[] */
  char *zTemp = 0;        /* a temporary file in same directory as zFilename */
  sqlite *dbNew = 0;      /* The new vacuumed database */
  int rc = SQLITE_OK;     /* Return code from service routines */
  int i;                  /* Loop counter */
  char *zErrMsg;          /* Error message */
  vacuumStruct sVac;      /* Information passed to callbacks */

  /* These are all of the pragmas that need to be transferred over
  ** to the new database */
  static const char *zPragma[] = {
     "default_synchronous",
     "default_cache_size",
     /* "default_temp_store", */
  };

  if( db->flags & SQLITE_InTrans ){
    sqlite3SetString(pzErrMsg, "cannot VACUUM from within a transaction", 
       (char*)0);
    return SQLITE_ERROR;

  }
  memset(&sVac, 0, sizeof(sVac));

  /* Get the full pathname of the database file and create two
  ** temporary filenames in the same directory as the original file.
  */
  zFilename = sqlite3BtreeGetFilename(db->aDb[0].pBt);
  if( zFilename==0 ){
    /* This only happens with the in-memory database.  VACUUM is a no-op
    ** there, so just return */
    return SQLITE_OK;
  }
  nFilename = strlen(zFilename);
  zTemp = sqliteMalloc( nFilename+100 );
  if( zTemp==0 ) return SQLITE_NOMEM;



  strcpy(zTemp, zFilename);
  for(i=0; i<10; i++){
    zTemp[nFilename] = '-';
    randomName((unsigned char*)&zTemp[nFilename+1]);
    if( !sqlite3OsFileExists(zTemp) ) break;
  }
  if( i>=10 ){
    sqlite3SetString(pzErrMsg, "unable to create a temporary database file "
       "in the same directory as the original database", (char*)0);


    goto end_of_vacuum;
  }

  
  if( SQLITE_OK!=sqlite3_open(zTemp, &dbNew, 0) ){
    sqlite3SetString(pzErrMsg, "unable to open a temporary database at ",
       zTemp, " - ", sqlite3_errmsg(dbNew), (char*)0);
    goto end_of_vacuum;
  }
  if( (rc = execsql(pzErrMsg, db, "BEGIN"))!=0 ) goto end_of_vacuum;
  if( (rc = execsql(pzErrMsg, dbNew, "PRAGMA synchronous=off; BEGIN"))!=0 ){
    goto end_of_vacuum;
  }











  




  sVac.dbOld = db;



  sVac.dbNew = dbNew;
  sVac.pzErrMsg = pzErrMsg;
  for(i=0; rc==SQLITE_OK && i<sizeof(zPragma)/sizeof(zPragma[0]); i++){
    char zBuf[200];
    assert( strlen(zPragma[i])<100 );
    sprintf(zBuf, "PRAGMA %s;", zPragma[i]);
    sVac.zPragma = zPragma[i];
    rc = sqlite3_exec(db, zBuf, vacuumCallback3, &sVac, &zErrMsg);
  }
  if( rc==SQLITE_OK ){





    rc = sqlite3_exec(db, 


      "SELECT type, name, sql FROM sqlite_master "
      "WHERE sql NOT NULL AND type!='view' "


      "UNION ALL "
      "SELECT type, name, sql FROM sqlite_master "








      "WHERE sql NOT NULL AND type=='view'",







      vacuumCallback1, &sVac, &zErrMsg);




  }




  if( rc==SQLITE_OK ){







    rc = sqlite3BtreeCopyFile(db->aDb[0].pBt, dbNew->aDb[0].pBt);
    sqlite3_exec(db, "COMMIT", 0, 0, 0);




    sqlite3ResetInternalSchema(db, 0);

  }

end_of_vacuum:
  if( rc && zErrMsg!=0 ){
    sqlite3SetString(pzErrMsg, "unable to vacuum database - ", 
       zErrMsg, (char*)0);
  }
  sqlite3_exec(db, "ROLLBACK", 0, 0, 0);
  if( dbNew ) sqlite3_close(dbNew);
  sqlite3OsDelete(zTemp);
  sqliteFree(zTemp);

  sqliteFree(sVac.s1.z);
  sqliteFree(sVac.s2.z);
  if( dbNew ) sqlite3_close(dbNew);
  if( zErrMsg ) sqlite3_freemem(zErrMsg);
  if( rc==SQLITE_ABORT ) sVac.rc = SQLITE_ERROR;
  return sVac.rc;
#endif
}

#    May you share freely, never taking more than you give.
#
#***********************************************************************
# This file implements regression tests for SQLite library.  The
# focus of this script is testing the ATTACH and DETACH commands
# and related functionality.
#
# $Id: attach.test,v 1.17 2004/05/29 02:37:20 danielk1977 Exp $
#

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

for {set i 2} {$i<=15} {incr i} {
  file delete -force test$i.db

    SELECT * FROM two.t2;
  }
} {1 {no such table: two.t2}}
do_test attach-1.8 {
  catchsql {
    ATTACH DATABASE 'test3.db' AS three;
  }
} {1 {cannot attach empty database: three}}
do_test attach-1.9 {
  catchsql {
    SELECT * FROM three.sqlite_master;
  }
} {1 {no such table: three.sqlite_master}}
do_test attach-1.10 {
  catchsql {
    DETACH DATABASE three;
  }
} {1 {no such database: three}}
do_test attach-1.11 {
  execsql {
    ATTACH 'test.db' AS db2;
    ATTACH 'test.db' AS db3;
    ATTACH 'test.db' AS db4;
    ATTACH 'test.db' AS db5;
    ATTACH 'test.db' AS db6;

# Check to make sure we get a sensible error if unable to open
# the file that we are trying to attach.
#
do_test attach-6.1 {
  catchsql {
    ATTACH DATABASE 'no-such-file' AS nosuch;
  }
} {1 {cannot attach empty database: nosuch}}
file delete -force no-such-file
if {$tcl_platform(platform)=="unix"} {
  do_test attach-6.2 {
    sqlite dbx cannot-read
    dbx eval {CREATE TABLE t1(a,b,c)}
    dbx close
    file attributes cannot-read -permission 0000

#    May you share freely, never taking more than you give.
#
#***********************************************************************
# This file implements regression tests for SQLite library.  The
# focus of this script is testing the ATTACH and DETACH commands
# and schema changes to attached databases.
#
# $Id: attach3.test,v 1.4 2004/05/29 02:37:20 danielk1977 Exp $
#


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

# Create tables t1 and t2 in the main database

    SELECT * FROM t3;
  }
} {10 20 20 40}
do_test attach3-5.3 {
  execsql {
    SELECT * FROM aux.sqlite_master WHERE name = 'tr1';
  }
} {trigger tr1 t3 0 {CREATE TRIGGER aux.tr1 AFTER INSERT ON t3 BEGIN
      INSERT INTO t3 VALUES(new.e*2, new.f*2);
    END}}

# Drop the trigger 
do_test attach3-8.1 {
  execsql {
    DROP TRIGGER aux.tr1;

# 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 VACUUM statement.
#
# $Id: vacuum.test,v 1.18 2004/05/26 10:11:07 danielk1977 Exp $

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

proc cksum {{db db}} {
  set txt [$db eval {SELECT name, type, sql FROM sqlite_master}]\n

  foreach tbl [$db eval {SELECT name FROM sqlite_master WHERE type='table'}] {

    append txt [$db eval "SELECT * FROM $tbl"]\n
  }
  foreach prag {default_synchronous default_cache_size} {
    append txt $prag-[$db eval "PRAGMA $prag"]\n
  }
  set cksum [string length $txt]-[md5 $txt]
  # puts $cksum-[file size test.db]