SQLite

**     CREATE INDEX
**     DROP INDEX
**     creating ID lists
**     BEGIN TRANSACTION
**     COMMIT
**     ROLLBACK
**
** $Id: build.c,v 1.312 2005/02/19 08:18:06 danielk1977 Exp $
*/
#include "sqliteInt.h"
#include <ctype.h>

/*
** This routine is called when a new SQL statement is beginning to
** be parsed.  Initialize the pParse structure as needed.

  ** and allocate the record number for the table entry now.  Before any
  ** PRIMARY KEY or UNIQUE keywords are parsed.  Those keywords will cause
  ** indices to be created and the table record must come before the 
  ** indices.  Hence, the record number for the table must be allocated
  ** now.
  */
  if( !db->init.busy && (v = sqlite3GetVdbe(pParse))!=0 ){
    int lbl;
    sqlite3BeginWriteOperation(pParse, 0, iDb);


    /* If the file format and encoding in the database have not been set, 
    ** set them now.
    */
    sqlite3VdbeAddOp(v, OP_ReadCookie, iDb, 1);   /* file_format */
    lbl = sqlite3VdbeMakeLabel(v);
    sqlite3VdbeAddOp(v, OP_If, 0, lbl);
    sqlite3VdbeAddOp(v, OP_Integer, db->file_format, 0);
    sqlite3VdbeAddOp(v, OP_SetCookie, iDb, 1);
    sqlite3VdbeAddOp(v, OP_Integer, db->enc, 0);
    sqlite3VdbeAddOp(v, OP_SetCookie, iDb, 4);
    sqlite3VdbeResolveLabel(v, lbl);

    /* This just creates a place-holder record in the sqlite_master table.
    ** The record created does not contain anything yet.  It will be replaced
    ** by the real entry in code generated at sqlite3EndTable().
    **
    ** The rowid for the new entry is left on the top of the stack.
    ** The rowid value is needed by the code that sqlite3EndTable will

**
*************************************************************************
** 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.281 2005/02/19 08:18:06 danielk1977 Exp $
*/
#include "sqliteInt.h"
#include "os.h"
#include <ctype.h>

/*
** The following constant value is used by the SQLITE_BIGENDIAN and

  if( iDb==0 ){
    db->file_format = meta[1];
    if( db->file_format==0 ){
      /* This happens if the database was initially empty */
      db->file_format = 1;
    }

    if( db->file_format==2 ){
      /* File format 2 is treated exactly as file format 1. New 
      ** databases are created with file format 1.
      */ 
      db->file_format = 1;
    }
  }

  /*
  ** file_format==1    Version 3.0.0.
  ** file_format==2    Version 3.1.3.
  **
  ** Version 3.0 can only use files with file_format==1. Version 3.1.3
  ** can read and write files with file_format==1 or file_format==2.
  */
  if( meta[1]>2 ){
    sqlite3BtreeCloseCursor(curMain);
    sqlite3SetString(pzErrMsg, "unsupported file format", (char*)0);
    return SQLITE_ERROR;
  }

  sqlite3BtreeSetCacheSize(db->aDb[iDb].pBt, db->aDb[iDb].cache_size);

**
** Various scripts scan this source file in order to generate HTML
** documentation, headers files, or other derived files.  The formatting
** of the code in this file is, therefore, important.  See other comments
** in this file for details.  If in doubt, do not deviate from existing
** commenting and indentation practices when changing or adding code.
**
** $Id: vdbe.c,v 1.456 2005/02/19 08:18:06 danielk1977 Exp $
*/
#include "sqliteInt.h"
#include "os.h"
#include <ctype.h>
#include "vdbeInt.h"

/*

}

/* Opcode: Column P1 P2 *
**
** Interpret the data that cursor P1 points to as a structure built using
** the MakeRecord instruction.  (See the MakeRecord opcode for additional
** information about the format of the data.) Push onto the stack the value
** of the P2-th column contained in the data. If there are less that (P2+1) 
** values in the record, push a NULL onto the stack.
**
** If the KeyAsData opcode has previously executed on this cursor, then the
** field might be extracted from the key rather than the data.
**
** If P1 is negative, then the record is stored on the stack rather than in
** a table.  For P1==-1, the top of the stack is used.  For P1==-2, the
** next on the stack is used.  And so forth.  The value pushed is always

    /* Scan the header and use it to fill in the aType[] and aOffset[]
    ** arrays.  aType[i] will contain the type integer for the i-th
    ** column and aOffset[i] will contain the offset from the beginning
    ** of the record to the start of the data for the i-th column
    */
    offset = szHdr;
    assert( offset>0 );
    i = 0;
    while( idx<szHdr && i<nField && offset<=payloadSize ){
      aOffset[i] = offset;
      idx += sqlite3GetVarint32(&zData[idx], &aType[i]);
      offset += sqlite3VdbeSerialTypeLen(aType[i]);
      i++;
    }
    Release(&sMem);
    sMem.flags = MEM_Null;

    /* If i is less that nField, then there are less fields in this
    ** record than SetNumColumns indicated there are columns in the
    ** table. Set the offset for any extra columns not present in
    ** the record to 0. This tells code below to push a NULL onto the
    ** stack instead of deserializing a value from the record.
    */
    while( i<nField ){
      aOffset[i++] = 0;
    }

    /* The header should end at the start of data and the data should
    ** end at last byte of the record. If this is not the case then
    ** we are dealing with a malformed record.
    */
    if( idx!=szHdr || offset!=payloadSize ){
      rc = SQLITE_CORRUPT;
      goto op_column_out;
    }

    /* Remember all aType and aColumn information if we have a cursor
    ** to remember it in. */
    if( pC ){
      pC->payloadSize = payloadSize;
      pC->aType = aType;
      pC->aOffset = aOffset;
      pC->cacheValid = 1;
    }
  }

  /* Get the column information. If aOffset[p2] is non-zero, then 
  ** deserialize the value from the record. If aOffset[p2] is zero,
  ** then there are not enough fields in the record to satisfy the
  ** request. The value is NULL in this case.
  */
  if( aOffset[p2] ){
    assert( rc==SQLITE_OK );
    if( zRec ){
      zData = &zRec[aOffset[p2]];
    }else{
      len = sqlite3VdbeSerialTypeLen(aType[p2]);
      rc = sqlite3VdbeMemFromBtree(pCrsr, aOffset[p2], len,pC->keyAsData,&sMem);
      if( rc!=SQLITE_OK ){
        goto op_column_out;
      }
      zData = sMem.z;
    }
    sqlite3VdbeSerialGet(zData, aType[p2], pTos);
    pTos->enc = db->enc;
  }else{
    pTos->flags = MEM_Null;
  }

  /* If we dynamically allocated space to hold the data (in the
  ** sqlite3VdbeMemFromBtree() call above) then transfer control of that
  ** dynamically allocated space over to the pTos structure rather.
  ** This prevents a memory copy.
  */
  if( (sMem.flags & MEM_Dyn)!=0 ){

# 2005 February 18
#
# 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 testing that SQLite can handle a subtle 
# file format change that may be used in the future to implement
# "ALTER TABLE ... ADD COLUMN".
#
# $Id: alter2.test,v 1.1 2005/02/19 08:18:06 danielk1977 Exp $
#

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

# The file format change affects the way row-records stored in tables (but 
# not indices) are interpreted. Before version 3.1.3, a row-record for a 
# table with N columns was guaranteed to contain exactly N fields. As
# of version 3.1.3, the record may contain up to N fields. In this case
# the M fields that are present are the values for the left-most M 
# columns. The (N-M) rightmost columns contain NULL.
#
# If any records in the database contain less fields than their table
# has columns, then the file-format meta value should be set to (at least) 2. 
#

# This procedure sets the value of the file-format in file 'test.db'
# to $newval. Also, the schema cookie is incremented.
# 
proc set_file_format {newval} {
  set bt [btree_open test.db 10 0]
  btree_begin_transaction $bt
  set meta [btree_get_meta $bt]
  lset meta 2 $newval                    ;# File format
  lset meta 1 [expr [lindex $meta 1]+1]  ;# Schema cookie
  eval "btree_update_meta $bt $meta"
  btree_commit $bt
  btree_close $bt
}

# This procedure returns the value of the file-format in file 'test.db'.
# 
proc get_file_format {{fname test.db}} {
  set bt [btree_open $fname 10 0]
  set meta [btree_get_meta $bt]
  btree_close $bt
  lindex $meta 2
}

# This procedure sets the SQL statement stored for table $tbl in the
# sqlite_master table of file 'test.db' to $sql.
#
proc alter_table {tbl sql} {
  sqlite3 dbat test.db
  dbat eval {
    PRAGMA writable_schema = 1;
    UPDATE sqlite_master SET sql = $sql WHERE name = $tbl AND type = 'table';
    PRAGMA writable_schema = 0;
  }
  dbat close
  set_file_format 2
}

#-----------------------------------------------------------------------
# Some basic tests to make sure short rows are handled.
#
do_test alter2-1.1 {
  execsql {
    CREATE TABLE abc(a, b);
    INSERT INTO abc VALUES(1, 2);
    INSERT INTO abc VALUES(3, 4);
    INSERT INTO abc VALUES(5, 6);
  }
} {}
do_test alter2-1.2 {
  # ALTER TABLE abc ADD COLUMN c;
  alter_table abc {CREATE TABLE abc(a, b, c);}
} {}
do_test alter2-1.3 {
  execsql {
    SELECT * FROM abc;
  }
} {1 2 {} 3 4 {} 5 6 {}}
do_test alter2-1.4 {
  execsql {
    UPDATE abc SET c = 10 WHERE a = 1;
    SELECT * FROM abc;
  }
} {1 2 10 3 4 {} 5 6 {}}
do_test alter2-1.5 {
  execsql {
    CREATE INDEX abc_i ON abc(c);
  }
} {}
do_test alter2-1.6 {
  execsql {
    SELECT c FROM abc ORDER BY c;
  }
} {{} {} 10}
do_test alter2-1.7 {
  execsql {
    SELECT * FROM abc WHERE c = 10;
  }
} {1 2 10}
do_test alter2-1.8 {
  execsql {
    SELECT sum(a), c FROM abc GROUP BY c;
  }
} {8.0 {} 1.0 10}
do_test alter2-1.9 {
  # ALTER TABLE abc ADD COLUMN d;
  alter_table abc {CREATE TABLE abc(a, b, c, d);}
  execsql { SELECT * FROM abc; }
  execsql {
    UPDATE abc SET d = 11 WHERE c IS NULL AND a<4;
    SELECT * FROM abc;
  }
} {1 2 10 {} 3 4 {} 11 5 6 {} {}}
do_test alter2-1.10 {
  execsql {
    SELECT typeof(d) FROM abc;
  }
} {null integer null}
do_test alter2-1.99 {
  execsql {
    DROP TABLE abc;
  }
} {}

#-----------------------------------------------------------------------
# Test that views work when the underlying table structure is changed.
#
ifcapable view {
  do_test alter2-2.1 {
    execsql {
      CREATE TABLE abc2(a, b, c);
      INSERT INTO abc2 VALUES(1, 2, 10);
      INSERT INTO abc2 VALUES(3, 4, NULL);
      INSERT INTO abc2 VALUES(5, 6, NULL);
      CREATE VIEW abc2_v AS SELECT * FROM abc2;
      SELECT * FROM abc2_v;
    }
  } {1 2 10 3 4 {} 5 6 {}}
  do_test alter2-2.2 {
    # ALTER TABLE abc ADD COLUMN d;
    alter_table abc2 {CREATE TABLE abc2(a, b, c, d);}
    execsql {
      SELECT * FROM abc2_v;
    }
  } {1 2 10 {} 3 4 {} {} 5 6 {} {}}
  do_test alter2-2.3 {
    execsql {
      DROP TABLE abc2;
      DROP VIEW abc2_v;
    }
  } {}
}

#-----------------------------------------------------------------------
# Test that triggers work when a short row is copied to the old.*
# trigger pseudo-table.
#
ifcapable trigger {
  do_test alter2-3.1 {
    execsql {
      CREATE TABLE abc3(a, b);
      CREATE TABLE blog(o, n);
      CREATE TRIGGER abc3_t AFTER UPDATE OF b ON abc3 BEGIN
        INSERT INTO blog VALUES(old.b, new.b);
      END;
    }
  } {}
  do_test alter2-3.2 {
    execsql {
      INSERT INTO abc3 VALUES(1, 4);
      UPDATE abc3 SET b = 2 WHERE b = 4;
      SELECT * FROM blog;
    }
  } {4 2}
  do_test alter2-3.3 {
    execsql {
      INSERT INTO abc3 VALUES(3, 4);
      INSERT INTO abc3 VALUES(5, 6);
    }
    alter_table abc3 {CREATE TABLE abc3(a, b, c);}
    execsql {
      SELECT * FROM abc3;
    }
  } {1 2 {} 3 4 {} 5 6 {}}
  do_test alter2-3.4 {
    execsql {
      UPDATE abc3 SET b = b*2 WHERE a<4;
      SELECT * FROM abc3;
    }
  } {1 4 {} 3 8 {} 5 6 {}}
  do_test alter2-3.5 {
    execsql {
      SELECT * FROM blog;
    }
  } {4 2 2 4 4 8}

  do_test alter2-3.6 {
    execsql {
      CREATE TABLE clog(o, n);
      CREATE TRIGGER abc3_t2 AFTER UPDATE OF c ON abc3 BEGIN
        INSERT INTO clog VALUES(old.c, new.c);
      END;
      UPDATE abc3 SET c = a*2;
      SELECT * FROM clog;
    }
  } {{} 2 {} 6 {} 10}
}

#---------------------------------------------------------------------
# Check that an error occurs if the database is upgraded to a file
# format that SQLite does not support (in this case 3). Note: The 
# file format is checked each time the schema is read, so changing the
# file format requires incrementing the schema cookie.
#
do_test alter2-4.1 {
  set_file_format 3
} {}
do_test alter2-4.2 {
  catchsql {
    SELECT * FROM sqlite_master;
  }
} {1 {unsupported file format}}
do_test alter2-4.3 {
  sqlite3_errcode $::DB
} {SQLITE_ERROR}
do_test alter2-4.4 {
  db close
  set ::DB [sqlite3 db test.db]
  catchsql {
    SELECT * FROM sqlite_master;
  }
} {1 {unsupported file format}}
do_test alter2-4.5 {
  sqlite3_errcode $::DB
} {SQLITE_ERROR}

#---------------------------------------------------------------------
# Check that executing VACUUM on a file with file-format version 2
# resets the file format to 1.
#
do_test alter2-5.1 {
  set_file_format 2
  get_file_format
} {2}
do_test alter2-5.2 {
  execsql {
    VACUUM;
  }
} {}
do_test alter2-5.3 {
  get_file_format
} {1}
 
#---------------------------------------------------------------------
# Test that when a database with file-format 2 is opened, new 
# databases are still created with file-format 1.
#
do_test alter2-6.1 {
  db close
  set_file_format 2
  set ::DB [sqlite3 db test.db]
  get_file_format
} {2}
do_test alter2-6.2 {
  file delete -force test2.db-journal
  file delete -force test2.db
  execsql {
    ATTACH 'test2.db' AS aux;
    CREATE TABLE aux.t1(a, b);
  }
  get_file_format test2.db
} {1}
do_test alter2-6.3 {
  execsql {
    CREATE TABLE t1(a, b);
  }
  get_file_format 
} {2}

finish_test

# 2003 January 29
#
# 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 testing the callback-free C/C++ API.
#
# $Id: capi3.test,v 1.30 2005/02/19 08:18:06 danielk1977 Exp $
#

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

# Return the UTF-16 representation of the supplied UTF-8 string $str.
# If $nt is true, append two 0x00 bytes as a nul terminator.

if {![sqlite3 -has-codec]} {
  # Test what happens when the library encounters a newer file format.
  # Do this by updating the file format via the btree layer.
  do_test capi3-7.1 {
    set ::bt [btree_open test.db 10 0]
    btree_begin_transaction $::bt
    set meta [btree_get_meta $::bt]
    lset meta 2 3
    eval [concat btree_update_meta $::bt [lrange $meta 0 end]]
    btree_commit $::bt
    btree_close $::bt
  } {}
  do_test capi3-7.2 {
    sqlite3 db test.db
    catchsql {

#
#***********************************************************************
# This file implements regression tests for SQLite library.
#
# This file implements tests to make sure SQLite does not crash or
# segfault if it sees a corrupt database file.
#
# $Id: corrupt.test,v 1.8 2005/02/19 08:18:06 danielk1977 Exp $

catch {file delete -force test.db}
catch {file delete -force test.db-journal}

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

# Construct a large database for testing.
#
do_test corrupt-1.1 {
  execsql {
    BEGIN;
    CREATE TABLE t1(x);
    INSERT INTO t1 VALUES(randstr(100,100));
    INSERT INTO t1 VALUES(randstr(90,90));
    INSERT INTO t1 VALUES(randstr(80,80));
    INSERT INTO t1 SELECT x || randstr(5,5) FROM t1;
    INSERT INTO t1 SELECT x || randstr(6,6) FROM t1;
    INSERT INTO t1 SELECT x || randstr(7,7) FROM t1;
    INSERT INTO t1 SELECT x || randstr(8,8) FROM t1;
    INSERT INTO t1 VALUES(randstr(3000,3000));
    INSERT INTO t1 SELECT x || randstr(9,9) FROM t1;
    INSERT INTO t1 SELECT x || randstr(10,10) FROM t1;
    INSERT INTO t1 SELECT x || randstr(11,11) FROM t1;
    INSERT INTO t1 SELECT x || randstr(12,12) FROM t1;
    CREATE INDEX t1i1 ON t1(x);
    CREATE TABLE t2 AS SELECT * FROM t1;
    DELETE FROM t2 WHERE rowid%5!=0;
    COMMIT;
  }
} {}
integrity_check corrupt-1.2