/ Check-in [39df35c4]
Login

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: 39df35c4ac65ffba76ba2c6f6727cf5e843e7517
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
Hide Diffs Side-by-Side Diffs Ignore Whitespace Patch

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