SQLite

**     COPY
**     VACUUM
**     BEGIN TRANSACTION
**     COMMIT
**     ROLLBACK
**     PRAGMA
**
** $Id: build.c,v 1.99 2002/06/25 13:16:03 drh 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

#endif

  if( sqliteStrICmp(zLeft, "integrity_check")==0 ){
    static VdbeOp checkDb[] = {
      { OP_SetInsert,   0, 0,        "2"},
      { OP_Open,        0, 2,        0},
      { OP_Rewind,      0, 6,        0},
      { OP_Column,      0, 3,        0},    /* 3 */
      { OP_SetInsert,   0, 0,        0},
      { OP_Next,        0, 3,        0},
      { OP_IntegrityCk, 0, 0,        0},    /* 6 */
      { OP_ColumnCount, 1, 0,        0},
      { OP_ColumnName,  0, 0,        "integrity_check"},
      { OP_Callback,    1, 0,        0},
      { OP_SetInsert,   1, 0,        "2"},
      { OP_OpenAux,     1, 2,        0},
      { OP_Rewind,      1, 16,       0},
      { OP_Column,      1, 3,        0},    /* 13 */
      { OP_SetInsert,   1, 0,        0},
      { OP_Next,        1, 13,       0},
      { OP_IntegrityCk, 1, 1,        0},    /* 16 */
      { OP_Callback,    1, 0,        0},
    };
    Vdbe *v = sqliteGetVdbe(pParse);
    if( v==0 ) return;
    sqliteVdbeAddOpList(v, ArraySize(checkDb), checkDb);
  }else

  {}
  sqliteFree(zLeft);
  sqliteFree(zRight);
}

** 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.160 2002/06/25 13:16:04 drh Exp $
*/
#include "sqliteInt.h"
#include <ctype.h>

/*
** The following global variable is incremented every time a cursor
** moves, either by the OP_MoveTo or the OP_Next opcode.  The test

    aStack[i].flags = STK_Int;
    *(u32*)pOp->p3 = pgno;
    pOp->p3 = 0;
  }
  break;
}

/* Opcode: IntegrityCk P1 P2 *
**
** Do an analysis of the currently open database.  Push onto the
** stack the text of an error message describing any problems.
** If there are no errors, push a "ok" onto the stack.
**
** P1 is the index of a set that contains the root page numbers
** for all tables and indices in the main database file.
**
** If P2 is not zero, the check is done on the auxiliary database
** file, not the main database file.
**
** This opcode is used for testing purposes only.
*/
case OP_IntegrityCk: {
  int nRoot;
  int *aRoot;
  int tos = ++p->tos;

  pSet = &p->aSet[iSet];
  nRoot = sqliteHashCount(&pSet->hash);
  aRoot = sqliteMalloc( sizeof(int)*(nRoot+1) );
  for(j=0, i=sqliteHashFirst(&pSet->hash); i; i=sqliteHashNext(i), j++){
    aRoot[j] = atoi((char*)sqliteHashKey(i));
  }
  aRoot[j] = 0;
  z = sqliteBtreeIntegrityCheck(pOp->p2 ? db->pBeTemp : pBt, aRoot, nRoot);
  if( z==0 || z[0]==0 ){
    if( z ) sqliteFree(z);
    zStack[tos] = "ok";
    aStack[tos].n = 3;
    aStack[tos].flags = STK_Str | STK_Static;

  }else{
    zStack[tos] = z;
    aStack[tos].n = strlen(z) + 1;
    aStack[tos].flags = STK_Str | STK_Dyn;
  }
  sqliteFree(aRoot);
  break;

#
#***********************************************************************
# This file implements regression tests for SQLite library.
#
# This file implements tests for the conflict resolution extension
# to SQLite.
#
# $Id: conflict.test,v 1.12 2002/06/25 13:16:04 drh Exp $

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

# Create tables for the first group of tests.
#
do_test conflict-1.0 {

    INSERT OR IGNORE INTO t1 SELECT a+3,b+3 FROM t1;
  }
} {3}
do_test conflict-8.6.1 {
  db changes
} {3}


integrity_check conflict-99.1




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 file is testing the COPY statement.
#
# $Id: copy.test,v 1.11 2002/06/25 13:16:04 drh Exp $

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

# Create a file of data from which to copy.
#
set f [open data1.txt w]

} {2}
do_test copy-5.7 {
  execsql {
    COPY OR IGNORE t1 FROM 'data5.txt' USING DELIMITERS '|';
  }
} {0}


integrity_check copy-6.0





# Cleanup 
#
file delete -force data1.txt data2.txt data3.txt data4.txt data5.txt data6.txt

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 file is testing the INSERT statement.
#
# $Id: insert.test,v 1.11 2002/06/25 13:16:04 drh Exp $

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

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

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





# Test of expressions in the VALUES clause
#
do_test insert-4.1 {
  execsql {
    CREATE TABLE t3(a,b,c);
    INSERT INTO t3 VALUES(1+2+3,4,5);

#    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 INSERT statement that takes is
# result from a SELECT.
#
# $Id: insert2.test,v 1.10 2002/06/25 13:16:04 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 {
    DELETE FROM t3;
    INSERT INTO t3(c,a,b) SELECT x, 'hi', y FROM t4;
    SELECT * FROM t3;
  }
} {hi 2 1}

integrity_check insert2-3.0





# File table t4 with lots of data
#
do_test insert2-3.1 {
  execsql {
    SELECT * from t4;
  }

    SELECT count(*) FROM t4;
    ROLLBACK;
  }
} {1}
do_test insert2-3.8 {
  db changes
} {159}
integrity_check insert2-3.9

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 database locks.
#
# $Id: lock.test,v 1.15 2002/06/25 13:16:04 drh Exp $


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

# Create an alternative connection to the database
#

    db eval {SELECT * FROM t1} qv {
      set r [catch {db2 eval {SELECT a FROM t1}} msg]
      lappend r $msg
    }
    set r
  } {0 2}
}
integrity_check lock-1.23

# If one thread has a transaction another thread cannot start
# a transaction.
#
do_test lock-2.1 {
  execsql {BEGIN TRANSACTION}
  set r [catch {execsql {BEGIN TRANSACTION} db2} msg]

  set ::callback_value {}
  db2 busy callback
  set r [catch {execsql {BEGIN TRANSACTION} db2} msg]
  execsql {ROLLBACK} db2
  lappend r $msg
  lappend r $::callback_value
} {0 {} {1 2 3}}
integrity_check lock-2.8

# Try to start two transactions in a row
#
do_test lock-3.1 {
  execsql {BEGIN TRANSACTION}
  set r [catch {execsql {BEGIN TRANSACTION}} msg]
  execsql {ROLLBACK}
  lappend r $msg
} {0 {}}
integrity_check lock-3.2

do_test lock-999.1 {
  rename db2 {}
} {}

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 some common TCL routines used for regression
# testing the SQLite library
#
# $Id: tester.tcl,v 1.23 2002/06/25 13:16:04 drh Exp $

# Make sure tclsqlite was compiled correctly.  Abort now with an
# error message if not.
#
if {[sqlite -tcl-uses-utf]} {
  if {"\u1234"=="u1234"} {
    puts stderr "***** BUILD PROBLEM *****"

# Delete a file or directory
#
proc forcedelete {filename} {
  if {[catch {file delete -force $filename}]} {
    exec rm -rf $filename
  }
}

# Do an integrity check of the entire database
#
proc integrity_check {name} {
  do_test $name {
    execsql {PRAGMA integrity_check}
  } {ok ok}
}

# 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 database locks.
#
# $Id: trans.test,v 1.12 2002/06/25 13:16:04 drh Exp $


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


# Create several tables to work with.

do_test trans-1.9 {
  sqlite altdb test.db
  execsql {SELECT b FROM one ORDER BY a} altdb
} {one two three}
do_test trans-1.10 {
  execsql {SELECT b FROM two ORDER BY a} altdb
} {I V X}
integrity_check trans-1.11

# Basic transactions
#
do_test trans-2.1 {
  set v [catch {execsql {BEGIN}} msg]
  lappend v $msg
} {0 {}}

  execsql {
    BEGIN;
    SELECT a FROM one ORDER BY a;
    SELECT a FROM two ORDER BY a;
    END;
  }
} {1 2 3 1 5 10}
integrity_check trans-2.11

# Check the locking behavior
#
do_test trans-3.1 {
  execsql {
    BEGIN;
    SELECT a FROM one ORDER BY a;

} {0 {1 4 5 10}}
do_test trans-3.14 {
  set v [catch {execsql {
    SELECT a FROM one ORDER BY a;
  } db} msg]
  lappend v $msg
} {0 {1 2 3 4}}
integrity_check trans-3.15

do_test trans-4.1 {
  set v [catch {execsql {
    COMMIT;
  } db} msg]
  lappend v $msg
} {0 {}}

} {0 {1 4 5 10}}
do_test trans-4.11 {
  set v [catch {execsql {
    SELECT a FROM one ORDER BY a;
  } altdb} msg]
  lappend v $msg
} {0 {1 2 3 4}}
integrity_check trans-4.12
do_test trans-4.98 {
  altdb close
  execsql {
    DROP TABLE one;
    DROP TABLE two;
  }
} {}
integrity_check trans-4.99

# Check out the commit/rollback behavior of the database
#
do_test trans-5.1 {
  execsql {SELECT name FROM sqlite_master WHERE type='table' ORDER BY name}
} {}
do_test trans-5.2 {

  }
} {i2x i2y t1 t2}
do_test trans-5.23 {
  execsql {
    SELECT * FROM t2;
  }
} {1 2 3}
integrity_check trans-5.23


# Try to DROP and CREATE tables and indices with the same name
# within a transaction.  Make sure ROLLBACK works.
#
do_test trans-6.1 {
  execsql2 {
    INSERT INTO t1 VALUES(1,2,3);

  }
} {4 -5 -6 1 -2 -3}
do_test trans-6.39 {
  execsql {
    SELECT * FROM t1 WHERE c<1;
  }
} {1 -2 -3 4 -5 -6}
integrity_check trans-6.40

# Test to make sure rollback restores the database back to its original
# state.
#
do_test trans-7.1 {
  execsql {BEGIN}
  for {set i 0} {$i<1000} {incr i} {

    ROLLBACK;
    SELECT md5sum(x,y,z) FROM t2;
  }
} $checksum
do_test trans-7.14 {
  execsql {SELECT md5sum(type,name,tbl_name,rootpage,sql) FROM sqlite_master}
} $checksum2
integrity_check trans-7.15

# Arrange for another process to begin modifying the database but abort
# and die in the middle of the modification.  Then have this process read
# the database.  This process should detect the journal file and roll it
# back.  Verify that this happens correctly.
#
set fd [open test.tcl w]

do_test trans-8.1 {
  catch {exec [info nameofexec] test.tcl}
  execsql {SELECT md5sum(x,y,z) FROM t2}
} $checksum
do_test trans-8.2 {
  execsql {SELECT md5sum(type,name,tbl_name,rootpage,sql) FROM sqlite_master}
} $checksum2
integrity_check trans-8.3

   
finish_test