diff options
| -rw-r--r-- | ext/ota/ota.c | 107 | ||||
| -rw-r--r-- | ext/ota/ota1.test | 570 | ||||
| -rw-r--r-- | ext/ota/ota10.test | 188 | ||||
| -rw-r--r-- | ext/ota/ota11.test | 198 | ||||
| -rw-r--r-- | ext/ota/ota12.test | 172 | ||||
| -rw-r--r-- | ext/ota/ota3.test | 207 | ||||
| -rw-r--r-- | ext/ota/ota5.test | 331 | ||||
| -rw-r--r-- | ext/ota/ota6.test | 103 | ||||
| -rw-r--r-- | ext/ota/ota7.test | 110 | ||||
| -rw-r--r-- | ext/ota/ota8.test | 75 | ||||
| -rw-r--r-- | ext/ota/ota9.test | 128 | ||||
| -rw-r--r-- | ext/ota/otaA.test | 83 | ||||
| -rw-r--r-- | ext/ota/otacrash.test | 141 | ||||
| -rw-r--r-- | ext/ota/otafault.test | 237 | ||||
| -rw-r--r-- | ext/ota/otafault2.test | 58 | ||||
| -rw-r--r-- | ext/ota/sqlite3ota.c | 3484 | ||||
| -rw-r--r-- | ext/ota/sqlite3ota.h | 369 | ||||
| -rw-r--r-- | ext/ota/test_ota.c | 245 | ||||
| -rw-r--r-- | main.mk | 16 | ||||
| -rw-r--r-- | manifest | 47 | ||||
| -rw-r--r-- | manifest.uuid | 2 | ||||
| -rw-r--r-- | src/sqlite.h.in | 6 | ||||
| -rw-r--r-- | src/tclsqlite.c | 2 | ||||
| -rw-r--r-- | src/test_config.c | 6 | ||||
| -rw-r--r-- | test/ota.test | 18 | ||||
| -rw-r--r-- | test/permutations.test | 8 | ||||
| -rw-r--r-- | test/pragma.test | 1 | ||||
| -rw-r--r-- | test/releasetest.tcl | 1 | ||||
| -rw-r--r-- | tool/mksqlite3c.tcl | 4 |
29 files changed, 6897 insertions, 20 deletions
diff --git a/ext/ota/ota.c b/ext/ota/ota.c new file mode 100644 index 000000000..febdbfe2d --- /dev/null +++ b/ext/ota/ota.c @@ -0,0 +1,107 @@ +/* +** 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 a command-line application that uses the OTA +** extension. See the usage() function below for an explanation. +*/ + +#include "sqlite3ota.h" +#include <stdio.h> +#include <stdlib.h> +#include <string.h> + +/* +** Print a usage message and exit. +*/ +void usage(const char *zArgv0){ + fprintf(stderr, +"Usage: %s [-step NSTEP] TARGET-DB OTA-DB\n" +"\n" +" Argument OTA-DB must be an OTA database containing an update suitable for\n" +" target database TARGET-DB. If NSTEP is set to less than or equal to zero\n" +" (the default value), this program attempts to apply the entire update to\n" +" the target database.\n" +"\n" +" If NSTEP is greater than zero, then a maximum of NSTEP calls are made\n" +" to sqlite3ota_step(). If the OTA update has not been completely applied\n" +" after the NSTEP'th call is made, the state is saved in the database OTA-DB\n" +" and the program exits. Subsequent invocations of this (or any other OTA)\n" +" application will use this state to resume applying the OTA update to the\n" +" target db.\n" +"\n" +, zArgv0); + exit(1); +} + +void report_default_vfs(){ + sqlite3_vfs *pVfs = sqlite3_vfs_find(0); + fprintf(stdout, "using vfs \"%s\"\n", pVfs->zName); +} + +int main(int argc, char **argv){ + int i; + const char *zTarget; /* Target database to apply OTA to */ + const char *zOta; /* Database containing OTA */ + char *zErrmsg; /* Error message, if any */ + sqlite3ota *pOta; /* OTA handle */ + int nStep = 0; /* Maximum number of step() calls */ + int rc; + sqlite3_int64 nProgress = 0; + + /* Process command line arguments. Following this block local variables + ** zTarget, zOta and nStep are all set. */ + if( argc==5 ){ + int nArg1 = strlen(argv[1]); + if( nArg1>5 || nArg1<2 || memcmp("-step", argv[1], nArg1) ) usage(argv[0]); + nStep = atoi(argv[2]); + }else if( argc!=3 ){ + usage(argv[0]); + } + zTarget = argv[argc-2]; + zOta = argv[argc-1]; + + report_default_vfs(); + + /* Open an OTA handle. If nStep is less than or equal to zero, call + ** sqlite3ota_step() until either the OTA has been completely applied + ** or an error occurs. Or, if nStep is greater than zero, call + ** sqlite3ota_step() a maximum of nStep times. */ + pOta = sqlite3ota_open(zTarget, zOta); + for(i=0; (nStep<=0 || i<nStep) && sqlite3ota_step(pOta)==SQLITE_OK; i++); + nProgress = sqlite3ota_progress(pOta); + rc = sqlite3ota_close(pOta, &zErrmsg); + + /* Let the user know what happened. */ + switch( rc ){ + case SQLITE_OK: + fprintf(stdout, + "SQLITE_OK: ota update incomplete (%lld operations so far)\n", + nProgress + ); + break; + + case SQLITE_DONE: + fprintf(stdout, + "SQLITE_DONE: ota update completed (%lld operations)\n", + nProgress + ); + break; + + default: + fprintf(stderr, "error=%d: %s\n", rc, zErrmsg); + break; + } + + sqlite3_free(zErrmsg); + return (rc==SQLITE_OK || rc==SQLITE_DONE) ? 0 : 1; +} + diff --git a/ext/ota/ota1.test b/ext/ota/ota1.test new file mode 100644 index 000000000..a2ebd56c4 --- /dev/null +++ b/ext/ota/ota1.test @@ -0,0 +1,570 @@ +# 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 ota1 + +db close +sqlite3_shutdown +sqlite3_config_uri 1 + +# Create a simple OTA database. That expects to write to a table: +# +# CREATE TABLE t1(a INTEGER PRIMARY KEY, b, c); +# +proc create_ota1 {filename} { + forcedelete $filename + sqlite3 ota1 $filename + ota1 eval { + CREATE TABLE data_t1(a, b, c, ota_control); + INSERT INTO data_t1 VALUES(1, 2, 3, 0); + INSERT INTO data_t1 VALUES(2, 'two', 'three', 0); + INSERT INTO data_t1 VALUES(3, NULL, 8.2, 0); + } + ota1 close + return $filename +} + +# Create a simple OTA database. That expects to write to a table: +# +# CREATE TABLE t1(a INTEGER PRIMARY KEY, b, c); +# +# This OTA includes both insert and delete operations. +# +proc create_ota4 {filename} { + forcedelete $filename + sqlite3 ota1 $filename + ota1 eval { + CREATE TABLE data_t1(a, b, c, ota_control); + INSERT INTO data_t1 VALUES(1, 2, 3, 0); + INSERT INTO data_t1 VALUES(2, NULL, 5, 1); + INSERT INTO data_t1 VALUES(3, 8, 9, 0); + INSERT INTO data_t1 VALUES(4, NULL, 11, 1); + } + ota1 close + return $filename +} +# +# Create a simple OTA database. That expects to write to a table: +# +# CREATE TABLE t1(c, b, '(a)' INTEGER PRIMARY KEY); +# +# This OTA includes both insert and delete operations. +# +proc create_ota4b {filename} { + forcedelete $filename + sqlite3 ota1 $filename + ota1 eval { + CREATE TABLE data_t1(c, b, '(a)', ota_control); + INSERT INTO data_t1 VALUES(3, 2, 1, 0); + INSERT INTO data_t1 VALUES(5, NULL, 2, 1); + INSERT INTO data_t1 VALUES(9, 8, 3, 0); + INSERT INTO data_t1 VALUES(11, NULL, 4, 1); + } + ota1 close + return $filename +} + +# Create a simple OTA database. That expects to write to a table: +# +# CREATE TABLE t1(a INTEGER PRIMARY KEY, b, c, d); +# +# This OTA includes update statements. +# +proc create_ota5 {filename} { + forcedelete $filename + sqlite3 ota5 $filename + ota5 eval { + CREATE TABLE data_t1(a, b, c, d, ota_control); + INSERT INTO data_t1 VALUES(1, NULL, NULL, 5, '...x'); -- SET d = 5 + INSERT INTO data_t1 VALUES(2, NULL, 10, 5, '..xx'); -- SET c=10, d = 5 + INSERT INTO data_t1 VALUES(3, 11, NULL, NULL, '.x..'); -- SET b=11 + } + ota5 close + return $filename +} + +# Run the OTA in file $ota on target database $target until completion. +# +proc run_ota {target ota} { + sqlite3ota ota $target $ota + while 1 { + set rc [ota step] + if {$rc!="SQLITE_OK"} break + } + ota close +} + +proc step_ota {target ota} { + while 1 { + sqlite3ota ota $target $ota + set rc [ota step] + ota close + if {$rc != "SQLITE_OK"} break + } + set rc +} + +# 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=&abc=123 $ota + set rc [ota step] + ota close + if {$rc != "SQLITE_OK"} break + } + set rc +} + +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 + $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(c, b, '(a)' INTEGER PRIMARY KEY); + CREATE INDEX i1 ON t1(c, b); + } + 2 { + CREATE TABLE t1(c, b, '(a)' PRIMARY KEY); + } + 3 { + CREATE TABLE t1(c, b, '(a)' PRIMARY KEY) WITHOUT ROWID; + } + } { + reset_db + execsql $schema + execsql { + INSERT INTO t1('(a)', b, c) VALUES(2, 'hello', 'world'); + INSERT INTO t1('(a)', b, c) VALUES(4, 'hello', 'planet'); + INSERT INTO t1('(a)', b, c) VALUES(6, 'hello', 'xyz'); + } + + do_test $tn3.4.$tn2.$tn.1 { + create_ota4b ota.db + $cmd test.db ota.db + } {SQLITE_DONE} + + do_execsql_test $tn3.4.$tn2.$tn.2 { + SELECT * FROM t1 ORDER BY "(a)" ASC; + } { + 3 2 1 + 9 8 3 + xyz hello 6 + } + + 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. + # + # 6: An update string of the wrong length + # + 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} + + 6 { + CREATE TABLE t1(a, b PRIMARY KEY) WITHOUT ROWID; + CREATE TABLE ota.data_t1(a, b, ota_control); + INSERT INTO ota.data_t1 VALUES(1, 2, 'x.x'); + } {SQLITE_ERROR - invalid ota_control value} + + 7 { + CREATE TABLE t1(a, b PRIMARY KEY) WITHOUT ROWID; + CREATE TABLE ota.data_t1(a, b, ota_control); + INSERT INTO ota.data_t1 VALUES(1, 2, NULL); + } {SQLITE_ERROR - invalid ota_control value} + + 8 { + CREATE TABLE t1(a, b PRIMARY KEY) WITHOUT ROWID; + CREATE TABLE ota.data_t1(a, b, ota_control); + INSERT INTO ota.data_t1 VALUES(1, 2, 4); + } {SQLITE_ERROR - invalid ota_control value} + + 9 { + CREATE TABLE t1(a, b PRIMARY KEY) WITHOUT ROWID; + CREATE TABLE ota.data_t1(a, b, ota_control); + INSERT INTO ota.data_t1 VALUES(1, 2, 2); + } {SQLITE_ERROR - invalid ota_control value} + + } { + 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] + } + } + + catch { db close } + eval $destroy_vfs +} + + +finish_test + diff --git a/ext/ota/ota10.test b/ext/ota/ota10.test new file mode 100644 index 000000000..52cc27a8f --- /dev/null +++ b/ext/ota/ota10.test @@ -0,0 +1,188 @@ +# 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 ota10 + + +#-------------------------------------------------------------------- +# Test that UPDATE commands work even if the input columns are in a +# different order to the output columns. +# +do_execsql_test 1.0 { + CREATE TABLE t1(a INTEGER PRIMARY KEY, b, c); + INSERT INTO t1 VALUES(1, 'b', 'c'); +} + +proc apply_ota {sql} { + forcedelete ota.db + sqlite3 db2 ota.db + db2 eval $sql + db2 close + sqlite3ota ota test.db ota.db + while { [ota step]=="SQLITE_OK" } {} + ota close +} + +do_test 1.1 { + apply_ota { + CREATE TABLE data_t1(a, c, b, ota_control); + INSERT INTO data_t1 VALUES(1, 'xxx', NULL, '.x.'); + } + db eval { SELECT * FROM t1 } +} {1 b xxx} + +#-------------------------------------------------------------------- +# Test that the hidden languageid column of an fts4 table can be +# written. +# +ifcapable fts3 { + do_execsql_test 2.0 { + CREATE VIRTUAL TABLE ft USING fts4(a, b, languageid='langid'); + } + do_test 2.1 { + apply_ota { + CREATE TABLE data_ft(a, b, ota_rowid, langid, ota_control); + INSERT INTO data_ft VALUES('a', 'b', 22, 1, 0); -- insert + INSERT INTO data_ft VALUES('a', 'b', 23, 10, 0); -- insert + INSERT INTO data_ft VALUES('a', 'b', 24, 100, 0); -- insert + } + db eval { SELECT a, b, rowid, langid FROM ft } + } [list {*}{ + a b 22 1 + a b 23 10 + a b 24 100 + }] + + # Or not - this data_xxx table has no langid column, so langid + # defaults to 0. + # + do_test 2.2 { + apply_ota { + CREATE TABLE data_ft(a, b, ota_rowid, ota_control); + INSERT INTO data_ft VALUES('a', 'b', 25, 0); -- insert + } + db eval { SELECT a, b, rowid, langid FROM ft } + } [list {*}{ + a b 22 1 + a b 23 10 + a b 24 100 + a b 25 0 + }] + + # Update langid. + # + do_test 2.3 { + apply_ota { + CREATE TABLE data_ft(a, b, ota_rowid, langid, ota_control); + INSERT INTO data_ft VALUES(NULL, NULL, 23, 50, '..x'); + INSERT INTO data_ft VALUES(NULL, NULL, 25, 500, '..x'); + } + db eval { SELECT a, b, rowid, langid FROM ft } + } [list {*}{ + a b 22 1 + a b 23 50 + a b 24 100 + a b 25 500 + }] +} + +#-------------------------------------------------------------------- +# Test that if writing a hidden virtual table column is an error, +# attempting to do so via ota is also an error. +# +ifcapable fts3 { + do_execsql_test 3.0 { + CREATE VIRTUAL TABLE xt USING fts4(a); + } + do_test 3.1 { + list [catch { + apply_ota { + CREATE TABLE data_xt(a, xt, ota_rowid, ota_control); + INSERT INTO data_xt VALUES('a', 'b', 1, 0); + } + } msg] $msg + } {1 {SQLITE_ERROR - SQL logic error or missing database}} +} + +#-------------------------------------------------------------------- +# Test that it is not possible to violate a NOT NULL constraint by +# applying an OTA update. +# +do_execsql_test 4.1 { + CREATE TABLE t2(a INTEGER NOT NULL, b TEXT NOT NULL, c PRIMARY KEY); + CREATE TABLE t3(a INTEGER NOT NULL, b TEXT NOT NULL, c INTEGER PRIMARY KEY); + CREATE TABLE t4(a, b, PRIMARY KEY(a, b)) WITHOUT ROWID; + + INSERT INTO t2 VALUES(10, 10, 10); + INSERT INTO t3 VALUES(10, 10, 10); + INSERT INTO t4 VALUES(10, 10); +} + +foreach {tn error ota} { + 2 {SQLITE_CONSTRAINT - NOT NULL constraint failed: t2.a} { + INSERT INTO data_t2 VALUES(NULL, 'abc', 1, 0); + } + 3 {SQLITE_CONSTRAINT - NOT NULL constraint failed: t2.b} { + INSERT INTO data_t2 VALUES(2, NULL, 1, 0); + } + 4 {SQLITE_CONSTRAINT - NOT NULL constraint failed: t2.c} { + INSERT INTO data_t2 VALUES(1, 'abc', NULL, 0); + } + + 5 {SQLITE_MISMATCH - datatype mismatch} { + INSERT INTO data_t3 VALUES(1, 'abc', NULL, 0); + } + + 6 {SQLITE_CONSTRAINT - NOT NULL constraint failed: t4.b} { + INSERT INTO data_t4 VALUES('a', NULL, 0); + } + 7 {SQLITE_CONSTRAINT - NOT NULL constraint failed: t4.a} { + INSERT INTO data_t4 VALUES(NULL, 'a', 0); + } + 8 {SQLITE_CONSTRAINT - NOT NULL constraint failed: t2.a} { + INSERT INTO data_t2 VALUES(NULL, 0, 10, 'x..'); + } + 9 {SQLITE_CONSTRAINT - NOT NULL constraint failed: t3.b} { + INSERT INTO data_t3 VALUES(10, NULL, 10, '.x.'); + } + + 10 {SQLITE_MISMATCH - datatype mismatch} { + INSERT INTO data_t3 VALUES(1, 'abc', 'text', 0); + } +} { + set ota " + CREATE TABLE data_t2(a, b, c, ota_control); + CREATE TABLE data_t3(a, b, c, ota_control); + CREATE TABLE data_t4(a, b, ota_control); + $ota + " + do_test 4.2.$tn { + list [catch { apply_ota $ota } msg] $msg + } [list 1 $error] +} + +do_test 4.3 { + set ota { + CREATE TABLE data_t3(a, b, c, ota_control); + INSERT INTO data_t3 VALUES(1, 'abc', '5', 0); + INSERT INTO data_t3 VALUES(1, 'abc', '-6.0', 0); + } + list [catch { apply_ota $ota } msg] $msg +} {0 SQLITE_DONE} + + +finish_test + diff --git a/ext/ota/ota11.test b/ext/ota/ota11.test new file mode 100644 index 000000000..6bd233c0c --- /dev/null +++ b/ext/ota/ota11.test @@ -0,0 +1,198 @@ +# 2015 February 16 +# +# 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 ota11 + + +#-------------------------------------------------------------------- +# Test that the xAccess() method of an ota vfs handles queries other +# than SQLITE_ACCESS_EXISTS correctly. The test code below causes +# SQLite to call xAccess(SQLITE_ACCESS_READWRITE) on the directory +# path argument passed to "PRAGMA temp_store_directory". +# +do_test 1.1 { + sqlite3ota_create_vfs -default ota "" + reset_db + catchsql { PRAGMA temp_store_directory = '/no/such/directory' } +} {1 {not a writable directory}} + +do_test 1.2 { + catchsql " PRAGMA temp_store_directory = '[pwd]' " +} {0 {}} + +do_test 1.3 { + catchsql " PRAGMA temp_store_directory = '' " +} {0 {}} + +do_test 1.4 { + db close + sqlite3ota_destroy_vfs ota +} {} + +#-------------------------------------------------------------------- +# Try to trick ota into operating on a database opened in wal mode. +# +reset_db +do_execsql_test 2.1 { + CREATE TABLE t1(a PRIMARY KEY, b, c); + INSERT INTO t1 VALUES(1, 2, 3); + PRAGMA journal_mode = 'wal'; + CREATE TABLE t2(d PRIMARY KEY, e, f); +} {wal} + +do_test 2.2 { + db_save + db close + + forcedelete ota.db + sqlite3 dbo ota.db + dbo eval { + CREATE TABLE data_t1(a, b, c, ota_control); + INSERT INTO data_t1 VALUES(4, 5, 6, 0); + INSERT INTO data_t1 VALUES(7, 8, 9, 0); + } + dbo close + + db_restore + hexio_write test.db 18 0101 + file exists test.db-wal +} {1} + +do_test 2.3 { + sqlite3ota ota test.db ota.db + ota step +} {SQLITE_ERROR} + +do_test 2.4 { + list [catch {ota close} msg] $msg +} {1 {SQLITE_ERROR - cannot update wal mode database}} + +#-------------------------------------------------------------------- +# Test a constraint violation message with an unusual table name. +# Specifically, one for which the first character is a codepoint +# smaller than 30 (character '0'). +# +reset_db +do_execsql_test 3.1 { + CREATE TABLE "(t1)"(a PRIMARY KEY, b, c); + INSERT INTO "(t1)" VALUES(1, 2, 3); + INSERT INTO "(t1)" VALUES(4, 5, 6); +} +db close + +do_test 3.2 { + forcedelete ota.db + sqlite3 dbo ota.db + dbo eval { + CREATE TABLE "data_(t1)"(a, b, c, ota_control); + INSERT INTO "data_(t1)" VALUES(4, 8, 9, 0); + } + dbo close + + sqlite3ota ota test.db ota.db + ota step + ota step +} {SQLITE_CONSTRAINT} + +do_test 3.3 { + list [catch {ota close} msg] $msg +} {1 {SQLITE_CONSTRAINT - UNIQUE constraint failed: (t1).a}} + +#-------------------------------------------------------------------- +# Check that once an OTA update has been applied, attempting to apply +# it a second time is a no-op (as the state stored in the OTA database is +# "all steps completed"). +# +reset_db +do_execsql_test 4.1 { + CREATE TABLE "(t1)"(a, b, c, PRIMARY KEY(c, b, a)); + INSERT INTO "(t1)" VALUES(1, 2, 3); + INSERT INTO "(t1)" VALUES(4, 5, 6); +} +db close + +do_test 4.2 { + forcedelete ota.db + sqlite3 dbo ota.db + dbo eval { + CREATE TABLE "data_(t1)"(a, b, c, ota_control); + INSERT INTO "data_(t1)" VALUES(7, 8, 9, 0); + INSERT INTO "data_(t1)" VALUES(1, 2, 3, 1); + } + dbo close + + sqlite3ota ota test.db ota.db + while {[ota step]=="SQLITE_OK"} { } + ota close +} {SQLITE_DONE} + +do_test 4.3 { + sqlite3ota ota test.db ota.db + ota step +} {SQLITE_DONE} + +do_test 4.4 { + ota close +} {SQLITE_DONE} + +do_test 4.5.1 { + sqlite3 dbo ota.db + dbo eval { INSERT INTO ota_state VALUES(100, 100) } + dbo close + sqlite3ota ota test.db ota.db + ota step +} {SQLITE_CORRUPT} +do_test 4.5.2 { + list [catch {ota close} msg] $msg +} {1 SQLITE_CORRUPT} +do_test 4.5.3 { + sqlite3 dbo ota.db + dbo eval { DELETE FROM ota_state WHERE k = 100 } + dbo close +} {} + +# Also, check that an invalid state value in the ota_state table is +# detected and reported as corruption. +do_test 4.6.1 { + sqlite3 dbo ota.db + dbo eval { UPDATE ota_state SET v = v*-1 WHERE k = 1 } + dbo close + sqlite3ota ota test.db ota.db + ota step +} {SQLITE_CORRUPT} +do_test 4.6.2 { + list [catch {ota close} msg] $msg +} {1 SQLITE_CORRUPT} +do_test 4.6.3 { + sqlite3 dbo ota.db + dbo eval { UPDATE ota_state SET v = v*-1 WHERE k = 1 } + dbo close +} {} + +do_test 4.7.1 { + sqlite3 dbo ota.db + dbo eval { UPDATE ota_state SET v = 1 WHERE k = 1 } + dbo eval { UPDATE ota_state SET v = 'nosuchtable' WHERE k = 2 } + dbo close + sqlite3ota ota test.db ota.db + ota step +} {SQLITE_ERROR} +do_test 4.7.2 { + list [catch {ota close} msg] $msg +} {1 {SQLITE_ERROR - ota_state mismatch error}} + +finish_test + diff --git a/ext/ota/ota12.test b/ext/ota/ota12.test new file mode 100644 index 000000000..844b54167 --- /dev/null +++ b/ext/ota/ota12.test @@ -0,0 +1,172 @@ +# 2015 February 16 +# +# 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 +source $testdir/lock_common.tcl +set ::testprefix ota12 + +set setup_sql { + DROP TABLE IF EXISTS xx; + DROP TABLE IF EXISTS xy; + CREATE TABLE xx(a, b, c PRIMARY KEY); + INSERT INTO xx VALUES(1, 2, 3); + CREATE TABLE xy(a, b, c PRIMARY KEY); + + ATTACH 'ota.db' AS ota; + DROP TABLE IF EXISTS data_xx; + CREATE TABLE ota.data_xx(a, b, c, ota_control); + INSERT INTO data_xx VALUES(4, 5, 6, 0); + INSERT INTO data_xx VALUES(7, 8, 9, 0); + CREATE TABLE ota.data_xy(a, b, c, ota_control); + INSERT INTO data_xy VALUES(10, 11, 12, 0); + DETACH ota; +} + +do_multiclient_test tn { + + # Initialize a target (test.db) and ota (ota.db) database. + # + forcedelete ota.db + sql1 $setup_sql + + # Using connection 2, open a read transaction on the target database. + # OTA will still be able to generate "test.db-oal", but it will not be + # able to rename it to "test.db-wal". + # + do_test 1.$tn.1 { + sql2 { BEGIN; SELECT * FROM xx; } + } {1 2 3} + do_test 1.$tn.2 { + sqlite3ota ota test.db ota.db + while 1 { + set res [ota step] + if {$res!="SQLITE_OK"} break + } + set res + } {SQLITE_BUSY} + + do_test 1.$tn.3 { sql2 { SELECT * FROM xx; } } {1 2 3} + do_test 1.$tn.4 { sql2 { SELECT * FROM xy; } } {} + do_test 1.$tn.5 { + list [file exists test.db-wal] [file exists test.db-oal] + } {0 1} + do_test 1.$tn.6 { sql2 COMMIT } {} + + # The ota object that hit the SQLITE_BUSY error above cannot be reused. + # It is stuck in a permanent SQLITE_BUSY state at this point. + # + do_test 1.$tn.7 { ota step } {SQLITE_BUSY} + do_test 1.$tn.8 { + list [catch { ota close } msg] $msg + } {1 SQLITE_BUSY} + + do_test 1.$tn.9.1 { sql2 { BEGIN EXCLUSIVE } } {} + do_test 1.$tn.9.2 { + sqlite3ota ota test.db ota.db + ota step + } {SQLITE_BUSY} + do_test 1.$tn.9.3 { + list [catch { ota close } msg] $msg + } {1 {SQLITE_BUSY - database is locked}} + do_test 1.$tn.9.4 { sql2 COMMIT } {} + + sqlite3ota ota test.db ota.db + do_test 1.$tn.10.1 { sql2 { BEGIN EXCLUSIVE } } {} + do_test 1.$tn.10.2 { + ota step + } {SQLITE_BUSY} + do_test 1.$tn.10.3 { + list [catch { ota close } msg] $msg + } {1 SQLITE_BUSY} + do_test 1.$tn.10.4 { sql2 COMMIT } {} + + # A new ota object can finish the work though. + # + do_test 1.$tn.11 { + sqlite3ota ota test.db ota.db + ota step + } {SQLITE_OK} + do_test 1.$tn.12 { + list [file exists test.db-wal] [file exists test.db-oal] + } {1 0} + do_test 1.$tn.13 { + while 1 { + set res [ota step] + if {$res!="SQLITE_OK"} break + } + set res + } {SQLITE_DONE} + + do_test 1.$tn.14 { + ota close + } {SQLITE_DONE} +} + +do_multiclient_test tn { + + # Initialize a target (test.db) and ota (ota.db) database. + # + forcedelete ota.db + sql1 $setup_sql + + do_test 2.$tn.1 { + sqlite3ota ota test.db ota.db + while {[file exists test.db-wal]==0} { + if {[ota step]!="SQLITE_OK"} {error "problem here...."} + } + ota close + } {SQLITE_OK} + + + do_test 2.$tn.2 { sql2 { BEGIN IMMEDIATE } } {} + + do_test 2.$tn.3 { + sqlite3ota ota test.db ota.db + ota step + } {SQLITE_BUSY} + + do_test 2.$tn.4 { list [catch { ota close } msg] $msg } {1 SQLITE_BUSY} + + do_test 2.$tn.5 { + sql2 { SELECT * FROM xx ; COMMIT } + } {1 2 3 4 5 6 7 8 9} + + do_test 2.$tn.6 { + sqlite3ota ota test.db ota.db + ota step + ota close + } {SQLITE_OK} + + do_test 2.$tn.7 { sql2 { BEGIN EXCLUSIVE } } {} + + do_test 2.$tn.8 { + sqlite3ota ota test.db ota.db + ota step + } {SQLITE_BUSY} + do_test 2.$tn.9 { list [catch { ota close } msg] $msg } {1 SQLITE_BUSY} + do_test 2.$tn.10 { + sql2 { SELECT * FROM xx ; COMMIT } + } {1 2 3 4 5 6 7 8 9} + + do_test 2.$tn.11 { + sqlite3ota ota test.db ota.db + while {[ota step]=="SQLITE_OK"} {} + ota close + } {SQLITE_DONE} + +} + +finish_test + diff --git a/ext/ota/ota3.test b/ext/ota/ota3.test new file mode 100644 index 000000000..24d5ffde3 --- /dev/null +++ b/ext/ota/ota3.test @@ -0,0 +1,207 @@ +# 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 ota3 + + +# Run the OTA in file $ota on target database $target until completion. +# +proc run_ota {target ota} { + sqlite3ota ota $target $ota + while { [ota step]=="SQLITE_OK" } {} + ota close +} + +forcedelete test.db-oal ota.db +db close +sqlite3_shutdown +sqlite3_config_uri 1 +reset_db + +#-------------------------------------------------------------------- +# Test that for an OTA to be applied, no corruption results if the +# affinities on the source and target table do not match. +# +do_execsql_test 1.0 { + CREATE TABLE x1(a INTEGER PRIMARY KEY, b TEXT, c REAL); + CREATE INDEX i1 ON x1(b, c); +} {} + +do_test 1.1 { + sqlite3 db2 ota.db + db2 eval { + CREATE TABLE data_x1(a, b, c, ota_control); + INSERT INTO data_x1 VALUES(1, '123', '123', 0); + INSERT INTO data_x1 VALUES(2, 123, 123, 0); + } + db2 close + run_ota test.db ota.db +} {SQLITE_DONE} + +do_execsql_test 1.2 { + PRAGMA integrity_check; +} {ok} + +#-------------------------------------------------------------------- +# Test that NULL values may not be inserted into INTEGER PRIMARY KEY +# columns. +# +forcedelete ota.db +reset_db + +do_execsql_test 2.0 { + CREATE TABLE x1(a INTEGER PRIMARY KEY, b TEXT, c REAL); + CREATE INDEX i1 ON x1(b, c); +} {} + +foreach {tn otadb} { + 1 { + CREATE TABLE data_x1(a, b, c, ota_control); + INSERT INTO data_x1 VALUES(NULL, 'a', 'b', 0); + } + + 2 { + CREATE TABLE data_x1(c, b, a, ota_control); + INSERT INTO data_x1 VALUES('b', 'a', NULL, 0); + } +} { + do_test 2.$tn.1 { + forcedelete ota.db + sqlite3 db2 ota.db + db2 eval $otadb + db2 close + list [catch { run_ota test.db ota.db } msg] $msg + } {1 {SQLITE_MISMATCH - datatype mismatch}} + + do_execsql_test 2.1.2 { + PRAGMA integrity_check; + } {ok} +} + +#-------------------------------------------------------------------- +# Test that missing columns are detected. +# +forcedelete ota.db +reset_db + +do_execsql_test 2.0 { + CREATE TABLE x1(a INTEGER PRIMARY KEY, b, c); + CREATE INDEX i1 ON x1(b, c); +} {} + +do_test 2.1 { + sqlite3 db2 ota.db + db2 eval { + CREATE TABLE data_x1(a, b, ota_control); + INSERT INTO data_x1 VALUES(1, 'a', 0); + } + db2 close + list [catch { run_ota test.db ota.db } msg] $msg +} {1 {SQLITE_ERROR - column missing from data_x1: c}} + +do_execsql_test 2.2 { + PRAGMA integrity_check; +} {ok} + +# Also extra columns. +# +do_execsql_test 2.3 { + CREATE TABLE x2(a INTEGER PRIMARY KEY, b, c); + CREATE INDEX i2 ON x2(b, c); +} {} + +do_test 2.4 { + forcedelete ota.db + sqlite3 db2 ota.db + db2 eval { + CREATE TABLE data_x2(a, b, c, d, ota_control); + INSERT INTO data_x2 VALUES(1, 'a', 2, 3, 0); + } + db2 close + list [catch { run_ota test.db ota.db } msg] $msg +} {1 SQLITE_ERROR} + +do_execsql_test 2.5 { + PRAGMA integrity_check; +} {ok} + + +#------------------------------------------------------------------------- +# Test that sqlite3ota_create_vfs() returns an error if the requested +# parent VFS is unknown. +# +# And that nothing disasterous happens if a VFS name passed to +# sqlite3ota_destroy_vfs() is unknown or not an OTA vfs. +# +do_test 3.1 { + list [catch {sqlite3ota_create_vfs xyz nosuchparent} msg] $msg +} {1 SQLITE_NOTFOUND} + +do_test 3.2 { + sqlite3ota_destroy_vfs nosuchvfs + sqlite3ota_destroy_vfs unix + sqlite3ota_destroy_vfs win32 +} {} + +#------------------------------------------------------------------------- +# Test that it is an error to specify an explicit VFS that does not +# include ota VFS functionality. +# +do_test 4.1 { + testvfs tvfs + sqlite3ota ota file:test.db?vfs=tvfs ota.db + list [catch { ota step } msg] $msg +} {0 SQLITE_ERROR} +do_test 4.2 { + list [catch { ota close } msg] $msg +} {1 {SQLITE_ERROR - ota vfs not found}} +tvfs delete + +#------------------------------------------------------------------------- +# Test a large ota update to ensure that wal_autocheckpoint does not get +# in the way. +# +forcedelete ota.db +reset_db +do_execsql_test 5.1 { + CREATE TABLE x1(a, b, c, PRIMARY KEY(a)) WITHOUT ROWID; + CREATE INDEX i1 ON x1(a); + + ATTACH 'ota.db' AS ota; + CREATE TABLE ota.data_x1(a, b, c, ota_control); + WITH s(a, b, c) AS ( + SELECT randomblob(300), randomblob(300), 1 + UNION ALL + SELECT randomblob(300), randomblob(300), c+1 FROM s WHERE c<2000 + ) + INSERT INTO data_x1 SELECT a, b, c, 0 FROM s; +} + +do_test 5.2 { + sqlite3ota ota test.db ota.db + while {[ota step]=="SQLITE_OK" && [file exists test.db-wal]==0} {} + ota close +} {SQLITE_OK} + +do_test 5.3 { + expr {[file size test.db-wal] > (1024 * 1200)} +} 1 + +do_test 6.1 { sqlite3ota_internal_test } {} + +finish_test + + diff --git a/ext/ota/ota5.test b/ext/ota/ota5.test new file mode 100644 index 000000000..8b62eb0f6 --- /dev/null +++ b/ext/ota/ota5.test @@ -0,0 +1,331 @@ +# 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 ota5 + + +# Run the OTA in file $ota on target database $target until completion. +# +proc run_ota {target ota} { + sqlite3ota ota $target $ota + while { [ota step]=="SQLITE_OK" } {} + ota close +} + + +# Run the OTA in file $ota on target database $target one step at a +# time until completion. +# +proc step_ota {target ota} { + while 1 { + sqlite3ota ota $target $ota + set rc [ota step] + ota close + if {$rc != "SQLITE_OK"} break + } + set rc +} + +# Return a list of the primary key columns for table $tbl in the database +# opened by database handle $db. +# +proc pkcols {db tbl} { + set ret [list] + $db eval "PRAGMA table_info = '$tbl'" { + if {$pk} { lappend ret $name } + } + return $ret +} + +# Return a list of all columns for table $tbl in the database opened by +# database handle $db. +# +proc allcols {db tbl} { + set ret [list] + $db eval "PRAGMA table_info = '$tbl'" { + lappend ret $name + } + return $ret +} + +# Return a checksum on all tables and data in the main database attached +# to database handle $db. It is possible to add indexes without changing +# the checksum. +# +proc datacksum {db} { + + $db eval { SELECT name FROM sqlite_master WHERE type='table' } { + append txt $name + set cols [list] + set order [list] + set cnt 0 + $db eval "PRAGMA table_info = $name" x { + lappend cols "quote($x(name))" + lappend order [incr cnt] + } + set cols [join $cols ,] + set order [join $order ,] + append txt [$db eval "SELECT $cols FROM $name ORDER BY $order"] + } + return "[string length $txt]-[md5 $txt]" +} + +proc ucontrol {args} { + set ret "" + foreach a $args { + if {$a} { + append ret . + } else { + append ret x + } + } + return $ret +} + +# Argument $target is the name of an SQLite database file. $sql is an SQL +# script containing INSERT, UPDATE and DELETE statements to execute against +# it. This command creates an OTA update database in file $ota that has +# the same effect as the script. The target database is not modified by +# this command. +# +proc generate_ota_db {target ota sql} { + + forcedelete $ota + forcecopy $target copy.db + + # Evaluate the SQL script to modify the contents of copy.db. + # + sqlite3 dbOta copy.db + dbOta eval $sql + + dbOta function ucontrol ucontrol + + # Evaluate the SQL script to modify the contents of copy.db. + set ret [datacksum dbOta] + + dbOta eval { ATTACH $ota AS ota } + dbOta eval { ATTACH $target AS orig } + + dbOta eval { SELECT name AS tbl FROM sqlite_master WHERE type = 'table' } { + set pk [pkcols dbOta $tbl] + set cols [allcols dbOta $tbl] + + # A WHERE clause to test that the PK columns match. + # + set where [list] + foreach c $pk { lappend where "main.$tbl.$c IS orig.$tbl.$c" } + set where [join $where " AND "] + + # A WHERE clause to test that all columns match. + # + set where2 [list] + foreach c $cols { lappend where2 "main.$tbl.$c IS orig.$tbl.$c" } + set ucontrol "ucontrol([join $where2 ,])" + set where2 [join $where2 " AND "] + + # Create a data_xxx table in the OTA update database. + dbOta eval " + CREATE TABLE ota.data_$tbl AS SELECT *, '' AS ota_control + FROM main.$tbl LIMIT 0 + " + + # Find all new rows INSERTed by the script. + dbOta eval " + INSERT INTO ota.data_$tbl + SELECT *, 0 AS ota_control FROM main.$tbl + WHERE NOT EXISTS ( + SELECT 1 FROM orig.$tbl WHERE $where + ) + " + + # Find all old rows DELETEd by the script. + dbOta eval " + INSERT INTO ota.data_$tbl + SELECT *, 1 AS ota_control FROM orig.$tbl + WHERE NOT EXISTS ( + SELECT 1 FROM main.$tbl WHERE $where + ) + " + + # Find all rows UPDATEd by the script. + set origcols [list] + foreach c $cols { lappend origcols "main.$tbl.$c" } + set origcols [join $origcols ,] + dbOta eval " + INSERT INTO ota.data_$tbl + SELECT $origcols, $ucontrol AS ota_control + FROM orig.$tbl, main.$tbl + WHERE $where AND NOT ($where2) + " + + } + + dbOta close + forcedelete copy.db + + return $ret +} + +#------------------------------------------------------------------------- +# +do_execsql_test 1.0 { + CREATE TABLE t1(a INTEGER PRIMARY KEY, b, c); + CREATE TABLE t2(x, y, z, PRIMARY KEY(y, z)) WITHOUT ROWID; + + INSERT INTO t1 VALUES(1, 2, 3); + INSERT INTO t1 VALUES(2, 4, 6); + INSERT INTO t1 VALUES(3, 6, 9); + + INSERT INTO t2 VALUES(1, 2, 3); + INSERT INTO t2 VALUES(2, 4, 6); + INSERT INTO t2 VALUES(3, 6, 9); +} +db close + +set cksum [generate_ota_db test.db ota.db { + INSERT INTO t1 VALUES(4, 8, 12); + DELETE FROM t1 WHERE a = 2; + UPDATE t1 SET c = 15 WHERE a=3; + + INSERT INTO t2 VALUES(4, 8, 12); + DELETE FROM t2 WHERE x = 2; + UPDATE t2 SET x = 15 WHERE z=9; +}] + +foreach {tn idx} { + 1 { + } + 2 { + CREATE INDEX i1 ON t1(a, b, c); + CREATE INDEX i2 ON t2(x, y, z); + } +} { + foreach cmd {run step} { + forcecopy test.db test.db2 + forcecopy ota.db ota.db2 + + sqlite3 db test.db2 + db eval $idx + + do_test 1.$tn.$cmd.1 { + ${cmd}_ota test.db2 ota.db2 + datacksum db + } $cksum + + do_test 1.$tn.$cmd.2 { + db eval { PRAGMA integrity_check } + } {ok} + + db close + } +} + +#------------------------------------------------------------------------- +# +reset_db +do_execsql_test 2.0 { + CREATE TABLE t1(a INTEGER PRIMARY KEY, b, c, d, e); + INSERT INTO t1 VALUES(-750250,'fyetckfaagjkzqjx',-185831,X'FEAD',444258.29); + INSERT INTO t1 VALUES(649081,NULL,X'7DF25BF78778',-342324.63,'akvspktocwozo'); + INSERT INTO t1 VALUES(-133045,-44822.31,X'',287935,NULL); + INSERT INTO t1 VALUES(202132,NULL,X'5399','cujsjtspryqeyovcdpz','m'); + INSERT INTO t1 VALUES(302910,NULL,'dvdhivtfkaedzhdcnn',-717113.41,688487); + INSERT INTO t1 VALUES(-582327,X'7A267A',X'7E6B3CFE5CB9','zacuzilrok',-196478); + INSERT INTO t1 VALUES(-190462,X'D1A087E7D68D9578','lsmleti',NULL,-928094); + INSERT INTO t1 VALUES(-467665,176344.57,-536684.23,828876.22,X'903E'); + INSERT INTO t1 VALUES(-629138,632630.29,X'28D6',-774501,X'819BBBFC65'); + INSERT INTO t1 VALUES(-828110,-54379.24,-881121.44,X'',X'8D5A894F0D'); + + CREATE TABLE t2(a PRIMARY KEY, b, c, d, e) WITHOUT ROWID; + INSERT INTO t2 VALUES(-65174,X'AC1DBFFE27310F',-194471.08,347988,X'84041BA6F9BDDE86A8'); + INSERT INTO t2 VALUES('bzbpi',-952693.69,811628.25,NULL,-817434); + INSERT INTO t2 VALUES(-643830,NULL,'n',NULL,'dio'); + INSERT INTO t2 VALUES('rovoenxxj',NULL,'owupbtdcoxxnvg',-119676,X'55431DFA'); + INSERT INTO t2 VALUES(899770,'jlygdl',X'DBCA4D1A',NULL,-631773); + INSERT INTO t2 VALUES(334698.80,NULL,-697585.58,-89277,-817352); + INSERT INTO t2 VALUES(X'1A9EB7547A4AAF38','aiprdhkpzdz','anw','szvjbwdvzucybpwwqjt',X'53'); + INSERT INTO t2 VALUES(713220,NULL,'hfcqhqzjuqplvkum',X'20B076075649DE','fthgpvqdyy'); + INSERT INTO t2 VALUES(763908,NULL,'xgslzcpvwfknbr',X'75',X'668146'); + INSERT INTO t2 VALUES(X'E1BA2B6BA27278','wjbpd',NULL,139341,-290086.15); +} +db close + +set cksum [generate_ota_db test.db ota.db { +INSERT INTO t2 VALUES(222916.23,'idh',X'472C517405',X'E3',X'7C4F31824669'); +INSERT INTO t2 VALUES('xcndjwafcoxwxizoktd',-319567.21,NULL,-720906.43,-577170); +INSERT INTO t2 VALUES(376369.99,-536058,'yoaiurfqupdscwc',X'29EC8A2542EC3953E9',-740485.22); +INSERT INTO t2 VALUES(X'0EFB4DC50693',-175590.83,X'1779E253CAB5B1789E',X'BC6903',NULL); +INSERT INTO t2 VALUES(-288299,'hfrp',NULL,528477,730676.77); +DELETE FROM t2 WHERE a < -60000; + +UPDATE t2 SET b = 'pgnnaaoflnw' WHERE a = 'bzbpi'; +UPDATE t2 SET c = -675583 WHERE a = 'rovoenxxj'; +UPDATE t2 SET d = X'09CDF2B2C241' WHERE a = 713220; + +INSERT INTO t1 VALUES(224938,'bmruycvfznhhnfmgqys','fr',854381,789143); +INSERT INTO t1 VALUES(-863931,-1386.26,X'2A058540C2FB5C',NULL,X'F9D5990A'); +INSERT INTO t1 VALUES(673696,X'97301F0AC5735F44B5',X'440C',227999.92,-709599.79); +INSERT INTO t1 VALUES(-243640,NULL,-71718.11,X'1EEFEB38',X'8CC7C55D95E142FBA5'); +INSERT INTO t1 VALUES(275893,X'',375606.30,X'0AF9EC334711FB',-468194); +DELETE FROM t1 WHERE a > 200000; + +UPDATE t1 SET b = 'pgnnaaoflnw' WHERE a = -190462; +UPDATE t1 SET c = -675583 WHERE a = -467665; +UPDATE t1 SET d = X'09CDF2B2C241' WHERE a = -133045; + +}] + +foreach {tn idx} { + 1 { + } + 2 { + CREATE UNIQUE INDEX i1 ON t1(b, c, d); + CREATE UNIQUE INDEX i2 ON t1(d, e, a); + CREATE UNIQUE INDEX i3 ON t1(e, d, c, b); + + CREATE UNIQUE INDEX i4 ON t2(b, c, d); + CREATE UNIQUE INDEX i5 ON t2(d, e, a); + CREATE UNIQUE INDEX i6 ON t2(e, d, c, b); + } +} { + foreach cmd {run step} { + forcecopy test.db test.db2 + forcecopy ota.db ota.db2 + + sqlite3 db test.db2 + db eval $idx + + do_test 2.$tn.$cmd.1 { + ${cmd}_ota test.db2 ota.db2 + datacksum db + } $cksum + + do_test 2.$tn.$cmd.2 { + db eval { PRAGMA integrity_check } + } {ok} + + db close + } +} + + +finish_test + + + + diff --git a/ext/ota/ota6.test b/ext/ota/ota6.test new file mode 100644 index 000000000..4fe14950b --- /dev/null +++ b/ext/ota/ota6.test @@ -0,0 +1,103 @@ +# 2014 October 21 +# +# 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. Specifically, it tests the +# outcome of some other client writing to the database while an OTA update +# is being applied. + +if {![info exists testdir]} { + set testdir [file join [file dirname [info script]] .. .. test] +} +source $testdir/tester.tcl +set ::testprefix ota6 + +proc setup_test {} { + reset_db + execsql { + CREATE TABLE t1(a INTEGER PRIMARY KEY, b UNIQUE); + CREATE TABLE t2(a INTEGER PRIMARY KEY, b UNIQUE); + CREATE TABLE t3(a INTEGER PRIMARY KEY, b UNIQUE); + } + db close + + forcedelete ota.db + sqlite3 ota ota.db + ota eval { + CREATE TABLE data_t1(a, b, ota_control); + CREATE TABLE data_t2(a, b, ota_control); + CREATE TABLE data_t3(a, b, ota_control); + INSERT INTO data_t1 VALUES(1, 't1', 0); + INSERT INTO data_t2 VALUES(2, 't2', 0); + INSERT INTO data_t3 VALUES(3, 't3', 0); + } + ota close +} + +# Test the outcome of some other client writing the db while the *-oal +# file is being generated. Once this has happened, the update cannot be +# progressed. +# +for {set nStep 1} {$nStep < 8} {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 + } {SQLITE_BUSY} + do_test 1.$nStep.2 { + ota step + } {SQLITE_BUSY} + 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 modified during ota update}} +} + +# Test the outcome of some other client writing the db after the *-oal +# file has been copied to the *-wal path. Once this has happened, any +# other client writing to the db causes OTA to consider its job finished. +# +for {set nStep 8} {$nStep < 20} {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 + } {SQLITE_DONE} + do_test 1.$nStep.2 { + ota step + } {SQLITE_DONE} + do_test 1.$nStep.3 { + file exists test.db-oal + } {0} + do_test 1.$nStep.4 { + list [catch { ota close } msg] $msg + } {0 SQLITE_DONE} + + do_execsql_test 1.$nStep.5 { + SELECT * FROM t1; + } {1 t1 5 hello} +} + + +finish_test + diff --git a/ext/ota/ota7.test b/ext/ota/ota7.test new file mode 100644 index 000000000..a4ee6b41a --- /dev/null +++ b/ext/ota/ota7.test @@ -0,0 +1,110 @@ +# 2014 October 21 +# +# 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. +# + + +if {![info exists testdir]} { + set testdir [file join [file dirname [info script]] .. .. test] +} +source $testdir/tester.tcl +set ::testprefix ota7 + +# Test index: +# +# 1.*: That affinities are correctly applied to values within the +# OTA database. +# +# 2.*: Tests for multi-column primary keys. +# + +do_test 1.0 { + execsql { + CREATE TABLE t1(a INT PRIMARY KEY, b) WITHOUT ROWID; + INSERT INTO t1 VALUES(1, 'abc'); + INSERT INTO t1 VALUES(2, 'def'); + } + + forcedelete ota.db + sqlite3 ota ota.db + ota eval { + CREATE TABLE data_t1(a, b, ota_control); + INSERT INTO data_t1 VALUES('1', NULL, 1); + } + ota close +} {} + +do_test 1.1 { + sqlite3ota ota test.db ota.db + while { [ota step]=="SQLITE_OK" } {} + ota close +} {SQLITE_DONE} + +sqlite3 db test.db +do_execsql_test 1.2 { + SELECT * FROM t1 +} {2 def} + +#------------------------------------------------------------------------- +# +foreach {tn tbl} { + 1 { CREATE TABLE t1(a, b, c, PRIMARY KEY(a, b)) WITHOUT ROWID } + 2 { CREATE TABLE t1(a, b, c, PRIMARY KEY(a, b)) } +} { + reset_db + + execsql $tbl + do_execsql_test 2.$tn.1 { + CREATE INDEX t1c ON t1(c); + INSERT INTO t1 VALUES(1, 1, 'a'); + INSERT INTO t1 VALUES(1, 2, 'b'); + INSERT INTO t1 VALUES(2, 1, 'c'); + INSERT INTO t1 VALUES(2, 2, 'd'); + } + + do_test 2.$tn.2 { + forcedelete ota.db + sqlite3 ota ota.db + execsql { + CREATE TABLE data_t1(a, b, c, ota_control); + INSERT INTO data_t1 VALUES(3, 1, 'e', 0); + INSERT INTO data_t1 VALUES(3, 2, 'f', 0); + INSERT INTO data_t1 VALUES(1, 2, NULL, 1); + INSERT INTO data_t1 VALUES(2, 1, 'X', '..x'); + } ota + ota close + } {} + + do_test 2.$tn.3 { + set rc "SQLITE_OK" + while {$rc == "SQLITE_OK"} { + sqlite3ota ota test.db ota.db + ota step + set rc [ota close] + } + set rc + } {SQLITE_DONE} + + do_execsql_test 2.$tn.1 { + SELECT * FROM t1 ORDER BY a, b + } { + 1 1 a + 2 1 X + 2 2 d + 3 1 e + 3 2 f + } +} + +finish_test + + diff --git a/ext/ota/ota8.test b/ext/ota/ota8.test new file mode 100644 index 000000000..24a6e7224 --- /dev/null +++ b/ext/ota/ota8.test @@ -0,0 +1,75 @@ +# 2014 November 20 +# +# 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 the ota_delta() feature. +# + +if {![info exists testdir]} { + set testdir [file join [file dirname [info script]] .. .. test] +} +source $testdir/tester.tcl +set ::testprefix ota8 + +do_execsql_test 1.0 { + CREATE TABLE t1(x, y PRIMARY KEY, z); + INSERT INTO t1 VALUES(NULL, 1, 'one'); + INSERT INTO t1 VALUES(NULL, 2, 'two'); + INSERT INTO t1 VALUES(NULL, 3, 'three'); + CREATE INDEX i1z ON t1(z, x); +} + +do_test 1.1 { + forcedelete ota.db + sqlite3 db2 ota.db + db2 eval { + CREATE TABLE data_t1(x, y, z, ota_control); + INSERT INTO data_t1 VALUES('a', 1, '_i' , 'x.d'); + INSERT INTO data_t1 VALUES('b', 2, 2 , '..x'); + INSERT INTO data_t1 VALUES('_iii', 3, '-III' , 'd.d'); + } + db2 close +} {} + +do_test 1.2.1 { + sqlite3ota ota test.db ota.db + ota step +} {SQLITE_ERROR} +do_test 1.2.2 { + list [catch {ota close} msg] $msg +} {1 {SQLITE_ERROR - no such function: ota_delta}} + +proc ota_delta {orig new} { + return "${orig}${new}" +} + +do_test 1.3.1 { + while 1 { + sqlite3ota ota test.db ota.db + ota create_ota_delta + set rc [ota step] + if {$rc != "SQLITE_OK"} break + ota close + } + ota close +} {SQLITE_DONE} + +do_execsql_test 1.3.2 { + SELECT * FROM t1 +} { + a 1 one_i + {} 2 2 + _iii 3 three-III +} +integrity_check 1.3.3 + + +finish_test + diff --git a/ext/ota/ota9.test b/ext/ota/ota9.test new file mode 100644 index 000000000..e746d363f --- /dev/null +++ b/ext/ota/ota9.test @@ -0,0 +1,128 @@ +# 2014 November 21 +# +# 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 OTA with virtual tables. And tables with no PRIMARY KEY declarations. +# + +if {![info exists testdir]} { + set testdir [file join [file dirname [info script]] .. .. test] +} +source $testdir/tester.tcl +set ::testprefix ota9 + +ifcapable !fts3 { + finish_test + return +} + +do_execsql_test 1.1 { + CREATE VIRTUAL TABLE f1 USING fts4(a, b, c); + INSERT INTO f1(rowid, a, b, c) VALUES(11, 'a', 'b', 'c'); + INSERT INTO f1(rowid, a, b, c) VALUES(12, 'd', 'e', 'f'); + INSERT INTO f1(rowid, a, b, c) VALUES(13, 'g', 'h', 'i'); +} + +do_test 1.1 { + forcedelete ota.db + sqlite3 db2 ota.db + db2 eval { + CREATE TABLE data_f1(ota_rowid, a, b, c, ota_control); + INSERT INTO data_f1 VALUES(14, 'x', 'y', 'z', 0); -- INSERT + INSERT INTO data_f1 VALUES(11, NULL, NULL, NULL, 1); -- DELETE + INSERT INTO data_f1 VALUES(13, NULL, NULL, 'X', '..x'); -- UPDATE + } + db2 close +} {} + +do_test 1.2.1 { + while 1 { + sqlite3ota ota test.db ota.db + set rc [ota step] + if {$rc != "SQLITE_OK"} break + ota close + } + ota close +} {SQLITE_DONE} + +do_execsql_test 1.2.2 { SELECT rowid, * FROM f1 } { + 12 d e f + 13 g h X + 14 x y z +} +do_execsql_test 1.2.3 { INSERT INTO f1(f1) VALUES('integrity-check') } +integrity_check 1.2.4 + +#------------------------------------------------------------------------- +# Tables with no PK declaration. +# + +# Run the OTA in file $ota on target database $target until completion. +# +proc run_ota {target ota} { + sqlite3ota ota $target $ota + while { [ota step]=="SQLITE_OK" } {} + ota close +} + +foreach {tn idx} { + 1 { } + 2 { + CREATE INDEX i1 ON t1(a); + } + 3 { + CREATE INDEX i1 ON t1(b, c); + CREATE INDEX i2 ON t1(c, b); + CREATE INDEX i3 ON t1(a, a, a, b, b, b, c, c, c); + } +} { + + reset_db + do_execsql_test 2.$tn.1 { + CREATE TABLE t1(a, b, c); + INSERT INTO t1 VALUES(1, 2, 3); + INSERT INTO t1 VALUES(4, 5, 6); + INSERT INTO t1(rowid, a, b, c) VALUES(-1, 'a', 'b', 'c'); + INSERT INTO t1(rowid, a, b, c) VALUES(-2, 'd', 'e', 'f'); + } + + db eval $idx + + do_test 2.$tn.2 { + forcedelete ota.db + sqlite3 db2 ota.db + db2 eval { + CREATE TABLE data_t1(ota_rowid, a, b, c, ota_control); + INSERT INTO data_t1 VALUES(3, 'x', 'y', 'z', 0); + INSERT INTO data_t1 VALUES(NULL, 'X', 'Y', 'Z', 0); + INSERT INTO data_t1 VALUES('1', NULL, NULL, NULL, 1); + INSERT INTO data_t1 VALUES(-2, NULL, NULL, 'fff', '..x'); + } + db2 close + } {} + + run_ota test.db ota.db + + do_execsql_test 2.$tn.3 { + SELECT rowid, a, b, c FROM t1 ORDER BY rowid; + } { + -2 d e fff + -1 a b c + 2 4 5 6 + 3 x y z + 4 X Y Z + } + + integrity_check 2.$tn.4 +} + + +finish_test + diff --git a/ext/ota/otaA.test b/ext/ota/otaA.test new file mode 100644 index 000000000..c76609035 --- /dev/null +++ b/ext/ota/otaA.test @@ -0,0 +1,83 @@ +# 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 + breakpoint + 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 + diff --git a/ext/ota/otacrash.test b/ext/ota/otacrash.test new file mode 100644 index 000000000..9474a9915 --- /dev/null +++ b/ext/ota/otacrash.test @@ -0,0 +1,141 @@ +# 2014 October 22 +# +# 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 otacrash + +# Set up a target database and an ota update database. The target +# db is the usual "test.db", the ota db is "test.db2". +# +forcedelete test.db2 +do_execsql_test 1.0 { + CREATE TABLE t1(a, b, c, PRIMARY KEY(a), UNIQUE(b)); + INSERT INTO t1 VALUES(1, 2, 3); + INSERT INTO t1 VALUES(4, 5, 6); + INSERT INTO t1 VALUES(7, 8, 9); + + ATTACH 'test.db2' AS ota; + CREATE TABLE ota.data_t1(a, b, c, ota_control); + INSERT INTO data_t1 VALUES(10, 11, 12, 0); + INSERT INTO data_t1 VALUES(13, 14, 15, 0); + INSERT INTO data_t1 VALUES(4, NULL, NULL, 1); + INSERT INTO data_t1 VALUES(1, NULL, 100, '..x'); +} +db_save_and_close + + +# Determine the number of steps in applying the ota update to the test +# target database created above. Set $::ota_num_steps accordingly +# +# Check that the same number of steps are required to apply the ota +# update using many calls to sqlite3ota_step() on a single ota handle +# as required to apply it using a series of ota handles, on each of +# which sqlite3ota_step() is called once. +# +do_test 1.1 { + db_restore + sqlite3ota ota test.db test.db2 + breakpoint + set nStep 0 + while {[ota step]=="SQLITE_OK"} { incr nStep } + ota close +} {SQLITE_DONE} +set ota_num_steps $nStep +do_test 1.2 { + db_restore + set nStep 0 + while {1} { + sqlite3ota ota test.db test.db2 + ota step + if {[ota close]=="SQLITE_DONE"} break + incr nStep + } + set nStep +} $ota_num_steps + + +# Run one or more tests using the target (test.db) and ota (test.db2) +# databases created above. As follows: +# +# 1. This process starts the ota update and calls sqlite3ota_step() +# $nPre times. Then closes the ota update handle. +# +# 2. A second process resumes the ota update and attempts to call +# sqlite3ota_step() $nStep times before closing the handle. A +# crash is simulated during each xSync() of file test.db2. +# +# 3. This process attempts to resume the ota update from whatever +# state it was left in by step (2). Test that it is successful +# in doing so and that the final target database is as expected. +# +# In total (nSync+1) tests are run, where nSync is the number of times +# xSync() is called on test.db2. +# +proc do_ota_crash_test {tn nPre nStep} { + + set script [subst -nocommands { + sqlite3ota ota test.db file:test.db2?vfs=crash + set i 0 + while {[set i] < $nStep} { + if {[ota step]!="SQLITE_OK"} break + incr i + } + ota close + }] + + set bDone 0 + for {set iDelay 1} {$bDone==0} {incr iDelay} { + forcedelete test.db2 test.db2-journal test.db test.db-oal test.db-wal + db_restore + + if {$nPre>0} { + sqlite3ota ota test.db file:test.db2 + set i 0 + for {set i 0} {$i < $nPre} {incr i} { + if {[ota step]!="SQLITE_OK"} break + } + ota close + } + + set res [crashsql -file test.db2 -delay $iDelay -tclbody $script {}] + + set bDone 1 + if {$res == "1 {child process exited abnormally}"} { + set bDone 0 + } elseif {$res != "0 {}"} { + error "unexected catchsql result: $res" + } + + sqlite3ota ota test.db test.db2 + while {[ota step]=="SQLITE_OK"} {} + ota close + + sqlite3 db test.db + do_execsql_test $tn.delay=$iDelay { + SELECT * FROM t1; + PRAGMA integrity_check; + } {1 2 100 7 8 9 10 11 12 13 14 15 ok} + db close + } +} + +for {set nPre 0} {$nPre < $ota_num_steps} {incr nPre} { + for {set is 1} {$is <= ($ota_num_steps - $nPre)} {incr is} { + do_ota_crash_test 2.pre=$nPre.step=$is $nPre $is + } +} + +finish_test + diff --git a/ext/ota/otafault.test b/ext/ota/otafault.test new file mode 100644 index 000000000..de6939a55 --- /dev/null +++ b/ext/ota/otafault.test @@ -0,0 +1,237 @@ +# 2014 October 22 +# +# 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 +source $testdir/malloc_common.tcl +set ::testprefix otafault + +proc copy_if_exists {src target} { + if {[file exists $src]} { + forcecopy $src $target + } else { + forcedelete $target + } +} + +foreach {tn2 setup sql expect} { + 1 { + CREATE TABLE t1(a INTEGER PRIMARY KEY, b, c); + CREATE INDEX t1cb ON t1(c, b); + INSERT INTO t1 VALUES(1, 1, 1); + INSERT INTO t1 VALUES(2, 2, 2); + INSERT INTO t1 VALUES(3, 3, 3); + + CREATE TABLE ota.data_t1(a, b, c, ota_control); + INSERT INTO data_t1 VALUES(2, NULL, NULL, 1); + INSERT INTO data_t1 VALUES(3, 'three', NULL, '.x.'); + INSERT INTO data_t1 VALUES(4, 4, 4, 0); + } { + SELECT * FROM t1 + } {1 1 1 3 three 3 4 4 4} + + 2 { + CREATE TABLE t2(a PRIMARY KEY, b, c) WITHOUT ROWID; + CREATE INDEX t2cb ON t2(c, b); + INSERT INTO t2 VALUES('a', 'a', 'a'); + INSERT INTO t2 VALUES('b', 'b', 'b'); + INSERT INTO t2 VALUES('c', 'c', 'c'); + + CREATE TABLE ota.data_t2(a, b, c, ota_control); + INSERT INTO data_t2 VALUES('b', NULL, NULL, 1); + INSERT INTO data_t2 VALUES('c', 'see', NULL, '.x.'); + INSERT INTO data_t2 VALUES('d', 'd', 'd', 0); + } { + SELECT * FROM t2 + } {a a a c see c d d d} + + 3 { + CREATE TABLE t1(a INTEGER PRIMARY KEY, b, c); + CREATE TABLE t2(a PRIMARY KEY, b, c) WITHOUT ROWID; + CREATE INDEX t1cb ON t1(c, b); + CREATE INDEX t2cb ON t2(c, b); + + CREATE TABLE ota.data_t1(a, b, c, ota_control); + CREATE TABLE ota.data_t2(a, b, c, ota_control); + INSERT INTO data_t1 VALUES(1, 2, 3, 0); + INSERT INTO data_t2 VALUES(4, 5, 6, 0); + } { + SELECT * FROM t1 UNION ALL SELECT * FROM t2 + } {1 2 3 4 5 6} + + 4 { + CREATE TABLE t1(a PRIMARY KEY, b, c); + CREATE INDEX t1c ON t1(c); + INSERT INTO t1 VALUES('A', 'B', 'C'); + INSERT INTO t1 VALUES('D', 'E', 'F'); + + CREATE TABLE ota.data_t1(a, b, c, ota_control); + INSERT INTO data_t1 VALUES('D', NULL, NULL, 1); + INSERT INTO data_t1 VALUES('A', 'Z', NULL, '.x.'); + INSERT INTO data_t1 VALUES('G', 'H', 'I', 0); + } { + SELECT * FROM t1 ORDER BY a; + } {A Z C G H I} + + 5 { + CREATE TABLE t1(a, b, c); + CREATE INDEX t1c ON t1(c, b); + + CREATE TABLE ota.data_t1(a, b, c, ota_rowid, ota_control); + INSERT INTO data_t1 VALUES('a', 'b', 'c', 1, 0); + INSERT INTO data_t1 VALUES('d', 'e', 'f', '2', 0); + } { + SELECT * FROM t1 ORDER BY a; + } {a b c d e f} + +} { + catch {db close} + forcedelete ota.db test.db + sqlite3 db test.db + execsql { + PRAGMA encoding = utf16; + ATTACH 'ota.db' AS ota; + } + execsql $setup + db close + + forcecopy test.db test.db.bak + forcecopy ota.db ota.db.bak + + foreach {tn f reslist} { + 1 oom-tra* { + {0 SQLITE_DONE} + {1 {SQLITE_NOMEM - out of memory}} + {1 SQLITE_NOMEM} + {1 SQLITE_IOERR_NOMEM} + {1 {SQLITE_NOMEM - unable to open a temporary database file for storing temporary tables}} + } + + 2 ioerr-* { + {0 SQLITE_DONE} + {1 {SQLITE_IOERR - disk I/O error}} + {1 SQLITE_IOERR} + {1 SQLITE_IOERR_WRITE} + {1 SQLITE_IOERR_READ} + {1 SQLITE_IOERR_FSYNC} + {1 {SQLITE_ERROR - SQL logic error or missing database}} + {1 {SQLITE_ERROR - unable to open database: ota.db}} + {1 {SQLITE_IOERR - unable to open database: ota.db}} + } + + 3 shmerr-* { + {0 SQLITE_DONE} + {1 {SQLITE_IOERR - disk I/O error}} + {1 SQLITE_IOERR} + } + } { + + catch {db close} + sqlite3_shutdown + set lookaside_config [sqlite3_config_lookaside 0 0] + sqlite3_initialize + autoinstall_test_functions + + do_faultsim_test 2.$tn2 -faults $::f -prep { + catch { db close } + forcedelete test.db-journal test.db-wal ota.db-journal ota.db-wal + forcecopy test.db.bak test.db + forcecopy ota.db.bak ota.db + } -body { + sqlite3ota ota test.db ota.db + while {[ota step]=="SQLITE_OK"} {} + ota close + } -test { + faultsim_test_result {*}$::reslist + if {$testrc==0} { + sqlite3 db test.db + faultsim_integrity_check + set res [db eval $::sql] + if {$res != [list {*}$::expect]} { + puts "" + puts "res: $res" + puts "exp: $expect" + error "data not as expected!" + } + } + } + + catch {db close} + sqlite3_shutdown + sqlite3_config_lookaside {*}$lookaside_config + sqlite3_initialize + autoinstall_test_functions + + + for {set iStep 0} {$iStep<=21} {incr iStep} { + + forcedelete test.db-journal test.db-wal ota.db-journal ota.db-wal + + copy_if_exists test.db.bak test.db + copy_if_exists ota.db.bak ota.db + + sqlite3ota ota test.db ota.db + for {set x 0} {$x < $::iStep} {incr x} { ota step } + ota close + +# sqlite3 x ota.db ; puts "XYZ [x eval { SELECT * FROM ota_state } ]" ; x close + + copy_if_exists test.db test.db.bak.2 + copy_if_exists test.db-wal test.db.bak.2-wal + copy_if_exists test.db-oal test.db.bak.2-oal + copy_if_exists ota.db ota.db.bak.2 + + do_faultsim_test 3.$tn.$iStep -faults $::f -prep { + catch { db close } + forcedelete test.db-journal test.db-wal ota.db-journal ota.db-wal + copy_if_exists test.db.bak.2 test.db + copy_if_exists test.db.bak.2-wal test.db-wal + copy_if_exists test.db.bak.2-oal test.db-oal + copy_if_exists ota.db.bak.2 ota.db + } -body { + sqlite3ota ota test.db ota.db + ota step + ota close + } -test { + + if {$testresult=="SQLITE_OK"} {set testresult "SQLITE_DONE"} + faultsim_test_result {*}$::reslist + + if {$testrc==0} { + # No error occurred. If the OTA has not already been fully applied, + # apply the rest of it now. Then ensure that the final state of the + # target db is as expected. And that "PRAGMA integrity_check" + # passes. + sqlite3ota ota test.db ota.db + while {[ota step] == "SQLITE_OK"} {} + ota close + + sqlite3 db test.db + faultsim_integrity_check + + set res [db eval $::sql] + if {$res != [list {*}$::expect]} { + puts "" + puts "res: $res" + puts "exp: $::expect" + error "data not as expected!" + } + } + } + } + } +} + +finish_test + diff --git a/ext/ota/otafault2.test b/ext/ota/otafault2.test new file mode 100644 index 000000000..659cfec1f --- /dev/null +++ b/ext/ota/otafault2.test @@ -0,0 +1,58 @@ +# 2014 October 22 +# +# 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 +source $testdir/malloc_common.tcl +set ::testprefix otafault2 + +forcedelete ota.db +do_execsql_test 1.0 { + CREATE TABLE target(x UNIQUE, y, z, PRIMARY KEY(y)); + INSERT INTO target VALUES(1, 2, 3); + INSERT INTO target VALUES(4, 5, 6); + + ATTACH 'ota.db' AS ota; + CREATE TABLE ota.data_target(x, y, z, ota_control); + INSERT INTO data_target VALUES(7, 8, 9, 0); + INSERT INTO data_target VALUES(1, 11, 12, 0); + DETACH ota; +} +db close + +forcecopy test.db test.db-bak +forcecopy ota.db ota.db-bak + +do_faultsim_test 1 -faults oom* -prep { + forcecopy test.db-bak test.db + forcecopy ota.db-bak ota.db + forcedelete test.db-oal test.db-wal ota.db-journal + sqlite3ota ota test.db ota.db +} -body { + while {[ota step]=="SQLITE_OK"} { } + ota close +} -test { + faultsim_test_result \ + {1 {SQLITE_CONSTRAINT - UNIQUE constraint failed: target.x}} \ + {1 SQLITE_CONSTRAINT} \ + {1 SQLITE_NOMEM} \ + {1 {SQLITE_NOMEM - unable to open a temporary database file for storing temporary tables}} \ + {1 {SQLITE_NOMEM - out of memory}} +} + + + + +finish_test + diff --git a/ext/ota/sqlite3ota.c b/ext/ota/sqlite3ota.c new file mode 100644 index 000000000..92d8ecb99 --- /dev/null +++ b/ext/ota/sqlite3ota.c @@ -0,0 +1,3484 @@ +/* +** 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. +** +************************************************************************* +** +** +** OVERVIEW +** +** The OTA extension requires that the OTA update be packaged as an +** SQLite database. The tables it expects to find are described in +** sqlite3ota.h. Essentially, for each table xyz in the target database +** that the user wishes to write to, a corresponding data_xyz table is +** created in the OTA database and populated with one row for each row to +** update, insert or delete from the target table. +** +** The update proceeds in three stages: +** +** 1) The database is updated. The modified database pages are written +** to a *-oal file. A *-oal file is just like a *-wal file, except +** that it is named "<database>-oal" instead of "<database>-wal". +** Because regular SQLite clients do not look for file named +** "<database>-oal", they go on using the original database in +** rollback mode while the *-oal file is being generated. +** +** During this stage OTA does not update the database by writing +** directly to the target tables. Instead it creates "imposter" +** tables using the SQLITE_TESTCTRL_IMPOSTER interface that it uses +** to update each b-tree individually. All updates required by each +** b-tree are completed before moving on to the next, and all +** updates are done in sorted key order. +** +** 2) The "<database>-oal" file is moved to the equivalent "<database>-wal" +** location using a call to rename(2). Before doing this the OTA +** module takes an EXCLUSIVE lock on the database file, ensuring +** that there are no other active readers. +** +** Once the EXCLUSIVE lock is released, any other database readers +** detect the new *-wal file and read the database in wal mode. At +** this point they see the new version of the database - including +** the updates made as part of the OTA update. +** +** 3) The new *-wal file is checkpointed. This proceeds in the same way +** as a regular database checkpoint, except that a single frame is +** checkpointed each time sqlite3ota_step() is called. If the OTA +** handle is closed before the entire *-wal file is checkpointed, +** the checkpoint progress is saved in the OTA database and the +** checkpoint can be resumed by another OTA client at some point in +** the future. +** +** POTENTIAL PROBLEMS +** +** The rename() call might not be portable. And OTA is not currently +** syncing the directory after renaming the file. +** +** When state is saved, any commit to the *-oal file and the commit to +** the OTA update database are not atomic. So if the power fails at the +** wrong moment they might get out of sync. As the main database will be +** committed before the OTA update database this will likely either just +** pass unnoticed, or result in SQLITE_CONSTRAINT errors (due to UNIQUE +** constraint violations). +** +** If some client does modify the target database mid OTA update, or some +** other error occurs, the OTA extension will keep throwing errors. It's +** not really clear how to get out of this state. The system could just +** by delete the OTA update database and *-oal file and have the device +** download the update again and start over. +** +** At present, for an UPDATE, both the new.* and old.* records are +** collected in the ota_xyz table. And for both UPDATEs and DELETEs all +** fields are collected. This means we're probably writing a lot more +** data to disk when saving the state of an ongoing update to the OTA +** update database than is strictly necessary. +** +*/ + +#include <assert.h> +#include <string.h> +#include <stdio.h> +#include <unistd.h> + +#include "sqlite3.h" + +#if !defined(SQLITE_CORE) || defined(SQLITE_ENABLE_OTA) +#include "sqlite3ota.h" + +/* +** Swap two objects of type TYPE. +*/ +#if !defined(SQLITE_AMALGAMATION) +# define SWAP(TYPE,A,B) {TYPE t=A; A=B; B=t;} +#endif + +/* +** The ota_state table is used to save the state of a partially applied +** update so that it can be resumed later. The table consists of integer +** keys mapped to values as follows: +** +** OTA_STATE_STAGE: +** May be set to integer values 1, 2, 4 or 5. As follows: +** 1: the *-ota file is currently under construction. +** 2: the *-ota file has been constructed, but not yet moved +** to the *-wal path. +** 4: the checkpoint is underway. +** 5: the ota update has been checkpointed. +** +** OTA_STATE_TBL: +** Only valid if STAGE==1. The target database name of the table +** currently being written. +** +** OTA_STATE_IDX: +** Only valid if STAGE==1. The target database name of the index +** currently being written, or NULL if the main table is currently being +** updated. +** +** OTA_STATE_ROW: +** Only valid if STAGE==1. Number of rows already processed for the current +** table/index. +** +** OTA_STATE_PROGRESS: +** Total number of sqlite3ota_step() calls made so far as part of this +** ota update. +** +** OTA_STATE_CKPT: +** Valid if STAGE==4. The 64-bit checksum associated with the wal-index +** header created by recovering the *-wal file. This is used to detect +** cases when another client appends frames to the *-wal file in the +** middle of an incremental checkpoint (an incremental checkpoint cannot +** be continued if this happens). +** +** OTA_STATE_COOKIE: +** Valid if STAGE==1. The current change-counter cookie value in the +** target db file. +** +** OTA_STATE_OALSZ: +** Valid if STAGE==1. The size in bytes of the *-oal 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_STATE_OALSZ 8 + +#define OTA_STAGE_OAL 1 +#define OTA_STAGE_MOVE 2 +#define OTA_STAGE_CAPTURE 3 +#define OTA_STAGE_CKPT 4 +#define OTA_STAGE_DONE 5 + + +#define OTA_CREATE_STATE "CREATE TABLE IF NOT EXISTS ota_state" \ + "(k INTEGER PRIMARY KEY, v)" + +typedef struct OtaFrame OtaFrame; +typedef struct OtaObjIter OtaObjIter; +typedef struct OtaState OtaState; +typedef struct ota_vfs ota_vfs; +typedef struct ota_file ota_file; + +#if !defined(SQLITE_AMALGAMATION) +typedef unsigned int u32; +typedef unsigned char u8; +typedef sqlite3_int64 i64; +#endif + +/* +** These values must match the values defined in wal.c for the equivalent +** locks. These are not magic numbers as they are part of the SQLite file +** format. +*/ +#define WAL_LOCK_WRITE 0 +#define WAL_LOCK_CKPT 1 +#define WAL_LOCK_READ0 3 + +/* +** A structure to store values read from the ota_state table in memory. +*/ +struct OtaState { + int eStage; + char *zTbl; + char *zIdx; + i64 iWalCksum; + int nRow; + i64 nProgress; + u32 iCookie; + i64 iOalSz; +}; + +/* +** An iterator of this type is used to iterate through all objects in +** the target database that require updating. For each such table, the +** iterator visits, in order: +** +** * the table itself, +** * each index of the table (zero or more points to visit), and +** * a special "cleanup table" state. +*/ +struct OtaObjIter { + sqlite3_stmt *pTblIter; /* Iterate through tables */ + sqlite3_stmt *pIdxIter; /* Index iterator */ + int nTblCol; /* Size of azTblCol[] array */ + char **azTblCol; /* Array of unquoted target column names */ + char **azTblType; /* Array of target column types */ + int *aiSrcOrder; /* src table col -> target table col */ + u8 *abTblPk; /* Array of flags, set on target PK columns */ + u8 *abNotNull; /* Array of flags, set on NOT NULL columns */ + int eType; /* Table type - an OTA_PK_XXX value */ + + /* Output variables. zTbl==0 implies EOF. */ + int bCleanup; /* True in "cleanup" state */ + const char *zTbl; /* Name of target db table */ + const char *zIdx; /* Name of target db index (or null) */ + int iTnum; /* Root page of current object */ + int iPkTnum; /* If eType==EXTERNAL, root of PK index */ + int bUnique; /* Current index is unique */ + + /* Statements created by otaObjIterPrepareAll() */ + int nCol; /* Number of columns in current object */ + sqlite3_stmt *pSelect; /* Source data */ + sqlite3_stmt *pInsert; /* Statement for INSERT operations */ + sqlite3_stmt *pDelete; /* Statement for DELETE ops */ + sqlite3_stmt *pTmpInsert; /* Insert into ota_tmp_$zTbl */ + + /* Last UPDATE used (for PK b-tree updates only), or NULL. */ + char *zMask; /* Copy of update mask used with pUpdate */ + sqlite3_stmt *pUpdate; /* Last update statement (or NULL) */ +}; + +/* +** Values for OtaObjIter.eType +** +** 0: Table does not exist (error) +** 1: Table has an implicit rowid. +** 2: Table has an explicit IPK column. +** 3: Table has an external PK index. +** 4: Table is WITHOUT ROWID. +** 5: Table is a virtual table. +*/ +#define OTA_PK_NOTABLE 0 +#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 + + +/* +** Within the OTA_STAGE_OAL stage, each call to sqlite3ota_step() performs +** one of the following operations. +*/ +#define OTA_INSERT 1 /* Insert on a main table b-tree */ +#define OTA_DELETE 2 /* Delete a row from a main table b-tree */ +#define OTA_IDX_DELETE 3 /* Delete a row from an aux. index b-tree */ +#define OTA_IDX_INSERT 4 /* Insert on an aux. index b-tree */ +#define OTA_UPDATE 5 /* Update a row in a main table b-tree */ + + +/* +** A single step of an incremental checkpoint - frame iWalFrame of the wal +** file should be copied to page iDbPage of the database file. +*/ +struct OtaFrame { + u32 iDbPage; + u32 iWalFrame; +}; + +/* +** OTA handle. +*/ +struct sqlite3ota { + int eStage; /* Value of OTA_STATE_STAGE field */ + sqlite3 *dbMain; /* target database handle */ + sqlite3 *dbOta; /* ota database handle */ + char *zTarget; /* Path to target db */ + char *zOta; /* Path to ota db */ + int rc; /* Value returned by last ota_step() call */ + 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 */ + const char *zVfsName; /* Name of automatically created ota vfs */ + ota_file *pTargetFd; /* File handle open on target db */ + i64 iOalSz; + + /* The following state variables are used as part of the incremental + ** checkpoint stage (eStage==OTA_STAGE_CKPT). See comments surrounding + ** function otaSetupCheckpoint() for details. */ + u32 iMaxFrame; /* Largest iWalFrame value in aFrame[] */ + u32 mLock; + int nFrame; /* Entries in aFrame[] array */ + int nFrameAlloc; /* Allocated size of aFrame[] array */ + OtaFrame *aFrame; + int pgsz; + u8 *aBuf; + i64 iWalCksum; +}; + +/* +** An ota VFS is implemented using an instance of this structure. +*/ +struct ota_vfs { + sqlite3_vfs base; /* ota VFS shim methods */ + sqlite3_vfs *pRealVfs; /* Underlying VFS */ + sqlite3_mutex *mutex; /* Mutex to protect pMain */ + ota_file *pMain; /* Linked list of main db files */ +}; + +/* +** Each file opened by an ota VFS is represented by an instance of +** the following structure. +*/ +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 */ + u32 iCookie; /* Cookie value for main db files */ + u8 iWriteVer; /* "write-version" value for main db files */ + + int nShm; /* Number of entries in apShm[] array */ + char **apShm; /* Array of mmap'd *-shm regions */ + char *zDel; /* Delete this when closing file */ + + const char *zWal; /* Wal filename for this main db file */ + ota_file *pWalFd; /* Wal file descriptor for this main db */ + ota_file *pMainNext; /* Next MAIN_DB file */ +}; + + +/* +** 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. +** +** Otherwise, if an error does occur, set *ppStmt to NULL and return +** an SQLite error code. Additionally, set output variable *pzErrmsg to +** point to a buffer containing an error message. It is the responsibility +** of the caller to (eventually) free this buffer using sqlite3_free(). +*/ +static int prepareAndCollectError( + sqlite3 *db, + sqlite3_stmt **ppStmt, + char **pzErrmsg, + const char *zSql +){ + int rc = sqlite3_prepare_v2(db, zSql, -1, ppStmt, 0); + if( rc!=SQLITE_OK ){ + *pzErrmsg = sqlite3_mprintf("%s", sqlite3_errmsg(db)); + *ppStmt = 0; + } + return rc; +} + +/* +** Reset the SQL statement passed as the first argument. Return a copy +** of the value returned by sqlite3_reset(). +** +** If an error has occurred, then set *pzErrmsg to point to a buffer +** containing an error message. It is the responsibility of the caller +** to eventually free this buffer using sqlite3_free(). +*/ +static int resetAndCollectError(sqlite3_stmt *pStmt, char **pzErrmsg){ + int rc = sqlite3_reset(pStmt); + if( rc!=SQLITE_OK ){ + *pzErrmsg = sqlite3_mprintf("%s", sqlite3_errmsg(sqlite3_db_handle(pStmt))); + } + return rc; +} + +/* +** Unless it is NULL, argument zSql points to a buffer allocated using +** sqlite3_malloc containing an SQL statement. This function prepares the SQL +** statement against database db and frees the buffer. If statement +** compilation is successful, *ppStmt is set to point to the new statement +** handle and SQLITE_OK is returned. +** +** Otherwise, if an error occurs, *ppStmt is set to NULL and an error code +** returned. In this case, *pzErrmsg may also be set to point to an error +** message. It is the responsibility of the caller to free this error message +** buffer using sqlite3_free(). +** +** If argument zSql is NULL, this function assumes that an OOM has occurred. +** In this case SQLITE_NOMEM is returned and *ppStmt set to NULL. +*/ +static int prepareFreeAndCollectError( + sqlite3 *db, + sqlite3_stmt **ppStmt, + char **pzErrmsg, + char *zSql +){ + int rc; + assert( *pzErrmsg==0 ); + if( zSql==0 ){ + rc = SQLITE_NOMEM; + *ppStmt = 0; + }else{ + rc = prepareAndCollectError(db, ppStmt, pzErrmsg, zSql); + sqlite3_free(zSql); + } + return rc; +} + +/* +** Free the OtaObjIter.azTblCol[] and OtaObjIter.abTblPk[] arrays allocated +** by an earlier call to otaObjIterCacheTableInfo(). +*/ +static void otaObjIterFreeCols(OtaObjIter *pIter){ + int i; + for(i=0; i<pIter->nTblCol; i++){ + sqlite3_free(pIter->azTblCol[i]); + sqlite3_free(pIter->azTblType[i]); + } + sqlite3_free(pIter->azTblCol); + pIter->azTblCol = 0; + pIter->azTblType = 0; + pIter->aiSrcOrder = 0; + pIter->abTblPk = 0; + pIter->abNotNull = 0; + pIter->nTblCol = 0; + sqlite3_free(pIter->zMask); + pIter->zMask = 0; + pIter->eType = 0; /* Invalid value */ +} + +/* +** Finalize all statements and free all allocations that are specific to +** the current object (table/index pair). +*/ +static void otaObjIterClearStatements(OtaObjIter *pIter){ + sqlite3_finalize(pIter->pSelect); + sqlite3_finalize(pIter->pInsert); + sqlite3_finalize(pIter->pDelete); + sqlite3_finalize(pIter->pUpdate); + sqlite3_finalize(pIter->pTmpInsert); + pIter->pSelect = 0; + pIter->pInsert = 0; + pIter->pDelete = 0; + pIter->pUpdate = 0; + pIter->pTmpInsert = 0; + pIter->nCol = 0; +} + +/* +** Clean up any resources allocated as part of the iterator object passed +** as the only argument. +*/ +static void otaObjIterFinalize(OtaObjIter *pIter){ + otaObjIterClearStatements(pIter); + sqlite3_finalize(pIter->pTblIter); + sqlite3_finalize(pIter->pIdxIter); + otaObjIterFreeCols(pIter); + memset(pIter, 0, sizeof(OtaObjIter)); +} + +/* +** Advance the iterator to the next position. +** +** If no error occurs, SQLITE_OK is returned and the iterator is left +** pointing to the next entry. Otherwise, an error code and message is +** left in the OTA handle passed as the first argument. A copy of the +** error code is returned. +*/ +static int otaObjIterNext(sqlite3ota *p, OtaObjIter *pIter){ + int rc = p->rc; + if( rc==SQLITE_OK ){ + + /* Free any SQLite statements used while processing the previous object */ + otaObjIterClearStatements(pIter); + if( pIter->zIdx==0 ){ + rc = sqlite3_exec(p->dbMain, + "DROP TRIGGER IF EXISTS temp.ota_insert_tr;" + "DROP TRIGGER IF EXISTS temp.ota_update1_tr;" + "DROP TRIGGER IF EXISTS temp.ota_update2_tr;" + "DROP TRIGGER IF EXISTS temp.ota_delete_tr;" + , 0, 0, &p->zErrmsg + ); + } + + if( rc==SQLITE_OK ){ + if( pIter->bCleanup ){ + otaObjIterFreeCols(pIter); + pIter->bCleanup = 0; + rc = sqlite3_step(pIter->pTblIter); + if( rc!=SQLITE_ROW ){ + rc = resetAndCollectError(pIter->pTblIter, &p->zErrmsg); + pIter->zTbl = 0; + }else{ + pIter->zTbl = (const char*)sqlite3_column_text(pIter->pTblIter, 0); + rc = pIter->zTbl ? SQLITE_OK : SQLITE_NOMEM; + } + }else{ + if( pIter->zIdx==0 ){ + sqlite3_stmt *pIdx = pIter->pIdxIter; + rc = sqlite3_bind_text(pIdx, 1, pIter->zTbl, -1, SQLITE_STATIC); + } + if( rc==SQLITE_OK ){ + rc = sqlite3_step(pIter->pIdxIter); + if( rc!=SQLITE_ROW ){ + rc = resetAndCollectError(pIter->pIdxIter, &p->zErrmsg); + pIter->bCleanup = 1; + pIter->zIdx = 0; + }else{ + pIter->zIdx = (const char*)sqlite3_column_text(pIter->pIdxIter, 0); + pIter->iTnum = sqlite3_column_int(pIter->pIdxIter, 1); + pIter->bUnique = sqlite3_column_int(pIter->pIdxIter, 2); + rc = pIter->zIdx ? SQLITE_OK : SQLITE_NOMEM; + } + } + } + } + } + + if( rc!=SQLITE_OK ){ + otaObjIterFinalize(pIter); + p->rc = rc; + } + return rc; +} + +/* +** Initialize the iterator structure passed as the second argument. +** +** If no error occurs, SQLITE_OK is returned and the iterator is left +** pointing to the first entry. Otherwise, an error code and message is +** left in the OTA handle passed as the first argument. A copy of the +** error code is returned. +*/ +static int otaObjIterFirst(sqlite3ota *p, OtaObjIter *pIter){ + int rc; + memset(pIter, 0, sizeof(OtaObjIter)); + + rc = prepareAndCollectError(p->dbOta, &pIter->pTblIter, &p->zErrmsg, + "SELECT substr(name, 6) FROM sqlite_master " + "WHERE type='table' AND name LIKE 'data_%'" + ); + + if( rc==SQLITE_OK ){ + rc = prepareAndCollectError(p->dbMain, &pIter->pIdxIter, &p->zErrmsg, + "SELECT name, rootpage, sql IS NULL OR substr(8, 6)=='UNIQUE' " + " FROM main.sqlite_master " + " WHERE type='index' AND tbl_name = ?" + ); + } + + pIter->bCleanup = 1; + p->rc = rc; + return otaObjIterNext(p, pIter); +} + +/* +** This is a wrapper around "sqlite3_mprintf(zFmt, ...)". If an OOM occurs, +** an error code is stored in the OTA handle passed as the first argument. +** +** If an error has already occurred (p->rc is already set to something other +** than SQLITE_OK), then this function returns NULL without modifying the +** stored error code. In this case it still calls sqlite3_free() on any +** printf() parameters associated with %z conversions. +*/ +static char *otaMPrintf(sqlite3ota *p, const char *zFmt, ...){ + char *zSql = 0; + va_list ap; + va_start(ap, zFmt); + zSql = sqlite3_vmprintf(zFmt, ap); + if( p->rc==SQLITE_OK ){ + if( zSql==0 ) p->rc = SQLITE_NOMEM; + }else{ + sqlite3_free(zSql); + zSql = 0; + } + va_end(ap); + return zSql; +} + +/* +** Argument zFmt is a sqlite3_mprintf() style format string. The trailing +** arguments are the usual subsitution values. This function performs +** the printf() style substitutions and executes the result as an SQL +** statement on the OTA handles database. +** +** If an error occurs, an error code and error message is stored in the +** OTA handle. If an error has already occurred when this function is +** called, it is a no-op. +*/ +static int otaMPrintfExec(sqlite3ota *p, sqlite3 *db, const char *zFmt, ...){ + va_list ap; + va_start(ap, zFmt); + char *zSql = sqlite3_vmprintf(zFmt, ap); + if( p->rc==SQLITE_OK ){ + if( zSql==0 ){ + p->rc = SQLITE_NOMEM; + }else{ + p->rc = sqlite3_exec(db, zSql, 0, 0, &p->zErrmsg); + } + } + sqlite3_free(zSql); + va_end(ap); + return p->rc; +} + +/* +** Attempt to allocate and return a pointer to a zeroed block of nByte +** bytes. +** +** If an error (i.e. an OOM condition) occurs, return NULL and leave an +** error code in the ota handle passed as the first argument. Or, if an +** error has already occurred when this function is called, return NULL +** immediately without attempting the allocation or modifying the stored +** error code. +*/ +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. +*/ +static void otaAllocateIterArrays(sqlite3ota *p, OtaObjIter *pIter, int nCol){ + int nByte = (2*sizeof(char*) + sizeof(int) + 2*sizeof(u8)) * nCol; + char **azNew; + + azNew = (char**)otaMalloc(p, nByte); + if( azNew ){ + pIter->azTblCol = azNew; + pIter->azTblType = &azNew[nCol]; + pIter->aiSrcOrder = (int*)&pIter->azTblType[nCol]; + pIter->abTblPk = (u8*)&pIter->aiSrcOrder[nCol]; + pIter->abNotNull = (u8*)&pIter->abTblPk[nCol]; + } +} + +/* +** The first argument must be a nul-terminated string. This function +** returns a copy of the string in memory obtained from sqlite3_malloc(). +** It is the responsibility of the caller to eventually free this memory +** using sqlite3_free(). +** +** If an OOM condition is encountered when attempting to allocate memory, +** output variable (*pRc) is set to SQLITE_NOMEM before returning. Otherwise, +** if the allocation succeeds, (*pRc) is left unchanged. +*/ +static char *otaStrndup(const char *zStr, int *pRc){ + char *zRet = 0; + + assert( *pRc==SQLITE_OK ); + if( zStr ){ + int nCopy = strlen(zStr) + 1; + zRet = (char*)sqlite3_malloc(nCopy); + if( zRet ){ + memcpy(zRet, zStr, nCopy); + }else{ + *pRc = SQLITE_NOMEM; + } + } + + return zRet; +} + +/* +** Finalize the statement passed as the second argument. +** +** If the sqlite3_finalize() call indicates that an error occurs, and the +** ota handle error code is not already set, set the error code and error +** message accordingly. +*/ +static void otaFinalize(sqlite3ota *p, sqlite3_stmt *pStmt){ + sqlite3 *db = sqlite3_db_handle(pStmt); + int rc = sqlite3_finalize(pStmt); + if( p->rc==SQLITE_OK && rc!=SQLITE_OK ){ + p->rc = rc; + p->zErrmsg = sqlite3_mprintf("%s", sqlite3_errmsg(db)); + } +} + +/* Determine the type of a table. +** +** peType is of type (int*), a pointer to an output parameter of type +** (int). This call sets the output parameter as follows, depending +** on the type of the table specified by parameters dbName and zTbl. +** +** OTA_PK_NOTABLE: No such table. +** OTA_PK_NONE: Table has an implicit rowid. +** OTA_PK_IPK: Table has an explicit IPK column. +** OTA_PK_EXTERNAL: Table has an external PK index. +** OTA_PK_WITHOUT_ROWID: Table is WITHOUT ROWID. +** OTA_PK_VTAB: Table is a virtual table. +** +** Argument *piPk is also of type (int*), and also points to an output +** parameter. Unless the table has an external primary key index +** (i.e. unless *peType is set to 3), then *piPk is set to zero. Or, +** if the table does have an external primary key index, then *piPk +** is set to the root page number of the primary key index before +** returning. +** +** ALGORITHM: +** +** if( no entry exists in sqlite_master ){ +** return OTA_PK_NOTABLE +** }else if( sql for the entry starts with "CREATE VIRTUAL" ){ +** return OTA_PK_VTAB +** }else if( "PRAGMA index_list()" for the table contains a "pk" index ){ +** if( the index that is the pk exists in sqlite_master ){ +** *piPK = rootpage of that index. +** return OTA_PK_EXTERNAL +** }else{ +** return OTA_PK_WITHOUT_ROWID +** } +** }else if( "PRAGMA table_info()" lists one or more "pk" columns ){ +** return OTA_PK_IPK +** }else{ +** return OTA_PK_NONE +** } +*/ +static void otaTableType( + sqlite3ota *p, + const char *zTab, + int *peType, + int *piTnum, + int *piPk +){ + /* + ** 0) SELECT count(*) FROM sqlite_master where name=%Q AND IsVirtual(%Q) + ** 1) PRAGMA index_list = ? + ** 2) SELECT count(*) FROM sqlite_master where name=%Q + ** 3) PRAGMA table_info = ? + */ + sqlite3_stmt *aStmt[4] = {0, 0, 0, 0}; + + *peType = OTA_PK_NOTABLE; + *piPk = 0; + + assert( p->rc==SQLITE_OK ); + p->rc = prepareFreeAndCollectError(p->dbMain, &aStmt[0], &p->zErrmsg, + sqlite3_mprintf( + "SELECT (sql LIKE 'create virtual%%'), rootpage" + " FROM sqlite_master" + " WHERE name=%Q", zTab + )); + if( p->rc!=SQLITE_OK || sqlite3_step(aStmt[0])!=SQLITE_ROW ){ + /* Either an error, or no such table. */ + goto otaTableType_end; + } + if( sqlite3_column_int(aStmt[0], 0) ){ + *peType = OTA_PK_VTAB; /* virtual table */ + goto otaTableType_end; + } + *piTnum = sqlite3_column_int(aStmt[0], 1); + + p->rc = prepareFreeAndCollectError(p->dbMain, &aStmt[1], &p->zErrmsg, + sqlite3_mprintf("PRAGMA index_list=%Q",zTab) + ); + if( p->rc ) goto otaTableType_end; + while( sqlite3_step(aStmt[1])==SQLITE_ROW ){ + const u8 *zOrig = sqlite3_column_text(aStmt[1], 3); + const u8 *zIdx = sqlite3_column_text(aStmt[1], 1); + if( zOrig && zIdx && zOrig[0]=='p' ){ + p->rc = prepareFreeAndCollectError(p->dbMain, &aStmt[2], &p->zErrmsg, + sqlite3_mprintf( + "SELECT rootpage FROM sqlite_master WHERE name = %Q", zIdx + )); + if( p->rc==SQLITE_OK ){ + if( sqlite3_step(aStmt[2])==SQLITE_ROW ){ + *piPk = sqlite3_column_int(aStmt[2], 0); + *peType = OTA_PK_EXTERNAL; + }else{ + *peType = OTA_PK_WITHOUT_ROWID; + } + } + goto otaTableType_end; + } + } + + p->rc = prepareFreeAndCollectError(p->dbMain, &aStmt[3], &p->zErrmsg, + sqlite3_mprintf("PRAGMA table_info=%Q",zTab) + ); + if( p->rc==SQLITE_OK ){ + while( sqlite3_step(aStmt[3])==SQLITE_ROW ){ + if( sqlite3_column_int(aStmt[3],5)>0 ){ + *peType = OTA_PK_IPK; /* explicit IPK column */ + goto otaTableType_end; + } + } + *peType = OTA_PK_NONE; + } + +otaTableType_end: { + int i; + for(i=0; i<sizeof(aStmt)/sizeof(aStmt[0]); i++){ + otaFinalize(p, aStmt[i]); + } + } +} + + +/* +** If they are not already populated, populate the pIter->azTblCol[], +** pIter->abTblPk[], pIter->nTblCol and pIter->bRowid variables according to +** the table (not index) that the iterator currently points to. +** +** Return SQLITE_OK if successful, or an SQLite error code otherwise. If +** an error does occur, an error code and error message are also left in +** the OTA handle. +*/ +static int otaObjIterCacheTableInfo(sqlite3ota *p, OtaObjIter *pIter){ + if( pIter->azTblCol==0 ){ + sqlite3_stmt *pStmt = 0; + int nCol = 0; + int i; /* for() loop iterator variable */ + int bOtaRowid = 0; /* If input table has column "ota_rowid" */ + int iOrder = 0; + int iTnum = 0; + + /* Figure out the type of table this step will deal with. */ + assert( pIter->eType==0 ); + otaTableType(p, pIter->zTbl, &pIter->eType, &iTnum, &pIter->iPkTnum); + if( p->rc ) return p->rc; + if( pIter->zIdx==0 ) pIter->iTnum = iTnum; + + assert( pIter->eType==OTA_PK_NONE || pIter->eType==OTA_PK_IPK + || pIter->eType==OTA_PK_EXTERNAL || pIter->eType==OTA_PK_WITHOUT_ROWID + || pIter->eType==OTA_PK_VTAB + ); + + /* Populate the azTblCol[] and nTblCol variables based on the columns + ** of the input table. Ignore any input table columns that begin with + ** "ota_". */ + p->rc = prepareFreeAndCollectError(p->dbOta, &pStmt, &p->zErrmsg, + sqlite3_mprintf("SELECT * FROM 'data_%q'", pIter->zTbl) + ); + if( p->rc==SQLITE_OK ){ + nCol = sqlite3_column_count(pStmt); + otaAllocateIterArrays(p, pIter, nCol); + } + for(i=0; p->rc==SQLITE_OK && i<nCol; i++){ + const char *zName = (const char*)sqlite3_column_name(pStmt, i); + if( sqlite3_strnicmp("ota_", zName, 4) ){ + char *zCopy = otaStrndup(zName, &p->rc); + pIter->aiSrcOrder[pIter->nTblCol] = pIter->nTblCol; + pIter->azTblCol[pIter->nTblCol++] = zCopy; + } + else if( 0==sqlite3_stricmp("ota_rowid", zName) ){ + bOtaRowid = 1; + } + } + sqlite3_finalize(pStmt); + pStmt = 0; + + if( p->rc==SQLITE_OK + && bOtaRowid!=(pIter->eType==OTA_PK_VTAB || pIter->eType==OTA_PK_NONE) + ){ + p->rc = SQLITE_ERROR; + p->zErrmsg = sqlite3_mprintf( + "table data_%q %s ota_rowid column", pIter->zTbl, + (bOtaRowid ? "may not have" : "requires") + ); + } + + /* Check that all non-HIDDEN columns in the destination table are also + ** present in the input table. Populate the abTblPk[], azTblType[] and + ** aiTblOrder[] arrays at the same time. */ + if( p->rc==SQLITE_OK ){ + p->rc = prepareFreeAndCollectError(p->dbMain, &pStmt, &p->zErrmsg, + sqlite3_mprintf("PRAGMA table_info(%Q)", pIter->zTbl) + ); + } + while( p->rc==SQLITE_OK && SQLITE_ROW==sqlite3_step(pStmt) ){ + const char *zName = (const char*)sqlite3_column_text(pStmt, 1); + if( zName==0 ) break; /* An OOM - finalize() below returns S_NOMEM */ + for(i=iOrder; i<pIter->nTblCol; i++){ + if( 0==strcmp(zName, pIter->azTblCol[i]) ) break; + } + if( i==pIter->nTblCol ){ + p->rc = SQLITE_ERROR; + p->zErrmsg = sqlite3_mprintf("column missing from data_%q: %s", + pIter->zTbl, zName + ); + }else{ + int iPk = sqlite3_column_int(pStmt, 5); + int bNotNull = sqlite3_column_int(pStmt, 3); + const char *zType = (const char*)sqlite3_column_text(pStmt, 2); + + if( i!=iOrder ){ + SWAP(int, pIter->aiSrcOrder[i], pIter->aiSrcOrder[iOrder]); + SWAP(char*, pIter->azTblCol[i], pIter->azTblCol[iOrder]); + } + + pIter->azTblType[iOrder] = otaStrndup(zType, &p->rc); + pIter->abTblPk[iOrder] = (iPk!=0); + pIter->abNotNull[iOrder] = (u8)bNotNull || (iPk!=0); + iOrder++; + } + } + + otaFinalize(p, pStmt); + } + + return p->rc; +} + +/* +** This function constructs and returns a pointer to a nul-terminated +** string containing some SQL clause or list based on one or more of the +** column names currently stored in the pIter->azTblCol[] array. +*/ +static char *otaObjIterGetCollist( + sqlite3ota *p, /* OTA object */ + OtaObjIter *pIter /* Object iterator for column names */ +){ + char *zList = 0; + const char *zSep = ""; + int i; + for(i=0; i<pIter->nTblCol; i++){ + const char *z = pIter->azTblCol[i]; + zList = otaMPrintf(p, "%z%s\"%w\"", zList, zSep, z); + zSep = ", "; + } + return zList; +} + +/* +** This function is used to create a SELECT list (the list of SQL +** expressions that follows a SELECT keyword) for a SELECT statement +** used to read from an ota_xxx table while updating the index object +** currently indicated by the iterator object passed as the second +** argument. A "PRAGMA index_xinfo = <idxname>" statement is used to +** obtain the required information. +** +** If the index is of the following form: +** +** CREATE INDEX i1 ON t1(c, b COLLATE nocase); +** +** and "t1" is a table with an explicit INTEGER PRIMARY KEY column +** "ipk", the returned string is: +** +** "`c` COLLATE 'BINARY', `b` COLLATE 'NOCASE', `ipk` COLLATE 'BINARY'" +** +** As well as the returned string, three other malloc'd strings are +** returned via output parameters. As follows: +** +** pzImposterCols: ... +** pzImposterPk: ... +** pzWhere: ... +*/ +static char *otaObjIterGetIndexCols( + sqlite3ota *p, /* OTA object */ + OtaObjIter *pIter, /* Object iterator for column names */ + char **pzImposterCols, /* OUT: Columns for imposter table */ + char **pzImposterPk, /* OUT: Imposter PK clause */ + char **pzWhere, /* OUT: WHERE clause */ + int *pnBind /* OUT: Total number of columns */ +){ + int rc = p->rc; /* Error code */ + int rc2; /* sqlite3_finalize() return code */ + char *zRet = 0; /* String to return */ + char *zImpCols = 0; /* String to return via *pzImposterCols */ + char *zImpPK = 0; /* String to return via *pzImposterPK */ + char *zWhere = 0; /* String to return via *pzWhere */ + int nBind = 0; /* Value to return via *pnBind */ + const char *zCom = ""; /* Set to ", " later on */ + const char *zAnd = ""; /* Set to " AND " later on */ + sqlite3_stmt *pXInfo = 0; /* PRAGMA index_xinfo = ? */ + + if( rc==SQLITE_OK ){ + assert( p->zErrmsg==0 ); + rc = prepareFreeAndCollectError(p->dbMain, &pXInfo, &p->zErrmsg, + sqlite3_mprintf("PRAGMA main.index_xinfo = %Q", pIter->zIdx) + ); + } + + while( rc==SQLITE_OK && SQLITE_ROW==sqlite3_step(pXInfo) ){ + int iCid = sqlite3_column_int(pXInfo, 1); + int bDesc = sqlite3_column_int(pXInfo, 3); + const char *zCollate = (const char*)sqlite3_column_text(pXInfo, 4); + const char *zCol; + const char *zType; + + if( iCid<0 ){ + /* An integer primary key. If the table has an explicit IPK, use + ** its name. Otherwise, use "ota_rowid". */ + if( pIter->eType==OTA_PK_IPK ){ + int i; + for(i=0; pIter->abTblPk[i]==0; i++); + assert( i<pIter->nTblCol ); + zCol = pIter->azTblCol[i]; + }else{ + zCol = "ota_rowid"; + } + zType = "INTEGER"; + }else{ + zCol = pIter->azTblCol[iCid]; + zType = pIter->azTblType[iCid]; + } + + zRet = sqlite3_mprintf("%z%s\"%w\" COLLATE %Q", zRet, zCom, zCol, zCollate); + if( pIter->bUnique==0 || sqlite3_column_int(pXInfo, 5) ){ + const char *zOrder = (bDesc ? " DESC" : ""); + zImpPK = sqlite3_mprintf("%z%s\"ota_imp_%d%w\"%s", + zImpPK, zCom, nBind, zCol, zOrder + ); + } + zImpCols = sqlite3_mprintf("%z%s\"ota_imp_%d%w\" %s COLLATE %Q", + zImpCols, zCom, nBind, zCol, zType, zCollate + ); + zWhere = sqlite3_mprintf( + "%z%s\"ota_imp_%d%w\" IS ?", zWhere, zAnd, nBind, zCol + ); + if( zRet==0 || zImpPK==0 || zImpCols==0 || zWhere==0 ) rc = SQLITE_NOMEM; + zCom = ", "; + zAnd = " AND "; + nBind++; + } + + rc2 = sqlite3_finalize(pXInfo); + if( rc==SQLITE_OK ) rc = rc2; + + if( rc!=SQLITE_OK ){ + sqlite3_free(zRet); + sqlite3_free(zImpCols); + sqlite3_free(zImpPK); + sqlite3_free(zWhere); + zRet = 0; + zImpCols = 0; + zImpPK = 0; + zWhere = 0; + p->rc = rc; + } + + *pzImposterCols = zImpCols; + *pzImposterPk = zImpPK; + *pzWhere = zWhere; + *pnBind = nBind; + return zRet; +} + +/* +** Assuming the current table columns are "a", "b" and "c", and the zObj +** paramter is passed "old", return a string of the form: +** +** "old.a, old.b, old.b" +** +** With the column names escaped. +** +** For tables with implicit rowids - OTA_PK_EXTERNAL and OTA_PK_NONE, append +** the text ", old._rowid_" to the returned value. +*/ +static char *otaObjIterGetOldlist( + sqlite3ota *p, + OtaObjIter *pIter, + const char *zObj +){ + char *zList = 0; + if( p->rc==SQLITE_OK ){ + const char *zS = ""; + int i; + for(i=0; i<pIter->nTblCol; i++){ + const char *zCol = pIter->azTblCol[i]; + zList = sqlite3_mprintf("%z%s%s.\"%w\"", zList, zS, zObj, zCol); + zS = ", "; + if( zList==0 ){ + p->rc = SQLITE_NOMEM; + break; + } + } + + /* For a table with implicit rowids, append "old._rowid_" to the list. */ + if( pIter->eType==OTA_PK_EXTERNAL || pIter->eType==OTA_PK_NONE ){ + zList = otaMPrintf(p, "%z, %s._rowid_", zList, zObj); + } + } + return zList; +} + +/* +** Return an expression that can be used in a WHERE clause to match the +** primary key of the current table. For example, if the table is: +** +** CREATE TABLE t1(a, b, c, PRIMARY KEY(b, c)); +** +** Return the string: +** +** "b = ?1 AND c = ?2" +*/ +static char *otaObjIterGetWhere( + sqlite3ota *p, + OtaObjIter *pIter +){ + char *zList = 0; + if( pIter->eType==OTA_PK_VTAB || pIter->eType==OTA_PK_NONE ){ + zList = otaMPrintf(p, "_rowid_ = ?%d", pIter->nTblCol+1); + }else if( pIter->eType==OTA_PK_EXTERNAL ){ + const char *zSep = ""; + int i; + for(i=0; i<pIter->nTblCol; i++){ + if( pIter->abTblPk[i] ){ + zList = otaMPrintf(p, "%z%sc%d=?%d", zList, zSep, i, i+1); + zSep = " AND "; + } + } + zList = otaMPrintf(p, + "_rowid_ = (SELECT id FROM ota_imposter2 WHERE %z)", zList + ); + + }else{ + const char *zSep = ""; + int i; + for(i=0; i<pIter->nTblCol; i++){ + if( pIter->abTblPk[i] ){ + const char *zCol = pIter->azTblCol[i]; + zList = otaMPrintf(p, "%z%s\"%w\"=?%d", zList, zSep, zCol, i+1); + zSep = " AND "; + } + } + } + return zList; +} + +/* +** The SELECT statement iterating through the keys for the current object +** (p->objiter.pSelect) currently points to a valid row. However, there +** is something wrong with the ota_control value in the ota_control value +** stored in the (p->nCol+1)'th column. Set the error code and error message +** of the OTA handle to something reflecting this. +*/ +static void otaBadControlError(sqlite3ota *p){ + p->rc = SQLITE_ERROR; + p->zErrmsg = sqlite3_mprintf("invalid ota_control value"); +} + + +/* +** Return a nul-terminated string containing the comma separated list of +** assignments that should be included following the "SET" keyword of +** an UPDATE statement used to update the table object that the iterator +** passed as the second argument currently points to if the ota_control +** column of the data_xxx table entry is set to zMask. +** +** The memory for the returned string is obtained from sqlite3_malloc(). +** It is the responsibility of the caller to eventually free it using +** sqlite3_free(). +** +** If an OOM error is encountered when allocating space for the new +** string, an error code is left in the ota handle passed as the first +** argument and NULL is returned. Or, if an error has already occurred +** when this function is called, NULL is returned immediately, without +** attempting the allocation or modifying the stored error code. +*/ +static char *otaObjIterGetSetlist( + sqlite3ota *p, + OtaObjIter *pIter, + const char *zMask +){ + char *zList = 0; + if( p->rc==SQLITE_OK ){ + int i; + + if( strlen(zMask)!=pIter->nTblCol ){ + otaBadControlError(p); + }else{ + const char *zSep = ""; + for(i=0; i<pIter->nTblCol; i++){ + char c = zMask[pIter->aiSrcOrder[i]]; + if( c=='x' ){ + zList = otaMPrintf(p, "%z%s\"%w\"=?%d", + zList, zSep, pIter->azTblCol[i], i+1 + ); + zSep = ", "; + } + if( c=='d' ){ + zList = otaMPrintf(p, "%z%s\"%w\"=ota_delta(\"%w\", ?%d)", + zList, zSep, pIter->azTblCol[i], pIter->azTblCol[i], i+1 + ); + zSep = ", "; + } + } + } + } + return zList; +} + +/* +** Return a nul-terminated string consisting of nByte comma separated +** "?" expressions. For example, if nByte is 3, return a pointer to +** a buffer containing the string "?,?,?". +** +** The memory for the returned string is obtained from sqlite3_malloc(). +** It is the responsibility of the caller to eventually free it using +** sqlite3_free(). +** +** If an OOM error is encountered when allocating space for the new +** string, an error code is left in the ota handle passed as the first +** argument and NULL is returned. Or, if an error has already occurred +** when this function is called, NULL is returned immediately, without +** attempting the allocation or modifying the stored error code. +*/ +static char *otaObjIterGetBindlist(sqlite3ota *p, int nBind){ + char *zRet = 0; + int nByte = nBind*2 + 1; + + zRet = (char*)otaMalloc(p, nByte); + if( zRet ){ + int i; + for(i=0; i<nBind; i++){ + zRet[i*2] = '?'; + zRet[i*2+1] = (i+1==nBind) ? '\0' : ','; + } + } + return zRet; +} + +/* +** The iterator currently points to a table (not index) of type +** OTA_PK_WITHOUT_ROWID. This function creates the PRIMARY KEY +** declaration for the corresponding imposter table. For example, +** if the iterator points to a table created as: +** +** CREATE TABLE t1(a, b, c, PRIMARY KEY(b, a DESC)) WITHOUT ROWID +** +** this function returns: +** +** PRIMARY KEY("b", "a" DESC) +*/ +static char *otaWithoutRowidPK(sqlite3ota *p, OtaObjIter *pIter){ + char *z = 0; + assert( pIter->zIdx==0 ); + if( p->rc==SQLITE_OK ){ + const char *zSep = "PRIMARY KEY("; + sqlite3_stmt *pXList = 0; /* PRAGMA index_list = (pIter->zTbl) */ + sqlite3_stmt *pXInfo = 0; /* PRAGMA index_xinfo = <pk-index> */ + + p->rc = prepareFreeAndCollectError(p->dbMain, &pXList, &p->zErrmsg, + sqlite3_mprintf("PRAGMA main.index_list = %Q", pIter->zTbl) + ); + while( p->rc==SQLITE_OK && SQLITE_ROW==sqlite3_step(pXList) ){ + const char *zOrig = (const char*)sqlite3_column_text(pXList,3); + if( zOrig && strcmp(zOrig, "pk")==0 ){ + const char *zIdx = (const char*)sqlite3_column_text(pXList,1); + if( zIdx ){ + p->rc = prepareFreeAndCollectError(p->dbMain, &pXInfo, &p->zErrmsg, + sqlite3_mprintf("PRAGMA main.index_xinfo = %Q", zIdx) + ); + } + break; + } + } + otaFinalize(p, pXList); + + while( p->rc==SQLITE_OK && SQLITE_ROW==sqlite3_step(pXInfo) ){ + if( sqlite3_column_int(pXInfo, 5) ){ + /* int iCid = sqlite3_column_int(pXInfo, 0); */ + const char *zCol = (const char*)sqlite3_column_text(pXInfo, 2); + const char *zDesc = sqlite3_column_int(pXInfo, 3) ? " DESC" : ""; + z = otaMPrintf(p, "%z%s\"%w\"%s", z, zSep, zCol, zDesc); + zSep = ", "; + } + } + z = otaMPrintf(p, "%z)", z); + otaFinalize(p, pXInfo); + } + return z; +} + +/* +** This function creates the second imposter table used when writing to +** a table b-tree where the table has an external primary key. If the +** iterator passed as the second argument does not currently point to +** a table (not index) with an external primary key, this function is a +** no-op. +** +** Assuming the iterator does point to a table with an external PK, this +** function creates a WITHOUT ROWID imposter table named "ota_imposter2" +** used to access that PK index. For example, if the target table is +** declared as follows: +** +** CREATE TABLE t1(a, b TEXT, c REAL, PRIMARY KEY(b, c)); +** +** then the imposter table schema is: +** +** CREATE TABLE ota_imposter2(c1 TEXT, c2 REAL, id INTEGER) WITHOUT ROWID; +** +*/ +static void otaCreateImposterTable2(sqlite3ota *p, OtaObjIter *pIter){ + if( p->rc==SQLITE_OK && pIter->eType==OTA_PK_EXTERNAL ){ + int tnum = pIter->iPkTnum; /* Root page of PK index */ + sqlite3_stmt *pQuery = 0; /* SELECT name ... WHERE rootpage = $tnum */ + const char *zIdx = 0; /* Name of PK index */ + sqlite3_stmt *pXInfo = 0; /* PRAGMA main.index_xinfo = $zIdx */ + const char *zComma = ""; + char *zCols = 0; /* Used to build up list of table cols */ + char *zPk = 0; /* Used to build up table PK declaration */ + + /* Figure out the name of the primary key index for the current table. + ** This is needed for the argument to "PRAGMA index_xinfo". Set + ** zIdx to point to a nul-terminated string containing this name. */ + p->rc = prepareAndCollectError(p->dbMain, &pQuery, &p->zErrmsg, + "SELECT name FROM sqlite_master WHERE rootpage = ?" + ); + if( p->rc==SQLITE_OK ){ + sqlite3_bind_int(pQuery, 1, tnum); + if( SQLITE_ROW==sqlite3_step(pQuery) ){ + zIdx = (const char*)sqlite3_column_text(pQuery, 0); + } + } + if( zIdx ){ + p->rc = prepareFreeAndCollectError(p->dbMain, &pXInfo, &p->zErrmsg, + sqlite3_mprintf("PRAGMA main.index_xinfo = %Q", zIdx) + ); + } + otaFinalize(p, pQuery); + + while( p->rc==SQLITE_OK && SQLITE_ROW==sqlite3_step(pXInfo) ){ + int bKey = sqlite3_column_int(pXInfo, 5); + if( bKey ){ + int iCid = sqlite3_column_int(pXInfo, 1); + int bDesc = sqlite3_column_int(pXInfo, 3); + const char *zCollate = (const char*)sqlite3_column_text(pXInfo, 4); + zCols = otaMPrintf(p, "%z%sc%d %s COLLATE %s", zCols, zComma, + iCid, pIter->azTblType[iCid], zCollate + ); + zPk = otaMPrintf(p, "%z%sc%d%s", zPk, zComma, iCid, bDesc?" DESC":""); + zComma = ", "; + } + } + zCols = otaMPrintf(p, "%z, id INTEGER", zCols); + otaFinalize(p, pXInfo); + + sqlite3_test_control(SQLITE_TESTCTRL_IMPOSTER, p->dbMain, "main", 1, tnum); + otaMPrintfExec(p, p->dbMain, + "CREATE TABLE ota_imposter2(%z, PRIMARY KEY(%z)) WITHOUT ROWID", + zCols, zPk + ); + sqlite3_test_control(SQLITE_TESTCTRL_IMPOSTER, p->dbMain, "main", 0, 0); + } +} + +/* +** If an error has already occurred when this function is called, it +** immediately returns zero (without doing any work). Or, if an error +** occurs during the execution of this function, it sets the error code +** in the sqlite3ota object indicated by the first argument and returns +** zero. +** +** The iterator passed as the second argument is guaranteed to point to +** a table (not an index) when this function is called. This function +** attempts to create any imposter table required to write to the main +** table b-tree of the table before returning. Non-zero is returned if +** an imposter table are created, or zero otherwise. +** +** An imposter table is required in all cases except OTA_PK_VTAB. Only +** virtual tables are written to directly. The imposter table has the +** same schema as the actual target table (less any UNIQUE constraints). +** More precisely, the "same schema" means the same columns, types, +** collation sequences. For tables that do not have an external PRIMARY +** KEY, it also means the same PRIMARY KEY declaration. +*/ +static void otaCreateImposterTable(sqlite3ota *p, OtaObjIter *pIter){ + if( p->rc==SQLITE_OK && pIter->eType!=OTA_PK_VTAB ){ + int tnum = pIter->iTnum; + const char *zComma = ""; + char *zSql = 0; + int iCol; + sqlite3_test_control(SQLITE_TESTCTRL_IMPOSTER, p->dbMain, "main", 0, 1); + + for(iCol=0; p->rc==SQLITE_OK && iCol<pIter->nTblCol; iCol++){ + const char *zPk = ""; + const char *zCol = pIter->azTblCol[iCol]; + const char *zColl = 0; + + p->rc = sqlite3_table_column_metadata( + p->dbMain, "main", pIter->zTbl, zCol, 0, &zColl, 0, 0, 0 + ); + + if( pIter->eType==OTA_PK_IPK && pIter->abTblPk[iCol] ){ + /* If the target table column is an "INTEGER PRIMARY KEY", add + ** "PRIMARY KEY" to the imposter table column declaration. */ + zPk = "PRIMARY KEY "; + } + zSql = otaMPrintf(p, "%z%s\"%w\" %s %sCOLLATE %s%s", + zSql, zComma, zCol, pIter->azTblType[iCol], zPk, zColl, + (pIter->abNotNull[iCol] ? " NOT NULL" : "") + ); + zComma = ", "; + } + + if( pIter->eType==OTA_PK_WITHOUT_ROWID ){ + char *zPk = otaWithoutRowidPK(p, pIter); + if( zPk ){ + zSql = otaMPrintf(p, "%z, %z", zSql, zPk); + } + } + + sqlite3_test_control(SQLITE_TESTCTRL_IMPOSTER, p->dbMain, "main", 1, tnum); + otaMPrintfExec(p, p->dbMain, "CREATE TABLE \"ota_imp_%w\"(%z)%s", + pIter->zTbl, zSql, + (pIter->eType==OTA_PK_WITHOUT_ROWID ? " WITHOUT ROWID" : "") + ); + sqlite3_test_control(SQLITE_TESTCTRL_IMPOSTER, p->dbMain, "main", 0, 0); + } +} + +/* +** Prepare a statement used to insert rows into the "ota_tmp_xxx" table. +** Specifically a statement of the form: +** +** INSERT INTO ota_tmp_xxx VALUES(?, ?, ? ...); +** +** The number of bound variables is equal to the number of columns in +** the target table, plus one (for the ota_control column), plus one more +** (for the ota_rowid column) if the target table is an implicit IPK or +** virtual table. +*/ +static void otaObjIterPrepareTmpInsert( + sqlite3ota *p, + OtaObjIter *pIter, + const char *zCollist, + const char *zOtaRowid +){ + int bOtaRowid = (pIter->eType==OTA_PK_EXTERNAL || pIter->eType==OTA_PK_NONE); + char *zBind = otaObjIterGetBindlist(p, pIter->nTblCol + 1 + bOtaRowid); + if( zBind ){ + assert( pIter->pTmpInsert==0 ); + p->rc = prepareFreeAndCollectError( + p->dbOta, &pIter->pTmpInsert, &p->zErrmsg, sqlite3_mprintf( + "INSERT INTO 'ota_tmp_%q'(ota_control,%s%s) VALUES(%z)", + pIter->zTbl, zCollist, zOtaRowid, zBind + )); + } +} + +static void otaTmpInsertFunc( + sqlite3_context *pCtx, + int nVal, + sqlite3_value **apVal +){ + sqlite3ota *p = sqlite3_user_data(pCtx); + int rc = SQLITE_OK; + int i; + + for(i=0; rc==SQLITE_OK && i<nVal; i++){ + rc = sqlite3_bind_value(p->objiter.pTmpInsert, i+1, apVal[i]); + } + if( rc==SQLITE_OK ){ + sqlite3_step(p->objiter.pTmpInsert); + rc = sqlite3_reset(p->objiter.pTmpInsert); + } + + if( rc!=SQLITE_OK ){ + sqlite3_result_error_code(pCtx, rc); + } +} + +/* +** Ensure that the SQLite statement handles required to update the +** target database object currently indicated by the iterator passed +** as the second argument are available. +*/ +static int otaObjIterPrepareAll( + sqlite3ota *p, + OtaObjIter *pIter, + int nOffset /* Add "LIMIT -1 OFFSET $nOffset" to SELECT */ +){ + assert( pIter->bCleanup==0 ); + if( pIter->pSelect==0 && otaObjIterCacheTableInfo(p, pIter)==SQLITE_OK ){ + const int tnum = pIter->iTnum; + char *zCollist = 0; /* List of indexed columns */ + char **pz = &p->zErrmsg; + const char *zIdx = pIter->zIdx; + char *zLimit = 0; + + if( nOffset ){ + zLimit = sqlite3_mprintf(" LIMIT -1 OFFSET %d", nOffset); + if( !zLimit ) p->rc = SQLITE_NOMEM; + } + + if( zIdx ){ + const char *zTbl = pIter->zTbl; + char *zImposterCols = 0; /* Columns for imposter table */ + char *zImposterPK = 0; /* Primary key declaration for imposter */ + char *zWhere = 0; /* WHERE clause on PK columns */ + char *zBind = 0; + int nBind = 0; + + assert( pIter->eType!=OTA_PK_VTAB ); + zCollist = otaObjIterGetIndexCols( + p, pIter, &zImposterCols, &zImposterPK, &zWhere, &nBind + ); + zBind = otaObjIterGetBindlist(p, nBind); + + /* Create the imposter table used to write to this index. */ + sqlite3_test_control(SQLITE_TESTCTRL_IMPOSTER, p->dbMain, "main", 0, 1); + sqlite3_test_control(SQLITE_TESTCTRL_IMPOSTER, p->dbMain, "main", 1,tnum); + otaMPrintfExec(p, p->dbMain, + "CREATE TABLE \"ota_imp_%w\"( %s, PRIMARY KEY( %s ) ) WITHOUT ROWID", + zTbl, zImposterCols, zImposterPK + ); + sqlite3_test_control(SQLITE_TESTCTRL_IMPOSTER, p->dbMain, "main", 0, 0); + + /* Create the statement to insert index entries */ + pIter->nCol = nBind; + if( p->rc==SQLITE_OK ){ + p->rc = prepareFreeAndCollectError( + p->dbMain, &pIter->pInsert, &p->zErrmsg, + sqlite3_mprintf("INSERT INTO \"ota_imp_%w\" VALUES(%s)", zTbl, zBind) + ); + } + + /* And to delete index entries */ + if( p->rc==SQLITE_OK ){ + p->rc = prepareFreeAndCollectError( + p->dbMain, &pIter->pDelete, &p->zErrmsg, + sqlite3_mprintf("DELETE FROM \"ota_imp_%w\" WHERE %s", zTbl, zWhere) + ); + } + + /* Create the SELECT statement to read keys in sorted order */ + if( p->rc==SQLITE_OK ){ + char *zSql; + if( pIter->eType==OTA_PK_EXTERNAL || pIter->eType==OTA_PK_NONE ){ + zSql = sqlite3_mprintf( + "SELECT %s, ota_control FROM 'ota_tmp_%q' ORDER BY %s%s", + zCollist, pIter->zTbl, + zCollist, zLimit + ); + }else{ + zSql = sqlite3_mprintf( + "SELECT %s, ota_control FROM 'data_%q' " + "WHERE typeof(ota_control)='integer' AND ota_control!=1 " + "UNION ALL " + "SELECT %s, ota_control FROM 'ota_tmp_%q' " + "ORDER BY %s%s", + zCollist, pIter->zTbl, + zCollist, pIter->zTbl, + zCollist, zLimit + ); + } + p->rc = prepareFreeAndCollectError(p->dbOta, &pIter->pSelect, pz, zSql); + } + + sqlite3_free(zImposterCols); + sqlite3_free(zImposterPK); + sqlite3_free(zWhere); + sqlite3_free(zBind); + }else{ + int bOtaRowid = (pIter->eType==OTA_PK_VTAB || pIter->eType==OTA_PK_NONE); + const char *zTbl = pIter->zTbl; /* Table this step applies to */ + const char *zWrite; /* Imposter table name */ + + char *zBindings = otaObjIterGetBindlist(p, pIter->nTblCol + bOtaRowid); + char *zWhere = otaObjIterGetWhere(p, pIter); + char *zOldlist = otaObjIterGetOldlist(p, pIter, "old"); + char *zNewlist = otaObjIterGetOldlist(p, pIter, "new"); + + zCollist = otaObjIterGetCollist(p, pIter); + pIter->nCol = pIter->nTblCol; + + /* Create the SELECT statement to read keys from data_xxx */ + if( p->rc==SQLITE_OK ){ + p->rc = prepareFreeAndCollectError(p->dbOta, &pIter->pSelect, pz, + sqlite3_mprintf( + "SELECT %s, ota_control%s FROM 'data_%q'%s", + zCollist, (bOtaRowid ? ", ota_rowid" : ""), zTbl, zLimit + ) + ); + } + + /* Create the imposter table or tables (if required). */ + otaCreateImposterTable(p, pIter); + otaCreateImposterTable2(p, pIter); + zWrite = (pIter->eType==OTA_PK_VTAB ? "" : "ota_imp_"); + + /* Create the INSERT statement to write to the target PK b-tree */ + if( p->rc==SQLITE_OK ){ + p->rc = prepareFreeAndCollectError(p->dbMain, &pIter->pInsert, pz, + sqlite3_mprintf( + "INSERT INTO \"%s%w\"(%s%s) VALUES(%s)", + zWrite, zTbl, zCollist, (bOtaRowid ? ", _rowid_" : ""), zBindings + ) + ); + } + + /* Create the DELETE statement to write to the target PK b-tree */ + if( p->rc==SQLITE_OK ){ + p->rc = prepareFreeAndCollectError(p->dbMain, &pIter->pDelete, pz, + sqlite3_mprintf( + "DELETE FROM \"%s%w\" WHERE %s", zWrite, zTbl, zWhere + ) + ); + } + + if( pIter->eType!=OTA_PK_VTAB ){ + const char *zOtaRowid = ""; + if( pIter->eType==OTA_PK_EXTERNAL || pIter->eType==OTA_PK_NONE ){ + zOtaRowid = ", ota_rowid"; + } + + /* Create the ota_tmp_xxx table and the triggers to populate it. */ + otaMPrintfExec(p, p->dbOta, + "CREATE TABLE IF NOT EXISTS 'ota_tmp_%q' AS " + "SELECT *%s FROM 'data_%q' WHERE 0;" + , zTbl, (pIter->eType==OTA_PK_EXTERNAL ? ", 0 AS ota_rowid" : "") + , zTbl + ); + + otaMPrintfExec(p, p->dbMain, + "CREATE TEMP TRIGGER ota_delete_tr BEFORE DELETE ON \"%s%w\" " + "BEGIN " + " SELECT ota_tmp_insert(2, %s);" + "END;" + + "CREATE TEMP TRIGGER ota_update1_tr BEFORE UPDATE ON \"%s%w\" " + "BEGIN " + " SELECT ota_tmp_insert(2, %s);" + "END;" + + "CREATE TEMP TRIGGER ota_update2_tr AFTER UPDATE ON \"%s%w\" " + "BEGIN " + " SELECT ota_tmp_insert(3, %s);" + "END;", + zWrite, zTbl, zOldlist, + zWrite, zTbl, zOldlist, + zWrite, zTbl, zNewlist + ); + + if( pIter->eType==OTA_PK_EXTERNAL || pIter->eType==OTA_PK_NONE ){ + otaMPrintfExec(p, p->dbMain, + "CREATE TEMP TRIGGER ota_insert_tr AFTER INSERT ON \"%s%w\" " + "BEGIN " + " SELECT ota_tmp_insert(0, %s);" + "END;", + zWrite, zTbl, zNewlist + ); + } + + otaObjIterPrepareTmpInsert(p, pIter, zCollist, zOtaRowid); + } + + /* Allocate space required for the zMask field. */ + pIter->zMask = (char*)otaMalloc(p, pIter->nTblCol+1); + + sqlite3_free(zWhere); + sqlite3_free(zOldlist); + sqlite3_free(zNewlist); + sqlite3_free(zBindings); + } + sqlite3_free(zCollist); + sqlite3_free(zLimit); + } + + return p->rc; +} + +/* +** Set output variable *ppStmt to point to an UPDATE statement that may +** be used to update the imposter table for the main table b-tree of the +** table object that pIter currently points to, assuming that the +** ota_control column of the data_xyz table contains zMask. +*/ +static int otaGetUpdateStmt( + sqlite3ota *p, /* OTA handle */ + OtaObjIter *pIter, /* Object iterator */ + const char *zMask, /* ota_control value ('x.x.') */ + sqlite3_stmt **ppStmt /* OUT: UPDATE statement handle */ +){ + if( pIter->pUpdate && strcmp(zMask, pIter->zMask)==0 ){ + *ppStmt = pIter->pUpdate; + }else{ + char *zWhere = otaObjIterGetWhere(p, pIter); + char *zSet = otaObjIterGetSetlist(p, pIter, zMask); + char *zUpdate = 0; + sqlite3_finalize(pIter->pUpdate); + pIter->pUpdate = 0; + if( p->rc==SQLITE_OK ){ + const char *zPrefix = ""; + + if( pIter->eType!=OTA_PK_VTAB ) zPrefix = "ota_imp_"; + zUpdate = sqlite3_mprintf("UPDATE \"%s%w\" SET %s WHERE %s", + zPrefix, pIter->zTbl, zSet, zWhere + ); + p->rc = prepareFreeAndCollectError( + p->dbMain, &pIter->pUpdate, &p->zErrmsg, zUpdate + ); + *ppStmt = pIter->pUpdate; + } + if( p->rc==SQLITE_OK ){ + memcpy(pIter->zMask, zMask, pIter->nTblCol); + } + sqlite3_free(zWhere); + sqlite3_free(zSet); + } + return p->rc; +} + +static sqlite3 *otaOpenDbhandle(sqlite3ota *p, const char *zName){ + sqlite3 *db = 0; + if( p->rc==SQLITE_OK ){ + const int flags = SQLITE_OPEN_READWRITE | SQLITE_OPEN_CREATE; + p->rc = sqlite3_open_v2(zName, &db, flags, p->zVfsName); + if( p->rc ){ + p->zErrmsg = sqlite3_mprintf("%s", sqlite3_errmsg(db)); + sqlite3_close(db); + db = 0; + } + } + return db; +} + +/* +** 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){ + assert( p->rc==SQLITE_OK ); + assert( p->dbMain==0 && p->dbOta==0 ); + + p->eStage = 0; + p->dbMain = otaOpenDbhandle(p, p->zTarget); + p->dbOta = otaOpenDbhandle(p, p->zOta); + + if( p->rc==SQLITE_OK ){ + p->rc = sqlite3_create_function(p->dbMain, + "ota_tmp_insert", -1, SQLITE_UTF8, (void*)p, otaTmpInsertFunc, 0, 0 + ); + } + + if( p->rc==SQLITE_OK ){ + p->rc = sqlite3_file_control(p->dbMain, "main", SQLITE_FCNTL_OTA, (void*)p); + } + otaMPrintfExec(p, p->dbMain, "SELECT * FROM sqlite_master"); + + /* 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->dbMain, "main", SQLITE_FCNTL_OTA, (void*)p); + } + + if( p->rc==SQLITE_NOTFOUND ){ + p->rc = SQLITE_ERROR; + p->zErrmsg = sqlite3_mprintf("ota vfs not found"); + } +} + +/* +** 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. +** +** If SQLITE_ENABLE_8_3_NAMES is set at compile-time and if the database +** filename in zBaseFilename is a URI with the "8_3_names=1" parameter and +** if filename in z[] has a suffix (a.k.a. "extension") that is longer than +** three characters, then shorten the suffix on z[] to be the last three +** characters of the original suffix. +** +** If SQLITE_ENABLE_8_3_NAMES is set to 2 at compile-time, then always +** do the suffix shortening regardless of URI parameter. +** +** Examples: +** +** test.db-journal => test.nal +** test.db-wal => test.wal +** test.db-shm => test.shm +** test.db-mj7f3319fa => test.9fa +*/ +static void otaFileSuffix3(const char *zBase, char *z){ +#ifdef SQLITE_ENABLE_8_3_NAMES +#if SQLITE_ENABLE_8_3_NAMES<2 + if( sqlite3_uri_boolean(zBase, "8_3_names", 0) ) +#endif + { + int i, sz; + sz = sqlite3Strlen30(z); + for(i=sz-1; i>0 && z[i]!='/' && z[i]!='.'; i--){} + if( z[i]=='.' && ALWAYS(sz>i+4) ) memmove(&z[i+1], &z[sz-3], 4); + } +#endif +} + +/* +** Return the current wal-index header checksum for the target database +** as a 64-bit integer. +** +** The checksum is store in the first page of xShmMap memory as an 8-byte +** blob starting at byte offset 40. +*/ +static i64 otaShmChecksum(sqlite3ota *p){ + i64 iRet; + if( p->rc==SQLITE_OK ){ + sqlite3_file *pDb = p->pTargetFd->pReal; + u32 volatile *ptr; + p->rc = pDb->pMethods->xShmMap(pDb, 0, 32*1024, 0, (void volatile**)&ptr); + if( p->rc==SQLITE_OK ){ + iRet = ((i64)ptr[10] << 32) + ptr[11]; + } + } + return iRet; +} + +/* +** This function is called as part of initializing or reinitializing an +** incremental checkpoint. +** +** It populates the sqlite3ota.aFrame[] array with the set of +** (wal frame -> db page) copy operations required to checkpoint the +** current wal file, and obtains the set of shm locks required to safely +** perform the copy operations directly on the file-system. +** +** If argument pState is not NULL, then the incremental checkpoint is +** being resumed. In this case, if the checksum of the wal-index-header +** following recovery is not the same as the checksum saved in the OtaState +** object, then the ota handle is set to DONE state. This occurs if some +** other client appends a transaction to the wal file in the middle of +** an incremental checkpoint. +*/ +static void otaSetupCheckpoint(sqlite3ota *p, OtaState *pState){ + + /* If pState is NULL, then the wal file may not have been opened and + ** recovered. Running a read-statement here to ensure that doing so + ** does not interfere with the "capture" process below. */ + if( pState==0 ){ + p->eStage = 0; + if( p->rc==SQLITE_OK ){ + p->rc = sqlite3_exec(p->dbMain, "SELECT * FROM sqlite_master", 0, 0, 0); + } + } + + /* Assuming no error has occurred, run a "restart" checkpoint with the + ** sqlite3ota.eStage variable set to CAPTURE. This turns on the following + ** special behaviour in the ota VFS: + ** + ** * If the exclusive shm WRITER or READ0 lock cannot be obtained, + ** the checkpoint fails with SQLITE_BUSY (normally SQLite would + ** proceed with running a passive checkpoint instead of failing). + ** + ** * Attempts to read from the *-wal file or write to the database file + ** do not perform any IO. Instead, the frame/page combinations that + ** would be read/written are recorded in the sqlite3ota.aFrame[] + ** array. + ** + ** * Calls to xShmLock(UNLOCK) to release the exclusive shm WRITER, + ** READ0 and CHECKPOINT locks taken as part of the checkpoint are + ** no-ops. These locks will not be released until the connection + ** is closed. + ** + ** * Attempting to xSync() the database file causes an SQLITE_INTERNAL + ** error. + ** + ** As a result, unless an error (i.e. OOM or SQLITE_BUSY) occurs, the + ** checkpoint below fails with SQLITE_INTERNAL, and leaves the aFrame[] + ** array populated with a set of (frame -> page) mappings. Because the + ** WRITER, CHECKPOINT and READ0 locks are still held, it is safe to copy + ** data from the wal file into the database file according to the + ** contents of aFrame[]. + */ + if( p->rc==SQLITE_OK ){ + int rc2; + p->eStage = OTA_STAGE_CAPTURE; + rc2 = sqlite3_exec(p->dbMain, "PRAGMA main.wal_checkpoint=restart", 0, 0,0); + if( rc2!=SQLITE_INTERNAL ) p->rc = rc2; + } + + if( p->rc==SQLITE_OK ){ + p->eStage = OTA_STAGE_CKPT; + p->nStep = (pState ? pState->nRow : 0); + p->aBuf = otaMalloc(p, p->pgsz); + p->iWalCksum = otaShmChecksum(p); + } + + if( p->rc==SQLITE_OK && pState && pState->iWalCksum!=p->iWalCksum ){ + p->rc = SQLITE_DONE; + p->eStage = OTA_STAGE_DONE; + } +} + +/* +** Called when iAmt bytes are read from offset iOff of the wal file while +** the ota object is in capture mode. Record the frame number of the frame +** being read in the aFrame[] array. +*/ +static int otaCaptureWalRead(sqlite3ota *pOta, i64 iOff, int iAmt){ + const u32 mReq = (1<<WAL_LOCK_WRITE)|(1<<WAL_LOCK_CKPT)|(1<<WAL_LOCK_READ0); + u32 iFrame; + + if( pOta->mLock!=mReq ){ + pOta->rc = SQLITE_BUSY; + return SQLITE_INTERNAL; + } + + pOta->pgsz = iAmt; + if( pOta->nFrame==pOta->nFrameAlloc ){ + int nNew = (pOta->nFrameAlloc ? pOta->nFrameAlloc : 64) * 2; + OtaFrame *aNew; + aNew = (OtaFrame*)sqlite3_realloc(pOta->aFrame, nNew * sizeof(OtaFrame)); + if( aNew==0 ) return SQLITE_NOMEM; + pOta->aFrame = aNew; + pOta->nFrameAlloc = nNew; + } + + iFrame = (u32)((iOff-32) / (i64)(iAmt+24)) + 1; + if( pOta->iMaxFrame<iFrame ) pOta->iMaxFrame = iFrame; + pOta->aFrame[pOta->nFrame].iWalFrame = iFrame; + pOta->aFrame[pOta->nFrame].iDbPage = 0; + pOta->nFrame++; + return SQLITE_OK; +} + +/* +** Called when a page of data is written to offset iOff of the database +** file while the ota handle is in capture mode. Record the page number +** of the page being written in the aFrame[] array. +*/ +static int otaCaptureDbWrite(sqlite3ota *pOta, i64 iOff){ + pOta->aFrame[pOta->nFrame-1].iDbPage = (u32)(iOff / pOta->pgsz) + 1; + return SQLITE_OK; +} + +/* +** This is called as part of an incremental checkpoint operation. Copy +** a single frame of data from the wal file into the database file, as +** indicated by the OtaFrame object. +*/ +static void otaCheckpointFrame(sqlite3ota *p, OtaFrame *pFrame){ + sqlite3_file *pWal = p->pTargetFd->pWalFd->pReal; + sqlite3_file *pDb = p->pTargetFd->pReal; + i64 iOff; + + assert( p->rc==SQLITE_OK ); + iOff = (i64)(pFrame->iWalFrame-1) * (p->pgsz + 24) + 32 + 24; + p->rc = pWal->pMethods->xRead(pWal, p->aBuf, p->pgsz, iOff); + if( p->rc ) return; + + iOff = (i64)(pFrame->iDbPage-1) * p->pgsz; + p->rc = pDb->pMethods->xWrite(pDb, p->aBuf, p->pgsz, iOff); +} + + +/* +** Take an EXCLUSIVE lock on the database file. +*/ +static void otaLockDatabase(sqlite3ota *p){ + sqlite3_file *pReal = p->pTargetFd->pReal; + assert( p->rc==SQLITE_OK ); + p->rc = pReal->pMethods->xLock(pReal, SQLITE_LOCK_SHARED); + if( p->rc==SQLITE_OK ){ + p->rc = pReal->pMethods->xLock(pReal, SQLITE_LOCK_EXCLUSIVE); + } +} + +/* +** The OTA handle is currently in OTA_STAGE_OAL state, with a SHARED lock +** on the database file. This proc moves the *-oal file to the *-wal path, +** then reopens the database file (this time in vanilla, non-oal, WAL mode). +** If an error occurs, leave an error code and error message in the ota +** handle. +*/ +static void otaMoveOalFile(sqlite3ota *p){ + const char *zBase = sqlite3_db_filename(p->dbMain, "main"); + + char *zWal = sqlite3_mprintf("%s-wal", zBase); + char *zOal = sqlite3_mprintf("%s-oal", zBase); + + assert( p->eStage==OTA_STAGE_MOVE ); + assert( p->rc==SQLITE_OK && p->zErrmsg==0 ); + if( zWal==0 || zOal==0 ){ + p->rc = SQLITE_NOMEM; + }else{ + /* Move the *-oal file to *-wal. At this point connection p->db is + ** holding a SHARED lock on the target database file (because it is + ** in WAL mode). So no other connection may be writing the db. + ** + ** In order to ensure that there are no database readers, an EXCLUSIVE + ** lock is obtained here before the *-oal is moved to *-wal. + */ + otaLockDatabase(p); + if( p->rc==SQLITE_OK ){ + otaFileSuffix3(zBase, zWal); + otaFileSuffix3(zBase, zOal); + rename(zOal, zWal); + + /* Re-open the databases. */ + otaObjIterFinalize(&p->objiter); + sqlite3_close(p->dbMain); + sqlite3_close(p->dbOta); + p->dbMain = 0; + p->dbOta = 0; + otaOpenDatabase(p); + otaSetupCheckpoint(p, 0); + } + } + + sqlite3_free(zWal); + sqlite3_free(zOal); +} + +/* +** The SELECT statement iterating through the keys for the current object +** (p->objiter.pSelect) currently points to a valid row. This function +** determines the type of operation requested by this row and returns +** one of the following values to indicate the result: +** +** * OTA_INSERT +** * OTA_DELETE +** * OTA_IDX_DELETE +** * OTA_UPDATE +** +** If OTA_UPDATE is returned, then output variable *pzMask is set to +** point to the text value indicating the columns to update. +** +** If the ota_control field contains an invalid value, an error code and +** message are left in the OTA handle and zero returned. +*/ +static int otaStepType(sqlite3ota *p, const char **pzMask){ + int iCol = p->objiter.nCol; /* Index of ota_control column */ + int res = 0; /* Return value */ + + switch( sqlite3_column_type(p->objiter.pSelect, iCol) ){ + case SQLITE_INTEGER: { + int iVal = sqlite3_column_int(p->objiter.pSelect, iCol); + if( iVal==0 ){ + res = OTA_INSERT; + }else if( iVal==1 ){ + res = OTA_DELETE; + }else if( iVal==2 ){ + res = OTA_IDX_DELETE; + }else if( iVal==3 ){ + res = OTA_IDX_INSERT; + } + break; + } + + case SQLITE_TEXT: { + const unsigned char *z = sqlite3_column_text(p->objiter.pSelect, iCol); + if( z==0 ){ + p->rc = SQLITE_NOMEM; + }else{ + *pzMask = (const char*)z; + } + res = OTA_UPDATE; + + break; + } + + default: + break; + } + + if( res==0 ){ + otaBadControlError(p); + } + return res; +} + +#ifdef SQLITE_DEBUG +/* +** Assert that column iCol of statement pStmt is named zName. +*/ +static void assertColumnName(sqlite3_stmt *pStmt, int iCol, const char *zName){ + const char *zCol = sqlite3_column_name(pStmt, iCol); + assert( 0==sqlite3_stricmp(zName, zCol) ); +} +#else +# define assertColumnName(x,y,z) +#endif + +/* +** This function does the work for an sqlite3ota_step() call. +** +** The object-iterator (p->objiter) currently points to a valid object, +** and the input cursor (p->objiter.pSelect) currently points to a valid +** input row. Perform whatever processing is required and return. +** +** If no error occurs, SQLITE_OK is returned. Otherwise, an error code +** and message is left in the OTA handle and a copy of the error code +** returned. +*/ +static int otaStep(sqlite3ota *p){ + OtaObjIter *pIter = &p->objiter; + const char *zMask = 0; + int i; + int eType = otaStepType(p, &zMask); + + if( eType ){ + assert( eType!=OTA_UPDATE || pIter->zIdx==0 ); + + if( pIter->zIdx==0 && eType==OTA_IDX_DELETE ){ + otaBadControlError(p); + } + else if( + eType==OTA_INSERT + || eType==OTA_DELETE + || eType==OTA_IDX_DELETE + || eType==OTA_IDX_INSERT + ){ + sqlite3_value *pVal; + sqlite3_stmt *pWriter; + + assert( eType!=OTA_UPDATE ); + assert( eType!=OTA_DELETE || pIter->zIdx==0 ); + + if( eType==OTA_IDX_DELETE || eType==OTA_DELETE ){ + pWriter = pIter->pDelete; + }else{ + pWriter = pIter->pInsert; + } + + for(i=0; i<pIter->nCol; i++){ + /* If this is an INSERT into a table b-tree and the table has an + ** explicit INTEGER PRIMARY KEY, check that this is not an attempt + ** to write a NULL into the IPK column. That is not permitted. */ + if( eType==OTA_INSERT + && pIter->zIdx==0 && pIter->eType==OTA_PK_IPK && pIter->abTblPk[i] + && sqlite3_column_type(pIter->pSelect, i)==SQLITE_NULL + ){ + p->rc = SQLITE_MISMATCH; + p->zErrmsg = sqlite3_mprintf("datatype mismatch"); + goto step_out; + } + + if( eType==OTA_DELETE && pIter->abTblPk[i]==0 ){ + continue; + } + + pVal = sqlite3_column_value(pIter->pSelect, i); + p->rc = sqlite3_bind_value(pWriter, i+1, pVal); + if( p->rc ) goto step_out; + } + if( pIter->zIdx==0 + && (pIter->eType==OTA_PK_VTAB || pIter->eType==OTA_PK_NONE) + ){ + /* For a virtual table, or a table with no primary key, the + ** SELECT statement is: + ** + ** SELECT <cols>, ota_control, ota_rowid FROM .... + ** + ** Hence column_value(pIter->nCol+1). + */ + assertColumnName(pIter->pSelect, pIter->nCol+1, "ota_rowid"); + pVal = sqlite3_column_value(pIter->pSelect, pIter->nCol+1); + p->rc = sqlite3_bind_value(pWriter, pIter->nCol+1, pVal); + } + if( p->rc==SQLITE_OK ){ + sqlite3_step(pWriter); + p->rc = resetAndCollectError(pWriter, &p->zErrmsg); + } + }else{ + sqlite3_value *pVal; + sqlite3_stmt *pUpdate = 0; + assert( eType==OTA_UPDATE ); + otaGetUpdateStmt(p, pIter, zMask, &pUpdate); + if( pUpdate ){ + for(i=0; p->rc==SQLITE_OK && i<pIter->nCol; i++){ + char c = zMask[pIter->aiSrcOrder[i]]; + pVal = sqlite3_column_value(pIter->pSelect, i); + if( pIter->abTblPk[i] || c=='x' || c=='d' ){ + p->rc = sqlite3_bind_value(pUpdate, i+1, pVal); + } + } + if( p->rc==SQLITE_OK + && (pIter->eType==OTA_PK_VTAB || pIter->eType==OTA_PK_NONE) + ){ + /* Bind the ota_rowid value to column _rowid_ */ + assertColumnName(pIter->pSelect, pIter->nCol+1, "ota_rowid"); + pVal = sqlite3_column_value(pIter->pSelect, pIter->nCol+1); + p->rc = sqlite3_bind_value(pUpdate, pIter->nCol+1, pVal); + } + if( p->rc==SQLITE_OK ){ + sqlite3_step(pUpdate); + p->rc = resetAndCollectError(pUpdate, &p->zErrmsg); + } + } + } + } + + step_out: + return p->rc; +} + +/* +** Increment the schema cookie of the main database opened by p->dbMain. +*/ +static void otaIncrSchemaCookie(sqlite3ota *p){ + if( p->rc==SQLITE_OK ){ + int iCookie = 1000000; + sqlite3_stmt *pStmt; + + p->rc = prepareAndCollectError(p->dbMain, &pStmt, &p->zErrmsg, + "PRAGMA schema_version" + ); + if( p->rc==SQLITE_OK ){ + /* Coverage: it may be that this sqlite3_step() cannot fail. There + ** is already a transaction open, so the prepared statement cannot + ** throw an SQLITE_SCHEMA exception. The only database page the + ** statement reads is page 1, which is guaranteed to be in the cache. + ** And no memory allocations are required. */ + if( SQLITE_ROW==sqlite3_step(pStmt) ){ + iCookie = sqlite3_column_int(pStmt, 0); + } + otaFinalize(p, pStmt); + } + if( p->rc==SQLITE_OK ){ + otaMPrintfExec(p, p->dbMain, "PRAGMA schema_version = %d", iCookie+1); + } + } +} + +/* +** Update the contents of the ota_state table within the ota database. The +** value stored in the OTA_STATE_STAGE column is eStage. All other values +** are determined by inspecting the ota handle passed as the first argument. +*/ +static void otaSaveState(sqlite3ota *p, int eStage){ + if( p->rc==SQLITE_OK || p->rc==SQLITE_DONE ){ + sqlite3_stmt *pInsert = 0; + int rc; + + assert( p->zErrmsg==0 ); + rc = prepareFreeAndCollectError(p->dbOta, &pInsert, &p->zErrmsg, + sqlite3_mprintf( + "INSERT OR REPLACE INTO ota_state(k, v) VALUES " + "(%d, %d), " + "(%d, %Q), " + "(%d, %Q), " + "(%d, %d), " + "(%d, %lld), " + "(%d, %lld), " + "(%d, %lld), " + "(%d, %lld) ", + OTA_STATE_STAGE, 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, p->iWalCksum, + OTA_STATE_COOKIE, (i64)p->pTargetFd->iCookie, + OTA_STATE_OALSZ, p->iOalSz + ) + ); + assert( pInsert==0 || rc==SQLITE_OK ); + + if( rc==SQLITE_OK ){ + sqlite3_step(pInsert); + rc = sqlite3_finalize(pInsert); + } + if( rc!=SQLITE_OK ) p->rc = rc; + } +} + + +/* +** Step the OTA object. +*/ +int sqlite3ota_step(sqlite3ota *p){ + if( p ){ + switch( p->eStage ){ + case OTA_STAGE_OAL: { + OtaObjIter *pIter = &p->objiter; + while( p->rc==SQLITE_OK && pIter->zTbl ){ + + if( pIter->bCleanup ){ + /* Clean up the ota_tmp_xxx table for the previous table. It + ** cannot be dropped as there are currently active SQL statements. + ** But the contents can be deleted. */ + if( pIter->eType!=OTA_PK_VTAB ){ + const char *zTbl = pIter->zTbl; + otaMPrintfExec(p, p->dbOta, "DELETE FROM 'ota_tmp_%q'", zTbl); + } + }else{ + otaObjIterPrepareAll(p, pIter, 0); + + /* Advance to the next row to process. */ + if( p->rc==SQLITE_OK ){ + int rc = sqlite3_step(pIter->pSelect); + if( rc==SQLITE_ROW ){ + p->nProgress++; + p->nStep++; + return otaStep(p); + } + p->rc = sqlite3_reset(pIter->pSelect); + p->nStep = 0; + } + } + + otaObjIterNext(p, pIter); + } + + if( p->rc==SQLITE_OK ){ + assert( pIter->zTbl==0 ); + otaSaveState(p, OTA_STAGE_MOVE); + otaIncrSchemaCookie(p); + if( p->rc==SQLITE_OK ){ + p->rc = sqlite3_exec(p->dbMain, "COMMIT", 0, 0, &p->zErrmsg); + } + if( p->rc==SQLITE_OK ){ + p->rc = sqlite3_exec(p->dbOta, "COMMIT", 0, 0, &p->zErrmsg); + } + p->eStage = OTA_STAGE_MOVE; + } + break; + } + + case OTA_STAGE_MOVE: { + if( p->rc==SQLITE_OK ){ + otaMoveOalFile(p); + p->nProgress++; + } + break; + } + + case OTA_STAGE_CKPT: { + if( p->rc==SQLITE_OK ){ + if( p->nStep>=p->nFrame ){ + sqlite3_file *pDb = p->pTargetFd->pReal; + + /* Sync the db file */ + p->rc = pDb->pMethods->xSync(pDb, SQLITE_SYNC_NORMAL); + + /* Update nBackfill */ + if( p->rc==SQLITE_OK ){ + void volatile *ptr; + p->rc = pDb->pMethods->xShmMap(pDb, 0, 32*1024, 0, &ptr); + if( p->rc==SQLITE_OK ){ + ((u32*)ptr)[12] = p->iMaxFrame; + } + } + + if( p->rc==SQLITE_OK ){ + p->eStage = OTA_STAGE_DONE; + p->rc = SQLITE_DONE; + } + }else{ + OtaFrame *pFrame = &p->aFrame[p->nStep]; + otaCheckpointFrame(p, pFrame); + p->nStep++; + } + p->nProgress++; + } + break; + } + + default: + break; + } + return p->rc; + }else{ + return SQLITE_NOMEM; + } +} + +/* +** Free an OtaState object allocated by otaLoadState(). +*/ +static void otaFreeState(OtaState *p){ + if( p ){ + sqlite3_free(p->zTbl); + sqlite3_free(p->zIdx); + sqlite3_free(p); + } +} + +/* +** Allocate an OtaState object and load the contents of the ota_state +** table into it. Return a pointer to the new object. It is the +** responsibility of the caller to eventually free the object using +** sqlite3_free(). +** +** If an error occurs, leave an error code and message in the ota handle +** and return NULL. +*/ +static OtaState *otaLoadState(sqlite3ota *p){ + const char *zSelect = "SELECT k, v FROM ota_state"; + OtaState *pRet = 0; + sqlite3_stmt *pStmt = 0; + int rc; + int rc2; + + pRet = (OtaState*)otaMalloc(p, sizeof(OtaState)); + if( pRet==0 ) return 0; + + rc = prepareAndCollectError(p->dbOta, &pStmt, &p->zErrmsg, zSelect); + 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_MOVE + && pRet->eStage!=OTA_STAGE_CKPT + ){ + p->rc = SQLITE_CORRUPT; + } + break; + + case OTA_STATE_TBL: + pRet->zTbl = otaStrndup((char*)sqlite3_column_text(pStmt, 1), &rc); + break; + + case OTA_STATE_IDX: + pRet->zIdx = otaStrndup((char*)sqlite3_column_text(pStmt, 1), &rc); + break; + + case OTA_STATE_ROW: + pRet->nRow = sqlite3_column_int(pStmt, 1); + break; + + case OTA_STATE_PROGRESS: + pRet->nProgress = sqlite3_column_int64(pStmt, 1); + break; + + case OTA_STATE_CKPT: + pRet->iWalCksum = sqlite3_column_int64(pStmt, 1); + break; + + case OTA_STATE_COOKIE: + pRet->iCookie = (u32)sqlite3_column_int64(pStmt, 1); + break; + + case OTA_STATE_OALSZ: + pRet->iOalSz = (u32)sqlite3_column_int64(pStmt, 1); + break; + + default: + rc = SQLITE_CORRUPT; + break; + } + } + rc2 = sqlite3_finalize(pStmt); + if( rc==SQLITE_OK ) rc = rc2; + + p->rc = rc; + return pRet; +} + +/* +** Compare strings z1 and z2, returning 0 if they are identical, or non-zero +** otherwise. Either or both argument may be NULL. Two NULL values are +** considered equal, and NULL is considered distinct from all other values. +*/ +static int otaStrCompare(const char *z1, const char *z2){ + if( z1==0 && z2==0 ) return 0; + if( z1==0 || z2==0 ) return 1; + return (sqlite3_stricmp(z1, z2)!=0); +} + +/* +** This function is called as part of sqlite3ota_open() when initializing +** an ota handle in OAL stage. If the ota update has not started (i.e. +** the ota_state table was empty) it is a no-op. Otherwise, it arranges +** things so that the next call to sqlite3ota_step() continues on from +** where the previous ota handle left off. +** +** If an error occurs, an error code and error message are left in the +** ota handle passed as the first argument. +*/ +static void otaSetupOal(sqlite3ota *p, OtaState *pState){ + assert( p->rc==SQLITE_OK ); + if( pState->zTbl ){ + OtaObjIter *pIter = &p->objiter; + int rc = SQLITE_OK; + + while( rc==SQLITE_OK && pIter->zTbl && (pIter->bCleanup + || otaStrCompare(pIter->zIdx, pState->zIdx) + || otaStrCompare(pIter->zTbl, pState->zTbl) + )){ + rc = otaObjIterNext(p, pIter); + } + + if( rc==SQLITE_OK && !pIter->zTbl ){ + rc = SQLITE_ERROR; + p->zErrmsg = sqlite3_mprintf("ota_state mismatch error"); + } + + if( rc==SQLITE_OK ){ + p->nStep = pState->nRow; + rc = otaObjIterPrepareAll(p, &p->objiter, p->nStep); + } + + p->rc = rc; + } +} + +/* +** If there is a "*-oal" file in the file-system corresponding to the +** target database in the file-system, delete it. If an error occurs, +** leave an error code and error message in the ota handle. +*/ +static void otaDeleteOalFile(sqlite3ota *p){ + char *zOal = sqlite3_mprintf("%s-oal", p->zTarget); + assert( p->rc==SQLITE_OK && p->zErrmsg==0 ); + unlink(zOal); + sqlite3_free(zOal); +} + +/* +** Allocate a private ota VFS for the ota handle passed as the only +** argument. This VFS will be used unless the call to sqlite3ota_open() +** specified a URI with a vfs=? option in place of a target database +** file name. +*/ +static void otaCreateVfs(sqlite3ota *p){ + 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, 0); + if( p->rc==SQLITE_OK ){ + sqlite3_vfs *pVfs = sqlite3_vfs_find(zRnd); + assert( pVfs ); + p->zVfsName = pVfs->zName; + } +} + +/* +** Destroy the private VFS created for the ota handle passed as the only +** argument by an earlier call to otaCreateVfs(). +*/ +static void otaDeleteVfs(sqlite3ota *p){ + if( p->zVfsName ){ + sqlite3ota_destroy_vfs(p->zVfsName); + p->zVfsName = 0; + } +} + +/* +** Open and return a new OTA handle. +*/ +sqlite3ota *sqlite3ota_open(const char *zTarget, const char *zOta){ + sqlite3ota *p; + int nTarget = strlen(zTarget); + int nOta = strlen(zOta); + + p = (sqlite3ota*)sqlite3_malloc(sizeof(sqlite3ota)+nTarget+1+nOta+1); + if( p ){ + OtaState *pState = 0; + + /* Create the custom VFS. */ + memset(p, 0, sizeof(sqlite3ota)); + otaCreateVfs(p); + + /* 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->dbOta, OTA_CREATE_STATE, 0, 0, &p->zErrmsg); + } + + if( p->rc==SQLITE_OK ){ + pState = otaLoadState(p); + assert( pState || p->rc!=SQLITE_OK ); + if( p->rc==SQLITE_OK ){ + + if( pState->eStage==0 ){ + otaDeleteOalFile(p); + p->eStage = OTA_STAGE_OAL; + }else{ + p->eStage = pState->eStage; + } + p->nProgress = pState->nProgress; + p->iOalSz = pState->iOalSz; + } + } + assert( p->rc!=SQLITE_OK || p->eStage!=0 ); + + if( p->rc==SQLITE_OK && p->pTargetFd->pWalFd ){ + if( p->eStage==OTA_STAGE_OAL ){ + p->rc = SQLITE_ERROR; + p->zErrmsg = sqlite3_mprintf("cannot update wal mode database"); + }else if( p->eStage==OTA_STAGE_MOVE ){ + p->eStage = OTA_STAGE_CKPT; + p->nStep = 0; + } + } + + if( p->rc==SQLITE_OK + && (p->eStage==OTA_STAGE_OAL || p->eStage==OTA_STAGE_MOVE) + && pState->eStage!=0 && p->pTargetFd->iCookie!=pState->iCookie + ){ + /* At this point (pTargetFd->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. */ + p->rc = SQLITE_BUSY; + p->zErrmsg = sqlite3_mprintf("database modified during ota update"); + } + + if( p->rc==SQLITE_OK ){ + if( p->eStage==OTA_STAGE_OAL ){ + + /* Open transactions both databases. The *-oal file is opened or + ** created at this point. */ + p->rc = sqlite3_exec(p->dbMain, "BEGIN IMMEDIATE", 0, 0, &p->zErrmsg); + if( p->rc==SQLITE_OK ){ + p->rc = sqlite3_exec(p->dbOta, "BEGIN IMMEDIATE", 0, 0, &p->zErrmsg); + } + assert( p->rc!=SQLITE_OK || p->pTargetFd->pWalFd ); + + /* Point the object iterator at the first object */ + if( p->rc==SQLITE_OK ){ + p->rc = otaObjIterFirst(p, &p->objiter); + } + + if( p->rc==SQLITE_OK ){ + otaSetupOal(p, pState); + } + }else if( p->eStage==OTA_STAGE_MOVE ){ + /* no-op */ + }else if( p->eStage==OTA_STAGE_CKPT ){ + otaSetupCheckpoint(p, pState); + }else if( p->eStage==OTA_STAGE_DONE ){ + p->rc = SQLITE_DONE; + }else{ + p->rc = SQLITE_CORRUPT; + } + } + + otaFreeState(pState); + } + + return p; +} + +/* +** Return the database handle used by pOta. +*/ +sqlite3 *sqlite3ota_db(sqlite3ota *pOta, int bOta){ + sqlite3 *db = 0; + if( pOta ){ + db = (bOta ? pOta->dbOta : pOta->dbMain); + } + return db; +} + + +/* +** If the error code currently stored in the OTA handle is SQLITE_CONSTRAINT, +** then edit any error message string so as to remove all occurrences of +** the pattern "ota_imp_[0-9]*". +*/ +static void otaEditErrmsg(sqlite3ota *p){ + if( p->rc==SQLITE_CONSTRAINT && p->zErrmsg ){ + int i; + int nErrmsg = strlen(p->zErrmsg); + for(i=0; i<(nErrmsg-8); i++){ + if( memcmp(&p->zErrmsg[i], "ota_imp_", 8)==0 ){ + int nDel = 8; + while( p->zErrmsg[i+nDel]>='0' && p->zErrmsg[i+nDel]<='9' ) nDel++; + memmove(&p->zErrmsg[i], &p->zErrmsg[i+nDel], nErrmsg + 1 - i - nDel); + nErrmsg -= nDel; + } + } + } +} + +/* +** Close the OTA handle. +*/ +int sqlite3ota_close(sqlite3ota *p, char **pzErrmsg){ + int rc; + if( p ){ + + /* Commit the transaction to the *-oal file. */ + if( p->rc==SQLITE_OK && p->eStage==OTA_STAGE_OAL ){ + p->rc = sqlite3_exec(p->dbMain, "COMMIT", 0, 0, &p->zErrmsg); + } + + otaSaveState(p, p->eStage); + + if( p->rc==SQLITE_OK && p->eStage==OTA_STAGE_OAL ){ + p->rc = sqlite3_exec(p->dbOta, "COMMIT", 0, 0, &p->zErrmsg); + } + + /* Close any open statement handles. */ + otaObjIterFinalize(&p->objiter); + + /* Close the open database handle and VFS object. */ + sqlite3_close(p->dbMain); + sqlite3_close(p->dbOta); + otaDeleteVfs(p); + sqlite3_free(p->aBuf); + sqlite3_free(p->aFrame); + + otaEditErrmsg(p); + rc = p->rc; + *pzErrmsg = p->zErrmsg; + sqlite3_free(p); + }else{ + rc = SQLITE_NOMEM; + *pzErrmsg = 0; + } + return rc; +} + +/* +** Return the total number of key-value operations (inserts, deletes or +** updates) that have been performed on the target database since the +** 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. Whenever an SQLITE_OPEN_WAL file is opened, the (ota_file.pWalFd) +** member variable of the associated database file descriptor is set +** to point to the new file. A mutex protected linked list of all main +** db fds opened using a particular OTA VFS is maintained at +** ota_vfs.pMain to facilitate this. +** +** 3. Using a new file-control "SQLITE_FCNTL_OTA", a main db ota_file +** object can be marked as the target database of an OTA update. This +** turns on the following extra special behaviour: +** +** 3a. If xAccess() is called to check if there exists a *-wal file +** associated with an OTA target database currently in OTA_STAGE_OAL +** stage (preparing the *-oal file), the following special handling +** applies: +** +** * if the *-wal file does exist, return SQLITE_CANTOPEN. An OTA +** target database may not be in wal mode already. +** +** * if the *-wal file does not exist, set the output parameter to +** non-zero (to tell SQLite that it does exist) anyway. +** +** Then, when xOpen() is called to open the *-wal file associated with +** the OTA target in OTA_STAGE_OAL stage, instead of opening the *-wal +** file, the ota vfs opens the corresponding *-oal file instead. +** +** 3b. The *-shm pages returned by xShmMap() for a target db file in +** OTA_STAGE_OAL mode are actually stored in heap memory. This is to +** avoid creating a *-shm file on disk. Additionally, xShmLock() calls +** are no-ops on target database files in OTA_STAGE_OAL mode. This is +** because assert() statements in some VFS implementations fail if +** xShmLock() is called before xShmMap(). +** +** 3c. If an EXCLUSIVE lock is attempted on a target database file in any +** mode except OTA_STAGE_DONE (all work completed and checkpointed), it +** fails with an SQLITE_BUSY error. This is to stop OTA connections +** from automatically checkpointing a *-wal (or *-oal) file from within +** sqlite3_close(). +** +** 3d. In OTA_STAGE_CAPTURE mode, all xRead() calls on the wal file, and +** all xWrite() calls on the target database file perform no IO. +** Instead the frame and page numbers that would be read and written +** are recorded. Additionally, successful attempts to obtain exclusive +** xShmLock() WRITER, CHECKPOINTER and READ0 locks on the target +** database file are recorded. xShmLock() calls to unlock the same +** locks are no-ops (so that once obtained, these locks are never +** relinquished). Finally, calls to xSync() on the target database +** file fail with SQLITE_INTERNAL errors. +*/ + +/* +** 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; i<p->nShm; i++){ + sqlite3_free(p->apShm[i]); + } + sqlite3_free(p->apShm); + p->apShm = 0; + sqlite3_free(p->zDel); + + if( p->openFlags & SQLITE_OPEN_MAIN_DB ){ + ota_file **pp; + sqlite3_mutex_enter(p->pOtaVfs->mutex); + for(pp=&p->pOtaVfs->pMain; *pp!=p; pp=&((*pp)->pMainNext)); + *pp = p->pMainNext; + sqlite3_mutex_leave(p->pOtaVfs->mutex); + p->pReal->pMethods->xShmUnmap(p->pReal, 0); + } + + /* 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 u32 otaGetU32(u8 *aBuf){ + return ((u32)aBuf[0] << 24) + + ((u32)aBuf[1] << 16) + + ((u32)aBuf[2] << 8) + + ((u32)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; + sqlite3ota *pOta = p->pOta; + int rc; + + if( pOta && pOta->eStage==OTA_STAGE_CAPTURE ){ + assert( p->openFlags & SQLITE_OPEN_WAL ); + rc = otaCaptureWalRead(p->pOta, iOfst, iAmt); + }else{ + if( pOta && pOta->eStage==OTA_STAGE_OAL + && (p->openFlags & SQLITE_OPEN_WAL) + && iOfst>=pOta->iOalSz + ){ + rc = SQLITE_OK; + memset(zBuf, 0, iAmt); + }else{ + 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. */ + u8 *pBuf = (u8*)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; + sqlite3ota *pOta = p->pOta; + int rc; + + if( pOta && pOta->eStage==OTA_STAGE_CAPTURE ){ + assert( p->openFlags & SQLITE_OPEN_MAIN_DB ); + rc = otaCaptureDbWrite(p->pOta, iOfst); + }else{ + if( pOta && pOta->eStage==OTA_STAGE_OAL + && (p->openFlags & SQLITE_OPEN_WAL) + && iOfst>=pOta->iOalSz + ){ + pOta->iOalSz = iAmt + iOfst; + } + 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. */ + u8 *pBuf = (u8*)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; + if( p->pOta && p->pOta->eStage==OTA_STAGE_CAPTURE ){ + if( p->openFlags & SQLITE_OPEN_MAIN_DB ){ + return SQLITE_INTERNAL; + } + return SQLITE_OK; + } + 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; + + assert( p->openFlags & (SQLITE_OPEN_MAIN_DB|SQLITE_OPEN_TEMP_DB) ); + if( pOta && eLock==SQLITE_LOCK_EXCLUSIVE && pOta->eStage!=OTA_STAGE_DONE ){ + /* 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; + + assert( p->openFlags & (SQLITE_OPEN_MAIN_DB|SQLITE_OPEN_TEMP_DB) ); + 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, &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; + if( p->pWalFd ) p->pWalFd->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); +} + +/* +** Take or release a shared-memory lock. +*/ +static int otaVfsShmLock(sqlite3_file *pFile, int ofst, int n, int flags){ + ota_file *p = (ota_file*)pFile; + sqlite3ota *pOta = p->pOta; + int rc = SQLITE_OK; + +#ifdef SQLITE_AMALGAMATION + assert( WAL_CKPT_LOCK==1 ); +#endif + + assert( p->openFlags & (SQLITE_OPEN_MAIN_DB|SQLITE_OPEN_TEMP_DB) ); + if( pOta && (pOta->eStage==OTA_STAGE_OAL || pOta->eStage==OTA_STAGE_MOVE) ){ + /* 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==WAL_LOCK_CKPT && n==1 ) rc = SQLITE_BUSY; + }else{ + int bCapture = 0; + if( n==1 && (flags & SQLITE_SHM_EXCLUSIVE) + && pOta && pOta->eStage==OTA_STAGE_CAPTURE + && (ofst==WAL_LOCK_WRITE || ofst==WAL_LOCK_CKPT || ofst==WAL_LOCK_READ0) + ){ + bCapture = 1; + } + + if( bCapture==0 || 0==(flags & SQLITE_SHM_UNLOCK) ){ + rc = p->pReal->pMethods->xShmLock(p->pReal, ofst, n, flags); + if( bCapture && rc==SQLITE_OK ){ + pOta->mLock |= (1 << ofst); + } + } + } + + return rc; +} + +/* +** Obtain a pointer to a mapping of a single 32KiB page of the *-shm file. +*/ +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; + int eStage = (p->pOta ? p->pOta->eStage : 0); + + /* 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. */ + assert( p->openFlags & (SQLITE_OPEN_MAIN_DB|SQLITE_OPEN_TEMP_DB) ); + if( eStage==OTA_STAGE_OAL || eStage==OTA_STAGE_MOVE ){ + 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); +} + +/* +** The xShmUnmap method. +*/ +static int otaVfsShmUnmap(sqlite3_file *pFile, int delFlag){ + ota_file *p = (ota_file*)pFile; + int rc = SQLITE_OK; + int eStage = (p->pOta ? p->pOta->eStage : 0); + + assert( p->openFlags & (SQLITE_OPEN_MAIN_DB|SQLITE_OPEN_TEMP_DB) ); + if( eStage==OTA_STAGE_OAL || eStage==OTA_STAGE_MOVE ){ + /* no-op */ + }else{ + rc = p->pReal->pMethods->xShmUnmap(p->pReal, delFlag); + } + return rc; +} + +/* +** Given that zWal points to a buffer containing a wal file name passed to +** either the xOpen() or xAccess() VFS method, return a pointer to the +** file-handle opened by the same database connection on the corresponding +** database file. +*/ +static ota_file *otaFindMaindb(ota_vfs *pOtaVfs, const char *zWal){ + ota_file *pDb; + sqlite3_mutex_enter(pOtaVfs->mutex); + for(pDb=pOtaVfs->pMain; pDb && pDb->zWal!=zWal; pDb=pDb->pMainNext); + sqlite3_mutex_leave(pOtaVfs->mutex); + return pDb; +} + +/* +** 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 ){ + ota_file *pDb = otaFindMaindb(pOtaVfs, zName); + if( pDb ){ + if( pDb->pOta && pDb->pOta->eStage==OTA_STAGE_OAL ){ + char *zCopy = otaStrndup(zName, &rc); + if( zCopy ){ + int nCopy = strlen(zCopy); + zCopy[nCopy-3] = 'o'; + zOpen = (const char*)(pFd->zDel = zCopy); + } + pFd->pOta = pDb->pOta; + } + pDb->pWalFd = pFd; + } + } + } + + if( rc==SQLITE_OK ){ + rc = pRealVfs->xOpen(pRealVfs, zOpen, pFd->pReal, flags, pOutFlags); + } + if( pFd->pReal->pMethods ){ + /* The xOpen() operation has succeeded. Set the sqlite3_file.pMethods + ** pointer and, if the file is a main database file, link it into the + ** mutex protected linked list of all such files. */ + pFile->pMethods = &otavfs_io_methods; + if( flags & SQLITE_OPEN_MAIN_DB ){ + sqlite3_mutex_enter(pOtaVfs->mutex); + pFd->pMainNext = pOtaVfs->pMain; + pOtaVfs->pMain = pFd; + sqlite3_mutex_leave(pOtaVfs->mutex); + } + } + + 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 this call is to check if a *-wal file associated with an OTA target + ** database connection exists, and the OTA update is in OTA_STAGE_OAL, + ** the following special handling is activated: + ** + ** a) if the *-wal file does exist, return SQLITE_CANTOPEN. This + ** ensures that the OTA extension never tries to update a database + ** in wal mode, even if the first page of the database file has + ** been damaged. + ** + ** b) if the *-wal file does not exist, claim that it does anyway, + ** causing SQLite to call xOpen() to open it. This call will also + ** be intercepted (see the otaVfsOpen() function) and the *-oal + ** file opened instead. + */ + if( rc==SQLITE_OK && flags==SQLITE_ACCESS_EXISTS ){ + ota_file *pDb = otaFindMaindb(pOtaVfs, zPath); + if( pDb && pDb->pOta && pDb->pOta->eStage==OTA_STAGE_OAL ){ + 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); +} + +/* +** No-op. +*/ +static int otaVfsGetLastError(sqlite3_vfs *pVfs, int a, char *b){ + return 0; +} + +/* +** Deregister and destroy an OTA vfs created by an earlier call to +** sqlite3ota_create_vfs(). +*/ +void sqlite3ota_destroy_vfs(const char *zName){ + sqlite3_vfs *pVfs = sqlite3_vfs_find(zName); + if( pVfs && pVfs->xOpen==otaVfsOpen ){ + sqlite3_mutex_free(((ota_vfs*)pVfs)->mutex); + sqlite3_vfs_unregister(pVfs); + sqlite3_free(pVfs); + } +} + +/* +** Create an OTA VFS named zName that accesses the underlying file-system +** via existing VFS zParent. The new object is registered as a non-default +** VFS with SQLite before returning. +*/ +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 */ + }; + + ota_vfs *pNew = 0; /* Newly allocated VFS */ + int nName; + int rc = SQLITE_OK; + + int nByte; + nName = strlen(zName); + nByte = sizeof(ota_vfs) + nName + 1; + pNew = (ota_vfs*)sqlite3_malloc(nByte); + if( pNew==0 ){ + rc = SQLITE_NOMEM; + }else{ + sqlite3_vfs *pParent; /* Parent VFS */ + memset(pNew, 0, nByte); + pParent = sqlite3_vfs_find(zParent); + if( pParent==0 ){ + rc = SQLITE_NOTFOUND; + }else{ + 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); + + /* Allocate the mutex and register the new VFS (not as the default) */ + pNew->mutex = sqlite3_mutex_alloc(SQLITE_MUTEX_RECURSIVE); + if( pNew->mutex==0 ){ + rc = SQLITE_NOMEM; + }else{ + rc = sqlite3_vfs_register(&pNew->base, 0); + } + } + + if( rc!=SQLITE_OK ){ + sqlite3_mutex_free(pNew->mutex); + sqlite3_free(pNew); + } + } + + return rc; +} + + +/**************************************************************************/ + +#endif /* !defined(SQLITE_CORE) || defined(SQLITE_ENABLE_OTA) */ diff --git a/ext/ota/sqlite3ota.h b/ext/ota/sqlite3ota.h new file mode 100644 index 000000000..ed5f652c2 --- /dev/null +++ b/ext/ota/sqlite3ota.h @@ -0,0 +1,369 @@ +/* +** 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 the public interface for the OTA extension. +*/ + +/* +** SUMMARY +** +** Writing a transaction containing a large number of operations on +** b-tree indexes that are collectively larger than the available cache +** memory can be very inefficient. +** +** The problem is that in order to update a b-tree, the leaf page (at least) +** containing the entry being inserted or deleted must be modified. If the +** working set of leaves is larger than the available cache memory, then a +** single leaf that is modified more than once as part of the transaction +** may be loaded from or written to the persistent media multiple times. +** Additionally, because the index updates are likely to be applied in +** random order, access to pages within the database is also likely to be in +** random order, which is itself quite inefficient. +** +** One way to improve the situation is to sort the operations on each index +** by index key before applying them to the b-tree. This leads to an IO +** pattern that resembles a single linear scan through the index b-tree, +** and all but guarantees each modified leaf page is loaded and stored +** exactly once. SQLite uses this trick to improve the performance of +** CREATE INDEX commands. This extension allows it to be used to improve +** the performance of large transactions on existing databases. +** +** Additionally, this extension allows the work involved in writing the +** large transaction to be broken down into sub-transactions performed +** sequentially by separate processes. This is useful if the system cannot +** guarantee that a single update process will run for long enough to apply +** the entire update, for example because the update is being applied on a +** mobile device that is frequently rebooted. Even after the writer process +** has committed one or more sub-transactions, other database clients continue +** to read from the original database snapshot. In other words, partially +** applied transactions are not visible to other clients. +** +** "OTA" stands for "Over The Air" update. As in a large database update +** transmitted via a wireless network to a mobile device. A transaction +** applied using this extension is hence refered to as an "OTA update". +** +** +** LIMITATIONS +** +** An "OTA update" transaction is subject to the following limitations: +** +** * The transaction must consist of INSERT, UPDATE and DELETE operations +** only. +** +** * INSERT statements may not use any default values. +** +** * UPDATE and DELETE statements must identify their target rows by +** PRIMARY KEY values. If the table being written has no PRIMARY KEY, +** affected rows must be identified by rowid. +** +** * UPDATE statements may not modify PRIMARY KEY columns. +** +** * No triggers will be fired. +** +** * No foreign key violations are detected or reported. +** +** * CHECK constraints are not enforced. +** +** * No constraint handling mode except for "OR ROLLBACK" is supported. +** +** +** PREPARATION +** +** An "OTA update" is stored as a separate SQLite database. A database +** containing an OTA update is an "OTA database". For each table in the +** target database to be updated, the OTA database should contain a table +** named "data_<target name>" containing the same set of columns as the +** target table, and one more - "ota_control". The data_% table should +** have no PRIMARY KEY or UNIQUE constraints, but each column should have +** the same type as the corresponding column in the target database. +** The "ota_control" column should have no type at all. For example, if +** the target database contains: +** +** CREATE TABLE t1(a INTEGER PRIMARY KEY, b TEXT, c UNIQUE); +** +** Then the OTA database should contain: +** +** CREATE TABLE data_t1(a INTEGER, b TEXT, c, ota_control); +** +** The order of the columns in the data_% table does not matter. +** +** If the target database table is a virtual table or a table that has no +** PRIMARY KEY declaration, the data_% table must also contain a column +** named "ota_rowid". This column is mapped to the tables implicit primary +** key column - "rowid". Virtual tables for which the "rowid" column does +** not function like a primary key value cannot be updated using OTA. For +** example, if the target db contains either of the following: +** +** CREATE VIRTUAL TABLE x1 USING fts3(a, b); +** CREATE TABLE x1(a, b) +** +** then the OTA database should contain: +** +** CREATE TABLE data_x1(a, b, ota_rowid, ota_control); +** +** All non-hidden columns (i.e. all columns matched by "SELECT *") of the +** target table must be present in the input table. For virtual tables, +** hidden columns are optional - they are updated by OTA if present in +** the input table, or not otherwise. For example, to write to an fts4 +** table with a hidden languageid column such as: +** +** CREATE VIRTUAL TABLE ft1 USING fts4(a, b, languageid='langid'); +** +** Either of the following input table schemas may be used: +** +** CREATE TABLE data_ft1(a, b, langid, ota_rowid, ota_control); +** CREATE TABLE data_ft1(a, b, ota_rowid, ota_control); +** +** For each row to INSERT into the target database as part of the OTA +** update, the corresponding data_% table should contain a single record +** with the "ota_control" column set to contain integer value 0. The +** other columns should be set to the values that make up the new record +** to insert. +** +** If the target database table has an INTEGER PRIMARY KEY, it is not +** possible to insert a NULL value into the IPK column. Attempting to +** do so results in an SQLITE_MISMATCH error. +** +** For each row to DELETE from the target database as part of the OTA +** update, the corresponding data_% table should contain a single record +** with the "ota_control" column set to contain integer value 1. The +** real primary key values of the row to delete should be stored in the +** corresponding columns of the data_% table. The values stored in the +** other columns are not used. +** +** For each row to UPDATE from the target database as part of the OTA +** update, the corresponding data_% table should contain a single record +** with the "ota_control" column set to contain a value of type text. +** The real primary key values identifying the row to update should be +** stored in the corresponding columns of the data_% table row, as should +** the new values of all columns being update. The text value in the +** "ota_control" column must contain the same number of characters as +** there are columns in the target database table, and must consist entirely +** of 'x' and '.' characters (or in some special cases 'd' - see below). For +** each column that is being updated, the corresponding character is set to +** 'x'. For those that remain as they are, the corresponding character of the +** ota_control value should be set to '.'. For example, given the tables +** above, the update statement: +** +** UPDATE t1 SET c = 'usa' WHERE a = 4; +** +** is represented by the data_t1 row created by: +** +** INSERT INTO data_t1(a, b, c, ota_control) VALUES(4, NULL, 'usa', '..x'); +** +** Instead of an 'x' character, characters of the ota_control value specified +** for UPDATEs may also be set to 'd'. In this case, instead of updating the +** target table with the value stored in the corresponding data_% column, the +** user-defined SQL function "ota_delta()" is invoked and the result stored in +** the target table column. ota_delta() is invoked with two arguments - the +** original value currently stored in the target table column and the +** value specified in the data_xxx table. +** +** For example, this row: +** +** INSERT INTO data_t1(a, b, c, ota_control) VALUES(4, NULL, 'usa', '..d'); +** +** is similar to an UPDATE statement such as: +** +** UPDATE t1 SET c = ota_delta(c, 'usa') WHERE a = 4; +** +** If the target database table is a virtual table or a table with no PRIMARY +** KEY, the ota_control value should not include a character corresponding +** to the ota_rowid value. For example, this: +** +** INSERT INTO data_ft1(a, b, ota_rowid, ota_control) +** VALUES(NULL, 'usa', 12, '.x'); +** +** causes a result similar to: +** +** UPDATE ft1 SET b = 'usa' WHERE rowid = 12; +** +** +** USAGE +** +** The API declared below allows an application to apply an OTA update +** stored on disk to an existing target database. Essentially, the +** application: +** +** 1) Opens an OTA handle using the sqlite3ota_open() function. +** +** 2) Registers any required virtual table modules with the database +** handle returned by sqlite3ota_db(). Also, if required, register +** the ota_delta() implementation. +** +** 3) Calls the sqlite3ota_step() function one or more times on +** the new handle. Each call to sqlite3ota_step() performs a single +** b-tree operation, so thousands of calls may be required to apply +** a complete update. +** +** 4) Calls sqlite3ota_close() to close the OTA update handle. If +** sqlite3ota_step() has been called enough times to completely +** apply the update to the target database, then the OTA database +** is marked as fully applied. Otherwise, the state of the OTA +** update application is saved in the OTA database for later +** resumption. +** +** See comments below for more detail on APIs. +** +** If an update is only partially applied to the target database by the +** time sqlite3ota_close() is called, various state information is saved +** within the OTA database. This allows subsequent processes to automatically +** resume the OTA update from where it left off. +** +** To remove all OTA extension state information, returning an OTA database +** to its original contents, it is sufficient to drop all tables that begin +** with the prefix "ota_" +*/ + +#ifndef _SQLITE3OTA_H +#define _SQLITE3OTA_H + +#include "sqlite3.h" /* Required for error code definitions */ + +typedef struct sqlite3ota sqlite3ota; + +/* +** 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); + +/* +** Internally, each OTA connection uses a separate SQLite database +** connection to access the target and ota update databases. This +** API allows the application direct access to these database handles. +** +** The first argument passed to this function must be a valid, open, OTA +** handle. The second argument should be passed zero to access the target +** database handle, or non-zero to access the ota update database handle. +** Accessing the underlying database handles may be useful in the +** following scenarios: +** +** * If any target tables are virtual tables, it may be necessary to +** call sqlite3_create_module() on the target database handle to +** register the required virtual table implementations. +** +** * If the data_xxx tables in the OTA source database are virtual +** tables, the application may need to call sqlite3_create_module() on +** the ota update db handle to any required virtual table +** implementations. +** +** * If the application uses the "ota_delta()" feature described above, +** it must use sqlite3_create_function() or similar to register the +** ota_delta() implementation with the target database handle. +*/ +sqlite3 *sqlite3ota_db(sqlite3ota*, int bOta); + +/* +** Do some work towards applying the OTA update to the target db. +** +** Return SQLITE_DONE if the update has been completely applied, or +** SQLITE_OK if no error occurs but there remains work to do to apply +** the OTA update. If an error does occur, some other error code is +** returned. +** +** Once a call to sqlite3ota_step() has returned a value other than +** SQLITE_OK, all subsequent calls on the same OTA handle are no-ops +** that immediately return the same value. +*/ +int sqlite3ota_step(sqlite3ota *pOta); + +/* +** Close an OTA handle. +** +** If the OTA update has been completely applied, mark the OTA database +** as fully applied. 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 +** point to a buffer containing a utf-8 formatted English language error +** message. It is the responsibility of the caller to eventually free any +** such buffer using sqlite3_free(). +** +** Otherwise, if no error occurs, this function returns SQLITE_OK if the +** update has been partially applied, or SQLITE_DONE if it has been +** completely applied. +*/ +int sqlite3ota_close(sqlite3ota *pOta, char **pzErrmsg); + +/* +** Return the total number of key-value operations (inserts, deletes or +** updates) that have been performed on the target database since the +** current OTA update was started. +*/ +sqlite3_int64 sqlite3ota_progress(sqlite3ota *pOta); + +/* +** Create an OTA VFS named zName that accesses the underlying file-system +** via existing VFS zParent. The new object is registered as a non-default +** VFS with SQLite before returning. +** +** 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 */ + diff --git a/ext/ota/test_ota.c b/ext/ota/test_ota.c new file mode 100644 index 000000000..601453e12 --- /dev/null +++ b/ext/ota/test_ota.c @@ -0,0 +1,245 @@ +/* +** 2015 February 16 +** +** 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. +** +************************************************************************* +*/ + +#include "sqlite3.h" + +#if defined(SQLITE_TEST) +#if !defined(SQLITE_CORE) || defined(SQLITE_ENABLE_OTA) + +#include "sqlite3ota.h" +#include <tcl.h> +#include <assert.h> + +/* From main.c (apparently...) */ +extern const char *sqlite3ErrName(int); + +void test_ota_delta(sqlite3_context *pCtx, int nArg, sqlite3_value **apVal){ + Tcl_Interp *interp = (Tcl_Interp*)sqlite3_user_data(pCtx); + Tcl_Obj *pScript; + int i; + + pScript = Tcl_NewObj(); + Tcl_IncrRefCount(pScript); + Tcl_ListObjAppendElement(0, pScript, Tcl_NewStringObj("ota_delta", -1)); + for(i=0; i<nArg; i++){ + sqlite3_value *pIn = apVal[i]; + const char *z = (const char*)sqlite3_value_text(pIn); + Tcl_ListObjAppendElement(0, pScript, Tcl_NewStringObj(z, -1)); + } + + if( TCL_OK==Tcl_EvalObjEx(interp, pScript, TCL_GLOBAL_ONLY) ){ + const char *z = Tcl_GetStringResult(interp); + sqlite3_result_text(pCtx, z, -1, SQLITE_TRANSIENT); + }else{ + Tcl_BackgroundError(interp); + } + + Tcl_DecrRefCount(pScript); +} + + +static int test_sqlite3ota_cmd( + ClientData clientData, + Tcl_Interp *interp, + int objc, + Tcl_Obj *CONST objv[] +){ + int ret = TCL_OK; + sqlite3ota *pOta = (sqlite3ota*)clientData; + const char *azMethod[] = { "step", "close", "create_ota_delta", 0 }; + int iMethod; + + if( objc!=2 ){ + Tcl_WrongNumArgs(interp, 1, objv, "METHOD"); + return TCL_ERROR; + } + if( Tcl_GetIndexFromObj(interp, objv[1], azMethod, "method", 0, &iMethod) ){ + return TCL_ERROR; + } + + switch( iMethod ){ + case 0: /* step */ { + int rc = sqlite3ota_step(pOta); + Tcl_SetObjResult(interp, Tcl_NewStringObj(sqlite3ErrName(rc), -1)); + break; + } + + case 1: /* close */ { + char *zErrmsg = 0; + int rc; + Tcl_DeleteCommand(interp, Tcl_GetString(objv[0])); + rc = sqlite3ota_close(pOta, &zErrmsg); + if( rc==SQLITE_OK || rc==SQLITE_DONE ){ + Tcl_SetObjResult(interp, Tcl_NewStringObj(sqlite3ErrName(rc), -1)); + assert( zErrmsg==0 ); + }else{ + Tcl_SetObjResult(interp, Tcl_NewStringObj(sqlite3ErrName(rc), -1)); + if( zErrmsg ){ + Tcl_AppendResult(interp, " - ", zErrmsg, 0); + sqlite3_free(zErrmsg); + } + ret = TCL_ERROR; + } + break; + } + + case 2: /* create_ota_delta */ { + sqlite3 *db = sqlite3ota_db(pOta, 0); + int rc = sqlite3_create_function( + db, "ota_delta", -1, SQLITE_UTF8, (void*)interp, test_ota_delta, 0, 0 + ); + Tcl_SetObjResult(interp, Tcl_NewStringObj(sqlite3ErrName(rc), -1)); + ret = (rc==SQLITE_OK ? TCL_OK : TCL_ERROR); + break; + } + + default: /* seems unlikely */ + assert( !"cannot happen" ); + break; + } + + return ret; +} + +/* +** Tclcmd: sqlite3ota CMD <target-db> <ota-db> +*/ +static int test_sqlite3ota( + ClientData clientData, + Tcl_Interp *interp, + int objc, + Tcl_Obj *CONST objv[] +){ + sqlite3ota *pOta = 0; + const char *zCmd; + const char *zTarget; + const char *zOta; + + if( objc!=4 ){ + Tcl_WrongNumArgs(interp, 1, objv, "NAME TARGET-DB OTA-DB"); + return TCL_ERROR; + } + zCmd = Tcl_GetString(objv[1]); + zTarget = Tcl_GetString(objv[2]); + zOta = Tcl_GetString(objv[3]); + + pOta = sqlite3ota_open(zTarget, zOta); + 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; +} + +/* +** Tclcmd: sqlite3ota_internal_test +*/ +static int test_sqlite3ota_internal_test( + ClientData clientData, + Tcl_Interp *interp, + int objc, + Tcl_Obj *CONST objv[] +){ + sqlite3 *db; + + if( objc!=1 ){ + Tcl_WrongNumArgs(interp, 1, objv, ""); + return TCL_ERROR; + } + + db = sqlite3ota_db(0, 0); + if( db!=0 ){ + Tcl_AppendResult(interp, "sqlite3ota_db(0, 0)!=0", 0); + return TCL_ERROR; + } + + return TCL_OK; +} + +int SqliteOta_Init(Tcl_Interp *interp){ + 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 }, + { "sqlite3ota_internal_test", test_sqlite3ota_internal_test }, + }; + int i; + for(i=0; i<sizeof(aObjCmd)/sizeof(aObjCmd[0]); i++){ + Tcl_CreateObjCommand(interp, aObjCmd[i].zName, aObjCmd[i].xProc, 0, 0); + } + return TCL_OK; +} + +#else +#include <tcl.h> +int SqliteOta_Init(Tcl_Interp *interp){ return TCL_OK; } +#endif /* !defined(SQLITE_CORE) || defined(SQLITE_ENABLE_OTA) */ +#endif /* defined(SQLITE_TEST) */ + @@ -65,7 +65,7 @@ LIBOBJ+= vdbe.o parse.o \ mutex.o mutex_noop.o mutex_unix.o mutex_w32.o \ notify.o opcodes.o os.o os_unix.o os_win.o \ pager.o pcache.o pcache1.o pragma.o prepare.o printf.o \ - random.o resolve.o rowset.o rtree.o select.o status.o \ + random.o resolve.o rowset.o rtree.o select.o sqlite3ota.o status.o \ table.o threads.o tokenize.o trigger.o \ update.o userauth.o util.o vacuum.o \ vdbeapi.o vdbeaux.o vdbeblob.o vdbemem.o vdbesort.o \ @@ -219,6 +219,9 @@ SRC += \ 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 # @@ -236,6 +239,7 @@ SRC += \ TESTSRC = \ $(TOP)/ext/fts3/fts3_term.c \ $(TOP)/ext/fts3/fts3_test.c \ + $(TOP)/ext/ota/test_ota.c \ $(TOP)/src/test1.c \ $(TOP)/src/test2.c \ $(TOP)/src/test3.c \ @@ -337,7 +341,7 @@ TESTSRC2 = \ $(TOP)/ext/fts3/fts3_expr.c \ $(TOP)/ext/fts3/fts3_tokenizer.c \ $(TOP)/ext/fts3/fts3_write.c \ - $(TOP)/ext/async/sqlite3async.c + $(TOP)/ext/async/sqlite3async.c # Header files used by all library source files. # @@ -571,6 +575,9 @@ rtree.o: $(TOP)/ext/rtree/rtree.c $(HDR) $(EXTHDR) userauth.o: $(TOP)/ext/userauth/userauth.c $(HDR) $(EXTHDR) $(TCCX) -DSQLITE_CORE -c $(TOP)/ext/userauth/userauth.c +sqlite3ota.o: $(TOP)/ext/ota/sqlite3ota.c $(HDR) $(EXTHDR) + $(TCCX) -DSQLITE_CORE -c $(TOP)/ext/ota/sqlite3ota.c + # Rules for building test programs and for running tests # @@ -683,6 +690,11 @@ wordcount$(EXE): $(TOP)/test/wordcount.c sqlite3.c speedtest1$(EXE): $(TOP)/test/speedtest1.c sqlite3.o $(TCC) -I. -o speedtest1$(EXE) $(TOP)/test/speedtest1.c sqlite3.o $(THREADLIB) +ota$(EXE): $(TOP)/ext/ota/ota.c $(TOP)/ext/ota/sqlite3ota.c sqlite3.o + $(TCC) -I. -o ota$(EXE) \ + $(TOP)/ext/ota/ota.c $(TOP)/ext/ota/sqlite3ota.c sqlite3.o \ + $(THREADLIB) + # This target will fail if the SQLite amalgamation contains any exported # symbols that do not begin with "sqlite3_". It is run as part of the # releasetest.tcl script. @@ -1,5 +1,5 @@ -C Update\sdocument\son\ssqlite3_mprintf()\sand\srelated\sfunctions.\s\sDiscuss\sthe\n%w\sformat\sand\spoint\sout\sthat\sobscure\sANSI-C\sformats\sare\snot\ssupported.\nNo\schanges\sto\scode. -D 2015-02-21T15:42:57.800 +C Merge\slatest\strunk\schanges\swith\sthis\sbranch. +D 2015-02-23T16:17:46.425 F Makefile.arm-wince-mingw32ce-gcc d6df77f1f48d690bd73162294bbba7f59507c72f F Makefile.in 6b9e7677829aa94b9f30949656e27312aefb9a46 F Makefile.linux-gcc 91d710bdc4998cb015f39edf3cb314ec4f4d7e23 @@ -123,6 +123,24 @@ F ext/misc/totype.c 4a167594e791abeed95e0a8db028822b5e8fe512 F ext/misc/vfslog.c fe40fab5c077a40477f7e5eba994309ecac6cc95 F ext/misc/vtshim.c babb0dc2bf116029e3e7c9a618b8a1377045303e F ext/misc/wholenumber.c 784b12543d60702ebdd47da936e278aa03076212 +F ext/ota/ota.c c11a85af71dccc45976622fe7a51169a481caa91 +F ext/ota/ota1.test 66cf5cb7fb1b2fcf74b9ec3fca2b5bf0286ae330 +F ext/ota/ota10.test 85e0f6e7964db5007590c1b299e75211ed4240d4 +F ext/ota/ota11.test 2f606cd2b4af260a86b549e91b9f395450fc75cb +F ext/ota/ota12.test 0dff44474de448fb4b0b28c20da63273a4149abb +F ext/ota/ota3.test 3fe3521fbdce32d0e4e116a60999c3cba47712c5 +F ext/ota/ota5.test ad0799daf8923ddebffe75ae8c5504ca90b7fadb +F ext/ota/ota6.test 3bde7f69a894748b27206b6753462ec3b75b6bb6 +F ext/ota/ota7.test 1fe2c5761705374530e29f70c39693076028221a +F ext/ota/ota8.test cd70e63a0c29c45c0906692827deafa34638feda +F ext/ota/ota9.test d3eee95dd836824d07a22e5efcdb7bf6e869358b +F ext/ota/otaA.test ef4bfa8cfd4ed814ae86f7457b64aa2f18c90171 +F ext/ota/otacrash.test a078d34e2edbcedac5f894e3e7d08d452a327007 +F ext/ota/otafault.test 8c43586c2b96ca16bbce00b5d7e7d67316126db8 +F ext/ota/otafault2.test fa202a98ca221faec318f3e5c5f39485b1256561 +F ext/ota/sqlite3ota.c e060a4cb49280ee86fc60055ed5de4b3bf56c892 +F ext/ota/sqlite3ota.h 4cd82fbac9cbea89bd51edace3ec5c57866c02e3 +F ext/ota/test_ota.c e34c801c665d64b4b9e00b71f1acf8c652404b2b F ext/rtree/README 6315c0d73ebf0ec40dedb5aa0e942bc8b54e3761 F ext/rtree/rtree.c 14e6239434d4e3f65d3e90320713f26aa24e167f F ext/rtree/rtree.h 834dbcb82dc85b2481cde6a07cdadfddc99e9b9e @@ -152,7 +170,7 @@ F ext/userauth/userauth.c 5fa3bdb492f481bbc1709fc83c91ebd13460c69e F install-sh 9d4de14ab9fb0facae2f48780b874848cbf2f895 x F ltmain.sh 3ff0879076df340d2e23ae905484d8c15d5fdea8 F magic.txt 8273bf49ba3b0c8559cb2774495390c31fd61c60 -F main.mk 0bae136db3f3ce451079ae335124b46163d37020 +F main.mk aac7f4bf0da24bd23faf1847d83f6b959e5a1635 F mkopcodec.awk c2ff431854d702cdd2d779c9c0d1f58fa16fa4ea F mkopcodeh.awk c6b3fa301db6ef7ac916b14c60868aeaec1337b5 F mkso.sh fd21c06b063bb16a5d25deea1752c2da6ac3ed83 @@ -232,14 +250,14 @@ F src/resolve.c f4d79e31ffa5820c2e3d1740baa5e9b190425f2b F src/rowset.c eccf6af6d620aaa4579bd3b72c1b6395d9e9fa1e F src/select.c e46cef4c224549b439384c88fc7f57ba064dad54 F src/shell.c 6276582ee4e9114e0bb0795772414caaf21c0f8e -F src/sqlite.h.in 86cddbfdb3155967858c1469108813bcc08eda21 +F src/sqlite.h.in f7df4082533ae9c5acf19bd513e4ff4b638f51d1 F src/sqlite3.rc 992c9f5fb8285ae285d6be28240a7e8d3a7f2bad F src/sqlite3ext.h 17d487c3c91b0b8c584a32fbeb393f6f795eea7d F src/sqliteInt.h 57a405ae6d2ed10fff52de376d18f21e04d96609 F src/sqliteLimit.h 164b0e6749d31e0daa1a4589a169d31c0dec7b3d F src/status.c 81712116e826b0089bb221b018929536b2b5406f F src/table.c e7a09215315a978057fb42c640f890160dbcc45e -F src/tclsqlite.c b8014393a96a9781bb635c8b1f52fc9b77a2bfcf +F src/tclsqlite.c c7897dcf036c0bb7bd814a0e615723b83bc7df86 F src/test1.c 90fbedce75330d48d99eadb7d5f4223e86969585 F src/test2.c 577961fe48961b2f2e5c8b56ee50c3f459d3359d F src/test3.c 64d2afdd68feac1bb5e2ffb8226c8c639f798622 @@ -254,7 +272,7 @@ F src/test_autoext.c dea8a01a7153b9adc97bd26161e4226329546e12 F src/test_backup.c 2e6e6a081870150f20c526a2e9d0d29cda47d803 F src/test_blob.c 1f2e3e25255b731c4fcf15ee7990d06347cb6c09 F src/test_btree.c 2e9978eca99a9a4bfa8cae949efb00886860a64f -F src/test_config.c e7b2e1634324d746aa5e1c7e0929470e8be27953 +F src/test_config.c a55a18bbbb117eab92e4343f7ee753b25e0aee49 F src/test_demovfs.c 0de72c2c89551629f58486fde5734b7d90758852 F src/test_devsym.c e7498904e72ba7491d142d5c83b476c4e76993bc F src/test_fs.c ced436e3d4b8e4681328409b8081051ce614e28f @@ -773,6 +791,7 @@ F test/orderby6.test 8b38138ab0972588240b3fca0985d2e400432859 F test/orderby7.test 3d1383d52ade5b9eb3a173b3147fdd296f0202da F test/orderby8.test 23ef1a5d72bd3adcc2f65561c654295d1b8047bd F test/oserror.test 14fec2796c2b6fe431c7823750e8a18a761176d7 +F test/ota.test 3a8d97cbf8f7210dc6a638797c4e4cd674036927 F test/ovfl.test 4f7ca651cba5c059a12d8c67dddd49bec5747799 F test/pager1.test 1acbdb14c5952a72dd43129cabdbf69aaa3ed1fa F test/pager2.test 67b8f40ae98112bcdba1f2b2d03ea83266418c71 @@ -786,8 +805,8 @@ F test/pagesize.test 1dd51367e752e742f58e861e65ed7390603827a0 F test/pcache.test b09104b03160aca0d968d99e8cd2c5b1921a993d F test/pcache2.test a83efe2dec0d392f814bfc998def1d1833942025 F test/percentile.test 4243af26b8f3f4555abe166f723715a1f74c77ff -F test/permutations.test f9cc1dd987986c9d4949211c7a4ed55ec9aecba1 -F test/pragma.test 6cf0f0ce4618e841457aa42745afda55ddbc95fe +F test/permutations.test 0e2dc2aab7b1043bd2b4404f51651c31da007e52 +F test/pragma.test 66776f48f533c7248d04c1473deb3ebb792daacd F test/pragma2.test aea7b3d82c76034a2df2b38a13745172ddc0bc13 F test/pragma3.test 6f849ccffeee7e496d2f2b5e74152306c0b8757c F test/printf.test ec9870c4dce8686a37818e0bf1aba6e6a1863552 @@ -805,7 +824,7 @@ F test/randexpr1.test eda062a97e60f9c38ae8d806b03b0ddf23d796df F test/rdonly.test dd30a4858d8e0fbad2304c2bd74a33d4df36412a F test/regexp1.test 497ea812f264d12b6198d6e50a76be4a1973a9d8 F test/reindex.test 44edd3966b474468b823d481eafef0c305022254 -F test/releasetest.tcl 13f401c10dd4fe1a2fb811ae6ed27fd7d1300d3c +F test/releasetest.tcl b290782d0697b4e83d671da192cd9a7f71e2f6c1 F test/resolver01.test 33abf37ff8335e6bf98f2b45a0af3e06996ccd9a F test/rollback.test 458fe73eb3ffdfdf9f6ba3e9b7350a6220414dea F test/rollback2.test fc14cf6d1a2b250d2735ef16124b971bce152f14 @@ -1206,7 +1225,7 @@ F tool/mkopts.tcl 66ac10d240cc6e86abd37dc908d50382f84ff46e F tool/mkpragmatab.tcl 94f196c9961e0ca3513e29f57125a3197808be2d F tool/mkspeedsql.tcl a1a334d288f7adfe6e996f2e712becf076745c97 F tool/mksqlite3c-noext.tcl 9ef48e1748dce7b844f67e2450ff9dfeb0fb4ab5 -F tool/mksqlite3c.tcl 6b8e572a90eb4e0086e3ba90d88b76c085919863 +F tool/mksqlite3c.tcl d8b0b0cc5f0e912058c9300f052769c62404d2d9 F tool/mksqlite3h.tcl ba24038056f51fde07c0079c41885ab85e2cff12 F tool/mksqlite3internalh.tcl eb994013e833359137eb53a55acdad0b5ae1049b F tool/mkvsix.tcl 52a4c613707ac34ae9c226e5ccc69cb948556105 @@ -1239,7 +1258,7 @@ F tool/vdbe_profile.tcl 67746953071a9f8f2f668b73fe899074e2c6d8c1 F tool/warnings-clang.sh f6aa929dc20ef1f856af04a730772f59283631d4 F tool/warnings.sh 0abfd78ceb09b7f7c27c688c8e3fe93268a13b32 F tool/win/sqlite.vsix deb315d026cc8400325c5863eef847784a219a2f -P c299e55a661c04f71ab43cb8aed04f8ece6e0567 -R dda772aafb6dee5a528cc1c5d5a14dc2 -U drh -Z 75f83919b9b483315b93f05f4ef8627c +P f7865b942834dd2f6b865336e08ba1adbf20612a f8917ba4d917bc762b3b252466ab72a8a70dc0d8 +R 330b1846f73876f19a54b99531a8e2fe +U dan +Z d8008c2bc531990fbe4786aa324b64a7 diff --git a/manifest.uuid b/manifest.uuid index c46ad2040..fe869247e 100644 --- a/manifest.uuid +++ b/manifest.uuid @@ -1 +1 @@ -f8917ba4d917bc762b3b252466ab72a8a70dc0d8
\ No newline at end of file +e5ca79d2d3c066252b0baa4f76ddbe0ee3b14cb6
\ No newline at end of file diff --git a/src/sqlite.h.in b/src/sqlite.h.in index ee910393e..1559e0036 100644 --- a/src/sqlite.h.in +++ b/src/sqlite.h.in @@ -941,6 +941,10 @@ struct sqlite3_io_methods { ** pointed to by the pArg argument. This capability is used during testing ** and only needs to be supported when SQLITE_TEST is defined. ** +** <li>[[SQLITE_FCNTL_ZIPVFS]] +** The [SQLITE_FCNTL_ZIPVFS] opcode is implemented by zipvfs only. All other +** VFS should return SQLITE_NOTFOUND for this opcode. +** ** </ul> */ #define SQLITE_FCNTL_LOCKSTATE 1 @@ -965,6 +969,8 @@ struct sqlite3_io_methods { #define SQLITE_FCNTL_SYNC 21 #define SQLITE_FCNTL_COMMIT_PHASETWO 22 #define SQLITE_FCNTL_WIN32_SET_HANDLE 23 +#define SQLITE_FCNTL_ZIPVFS 24 +#define SQLITE_FCNTL_OTA 25 /* deprecated names */ #define SQLITE_GET_LOCKPROXYFILE SQLITE_FCNTL_GET_LOCKPROXYFILE diff --git a/src/tclsqlite.c b/src/tclsqlite.c index b1d4dc413..075fb6254 100644 --- a/src/tclsqlite.c +++ b/src/tclsqlite.c @@ -3756,6 +3756,7 @@ static void init_all(Tcl_Interp *interp){ extern int Sqlitemultiplex_Init(Tcl_Interp*); extern int SqliteSuperlock_Init(Tcl_Interp*); extern int SqlitetestSyscall_Init(Tcl_Interp*); + extern int SqliteOta_Init(Tcl_Interp*); #if defined(SQLITE_ENABLE_FTS3) || defined(SQLITE_ENABLE_FTS4) extern int Sqlitetestfts3_Init(Tcl_Interp *interp); @@ -3799,6 +3800,7 @@ static void init_all(Tcl_Interp *interp){ Sqlitemultiplex_Init(interp); SqliteSuperlock_Init(interp); SqlitetestSyscall_Init(interp); + SqliteOta_Init(interp); #if defined(SQLITE_ENABLE_FTS3) || defined(SQLITE_ENABLE_FTS4) Sqlitetestfts3_Init(interp); diff --git a/src/test_config.c b/src/test_config.c index 25e6a5698..6f5e14ee6 100644 --- a/src/test_config.c +++ b/src/test_config.c @@ -430,6 +430,12 @@ Tcl_SetVar2(interp, "sqlite_options", "mergesort", "1", TCL_GLOBAL_ONLY); Tcl_SetVar2(interp, "sqlite_options", "or_opt", "1", TCL_GLOBAL_ONLY); #endif +#ifdef SQLITE_ENABLE_OTA + Tcl_SetVar2(interp, "sqlite_options", "ota", "1", TCL_GLOBAL_ONLY); +#else + Tcl_SetVar2(interp, "sqlite_options", "ota", "0", TCL_GLOBAL_ONLY); +#endif + #ifdef SQLITE_OMIT_PAGER_PRAGMAS Tcl_SetVar2(interp, "sqlite_options", "pager_pragmas", "0", TCL_GLOBAL_ONLY); #else diff --git a/test/ota.test b/test/ota.test new file mode 100644 index 000000000..9dc01c2b3 --- /dev/null +++ b/test/ota.test @@ -0,0 +1,18 @@ +# 2014 September 20 +# +# 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 runs all rtree related tests. +# + +set testdir [file dirname $argv0] +source $testdir/permutations.test + +ifcapable !ota { finish_test ; return } + +run_test_suite ota +finish_test + diff --git a/test/permutations.test b/test/permutations.test index 44f62e806..ba49700b7 100644 --- a/test/permutations.test +++ b/test/permutations.test @@ -113,7 +113,7 @@ set allquicktests [test_set $alltests -exclude { vtab_err.test walslow.test walcrash.test walcrash3.test walthread.test rtree3.test indexfault.test securedel2.test sort3.test sort4.test fts4growth.test fts4growth2.test - bigsort.test + bigsort.test ota.test }] if {[info exists ::env(QUICKTEST_INCLUDE)]} { set allquicktests [concat $allquicktests $::env(QUICKTEST_INCLUDE)] @@ -939,6 +939,12 @@ test_suite "rtree" -description { All R-tree related tests. Provides coverage of source file rtree.c. } -files [glob -nocomplain $::testdir/../ext/rtree/*.test] +test_suite "ota" -description { + OTA tests. +} -files [ + test_set [glob -nocomplain $::testdir/../ext/ota/*.test] -exclude ota.test +] + test_suite "no_optimization" -description { Run test scripts with optimizations disabled using the sqlite3_test_control(SQLITE_TESTCTRL_OPTIMIZATIONS) interface. diff --git a/test/pragma.test b/test/pragma.test index e2673d3ee..1628cbaec 100644 --- a/test/pragma.test +++ b/test/pragma.test @@ -1743,7 +1743,6 @@ do_test 23.2a { db2 eval {SELECT cid, name, "desc", coll, "key", '|' FROM out ORDER BY seqno} } {2 c 0 BINARY 1 | 3 d 0 BINARY 1 | 1 b 0 BINARY 1 |} do_test 23.2b { -breakpoint; capture_pragma db2 out {PRAGMA index_xinfo(i2)} db2 eval {SELECT cid, name, "desc", coll, "key", '|' FROM out ORDER BY seqno} } {2 c 0 BINARY 1 | 3 d 0 BINARY 1 | 1 b 0 BINARY 1 | -1 {} 0 BINARY 0 |} diff --git a/test/releasetest.tcl b/test/releasetest.tcl index 746fc9bb3..aa12433af 100644 --- a/test/releasetest.tcl +++ b/test/releasetest.tcl @@ -106,6 +106,7 @@ array set ::Configs [strip_comments { -DSQLITE_ENABLE_MEMSYS3=1 -DSQLITE_ENABLE_COLUMN_METADATA=1 -DSQLITE_ENABLE_STAT4 + -DSQLITE_ENABLE_OTA -DSQLITE_MAX_ATTACHED=125 } "Device-One" { diff --git a/tool/mksqlite3c.tcl b/tool/mksqlite3c.tcl index 4ab8b12b4..4034128d5 100644 --- a/tool/mksqlite3c.tcl +++ b/tool/mksqlite3c.tcl @@ -112,8 +112,9 @@ foreach hdr { pcache.h pragma.h rtree.h - sqlite3ext.h sqlite3.h + sqlite3ext.h + sqlite3ota.h sqliteicu.h sqliteInt.h sqliteLimit.h @@ -334,6 +335,7 @@ foreach file { rtree.c icu.c fts3_icu.c + sqlite3ota.c } { copy_file tsrc/$file } |