Index: ext/ota/README.txt ================================================================== --- ext/ota/README.txt +++ ext/ota/README.txt @@ -3,25 +3,12 @@ User documentation is in sqlite3ota.h. SQLite Hacks ------------ -1) PRAGMA ota_mode: - - This is a new flag pragma. If the flag is set: - - * INSERT/DELETE/UPDATE commands are prevented from updating any but the main - b-tree for each table (the PK index for WITHOUT ROWID tables or the - rowid b-tree for others). - - * The above statements do not check UNIQUE constraints - except those enforced - by the main b-tree. - - * All non-temporary triggers are disabled. - - -2) PRAGMA pager_ota_mode=1: + +1) PRAGMA pager_ota_mode=1: This pragma sets a flag on the pager associated with the main database only. In a zipvfs system, this pragma is intercepted by zipvfs and the flag is set on the lower level pager only. @@ -49,27 +36,20 @@ Other clients see a rollback-mode database on which the pager_ota_mode client is holding a SHARED lock. There are no locks to arbitrate between multiple pager_ota_mode connections. If two or more such connections attempt to write simultaneously, the results are undefined. -3) PRAGMA pager_ota_mode=2: +2) PRAGMA pager_ota_mode=2: The pager_ota_mode pragma may also be set to 2 if the main database is open in WAL mode. This prevents SQLite from checkpointing the wal file as part of sqlite3_close(). The effects of setting pager_ota_mode=2 if the db is not in WAL mode are undefined. -4) sqlite3_index_writer() - - This new API function is used to create VMs that can insert or delete entries - from individual index b-trees within the database. The VMs apply affinities - and check that UNIQUE constraints are not violated before updating index - b-trees. - -5) sqlite3_ckpt_open/step/close() +3) sqlite3_ckpt_open/step/close() API for performing (and resuming) incremental checkpoints. The OTA extension Index: ext/ota/ota1.test ================================================================== --- ext/ota/ota1.test +++ ext/ota/ota1.test @@ -96,373 +96,388 @@ # Same as [step_ota], except using a URI to open the target db. # proc step_ota_uri {target ota} { while 1 { - sqlite3ota ota file:$target?xyz=123 $ota + sqlite3ota ota file:$target?xyz=&abc=123 $ota set rc [ota step] ota close if {$rc != "SQLITE_OK"} break } set rc } -foreach {tn2 cmd} {1 run_ota 2 step_ota 3 step_ota_uri} { - foreach {tn schema} { - 1 { - CREATE TABLE t1(a INTEGER PRIMARY KEY, b, c); - } - 2 { - CREATE TABLE t1(a INTEGER PRIMARY KEY, b, c); - CREATE INDEX i1 ON t1(b); - } - 3 { - CREATE TABLE t1(a PRIMARY KEY, b, c) WITHOUT ROWID; - } - 4 { - CREATE TABLE t1(a PRIMARY KEY, b, c) WITHOUT ROWID; - CREATE INDEX i1 ON t1(b); - } - 5 { - CREATE TABLE t1(a, b, c, PRIMARY KEY(a, c)) WITHOUT ROWID; - CREATE INDEX i1 ON t1(b); - } - 6 { - CREATE TABLE t1(a, b, c, PRIMARY KEY(c)) WITHOUT ROWID; - CREATE INDEX i1 ON t1(b, a); - } - 7 { - CREATE TABLE t1(a INTEGER PRIMARY KEY, b, c); - CREATE INDEX i1 ON t1(b, c); - CREATE INDEX i2 ON t1(c, b); - CREATE INDEX i3 ON t1(a, b, c, a, b, c); - } - - 8 { - CREATE TABLE t1(a PRIMARY KEY, b, c); - CREATE INDEX i1 ON t1(b, c); - CREATE INDEX i2 ON t1(c, b); - CREATE INDEX i3 ON t1(a, b, c, a, b, c); - } - - 9 { - CREATE TABLE t1(a, b, c, PRIMARY KEY(a, c)); - CREATE INDEX i1 ON t1(b); - } - - 10 { - CREATE TABLE t1(a INTEGER PRIMARY KEY, b, c); - CREATE INDEX i1 ON t1(b DESC); - } - - 11 { - CREATE TABLE t1(a INTEGER PRIMARY KEY, b, c); - CREATE INDEX i1 ON t1(b DESC, a ASC, c DESC); - } - - 12 { - CREATE TABLE t1(a INT PRIMARY KEY DESC, b, c) WITHOUT ROWID; - } - - 13 { - CREATE TABLE t1(a INT, b, c, PRIMARY KEY(a DESC)) WITHOUT ROWID; - } - - 14 { - CREATE TABLE t1(a, b, c, PRIMARY KEY(a DESC, c)) WITHOUT ROWID; - CREATE INDEX i1 ON t1(b); - } - - 15 { - CREATE TABLE t1(a, b, c, PRIMARY KEY(a, c DESC)) WITHOUT ROWID; - CREATE INDEX i1 ON t1(b); - } - - 16 { - CREATE TABLE t1(a, b, c, PRIMARY KEY(c DESC, a)) WITHOUT ROWID; - CREATE INDEX i1 ON t1(b DESC, c, a); - } - } { - reset_db - execsql $schema - - do_test 1.$tn2.$tn.1 { - create_ota1 ota.db - $cmd test.db ota.db - } {SQLITE_DONE} - - do_execsql_test 1.$tn2.$tn.2 { SELECT * FROM t1 ORDER BY a ASC } { - 1 2 3 - 2 two three - 3 {} 8.2 - } - do_execsql_test 1.$tn2.$tn.3 { SELECT * FROM t1 ORDER BY b ASC } { - 3 {} 8.2 - 1 2 3 - 2 two three - } - do_execsql_test 1.$tn2.$tn.4 { SELECT * FROM t1 ORDER BY c ASC } { - 1 2 3 - 3 {} 8.2 - 2 two three - } - - do_execsql_test 1.$tn2.$tn.5 { PRAGMA integrity_check } ok - } -} - -#------------------------------------------------------------------------- -# Check that an OTA cannot be applied to a table that has no PK. -# -# UPDATE: At one point OTA required that all tables featured either -# explicit IPK columns or were declared WITHOUT ROWID. This has been -# relaxed so that external PRIMARY KEYs on tables with automatic rowids -# are now allowed. -# -# UPDATE 2: Tables without any PRIMARY KEY declaration are now allowed. -# However the input table must feature an "ota_rowid" column. -# -reset_db -create_ota1 ota.db -do_execsql_test 2.1 { CREATE TABLE t1(a, b, c) } -do_test 2.2 { - sqlite3ota ota test.db ota.db - ota step -} {SQLITE_ERROR} -do_test 2.3 { - list [catch { ota close } msg] $msg -} {1 {SQLITE_ERROR - table data_t1 requires ota_rowid column}} -reset_db -do_execsql_test 2.4 { CREATE TABLE t1(a PRIMARY KEY, b, c) } -do_test 2.5 { - sqlite3ota ota test.db ota.db - ota step -} {SQLITE_OK} -do_test 2.6 { - list [catch { ota close } msg] $msg -} {0 SQLITE_OK} - -#------------------------------------------------------------------------- -# Check that if a UNIQUE constraint is violated the current and all -# subsequent [ota step] calls return SQLITE_CONSTRAINT. And that the OTA -# transaction is rolled back by the [ota close] that deletes the ota -# handle. -# -foreach {tn errcode errmsg schema} { - 1 SQLITE_CONSTRAINT "UNIQUE constraint failed: t1.a" { - CREATE TABLE t1(a INTEGER PRIMARY KEY, b, c); - INSERT INTO t1 VALUES(3, 2, 1); - } - - 2 SQLITE_CONSTRAINT "UNIQUE constraint failed: t1.c" { - CREATE TABLE t1(a INTEGER PRIMARY KEY, b, c UNIQUE); - INSERT INTO t1 VALUES(4, 2, 'three'); - } - - 3 SQLITE_CONSTRAINT "UNIQUE constraint failed: t1.a" { - CREATE TABLE t1(a PRIMARY KEY, b, c); - INSERT INTO t1 VALUES(3, 2, 1); - } - - 4 SQLITE_CONSTRAINT "UNIQUE constraint failed: t1.c" { - CREATE TABLE t1(a PRIMARY KEY, b, c UNIQUE); - INSERT INTO t1 VALUES(4, 2, 'three'); - } - -} { - reset_db - execsql $schema - set cksum [dbcksum db main] - - do_test 3.$tn.1 { - create_ota1 ota.db - sqlite3ota ota test.db ota.db - while {[set res [ota step]]=="SQLITE_OK"} {} - set res - } $errcode - - do_test 3.$tn.2 { ota step } $errcode - - do_test 3.$tn.3 { - list [catch { ota close } msg] $msg - } [list 1 "$errcode - $errmsg"] - - do_test 3.$tn.4 { dbcksum db main } $cksum -} - -#------------------------------------------------------------------------- -# -foreach {tn2 cmd} {1 run_ota 2 step_ota} { - foreach {tn schema} { - 1 { - CREATE TABLE t1(a INTEGER PRIMARY KEY, b, c); - } - 2 { - CREATE TABLE t1(a INTEGER PRIMARY KEY, b, c); - CREATE INDEX i1 ON t1(b); - } - 3 { - CREATE TABLE t1(a INTEGER PRIMARY KEY, b, c); - CREATE INDEX i1 ON t1(b); - CREATE INDEX i2 ON t1(c, b); - CREATE INDEX i3 ON t1(c, b, c); - } - 4 { - CREATE TABLE t1(a INT PRIMARY KEY, b, c) WITHOUT ROWID; - CREATE INDEX i1 ON t1(b); - CREATE INDEX i2 ON t1(c, b); - CREATE INDEX i3 ON t1(c, b, c); - } - 5 { - CREATE TABLE t1(a INT PRIMARY KEY, b, c); - CREATE INDEX i1 ON t1(b); - CREATE INDEX i2 ON t1(c, b); - CREATE INDEX i3 ON t1(c, b, c); - } - - 6 { - CREATE TABLE t1(a INT PRIMARY KEY DESC, b, c); - CREATE INDEX i1 ON t1(b DESC); - CREATE INDEX i2 ON t1(c, b); - CREATE INDEX i3 ON t1(c DESC, b, c); - } - 7 { - CREATE TABLE t1(a INT PRIMARY KEY DESC, b, c) WITHOUT ROWID; - CREATE INDEX i1 ON t1(b); - CREATE INDEX i2 ON t1(c, b); - CREATE INDEX i3 ON t1(c, b, c); - } - } { - reset_db - execsql $schema - execsql { - INSERT INTO t1 VALUES(2, 'hello', 'world'); - INSERT INTO t1 VALUES(4, 'hello', 'planet'); - INSERT INTO t1 VALUES(6, 'hello', 'xyz'); - } - - do_test 4.$tn2.$tn.1 { - create_ota4 ota.db - $cmd test.db ota.db - } {SQLITE_DONE} - - do_execsql_test 4.$tn2.$tn.2 { - SELECT * FROM t1 ORDER BY a ASC; - } { - 1 2 3 - 3 8 9 - 6 hello xyz - } - - do_execsql_test 4.$tn2.$tn.3 { PRAGMA integrity_check } ok - } -} - -#------------------------------------------------------------------------- -# -foreach {tn2 cmd} {1 run_ota 2 step_ota} { - foreach {tn schema} { - 1 { - CREATE TABLE t1(a INTEGER PRIMARY KEY, b, c, d); - } - 2 { - CREATE TABLE t1(a INTEGER PRIMARY KEY, b, c, d); - CREATE INDEX i1 ON t1(d); - CREATE INDEX i2 ON t1(d, c); - CREATE INDEX i3 ON t1(d, c, b); - CREATE INDEX i4 ON t1(b); - CREATE INDEX i5 ON t1(c); - CREATE INDEX i6 ON t1(c, b); - } - 3 { - CREATE TABLE t1(a PRIMARY KEY, b, c, d) WITHOUT ROWID; - CREATE INDEX i1 ON t1(d); - CREATE INDEX i2 ON t1(d, c); - CREATE INDEX i3 ON t1(d, c, b); - CREATE INDEX i4 ON t1(b); - CREATE INDEX i5 ON t1(c); - CREATE INDEX i6 ON t1(c, b); - } - 4 { - CREATE TABLE t1(a PRIMARY KEY, b, c, d); - CREATE INDEX i1 ON t1(d); - CREATE INDEX i2 ON t1(d, c); - CREATE INDEX i3 ON t1(d, c, b); - CREATE INDEX i4 ON t1(b); - CREATE INDEX i5 ON t1(c); - CREATE INDEX i6 ON t1(c, b); - } - } { - reset_db - execsql $schema - execsql { - INSERT INTO t1 VALUES(1, 2, 3, 4); - INSERT INTO t1 VALUES(2, 5, 6, 7); - INSERT INTO t1 VALUES(3, 8, 9, 10); - } - - do_test 5.$tn2.$tn.1 { - create_ota5 ota.db - $cmd test.db ota.db - } {SQLITE_DONE} - - do_execsql_test 5.$tn2.$tn.2 { - SELECT * FROM t1 ORDER BY a ASC; - } { - 1 2 3 5 - 2 5 10 5 - 3 11 9 10 - } - - do_execsql_test 5.$tn2.$tn.3 { PRAGMA integrity_check } ok - } -} - -#------------------------------------------------------------------------- -# Test some error cases: -# -# * A virtual table with no ota_rowid column. -# * A no-PK table with no ota_rowid column. -# * A PK table with an ota_rowid column. -# -ifcapable fts3 { - foreach {tn schema error} { - 1 { - CREATE TABLE t1(a, b); - CREATE TABLE ota.data_t1(a, b, ota_control); - } {SQLITE_ERROR - table data_t1 requires ota_rowid column} - - 2 { - CREATE VIRTUAL TABLE t1 USING fts4(a, b); - CREATE TABLE ota.data_t1(a, b, ota_control); - } {SQLITE_ERROR - table data_t1 requires ota_rowid column} - - 3 { - CREATE TABLE t1(a PRIMARY KEY, b); - CREATE TABLE ota.data_t1(a, b, ota_rowid, ota_control); - } {SQLITE_ERROR - table data_t1 may not have ota_rowid column} - - 4 { - CREATE TABLE t1(a INTEGER PRIMARY KEY, b); - CREATE TABLE ota.data_t1(a, b, ota_rowid, ota_control); - } {SQLITE_ERROR - table data_t1 may not have ota_rowid column} - - 5 { - CREATE TABLE t1(a, b PRIMARY KEY) WITHOUT ROWID; - CREATE TABLE ota.data_t1(a, b, ota_rowid, ota_control); - } {SQLITE_ERROR - table data_t1 may not have ota_rowid column} - - } { - reset_db - forcedelete ota.db - execsql { ATTACH 'ota.db' AS ota } - execsql $schema - - do_test 6.$tn { - list [catch { run_ota test.db ota.db } msg] $msg - } [list 1 $error] - } +foreach {tn3 create_vfs destroy_vfs} { + 1 {} {} + 2 { + sqlite3ota_create_vfs -default myota "" + } { + sqlite3ota_destroy_vfs myota + } +} { + + eval $create_vfs + + foreach {tn2 cmd} {1 run_ota 2 step_ota 3 step_ota_uri} { + foreach {tn schema} { + 1 { + CREATE TABLE t1(a INTEGER PRIMARY KEY, b, c); + } + 2 { + CREATE TABLE t1(a INTEGER PRIMARY KEY, b, c); + CREATE INDEX i1 ON t1(b); + } + 3 { + CREATE TABLE t1(a PRIMARY KEY, b, c) WITHOUT ROWID; + } + 4 { + CREATE TABLE t1(a PRIMARY KEY, b, c) WITHOUT ROWID; + CREATE INDEX i1 ON t1(b); + } + 5 { + CREATE TABLE t1(a, b, c, PRIMARY KEY(a, c)) WITHOUT ROWID; + CREATE INDEX i1 ON t1(b); + } + 6 { + CREATE TABLE t1(a, b, c, PRIMARY KEY(c)) WITHOUT ROWID; + CREATE INDEX i1 ON t1(b, a); + } + 7 { + CREATE TABLE t1(a INTEGER PRIMARY KEY, b, c); + CREATE INDEX i1 ON t1(b, c); + CREATE INDEX i2 ON t1(c, b); + CREATE INDEX i3 ON t1(a, b, c, a, b, c); + } + + 8 { + CREATE TABLE t1(a PRIMARY KEY, b, c); + CREATE INDEX i1 ON t1(b, c); + CREATE INDEX i2 ON t1(c, b); + CREATE INDEX i3 ON t1(a, b, c, a, b, c); + } + + 9 { + CREATE TABLE t1(a, b, c, PRIMARY KEY(a, c)); + CREATE INDEX i1 ON t1(b); + } + + 10 { + CREATE TABLE t1(a INTEGER PRIMARY KEY, b, c); + CREATE INDEX i1 ON t1(b DESC); + } + + 11 { + CREATE TABLE t1(a INTEGER PRIMARY KEY, b, c); + CREATE INDEX i1 ON t1(b DESC, a ASC, c DESC); + } + + 12 { + CREATE TABLE t1(a INT PRIMARY KEY DESC, b, c) WITHOUT ROWID; + } + + 13 { + CREATE TABLE t1(a INT, b, c, PRIMARY KEY(a DESC)) WITHOUT ROWID; + } + + 14 { + CREATE TABLE t1(a, b, c, PRIMARY KEY(a DESC, c)) WITHOUT ROWID; + CREATE INDEX i1 ON t1(b); + } + + 15 { + CREATE TABLE t1(a, b, c, PRIMARY KEY(a, c DESC)) WITHOUT ROWID; + CREATE INDEX i1 ON t1(b); + } + + 16 { + CREATE TABLE t1(a, b, c, PRIMARY KEY(c DESC, a)) WITHOUT ROWID; + CREATE INDEX i1 ON t1(b DESC, c, a); + } + } { + reset_db + execsql $schema + + do_test $tn3.1.$tn2.$tn.1 { + create_ota1 ota.db + breakpoint + $cmd test.db ota.db + } {SQLITE_DONE} + + do_execsql_test $tn3.1.$tn2.$tn.2 { SELECT * FROM t1 ORDER BY a ASC } { + 1 2 3 + 2 two three + 3 {} 8.2 + } + do_execsql_test $tn3.1.$tn2.$tn.3 { SELECT * FROM t1 ORDER BY b ASC } { + 3 {} 8.2 + 1 2 3 + 2 two three + } + do_execsql_test $tn3.1.$tn2.$tn.4 { SELECT * FROM t1 ORDER BY c ASC } { + 1 2 3 + 3 {} 8.2 + 2 two three + } + + do_execsql_test $tn3.1.$tn2.$tn.5 { PRAGMA integrity_check } ok + } + } + + #------------------------------------------------------------------------- + # Check that an OTA cannot be applied to a table that has no PK. + # + # UPDATE: At one point OTA required that all tables featured either + # explicit IPK columns or were declared WITHOUT ROWID. This has been + # relaxed so that external PRIMARY KEYs on tables with automatic rowids + # are now allowed. + # + # UPDATE 2: Tables without any PRIMARY KEY declaration are now allowed. + # However the input table must feature an "ota_rowid" column. + # + reset_db + create_ota1 ota.db + do_execsql_test $tn3.2.1 { CREATE TABLE t1(a, b, c) } + do_test $tn3.2.2 { + sqlite3ota ota test.db ota.db + ota step + } {SQLITE_ERROR} + do_test $tn3.2.3 { + list [catch { ota close } msg] $msg + } {1 {SQLITE_ERROR - table data_t1 requires ota_rowid column}} + reset_db + do_execsql_test $tn3.2.4 { CREATE TABLE t1(a PRIMARY KEY, b, c) } + do_test $tn3.2.5 { + sqlite3ota ota test.db ota.db + ota step + } {SQLITE_OK} + do_test $tn3.2.6 { + list [catch { ota close } msg] $msg + } {0 SQLITE_OK} + + #------------------------------------------------------------------------- + # Check that if a UNIQUE constraint is violated the current and all + # subsequent [ota step] calls return SQLITE_CONSTRAINT. And that the OTA + # transaction is rolled back by the [ota close] that deletes the ota + # handle. + # + foreach {tn errcode errmsg schema} { + 1 SQLITE_CONSTRAINT "UNIQUE constraint failed: t1.a" { + CREATE TABLE t1(a INTEGER PRIMARY KEY, b, c); + INSERT INTO t1 VALUES(3, 2, 1); + } + + 2 SQLITE_CONSTRAINT "UNIQUE constraint failed: t1.c" { + CREATE TABLE t1(a INTEGER PRIMARY KEY, b, c UNIQUE); + INSERT INTO t1 VALUES(4, 2, 'three'); + } + + 3 SQLITE_CONSTRAINT "UNIQUE constraint failed: t1.a" { + CREATE TABLE t1(a PRIMARY KEY, b, c); + INSERT INTO t1 VALUES(3, 2, 1); + } + + 4 SQLITE_CONSTRAINT "UNIQUE constraint failed: t1.c" { + CREATE TABLE t1(a PRIMARY KEY, b, c UNIQUE); + INSERT INTO t1 VALUES(4, 2, 'three'); + } + + } { + reset_db + execsql $schema + set cksum [dbcksum db main] + + do_test $tn3.3.$tn.1 { + create_ota1 ota.db + sqlite3ota ota test.db ota.db + while {[set res [ota step]]=="SQLITE_OK"} {} + set res + } $errcode + + do_test $tn3.3.$tn.2 { ota step } $errcode + + do_test $tn3.3.$tn.3 { + list [catch { ota close } msg] $msg + } [list 1 "$errcode - $errmsg"] + + do_test $tn3.3.$tn.4 { dbcksum db main } $cksum + } + + #------------------------------------------------------------------------- + # + foreach {tn2 cmd} {1 run_ota 2 step_ota} { + foreach {tn schema} { + 1 { + CREATE TABLE t1(a INTEGER PRIMARY KEY, b, c); + } + 2 { + CREATE TABLE t1(a INTEGER PRIMARY KEY, b, c); + CREATE INDEX i1 ON t1(b); + } + 3 { + CREATE TABLE t1(a INTEGER PRIMARY KEY, b, c); + CREATE INDEX i1 ON t1(b); + CREATE INDEX i2 ON t1(c, b); + CREATE INDEX i3 ON t1(c, b, c); + } + 4 { + CREATE TABLE t1(a INT PRIMARY KEY, b, c) WITHOUT ROWID; + CREATE INDEX i1 ON t1(b); + CREATE INDEX i2 ON t1(c, b); + CREATE INDEX i3 ON t1(c, b, c); + } + 5 { + CREATE TABLE t1(a INT PRIMARY KEY, b, c); + CREATE INDEX i1 ON t1(b); + CREATE INDEX i2 ON t1(c, b); + CREATE INDEX i3 ON t1(c, b, c); + } + + 6 { + CREATE TABLE t1(a INT PRIMARY KEY DESC, b, c); + CREATE INDEX i1 ON t1(b DESC); + CREATE INDEX i2 ON t1(c, b); + CREATE INDEX i3 ON t1(c DESC, b, c); + } + 7 { + CREATE TABLE t1(a INT PRIMARY KEY DESC, b, c) WITHOUT ROWID; + CREATE INDEX i1 ON t1(b); + CREATE INDEX i2 ON t1(c, b); + CREATE INDEX i3 ON t1(c, b, c); + } + } { + reset_db + execsql $schema + execsql { + INSERT INTO t1 VALUES(2, 'hello', 'world'); + INSERT INTO t1 VALUES(4, 'hello', 'planet'); + INSERT INTO t1 VALUES(6, 'hello', 'xyz'); + } + + do_test $tn3.4.$tn2.$tn.1 { + create_ota4 ota.db + $cmd test.db ota.db + } {SQLITE_DONE} + + do_execsql_test $tn3.4.$tn2.$tn.2 { + SELECT * FROM t1 ORDER BY a ASC; + } { + 1 2 3 + 3 8 9 + 6 hello xyz + } + + do_execsql_test $tn3.4.$tn2.$tn.3 { PRAGMA integrity_check } ok + } + } + + #------------------------------------------------------------------------- + # + foreach {tn2 cmd} {1 run_ota 2 step_ota} { + foreach {tn schema} { + 1 { + CREATE TABLE t1(a INTEGER PRIMARY KEY, b, c, d); + } + 2 { + CREATE TABLE t1(a INTEGER PRIMARY KEY, b, c, d); + CREATE INDEX i1 ON t1(d); + CREATE INDEX i2 ON t1(d, c); + CREATE INDEX i3 ON t1(d, c, b); + CREATE INDEX i4 ON t1(b); + CREATE INDEX i5 ON t1(c); + CREATE INDEX i6 ON t1(c, b); + } + 3 { + CREATE TABLE t1(a PRIMARY KEY, b, c, d) WITHOUT ROWID; + CREATE INDEX i1 ON t1(d); + CREATE INDEX i2 ON t1(d, c); + CREATE INDEX i3 ON t1(d, c, b); + CREATE INDEX i4 ON t1(b); + CREATE INDEX i5 ON t1(c); + CREATE INDEX i6 ON t1(c, b); + } + 4 { + CREATE TABLE t1(a PRIMARY KEY, b, c, d); + CREATE INDEX i1 ON t1(d); + CREATE INDEX i2 ON t1(d, c); + CREATE INDEX i3 ON t1(d, c, b); + CREATE INDEX i4 ON t1(b); + CREATE INDEX i5 ON t1(c); + CREATE INDEX i6 ON t1(c, b); + } + } { + reset_db + execsql $schema + execsql { + INSERT INTO t1 VALUES(1, 2, 3, 4); + INSERT INTO t1 VALUES(2, 5, 6, 7); + INSERT INTO t1 VALUES(3, 8, 9, 10); + } + + do_test $tn3.5.$tn2.$tn.1 { + create_ota5 ota.db + $cmd test.db ota.db + } {SQLITE_DONE} + + do_execsql_test $tn3.5.$tn2.$tn.2 { + SELECT * FROM t1 ORDER BY a ASC; + } { + 1 2 3 5 + 2 5 10 5 + 3 11 9 10 + } + + do_execsql_test $tn3.5.$tn2.$tn.3 { PRAGMA integrity_check } ok + } + } + + #------------------------------------------------------------------------- + # Test some error cases: + # + # * A virtual table with no ota_rowid column. + # * A no-PK table with no ota_rowid column. + # * A PK table with an ota_rowid column. + # + ifcapable fts3 { + foreach {tn schema error} { + 1 { + CREATE TABLE t1(a, b); + CREATE TABLE ota.data_t1(a, b, ota_control); + } {SQLITE_ERROR - table data_t1 requires ota_rowid column} + + 2 { + CREATE VIRTUAL TABLE t1 USING fts4(a, b); + CREATE TABLE ota.data_t1(a, b, ota_control); + } {SQLITE_ERROR - table data_t1 requires ota_rowid column} + + 3 { + CREATE TABLE t1(a PRIMARY KEY, b); + CREATE TABLE ota.data_t1(a, b, ota_rowid, ota_control); + } {SQLITE_ERROR - table data_t1 may not have ota_rowid column} + + 4 { + CREATE TABLE t1(a INTEGER PRIMARY KEY, b); + CREATE TABLE ota.data_t1(a, b, ota_rowid, ota_control); + } {SQLITE_ERROR - table data_t1 may not have ota_rowid column} + + 5 { + CREATE TABLE t1(a, b PRIMARY KEY) WITHOUT ROWID; + CREATE TABLE ota.data_t1(a, b, ota_rowid, ota_control); + } {SQLITE_ERROR - table data_t1 may not have ota_rowid column} + + } { + reset_db + forcedelete ota.db + execsql { ATTACH 'ota.db' AS ota } + execsql $schema + + do_test $tn3.6.$tn { + list [catch { run_ota test.db ota.db } msg] $msg + } [list 1 $error] + } + } + + eval $destroy_vfs } finish_test DELETED ext/ota/ota2.test Index: ext/ota/ota2.test ================================================================== --- ext/ota/ota2.test +++ /dev/null @@ -1,74 +0,0 @@ -# 2014 August 30 -# -# 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. -# -#*********************************************************************** -# - -if {![info exists testdir]} { - set testdir [file join [file dirname [info script]] .. .. test] -} -source $testdir/tester.tcl -set ::testprefix ota2 - -forcedelete {*}[glob -nocomplain test.db?*] - -do_execsql_test 1.0 { - CREATE TABLE t1(a, b); - INSERT INTO t1 VALUES(1, 2); -} {} -do_test 1.1 { glob test.db* } {test.db} - -do_execsql_test 1.2 { - PRAGMA pager_ota_mode = 1; - INSERT INTO t1 VALUES(3, 4); - INSERT INTO t1 VALUES(5, 6); - SELECT * FROM t1; -} {1 2 3 4 5 6} - -do_test 1.3 { lsort [glob test.db*] } {test.db test.db-oal} - -do_test 1.4 { - sqlite3 db2 test.db - db2 eval { SELECT * FROM t1 } -} {1 2} - -do_test 1.5 { - catchsql { INSERT INTO t1 VALUES(7, 8) } db2 -} {1 {database is locked}} - -db2 close -db close - -sqlite3 db test.db -do_execsql_test 1.6 { - PRAGMA pager_ota_mode = 1; - SELECT * FROM t1; -} {1 2 3 4 5 6} - -do_execsql_test 1.7 { - INSERT INTO t1 VALUES(7,8); - SELECT * FROM t1; -} {1 2 3 4 5 6 7 8} - -db close -sqlite3 db2 test.db - -do_test 1.8 { - execsql { BEGIN; SELECT * FROM t1 } db2 -} {1 2} -do_test 1.9 { - file rename test.db-oal test.db-wal - execsql { SELECT * FROM t1 } db2 -} {1 2} -do_test 1.10 { - execsql { COMMIT; SELECT * FROM t1 } db2 -} {1 2 3 4 5 6 7 8} - - -finish_test DELETED ext/ota/ota4.test Index: ext/ota/ota4.test ================================================================== --- ext/ota/ota4.test +++ /dev/null @@ -1,128 +0,0 @@ -# 2014 August 30 -# -# 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. -# -#*********************************************************************** -# -# Test some properties of the pager_ota_mode and ota_mode pragmas. -# - -if {![info exists testdir]} { - set testdir [file join [file dirname [info script]] .. .. test] -} -source $testdir/tester.tcl -set ::testprefix ota4 - -#------------------------------------------------------------------------- -# The following tests aim to verify some properties of the pager_ota_mode -# pragma: -# -# 1. Cannot set the pager_ota_mode flag on a WAL mode database. -# -# 2. Or if there is an open read transaction. -# -# 3. Cannot start a transaction with pager_ota_mode set if there -# is a WAL file in the file-system. -# -# 4. Or if the wal-mode flag is set in the database file header. -# -# 5. Cannot open a transaction with pager_ota_mode set if the database -# file has been modified by a rollback mode client since the *-oal -# file was started. -# - -do_execsql_test 1.1.1 { - PRAGMA journal_mode = wal; - SELECT * FROM sqlite_master; -} {wal} -do_catchsql_test 1.1.2 { - PRAGMA pager_ota_mode = 1 -} {1 {cannot set pager_ota_mode in wal mode}} - - -do_execsql_test 1.2.1 { - PRAGMA journal_mode = delete; - BEGIN; - SELECT * FROM sqlite_master; -} {delete} -do_catchsql_test 1.2.2 { - PRAGMA pager_ota_mode = 1 -} {1 {cannot set pager_ota_mode with open transaction}} -do_execsql_test 1.2.3 { - COMMIT; -} {} - - -do_execsql_test 1.3.1 { - PRAGMA journal_mode = wal; - CREATE TABLE t1(a, b); - INSERT INTO t1 VALUES(1, 2); -} {wal} -do_test 1.3.2 { - forcecopy test.db-wal test.db-bak - execsql { - PRAGMA journal_mode = delete; - PRAGMA pager_ota_mode = 1; - } - forcecopy test.db-bak test.db-wal - catchsql { - SELECT * FROM sqlite_master - } -} {1 {unable to open database file}} - -do_test 1.4.1 { - db close - forcedelete test.db-wal test.db-oal - sqlite3 db test.db - execsql { - PRAGMA journal_mode = wal; - PRAGMA pager_ota_mode = 1; - } - catchsql { - SELECT * FROM sqlite_master; - } -} {1 {unable to open database file}} - -do_test 1.5.1 { - forcedelete test.db-oal - reset_db - execsql { - PRAGMA journal_mode = delete; - CREATE TABLE t1(a, b); - INSERT INTO t1 VALUES(1, 2); - } - execsql { - PRAGMA pager_ota_mode = 1; - INSERT INTO t1 VALUES(3, 4); - } - db close - sqlite3 db test.db - execsql { - SELECT * FROM t1; - } -} {1 2} -do_execsql_test 1.5.2 { - PRAGMA pager_ota_mode = 1; - SELECT * FROM t1; - INSERT INTO t1 VALUES(5, 6); -} {1 2 3 4} -do_test 5.3 { - db close - sqlite3 db test.db - execsql { - INSERT INTO t1 VALUES(7, 8); - SELECT * FROM t1; - } -} {1 2 7 8} -do_catchsql_test 1.5.4 { - PRAGMA pager_ota_mode = 1; - SELECT * FROM t1; -} {1 {database is locked}} - -finish_test - Index: ext/ota/ota6.test ================================================================== --- ext/ota/ota6.test +++ ext/ota/ota6.test @@ -48,11 +48,11 @@ for {set nStep 1} {$nStep < 7} {incr nStep} { do_test 1.$nStep.1 { setup_test sqlite3ota ota test.db ota.db for {set i 0} {$i<$nStep} {incr i} {ota step} - + ota close sqlite3 db test.db execsql { INSERT INTO t1 VALUES(5, 'hello') } sqlite3ota ota test.db ota.db ota step @@ -63,11 +63,11 @@ do_test 1.$nStep.3 { list [file exists test.db-oal] [file exists test.db-wal] } {1 0} do_test 1.$nStep.4 { list [catch { ota close } msg] $msg - } {1 {SQLITE_BUSY - database is locked}} + } {1 {SQLITE_BUSY - database modified during ota update}} } for {set nStep 7} {$nStep < 8} {incr nStep} { do_test 1.$nStep.1 { setup_test @@ -120,9 +120,7 @@ SELECT * FROM t1; } {1 t1 5 hello} } - finish_test - ADDED ext/ota/otaA.test Index: ext/ota/otaA.test ================================================================== --- /dev/null +++ ext/ota/otaA.test @@ -0,0 +1,82 @@ +# 2014 August 30 +# +# 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 contains tests for the OTA module. More specifically, it +# contains tests to ensure that it is an error to attempt to update +# a wal mode database via OTA. +# + +if {![info exists testdir]} { + set testdir [file join [file dirname [info script]] .. .. test] +} +source $testdir/tester.tcl +set ::testprefix otaA + +set db_sql { + CREATE TABLE t1(a PRIMARY KEY, b, c); +} +set ota_sql { + CREATE TABLE data_t1(a, b, c, ota_control); + INSERT INTO data_t1 VALUES(1, 2, 3, 0); + INSERT INTO data_t1 VALUES(4, 5, 6, 0); + INSERT INTO data_t1 VALUES(7, 8, 9, 0); +} + +do_test 1.0 { + forcedelete test.db ota.db + + sqlite3 db test.db + db eval $db_sql + db eval { PRAGMA journal_mode = wal } + db close + + sqlite3 db ota.db + db eval $ota_sql + db close + + sqlite3ota ota test.db ota.db + ota step +} {SQLITE_ERROR} +do_test 1.1 { + list [catch { ota close } msg] $msg +} {1 {SQLITE_ERROR - cannot update wal mode database}} + +do_test 2.0 { + forcedelete test.db ota.db + + sqlite3 db test.db + db eval $db_sql + db close + + sqlite3 db ota.db + db eval $ota_sql + db close + + sqlite3ota ota test.db ota.db + ota step + ota close +} {SQLITE_OK} + +do_test 2.1 { + sqlite3 db test.db + db eval {PRAGMA journal_mode = wal} + db close + sqlite3ota ota test.db ota.db + ota step +} {SQLITE_ERROR} + +do_test 2.2 { + list [catch { ota close } msg] $msg +} {1 {SQLITE_ERROR - cannot update wal mode database}} + + +finish_test + Index: ext/ota/sqlite3ota.c ================================================================== --- ext/ota/sqlite3ota.c +++ ext/ota/sqlite3ota.c @@ -60,29 +60,34 @@ ** ** OTA_STATE_CKPT: ** Valid if STAGE==3. The blob to pass to sqlite3ckpt_start() to resume ** the incremental checkpoint. ** +** OTA_STATE_COOKIE: +** Valid if STAGE==1. The current change-counter cookie value in the +** target db file. */ #define OTA_STATE_STAGE 1 #define OTA_STATE_TBL 2 #define OTA_STATE_IDX 3 #define OTA_STATE_ROW 4 #define OTA_STATE_PROGRESS 5 #define OTA_STATE_CKPT 6 +#define OTA_STATE_COOKIE 7 #define OTA_STAGE_OAL 1 -#define OTA_STAGE_COPY 2 #define OTA_STAGE_CKPT 3 #define OTA_STAGE_DONE 4 #define OTA_CREATE_STATE "CREATE TABLE IF NOT EXISTS ota.ota_state" \ "(k INTEGER PRIMARY KEY, v)" typedef struct OtaState OtaState; typedef struct OtaObjIter OtaObjIter; +typedef struct ota_vfs ota_vfs; +typedef struct ota_file ota_file; /* ** A structure to store values read from the ota_state table in memory. */ struct OtaState { @@ -149,10 +154,11 @@ #define OTA_PK_NONE 1 #define OTA_PK_IPK 2 #define OTA_PK_EXTERNAL 3 #define OTA_PK_WITHOUT_ROWID 4 #define OTA_PK_VTAB 5 + /* ** OTA handle. */ struct sqlite3ota { @@ -164,12 +170,41 @@ char *zErrmsg; /* Error message if rc!=SQLITE_OK */ int nStep; /* Rows processed for current object */ int nProgress; /* Rows processed for all objects */ OtaObjIter objiter; /* Iterator for skipping through tbl/idx */ sqlite3_ckpt *pCkpt; /* Incr-checkpoint handle */ + ota_file *pTargetFd; /* File handle open on target db */ + const char *zVfsName; /* Name of automatically created ota vfs */ +}; + +struct ota_vfs { + sqlite3_vfs base; /* ota VFS shim methods */ + sqlite3_vfs *pRealVfs; /* Underlying VFS */ + sqlite3_mutex *mutex; + const char *zOtaWal; +}; + +struct ota_file { + sqlite3_file base; /* sqlite3_file methods */ + sqlite3_file *pReal; /* Underlying file handle */ + ota_vfs *pOtaVfs; /* Pointer to the ota_vfs object */ + sqlite3ota *pOta; /* Pointer to ota object (ota target only) */ + + int openFlags; /* Flags this file was opened with */ + unsigned int iCookie; /* Cookie value for main db files */ + unsigned char iWriteVer; /* "write-version" value for main db files */ + + int nShm; /* Number of entries in apShm[] array */ + char **apShm; /* Array of mmap'd *-shm regions */ + const char *zWal; /* Wal filename for this db file */ + char *zDel; /* Delete this when closing file */ }; + +static void otaCreateVfs(sqlite3ota*, const char*); +static void otaDeleteVfs(sqlite3ota*); + /* ** Prepare the SQL statement in buffer zSql against database handle db. ** If successful, set *ppStmt to point to the new statement and return ** SQLITE_OK. ** @@ -410,10 +445,24 @@ } } va_end(ap); return p->rc; } + +static void *otaMalloc(sqlite3ota *p, int nByte){ + void *pRet = 0; + if( p->rc==SQLITE_OK ){ + pRet = sqlite3_malloc(nByte); + if( pRet==0 ){ + p->rc = SQLITE_NOMEM; + }else{ + memset(pRet, 0, nByte); + } + } + return pRet; +} + /* ** Allocate and zero the pIter->azTblCol[] and abTblPk[] arrays so that ** there is room for at least nCol elements. If an OOM occurs, store an ** error code in the OTA handle passed as the first argument. @@ -420,21 +469,17 @@ */ static void otaAllocateIterArrays(sqlite3ota *p, OtaObjIter *pIter, int nCol){ int nByte = (2*sizeof(char*) + sizeof(int) + 2*sizeof(unsigned char)) * nCol; char **azNew; - assert( p->rc==SQLITE_OK ); - azNew = (char**)sqlite3_malloc(nByte); + azNew = (char**)otaMalloc(p, nByte); if( azNew ){ - memset(azNew, 0, nByte); pIter->azTblCol = azNew; pIter->azTblType = &azNew[nCol]; pIter->aiSrcOrder = (int*)&pIter->azTblType[nCol]; pIter->abTblPk = (unsigned char*)&pIter->aiSrcOrder[nCol]; pIter->abNotNull = (unsigned char*)&pIter->abTblPk[nCol]; - }else{ - p->rc = SQLITE_NOMEM; } } static char *otaStrndup(const char *zStr, int nStr, int *pRc){ char *zRet = 0; @@ -967,21 +1012,18 @@ return zList; } static char *otaObjIterGetBindlist(sqlite3ota *p, int nBind){ char *zRet = 0; - if( p->rc==SQLITE_OK ){ - int nByte = nBind*2 + 1; - zRet = sqlite3_malloc(nByte); - if( zRet==0 ){ - p->rc = SQLITE_NOMEM; - }else{ - int i; - for(i=0; irc; } + +static void otaSqlTrace(void *pCtx, const char *zSql){ + /* printf("SQL: %s\n", zSql); */ +} /* ** Open the database handle and attach the OTA database as "ota". If an ** error occurs, leave an error code and message in the OTA handle. */ static void otaOpenDatabase(sqlite3ota *p){ + int flags = SQLITE_OPEN_READWRITE | SQLITE_OPEN_CREATE; assert( p->rc==SQLITE_OK ); - sqlite3_close(p->db); - p->db = 0; + assert( p->db==0 ); - p->rc = sqlite3_open(p->zTarget, &p->db); + p->rc = sqlite3_open_v2(p->zTarget, &p->db, flags, p->zVfsName); if( p->rc ){ p->zErrmsg = sqlite3_mprintf("%s", sqlite3_errmsg(p->db)); + }else{ + otaMPrintfExec(p, "ATTACH %Q AS ota", p->zOta); + /* sqlite3_trace(p->db, otaSqlTrace, 0); */ + + /* Mark the database file just opened as an OTA target database. If + ** this call returns SQLITE_NOTFOUND, then the OTA vfs is not in use. + ** This is an error. */ + if( p->rc==SQLITE_OK ){ + p->rc = sqlite3_file_control(p->db, "main", SQLITE_FCNTL_OTA, (void*)p); + if( p->rc==SQLITE_NOTFOUND ){ + p->rc = SQLITE_ERROR; + p->zErrmsg = sqlite3_mprintf("ota vfs not found"); + } + } } - otaMPrintfExec(p, "ATTACH %Q AS ota", p->zOta); } /* ** This routine is a copy of the sqlite3FileSuffix3() routine from the core. ** It is a no-op unless SQLITE_ENABLE_8_3_NAMES is defined. @@ -1504,12 +1563,14 @@ otaFileSuffix3(zBase, zOal); rename(zOal, zWal); /* Re-open the databases. */ otaObjIterFinalize(&p->objiter); - otaOpenDatabase(p); + sqlite3_close(p->db); + p->db = 0; p->eStage = OTA_STAGE_CKPT; + otaOpenDatabase(p); } sqlite3_free(zWal); sqlite3_free(zOal); } @@ -1793,17 +1854,19 @@ "(%d, %d), " "(%d, %Q), " "(%d, %Q), " "(%d, %d), " "(%d, %lld), " - "(%d, ?) ", + "(%d, ?), " + "(%d, %lld) ", OTA_STATE_STAGE, p->eStage, OTA_STATE_TBL, p->objiter.zTbl, OTA_STATE_IDX, p->objiter.zIdx, OTA_STATE_ROW, p->nStep, OTA_STATE_PROGRESS, p->nProgress, - OTA_STATE_CKPT + OTA_STATE_CKPT, + OTA_STATE_COOKIE, (sqlite3_int64)p->pTargetFd->iCookie ) ); assert( pInsert==0 || rc==SQLITE_OK ); if( rc==SQLITE_OK ){ if( p->pCkpt ){ @@ -1846,11 +1909,11 @@ ** and return NULL. */ static OtaState *otaLoadState(sqlite3ota *p){ const char *zSelect = "SELECT k, v FROM ota.ota_state"; OtaState *pRet = 0; - sqlite3_stmt *pStmt; + sqlite3_stmt *pStmt = 0; int rc; int rc2; assert( p->rc==SQLITE_OK ); pRet = (OtaState*)sqlite3_malloc(sizeof(OtaState)); @@ -1864,11 +1927,10 @@ while( rc==SQLITE_OK && SQLITE_ROW==sqlite3_step(pStmt) ){ switch( sqlite3_column_int(pStmt, 0) ){ case OTA_STATE_STAGE: pRet->eStage = sqlite3_column_int(pStmt, 1); if( pRet->eStage!=OTA_STAGE_OAL - && pRet->eStage!=OTA_STAGE_COPY && pRet->eStage!=OTA_STAGE_CKPT ){ p->rc = SQLITE_CORRUPT; } break; @@ -1893,10 +1955,23 @@ pRet->nCkptState = sqlite3_column_bytes(pStmt, 1); pRet->pCkptState = (unsigned char*)otaStrndup( (char*)sqlite3_column_blob(pStmt, 1), pRet->nCkptState, &rc ); break; + + case OTA_STATE_COOKIE: + /* At this point (p->iCookie) contains the value of the change-counter + ** cookie (the thing that gets incremented when a transaction is + ** committed in rollback mode) currently stored on page 1 of the + ** database file. */ + if( pRet->eStage==OTA_STAGE_OAL + && p->pTargetFd->iCookie!=(unsigned int)sqlite3_column_int64(pStmt, 1) + ){ + rc = SQLITE_BUSY; + p->zErrmsg = sqlite3_mprintf("database modified during ota update"); + } + break; default: rc = SQLITE_CORRUPT; break; } @@ -1963,22 +2038,32 @@ p = (sqlite3ota*)sqlite3_malloc(sizeof(sqlite3ota)+nTarget+1+nOta+1); if( p ){ OtaState *pState = 0; - /* Open the target database */ + /* Create the custom VFS */ memset(p, 0, sizeof(sqlite3ota)); - p->zTarget = (char*)&p[1]; - memcpy(p->zTarget, zTarget, nTarget+1); - p->zOta = &p->zTarget[nTarget+1]; - memcpy(p->zOta, zOta, nOta+1); - otaOpenDatabase(p); + otaCreateVfs(p, 0); + + /* Open the target database */ + if( p->rc==SQLITE_OK ){ + p->zTarget = (char*)&p[1]; + memcpy(p->zTarget, zTarget, nTarget+1); + p->zOta = &p->zTarget[nTarget+1]; + memcpy(p->zOta, zOta, nOta+1); + otaOpenDatabase(p); + } /* If it has not already been created, create the ota_state table */ if( p->rc==SQLITE_OK ){ p->rc = sqlite3_exec(p->db, OTA_CREATE_STATE, 0, 0, &p->zErrmsg); } + + if( p->rc==SQLITE_OK && p->pTargetFd->iWriteVer>1 ){ + p->rc = SQLITE_ERROR; + p->zErrmsg = sqlite3_mprintf("cannot update wal mode database"); + } if( p->rc==SQLITE_OK ){ pState = otaLoadState(p); assert( pState || p->rc!=SQLITE_OK ); if( p->rc==SQLITE_OK ){ @@ -1993,16 +2078,18 @@ } assert( p->rc!=SQLITE_OK || p->eStage!=0 ); if( p->rc==SQLITE_OK ){ if( p->eStage==OTA_STAGE_OAL ){ - const char *zScript = - "PRAGMA journal_mode=off;" - "PRAGMA pager_ota_mode=1;" - "BEGIN IMMEDIATE;" - ; - p->rc = sqlite3_exec(p->db, zScript, 0, 0, &p->zErrmsg); + ota_vfs *pOtaVfs = p->pTargetFd->pOtaVfs; + + sqlite3_mutex_enter(pOtaVfs->mutex); + assert( pOtaVfs->zOtaWal==0 ); + pOtaVfs->zOtaWal = p->pTargetFd->zWal; + p->rc = sqlite3_exec(p->db, "BEGIN IMMEDIATE", 0, 0, &p->zErrmsg); + pOtaVfs->zOtaWal = 0; + sqlite3_mutex_leave(pOtaVfs->mutex); /* Point the object iterator at the first object */ if( p->rc==SQLITE_OK ){ p->rc = otaObjIterFirst(p, &p->objiter); } @@ -2079,17 +2166,14 @@ /* Commit the transaction to the *-oal file. */ if( p->rc==SQLITE_OK && p->eStage==OTA_STAGE_OAL ){ p->rc = sqlite3_exec(p->db, "COMMIT", 0, 0, &p->zErrmsg); } - if( p->rc==SQLITE_OK && p->eStage==OTA_STAGE_CKPT ){ - p->rc = sqlite3_exec(p->db, "PRAGMA pager_ota_mode=2", 0, 0, &p->zErrmsg); - } - - /* Close the open database handle */ + /* Close the open database handle and VFS object. */ if( p->pCkpt ) sqlite3_ckpt_close(p->pCkpt, 0, 0); sqlite3_close(p->db); + otaDeleteVfs(p); otaEditErrmsg(p); rc = p->rc; *pzErrmsg = p->zErrmsg; sqlite3_free(p); @@ -2106,10 +2190,628 @@ ** current OTA update was started. */ sqlite3_int64 sqlite3ota_progress(sqlite3ota *pOta){ return pOta->nProgress; } + +/************************************************************************** +** Beginning of OTA VFS shim methods. The VFS shim modifies the behaviour +** of a standard VFS in the following ways: +** +** 1. Whenever the first page of a main database file is read or +** written, the value of the change-counter cookie is stored in +** ota_file.iCookie. Similarly, the value of the "write-version" +** database header field is stored in ota_file.iWriteVer. This ensures +** that the values are always trustworthy within an open transaction. +** +** 2. When the ota handle is in OTA_STAGE_OAL or OTA_STAGE_CKPT state, all +** EXCLUSIVE lock attempts on the target database fail. This prevents +** sqlite3_close() from running an automatic checkpoint. Until the +** ota handle reaches OTA_STAGE_DONE - at that point the automatic +** checkpoint may be required to delete the *-wal file. +** +** 3. In OTA_STAGE_OAL, the *-shm file is stored in memory. All xShmLock() +** calls are noops. This is just an optimization. +** +** 4. In OTA_STAGE_OAL mode, when SQLite calls xAccess() to check if a +** *-wal file associated with the target database exists, the following +** special handling applies: +** +** a) if the *-wal file does exist, return SQLITE_CANTOPEN. An OTA +** target database may not be in wal mode already. +** +** b) if the *-wal file does not exist, set the output parameter to +** non-zero (to tell SQLite that it does exist) anyway. +** +** 5. In OTA_STAGE_OAL mode, if SQLite tries to open a *-wal file +** associated with a target database, open the corresponding *-oal file +** instead. +*/ + +/* +** Close an ota file. +*/ +static int otaVfsClose(sqlite3_file *pFile){ + ota_file *p = (ota_file*)pFile; + int rc; + int i; + + /* Free the contents of the apShm[] array. And the array itself. */ + for(i=0; inShm; i++){ + sqlite3_free(p->apShm[i]); + } + sqlite3_free(p->apShm); + p->apShm = 0; + sqlite3_free(p->zDel); + + /* Close the underlying file handle */ + rc = p->pReal->pMethods->xClose(p->pReal); + return rc; +} + + +/* +** Read and return an unsigned 32-bit big-endian integer from the buffer +** passed as the only argument. +*/ +static unsigned int otaGetU32(unsigned char *aBuf){ + return ((unsigned int)aBuf[0] << 24) + + ((unsigned int)aBuf[1] << 16) + + ((unsigned int)aBuf[2] << 8) + + ((unsigned int)aBuf[3]); +} + +/* +** Read data from an otaVfs-file. +*/ +static int otaVfsRead( + sqlite3_file *pFile, + void *zBuf, + int iAmt, + sqlite_int64 iOfst +){ + ota_file *p = (ota_file*)pFile; + int rc = p->pReal->pMethods->xRead(p->pReal, zBuf, iAmt, iOfst); + if( rc==SQLITE_OK && iOfst==0 && (p->openFlags & SQLITE_OPEN_MAIN_DB) ){ + /* These look like magic numbers. But they are stable, as they are part + ** of the definition of the SQLite file format, which may not change. */ + unsigned char *pBuf = (unsigned char*)zBuf; + p->iCookie = otaGetU32(&pBuf[24]); + p->iWriteVer = pBuf[19]; + } + return rc; +} + +/* +** Write data to an otaVfs-file. +*/ +static int otaVfsWrite( + sqlite3_file *pFile, + const void *zBuf, + int iAmt, + sqlite_int64 iOfst +){ + ota_file *p = (ota_file*)pFile; + int rc = p->pReal->pMethods->xWrite(p->pReal, zBuf, iAmt, iOfst); + if( rc==SQLITE_OK && iOfst==0 && (p->openFlags & SQLITE_OPEN_MAIN_DB) ){ + /* These look like magic numbers. But they are stable, as they are part + ** of the definition of the SQLite file format, which may not change. */ + unsigned char *pBuf = (unsigned char*)zBuf; + p->iCookie = otaGetU32(&pBuf[24]); + p->iWriteVer = pBuf[19]; + } + return rc; +} + +/* +** Truncate an otaVfs-file. +*/ +static int otaVfsTruncate(sqlite3_file *pFile, sqlite_int64 size){ + ota_file *p = (ota_file*)pFile; + return p->pReal->pMethods->xTruncate(p->pReal, size); +} + +/* +** Sync an otaVfs-file. +*/ +static int otaVfsSync(sqlite3_file *pFile, int flags){ + ota_file *p = (ota_file *)pFile; + return p->pReal->pMethods->xSync(p->pReal, flags); +} + +/* +** Return the current file-size of an otaVfs-file. +*/ +static int otaVfsFileSize(sqlite3_file *pFile, sqlite_int64 *pSize){ + ota_file *p = (ota_file *)pFile; + return p->pReal->pMethods->xFileSize(p->pReal, pSize); +} + +/* +** Lock an otaVfs-file. +*/ +static int otaVfsLock(sqlite3_file *pFile, int eLock){ + ota_file *p = (ota_file*)pFile; + sqlite3ota *pOta = p->pOta; + int rc = SQLITE_OK; + + if( pOta && eLock==SQLITE_LOCK_EXCLUSIVE + && (pOta->eStage==OTA_STAGE_OAL || pOta->eStage==OTA_STAGE_CKPT) + ){ + /* Do not allow EXCLUSIVE locks. Preventing SQLite from taking this + ** prevents it from checkpointing the database from sqlite3_close(). */ + rc = SQLITE_BUSY; + }else{ + rc = p->pReal->pMethods->xLock(p->pReal, eLock); + } + + return rc; +} + +/* +** Unlock an otaVfs-file. +*/ +static int otaVfsUnlock(sqlite3_file *pFile, int eLock){ + ota_file *p = (ota_file *)pFile; + return p->pReal->pMethods->xUnlock(p->pReal, eLock); +} + +/* +** Check if another file-handle holds a RESERVED lock on an otaVfs-file. +*/ +static int otaVfsCheckReservedLock(sqlite3_file *pFile, int *pResOut){ + ota_file *p = (ota_file *)pFile; + return p->pReal->pMethods->xCheckReservedLock(p->pReal, pResOut); +} + +/* +** File control method. For custom operations on an otaVfs-file. +*/ +static int otaVfsFileControl(sqlite3_file *pFile, int op, void *pArg){ + ota_file *p = (ota_file *)pFile; + int (*xControl)(sqlite3_file*,int,void*) = p->pReal->pMethods->xFileControl; + + if( op==SQLITE_FCNTL_OTA ){ + int rc; + sqlite3ota *pOta = (sqlite3ota*)pArg; + + /* First try to find another OTA vfs lower down in the vfs stack. If + ** one is found, this vfs will operate in pass-through mode. The lower + ** level vfs will do the special OTA handling. */ + rc = xControl(p->pReal, op, pArg); + + if( rc==SQLITE_NOTFOUND ){ + /* Now search for a zipvfs instance lower down in the VFS stack. If + ** one is found, this is an error. */ + void *dummy = 0; + rc = xControl(p->pReal, SQLITE_FCNTL_ZIPVFS_PAGER, &dummy); + if( rc==SQLITE_OK ){ + rc = SQLITE_ERROR; + pOta->zErrmsg = sqlite3_mprintf("ota/zipvfs setup error"); + }else if( rc==SQLITE_NOTFOUND ){ + pOta->pTargetFd = p; + p->pOta = pOta; + rc = SQLITE_OK; + } + } + return rc; + } + return xControl(p->pReal, op, pArg); +} + +/* +** Return the sector-size in bytes for an otaVfs-file. +*/ +static int otaVfsSectorSize(sqlite3_file *pFile){ + ota_file *p = (ota_file *)pFile; + return p->pReal->pMethods->xSectorSize(p->pReal); +} + +/* +** Return the device characteristic flags supported by an otaVfs-file. +*/ +static int otaVfsDeviceCharacteristics(sqlite3_file *pFile){ + ota_file *p = (ota_file *)pFile; + return p->pReal->pMethods->xDeviceCharacteristics(p->pReal); +} + +/* +** Shared-memory methods are all pass-thrus. +*/ +static int otaVfsShmLock(sqlite3_file *pFile, int ofst, int n, int flags){ + ota_file *p = (ota_file*)pFile; + int rc = SQLITE_OK; + +#ifdef SQLITE_AMALGAMATION + assert( WAL_CKPT_LOCK==1 ); +#endif + + if( p->pOta && p->pOta->eStage==OTA_STAGE_OAL ){ + /* Magic number 1 is the WAL_CKPT_LOCK lock. Preventing SQLite from + ** taking this lock also prevents any checkpoints from occurring. + ** todo: really, it's not clear why this might occur, as + ** wal_autocheckpoint ought to be turned off. */ + if( ofst==1 && n==1 ) rc = SQLITE_BUSY; + }else{ + assert( p->nShm==0 ); + rc = p->pReal->pMethods->xShmLock(p->pReal, ofst, n, flags); + } + + return rc; +} + +static int otaVfsShmMap( + sqlite3_file *pFile, + int iRegion, + int szRegion, + int isWrite, + void volatile **pp +){ + ota_file *p = (ota_file*)pFile; + int rc = SQLITE_OK; + + /* If not in OTA_STAGE_OAL, allow this call to pass through. Or, if this + ** ota is in the OTA_STAGE_OAL state, use heap memory for *-shm space + ** instead of a file on disk. */ + if( p->pOta && p->pOta->eStage==OTA_STAGE_OAL ){ + if( iRegion<=p->nShm ){ + int nByte = (iRegion+1) * sizeof(char*); + char **apNew = (char**)sqlite3_realloc(p->apShm, nByte); + if( apNew==0 ){ + rc = SQLITE_NOMEM; + }else{ + memset(&apNew[p->nShm], 0, sizeof(char*) * (1 + iRegion - p->nShm)); + p->apShm = apNew; + p->nShm = iRegion+1; + } + } + + if( rc==SQLITE_OK && p->apShm[iRegion]==0 ){ + char *pNew = (char*)sqlite3_malloc(szRegion); + if( pNew==0 ){ + rc = SQLITE_NOMEM; + }else{ + memset(pNew, 0, szRegion); + p->apShm[iRegion] = pNew; + } + } + + if( rc==SQLITE_OK ){ + *pp = p->apShm[iRegion]; + }else{ + *pp = 0; + } + }else{ + assert( p->apShm==0 ); + rc = p->pReal->pMethods->xShmMap(p->pReal, iRegion, szRegion, isWrite, pp); + } + + return rc; +} + +/* +** Memory barrier. +*/ +static void otaVfsShmBarrier(sqlite3_file *pFile){ + ota_file *p = (ota_file *)pFile; + p->pReal->pMethods->xShmBarrier(p->pReal); +} + +static int otaVfsShmUnmap(sqlite3_file *pFile, int delFlag){ + ota_file *p = (ota_file*)pFile; + int rc = SQLITE_OK; + + if( p->pOta && p->pOta->eStage==OTA_STAGE_OAL ){ + /* no-op */ + }else{ + rc = p->pReal->pMethods->xShmUnmap(p->pReal, delFlag); + } + return rc; +} + +/* +** Open an ota file handle. +*/ +static int otaVfsOpen( + sqlite3_vfs *pVfs, + const char *zName, + sqlite3_file *pFile, + int flags, + int *pOutFlags +){ + static sqlite3_io_methods otavfs_io_methods = { + 2, /* iVersion */ + otaVfsClose, /* xClose */ + otaVfsRead, /* xRead */ + otaVfsWrite, /* xWrite */ + otaVfsTruncate, /* xTruncate */ + otaVfsSync, /* xSync */ + otaVfsFileSize, /* xFileSize */ + otaVfsLock, /* xLock */ + otaVfsUnlock, /* xUnlock */ + otaVfsCheckReservedLock, /* xCheckReservedLock */ + otaVfsFileControl, /* xFileControl */ + otaVfsSectorSize, /* xSectorSize */ + otaVfsDeviceCharacteristics, /* xDeviceCharacteristics */ + otaVfsShmMap, /* xShmMap */ + otaVfsShmLock, /* xShmLock */ + otaVfsShmBarrier, /* xShmBarrier */ + otaVfsShmUnmap /* xShmUnmap */ + }; + ota_vfs *pOtaVfs = (ota_vfs*)pVfs; + sqlite3_vfs *pRealVfs = pOtaVfs->pRealVfs; + ota_file *pFd = (ota_file *)pFile; + int rc = SQLITE_OK; + const char *zOpen = zName; + + memset(pFd, 0, sizeof(ota_file)); + pFd->pReal = (sqlite3_file*)&pFd[1]; + pFd->pOtaVfs = pOtaVfs; + pFd->openFlags = flags; + if( zName ){ + if( flags & SQLITE_OPEN_MAIN_DB ){ + /* A main database has just been opened. The following block sets + ** (pFd->zWal) to point to a buffer owned by SQLite that contains + ** the name of the *-wal file this db connection will use. SQLite + ** happens to pass a pointer to this buffer when using xAccess() + ** or xOpen() to operate on the *-wal file. */ + int n = strlen(zName); + const char *z = &zName[n]; + if( flags & SQLITE_OPEN_URI ){ + int odd = 0; + while( 1 ){ + if( z[0]==0 ){ + odd = 1 - odd; + if( odd && z[1]==0 ) break; + } + z++; + } + z += 2; + }else{ + while( *z==0 ) z++; + } + z += (n + 8 + 1); + pFd->zWal = z; + } + else if( (flags & SQLITE_OPEN_WAL) && zName==pOtaVfs->zOtaWal ){ + char *zCopy = otaStrndup(zName, -1, &rc); + if( zCopy ){ + int nCopy = strlen(zCopy); + zCopy[nCopy-3] = 'o'; + zOpen = (const char*)(pFd->zDel = zCopy); + } + } + } + + if( rc==SQLITE_OK ){ + rc = pRealVfs->xOpen(pRealVfs, zOpen, pFd->pReal, flags, pOutFlags); + } + if( pFd->pReal->pMethods ){ + pFile->pMethods = &otavfs_io_methods; + } + + return rc; +} + +/* +** Delete the file located at zPath. +*/ +static int otaVfsDelete(sqlite3_vfs *pVfs, const char *zPath, int dirSync){ + sqlite3_vfs *pRealVfs = ((ota_vfs*)pVfs)->pRealVfs; + return pRealVfs->xDelete(pRealVfs, zPath, dirSync); +} + +/* +** Test for access permissions. Return true if the requested permission +** is available, or false otherwise. +*/ +static int otaVfsAccess( + sqlite3_vfs *pVfs, + const char *zPath, + int flags, + int *pResOut +){ + ota_vfs *pOtaVfs = (ota_vfs*)pVfs; + sqlite3_vfs *pRealVfs = pOtaVfs->pRealVfs; + int rc; + + rc = pRealVfs->xAccess(pRealVfs, zPath, flags, pResOut); + + if( rc==SQLITE_OK && flags==SQLITE_ACCESS_EXISTS && pOtaVfs->zOtaWal==zPath ){ + if( *pResOut ){ + rc = SQLITE_CANTOPEN; + }else{ + *pResOut = 1; + } + } + + return rc; +} + +/* +** Populate buffer zOut with the full canonical pathname corresponding +** to the pathname in zPath. zOut is guaranteed to point to a buffer +** of at least (DEVSYM_MAX_PATHNAME+1) bytes. +*/ +static int otaVfsFullPathname( + sqlite3_vfs *pVfs, + const char *zPath, + int nOut, + char *zOut +){ + sqlite3_vfs *pRealVfs = ((ota_vfs*)pVfs)->pRealVfs; + return pRealVfs->xFullPathname(pRealVfs, zPath, nOut, zOut); +} + +#ifndef SQLITE_OMIT_LOAD_EXTENSION +/* +** Open the dynamic library located at zPath and return a handle. +*/ +static void *otaVfsDlOpen(sqlite3_vfs *pVfs, const char *zPath){ + sqlite3_vfs *pRealVfs = ((ota_vfs*)pVfs)->pRealVfs; + return pRealVfs->xDlOpen(pRealVfs, zPath); +} + +/* +** Populate the buffer zErrMsg (size nByte bytes) with a human readable +** utf-8 string describing the most recent error encountered associated +** with dynamic libraries. +*/ +static void otaVfsDlError(sqlite3_vfs *pVfs, int nByte, char *zErrMsg){ + sqlite3_vfs *pRealVfs = ((ota_vfs*)pVfs)->pRealVfs; + pRealVfs->xDlError(pRealVfs, nByte, zErrMsg); +} + +/* +** Return a pointer to the symbol zSymbol in the dynamic library pHandle. +*/ +static void (*otaVfsDlSym( + sqlite3_vfs *pVfs, + void *pArg, + const char *zSym +))(void){ + sqlite3_vfs *pRealVfs = ((ota_vfs*)pVfs)->pRealVfs; + return pRealVfs->xDlSym(pRealVfs, pArg, zSym); +} + +/* +** Close the dynamic library handle pHandle. +*/ +static void otaVfsDlClose(sqlite3_vfs *pVfs, void *pHandle){ + sqlite3_vfs *pRealVfs = ((ota_vfs*)pVfs)->pRealVfs; + return pRealVfs->xDlClose(pRealVfs, pHandle); +} +#endif /* SQLITE_OMIT_LOAD_EXTENSION */ + +/* +** Populate the buffer pointed to by zBufOut with nByte bytes of +** random data. +*/ +static int otaVfsRandomness(sqlite3_vfs *pVfs, int nByte, char *zBufOut){ + sqlite3_vfs *pRealVfs = ((ota_vfs*)pVfs)->pRealVfs; + return pRealVfs->xRandomness(pRealVfs, nByte, zBufOut); +} + +/* +** Sleep for nMicro microseconds. Return the number of microseconds +** actually slept. +*/ +static int otaVfsSleep(sqlite3_vfs *pVfs, int nMicro){ + sqlite3_vfs *pRealVfs = ((ota_vfs*)pVfs)->pRealVfs; + return pRealVfs->xSleep(pRealVfs, nMicro); +} + +/* +** Return the current time as a Julian Day number in *pTimeOut. +*/ +static int otaVfsCurrentTime(sqlite3_vfs *pVfs, double *pTimeOut){ + sqlite3_vfs *pRealVfs = ((ota_vfs*)pVfs)->pRealVfs; + return pRealVfs->xCurrentTime(pRealVfs, pTimeOut); +} + +static int otaVfsGetLastError(sqlite3_vfs *pVfs, int a, char *b){ + return 0; +} + +void sqlite3ota_destroy_vfs(const char *zName){ + sqlite3_vfs *pVfs = sqlite3_vfs_find(zName); + if( pVfs ){ + sqlite3_vfs_unregister(pVfs); + sqlite3_free(pVfs); + } +} + +int sqlite3ota_create_vfs(const char *zName, const char *zParent){ + + /* Template for VFS */ + static sqlite3_vfs vfs_template = { + 1, /* iVersion */ + 0, /* szOsFile */ + 0, /* mxPathname */ + 0, /* pNext */ + 0, /* zName */ + 0, /* pAppData */ + otaVfsOpen, /* xOpen */ + otaVfsDelete, /* xDelete */ + otaVfsAccess, /* xAccess */ + otaVfsFullPathname, /* xFullPathname */ + + otaVfsDlOpen, /* xDlOpen */ + otaVfsDlError, /* xDlError */ + otaVfsDlSym, /* xDlSym */ + otaVfsDlClose, /* xDlClose */ + + otaVfsRandomness, /* xRandomness */ + otaVfsSleep, /* xSleep */ + otaVfsCurrentTime, /* xCurrentTime */ + otaVfsGetLastError, /* xGetLastError */ + 0, /* xCurrentTimeInt64 (version 2) */ + 0, 0, 0 /* Unimplemented version 3 methods */ + }; + + sqlite3_vfs *pParent; /* Parent VFS */ + ota_vfs *pNew = 0; /* Newly allocated VFS */ + int nName; + int rc = SQLITE_OK; + + nName = strlen(zName); + pParent = sqlite3_vfs_find(zParent); + if( pParent==0 ){ + rc = SQLITE_NOTFOUND; + }else{ + int nByte = sizeof(ota_vfs) + nName + 1; + pNew = (ota_vfs*)sqlite3_malloc(nByte); + if( pNew==0 ){ + rc = SQLITE_NOMEM; + }else{ + memset(pNew, 0, nByte); + } + } + + if( rc==SQLITE_OK ){ + char *zSpace; + memcpy(&pNew->base, &vfs_template, sizeof(sqlite3_vfs)); + pNew->base.mxPathname = pParent->mxPathname; + pNew->base.szOsFile = sizeof(ota_file) + pParent->szOsFile; + pNew->pRealVfs = pParent; + + pNew->base.zName = (const char*)(zSpace = (char*)&pNew[1]); + memcpy(zSpace, zName, nName); + + /* Register the new VFS (not as the default) */ + rc = sqlite3_vfs_register(&pNew->base, 0); + if( rc ){ + sqlite3_free(pNew); + } + } + + return rc; +} + +static void otaCreateVfs(sqlite3ota *p, const char *zParent){ + int rnd; + char zRnd[64]; + + assert( p->rc==SQLITE_OK ); + sqlite3_randomness(sizeof(int), (void*)&rnd); + sprintf(zRnd, "ota_vfs_%d", rnd); + p->rc = sqlite3ota_create_vfs(zRnd, zParent); + if( p->rc==SQLITE_NOTFOUND ){ + p->zErrmsg = sqlite3_mprintf("no such vfs: %s", zParent); + }else if( p->rc==SQLITE_OK ){ + sqlite3_vfs *pVfs = sqlite3_vfs_find(zRnd); + assert( pVfs ); + p->zVfsName = pVfs->zName; + } +} + +static void otaDeleteVfs(sqlite3ota *p){ + if( p->zVfsName ){ + sqlite3ota_destroy_vfs(p->zVfsName); + p->zVfsName = 0; + } +} /**************************************************************************/ #ifdef SQLITE_TEST @@ -2233,17 +2935,84 @@ Tcl_CreateObjCommand(interp, zCmd, test_sqlite3ota_cmd, (ClientData)pOta, 0); Tcl_SetObjResult(interp, objv[1]); return TCL_OK; } +/* +** Tclcmd: sqlite3ota_create_vfs ?-default? NAME PARENT +*/ +static int test_sqlite3ota_create_vfs( + ClientData clientData, + Tcl_Interp *interp, + int objc, + Tcl_Obj *CONST objv[] +){ + const char *zName; + const char *zParent; + int rc; + + if( objc!=3 && objc!=4 ){ + Tcl_WrongNumArgs(interp, 1, objv, "?-default? NAME PARENT"); + return TCL_ERROR; + } + + zName = Tcl_GetString(objv[objc-2]); + zParent = Tcl_GetString(objv[objc-1]); + if( zParent[0]=='\0' ) zParent = 0; + + rc = sqlite3ota_create_vfs(zName, zParent); + if( rc!=SQLITE_OK ){ + Tcl_SetObjResult(interp, Tcl_NewStringObj(sqlite3ErrName(rc), -1)); + return TCL_ERROR; + }else if( objc==4 ){ + sqlite3_vfs *pVfs = sqlite3_vfs_find(zName); + sqlite3_vfs_register(pVfs, 1); + } + + Tcl_ResetResult(interp); + return TCL_OK; +} + +/* +** Tclcmd: sqlite3ota_destroy_vfs NAME +*/ +static int test_sqlite3ota_destroy_vfs( + ClientData clientData, + Tcl_Interp *interp, + int objc, + Tcl_Obj *CONST objv[] +){ + const char *zName; + + if( objc!=2 ){ + Tcl_WrongNumArgs(interp, 1, objv, "NAME"); + return TCL_ERROR; + } + + zName = Tcl_GetString(objv[1]); + sqlite3ota_destroy_vfs(zName); + return TCL_OK; +} + int SqliteOta_Init(Tcl_Interp *interp){ - Tcl_CreateObjCommand(interp, "sqlite3ota", test_sqlite3ota, 0, 0); + static struct { + char *zName; + Tcl_ObjCmdProc *xProc; + } aObjCmd[] = { + { "sqlite3ota", test_sqlite3ota }, + { "sqlite3ota_create_vfs", test_sqlite3ota_create_vfs }, + { "sqlite3ota_destroy_vfs", test_sqlite3ota_destroy_vfs }, + }; + int i; + for(i=0; i int SqliteOta_Init(Tcl_Interp *interp){ return TCL_OK; } # endif #endif Index: ext/ota/sqlite3ota.h ================================================================== --- ext/ota/sqlite3ota.h +++ ext/ota/sqlite3ota.h @@ -235,10 +235,19 @@ ** Open an OTA handle. ** ** Argument zTarget is the path to the target database. Argument zOta is ** the path to the OTA database. Each call to this function must be matched ** by a call to sqlite3ota_close(). +** +** By default, OTA uses the default VFS to access the files on disk. To +** use a VFS other than the default, an SQLite "file:" URI containing a +** "vfs=..." option may be passed as the zTarget option. +** +** IMPORTANT NOTE FOR ZIPVFS USERS: The OTA extension works with all of +** SQLite's built-in VFSs, including the multiplexor VFS. However it does +** not work out of the box with zipvfs. Refer to the comment describing +** the zipvfs_create_vfs() API below for details on using OTA with zipvfs. */ sqlite3ota *sqlite3ota_open(const char *zTarget, const char *zOta); /* ** Obtain the underlying database handle used by the OTA extension. @@ -274,12 +283,12 @@ int sqlite3ota_step(sqlite3ota *pOta); /* ** Close an OTA handle. ** -** If the OTA update has been completely applied, commit it to the target -** database. Otherwise, assuming no error has occurred, save the current +** If the OTA update has been completely applied, commit it to the target +** database. Otherwise, assuming no error has occurred, save the current ** state of the OTA update appliation to the OTA database. ** ** If an error has already occurred as part of an sqlite3ota_step() ** or sqlite3ota_open() call, or if one occurs within this function, an ** SQLite error code is returned. Additionally, *pzErrmsg may be set to @@ -298,7 +307,52 @@ ** updates) that have been performed on the target database since the ** current OTA update was started. */ sqlite3_int64 sqlite3ota_progress(sqlite3ota *pOta); +/* +** Part of the OTA implementation uses a custom VFS object. Usually, this +** object is created and deleted automatically by OTA. +** +** The exception is for applications that also use zipvfs. In this case, +** the custom VFS must be explicitly created by the user before the OTA +** handle is opened. The OTA VFS should be installed so that the zipvfs +** VFS uses the OTA VFS, which in turn uses any other VFS layers in use +** (for example multiplexor) to access the file-system. For example, +** to assemble an OTA enabled VFS stack that uses both zipvfs and +** multiplexor (error checking omitted): +** +** // Create a VFS named "multiplexor" (not the default). +** sqlite3_multiplex_initialize(zVfsName, 0); +** +** // Create an ota VFS named "ota" that uses multiplexor. +** sqlite3ota_create_vfs("ota", "multiplexor"); +** +** // Create a zipvfs VFS named "zipvfs" that uses ota. +** zipvfs_create_vfs_v3("zipvfs", "ota", 0, xCompressorAlgorithmDetector); +** +** // Make zipvfs the default VFS. +** sqlite3_vfs_register(sqlite3_vfs_find("zipvfs"), 1); +** +** Because the default VFS created above includes a OTA functionality, it +** may be used by OTA clients. Attempting to use OTA with a zipvfs VFS stack +** that does not include the OTA layer results in an error. +** +** The overhead of adding the "ota" VFS to the system is negligible for +** non-OTA users. There is no harm in an application accessing the +** file-system via "ota" all the time, even if it only uses OTA functionality +** occasionally. +*/ +int sqlite3ota_create_vfs(const char *zName, const char *zParent); + +/* +** Deregister and destroy an OTA vfs created by an earlier call to +** sqlite3ota_create_vfs(). +** +** VFS objects are not reference counted. If a VFS object is destroyed +** before all database handles that use it have been closed, the results +** are undefined. +*/ +void sqlite3ota_destroy_vfs(const char *zName); + #endif /* _SQLITE3OTA_H */ Index: main.mk ================================================================== --- main.mk +++ main.mk @@ -217,10 +217,13 @@ $(TOP)/ext/rtree/rtree.h \ $(TOP)/ext/rtree/rtree.c SRC += \ $(TOP)/ext/userauth/userauth.c \ $(TOP)/ext/userauth/sqlite3userauth.h +SRC += \ + $(TOP)/ext/ota/sqlite3ota.c \ + $(TOP)/ext/ota/sqlite3ota.h # Generated source code files # SRC += \ keywordhash.h \ Index: src/delete.c ================================================================== --- src/delete.c +++ src/delete.c @@ -187,11 +187,11 @@ pInClause = sqlite3PExpr(pParse, TK_IN, pWhereRowid, 0, 0); if( pInClause == 0 ) goto limit_where_cleanup_1; pInClause->x.pSelect = pSelect; pInClause->flags |= EP_xIsSelect; - sqlite3ExprSetHeight(pParse, pInClause); + sqlite3ExprSetHeightAndFlags(pParse, pInClause); return pInClause; /* something went wrong. clean up anything allocated. */ limit_where_cleanup_1: sqlite3SelectDelete(pParse->db, pSelect); Index: src/expr.c ================================================================== --- src/expr.c +++ src/expr.c @@ -144,14 +144,29 @@ pColl = sqlite3FindCollSeq(db, ENC(db), zColl, 0); } break; } if( p->flags & EP_Collate ){ - if( ALWAYS(p->pLeft) && (p->pLeft->flags & EP_Collate)!=0 ){ + if( p->pLeft && (p->pLeft->flags & EP_Collate)!=0 ){ p = p->pLeft; }else{ - p = p->pRight; + Expr *pNext = p->pRight; + /* The Expr.x union is never used at the same time as Expr.pRight */ + assert( p->x.pList==0 || p->pRight==0 ); + /* p->flags holds EP_Collate and p->pLeft->flags does not. And + ** p->x.pSelect cannot. So if p->x.pLeft exists, it must hold at + ** least one EP_Collate. Thus the following two ALWAYS. */ + if( p->x.pList!=0 && ALWAYS(!ExprHasProperty(p, EP_xIsSelect)) ){ + int i; + for(i=0; ALWAYS(ix.pList->nExpr); i++){ + if( ExprHasProperty(p->x.pList->a[i].pExpr, EP_Collate) ){ + pNext = p->x.pList->a[i].pExpr; + break; + } + } + } + p = pNext; } }else{ break; } } @@ -353,29 +368,36 @@ ** Set the Expr.nHeight variable in the structure passed as an ** argument. An expression with no children, Expr.pList or ** Expr.pSelect member has a height of 1. Any other expression ** has a height equal to the maximum height of any other ** referenced Expr plus one. +** +** Also propagate EP_Propagate flags up from Expr.x.pList to Expr.flags, +** if appropriate. */ static void exprSetHeight(Expr *p){ int nHeight = 0; heightOfExpr(p->pLeft, &nHeight); heightOfExpr(p->pRight, &nHeight); if( ExprHasProperty(p, EP_xIsSelect) ){ heightOfSelect(p->x.pSelect, &nHeight); - }else{ + }else if( p->x.pList ){ heightOfExprList(p->x.pList, &nHeight); + p->flags |= EP_Propagate & sqlite3ExprListFlags(p->x.pList); } p->nHeight = nHeight + 1; } /* ** Set the Expr.nHeight variable using the exprSetHeight() function. If ** the height is greater than the maximum allowed expression depth, ** leave an error in pParse. +** +** Also propagate all EP_Propagate flags from the Expr.x.pList into +** Expr.flags. */ -void sqlite3ExprSetHeight(Parse *pParse, Expr *p){ +void sqlite3ExprSetHeightAndFlags(Parse *pParse, Expr *p){ exprSetHeight(p); sqlite3ExprCheckHeight(pParse, p->nHeight); } /* @@ -385,12 +407,21 @@ int sqlite3SelectExprHeight(Select *p){ int nHeight = 0; heightOfSelect(p, &nHeight); return nHeight; } -#else - #define exprSetHeight(y) +#else /* ABOVE: Height enforcement enabled. BELOW: Height enforcement off */ +/* +** Propagate all EP_Propagate flags from the Expr.x.pList into +** Expr.flags. +*/ +void sqlite3ExprSetHeightAndFlags(Parse *pParse, Expr *p){ + if( p && p->x.pList && !ExprHasProperty(p, EP_xIsSelect) ){ + p->flags |= EP_Propagate & sqlite3ExprListFlags(p->x.pList); + } +} +#define exprSetHeight(y) #endif /* SQLITE_MAX_EXPR_DEPTH>0 */ /* ** This routine is the core allocator for Expr nodes. ** @@ -488,15 +519,15 @@ sqlite3ExprDelete(db, pLeft); sqlite3ExprDelete(db, pRight); }else{ if( pRight ){ pRoot->pRight = pRight; - pRoot->flags |= EP_Collate & pRight->flags; + pRoot->flags |= EP_Propagate & pRight->flags; } if( pLeft ){ pRoot->pLeft = pLeft; - pRoot->flags |= EP_Collate & pLeft->flags; + pRoot->flags |= EP_Propagate & pLeft->flags; } exprSetHeight(pRoot); } } @@ -592,11 +623,11 @@ sqlite3ExprListDelete(db, pList); /* Avoid memory leak when malloc fails */ return 0; } pNew->x.pList = pList; assert( !ExprHasProperty(pNew, EP_xIsSelect) ); - sqlite3ExprSetHeight(pParse, pNew); + sqlite3ExprSetHeightAndFlags(pParse, pNew); return pNew; } /* ** Assign a variable number to an expression that encodes a wildcard @@ -1206,10 +1237,25 @@ sqlite3DbFree(db, pItem->zSpan); } sqlite3DbFree(db, pList->a); sqlite3DbFree(db, pList); } + +/* +** Return the bitwise-OR of all Expr.flags fields in the given +** ExprList. +*/ +u32 sqlite3ExprListFlags(const ExprList *pList){ + int i; + u32 m = 0; + if( pList ){ + for(i=0; inExpr; i++){ + m |= pList->a[i].pExpr->flags; + } + } + return m; +} /* ** These routines are Walker callbacks used to check expressions to ** see if they are "constant" for some definition of constant. The ** Walker.eCode value determines the type of "constant" we are looking @@ -1247,11 +1293,11 @@ switch( pExpr->op ){ /* Consider functions to be constant if all their arguments are constant ** and either pWalker->eCode==4 or 5 or the function has the ** SQLITE_FUNC_CONST flag. */ case TK_FUNCTION: - if( pWalker->eCode>=4 || ExprHasProperty(pExpr,EP_Constant) ){ + if( pWalker->eCode>=4 || ExprHasProperty(pExpr,EP_ConstFunc) ){ return WRC_Continue; }else{ pWalker->eCode = 0; return WRC_Abort; } Index: src/pager.c ================================================================== --- src/pager.c +++ src/pager.c @@ -613,22 +613,10 @@ ** ** The Pager.errCode variable is only ever used in PAGER_ERROR state. It ** is set to zero in all other states. In PAGER_ERROR state, Pager.errCode ** is always set to SQLITE_FULL, SQLITE_IOERR or one of the SQLITE_IOERR_XXX ** sub-codes. -** -** otaMode -** This variable is normally 0. It is set to 1 by the PagerSetOtaMode() -** function - as a result of a "PRAGMA pager_ota_mode=1" command. Once -** the *-oal file has been opened and it has been determined that the -** database file has not been modified since it was created, this variable -** is set to 2. -** -** It is also possible to use PagerSetOtaMode() to 2 if the database is -** already in WAL mode. In this case the only effect is to prevent the -** connection from checkpointing the db as part of an sqlite3PagerClose() -** call. */ struct Pager { sqlite3_vfs *pVfs; /* OS functions to use for IO */ u8 exclusiveMode; /* Boolean. True if locking_mode==EXCLUSIVE */ u8 journalMode; /* One of the PAGER_JOURNALMODE_* values */ @@ -640,13 +628,10 @@ u8 syncFlags; /* SYNC_NORMAL or SYNC_FULL otherwise */ u8 tempFile; /* zFilename is a temporary or immutable file */ u8 noLock; /* Do not lock (except in WAL mode) */ u8 readOnly; /* True for a read-only database */ u8 memDb; /* True to inhibit all file I/O */ -#ifdef SQLITE_ENABLE_OTA - u8 otaMode; /* Non-zero if in ota_mode */ -#endif /************************************************************************** ** The following block contains those class members that change during ** routine operation. Class members not in this block are either fixed ** when the pager is first created or else only change when there is a @@ -718,20 +703,10 @@ Wal *pWal; /* Write-ahead log used by "journal_mode=wal" */ char *zWal; /* File name for write-ahead log */ #endif }; -/* -** Return the value of the pager otaMode flag (0, 1 or 2). Or, if -** SQLITE_ENABLE_OTA is not defined, return constant value 0. -*/ -#ifdef SQLITE_ENABLE_OTA -# define PagerOtaMode(pPager) ((pPager)->otaMode) -#else -# define PagerOtaMode(pPager) 0 -#endif - /* ** Indexes for use with Pager.aStat[]. The Pager.aStat[] array contains ** the values accessed by passing SQLITE_DBSTATUS_CACHE_HIT, CACHE_MISS ** or CACHE_WRITE to sqlite3_db_status(). */ @@ -849,12 +824,10 @@ # define pagerWalFrames(v,w,x,y) 0 # define pagerOpenWalIfPresent(z) SQLITE_OK # define pagerBeginReadTransaction(z) SQLITE_OK #endif -static int pagerOpenWalInternal(Pager*, int*); - #ifndef NDEBUG /* ** Usage: ** ** assert( assert_pager_state(pPager) ); @@ -2050,11 +2023,11 @@ if( rc==SQLITE_OK && bCommit && isOpen(pPager->fd) ){ rc = sqlite3OsFileControl(pPager->fd, SQLITE_FCNTL_COMMIT_PHASETWO, 0); if( rc==SQLITE_NOTFOUND ) rc = SQLITE_OK; } - if( !pPager->exclusiveMode && !PagerOtaMode(pPager) + if( !pPager->exclusiveMode && (!pagerUseWal(pPager) || sqlite3WalExclusiveMode(pPager->pWal, 0)) ){ rc2 = pagerUnlockDb(pPager, SHARED_LOCK); pPager->changeCountDone = 0; } @@ -4005,13 +3978,11 @@ sqlite3BeginBenignMalloc(); pagerFreeMapHdrs(pPager); /* pPager->errCode = 0; */ pPager->exclusiveMode = 0; #ifndef SQLITE_OMIT_WAL - sqlite3WalClose(pPager->pWal, - pPager->ckptSyncFlags, pPager->pageSize, (PagerOtaMode(pPager)?0:pTmp) - ); + sqlite3WalClose(pPager->pWal, pPager->ckptSyncFlags, pPager->pageSize, pTmp); pPager->pWal = 0; #endif pager_reset(pPager); if( MEMDB ){ pager_unlock(pPager); @@ -5208,35 +5179,19 @@ /* If there is a WAL file in the file-system, open this database in WAL ** mode. Otherwise, the following function call is a no-op. */ rc = pagerOpenWalIfPresent(pPager); - if( rc==SQLITE_OK && PagerOtaMode(pPager) ){ - int nWal = sqlite3Strlen30(pPager->zWal); - pPager->zWal[nWal-3] = 'o'; - rc = pagerOpenWalInternal(pPager, 0); - } #ifndef SQLITE_OMIT_WAL assert( pPager->pWal==0 || rc==SQLITE_OK ); #endif } if( pagerUseWal(pPager) ){ assert( rc==SQLITE_OK ); rc = pagerBeginReadTransaction(pPager); - if( rc==SQLITE_OK && PagerOtaMode(pPager)==1 ){ - rc = sqlite3WalCheckSalt(pPager->pWal, pPager->fd); - if( rc!=SQLITE_OK ){ - sqlite3WalClose(pPager->pWal, 0, 0, 0); - pPager->pWal = 0; - }else{ -#ifdef SQLITE_ENABLE_OTA - pPager->otaMode = 2; -#endif - } - } } if( pPager->eState==PAGER_OPEN && rc==SQLITE_OK ){ rc = pagerPagecount(pPager, &pPager->dbSize); } @@ -7127,11 +7082,11 @@ ** ** Parameter eMode is one of SQLITE_CHECKPOINT_PASSIVE, FULL or RESTART. */ int sqlite3PagerCheckpoint(Pager *pPager, int eMode, int *pnLog, int *pnCkpt){ int rc = SQLITE_OK; - if( pPager->pWal && PagerOtaMode(pPager)==0 ){ + if( pPager->pWal ){ rc = sqlite3WalCheckpoint(pPager->pWal, eMode, (eMode==SQLITE_CHECKPOINT_PASSIVE ? 0 : pPager->xBusyHandler), pPager->pBusyHandlerArg, pPager->ckptSyncFlags, pPager->pageSize, (u8 *)pPager->pTmpSpace, pnLog, pnCkpt @@ -7195,28 +7150,36 @@ /* Open the connection to the log file. If this operation fails, ** (e.g. due to malloc() failure), return an error code. */ if( rc==SQLITE_OK ){ rc = sqlite3WalOpen(pPager->pVfs, - pPager->fd, pPager->zWal, pPager->exclusiveMode || PagerOtaMode(pPager), + pPager->fd, pPager->zWal, pPager->exclusiveMode, pPager->journalSizeLimit, &pPager->pWal ); } pagerFixMaplimit(pPager); return rc; } + /* -** Open the WAL file if it is not open. If it is already open, set *pbOpen -** to 1 before returning. Return SQLITE_OK if successful, or an SQLite error -** code otherwise. +** The caller must be holding a SHARED lock on the database file to call +** this function. +** +** If the pager passed as the first argument is open on a real database +** file (not a temp file or an in-memory database), and the WAL file +** is not already open, make an attempt to open it now. If successful, +** return SQLITE_OK. If an error occurs or the VFS used by the pager does +** not support the xShmXXX() methods, return an error code. *pbOpen is +** not modified in either case. ** -** The difference between this function and sqlite3PagerOpenWal() is that -** PagerOpenWal() does not open the WAL file if the pager is in OTA mode. +** If the pager is open on a temp-file (or in-memory database), or if +** the WAL file is already open, set *pbOpen to 1 and return SQLITE_OK +** without doing anything. */ -static int pagerOpenWalInternal( +int sqlite3PagerOpenWal( Pager *pPager, /* Pager object */ int *pbOpen /* OUT: Set to true if call is a no-op */ ){ int rc = SQLITE_OK; /* Return code */ @@ -7242,33 +7205,10 @@ } return rc; } -/* -** The caller must be holding a SHARED lock on the database file to call -** this function. -** -** If the pager passed as the first argument is open on a real database -** file (not a temp file or an in-memory database), and the WAL file -** is not already open, make an attempt to open it now. If successful, -** return SQLITE_OK. If an error occurs or the VFS used by the pager does -** not support the xShmXXX() methods, return an error code. *pbOpen is -** not modified in either case. -** -** If the pager is open on a temp-file (or in-memory database), or if -** the WAL file is already open, set *pbOpen to 1 and return SQLITE_OK -** without doing anything. -*/ -int sqlite3PagerOpenWal( - Pager *pPager, /* Pager object */ - int *pbOpen /* OUT: Set to true if call is a no-op */ -){ - if( PagerOtaMode(pPager) ) return SQLITE_CANTOPEN_BKPT; - return pagerOpenWalInternal(pPager, pbOpen); -} - /* ** This function is called to close the connection to the log file prior ** to switching from WAL to rollback mode. ** ** Before closing the log file, this function attempts to take an @@ -7311,24 +7251,10 @@ } } return rc; } -/* -** This function is called by the wal.c module to obtain the 8 bytes of -** "salt" written into the wal file header. In OTA mode, this is a copy -** of bytes 24-31 of the database file. In non-OTA mode, it is 8 bytes -** of pseudo-random data. -*/ -void sqlite3PagerWalSalt(Pager *pPager, u32 *aSalt){ - if( PagerOtaMode(pPager) ){ - memcpy(aSalt, pPager->dbFileVers, 8); - }else{ - sqlite3_randomness(8, aSalt); - } -} - #endif /* !SQLITE_OMIT_WAL */ #ifdef SQLITE_ENABLE_ZIPVFS /* ** A read-lock must be held on the pager when this function is called. If @@ -7342,21 +7268,10 @@ return sqlite3WalFramesize(pPager->pWal); } #endif #ifdef SQLITE_ENABLE_OTA -/* -** Set or clear the "OTA mode" flag. -*/ -int sqlite3PagerSetOtaMode(Pager *pPager, int iOta){ - assert( iOta==1 || iOta==2 ); - if( iOta==1 && (pPager->pWal || pPager->eState!=PAGER_OPEN) ){ - return SQLITE_ERROR; - } - pPager->otaMode = iOta; - return SQLITE_OK; -} /* ** Open an incremental checkpoint handle. */ int sqlite3PagerWalCheckpointStart( Index: src/pager.h ================================================================== --- src/pager.h +++ src/pager.h @@ -208,10 +208,8 @@ #else # define disable_simulated_io_errors() # define enable_simulated_io_errors() #endif -int sqlite3PagerSetOtaMode(Pager *pPager, int bOta); -void sqlite3PagerWalSalt(Pager *pPager, u32 *aSalt); int sqlite3PagerWalCheckpointStart(sqlite3*, Pager*, u8*, int, sqlite3_ckpt**); #endif /* _PAGER_H_ */ Index: src/parse.y ================================================================== --- src/parse.y +++ src/parse.y @@ -1076,11 +1076,11 @@ A.pExpr = sqlite3PExpr(pParse, N ? TK_NE : TK_EQ, X.pExpr, pRHS, 0); }else{ A.pExpr = sqlite3PExpr(pParse, TK_IN, X.pExpr, 0, 0); if( A.pExpr ){ A.pExpr->x.pList = Y; - sqlite3ExprSetHeight(pParse, A.pExpr); + sqlite3ExprSetHeightAndFlags(pParse, A.pExpr); }else{ sqlite3ExprListDelete(pParse->db, Y); } if( N ) A.pExpr = sqlite3PExpr(pParse, TK_NOT, A.pExpr, 0, 0); } @@ -1089,12 +1089,12 @@ } expr(A) ::= LP(B) select(X) RP(E). { A.pExpr = sqlite3PExpr(pParse, TK_SELECT, 0, 0, 0); if( A.pExpr ){ A.pExpr->x.pSelect = X; - ExprSetProperty(A.pExpr, EP_xIsSelect); - sqlite3ExprSetHeight(pParse, A.pExpr); + ExprSetProperty(A.pExpr, EP_xIsSelect|EP_Subquery); + sqlite3ExprSetHeightAndFlags(pParse, A.pExpr); }else{ sqlite3SelectDelete(pParse->db, X); } A.zStart = B.z; A.zEnd = &E.z[E.n]; @@ -1101,12 +1101,12 @@ } expr(A) ::= expr(X) in_op(N) LP select(Y) RP(E). [IN] { A.pExpr = sqlite3PExpr(pParse, TK_IN, X.pExpr, 0, 0); if( A.pExpr ){ A.pExpr->x.pSelect = Y; - ExprSetProperty(A.pExpr, EP_xIsSelect); - sqlite3ExprSetHeight(pParse, A.pExpr); + ExprSetProperty(A.pExpr, EP_xIsSelect|EP_Subquery); + sqlite3ExprSetHeightAndFlags(pParse, A.pExpr); }else{ sqlite3SelectDelete(pParse->db, Y); } if( N ) A.pExpr = sqlite3PExpr(pParse, TK_NOT, A.pExpr, 0, 0); A.zStart = X.zStart; @@ -1115,12 +1115,12 @@ expr(A) ::= expr(X) in_op(N) nm(Y) dbnm(Z). [IN] { SrcList *pSrc = sqlite3SrcListAppend(pParse->db, 0,&Y,&Z); A.pExpr = sqlite3PExpr(pParse, TK_IN, X.pExpr, 0, 0); if( A.pExpr ){ A.pExpr->x.pSelect = sqlite3SelectNew(pParse, 0,pSrc,0,0,0,0,0,0,0); - ExprSetProperty(A.pExpr, EP_xIsSelect); - sqlite3ExprSetHeight(pParse, A.pExpr); + ExprSetProperty(A.pExpr, EP_xIsSelect|EP_Subquery); + sqlite3ExprSetHeightAndFlags(pParse, A.pExpr); }else{ sqlite3SrcListDelete(pParse->db, pSrc); } if( N ) A.pExpr = sqlite3PExpr(pParse, TK_NOT, A.pExpr, 0, 0); A.zStart = X.zStart; @@ -1128,12 +1128,12 @@ } expr(A) ::= EXISTS(B) LP select(Y) RP(E). { Expr *p = A.pExpr = sqlite3PExpr(pParse, TK_EXISTS, 0, 0, 0); if( p ){ p->x.pSelect = Y; - ExprSetProperty(p, EP_xIsSelect); - sqlite3ExprSetHeight(pParse, p); + ExprSetProperty(p, EP_xIsSelect|EP_Subquery); + sqlite3ExprSetHeightAndFlags(pParse, p); }else{ sqlite3SelectDelete(pParse->db, Y); } A.zStart = B.z; A.zEnd = &E.z[E.n]; @@ -1143,11 +1143,11 @@ /* CASE expressions */ expr(A) ::= CASE(C) case_operand(X) case_exprlist(Y) case_else(Z) END(E). { A.pExpr = sqlite3PExpr(pParse, TK_CASE, X, 0, 0); if( A.pExpr ){ A.pExpr->x.pList = Z ? sqlite3ExprListAppend(pParse,Y,Z) : Y; - sqlite3ExprSetHeight(pParse, A.pExpr); + sqlite3ExprSetHeightAndFlags(pParse, A.pExpr); }else{ sqlite3ExprListDelete(pParse->db, Y); sqlite3ExprDelete(pParse->db, Z); } A.zStart = C.z; Index: src/pragma.c ================================================================== --- src/pragma.c +++ src/pragma.c @@ -420,54 +420,10 @@ } break; } #endif /* !SQLITE_OMIT_PAGER_PRAGMAS && !SQLITE_OMIT_DEPRECATED */ - /* - ** PRAGMA [database.]pager_ota_mode=[01] - ** - ** This pragma sets a flag on the pager associated with the main database - ** only. The flag can only be set when there is no open transaction and - ** the pager does not already have an open WAL file. - ** - ** Once the flag has been set, it is not possible to open a regular WAL - ** file. If, when the next read-transaction is opened, a *-wal file is - ** found or the database header flags indicate that it is a wal-mode - ** database, SQLITE_CANTOPEN is returned. - ** - ** Otherwise, if no WAL file or flags are found, the pager opens the *-oal - ** file and uses it as a write-ahead-log with the *-shm data stored in - ** heap-memory. If the *-oal file already exists but the database file has - ** been modified since it was created, an SQLITE_BUSY_SNAPSHOT error is - ** returned and the read-transaction cannot be opened. - ** - ** Other clients see a rollback-mode database on which the pager_ota_mode - ** client is holding a SHARED lock. - */ -#ifdef SQLITE_ENABLE_OTA - case PragTyp_PAGER_OTA_MODE: { - Btree *pBt = pDb->pBt; - assert( pBt!=0 ); - if( zRight ){ - int iArg = sqlite3Atoi(zRight); - Pager *pPager = sqlite3BtreePager(pBt); - if( sqlite3BtreeIsInReadTrans(pBt) ){ - sqlite3ErrorMsg(pParse, - "cannot set pager_ota_mode with open transaction" - ); - }else if( sqlite3PagerWalSupported(pPager)==0 ){ - sqlite3ErrorMsg(pParse, - "cannot set pager_ota_mode without wal support" - ); - }else if( sqlite3PagerSetOtaMode(sqlite3BtreePager(pBt), iArg) ){ - sqlite3ErrorMsg(pParse, "cannot set pager_ota_mode in wal mode"); - } - } - break; - } -#endif /* SQLITE_ENABLE_OTA */ - #if !defined(SQLITE_OMIT_PAGER_PRAGMAS) /* ** PRAGMA [database.]page_size ** PRAGMA [database.]page_size=N ** Index: src/pragma.h ================================================================== --- src/pragma.h +++ src/pragma.h @@ -26,28 +26,27 @@ #define PragTyp_LOCK_PROXY_FILE 20 #define PragTyp_LOCKING_MODE 21 #define PragTyp_PAGE_COUNT 22 #define PragTyp_MMAP_SIZE 23 #define PragTyp_PAGE_SIZE 24 -#define PragTyp_PAGER_OTA_MODE 25 -#define PragTyp_SECURE_DELETE 26 -#define PragTyp_SHRINK_MEMORY 27 -#define PragTyp_SOFT_HEAP_LIMIT 28 -#define PragTyp_STATS 29 -#define PragTyp_SYNCHRONOUS 30 -#define PragTyp_TABLE_INFO 31 -#define PragTyp_TEMP_STORE 32 -#define PragTyp_TEMP_STORE_DIRECTORY 33 -#define PragTyp_THREADS 34 -#define PragTyp_WAL_AUTOCHECKPOINT 35 -#define PragTyp_WAL_CHECKPOINT 36 -#define PragTyp_ACTIVATE_EXTENSIONS 37 -#define PragTyp_HEXKEY 38 -#define PragTyp_KEY 39 -#define PragTyp_REKEY 40 -#define PragTyp_LOCK_STATUS 41 -#define PragTyp_PARSER_TRACE 42 +#define PragTyp_SECURE_DELETE 25 +#define PragTyp_SHRINK_MEMORY 26 +#define PragTyp_SOFT_HEAP_LIMIT 27 +#define PragTyp_STATS 28 +#define PragTyp_SYNCHRONOUS 29 +#define PragTyp_TABLE_INFO 30 +#define PragTyp_TEMP_STORE 31 +#define PragTyp_TEMP_STORE_DIRECTORY 32 +#define PragTyp_THREADS 33 +#define PragTyp_WAL_AUTOCHECKPOINT 34 +#define PragTyp_WAL_CHECKPOINT 35 +#define PragTyp_ACTIVATE_EXTENSIONS 36 +#define PragTyp_HEXKEY 37 +#define PragTyp_KEY 38 +#define PragTyp_REKEY 39 +#define PragTyp_LOCK_STATUS 40 +#define PragTyp_PARSER_TRACE 41 #define PragFlag_NeedSchema 0x01 #define PragFlag_ReadOnly 0x02 static const struct sPragmaNames { const char *const zName; /* Name of pragma */ u8 ePragTyp; /* PragTyp_XXX value */ @@ -302,16 +301,10 @@ { /* zName: */ "page_size", /* ePragTyp: */ PragTyp_PAGE_SIZE, /* ePragFlag: */ 0, /* iArg: */ 0 }, #endif -#if defined(SQLITE_ENABLE_OTA) - { /* zName: */ "pager_ota_mode", - /* ePragTyp: */ PragTyp_PAGER_OTA_MODE, - /* ePragFlag: */ 0, - /* iArg: */ 0 }, -#endif #if defined(SQLITE_DEBUG) { /* zName: */ "parser_trace", /* ePragTyp: */ PragTyp_PARSER_TRACE, /* ePragFlag: */ 0, /* iArg: */ 0 }, @@ -461,6 +454,6 @@ /* ePragTyp: */ PragTyp_FLAG, /* ePragFlag: */ 0, /* iArg: */ SQLITE_WriteSchema|SQLITE_RecoveryMode }, #endif }; -/* Number of pragmas: 59 on by default, 73 total. */ +/* Number of pragmas: 59 on by default, 72 total. */ Index: src/resolve.c ================================================================== --- src/resolve.c +++ src/resolve.c @@ -245,13 +245,14 @@ */ if( zDb ){ testcase( pNC->ncFlags & NC_PartIdx ); testcase( pNC->ncFlags & NC_IsCheck ); if( (pNC->ncFlags & (NC_PartIdx|NC_IsCheck))!=0 ){ - /* Silently ignore database qualifiers inside CHECK constraints and partial - ** indices. Do not raise errors because that might break legacy and - ** because it does not hurt anything to just ignore the database name. */ + /* Silently ignore database qualifiers inside CHECK constraints and + ** partial indices. Do not raise errors because that might break + ** legacy and because it does not hurt anything to just ignore the + ** database name. */ zDb = 0; }else{ for(i=0; inDb; i++){ assert( db->aDb[i].zName ); if( sqlite3StrICmp(db->aDb[i].zName,zDb)==0 ){ @@ -318,11 +319,12 @@ } } if( pMatch ){ pExpr->iTable = pMatch->iCursor; pExpr->pTab = pMatch->pTab; - assert( (pMatch->jointype & JT_RIGHT)==0 ); /* RIGHT JOIN not (yet) supported */ + /* RIGHT JOIN not (yet) supported */ + assert( (pMatch->jointype & JT_RIGHT)==0 ); if( (pMatch->jointype & JT_LEFT)!=0 ){ ExprSetProperty(pExpr, EP_CanBeNull); } pSchema = pExpr->pTab->pSchema; } @@ -639,11 +641,12 @@ pExpr->iTable = pItem->iCursor; pExpr->iColumn = -1; pExpr->affinity = SQLITE_AFF_INTEGER; break; } -#endif /* defined(SQLITE_ENABLE_UPDATE_DELETE_LIMIT) && !defined(SQLITE_OMIT_SUBQUERY) */ +#endif /* defined(SQLITE_ENABLE_UPDATE_DELETE_LIMIT) + && !defined(SQLITE_OMIT_SUBQUERY) */ /* A lone identifier is the name of a column. */ case TK_ID: { return lookupName(pParse, 0, 0, pExpr->u.zToken, pNC, pExpr); @@ -704,23 +707,24 @@ if( pDef->funcFlags & SQLITE_FUNC_UNLIKELY ){ ExprSetProperty(pExpr, EP_Unlikely|EP_Skip); if( n==2 ){ pExpr->iTable = exprProbability(pList->a[1].pExpr); if( pExpr->iTable<0 ){ - sqlite3ErrorMsg(pParse, "second argument to likelihood() must be a " - "constant between 0.0 and 1.0"); + sqlite3ErrorMsg(pParse, + "second argument to likelihood() must be a " + "constant between 0.0 and 1.0"); pNC->nErr++; } }else{ - /* EVIDENCE-OF: R-61304-29449 The unlikely(X) function is equivalent to - ** likelihood(X, 0.0625). - ** EVIDENCE-OF: R-01283-11636 The unlikely(X) function is short-hand for - ** likelihood(X,0.0625). - ** EVIDENCE-OF: R-36850-34127 The likely(X) function is short-hand for - ** likelihood(X,0.9375). - ** EVIDENCE-OF: R-53436-40973 The likely(X) function is equivalent to - ** likelihood(X,0.9375). */ + /* EVIDENCE-OF: R-61304-29449 The unlikely(X) function is + ** equivalent to likelihood(X, 0.0625). + ** EVIDENCE-OF: R-01283-11636 The unlikely(X) function is + ** short-hand for likelihood(X,0.0625). + ** EVIDENCE-OF: R-36850-34127 The likely(X) function is short-hand + ** for likelihood(X,0.9375). + ** EVIDENCE-OF: R-53436-40973 The likely(X) function is equivalent + ** to likelihood(X,0.9375). */ /* TUNING: unlikely() probability is 0.0625. likely() is 0.9375 */ pExpr->iTable = pDef->zName[0]=='u' ? 8388608 : 125829120; } } #ifndef SQLITE_OMIT_AUTHORIZATION @@ -733,11 +737,13 @@ } pExpr->op = TK_NULL; return WRC_Prune; } #endif - if( pDef->funcFlags & SQLITE_FUNC_CONSTANT ) ExprSetProperty(pExpr,EP_Constant); + if( pDef->funcFlags & SQLITE_FUNC_CONSTANT ){ + ExprSetProperty(pExpr,EP_ConstFunc); + } } if( is_agg && (pNC->ncFlags & NC_AllowAgg)==0 ){ sqlite3ErrorMsg(pParse, "misuse of aggregate function %.*s()", nId,zId); pNC->nErr++; is_agg = 0; @@ -1044,11 +1050,12 @@ if( pItem->u.x.iOrderByCol ){ if( pItem->u.x.iOrderByCol>pEList->nExpr ){ resolveOutOfRangeError(pParse, zType, i+1, pEList->nExpr); return 1; } - resolveAlias(pParse, pEList, pItem->u.x.iOrderByCol-1, pItem->pExpr, zType,0); + resolveAlias(pParse, pEList, pItem->u.x.iOrderByCol-1, pItem->pExpr, + zType,0); } } return 0; } Index: src/select.c ================================================================== --- src/select.c +++ src/select.c @@ -3192,11 +3192,14 @@ ** ** Flattening is only attempted if all of the following are true: ** ** (1) The subquery and the outer query do not both use aggregates. ** -** (2) The subquery is not an aggregate or the outer query is not a join. +** (2) The subquery is not an aggregate or (2a) the outer query is not a join +** and (2b) the outer query does not use subqueries other than the one +** FROM-clause subquery that is a candidate for flattening. (2b is +** due to ticket [2f7170d73bf9abf80] from 2015-02-09.) ** ** (3) The subquery is not the right operand of a left outer join ** (Originally ticket #306. Strengthened by ticket #3300) ** ** (4) The subquery is not DISTINCT. @@ -3329,12 +3332,21 @@ assert( pSrc && iFrom>=0 && iFromnSrc ); pSubitem = &pSrc->a[iFrom]; iParent = pSubitem->iCursor; pSub = pSubitem->pSelect; assert( pSub!=0 ); - if( isAgg && subqueryIsAgg ) return 0; /* Restriction (1) */ - if( subqueryIsAgg && pSrc->nSrc>1 ) return 0; /* Restriction (2) */ + if( subqueryIsAgg ){ + if( isAgg ) return 0; /* Restriction (1) */ + if( pSrc->nSrc>1 ) return 0; /* Restriction (2a) */ + if( (p->pWhere && ExprHasProperty(p->pWhere,EP_Subquery)) + || (sqlite3ExprListFlags(p->pEList) & EP_Subquery)!=0 + || (sqlite3ExprListFlags(p->pOrderBy) & EP_Subquery)!=0 + ){ + return 0; /* Restriction (2b) */ + } + } + pSubSrc = pSub->pSrc; assert( pSubSrc ); /* Prior to version 3.1.2, when LIMIT and OFFSET had to be simple constants, ** not arbitrary expressions, we allowed some combining of LIMIT and OFFSET ** because they could be computed at compile-time. But when LIMIT and OFFSET @@ -4750,10 +4762,17 @@ if( pParse->nErr || db->mallocFailed ){ goto select_end; } isAgg = (p->selFlags & SF_Aggregate)!=0; assert( pEList!=0 ); +#if SELECTTRACE_ENABLED + if( sqlite3SelectTrace & 0x100 ){ + SELECTTRACE(0x100,pParse,p, ("after name resolution:\n")); + sqlite3TreeViewSelect(0, p, 0); + } +#endif + /* Begin generating code. */ v = sqlite3GetVdbe(pParse); if( v==0 ) goto select_end; @@ -5495,13 +5514,13 @@ ** Generate a human-readable description of a the Select object. */ void sqlite3TreeViewSelect(TreeView *pView, const Select *p, u8 moreToFollow){ int n = 0; pView = sqlite3TreeViewPush(pView, moreToFollow); - sqlite3TreeViewLine(pView, "SELECT%s%s", + sqlite3TreeViewLine(pView, "SELECT%s%s (0x%p)", ((p->selFlags & SF_Distinct) ? " DISTINCT" : ""), - ((p->selFlags & SF_Aggregate) ? " agg_flag" : "") + ((p->selFlags & SF_Aggregate) ? " agg_flag" : ""), p ); if( p->pSrc && p->pSrc->nSrc ) n++; if( p->pWhere ) n++; if( p->pGroupBy ) n++; if( p->pHaving ) n++; Index: src/sqlite.h.in ================================================================== --- src/sqlite.h.in +++ src/sqlite.h.in @@ -968,10 +968,11 @@ #define SQLITE_FCNTL_HAS_MOVED 20 #define SQLITE_FCNTL_SYNC 21 #define SQLITE_FCNTL_COMMIT_PHASETWO 22 #define SQLITE_FCNTL_WIN32_SET_HANDLE 23 #define SQLITE_FCNTL_ZIPVFS_PAGER 24 +#define SQLITE_FCNTL_OTA 25 /* ** CAPI3REF: Mutex Handle ** ** The mutex module within SQLite defines [sqlite3_mutex] to be an Index: src/sqliteInt.h ================================================================== --- src/sqliteInt.h +++ src/sqliteInt.h @@ -1204,10 +1204,11 @@ #define SQLITE_LoadExtension 0x00400000 /* Enable load_extension */ #define SQLITE_EnableTrigger 0x00800000 /* True to enable triggers */ #define SQLITE_DeferFKs 0x01000000 /* Defer all FK constraints */ #define SQLITE_QueryOnly 0x02000000 /* Disable database changes */ #define SQLITE_VdbeEQP 0x04000000 /* Debug EXPLAIN QUERY PLAN */ + /* ** Bits of the sqlite3.dbOptFlags field that are used by the ** sqlite3_test_control(SQLITE_TESTCTRL_OPTIMIZATIONS,...) interface to ** selectively disable various optimizations. @@ -2041,12 +2042,18 @@ #define EP_TokenOnly 0x004000 /* Expr struct EXPR_TOKENONLYSIZE bytes only */ #define EP_Static 0x008000 /* Held in memory not obtained from malloc() */ #define EP_MemToken 0x010000 /* Need to sqlite3DbFree() Expr.zToken */ #define EP_NoReduce 0x020000 /* Cannot EXPRDUP_REDUCE this Expr */ #define EP_Unlikely 0x040000 /* unlikely() or likelihood() function */ -#define EP_Constant 0x080000 /* Node is a constant */ +#define EP_ConstFunc 0x080000 /* Node is a SQLITE_FUNC_CONSTANT function */ #define EP_CanBeNull 0x100000 /* Can be null despite NOT NULL constraint */ +#define EP_Subquery 0x200000 /* Tree contains a TK_SELECT operator */ + +/* +** Combinations of two or more EP_* flags +*/ +#define EP_Propagate (EP_Collate|EP_Subquery) /* Propagate these bits up tree */ /* ** These macros can be used to test, set, or clear bits in the ** Expr.flags field. */ @@ -3150,10 +3157,11 @@ void sqlite3ExprDelete(sqlite3*, Expr*); ExprList *sqlite3ExprListAppend(Parse*,ExprList*,Expr*); void sqlite3ExprListSetName(Parse*,ExprList*,Token*,int); void sqlite3ExprListSetSpan(Parse*,ExprList*,ExprSpan*); void sqlite3ExprListDelete(sqlite3*, ExprList*); +u32 sqlite3ExprListFlags(const ExprList*); int sqlite3Init(sqlite3*, char**); int sqlite3InitCallback(void*, int, char**, char**); void sqlite3Pragma(Parse*,Token*,Token*,Token*,int); void sqlite3ResetAllSchemasOfConnection(sqlite3*); void sqlite3ResetOneSchema(sqlite3*,int); @@ -3733,16 +3741,15 @@ void sqlite3MemJournalOpen(sqlite3_file *); int sqlite3MemJournalSize(void); int sqlite3IsMemJournal(sqlite3_file *); +void sqlite3ExprSetHeightAndFlags(Parse *pParse, Expr *p); #if SQLITE_MAX_EXPR_DEPTH>0 - void sqlite3ExprSetHeight(Parse *pParse, Expr *p); int sqlite3SelectExprHeight(Select *); int sqlite3ExprCheckHeight(Parse*, int); #else - #define sqlite3ExprSetHeight(x,y) #define sqlite3SelectExprHeight(x) 0 #define sqlite3ExprCheckHeight(x,y) #endif u32 sqlite3Get4byte(const u8*); @@ -3815,11 +3822,10 @@ #endif #define MEMTYPE_HEAP 0x01 /* General heap allocations */ #define MEMTYPE_LOOKASIDE 0x02 /* Heap that might have been lookaside */ #define MEMTYPE_SCRATCH 0x04 /* Scratch allocations */ #define MEMTYPE_PCACHE 0x08 /* Page cache allocations */ - /* ** Threading interface */ #if SQLITE_MAX_WORKER_THREADS>0 Index: src/test1.c ================================================================== --- src/test1.c +++ src/test1.c @@ -6926,11 +6926,10 @@ { "sqlite3_stmt_scanstatus", test_stmt_scanstatus, 0 }, { "sqlite3_stmt_scanstatus_reset", test_stmt_scanstatus_reset, 0 }, #endif }; - static int bitmask_size = sizeof(Bitmask)*8; int i; extern int sqlite3_sync_count, sqlite3_fullsync_count; extern int sqlite3_opentemp_count; extern int sqlite3_like_count; Index: src/vdbeblob.c ================================================================== --- src/vdbeblob.c +++ src/vdbeblob.c @@ -152,16 +152,21 @@ Table *pTab; Parse *pParse = 0; Incrblob *pBlob = 0; #ifdef SQLITE_ENABLE_API_ARMOR - if( !sqlite3SafetyCheckOk(db) || ppBlob==0 || zTable==0 ){ + if( ppBlob==0 ){ + return SQLITE_MISUSE_BKPT; + } +#endif + *ppBlob = 0; +#ifdef SQLITE_ENABLE_API_ARMOR + if( !sqlite3SafetyCheckOk(db) || zTable==0 ){ return SQLITE_MISUSE_BKPT; } #endif flags = !!flags; /* flags = (flags ? 1 : 0); */ - *ppBlob = 0; sqlite3_mutex_enter(db->mutex); pBlob = (Incrblob *)sqlite3DbMallocZero(db, sizeof(Incrblob)); if( !pBlob ) goto blob_open_out; @@ -371,11 +376,11 @@ if( p==0 ) return SQLITE_MISUSE_BKPT; db = p->db; sqlite3_mutex_enter(db->mutex); v = (Vdbe*)p->pStmt; - if( n<0 || iOffset<0 || (iOffset+n)>p->nByte ){ + if( n<0 || iOffset<0 || ((sqlite3_int64)iOffset+n)>p->nByte ){ /* Request is out of range. Return a transient error. */ rc = SQLITE_ERROR; }else if( v==0 ){ /* If there is no statement handle, then the blob-handle has ** already been invalidated. Return SQLITE_ABORT in this case. Index: src/wal.c ================================================================== --- src/wal.c +++ src/wal.c @@ -2918,13 +2918,11 @@ sqlite3Put4byte(&aWalHdr[0], (WAL_MAGIC | SQLITE_BIGENDIAN)); sqlite3Put4byte(&aWalHdr[4], WAL_MAX_VERSION); sqlite3Put4byte(&aWalHdr[8], szPage); sqlite3Put4byte(&aWalHdr[12], pWal->nCkpt); - if( pWal->nCkpt==0 ){ - sqlite3PagerWalSalt(pList->pPager, pWal->hdr.aSalt); - } + if( pWal->nCkpt==0 ) sqlite3_randomness(8, pWal->hdr.aSalt); memcpy(&aWalHdr[16], pWal->hdr.aSalt, 8); walChecksumBytes(1, aWalHdr, WAL_HDRSIZE-2*4, 0, aCksum); sqlite3Put4byte(&aWalHdr[24], aCksum[0]); sqlite3Put4byte(&aWalHdr[28], aCksum[1]); Index: test/collate8.test ================================================================== --- test/collate8.test +++ test/collate8.test @@ -11,11 +11,13 @@ #*********************************************************************** # This file implements regression tests for SQLite library. The # focus of this script is making sure collations pass through the # unary + operator. # -# $Id: collate8.test,v 1.2 2008/08/25 12:14:09 drh Exp $ +# 2015-02-09: Added tests to make sure COLLATE passes through function +# calls. Ticket [ca0d20b6cdddec5e81b8d66f89c46a5583b5f6f6]. +# set testdir [file dirname $argv0] source $testdir/tester.tcl do_test collate8-1.1 { @@ -119,7 +121,37 @@ do_test collate8-2.8 { execsql { SELECT a COLLATE nocase AS x FROM t2 WHERE 'abc'=x COLLATE binary; } } {abc} + +# Make sure the COLLATE operator perculates up through function calls +# and other Expr structures that use the Expr.x.pList field. +# +do_execsql_test collate8-3.1 { + SELECT 'abc'==('ABC'||'') COLLATE nocase; + SELECT 'abc'==('ABC'||'' COLLATE nocase); + SELECT 'abc'==('ABC'||('' COLLATE nocase)); + SELECT 'abc'==('ABC'||upper('' COLLATE nocase)); +} {1 1 1 1} +do_execsql_test collate8-3.2 { + SELECT 'abc'==('ABC'||max('' COLLATE nocase,'' COLLATE binary)); +} {1} + +# The COLLATE binary is on the left and so takes precedence +do_execsql_test collate8-3.3 { + SELECT 'abc'==('ABC'||max('' COLLATE binary,'' COLLATE nocase)); +} {0} + +do_execsql_test collate8-3.4 { + SELECT 'abc'==('ABC'||CASE WHEN 1-1=2 THEN '' COLLATE nocase + ELSE '' COLLATE binary END); + SELECT 'abc'==('ABC'||CASE WHEN 1+1=2 THEN '' COLLATE nocase + ELSE '' COLLATE binary END); +} {1 1} +do_execsql_test collate8-3.5 { + SELECT 'abc'==('ABC'||CASE WHEN 1=2 THEN '' COLLATE binary + ELSE '' COLLATE nocase END); +} {0} + finish_test Index: test/incrblob2.test ================================================================== --- test/incrblob2.test +++ test/incrblob2.test @@ -321,16 +321,38 @@ do_test incrblob2-6.2 { set rdHandle [db incrblob -readonly t1 data 1] sqlite3_blob_read $rdHandle 0 2 } {AB} + +do_test incrblob2-6.2b { + set rc [catch { + # Prior to 2015-02-07, the following caused a segfault due to + # integer overflow. + sqlite3_blob_read $rdHandle 2147483647 2147483647 + } errmsg] + lappend rc $errmsg +} {1 SQLITE_ERROR} do_test incrblob2-6.3 { set wrHandle [db incrblob t1 data 1] sqlite3_blob_write $wrHandle 0 ZZZZZZZZZZ sqlite3_blob_read $rdHandle 2 4 } {ZZZZ} + +do_test incrblob2-6.3b { + set rc [catch { + # Prior to 2015-02-07, the following caused a segfault due to + # integer overflow. + sqlite3_blob_write $wrHandle 2147483647 YYYYYYYYYYYYYYYYYY + } errmsg] + lappend rc $errmsg +} {1 SQLITE_ERROR} +do_test incrblob2-6.3c { + sqlite3_blob_read $rdHandle 2 4 +} {ZZZZ} + do_test incrblob2-6.4 { close $wrHandle close $rdHandle } {} Index: test/select6.test ================================================================== --- test/select6.test +++ test/select6.test @@ -554,8 +554,64 @@ SELECT * FROM k UNION ALL SELECT * FROM t UNION ALL SELECT l,m,l FROM j ) } $err + +# 2015-02-09 Ticket [2f7170d73bf9abf80339187aa3677dce3dbcd5ca] +# "misuse of aggregate" error if aggregate column from FROM +# subquery is used in correlated subquery +# +do_execsql_test 11.1 { + DROP TABLE IF EXISTS t1; + CREATE TABLE t1(w INT, x INT); + INSERT INTO t1(w,x) + VALUES(1,10),(2,20),(3,30), + (2,21),(3,31), + (3,32); + CREATE INDEX t1wx ON t1(w,x); + + DROP TABLE IF EXISTS t2; + CREATE TABLE t2(w INT, y VARCHAR(8)); + INSERT INTO t2(w,y) VALUES(1,'one'),(2,'two'),(3,'three'),(4,'four'); + CREATE INDEX t2wy ON t2(w,y); + + SELECT cnt, xyz, (SELECT y FROM t2 WHERE w=cnt), '|' + FROM (SELECT count(*) AS cnt, w AS xyz FROM t1 GROUP BY 2) + ORDER BY cnt, xyz; +} {1 1 one | 2 2 two | 3 3 three |} +do_execsql_test 11.2 { + SELECT cnt, xyz, lower((SELECT y FROM t2 WHERE w=cnt)), '|' + FROM (SELECT count(*) AS cnt, w AS xyz FROM t1 GROUP BY 2) + ORDER BY cnt, xyz; +} {1 1 one | 2 2 two | 3 3 three |} +do_execsql_test 11.3 { + SELECT cnt, xyz, '|' + FROM (SELECT count(*) AS cnt, w AS xyz FROM t1 GROUP BY 2) + WHERE (SELECT y FROM t2 WHERE w=cnt)!='two' + ORDER BY cnt, xyz; +} {1 1 | 3 3 |} +do_execsql_test 11.4 { + SELECT cnt, xyz, '|' + FROM (SELECT count(*) AS cnt, w AS xyz FROM t1 GROUP BY 2) + ORDER BY lower((SELECT y FROM t2 WHERE w=cnt)); +} {1 1 | 3 3 | 2 2 |} +do_execsql_test 11.5 { + SELECT cnt, xyz, + CASE WHEN (SELECT y FROM t2 WHERE w=cnt)=='two' + THEN 'aaa' ELSE 'bbb' + END, '|' + FROM (SELECT count(*) AS cnt, w AS xyz FROM t1 GROUP BY 2) + ORDER BY +cnt; +} {1 1 bbb | 2 2 aaa | 3 3 bbb |} + +do_execsql_test 11.100 { + DROP TABLE t1; + DROP TABLE t2; + CREATE TABLE t1(x); + CREATE TABLE t2(y, z); + SELECT ( SELECT y FROM t2 WHERE z = cnt ) + FROM ( SELECT count(*) AS cnt FROM t1 ); +} {{}} finish_test Index: tool/mkpragmatab.tcl ================================================================== --- tool/mkpragmatab.tcl +++ tool/mkpragmatab.tcl @@ -313,13 +313,10 @@ IF: defined(SQLITE_HAS_CODEC) || defined(SQLITE_ENABLE_CEROD) NAME: soft_heap_limit NAME: threads - - NAME: pager_ota_mode - IF: defined(SQLITE_ENABLE_OTA) } # Open the output file # set destfile "[file dir [file dir [file normal $argv0]]]/src/pragma.h" Index: tool/mksqlite3c.tcl ================================================================== --- tool/mksqlite3c.tcl +++ tool/mksqlite3c.tcl @@ -110,12 +110,13 @@ pager.h parse.h pcache.h pragma.h rtree.h - sqlite3ext.h sqlite3.h + sqlite3ext.h + sqlite3ota.h sqliteicu.h sqliteInt.h sqliteLimit.h vdbe.h vdbeInt.h @@ -332,10 +333,11 @@ fts3_unicode2.c rtree.c icu.c fts3_icu.c + sqlite3ota.c } { copy_file tsrc/$file } close $out Index: tool/spaceanal.tcl ================================================================== --- tool/spaceanal.tcl +++ tool/spaceanal.tcl @@ -2,10 +2,28 @@ # how much disk space is used by a particular data to actually store data # versus how much space is unused. # if {[catch { + +# Argument $tname is the name of a table within the database opened by +# database handle [db]. Return true if it is a WITHOUT ROWID table, or +# false otherwise. +# +proc is_without_rowid {tname} { + set t [string map {' ''} $tname] + db eval "PRAGMA index_list = '$t'" o { + if {$o(origin) == "pk"} { + set n $o(name) + if {0==[db one { SELECT count(*) FROM sqlite_master WHERE name=$n }]} { + return 1 + } + } + } + return 0 +} + # Get the name of the database to analyze # proc usage {} { set argv0 [file rootname [file tail [info nameofexecutable]]] puts stderr "Usage: $argv0 database-name" @@ -165,24 +183,25 @@ set compressOverhead 0 set sql { SELECT name, tbl_name FROM sqlite_master WHERE rootpage>0 } foreach {name tblname} [concat sqlite_master sqlite_master [db eval $sql]] { set is_index [expr {$name!=$tblname}] + set idx_btree [expr {$is_index || [is_without_rowid $name]}] db eval { SELECT sum(ncell) AS nentry, - sum(isleaf(pagetype, $is_index) * ncell) AS leaf_entries, + sum(isleaf(pagetype, $idx_btree) * ncell) AS leaf_entries, sum(payload) AS payload, - sum(isoverflow(pagetype, $is_index) * payload) AS ovfl_payload, + sum(isoverflow(pagetype, $idx_btree) * payload) AS ovfl_payload, sum(path LIKE '%+000000') AS ovfl_cnt, max(mx_payload) AS mx_payload, - sum(isinternal(pagetype, $is_index)) AS int_pages, - sum(isleaf(pagetype, $is_index)) AS leaf_pages, - sum(isoverflow(pagetype, $is_index)) AS ovfl_pages, - sum(isinternal(pagetype, $is_index) * unused) AS int_unused, - sum(isleaf(pagetype, $is_index) * unused) AS leaf_unused, - sum(isoverflow(pagetype, $is_index) * unused) AS ovfl_unused, + sum(isinternal(pagetype, $idx_btree)) AS int_pages, + sum(isleaf(pagetype, $idx_btree)) AS leaf_pages, + sum(isoverflow(pagetype, $idx_btree)) AS ovfl_pages, + sum(isinternal(pagetype, $idx_btree) * unused) AS int_unused, + sum(isleaf(pagetype, $idx_btree) * unused) AS leaf_unused, + sum(isoverflow(pagetype, $idx_btree) * unused) AS ovfl_unused, sum(pgsize) AS compressed_size FROM temp.dbstat WHERE name = $name } break set total_pages [expr {$leaf_pages+$int_pages+$ovfl_pages}]