SQLite

Check-in [161c0c5f5d]
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
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1: 161c0c5f5db66815e4345c9b5f7a600c03a67475
User & Date: drh 2002-03-06 22:01:35.000
Context
2002-03-06
22:04
Beta 2 (CVS 422) (check-in: 6c3fb5470e 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: 161c0c5f5d user: drh tags: trunk)
03:08
Optimizations to the processing of integer comparisons. (CVS 420) (check-in: b7a7dae919 user: drh tags: trunk)
Changes
Unified Diff Ignore Whitespace Patch
Changes to src/btree.c.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
/*
** 2001 September 15
**
** 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.
**
*************************************************************************
** $Id: btree.c,v 1.59 2002/03/05 12:41:20 drh Exp $
**
** This file implements a external (disk-based) database using BTrees.
** For a detailed discussion of BTrees, refer to
**
**     Donald E. Knuth, THE ART OF COMPUTER PROGRAMMING, Volume 3:
**     "Sorting And Searching", pages 473-480. Addison-Wesley
**     Publishing Company, Reading, Massachusetts.











|







1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
/*
** 2001 September 15
**
** 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.
**
*************************************************************************
** $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
**
**     Donald E. Knuth, THE ART OF COMPUTER PROGRAMMING, Volume 3:
**     "Sorting And Searching", pages 473-480. Addison-Wesley
**     Publishing Company, Reading, Massachusetts.
646
647
648
649
650
651
652












653
654
655
656
657
658
659
  sqliteHashClear(&pBt->locks);
  sqliteFree(pBt);
  return SQLITE_OK;
}

/*
** Change the limit on the number of pages allowed the cache.












*/
int sqliteBtreeSetCacheSize(Btree *pBt, int mxPage){
  sqlitepager_set_cachesize(pBt->pPager, mxPage);
  return SQLITE_OK;
}

/*







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







646
647
648
649
650
651
652
653
654
655
656
657
658
659
660
661
662
663
664
665
666
667
668
669
670
671
  sqliteHashClear(&pBt->locks);
  sqliteFree(pBt);
  return SQLITE_OK;
}

/*
** 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);
  return SQLITE_OK;
}

/*
Changes to src/build.c.
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
**     COPY
**     VACUUM
**     BEGIN TRANSACTION
**     COMMIT
**     ROLLBACK
**     PRAGMA
**
** $Id: build.c,v 1.85 2002/03/05 01:11:13 drh Exp $
*/
#include "sqliteInt.h"
#include <ctype.h>

/*
** This routine is called after a single SQL statement has been
** parsed and we want to execute the VDBE code to implement 







|







21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
**     COPY
**     VACUUM
**     BEGIN TRANSACTION
**     COMMIT
**     ROLLBACK
**     PRAGMA
**
** $Id: build.c,v 1.86 2002/03/06 22:01:36 drh Exp $
*/
#include "sqliteInt.h"
#include <ctype.h>

/*
** This routine is called after a single SQL statement has been
** parsed and we want to execute the VDBE code to implement 
1732
1733
1734
1735
1736
1737
1738
















1739
1740
1741
1742




1743
1744
1745
1746
1747
1748
1749
1750
1751
1752
1753
1754
1755
1756
1757
1758
1759
1760
1761
1762


1763
1764
1765














1766













































1767
1768
1769
1770
1771
1772
1773
1774
1775
1776
1777
1778
1779
1780
1781
1782


1783
1784




1785
1786
1787

1788
1789
1790































1791
1792
1793
1794
1795
1796
1797
    zRight = 0;
    sqliteSetNString(&zRight, "-", 1, pRight->z, pRight->n, 0);
  }else{
    zRight = sqliteStrNDup(pRight->z, pRight->n);
    sqliteDequote(zRight);
  }
 
















  if( sqliteStrICmp(zLeft,"cache_size")==0 ){
    static VdbeOp getCacheSize[] = {
      { OP_ReadCookie,  0, 2,        0},
      { OP_AbsValue,    0, 0,        0},




      { 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 ){
      sqliteVdbeAddOpList(v, ArraySize(getCacheSize), getCacheSize);
    }else{
      int addr;
      int size = atoi(zRight);
      if( size<0 ) size = -size;
      sqliteBeginWriteOperation(pParse);
      sqliteVdbeAddOp(v, OP_Integer, size, 0);
      sqliteVdbeAddOp(v, OP_ReadCookie, 0, 2);
      addr = sqliteVdbeAddOp(v, OP_Integer, 0, 0);
      sqliteVdbeAddOp(v, OP_Ge, 0, addr+3);
      sqliteVdbeAddOp(v, OP_Negative, 0, 0);
      sqliteVdbeAddOp(v, OP_SetCookie, 0, 2);
      sqliteEndWriteOperation(pParse);


    }
  }else















  if( sqliteStrICmp(zLeft,"synchronous")==0 ){













































    static VdbeOp getSync[] = {
      { OP_Integer,     0, 0,        0},
      { OP_ReadCookie,  0, 2,        0},
      { OP_Integer,     0, 0,        0},
      { OP_Lt,          0, 5,        0},
      { OP_AddImm,      1, 0,        0},
      { 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 ){
      sqliteVdbeAddOpList(v, ArraySize(getSync), getSync);
    }else{
      int addr;


      sqliteBeginWriteOperation(pParse);
      sqliteVdbeAddOp(v, OP_ReadCookie, 0, 2);




      sqliteVdbeAddOp(v, OP_AbsValue, 0, 0);
      if( !getBoolean(zRight) ){
        sqliteVdbeAddOp(v, OP_Negative, 0, 0);

      }
      sqliteVdbeAddOp(v, OP_SetCookie, 0, 2);
      sqliteEndWriteOperation(pParse);































    }
  }else

  if( sqliteStrICmp(zLeft, "vdbe_trace")==0 ){
    if( getBoolean(zRight) ){
      db->flags |= SQLITE_VdbeTrace;
    }else{







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



>
>
>
>




















>
>



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
















>
>


>
>
>
>



>



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







1732
1733
1734
1735
1736
1737
1738
1739
1740
1741
1742
1743
1744
1745
1746
1747
1748
1749
1750
1751
1752
1753
1754
1755
1756
1757
1758
1759
1760
1761
1762
1763
1764
1765
1766
1767
1768
1769
1770
1771
1772
1773
1774
1775
1776
1777
1778
1779
1780
1781
1782
1783
1784
1785
1786
1787
1788
1789
1790
1791
1792
1793
1794
1795
1796
1797
1798
1799
1800
1801
1802
1803
1804
1805
1806
1807
1808
1809
1810
1811
1812
1813
1814
1815
1816
1817
1818
1819
1820
1821
1822
1823
1824
1825
1826
1827
1828
1829
1830
1831
1832
1833
1834
1835
1836
1837
1838
1839
1840
1841
1842
1843
1844
1845
1846
1847
1848
1849
1850
1851
1852
1853
1854
1855
1856
1857
1858
1859
1860
1861
1862
1863
1864
1865
1866
1867
1868
1869
1870
1871
1872
1873
1874
1875
1876
1877
1878
1879
1880
1881
1882
1883
1884
1885
1886
1887
1888
1889
1890
1891
1892
1893
1894
1895
1896
1897
1898
1899
1900
1901
1902
1903
1904
1905
1906
1907
1908
1909
1910
1911
1912
1913
1914
1915
1916
    zRight = 0;
    sqliteSetNString(&zRight, "-", 1, pRight->z, pRight->n, 0);
  }else{
    zRight = sqliteStrNDup(pRight->z, pRight->n);
    sqliteDequote(zRight);
  }
 
  /*
  **  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},
    };
    Vdbe *v = sqliteGetVdbe(pParse);
    if( v==0 ) return;
    if( pRight->z==pLeft->z ){
      sqliteVdbeAddOpList(v, ArraySize(getCacheSize), getCacheSize);
    }else{
      int addr;
      int size = atoi(zRight);
      if( size<0 ) size = -size;
      sqliteBeginWriteOperation(pParse);
      sqliteVdbeAddOp(v, OP_Integer, size, 0);
      sqliteVdbeAddOp(v, OP_ReadCookie, 0, 2);
      addr = sqliteVdbeAddOp(v, OP_Integer, 0, 0);
      sqliteVdbeAddOp(v, OP_Ge, 0, addr+3);
      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

  /*
  **  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},
      { OP_Integer,     0, 0,        0},
      { OP_Lt,          0, 5,        0},
      { OP_AddImm,      1, 0,        0},
      { 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 ){
      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

  if( sqliteStrICmp(zLeft, "vdbe_trace")==0 ){
    if( getBoolean(zRight) ){
      db->flags |= SQLITE_VdbeTrace;
    }else{
Changes to src/main.c.
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
**
*************************************************************************
** Main file for the SQLite library.  The routines in this file
** implement the programmer interface to the library.  Routines in
** 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 $
*/
#include "sqliteInt.h"
#include "os.h"

/*
** This is the callback routine for the code that initializes the
** database.  See sqliteInit() below for additional information.







|







10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
**
*************************************************************************
** Main file for the SQLite library.  The routines in this file
** implement the programmer interface to the library.  Routines in
** other files are for internal use by SQLite and should not be
** accessed by users of the library.
**
** $Id: main.c,v 1.68 2002/03/06 22:01:36 drh Exp $
*/
#include "sqliteInt.h"
#include "os.h"

/*
** This is the callback routine for the code that initializes the
** database.  See sqliteInit() below for additional information.
40
41
42
43
44
45
46
47

48
49
50
51
52
53
54
55
56
  ** make sure fields do not contain NULLs. Otherwise we might core
  ** when attempting to initialize from a corrupt database file. */

  assert( argc==4 );
  switch( argv[0][0] ){
    case 'c': {  /* Recommended pager cache size */
      int size = atoi(argv[3]);
      if( size!=0 ){

        sqliteBtreeSetCacheSize(db->pBe, size);
      }
      break;
    }
    case 'f': {  /* File format */
      db->file_format = atoi(argv[3]);
      break;
    }
    case 's': { /* Schema cookie */







|
>
|
<







40
41
42
43
44
45
46
47
48
49

50
51
52
53
54
55
56
  ** make sure fields do not contain NULLs. Otherwise we might core
  ** when attempting to initialize from a corrupt database file. */

  assert( argc==4 );
  switch( argv[0][0] ){
    case 'c': {  /* Recommended pager cache size */
      int size = atoi(argv[3]);
      if( size==0 ){ size = MAX_PAGES; }
      db->cache_size = size;
      sqliteBtreeSetCacheSize(db->pBe, size);

      break;
    }
    case 'f': {  /* File format */
      db->file_format = atoi(argv[3]);
      break;
    }
    case 's': { /* Schema cookie */
Changes to src/pager.c.
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
** 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.43 2002/03/05 12:41:20 drh Exp $
*/
#include "sqliteInt.h"
#include "pager.h"
#include "os.h"
#include <assert.h>
#include <string.h>








|







14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
** 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.44 2002/03/06 22:01:36 drh Exp $
*/
#include "sqliteInt.h"
#include "pager.h"
#include "os.h"
#include <assert.h>
#include <string.h>

430
431
432
433
434
435
436







437
438
439
440
441
442
443
    rc = SQLITE_CORRUPT;
  }
  return rc;
}

/*
** Change the maximum number of in-memory pages that are allowed.







*/
void sqlitepager_set_cachesize(Pager *pPager, int mxPage){
  if( mxPage>=0 ){
    pPager->noSync = 0;
  }else{
    pPager->noSync = 1;
    mxPage = -mxPage;







>
>
>
>
>
>
>







430
431
432
433
434
435
436
437
438
439
440
441
442
443
444
445
446
447
448
449
450
    rc = SQLITE_CORRUPT;
  }
  return rc;
}

/*
** 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 ){
    pPager->noSync = 0;
  }else{
    pPager->noSync = 1;
    mxPage = -mxPage;
Changes to src/sqliteInt.h.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
/*
** 2001 September 15
**
** 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.
**
*************************************************************************
** Internal interface definitions for SQLite.
**
** @(#) $Id: sqliteInt.h,v 1.102 2002/03/05 01:11:14 drh Exp $
*/
#include "sqlite.h"
#include "hash.h"
#include "vdbe.h"
#include "parse.h"
#include "btree.h"
#include <stdio.h>













|







1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
/*
** 2001 September 15
**
** 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.
**
*************************************************************************
** Internal interface definitions for SQLite.
**
** @(#) $Id: sqliteInt.h,v 1.103 2002/03/06 22:01:36 drh Exp $
*/
#include "sqlite.h"
#include "hash.h"
#include "vdbe.h"
#include "parse.h"
#include "btree.h"
#include <stdio.h>
151
152
153
154
155
156
157

158
159
160
161
162
163
164
struct sqlite {
  Btree *pBe;                   /* The B*Tree backend */
  Btree *pBeTemp;               /* Backend for session temporary tables */
  int flags;                    /* Miscellanous flags. See below */
  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 nTable;                   /* Number of tables in the database */
  void *pBusyArg;               /* 1st Argument to the busy callback */
  int (*xBusyCallback)(void *,const char*,int);  /* The busy callback */
  Hash tblHash;                 /* All tables indexed by name */
  Hash idxHash;                 /* All (named) indices indexed by name */
  Hash tblDrop;                 /* Uncommitted DROP TABLEs */
  Hash idxDrop;                 /* Uncommitted DROP INDEXs */







>







151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
struct sqlite {
  Btree *pBe;                   /* The B*Tree backend */
  Btree *pBeTemp;               /* Backend for session temporary tables */
  int flags;                    /* Miscellanous flags. See below */
  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 */
  Hash tblHash;                 /* All tables indexed by name */
  Hash idxHash;                 /* All (named) indices indexed by name */
  Hash tblDrop;                 /* Uncommitted DROP TABLEs */
  Hash idxDrop;                 /* Uncommitted DROP INDEXs */
Changes to src/vdbe.c.
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
** type to the other occurs as necessary.
** 
** Most of the code in this file is taken up by the sqliteVdbeExec()
** function which does the work of interpreting a VDBE program.
** 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 $
*/
#include "sqliteInt.h"
#include <ctype.h>

/*
** The following global variable is incremented every time a cursor
** moves, either by the OP_MoveTo or the OP_Next opcode.  The test







|







26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
** type to the other occurs as necessary.
** 
** Most of the code in this file is taken up by the sqliteVdbeExec()
** function which does the work of interpreting a VDBE program.
** But other routines are also provided to help in building up
** a program instruction by instruction.
**
** $Id: vdbe.c,v 1.133 2002/03/06 22:01:36 drh Exp $
*/
#include "sqliteInt.h"
#include <ctype.h>

/*
** The following global variable is incremented every time a cursor
** moves, either by the OP_MoveTo or the OP_Next opcode.  The test
4305
4306
4307
4308
4309
4310
4311

4312
4313
4314
4315
4316
4317
4318
4319
  int tos = ++p->tos;
  int i = pOp->p1;
  VERIFY( if( NeedStack(p, tos) ) goto no_mem; )
  VERIFY( if( i<0 || i>=p->nMem ) goto bad_instruction; )
  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;
  }
  break;
}

/* Opcode: AggReset * P2 *
**
** Reset the aggregator so that it no longer contains any data.







>
|







4305
4306
4307
4308
4309
4310
4311
4312
4313
4314
4315
4316
4317
4318
4319
4320
  int tos = ++p->tos;
  int i = pOp->p1;
  VERIFY( if( NeedStack(p, tos) ) goto no_mem; )
  VERIFY( if( i<0 || i>=p->nMem ) goto bad_instruction; )
  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_Static;
    aStack[tos].flags &= ~STK_Dyn;
  }
  break;
}

/* Opcode: AggReset * P2 *
**
** Reset the aggregator so that it no longer contains any data.
Changes to test/all.test.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
# 2001 September 15
#
# 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 runs all tests.
#
# $Id: all.test,v 1.12 2002/02/18 01:17:00 drh Exp $

set testdir [file dirname $argv0]
source $testdir/tester.tcl
rename finish_test really_finish_test
proc finish_test {} {memleak_check}

if {[file exists ./sqlite_test_count]} {












|







1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
# 2001 September 15
#
# 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 runs all tests.
#
# $Id: all.test,v 1.13 2002/03/06 22:01:36 drh Exp $

set testdir [file dirname $argv0]
source $testdir/tester.tcl
rename finish_test really_finish_test
proc finish_test {} {memleak_check}

if {[file exists ./sqlite_test_count]} {
33
34
35
36
37
38
39





40
41
42
43
44
45
46
  all.test
  quick.test
  malloc.test
  btree2.test
}

for {set Counter 0} {$Counter<$COUNT && $nErr==0} {incr Counter} {





  foreach testfile [lsort -dictionary [glob $testdir/*.test]] {
    set tail [file tail $testfile]
    if {[lsearch -exact $EXCLUDE $tail]>=0} continue
    source $testfile
  }
  if {[info exists Leak]} {
    lappend LeakList $Leak







>
>
>
>
>







33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
  all.test
  quick.test
  malloc.test
  btree2.test
}

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
    source $testfile
  }
  if {[info exists Leak]} {
    lappend LeakList $Leak
Added test/pragma.test.
















































































































































































































































>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
# 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
Changes to test/tester.tcl.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
# 2001 September 15
#
# 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 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 $

# Make sure tclsqlite was compiled correctly.  Abort now with an
# error message if not.
#
if {[sqlite -tcl-uses-utf]} {
  if {"\u1234"=="u1234"} {
    puts stderr "***** BUILD PROBLEM *****"













|







1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
# 2001 September 15
#
# 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 some common TCL routines used for regression
# testing the SQLite library
#
# $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.
#
if {[sqlite -tcl-uses-utf]} {
  if {"\u1234"=="u1234"} {
    puts stderr "***** BUILD PROBLEM *****"
42
43
44
45
46
47
48



49
50
51
52
53
54
55

# Create a test database
#
catch {db close}
file delete -force test.db
file delete -force test.db-journal
sqlite db ./test.db




# Abort early if this script has been run before.
#
if {[info exists nTest]} return

# Set the test counters to zero
#







>
>
>







42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58

# Create a test database
#
catch {db close}
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.
#
if {[info exists nTest]} return

# Set the test counters to zero
#
Changes to tool/speedtest.tcl.
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
  set format {<tr><td>%s</td><td align="right">&nbsp;&nbsp;&nbsp;%.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;







|
|
|







33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
  set format {<tr><td>%s</td><td align="right">&nbsp;&nbsp;&nbsp;%.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;
69
70
71
72
73
74
75
76



77
78
79
80
81



82
83
84
85
86
87
88
  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 cache_size=2000; PRAGMA 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;}



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} {







|
>
>
>


<

|
>
>
>







69
70
71
72
73
74
75
76
77
78
79
80
81

82
83
84
85
86
87
88
89
90
91
92
93
  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

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} {
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187











188
189
190
191
192

193
194
195
196
197
198
199
200
201
202
203
204
runtest {100 SELECTs on a string comparison}



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}



set fd [open test$cnt.sql w]
for {set i 0} {$i<5000} {incr i} {
  set lwr [expr {$i*100}]
  set upr [expr {($i+1)*100}]
  puts $fd "SELECT count(*), avg(b) FROM t2 WHERE b>=$lwr AND b<$upr;"
}
close $fd
runtest {5000 SELECTs with an index}



set fd [open test$cnt.sql w]
puts $fd "BEGIN;"
for {set i 0} {$i<100} {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}














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;"
}
puts $fd "COMMIT;"
close $fd
runtest {25000 UPDATEs with an index}



set fd [open test$cnt.sql w]
puts $fd "BEGIN;"
puts $fd "INSERT INTO t1 SELECT * FROM t2;"
puts $fd "INSERT INTO t2 SELECT * FROM t1;"







<


















|






|

>
>
>
>
>
>
>
>
>
>
>





>
|



|







158
159
160
161
162
163
164

165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
runtest {100 SELECTs on a string comparison}



set fd [open test$cnt.sql w]
puts $fd {CREATE INDEX i2a ON t2(a);}
puts $fd {CREATE INDEX i2b ON t2(b);}

close $fd
runtest {Creating an index}



set fd [open test$cnt.sql w]
for {set i 0} {$i<5000} {incr i} {
  set lwr [expr {$i*100}]
  set upr [expr {($i+1)*100}]
  puts $fd "SELECT count(*), avg(b) FROM t2 WHERE b>=$lwr AND b<$upr;"
}
close $fd
runtest {5000 SELECTs with an index}



set fd [open test$cnt.sql w]
puts $fd "BEGIN;"
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 {1000 UPDATEs without 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 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;"
puts $fd "INSERT INTO t1 SELECT * FROM t2;"
puts $fd "INSERT INTO t2 SELECT * FROM t1;"
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
runtest {A big INSERT after a big DELETE}



set fd [open test$cnt.sql w]
puts $fd {BEGIN;}
puts $fd {DELETE FROM t1;}
for {set i 1} {$i<=1000} {incr i} {
  set r [expr {int(rand()*100000)}]
  puts $fd "INSERT INTO t1 VALUES($i,$r,'[number_name $r]');"
}
puts $fd {COMMIT;}
close $fd
runtest {A big DELETE followed by many small INSERTs}








|







244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
runtest {A big INSERT after a big DELETE}



set fd [open test$cnt.sql w]
puts $fd {BEGIN;}
puts $fd {DELETE FROM t1;}
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]');"
}
puts $fd {COMMIT;}
close $fd
runtest {A big DELETE followed by many small INSERTs}

Added tool/speedtest2.tcl.






























































































































































































































































































































































































































>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
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">&nbsp;&nbsp;&nbsp;%.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}