SQLite

Check-in [349aab42c7]
Login

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

Overview
Comment:Remove entries from the sqlite_stat1 table whenever an index is dropped. Related to #3033. (CVS 4989)
Downloads: Tarball | ZIP archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1: 349aab42c7896f04ecb6ca33f08a4c194051e837
User & Date: danielk1977 2008-04-11 17:11:27.000
Context
2008-04-11
17:15
Remove a recently added assert() that is failing with certain compilers on 32-bit platforms. Ticket #3043. (CVS 4990) (check-in: 03c0279d7b user: danielk1977 tags: trunk)
17:11
Remove entries from the sqlite_stat1 table whenever an index is dropped. Related to #3033. (CVS 4989) (check-in: 349aab42c7 user: danielk1977 tags: trunk)
15:36
Additional reductions in the use of memset(). (CVS 4988) (check-in: 38746c5438 user: drh tags: trunk)
Changes
Unified Diff Ignore Whitespace Patch
Changes to src/build.c.
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
**     CREATE INDEX
**     DROP INDEX
**     creating ID lists
**     BEGIN TRANSACTION
**     COMMIT
**     ROLLBACK
**
** $Id: build.c,v 1.479 2008/04/02 16:29:31 drh Exp $
*/
#include "sqliteInt.h"
#include <ctype.h>

/*
** This routine is called when a new SQL statement is beginning to
** be parsed.  Initialize the pParse structure as needed.







|







18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
**     CREATE INDEX
**     DROP INDEX
**     creating ID lists
**     BEGIN TRANSACTION
**     COMMIT
**     ROLLBACK
**
** $Id: build.c,v 1.480 2008/04/11 17:11:27 danielk1977 Exp $
*/
#include "sqliteInt.h"
#include <ctype.h>

/*
** This routine is called when a new SQL statement is beginning to
** be parsed.  Initialize the pParse structure as needed.
2068
2069
2070
2071
2072
2073
2074








2075
2076
2077
2078
2079
2080
2081
    ** dropped. Triggers are handled seperately because a trigger can be
    ** created in the temp database that refers to a table in another
    ** database.
    */
    sqlite3NestedParse(pParse, 
        "DELETE FROM %Q.%s WHERE tbl_name=%Q and type!='trigger'",
        pDb->zName, SCHEMA_TABLE(iDb), pTab->zName);








    if( !isView && !IsVirtual(pTab) ){
      destroyTable(pParse, pTab);
    }

    /* Remove the table entry from SQLite's internal schema and modify
    ** the schema cookie.
    */







>
>
>
>
>
>
>
>







2068
2069
2070
2071
2072
2073
2074
2075
2076
2077
2078
2079
2080
2081
2082
2083
2084
2085
2086
2087
2088
2089
    ** dropped. Triggers are handled seperately because a trigger can be
    ** created in the temp database that refers to a table in another
    ** database.
    */
    sqlite3NestedParse(pParse, 
        "DELETE FROM %Q.%s WHERE tbl_name=%Q and type!='trigger'",
        pDb->zName, SCHEMA_TABLE(iDb), pTab->zName);

    /* Drop any statistics from the sqlite_stat1 table, if it exists */
    if( sqlite3FindTable(db, "sqlite_stat1", db->aDb[iDb].zName) ){
      sqlite3NestedParse(pParse,
        "DELETE FROM %Q.sqlite_stat1 WHERE tbl=%Q", pDb->zName, pTab->zName
      );
    }

    if( !isView && !IsVirtual(pTab) ){
      destroyTable(pParse, pTab);
    }

    /* Remove the table entry from SQLite's internal schema and modify
    ** the schema cookie.
    */
2853
2854
2855
2856
2857
2858
2859






2860
2861
2862
2863
2864
2865
2866
  if( v ){
    sqlite3BeginWriteOperation(pParse, 1, iDb);
    sqlite3NestedParse(pParse,
       "DELETE FROM %Q.%s WHERE name=%Q",
       db->aDb[iDb].zName, SCHEMA_TABLE(iDb),
       pIndex->zName
    );






    sqlite3ChangeCookie(pParse, iDb);
    destroyRootPage(pParse, pIndex->tnum, iDb);
    sqlite3VdbeAddOp4(v, OP_DropIndex, iDb, 0, 0, pIndex->zName, 0);
  }

exit_drop_index:
  sqlite3SrcListDelete(pName);







>
>
>
>
>
>







2861
2862
2863
2864
2865
2866
2867
2868
2869
2870
2871
2872
2873
2874
2875
2876
2877
2878
2879
2880
  if( v ){
    sqlite3BeginWriteOperation(pParse, 1, iDb);
    sqlite3NestedParse(pParse,
       "DELETE FROM %Q.%s WHERE name=%Q",
       db->aDb[iDb].zName, SCHEMA_TABLE(iDb),
       pIndex->zName
    );
    if( sqlite3FindTable(db, "sqlite_stat1", db->aDb[iDb].zName) ){
      sqlite3NestedParse(pParse,
        "DELETE FROM %Q.sqlite_stat1 WHERE idx=%Q",
        db->aDb[iDb].zName, pIndex->zName
      );
    }
    sqlite3ChangeCookie(pParse, iDb);
    destroyRootPage(pParse, pIndex->tnum, iDb);
    sqlite3VdbeAddOp4(v, OP_DropIndex, iDb, 0, 0, pIndex->zName, 0);
  }

exit_drop_index:
  sqlite3SrcListDelete(pName);
Changes to test/analyze.test.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
# 2005 July 22
#
# The author disclaims copyright to this source code.  In place of
# a legal notice, here is a blessing:
#
#    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.
#
#***********************************************************************
# This file implements regression tests for SQLite library.
# This file implements tests for the ANALYZE command.
#
# $Id: analyze.test,v 1.6 2008/03/19 13:03:34 drh Exp $

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

# There is nothing to test if ANALYZE is disable for this build.
#
ifcapable {!analyze} {













|







1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
# 2005 July 22
#
# The author disclaims copyright to this source code.  In place of
# a legal notice, here is a blessing:
#
#    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.
#
#***********************************************************************
# This file implements regression tests for SQLite library.
# This file implements tests for the ANALYZE command.
#
# $Id: analyze.test,v 1.7 2008/04/11 17:11:27 danielk1977 Exp $

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

# There is nothing to test if ANALYZE is disable for this build.
#
ifcapable {!analyze} {
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
} {t1i1 {5 3} t1i2 {5 2} t1i3 {5 3 1} t2i1 {5 3} t2i2 {5 2} t2i3 {5 3 1}}
do_test analyze-3.5 {
  execsql {
    DROP INDEX t2i3;
    ANALYZE t1;
    SELECT idx, stat FROM sqlite_stat1 ORDER BY idx;
  }
} {t1i1 {5 3} t1i2 {5 2} t1i3 {5 3 1} t2i1 {5 3} t2i2 {5 2} t2i3 {5 3 1}}
do_test analyze-3.6 {
  execsql {
    ANALYZE t2;
    SELECT idx, stat FROM sqlite_stat1 ORDER BY idx;
  }
} {t1i1 {5 3} t1i2 {5 2} t1i3 {5 3 1} t2i1 {5 3} t2i2 {5 2}}
do_test analyze-3.7 {







|







161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
} {t1i1 {5 3} t1i2 {5 2} t1i3 {5 3 1} t2i1 {5 3} t2i2 {5 2} t2i3 {5 3 1}}
do_test analyze-3.5 {
  execsql {
    DROP INDEX t2i3;
    ANALYZE t1;
    SELECT idx, stat FROM sqlite_stat1 ORDER BY idx;
  }
} {t1i1 {5 3} t1i2 {5 2} t1i3 {5 3 1} t2i1 {5 3} t2i2 {5 2}}
do_test analyze-3.6 {
  execsql {
    ANALYZE t2;
    SELECT idx, stat FROM sqlite_stat1 ORDER BY idx;
  }
} {t1i1 {5 3} t1i2 {5 2} t1i3 {5 3 1} t2i1 {5 3} t2i2 {5 2}}
do_test analyze-3.7 {
183
184
185
186
187
188
189





190
191
























192
193
194
195
196
197
198
  execsql {
    CREATE TABLE t3 AS SELECT a, b, rowid AS c, 'hi' AS d FROM t1;
    CREATE INDEX t3i1 ON t3(a);
    CREATE INDEX t3i2 ON t3(a,b,c,d);
    CREATE INDEX t3i3 ON t3(d,b,c,a);
    DROP TABLE t1;
    DROP TABLE t2;





    ANALYZE;
    SELECT idx, stat FROM sqlite_stat1 ORDER BY idx;
























  }
} {t3i1 {5 3} t3i2 {5 3 1 1 1} t3i3 {5 5 2 1 1}}

# Try corrupting the sqlite_stat1 table and make sure the
# database is still able to function.
#
do_test analyze-4.0 {







>
>
>
>
>


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







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
  execsql {
    CREATE TABLE t3 AS SELECT a, b, rowid AS c, 'hi' AS d FROM t1;
    CREATE INDEX t3i1 ON t3(a);
    CREATE INDEX t3i2 ON t3(a,b,c,d);
    CREATE INDEX t3i3 ON t3(d,b,c,a);
    DROP TABLE t1;
    DROP TABLE t2;
    SELECT idx, stat FROM sqlite_stat1 ORDER BY idx;
  }
} {}
do_test analyze-3.9 {
  execsql {
    ANALYZE;
    SELECT idx, stat FROM sqlite_stat1 ORDER BY idx;
  }
} {t3i1 {5 3} t3i2 {5 3 1 1 1} t3i3 {5 5 2 1 1}}

do_test analyze-3.10 {
  execsql {
    CREATE TABLE [silly " name](a, b, c);
    CREATE INDEX 'foolish '' name' ON [silly " name](a, b);
    CREATE INDEX 'another foolish '' name' ON [silly " name](c);
    INSERT INTO [silly " name] VALUES(1, 2, 3);
    INSERT INTO [silly " name] VALUES(4, 5, 6);
    ANALYZE;
    SELECT idx, stat FROM sqlite_stat1 ORDER BY idx;
  }
} {{another foolish ' name} {2 1} {foolish ' name} {2 1 1} t3i1 {5 3} t3i2 {5 3 1 1 1} t3i3 {5 5 2 1 1}}
do_test analyze-3.11 {
  execsql {
    DROP INDEX "foolish ' name";
    SELECT idx, stat FROM sqlite_stat1 ORDER BY idx;
  }
} {{another foolish ' name} {2 1} t3i1 {5 3} t3i2 {5 3 1 1 1} t3i3 {5 5 2 1 1}}
do_test analyze-3.11 {
  execsql {
    DROP TABLE "silly "" name";
    SELECT idx, stat FROM sqlite_stat1 ORDER BY idx;
  }
} {t3i1 {5 3} t3i2 {5 3 1 1 1} t3i3 {5 5 2 1 1}}

# Try corrupting the sqlite_stat1 table and make sure the
# database is still able to function.
#
do_test analyze-4.0 {