aboutsummaryrefslogtreecommitdiff
path: root/test
diff options
context:
space:
mode:
Diffstat (limited to 'test')
-rw-r--r--test/capi3d.test26
-rw-r--r--test/multiplex2.test59
-rw-r--r--test/multiplex3.test2
-rw-r--r--test/selectB.test71
-rw-r--r--test/tkt-3a77c9714e.test68
-rw-r--r--test/tkt-7bbfb7d442.test154
-rw-r--r--test/walpersist.test22
7 files changed, 331 insertions, 71 deletions
diff --git a/test/capi3d.test b/test/capi3d.test
index 49e64476e..746ec20b2 100644
--- a/test/capi3d.test
+++ b/test/capi3d.test
@@ -11,7 +11,7 @@
# This file implements regression tests for SQLite library.
#
# This file is devoted to testing the sqlite3_next_stmt and
-# sqlite3_stmt_readonly interfaces.
+# sqlite3_stmt_readonly and sqlite3_stmt_busy interfaces.
#
# $Id: capi3d.test,v 1.2 2008/07/14 15:11:20 drh Exp $
#
@@ -112,5 +112,29 @@ do_test capi3-2.99 {
sqlite3_stmt_readonly 0
} 1
+# Tests for sqlite3_stmt_busy
+#
+do_test capi3d-3.1 {
+ db eval {INSERT INTO t1 VALUES(6); INSERT INTO t1 VALUES(7);}
+ set STMT [sqlite3_prepare db {SELECT * FROM t1} -1 TAIL]
+ sqlite3_stmt_busy $STMT
+} {0}
+do_test capi3d-3.2 {
+ sqlite3_step $STMT
+ sqlite3_stmt_busy $STMT
+} {1}
+do_test capi3d-3.3 {
+ sqlite3_step $STMT
+ sqlite3_stmt_busy $STMT
+} {1}
+do_test capi3d-3.4 {
+ sqlite3_reset $STMT
+ sqlite3_stmt_busy $STMT
+} {0}
+
+do_test capi3d-3.99 {
+ sqlite3_finalize $STMT
+ sqlite3_stmt_busy 0
+} {0}
finish_test
diff --git a/test/multiplex2.test b/test/multiplex2.test
index bf5791fe8..bdfc05b82 100644
--- a/test/multiplex2.test
+++ b/test/multiplex2.test
@@ -14,12 +14,9 @@ set testdir [file dirname $argv0]
source $testdir/tester.tcl
source $testdir/malloc_common.tcl
source $testdir/lock_common.tcl
-set testprefix multiplex2
-db close
-do_multiclient_test tn {
- foreach f [glob -nocomplain test.*] { forcedelete $f }
+do_multiclient_test tn {
code1 { catch { sqlite3_multiplex_initialize "" 0 } }
code2 { catch { sqlite3_multiplex_initialize "" 0 } }
@@ -47,10 +44,10 @@ do_multiclient_test tn {
SELECT count(*) FROM t1;
}
- do_test 1.$tn.1 { sql1 { SELECT count(*) FROM t1 } } 512
- do_test 1.$tn.2 { sql2 { SELECT count(*) FROM t1 } } 512
+ do_test multiplex-1.$tn.1 { sql1 { SELECT count(*) FROM t1 } } 512
+ do_test multiplex-1.$tn.2 { sql2 { SELECT count(*) FROM t1 } } 512
sql2 { DELETE FROM t1 ; VACUUM }
- do_test 1.$tn.3 { sql1 { SELECT count(*) FROM t1 } } 0
+ do_test multiplex-1.$tn.3 { sql1 { SELECT count(*) FROM t1 } } 0
sql1 {
INSERT INTO t1 VALUES(randomblob(10), randomblob(4000)); -- 1
@@ -66,54 +63,8 @@ do_multiclient_test tn {
SELECT count(*) FROM t1;
}
- do_test 1.$tn.4 { sql2 { SELECT count(*) FROM t1 } } 512
-}
-
-catch {db close}
-foreach f [glob -nocomplain test.*] { forcedelete $f }
-
-ifcapable 8_3_names {
- sqlite3 db test.db -vfs multiplex
- sqlite3_multiplex_control db main chunk_size [expr 256*1024]
-
- # Insert 512 * 256K (128MB) of data. If each row is around 4K, this means
- # we need 32768 rows.
- do_catchsql_test 2.1 {
- CREATE TABLE t1(a, b);
- INSERT INTO t1 VALUES(randomblob(10), randomblob(4000)); -- 1
- INSERT INTO t1 SELECT randomblob(10), randomblob(4000) FROM t1; -- 2
- INSERT INTO t1 SELECT randomblob(10), randomblob(4000) FROM t1; -- 4
- INSERT INTO t1 SELECT randomblob(10), randomblob(4000) FROM t1; -- 8
- INSERT INTO t1 SELECT randomblob(10), randomblob(4000) FROM t1; -- 16
- INSERT INTO t1 SELECT randomblob(10), randomblob(4000) FROM t1; -- 32
- INSERT INTO t1 SELECT randomblob(10), randomblob(4000) FROM t1; -- 64
- INSERT INTO t1 SELECT randomblob(10), randomblob(4000) FROM t1; -- 128
- INSERT INTO t1 SELECT randomblob(10), randomblob(4000) FROM t1; -- 256
- INSERT INTO t1 SELECT randomblob(10), randomblob(4000) FROM t1; -- 512
-
- INSERT INTO t1 SELECT randomblob(10), randomblob(4000) FROM t1; -- 1K
- INSERT INTO t1 SELECT randomblob(10), randomblob(4000) FROM t1; -- 2K
- INSERT INTO t1 SELECT randomblob(10), randomblob(4000) FROM t1; -- 4K
- INSERT INTO t1 SELECT randomblob(10), randomblob(4000) FROM t1; -- 8K
- INSERT INTO t1 SELECT randomblob(10), randomblob(4000) FROM t1; -- 16K
- INSERT INTO t1 SELECT randomblob(10), randomblob(4000) FROM t1; -- 32K
-
- } {1 {database or disk is full}}
-
- do_execsql_test 2.2 {
- UPDATE t1 SET a=randomblob(9), b=randomblob(3900);
- PRAGMA integrity_check;
- } ok
-
- db close
- sqlite3 db test.db -vfs multiplex
- sqlite3_multiplex_control db main chunk_size [expr 256*1024]
-
- do_execsql_test 2.3 {
- PRAGMA integrity_check;
- } ok
+ do_test multiplex-1.$tn.4 { sql2 { SELECT count(*) FROM t1 } } 512
}
-catch { db close }
catch { sqlite3_multiplex_shutdown }
finish_test
diff --git a/test/multiplex3.test b/test/multiplex3.test
index bdb264fde..0f52d763b 100644
--- a/test/multiplex3.test
+++ b/test/multiplex3.test
@@ -21,7 +21,7 @@ set ::testprefix multiplex3
ifcapable !8_3_names {
puts -nonewline "SQLite compiled without SQLITE_ENABLE_8_3_NAMES. "
- puts "Skipping tests zipvfsD-*."
+ puts "Skipping tests multiplex3-*."
finish_test
return
}
diff --git a/test/selectB.test b/test/selectB.test
index b9d979acb..05ec9c6bd 100644
--- a/test/selectB.test
+++ b/test/selectB.test
@@ -194,19 +194,28 @@ do_test selectB-3.0 {
}
} {}
-for {set ii 3} {$ii <= 4} {incr ii} {
+for {set ii 3} {$ii <= 6} {incr ii} {
- if {$ii == 4} {
- do_test selectB-4.0 {
- execsql {
- CREATE INDEX i1 ON t1(a);
- CREATE INDEX i2 ON t1(b);
- CREATE INDEX i3 ON t1(c);
- CREATE INDEX i4 ON t2(d);
- CREATE INDEX i5 ON t2(e);
- CREATE INDEX i6 ON t2(f);
- }
- } {}
+ switch $ii {
+ 4 {
+ optimization_control db query-flattener off
+ }
+ 5 {
+ optimization_control db query-flattener on
+ do_test selectB-5.0 {
+ execsql {
+ CREATE INDEX i1 ON t1(a);
+ CREATE INDEX i2 ON t1(b);
+ CREATE INDEX i3 ON t1(c);
+ CREATE INDEX i4 ON t2(d);
+ CREATE INDEX i5 ON t2(e);
+ CREATE INDEX i6 ON t2(f);
+ }
+ } {}
+ }
+ 6 {
+ optimization_control db query-flattener off
+ }
}
do_test selectB-$ii.1 {
@@ -371,11 +380,47 @@ for {set ii 3} {$ii <= 4} {incr ii} {
}
} {2 4 6 3 6 9 8 10 12 12 15 18 14 16 18 21 24 27}
- do_test selectB-$ii.21 {
+ do_test selectB-$ii.22 {
execsql {
SELECT * FROM (SELECT 345 UNION ALL SELECT d FROM t2) ORDER BY 1;
}
} {3 12 21 345}
+
+ do_test selectB-$ii.23 {
+ execsql {
+ SELECT x, y FROM (
+ SELECT a AS x, b AS y FROM t1
+ UNION ALL
+ SELECT a*10 + 0.1, f*10 + 0.1 FROM t1 JOIN t2 ON (c=d)
+ UNION ALL
+ SELECT a*100, b*100 FROM t1
+ ) ORDER BY 1;
+ }
+ } {2 4 8 10 14 16 80.1 180.1 200 400 800 1000 1400 1600}
+
+ do_test selectB-$ii.24 {
+ execsql {
+ SELECT x, y FROM (
+ SELECT a AS x, b AS y FROM t1
+ UNION ALL
+ SELECT a*10 + 0.1, f*10 + 0.1 FROM t1 LEFT JOIN t2 ON (c=d)
+ UNION ALL
+ SELECT a*100, b*100 FROM t1
+ ) ORDER BY 1;
+ }
+ } {2 4 8 10 14 16 20.1 {} 80.1 180.1 140.1 {} 200 400 800 1000 1400 1600}
+
+ do_test selectB-$ii.25 {
+ execsql {
+ SELECT x+y FROM (
+ SELECT a AS x, b AS y FROM t1
+ UNION ALL
+ SELECT a*10 + 0.1, f*10 + 0.1 FROM t1 LEFT JOIN t2 ON (c=d)
+ UNION ALL
+ SELECT a*100, b*100 FROM t1
+ ) WHERE y+x NOT NULL ORDER BY 1;
+ }
+ } {6 18 30 260.2 600 1800 3000}
}
finish_test
diff --git a/test/tkt-3a77c9714e.test b/test/tkt-3a77c9714e.test
new file mode 100644
index 000000000..d77ee42bb
--- /dev/null
+++ b/test/tkt-3a77c9714e.test
@@ -0,0 +1,68 @@
+# 2011 December 06
+#
+# 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.
+#
+# This file implements tests to verify that ticket [3a77c9714e] has been
+# fixed.
+
+set testdir [file dirname $argv0]
+source $testdir/tester.tcl
+
+set testprefix "tkt-3a77c9714e"
+
+do_execsql_test 1.1 {
+ CREATE TABLE t1(t1_id INTEGER PRIMARY KEY, t1_title TEXT);
+ CREATE TABLE t2(t2_id INTEGER PRIMARY KEY, t2_title TEXT);
+ CREATE TABLE t3(t3_id INTEGER PRIMARY KEY, t3_title TEXT);
+
+ INSERT INTO t1 (t1_id, t1_title) VALUES (888, 'ABCDEF');
+ INSERT INTO t2 (t2_id, t2_title) VALUES (999, 'ABCDEF');
+ INSERT INTO t3 (t3_id, t3_title) VALUES (999, 'ABCDEF');
+}
+
+do_execsql_test 1.2 {
+ SELECT t1_title, t2_title
+ FROM t1 LEFT JOIN t2
+ WHERE
+ t2_id = (SELECT t3_id FROM
+ ( SELECT t3_id FROM t3 WHERE t3_title=t1_title LIMIT 500 )
+ )
+} {ABCDEF ABCDEF}
+
+do_execsql_test 2.1 {
+ CREATE TABLE [Beginnings] (
+ [Id] INTEGER PRIMARY KEY AUTOINCREMENT,[Title] TEXT, [EndingId] INTEGER
+ );
+ CREATE TABLE [Endings] (Id INT,Title TEXT,EndingId INT);
+ INSERT INTO Beginnings (Id, Title, EndingId) VALUES (1, 'FACTOR', 18);
+ INSERT INTO Beginnings (Id, Title, EndingId) VALUES (2, 'SWIMM', 18);
+ INSERT INTO Endings (Id, Title, EndingId) VALUES (1, 'ING', 18);
+}
+
+do_execsql_test 2.2 {
+ SELECT
+ SrcWord, Beginnings.Title
+ FROM
+ (SELECT 'FACTORING' AS SrcWord UNION SELECT 'SWIMMING' AS SrcWord )
+ LEFT JOIN
+ Beginnings
+ WHERE Beginnings.Id= (
+ SELECT BeginningId FROM (
+ SELECT SrcWord, B.Id as BeginningId, B.Title || E.Title As Connected
+ FROM Beginnings B LEFT JOIN Endings E ON B.EndingId=E.EndingId
+ WHERE Connected=SrcWord LIMIT 1
+ )
+ )
+} {FACTORING FACTOR SWIMMING SWIMM}
+
+
+finish_test
+
diff --git a/test/tkt-7bbfb7d442.test b/test/tkt-7bbfb7d442.test
new file mode 100644
index 000000000..e560a0d4d
--- /dev/null
+++ b/test/tkt-7bbfb7d442.test
@@ -0,0 +1,154 @@
+# 2011 December 9
+#
+# 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.
+#
+# This file implements tests to verify that ticket [7bbfb7d442] has been
+# fixed.
+#
+
+set testdir [file dirname $argv0]
+source $testdir/tester.tcl
+set testprefix tkt-7bbfb7d442
+
+do_execsql_test 1.1 {
+ CREATE TABLE t1(a, b);
+ INSERT INTO t1 VALUES(1, 'one');
+ INSERT INTO t1 VALUES(2, 'two');
+ INSERT INTO t1 VALUES(3, 'three');
+
+ CREATE TABLE t2(c, d);
+ INSERT INTO t2 VALUES('one', 'I');
+ INSERT INTO t2 VALUES('two', 'II');
+ INSERT INTO t2 VALUES('three', 'III');
+
+ CREATE TABLE t3(t3_a PRIMARY KEY, t3_d);
+ CREATE TRIGGER t3t AFTER INSERT ON t3 WHEN new.t3_d IS NULL BEGIN
+ UPDATE t3 SET t3_d = (
+ SELECT d FROM
+ (SELECT * FROM t2 WHERE (new.t3_a%2)=(rowid%2) LIMIT 10),
+ (SELECT * FROM t1 WHERE (new.t3_a%2)=(rowid%2) LIMIT 10)
+ WHERE a = new.t3_a AND b = c
+ ) WHERE t3_a = new.t3_a;
+ END;
+}
+
+do_execsql_test 1.2 {
+ INSERT INTO t3(t3_a) VALUES(1);
+ INSERT INTO t3(t3_a) VALUES(2);
+ INSERT INTO t3(t3_a) VALUES(3);
+ SELECT * FROM t3;
+} {1 I 2 II 3 III}
+
+do_execsql_test 1.3 { DELETE FROM t3 }
+
+do_execsql_test 1.4 {
+ INSERT INTO t3(t3_a) SELECT 1 UNION SELECT 2 UNION SELECT 3;
+ SELECT * FROM t3;
+} {1 I 2 II 3 III}
+
+
+
+#-------------------------------------------------------------------------
+# The following test case - 2.* - is from the original bug report as
+# posted to the mailing list.
+#
+do_execsql_test 2.1 {
+ CREATE TABLE InventoryControl (
+ InventoryControlId INTEGER PRIMARY KEY AUTOINCREMENT,
+ SKU INTEGER NOT NULL,
+ Variant INTEGER NOT NULL DEFAULT 0,
+ ControlDate DATE NOT NULL,
+ ControlState INTEGER NOT NULL DEFAULT -1,
+ DeliveredQty VARCHAR(30)
+ );
+
+ CREATE TRIGGER TGR_InventoryControl_AfterInsert
+ AFTER INSERT ON InventoryControl
+ FOR EACH ROW WHEN NEW.ControlState=-1 BEGIN
+
+ INSERT OR REPLACE INTO InventoryControl(
+ InventoryControlId,SKU,Variant,ControlDate,ControlState,DeliveredQty
+ ) SELECT
+ T1.InventoryControlId AS InventoryControlId,
+ T1.SKU AS SKU,
+ T1.Variant AS Variant,
+ T1.ControlDate AS ControlDate,
+ 1 AS ControlState,
+ COALESCE(T2.DeliveredQty,0) AS DeliveredQty
+ FROM (
+ SELECT
+ NEW.InventoryControlId AS InventoryControlId,
+ II.SKU AS SKU,
+ II.Variant AS Variant,
+ COALESCE(LastClosedIC.ControlDate,NEW.ControlDate) AS ControlDate
+ FROM
+ InventoryItem II
+ LEFT JOIN
+ InventoryControl LastClosedIC
+ ON LastClosedIC.InventoryControlId IN ( SELECT 99999 )
+ WHERE
+ II.SKU=NEW.SKU AND
+ II.Variant=NEW.Variant
+ ) T1
+ LEFT JOIN (
+ SELECT
+ TD.SKU AS SKU,
+ TD.Variant AS Variant,
+ 10 AS DeliveredQty
+ FROM
+ TransactionDetail TD
+ WHERE
+ TD.SKU=NEW.SKU AND
+ TD.Variant=NEW.Variant
+ ) T2
+ ON T2.SKU=T1.SKU AND
+ T2.Variant=T1.Variant;
+ END;
+
+ CREATE TABLE InventoryItem (
+ SKU INTEGER NOT NULL,
+ Variant INTEGER NOT NULL DEFAULT 0,
+ DeptCode INTEGER NOT NULL,
+ GroupCode INTEGER NOT NULL,
+ ItemDescription VARCHAR(120) NOT NULL,
+ PRIMARY KEY(SKU, Variant)
+ );
+
+ INSERT INTO InventoryItem VALUES(220,0,1,170,'Scoth Tampon Recurer');
+ INSERT INTO InventoryItem VALUES(31,0,1,110,'Fromage');
+
+ CREATE TABLE TransactionDetail (
+ TransactionId INTEGER NOT NULL,
+ SKU INTEGER NOT NULL,
+ Variant INTEGER NOT NULL DEFAULT 0,
+ PRIMARY KEY(TransactionId, SKU, Variant)
+ );
+ INSERT INTO TransactionDetail(TransactionId, SKU, Variant) VALUES(44, 31, 0);
+
+
+ INSERT INTO InventoryControl(SKU, Variant, ControlDate) SELECT
+ II.SKU AS SKU, II.Variant AS Variant, '2011-08-30' AS ControlDate
+ FROM InventoryItem II;
+}
+
+do_execsql_test 2.2 {
+ SELECT SKU, DeliveredQty FROM InventoryControl WHERE SKU=31
+} {31 10}
+
+do_execsql_test 2.3 {
+ SELECT CASE WHEN DeliveredQty=10 THEN "TEST PASSED!" ELSE "TEST FAILED!" END
+ FROM InventoryControl WHERE SKU=31;
+} {{TEST PASSED!}}
+
+
+finish_test
+
+
diff --git a/test/walpersist.test b/test/walpersist.test
index 175dcbf89..bf65bd1e1 100644
--- a/test/walpersist.test
+++ b/test/walpersist.test
@@ -67,7 +67,25 @@ do_test walpersist-1.11 {
list [file exists test.db] [file exists test.db-wal] [file exists test.db-shm]
} {1 1 1}
-
-
+# Make sure the journal_size_limit works to limit the size of the
+# persisted wal file.
+forcedelete test.db test.db-shm test.db-wal
+do_test walpersist-2.1 {
+ sqlite3 db test.db
+ db eval {
+ PRAGMA journal_mode=WAL;
+ PRAGMA wal_autocheckpoint=OFF;
+ PRAGMA journal_size_limit=12000;
+ CREATE TABLE t1(x);
+ INSERT INTO t1 VALUES(randomblob(50000));
+ UPDATE t1 SET x=randomblob(50000);
+ }
+ expr {[file size test.db-wal]>100000}
+} {1}
+do_test walpersist-2.2 {
+ file_control_persist_wal db 1
+ db close
+ file size test.db-wal
+} {12000}
finish_test
generated by cgit v1.2.3 (git 2.25.1) at 2025-07-21 12:27:31 +0000