/ Check-in [226a9056]
Login
Home Timeline Branches Tags Tickets Wiki More...
SQLite training in Houston TX on 2019-11-05 (details)
Part of the 2019 Tcl Conference

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

Overview
Comment:All the page_size pragma to change the page size on a new :memory: database, but not a vacuumed :memory: database. Ticket #3335 (CVS 5617)
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1: 226a9056783247679fcf442e10807a1f2707f463
User & Date: drh 2008-08-26 21:07:27
Context
2008-08-26
21:33
In the TCL interface, disable the authorizer when during a BEGIN, COMMIT, or ROLLBACK associated with the transaction method. Ticket #3336. (CVS 5618) check-in: 7e1032ab user: drh tags: trunk
21:07
All the page_size pragma to change the page size on a new :memory: database, but not a vacuumed :memory: database. Ticket #3335 (CVS 5617) check-in: 226a9056 user: drh tags: trunk
19:08
Disable some very expensive asserts in pcache.c that are too slow for all.test. (CVS 5616) check-in: 555dad90 user: danielk1977 tags: trunk
Changes
Hide Diffs Unified Diffs Ignore Whitespace Patch

Changes to src/pager.c. 

14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
....
1941
1942
1943
1944
1945
1946
1947

1948
1949
1950
1951
1952
1953
1954
1955
1956
1957
1958
1959
1960
1961
1962
1963
....
3568
3569
3570
3571
3572
3573
3574



3575

3576
3577
3578
3579
3580
3581
3582

** The pager is used to access a database disk file.  It implements
** atomic commit and rollback through the use of a journal file that
** is separate from the database file.  The pager also implements file
** locking to prevent two processes from writing the same database
** file simultaneously, or one process from reading the database while
** another is writing.
**
** @(#) $Id: pager.c,v 1.480 2008/08/26 18:05:48 danielk1977 Exp $
*/
#ifndef SQLITE_OMIT_DISKIO
#include "sqliteInt.h"

/*
** Macros for troubleshooting.  Normally turned off
*/
................................................................................
** value before returning.
*/
int sqlite3PagerSetPagesize(Pager *pPager, u16 *pPageSize){
  int rc = SQLITE_OK;
  u16 pageSize = *pPageSize;
  assert( pageSize==0 || (pageSize>=512 && pageSize<=SQLITE_MAX_PAGE_SIZE) );
  if( pageSize && pageSize!=pPager->pageSize 

   && !pPager->memDb && sqlite3PcacheRefCount(pPager->pPCache)==0 
  ){
    char *pNew = (char *)sqlite3PageMalloc(pageSize);
    if( !pNew ){
      rc = SQLITE_NOMEM;
    }else{
      pager_reset(pPager);
      pPager->pageSize = pageSize;
      setSectorSize(pPager);
      sqlite3PageFree(pPager->pTmpSpace);
      pPager->pTmpSpace = pNew;
      sqlite3PcacheSetPageSize(pPager->pPCache, pageSize);
    }
  }
  *pPageSize = pPager->pageSize;
  return rc;
................................................................................
}

/*
** Sync the pager file to disk.
*/
int sqlite3PagerSync(Pager *pPager){
  int rc;



  rc = sqlite3OsSync(pPager->fd, pPager->sync_flags);

  return rc;
}

/*
** Sync the database file for the pager pPager. zMaster points to the name
** of a master journal file that should be written into the individual
** journal file. zMaster may be NULL, which is interpreted as no master








|







 







>
|







|







 







>
>
>
|
>








14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
....
1941
1942
1943
1944
1945
1946
1947
1948
1949
1950
1951
1952
1953
1954
1955
1956
1957
1958
1959
1960
1961
1962
1963
1964
....
3569
3570
3571
3572
3573
3574
3575
3576
3577
3578
3579
3580
3581
3582
3583
3584
3585
3586
3587

** The pager is used to access a database disk file.  It implements
** atomic commit and rollback through the use of a journal file that
** is separate from the database file.  The pager also implements file
** locking to prevent two processes from writing the same database
** file simultaneously, or one process from reading the database while
** another is writing.
**
** @(#) $Id: pager.c,v 1.481 2008/08/26 21:07:27 drh Exp $
*/
#ifndef SQLITE_OMIT_DISKIO
#include "sqliteInt.h"

/*
** Macros for troubleshooting.  Normally turned off
*/
................................................................................
** value before returning.
*/
int sqlite3PagerSetPagesize(Pager *pPager, u16 *pPageSize){
  int rc = SQLITE_OK;
  u16 pageSize = *pPageSize;
  assert( pageSize==0 || (pageSize>=512 && pageSize<=SQLITE_MAX_PAGE_SIZE) );
  if( pageSize && pageSize!=pPager->pageSize 
   && (pPager->memDb==0 || pPager->dbSize==0)
   && sqlite3PcacheRefCount(pPager->pPCache)==0 
  ){
    char *pNew = (char *)sqlite3PageMalloc(pageSize);
    if( !pNew ){
      rc = SQLITE_NOMEM;
    }else{
      pager_reset(pPager);
      pPager->pageSize = pageSize;
      if( !pPager->memDb ) setSectorSize(pPager);
      sqlite3PageFree(pPager->pTmpSpace);
      pPager->pTmpSpace = pNew;
      sqlite3PcacheSetPageSize(pPager->pPCache, pageSize);
    }
  }
  *pPageSize = pPager->pageSize;
  return rc;
................................................................................
}

/*
** Sync the pager file to disk.
*/
int sqlite3PagerSync(Pager *pPager){
  int rc;
  if( MEMDB ){
    rc = SQLITE_OK;
  }else{
    rc = sqlite3OsSync(pPager->fd, pPager->sync_flags);
  }
  return rc;
}

/*
** Sync the database file for the pager pPager. zMaster points to the name
** of a master journal file that should be written into the individual
** journal file. zMaster may be NULL, which is interpreted as no master

Changes to src/vacuum.c. 

10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
..
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
...
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147

**
*************************************************************************
** 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.82 2008/08/23 16:17:56 danielk1977 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.
................................................................................

/*
** This routine implements the OP_Vacuum opcode of the VDBE.
*/
int sqlite3RunVacuum(char **pzErrMsg, sqlite3 *db){
  int rc = SQLITE_OK;     /* Return code from service routines */
  Btree *pMain;           /* The database being vacuumed */

  Btree *pTemp;           /* The temporary database we vacuum into */
  char *zSql = 0;         /* SQL statements */
  int saved_flags;        /* Saved value of the db->flags */
  int saved_nChange;      /* Saved value of db->nChange */
  int saved_nTotalChange; /* Saved value of db->nTotalChange */
  Db *pDb = 0;            /* Database to detach at end of vacuum */

  int nRes;

  /* Save the current value of the write-schema flag before setting it. */
  saved_flags = db->flags;
  saved_nChange = db->nChange;
  saved_nTotalChange = db->nTotalChange;
  db->flags |= SQLITE_WriteSchema | SQLITE_IgnoreChecks;
................................................................................

  if( !db->autoCommit ){
    sqlite3SetString(pzErrMsg, db, "cannot VACUUM from within a transaction");
    rc = SQLITE_ERROR;
    goto end_of_vacuum;
  }
  pMain = db->aDb[0].pBt;



  /* Attach the temporary database as 'vacuum_db'. The synchronous pragma
  ** can be set to 'off' for this file, as it is not recovered if a crash
  ** occurs anyway. The integrity of the database is maintained by a
  ** (possibly synchronous) transaction opened on the main database before
  ** sqlite3BtreeCopyFile() is called.
  **
................................................................................
    char *zKey;
    sqlite3CodecGetKey(db, 0, (void**)&zKey, &nKey);
    if( nKey ) db->nextPagesize = 0;
  }
#endif

  if( sqlite3BtreeSetPageSize(pTemp, sqlite3BtreeGetPageSize(pMain), nRes)
   || sqlite3BtreeSetPageSize(pTemp, db->nextPagesize, nRes)
   || db->mallocFailed 
  ){
    rc = SQLITE_NOMEM;
    goto end_of_vacuum;
  }
  rc = execSql(db, "PRAGMA vacuum_db.synchronous=OFF");
  if( rc!=SQLITE_OK ){








|







 







>






>







 







>
>







 







|








10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
..
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
...
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151

**
*************************************************************************
** 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.83 2008/08/26 21:07:27 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.
................................................................................

/*
** This routine implements the OP_Vacuum opcode of the VDBE.
*/
int sqlite3RunVacuum(char **pzErrMsg, sqlite3 *db){
  int rc = SQLITE_OK;     /* Return code from service routines */
  Btree *pMain;           /* The database being vacuumed */
  Pager *pMainPager;      /* Pager for database being vacuumed */
  Btree *pTemp;           /* The temporary database we vacuum into */
  char *zSql = 0;         /* SQL statements */
  int saved_flags;        /* Saved value of the db->flags */
  int saved_nChange;      /* Saved value of db->nChange */
  int saved_nTotalChange; /* Saved value of db->nTotalChange */
  Db *pDb = 0;            /* Database to detach at end of vacuum */
  int isMemDb;            /* True is vacuuming a :memory: database */
  int nRes;

  /* Save the current value of the write-schema flag before setting it. */
  saved_flags = db->flags;
  saved_nChange = db->nChange;
  saved_nTotalChange = db->nTotalChange;
  db->flags |= SQLITE_WriteSchema | SQLITE_IgnoreChecks;
................................................................................

  if( !db->autoCommit ){
    sqlite3SetString(pzErrMsg, db, "cannot VACUUM from within a transaction");
    rc = SQLITE_ERROR;
    goto end_of_vacuum;
  }
  pMain = db->aDb[0].pBt;
  pMainPager = sqlite3BtreePager(pMain);
  isMemDb = sqlite3PagerFile(pMainPager)->pMethods==0;

  /* Attach the temporary database as 'vacuum_db'. The synchronous pragma
  ** can be set to 'off' for this file, as it is not recovered if a crash
  ** occurs anyway. The integrity of the database is maintained by a
  ** (possibly synchronous) transaction opened on the main database before
  ** sqlite3BtreeCopyFile() is called.
  **
................................................................................
    char *zKey;
    sqlite3CodecGetKey(db, 0, (void**)&zKey, &nKey);
    if( nKey ) db->nextPagesize = 0;
  }
#endif

  if( sqlite3BtreeSetPageSize(pTemp, sqlite3BtreeGetPageSize(pMain), nRes)
   || (!isMemDb && sqlite3BtreeSetPageSize(pTemp, db->nextPagesize, nRes))
   || db->mallocFailed 
  ){
    rc = SQLITE_NOMEM;
    goto end_of_vacuum;
  }
  rc = execSql(db, "PRAGMA vacuum_db.synchronous=OFF");
  if( rc!=SQLITE_OK ){

Changes to test/pagesize.test. 

7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
..
75
76
77
78
79
80
81
82
83
84
85
86
87











88
89
90
91
92
93
94

#    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.12 2007/04/06 21:42:22 drh Exp $


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

# This test script depends entirely on "PRAGMA page_size". So if this
# pragma is not available, omit the whole file.
................................................................................
    }
  } 8192
}  
foreach PGSZ {512 2048 4096 8192} {
  if {[info exists SQLITE_MAX_PAGE_SIZE]
           && $SQLITE_MAX_PAGE_SIZE<$PGSZ} continue
  ifcapable memorydb {
    do_test pagesize-2.$PGSZ.0 {
      db close
      sqlite3 db :memory:
      execsql "PRAGMA page_size=$PGSZ;"
      execsql {PRAGMA page_size}
    } 1024











  }
  do_test pagesize-2.$PGSZ.1 {
    db close
    file delete -force test.db
    sqlite3 db test.db
    execsql "PRAGMA page_size=$PGSZ"
    execsql {








|







 







|




|
>
>
>
>
>
>
>
>
>
>
>








7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
..
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

#    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.13 2008/08/26 21:07:27 drh Exp $


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

# This test script depends entirely on "PRAGMA page_size". So if this
# pragma is not available, omit the whole file.
................................................................................
    }
  } 8192
}  
foreach PGSZ {512 2048 4096 8192} {
  if {[info exists SQLITE_MAX_PAGE_SIZE]
           && $SQLITE_MAX_PAGE_SIZE<$PGSZ} continue
  ifcapable memorydb {
    do_test pagesize-2.$PGSZ.0.1 {
      db close
      sqlite3 db :memory:
      execsql "PRAGMA page_size=$PGSZ;"
      execsql {PRAGMA page_size}
    } $PGSZ
    do_test pagesize-2.$PGSZ.0.2 {
      execsql {CREATE TABLE t1(x UNIQUE, y UNIQUE, z UNIQUE)}
      execsql {PRAGMA page_size}
    } $PGSZ
    do_test pagesize-2.$PGSZ.0.3 {
      execsql {
        INSERT INTO t1 VALUES(1,2,3);
        INSERT INTO t1 VALUES(2,3,4);
        SELECT * FROM t1;
      }
    } {1 2 3 2 3 4}
  }
  do_test pagesize-2.$PGSZ.1 {
    db close
    file delete -force test.db
    sqlite3 db test.db
    execsql "PRAGMA page_size=$PGSZ"
    execsql {

Changes to test/vacuum3.test. 

8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
...
231
232
233
234
235
236
237

238















239
240
241
242
243
244
245

#    May you share freely, never taking more than you give.
#
#***********************************************************************
# This file implements regression tests for SQLite library.  The
# focus of this file is changing the database page size using a 
# VACUUM statement.
#
# $Id: vacuum3.test,v 1.8 2008/08/23 16:17:56 danielk1977 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.
#
................................................................................
  execsql {
    PRAGMA page_size=1024;
    VACUUM;
  }
  execsql { SELECT * FROM abc } db2
} {1 2 3 4 5 6}


db2 close
















set create_database_sql {
  BEGIN; 
  CREATE TABLE t1(a, b, c); 
  INSERT INTO t1 VALUES(1, randstr(50,50), randstr(50,50)); 
  INSERT INTO t1 SELECT a+2, b||'-'||rowid, c||'-'||rowid FROM t1; 
  INSERT INTO t1 SELECT a+4, b||'-'||rowid, c||'-'||rowid FROM t1;








|







 







>

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








8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
...
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261

#    May you share freely, never taking more than you give.
#
#***********************************************************************
# This file implements regression tests for SQLite library.  The
# focus of this file is changing the database page size using a 
# VACUUM statement.
#
# $Id: vacuum3.test,v 1.9 2008/08/26 21:07:27 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.
#
................................................................................
  execsql {
    PRAGMA page_size=1024;
    VACUUM;
  }
  execsql { SELECT * FROM abc } db2
} {1 2 3 4 5 6}

# Unable to change the page-size of an in-memory using vacuum.
db2 close
sqlite3 db2 :memory:
do_test vacuum3-5.1 {
  db2 eval {
    CREATE TABLE t1(x);
    INSERT INTO t1 VALUES(1234);
    PRAGMA page_size=4096;
    VACUUM;
    SELECT * FROM t1;
  }
} {1234}
do_test vacuum3-5.2 {
  db2 eval {
    PRAGMA page_size
  }
} {1024}

set create_database_sql {
  BEGIN; 
  CREATE TABLE t1(a, b, c); 
  INSERT INTO t1 VALUES(1, randstr(50,50), randstr(50,50)); 
  INSERT INTO t1 SELECT a+2, b||'-'||rowid, c||'-'||rowid FROM t1; 
  INSERT INTO t1 SELECT a+4, b||'-'||rowid, c||'-'||rowid FROM t1;

This page was generated in about 0.036s by Fossil 2.10 [9d9ef82234] 2019-10-04 21:41:13