SQLite

# focus of this script is testing the AUTOINCREMENT features.
#
# $Id: autoinc.test,v 1.14 2009/06/23 20:28:54 drh Exp $
#

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

# If the library is not compiled with autoincrement support then
# skip all tests in this file.
#
ifcapable {!autoinc} {
  finish_test
  return

  set res [catch {db eval {
    INSERT INTO t1(b) VALUES('two'),('three'),('four');
    INSERT INTO t1(b) VALUES('five');
    PRAGMA integrity_check;
  }} msg]
  lappend res $msg
} {0 ok}

#--------------------------------------------------------------------------
reset_db
do_execsql_test 13.0 {
  CREATE TABLE t1(i INTEGER PRIMARY KEY AUTOINCREMENT, j);
  CREATE TABLE t2(i INTEGER PRIMARY KEY AUTOINCREMENT, j);
  CREATE TABLE t3(i INTEGER PRIMARY KEY AUTOINCREMENT, j);

  INSERT INTO t1 VALUES(NULL, 1);
  INSERT INTO t2 VALUES(NULL, 2);
  INSERT INTO t3 VALUES(NULL, 3);

  SELECT name FROM sqlite_sequence;
} {t1 t2 t3}

do_execsql_test 13.1 {
  UPDATE sqlite_sequence SET name=NULL WHERE name='t2';
  INSERT INTO t3 VALUES(NULL, 4);
  DELETE FROM t3;
  INSERT INTO t3 VALUES(NULL, 5);
  SELECT * FROM t3;
} {3 5}


finish_test

do_execsql_test 6.1 {
  DROP TABLE IF EXISTS t2;
  CREATE TABLE t2(x INT);
  INSERT INTO t2(x) VALUES(1),(2),(3),(4),(5);
  DELETE FROM t2 WHERE EXISTS(SELECT 1 FROM t2 AS v WHERE v.x=t2.x+1);
  SELECT x FROM t2;
} {5}

#-------------------------------------------------------------------------
# Test the effect of failing to find a table row based on an index key
# within a DELETE. Either because the db is corrupt, or a trigger on another
# row already deleted the entry, or because a BEFORE trigger on the current
# row has already deleted it.
#
do_execsql_test 7.1.0 {
  CREATE TABLE t3(id INT PRIMARY KEY, a, b) WITHOUT ROWID;
  CREATE INDEX t3a ON t3(a);
  CREATE INDEX t3b ON t3(b);

  INSERT INTO t3 VALUES(1, 1, 1);
  INSERT INTO t3 VALUES(2, 2, 2);
  INSERT INTO t3 VALUES(3, 3, 3);
  INSERT INTO t3 VALUES(4, 4, 1);
}
do_execsql_test 7.1.1 {
  DELETE FROM t3 WHERE a=4 OR b=1;
}
do_execsql_test 7.1.2 {
  SELECT * FROM t3;
} { 2 2 2   3 3 3 }

do_execsql_test 7.2.0 {
  CREATE TABLE t4(a PRIMARY KEY, b) WITHOUT ROWID;
  CREATE INDEX t4i ON t4(b);
  INSERT INTO t4 VALUES(1, 'hello');
  INSERT INTO t4 VALUES(2, 'world');

  CREATE TABLE t5(a PRIMARY KEY, b) WITHOUT ROWID;
  CREATE INDEX t5i ON t5(b);
  INSERT INTO t5 VALUES(1, 'hello');
  INSERT INTO t5 VALUES(3, 'world');

  PRAGMA writable_schema = 1;
  UPDATE sqlite_master SET rootpage = (
    SELECT rootpage FROM sqlite_master WHERE name = 't5'
  ) WHERE name = 't4';
}

db close
sqlite3 db test.db
do_execsql_test 7.2.1 {
  DELETE FROM t4 WHERE b='world'
}
reset_db

do_execsql_test 7.3.0 {
  CREATE TABLE t3(id INT PRIMARY KEY, a, b) WITHOUT ROWID;
  INSERT INTO t3 VALUES(1, 2, 3);
  INSERT INTO t3 VALUES(4, 5, 6);
  INSERT INTO t3 VALUES(7, 8, 9);
  CREATE TRIGGER t3t BEFORE DELETE ON t3 BEGIN
    DELETE FROM t3 WHERE id=old.id+3;
  END;
}

do_execsql_test 7.3.1 {
  DELETE FROM t3 WHERE a IN(2, 5, 8);
  SELECT * FROM t3;
} {}

do_execsql_test 7.3.2 {
  DROP TRIGGER t3t;
  INSERT INTO t3 VALUES(1, 2, 3);
  INSERT INTO t3 VALUES(4, 5, 6);
  INSERT INTO t3 VALUES(7, 8, 9);
  CREATE TRIGGER t3t BEFORE DELETE ON t3 BEGIN
    DELETE FROM t3 WHERE id=old.id;
  END;
}

do_execsql_test 7.3.3 {
  DELETE FROM t3 WHERE a IN(2, 5, 8);
  SELECT * FROM t3;
} {}


finish_test

do_test 10.3 {
  execsql { PRAGMA integrity_check }
  set sqlite3_xferopt_count 0
  execsql { INSERT INTO x     SELECT * FROM t8 }
  set sqlite3_xferopt_count
} {1}

#-------------------------------------------------------------------------
# xfer transfer between tables where the source has an empty partial index.
#
do_execsql_test 11.0 {
  CREATE TABLE t9(a, b, c);
  CREATE INDEX t9a ON t9(a);
  CREATE INDEX t9b ON t9(b) WHERE c=0;

  INSERT INTO t9 VALUES(1, 1, 1);
  INSERT INTO t9 VALUES(2, 2, 2);
  INSERT INTO t9 VALUES(3, 3, 3);

  CREATE TABLE t10(a, b, c);
  CREATE INDEX t10a ON t10(a);
  CREATE INDEX t10b ON t10(b) WHERE c=0;

  INSERT INTO t10 SELECT * FROM t9;
  SELECT * FROM t10;
  PRAGMA integrity_check;
} {1 1 1  2 2 2  3 3 3  ok}

finish_test

} {0}
do_test pragma-1.14.4 {
  execsql {
    PRAGMA synchronous=10;
    PRAGMA synchronous;
  }
} {2}

do_execsql_test 1.15.1 {
  PRAGMA default_cache_size = 0;
}
do_execsql_test 1.15.2 {
  PRAGMA default_cache_size;
} $DFLT_CACHE_SZ
do_execsql_test 1.15.3 {
  PRAGMA default_cache_size = -500;
}
do_execsql_test 1.15.4 {
  PRAGMA default_cache_size;
} 500
do_execsql_test 1.15.3 {
  PRAGMA default_cache_size = 500;
}
do_execsql_test 1.15.4 {
  PRAGMA default_cache_size;
} 500
db close
hexio_write test.db 48 FFFFFF00
sqlite3 db test.db
do_execsql_test 1.15.4 {
  PRAGMA default_cache_size;
} 256
} ;# ifcapable pager_pragmas

# Test turning "flag" pragmas on and off.
#
ifcapable debug {
  # Pragma "vdbe_listing" is only available if compiled with SQLITE_DEBUG
  #

} {1 {1st ORDER BY term does not match any column in the result set}}

do_catchsql_test 16.2 {
  SELECT count(*) FROM sqlite_master 
  GROUP BY raise(IGNORE) 
  HAVING raise(ABORT, 'msg');
} {1 {RAISE() may only be used within a trigger-program}}

#-------------------------------------------------------------------------
# Datatype mismatch on IPK when there are BEFORE triggers.
#
do_execsql_test 17.0 {
  CREATE TABLE xyz(x INTEGER PRIMARY KEY, y, z);
  CREATE TRIGGER xyz_tr BEFORE INSERT ON xyz BEGIN
    SELECT new.x;
  END;
}
do_catchsql_test 17.1 {
  INSERT INTO xyz VALUES('hello', 2, 3);
} {1 {datatype mismatch}}


finish_test

  CREATE UNIQUE INDEX t2b ON t2(b);
  UPDATE t2 SET b=1 WHERE b='';
}

do_execsql_test 10.1 {
  DELETE FROM t2 WHERE b=1
}

#-------------------------------------------------------------------------
# UNIQUE constraint violation in an UPDATE with a multi-column PK.
#
reset_db
do_execsql_test 10.0 {
  CREATE TABLE t1(a, b, c UNIQUE, PRIMARY KEY(a, b)) WITHOUT ROWID;
  INSERT INTO t1 VALUES('a', 'a', 1);
  INSERT INTO t1 VALUES('a', 'b', 2);
  INSERT INTO t1 VALUES('b', 'a', 3);
  INSERT INTO t1 VALUES('b', 'b', 4);
}

do_catchsql_test 10.1 {
  UPDATE t1 SET c=1 WHERE (a, b) = ('a', 'a');
} {0 {}}
do_catchsql_test 10.2 {
  UPDATE t1 SET c=1 WHERE (a, b) = ('a', 'b');
} {1 {UNIQUE constraint failed: t1.c}}
do_catchsql_test 10.3 {
  UPDATE t1 SET c=1 WHERE (a, b) = ('b', 'a');
} {1 {UNIQUE constraint failed: t1.c}}
do_catchsql_test 10.4 {
  UPDATE t1 SET c=1 WHERE (a, b) = ('b', 'b');
} {1 {UNIQUE constraint failed: t1.c}}

  
finish_test