/ Check-in [fa82beca]
Login
Overview
Comment:Make VACUUM work when the page size is different than the default 1024. Ticket #890. (CVS 1939)
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1:fa82becae7e41c47a6387061932f692c6f9f472e
User & Date: drh 2004-09-05 00:33:43
Context
2004-09-05
23:23
Comment changes and minor code cleanup. (CVS 1940) check-in: dfa9ea89 user: drh tags: trunk
00:33
Make VACUUM work when the page size is different than the default 1024. Ticket #890. (CVS 1939) check-in: fa82beca user: drh tags: trunk
2004-09-03
23:32
Fix a comment. (CVS 1938) check-in: af44ddee user: drh tags: trunk
Changes
Hide Diffs Unified Diffs Ignore Whitespace Patch

Changes to src/btree.c.

5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
....
1141
1142
1143
1144
1145
1146
1147
1148
1149
1150
1151
1152
1153
1154
1155
....
1221
1222
1223
1224
1225
1226
1227
1228
1229
1230
1231
1232
1233
1234
1235
....
1279
1280
1281
1282
1283
1284
1285

1286
1287
1288
1289
1290
1291
1292
** 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.
**
*************************************************************************
** $Id: btree.c,v 1.187 2004/09/03 23:32:19 drh Exp $
**
** This file implements a external (disk-based) database using BTrees.
** For a detailed discussion of BTrees, refer to
**
**     Donald E. Knuth, THE ART OF COMPUTER PROGRAMMING, Volume 3:
**     "Sorting And Searching", pages 473-480. Addison-Wesley
**     Publishing Company, Reading, Massachusetts.
................................................................................
int sqlite3BtreeSetPageSize(Btree *pBt, int pageSize, int nReserve){
  if( pBt->pageSizeFixed ){
    return SQLITE_READONLY;
  }
  if( nReserve<0 ){
    nReserve = pBt->pageSize - pBt->usableSize;
  }
  if( pageSize>512 && pageSize<SQLITE_MAX_PAGE_SIZE ){
    pBt->pageSize = pageSize;
    sqlite3pager_set_pagesize(pBt->pPager, pageSize);
  }
  pBt->usableSize = pBt->pageSize - nReserve;
  return SQLITE_OK;
}

................................................................................
  pBt->maxLeaf = pBt->usableSize - 35;
  pBt->minLeaf = (pBt->usableSize-12)*pBt->minLeafFrac/255 - 23;
  if( pBt->minLocal>pBt->maxLocal || pBt->maxLocal<0 ){
    goto page1_init_failed;
  }
  assert( pBt->maxLeaf + 23 <= MX_CELL_SIZE(pBt) );
  pBt->pPage1 = pPage1;
  pBt->pageSizeFixed = 1;
  return SQLITE_OK;

page1_init_failed:
  releasePage(pPage1);
  pBt->pPage1 = 0;
  return rc;
}
................................................................................
  data[19] = 1;
  data[20] = pBt->pageSize - pBt->usableSize;
  data[21] = pBt->maxEmbedFrac;
  data[22] = pBt->minEmbedFrac;
  data[23] = pBt->minLeafFrac;
  memset(&data[24], 0, 100-24);
  zeroPage(pP1, PTF_INTKEY|PTF_LEAF|PTF_LEAFDATA );

  return SQLITE_OK;
}

/*
** Attempt to start a new transaction. A write-transaction
** is started if the second argument is true, otherwise a read-
** transaction.







|







 







|







 







<







 







>







5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
....
1141
1142
1143
1144
1145
1146
1147
1148
1149
1150
1151
1152
1153
1154
1155
....
1221
1222
1223
1224
1225
1226
1227

1228
1229
1230
1231
1232
1233
1234
....
1278
1279
1280
1281
1282
1283
1284
1285
1286
1287
1288
1289
1290
1291
1292
** 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.
**
*************************************************************************
** $Id: btree.c,v 1.188 2004/09/05 00:33:43 drh Exp $
**
** This file implements a external (disk-based) database using BTrees.
** For a detailed discussion of BTrees, refer to
**
**     Donald E. Knuth, THE ART OF COMPUTER PROGRAMMING, Volume 3:
**     "Sorting And Searching", pages 473-480. Addison-Wesley
**     Publishing Company, Reading, Massachusetts.
................................................................................
int sqlite3BtreeSetPageSize(Btree *pBt, int pageSize, int nReserve){
  if( pBt->pageSizeFixed ){
    return SQLITE_READONLY;
  }
  if( nReserve<0 ){
    nReserve = pBt->pageSize - pBt->usableSize;
  }
  if( pageSize>=512 && pageSize<=SQLITE_MAX_PAGE_SIZE ){
    pBt->pageSize = pageSize;
    sqlite3pager_set_pagesize(pBt->pPager, pageSize);
  }
  pBt->usableSize = pBt->pageSize - nReserve;
  return SQLITE_OK;
}

................................................................................
  pBt->maxLeaf = pBt->usableSize - 35;
  pBt->minLeaf = (pBt->usableSize-12)*pBt->minLeafFrac/255 - 23;
  if( pBt->minLocal>pBt->maxLocal || pBt->maxLocal<0 ){
    goto page1_init_failed;
  }
  assert( pBt->maxLeaf + 23 <= MX_CELL_SIZE(pBt) );
  pBt->pPage1 = pPage1;

  return SQLITE_OK;

page1_init_failed:
  releasePage(pPage1);
  pBt->pPage1 = 0;
  return rc;
}
................................................................................
  data[19] = 1;
  data[20] = pBt->pageSize - pBt->usableSize;
  data[21] = pBt->maxEmbedFrac;
  data[22] = pBt->minEmbedFrac;
  data[23] = pBt->minLeafFrac;
  memset(&data[24], 0, 100-24);
  zeroPage(pP1, PTF_INTKEY|PTF_LEAF|PTF_LEAFDATA );
  pBt->pageSizeFixed = 1;
  return SQLITE_OK;
}

/*
** Attempt to start a new transaction. A write-transaction
** is started if the second argument is true, otherwise a read-
** transaction.

Changes to src/vacuum.c.

10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
..
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
...
146
147
148
149
150
151
152




153
154
155
156
157
158
159
160
...
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
**
*************************************************************************
** 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.29 2004/09/02 15:27:42 drh Exp $
*/
#include "sqliteInt.h"
#include "os.h"

#if !defined(SQLITE_OMIT_VACUUM) || SQLITE_OMIT_VACUUM
/*
** Generate a random name of 20 character in length.
................................................................................
int sqlite3RunVacuum(char **pzErrMsg, sqlite *db){
  int rc = SQLITE_OK;     /* Return code from service routines */
#if !defined(SQLITE_OMIT_VACUUM) || SQLITE_OMIT_VACUUM
  const char *zFilename;  /* full pathname of the database file */
  int nFilename;          /* number of characters  in zFilename[] */
  char *zTemp = 0;        /* a temporary file in same directory as zFilename */
  int i;                  /* Loop counter */

  Btree *pTemp;
  char *zSql = 0;

  if( !db->autoCommit ){
    sqlite3SetString(pzErrMsg, "cannot VACUUM from within a transaction", 
       (char*)0);
    rc = SQLITE_ERROR;
    goto end_of_vacuum;
  }

  /* Get the full pathname of the database file and create a
  ** temporary filename in the same directory as the original file.
  */

  zFilename = sqlite3BtreeGetFilename(db->aDb[0].pBt);
  assert( zFilename );
  if( zFilename[0]=='\0' ){
    /* The in-memory database. Do nothing. Return directly to avoid causing
    ** an error trying to DETACH the vacuum_db (which never got attached)
    ** in the exit-handler.
    */
    return SQLITE_OK;
................................................................................
    rc = SQLITE_NOMEM;
    goto end_of_vacuum;
  }
  rc = execSql(db, zSql);
  sqliteFree(zSql);
  zSql = 0;
  if( rc!=SQLITE_OK ) goto end_of_vacuum;




  execSql(db, "PRAGMA vacuum_db.synchronous = off;");

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

  /* Query the schema of the main database. Create a mirror schema
  ** in the temporary database.
................................................................................
  ** transaction open on the vacuum database, but not on the main database.
  ** Open a btree level transaction on the main database. This allows a
  ** call to sqlite3BtreeCopyFile(). The main database btree level
  ** transaction is then committed, so the SQL level never knows it was
  ** opened for writing. This way, the SQL transaction used to create the
  ** temporary database never needs to be committed.
  */
  pTemp = db->aDb[db->nDb-1].pBt;
  if( sqlite3BtreeIsInTrans(pTemp) ){
    Btree *pMain = db->aDb[0].pBt;
    u32 meta;

    assert( 0==sqlite3BtreeIsInTrans(pMain) );
    rc = sqlite3BtreeBeginTrans(pMain, 1);
    if( rc!=SQLITE_OK ) goto end_of_vacuum;

    /* Copy Btree meta values 3 and 4. These correspond to SQL layer meta 







|







 







>













>
|







 







>
>
>
>
|







 







<

<







10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
..
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
...
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
...
208
209
210
211
212
213
214

215

216
217
218
219
220
221
222
**
*************************************************************************
** 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.30 2004/09/05 00:33:43 drh Exp $
*/
#include "sqliteInt.h"
#include "os.h"

#if !defined(SQLITE_OMIT_VACUUM) || SQLITE_OMIT_VACUUM
/*
** Generate a random name of 20 character in length.
................................................................................
int sqlite3RunVacuum(char **pzErrMsg, sqlite *db){
  int rc = SQLITE_OK;     /* Return code from service routines */
#if !defined(SQLITE_OMIT_VACUUM) || SQLITE_OMIT_VACUUM
  const char *zFilename;  /* full pathname of the database file */
  int nFilename;          /* number of characters  in zFilename[] */
  char *zTemp = 0;        /* a temporary file in same directory as zFilename */
  int i;                  /* Loop counter */
  Btree *pMain;           /* The database being vacuumed */
  Btree *pTemp;
  char *zSql = 0;

  if( !db->autoCommit ){
    sqlite3SetString(pzErrMsg, "cannot VACUUM from within a transaction", 
       (char*)0);
    rc = SQLITE_ERROR;
    goto end_of_vacuum;
  }

  /* Get the full pathname of the database file and create a
  ** temporary filename in the same directory as the original file.
  */
  pMain = db->aDb[0].pBt;
  zFilename = sqlite3BtreeGetFilename(pMain);
  assert( zFilename );
  if( zFilename[0]=='\0' ){
    /* The in-memory database. Do nothing. Return directly to avoid causing
    ** an error trying to DETACH the vacuum_db (which never got attached)
    ** in the exit-handler.
    */
    return SQLITE_OK;
................................................................................
    rc = SQLITE_NOMEM;
    goto end_of_vacuum;
  }
  rc = execSql(db, zSql);
  sqliteFree(zSql);
  zSql = 0;
  if( rc!=SQLITE_OK ) goto end_of_vacuum;
  assert( strcmp(db->aDb[db->nDb-1].zName,"vacuum_db")==0 );
  pTemp = db->aDb[db->nDb-1].pBt;
  sqlite3BtreeSetPageSize(pTemp, sqlite3BtreeGetPageSize(pMain), 0);
  assert( sqlite3BtreeGetPageSize(pTemp)==sqlite3BtreeGetPageSize(pMain) );
  execSql(db, "PRAGMA vacuum_db.synchronous=OFF");

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

  /* Query the schema of the main database. Create a mirror schema
  ** in the temporary database.
................................................................................
  ** transaction open on the vacuum database, but not on the main database.
  ** Open a btree level transaction on the main database. This allows a
  ** call to sqlite3BtreeCopyFile(). The main database btree level
  ** transaction is then committed, so the SQL level never knows it was
  ** opened for writing. This way, the SQL transaction used to create the
  ** temporary database never needs to be committed.
  */

  if( sqlite3BtreeIsInTrans(pTemp) ){

    u32 meta;

    assert( 0==sqlite3BtreeIsInTrans(pMain) );
    rc = sqlite3BtreeBeginTrans(pMain, 1);
    if( rc!=SQLITE_OK ) goto end_of_vacuum;

    /* Copy Btree meta values 3 and 4. These correspond to SQL layer meta 

Changes to test/pagesize.test.

7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
..
26
27
28
29
30
31
32

33
34
35
36
37

38






























39
40
41
42

43
44
45
46
47
48
49

50
51






















52




53
















54






55
























56
#    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 page_size PRAGMA.
#
# $Id: pagesize.test,v 1.2 2004/09/02 15:49:08 drh Exp $


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

do_test pagesize-1.1 {
  execsql {PRAGMA page_size}
................................................................................
do_test pagesize-1.3 {
  execsql {
    CREATE TABLE t1(a);
    PRAGMA page_size=2048;
    PRAGMA page_size;
  }
} 1024

do_test pagesize-1.4 {
  db close
  file delete -force test.db
  sqlite3 db test.db
  execsql {

    PRAGMA page_size=2048;






























    CREATE TABLE t1(a);
    PRAGMA page_size;
  }
} 2048

do_test pagesize-1.5 {
  db close
  sqlite3 db test.db
  execsql {
    PRAGMA page_size
  }
} 2048

do_test pagesize-1.6 {
  file size test.db






















} 4096





















































finish_test







|







 







>





>
|
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
|
|
|
<
>
|
|
|
|
|
|
<
>
|
|
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
|
>
>
>
>
|
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
|
>
>
>
>
>
>
|
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>

7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
..
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
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
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
#    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 page_size PRAGMA.
#
# $Id: pagesize.test,v 1.3 2004/09/05 00:33:44 drh Exp $


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

do_test pagesize-1.1 {
  execsql {PRAGMA page_size}
................................................................................
do_test pagesize-1.3 {
  execsql {
    CREATE TABLE t1(a);
    PRAGMA page_size=2048;
    PRAGMA page_size;
  }
} 1024

do_test pagesize-1.4 {
  db close
  file delete -force test.db
  sqlite3 db test.db
  execsql {
    PRAGMA page_size=511;
    PRAGMA page_size;
  }
} 1024
do_test pagesize-1.5 {
  execsql {
    PRAGMA page_size=512;
    PRAGMA page_size;
  }
} 512
do_test pagesize-1.6 {
  execsql {
    PRAGMA page_size=8192;
    PRAGMA page_size;
  }
} 8192
do_test pagesize-1.7 {
  execsql {
    PRAGMA page_size=65537;
    PRAGMA page_size;
  }
} 8192
  


foreach PGSZ {512 2000 2048 3000 4096} {
  do_test pagesize-2.$PGSZ.1 {
    db close
    file delete -force test.db
    sqlite3 db test.db
    execsql "PRAGMA page_size=$PGSZ"
    execsql {
      CREATE TABLE t1(x);
      PRAGMA page_size;
    }

  } $PGSZ
  do_test pagesize-2.$PGSZ.2 {
    db close
    sqlite3 db test.db
    execsql {
      PRAGMA page_size
    }

  } $PGSZ
  do_test pagesize-2.$PGSZ.3 {
    file size test.db
  } [expr {$PGSZ*2}]
  do_test pagesize-2.$PGSZ.4 {
    execsql {VACUUM}
  } {}
  integrity_check pagesize-2.$PGSZ.5
  do_test pagesize-2.$PGSZ.6 {
    db close
    sqlite3 db test.db
    execsql {PRAGMA page_size}
  } $PGSZ
  do_test pagesize-2.$PGSZ.7 {
    execsql {
      INSERT INTO t1 VALUES(randstr(10,9000));
      INSERT INTO t1 VALUES(randstr(10,9000));
      INSERT INTO t1 VALUES(randstr(10,9000));
      BEGIN;
      INSERT INTO t1 SELECT x||x FROM t1;
      INSERT INTO t1 SELECT x||x FROM t1;
      INSERT INTO t1 SELECT x||x FROM t1;
      INSERT INTO t1 SELECT x||x FROM t1;
      SELECT count(*) FROM t1;
    }
  } 48
  do_test pagesize-2.$PGSZ.8 {
    execsql {
      ROLLBACK;
      SELECT count(*) FROM t1;
    }
  } 3
  integrity_check pagesize-2.$PGSZ.9
  do_test pagesize-2.$PGSZ.10 {
    db close
    sqlite3 db test.db
    execsql {PRAGMA page_size}
  } $PGSZ
  do_test pagesize-2.$PGSZ.11 {
    execsql {
      INSERT INTO t1 SELECT x||x FROM t1;
      INSERT INTO t1 SELECT x||x FROM t1;
      INSERT INTO t1 SELECT x||x FROM t1;
      INSERT INTO t1 SELECT x||x FROM t1;
      INSERT INTO t1 SELECT x||x FROM t1;
      INSERT INTO t1 SELECT x||x FROM t1;
      SELECT count(*) FROM t1;
    }
  } 192
  do_test pagesize-2.$PGSZ.12 {
    execsql {
      BEGIN;
      DELETE FROM t1 WHERE rowid%5!=0;
      SELECT count(*) FROM t1;
    }
  } 38
  do_test pagesize-2.$PGSZ.13 {
    execsql {
      ROLLBACK;
      SELECT count(*) FROM t1;
    }
  } 192
  integrity_check pagesize-2.$PGSZ.14
  do_test pagesize-2.$PGSZ.15 {
    execsql {
      DELETE FROM t1 WHERE rowid%5!=0;
      VACUUM;
      SELECT count(*) FROM t1;
    }
  } 38
  do_test pagesize-2.$PGSZ.16 {
    execsql {
      DROP TABLE t1;
      VACUUM;
    }
  } {}
  integrity_check pagesize-2.$PGSZ.17
}

finish_test