SQLite

Check-in [c80bbf14b3]
Login

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

Overview
Comment:Fix a problem with the DROP TABLE command on this branch.
Downloads: Tarball | ZIP archive
Timelines: family | ancestors | descendants | both | onepass-delete-or
Files: files | file ages | folders
SHA1: c80bbf14b365d2131210a849f26a69a5ea15101d
User & Date: dan 2015-12-09 16:08:22.445
References
2015-12-09
16:26
Simplification of the DROP TRIGGER logic using sqlite3NestedParse() instead of hand-coded VDBE code. This is a manual cherry-pick of the key change from check-in [c80bbf14b365d]. (check-in: 8021b4c813 user: drh tags: trunk)
Context
2015-12-09
17:45
Add extra test cases to delete4.test. (check-in: 571b64b923 user: dan tags: onepass-delete-or)
16:08
Fix a problem with the DROP TABLE command on this branch. (check-in: c80bbf14b3 user: dan tags: onepass-delete-or)
10:06
Add simple tests and bugfixes for DELETE statements that qualify for the OR-optimization. (check-in: 10ca7357b2 user: dan tags: onepass-delete-or)
Changes
Unified Diff Ignore Whitespace Patch
Changes to src/trigger.c.
555
556
557
558
559
560
561
562
563
564
565
566
567
568
569
570
571
572
573
574
575
576
577
578
579
580

581
582
583
584
585
586
587
588
589
590
591
592
593
  }
#endif

  /* Generate code to destroy the database record of the trigger.
  */
  assert( pTable!=0 );
  if( (v = sqlite3GetVdbe(pParse))!=0 ){
    int base;
    static const int iLn = VDBE_OFFSET_LINENO(2);
    static const VdbeOpList dropTrigger[] = {
      { OP_Rewind,     0, ADDR(9),  0},
      { OP_String8,    0, 1,        0}, /* 1 */
      { OP_Column,     0, 1,        2},
      { OP_Ne,         2, ADDR(8),  1},
      { OP_String8,    0, 1,        0}, /* 4: "trigger" */
      { OP_Column,     0, 0,        2},
      { OP_Ne,         2, ADDR(8),  1},
      { OP_Delete,     0, 0,        0},
      { OP_Next,       0, ADDR(1),  0}, /* 8 */
    };

    sqlite3BeginWriteOperation(pParse, 0, iDb);
    sqlite3OpenMasterTable(pParse, iDb);
    base = sqlite3VdbeAddOpList(v,  ArraySize(dropTrigger), dropTrigger, iLn);
    sqlite3VdbeChangeP4(v, base+1, pTrigger->zName, P4_TRANSIENT);
    sqlite3VdbeChangeP4(v, base+4, "trigger", P4_STATIC);

    sqlite3ChangeCookie(pParse, iDb);
    sqlite3VdbeAddOp2(v, OP_Close, 0, 0);
    sqlite3VdbeAddOp4(v, OP_DropTrigger, iDb, 0, 0, pTrigger->zName, 0);
    if( pParse->nMem<3 ){
      pParse->nMem = 3;
    }
  }
}

/*
** Remove a trigger from the hash tables of the sqlite* pointer.
*/
void sqlite3UnlinkAndDeleteTrigger(sqlite3 *db, int iDb, const char *zName){







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

<

<
<
<







555
556
557
558
559
560
561
562












563



564

565
566

567



568
569
570
571
572
573
574
  }
#endif

  /* Generate code to destroy the database record of the trigger.
  */
  assert( pTable!=0 );
  if( (v = sqlite3GetVdbe(pParse))!=0 ){
    sqlite3NestedParse(pParse,












       "DELETE FROM %Q.%s WHERE name=%Q AND type='trigger'",



       db->aDb[iDb].zName, SCHEMA_TABLE(iDb), pTrigger->zName

    );
    sqlite3ChangeCookie(pParse, iDb);

    sqlite3VdbeAddOp4(v, OP_DropTrigger, iDb, 0, 0, pTrigger->zName, 0);



  }
}

/*
** Remove a trigger from the hash tables of the sqlite* pointer.
*/
void sqlite3UnlinkAndDeleteTrigger(sqlite3 *db, int iDb, const char *zName){
Changes to test/delete4.test.
135
136
137
138
139
140
141


142








143






































144
145
  INSERT INTO t4 VALUES(14, 'abcde','xyzzy');
  CREATE INDEX idx_t4_3 ON t4 (col0);
  CREATE INDEX idx_t4_0 ON t4 (col1, col0);
  DELETE FROM t4 WHERE col0=69 OR col0>7;
  PRAGMA integrity_check;
} {ok}




















































finish_test







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


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
  INSERT INTO t4 VALUES(14, 'abcde','xyzzy');
  CREATE INDEX idx_t4_3 ON t4 (col0);
  CREATE INDEX idx_t4_0 ON t4 (col1, col0);
  DELETE FROM t4 WHERE col0=69 OR col0>7;
  PRAGMA integrity_check;
} {ok}

#-------------------------------------------------------------------------
# Further tests for DELETE statements that use the OR optimization.
#
array unset -nocomplain res
foreach {tn tbl} {
  0 { CREATE TABLE t5(i INTEGER, a, b, c) }
  1 { 
    CREATE TABLE t5(i INTEGER PRIMARY KEY, a, b, c);
    CREATE INDEX t5a ON t5(a);
    CREATE INDEX t5bc ON t5(b, c);
    CREATE INDEX t5c ON t5(c);
  }
  2 { 
    CREATE TABLE t5(i INT PRIMARY KEY, a, b, c) WITHOUT ROWID;
    CREATE INDEX t5a ON t5(a);
    CREATE INDEX t5bc ON t5(b, c);
    CREATE INDEX t5c ON t5(c);
  }
} {
  do_test 5.$tn.0 {
    execsql { DROP TABLE IF EXISTS t5 }
    execsql $tbl


    for {set i 1} {$i <= 100} {incr i} {
      set a [expr $i*2]
      set b [expr ($i*7 % 100)]
      set c [expr 100-$i]
      execsql { INSERT INTO t5 VALUES($i, $a, $b, $c) }
    }
  } {}

  foreach {tn2 sql} {
    1 { DELETE FROM t5 WHERE a=4 OR b=10 OR c=17 }
    2 { DELETE FROM t5 WHERE (a=4 OR b=22) AND c=98 }
  } {
    execsql BEGIN
    execsql $sql
    do_execsql_test 5.$tn.$tn2.1 { PRAGMA integrity_check } ok
    if {$tn==0} { 
      set res($tn2) [db eval { SELECT count(*), md5sum(i, a, b, c) FROM t5 }]
    }

    do_execsql_test 5.$tn.$tn2.2 { 
      SELECT count(*), md5sum(i, a, b, c) FROM t5 
    } $res($tn2)
     
    execsql ROLLBACK
  }
}

finish_test
Changes to test/table.test.
830
831
832
833
834
835
836










837
838
839
# with a NULL instead of a record created by OP_Record.
#
do_execsql_test table-19.1 {
  CREATE TABLE t19 AS SELECT * FROM sqlite_master;
  SELECT name FROM t19 ORDER BY name;
} {{} savepoint t10 t11 t12 t13 t16 t2 t3 t3\"xyz t4\"abc t7 t8 t9 tablet8 test1 weird}













finish_test







>
>
>
>
>
>
>
>
>
>



830
831
832
833
834
835
836
837
838
839
840
841
842
843
844
845
846
847
848
849
# with a NULL instead of a record created by OP_Record.
#
do_execsql_test table-19.1 {
  CREATE TABLE t19 AS SELECT * FROM sqlite_master;
  SELECT name FROM t19 ORDER BY name;
} {{} savepoint t10 t11 t12 t13 t16 t2 t3 t3\"xyz t4\"abc t7 t8 t9 tablet8 test1 weird}

# At one point the DROP TABLE in the following was causing problems on an
# experimental branch.
#
do_execsql_test 20.0 {
  CREATE TABLE t20(a PRIMARY KEY, b, c);
  CREATE TRIGGER tr6 AFTER INSERT ON t20 BEGIN SELECT 1+1+1; END;
  ANALYZE;
  DROP TABLE t20;
  SELECT name FROM sqlite_master
} {sqlite_stat1 sqlite_stat4}


finish_test