xref: /sqlite-3.40.0/test/pagesize.test (revision dd14ecbc) 
1# 2004 September 2
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# This file implements regression tests for SQLite library.
12# This file implements tests for the page_size PRAGMA.
13#
14# $Id: pagesize.test,v 1.13 2008/08/26 21:07:27 drh Exp $
15
16
17set testdir [file dirname $argv0]
18source $testdir/tester.tcl
19
20# This test script depends entirely on "PRAGMA page_size". So if this
21# pragma is not available, omit the whole file.
22ifcapable !pager_pragmas {
23  finish_test
24  return
25}
26
27do_test pagesize-1.1 {
28  execsql {PRAGMA page_size}
29} 1024
30ifcapable {explain} {
31  do_test pagesize-1.2 {
32    catch {execsql {EXPLAIN PRAGMA page_size}}
33  } 0
34}
35do_test pagesize-1.3 {
36  execsql {
37    CREATE TABLE t1(a);
38    PRAGMA page_size=2048;
39    PRAGMA page_size;
40  }
41} 1024
42
43do_test pagesize-1.4 {
44  db close
45  forcedelete test.db
46  sqlite3 db test.db
47  execsql {
48    PRAGMA page_size=511;
49    PRAGMA page_size;
50  }
51} 1024
52do_test pagesize-1.5 {
53  execsql {
54    PRAGMA page_size=512;
55    PRAGMA page_size;
56  }
57} 512
58if {![info exists SQLITE_MAX_PAGE_SIZE] || $SQLITE_MAX_PAGE_SIZE>=8192} {
59  do_test pagesize-1.6 {
60    execsql {
61      PRAGMA page_size=8192;
62      PRAGMA page_size;
63    }
64  } 8192
65  do_test pagesize-1.7 {
66    execsql {
67      PRAGMA page_size=65537;
68      PRAGMA page_size;
69    }
70  } 8192
71  do_test pagesize-1.8 {
72    execsql {
73      PRAGMA page_size=1234;
74      PRAGMA page_size
75    }
76  } 8192
77}
78foreach PGSZ {512 2048 4096 8192} {
79  if {[info exists SQLITE_MAX_PAGE_SIZE]
80           && $SQLITE_MAX_PAGE_SIZE<$PGSZ} continue
81  ifcapable memorydb {
82    do_test pagesize-2.$PGSZ.0.1 {
83      db close
84      sqlite3 db :memory:
85      execsql "PRAGMA page_size=$PGSZ;"
86      execsql {PRAGMA page_size}
87    } $PGSZ
88    do_test pagesize-2.$PGSZ.0.2 {
89      execsql {CREATE TABLE t1(x UNIQUE, y UNIQUE, z UNIQUE)}
90      execsql {PRAGMA page_size}
91    } $PGSZ
92    do_test pagesize-2.$PGSZ.0.3 {
93      execsql {
94        INSERT INTO t1 VALUES(1,2,3);
95        INSERT INTO t1 VALUES(2,3,4);
96        SELECT * FROM t1;
97      }
98    } {1 2 3 2 3 4}
99  }
100  do_test pagesize-2.$PGSZ.1 {
101    db close
102    forcedelete test.db
103    sqlite3 db test.db
104    execsql "PRAGMA page_size=$PGSZ"
105    execsql {
106      CREATE TABLE t1(x);
107      PRAGMA page_size;
108    }
109  } $PGSZ
110  do_test pagesize-2.$PGSZ.2 {
111    db close
112    sqlite3 db test.db
113    execsql {
114      PRAGMA page_size
115    }
116  } $PGSZ
117  do_test pagesize-2.$PGSZ.3 {
118    file size test.db
119  } [expr {$PGSZ*($AUTOVACUUM?3:2)}]
120  ifcapable {vacuum} {
121    do_test pagesize-2.$PGSZ.4 {
122      execsql {VACUUM}
123    } {}
124  }
125  integrity_check pagesize-2.$PGSZ.5
126  do_test pagesize-2.$PGSZ.6 {
127    db close
128    sqlite3 db test.db
129    execsql {PRAGMA page_size}
130  } $PGSZ
131  do_test pagesize-2.$PGSZ.7 {
132    execsql {
133      INSERT INTO t1 VALUES(randstr(10,9000));
134      INSERT INTO t1 VALUES(randstr(10,9000));
135      INSERT INTO t1 VALUES(randstr(10,9000));
136      BEGIN;
137      INSERT INTO t1 SELECT x||x FROM t1;
138      INSERT INTO t1 SELECT x||x FROM t1;
139      INSERT INTO t1 SELECT x||x FROM t1;
140      INSERT INTO t1 SELECT x||x FROM t1;
141      SELECT count(*) FROM t1;
142    }
143  } 48
144  do_test pagesize-2.$PGSZ.8 {
145    execsql {
146      ROLLBACK;
147      SELECT count(*) FROM t1;
148    }
149  } 3
150  integrity_check pagesize-2.$PGSZ.9
151  do_test pagesize-2.$PGSZ.10 {
152    db close
153    sqlite3 db test.db
154    execsql {PRAGMA page_size}
155  } $PGSZ
156  do_test pagesize-2.$PGSZ.11 {
157    execsql {
158      INSERT INTO t1 SELECT x||x FROM t1;
159      INSERT INTO t1 SELECT x||x FROM t1;
160      INSERT INTO t1 SELECT x||x FROM t1;
161      INSERT INTO t1 SELECT x||x FROM t1;
162      INSERT INTO t1 SELECT x||x FROM t1;
163      INSERT INTO t1 SELECT x||x FROM t1;
164      SELECT count(*) FROM t1;
165    }
166  } 192
167  do_test pagesize-2.$PGSZ.12 {
168    execsql {
169      BEGIN;
170      DELETE FROM t1 WHERE rowid%5!=0;
171      SELECT count(*) FROM t1;
172    }
173  } 38
174  do_test pagesize-2.$PGSZ.13 {
175    execsql {
176      ROLLBACK;
177      SELECT count(*) FROM t1;
178    }
179  } 192
180  integrity_check pagesize-2.$PGSZ.14
181  do_test pagesize-2.$PGSZ.15 {
182    execsql {DELETE FROM t1 WHERE rowid%5!=0}
183    ifcapable {vacuum} {execsql VACUUM}
184    execsql {SELECT count(*) FROM t1}
185  } 38
186  do_test pagesize-2.$PGSZ.16 {
187    execsql {DROP TABLE t1}
188    ifcapable {vacuum} {execsql VACUUM}
189  } {}
190  integrity_check pagesize-2.$PGSZ.17
191
192  db close
193  forcedelete test.db
194  sqlite3 db test.db
195  do_test pagesize-2.$PGSZ.30 {
196    execsql "
197      CREATE TABLE t1(x);
198      PRAGMA temp.page_size=$PGSZ;
199      CREATE TEMP TABLE t2(y);
200      PRAGMA main.page_size;
201      PRAGMA temp.page_size;
202    "
203  } [list 1024 $PGSZ]
204
205  db close
206  forcedelete test.db
207  sqlite3 db test.db
208  do_test pagesize-2.$PGSZ.40 {
209    execsql "
210      PRAGMA page_size=$PGSZ;
211      CREATE TABLE t1(x);
212      CREATE TEMP TABLE t2(y);
213      PRAGMA main.page_size;
214      PRAGMA temp.page_size;
215    "
216  } [list $PGSZ $PGSZ]
217}
218
219reset_db
220do_execsql_test pagesize-3.1 {
221  BEGIN;
222  SELECT * FROM sqlite_master;
223  PRAGMA page_size=2048;
224  PRAGMA main.page_size;
225} {1024}
226do_execsql_test pagesize-3.2 {
227  CREATE TABLE t1(x);
228  COMMIT;
229}
230do_execsql_test pagesize-3.3 {
231  BEGIN;
232    PRAGMA page_size = 2048;
233  COMMIT;
234  PRAGMA main.page_size;
235} {1024}
236
237finish_test
238