/ Check-in [589f3f56]
Login

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

Overview
Comment:Change the ANALYZE command so that it no longer tried to delete unused sqlite_stat2 and sqlite_stat3 tables. Change the DROP TABLE command so that it is able to drop those tables.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1: 589f3f5652e3674d3203f8bd32784a46b5ed530c
User & Date: drh 2011-10-07 23:52:25
Context
2011-10-08
14:57
Add the SQLITE_DIRECT_OVERFLOW_READ compile time option. check-in: 2ab14a84 user: dan tags: trunk
2011-10-07
23:52
Change the ANALYZE command so that it no longer tried to delete unused sqlite_stat2 and sqlite_stat3 tables. Change the DROP TABLE command so that it is able to drop those tables. check-in: 589f3f56 user: drh tags: trunk
18:24
Make sure sqlite3_data_count() behaves as documented, even for EXPLAIN QUERY PLAN queries. check-in: d4f95b3b user: drh tags: trunk
Changes
Hide Diffs Side-by-Side Diffs Ignore Whitespace Patch

Changes to src/analyze.c.

   144    144       const char *zCols;
   145    145     } aTable[] = {
   146    146       { "sqlite_stat1", "tbl,idx,stat" },
   147    147   #ifdef SQLITE_ENABLE_STAT3
   148    148       { "sqlite_stat3", "tbl,idx,neq,nlt,ndlt,sample" },
   149    149   #endif
   150    150     };
   151         -  static const char *azToDrop[] = { 
   152         -    "sqlite_stat2",
   153         -#ifndef SQLITE_ENABLE_STAT3
   154         -    "sqlite_stat3",
   155         -#endif
   156         -  };
   157    151   
   158    152     int aRoot[] = {0, 0};
   159    153     u8 aCreateTbl[] = {0, 0};
   160    154   
   161    155     int i;
   162    156     sqlite3 *db = pParse->db;
   163    157     Db *pDb;
   164    158     Vdbe *v = sqlite3GetVdbe(pParse);
   165    159     if( v==0 ) return;
   166    160     assert( sqlite3BtreeHoldsAllMutexes(db) );
   167    161     assert( sqlite3VdbeDb(v)==db );
   168    162     pDb = &db->aDb[iDb];
   169    163   
   170         -  /* Drop all statistics tables that this version of SQLite does not
   171         -  ** understand.
   172         -  */
   173         -  for(i=0; i<ArraySize(azToDrop); i++){
   174         -    Table *pTab = sqlite3FindTable(db, azToDrop[i], pDb->zName);
   175         -    if( pTab ){
   176         -      sqlite3CodeDropTable(pParse, pTab, iDb, 0);
   177         -      break;
   178         -    }
   179         -  }
   180         -
   181    164     /* Create new statistic tables if they do not exist, or clear them
   182    165     ** if they do already exist.
   183    166     */
   184    167     for(i=0; i<ArraySize(aTable); i++){
   185    168       const char *zTab = aTable[i].zName;
   186    169       Table *pStat;
   187    170       if( (pStat = sqlite3FindTable(db, zTab, pDb->zName))==0 ){

Changes to src/build.c.

  2141   2141         goto exit_drop_table;
  2142   2142       }
  2143   2143       if( sqlite3AuthCheck(pParse, SQLITE_DELETE, pTab->zName, 0, zDb) ){
  2144   2144         goto exit_drop_table;
  2145   2145       }
  2146   2146     }
  2147   2147   #endif
  2148         -  if( sqlite3StrNICmp(pTab->zName, "sqlite_", 7)==0 ){
         2148  +  if( sqlite3StrNICmp(pTab->zName, "sqlite_", 7)==0 
         2149  +    && sqlite3StrNICmp(pTab->zName, "sqlite_stat", 11)!=0 ){
  2149   2150       sqlite3ErrorMsg(pParse, "table %s may not be dropped", pTab->zName);
  2150   2151       goto exit_drop_table;
  2151   2152     }
  2152   2153   
  2153   2154   #ifndef SQLITE_OMIT_VIEW
  2154   2155     /* Ensure DROP TABLE is not used on a view, and DROP VIEW is not used
  2155   2156     ** on a table.

Deleted test/stat3.test.

     1         -# 2011 August 08
     2         -#
     3         -# The author disclaims copyright to this source code.  In place of
     4         -# a legal notice, here is a blessing:
     5         -#
     6         -#    May you do good and not evil.
     7         -#    May you find forgiveness for yourself and forgive others.
     8         -#    May you share freely, never taking more than you give.
     9         -#
    10         -#***********************************************************************
    11         -#
    12         -# This file implements regression tests for SQLite library. This file 
    13         -# implements tests for the extra functionality provided by the ANALYZE 
    14         -# command when the library is compiled with SQLITE_ENABLE_STAT3 defined.
    15         -#
    16         -
    17         -set testdir [file dirname $argv0]
    18         -source $testdir/tester.tcl
    19         -
    20         -set testprefix stat3
    21         -
    22         -
    23         -# Verify that if not compiled with SQLITE_ENABLE_STAT2 that the ANALYZE
    24         -# command will delete the sqlite_stat2 table.  Likewise, if not compiled
    25         -# with SQLITE_ENABLE_STAT3, the sqlite_stat3 table is deleted.
    26         -#
    27         -do_test 1.1 {
    28         -  db eval {
    29         -    PRAGMA writable_schema=ON;
    30         -    CREATE TABLE sqlite_stat2(tbl,idx,sampleno,sample);
    31         -    CREATE TABLE sqlite_stat3(tbl,idx,neq,nlt,ndlt,sample);
    32         -    SELECT name FROM sqlite_master ORDER BY 1;
    33         -  }
    34         -} {sqlite_stat2 sqlite_stat3}
    35         -do_test 1.2 {
    36         -  db close
    37         -  sqlite3 db test.db
    38         -  db eval {SELECT name FROM sqlite_master ORDER BY 1}
    39         -} {sqlite_stat2 sqlite_stat3}
    40         -
    41         -ifcapable {stat3} {
    42         -  do_test 1.3 {
    43         -    db eval {ANALYZE; SELECT name FROM sqlite_master ORDER BY 1}
    44         -  } {sqlite_stat1 sqlite_stat3}
    45         -} else {
    46         -  do_test 1.4 {
    47         -    db eval {ANALYZE; ANALYZE; SELECT name FROM sqlite_master ORDER BY 1}
    48         -  } {sqlite_stat1}
    49         -  finish_test
    50         -  return
    51         -}
    52         -
    53         -
    54         -
    55         -
    56         -finish_test

Changes to test/table.test.

   255    255   } {0 {}}
   256    256   
   257    257   # Try to drop sqlite_master
   258    258   #
   259    259   do_test table-5.2 {
   260    260     catchsql {DROP TABLE IF EXISTS sqlite_master}
   261    261   } {1 {table sqlite_master may not be dropped}}
          262  +
          263  +# Dropping sqlite_statN tables is OK.
          264  +#
          265  +do_test table-5.2.1 {
          266  +  db eval {
          267  +    ANALYZE;
          268  +    DROP TABLE IF EXISTS sqlite_stat1;
          269  +    DROP TABLE IF EXISTS sqlite_stat2;
          270  +    DROP TABLE IF EXISTS sqlite_stat3;
          271  +    SELECT name FROM sqlite_master WHERE name GLOB 'sqlite_stat*';
          272  +  }
          273  +} {}
   262    274   
   263    275   # Make sure an EXPLAIN does not really create a new table
   264    276   #
   265    277   do_test table-5.3 {
   266    278     ifcapable {explain} {
   267    279       execsql {EXPLAIN CREATE TABLE test1(f1 int)}
   268    280     }