SQLite

**     DROP INDEX
**     creating ID lists
**     BEGIN TRANSACTION
**     COMMIT
**     ROLLBACK
**     PRAGMA
**
** $Id: build.c,v 1.259 2004/11/04 14:30:05 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

    Db *pDb = &db->aDb[i];
    temp1 = pDb->tblHash;
    temp2 = pDb->trigHash;
    sqlite3HashInit(&pDb->trigHash, SQLITE_HASH_STRING, 0);
    sqlite3HashClear(&pDb->aFKey);
    sqlite3HashClear(&pDb->idxHash);
    for(pElem=sqliteHashFirst(&temp2); pElem; pElem=sqliteHashNext(pElem)){
      Trigger *pTrigger = sqliteHashData(pElem);
      sqlite3DeleteTrigger(pTrigger);
    }
    sqlite3HashClear(&temp2);
    sqlite3HashInit(&pDb->tblHash, SQLITE_HASH_STRING, 0);
    for(pElem=sqliteHashFirst(&temp1); pElem; pElem=sqliteHashNext(pElem)){
      Table *pTab = sqliteHashData(pElem);
      sqlite3DeleteTable(db, pTab);
    }

** Also write code to modify the sqlite_master table and internal schema
** if a root-page of another table is moved by the btree-layer whilst
** erasing iTable (this can happen with an auto-vacuum database).
*/ 
static void destroyRootPage(Vdbe *v, int iTable, int iDb){
#ifndef SQLITE_OMIT_AUTOVACUUM
  int base;
#endif
  sqlite3VdbeAddOp(v, OP_Destroy, iTable, iDb);
#ifndef SQLITE_OMIT_AUTOVACUUM
  /* If SQLITE_OMIT_AUTOVACUUM is not defined, then OP_Destroy pushes
  ** an integer onto the stack. If this integer is non-zero, then it is
  ** the root page number of a table moved to location iTable. The 
  ** following writes VDBE code to modify the sqlite_master table to
  ** reflect this. It is assumed that cursor number 0 is a write-cursor
  ** opened on the sqlite_master table.
  */

    { OP_Column,     0, 1,       0},
    { OP_Column,     0, 2,       0},
    { OP_Integer,    4, 0,       0}, /* 13 */
    { OP_Column,     0, 4,       0},
    { OP_MakeRecord, 5, 0,       0},
    { OP_PutIntKey,  0, 0,       0}  /* 16 */
  };




  base = sqlite3VdbeAddOpList(v, ArraySize(updateMaster), updateMaster);
  sqlite3VdbeChangeP1(v, base+13, iTable);
#endif
}

/*
** Write VDBE code to erase table pTab and all associated indices on disk.

void sqlite3CreateForeignKey(
  Parse *pParse,       /* Parsing context */
  ExprList *pFromCol,  /* Columns in this table that point to other table */
  Token *pTo,          /* Name of the other table */
  ExprList *pToCol,    /* Columns in the other table */
  int flags            /* Conflict resolution algorithms. */
){
  FKey *pFKey = 0;
#ifndef SQLITE_OMIT_FOREIGN_KEY

  Table *p = pParse->pNewTable;
  int nByte;
  int i;
  int nCol;
  char *z;

  assert( pTo!=0 );

**    May you share freely, never taking more than you give.
**
*************************************************************************
** Code for testing the btree.c module in SQLite.  This code
** is not included in the SQLite library.  It is used for automated
** testing of the SQLite library.
**
** $Id: test3.c,v 1.54 2004/11/04 14:30:06 danielk1977 Exp $
*/
#include "sqliteInt.h"
#include "pager.h"
#include "btree.h"
#include "tcl.h"
#include <stdlib.h>
#include <string.h>

static int btree_integrity_check(
  void *NotUsed,
  Tcl_Interp *interp,    /* The TCL interpreter that invoked this command */
  int argc,              /* Number of arguments */
  const char **argv      /* Text of each argument */
){
  Btree *pBt;
  char *zResult;
  int nRoot;
  int *aRoot;
  int i;


  if( argc<3 ){
    Tcl_AppendResult(interp, "wrong # args: should be \"", argv[0],
       " ID ROOT ...\"", 0);
    return TCL_ERROR;
  }
  pBt = sqlite3TextToPtr(argv[1]);
  nRoot = argc-2;
  aRoot = malloc( sizeof(int)*(argc-2) );
  for(i=0; i<argc-2; i++){
    if( Tcl_GetInt(interp, argv[i+2], &aRoot[i]) ) return TCL_ERROR;
  }
#ifndef SQLITE_OMIT_INTEGRITY_CHECK
  zResult = sqlite3BtreeIntegrityCheck(pBt, aRoot, nRoot);
#else
  zResult = "ok";
#endif
  if( zResult ){
    Tcl_AppendResult(interp, zResult, 0);
    sqliteFree(zResult); 
  }
  return TCL_OK;
}

#    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.28 2004/11/04 04:42:28 drh Exp $
#

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

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

do_test attach-1.20.1 {
  execsql {
    DETACH db5;
  }
  db_list db
} {0 main 2 db2 3 db3 4 db4 5 db6 6 db7 7 db8 8 db9 9 db10 10 db11}
integrity_check attach-1.20.2

do_test attach-1.21 {
  catchsql {
    ATTACH 'test.db' as db12;
  }
} {0 {}}
do_test attach-1.22 {
  catchsql {

# 2004 Jun 4	
#
# 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 script is btree database backend.
#
# $Id: btree7.test,v 1.1 2004/06/05 00:01:46 drh Exp $


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

# Stress the balance routine by trying to create situations where
# 3 neighboring nodes split into 5.

    INSERT INTO t1 VALUES(2, '$bigdata');
    INSERT INTO t1 VALUES(3, '$data450');
    INSERT INTO t1 VALUES(5, '$data450');
    INSERT INTO t1 VALUES(8, '$bigdata');
    INSERT INTO t1 VALUES(9, '$bigdata');
  "
} {}
#puts [execsql {select * from sqlite_master}]
#set bt [btree_open test.db 2000 0]
#btree_tree_dump $bt 2
do_test btree7-1.2 {
  execsql {PRAGMA integrity_check}
} {ok}
do_test btree7-1.3 {
  execsql "
    INSERT INTO t1 VALUES(4, '$bigdata');
  "
} {}
#btree_tree_dump $bt 2
do_test btree7-1.4 {
  execsql {PRAGMA integrity_check}
} {ok}

finish_test

# 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 script is page cache subsystem.
#
# $Id: collate3.test,v 1.3 2004/06/19 00:16:31 drh Exp $

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

#
# Tests are organised as follows:
#

  }
} {0 {xxx xxx}}
do_test collate3-3.6 {
  catchsql {
    DELETE FROM collate3t1;
  }
} {0 {}}

do_test collate3-3.8 {
  catchsql {
    PRAGMA integrity_check
  }
} {1 {no such collation sequence: string_compare}}

do_test collate3-3.9 {
  catchsql {
    SELECT * FROM collate3t1;
  }
} {0 {}}
do_test collate3-3.10 {
  catchsql {

# index entry was deleted first, before the table entry.  And the index
# delete worked.  Thus an entry was deleted from the index but not from
# the table.
#
# The solution to the problem was to detect that the table is locked
# before the index entry is deleted.
#
# $Id: delete2.test,v 1.1 2004/08/08 19:43:30 drh Exp $
#

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

# Create a table that has an index.
#

  }
} {hello id.1 goodbye id.2 again id.3}
do_test delete2-1.2 {
  execsql {
    SELECT * FROM q WHERE id='id.1';
  }
} {hello id.1}
do_test delete2-1.3 {
  execsql {
    PRAGMA integrity_check
  }
} ok

# Start a query on the table.  The query should not use the index.
# Do not complete the query, thus leaving the table locked.
#
do_test delete2-1.4 {
  set STMT [sqlite3_prepare $DB {SELECT * FROM q} -1 TAIL]
  sqlite3_step $STMT
} SQLITE_ROW
do_test delete2-1.5 {
  execsql {PRAGMA integrity_check}
} {ok}

# Try to delete a row from the table.  The delete should fail.
#
do_test delete2-1.6 {
  catchsql {
    DELETE FROM q WHERE rowid=1
  }
} {1 {database table is locked}}
do_test delete2-1.7 {
  execsql {PRAGMA integrity_check}
} {ok}
do_test delete2-1.8 {
  execsql {
    SELECT * FROM q;
  }
} {hello id.1 goodbye id.2 again id.3}

# Finalize the query, thus clearing the lock on the table.  Then
# retry the delete.  The delete should work this time.
#
do_test delete2-1.9 {
  sqlite3_finalize $STMT
  catchsql {
    DELETE FROM q WHERE rowid=1
  }
} {0 {}}
do_test delete2-1.10 {
  execsql {PRAGMA integrity_check}
} {ok}
do_test delete2-1.11 {
  execsql {
    SELECT * FROM q;
  }
} {goodbye id.2 again id.3}

finish_test

# 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 script is in-memory database backend.
#
# $Id: memdb.test,v 1.9 2004/06/19 00:16:31 drh Exp $


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

# In the following sequence of tests, compute the MD5 sum of the content
# of a table, make lots of modifications to that table, then do a rollback.

         INSERT INTO t3 SELECT randstr(10,400) FROM t3 WHERE random()%10==0;
       }
    } {}
  }
  set ::pager_old_format 0
}

do_test memdb-2.1 {
  execsql {
    PRAGMA integrity_check
  }
} {ok}

do_test memdb-3.1 {
  execsql {
    CREATE TABLE t4(a,b,c,d);
    BEGIN;
    INSERT INTO t4 VALUES(1,2,3,4);
    SELECT * FROM t4;

#
#***********************************************************************
# This file implements regression tests for SQLite library.
#
# This file implements tests for miscellanous features that were
# left out of other test files.
#
# $Id: misc3.test,v 1.14 2004/11/04 04:42:28 drh Exp $

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

ifcapable {integrityck} {
  # Ticket #529.  Make sure an ABORT does not damage the in-memory cache
  # that will be used by subsequent statements in the same transaction.

do_test misc3-2.9 {
  execsql {SELECT 2.0e-27 * '+0.000005e+132'}
} 1e+100

# Ticket #522.  Make sure integer overflow is handled properly in
# indices.
#
do_test misc3-3.1 {
  execsql {PRAGMA integrity_check}
} ok
do_test misc3-3.2 {
  execsql {
    CREATE TABLE t2(a INT UNIQUE);
    PRAGMA integrity_check;
  }
} ok

do_test misc3-3.3 {
  execsql {
    INSERT INTO t2 VALUES(2147483648);
    PRAGMA integrity_check;
  }
} ok

do_test misc3-3.4 {
  execsql {
    INSERT INTO t2 VALUES(-2147483649);
    PRAGMA integrity_check;
  }
} ok

do_test misc3-3.5 {
  execsql {
    INSERT INTO t2 VALUES(+2147483649);
    PRAGMA integrity_check;
  }
} ok

do_test misc3-3.6 {
  execsql {
    INSERT INTO t2 VALUES(+2147483647);
    INSERT INTO t2 VALUES(-2147483648);
    INSERT INTO t2 VALUES(-2147483647);
    INSERT INTO t2 VALUES(2147483646);
    SELECT * FROM t2 ORDER BY a;

# 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 script is page cache subsystem.
#
# $Id: pager3.test,v 1.1 2004/08/18 19:09:44 drh Exp $


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

# This test makes sure the database file is truncated back to the correct
# length on a rollback.

    insert into t1 select 4-a, b from t2;  ----- NOTE (2)
  }
  execsql {
    rollback;  ------- NOTE (3)
  }
  db close
  sqlite3 db test.db


  execsql {
    pragma integrity_check;

  }

} ok


finish_test

#    May you share freely, never taking more than you give.
#
#***********************************************************************
# This file implements regression tests for SQLite library.
#
# This file implements tests for the PRAGMA command.
#
# $Id: pragma.test,v 1.20 2004/11/03 16:27:02 drh Exp $

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

# Test organization:
#
# pragma-1.*: Test cache_size, default_cache_size and synchronous on main db.

    CREATE INDEX i2 ON t2(a);
    INSERT INTO t2 VALUES(11,2,3);
    INSERT INTO t2 VALUES(22,3,4);
    COMMIT;
    SELECT rowid, * from t2;
  }
} {1 11 2 3 2 22 3 4}
if {![sqlite3 -has-codec]} {
  do_test pragma-3.2 {
    set rootpage [execsql {SELECT rootpage FROM sqlite_master WHERE name='i2'}]
    set db [btree_open test.db 100 0]
    btree_begin_transaction $db
    set c [btree_cursor $db $rootpage 1]
    btree_first $c
    btree_delete $c

} {2}
catchsql {COMMIT;}

# Test schema-query pragmas
#
do_test pragma-6.1 {
  set res {}

  foreach {idx name file} [execsql {pragma database_list}] {
    lappend res $idx $name
  }
  set res
} {0 main 1 temp 2 aux}
do_test pragma-6.2 {
  execsql {

      DROP TABLE other_tbl;

      SELECT * FROM rlog;
    }
  } [list 1 0 0 0.0 0.0 5 6 \
          2 0 0 5.0 6.0 5 6 ]

  do_test trigger2-1.$ii.4 {
    execsql {
      PRAGMA integrity_check;
    }
  } {ok}
}
catchsql {
  DROP TABLE rlog;
  DROP TABLE clog;
  DROP TABLE tbl;
  DROP TABLE other_tbl;
}

    execsql "DELETE FROM tbl; DELETE FROM log; $prep";
    execsql "CREATE TRIGGER the_trigger AFTER [string range $statement 0 6]\
             ON tbl BEGIN $tr_program_fixed END;"

    do_test trigger2-2.$ii-after "execsql {$statement $query}" $after_data
    execsql "DROP TRIGGER the_trigger;"

    do_test trigger2-2.$ii-integrity {
      execsql {
        PRAGMA integrity_check;
      }
    } {ok}

  }
}
catchsql {
  DROP TABLE tbl;
  DROP TABLE log;
}

    UPDATE log SET a = 0;
  }
} {1 0 1}
execsql {
  DROP TABLE tbl;
  DROP TABLE log;
}
do_test trigger2-3.3 {
  execsql {
    PRAGMA integrity_check;
  }
} {ok}

# Simple cascaded trigger
execsql {
  CREATE TABLE tblA(a, b);
  CREATE TABLE tblB(a, b);
  CREATE TABLE tblC(a, b);

    END;
    DELETE FROM v1log;
    UPDATE v1 SET x=x+100, y=y+200, z=z+300;
    SELECT * FROM v1log;
  }
} {3 103 5 205 4 304 9 109 11 211 10 310}

do_test trigger2-9.9 {
  execsql {PRAGMA integrity_check}
} {ok}

finish_test