/ 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 Unified Diffs Ignore Whitespace Patch

Changes to ext/ota/ota2.test.

10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
#***********************************************************************
#

set testdir [file join [file dirname $argv0] .. .. test]
source $testdir/tester.tcl
set ::testprefix ota2

forcedelete test.db-oal 

do_execsql_test 1.0 {
  CREATE TABLE t1(a, b);
  INSERT INTO t1 VALUES(1, 2);
} {}
do_test 1.1 { glob test.db* } {test.db}








|







10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
#***********************************************************************
#

set testdir [file join [file dirname $argv0] .. .. test]
source $testdir/tester.tcl
set ::testprefix ota2

forcedelete test.db-oal test.db-bak

do_execsql_test 1.0 {
  CREATE TABLE t1(a, b);
  INSERT INTO t1 VALUES(1, 2);
} {}
do_test 1.1 { glob test.db* } {test.db}

Changes to ext/ota/ota4.test.

5
6
7
8
9
10
11
12
13
14
15
16
17
18




19
20
21
22
23
24
25
..
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
..
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120









































































































121

#
#    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 pragma.
#

set testdir [file join [file dirname $argv0] .. .. test]
source $testdir/tester.tcl
set ::testprefix ota4





# 1. Cannot set the pager_ota_mode flag on a WAL mode database.
#
# 2. Or if there is an open read transaction.
#
# 3. Cannot start a transaction with pager_ota_mode set if there
#    is a WAL file in the file-system.
# 
................................................................................
# 4. Or if the wal-mode flag is set in the database file header.
# 
# 5. Cannot open a transaction with pager_ota_mode set if the database
#    file has been modified by a rollback mode client since the *-oal
#    file was started.
#

do_execsql_test 1.1 { 
  PRAGMA journal_mode = wal;
  SELECT * FROM sqlite_master;
} {wal}
do_catchsql_test 1.2 { 
  PRAGMA pager_ota_mode = 1 
} {1 {cannot set pager_ota_mode in wal mode}}


do_execsql_test 2.1 { 
  PRAGMA journal_mode = delete;
  BEGIN;
    SELECT * FROM sqlite_master;
} {delete}
do_catchsql_test 2.2 { 
  PRAGMA pager_ota_mode = 1 
} {1 {cannot set pager_ota_mode with open transaction}}
do_execsql_test 2.3 { 
  COMMIT;
} {}


do_execsql_test 3.1 {
  PRAGMA journal_mode = wal;
  CREATE TABLE t1(a, b);
  INSERT INTO t1 VALUES(1, 2);
} {wal}
do_test 3.2 {
  forcecopy test.db-wal test.db-bak
  execsql { 
    PRAGMA journal_mode = delete;
    PRAGMA pager_ota_mode = 1;
  }
  forcecopy test.db-bak test.db-wal
  catchsql {
    SELECT * FROM sqlite_master
  }
} {1 {unable to open database file}}

do_test 4.1 {
  db close
  forcedelete test.db-wal test.db-oal
  sqlite3 db test.db
  execsql { 
    PRAGMA journal_mode = wal;
    PRAGMA pager_ota_mode = 1;
  }
  catchsql {
    SELECT * FROM sqlite_master;
  }
} {1 {unable to open database file}}

do_test 5.1 {
  forcedelete test.db-oal
  reset_db
  execsql {
    PRAGMA journal_mode = delete;
    CREATE TABLE t1(a, b);
    INSERT INTO t1 VALUES(1, 2);
  }
................................................................................
  }
  db close
  sqlite3 db test.db
  execsql {
    SELECT * FROM t1;
  }
} {1 2}
do_execsql_test 5.2 {
  PRAGMA pager_ota_mode = 1;
  SELECT * FROM t1;
  INSERT INTO t1 VALUES(5, 6);
} {1 2 3 4}
do_test 5.3 {
  db close
  sqlite3 db test.db
  execsql {
    INSERT INTO t1 VALUES(7, 8);
    SELECT * FROM t1;
  }
} {1 2 7 8}
do_catchsql_test 5.4 {
  PRAGMA pager_ota_mode = 1;
  SELECT * FROM t1;
} {1 {database is locked}}










































































































finish_test








|






>
>
>
>







 







|



|




|




|


|




|




|











|












|







 







|












|




>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>

>
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
..
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
...
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
#
#    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 ota4

#-------------------------------------------------------------------------
# The following tests aim to verify some properties of the pager_ota_mode
# pragma:
#
# 1. Cannot set the pager_ota_mode flag on a WAL mode database.
#
# 2. Or if there is an open read transaction.
#
# 3. Cannot start a transaction with pager_ota_mode set if there
#    is a WAL file in the file-system.
# 
................................................................................
# 4. Or if the wal-mode flag is set in the database file header.
# 
# 5. Cannot open a transaction with pager_ota_mode set if the database
#    file has been modified by a rollback mode client since the *-oal
#    file was started.
#

do_execsql_test 1.1.1 { 
  PRAGMA journal_mode = wal;
  SELECT * FROM sqlite_master;
} {wal}
do_catchsql_test 1.1.2 { 
  PRAGMA pager_ota_mode = 1 
} {1 {cannot set pager_ota_mode in wal mode}}


do_execsql_test 1.2.1 { 
  PRAGMA journal_mode = delete;
  BEGIN;
    SELECT * FROM sqlite_master;
} {delete}
do_catchsql_test 1.2.2 { 
  PRAGMA pager_ota_mode = 1 
} {1 {cannot set pager_ota_mode with open transaction}}
do_execsql_test 1.2.3 { 
  COMMIT;
} {}


do_execsql_test 1.3.1 {
  PRAGMA journal_mode = wal;
  CREATE TABLE t1(a, b);
  INSERT INTO t1 VALUES(1, 2);
} {wal}
do_test 1.3.2 {
  forcecopy test.db-wal test.db-bak
  execsql { 
    PRAGMA journal_mode = delete;
    PRAGMA pager_ota_mode = 1;
  }
  forcecopy test.db-bak test.db-wal
  catchsql {
    SELECT * FROM sqlite_master
  }
} {1 {unable to open database file}}

do_test 1.4.1 {
  db close
  forcedelete test.db-wal test.db-oal
  sqlite3 db test.db
  execsql { 
    PRAGMA journal_mode = wal;
    PRAGMA pager_ota_mode = 1;
  }
  catchsql {
    SELECT * FROM sqlite_master;
  }
} {1 {unable to open database file}}

do_test 1.5.1 {
  forcedelete test.db-oal
  reset_db
  execsql {
    PRAGMA journal_mode = delete;
    CREATE TABLE t1(a, b);
    INSERT INTO t1 VALUES(1, 2);
  }
................................................................................
  }
  db close
  sqlite3 db test.db
  execsql {
    SELECT * FROM t1;
  }
} {1 2}
do_execsql_test 1.5.2 {
  PRAGMA pager_ota_mode = 1;
  SELECT * FROM t1;
  INSERT INTO t1 VALUES(5, 6);
} {1 2 3 4}
do_test 5.3 {
  db close
  sqlite3 db test.db
  execsql {
    INSERT INTO t1 VALUES(7, 8);
    SELECT * FROM t1;
  }
} {1 2 7 8}
do_catchsql_test 1.5.4 {
  PRAGMA pager_ota_mode = 1;
  SELECT * FROM t1;
} {1 {database is locked}}

#-------------------------------------------------------------------------
# These tests - ota4-2.* - aim to verify some properties of the ota_mode
# pragma.
#
#   1. Check that UNIQUE constraints are not tested in ota_mode.
#   2. Except for (real) PRIMARY KEY constraints.
#   3. Check that all non-temporary triggers are ignored.
#
reset_db
do_execsql_test 2.1.1 {
  CREATE TABLE t1(a INTEGER PRIMARY KEY, b, c);
  CREATE UNIQUE INDEX i1 ON t1(b);
  INSERT INTO t1 VALUES(1, 2, 3);
  INSERT INTO t1 VALUES(2, 4, 6);
}

do_execsql_test 2.1.2 {
  PRAGMA ota_mode = 1;
  INSERT INTO t1 VALUES(3, 2, 6);
  UPDATE t1 SET b=2 WHERE a=2;
  SELECT * FROM t1;
} {
  1 2 3
  2 2 6
  3 2 6
}

reset_db
do_execsql_test 2.2.1 {
  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 t2 VALUES(4, 5, 6);
  PRAGMA ota_mode = 1;
}
do_catchsql_test 2.2.2 {
  INSERT INTO t1 VALUES(1, 'two', 'three');
} {1 {UNIQUE constraint failed: t1.a}}
do_catchsql_test 2.2.3 {
  INSERT INTO t2 VALUES('four', 5, 6);
} {1 {UNIQUE constraint failed: t2.y, t2.z}}

reset_db
do_execsql_test 2.3.1 {
  CREATE TABLE t1(a, b, c);
  CREATE TABLE log(x);
  INSERT INTO t1 VALUES(1, 2, 3);

  CREATE TRIGGER tr1 BEFORE INSERT ON t1 BEGIN
    INSERT INTO log VALUES('permanent');
  END;
  CREATE TRIGGER tr2 AFTER INSERT ON t1 BEGIN
    INSERT INTO log VALUES('permanent');
  END;
  CREATE TRIGGER tr3 BEFORE DELETE ON t1 BEGIN
    INSERT INTO log VALUES('permanent');
  END;
  CREATE TRIGGER tr4 AFTER DELETE ON t1 BEGIN
    INSERT INTO log VALUES('permanent');
  END;
  CREATE TRIGGER tr5 BEFORE UPDATE ON t1 BEGIN
    INSERT INTO log VALUES('permanent');
  END;
  CREATE TRIGGER tr6 AFTER UPDATE ON t1 BEGIN
    INSERT INTO log VALUES('permanent');
  END;

  CREATE TEMP TRIGGER ttr1 BEFORE INSERT ON t1 BEGIN
    INSERT INTO log VALUES('temp');
  END;
  CREATE TEMP TRIGGER ttr2 AFTER INSERT ON t1 BEGIN
    INSERT INTO log VALUES('temp');
  END;
  CREATE TEMP TRIGGER ttr3 BEFORE DELETE ON t1 BEGIN
    INSERT INTO log VALUES('temp');
  END;
  CREATE TEMP TRIGGER ttr4 AFTER DELETE ON t1 BEGIN
    INSERT INTO log VALUES('temp');
  END;
  CREATE TEMP TRIGGER ttr5 BEFORE UPDATE ON t1 BEGIN
    INSERT INTO log VALUES('temp');
  END;
  CREATE TEMP TRIGGER ttr6 AFTER UPDATE ON t1 BEGIN
    INSERT INTO log VALUES('temp');
  END;
}
do_execsql_test 2.3.2 {
  INSERT INTO t1 VALUES(4, 5, 6);
  DELETE FROM t1 WHERE a = 4;
  UPDATE t1 SET c = 6;
  SELECT x FROM log;
} {
  temp permanent temp permanent temp permanent 
  temp permanent temp permanent temp permanent
}
do_execsql_test 2.3.3 {
  DELETE FROM log;
  PRAGMA ota_mode = 1;
  INSERT INTO t1 VALUES(4, 5, 6);
  DELETE FROM t1 WHERE a = 4;
  UPDATE t1 SET c = 6;
  SELECT x FROM log;
} {temp temp temp temp temp temp}

finish_test

Changes to src/insert.c.

1560
1561
1562
1563
1564
1565
1566
1567
1568
1569
1570
1571
1572
1573
1574
  assert( pTab->pSelect==0 );  /* This table is not a VIEW */
  for(i=0, pIdx=pTab->pIndex; pIdx; pIdx=pIdx->pNext, i++){
    if( aRegIdx[i]==0 ) continue;

    /* If the "ota_mode" flag is set, ignore all indexes except the PK 
    ** index of WITHOUT ROWID tables.  */
    if( (pParse->db->flags & SQLITE_OtaMode) 
     && (pTab->iPKey>=0 || pIdx->idxType!=SQLITE_IDXTYPE_PRIMARYKEY) 
    ){
      continue;
    }

    bAffinityDone = 1;
    if( pIdx->pPartIdxWhere ){
      sqlite3VdbeAddOp2(v, OP_IsNull, aRegIdx[i], sqlite3VdbeCurrentAddr(v)+2);







|







1560
1561
1562
1563
1564
1565
1566
1567
1568
1569
1570
1571
1572
1573
1574
  assert( pTab->pSelect==0 );  /* This table is not a VIEW */
  for(i=0, pIdx=pTab->pIndex; pIdx; pIdx=pIdx->pNext, i++){
    if( aRegIdx[i]==0 ) continue;

    /* If the "ota_mode" flag is set, ignore all indexes except the PK 
    ** index of WITHOUT ROWID tables.  */
    if( (pParse->db->flags & SQLITE_OtaMode) 
     && (HasRowid(pTab) || pIdx->idxType!=SQLITE_IDXTYPE_PRIMARYKEY) 
    ){
      continue;
    }

    bAffinityDone = 1;
    if( pIdx->pPartIdxWhere ){
      sqlite3VdbeAddOp2(v, OP_IsNull, aRegIdx[i], sqlite3VdbeCurrentAddr(v)+2);

Changes to src/trigger.c.

39
40
41
42
43
44
45



46
47
48
49

50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
** triggers on pTab in the TEMP schema.  This routine prepends all
** TEMP triggers on pTab to the beginning of the pTab->pTrigger list
** and returns the combined list.
**
** To state it another way:  This routine returns a list of all triggers
** that fire off of pTab.  The list will include any TEMP triggers on
** pTab as well as the triggers lised in pTab->pTrigger.



*/
Trigger *sqlite3TriggerList(Parse *pParse, Table *pTab){
  Schema * const pTmpSchema = pParse->db->aDb[1].pSchema;
  Trigger *pList = 0;                  /* List of triggers to return */


  if( pParse->disableTriggers ){
    return 0;
  }

  if( pTmpSchema!=pTab->pSchema ){
    HashElem *p;
    assert( sqlite3SchemaMutexHeld(pParse->db, 0, pTmpSchema) );
    for(p=sqliteHashFirst(&pTmpSchema->trigHash); p; p=sqliteHashNext(p)){
      Trigger *pTrig = (Trigger *)sqliteHashData(p);
      if( pTrig->pTabSchema==pTab->pSchema
       && 0==sqlite3StrICmp(pTrig->table, pTab->zName) 
      ){
        pTrig->pNext = (pList ? pList : pTab->pTrigger);
        pList = pTrig;
      }
    }
  }

  return (pList ? pList : pTab->pTrigger);
}

/*
** This is called by the parser when it sees a CREATE TRIGGER statement
** up to the point of the BEGIN before the trigger actions.  A Trigger
** structure is generated based on the information available and stored
** in pParse->pNewTrigger.  After the trigger actions have been parsed, the







>
>
>




>













|





|







39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
** triggers on pTab in the TEMP schema.  This routine prepends all
** TEMP triggers on pTab to the beginning of the pTab->pTrigger list
** and returns the combined list.
**
** To state it another way:  This routine returns a list of all triggers
** that fire off of pTab.  The list will include any TEMP triggers on
** pTab as well as the triggers lised in pTab->pTrigger.
**
** If the SQLITE_OtaMode flag is set, do not include any non-temporary
** triggers in the returned list.
*/
Trigger *sqlite3TriggerList(Parse *pParse, Table *pTab){
  Schema * const pTmpSchema = pParse->db->aDb[1].pSchema;
  Trigger *pList = 0;                  /* List of triggers to return */
  int bOta = !!(pParse->db->flags & SQLITE_OtaMode);

  if( pParse->disableTriggers ){
    return 0;
  }

  if( pTmpSchema!=pTab->pSchema ){
    HashElem *p;
    assert( sqlite3SchemaMutexHeld(pParse->db, 0, pTmpSchema) );
    for(p=sqliteHashFirst(&pTmpSchema->trigHash); p; p=sqliteHashNext(p)){
      Trigger *pTrig = (Trigger *)sqliteHashData(p);
      if( pTrig->pTabSchema==pTab->pSchema
       && 0==sqlite3StrICmp(pTrig->table, pTab->zName) 
      ){
        pTrig->pNext = ((pList || bOta) ? pList : pTab->pTrigger);
        pList = pTrig;
      }
    }
  }

  return ((pList || bOta) ? pList : pTab->pTrigger);
}

/*
** This is called by the parser when it sees a CREATE TRIGGER statement
** up to the point of the BEGIN before the trigger actions.  A Trigger
** structure is generated based on the information available and stored
** in pParse->pNewTrigger.  After the trigger actions have been parsed, the