From d1193a98939f17e2ea4bc9f60542a9ab33541bbe Mon Sep 17 00:00:00 2001 From: dan Date: Fri, 19 Sep 2014 15:06:23 +0000 Subject: [PATCH] Add extra tests for the ota extension. FossilOrigin-Name: 1e468fe1e408e513a1e1bbe72fe2a240f2991b3d --- ext/ota/ota5.test | 244 ++++++++++++++++++++++++++++++++++++++++++++++ manifest | 11 ++- manifest.uuid | 2 +- 3 files changed, 251 insertions(+), 6 deletions(-) create mode 100644 ext/ota/ota5.test diff --git a/ext/ota/ota5.test b/ext/ota/ota5.test new file mode 100644 index 0000000000..4f85c22b94 --- /dev/null +++ b/ext/ota/ota5.test @@ -0,0 +1,244 @@ +# 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. +# + +set testdir [file join [file dirname $argv0] .. .. 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 + } +} + +#------------------------------------------------------------------------- +# + + +finish_test + + + + diff --git a/manifest b/manifest index ec203b7073..f6a8d1cd9c 100644 --- a/manifest +++ b/manifest @@ -1,5 +1,5 @@ -C Update\sthe\sota\sextension\sto\ssupport\sSQLITE_ENABLE_8_3_NAMES\sbuilds. -D 2014-09-18T17:57:46.895 +C Add\sextra\stests\sfor\sthe\sota\sextension. +D 2014-09-19T15:06:23.790 F Makefile.arm-wince-mingw32ce-gcc d6df77f1f48d690bd73162294bbba7f59507c72f F Makefile.in cf57f673d77606ab0f2d9627ca52a9ba1464146a F Makefile.linux-gcc 91d710bdc4998cb015f39edf3cb314ec4f4d7e23 @@ -127,6 +127,7 @@ F ext/ota/ota1.test 7cbf37a9f6cd29320f47b041cfeb0cc1d7eaa916 F ext/ota/ota2.test 716f9c66e8bf8b0ad2fe3a5d8323e6cf460a2e27 F ext/ota/ota3.test 1c48b7476af1c5920db9a43e7b1476d421a463b5 F ext/ota/ota4.test baf23b47748a5056c713871959cc70fc623c90e9 +F ext/ota/ota5.test bd0e0f7609ece4eee2c62c5132935f0c80190cb8 F ext/ota/sqlite3ota.c b22002105b3b7f3baf63bda2b4e6a00c4973418c F ext/ota/sqlite3ota.h 7b20abe9247d292429d00f0a5c237ff6e0dc0196 F ext/rtree/README 6315c0d73ebf0ec40dedb5aa0e942bc8b54e3761 @@ -1206,7 +1207,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 fce9c6ccf101d28492a84b85463433f32ef569fb -R feab70887c117c061b61fe713ccedbe4 +P 718da6de870231d358384473b40e81c4394b8067 +R 4772700da78a181e555d5cf639b019fb U dan -Z 86cf672a01ea748dc788d9b12ee5fdc1 +Z 93b575ac2dc5f2154ac020bd95667b18 diff --git a/manifest.uuid b/manifest.uuid index 1b666612e7..9eb7806eea 100644 --- a/manifest.uuid +++ b/manifest.uuid @@ -1 +1 @@ -718da6de870231d358384473b40e81c4394b8067 \ No newline at end of file +1e468fe1e408e513a1e1bbe72fe2a240f2991b3d \ No newline at end of file -- 2.47.2