/ Check-in [161c0c5f]
Login

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

Overview
Comment:Added the default_cache_size and default_synchronous pragmas. Added additional tests for pragmas. Added a new speedtest script. (CVS 421)
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1:161c0c5f5db66815e4345c9b5f7a600c03a67475
User & Date: drh 2002-03-06 22:01:35
Context
2002-03-06
22:04
Beta 2 (CVS 422) check-in: 6c3fb547 user: drh tags: trunk
22:01
Added the default_cache_size and default_synchronous pragmas. Added additional tests for pragmas. Added a new speedtest script. (CVS 421) check-in: 161c0c5f user: drh tags: trunk
03:08
Optimizations to the processing of integer comparisons. (CVS 420) check-in: b7a7dae9 user: drh tags: trunk
Changes
Hide Diffs Side-by-Side Diffs Ignore Whitespace Patch

Changes to src/btree.c.

     5      5   ** a legal notice, here is a blessing:
     6      6   **
     7      7   **    May you do good and not evil.
     8      8   **    May you find forgiveness for yourself and forgive others.
     9      9   **    May you share freely, never taking more than you give.
    10     10   **
    11     11   *************************************************************************
    12         -** $Id: btree.c,v 1.59 2002/03/05 12:41:20 drh Exp $
           12  +** $Id: btree.c,v 1.60 2002/03/06 22:01:35 drh Exp $
    13     13   **
    14     14   ** This file implements a external (disk-based) database using BTrees.
    15     15   ** For a detailed discussion of BTrees, refer to
    16     16   **
    17     17   **     Donald E. Knuth, THE ART OF COMPUTER PROGRAMMING, Volume 3:
    18     18   **     "Sorting And Searching", pages 473-480. Addison-Wesley
    19     19   **     Publishing Company, Reading, Massachusetts.
................................................................................
   646    646     sqliteHashClear(&pBt->locks);
   647    647     sqliteFree(pBt);
   648    648     return SQLITE_OK;
   649    649   }
   650    650   
   651    651   /*
   652    652   ** Change the limit on the number of pages allowed the cache.
          653  +**
          654  +** The maximum number of cache pages is set to the absolute
          655  +** value of mxPage.  If mxPage is negative, the pager will
          656  +** operate asynchronously - it will not stop to do fsync()s
          657  +** to insure data is written to the disk surface before
          658  +** continuing.  Transactions still work if synchronous is off,
          659  +** and the database cannot be corrupted if this program
          660  +** crashes.  But if the operating system crashes or there is
          661  +** an abrupt power failure when synchronous is off, the database
          662  +** could be left in an inconsistent and unrecoverable state.
          663  +** Synchronous is on by default so database corruption is not
          664  +** normally a worry.
   653    665   */
   654    666   int sqliteBtreeSetCacheSize(Btree *pBt, int mxPage){
   655    667     sqlitepager_set_cachesize(pBt->pPager, mxPage);
   656    668     return SQLITE_OK;
   657    669   }
   658    670   
   659    671   /*

Changes to src/build.c.

    21     21   **     COPY
    22     22   **     VACUUM
    23     23   **     BEGIN TRANSACTION
    24     24   **     COMMIT
    25     25   **     ROLLBACK
    26     26   **     PRAGMA
    27     27   **
    28         -** $Id: build.c,v 1.85 2002/03/05 01:11:13 drh Exp $
           28  +** $Id: build.c,v 1.86 2002/03/06 22:01:36 drh Exp $
    29     29   */
    30     30   #include "sqliteInt.h"
    31     31   #include <ctype.h>
    32     32   
    33     33   /*
    34     34   ** This routine is called after a single SQL statement has been
    35     35   ** parsed and we want to execute the VDBE code to implement 
................................................................................
  1732   1732       zRight = 0;
  1733   1733       sqliteSetNString(&zRight, "-", 1, pRight->z, pRight->n, 0);
  1734   1734     }else{
  1735   1735       zRight = sqliteStrNDup(pRight->z, pRight->n);
  1736   1736       sqliteDequote(zRight);
  1737   1737     }
  1738   1738    
  1739         -  if( sqliteStrICmp(zLeft,"cache_size")==0 ){
         1739  +  /*
         1740  +  **  PRAGMA default_cache_size
         1741  +  **  PRAGMA default_cache_size=N
         1742  +  **
         1743  +  ** The first form reports the current persistent setting for the
         1744  +  ** page cache size.  The value returned is the maximum number of
         1745  +  ** pages in the page cache.  The second form sets both the current
         1746  +  ** page cache size value and the persistent page cache size value
         1747  +  ** stored in the database file.
         1748  +  **
         1749  +  ** The default cache size is stored in meta-value 2 of page 1 of the
         1750  +  ** database file.  The cache size is actually the absolute value of
         1751  +  ** this memory location.  The sign of meta-value 2 determines the
         1752  +  ** synchronous setting.  A negative value means synchronous is off
         1753  +  ** and a positive value means synchronous is on.
         1754  +  */
         1755  +  if( sqliteStrICmp(zLeft,"default_cache_size")==0 ){
  1740   1756       static VdbeOp getCacheSize[] = {
  1741   1757         { OP_ReadCookie,  0, 2,        0},
  1742   1758         { OP_AbsValue,    0, 0,        0},
         1759  +      { OP_Dup,         0, 0,        0},
         1760  +      { OP_Integer,     0, 0,        0},
         1761  +      { OP_Ne,          0, 6,        0},
         1762  +      { OP_Integer,     MAX_PAGES,0, 0},
  1743   1763         { OP_ColumnCount, 1, 0,        0},
  1744   1764         { OP_ColumnName,  0, 0,        "cache_size"},
  1745   1765         { OP_Callback,    1, 0,        0},
  1746   1766       };
  1747   1767       Vdbe *v = sqliteGetVdbe(pParse);
  1748   1768       if( v==0 ) return;
  1749   1769       if( pRight->z==pLeft->z ){
................................................................................
  1756   1776         sqliteVdbeAddOp(v, OP_Integer, size, 0);
  1757   1777         sqliteVdbeAddOp(v, OP_ReadCookie, 0, 2);
  1758   1778         addr = sqliteVdbeAddOp(v, OP_Integer, 0, 0);
  1759   1779         sqliteVdbeAddOp(v, OP_Ge, 0, addr+3);
  1760   1780         sqliteVdbeAddOp(v, OP_Negative, 0, 0);
  1761   1781         sqliteVdbeAddOp(v, OP_SetCookie, 0, 2);
  1762   1782         sqliteEndWriteOperation(pParse);
         1783  +      db->cache_size = db->cache_size<0 ? -size : size;
         1784  +      sqliteBtreeSetCacheSize(db->pBe, db->cache_size);
  1763   1785       }
  1764   1786     }else
  1765   1787   
  1766         -  if( sqliteStrICmp(zLeft,"synchronous")==0 ){
         1788  +  /*
         1789  +  **  PRAGMA cache_size
         1790  +  **  PRAGMA cache_size=N
         1791  +  **
         1792  +  ** The first form reports the current local setting for the
         1793  +  ** page cache size.  The local setting can be different from
         1794  +  ** the persistent cache size value that is stored in the database
         1795  +  ** file itself.  The value returned is the maximum number of
         1796  +  ** pages in the page cache.  The second form sets the local
         1797  +  ** page cache size value.  It does not change the persistent
         1798  +  ** cache size stored on the disk so the cache size will revert
         1799  +  ** to its default value when the database is closed and reopened.
         1800  +  ** N should be a positive integer.
         1801  +  */
         1802  +  if( sqliteStrICmp(zLeft,"cache_size")==0 ){
         1803  +    static VdbeOp getCacheSize[] = {
         1804  +      { OP_ColumnCount, 1, 0,        0},
         1805  +      { OP_ColumnName,  0, 0,        "cache_size"},
         1806  +      { OP_Callback,    1, 0,        0},
         1807  +    };
         1808  +    Vdbe *v = sqliteGetVdbe(pParse);
         1809  +    if( v==0 ) return;
         1810  +    if( pRight->z==pLeft->z ){
         1811  +      int size = db->cache_size;;
         1812  +      if( size<0 ) size = -size;
         1813  +      sqliteVdbeAddOp(v, OP_Integer, size, 0);
         1814  +      sqliteVdbeAddOpList(v, ArraySize(getCacheSize), getCacheSize);
         1815  +    }else{
         1816  +      int size = atoi(zRight);
         1817  +      if( size<0 ) size = -size;
         1818  +      if( db->cache_size<0 ) size = -size;
         1819  +      db->cache_size = size;
         1820  +      sqliteBtreeSetCacheSize(db->pBe, db->cache_size);
         1821  +    }
         1822  +  }else
         1823  +
         1824  +  /*
         1825  +  **  PRAGMA default_synchronous
         1826  +  **  PRAGMA default_synchronous=BOOLEAN
         1827  +  **
         1828  +  ** The first form returns the persistent value of the "synchronous" setting
         1829  +  ** that is stored in the database.  This is the synchronous setting that
         1830  +  ** is used whenever the database is opened unless overridden by a separate
         1831  +  ** "synchronous" pragma.  The second form changes the persistent and the
         1832  +  ** local synchronous setting to the value given.
         1833  +  **
         1834  +  ** If synchronous is on, SQLite will do an fsync() system call at strategic
         1835  +  ** points to insure that all previously written data has actually been
         1836  +  ** written onto the disk surface before continuing.  This mode insures that
         1837  +  ** the database will always be in a consistent state event if the operating
         1838  +  ** system crashes or power to the computer is interrupted unexpectedly.
         1839  +  ** When synchronous is off, SQLite will not wait for changes to actually
         1840  +  ** be written to the disk before continuing.  As soon as it hands changes
         1841  +  ** to the operating system, it assumes that the changes are permanent and
         1842  +  ** it continues going.  The database cannot be corrupted by a program crash
         1843  +  ** even with synchronous off, but an operating system crash or power loss
         1844  +  ** could potentially corrupt data.  On the other hand, synchronous off is
         1845  +  ** faster than synchronous on.
         1846  +  */
         1847  +  if( sqliteStrICmp(zLeft,"default_synchronous")==0 ){
  1767   1848       static VdbeOp getSync[] = {
  1768   1849         { OP_Integer,     0, 0,        0},
  1769   1850         { OP_ReadCookie,  0, 2,        0},
  1770   1851         { OP_Integer,     0, 0,        0},
  1771   1852         { OP_Lt,          0, 5,        0},
  1772   1853         { OP_AddImm,      1, 0,        0},
  1773   1854         { OP_ColumnCount, 1, 0,        0},
................................................................................
  1776   1857       };
  1777   1858       Vdbe *v = sqliteGetVdbe(pParse);
  1778   1859       if( v==0 ) return;
  1779   1860       if( pRight->z==pLeft->z ){
  1780   1861         sqliteVdbeAddOpList(v, ArraySize(getSync), getSync);
  1781   1862       }else{
  1782   1863         int addr;
         1864  +      int size = db->cache_size;
         1865  +      if( size<0 ) size = -size;
  1783   1866         sqliteBeginWriteOperation(pParse);
  1784   1867         sqliteVdbeAddOp(v, OP_ReadCookie, 0, 2);
         1868  +      sqliteVdbeAddOp(v, OP_Dup, 0, 0);
         1869  +      addr = sqliteVdbeAddOp(v, OP_Integer, 0, 0);
         1870  +      sqliteVdbeAddOp(v, OP_Ne, 0, addr+3);
         1871  +      sqliteVdbeAddOp(v, OP_AddImm, MAX_PAGES, 0);
  1785   1872         sqliteVdbeAddOp(v, OP_AbsValue, 0, 0);
  1786   1873         if( !getBoolean(zRight) ){
  1787   1874           sqliteVdbeAddOp(v, OP_Negative, 0, 0);
         1875  +        size = -size;
  1788   1876         }
  1789   1877         sqliteVdbeAddOp(v, OP_SetCookie, 0, 2);
  1790   1878         sqliteEndWriteOperation(pParse);
         1879  +      db->cache_size = size;
         1880  +      sqliteBtreeSetCacheSize(db->pBe, db->cache_size);
         1881  +    }
         1882  +  }else
         1883  +
         1884  +  /*
         1885  +  **   PRAGMA synchronous
         1886  +  **   PRAGMA synchronous=BOOLEAN
         1887  +  **
         1888  +  ** Return or set the local value of the synchronous flag.  Changing
         1889  +  ** the local value does not make changes to the disk file and the
         1890  +  ** default value will be restored the next time the database is
         1891  +  ** opened.
         1892  +  */
         1893  +  if( sqliteStrICmp(zLeft,"synchronous")==0 ){
         1894  +    static VdbeOp getSync[] = {
         1895  +      { OP_ColumnCount, 1, 0,        0},
         1896  +      { OP_ColumnName,  0, 0,        "synchronous"},
         1897  +      { OP_Callback,    1, 0,        0},
         1898  +    };
         1899  +    Vdbe *v = sqliteGetVdbe(pParse);
         1900  +    if( v==0 ) return;
         1901  +    if( pRight->z==pLeft->z ){
         1902  +      sqliteVdbeAddOp(v, OP_Integer, db->cache_size>=0, 0);
         1903  +      sqliteVdbeAddOpList(v, ArraySize(getSync), getSync);
         1904  +    }else{
         1905  +      int size = db->cache_size;
         1906  +      if( size<0 ) size = -size;
         1907  +      if( !getBoolean(zRight) ) size = -size;
         1908  +      db->cache_size = size;
         1909  +      sqliteBtreeSetCacheSize(db->pBe, db->cache_size);
  1791   1910       }
  1792   1911     }else
  1793   1912   
  1794   1913     if( sqliteStrICmp(zLeft, "vdbe_trace")==0 ){
  1795   1914       if( getBoolean(zRight) ){
  1796   1915         db->flags |= SQLITE_VdbeTrace;
  1797   1916       }else{

Changes to src/main.c.

    10     10   **
    11     11   *************************************************************************
    12     12   ** Main file for the SQLite library.  The routines in this file
    13     13   ** implement the programmer interface to the library.  Routines in
    14     14   ** other files are for internal use by SQLite and should not be
    15     15   ** accessed by users of the library.
    16     16   **
    17         -** $Id: main.c,v 1.67 2002/03/05 01:11:14 drh Exp $
           17  +** $Id: main.c,v 1.68 2002/03/06 22:01:36 drh Exp $
    18     18   */
    19     19   #include "sqliteInt.h"
    20     20   #include "os.h"
    21     21   
    22     22   /*
    23     23   ** This is the callback routine for the code that initializes the
    24     24   ** database.  See sqliteInit() below for additional information.
................................................................................
    40     40     ** make sure fields do not contain NULLs. Otherwise we might core
    41     41     ** when attempting to initialize from a corrupt database file. */
    42     42   
    43     43     assert( argc==4 );
    44     44     switch( argv[0][0] ){
    45     45       case 'c': {  /* Recommended pager cache size */
    46     46         int size = atoi(argv[3]);
    47         -      if( size!=0 ){
    48         -        sqliteBtreeSetCacheSize(db->pBe, size);
    49         -      }
           47  +      if( size==0 ){ size = MAX_PAGES; }
           48  +      db->cache_size = size;
           49  +      sqliteBtreeSetCacheSize(db->pBe, size);
    50     50         break;
    51     51       }
    52     52       case 'f': {  /* File format */
    53     53         db->file_format = atoi(argv[3]);
    54     54         break;
    55     55       }
    56     56       case 's': { /* Schema cookie */

Changes to src/pager.c.

    14     14   ** The pager is used to access a database disk file.  It implements
    15     15   ** atomic commit and rollback through the use of a journal file that
    16     16   ** is separate from the database file.  The pager also implements file
    17     17   ** locking to prevent two processes from writing the same database
    18     18   ** file simultaneously, or one process from reading the database while
    19     19   ** another is writing.
    20     20   **
    21         -** @(#) $Id: pager.c,v 1.43 2002/03/05 12:41:20 drh Exp $
           21  +** @(#) $Id: pager.c,v 1.44 2002/03/06 22:01:36 drh Exp $
    22     22   */
    23     23   #include "sqliteInt.h"
    24     24   #include "pager.h"
    25     25   #include "os.h"
    26     26   #include <assert.h>
    27     27   #include <string.h>
    28     28   
................................................................................
   430    430       rc = SQLITE_CORRUPT;
   431    431     }
   432    432     return rc;
   433    433   }
   434    434   
   435    435   /*
   436    436   ** Change the maximum number of in-memory pages that are allowed.
          437  +**
          438  +** The maximum number is the absolute value of the mxPage parameter.
          439  +** If mxPage is negative, the noSync flag is also set.  noSync bypasses
          440  +** calls to sqliteOsSync().  The pager runs much faster with noSync on,
          441  +** but if the operating system crashes or there is an abrupt power 
          442  +** failure, the database file might be left in an inconsistent and
          443  +** unrepairable state.  
   437    444   */
   438    445   void sqlitepager_set_cachesize(Pager *pPager, int mxPage){
   439    446     if( mxPage>=0 ){
   440    447       pPager->noSync = 0;
   441    448     }else{
   442    449       pPager->noSync = 1;
   443    450       mxPage = -mxPage;

Changes to src/sqliteInt.h.

     7      7   **    May you do good and not evil.
     8      8   **    May you find forgiveness for yourself and forgive others.
     9      9   **    May you share freely, never taking more than you give.
    10     10   **
    11     11   *************************************************************************
    12     12   ** Internal interface definitions for SQLite.
    13     13   **
    14         -** @(#) $Id: sqliteInt.h,v 1.102 2002/03/05 01:11:14 drh Exp $
           14  +** @(#) $Id: sqliteInt.h,v 1.103 2002/03/06 22:01:36 drh Exp $
    15     15   */
    16     16   #include "sqlite.h"
    17     17   #include "hash.h"
    18     18   #include "vdbe.h"
    19     19   #include "parse.h"
    20     20   #include "btree.h"
    21     21   #include <stdio.h>
................................................................................
   151    151   struct sqlite {
   152    152     Btree *pBe;                   /* The B*Tree backend */
   153    153     Btree *pBeTemp;               /* Backend for session temporary tables */
   154    154     int flags;                    /* Miscellanous flags. See below */
   155    155     int file_format;              /* What file format version is this database? */
   156    156     int schema_cookie;            /* Magic number that changes with the schema */
   157    157     int next_cookie;              /* Value of schema_cookie after commit */
          158  +  int cache_size;               /* Number of pages to use in the cache */
   158    159     int nTable;                   /* Number of tables in the database */
   159    160     void *pBusyArg;               /* 1st Argument to the busy callback */
   160    161     int (*xBusyCallback)(void *,const char*,int);  /* The busy callback */
   161    162     Hash tblHash;                 /* All tables indexed by name */
   162    163     Hash idxHash;                 /* All (named) indices indexed by name */
   163    164     Hash tblDrop;                 /* Uncommitted DROP TABLEs */
   164    165     Hash idxDrop;                 /* Uncommitted DROP INDEXs */

Changes to src/vdbe.c.

    26     26   ** type to the other occurs as necessary.
    27     27   ** 
    28     28   ** Most of the code in this file is taken up by the sqliteVdbeExec()
    29     29   ** function which does the work of interpreting a VDBE program.
    30     30   ** But other routines are also provided to help in building up
    31     31   ** a program instruction by instruction.
    32     32   **
    33         -** $Id: vdbe.c,v 1.132 2002/03/06 03:08:26 drh Exp $
           33  +** $Id: vdbe.c,v 1.133 2002/03/06 22:01:36 drh Exp $
    34     34   */
    35     35   #include "sqliteInt.h"
    36     36   #include <ctype.h>
    37     37   
    38     38   /*
    39     39   ** The following global variable is incremented every time a cursor
    40     40   ** moves, either by the OP_MoveTo or the OP_Next opcode.  The test
................................................................................
  4305   4305     int tos = ++p->tos;
  4306   4306     int i = pOp->p1;
  4307   4307     VERIFY( if( NeedStack(p, tos) ) goto no_mem; )
  4308   4308     VERIFY( if( i<0 || i>=p->nMem ) goto bad_instruction; )
  4309   4309     memcpy(&aStack[tos], &p->aMem[i].s, sizeof(aStack[tos])-NBFS);;
  4310   4310     if( aStack[tos].flags & STK_Str ){
  4311   4311       zStack[tos] = p->aMem[i].z;
  4312         -    aStack[tos].flags = STK_Str | STK_Static;
         4312  +    aStack[tos].flags |= STK_Static;
         4313  +    aStack[tos].flags &= ~STK_Dyn;
  4313   4314     }
  4314   4315     break;
  4315   4316   }
  4316   4317   
  4317   4318   /* Opcode: AggReset * P2 *
  4318   4319   **
  4319   4320   ** Reset the aggregator so that it no longer contains any data.

Changes to test/all.test.

     6      6   #    May you do good and not evil.
     7      7   #    May you find forgiveness for yourself and forgive others.
     8      8   #    May you share freely, never taking more than you give.
     9      9   #
    10     10   #***********************************************************************
    11     11   # This file runs all tests.
    12     12   #
    13         -# $Id: all.test,v 1.12 2002/02/18 01:17:00 drh Exp $
           13  +# $Id: all.test,v 1.13 2002/03/06 22:01:36 drh Exp $
    14     14   
    15     15   set testdir [file dirname $argv0]
    16     16   source $testdir/tester.tcl
    17     17   rename finish_test really_finish_test
    18     18   proc finish_test {} {memleak_check}
    19     19   
    20     20   if {[file exists ./sqlite_test_count]} {
................................................................................
    33     33     all.test
    34     34     quick.test
    35     35     malloc.test
    36     36     btree2.test
    37     37   }
    38     38   
    39     39   for {set Counter 0} {$Counter<$COUNT && $nErr==0} {incr Counter} {
           40  +  if {$Counter%2} {
           41  +    set ::SETUP_SQL {PRAGMA default_synchronous=off;}
           42  +  } else {
           43  +    catch {unset ::SETUP_SQL}
           44  +  }
    40     45     foreach testfile [lsort -dictionary [glob $testdir/*.test]] {
    41     46       set tail [file tail $testfile]
    42     47       if {[lsearch -exact $EXCLUDE $tail]>=0} continue
    43     48       source $testfile
    44     49     }
    45     50     if {[info exists Leak]} {
    46     51       lappend LeakList $Leak

Added test/pragma.test.

            1  +# 2002 March 6
            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  +#
           13  +# This file implements tests for the PRAGMA command.
           14  +#
           15  +# $Id: pragma.test,v 1.1 2002/03/06 22:01:37 drh Exp $
           16  +
           17  +set testdir [file dirname $argv0]
           18  +source $testdir/tester.tcl
           19  +
           20  +# Delete the preexisting database to avoid the special setup
           21  +# that the "all.test" script does.
           22  +#
           23  +db close
           24  +file delete test.db
           25  +sqlite db test.db
           26  +
           27  +do_test pragma-1.1 {
           28  +  execsql {
           29  +    PRAGMA cache_size;
           30  +    PRAGMA default_cache_size;
           31  +    PRAGMA synchronous;
           32  +    PRAGMA default_synchronous;
           33  +  }
           34  +} {2000 2000 1 1}
           35  +do_test pragma-1.2 {
           36  +  execsql {
           37  +    PRAGMA cache_size=1234;
           38  +    PRAGMA cache_size;
           39  +    PRAGMA default_cache_size;
           40  +    PRAGMA synchronous;
           41  +    PRAGMA default_synchronous;
           42  +  }
           43  +} {1234 2000 1 1}
           44  +do_test pragma-1.3 {
           45  +  db close
           46  +  sqlite db test.db
           47  +  execsql {
           48  +    PRAGMA cache_size;
           49  +    PRAGMA default_cache_size;
           50  +    PRAGMA synchronous;
           51  +    PRAGMA default_synchronous;
           52  +  }
           53  +} {2000 2000 1 1}
           54  +do_test pragma-1.4 {
           55  +  execsql {
           56  +    PRAGMA synchronous=OFF;
           57  +    PRAGMA cache_size;
           58  +    PRAGMA default_cache_size;
           59  +    PRAGMA synchronous;
           60  +    PRAGMA default_synchronous;
           61  +  }
           62  +} {2000 2000 0 1}
           63  +do_test pragma-1.5 {
           64  +  execsql {
           65  +    PRAGMA cache_size=4321;
           66  +    PRAGMA cache_size;
           67  +    PRAGMA default_cache_size;
           68  +    PRAGMA synchronous;
           69  +    PRAGMA default_synchronous;
           70  +  }
           71  +} {4321 2000 0 1}
           72  +do_test pragma-1.6 {
           73  +  execsql {
           74  +    PRAGMA synchronous=ON;
           75  +    PRAGMA cache_size;
           76  +    PRAGMA default_cache_size;
           77  +    PRAGMA synchronous;
           78  +    PRAGMA default_synchronous;
           79  +  }
           80  +} {4321 2000 1 1}
           81  +do_test pragma-1.7 {
           82  +  db close
           83  +  sqlite db test.db
           84  +  execsql {
           85  +    PRAGMA cache_size;
           86  +    PRAGMA default_cache_size;
           87  +    PRAGMA synchronous;
           88  +    PRAGMA default_synchronous;
           89  +  }
           90  +} {2000 2000 1 1}
           91  +do_test pragma-1.8 {
           92  +  execsql {
           93  +    PRAGMA default_synchronous=OFF;
           94  +    PRAGMA cache_size;
           95  +    PRAGMA default_cache_size;
           96  +    PRAGMA synchronous;
           97  +    PRAGMA default_synchronous;
           98  +  }
           99  +} {2000 2000 0 0}
          100  +do_test pragma-1.9 {
          101  +  execsql {
          102  +    PRAGMA default_cache_size=123;
          103  +    PRAGMA cache_size;
          104  +    PRAGMA default_cache_size;
          105  +    PRAGMA synchronous;
          106  +    PRAGMA default_synchronous;
          107  +  }
          108  +} {123 123 0 0}
          109  +do_test pragma-1.10 {
          110  +  db close
          111  +  sqlite db test.db
          112  +  execsql {
          113  +    PRAGMA cache_size;
          114  +    PRAGMA default_cache_size;
          115  +    PRAGMA synchronous;
          116  +    PRAGMA default_synchronous;
          117  +  }
          118  +} {123 123 0 0}
          119  +
          120  +finish_test

Changes to test/tester.tcl.

     7      7   #    May you find forgiveness for yourself and forgive others.
     8      8   #    May you share freely, never taking more than you give.
     9      9   #
    10     10   #***********************************************************************
    11     11   # This file implements some common TCL routines used for regression
    12     12   # testing the SQLite library
    13     13   #
    14         -# $Id: tester.tcl,v 1.21 2001/11/09 22:41:45 drh Exp $
           14  +# $Id: tester.tcl,v 1.22 2002/03/06 22:01:37 drh Exp $
    15     15   
    16     16   # Make sure tclsqlite was compiled correctly.  Abort now with an
    17     17   # error message if not.
    18     18   #
    19     19   if {[sqlite -tcl-uses-utf]} {
    20     20     if {"\u1234"=="u1234"} {
    21     21       puts stderr "***** BUILD PROBLEM *****"
................................................................................
    42     42   
    43     43   # Create a test database
    44     44   #
    45     45   catch {db close}
    46     46   file delete -force test.db
    47     47   file delete -force test.db-journal
    48     48   sqlite db ./test.db
           49  +if {[info exists ::SETUP_SQL]} {
           50  +  db eval $::SETUP_SQL
           51  +}
    49     52   
    50     53   # Abort early if this script has been run before.
    51     54   #
    52     55   if {[info exists nTest]} return
    53     56   
    54     57   # Set the test counters to zero
    55     58   #

Changes to tool/speedtest.tcl.

    33     33     set format {<tr><td>%s</td><td align="right">&nbsp;&nbsp;&nbsp;%.3f</td></tr>}
    34     34     set delay 1000
    35     35     exec sync; after $delay;
    36     36     set t [time "exec psql drh <$sqlfile" 1]
    37     37     set t [expr {[lindex $t 0]/1000000.0}]
    38     38     puts [format $format PostgreSQL: $t]
    39     39     exec sync; after $delay;
    40         -#  set t [time "exec mysql -f drh <$sqlfile" 1]
    41         -#  set t [expr {[lindex $t 0]/1000000.0}]
    42         -#  puts [format $format MySQL: $t]
           40  +  set t [time "exec mysql -f drh <$sqlfile" 1]
           41  +  set t [expr {[lindex $t 0]/1000000.0}]
           42  +  puts [format $format MySQL: $t]
    43     43   #  set t [time "exec ./sqlite232 s232.db <$sqlfile" 1]
    44     44   #  set t [expr {[lindex $t 0]/1000000.0}]
    45     45   #  puts [format $format {SQLite 2.3.2:} $t]
    46     46   #  set t [time "exec ./sqlite-100 s100.db <$sqlfile" 1]
    47     47   #  set t [expr {[lindex $t 0]/1000000.0}]
    48     48   #  puts [format $format {SQLite 2.4 (cache=100):} $t]
    49     49     exec sync; after $delay;
................................................................................
    69     69     drop table t1;
    70     70     drop table t2;
    71     71   }
    72     72   close $fd
    73     73   catch {exec psql drh <clear.sql}
    74     74   catch {exec mysql drh <clear.sql}
    75     75   set fd [open 2kinit.sql w]
    76         -puts $fd {PRAGMA cache_size=2000; PRAGMA synchronous=on;}
           76  +puts $fd {
           77  +  PRAGMA default_cache_size=2000;
           78  +  PRAGMA default_synchronous=on;
           79  +}
    77     80   close $fd
    78     81   exec ./sqlite240 s2k.db <2kinit.sql
    79         -exec ./sqlite-t1 st1.db <2kinit.sql
    80     82   set fd [open nosync-init.sql w]
    81         -puts $fd {PRAGMA cache_size=2000; PRAGMA synchronous=off;}
           83  +puts $fd {
           84  +  PRAGMA default_cache_size=2000;
           85  +  PRAGMA default_synchronous=off;
           86  +}
    82     87   close $fd
    83     88   exec ./sqlite240 sns.db <nosync-init.sql
    84     89   set ones {zero one two three four five six seven eight nine
    85     90             ten eleven twelve thirteen fourteen fifteen sixteen seventeen
    86     91             eighteen nineteen}
    87     92   set tens {{} ten twenty thirty forty fifty sixty seventy eighty ninety}
    88     93   proc number_name {n} {
................................................................................
   153    158   runtest {100 SELECTs on a string comparison}
   154    159   
   155    160   
   156    161   
   157    162   set fd [open test$cnt.sql w]
   158    163   puts $fd {CREATE INDEX i2a ON t2(a);}
   159    164   puts $fd {CREATE INDEX i2b ON t2(b);}
   160         -puts $fd {VACUUM;}
   161    165   close $fd
   162    166   runtest {Creating an index}
   163    167   
   164    168   
   165    169   
   166    170   set fd [open test$cnt.sql w]
   167    171   for {set i 0} {$i<5000} {incr i} {
................................................................................
   172    176   close $fd
   173    177   runtest {5000 SELECTs with an index}
   174    178   
   175    179   
   176    180   
   177    181   set fd [open test$cnt.sql w]
   178    182   puts $fd "BEGIN;"
   179         -for {set i 0} {$i<100} {incr i} {
          183  +for {set i 0} {$i<1000} {incr i} {
   180    184     set lwr [expr {$i*10}]
   181    185     set upr [expr {($i+1)*10}]
   182    186     puts $fd "UPDATE t1 SET b=b*2 WHERE a>=$lwr AND a<$upr;"
   183    187   }
   184    188   puts $fd "COMMIT;"
   185    189   close $fd
   186         -runtest {100 UPDATEs without an index}
          190  +runtest {1000 UPDATEs without an index}
   187    191   
   188    192   
          193  +
          194  +set fd [open test$cnt.sql w]
          195  +puts $fd "BEGIN;"
          196  +for {set i 1} {$i<=25000} {incr i} {
          197  +  set r [expr {int(rand()*500000)}]
          198  +  puts $fd "UPDATE t2 SET b=$r WHERE a=$i;"
          199  +}
          200  +puts $fd "COMMIT;"
          201  +close $fd
          202  +runtest {25000 UPDATEs with an index}
          203  +
   189    204   
   190    205   set fd [open test$cnt.sql w]
   191    206   puts $fd "BEGIN;"
   192    207   for {set i 1} {$i<=25000} {incr i} {
   193         -  puts $fd "UPDATE t2 SET b=b+a WHERE a=$i;"
          208  +  set r [expr {int(rand()*500000)}]
          209  +  puts $fd "UPDATE t2 SET c='[number_name $r]' WHERE a=$i;"
   194    210   }
   195    211   puts $fd "COMMIT;"
   196    212   close $fd
   197         -runtest {25000 UPDATEs with an index}
          213  +runtest {25000 text UPDATEs with an index}
   198    214   
   199    215   
   200    216   
   201    217   set fd [open test$cnt.sql w]
   202    218   puts $fd "BEGIN;"
   203    219   puts $fd "INSERT INTO t1 SELECT * FROM t2;"
   204    220   puts $fd "INSERT INTO t2 SELECT * FROM t1;"
................................................................................
   228    244   runtest {A big INSERT after a big DELETE}
   229    245   
   230    246   
   231    247   
   232    248   set fd [open test$cnt.sql w]
   233    249   puts $fd {BEGIN;}
   234    250   puts $fd {DELETE FROM t1;}
   235         -for {set i 1} {$i<=1000} {incr i} {
          251  +for {set i 1} {$i<=3000} {incr i} {
   236    252     set r [expr {int(rand()*100000)}]
   237    253     puts $fd "INSERT INTO t1 VALUES($i,$r,'[number_name $r]');"
   238    254   }
   239    255   puts $fd {COMMIT;}
   240    256   close $fd
   241    257   runtest {A big DELETE followed by many small INSERTs}
   242    258   

Added tool/speedtest2.tcl.

            1  +#!/usr/bin/tclsh
            2  +#
            3  +# Run this script using TCLSH to do a speed comparison between
            4  +# various versions of SQLite and PostgreSQL and MySQL
            5  +#
            6  +
            7  +# Run a test
            8  +#
            9  +set cnt 1
           10  +proc runtest {title} {
           11  +  global cnt
           12  +  set sqlfile test$cnt.sql
           13  +  puts "<h2>Test $cnt: $title</h2>"
           14  +  incr cnt
           15  +  set fd [open $sqlfile r]
           16  +  set sql [string trim [read $fd [file size $sqlfile]]]
           17  +  close $fd
           18  +  set sx [split $sql \n]
           19  +  set n [llength $sx]
           20  +  if {$n>8} {
           21  +    set sql {}
           22  +    for {set i 0} {$i<3} {incr i} {append sql [lindex $sx $i]<br>\n}
           23  +    append sql  "<i>... [expr {$n-6}] lines omitted</i><br>\n"
           24  +    for {set i [expr {$n-3}]} {$i<$n} {incr i} {
           25  +      append sql [lindex $sx $i]<br>\n
           26  +    }
           27  +  } else {
           28  +    regsub -all \n [string trim $sql] <br> sql
           29  +  }
           30  +  puts "<blockquote>"
           31  +  puts "$sql"
           32  +  puts "</blockquote><table border=0 cellpadding=0 cellspacing=0>"
           33  +  set format {<tr><td>%s</td><td align="right">&nbsp;&nbsp;&nbsp;%.3f</td></tr>}
           34  +  set delay 1000
           35  +  exec sync; after $delay;
           36  +  set t [time "exec psql drh <$sqlfile" 1]
           37  +  set t [expr {[lindex $t 0]/1000000.0}]
           38  +  puts [format $format PostgreSQL: $t]
           39  +  exec sync; after $delay;
           40  +  set t [time "exec mysql -f drh <$sqlfile" 1]
           41  +  set t [expr {[lindex $t 0]/1000000.0}]
           42  +  puts [format $format MySQL: $t]
           43  +#  set t [time "exec ./sqlite232 s232.db <$sqlfile" 1]
           44  +#  set t [expr {[lindex $t 0]/1000000.0}]
           45  +#  puts [format $format {SQLite 2.3.2:} $t]
           46  +#  set t [time "exec ./sqlite-100 s100.db <$sqlfile" 1]
           47  +#  set t [expr {[lindex $t 0]/1000000.0}]
           48  +#  puts [format $format {SQLite 2.4 (cache=100):} $t]
           49  +  exec sync; after $delay;
           50  +  set t [time "exec ./sqlite240 s2k.db <$sqlfile" 1]
           51  +  set t [expr {[lindex $t 0]/1000000.0}]
           52  +  puts [format $format {SQLite 2.4:} $t]
           53  +  exec sync; after $delay;
           54  +  set t [time "exec ./sqlite240 sns.db <$sqlfile" 1]
           55  +  set t [expr {[lindex $t 0]/1000000.0}]
           56  +  puts [format $format {SQLite 2.4 (nosync):} $t]
           57  +#  set t [time "exec ./sqlite-t1 st1.db <$sqlfile" 1]
           58  +#  set t [expr {[lindex $t 0]/1000000.0}]
           59  +#  puts [format $format {SQLite 2.4 (test):} $t]
           60  +  puts "</table>"
           61  +}
           62  +
           63  +# Initialize the environment
           64  +#
           65  +expr srand(1)
           66  +catch {exec /bin/sh -c {rm -f s*.db}}
           67  +set fd [open clear.sql w]
           68  +puts $fd {
           69  +  drop table t1;
           70  +  drop table t2;
           71  +}
           72  +close $fd
           73  +catch {exec psql drh <clear.sql}
           74  +catch {exec mysql drh <clear.sql}
           75  +set fd [open 2kinit.sql w]
           76  +puts $fd {
           77  +  PRAGMA default_cache_size=2000;
           78  +  PRAGMA default_synchronous=on;
           79  +}
           80  +close $fd
           81  +exec ./sqlite240 s2k.db <2kinit.sql
           82  +exec ./sqlite-t1 st1.db <2kinit.sql
           83  +set fd [open nosync-init.sql w]
           84  +puts $fd {
           85  +  PRAGMA default_cache_size=2000;
           86  +  PRAGMA default_synchronous=off;
           87  +}
           88  +close $fd
           89  +exec ./sqlite240 sns.db <nosync-init.sql
           90  +set ones {zero one two three four five six seven eight nine
           91  +          ten eleven twelve thirteen fourteen fifteen sixteen seventeen
           92  +          eighteen nineteen}
           93  +set tens {{} ten twenty thirty forty fifty sixty seventy eighty ninety}
           94  +proc number_name {n} {
           95  +  if {$n>=1000} {
           96  +    set txt "[number_name [expr {$n/1000}]] thousand"
           97  +    set n [expr {$n%1000}]
           98  +  } else {
           99  +    set txt {}
          100  +  }
          101  +  if {$n>=100} {
          102  +    append txt " [lindex $::ones [expr {$n/100}]] hundred"
          103  +    set n [expr {$n%100}]
          104  +  }
          105  +  if {$n>=20} {
          106  +    append txt " [lindex $::tens [expr {$n/10}]]"
          107  +    set n [expr {$n%10}]
          108  +  }
          109  +  if {$n>0} {
          110  +    append txt " [lindex $::ones $n]"
          111  +  }
          112  +  set txt [string trim $txt]
          113  +  if {$txt==""} {set txt zero}
          114  +  return $txt
          115  +}
          116  +
          117  +
          118  +set fd [open test$cnt.sql w]
          119  +puts $fd "BEGIN;"
          120  +puts $fd "CREATE TABLE t1(a INTEGER, b INTEGER, c VARCHAR(100));"
          121  +for {set i 1} {$i<=25000} {incr i} {
          122  +  set r [expr {int(rand()*500000)}]
          123  +  puts $fd "INSERT INTO t1 VALUES($i,$r,'[number_name $r]');"
          124  +}
          125  +puts $fd "COMMIT;"
          126  +close $fd
          127  +runtest {25000 INSERTs in a transaction}
          128  +
          129  +
          130  +set fd [open test$cnt.sql w]
          131  +puts $fd "DELETE FROM t1;"
          132  +close $fd
          133  +runtest {DELETE everything}
          134  +
          135  +
          136  +set fd [open test$cnt.sql w]
          137  +puts $fd "BEGIN;"
          138  +for {set i 1} {$i<=25000} {incr i} {
          139  +  set r [expr {int(rand()*500000)}]
          140  +  puts $fd "INSERT INTO t1 VALUES($i,$r,'[number_name $r]');"
          141  +}
          142  +puts $fd "COMMIT;"
          143  +close $fd
          144  +runtest {25000 INSERTs in a transaction}
          145  +
          146  +
          147  +set fd [open test$cnt.sql w]
          148  +puts $fd "DELETE FROM t1;"
          149  +close $fd
          150  +runtest {DELETE everything}
          151  +
          152  +
          153  +set fd [open test$cnt.sql w]
          154  +puts $fd "BEGIN;"
          155  +for {set i 1} {$i<=25000} {incr i} {
          156  +  set r [expr {int(rand()*500000)}]
          157  +  puts $fd "INSERT INTO t1 VALUES($i,$r,'[number_name $r]');"
          158  +}
          159  +puts $fd "COMMIT;"
          160  +close $fd
          161  +runtest {25000 INSERTs in a transaction}
          162  +
          163  +
          164  +set fd [open test$cnt.sql w]
          165  +puts $fd "DELETE FROM t1;"
          166  +close $fd
          167  +runtest {DELETE everything}
          168  +
          169  +
          170  +set fd [open test$cnt.sql w]
          171  +puts $fd "BEGIN;"
          172  +for {set i 1} {$i<=25000} {incr i} {
          173  +  set r [expr {int(rand()*500000)}]
          174  +  puts $fd "INSERT INTO t1 VALUES($i,$r,'[number_name $r]');"
          175  +}
          176  +puts $fd "COMMIT;"
          177  +close $fd
          178  +runtest {25000 INSERTs in a transaction}
          179  +
          180  +
          181  +set fd [open test$cnt.sql w]
          182  +puts $fd "DELETE FROM t1;"
          183  +close $fd
          184  +runtest {DELETE everything}
          185  +
          186  +
          187  +set fd [open test$cnt.sql w]
          188  +puts $fd "BEGIN;"
          189  +for {set i 1} {$i<=25000} {incr i} {
          190  +  set r [expr {int(rand()*500000)}]
          191  +  puts $fd "INSERT INTO t1 VALUES($i,$r,'[number_name $r]');"
          192  +}
          193  +puts $fd "COMMIT;"
          194  +close $fd
          195  +runtest {25000 INSERTs in a transaction}
          196  +
          197  +
          198  +set fd [open test$cnt.sql w]
          199  +puts $fd "DELETE FROM t1;"
          200  +close $fd
          201  +runtest {DELETE everything}
          202  +
          203  +
          204  +set fd [open test$cnt.sql w]
          205  +puts $fd {DROP TABLE t1;}
          206  +close $fd
          207  +runtest {DROP TABLE}