/ Check-in [c33b3a61]
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:All tests pass even if OMIT_TRIGGER is defined. (CVS 2053)
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1: c33b3a613751057e8a46fdcd428b8448329d414d
User & Date: drh 2004-11-04 04:42:28
Context
2004-11-04
14:30
Support root-page allocation/deallocation in auto-vacuum databases. Still a few problems. (CVS 2054) check-in: 1da361fa user: danielk1977 tags: trunk
04:42
All tests pass even if OMIT_TRIGGER is defined. (CVS 2053) check-in: c33b3a61 user: drh tags: trunk
04:34
Fix a #ifdef in util.c. Ticket #984. (CVS 2052) check-in: da045bd1 user: drh tags: trunk
Changes
Hide Diffs Unified Diffs Ignore Whitespace Patch

Changes to src/delete.c.

8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
..
77
78
79
80
81
82
83
84
85

86


87
88
89
90
91
92
93
94
95
96
97
...
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
...
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
**    May you find forgiveness for yourself and forgive others.
**    May you share freely, never taking more than you give.
**
*************************************************************************
** This file contains C code routines that are called by the parser
** to handle DELETE FROM statements.
**
** $Id: delete.c,v 1.84 2004/10/31 02:22:49 drh Exp $
*/
#include "sqliteInt.h"

/*
** Look up every table that is named in pSrc.  If any table is not found,
** add an error message to pParse->zErrMsg and return NULL.  If all tables
** are found, return a pointer to the last table.
................................................................................
  const char *zDb;       /* Name of database holding pTab */
  int end, addr = 0;     /* A couple addresses of generated code */
  int i;                 /* Loop counter */
  WhereInfo *pWInfo;     /* Information about the WHERE clause */
  Index *pIdx;           /* For looping over indices of the table */
  int iCur;              /* VDBE Cursor number for pTab */
  sqlite3 *db;           /* Main database structure */
  int isView;            /* True if attempting to delete from a view */
  AuthContext sContext;  /* Authorization context */




  int row_triggers_exist = 0;  /* True if any triggers exist */
  int before_triggers;         /* True if there are BEFORE triggers */
  int after_triggers;          /* True if there are AFTER triggers */
  int oldIdx = -1;             /* Cursor for the OLD table of AFTER triggers */

  sContext.pParse = 0;
  if( pParse->nErr || sqlite3_malloc_failed ){
    pTabList = 0;
    goto delete_from_cleanup;
  }
  db = pParse->db;
................................................................................
      sqlite3VdbeAddOp(v, OP_Recno, iCur, 0);
      sqlite3VdbeAddOp(v, OP_RowData, iCur, 0);
      sqlite3VdbeAddOp(v, OP_PutIntKey, oldIdx, 0);
      if( !isView ){
        sqlite3VdbeAddOp(v, OP_Close, iCur, 0);
      }

      sqlite3CodeRowTrigger(pParse, TK_DELETE, 0, TK_BEFORE, pTab, -1, 
          oldIdx, (pParse->trigStack)?pParse->trigStack->orconf:OE_Default,
	  addr);
    }

    if( !isView ){
      /* Open cursors for the table we are deleting from and all its
      ** indices.  If there are row triggers, this happens inside the
................................................................................
    if( row_triggers_exist ){
      if( !isView ){
        for(i=1, pIdx=pTab->pIndex; pIdx; i++, pIdx=pIdx->pNext){
          sqlite3VdbeAddOp(v, OP_Close, iCur + i, pIdx->tnum);
        }
        sqlite3VdbeAddOp(v, OP_Close, iCur, 0);
      }
      sqlite3CodeRowTrigger(pParse, TK_DELETE, 0, TK_AFTER, pTab, -1, 
          oldIdx, (pParse->trigStack)?pParse->trigStack->orconf:OE_Default,
	  addr);
    }

    /* End of the delete loop */
    sqlite3VdbeAddOp(v, OP_Goto, 0, addr);
    sqlite3VdbeResolveLabel(v, end);







|







 







<

>

>
>



|







 







|







 







|







8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
..
77
78
79
80
81
82
83

84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
...
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
...
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
**    May you find forgiveness for yourself and forgive others.
**    May you share freely, never taking more than you give.
**
*************************************************************************
** This file contains C code routines that are called by the parser
** to handle DELETE FROM statements.
**
** $Id: delete.c,v 1.85 2004/11/04 04:42:28 drh Exp $
*/
#include "sqliteInt.h"

/*
** Look up every table that is named in pSrc.  If any table is not found,
** add an error message to pParse->zErrMsg and return NULL.  If all tables
** are found, return a pointer to the last table.
................................................................................
  const char *zDb;       /* Name of database holding pTab */
  int end, addr = 0;     /* A couple addresses of generated code */
  int i;                 /* Loop counter */
  WhereInfo *pWInfo;     /* Information about the WHERE clause */
  Index *pIdx;           /* For looping over indices of the table */
  int iCur;              /* VDBE Cursor number for pTab */
  sqlite3 *db;           /* Main database structure */

  AuthContext sContext;  /* Authorization context */
  int oldIdx = -1;       /* Cursor for the OLD table of AFTER triggers */

#ifndef SQLITE_OMIT_TRIGGER
  int isView;                  /* True if attempting to delete from a view */
  int row_triggers_exist = 0;  /* True if any triggers exist */
  int before_triggers;         /* True if there are BEFORE triggers */
  int after_triggers;          /* True if there are AFTER triggers */
#endif

  sContext.pParse = 0;
  if( pParse->nErr || sqlite3_malloc_failed ){
    pTabList = 0;
    goto delete_from_cleanup;
  }
  db = pParse->db;
................................................................................
      sqlite3VdbeAddOp(v, OP_Recno, iCur, 0);
      sqlite3VdbeAddOp(v, OP_RowData, iCur, 0);
      sqlite3VdbeAddOp(v, OP_PutIntKey, oldIdx, 0);
      if( !isView ){
        sqlite3VdbeAddOp(v, OP_Close, iCur, 0);
      }

      (void)sqlite3CodeRowTrigger(pParse, TK_DELETE, 0, TK_BEFORE, pTab, -1, 
          oldIdx, (pParse->trigStack)?pParse->trigStack->orconf:OE_Default,
	  addr);
    }

    if( !isView ){
      /* Open cursors for the table we are deleting from and all its
      ** indices.  If there are row triggers, this happens inside the
................................................................................
    if( row_triggers_exist ){
      if( !isView ){
        for(i=1, pIdx=pTab->pIndex; pIdx; i++, pIdx=pIdx->pNext){
          sqlite3VdbeAddOp(v, OP_Close, iCur + i, pIdx->tnum);
        }
        sqlite3VdbeAddOp(v, OP_Close, iCur, 0);
      }
      (void)sqlite3CodeRowTrigger(pParse, TK_DELETE, 0, TK_AFTER, pTab, -1, 
          oldIdx, (pParse->trigStack)?pParse->trigStack->orconf:OE_Default,
	  addr);
    }

    /* End of the delete loop */
    sqlite3VdbeAddOp(v, OP_Goto, 0, addr);
    sqlite3VdbeResolveLabel(v, end);

Changes to src/insert.c.

8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
...
184
185
186
187
188
189
190
191
192


193
194
195
196
197
198
199
200
201
202
203
**    May you find forgiveness for yourself and forgive others.
**    May you share freely, never taking more than you give.
**
*************************************************************************
** This file contains C code routines that are called by the parser
** to handle INSERT statements in SQLite.
**
** $Id: insert.c,v 1.120 2004/10/31 02:22:49 drh Exp $
*/
#include "sqliteInt.h"

/*
** Set P3 of the most recently inserted opcode to a column affinity
** string for index pIdx. A column affinity string has one character
** for each column in the table, according to the affinity of the column:
................................................................................
  int endOfLoop;        /* Label for the end of the insertion loop */
  int useTempTable;     /* Store SELECT results in intermediate table */
  int srcTab = 0;       /* Data comes from this temporary cursor if >=0 */
  int iSelectLoop = 0;  /* Address of code that implements the SELECT */
  int iCleanup = 0;     /* Address of the cleanup code */
  int iInsertBlock = 0; /* Address of the subroutine used to insert data */
  int iCntMem = 0;      /* Memory cell used for the row counter */
  int isView;           /* True if attempting to insert into a view */



  int row_triggers_exist = 0; /* True if there are FOR EACH ROW triggers */
  int before_triggers;        /* True if there are BEFORE triggers */
  int after_triggers;         /* True if there are AFTER triggers */
  int newIdx = -1;            /* Cursor for the NEW table */

  if( pParse->nErr || sqlite3_malloc_failed ) goto insert_cleanup;
  db = pParse->db;

  /* Locate the table into which we will be inserting new information.
  */
  assert( pTabList->nSrc==1 );







|







 







|

>
>



|







8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
...
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
**    May you find forgiveness for yourself and forgive others.
**    May you share freely, never taking more than you give.
**
*************************************************************************
** This file contains C code routines that are called by the parser
** to handle INSERT statements in SQLite.
**
** $Id: insert.c,v 1.121 2004/11/04 04:42:28 drh Exp $
*/
#include "sqliteInt.h"

/*
** Set P3 of the most recently inserted opcode to a column affinity
** string for index pIdx. A column affinity string has one character
** for each column in the table, according to the affinity of the column:
................................................................................
  int endOfLoop;        /* Label for the end of the insertion loop */
  int useTempTable;     /* Store SELECT results in intermediate table */
  int srcTab = 0;       /* Data comes from this temporary cursor if >=0 */
  int iSelectLoop = 0;  /* Address of code that implements the SELECT */
  int iCleanup = 0;     /* Address of the cleanup code */
  int iInsertBlock = 0; /* Address of the subroutine used to insert data */
  int iCntMem = 0;      /* Memory cell used for the row counter */
  int newIdx = -1;      /* Cursor for the NEW table */

#ifndef SQLITE_OMIT_TRIGGER
  int isView;                 /* True if attempting to insert into a view */
  int row_triggers_exist = 0; /* True if there are FOR EACH ROW triggers */
  int before_triggers;        /* True if there are BEFORE triggers */
  int after_triggers;         /* True if there are AFTER triggers */
#endif

  if( pParse->nErr || sqlite3_malloc_failed ) goto insert_cleanup;
  db = pParse->db;

  /* Locate the table into which we will be inserting new information.
  */
  assert( pTabList->nSrc==1 );

Changes to src/update.c.

8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
..
44
45
46
47
48
49
50
51
52
53


54
55
56

57
58
59
60
61
62
63
**    May you find forgiveness for yourself and forgive others.
**    May you share freely, never taking more than you give.
**
*************************************************************************
** This file contains C code routines that are called by the parser
** to handle UPDATE statements.
**
** $Id: update.c,v 1.91 2004/10/31 02:22:49 drh Exp $
*/
#include "sqliteInt.h"

/*
** Process an UPDATE statement.
**
**   UPDATE OR IGNORE table_wxyz SET a=b, c=d WHERE e<5 AND f NOT NULL;
................................................................................
  char *aIdxUsed = 0;    /* aIdxUsed[i]==1 if the i-th index is used */
  int *aXRef = 0;        /* aXRef[i] is the index in pChanges->a[] of the
                         ** an expression for the i-th column of the table.
                         ** aXRef[i]==-1 if the i-th column is not changed. */
  int chngRecno;         /* True if the record number is being changed */
  Expr *pRecnoExpr = 0;  /* Expression defining the new record number */
  int openAll = 0;       /* True if all indices need to be opened */
  int isView;            /* Trying to update a view */
  AuthContext sContext;  /* The authorization context */



  int before_triggers;         /* True if there are any BEFORE triggers */
  int after_triggers;          /* True if there are any AFTER triggers */
  int row_triggers_exist = 0;  /* True if any row triggers exist */


  int newIdx      = -1;  /* index of trigger "new" temp table       */
  int oldIdx      = -1;  /* index of trigger "old" temp table       */

  sContext.pParse = 0;
  if( pParse->nErr || sqlite3_malloc_failed ) goto update_cleanup;
  db = pParse->db;







|







 







<


>
>



>







8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
..
44
45
46
47
48
49
50

51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
**    May you find forgiveness for yourself and forgive others.
**    May you share freely, never taking more than you give.
**
*************************************************************************
** This file contains C code routines that are called by the parser
** to handle UPDATE statements.
**
** $Id: update.c,v 1.92 2004/11/04 04:42:28 drh Exp $
*/
#include "sqliteInt.h"

/*
** Process an UPDATE statement.
**
**   UPDATE OR IGNORE table_wxyz SET a=b, c=d WHERE e<5 AND f NOT NULL;
................................................................................
  char *aIdxUsed = 0;    /* aIdxUsed[i]==1 if the i-th index is used */
  int *aXRef = 0;        /* aXRef[i] is the index in pChanges->a[] of the
                         ** an expression for the i-th column of the table.
                         ** aXRef[i]==-1 if the i-th column is not changed. */
  int chngRecno;         /* True if the record number is being changed */
  Expr *pRecnoExpr = 0;  /* Expression defining the new record number */
  int openAll = 0;       /* True if all indices need to be opened */

  AuthContext sContext;  /* The authorization context */

#ifndef SQLITE_OMIT_TRIGGER
  int isView;                  /* Trying to update a view */
  int before_triggers;         /* True if there are any BEFORE triggers */
  int after_triggers;          /* True if there are any AFTER triggers */
  int row_triggers_exist = 0;  /* True if any row triggers exist */
#endif

  int newIdx      = -1;  /* index of trigger "new" temp table       */
  int oldIdx      = -1;  /* index of trigger "old" temp table       */

  sContext.pParse = 0;
  if( pParse->nErr || sqlite3_malloc_failed ) goto update_cleanup;
  db = pParse->db;

Changes to test/attach.test.

8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
...
450
451
452
453
454
455
456
457
458
459
460
461


462
463
464
465
466
467
468
...
475
476
477
478
479
480
481

















482
483
484
485
486
487
488
...
492
493
494
495
496
497
498

499
500
501
502
503
504
505
...
526
527
528
529
530
531
532

533
534
535
536
537
538
539
...
605
606
607
608
609
610
611

612
613
614
615
616
617
618
#    May you share freely, never taking more than you give.
#
#***********************************************************************
# This file implements regression tests for SQLite library.  The
# focus of this script is testing the ATTACH and DETACH commands
# and related functionality.
#
# $Id: attach.test,v 1.27 2004/10/31 02:22:50 drh Exp $
#

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

for {set i 2} {$i<=15} {incr i} {
  file delete -force test$i.db
................................................................................
} {9 10}
do_test attach-4.5 {
  execsql {
    INSERT INTO db2.t3 VALUES(9,10);
    SELECT * FROM db2.t3;
  }
} {1 2 9 10}
ifcapable {trigger} {
  do_test attach-4.6 {
    execsql {
      DETACH db2;
    }


    execsql {
      CREATE TABLE t4(x);
      CREATE TRIGGER t3r3 AFTER INSERT ON t3 BEGIN
        INSERT INTO t4 VALUES('db2.' || NEW.x);
      END;
      INSERT INTO t3 VALUES(6,7);
      SELECT * FROM t4;
................................................................................
        INSERT INTO t4 VALUES('main.' || NEW.a);
      END;
      INSERT INTO main.t3 VALUES(11,12);
      SELECT * FROM main.t4;
    }
  } {main.11}
}


















# This one is tricky.  On the UNION ALL select, we have to make sure
# the schema for both main and db2 is valid before starting to execute
# the first query of the UNION ALL.  If we wait to test the validity of
# the schema for main until after the first query has run, that test will
# fail and the query will abort but we will have already output some
# results.  When the query is retried, the results will be repeated.
................................................................................
    ATTACH DATABASE 'test2.db' AS db2;
    INSERT INTO db2.t3 VALUES(13,14);
    SELECT * FROM db2.t4 UNION ALL SELECT * FROM main.t4;
  }
} {db2.6 db2.13 main.11}

do_test attach-4.9 {

  execsql {
    INSERT INTO main.t3 VALUES(15,16);
    SELECT * FROM db2.t4 UNION ALL SELECT * FROM main.t4;
  }
} {db2.6 db2.13 main.11 main.15}
do_test attach-4.10 {
  execsql {
................................................................................
  execsql {
    SELECT * FROM main.v3;
  }
} {910 1112 1516}

# Tests for the sqliteFix...() routines in attach.c
#

do_test attach-5.1 {
  db close
  sqlite3 db test.db
  db2 close
  file delete -force test2.db
  sqlite3 db2 test2.db
  catchsql {
................................................................................
do_test attach-5.9 {
  catchsql {
    CREATE TRIGGER r5 AFTER INSERT ON t5 BEGIN
      DELETE FROM t1 WHERE x<(SELECT min(x) FROM temp.t6);
    END;
  } db2
} {1 {trigger r5 cannot reference objects in database temp}}


# Check to make sure we get a sensible error if unable to open
# the file that we are trying to attach.
#
do_test attach-6.1 {
  catchsql {
    ATTACH DATABASE 'no-such-file' AS nosuch;







|







 







<
<
|
|
|
>
>







 







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







 







>







 







>







 







>







8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
...
450
451
452
453
454
455
456


457
458
459
460
461
462
463
464
465
466
467
468
...
475
476
477
478
479
480
481
482
483
484
485
486
487
488
489
490
491
492
493
494
495
496
497
498
499
500
501
502
503
504
505
...
509
510
511
512
513
514
515
516
517
518
519
520
521
522
523
...
544
545
546
547
548
549
550
551
552
553
554
555
556
557
558
...
624
625
626
627
628
629
630
631
632
633
634
635
636
637
638
#    May you share freely, never taking more than you give.
#
#***********************************************************************
# This file implements regression tests for SQLite library.  The
# focus of this script is testing the ATTACH and DETACH commands
# and related functionality.
#
# $Id: attach.test,v 1.28 2004/11/04 04:42:28 drh Exp $
#

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

for {set i 2} {$i<=15} {incr i} {
  file delete -force test$i.db
................................................................................
} {9 10}
do_test attach-4.5 {
  execsql {
    INSERT INTO db2.t3 VALUES(9,10);
    SELECT * FROM db2.t3;
  }
} {1 2 9 10}


execsql {
  DETACH db2;
}
ifcapable {trigger} {
  do_test attach-4.6 {
    execsql {
      CREATE TABLE t4(x);
      CREATE TRIGGER t3r3 AFTER INSERT ON t3 BEGIN
        INSERT INTO t4 VALUES('db2.' || NEW.x);
      END;
      INSERT INTO t3 VALUES(6,7);
      SELECT * FROM t4;
................................................................................
        INSERT INTO t4 VALUES('main.' || NEW.a);
      END;
      INSERT INTO main.t3 VALUES(11,12);
      SELECT * FROM main.t4;
    }
  } {main.11}
}
ifcapable {!trigger} {
  # When we do not have trigger support, set up the table like they
  # would have been had triggers been there.  The tests that follow need
  # this setup.
  execsql {
    CREATE TABLE t4(x);
    INSERT INTO t3 VALUES(6,7);
    INSERT INTO t4 VALUES('db2.6');
    INSERT INTO t4 VALUES('db2.13');
  } db2
  execsql {
    CREATE TABLE t4(y);
    INSERT INTO main.t3 VALUES(11,12);
    INSERT INTO t4 VALUES('main.11');
  }
}


# This one is tricky.  On the UNION ALL select, we have to make sure
# the schema for both main and db2 is valid before starting to execute
# the first query of the UNION ALL.  If we wait to test the validity of
# the schema for main until after the first query has run, that test will
# fail and the query will abort but we will have already output some
# results.  When the query is retried, the results will be repeated.
................................................................................
    ATTACH DATABASE 'test2.db' AS db2;
    INSERT INTO db2.t3 VALUES(13,14);
    SELECT * FROM db2.t4 UNION ALL SELECT * FROM main.t4;
  }
} {db2.6 db2.13 main.11}

do_test attach-4.9 {
  ifcapable {!trigger} {execsql {INSERT INTO main.t4 VALUES('main.15')}}
  execsql {
    INSERT INTO main.t3 VALUES(15,16);
    SELECT * FROM db2.t4 UNION ALL SELECT * FROM main.t4;
  }
} {db2.6 db2.13 main.11 main.15}
do_test attach-4.10 {
  execsql {
................................................................................
  execsql {
    SELECT * FROM main.v3;
  }
} {910 1112 1516}

# Tests for the sqliteFix...() routines in attach.c
#
ifcapable {trigger} {
do_test attach-5.1 {
  db close
  sqlite3 db test.db
  db2 close
  file delete -force test2.db
  sqlite3 db2 test2.db
  catchsql {
................................................................................
do_test attach-5.9 {
  catchsql {
    CREATE TRIGGER r5 AFTER INSERT ON t5 BEGIN
      DELETE FROM t1 WHERE x<(SELECT min(x) FROM temp.t6);
    END;
  } db2
} {1 {trigger r5 cannot reference objects in database temp}}
} ;# endif trigger

# Check to make sure we get a sensible error if unable to open
# the file that we are trying to attach.
#
do_test attach-6.1 {
  catchsql {
    ATTACH DATABASE 'no-such-file' AS nosuch;

Changes to test/attach3.test.

8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
...
149
150
151
152
153
154
155

156
157
158
159
160
161
162
...
207
208
209
210
211
212
213

214
215
216
217
218
219
220
#    May you share freely, never taking more than you give.
#
#***********************************************************************
# This file implements regression tests for SQLite library.  The
# focus of this script is testing the ATTACH and DETACH commands
# and schema changes to attached databases.
#
# $Id: attach3.test,v 1.9 2004/07/19 00:56:24 drh Exp $
#


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

# Create tables t1 and t2 in the main database
................................................................................
} {}
do_test attach3-6.2 {
  execsql {
    SELECT * FROM aux.sqlite_master WHERE name = 'v1';
  }
} {}


# Create a trigger in the auxilary database.
do_test attach3-7.1 {
  execsql {
    CREATE TRIGGER aux.tr1 AFTER INSERT ON t3 BEGIN
      INSERT INTO t3 VALUES(new.e*2, new.f*2);
    END;
  }
................................................................................
} {1}
do_test attach3-9.2 {
  execsql {
    DROP TABLE aux.t4;
    SELECT count(*) FROM sqlite_temp_master;
  }
} {0}


# Make sure the aux.sqlite_master table is read-only
do_test attach3-10.0 {
  catchsql {
    INSERT INTO aux.sqlite_master VALUES(1, 2, 3, 4, 5);
  }
} {1 {table sqlite_master may not be modified}}







|







 







>







 







>







8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
...
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
...
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
#    May you share freely, never taking more than you give.
#
#***********************************************************************
# This file implements regression tests for SQLite library.  The
# focus of this script is testing the ATTACH and DETACH commands
# and schema changes to attached databases.
#
# $Id: attach3.test,v 1.10 2004/11/04 04:42:28 drh Exp $
#


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

# Create tables t1 and t2 in the main database
................................................................................
} {}
do_test attach3-6.2 {
  execsql {
    SELECT * FROM aux.sqlite_master WHERE name = 'v1';
  }
} {}

ifcapable {trigger} {
# Create a trigger in the auxilary database.
do_test attach3-7.1 {
  execsql {
    CREATE TRIGGER aux.tr1 AFTER INSERT ON t3 BEGIN
      INSERT INTO t3 VALUES(new.e*2, new.f*2);
    END;
  }
................................................................................
} {1}
do_test attach3-9.2 {
  execsql {
    DROP TABLE aux.t4;
    SELECT count(*) FROM sqlite_temp_master;
  }
} {0}
} ;# endif trigger

# Make sure the aux.sqlite_master table is read-only
do_test attach3-10.0 {
  catchsql {
    INSERT INTO aux.sqlite_master VALUES(1, 2, 3, 4, 5);
  }
} {1 {table sqlite_master may not be modified}}

Changes to test/collate6.test.

8
9
10
11
12
13
14
15
16
17
18








19
20
21
22
23
24
25
..
97
98
99
100
101
102
103
104
105
106
#    May you find forgiveness for yourself and forgive others.
#    May you share freely, never taking more than you give.
#
#***********************************************************************
# This file implements regression tests for SQLite library.  The
# focus of this script is collation sequences in concert with triggers.
#
# $Id: collate6.test,v 1.1 2004/06/11 10:51:41 danielk1977 Exp $

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









# Create a case-insensitive collation type NOCASE for use in testing. 
# Normally, capital letters are less than their lower-case counterparts.
db collate NOCASE nocase_collate
proc nocase_collate {a b} {
  return [string compare -nocase $a $b]
}
................................................................................
  execsql {
    DROP TABLE collate6tab;
  }
} {}


finish_test










|



>
>
>
>
>
>
>
>







 







<
<
<
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
...
105
106
107
108
109
110
111



#    May you find forgiveness for yourself and forgive others.
#    May you share freely, never taking more than you give.
#
#***********************************************************************
# This file implements regression tests for SQLite library.  The
# focus of this script is collation sequences in concert with triggers.
#
# $Id: collate6.test,v 1.2 2004/11/04 04:42:28 drh Exp $

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

# There are no tests in this file that will work without
# trigger support.
#
ifcapable {!trigger} {
  finish_test
  return
}

# Create a case-insensitive collation type NOCASE for use in testing. 
# Normally, capital letters are less than their lower-case counterparts.
db collate NOCASE nocase_collate
proc nocase_collate {a b} {
  return [string compare -nocase $a $b]
}
................................................................................
  execsql {
    DROP TABLE collate6tab;
  }
} {}


finish_test



Changes to test/delete.test.

7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
...
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
#    May you find forgiveness for yourself and forgive others.
#    May you share freely, never taking more than you give.
#
#***********************************************************************
# This file implements regression tests for SQLite library.  The
# focus of this file is testing the DELETE FROM statement.
#
# $Id: delete.test,v 1.17 2004/07/15 20:08:39 drh Exp $

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

# Try to delete from a non-existant table.
#
do_test delete-1.1 {
................................................................................
    CREATE TABLE t3(a);
    INSERT INTO t3 VALUES(1);
    INSERT INTO t3 SELECT a+1 FROM t3;
    INSERT INTO t3 SELECT a+2 FROM t3;
    SELECT * FROM t3;
  }
} {1 2 3 4}

do_test delete-7.2 {
  execsql {
    CREATE TABLE cnt(del);
    INSERT INTO cnt VALUES(0);
    CREATE TRIGGER r1 AFTER DELETE ON t3 FOR EACH ROW BEGIN
      UPDATE cnt SET del=del+1;
    END;
    DELETE FROM t3 WHERE a<2;
    SELECT * FROM t3;
  }
} {2 3 4}
do_test delete-7.3 {
  execsql {
    SELECT * FROM cnt;
  }
} {1}
do_test delete-7.4 {
  execsql {
    DELETE FROM t3;
    SELECT * FROM t3;
  }
} {}
do_test delete-7.5 {
  execsql {
    SELECT * FROM cnt;
  }
} {4}
do_test delete-7.6 {
  execsql {
    INSERT INTO t3 VALUES(1);
    INSERT INTO t3 SELECT a+1 FROM t3;
    INSERT INTO t3 SELECT a+2 FROM t3;
    CREATE TABLE t4 AS SELECT * FROM t3;
    PRAGMA count_changes=ON;
    DELETE FROM t3;
    DELETE FROM t4;
  }
} {4 4}




integrity_check delete-7.7

# Make sure error messages are consistent when attempting to delete
# from a read-only database.  Ticket #304.
#
do_test delete-8.0 {
  execsql {







|







 







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







7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
...
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
265
266
267
268
#    May you find forgiveness for yourself and forgive others.
#    May you share freely, never taking more than you give.
#
#***********************************************************************
# This file implements regression tests for SQLite library.  The
# focus of this file is testing the DELETE FROM statement.
#
# $Id: delete.test,v 1.18 2004/11/04 04:42:28 drh Exp $

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

# Try to delete from a non-existant table.
#
do_test delete-1.1 {
................................................................................
    CREATE TABLE t3(a);
    INSERT INTO t3 VALUES(1);
    INSERT INTO t3 SELECT a+1 FROM t3;
    INSERT INTO t3 SELECT a+2 FROM t3;
    SELECT * FROM t3;
  }
} {1 2 3 4}
ifcapable {trigger} {
  do_test delete-7.2 {
    execsql {
      CREATE TABLE cnt(del);
      INSERT INTO cnt VALUES(0);
      CREATE TRIGGER r1 AFTER DELETE ON t3 FOR EACH ROW BEGIN
        UPDATE cnt SET del=del+1;
      END;
      DELETE FROM t3 WHERE a<2;
      SELECT * FROM t3;
    }
  } {2 3 4}
  do_test delete-7.3 {
    execsql {
      SELECT * FROM cnt;
    }
  } {1}
  do_test delete-7.4 {
    execsql {
      DELETE FROM t3;
      SELECT * FROM t3;
    }
  } {}
  do_test delete-7.5 {
    execsql {
      SELECT * FROM cnt;
    }
  } {4}
  do_test delete-7.6 {
    execsql {
      INSERT INTO t3 VALUES(1);
      INSERT INTO t3 SELECT a+1 FROM t3;
      INSERT INTO t3 SELECT a+2 FROM t3;
      CREATE TABLE t4 AS SELECT * FROM t3;
      PRAGMA count_changes=ON;
      DELETE FROM t3;
      DELETE FROM t4;
    }
  } {4 4}
} ;# endif trigger
ifcapable {!trigger} {
  execsql {DELETE FROM t3}
}
integrity_check delete-7.7

# Make sure error messages are consistent when attempting to delete
# from a read-only database.  Ticket #304.
#
do_test delete-8.0 {
  execsql {

Changes to test/index.test.

7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
...
594
595
596
597
598
599
600

601
602
603
604
605

606
607
608
609
610
611
612
#    May you find forgiveness for yourself and forgive others.
#    May you share freely, never taking more than you give.
#
#***********************************************************************
# This file implements regression tests for SQLite library.  The
# focus of this file is testing the CREATE INDEX statement.
#
# $Id: index.test,v 1.33 2004/11/03 16:27:02 drh Exp $

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

# Create a basic index and verify it is added to sqlite_master
#
do_test index-1.1 {
................................................................................
  }
} {1 {object name reserved for internal use: sqlite_i1}}
do_test index-18.3 {
  catchsql {
    CREATE VIEW sqlite_v1 AS SELECT * FROM t7;
  }
} {1 {object name reserved for internal use: sqlite_v1}}

do_test index-18.4 {
  catchsql {
    CREATE TRIGGER sqlite_tr1 BEFORE INSERT ON t7 BEGIN SELECT 1; END;
  }
} {1 {object name reserved for internal use: sqlite_tr1}}

do_test index-18.5 {
  execsql {
    DROP TABLE t7;
  }
} {}

# These tests ensure that if multiple table definition constraints are







|







 







>
|
|
|
|
|
>







7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
...
594
595
596
597
598
599
600
601
602
603
604
605
606
607
608
609
610
611
612
613
614
#    May you find forgiveness for yourself and forgive others.
#    May you share freely, never taking more than you give.
#
#***********************************************************************
# This file implements regression tests for SQLite library.  The
# focus of this file is testing the CREATE INDEX statement.
#
# $Id: index.test,v 1.34 2004/11/04 04:42:28 drh Exp $

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

# Create a basic index and verify it is added to sqlite_master
#
do_test index-1.1 {
................................................................................
  }
} {1 {object name reserved for internal use: sqlite_i1}}
do_test index-18.3 {
  catchsql {
    CREATE VIEW sqlite_v1 AS SELECT * FROM t7;
  }
} {1 {object name reserved for internal use: sqlite_v1}}
ifcapable {trigger} {
  do_test index-18.4 {
    catchsql {
      CREATE TRIGGER sqlite_tr1 BEFORE INSERT ON t7 BEGIN SELECT 1; END;
    }
  } {1 {object name reserved for internal use: sqlite_tr1}}
}
do_test index-18.5 {
  execsql {
    DROP TABLE t7;
  }
} {}

# These tests ensure that if multiple table definition constraints are

Changes to test/interrupt.test.

7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
..
90
91
92
93
94
95
96

97

98
99
100
101
102
103
104
#    May you find forgiveness for yourself and forgive others.
#    May you share freely, never taking more than you give.
#
#***********************************************************************
# This file implements regression tests for SQLite library.  The
# focus of this script is the sqlite_interrupt() API.
#
# $Id: interrupt.test,v 1.5 2004/10/30 20:23:10 drh Exp $


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

# Compute a checksum on the entire database.
#
................................................................................
    COMMIT;
    UPDATE t1 SET b=substr(b,-5,5);
    SELECT count(*) from t1;
  }
} 64
set origsize [file size test.db]
set cksum [db eval {SELECT md5sum(a || b) FROM t1}]

interrupt_test interrupt-2.2 {VACUUM} {} 100

do_test interrupt-2.3 {
  execsql {
    SELECT md5sum(a || b) FROM t1;
  }
} $cksum
ifcapable vacuum {
  do_test interrupt-2.4 {







|







 







>
|
>







7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
..
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
#    May you find forgiveness for yourself and forgive others.
#    May you share freely, never taking more than you give.
#
#***********************************************************************
# This file implements regression tests for SQLite library.  The
# focus of this script is the sqlite_interrupt() API.
#
# $Id: interrupt.test,v 1.6 2004/11/04 04:42:28 drh Exp $


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

# Compute a checksum on the entire database.
#
................................................................................
    COMMIT;
    UPDATE t1 SET b=substr(b,-5,5);
    SELECT count(*) from t1;
  }
} 64
set origsize [file size test.db]
set cksum [db eval {SELECT md5sum(a || b) FROM t1}]
ifcapable {vacuum} {
  interrupt_test interrupt-2.2 {VACUUM} {} 100
}
do_test interrupt-2.3 {
  execsql {
    SELECT md5sum(a || b) FROM t1;
  }
} $cksum
ifcapable vacuum {
  do_test interrupt-2.4 {

Changes to test/lastinsert.test.

56
57
58
59
60
61
62








63
64
65
66
67
68
69
...
312
313
314
315
316
317
318
319
do_test lastinsert-1.4 {
    catchsql {
        create table t2 (k integer primary key, val1, val2, val3);
        create view v as select * from t1;
        select last_insert_rowid();
    }
} {0 3}









# ----------------------------------------------------------------------------
# 2.x - tests with after insert trigger

# LIRID changed properly after an insert into table containing an after trigger
do_test lastinsert-2.1 {
    catchsql {
................................................................................
do_test lastinsert-7.6 {
    catchsql {
        select rout from rid where k=2;
    }
} {0 1205}

finish_test








>
>
>
>
>
>
>
>







 







<
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
...
320
321
322
323
324
325
326

do_test lastinsert-1.4 {
    catchsql {
        create table t2 (k integer primary key, val1, val2, val3);
        create view v as select * from t1;
        select last_insert_rowid();
    }
} {0 3}

# All remaining tests involve triggers.  Skip them if triggers are not
# supported in this build.
#
ifcapable {!trigger} {
  finish_test
  return
}

# ----------------------------------------------------------------------------
# 2.x - tests with after insert trigger

# LIRID changed properly after an insert into table containing an after trigger
do_test lastinsert-2.1 {
    catchsql {
................................................................................
do_test lastinsert-7.6 {
    catchsql {
        select rout from rid where k=2;
    }
} {0 1205}

finish_test

Changes to test/laststmtchanges.test.

69
70
71
72
73
74
75









76
77
78
79
80
81
82
...
250
251
252
253
254
255
256
257
# changes() set properly after delete from table
do_test laststmtchanges-1.5 {
    catchsql {
        delete from t0 where x=2;
        select changes();
    }
} {0 3}










# ----------------------------------------------------------------------------
# 2.x - tests with after insert trigger

# changes() changed properly after insert into table containing after trigger
do_test laststmtchanges-2.1 {
    set ::tc [db total_changes]
................................................................................
do_test laststmtchanges-5.5 {
    catchsql {
        select n from n2;
    }
} {0 {0 1 0 3}}

finish_test








>
>
>
>
>
>
>
>
>







 







<
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
...
259
260
261
262
263
264
265

# changes() set properly after delete from table
do_test laststmtchanges-1.5 {
    catchsql {
        delete from t0 where x=2;
        select changes();
    }
} {0 3}

# All remaining tests involve triggers.  Skip them if triggers are not
# supported in this build.
#
ifcapable {!trigger} {
  finish_test
  return
}


# ----------------------------------------------------------------------------
# 2.x - tests with after insert trigger

# changes() changed properly after insert into table containing after trigger
do_test laststmtchanges-2.1 {
    set ::tc [db total_changes]
................................................................................
do_test laststmtchanges-5.5 {
    catchsql {
        select n from n2;
    }
} {0 {0 1 0 3}}

finish_test

Changes to test/main.test.

7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
..
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
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
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
#    May you find forgiveness for yourself and forgive others.
#    May you share freely, never taking more than you give.
#
#***********************************************************************
# This file implements regression tests for SQLite library.  The
# focus of this file is exercising the code in main.c.
#
# $Id: main.test,v 1.17 2004/11/03 16:27:02 drh Exp $

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

# Tests of the sqlite_complete() function.
#
do_test main-1.1 {
................................................................................
  db complete {SELECT a-b FROM t1 }
} {0}
do_test main-1.16 {
  db complete {
    CREATE TABLE abc(x,y);
  }
} {1}

do_test main-1.17 {
  db complete {
    CREATE TRIGGER xyz AFTER DELETE abc BEGIN UPDATE pqr;
  }
} {0}
do_test main-1.18 {
  db complete {
    CREATE TRIGGER xyz AFTER DELETE abc BEGIN UPDATE pqr; END;
  }
} {1}
do_test main-1.19 {
  db complete {
    CREATE TRIGGER xyz AFTER DELETE abc BEGIN
       UPDATE pqr;
       unknown command;
  }
} {0}
do_test main-1.20 {
  db complete {
    CREATE TRIGGER xyz AFTER DELETE backend BEGIN
       UPDATE pqr;
  }
} {0}
do_test main-1.21 {
  db complete {
    CREATE TRIGGER xyz AFTER DELETE end BEGIN
       SELECT a, b FROM end;
  }
} {0}
do_test main-1.22 {
  db complete {
    CREATE TRIGGER xyz AFTER DELETE end BEGIN
       SELECT a, b FROM end;
    END;
  }
} {1}
do_test main-1.23 {
  db complete {
    CREATE TRIGGER xyz AFTER DELETE end BEGIN
       SELECT a, b FROM end;
    END;
    SELECT a, b FROM end;
  }
} {1}
do_test main-1.24 {
  db complete {
    CREATE TRIGGER xyz AFTER DELETE [;end;] BEGIN
       UPDATE pqr;
  }
} {0}
do_test main-1.25 {
  db complete {
    CREATE TRIGGER xyz AFTER DELETE backend BEGIN
       UPDATE pqr SET a=[;end;];;;
  }
} {0}
do_test main-1.26 {
  db complete {
    CREATE -- a comment
    TRIGGER xyz AFTER DELETE backend BEGIN
       UPDATE pqr SET a=5;
  }
} {0}
do_test main-1.27.1 {
  db complete {
    CREATE -- a comment
    TRIGGERX xyz AFTER DELETE backend BEGIN
       UPDATE pqr SET a=5;
  }
} {1}
do_test main-1.27.2 {
  db complete {
    CREATE/**/TRIGGER xyz AFTER DELETE backend BEGIN
       UPDATE pqr SET a=5;
  }
} {0}
ifcapable {explain} {
  do_test main-1.27.3 {
    db complete {
      /* */ EXPLAIN -- A comment
      CREATE/**/TRIGGER xyz AFTER DELETE backend BEGIN
         UPDATE pqr SET a=5;
    }
  } {0}






}


do_test main-1.27.4 {
  db complete {
    BOGUS token
    CREATE  TRIGGER xyz AFTER DELETE backend BEGIN
       UPDATE pqr SET a=5;
  }
} {1}
ifcapable {explain} {
  do_test main-1.27.5 {
    db complete {
      EXPLAIN 







      CREATE TEMP TRIGGER xyz AFTER DELETE backend BEGIN
         UPDATE pqr SET a=5;
    }
  } {0}
}
do_test main-1.28 {
  db complete {
    CREATE TEMP TRIGGER xyz AFTER DELETE backend BEGIN
       UPDATE pqr SET a=5;
  }
} {0}
do_test main-1.29 {
  db complete {
    CREATE TRIGGER xyz AFTER DELETE backend BEGIN
       UPDATE pqr SET a=5;
       EXPLAIN select * from xyz;
  }
} {0}

do_test main-1.30 {
  db complete {
     CREATE TABLE /* In comment ; */
  }
} {0}
do_test main-1.31 {
  db complete {







|







 







>
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
<
<
<
<
<
<
<
<




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




<
<
<
<
<
<
<
|
|
|
|
|
|
|
>







7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
..
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
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
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
#    May you find forgiveness for yourself and forgive others.
#    May you share freely, never taking more than you give.
#
#***********************************************************************
# This file implements regression tests for SQLite library.  The
# focus of this file is exercising the code in main.c.
#
# $Id: main.test,v 1.18 2004/11/04 04:42:28 drh Exp $

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

# Tests of the sqlite_complete() function.
#
do_test main-1.1 {
................................................................................
  db complete {SELECT a-b FROM t1 }
} {0}
do_test main-1.16 {
  db complete {
    CREATE TABLE abc(x,y);
  }
} {1}
ifcapable {trigger} {
  do_test main-1.17 {
    db complete {
      CREATE TRIGGER xyz AFTER DELETE abc BEGIN UPDATE pqr;
    }
  } {0}
  do_test main-1.18 {
    db complete {
      CREATE TRIGGER xyz AFTER DELETE abc BEGIN UPDATE pqr; END;
    }
  } {1}
  do_test main-1.19 {
    db complete {
      CREATE TRIGGER xyz AFTER DELETE abc BEGIN
         UPDATE pqr;
         unknown command;
    }
  } {0}
  do_test main-1.20 {
    db complete {
      CREATE TRIGGER xyz AFTER DELETE backend BEGIN
         UPDATE pqr;
    }
  } {0}
  do_test main-1.21 {
    db complete {
      CREATE TRIGGER xyz AFTER DELETE end BEGIN
         SELECT a, b FROM end;
    }
  } {0}
  do_test main-1.22 {
    db complete {
      CREATE TRIGGER xyz AFTER DELETE end BEGIN
         SELECT a, b FROM end;
      END;
    }
  } {1}
  do_test main-1.23 {
    db complete {
      CREATE TRIGGER xyz AFTER DELETE end BEGIN
         SELECT a, b FROM end;
      END;
      SELECT a, b FROM end;
    }
  } {1}
  do_test main-1.24 {
    db complete {
      CREATE TRIGGER xyz AFTER DELETE [;end;] BEGIN
         UPDATE pqr;
    }
  } {0}
  do_test main-1.25 {
    db complete {
      CREATE TRIGGER xyz AFTER DELETE backend BEGIN
         UPDATE pqr SET a=[;end;];;;
    }
  } {0}
  do_test main-1.26 {
    db complete {
      CREATE -- a comment
      TRIGGER xyz AFTER DELETE backend BEGIN
         UPDATE pqr SET a=5;
    }
  } {0}
  do_test main-1.27.1 {
    db complete {
      CREATE -- a comment
      TRIGGERX xyz AFTER DELETE backend BEGIN
         UPDATE pqr SET a=5;
    }
  } {1}
  do_test main-1.27.2 {
    db complete {








      CREATE/**/TRIGGER xyz AFTER DELETE backend BEGIN
         UPDATE pqr SET a=5;
    }
  } {0}
  ifcapable {explain} {
    do_test main-1.27.3 {
      db complete {
        /* */ EXPLAIN -- A comment
        CREATE/**/TRIGGER xyz AFTER DELETE backend BEGIN
           UPDATE pqr SET a=5;
      }
    } {0}
  }
  do_test main-1.27.4 {
    db complete {
      BOGUS token
      CREATE  TRIGGER xyz AFTER DELETE backend BEGIN
         UPDATE pqr SET a=5;
    }
  } {1}
  ifcapable {explain} {
    do_test main-1.27.5 {
      db complete {
        EXPLAIN 
        CREATE TEMP TRIGGER xyz AFTER DELETE backend BEGIN
           UPDATE pqr SET a=5;
      }
    } {0}
  }
  do_test main-1.28 {
    db complete {
      CREATE TEMP TRIGGER xyz AFTER DELETE backend BEGIN
         UPDATE pqr SET a=5;
    }
  } {0}







  do_test main-1.29 {
    db complete {
      CREATE TRIGGER xyz AFTER DELETE backend BEGIN
         UPDATE pqr SET a=5;
         EXPLAIN select * from xyz;
    }
  } {0}
}
do_test main-1.30 {
  db complete {
     CREATE TABLE /* In comment ; */
  }
} {0}
do_test main-1.31 {
  db complete {

Changes to test/misc1.test.

9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
...
534
535
536
537
538
539
540

541
542
543
544
545
546
547
...
552
553
554
555
556
557
558
559

560
#
#***********************************************************************
# This file implements regression tests for SQLite library.
#
# This file implements tests for miscellanous features that were
# left out of other test files.
#
# $Id: misc1.test,v 1.30 2004/08/20 18:34:20 drh Exp $

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

# Mimic the SQLite 2 collation type NUMERIC.
db collate numeric numeric_collate
proc numeric_collate {lhs rhs} {
................................................................................
do_test misc1-16.6 {
  execsql {
    INSERT INTO test VALUES(NULL);
    SELECT rowid, a FROM test;
  }
} {1 1 5 5 6 6}


# Ticket #333: Temp triggers that modify persistent tables.
#
do_test misc1-17.1 {
  execsql {
    BEGIN;
    CREATE TABLE RealTable(TestID INTEGER PRIMARY KEY, TestString TEXT);
    CREATE TEMP TABLE TempTable(TestID INTEGER PRIMARY KEY, TestString TEXT);
................................................................................
    INSERT INTO TempTable(TestString) VALUES ('1');
    INSERT INTO TempTable(TestString) VALUES ('2');
    UPDATE TempTable SET TestString = TestString + 1 WHERE TestID IN (1, 2);
    COMMIT;
    SELECT TestString FROM RealTable ORDER BY 1;
  }
} {2 3}


finish_test







|







 







>







 







|
>

9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
...
534
535
536
537
538
539
540
541
542
543
544
545
546
547
548
...
553
554
555
556
557
558
559
560
561
562
#
#***********************************************************************
# This file implements regression tests for SQLite library.
#
# This file implements tests for miscellanous features that were
# left out of other test files.
#
# $Id: misc1.test,v 1.31 2004/11/04 04:42:28 drh Exp $

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

# Mimic the SQLite 2 collation type NUMERIC.
db collate numeric numeric_collate
proc numeric_collate {lhs rhs} {
................................................................................
do_test misc1-16.6 {
  execsql {
    INSERT INTO test VALUES(NULL);
    SELECT rowid, a FROM test;
  }
} {1 1 5 5 6 6}

ifcapable {trigger} {
# Ticket #333: Temp triggers that modify persistent tables.
#
do_test misc1-17.1 {
  execsql {
    BEGIN;
    CREATE TABLE RealTable(TestID INTEGER PRIMARY KEY, TestString TEXT);
    CREATE TEMP TABLE TempTable(TestID INTEGER PRIMARY KEY, TestString TEXT);
................................................................................
    INSERT INTO TempTable(TestString) VALUES ('1');
    INSERT INTO TempTable(TestString) VALUES ('2');
    UPDATE TempTable SET TestString = TestString + 1 WHERE TestID IN (1, 2);
    COMMIT;
    SELECT TestString FROM RealTable ORDER BY 1;
  }
} {2 3}
}

finish_test

Changes to test/misc2.test.

9
10
11
12
13
14
15
16
17
18
19
20

21
22
23
24
25
26
27
..
31
32
33
34
35
36
37

38
39
40
41
42
43
44
#
#***********************************************************************
# This file implements regression tests for SQLite library.
#
# This file implements tests for miscellanous features that were
# left out of other test files.
#
# $Id: misc2.test,v 1.12 2004/06/19 00:16:31 drh Exp $

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


# Test for ticket #360
#
do_test misc2-1.1 {
  catchsql {
    CREATE TABLE FOO(bar integer);
    CREATE TRIGGER foo_insert BEFORE INSERT ON foo BEGIN
      SELECT CASE WHEN (NOT new.bar BETWEEN 0 AND 20)
................................................................................
  }
} {0 {}}
do_test misc2-1.2 {
  catchsql {
    INSERT INTO foo(bar) VALUES (111);
  }
} {1 aiieee}


# Make sure ROWID works on a view and a subquery.  Ticket #364
#
do_test misc2-2.1 {
  execsql {
    CREATE TABLE t1(a,b,c);
    INSERT INTO t1 VALUES(1,2,3);







|




>







 







>







9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
..
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
#
#***********************************************************************
# This file implements regression tests for SQLite library.
#
# This file implements tests for miscellanous features that were
# left out of other test files.
#
# $Id: misc2.test,v 1.13 2004/11/04 04:42:28 drh Exp $

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

ifcapable {trigger} {
# Test for ticket #360
#
do_test misc2-1.1 {
  catchsql {
    CREATE TABLE FOO(bar integer);
    CREATE TRIGGER foo_insert BEFORE INSERT ON foo BEGIN
      SELECT CASE WHEN (NOT new.bar BETWEEN 0 AND 20)
................................................................................
  }
} {0 {}}
do_test misc2-1.2 {
  catchsql {
    INSERT INTO foo(bar) VALUES (111);
  }
} {1 aiieee}
} ;# endif trigger

# Make sure ROWID works on a view and a subquery.  Ticket #364
#
do_test misc2-2.1 {
  execsql {
    CREATE TABLE t1(a,b,c);
    INSERT INTO t1 VALUES(1,2,3);

Changes to test/misc3.test.

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
...
249
250
251
252
253
254
255

256
257
258
259
260
261
262
...
283
284
285
286
287
288
289

290
291
292
293
294
295
296
#
#***********************************************************************
# This file implements regression tests for SQLite library.
#
# This file implements tests for miscellanous features that were
# left out of other test files.
#
# $Id: misc3.test,v 1.13 2004/11/03 16:27:02 drh Exp $

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


# Ticket #529.  Make sure an ABORT does not damage the in-memory cache
# that will be used by subsequent statements in the same transaction.
#
do_test misc3-1.1 {
  execsql {
    CREATE TABLE t1(a UNIQUE,b);
    INSERT INTO t1
      VALUES(1,'a23456789_b23456789_c23456789_d23456789_e23456789_');
    UPDATE t1 SET b=b||b;
    UPDATE t1 SET b=b||b;
    UPDATE t1 SET b=b||b;
    UPDATE t1 SET b=b||b;
    UPDATE t1 SET b=b||b;
    INSERT INTO t1 VALUES(2,'x');
    UPDATE t1 SET b=substr(b,1,500);
    BEGIN;
  }
  catchsql {UPDATE t1 SET a=CASE a WHEN 2 THEN 1 ELSE a END, b='y';}
  execsql {
    CREATE TABLE t2(x,y);
    COMMIT;
    PRAGMA integrity_check;
  }
} ok


do_test misc3-1.2 {
  execsql {
    DROP TABLE t1;
    DROP TABLE t2;
    VACUUM;



    CREATE TABLE t1(a UNIQUE,b);
    INSERT INTO t1
       VALUES(1,'a23456789_b23456789_c23456789_d23456789_e23456789_');
    INSERT INTO t1 SELECT a+1, b||b FROM t1;
    INSERT INTO t1 SELECT a+2, b||b FROM t1;
    INSERT INTO t1 SELECT a+4, b FROM t1;
    INSERT INTO t1 SELECT a+8, b FROM t1;
    INSERT INTO t1 SELECT a+16, b FROM t1;
    INSERT INTO t1 SELECT a+32, b FROM t1;
    INSERT INTO t1 SELECT a+64, b FROM t1;

    BEGIN;
  }
  catchsql {UPDATE t1 SET a=CASE a WHEN 128 THEN 127 ELSE a END, b='';}
  execsql {
    INSERT INTO t1 VALUES(200,'hello out there');
    COMMIT;
    PRAGMA integrity_check;
  }
} ok


# Tests of the sqliteAtoF() function in util.c
#
do_test misc3-2.1 {
  execsql {SELECT 2e-25*0.5e25}
} 1.0
do_test misc3-2.2 {
................................................................................
  } {0 {}}
  do_test misc3-6.3 {
    execsql {BEGIN; EXPLAIN ROLLBACK}
    catchsql {ROLLBACK}
  } {0 {}}
}


# Ticket #640:  vdbe stack overflow with a LIMIT clause on a SELECT inside
# of a trigger.
#
do_test misc3-7.1 {
  execsql {
    BEGIN;
    CREATE TABLE y1(a);
................................................................................
  }
} 0
do_test misc3-7.3 {
  execsql {
    SELECT count(*) FROM y3;
  }
} 32


# Ticket #668:  VDBE stack overflow occurs when the left-hand side
# of an IN expression is NULL and the result is used as an integer, not
# as a jump.
#
do_test misc-8.1 {
  execsql {







|




>
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
>
>
|
|
|
|
<
>
>
>
|
|
|
|
|
|
|
|
|
|
<
|
|
|
|
|
|
|
|
|
>







 







>







 







>







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
...
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
...
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
#
#***********************************************************************
# This file implements regression tests for SQLite library.
#
# This file implements tests for miscellanous features that were
# left out of other test files.
#
# $Id: misc3.test,v 1.14 2004/11/04 04:42:28 drh Exp $

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

ifcapable {integrityck} {
  # Ticket #529.  Make sure an ABORT does not damage the in-memory cache
  # that will be used by subsequent statements in the same transaction.
  #
  do_test misc3-1.1 {
    execsql {
      CREATE TABLE t1(a UNIQUE,b);
      INSERT INTO t1
        VALUES(1,'a23456789_b23456789_c23456789_d23456789_e23456789_');
      UPDATE t1 SET b=b||b;
      UPDATE t1 SET b=b||b;
      UPDATE t1 SET b=b||b;
      UPDATE t1 SET b=b||b;
      UPDATE t1 SET b=b||b;
      INSERT INTO t1 VALUES(2,'x');
      UPDATE t1 SET b=substr(b,1,500);
      BEGIN;
    }
    catchsql {UPDATE t1 SET a=CASE a WHEN 2 THEN 1 ELSE a END, b='y';}
    execsql {
      CREATE TABLE t2(x,y);
      COMMIT;
      PRAGMA integrity_check;
    }
  } ok
}
ifcapable {integrityck} {
  do_test misc3-1.2 {
    execsql {
      DROP TABLE t1;
      DROP TABLE t2;

    }
    ifcapable {vacuum} {execsql VACUUM}
    execsql {
      CREATE TABLE t1(a UNIQUE,b);
      INSERT INTO t1
      VALUES(1,'a23456789_b23456789_c23456789_d23456789_e23456789_');
      INSERT INTO t1 SELECT a+1, b||b FROM t1;
      INSERT INTO t1 SELECT a+2, b||b FROM t1;
      INSERT INTO t1 SELECT a+4, b FROM t1;
      INSERT INTO t1 SELECT a+8, b FROM t1;
      INSERT INTO t1 SELECT a+16, b FROM t1;
      INSERT INTO t1 SELECT a+32, b FROM t1;
      INSERT INTO t1 SELECT a+64, b FROM t1;

      BEGIN;
    }
    catchsql {UPDATE t1 SET a=CASE a WHEN 128 THEN 127 ELSE a END, b='';}
    execsql {
      INSERT INTO t1 VALUES(200,'hello out there');
      COMMIT;
      PRAGMA integrity_check;
    }
  } ok
}

# Tests of the sqliteAtoF() function in util.c
#
do_test misc3-2.1 {
  execsql {SELECT 2e-25*0.5e25}
} 1.0
do_test misc3-2.2 {
................................................................................
  } {0 {}}
  do_test misc3-6.3 {
    execsql {BEGIN; EXPLAIN ROLLBACK}
    catchsql {ROLLBACK}
  } {0 {}}
}

ifcapable {trigger} {
# Ticket #640:  vdbe stack overflow with a LIMIT clause on a SELECT inside
# of a trigger.
#
do_test misc3-7.1 {
  execsql {
    BEGIN;
    CREATE TABLE y1(a);
................................................................................
  }
} 0
do_test misc3-7.3 {
  execsql {
    SELECT count(*) FROM y3;
  }
} 32
} ;# endif trigger

# Ticket #668:  VDBE stack overflow occurs when the left-hand side
# of an IN expression is NULL and the result is used as an integer, not
# as a jump.
#
do_test misc-8.1 {
  execsql {

Changes to test/pagesize.test.

7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
..
84
85
86
87
88
89
90

91
92
93

94
95
96
97
98
99
100
...
144
145
146
147
148
149
150
151
152
153

154
155
156
157
158
159
160
161

162
163
164
165
166
#    May you find forgiveness for yourself and forgive others.
#    May you share freely, never taking more than you give.
#
#***********************************************************************
# This file implements regression tests for SQLite library.
# This file implements tests for the page_size PRAGMA.
#
# $Id: pagesize.test,v 1.6 2004/11/03 16:27:02 drh Exp $


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

do_test pagesize-1.1 {
  execsql {PRAGMA page_size}
................................................................................
    execsql {
      PRAGMA page_size
    }
  } $PGSZ
  do_test pagesize-2.$PGSZ.3 {
    file size test.db
  } [expr {$PGSZ*2}]

  do_test pagesize-2.$PGSZ.4 {
    execsql {VACUUM}
  } {}

  integrity_check pagesize-2.$PGSZ.5
  do_test pagesize-2.$PGSZ.6 {
    db close
    sqlite3 db test.db
    execsql {PRAGMA page_size}
  } $PGSZ
  do_test pagesize-2.$PGSZ.7 {
................................................................................
    execsql {
      ROLLBACK;
      SELECT count(*) FROM t1;
    }
  } 192
  integrity_check pagesize-2.$PGSZ.14
  do_test pagesize-2.$PGSZ.15 {
    execsql {
      DELETE FROM t1 WHERE rowid%5!=0;
      VACUUM;

      SELECT count(*) FROM t1;
    }
  } 38
  do_test pagesize-2.$PGSZ.16 {
    execsql {
      DROP TABLE t1;
      VACUUM;
    }

  } {}
  integrity_check pagesize-2.$PGSZ.17
}

finish_test







|







 







>
|
|
|
>







 







<
|
<
>
|
<


<
|
<
<
>





7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
..
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
...
146
147
148
149
150
151
152

153

154
155

156
157

158


159
160
161
162
163
164
#    May you find forgiveness for yourself and forgive others.
#    May you share freely, never taking more than you give.
#
#***********************************************************************
# This file implements regression tests for SQLite library.
# This file implements tests for the page_size PRAGMA.
#
# $Id: pagesize.test,v 1.7 2004/11/04 04:42:28 drh Exp $


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

do_test pagesize-1.1 {
  execsql {PRAGMA page_size}
................................................................................
    execsql {
      PRAGMA page_size
    }
  } $PGSZ
  do_test pagesize-2.$PGSZ.3 {
    file size test.db
  } [expr {$PGSZ*2}]
  ifcapable {vacuum} {
    do_test pagesize-2.$PGSZ.4 {
      execsql {VACUUM}
    } {}
  }
  integrity_check pagesize-2.$PGSZ.5
  do_test pagesize-2.$PGSZ.6 {
    db close
    sqlite3 db test.db
    execsql {PRAGMA page_size}
  } $PGSZ
  do_test pagesize-2.$PGSZ.7 {
................................................................................
    execsql {
      ROLLBACK;
      SELECT count(*) FROM t1;
    }
  } 192
  integrity_check pagesize-2.$PGSZ.14
  do_test pagesize-2.$PGSZ.15 {

    execsql {DELETE FROM t1 WHERE rowid%5!=0}

    ifcapable {vacuum} {execsql VACUUM}
    execsql {SELECT count(*) FROM t1}

  } 38
  do_test pagesize-2.$PGSZ.16 {

    execsql {DROP TABLE t1}


    ifcapable {vacuum} {execsql VACUUM}
  } {}
  integrity_check pagesize-2.$PGSZ.17
}

finish_test

Changes to test/rowid.test.

8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
...
364
365
366
367
368
369
370

371
372
373
374
375
376
377
...
415
416
417
418
419
420
421











422
423
424
425
426
427
428
#    May you share freely, never taking more than you give.
#
#***********************************************************************
# This file implements regression tests for SQLite library.  The
# focus of this file is testing the magic ROWID column that is
# found on all tables.
#
# $Id: rowid.test,v 1.16 2004/06/27 21:31:40 drh Exp $

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

# Basic ROWID functionality tests.
#
do_test rowid-1.1 {
................................................................................
  execsql {
    DELETE FROM t2 WHERE a!=2;
    INSERT INTO t2(b) VALUES(111);
    SELECT * FROM t2;
  }
} {2 66 3 111}


# Make sure AFTER triggers that do INSERTs do not change the last_insert_rowid.
# Ticket #290
#
do_test rowid-8.1 {
  execsql {
    CREATE TABLE t3(a integer primary key);
    CREATE TABLE t4(x);
................................................................................
  }
} {123 124}
do_test rowid-8.8 {
  execsql {
    SELECT rowid, * FROM t4;
  }
} {1 1 2 133 3 134}












# ticket #377: Comparison between integer primiary key and floating point
# values.
#
do_test rowid-9.1 {
  execsql {
    SELECT * FROM t3 WHERE a<123.5







|







 







>







 







>
>
>
>
>
>
>
>
>
>
>







8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
...
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
...
416
417
418
419
420
421
422
423
424
425
426
427
428
429
430
431
432
433
434
435
436
437
438
439
440
#    May you share freely, never taking more than you give.
#
#***********************************************************************
# This file implements regression tests for SQLite library.  The
# focus of this file is testing the magic ROWID column that is
# found on all tables.
#
# $Id: rowid.test,v 1.17 2004/11/04 04:42:28 drh Exp $

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

# Basic ROWID functionality tests.
#
do_test rowid-1.1 {
................................................................................
  execsql {
    DELETE FROM t2 WHERE a!=2;
    INSERT INTO t2(b) VALUES(111);
    SELECT * FROM t2;
  }
} {2 66 3 111}

ifcapable {trigger} {
# Make sure AFTER triggers that do INSERTs do not change the last_insert_rowid.
# Ticket #290
#
do_test rowid-8.1 {
  execsql {
    CREATE TABLE t3(a integer primary key);
    CREATE TABLE t4(x);
................................................................................
  }
} {123 124}
do_test rowid-8.8 {
  execsql {
    SELECT rowid, * FROM t4;
  }
} {1 1 2 133 3 134}
} ;# endif trigger

# If triggers are not enable, simulate their effect for the tests that
# follow.
ifcapable {!trigger} {
  execsql {
    CREATE TABLE t3(a integer primary key);
    INSERT INTO t3 VALUES(123);
    INSERT INTO t3 VALUES(124);
  }
}

# ticket #377: Comparison between integer primiary key and floating point
# values.
#
do_test rowid-9.1 {
  execsql {
    SELECT * FROM t3 WHERE a<123.5

Changes to test/vacuum.test.

7
8
9
10
11
12
13
14
15
16
17








18
19
20
21
22
23
24
#    May you find forgiveness for yourself and forgive others.
#    May you share freely, never taking more than you give.
#
#***********************************************************************
# This file implements regression tests for SQLite library.  The
# focus of this file is testing the VACUUM statement.
#
# $Id: vacuum.test,v 1.27 2004/11/03 13:59:06 drh Exp $

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









set fcnt 1
proc cksum {{db db}} {
  set sql "SELECT name, type, sql FROM sqlite_master ORDER BY name, type"
  set txt [$db eval $sql]\n
  set sql "SELECT name FROM sqlite_master WHERE type='table' ORDER BY name"
  foreach tbl [$db eval $sql] {







|



>
>
>
>
>
>
>
>







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
#    May you find forgiveness for yourself and forgive others.
#    May you share freely, never taking more than you give.
#
#***********************************************************************
# This file implements regression tests for SQLite library.  The
# focus of this file is testing the VACUUM statement.
#
# $Id: vacuum.test,v 1.28 2004/11/04 04:42:28 drh Exp $

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

# If the VACUUM statement is disabled in the current build, skip all
# the tests in this file.
#
ifcapable {!vacuum} {
  finish_test
  return
}

set fcnt 1
proc cksum {{db db}} {
  set sql "SELECT name, type, sql FROM sqlite_master ORDER BY name, type"
  set txt [$db eval $sql]\n
  set sql "SELECT name FROM sqlite_master WHERE type='table' ORDER BY name"
  foreach tbl [$db eval $sql] {