SQLite

** The pager is used to access a database disk file.  It implements
** atomic commit and rollback through the use of a journal file that
** is separate from the database file.  The pager also implements file
** locking to prevent two processes from writing the same database
** file simultaneously, or one process from reading the database while
** another is writing.
**
** @(#) $Id: pager.c,v 1.77 2003/02/15 23:09:17 drh Exp $
*/
#include "os.h"         /* Must be first to enable large file support */
#include "sqliteInt.h"
#include "pager.h"
#include <assert.h>
#include <string.h>

  pPager->alwaysRollback = 0;
  pPager->nRec = 0;
  sqlitepager_pagecount(pPager);
  pPager->origDbSize = pPager->dbSize;
  if( journal_format==JOURNAL_FORMAT_3 ){
    rc = sqliteOsWrite(&pPager->jfd, aJournalMagic3, sizeof(aJournalMagic3));
    if( rc==SQLITE_OK ){
      rc = write32bits(&pPager->jfd, pPager->noSync ? 0xffffffff : 0);
    }
    if( rc==SQLITE_OK ){
      pPager->cksumInit = (u32)sqliteRandomInteger();
      rc = write32bits(&pPager->jfd, pPager->cksumInit);
    }
  }else if( journal_format==JOURNAL_FORMAT_2 ){
    rc = sqliteOsWrite(&pPager->jfd, aJournalMagic2, sizeof(aJournalMagic2));

#    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.4 2003/02/15 23:09:17 drh Exp $

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

# Delete the preexisting database to avoid the special setup
# that the "all.test" script does.
#

  execsql {
    PRAGMA cache_size;
    PRAGMA default_cache_size;
    PRAGMA synchronous;
    PRAGMA default_synchronous;
  }
} {123 123 0 0}
do_test pragma-1.11 {
  execsql {
    PRAGMA synchronous=NORMAL;
    PRAGMA cache_size;
    PRAGMA default_cache_size;
    PRAGMA synchronous;
    PRAGMA default_synchronous;
  }
} {123 123 1 0}
do_test pragma-1.12 {
  execsql {
    PRAGMA synchronous=FULL;
    PRAGMA cache_size;
    PRAGMA default_cache_size;
    PRAGMA synchronous;
    PRAGMA default_synchronous;
  }
} {123 123 2 0}
do_test pragma-1.13 {
  db close
  set ::DB [sqlite db test.db]
  execsql {
    PRAGMA cache_size;
    PRAGMA default_cache_size;
    PRAGMA synchronous;
    PRAGMA default_synchronous;
  }
} {123 123 0 0}
do_test pragma-1.14 {
  execsql {
    PRAGMA default_synchronous=FULL;
    PRAGMA cache_size;
    PRAGMA default_cache_size;
    PRAGMA synchronous;
    PRAGMA default_synchronous;
  }
} {123 123 2 2}
do_test pragma-1.15 {
  db close
  set ::DB [sqlite db test.db]
  execsql {
    PRAGMA cache_size;
    PRAGMA default_cache_size;
    PRAGMA synchronous;
    PRAGMA default_synchronous;
  }
} {123 123 2 2}

do_test pragma-2.1 {
  execsql {
    PRAGMA show_datatypes=on;
    PRAGMA empty_result_callbacks=off;
  }
  sqlite_datatypes $::DB {SELECT * FROM sqlite_master}
} {}
do_test pragma-2.2 {
  execsql {
    PRAGMA empty_result_callbacks=on;
  }
  sqlite_datatypes $::DB {SELECT * FROM sqlite_master}
} {text text text integer text}
do_test pragma-2.3 {
  execsql {
    CREATE TABLE t1(
       a INTEGER,
       b TEXT,
       c WHATEVER,
       d CLOB,
       e BLOB,
       f VARCHAR(123),
       g nVaRcHaR(432)
    );
  }
  sqlite_datatypes $::DB {SELECT * FROM t1}
} {INTEGER TEXT WHATEVER CLOB BLOB VARCHAR(123) nVaRcHaR(432)}
do_test pragma-2.4 {
  sqlite_datatypes $::DB {
     SELECT 1, 'hello', NULL
  }
} {NUMERIC TEXT TEXT}
do_test pragma-2.5 {
  sqlite_datatypes $::DB {
     SELECT 1+2 AS X, 'hello' || 5 AS Y, NULL AS Z
  }
} {NUMERIC TEXT TEXT}
do_test pragma-2.6 {
  execsql {
    CREATE VIEW v1 AS SELECT a+b, b||c, * FROM t1;
  }
  sqlite_datatypes $::DB {SELECT * FROM v1}
} {NUMERIC TEXT INTEGER TEXT WHATEVER CLOB BLOB VARCHAR(123) nVaRcHaR(432)}
do_test pragma-2.7 {
  sqlite_datatypes $::DB {
    SELECT d,e FROM t1 UNION SELECT a,c FROM t1
  }
} {INTEGER WHATEVER}
do_test pragma-2.8 {
  sqlite_datatypes $::DB {
    SELECT d,e FROM t1 EXCEPT SELECT c,e FROM t1
  }
} {WHATEVER BLOB}
do_test pragma-2.9 {
  sqlite_datatypes $::DB {
    SELECT d,e FROM t1 INTERSECT SELECT c,e FROM t1
  }
} {WHATEVER BLOB}
do_test pragma-2.10 {
  sqlite_datatypes $::DB {
    SELECT d,e FROM t1 INTERSECT SELECT c,e FROM v1
  }
} {WHATEVER BLOB}
    


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: trans.test,v 1.18 2003/02/15 23:09:17 drh Exp $


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


# Create several tables to work with.

# 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.
# Verify that after the rollback, the MD5 checksum is unchanged.
#
do_test trans-9.1 {
  execsql {
    PRAGMA default_cache_size=10;
  }
  db close
  sqlite db test.db
  execsql {
    BEGIN;
    CREATE TABLE t3(x TEXT);
    INSERT INTO t3 VALUES(randstr(10,400));
    INSERT INTO t3 VALUES(randstr(10,400));
    INSERT INTO t3 SELECT randstr(10,400) FROM t3;
    INSERT INTO t3 SELECT randstr(10,400) FROM t3;
    INSERT INTO t3 SELECT randstr(10,400) FROM t3;

# Do rollbacks.  Make sure the signature does not change.
#
for {set i 2} {$i<=$limit} {incr i} {
  set ::sig [signature]
  set cnt [lindex $::sig 0]
  set ::journal_format [expr {($i%3)+1}]
  if {$i%2==0} {
    execsql {PRAGMA synchronous=FULL}
  } else {
    execsql {PRAGMA synchronous=NORMAL}
  }
  do_test trans-9.$i.1-$cnt {
     execsql {
       BEGIN;
       DELETE FROM t3 WHERE random()%10!=0;
       INSERT INTO t3 SELECT randstr(10,10)||x FROM t3;
       INSERT INTO t3 SELECT randstr(10,10)||x FROM t3;
       ROLLBACK;

  }
} {1 {uniqueness constraint failed}}
do_test trigger2-6.1h {
  execsql {
    SELECT * from tbl;
  }
} {}
execsql {DELETE FROM tbl}


# Handling of ON CONFLICT by UPDATE statements inside triggers
execsql {
  INSERT INTO tbl values (4, 2, 3);
  INSERT INTO tbl values (6, 3, 4);
  CREATE TRIGGER au_tbl AFTER UPDATE ON tbl BEGIN

  }
} {1 {uniqueness constraint failed}}
do_test trigger2-6.2e {
  execsql {
    SELECT * from tbl;
  }
} {4 2 10 6 3 4}
do_test trigger2-6.2f.1 {
  execsql {
    UPDATE OR REPLACE tbl SET a = 1 WHERE a = 4;
    SELECT * from tbl;
  }
} {1 3 10}
do_test trigger2-6.2f.2 {
  execsql {
    INSERT INTO tbl VALUES (2, 3, 4);
    SELECT * FROM tbl;
  }
} {1 3 10 2 3 4}
do_test trigger2-6.2g {
  catchsql {
    UPDATE OR ROLLBACK tbl SET a = 4 WHERE a = 1;
  }
} {1 {uniqueness constraint failed}}
do_test trigger2-6.2h {
  execsql {

# 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 UPDATE statement.
#
# $Id: update.test,v 1.12 2003/02/15 23:09:17 drh Exp $

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

# Try to update an non-existent table
#
do_test update-1.1 {

} {777 128}
do_test update-5.6.5 {
  execsql {SELECT * FROM test1 WHERE f1==8 ORDER BY f1,f2}
} {8 88 8 256 8 888}

# Repeat the previous sequence of tests with a different index.
#
execsql {PRAGMA synchronous=FULL}
do_test update-6.0 {
  execsql {DROP INDEX idx1}
  execsql {CREATE INDEX idx1 ON test1(f2)}
  execsql {SELECT * FROM test1 ORDER BY f1,f2}
} {6 64 7 128 8 88 8 256 8 888 9 512 10 1024 77 128 777 128}
do_test update-6.1 {
  execsql {UPDATE test1 SET f2=f2+1 WHERE f1==8}

#
# Run this TCL script to generate HTML for the index.html file.
#
set rcsid {$Id: index.tcl,v 1.73 2003/02/15 23:09:17 drh Exp $}

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

<li>A complete database (with multiple tables and indices) is
    stored in a single disk file.</li>
<li>Atomic commit and rollback protect data integrity.</li>
<li>Database files can be freely shared between machines with
    different byte orders.</li>
<li>Supports databases up to 2 terabytes (2^41 bytes) in size.</li>
<li>Small memory footprint: less than 25K lines of C code.</li>
<li><a href="speed.html">Two times faster</a> than PostgreSQL and

    MySQL for many common operations.</li>
<li>Very simple 
<a href="c_interface.html">C/C++ interface</a> requires the use of only
three functions and one opaque structure.</li>
<li><a href="tclsqlite.html">TCL bindings</a> included.
    Bindings for many other languages available separately.</li>
<li>Simple, well-commented source code.</li>
<li>Automated test suite provides near 100% code coverage.</li>

#
# Run this TCL script to generate HTML for the quickstart.html file.
#
set rcsid {$Id: quickstart.tcl,v 1.4 2003/02/15 23:09:17 drh Exp $}

puts {<html>
<head><title>SQLite In 5 Minutes Or Less</title></head>
<body bgcolor=white>
<h1 align=center>SQLite In 5 Minutes Or Less</h1>}

puts {

<li><p>Below is a simple C program that demonstrates how to use
the C/C++ interface to SQLite.  The name of a database is given by
the first argument and the second argument is one or more SQL statements
to execute against the database.  The function calls to pay attention
to here are the call to <b>sqlite_open()</b> on line 22 which opens
the database, <b>sqlite_exec()</b> on line 27 that executes SQL
commands against the database, and <b>sqlite_close()</b> on line 31
that closes the database connection.</p>

<blockquote><pre>
#include &lt;stdio.h&gt;
#include &lt;sqlite.h&gt;

static int callback(void *NotUsed, int argc, char **argv, char **azColName){