/ Check-in [bdfc19e8]
Login

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

Overview
Comment:Add the ability to change the autovacuum status of an existing database by setting the auto_vacuum pragma then running the VACUUM command. (CVS 4592)
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1: bdfc19e838b369a8c5d5d23663fad690f55ba3d7
User & Date: drh 2007-12-05 01:38:23
Context
2007-12-05
18:05
Begin adding requirements numbers to the C/C++ interface documentation. (CVS 4593) check-in: ae1936aa user: drh tags: trunk
01:38
Add the ability to change the autovacuum status of an existing database by setting the auto_vacuum pragma then running the VACUUM command. (CVS 4592) check-in: bdfc19e8 user: drh tags: trunk
2007-12-04
16:54
Make sure statement journals are initiated when doing DROP operations (since the DROP might fail after sqlite_master changes). Also make sure statement journals are initiated if there are pending SELECT statements. Ticket #2820. (CVS 4591) check-in: bf34284f user: drh tags: trunk
Changes
Hide Diffs Side-by-Side Diffs Ignore Whitespace Patch

Changes to src/main.c.

    10     10   **
    11     11   *************************************************************************
    12     12   ** Main file for the SQLite library.  The routines in this file
    13     13   ** implement the programmer interface to the library.  Routines in
    14     14   ** other files are for internal use by SQLite and should not be
    15     15   ** accessed by users of the library.
    16     16   **
    17         -** $Id: main.c,v 1.407 2007/10/12 19:35:49 drh Exp $
           17  +** $Id: main.c,v 1.408 2007/12/05 01:38:23 drh Exp $
    18     18   */
    19     19   #include "sqliteInt.h"
    20     20   #include <ctype.h>
    21     21   
    22     22   /*
    23     23   ** The version of the library
    24     24   */
................................................................................
   961    961     sqlite3_mutex_enter(db->mutex);
   962    962     db->errMask = 0xff;
   963    963     db->priorNewRowid = 0;
   964    964     db->nDb = 2;
   965    965     db->magic = SQLITE_MAGIC_BUSY;
   966    966     db->aDb = db->aDbStatic;
   967    967     db->autoCommit = 1;
          968  +  db->nextAutovac = -1;
   968    969     db->flags |= SQLITE_ShortColNames
   969    970   #if SQLITE_DEFAULT_FILE_FORMAT<4
   970    971                    | SQLITE_LegacyFileFmt
   971    972   #endif
   972    973   #ifdef SQLITE_ENABLE_LOAD_EXTENSION
   973    974                    | SQLITE_LoadExtension
   974    975   #endif

Changes to src/pragma.c.

     7      7   **    May you do good and not evil.
     8      8   **    May you find forgiveness for yourself and forgive others.
     9      9   **    May you share freely, never taking more than you give.
    10     10   **
    11     11   *************************************************************************
    12     12   ** This file contains code used to implement the PRAGMA command.
    13     13   **
    14         -** $Id: pragma.c,v 1.150 2007/11/13 10:30:25 danielk1977 Exp $
           14  +** $Id: pragma.c,v 1.151 2007/12/05 01:38:24 drh Exp $
    15     15   */
    16     16   #include "sqliteInt.h"
    17     17   #include <ctype.h>
    18     18   
    19     19   /* Ignore this whole file if pragmas are disabled
    20     20   */
    21     21   #if !defined(SQLITE_OMIT_PRAGMA) && !defined(SQLITE_OMIT_PARSER)
................................................................................
   436    436       }
   437    437       if( !zRight ){
   438    438         int auto_vacuum = 
   439    439             pBt ? sqlite3BtreeGetAutoVacuum(pBt) : SQLITE_DEFAULT_AUTOVACUUM;
   440    440         returnSingleInt(pParse, "auto_vacuum", auto_vacuum);
   441    441       }else{
   442    442         int eAuto = getAutoVacuum(zRight);
          443  +      db->nextAutovac = eAuto;
   443    444         if( eAuto>=0 ){
   444    445           /* Call SetAutoVacuum() to set initialize the internal auto and
   445    446           ** incr-vacuum flags. This is required in case this connection
   446    447           ** creates the database file. It is important that it is created
   447    448           ** as an auto-vacuum capable db.
   448    449           */
   449    450           int rc = sqlite3BtreeSetAutoVacuum(pBt, eAuto);

Changes to src/sqliteInt.h.

     7      7   **    May you do good and not evil.
     8      8   **    May you find forgiveness for yourself and forgive others.
     9      9   **    May you share freely, never taking more than you give.
    10     10   **
    11     11   *************************************************************************
    12     12   ** Internal interface definitions for SQLite.
    13     13   **
    14         -** @(#) $Id: sqliteInt.h,v 1.622 2007/11/29 17:05:18 danielk1977 Exp $
           14  +** @(#) $Id: sqliteInt.h,v 1.623 2007/12/05 01:38:24 drh Exp $
    15     15   */
    16     16   #ifndef _SQLITEINT_H_
    17     17   #define _SQLITEINT_H_
    18     18   
    19     19   /*
    20     20   ** The macro unlikely() is a hint that surrounds a boolean
    21     21   ** expression that is usually false.  Macro likely() surrounds
................................................................................
   477    477     int flags;                    /* Miscellanous flags. See below */
   478    478     int openFlags;                /* Flags passed to sqlite3_vfs.xOpen() */
   479    479     int errCode;                  /* Most recent error code (SQLITE_*) */
   480    480     int errMask;                  /* & result codes with this before returning */
   481    481     u8 autoCommit;                /* The auto-commit flag. */
   482    482     u8 temp_store;                /* 1: file 2: memory 0: default */
   483    483     u8 mallocFailed;              /* True if we have seen a malloc failure */
          484  +  char nextAutovac;             /* Autovac setting after VACUUM if >=0 */
   484    485     int nTable;                   /* Number of tables in the database */
   485    486     CollSeq *pDfltColl;           /* The default collating sequence (BINARY) */
   486    487     i64 lastRowid;                /* ROWID of most recent insert (see above) */
   487    488     i64 priorNewRowid;            /* Last randomly generated ROWID */
   488    489     int magic;                    /* Magic number for detect library misuse */
   489    490     int nChange;                  /* Value returned by sqlite3_changes() */
   490    491     int nTotalChange;             /* Value returned by sqlite3_total_changes() */

Changes to src/vacuum.c.

    10     10   **
    11     11   *************************************************************************
    12     12   ** This file contains code used to implement the VACUUM command.
    13     13   **
    14     14   ** Most of the code in this file may be omitted by defining the
    15     15   ** SQLITE_OMIT_VACUUM macro.
    16     16   **
    17         -** $Id: vacuum.c,v 1.74 2007/10/20 20:58:57 drh Exp $
           17  +** $Id: vacuum.c,v 1.75 2007/12/05 01:38:24 drh Exp $
    18     18   */
    19     19   #include "sqliteInt.h"
    20     20   #include "vdbeInt.h"
    21     21   
    22     22   #if !defined(SQLITE_OMIT_VACUUM) && !defined(SQLITE_OMIT_ATTACH)
    23     23   /*
    24     24   ** Execute zSql on database db. Return an error code.
................................................................................
   121    121     assert( sqlite3BtreeGetPageSize(pTemp)==sqlite3BtreeGetPageSize(pMain) );
   122    122     rc = execSql(db, "PRAGMA vacuum_db.synchronous=OFF");
   123    123     if( rc!=SQLITE_OK ){
   124    124       goto end_of_vacuum;
   125    125     }
   126    126   
   127    127   #ifndef SQLITE_OMIT_AUTOVACUUM
   128         -  sqlite3BtreeSetAutoVacuum(pTemp, sqlite3BtreeGetAutoVacuum(pMain));
          128  +  sqlite3BtreeSetAutoVacuum(pTemp, db->nextAutovac>=0 ? db->nextAutovac :
          129  +                                           sqlite3BtreeGetAutoVacuum(pMain));
   129    130   #endif
   130    131   
   131    132     /* Begin a transaction */
   132    133     rc = execSql(db, "BEGIN EXCLUSIVE;");
   133    134     if( rc!=SQLITE_OK ) goto end_of_vacuum;
   134    135   
   135    136     /* Query the schema of the main database. Create a mirror schema

Changes to test/vacuum2.test.

     7      7   #    May you find forgiveness for yourself and forgive others.
     8      8   #    May you share freely, never taking more than you give.
     9      9   #
    10     10   #***********************************************************************
    11     11   # This file implements regression tests for SQLite library.  The
    12     12   # focus of this file is testing the VACUUM statement.
    13     13   #
    14         -# $Id: vacuum2.test,v 1.3 2007/07/19 16:35:17 drh Exp $
           14  +# $Id: vacuum2.test,v 1.4 2007/12/05 01:38:24 drh Exp $
    15     15   
    16     16   set testdir [file dirname $argv0]
    17     17   source $testdir/tester.tcl
    18     18   
    19     19   # If the VACUUM statement is disabled in the current build, skip all
    20     20   # the tests in this file.
    21     21   #
................................................................................
    52     52   } [expr {[hexio_get_int [hexio_read test.db 24 4]]+3}]
    53     53   do_test vacuum2-2.1 {
    54     54     execsql {
    55     55       VACUUM
    56     56     }
    57     57     hexio_get_int [hexio_read test.db 24 4]
    58     58   } [expr {[hexio_get_int [hexio_read test.db 24 4]]+1}]
           59  +
           60  +############################################################################
           61  +# Verify that we can use the auto_vacuum pragma to request a new
           62  +# autovacuum setting, do a VACUUM, and the new setting takes effect.
           63  +# Make sure this happens correctly even if there are multiple open
           64  +# connections to the same database file.
           65  +#
           66  +sqlite3 db2 test.db
           67  +set pageSize [db eval {pragma page_size}]
           68  +
           69  +# Compute a checksum on the content of a database
           70  +#
           71  +proc cksum {{db db}} {
           72  +  set sql "SELECT name, type, sql FROM sqlite_master ORDER BY name, type"
           73  +  set txt [$db eval $sql]\n
           74  +  set sql "SELECT name FROM sqlite_master WHERE type='table' ORDER BY name"
           75  +  foreach tbl [$db eval $sql] {
           76  +    append txt [$db eval "SELECT * FROM $tbl"]\n
           77  +  }
           78  +  foreach prag {default_cache_size} {
           79  +    append txt $prag-[$db eval "PRAGMA $prag"]\n
           80  +  }
           81  +  set cksum [string length $txt]-[md5 $txt]
           82  +  return $cksum
           83  +}
           84  +
           85  +# We are currently not autovacuuming so the database should be 3 pages
           86  +# in size.  1 page for each of sqlite_master, t1, and t2.
           87  +#
           88  +do_test vacuum2-3.1 {
           89  +  execsql {
           90  +    INSERT INTO t1 VALUES('hello');
           91  +    INSERT INTO t2 VALUES('out there');
           92  +  }
           93  +  expr {[file size test.db]/$pageSize}
           94  +} {3}
           95  +set cksum [cksum]
           96  +do_test vacuum2-3.2 {
           97  +  cksum db2
           98  +} $cksum
           99  +
          100  +# Convert the database to an autovacuumed database.
          101  +do_test vacuum2-3.3 {
          102  +  execsql {
          103  +    PRAGMA auto_vacuum=FULL;
          104  +    VACUUM;
          105  +  }
          106  +  expr {[file size test.db]/$pageSize}
          107  +} {4}
          108  +do_test vacuum2-3.4 {
          109  +  cksum db2
          110  +} $cksum
          111  +do_test vacuum2-3.5 {
          112  +  cksum
          113  +} $cksum
          114  +do_test vacuum2-3.6 {
          115  +  execsql {PRAGMA integrity_check} db2
          116  +} {ok}
          117  +do_test vacuum2-3.7 {
          118  +  execsql {PRAGMA integrity_check} db
          119  +} {ok}
          120  +
          121  +# Convert the database back to a non-autovacuumed database.
          122  +do_test vacuum2-3.13 {
          123  +  execsql {
          124  +    PRAGMA auto_vacuum=NONE;
          125  +    VACUUM;
          126  +  }
          127  +  expr {[file size test.db]/$pageSize}
          128  +} {3}
          129  +do_test vacuum2-3.14 {
          130  +  cksum db2
          131  +} $cksum
          132  +do_test vacuum2-3.15 {
          133  +  cksum
          134  +} $cksum
          135  +do_test vacuum2-3.16 {
          136  +  execsql {PRAGMA integrity_check} db2
          137  +} {ok}
          138  +do_test vacuum2-3.17 {
          139  +  execsql {PRAGMA integrity_check} db
          140  +} {ok}
    59    141   
    60    142   finish_test