/ Check-in [4cef609d]
Login
SQLite training in Houston TX on 2019-11-05 (details)
Part of the 2019 Tcl Conference

Many hyperlinks are disabled.
Use anonymous login to enable hyperlinks.

Overview
Comment:Add test cases to improve coverage of VDBE branches. Still some to go.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA3-256: 4cef609d61de272cfdc2b39e1d49c7cd56ec834086bd63095116ff98f4d402bd
User & Date: dan 2019-04-04 17:58:34
Context
2019-04-04
18:20
Minor fixes to requirements marks. check-in: 02ebc60b user: drh tags: trunk
17:58
Add test cases to improve coverage of VDBE branches. Still some to go. check-in: 4cef609d user: dan tags: trunk
17:39
Correctly initialize the loadable extension thunk. check-in: 4fca46de user: drh tags: trunk
Changes
Hide Diffs Unified Diffs Ignore Whitespace Patch

Changes to test/autoinc.test.

12
13
14
15
16
17
18

19
20
21
22
23
24
25
...
851
852
853
854
855
856
857
858























859
# focus of this script is testing the AUTOINCREMENT features.
#
# $Id: autoinc.test,v 1.14 2009/06/23 20:28:54 drh Exp $
#

set testdir [file dirname $argv0]
source $testdir/tester.tcl


# If the library is not compiled with autoincrement support then
# skip all tests in this file.
#
ifcapable {!autoinc} {
  finish_test
  return
................................................................................
  set res [catch {db eval {
    INSERT INTO t1(b) VALUES('two'),('three'),('four');
    INSERT INTO t1(b) VALUES('five');
    PRAGMA integrity_check;
  }} msg]
  lappend res $msg
} {0 ok}
























finish_test







>







 








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

12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
...
852
853
854
855
856
857
858
859
860
861
862
863
864
865
866
867
868
869
870
871
872
873
874
875
876
877
878
879
880
881
882
883
# focus of this script is testing the AUTOINCREMENT features.
#
# $Id: autoinc.test,v 1.14 2009/06/23 20:28:54 drh Exp $
#

set testdir [file dirname $argv0]
source $testdir/tester.tcl
set testprefix autoinc

# If the library is not compiled with autoincrement support then
# skip all tests in this file.
#
ifcapable {!autoinc} {
  finish_test
  return
................................................................................
  set res [catch {db eval {
    INSERT INTO t1(b) VALUES('two'),('three'),('four');
    INSERT INTO t1(b) VALUES('five');
    PRAGMA integrity_check;
  }} msg]
  lappend res $msg
} {0 ok}

#--------------------------------------------------------------------------
reset_db
do_execsql_test 13.0 {
  CREATE TABLE t1(i INTEGER PRIMARY KEY AUTOINCREMENT, j);
  CREATE TABLE t2(i INTEGER PRIMARY KEY AUTOINCREMENT, j);
  CREATE TABLE t3(i INTEGER PRIMARY KEY AUTOINCREMENT, j);

  INSERT INTO t1 VALUES(NULL, 1);
  INSERT INTO t2 VALUES(NULL, 2);
  INSERT INTO t3 VALUES(NULL, 3);

  SELECT name FROM sqlite_sequence;
} {t1 t2 t3}

do_execsql_test 13.1 {
  UPDATE sqlite_sequence SET name=NULL WHERE name='t2';
  INSERT INTO t3 VALUES(NULL, 4);
  DELETE FROM t3;
  INSERT INTO t3 VALUES(NULL, 5);
  SELECT * FROM t3;
} {3 5}


finish_test

Changes to test/delete4.test.

178
179
180
181
182
183
184
185





186








































































187
do_execsql_test 6.1 {
  DROP TABLE IF EXISTS t2;
  CREATE TABLE t2(x INT);
  INSERT INTO t2(x) VALUES(1),(2),(3),(4),(5);
  DELETE FROM t2 WHERE EXISTS(SELECT 1 FROM t2 AS v WHERE v.x=t2.x+1);
  SELECT x FROM t2;
} {5}















































































finish_test








>
>
>
>
>
|
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>

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
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
do_execsql_test 6.1 {
  DROP TABLE IF EXISTS t2;
  CREATE TABLE t2(x INT);
  INSERT INTO t2(x) VALUES(1),(2),(3),(4),(5);
  DELETE FROM t2 WHERE EXISTS(SELECT 1 FROM t2 AS v WHERE v.x=t2.x+1);
  SELECT x FROM t2;
} {5}

#-------------------------------------------------------------------------
# Test the effect of failing to find a table row based on an index key
# within a DELETE. Either because the db is corrupt, or a trigger on another
# row already deleted the entry, or because a BEFORE trigger on the current
# row has already deleted it.
#
do_execsql_test 7.1.0 {
  CREATE TABLE t3(id INT PRIMARY KEY, a, b) WITHOUT ROWID;
  CREATE INDEX t3a ON t3(a);
  CREATE INDEX t3b ON t3(b);

  INSERT INTO t3 VALUES(1, 1, 1);
  INSERT INTO t3 VALUES(2, 2, 2);
  INSERT INTO t3 VALUES(3, 3, 3);
  INSERT INTO t3 VALUES(4, 4, 1);
}
do_execsql_test 7.1.1 {
  DELETE FROM t3 WHERE a=4 OR b=1;
}
do_execsql_test 7.1.2 {
  SELECT * FROM t3;
} { 2 2 2   3 3 3 }

do_execsql_test 7.2.0 {
  CREATE TABLE t4(a PRIMARY KEY, b) WITHOUT ROWID;
  CREATE INDEX t4i ON t4(b);
  INSERT INTO t4 VALUES(1, 'hello');
  INSERT INTO t4 VALUES(2, 'world');

  CREATE TABLE t5(a PRIMARY KEY, b) WITHOUT ROWID;
  CREATE INDEX t5i ON t5(b);
  INSERT INTO t5 VALUES(1, 'hello');
  INSERT INTO t5 VALUES(3, 'world');

  PRAGMA writable_schema = 1;
  UPDATE sqlite_master SET rootpage = (
    SELECT rootpage FROM sqlite_master WHERE name = 't5'
  ) WHERE name = 't4';
}

db close
sqlite3 db test.db
do_execsql_test 7.2.1 {
  DELETE FROM t4 WHERE b='world'
}
reset_db

do_execsql_test 7.3.0 {
  CREATE TABLE t3(id INT PRIMARY KEY, a, b) WITHOUT ROWID;
  INSERT INTO t3 VALUES(1, 2, 3);
  INSERT INTO t3 VALUES(4, 5, 6);
  INSERT INTO t3 VALUES(7, 8, 9);
  CREATE TRIGGER t3t BEFORE DELETE ON t3 BEGIN
    DELETE FROM t3 WHERE id=old.id+3;
  END;
}

do_execsql_test 7.3.1 {
  DELETE FROM t3 WHERE a IN(2, 5, 8);
  SELECT * FROM t3;
} {}

do_execsql_test 7.3.2 {
  DROP TRIGGER t3t;
  INSERT INTO t3 VALUES(1, 2, 3);
  INSERT INTO t3 VALUES(4, 5, 6);
  INSERT INTO t3 VALUES(7, 8, 9);
  CREATE TRIGGER t3t BEFORE DELETE ON t3 BEGIN
    DELETE FROM t3 WHERE id=old.id;
  END;
}

do_execsql_test 7.3.3 {
  DELETE FROM t3 WHERE a IN(2, 5, 8);
  SELECT * FROM t3;
} {}


finish_test

Changes to test/insert4.test.

594
595
596
597
598
599
600


601


















602
do_test 10.3 {
  execsql { PRAGMA integrity_check }
  set sqlite3_xferopt_count 0
  execsql { INSERT INTO x     SELECT * FROM t8 }
  set sqlite3_xferopt_count
} {1}






















finish_test







>
>
|
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>

594
595
596
597
598
599
600
601
602
603
604
605
606
607
608
609
610
611
612
613
614
615
616
617
618
619
620
621
622
do_test 10.3 {
  execsql { PRAGMA integrity_check }
  set sqlite3_xferopt_count 0
  execsql { INSERT INTO x     SELECT * FROM t8 }
  set sqlite3_xferopt_count
} {1}

#-------------------------------------------------------------------------
# xfer transfer between tables where the source has an empty partial index.
#
do_execsql_test 11.0 {
  CREATE TABLE t9(a, b, c);
  CREATE INDEX t9a ON t9(a);
  CREATE INDEX t9b ON t9(b) WHERE c=0;

  INSERT INTO t9 VALUES(1, 1, 1);
  INSERT INTO t9 VALUES(2, 2, 2);
  INSERT INTO t9 VALUES(3, 3, 3);

  CREATE TABLE t10(a, b, c);
  CREATE INDEX t10a ON t10(a);
  CREATE INDEX t10b ON t10(b) WHERE c=0;

  INSERT INTO t10 SELECT * FROM t9;
  SELECT * FROM t10;
  PRAGMA integrity_check;
} {1 1 1  2 2 2  3 3 3  ok}

finish_test

Changes to test/pragma.test.

247
248
249
250
251
252
253

























254
255
256
257
258
259
260
} {0}
do_test pragma-1.14.4 {
  execsql {
    PRAGMA synchronous=10;
    PRAGMA synchronous;
  }
} {2}

























} ;# ifcapable pager_pragmas

# Test turning "flag" pragmas on and off.
#
ifcapable debug {
  # Pragma "vdbe_listing" is only available if compiled with SQLITE_DEBUG
  #







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







247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
} {0}
do_test pragma-1.14.4 {
  execsql {
    PRAGMA synchronous=10;
    PRAGMA synchronous;
  }
} {2}

do_execsql_test 1.15.1 {
  PRAGMA default_cache_size = 0;
}
do_execsql_test 1.15.2 {
  PRAGMA default_cache_size;
} $DFLT_CACHE_SZ
do_execsql_test 1.15.3 {
  PRAGMA default_cache_size = -500;
}
do_execsql_test 1.15.4 {
  PRAGMA default_cache_size;
} 500
do_execsql_test 1.15.3 {
  PRAGMA default_cache_size = 500;
}
do_execsql_test 1.15.4 {
  PRAGMA default_cache_size;
} 500
db close
hexio_write test.db 48 FFFFFF00
sqlite3 db test.db
do_execsql_test 1.15.4 {
  PRAGMA default_cache_size;
} 256
} ;# ifcapable pager_pragmas

# Test turning "flag" pragmas on and off.
#
ifcapable debug {
  # Pragma "vdbe_listing" is only available if compiled with SQLITE_DEBUG
  #

Changes to test/triggerC.test.

1052
1053
1054
1055
1056
1057
1058














1059
1060
1061
} {1 {1st ORDER BY term does not match any column in the result set}}

do_catchsql_test 16.2 {
  SELECT count(*) FROM sqlite_master 
  GROUP BY raise(IGNORE) 
  HAVING raise(ABORT, 'msg');
} {1 {RAISE() may only be used within a trigger-program}}















finish_test








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



1052
1053
1054
1055
1056
1057
1058
1059
1060
1061
1062
1063
1064
1065
1066
1067
1068
1069
1070
1071
1072
1073
1074
1075
} {1 {1st ORDER BY term does not match any column in the result set}}

do_catchsql_test 16.2 {
  SELECT count(*) FROM sqlite_master 
  GROUP BY raise(IGNORE) 
  HAVING raise(ABORT, 'msg');
} {1 {RAISE() may only be used within a trigger-program}}

#-------------------------------------------------------------------------
# Datatype mismatch on IPK when there are BEFORE triggers.
#
do_execsql_test 17.0 {
  CREATE TABLE xyz(x INTEGER PRIMARY KEY, y, z);
  CREATE TRIGGER xyz_tr BEFORE INSERT ON xyz BEGIN
    SELECT new.x;
  END;
}
do_catchsql_test 17.1 {
  INSERT INTO xyz VALUES('hello', 2, 3);
} {1 {datatype mismatch}}


finish_test

Changes to test/without_rowid1.test.

350
351
352
353
354
355
356
357


358























359
  CREATE UNIQUE INDEX t2b ON t2(b);
  UPDATE t2 SET b=1 WHERE b='';
}

do_execsql_test 10.1 {
  DELETE FROM t2 WHERE b=1
}



  























finish_test








>
>
|
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>

350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
  CREATE UNIQUE INDEX t2b ON t2(b);
  UPDATE t2 SET b=1 WHERE b='';
}

do_execsql_test 10.1 {
  DELETE FROM t2 WHERE b=1
}

#-------------------------------------------------------------------------
# UNIQUE constraint violation in an UPDATE with a multi-column PK.
#
reset_db
do_execsql_test 10.0 {
  CREATE TABLE t1(a, b, c UNIQUE, PRIMARY KEY(a, b)) WITHOUT ROWID;
  INSERT INTO t1 VALUES('a', 'a', 1);
  INSERT INTO t1 VALUES('a', 'b', 2);
  INSERT INTO t1 VALUES('b', 'a', 3);
  INSERT INTO t1 VALUES('b', 'b', 4);
}

do_catchsql_test 10.1 {
  UPDATE t1 SET c=1 WHERE (a, b) = ('a', 'a');
} {0 {}}
do_catchsql_test 10.2 {
  UPDATE t1 SET c=1 WHERE (a, b) = ('a', 'b');
} {1 {UNIQUE constraint failed: t1.c}}
do_catchsql_test 10.3 {
  UPDATE t1 SET c=1 WHERE (a, b) = ('b', 'a');
} {1 {UNIQUE constraint failed: t1.c}}
do_catchsql_test 10.4 {
  UPDATE t1 SET c=1 WHERE (a, b) = ('b', 'b');
} {1 {UNIQUE constraint failed: t1.c}}

  
finish_test