sqlite/go-sqlite
1
0
Fork 0
mirror of https://gitlab.com/cznic/sqlite.git synced 2024-11-24 02:26:14 +00:00
Code
master
go-sqlite/testdata/tcl/autoinc.test
Jan Mercl 8db47beb23 upgrade to tcl8.6.12/2
2021-12-08 11:33:15 +01:00

884 lines
23 KiB
Plaintext
Raw Permalink Blame History

# 2004 November 12
#
# 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 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
}
if {[permutation]=="inmemory_journal"} {
finish_test
return
}
sqlite3_db_config_lookaside db 0 0 0
# The database is initially empty.
#
do_test autoinc-1.1 {
execsql {
SELECT name FROM sqlite_master WHERE type='table';
}
} {}
# Add a table with the AUTOINCREMENT feature. Verify that the
# SQLITE_SEQUENCE table gets created.
#
do_test autoinc-1.2 {
execsql {
CREATE TABLE t1(x INTEGER PRIMARY KEY AUTOINCREMENT, y);
SELECT name FROM sqlite_master WHERE type='table';
}
} {t1 sqlite_sequence}
# The SQLITE_SEQUENCE table is initially empty
#
do_test autoinc-1.3 {
execsql {
SELECT * FROM sqlite_sequence;
}
} {}
do_test autoinc-1.3.1 {
catchsql {
CREATE INDEX seqidx ON sqlite_sequence(name)
}
} {1 {table sqlite_sequence may not be indexed}}
# Close and reopen the database. Verify that everything is still there.
#
do_test autoinc-1.4 {
db close
sqlite3 db test.db
execsql {
SELECT * FROM sqlite_sequence;
}
} {}
# We are not allowed to drop the sqlite_sequence table.
#
do_test autoinc-1.5 {
catchsql {DROP TABLE sqlite_sequence}
} {1 {table sqlite_sequence may not be dropped}}
do_test autoinc-1.6 {
execsql {SELECT name FROM sqlite_master WHERE type='table'}
} {t1 sqlite_sequence}
# Insert an entries into the t1 table and make sure the largest key
# is always recorded in the sqlite_sequence table.
#
do_test autoinc-2.1 {
execsql {
SELECT * FROM sqlite_sequence
}
} {}
do_test autoinc-2.2 {
execsql {
INSERT INTO t1 VALUES(12,34);
SELECT * FROM sqlite_sequence;
}
} {t1 12}
do_test autoinc-2.3 {
execsql {
INSERT INTO t1 VALUES(1,23);
SELECT * FROM sqlite_sequence;
}
} {t1 12}
do_test autoinc-2.4 {
execsql {
INSERT INTO t1 VALUES(123,456);
SELECT * FROM sqlite_sequence;
}
} {t1 123}
do_test autoinc-2.5 {
execsql {
INSERT INTO t1 VALUES(NULL,567);
SELECT * FROM sqlite_sequence;
}
} {t1 124}
do_test autoinc-2.6 {
execsql {
DELETE FROM t1 WHERE y=567;
SELECT * FROM sqlite_sequence;
}
} {t1 124}
do_test autoinc-2.7 {
execsql {
INSERT INTO t1 VALUES(NULL,567);
SELECT * FROM sqlite_sequence;
}
} {t1 125}
do_test autoinc-2.8 {
execsql {
DELETE FROM t1;
SELECT * FROM sqlite_sequence;
}
} {t1 125}
do_test autoinc-2.9 {
execsql {
INSERT INTO t1 VALUES(12,34);
SELECT * FROM sqlite_sequence;
}
} {t1 125}
do_test autoinc-2.10 {
execsql {
INSERT INTO t1 VALUES(125,456);
SELECT * FROM sqlite_sequence;
}
} {t1 125}
do_test autoinc-2.11 {
execsql {
INSERT INTO t1 VALUES(-1234567,-1);
SELECT * FROM sqlite_sequence;
}
} {t1 125}
do_test autoinc-2.12 {
execsql {
INSERT INTO t1 VALUES(234,5678);
SELECT * FROM sqlite_sequence;
}
} {t1 234}
do_test autoinc-2.13 {
execsql {
DELETE FROM t1;
INSERT INTO t1 VALUES(NULL,1);
SELECT * FROM sqlite_sequence;
}
} {t1 235}
do_test autoinc-2.14 {
execsql {
SELECT * FROM t1;
}
} {235 1}
# Manually change the autoincrement values in sqlite_sequence.
#
do_test autoinc-2.20 {
execsql {
UPDATE sqlite_sequence SET seq=1234 WHERE name='t1';
INSERT INTO t1 VALUES(NULL,2);
SELECT * FROM t1;
}
} {235 1 1235 2}
do_test autoinc-2.21 {
execsql {
SELECT * FROM sqlite_sequence;
}
} {t1 1235}
do_test autoinc-2.22 {
execsql {
UPDATE sqlite_sequence SET seq=NULL WHERE name='t1';
INSERT INTO t1 VALUES(NULL,3);
SELECT * FROM t1;
}
} {235 1 1235 2 1236 3}
do_test autoinc-2.23 {
execsql {
SELECT * FROM sqlite_sequence;
}
} {t1 1236}
do_test autoinc-2.24 {
execsql {
UPDATE sqlite_sequence SET seq='a-string' WHERE name='t1';
INSERT INTO t1 VALUES(NULL,4);
SELECT * FROM t1;
}
} {235 1 1235 2 1236 3 1237 4}
do_test autoinc-2.25 {
execsql {
SELECT * FROM sqlite_sequence;
}
} {t1 1237}
do_test autoinc-2.26 {
execsql {
DELETE FROM sqlite_sequence WHERE name='t1';
INSERT INTO t1 VALUES(NULL,5);
SELECT * FROM t1;
}
} {235 1 1235 2 1236 3 1237 4 1238 5}
do_test autoinc-2.27 {
execsql {
SELECT * FROM sqlite_sequence;
}
} {t1 1238}
do_test autoinc-2.28 {
execsql {
UPDATE sqlite_sequence SET seq='-12345678901234567890'
WHERE name='t1';
INSERT INTO t1 VALUES(NULL,6);
SELECT * FROM t1;
}
} {235 1 1235 2 1236 3 1237 4 1238 5 1239 6}
do_test autoinc-2.29 {
execsql {
SELECT * FROM sqlite_sequence;
}
} {t1 1239}
# Test multi-row inserts
#
do_test autoinc-2.50 {
execsql {
DELETE FROM t1 WHERE y>=3;
INSERT INTO t1 SELECT NULL, y+2 FROM t1;
SELECT * FROM t1;
}
} {235 1 1235 2 1240 3 1241 4}
do_test autoinc-2.51 {
execsql {
SELECT * FROM sqlite_sequence
}
} {t1 1241}
ifcapable tempdb {
do_test autoinc-2.52 {
execsql {
CREATE TEMP TABLE t2 AS SELECT y FROM t1;
}
execsql {
INSERT INTO t1 SELECT NULL, y+4 FROM t2;
SELECT * FROM t1;
}
} {235 1 1235 2 1240 3 1241 4 1242 5 1243 6 1244 7 1245 8}
do_test autoinc-2.53 {
execsql {
SELECT * FROM sqlite_sequence
}
} {t1 1245}
do_test autoinc-2.54 {
execsql {
DELETE FROM t1;
INSERT INTO t1 SELECT NULL, y FROM t2;
SELECT * FROM t1;
}
} {1246 1 1247 2 1248 3 1249 4}
do_test autoinc-2.55 {
execsql {
SELECT * FROM sqlite_sequence
}
} {t1 1249}
}
# Create multiple AUTOINCREMENT tables. Make sure all sequences are
# tracked separately and do not interfere with one another.
#
do_test autoinc-2.70 {
catchsql {
DROP TABLE t2;
}
execsql {
CREATE TABLE t2(d, e INTEGER PRIMARY KEY AUTOINCREMENT, f);
INSERT INTO t2(d) VALUES(1);
SELECT * FROM sqlite_sequence;
}
} [ifcapable tempdb {list t1 1249 t2 1} else {list t1 1241 t2 1}]
do_test autoinc-2.71 {
execsql {
INSERT INTO t2(d) VALUES(2);
SELECT * FROM sqlite_sequence;
}
} [ifcapable tempdb {list t1 1249 t2 2} else {list t1 1241 t2 2}]
do_test autoinc-2.72 {
execsql {
INSERT INTO t1(x) VALUES(10000);
SELECT * FROM sqlite_sequence;
}
} {t1 10000 t2 2}
do_test autoinc-2.73 {
execsql {
CREATE TABLE t3(g INTEGER PRIMARY KEY AUTOINCREMENT, h);
INSERT INTO t3(h) VALUES(1);
SELECT * FROM sqlite_sequence;
}
} {t1 10000 t2 2 t3 1}
do_test autoinc-2.74 {
execsql {
INSERT INTO t2(d,e) VALUES(3,100);
SELECT * FROM sqlite_sequence;
}
} {t1 10000 t2 100 t3 1}
# When a table with an AUTOINCREMENT is deleted, the corresponding entry
# in the SQLITE_SEQUENCE table should also be deleted. But the SQLITE_SEQUENCE
# table itself should remain behind.
#
do_test autoinc-3.1 {
execsql {SELECT name FROM sqlite_sequence}
} {t1 t2 t3}
do_test autoinc-3.2 {
execsql {
DROP TABLE t1;
SELECT name FROM sqlite_sequence;
}
} {t2 t3}
do_test autoinc-3.3 {
execsql {
DROP TABLE t3;
SELECT name FROM sqlite_sequence;
}
} {t2}
do_test autoinc-3.4 {
execsql {
DROP TABLE t2;
SELECT name FROM sqlite_sequence;
}
} {}
# AUTOINCREMENT on TEMP tables.
#
ifcapable tempdb {
do_test autoinc-4.1 {
execsql {
SELECT 1, name FROM sqlite_master WHERE type='table';
SELECT 2, name FROM temp.sqlite_master WHERE type='table';
}
} {1 sqlite_sequence}
do_test autoinc-4.2 {
execsql {
CREATE TABLE t1(x INTEGER PRIMARY KEY AUTOINCREMENT, y);
CREATE TEMP TABLE t3(a INTEGER PRIMARY KEY AUTOINCREMENT, b);
SELECT 1, name FROM sqlite_master WHERE type='table';
SELECT 2, name FROM sqlite_temp_master WHERE type='table';
}
} {1 sqlite_sequence 1 t1 2 t3 2 sqlite_sequence}
do_test autoinc-4.3 {
execsql {
SELECT 1, * FROM main.sqlite_sequence;
SELECT 2, * FROM temp.sqlite_sequence;
}
} {}
do_test autoinc-4.4 {
execsql {
INSERT INTO t1 VALUES(10,1);
INSERT INTO t3 VALUES(20,2);
INSERT INTO t1 VALUES(NULL,3);
INSERT INTO t3 VALUES(NULL,4);
}
} {}
ifcapable compound {
do_test autoinc-4.4.1 {
execsql {
SELECT * FROM t1 UNION ALL SELECT * FROM t3;
}
} {10 1 11 3 20 2 21 4}
} ;# ifcapable compound
do_test autoinc-4.5 {
execsql {
SELECT 1, * FROM main.sqlite_sequence;
SELECT 2, * FROM temp.sqlite_sequence;
}
} {1 t1 11 2 t3 21}
do_test autoinc-4.6 {
execsql {
INSERT INTO t1 SELECT * FROM t3;
SELECT 1, * FROM main.sqlite_sequence;
SELECT 2, * FROM temp.sqlite_sequence;
}
} {1 t1 21 2 t3 21}
do_test autoinc-4.7 {
execsql {
INSERT INTO t3 SELECT x+100, y FROM t1;
SELECT 1, * FROM main.sqlite_sequence;
SELECT 2, * FROM temp.sqlite_sequence;
}
} {1 t1 21 2 t3 121}
do_test autoinc-4.8 {
execsql {
DROP TABLE t3;
SELECT 1, * FROM main.sqlite_sequence;
SELECT 2, * FROM temp.sqlite_sequence;
}
} {1 t1 21}
do_test autoinc-4.9 {
execsql {
CREATE TEMP TABLE t2(p INTEGER PRIMARY KEY AUTOINCREMENT, q);
INSERT INTO t2 SELECT * FROM t1;
DROP TABLE t1;
SELECT 1, * FROM main.sqlite_sequence;
SELECT 2, * FROM temp.sqlite_sequence;
}
} {2 t2 21}
do_test autoinc-4.10 {
execsql {
DROP TABLE t2;
SELECT 1, * FROM main.sqlite_sequence;
SELECT 2, * FROM temp.sqlite_sequence;
}
} {}
}
# Make sure AUTOINCREMENT works on ATTACH-ed tables.
#
ifcapable tempdb&&attach {
do_test autoinc-5.1 {
forcedelete test2.db
forcedelete test2.db-journal
sqlite3 db2 test2.db
execsql {
CREATE TABLE t4(m INTEGER PRIMARY KEY AUTOINCREMENT, n);
CREATE TABLE t5(o, p INTEGER PRIMARY KEY AUTOINCREMENT);
} db2;
execsql {
ATTACH 'test2.db' as aux;
SELECT 1, * FROM main.sqlite_sequence;
SELECT 2, * FROM temp.sqlite_sequence;
SELECT 3, * FROM aux.sqlite_sequence;
}
} {}
do_test autoinc-5.2 {
execsql {
INSERT INTO t4 VALUES(NULL,1);
SELECT 1, * FROM main.sqlite_sequence;
SELECT 2, * FROM temp.sqlite_sequence;
SELECT 3, * FROM aux.sqlite_sequence;
}
} {3 t4 1}
do_test autoinc-5.3 {
execsql {
INSERT INTO t5 VALUES(100,200);
SELECT * FROM sqlite_sequence
} db2
} {t4 1 t5 200}
do_test autoinc-5.4 {
execsql {
SELECT 1, * FROM main.sqlite_sequence;
SELECT 2, * FROM temp.sqlite_sequence;
SELECT 3, * FROM aux.sqlite_sequence;
}
} {3 t4 1 3 t5 200}
}
# Requirement REQ00310: Make sure an insert fails if the sequence is
# already at its maximum value.
#
ifcapable {rowid32} {
do_test autoinc-6.1 {
execsql {
CREATE TABLE t6(v INTEGER PRIMARY KEY AUTOINCREMENT, w);
INSERT INTO t6 VALUES(2147483647,1);
SELECT seq FROM main.sqlite_sequence WHERE name='t6';
}
} 2147483647
}
ifcapable {!rowid32} {
do_test autoinc-6.1 {
execsql {
CREATE TABLE t6(v INTEGER PRIMARY KEY AUTOINCREMENT, w);
INSERT INTO t6 VALUES(9223372036854775807,1);
SELECT seq FROM main.sqlite_sequence WHERE name='t6';
}
} 9223372036854775807
}
do_test autoinc-6.2 {
catchsql {
INSERT INTO t6 VALUES(NULL,1);
}
} {1 {database or disk is full}}
# Allow the AUTOINCREMENT keyword inside the parentheses
# on a separate PRIMARY KEY designation.
#
do_test autoinc-7.1 {
execsql {
CREATE TABLE t7(x INTEGER, y REAL, PRIMARY KEY(x AUTOINCREMENT));
INSERT INTO t7(y) VALUES(123);
INSERT INTO t7(y) VALUES(234);
DELETE FROM t7;
INSERT INTO t7(y) VALUES(345);
SELECT * FROM t7;
}
} {3 345.0}
# Test that if the AUTOINCREMENT is applied to a non integer primary key
# the error message is sensible.
do_test autoinc-7.2 {
catchsql {
CREATE TABLE t8(x TEXT PRIMARY KEY AUTOINCREMENT);
}
} {1 {AUTOINCREMENT is only allowed on an INTEGER PRIMARY KEY}}
# Ticket #1283. Make sure that preparing but never running a statement
# that creates the sqlite_sequence table does not mess up the database.
#
do_test autoinc-8.1 {
catch {db2 close}
catch {db close}
forcedelete test.db
sqlite3 db test.db
set DB [sqlite3_connection_pointer db]
set STMT [sqlite3_prepare $DB {
CREATE TABLE t1(
x INTEGER PRIMARY KEY AUTOINCREMENT
)
} -1 TAIL]
sqlite3_finalize $STMT
set STMT [sqlite3_prepare $DB {
CREATE TABLE t1(
x INTEGER PRIMARY KEY AUTOINCREMENT
)
} -1 TAIL]
sqlite3_step $STMT
sqlite3_finalize $STMT
execsql {
INSERT INTO t1 VALUES(NULL);
SELECT * FROM t1;
}
} {1}
# Ticket #3148
# Make sure the sqlite_sequence table is not damaged when doing
# an empty insert - an INSERT INTO ... SELECT ... where the SELECT
# clause returns an empty set.
#
do_test autoinc-9.1 {
db eval {
CREATE TABLE t2(x INTEGER PRIMARY KEY AUTOINCREMENT, y);
INSERT INTO t2 VALUES(NULL, 1);
CREATE TABLE t3(a INTEGER PRIMARY KEY AUTOINCREMENT, b);
INSERT INTO t3 SELECT * FROM t2 WHERE y>1;
SELECT * FROM sqlite_sequence WHERE name='t3';
}
} {t3 0}
ifcapable trigger {
catchsql { pragma recursive_triggers = off }
# Ticket #3928. Make sure that triggers to not make extra slots in
# the SQLITE_SEQUENCE table.
#
do_test autoinc-3928.1 {
db eval {
CREATE TABLE t3928(a INTEGER PRIMARY KEY AUTOINCREMENT, b);
CREATE TRIGGER t3928r1 BEFORE INSERT ON t3928 BEGIN
INSERT INTO t3928(b) VALUES('before1');
INSERT INTO t3928(b) VALUES('before2');
END;
CREATE TRIGGER t3928r2 AFTER INSERT ON t3928 BEGIN
INSERT INTO t3928(b) VALUES('after1');
INSERT INTO t3928(b) VALUES('after2');
END;
INSERT INTO t3928(b) VALUES('test');
SELECT * FROM t3928 ORDER BY a;
}
} {1 before1 2 after1 3 after2 4 before2 5 after1 6 after2 7 test 8 before1 9 before2 10 after1 11 before1 12 before2 13 after2}
do_test autoinc-3928.2 {
db eval {
SELECT * FROM sqlite_sequence WHERE name='t3928'
}
} {t3928 13}
do_test autoinc-3928.3 {
db eval {
DROP TRIGGER t3928r1;
DROP TRIGGER t3928r2;
CREATE TRIGGER t3928r3 BEFORE UPDATE ON t3928
WHEN typeof(new.b)=='integer' BEGIN
INSERT INTO t3928(b) VALUES('before-int-' || new.b);
END;
CREATE TRIGGER t3928r4 AFTER UPDATE ON t3928
WHEN typeof(new.b)=='integer' BEGIN
INSERT INTO t3928(b) VALUES('after-int-' || new.b);
END;
DELETE FROM t3928 WHERE a!=1;
UPDATE t3928 SET b=456 WHERE a=1;
SELECT * FROM t3928 ORDER BY a;
}
} {1 456 14 before-int-456 15 after-int-456}
do_test autoinc-3928.4 {
db eval {
SELECT * FROM sqlite_sequence WHERE name='t3928'
}
} {t3928 15}
do_test autoinc-3928.5 {
db eval {
CREATE TABLE t3928b(x);
INSERT INTO t3928b VALUES(100);
INSERT INTO t3928b VALUES(200);
INSERT INTO t3928b VALUES(300);
DELETE FROM t3928;
CREATE TABLE t3928c(y INTEGER PRIMARY KEY AUTOINCREMENT, z);
CREATE TRIGGER t3928br1 BEFORE DELETE ON t3928b BEGIN
INSERT INTO t3928(b) VALUES('before-del-'||old.x);
INSERT INTO t3928c(z) VALUES('before-del-'||old.x);
END;
CREATE TRIGGER t3928br2 AFTER DELETE ON t3928b BEGIN
INSERT INTO t3928(b) VALUES('after-del-'||old.x);
INSERT INTO t3928c(z) VALUES('after-del-'||old.x);
END;
DELETE FROM t3928b;
SELECT * FROM t3928 ORDER BY a;
}
} {16 before-del-100 17 after-del-100 18 before-del-200 19 after-del-200 20 before-del-300 21 after-del-300}
do_test autoinc-3928.6 {
db eval {
SELECT * FROM t3928c ORDER BY y;
}
} {1 before-del-100 2 after-del-100 3 before-del-200 4 after-del-200 5 before-del-300 6 after-del-300}
do_test autoinc-3928.7 {
db eval {
SELECT * FROM sqlite_sequence WHERE name LIKE 't3928%' ORDER BY name;
}
} {t3928 21 t3928c 6}
# Ticket [a696379c1f0886615541a48b35bd8181a80e88f8]
do_test autoinc-a69637.1 {
db eval {
CREATE TABLE ta69637_1(x INTEGER PRIMARY KEY AUTOINCREMENT, y);
CREATE TABLE ta69637_2(z);
CREATE TRIGGER ra69637_1 AFTER INSERT ON ta69637_2 BEGIN
INSERT INTO ta69637_1(y) VALUES(new.z+1);
END;
INSERT INTO ta69637_2 VALUES(123);
SELECT * FROM ta69637_1;
}
} {1 124}
do_test autoinc-a69637.2 {
db eval {
CREATE VIEW va69637_2 AS SELECT * FROM ta69637_2;
CREATE TRIGGER ra69637_2 INSTEAD OF INSERT ON va69637_2 BEGIN
INSERT INTO ta69637_1(y) VALUES(new.z+10000);
END;
INSERT INTO va69637_2 VALUES(123);
SELECT * FROM ta69637_1;
}
} {1 124 2 10123}
}
# 2016-10-03 ticket https://www.sqlite.org/src/tktview/7b3328086a5c1
# Make sure autoincrement plays nicely with the xfer optimization
#
do_execsql_test autoinc-10.1 {
DELETE FROM sqlite_sequence;
CREATE TABLE t10a(a INTEGER PRIMARY KEY AUTOINCREMENT, b UNIQUE);
INSERT INTO t10a VALUES(888,9999);
CREATE TABLE t10b(x INTEGER PRIMARY KEY AUTOINCREMENT, y UNIQUE);
INSERT INTO t10b SELECT * FROM t10a;
SELECT * FROM sqlite_sequence;
} {t10a 888 t10b 888}
# 2018-04-21 autoincrement does not cause problems for upsert
#
do_execsql_test autoinc-11.1 {
CREATE TABLE t11(a INTEGER PRIMARY KEY AUTOINCREMENT,b UNIQUE);
INSERT INTO t11(a,b) VALUES(2,3),(5,6),(4,3),(1,2)
ON CONFLICT(b) DO UPDATE SET a=a+1000;
SELECT seq FROM sqlite_sequence WHERE name='t11';
} {5}
# 2018-05-23 ticket d8dc2b3a58cd5dc2918a1d4acbba4676a23ada4c
# Does not crash if the sqlite_sequence table schema is missing
# or corrupt.
#
do_test autoinc-12.1 {
db close
forcedelete test.db
sqlite3 db test.db
sqlite3_db_config db DEFENSIVE 0
db eval {
CREATE TABLE fake_sequence(name TEXT PRIMARY KEY,seq) WITHOUT ROWID;
PRAGMA writable_schema=on;
UPDATE sqlite_master SET
sql=replace(sql,'fake_','sqlite_'),
name='sqlite_sequence',
tbl_name='sqlite_sequence'
WHERE name='fake_sequence';
}
db close
sqlite3 db test.db
set res [catch {db eval {
CREATE TABLE t1(a INTEGER PRIMARY KEY AUTOINCREMENT, b TEXT);
INSERT INTO t1(b) VALUES('one');
}} msg]
lappend res $msg
} {1 {database disk image is malformed}}
do_test autoinc-12.2 {
db close
forcedelete test.db
sqlite3 db test.db
sqlite3_db_config db DEFENSIVE 0
db eval {
CREATE TABLE t1(a INTEGER PRIMARY KEY AUTOINCREMENT, b TEXT);
INSERT INTO t1(b) VALUES('one');
PRAGMA writable_schema=on;
UPDATE sqlite_master SET
sql=replace(sql,'sqlite_','x_'),
name='x_sequence',
tbl_name='x_sequence'
WHERE name='sqlite_sequence';
}
db close
sqlite3 db test.db
set res [catch {db eval {
INSERT INTO t1(b) VALUES('two');
}} msg]
lappend res $msg
} {1 {database disk image is malformed}}
ifcapable vtab {
set err "database disk image is malformed"
} else {
set err {malformed database schema (sqlite_sequence) - near "VIRTUAL": syntax error}
}
do_test autoinc-12.3 {
db close
forcedelete test.db
sqlite3 db test.db
sqlite3_db_config db DEFENSIVE 0
db eval {
CREATE TABLE t1(a INTEGER PRIMARY KEY AUTOINCREMENT, b TEXT);
INSERT INTO t1(b) VALUES('one');
PRAGMA writable_schema=on;
UPDATE sqlite_master SET
sql='CREATE VIRTUAL TABLE sqlite_sequence USING sqlite_dbpage'
WHERE name='sqlite_sequence';
}
db close
sqlite3 db test.db
set res [catch {db eval {
INSERT INTO t1(b) VALUES('two');
}} msg]
lappend res $msg
} [list 1 $err]
do_test autoinc-12.4 {
db close
forcedelete test.db
sqlite3 db test.db
db eval {
CREATE TABLE t1(a INTEGER PRIMARY KEY AUTOINCREMENT, b TEXT);
INSERT INTO t1(b) VALUES('one');
CREATE TABLE fake(name TEXT PRIMARY KEY,seq) WITHOUT ROWID;
}
set root1 [db one {SELECT rootpage FROM sqlite_master
WHERE name='sqlite_sequence'}]
set root2 [db one {SELECT rootpage FROM sqlite_master
WHERE name='fake'}]
sqlite3_db_config db DEFENSIVE 0
db eval {
PRAGMA writable_schema=on;
UPDATE sqlite_master SET rootpage=$root2
WHERE name='sqlite_sequence';
UPDATE sqlite_master SET rootpage=$root1
WHERE name='fake';
}
db close
sqlite3 db test.db
set res [catch {db eval {
INSERT INTO t1(b) VALUES('two');
}} msg]
lappend res $msg
} {1 {database disk image is malformed}}
breakpoint
do_test autoinc-12.5 {
db close
forcedelete test.db
sqlite3 db test.db
sqlite3_db_config db DEFENSIVE 0
db eval {
CREATE TABLE t1(a INTEGER PRIMARY KEY AUTOINCREMENT, b TEXT);
INSERT INTO t1(b) VALUES('one');
PRAGMA writable_schema=on;
UPDATE sqlite_master SET
sql='CREATE TABLE sqlite_sequence(x)'
WHERE name='sqlite_sequence';
}
db close
sqlite3 db test.db
set res [catch {db eval {
INSERT INTO t1(b) VALUES('two');
}} msg]
lappend res $msg
} {1 {database disk image is malformed}}
do_test autoinc-12.6 {
db close
forcedelete test.db
sqlite3 db test.db
sqlite3_db_config db DEFENSIVE 0
db eval {
CREATE TABLE t1(a INTEGER PRIMARY KEY AUTOINCREMENT, b TEXT);
INSERT INTO t1(b) VALUES('one');
PRAGMA writable_schema=on;
UPDATE sqlite_master SET
sql='CREATE TABLE sqlite_sequence(x,y INTEGER PRIMARY KEY)'
WHERE name='sqlite_sequence';
}
db close
sqlite3 db test.db
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}
do_test autoinc-12.7 {
db close
forcedelete test.db
sqlite3 db test.db
sqlite3_db_config db DEFENSIVE 0
db eval {
CREATE TABLE t1(a INTEGER PRIMARY KEY AUTOINCREMENT, b TEXT);
INSERT INTO t1(b) VALUES('one');
PRAGMA writable_schema=on;
UPDATE sqlite_master SET
sql='CREATE TABLE sqlite_sequence(y INTEGER PRIMARY KEY,x)'
WHERE name='sqlite_sequence';
}
db close
sqlite3 db test.db
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
View Git Blame Copy Permalink