/ Check-in [3c8f97ae]
Login

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

Overview
Comment:Remove relevant elements from the sqlite_stat2 table when doing a DROP INDEX or DROP TABLE.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1: 3c8f97ae527e380bf2583c7cf8ceac9509f29bfe
User & Date: drh 2011-08-07 01:31:52
Context
2011-08-08
23:18
Allow the unlink() system call to be overridden in os_unix.c. check-in: 8d1b5c3a user: drh tags: trunk
2011-08-07
01:31
Remove relevant elements from the sqlite_stat2 table when doing a DROP INDEX or DROP TABLE. check-in: 3c8f97ae user: drh tags: trunk
2011-08-03
22:06
Merge the winopen-retry-logic branch into trunk. The biggest change here is to test scripts, which should now use such as copy_file and delete_file from tester.tcl rather than the raw file commands of TCL. check-in: b90c28be user: drh tags: trunk
Changes
Hide Diffs Side-by-Side Diffs Ignore Whitespace Patch

Changes to src/build.c.

  1975   1975         int iDb = sqlite3SchemaToIndex(pParse->db, pTab->pSchema);
  1976   1976         destroyRootPage(pParse, iLargest, iDb);
  1977   1977         iDestroyed = iLargest;
  1978   1978       }
  1979   1979     }
  1980   1980   #endif
  1981   1981   }
         1982  +
         1983  +/*
         1984  +** Remove entries from the sqlite_stat1 and sqlite_stat2 tables
         1985  +** after a DROP INDEX or DROP TABLE command.
         1986  +*/
         1987  +static void sqlite3ClearStatTables(
         1988  +  Parse *pParse,         /* The parsing context */
         1989  +  int iDb,               /* The database number */
         1990  +  const char *zType,     /* "idx" or "tbl" */
         1991  +  const char *zName      /* Name of index or table */
         1992  +){
         1993  +  static const char *azStatTab[] = { "sqlite_stat1", "sqlite_stat2" };
         1994  +  int i;
         1995  +  const char *zDbName = pParse->db->aDb[iDb].zName;
         1996  +  for(i=0; i<ArraySize(azStatTab); i++){
         1997  +    if( sqlite3FindTable(pParse->db, azStatTab[i], zDbName) ){
         1998  +      sqlite3NestedParse(pParse,
         1999  +        "DELETE FROM %Q.%s WHERE %s=%Q",
         2000  +        zDbName, azStatTab[i], zType, zName
         2001  +      );
         2002  +    }
         2003  +  }
         2004  +}
  1982   2005   
  1983   2006   /*
  1984   2007   ** This routine is called to do the work of a DROP TABLE statement.
  1985   2008   ** pName is the name of the table to be dropped.
  1986   2009   */
  1987   2010   void sqlite3DropTable(Parse *pParse, SrcList *pName, int isView, int noErr){
  1988   2011     Table *pTab;
................................................................................
  2115   2138       ** dropped. Triggers are handled seperately because a trigger can be
  2116   2139       ** created in the temp database that refers to a table in another
  2117   2140       ** database.
  2118   2141       */
  2119   2142       sqlite3NestedParse(pParse, 
  2120   2143           "DELETE FROM %Q.%s WHERE tbl_name=%Q and type!='trigger'",
  2121   2144           pDb->zName, SCHEMA_TABLE(iDb), pTab->zName);
  2122         -
  2123         -    /* Drop any statistics from the sqlite_stat1 table, if it exists */
  2124         -    if( sqlite3FindTable(db, "sqlite_stat1", db->aDb[iDb].zName) ){
  2125         -      sqlite3NestedParse(pParse,
  2126         -        "DELETE FROM %Q.sqlite_stat1 WHERE tbl=%Q", pDb->zName, pTab->zName
  2127         -      );
  2128         -    }
  2129         -
         2145  +    sqlite3ClearStatTables(pParse, iDb, "tbl", pTab->zName);
  2130   2146       if( !isView && !IsVirtual(pTab) ){
  2131   2147         destroyTable(pParse, pTab);
  2132   2148       }
  2133   2149   
  2134   2150       /* Remove the table entry from SQLite's internal schema and modify
  2135   2151       ** the schema cookie.
  2136   2152       */
................................................................................
  2945   2961   
  2946   2962     /* Generate code to remove the index and from the master table */
  2947   2963     v = sqlite3GetVdbe(pParse);
  2948   2964     if( v ){
  2949   2965       sqlite3BeginWriteOperation(pParse, 1, iDb);
  2950   2966       sqlite3NestedParse(pParse,
  2951   2967          "DELETE FROM %Q.%s WHERE name=%Q AND type='index'",
  2952         -       db->aDb[iDb].zName, SCHEMA_TABLE(iDb),
  2953         -       pIndex->zName
         2968  +       db->aDb[iDb].zName, SCHEMA_TABLE(iDb), pIndex->zName
  2954   2969       );
  2955         -    if( sqlite3FindTable(db, "sqlite_stat1", db->aDb[iDb].zName) ){
  2956         -      sqlite3NestedParse(pParse,
  2957         -        "DELETE FROM %Q.sqlite_stat1 WHERE idx=%Q",
  2958         -        db->aDb[iDb].zName, pIndex->zName
  2959         -      );
  2960         -    }
         2970  +    sqlite3ClearStatTables(pParse, iDb, "idx", pIndex->zName);
  2961   2971       sqlite3ChangeCookie(pParse, iDb);
  2962   2972       destroyRootPage(pParse, pIndex->tnum, iDb);
  2963   2973       sqlite3VdbeAddOp4(v, OP_DropIndex, iDb, 0, 0, pIndex->zName, 0);
  2964   2974     }
  2965   2975   
  2966   2976   exit_drop_index:
  2967   2977     sqlite3SrcListDelete(db, pName);

Changes to test/analyze.test.

   282    282     }
   283    283     db close
   284    284     sqlite3 db test.db
   285    285     execsql {
   286    286       SELECT * FROM t4 WHERE x=1234;
   287    287     }
   288    288   } {}
          289  +
          290  +# Verify that DROP TABLE and DROP INDEX remove entries from the 
          291  +# sqlite_stat1 and sqlite_stat2 tables.
          292  +#
          293  +do_test analyze-5.0 {
          294  +  execsql {
          295  +    DELETE FROM t3;
          296  +    DELETE FROM t4;
          297  +    INSERT INTO t3 VALUES(1,2,3,4);
          298  +    INSERT INTO t3 VALUES(5,6,7,8);
          299  +    INSERT INTO t3 SELECT a+8, b+8, c+8, d+8 FROM t3;
          300  +    INSERT INTO t3 SELECT a+16, b+16, c+16, d+16 FROM t3;
          301  +    INSERT INTO t3 SELECT a+32, b+32, c+32, d+32 FROM t3;
          302  +    INSERT INTO t3 SELECT a+64, b+64, c+64, d+64 FROM t3;
          303  +    INSERT INTO t4 SELECT a, b, c FROM t3;
          304  +    ANALYZE;
          305  +    SELECT DISTINCT idx FROM sqlite_stat1 ORDER BY 1;
          306  +    SELECT DISTINCT tbl FROM sqlite_stat1 ORDER BY 1;
          307  +  }
          308  +} {t3i1 t3i2 t3i3 t4i1 t4i2 t3 t4}
          309  +ifcapable stat2 {
          310  +  do_test analyze-5.1 {
          311  +    execsql {
          312  +      SELECT DISTINCT idx FROM sqlite_stat2 ORDER BY 1;
          313  +      SELECT DISTINCT tbl FROM sqlite_stat2 ORDER BY 1;
          314  +    }
          315  +  } {t3i1 t3i2 t3i3 t4i1 t4i2 t3 t4}
          316  +}
          317  +do_test analyze-5.2 {
          318  +  execsql {
          319  +    DROP INDEX t3i2;
          320  +    SELECT DISTINCT idx FROM sqlite_stat1 ORDER BY 1;
          321  +    SELECT DISTINCT tbl FROM sqlite_stat1 ORDER BY 1;
          322  +  }
          323  +} {t3i1 t3i3 t4i1 t4i2 t3 t4}
          324  +ifcapable stat2 {
          325  +  do_test analyze-5.3 {
          326  +    execsql {
          327  +      SELECT DISTINCT idx FROM sqlite_stat2 ORDER BY 1;
          328  +      SELECT DISTINCT tbl FROM sqlite_stat2 ORDER BY 1;
          329  +    }
          330  +  } {t3i1 t3i3 t4i1 t4i2 t3 t4}
          331  +}
          332  +do_test analyze-5.4 {
          333  +  execsql {
          334  +    DROP TABLE t3;
          335  +    SELECT DISTINCT idx FROM sqlite_stat1 ORDER BY 1;
          336  +    SELECT DISTINCT tbl FROM sqlite_stat1 ORDER BY 1;
          337  +  }
          338  +} {t4i1 t4i2 t4}
          339  +ifcapable stat2 {
          340  +  do_test analyze-5.5 {
          341  +    execsql {
          342  +      SELECT DISTINCT idx FROM sqlite_stat2 ORDER BY 1;
          343  +      SELECT DISTINCT tbl FROM sqlite_stat2 ORDER BY 1;
          344  +    }
          345  +  } {t4i1 t4i2 t4}
          346  +}
   289    347   
   290    348   # This test corrupts the database file so it must be the last test
   291    349   # in the series.
   292    350   #
   293    351   do_test analyze-99.1 {
   294    352     execsql {
   295    353       PRAGMA writable_schema=on;