Many hyperlinks are disabled.
Use anonymous login
to enable hyperlinks.
Overview
| Comment: | Add tests and fixes for "PRAGMA ota_mode". |
|---|---|
| Downloads: | Tarball | ZIP archive | SQL archive |
| Timelines: | family | ancestors | descendants | both | ota-update |
| Files: | files | file ages | folders |
| SHA1: |
39df35c4ac65ffba76ba2c6f6727cf5e |
| User & Date: | dan 2014-09-17 15:20:24 |
Context
|
2014-09-17
| ||
| 19:05 | Fix an unintialized variable problem in sqlite3ota.c. (check-in: 01268607 user: dan tags: ota-update) | |
| 15:20 | Add tests and fixes for "PRAGMA ota_mode". (check-in: 39df35c4 user: dan tags: ota-update) | |
|
2014-09-16
| ||
| 20:02 | Clarify the effects of the pager_ota_mode pragma. Add tests and fixes for the same. (check-in: decaccc3 user: dan tags: ota-update) | |
Changes
Changes to ext/ota/ota2.test.
10 10 #*********************************************************************** 11 11 # 12 12 13 13 set testdir [file join [file dirname $argv0] .. .. test] 14 14 source $testdir/tester.tcl 15 15 set ::testprefix ota2 16 16 17 -forcedelete test.db-oal 17 +forcedelete test.db-oal test.db-bak 18 18 19 19 do_execsql_test 1.0 { 20 20 CREATE TABLE t1(a, b); 21 21 INSERT INTO t1 VALUES(1, 2); 22 22 } {} 23 23 do_test 1.1 { glob test.db* } {test.db} 24 24
Changes to ext/ota/ota4.test.
5 5 # 6 6 # May you do good and not evil. 7 7 # May you find forgiveness for yourself and forgive others. 8 8 # May you share freely, never taking more than you give. 9 9 # 10 10 #*********************************************************************** 11 11 # 12 -# Test some properties of the pager_ota_mode pragma. 12 +# Test some properties of the pager_ota_mode and ota_mode pragmas. 13 13 # 14 14 15 15 set testdir [file join [file dirname $argv0] .. .. test] 16 16 source $testdir/tester.tcl 17 17 set ::testprefix ota4 18 18 19 +#------------------------------------------------------------------------- 20 +# The following tests aim to verify some properties of the pager_ota_mode 21 +# pragma: 22 +# 19 23 # 1. Cannot set the pager_ota_mode flag on a WAL mode database. 20 24 # 21 25 # 2. Or if there is an open read transaction. 22 26 # 23 27 # 3. Cannot start a transaction with pager_ota_mode set if there 24 28 # is a WAL file in the file-system. 25 29 # ................................................................................ 26 30 # 4. Or if the wal-mode flag is set in the database file header. 27 31 # 28 32 # 5. Cannot open a transaction with pager_ota_mode set if the database 29 33 # file has been modified by a rollback mode client since the *-oal 30 34 # file was started. 31 35 # 32 36 33 -do_execsql_test 1.1 { 37 +do_execsql_test 1.1.1 { 34 38 PRAGMA journal_mode = wal; 35 39 SELECT * FROM sqlite_master; 36 40 } {wal} 37 -do_catchsql_test 1.2 { 41 +do_catchsql_test 1.1.2 { 38 42 PRAGMA pager_ota_mode = 1 39 43 } {1 {cannot set pager_ota_mode in wal mode}} 40 44 41 45 42 -do_execsql_test 2.1 { 46 +do_execsql_test 1.2.1 { 43 47 PRAGMA journal_mode = delete; 44 48 BEGIN; 45 49 SELECT * FROM sqlite_master; 46 50 } {delete} 47 -do_catchsql_test 2.2 { 51 +do_catchsql_test 1.2.2 { 48 52 PRAGMA pager_ota_mode = 1 49 53 } {1 {cannot set pager_ota_mode with open transaction}} 50 -do_execsql_test 2.3 { 54 +do_execsql_test 1.2.3 { 51 55 COMMIT; 52 56 } {} 53 57 54 58 55 -do_execsql_test 3.1 { 59 +do_execsql_test 1.3.1 { 56 60 PRAGMA journal_mode = wal; 57 61 CREATE TABLE t1(a, b); 58 62 INSERT INTO t1 VALUES(1, 2); 59 63 } {wal} 60 -do_test 3.2 { 64 +do_test 1.3.2 { 61 65 forcecopy test.db-wal test.db-bak 62 66 execsql { 63 67 PRAGMA journal_mode = delete; 64 68 PRAGMA pager_ota_mode = 1; 65 69 } 66 70 forcecopy test.db-bak test.db-wal 67 71 catchsql { 68 72 SELECT * FROM sqlite_master 69 73 } 70 74 } {1 {unable to open database file}} 71 75 72 -do_test 4.1 { 76 +do_test 1.4.1 { 73 77 db close 74 78 forcedelete test.db-wal test.db-oal 75 79 sqlite3 db test.db 76 80 execsql { 77 81 PRAGMA journal_mode = wal; 78 82 PRAGMA pager_ota_mode = 1; 79 83 } 80 84 catchsql { 81 85 SELECT * FROM sqlite_master; 82 86 } 83 87 } {1 {unable to open database file}} 84 88 85 -do_test 5.1 { 89 +do_test 1.5.1 { 86 90 forcedelete test.db-oal 87 91 reset_db 88 92 execsql { 89 93 PRAGMA journal_mode = delete; 90 94 CREATE TABLE t1(a, b); 91 95 INSERT INTO t1 VALUES(1, 2); 92 96 } ................................................................................ 96 100 } 97 101 db close 98 102 sqlite3 db test.db 99 103 execsql { 100 104 SELECT * FROM t1; 101 105 } 102 106 } {1 2} 103 -do_execsql_test 5.2 { 107 +do_execsql_test 1.5.2 { 104 108 PRAGMA pager_ota_mode = 1; 105 109 SELECT * FROM t1; 106 110 INSERT INTO t1 VALUES(5, 6); 107 111 } {1 2 3 4} 108 112 do_test 5.3 { 109 113 db close 110 114 sqlite3 db test.db 111 115 execsql { 112 116 INSERT INTO t1 VALUES(7, 8); 113 117 SELECT * FROM t1; 114 118 } 115 119 } {1 2 7 8} 116 -do_catchsql_test 5.4 { 120 +do_catchsql_test 1.5.4 { 117 121 PRAGMA pager_ota_mode = 1; 118 122 SELECT * FROM t1; 119 123 } {1 {database is locked}} 120 124 125 +#------------------------------------------------------------------------- 126 +# These tests - ota4-2.* - aim to verify some properties of the ota_mode 127 +# pragma. 128 +# 129 +# 1. Check that UNIQUE constraints are not tested in ota_mode. 130 +# 2. Except for (real) PRIMARY KEY constraints. 131 +# 3. Check that all non-temporary triggers are ignored. 132 +# 133 +reset_db 134 +do_execsql_test 2.1.1 { 135 + CREATE TABLE t1(a INTEGER PRIMARY KEY, b, c); 136 + CREATE UNIQUE INDEX i1 ON t1(b); 137 + INSERT INTO t1 VALUES(1, 2, 3); 138 + INSERT INTO t1 VALUES(2, 4, 6); 139 +} 140 + 141 +do_execsql_test 2.1.2 { 142 + PRAGMA ota_mode = 1; 143 + INSERT INTO t1 VALUES(3, 2, 6); 144 + UPDATE t1 SET b=2 WHERE a=2; 145 + SELECT * FROM t1; 146 +} { 147 + 1 2 3 148 + 2 2 6 149 + 3 2 6 150 +} 151 + 152 +reset_db 153 +do_execsql_test 2.2.1 { 154 + CREATE TABLE t1(a INTEGER PRIMARY KEY, b, c); 155 + CREATE TABLE t2(x, y, z, PRIMARY KEY(y, z)) WITHOUT ROWID; 156 + 157 + INSERT INTO t1 VALUES(1, 2, 3); 158 + INSERT INTO t2 VALUES(4, 5, 6); 159 + PRAGMA ota_mode = 1; 160 +} 161 +do_catchsql_test 2.2.2 { 162 + INSERT INTO t1 VALUES(1, 'two', 'three'); 163 +} {1 {UNIQUE constraint failed: t1.a}} 164 +do_catchsql_test 2.2.3 { 165 + INSERT INTO t2 VALUES('four', 5, 6); 166 +} {1 {UNIQUE constraint failed: t2.y, t2.z}} 167 + 168 +reset_db 169 +do_execsql_test 2.3.1 { 170 + CREATE TABLE t1(a, b, c); 171 + CREATE TABLE log(x); 172 + INSERT INTO t1 VALUES(1, 2, 3); 173 + 174 + CREATE TRIGGER tr1 BEFORE INSERT ON t1 BEGIN 175 + INSERT INTO log VALUES('permanent'); 176 + END; 177 + CREATE TRIGGER tr2 AFTER INSERT ON t1 BEGIN 178 + INSERT INTO log VALUES('permanent'); 179 + END; 180 + CREATE TRIGGER tr3 BEFORE DELETE ON t1 BEGIN 181 + INSERT INTO log VALUES('permanent'); 182 + END; 183 + CREATE TRIGGER tr4 AFTER DELETE ON t1 BEGIN 184 + INSERT INTO log VALUES('permanent'); 185 + END; 186 + CREATE TRIGGER tr5 BEFORE UPDATE ON t1 BEGIN 187 + INSERT INTO log VALUES('permanent'); 188 + END; 189 + CREATE TRIGGER tr6 AFTER UPDATE ON t1 BEGIN 190 + INSERT INTO log VALUES('permanent'); 191 + END; 192 + 193 + CREATE TEMP TRIGGER ttr1 BEFORE INSERT ON t1 BEGIN 194 + INSERT INTO log VALUES('temp'); 195 + END; 196 + CREATE TEMP TRIGGER ttr2 AFTER INSERT ON t1 BEGIN 197 + INSERT INTO log VALUES('temp'); 198 + END; 199 + CREATE TEMP TRIGGER ttr3 BEFORE DELETE ON t1 BEGIN 200 + INSERT INTO log VALUES('temp'); 201 + END; 202 + CREATE TEMP TRIGGER ttr4 AFTER DELETE ON t1 BEGIN 203 + INSERT INTO log VALUES('temp'); 204 + END; 205 + CREATE TEMP TRIGGER ttr5 BEFORE UPDATE ON t1 BEGIN 206 + INSERT INTO log VALUES('temp'); 207 + END; 208 + CREATE TEMP TRIGGER ttr6 AFTER UPDATE ON t1 BEGIN 209 + INSERT INTO log VALUES('temp'); 210 + END; 211 +} 212 +do_execsql_test 2.3.2 { 213 + INSERT INTO t1 VALUES(4, 5, 6); 214 + DELETE FROM t1 WHERE a = 4; 215 + UPDATE t1 SET c = 6; 216 + SELECT x FROM log; 217 +} { 218 + temp permanent temp permanent temp permanent 219 + temp permanent temp permanent temp permanent 220 +} 221 +do_execsql_test 2.3.3 { 222 + DELETE FROM log; 223 + PRAGMA ota_mode = 1; 224 + INSERT INTO t1 VALUES(4, 5, 6); 225 + DELETE FROM t1 WHERE a = 4; 226 + UPDATE t1 SET c = 6; 227 + SELECT x FROM log; 228 +} {temp temp temp temp temp temp} 229 + 121 230 finish_test 231 +
Changes to src/insert.c.
1560 1560 assert( pTab->pSelect==0 ); /* This table is not a VIEW */ 1561 1561 for(i=0, pIdx=pTab->pIndex; pIdx; pIdx=pIdx->pNext, i++){ 1562 1562 if( aRegIdx[i]==0 ) continue; 1563 1563 1564 1564 /* If the "ota_mode" flag is set, ignore all indexes except the PK 1565 1565 ** index of WITHOUT ROWID tables. */ 1566 1566 if( (pParse->db->flags & SQLITE_OtaMode) 1567 - && (pTab->iPKey>=0 || pIdx->idxType!=SQLITE_IDXTYPE_PRIMARYKEY) 1567 + && (HasRowid(pTab) || pIdx->idxType!=SQLITE_IDXTYPE_PRIMARYKEY) 1568 1568 ){ 1569 1569 continue; 1570 1570 } 1571 1571 1572 1572 bAffinityDone = 1; 1573 1573 if( pIdx->pPartIdxWhere ){ 1574 1574 sqlite3VdbeAddOp2(v, OP_IsNull, aRegIdx[i], sqlite3VdbeCurrentAddr(v)+2);
Changes to src/trigger.c.
39 39 ** triggers on pTab in the TEMP schema. This routine prepends all 40 40 ** TEMP triggers on pTab to the beginning of the pTab->pTrigger list 41 41 ** and returns the combined list. 42 42 ** 43 43 ** To state it another way: This routine returns a list of all triggers 44 44 ** that fire off of pTab. The list will include any TEMP triggers on 45 45 ** pTab as well as the triggers lised in pTab->pTrigger. 46 +** 47 +** If the SQLITE_OtaMode flag is set, do not include any non-temporary 48 +** triggers in the returned list. 46 49 */ 47 50 Trigger *sqlite3TriggerList(Parse *pParse, Table *pTab){ 48 51 Schema * const pTmpSchema = pParse->db->aDb[1].pSchema; 49 52 Trigger *pList = 0; /* List of triggers to return */ 53 + int bOta = !!(pParse->db->flags & SQLITE_OtaMode); 50 54 51 55 if( pParse->disableTriggers ){ 52 56 return 0; 53 57 } 54 58 55 59 if( pTmpSchema!=pTab->pSchema ){ 56 60 HashElem *p; 57 61 assert( sqlite3SchemaMutexHeld(pParse->db, 0, pTmpSchema) ); 58 62 for(p=sqliteHashFirst(&pTmpSchema->trigHash); p; p=sqliteHashNext(p)){ 59 63 Trigger *pTrig = (Trigger *)sqliteHashData(p); 60 64 if( pTrig->pTabSchema==pTab->pSchema 61 65 && 0==sqlite3StrICmp(pTrig->table, pTab->zName) 62 66 ){ 63 - pTrig->pNext = (pList ? pList : pTab->pTrigger); 67 + pTrig->pNext = ((pList || bOta) ? pList : pTab->pTrigger); 64 68 pList = pTrig; 65 69 } 66 70 } 67 71 } 68 72 69 - return (pList ? pList : pTab->pTrigger); 73 + return ((pList || bOta) ? pList : pTab->pTrigger); 70 74 } 71 75 72 76 /* 73 77 ** This is called by the parser when it sees a CREATE TRIGGER statement 74 78 ** up to the point of the BEGIN before the trigger actions. A Trigger 75 79 ** structure is generated based on the information available and stored 76 80 ** in pParse->pNewTrigger. After the trigger actions have been parsed, the