SQLite

/*
** 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.
**
*************************************************************************
** $Id: btree.c,v 1.51 2002/02/03 19:06:03 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.

/******************************************************************************
** The complete implementation of the BTree subsystem is above this line.
** All the code the follows is for testing and troubleshooting the BTree
** subsystem.  None of the code that follows is used during normal operation.
** All of the following code is omitted if the library is compiled with
** the -DNDEBUG=1 compiler option.
******************************************************************************/
#ifndef NDEBUG

/*
** Print a disassembly of the given page on standard output.  This routine
** is used for debugging and testing only.
*/
int sqliteBtreePageDump(Btree *pBt, int pgno, int recursive){
  int rc;

**    May you find forgiveness for yourself and forgive others.
**    May you share freely, never taking more than you give.
**
*************************************************************************
** This file contains C code routines that are called by the parser
** to handle INSERT statements in SQLite.
**
** $Id: insert.c,v 1.42 2002/02/03 19:06:03 drh Exp $
*/
#include "sqliteInt.h"

/*
** This routine is call to handle SQL of the following forms:
**
**    insert into TABLE (IDLIST) values(EXPRLIST)

        if( pColumn->a[j].idx==i ) break;
      }
    }
    if( pColumn && j>=pColumn->nId ){
      sqliteVdbeAddOp(v, OP_String, 0, 0);
      sqliteVdbeChangeP3(v, -1, pTab->aCol[i].zDflt, P3_STATIC);
    }else if( srcTab>=0 ){
      sqliteVdbeAddOp(v, OP_Column, srcTab, j); 
    }else{
      sqliteExprCode(pParse, pList->a[j].pExpr);
    }
  }

  /* Generate code to check constraints and generate index keys and
  ** do the insertion.

**    May you find forgiveness for yourself and forgive others.
**    May you share freely, never taking more than you give.
**
*************************************************************************
** This file contains C code routines that are called by the parser
** to handle SELECT statements in SQLite.
**
** $Id: select.c,v 1.57 2002/02/03 19:06:03 drh Exp $
*/
#include "sqliteInt.h"

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

    if( pTabList->a[i].pTab ){
      /* This routine has run before!  No need to continue */
      return 0;
    }
    if( pTabList->a[i].zName==0 ){
      /* No table name is given.  Instead, there is a (SELECT ...) statement
      ** the results of which should be used in place of the table.  The
      ** way this is implemented is that the (SELECT ...) writes its results
      ** into a temporary table which is then scanned like any other table.
      */
      sqliteSetString(&pParse->zErrMsg, 
          "(SELECT...) in a FROM clause is not yet implemented.", 0);
      pParse->nErr++;
      return 1;
    }

** 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.117 2002/02/03 19:06:03 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

**
** P1 is the index of a set that contains the root page numbers
** for all tables and indices in this database.
**
** This opcode is used for testing purposes only.
*/
case OP_SanityCheck: {
#ifndef NDEBUG  /* This opcode used for testing only */
  int nRoot;
  int *aRoot;
  int tos = ++p->tos;
  int iSet = pOp->p1;
  Set *pSet;
  int j;
  HashElem *i;
  char *z;

  if( iSet<0 || iSet>=p->nSet ) goto bad_instruction;


  if( NeedStack(p, p->tos) ) goto no_mem;
  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 = sqliteBtreeSanityCheck(pBt, aRoot, nRoot);
  if( z==0 || z[0]==0 ){
    zStack[tos] = "ok";
    aStack[tos].n = 3;
    aStack[tos].flags = STK_Str | STK_Static;
    if( z ) sqliteFree(z);
  }else{
    zStack[tos] = z;
    aStack[tos].n = strlen(z) + 1;
    aStack[tos].flags = STK_Str | STK_Dyn;
  }
  sqliteFree(aRoot);
#endif /* !define(NDEBUG) */
  break;
}

/* Opcode:  Limit P1 P2 *
**
** Set a limit and offset on callbacks.  P1 is the limit and P2 is
** the offset.  If the offset counter is positive, no callbacks are

#
#***********************************************************************
# This file implements regression tests for SQLite library.
#
# This file implements tests for the conflict resolution extension
# to SQLite.
#
# $Id: conflict.test,v 1.7 2002/02/03 19:06:03 drh Exp $

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

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

    execsql {COMMIT}
    if {!$r0} {set r1 [execsql {SELECT a FROM t1 ORDER BY b}]}
    set r2 [execsql {SELECT x FROM t3}]
    list $r0 $r1 $r2
  } [list $t0 $t1 $t2]
}

do_test insert-99.1 {
  set x [execsql {PRAGMA sanity_check}]
  if {$x==""} {set x ok}
  set x
} {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 file is testing the COPY statement.
#
# $Id: copy.test,v 1.9 2002/02/03 19:06:03 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]

  puts $fd "33|22|44"
  close $fd
  catchsql {
    COPY OR REPLACE t1 FROM 'data6.txt' USING DELIMITERS '|';
    SELECT * FROM t1;
  }
} {0 {22 33 11 33 22 44}}

do_test copy-5.5 {
  execsql {
    DELETE FROM t1;
    PRAGMA count_changes=on;
    COPY t1 FROM 'data5.txt' USING DELIMITERS '|';
  }
} {2}
do_test copy-5.6 {
  execsql {
    COPY OR REPLACE t1 FROM 'data5.txt' USING DELIMITERS '|';
  }
} {2}
do_test copy-5.7 {
  execsql {
    COPY OR IGNORE t1 FROM 'data5.txt' USING DELIMITERS '|';
  }
} {0}

do_test copy-6.0 {
  set x [execsql {PRAGMA sanity_check}]
  if {$x==""} {set x ok}
  set x
} {ok}
 

# 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.7 2002/02/03 19:06:03 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}
do_test insert-3.5 {
  set x [execsql {PRAGMA sanity_check}]
  if {$x==""} {set x ok}
  set x
} {ok}


finish_test

#    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.6 2002/02/03 19:06:03 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 {

    CREATE INDEX i2 ON t1(cnt);
    INSERT INTO t1 SELECT log, count() FROM d1 GROUP BY log;
    SELECT * FROM t1 ORDER BY log;
  }]
  lappend r [execsql {SELECT cnt FROM t1 WHERE log=3}]
  lappend r [execsql {SELECT log FROM t1 WHERE cnt=4 ORDER BY log}]
} {0 1 1 1 2 2 3 4 4 8 5 4 4 {3 5}}

do_test insert2-2.0 {
  execsql {
    CREATE TABLE t3(a,b,c);
    CREATE TABLE t4(x,y);
    INSERT INTO t4 VALUES(1,2);
    SELECT * FROM t4;
  }
} {1 2}
do_test insert2-2.1 {
  execsql {
    INSERT INTO t3(a,c) SELECT * FROM t4;
    SELECT * FROM t3;
  }
} {1 {} 2}
do_test insert2-2.2 {
  execsql {
    DELETE FROM t3;
    INSERT INTO t3(c,b) SELECT * FROM t4;
    SELECT * FROM t3;
  }
} {{} 2 1}
do_test insert2-2.3 {
  execsql {
    DELETE FROM t3;
    INSERT INTO t3(c,a,b) SELECT x, 'hi', y FROM t4;
    SELECT * FROM t3;
  }
} {hi 2 1}

do_test insert2-4.0 {
  set x [execsql {PRAGMA sanity_check}]
  if {$x==""} {set x ok}
  set x
} {ok}

finish_test

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

chng {2002 Jan 30 (2.3.0)} {
<li>Fix a serious bug in the INSERT command which was causing data to go
    into the wrong columns if the data source was a SELECT and the INSERT
    clauses specified its columns in some order other than the default.</li>
<li>Added the ability to resolve constraint conflicts is ways other than
    an abort and rollback.  See the documentation on the "ON CONFLICT"
    clause for details.</li>
<li>Temporary files are now automatically deleted by the operating system
    when closed.  There are no more dangling temporary files on a program
    crash.  (If the OS crashes, fsck will delete the file after reboot 
    under Unix.  I do not know what happens under Windows.)</li>
<li>NOT NULL constraints are honored.</li>
<li>The COPY command puts NULLs in columns whose data is '\N'.</li>
<li>In the COPY command, backslash can now be used to escape a newline.</li>
<li>Added the SANITY_CHECK pragma.</li>
}

chng {2002 Jan 28 (2.2.5)} {
<li>Important bug fix: the IN operator was not working if either the
    left-hand or right-hand side was derived from an INTEGER PRIMARY KEY.</li>
<li>Do not escape the backslash '\' character in the output of the
    <b>sqlite</b> command-line access program.</li>

#
# Run this TCL script to generate HTML for the index.html file.
#
set rcsid {$Id: index.tcl,v 1.53 2002/02/03 19:06:04 drh Exp $}

puts {<html>
<head><title>SQLite: An SQL Database Engine In A C Library</title></head>
<body bgcolor=white>
<h1 align=center>SQLite: An SQL Database Engine In A C Library</h1>
<p align=center>}
puts "This page was last modified on [lrange $rcsid 3 4] UTC<br>"

}

puts {<h2>Current Status</h2>

<p>A <a href="changes.html">change history</a> is available online.
The latest source code is
<a href="download.html">available for download</a>.
For a list of recently discovered bugs, refer to the change history.

</p>

<p>
Whenever either of the first two digits in the version number
for SQLite change, it means that the underlying file format
has changed.  See <a href="formatchng.html">formatchng.html</a>
for additional information.

#
# Run this Tcl script to generate the sqlite.html file.
#
set rcsid {$Id: lang.tcl,v 1.22 2002/02/03 19:06:04 drh Exp $}

puts {<html>
<head>
  <title>Query Language Understood By SQLite</title>
</head>
<body bgcolor=white>
<h1 align=center>

<dt><b>FAIL</b></dt>
<dd><p>When a constraint violation occurs, the command aborts with a
return code SQLITE_CONSTRAINT.  But any changes to the database that
the command made prior to encountering the constraint violation
are preserved and are not backed out.  For example, if an UPDATE
statement encountered a constraint violation on the 100th row that
it attempts to update, then the first 99 row changes are preserved
but changes to rows 100 and beyond never occur.</p></dd>

<dt><b>IGNORE</b></dt>
<dd><p>When a constraint violation occurs, the one row that contains
the constraint violation is not inserted or changed.  But the command
continues executing normally.  Other rows before and after the row that
contained the constraint violation continue to be inserted or updated
normally.  No error is returned.</p></dd>

<dt><b>REPLACE</b></dt>
<dd><p>When a UNIQUE constraint violation occurs, the pre-existing row
that is causing the constraint violation is removed prior to inserting
or updating the current row.  Thus the insert or update always occurs.
The command continues executing normally.  No error is returned.</p>
<p>If a NOT NULL constraint violation occurs, the NULL value is replaced
by the default value for that column.  If the column has no default
value, then the ABORT algorithm is used.</p>
</dd>
</dl>

<p>
The conflict resolution algorithm can be specified in three places,
in order from lowest to highest precedence:
</p>

<li><p><b>PRAGMA parser_trace = ON;<br>PRAGMA parser_trace = OFF;</b></p>
    <p>Turn tracing of the SQL parser inside of the
    SQLite library on and off.  This is used for debugging.
    This only works if the library is compiled without the NDEBUG macro.
    </p></li>

<li><p><b>PRAGMA sanity_check;</b></p>
    <p>The command does an integrity check of the entire database.  It
    looks for out-of-order records, missing pages, and malformed records.
    If any problems are found, then a single string is returned which is
    a description of all problems.  If everything is in order, "ok" is
    returned.  This command is used for testing and debugging only and
    is not available if the library is compiled 
    with the -DNDEBUG=1 compiler option.</p>

<li><p><b>PRAGMA table_info(</b><i>table-name</i><b>);</b></p>
    <p>For each column in the named table, invoke the callback function
    once with information about that column, including the column name,
    data type, whether or not the column can be NULL, and the default
    value for the column.</p>

<li><p><b>PRAGMA vdbe_trace = ON;<br>PRAGMA vdbe_trace = OFF;</b></p>