/ 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 Unified Diffs Ignore Whitespace Patch

Changes to src/main.c.

10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
...
961
962
963
964
965
966
967

968
969
970
971
972
973
974
**
*************************************************************************
** Main file for the SQLite library.  The routines in this file
** implement the programmer interface to the library.  Routines in
** other files are for internal use by SQLite and should not be
** accessed by users of the library.
**
** $Id: main.c,v 1.407 2007/10/12 19:35:49 drh Exp $
*/
#include "sqliteInt.h"
#include <ctype.h>

/*
** The version of the library
*/
................................................................................
  sqlite3_mutex_enter(db->mutex);
  db->errMask = 0xff;
  db->priorNewRowid = 0;
  db->nDb = 2;
  db->magic = SQLITE_MAGIC_BUSY;
  db->aDb = db->aDbStatic;
  db->autoCommit = 1;

  db->flags |= SQLITE_ShortColNames
#if SQLITE_DEFAULT_FILE_FORMAT<4
                 | SQLITE_LegacyFileFmt
#endif
#ifdef SQLITE_ENABLE_LOAD_EXTENSION
                 | SQLITE_LoadExtension
#endif







|







 







>







10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
...
961
962
963
964
965
966
967
968
969
970
971
972
973
974
975
**
*************************************************************************
** Main file for the SQLite library.  The routines in this file
** implement the programmer interface to the library.  Routines in
** other files are for internal use by SQLite and should not be
** accessed by users of the library.
**
** $Id: main.c,v 1.408 2007/12/05 01:38:23 drh Exp $
*/
#include "sqliteInt.h"
#include <ctype.h>

/*
** The version of the library
*/
................................................................................
  sqlite3_mutex_enter(db->mutex);
  db->errMask = 0xff;
  db->priorNewRowid = 0;
  db->nDb = 2;
  db->magic = SQLITE_MAGIC_BUSY;
  db->aDb = db->aDbStatic;
  db->autoCommit = 1;
  db->nextAutovac = -1;
  db->flags |= SQLITE_ShortColNames
#if SQLITE_DEFAULT_FILE_FORMAT<4
                 | SQLITE_LegacyFileFmt
#endif
#ifdef SQLITE_ENABLE_LOAD_EXTENSION
                 | SQLITE_LoadExtension
#endif

Changes to src/pragma.c.

7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
...
436
437
438
439
440
441
442

443
444
445
446
447
448
449
**    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 contains code used to implement the PRAGMA command.
**
** $Id: pragma.c,v 1.150 2007/11/13 10:30:25 danielk1977 Exp $
*/
#include "sqliteInt.h"
#include <ctype.h>

/* Ignore this whole file if pragmas are disabled
*/
#if !defined(SQLITE_OMIT_PRAGMA) && !defined(SQLITE_OMIT_PARSER)
................................................................................
    }
    if( !zRight ){
      int auto_vacuum = 
          pBt ? sqlite3BtreeGetAutoVacuum(pBt) : SQLITE_DEFAULT_AUTOVACUUM;
      returnSingleInt(pParse, "auto_vacuum", auto_vacuum);
    }else{
      int eAuto = getAutoVacuum(zRight);

      if( eAuto>=0 ){
        /* Call SetAutoVacuum() to set initialize the internal auto and
        ** incr-vacuum flags. This is required in case this connection
        ** creates the database file. It is important that it is created
        ** as an auto-vacuum capable db.
        */
        int rc = sqlite3BtreeSetAutoVacuum(pBt, eAuto);







|







 







>







7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
...
436
437
438
439
440
441
442
443
444
445
446
447
448
449
450
**    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 contains code used to implement the PRAGMA command.
**
** $Id: pragma.c,v 1.151 2007/12/05 01:38:24 drh Exp $
*/
#include "sqliteInt.h"
#include <ctype.h>

/* Ignore this whole file if pragmas are disabled
*/
#if !defined(SQLITE_OMIT_PRAGMA) && !defined(SQLITE_OMIT_PARSER)
................................................................................
    }
    if( !zRight ){
      int auto_vacuum = 
          pBt ? sqlite3BtreeGetAutoVacuum(pBt) : SQLITE_DEFAULT_AUTOVACUUM;
      returnSingleInt(pParse, "auto_vacuum", auto_vacuum);
    }else{
      int eAuto = getAutoVacuum(zRight);
      db->nextAutovac = eAuto;
      if( eAuto>=0 ){
        /* Call SetAutoVacuum() to set initialize the internal auto and
        ** incr-vacuum flags. This is required in case this connection
        ** creates the database file. It is important that it is created
        ** as an auto-vacuum capable db.
        */
        int rc = sqlite3BtreeSetAutoVacuum(pBt, eAuto);

Changes to src/sqliteInt.h.

7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
...
477
478
479
480
481
482
483

484
485
486
487
488
489
490
**    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.
**
*************************************************************************
** Internal interface definitions for SQLite.
**
** @(#) $Id: sqliteInt.h,v 1.622 2007/11/29 17:05:18 danielk1977 Exp $
*/
#ifndef _SQLITEINT_H_
#define _SQLITEINT_H_

/*
** The macro unlikely() is a hint that surrounds a boolean
** expression that is usually false.  Macro likely() surrounds
................................................................................
  int flags;                    /* Miscellanous flags. See below */
  int openFlags;                /* Flags passed to sqlite3_vfs.xOpen() */
  int errCode;                  /* Most recent error code (SQLITE_*) */
  int errMask;                  /* & result codes with this before returning */
  u8 autoCommit;                /* The auto-commit flag. */
  u8 temp_store;                /* 1: file 2: memory 0: default */
  u8 mallocFailed;              /* True if we have seen a malloc failure */

  int nTable;                   /* Number of tables in the database */
  CollSeq *pDfltColl;           /* The default collating sequence (BINARY) */
  i64 lastRowid;                /* ROWID of most recent insert (see above) */
  i64 priorNewRowid;            /* Last randomly generated ROWID */
  int magic;                    /* Magic number for detect library misuse */
  int nChange;                  /* Value returned by sqlite3_changes() */
  int nTotalChange;             /* Value returned by sqlite3_total_changes() */







|







 







>







7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
...
477
478
479
480
481
482
483
484
485
486
487
488
489
490
491
**    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.
**
*************************************************************************
** Internal interface definitions for SQLite.
**
** @(#) $Id: sqliteInt.h,v 1.623 2007/12/05 01:38:24 drh Exp $
*/
#ifndef _SQLITEINT_H_
#define _SQLITEINT_H_

/*
** The macro unlikely() is a hint that surrounds a boolean
** expression that is usually false.  Macro likely() surrounds
................................................................................
  int flags;                    /* Miscellanous flags. See below */
  int openFlags;                /* Flags passed to sqlite3_vfs.xOpen() */
  int errCode;                  /* Most recent error code (SQLITE_*) */
  int errMask;                  /* & result codes with this before returning */
  u8 autoCommit;                /* The auto-commit flag. */
  u8 temp_store;                /* 1: file 2: memory 0: default */
  u8 mallocFailed;              /* True if we have seen a malloc failure */
  char nextAutovac;             /* Autovac setting after VACUUM if >=0 */
  int nTable;                   /* Number of tables in the database */
  CollSeq *pDfltColl;           /* The default collating sequence (BINARY) */
  i64 lastRowid;                /* ROWID of most recent insert (see above) */
  i64 priorNewRowid;            /* Last randomly generated ROWID */
  int magic;                    /* Magic number for detect library misuse */
  int nChange;                  /* Value returned by sqlite3_changes() */
  int nTotalChange;             /* Value returned by sqlite3_total_changes() */

Changes to src/vacuum.c.

10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
...
121
122
123
124
125
126
127

128
129
130
131
132
133
134
135
**
*************************************************************************
** This file contains code used to implement the VACUUM command.
**
** Most of the code in this file may be omitted by defining the
** SQLITE_OMIT_VACUUM macro.
**
** $Id: vacuum.c,v 1.74 2007/10/20 20:58:57 drh Exp $
*/
#include "sqliteInt.h"
#include "vdbeInt.h"

#if !defined(SQLITE_OMIT_VACUUM) && !defined(SQLITE_OMIT_ATTACH)
/*
** Execute zSql on database db. Return an error code.
................................................................................
  assert( sqlite3BtreeGetPageSize(pTemp)==sqlite3BtreeGetPageSize(pMain) );
  rc = execSql(db, "PRAGMA vacuum_db.synchronous=OFF");
  if( rc!=SQLITE_OK ){
    goto end_of_vacuum;
  }

#ifndef SQLITE_OMIT_AUTOVACUUM

  sqlite3BtreeSetAutoVacuum(pTemp, sqlite3BtreeGetAutoVacuum(pMain));
#endif

  /* Begin a transaction */
  rc = execSql(db, "BEGIN EXCLUSIVE;");
  if( rc!=SQLITE_OK ) goto end_of_vacuum;

  /* Query the schema of the main database. Create a mirror schema







|







 







>
|







10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
...
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
**
*************************************************************************
** This file contains code used to implement the VACUUM command.
**
** Most of the code in this file may be omitted by defining the
** SQLITE_OMIT_VACUUM macro.
**
** $Id: vacuum.c,v 1.75 2007/12/05 01:38:24 drh Exp $
*/
#include "sqliteInt.h"
#include "vdbeInt.h"

#if !defined(SQLITE_OMIT_VACUUM) && !defined(SQLITE_OMIT_ATTACH)
/*
** Execute zSql on database db. Return an error code.
................................................................................
  assert( sqlite3BtreeGetPageSize(pTemp)==sqlite3BtreeGetPageSize(pMain) );
  rc = execSql(db, "PRAGMA vacuum_db.synchronous=OFF");
  if( rc!=SQLITE_OK ){
    goto end_of_vacuum;
  }

#ifndef SQLITE_OMIT_AUTOVACUUM
  sqlite3BtreeSetAutoVacuum(pTemp, db->nextAutovac>=0 ? db->nextAutovac :
                                           sqlite3BtreeGetAutoVacuum(pMain));
#endif

  /* Begin a transaction */
  rc = execSql(db, "BEGIN EXCLUSIVE;");
  if( rc!=SQLITE_OK ) goto end_of_vacuum;

  /* Query the schema of the main database. Create a mirror schema

Changes to test/vacuum2.test.

7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
..
52
53
54
55
56
57
58
59


















































































60
#    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.  The
# focus of this file is testing the VACUUM statement.
#
# $Id: vacuum2.test,v 1.3 2007/07/19 16:35:17 drh Exp $

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

# If the VACUUM statement is disabled in the current build, skip all
# the tests in this file.
#
................................................................................
} [expr {[hexio_get_int [hexio_read test.db 24 4]]+3}]
do_test vacuum2-2.1 {
  execsql {
    VACUUM
  }
  hexio_get_int [hexio_read test.db 24 4]
} [expr {[hexio_get_int [hexio_read test.db 24 4]]+1}]



















































































finish_test







|







 








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

7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
..
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
#    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.  The
# focus of this file is testing the VACUUM statement.
#
# $Id: vacuum2.test,v 1.4 2007/12/05 01:38:24 drh Exp $

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

# If the VACUUM statement is disabled in the current build, skip all
# the tests in this file.
#
................................................................................
} [expr {[hexio_get_int [hexio_read test.db 24 4]]+3}]
do_test vacuum2-2.1 {
  execsql {
    VACUUM
  }
  hexio_get_int [hexio_read test.db 24 4]
} [expr {[hexio_get_int [hexio_read test.db 24 4]]+1}]

############################################################################
# Verify that we can use the auto_vacuum pragma to request a new
# autovacuum setting, do a VACUUM, and the new setting takes effect.
# Make sure this happens correctly even if there are multiple open
# connections to the same database file.
#
sqlite3 db2 test.db
set pageSize [db eval {pragma page_size}]

# Compute a checksum on the content of a database
#
proc cksum {{db db}} {
  set sql "SELECT name, type, sql FROM sqlite_master ORDER BY name, type"
  set txt [$db eval $sql]\n
  set sql "SELECT name FROM sqlite_master WHERE type='table' ORDER BY name"
  foreach tbl [$db eval $sql] {
    append txt [$db eval "SELECT * FROM $tbl"]\n
  }
  foreach prag {default_cache_size} {
    append txt $prag-[$db eval "PRAGMA $prag"]\n
  }
  set cksum [string length $txt]-[md5 $txt]
  return $cksum
}

# We are currently not autovacuuming so the database should be 3 pages
# in size.  1 page for each of sqlite_master, t1, and t2.
#
do_test vacuum2-3.1 {
  execsql {
    INSERT INTO t1 VALUES('hello');
    INSERT INTO t2 VALUES('out there');
  }
  expr {[file size test.db]/$pageSize}
} {3}
set cksum [cksum]
do_test vacuum2-3.2 {
  cksum db2
} $cksum

# Convert the database to an autovacuumed database.
do_test vacuum2-3.3 {
  execsql {
    PRAGMA auto_vacuum=FULL;
    VACUUM;
  }
  expr {[file size test.db]/$pageSize}
} {4}
do_test vacuum2-3.4 {
  cksum db2
} $cksum
do_test vacuum2-3.5 {
  cksum
} $cksum
do_test vacuum2-3.6 {
  execsql {PRAGMA integrity_check} db2
} {ok}
do_test vacuum2-3.7 {
  execsql {PRAGMA integrity_check} db
} {ok}

# Convert the database back to a non-autovacuumed database.
do_test vacuum2-3.13 {
  execsql {
    PRAGMA auto_vacuum=NONE;
    VACUUM;
  }
  expr {[file size test.db]/$pageSize}
} {3}
do_test vacuum2-3.14 {
  cksum db2
} $cksum
do_test vacuum2-3.15 {
  cksum
} $cksum
do_test vacuum2-3.16 {
  execsql {PRAGMA integrity_check} db2
} {ok}
do_test vacuum2-3.17 {
  execsql {PRAGMA integrity_check} db
} {ok}

finish_test