Added the default_cache_size and default_synchronous pragmas. Added additional
tests for pragmas. Added a new speedtest script. (CVS 421)
FossilOrigin-Name: 161c0c5f5db66815e4345c9b5f7a600c03a67475
diff --git a/manifest b/manifest
index 42c7d5d..6d1e726 100644
--- a/manifest
+++ b/manifest
@@ -1,5 +1,5 @@
-C Optimizations\sto\sthe\sprocessing\sof\sinteger\scomparisons.\s(CVS\s420)
-D 2002-03-06T03:08:26
+C Added\sthe\sdefault_cache_size\sand\sdefault_synchronous\spragmas.\s\sAdded\sadditional\ntests\sfor\spragmas.\s\sAdded\sa\snew\sspeedtest\sscript.\s(CVS\s421)
+D 2002-03-06T22:01:35
F Makefile.in 50f1b3351df109b5774771350d8c1b8d3640130d
F Makefile.template 89e373b2dad0321df00400fa968dc14b61a03296
F README a4c0ba11354ef6ba0776b400d057c59da47a4cc0
@@ -19,20 +19,20 @@
F publish.sh 5b59f4aff037aafa0e4a3b6fa599495dbd73f360
F sqlite.1 2e2bb0529ef468ade9e4322bd609d0695fb9ded9
F src/TODO af7f3cab0228e34149cf98e073aa83d45878e7e6
-F src/btree.c b7d1b8875113ab4061a6a8cc759f75728ae7daf0
+F src/btree.c 7dd7ddc66459982dd0cb9800958c1f8d65a32d9f
F src/btree.h 8abeabfe6e0b1a990b64fa457592a6482f6674f3
-F src/build.c 0f3c6b6482e0b74284b22661de1b813a0bfb0197
+F src/build.c d01b81f41481e733e27ab2fa8e1bfcc64f24257d
F src/delete.c 577da499162291c1855f0b304b211bffcf9da945
F src/expr.c d2adad05dc2fe112aef364bdb9c0eb8877618bbe
F src/func.c 87516e7dc37190c24af77593931a5d09d797520a
F src/hash.c cc259475e358baaf299b00a2c7370f2b03dda892
F src/hash.h dca065dda89d4575f3176e75e9a3dc0f4b4fb8b9
F src/insert.c 42bfd145efd428d7e5f200dd49ea0b816fc30d79
-F src/main.c 3015c23faeff1709b1528f07c0bfe839284090a8
+F src/main.c b21019084b93fe685a8a25217d01f6958584ae9b
F src/md5.c 52f677bfc590e09f71d07d7e327bd59da738d07c
F src/os.c db969ecd1bcb4fef01b0b541b8b17401b0eb7ed2
F src/os.h a17596ecc7f38a228b83ecdb661fb03ce44726d6
-F src/pager.c e1419353e68c45aaae2555d2000f72de6525faac
+F src/pager.c f136f5ba82c896d500a10b6a2e5caea62abf716b
F src/pager.h 6fddfddd3b73aa8abc081b973886320e3c614f0e
F src/parse.y f7483ccff7b8f16d3655df59775d85b62b06897e
F src/printf.c 300a90554345751f26e1fc0c0333b90a66110a1d
@@ -41,7 +41,7 @@
F src/shell.c b3454229599246b944cdb5b95753af3fca5d8bb0
F src/shell.tcl 27ecbd63dd88396ad16d81ab44f73e6c0ea9d20e
F src/sqlite.h.in a9b5772604265f98f3120573ef29e37b9d917216
-F src/sqliteInt.h 46f41e2f4cf90cdbe002f6b4143ae3f16ae0b872
+F src/sqliteInt.h 6f4a1bea4858089eb516f59562762965c6ef5cb8
F src/table.c 203a09d5d0009eeeb1f670370d52b4ce163a3b52
F src/tclsqlite.c b9cf346e95291cb4c4f1bf5ac1d77db6b8ad023d
F src/test1.c 33efd350dca27c52c58c553c04fd3a6a51f13c1f
@@ -51,10 +51,10 @@
F src/tokenize.c 4b5d30590a744b9bb5605a92d1f620ab2e7e75af
F src/update.c 7dd714a6a7fa47f849ebb36b6d915974d6c6accb
F src/util.c b34cd91387bbfdc79319ea451a7d120cef478120
-F src/vdbe.c fcbe9d3c1b87f18ed3d7f0553f6c4194fb74a85d
+F src/vdbe.c ce375b8948fb3b21da50536b7d063a3071b5c982
F src/vdbe.h f9be1f6e9a336c3ff4d14ea7489ee976e07460cc
F src/where.c 34d91fd5d822c2663caeb023f72d60df316ebf29
-F test/all.test 7a8a8a7a579ed2bb4d8976d55402f21eacd58049
+F test/all.test 6aa106eee4d7127afa5cee97c51a783a79694ead
F test/bigrow.test 8ab252dba108f12ad64e337b0f2ff31a807ac578
F test/btree.test bf326f546a666617367a7033fa2c07451bd4f8e1
F test/btree2.test e3b81ec33dc2f89b3e6087436dfe605b870c9080
@@ -78,6 +78,7 @@
F test/misc1.test 7fd54d33547177da86e39e13e9608c5112681831
F test/notnull.test b1f3e42fc475b0b5827b27b2e9b562081995ff30
F test/pager.test b0c0d00cd5dce0ce21f16926956b195c0ab5044c
+F test/pragma.test 0b9675ef1f5ba5b43abfa337744445fc5b01a34a
F test/printf.test 3cb415073754cb8ff076f26173143c3cd293a9da
F test/quick.test 6f023c7a73fc413e6d65b7a1879c79764038dc05
F test/quote.test 286db944717afa9a9bf829dd85e59185c65d5435
@@ -94,7 +95,7 @@
F test/tableapi.test 51d0c209aa6b1158cb952ec917c656d4ce66e9e4
F test/tclsqlite.test ca8dd89b02ab68bd4540163c24551756a69f6783
F test/temptable.test 0e9934283259a5e637eec756a7eefd6964c0f79b
-F test/tester.tcl 96db1b49157388edb57e11bf33285e3811a897e4
+F test/tester.tcl dc1b56bd628b487e4d75bfd1e7480b5ed8810ac6
F test/trans.test 9e49495c06b1c41f889bf4f0fb195a015b126de0
F test/unique.test 07776624b82221a80c8b4138ce0dd8b0853bb3ea
F test/update.test 3cf1ca0565f678063c2dfa9a7948d2d66ae1a778
@@ -108,7 +109,8 @@
F tool/opcodeDoc.awk b3a2a3d5d3075b8bd90b7afe24283efdd586659c
F tool/renumberOps.awk 6d067177ad5f8d711b79577b462da9b3634bd0a9
F tool/report1.txt 9eae07f26a8fc53889b45fc833a66a33daa22816
-F tool/speedtest.tcl 61660a00b9a5ad8566dbc48e9b09071b1ad8a1c3
+F tool/speedtest.tcl 6d89431651f2eb800fb0998a8de44b84168e08b9
+F tool/speedtest2.tcl ee2149167303ba8e95af97873c575c3e0fab58ff
F www/arch.fig d5f9752a4dbf242e9cfffffd3f5762b6c63b3bcf
F www/arch.png 82ef36db1143828a7abc88b1e308a5f55d4336f4
F www/arch.tcl 72a0c80e9054cc7025a50928d28d9c75c02c2b8b
@@ -128,7 +130,7 @@
F www/sqlite.tcl 8b5884354cb615049aed83039f8dfe1552a44279
F www/tclsqlite.tcl 829b393d1ab187fd7a5e978631b3429318885c49
F www/vdbe.tcl 2013852c27a02a091d39a766bc87cff329f21218
-P 480eef1a3a4f049bc0d0cbee32dc8a8d138597c6
-R 293e4b0f9d71b9a7cc31da9375205a15
+P b7a7dae919be0e4c35b1fe8cb24fa7359a4b1200
+R 4ed5325a3b84cb330db6f062fc9a97e7
U drh
-Z be460c9473f4d69e9aab54c7c0b566d7
+Z 34b0d04d29e433a78df2f11cc397e416
diff --git a/manifest.uuid b/manifest.uuid
index dbe1aa7..3d71b2d 100644
--- a/manifest.uuid
+++ b/manifest.uuid
@@ -1 +1 @@
-b7a7dae919be0e4c35b1fe8cb24fa7359a4b1200
\ No newline at end of file
+161c0c5f5db66815e4345c9b5f7a600c03a67475
\ No newline at end of file
diff --git a/src/btree.c b/src/btree.c
index a3a269e..7a39089 100644
--- a/src/btree.c
+++ b/src/btree.c
@@ -9,7 +9,7 @@
** May you share freely, never taking more than you give.
**
*************************************************************************
-** $Id: btree.c,v 1.59 2002/03/05 12:41:20 drh Exp $
+** $Id: btree.c,v 1.60 2002/03/06 22:01:35 drh Exp $
**
** This file implements a external (disk-based) database using BTrees.
** For a detailed discussion of BTrees, refer to
@@ -650,6 +650,18 @@
/*
** Change the limit on the number of pages allowed the cache.
+**
+** The maximum number of cache pages is set to the absolute
+** value of mxPage. If mxPage is negative, the pager will
+** operate asynchronously - it will not stop to do fsync()s
+** to insure data is written to the disk surface before
+** continuing. Transactions still work if synchronous is off,
+** and the database cannot be corrupted if this program
+** crashes. But if the operating system crashes or there is
+** an abrupt power failure when synchronous is off, the database
+** could be left in an inconsistent and unrecoverable state.
+** Synchronous is on by default so database corruption is not
+** normally a worry.
*/
int sqliteBtreeSetCacheSize(Btree *pBt, int mxPage){
sqlitepager_set_cachesize(pBt->pPager, mxPage);
diff --git a/src/build.c b/src/build.c
index 639ba00..108bb4b 100644
--- a/src/build.c
+++ b/src/build.c
@@ -25,7 +25,7 @@
** ROLLBACK
** PRAGMA
**
-** $Id: build.c,v 1.85 2002/03/05 01:11:13 drh Exp $
+** $Id: build.c,v 1.86 2002/03/06 22:01:36 drh Exp $
*/
#include "sqliteInt.h"
#include <ctype.h>
@@ -1736,10 +1736,30 @@
sqliteDequote(zRight);
}
- if( sqliteStrICmp(zLeft,"cache_size")==0 ){
+ /*
+ ** PRAGMA default_cache_size
+ ** PRAGMA default_cache_size=N
+ **
+ ** The first form reports the current persistent setting for the
+ ** page cache size. The value returned is the maximum number of
+ ** pages in the page cache. The second form sets both the current
+ ** page cache size value and the persistent page cache size value
+ ** stored in the database file.
+ **
+ ** The default cache size is stored in meta-value 2 of page 1 of the
+ ** database file. The cache size is actually the absolute value of
+ ** this memory location. The sign of meta-value 2 determines the
+ ** synchronous setting. A negative value means synchronous is off
+ ** and a positive value means synchronous is on.
+ */
+ if( sqliteStrICmp(zLeft,"default_cache_size")==0 ){
static VdbeOp getCacheSize[] = {
{ OP_ReadCookie, 0, 2, 0},
{ OP_AbsValue, 0, 0, 0},
+ { OP_Dup, 0, 0, 0},
+ { OP_Integer, 0, 0, 0},
+ { OP_Ne, 0, 6, 0},
+ { OP_Integer, MAX_PAGES,0, 0},
{ OP_ColumnCount, 1, 0, 0},
{ OP_ColumnName, 0, 0, "cache_size"},
{ OP_Callback, 1, 0, 0},
@@ -1760,10 +1780,71 @@
sqliteVdbeAddOp(v, OP_Negative, 0, 0);
sqliteVdbeAddOp(v, OP_SetCookie, 0, 2);
sqliteEndWriteOperation(pParse);
+ db->cache_size = db->cache_size<0 ? -size : size;
+ sqliteBtreeSetCacheSize(db->pBe, db->cache_size);
}
}else
- if( sqliteStrICmp(zLeft,"synchronous")==0 ){
+ /*
+ ** PRAGMA cache_size
+ ** PRAGMA cache_size=N
+ **
+ ** The first form reports the current local setting for the
+ ** page cache size. The local setting can be different from
+ ** the persistent cache size value that is stored in the database
+ ** file itself. The value returned is the maximum number of
+ ** pages in the page cache. The second form sets the local
+ ** page cache size value. It does not change the persistent
+ ** cache size stored on the disk so the cache size will revert
+ ** to its default value when the database is closed and reopened.
+ ** N should be a positive integer.
+ */
+ if( sqliteStrICmp(zLeft,"cache_size")==0 ){
+ static VdbeOp getCacheSize[] = {
+ { OP_ColumnCount, 1, 0, 0},
+ { OP_ColumnName, 0, 0, "cache_size"},
+ { OP_Callback, 1, 0, 0},
+ };
+ Vdbe *v = sqliteGetVdbe(pParse);
+ if( v==0 ) return;
+ if( pRight->z==pLeft->z ){
+ int size = db->cache_size;;
+ if( size<0 ) size = -size;
+ sqliteVdbeAddOp(v, OP_Integer, size, 0);
+ sqliteVdbeAddOpList(v, ArraySize(getCacheSize), getCacheSize);
+ }else{
+ int size = atoi(zRight);
+ if( size<0 ) size = -size;
+ if( db->cache_size<0 ) size = -size;
+ db->cache_size = size;
+ sqliteBtreeSetCacheSize(db->pBe, db->cache_size);
+ }
+ }else
+
+ /*
+ ** PRAGMA default_synchronous
+ ** PRAGMA default_synchronous=BOOLEAN
+ **
+ ** The first form returns the persistent value of the "synchronous" setting
+ ** that is stored in the database. This is the synchronous setting that
+ ** is used whenever the database is opened unless overridden by a separate
+ ** "synchronous" pragma. The second form changes the persistent and the
+ ** local synchronous setting to the value given.
+ **
+ ** If synchronous is on, SQLite will do an fsync() system call at strategic
+ ** points to insure that all previously written data has actually been
+ ** written onto the disk surface before continuing. This mode insures that
+ ** the database will always be in a consistent state event if the operating
+ ** system crashes or power to the computer is interrupted unexpectedly.
+ ** When synchronous is off, SQLite will not wait for changes to actually
+ ** be written to the disk before continuing. As soon as it hands changes
+ ** to the operating system, it assumes that the changes are permanent and
+ ** it continues going. The database cannot be corrupted by a program crash
+ ** even with synchronous off, but an operating system crash or power loss
+ ** could potentially corrupt data. On the other hand, synchronous off is
+ ** faster than synchronous on.
+ */
+ if( sqliteStrICmp(zLeft,"default_synchronous")==0 ){
static VdbeOp getSync[] = {
{ OP_Integer, 0, 0, 0},
{ OP_ReadCookie, 0, 2, 0},
@@ -1780,14 +1861,52 @@
sqliteVdbeAddOpList(v, ArraySize(getSync), getSync);
}else{
int addr;
+ int size = db->cache_size;
+ if( size<0 ) size = -size;
sqliteBeginWriteOperation(pParse);
sqliteVdbeAddOp(v, OP_ReadCookie, 0, 2);
+ sqliteVdbeAddOp(v, OP_Dup, 0, 0);
+ addr = sqliteVdbeAddOp(v, OP_Integer, 0, 0);
+ sqliteVdbeAddOp(v, OP_Ne, 0, addr+3);
+ sqliteVdbeAddOp(v, OP_AddImm, MAX_PAGES, 0);
sqliteVdbeAddOp(v, OP_AbsValue, 0, 0);
if( !getBoolean(zRight) ){
sqliteVdbeAddOp(v, OP_Negative, 0, 0);
+ size = -size;
}
sqliteVdbeAddOp(v, OP_SetCookie, 0, 2);
sqliteEndWriteOperation(pParse);
+ db->cache_size = size;
+ sqliteBtreeSetCacheSize(db->pBe, db->cache_size);
+ }
+ }else
+
+ /*
+ ** PRAGMA synchronous
+ ** PRAGMA synchronous=BOOLEAN
+ **
+ ** Return or set the local value of the synchronous flag. Changing
+ ** the local value does not make changes to the disk file and the
+ ** default value will be restored the next time the database is
+ ** opened.
+ */
+ if( sqliteStrICmp(zLeft,"synchronous")==0 ){
+ static VdbeOp getSync[] = {
+ { OP_ColumnCount, 1, 0, 0},
+ { OP_ColumnName, 0, 0, "synchronous"},
+ { OP_Callback, 1, 0, 0},
+ };
+ Vdbe *v = sqliteGetVdbe(pParse);
+ if( v==0 ) return;
+ if( pRight->z==pLeft->z ){
+ sqliteVdbeAddOp(v, OP_Integer, db->cache_size>=0, 0);
+ sqliteVdbeAddOpList(v, ArraySize(getSync), getSync);
+ }else{
+ int size = db->cache_size;
+ if( size<0 ) size = -size;
+ if( !getBoolean(zRight) ) size = -size;
+ db->cache_size = size;
+ sqliteBtreeSetCacheSize(db->pBe, db->cache_size);
}
}else
diff --git a/src/main.c b/src/main.c
index 62f0952..3895079 100644
--- a/src/main.c
+++ b/src/main.c
@@ -14,7 +14,7 @@
** other files are for internal use by SQLite and should not be
** accessed by users of the library.
**
-** $Id: main.c,v 1.67 2002/03/05 01:11:14 drh Exp $
+** $Id: main.c,v 1.68 2002/03/06 22:01:36 drh Exp $
*/
#include "sqliteInt.h"
#include "os.h"
@@ -44,9 +44,9 @@
switch( argv[0][0] ){
case 'c': { /* Recommended pager cache size */
int size = atoi(argv[3]);
- if( size!=0 ){
- sqliteBtreeSetCacheSize(db->pBe, size);
- }
+ if( size==0 ){ size = MAX_PAGES; }
+ db->cache_size = size;
+ sqliteBtreeSetCacheSize(db->pBe, size);
break;
}
case 'f': { /* File format */
diff --git a/src/pager.c b/src/pager.c
index 1f0fb3a..ced0016 100644
--- a/src/pager.c
+++ b/src/pager.c
@@ -18,7 +18,7 @@
** file simultaneously, or one process from reading the database while
** another is writing.
**
-** @(#) $Id: pager.c,v 1.43 2002/03/05 12:41:20 drh Exp $
+** @(#) $Id: pager.c,v 1.44 2002/03/06 22:01:36 drh Exp $
*/
#include "sqliteInt.h"
#include "pager.h"
@@ -434,6 +434,13 @@
/*
** Change the maximum number of in-memory pages that are allowed.
+**
+** The maximum number is the absolute value of the mxPage parameter.
+** If mxPage is negative, the noSync flag is also set. noSync bypasses
+** calls to sqliteOsSync(). The pager runs much faster with noSync on,
+** but if the operating system crashes or there is an abrupt power
+** failure, the database file might be left in an inconsistent and
+** unrepairable state.
*/
void sqlitepager_set_cachesize(Pager *pPager, int mxPage){
if( mxPage>=0 ){
diff --git a/src/sqliteInt.h b/src/sqliteInt.h
index 3520a94..e404a73 100644
--- a/src/sqliteInt.h
+++ b/src/sqliteInt.h
@@ -11,7 +11,7 @@
*************************************************************************
** Internal interface definitions for SQLite.
**
-** @(#) $Id: sqliteInt.h,v 1.102 2002/03/05 01:11:14 drh Exp $
+** @(#) $Id: sqliteInt.h,v 1.103 2002/03/06 22:01:36 drh Exp $
*/
#include "sqlite.h"
#include "hash.h"
@@ -155,6 +155,7 @@
int file_format; /* What file format version is this database? */
int schema_cookie; /* Magic number that changes with the schema */
int next_cookie; /* Value of schema_cookie after commit */
+ int cache_size; /* Number of pages to use in the cache */
int nTable; /* Number of tables in the database */
void *pBusyArg; /* 1st Argument to the busy callback */
int (*xBusyCallback)(void *,const char*,int); /* The busy callback */
diff --git a/src/vdbe.c b/src/vdbe.c
index adb3fbe..f37d57e 100644
--- a/src/vdbe.c
+++ b/src/vdbe.c
@@ -30,7 +30,7 @@
** But other routines are also provided to help in building up
** a program instruction by instruction.
**
-** $Id: vdbe.c,v 1.132 2002/03/06 03:08:26 drh Exp $
+** $Id: vdbe.c,v 1.133 2002/03/06 22:01:36 drh Exp $
*/
#include "sqliteInt.h"
#include <ctype.h>
@@ -4309,7 +4309,8 @@
memcpy(&aStack[tos], &p->aMem[i].s, sizeof(aStack[tos])-NBFS);;
if( aStack[tos].flags & STK_Str ){
zStack[tos] = p->aMem[i].z;
- aStack[tos].flags = STK_Str | STK_Static;
+ aStack[tos].flags |= STK_Static;
+ aStack[tos].flags &= ~STK_Dyn;
}
break;
}
diff --git a/test/all.test b/test/all.test
index 3139bba..ff5c4fb 100644
--- a/test/all.test
+++ b/test/all.test
@@ -10,7 +10,7 @@
#***********************************************************************
# This file runs all tests.
#
-# $Id: all.test,v 1.12 2002/02/18 01:17:00 drh Exp $
+# $Id: all.test,v 1.13 2002/03/06 22:01:36 drh Exp $
set testdir [file dirname $argv0]
source $testdir/tester.tcl
@@ -37,6 +37,11 @@
}
for {set Counter 0} {$Counter<$COUNT && $nErr==0} {incr Counter} {
+ if {$Counter%2} {
+ set ::SETUP_SQL {PRAGMA default_synchronous=off;}
+ } else {
+ catch {unset ::SETUP_SQL}
+ }
foreach testfile [lsort -dictionary [glob $testdir/*.test]] {
set tail [file tail $testfile]
if {[lsearch -exact $EXCLUDE $tail]>=0} continue
diff --git a/test/pragma.test b/test/pragma.test
new file mode 100644
index 0000000..f69cb6c
--- /dev/null
+++ b/test/pragma.test
@@ -0,0 +1,120 @@
+# 2002 March 6
+#
+# The author disclaims copyright to this source code. In place of
+# 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.
+#
+#***********************************************************************
+# This file implements regression tests for SQLite library.
+#
+# This file implements tests for the PRAGMA command.
+#
+# $Id: pragma.test,v 1.1 2002/03/06 22:01:37 drh Exp $
+
+set testdir [file dirname $argv0]
+source $testdir/tester.tcl
+
+# Delete the preexisting database to avoid the special setup
+# that the "all.test" script does.
+#
+db close
+file delete test.db
+sqlite db test.db
+
+do_test pragma-1.1 {
+ execsql {
+ PRAGMA cache_size;
+ PRAGMA default_cache_size;
+ PRAGMA synchronous;
+ PRAGMA default_synchronous;
+ }
+} {2000 2000 1 1}
+do_test pragma-1.2 {
+ execsql {
+ PRAGMA cache_size=1234;
+ PRAGMA cache_size;
+ PRAGMA default_cache_size;
+ PRAGMA synchronous;
+ PRAGMA default_synchronous;
+ }
+} {1234 2000 1 1}
+do_test pragma-1.3 {
+ db close
+ sqlite db test.db
+ execsql {
+ PRAGMA cache_size;
+ PRAGMA default_cache_size;
+ PRAGMA synchronous;
+ PRAGMA default_synchronous;
+ }
+} {2000 2000 1 1}
+do_test pragma-1.4 {
+ execsql {
+ PRAGMA synchronous=OFF;
+ PRAGMA cache_size;
+ PRAGMA default_cache_size;
+ PRAGMA synchronous;
+ PRAGMA default_synchronous;
+ }
+} {2000 2000 0 1}
+do_test pragma-1.5 {
+ execsql {
+ PRAGMA cache_size=4321;
+ PRAGMA cache_size;
+ PRAGMA default_cache_size;
+ PRAGMA synchronous;
+ PRAGMA default_synchronous;
+ }
+} {4321 2000 0 1}
+do_test pragma-1.6 {
+ execsql {
+ PRAGMA synchronous=ON;
+ PRAGMA cache_size;
+ PRAGMA default_cache_size;
+ PRAGMA synchronous;
+ PRAGMA default_synchronous;
+ }
+} {4321 2000 1 1}
+do_test pragma-1.7 {
+ db close
+ sqlite db test.db
+ execsql {
+ PRAGMA cache_size;
+ PRAGMA default_cache_size;
+ PRAGMA synchronous;
+ PRAGMA default_synchronous;
+ }
+} {2000 2000 1 1}
+do_test pragma-1.8 {
+ execsql {
+ PRAGMA default_synchronous=OFF;
+ PRAGMA cache_size;
+ PRAGMA default_cache_size;
+ PRAGMA synchronous;
+ PRAGMA default_synchronous;
+ }
+} {2000 2000 0 0}
+do_test pragma-1.9 {
+ execsql {
+ PRAGMA default_cache_size=123;
+ PRAGMA cache_size;
+ PRAGMA default_cache_size;
+ PRAGMA synchronous;
+ PRAGMA default_synchronous;
+ }
+} {123 123 0 0}
+do_test pragma-1.10 {
+ db close
+ sqlite db test.db
+ execsql {
+ PRAGMA cache_size;
+ PRAGMA default_cache_size;
+ PRAGMA synchronous;
+ PRAGMA default_synchronous;
+ }
+} {123 123 0 0}
+
+finish_test
diff --git a/test/tester.tcl b/test/tester.tcl
index a30dad8..bcda99d 100644
--- a/test/tester.tcl
+++ b/test/tester.tcl
@@ -11,7 +11,7 @@
# This file implements some common TCL routines used for regression
# testing the SQLite library
#
-# $Id: tester.tcl,v 1.21 2001/11/09 22:41:45 drh Exp $
+# $Id: tester.tcl,v 1.22 2002/03/06 22:01:37 drh Exp $
# Make sure tclsqlite was compiled correctly. Abort now with an
# error message if not.
@@ -46,6 +46,9 @@
file delete -force test.db
file delete -force test.db-journal
sqlite db ./test.db
+if {[info exists ::SETUP_SQL]} {
+ db eval $::SETUP_SQL
+}
# Abort early if this script has been run before.
#
diff --git a/tool/speedtest.tcl b/tool/speedtest.tcl
index 276fcc1..7090e2e 100644
--- a/tool/speedtest.tcl
+++ b/tool/speedtest.tcl
@@ -37,9 +37,9 @@
set t [expr {[lindex $t 0]/1000000.0}]
puts [format $format PostgreSQL: $t]
exec sync; after $delay;
-# set t [time "exec mysql -f drh <$sqlfile" 1]
-# set t [expr {[lindex $t 0]/1000000.0}]
-# puts [format $format MySQL: $t]
+ set t [time "exec mysql -f drh <$sqlfile" 1]
+ set t [expr {[lindex $t 0]/1000000.0}]
+ puts [format $format MySQL: $t]
# set t [time "exec ./sqlite232 s232.db <$sqlfile" 1]
# set t [expr {[lindex $t 0]/1000000.0}]
# puts [format $format {SQLite 2.3.2:} $t]
@@ -73,12 +73,17 @@
catch {exec psql drh <clear.sql}
catch {exec mysql drh <clear.sql}
set fd [open 2kinit.sql w]
-puts $fd {PRAGMA cache_size=2000; PRAGMA synchronous=on;}
+puts $fd {
+ PRAGMA default_cache_size=2000;
+ PRAGMA default_synchronous=on;
+}
close $fd
exec ./sqlite240 s2k.db <2kinit.sql
-exec ./sqlite-t1 st1.db <2kinit.sql
set fd [open nosync-init.sql w]
-puts $fd {PRAGMA cache_size=2000; PRAGMA synchronous=off;}
+puts $fd {
+ PRAGMA default_cache_size=2000;
+ PRAGMA default_synchronous=off;
+}
close $fd
exec ./sqlite240 sns.db <nosync-init.sql
set ones {zero one two three four five six seven eight nine
@@ -157,7 +162,6 @@
set fd [open test$cnt.sql w]
puts $fd {CREATE INDEX i2a ON t2(a);}
puts $fd {CREATE INDEX i2b ON t2(b);}
-puts $fd {VACUUM;}
close $fd
runtest {Creating an index}
@@ -176,27 +180,39 @@
set fd [open test$cnt.sql w]
puts $fd "BEGIN;"
-for {set i 0} {$i<100} {incr i} {
+for {set i 0} {$i<1000} {incr i} {
set lwr [expr {$i*10}]
set upr [expr {($i+1)*10}]
puts $fd "UPDATE t1 SET b=b*2 WHERE a>=$lwr AND a<$upr;"
}
puts $fd "COMMIT;"
close $fd
-runtest {100 UPDATEs without an index}
+runtest {1000 UPDATEs without an index}
set fd [open test$cnt.sql w]
puts $fd "BEGIN;"
for {set i 1} {$i<=25000} {incr i} {
- puts $fd "UPDATE t2 SET b=b+a WHERE a=$i;"
+ set r [expr {int(rand()*500000)}]
+ puts $fd "UPDATE t2 SET b=$r WHERE a=$i;"
}
puts $fd "COMMIT;"
close $fd
runtest {25000 UPDATEs with an index}
+set fd [open test$cnt.sql w]
+puts $fd "BEGIN;"
+for {set i 1} {$i<=25000} {incr i} {
+ set r [expr {int(rand()*500000)}]
+ puts $fd "UPDATE t2 SET c='[number_name $r]' WHERE a=$i;"
+}
+puts $fd "COMMIT;"
+close $fd
+runtest {25000 text UPDATEs with an index}
+
+
set fd [open test$cnt.sql w]
puts $fd "BEGIN;"
@@ -232,7 +248,7 @@
set fd [open test$cnt.sql w]
puts $fd {BEGIN;}
puts $fd {DELETE FROM t1;}
-for {set i 1} {$i<=1000} {incr i} {
+for {set i 1} {$i<=3000} {incr i} {
set r [expr {int(rand()*100000)}]
puts $fd "INSERT INTO t1 VALUES($i,$r,'[number_name $r]');"
}
diff --git a/tool/speedtest2.tcl b/tool/speedtest2.tcl
new file mode 100644
index 0000000..4fd632d
--- /dev/null
+++ b/tool/speedtest2.tcl
@@ -0,0 +1,207 @@
+#!/usr/bin/tclsh
+#
+# Run this script using TCLSH to do a speed comparison between
+# various versions of SQLite and PostgreSQL and MySQL
+#
+
+# Run a test
+#
+set cnt 1
+proc runtest {title} {
+ global cnt
+ set sqlfile test$cnt.sql
+ puts "<h2>Test $cnt: $title</h2>"
+ incr cnt
+ set fd [open $sqlfile r]
+ set sql [string trim [read $fd [file size $sqlfile]]]
+ close $fd
+ set sx [split $sql \n]
+ set n [llength $sx]
+ if {$n>8} {
+ set sql {}
+ for {set i 0} {$i<3} {incr i} {append sql [lindex $sx $i]<br>\n}
+ append sql "<i>... [expr {$n-6}] lines omitted</i><br>\n"
+ for {set i [expr {$n-3}]} {$i<$n} {incr i} {
+ append sql [lindex $sx $i]<br>\n
+ }
+ } else {
+ regsub -all \n [string trim $sql] <br> sql
+ }
+ puts "<blockquote>"
+ puts "$sql"
+ puts "</blockquote><table border=0 cellpadding=0 cellspacing=0>"
+ set format {<tr><td>%s</td><td align="right"> %.3f</td></tr>}
+ set delay 1000
+ exec sync; after $delay;
+ set t [time "exec psql drh <$sqlfile" 1]
+ set t [expr {[lindex $t 0]/1000000.0}]
+ puts [format $format PostgreSQL: $t]
+ exec sync; after $delay;
+ set t [time "exec mysql -f drh <$sqlfile" 1]
+ set t [expr {[lindex $t 0]/1000000.0}]
+ puts [format $format MySQL: $t]
+# set t [time "exec ./sqlite232 s232.db <$sqlfile" 1]
+# set t [expr {[lindex $t 0]/1000000.0}]
+# puts [format $format {SQLite 2.3.2:} $t]
+# set t [time "exec ./sqlite-100 s100.db <$sqlfile" 1]
+# set t [expr {[lindex $t 0]/1000000.0}]
+# puts [format $format {SQLite 2.4 (cache=100):} $t]
+ exec sync; after $delay;
+ set t [time "exec ./sqlite240 s2k.db <$sqlfile" 1]
+ set t [expr {[lindex $t 0]/1000000.0}]
+ puts [format $format {SQLite 2.4:} $t]
+ exec sync; after $delay;
+ set t [time "exec ./sqlite240 sns.db <$sqlfile" 1]
+ set t [expr {[lindex $t 0]/1000000.0}]
+ puts [format $format {SQLite 2.4 (nosync):} $t]
+# set t [time "exec ./sqlite-t1 st1.db <$sqlfile" 1]
+# set t [expr {[lindex $t 0]/1000000.0}]
+# puts [format $format {SQLite 2.4 (test):} $t]
+ puts "</table>"
+}
+
+# Initialize the environment
+#
+expr srand(1)
+catch {exec /bin/sh -c {rm -f s*.db}}
+set fd [open clear.sql w]
+puts $fd {
+ drop table t1;
+ drop table t2;
+}
+close $fd
+catch {exec psql drh <clear.sql}
+catch {exec mysql drh <clear.sql}
+set fd [open 2kinit.sql w]
+puts $fd {
+ PRAGMA default_cache_size=2000;
+ PRAGMA default_synchronous=on;
+}
+close $fd
+exec ./sqlite240 s2k.db <2kinit.sql
+exec ./sqlite-t1 st1.db <2kinit.sql
+set fd [open nosync-init.sql w]
+puts $fd {
+ PRAGMA default_cache_size=2000;
+ PRAGMA default_synchronous=off;
+}
+close $fd
+exec ./sqlite240 sns.db <nosync-init.sql
+set ones {zero one two three four five six seven eight nine
+ ten eleven twelve thirteen fourteen fifteen sixteen seventeen
+ eighteen nineteen}
+set tens {{} ten twenty thirty forty fifty sixty seventy eighty ninety}
+proc number_name {n} {
+ if {$n>=1000} {
+ set txt "[number_name [expr {$n/1000}]] thousand"
+ set n [expr {$n%1000}]
+ } else {
+ set txt {}
+ }
+ if {$n>=100} {
+ append txt " [lindex $::ones [expr {$n/100}]] hundred"
+ set n [expr {$n%100}]
+ }
+ if {$n>=20} {
+ append txt " [lindex $::tens [expr {$n/10}]]"
+ set n [expr {$n%10}]
+ }
+ if {$n>0} {
+ append txt " [lindex $::ones $n]"
+ }
+ set txt [string trim $txt]
+ if {$txt==""} {set txt zero}
+ return $txt
+}
+
+
+set fd [open test$cnt.sql w]
+puts $fd "BEGIN;"
+puts $fd "CREATE TABLE t1(a INTEGER, b INTEGER, c VARCHAR(100));"
+for {set i 1} {$i<=25000} {incr i} {
+ set r [expr {int(rand()*500000)}]
+ puts $fd "INSERT INTO t1 VALUES($i,$r,'[number_name $r]');"
+}
+puts $fd "COMMIT;"
+close $fd
+runtest {25000 INSERTs in a transaction}
+
+
+set fd [open test$cnt.sql w]
+puts $fd "DELETE FROM t1;"
+close $fd
+runtest {DELETE everything}
+
+
+set fd [open test$cnt.sql w]
+puts $fd "BEGIN;"
+for {set i 1} {$i<=25000} {incr i} {
+ set r [expr {int(rand()*500000)}]
+ puts $fd "INSERT INTO t1 VALUES($i,$r,'[number_name $r]');"
+}
+puts $fd "COMMIT;"
+close $fd
+runtest {25000 INSERTs in a transaction}
+
+
+set fd [open test$cnt.sql w]
+puts $fd "DELETE FROM t1;"
+close $fd
+runtest {DELETE everything}
+
+
+set fd [open test$cnt.sql w]
+puts $fd "BEGIN;"
+for {set i 1} {$i<=25000} {incr i} {
+ set r [expr {int(rand()*500000)}]
+ puts $fd "INSERT INTO t1 VALUES($i,$r,'[number_name $r]');"
+}
+puts $fd "COMMIT;"
+close $fd
+runtest {25000 INSERTs in a transaction}
+
+
+set fd [open test$cnt.sql w]
+puts $fd "DELETE FROM t1;"
+close $fd
+runtest {DELETE everything}
+
+
+set fd [open test$cnt.sql w]
+puts $fd "BEGIN;"
+for {set i 1} {$i<=25000} {incr i} {
+ set r [expr {int(rand()*500000)}]
+ puts $fd "INSERT INTO t1 VALUES($i,$r,'[number_name $r]');"
+}
+puts $fd "COMMIT;"
+close $fd
+runtest {25000 INSERTs in a transaction}
+
+
+set fd [open test$cnt.sql w]
+puts $fd "DELETE FROM t1;"
+close $fd
+runtest {DELETE everything}
+
+
+set fd [open test$cnt.sql w]
+puts $fd "BEGIN;"
+for {set i 1} {$i<=25000} {incr i} {
+ set r [expr {int(rand()*500000)}]
+ puts $fd "INSERT INTO t1 VALUES($i,$r,'[number_name $r]');"
+}
+puts $fd "COMMIT;"
+close $fd
+runtest {25000 INSERTs in a transaction}
+
+
+set fd [open test$cnt.sql w]
+puts $fd "DELETE FROM t1;"
+close $fd
+runtest {DELETE everything}
+
+
+set fd [open test$cnt.sql w]
+puts $fd {DROP TABLE t1;}
+close $fd
+runtest {DROP TABLE}