/ Check-in [414da4af]
Login

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

Overview
Comment:Add the ability to turn of calls to fsync() using the "synchronous" pragma. Increased the default cache size from 100 to 2000 and made the "cache_size" pragma persistent. (CVS 418)
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1:414da4af1f4aebc3936ca339fbc7932add081912
User & Date: drh 2002-03-05 01:11:13
Context
2002-03-05
12:41
Change the pager locking mechanism so that we don't have to write page 1 to the journal and to the database unless it actually changes. (CVS 419) check-in: 480eef1a user: drh tags: trunk
01:11
Add the ability to turn of calls to fsync() using the "synchronous" pragma. Increased the default cache size from 100 to 2000 and made the "cache_size" pragma persistent. (CVS 418) check-in: 414da4af user: drh tags: trunk
2002-03-04
02:26
Updates to the documentation. Changed version number to 2.4.0-beta1 (CVS 417) check-in: 36a8fe0a user: drh tags: trunk
Changes
Hide Diffs Unified Diffs Ignore Whitespace Patch

Changes to src/build.c.

21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
...
419
420
421
422
423
424
425

426
427
428
429
430
431
432
433
...
790
791
792
793
794
795
796

797
798
799
800
801
802
803
804
....
1019
1020
1021
1022
1023
1024
1025

1026
1027
1028
1029
1030
1031
1032
1033
....
1333
1334
1335
1336
1337
1338
1339

1340
1341
1342
1343
1344
1345
1346
1347
....
1383
1384
1385
1386
1387
1388
1389

1390
1391
1392
1393
1394
1395
1396
1397
....
1728
1729
1730
1731
1732
1733
1734













1735
1736






































1737
1738
1739
1740
1741
1742
1743
**     COPY
**     VACUUM
**     BEGIN TRANSACTION
**     COMMIT
**     ROLLBACK
**     PRAGMA
**
** $Id: build.c,v 1.84 2002/03/04 02:26:16 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 
................................................................................
  ** indices to be created and the table record must come before the 
  ** indices.  Hence, the record number for the table must be allocated
  ** now.
  */
  if( !pParse->initFlag && (v = sqliteGetVdbe(pParse))!=0 ){
    sqliteBeginWriteOperation(pParse);
    if( !isTemp ){

      sqliteVdbeAddOp(v, OP_SetCookie, db->file_format, 1);
      sqliteVdbeAddOp(v, OP_OpenWrite, 0, 2);
      sqliteVdbeChangeP3(v, -1, MASTER_NAME, P3_STATIC);
      sqliteVdbeAddOp(v, OP_NewRecno, 0, 0);
      sqliteVdbeAddOp(v, OP_Dup, 0, 0);
      sqliteVdbeAddOp(v, OP_String, 0, 0);
      sqliteVdbeAddOp(v, OP_PutIntKey, 0, 0);
    }
................................................................................
        assert( pEnd!=0 );
        n = Addr(pEnd->z) - Addr(pParse->sFirstToken.z) + 1;
        sqliteVdbeChangeP3(v, -1, pParse->sFirstToken.z, n);
      }
      sqliteVdbeAddOp(v, OP_MakeRecord, 5, 0);
      sqliteVdbeAddOp(v, OP_PutIntKey, 0, 0);
      changeCookie(db);

      sqliteVdbeAddOp(v, OP_SetCookie, db->next_cookie, 0);
      sqliteVdbeAddOp(v, OP_Close, 0, 0);
    }
    if( pSelect ){
      int op = p->isTemp ? OP_OpenWrAux : OP_OpenWrite;
      sqliteVdbeAddOp(v, op, 1, 0);
      pParse->nTab = 2;
      sqliteSelect(pParse, pSelect, SRT_Table, 1, 0, 0, 0);
................................................................................
      { OP_String,     0, 0,        0}, /* 2 */
      { OP_MemStore,   1, 1,        0},
      { OP_MemLoad,    1, 0,        0}, /* 4 */
      { OP_Column,     0, 2,        0},
      { OP_Ne,         0, ADDR(8),  0},
      { OP_Delete,     0, 0,        0},
      { OP_Next,       0, ADDR(4),  0}, /* 8 */

      { OP_SetCookie,  0, 0,        0}, /* 9 */
      { OP_Close,      0, 0,        0},
    };
    Index *pIdx;
    sqliteBeginWriteOperation(pParse);
    if( !pTable->isTemp ){
      base = sqliteVdbeAddOpList(v, ArraySize(dropTable), dropTable);
      sqliteVdbeChangeP3(v, base+2, pTable->zName, 0);
................................................................................
      sqliteVdbeResolveLabel(v, lbl2);
      sqliteVdbeAddOp(v, OP_Close, 2, 0);
      sqliteVdbeAddOp(v, OP_Close, 1, 0);
    }
    if( pTable!=0 ){
      if( !isTemp ){
        changeCookie(db);

        sqliteVdbeAddOp(v, OP_SetCookie, db->next_cookie, 0);
        sqliteVdbeAddOp(v, OP_Close, 0, 0);
      }
      sqliteEndWriteOperation(pParse);
    }
  }

  /* Clean up before exiting */
................................................................................
      { OP_MemStore,   1, 1,       0},
      { OP_MemLoad,    1, 0,       0}, /* 4 */
      { OP_Column,     0, 1,       0},
      { OP_Eq,         0, ADDR(9), 0},
      { OP_Next,       0, ADDR(4), 0},
      { OP_Goto,       0, ADDR(10),0},
      { OP_Delete,     0, 0,       0}, /* 9 */

      { OP_SetCookie,  0, 0,       0}, /* 10 */
      { OP_Close,      0, 0,       0},
    };
    int base;
    Table *pTab = pIndex->pTable;

    sqliteBeginWriteOperation(pParse);
    if( !pTab->isTemp ){
................................................................................
    sqliteSetNString(&zRight, "-", 1, pRight->z, pRight->n, 0);
  }else{
    zRight = sqliteStrNDup(pRight->z, pRight->n);
    sqliteDequote(zRight);
  }
 
  if( sqliteStrICmp(zLeft,"cache_size")==0 ){













    int size = atoi(zRight);
    sqliteBtreeSetCacheSize(db->pBe, size);






































  }else

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







|







 







>
|







 







>
|







 







>
|







 







>
|







 







>
|







 







>
>
>
>
>
>
>
>
>
>
>
>
>
|
<
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>







21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
...
419
420
421
422
423
424
425
426
427
428
429
430
431
432
433
434
...
791
792
793
794
795
796
797
798
799
800
801
802
803
804
805
806
....
1021
1022
1023
1024
1025
1026
1027
1028
1029
1030
1031
1032
1033
1034
1035
1036
....
1336
1337
1338
1339
1340
1341
1342
1343
1344
1345
1346
1347
1348
1349
1350
1351
....
1387
1388
1389
1390
1391
1392
1393
1394
1395
1396
1397
1398
1399
1400
1401
1402
....
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
**     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 
................................................................................
  ** indices to be created and the table record must come before the 
  ** indices.  Hence, the record number for the table must be allocated
  ** now.
  */
  if( !pParse->initFlag && (v = sqliteGetVdbe(pParse))!=0 ){
    sqliteBeginWriteOperation(pParse);
    if( !isTemp ){
      sqliteVdbeAddOp(v, OP_Integer, db->file_format, 0);
      sqliteVdbeAddOp(v, OP_SetCookie, 0, 1);
      sqliteVdbeAddOp(v, OP_OpenWrite, 0, 2);
      sqliteVdbeChangeP3(v, -1, MASTER_NAME, P3_STATIC);
      sqliteVdbeAddOp(v, OP_NewRecno, 0, 0);
      sqliteVdbeAddOp(v, OP_Dup, 0, 0);
      sqliteVdbeAddOp(v, OP_String, 0, 0);
      sqliteVdbeAddOp(v, OP_PutIntKey, 0, 0);
    }
................................................................................
        assert( pEnd!=0 );
        n = Addr(pEnd->z) - Addr(pParse->sFirstToken.z) + 1;
        sqliteVdbeChangeP3(v, -1, pParse->sFirstToken.z, n);
      }
      sqliteVdbeAddOp(v, OP_MakeRecord, 5, 0);
      sqliteVdbeAddOp(v, OP_PutIntKey, 0, 0);
      changeCookie(db);
      sqliteVdbeAddOp(v, OP_Integer, db->next_cookie, 0);
      sqliteVdbeAddOp(v, OP_SetCookie, 0, 0);
      sqliteVdbeAddOp(v, OP_Close, 0, 0);
    }
    if( pSelect ){
      int op = p->isTemp ? OP_OpenWrAux : OP_OpenWrite;
      sqliteVdbeAddOp(v, op, 1, 0);
      pParse->nTab = 2;
      sqliteSelect(pParse, pSelect, SRT_Table, 1, 0, 0, 0);
................................................................................
      { OP_String,     0, 0,        0}, /* 2 */
      { OP_MemStore,   1, 1,        0},
      { OP_MemLoad,    1, 0,        0}, /* 4 */
      { OP_Column,     0, 2,        0},
      { OP_Ne,         0, ADDR(8),  0},
      { OP_Delete,     0, 0,        0},
      { OP_Next,       0, ADDR(4),  0}, /* 8 */
      { OP_Integer,    0, 0,        0}, /* 9 */
      { OP_SetCookie,  0, 0,        0},
      { OP_Close,      0, 0,        0},
    };
    Index *pIdx;
    sqliteBeginWriteOperation(pParse);
    if( !pTable->isTemp ){
      base = sqliteVdbeAddOpList(v, ArraySize(dropTable), dropTable);
      sqliteVdbeChangeP3(v, base+2, pTable->zName, 0);
................................................................................
      sqliteVdbeResolveLabel(v, lbl2);
      sqliteVdbeAddOp(v, OP_Close, 2, 0);
      sqliteVdbeAddOp(v, OP_Close, 1, 0);
    }
    if( pTable!=0 ){
      if( !isTemp ){
        changeCookie(db);
        sqliteVdbeAddOp(v, OP_Integer, db->next_cookie, 0);
        sqliteVdbeAddOp(v, OP_SetCookie, 0, 0);
        sqliteVdbeAddOp(v, OP_Close, 0, 0);
      }
      sqliteEndWriteOperation(pParse);
    }
  }

  /* Clean up before exiting */
................................................................................
      { OP_MemStore,   1, 1,       0},
      { OP_MemLoad,    1, 0,       0}, /* 4 */
      { OP_Column,     0, 1,       0},
      { OP_Eq,         0, ADDR(9), 0},
      { OP_Next,       0, ADDR(4), 0},
      { OP_Goto,       0, ADDR(10),0},
      { OP_Delete,     0, 0,       0}, /* 9 */
      { OP_Integer,    0, 0,       0}, /* 10 */
      { OP_SetCookie,  0, 0,       0},
      { OP_Close,      0, 0,       0},
    };
    int base;
    Table *pTab = pIndex->pTable;

    sqliteBeginWriteOperation(pParse);
    if( !pTab->isTemp ){
................................................................................
    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{
      db->flags &= ~SQLITE_VdbeTrace;

Changes to src/main.c.

10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
..
38
39
40
41
42
43
44







45
46
47
48
49
50
51
...
171
172
173
174
175
176
177








178
179
180
181
182
183
184
...
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
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
**
*************************************************************************
** 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.66 2002/02/28 00:41:11 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.
................................................................................

  /* TODO: Do some validity checks on all fields.  In particular,
  ** 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 'f': {  /* File format */
      db->file_format = atoi(argv[3]);
      break;
    }
    case 's': { /* Schema cookie */
      db->schema_cookie = atoi(argv[3]);
      db->next_cookie = db->schema_cookie;
................................................................................
    */
    { OP_Open,       0, 2,  0},
    { OP_String,     0, 0,  "file-format"},
    { OP_String,     0, 0,  0},
    { OP_String,     0, 0,  0},
    { OP_ReadCookie, 0, 1,  0},
    { OP_Callback,   4, 0,  0},









    /* Send the initial schema cookie to the callback
    */
    { OP_String,     0, 0,  "schema_cookie"},
    { OP_String,     0, 0,  0},
    { OP_String,     0, 0,  0},
    { OP_ReadCookie, 0, 0,  0},
................................................................................
    ** then do a single pass through the SQLITE_MASTER table.  For
    ** a format number of less than 2, jump forward to a different
    ** algorithm that makes two passes through the SQLITE_MASTER table,
    ** once for tables and a second time for indices.
    */
    { OP_ReadCookie, 0, 1,  0},
    { OP_Integer,    2, 0,  0},
    { OP_Lt,         0, 23, 0},

    /* This is the code for doing a single scan through the SQLITE_MASTER
    ** table.  This code runs for format 2 and greater.
    */
    { OP_Rewind,     0, 21, 0},
    { OP_Column,     0, 0,  0},           /* 15 */
    { OP_Column,     0, 1,  0},
    { OP_Column,     0, 3,  0},
    { OP_Column,     0, 4,  0},
    { OP_Callback,   4, 0,  0},
    { OP_Next,       0, 15, 0},
    { OP_Close,      0, 0,  0},           /* 21 */
    { OP_Halt,       0, 0,  0},

    /* This is the code for doing two passes through SQLITE_MASTER.  This
    ** code runs for file format 1.
    */
    { OP_Rewind,     0, 43, 0},           /* 23 */
    { OP_Column,     0, 0,  0},           /* 24 */
    { OP_String,     0, 0,  "table"},
    { OP_Ne,         0, 32, 0},
    { OP_Column,     0, 0,  0},
    { OP_Column,     0, 1,  0},
    { OP_Column,     0, 3,  0},
    { OP_Column,     0, 4,  0},
    { OP_Callback,   4, 0,  0},
    { OP_Next,       0, 24, 0},           /* 32 */
    { OP_Rewind,     0, 43, 0},           /* 33 */
    { OP_Column,     0, 0,  0},           /* 34 */
    { OP_String,     0, 0,  "index"},
    { OP_Ne,         0, 42, 0},
    { OP_Column,     0, 0,  0},
    { OP_Column,     0, 1,  0},
    { OP_Column,     0, 3,  0},
    { OP_Column,     0, 4,  0},
    { OP_Callback,   4, 0,  0},
    { OP_Next,       0, 34, 0},           /* 42 */
    { OP_Close,      0, 0,  0},           /* 43 */
    { OP_Halt,       0, 0,  0},
  };

  /* Create a virtual machine to run the initialization program.  Run
  ** the program.  Then delete the virtual machine.
  */
  vdbe = sqliteVdbeCreate(db);







|







 







>
>
>
>
>
>
>







 







>
>
>
>
>
>
>
>







 







|




|
|




|
|





|
|

|





|
|
|

|





|
|







10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
..
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
...
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
...
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
**
*************************************************************************
** 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.
................................................................................

  /* TODO: Do some validity checks on all fields.  In particular,
  ** 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 */
      db->schema_cookie = atoi(argv[3]);
      db->next_cookie = db->schema_cookie;
................................................................................
    */
    { OP_Open,       0, 2,  0},
    { OP_String,     0, 0,  "file-format"},
    { OP_String,     0, 0,  0},
    { OP_String,     0, 0,  0},
    { OP_ReadCookie, 0, 1,  0},
    { OP_Callback,   4, 0,  0},

    /* Send the recommended pager cache size to the callback routine
    */
    { OP_String,     0, 0,  "cache-size"},
    { OP_String,     0, 0,  0},
    { OP_String,     0, 0,  0},
    { OP_ReadCookie, 0, 2,  0},
    { OP_Callback,   4, 0,  0},

    /* Send the initial schema cookie to the callback
    */
    { OP_String,     0, 0,  "schema_cookie"},
    { OP_String,     0, 0,  0},
    { OP_String,     0, 0,  0},
    { OP_ReadCookie, 0, 0,  0},
................................................................................
    ** then do a single pass through the SQLITE_MASTER table.  For
    ** a format number of less than 2, jump forward to a different
    ** algorithm that makes two passes through the SQLITE_MASTER table,
    ** once for tables and a second time for indices.
    */
    { OP_ReadCookie, 0, 1,  0},
    { OP_Integer,    2, 0,  0},
    { OP_Lt,         0, 28, 0},

    /* This is the code for doing a single scan through the SQLITE_MASTER
    ** table.  This code runs for format 2 and greater.
    */
    { OP_Rewind,     0, 26, 0},
    { OP_Column,     0, 0,  0},           /* 20 */
    { OP_Column,     0, 1,  0},
    { OP_Column,     0, 3,  0},
    { OP_Column,     0, 4,  0},
    { OP_Callback,   4, 0,  0},
    { OP_Next,       0, 20, 0},
    { OP_Close,      0, 0,  0},           /* 26 */
    { OP_Halt,       0, 0,  0},

    /* This is the code for doing two passes through SQLITE_MASTER.  This
    ** code runs for file format 1.
    */
    { OP_Rewind,     0, 48, 0},           /* 28 */
    { OP_Column,     0, 0,  0},           /* 29 */
    { OP_String,     0, 0,  "table"},
    { OP_Ne,         0, 37, 0},
    { OP_Column,     0, 0,  0},
    { OP_Column,     0, 1,  0},
    { OP_Column,     0, 3,  0},
    { OP_Column,     0, 4,  0},
    { OP_Callback,   4, 0,  0},
    { OP_Next,       0, 29, 0},           /* 37 */
    { OP_Rewind,     0, 48, 0},           /* 38 */
    { OP_Column,     0, 0,  0},           /* 39 */
    { OP_String,     0, 0,  "index"},
    { OP_Ne,         0, 47, 0},
    { OP_Column,     0, 0,  0},
    { OP_Column,     0, 1,  0},
    { OP_Column,     0, 3,  0},
    { OP_Column,     0, 4,  0},
    { OP_Callback,   4, 0,  0},
    { OP_Next,       0, 39, 0},           /* 47 */
    { OP_Close,      0, 0,  0},           /* 48 */
    { OP_Halt,       0, 0,  0},
  };

  /* Create a virtual machine to run the initialization program.  Run
  ** the program.  Then delete the virtual machine.
  */
  vdbe = sqliteVdbeCreate(db);

Changes to src/os.c.

39
40
41
42
43
44
45














46
47
48
49
50
51
52
...
473
474
475
476
477
478
479

480
481
482
483
484
485
486
...
495
496
497
498
499
500
501

502
503
504
505
506
507
508
...
513
514
515
516
517
518
519

520
521
522
523
524
525
526
...
528
529
530
531
532
533
534

535
536
537
538
539
540
541
# include <sys/stat.h>
# include <time.h>
#endif
#if OS_WIN
# include <winbase.h>
#endif
















#if OS_UNIX
/*
** Here is the dirt on POSIX advisory locks:  ANSI STD 1003.1 (1996)
** section 6.5.2.2 lines 483 through 490 specify that when a process
** sets or clears a lock, that operation overrides any prior locks set
** by the same process.  It does not explicitly say so, but this implies
................................................................................
** bytes were read successfully and SQLITE_IOERR if anything goes
** wrong.
*/
int sqliteOsRead(OsFile *id, void *pBuf, int amt){
#if OS_UNIX
  int got;
  SimulateIOError(SQLITE_IOERR);

  got = read(id->fd, pBuf, amt);
  if( got<0 ) got = 0;
  return got==amt ? SQLITE_OK : SQLITE_IOERR;
#endif
#if OS_WIN
  DWORD got;
  SimulateIOError(SQLITE_IOERR);
................................................................................
** Write data from a buffer into a file.  Return SQLITE_OK on success
** or some other error code on failure.
*/
int sqliteOsWrite(OsFile *id, const void *pBuf, int amt){
#if OS_UNIX
  int wrote;
  SimulateIOError(SQLITE_IOERR);

  wrote = write(id->fd, pBuf, amt);
  if( wrote<amt ) return SQLITE_FULL;
  return SQLITE_OK;
#endif
#if OS_WIN
  DWORD wrote;
  SimulateIOError(SQLITE_IOERR);
................................................................................
#endif
}

/*
** Move the read/write pointer in a file.
*/
int sqliteOsSeek(OsFile *id, int offset){

#if OS_UNIX
  lseek(id->fd, offset, SEEK_SET);
  return SQLITE_OK;
#endif
#if OS_WIN
  SetFilePointer(id->h, offset, 0, FILE_BEGIN);
  return SQLITE_OK;
................................................................................
}

/*
** Make sure all writes to a particular file are committed to disk.
*/
int sqliteOsSync(OsFile *id){
  SimulateIOError(SQLITE_IOERR);

#if OS_UNIX
  return fsync(id->fd)==0 ? SQLITE_OK : SQLITE_IOERR;
#endif
#if OS_WIN
  return FlushFileBuffers(id->h) ? SQLITE_OK : SQLITE_IOERR;
#endif
}







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







 







>







 







>







 







>







 







>







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
...
487
488
489
490
491
492
493
494
495
496
497
498
499
500
501
...
510
511
512
513
514
515
516
517
518
519
520
521
522
523
524
...
529
530
531
532
533
534
535
536
537
538
539
540
541
542
543
...
545
546
547
548
549
550
551
552
553
554
555
556
557
558
559
# include <sys/stat.h>
# include <time.h>
#endif
#if OS_WIN
# include <winbase.h>
#endif

/*
** Macros for performance tracing.  Normally turned off
*/
#if 0
static int last_page = 0;
#define SEEK(X)     last_page=(X)
#define TRACE1(X)   fprintf(stderr,X)
#define TRACE2(X,Y) fprintf(stderr,X,Y)
#else
#define SEEK(X)
#define TRACE1(X)
#define TRACE2(X,Y)
#endif


#if OS_UNIX
/*
** Here is the dirt on POSIX advisory locks:  ANSI STD 1003.1 (1996)
** section 6.5.2.2 lines 483 through 490 specify that when a process
** sets or clears a lock, that operation overrides any prior locks set
** by the same process.  It does not explicitly say so, but this implies
................................................................................
** bytes were read successfully and SQLITE_IOERR if anything goes
** wrong.
*/
int sqliteOsRead(OsFile *id, void *pBuf, int amt){
#if OS_UNIX
  int got;
  SimulateIOError(SQLITE_IOERR);
  TRACE2("READ %d\n", last_page);
  got = read(id->fd, pBuf, amt);
  if( got<0 ) got = 0;
  return got==amt ? SQLITE_OK : SQLITE_IOERR;
#endif
#if OS_WIN
  DWORD got;
  SimulateIOError(SQLITE_IOERR);
................................................................................
** Write data from a buffer into a file.  Return SQLITE_OK on success
** or some other error code on failure.
*/
int sqliteOsWrite(OsFile *id, const void *pBuf, int amt){
#if OS_UNIX
  int wrote;
  SimulateIOError(SQLITE_IOERR);
  TRACE2("WRITE %d\n", last_page);
  wrote = write(id->fd, pBuf, amt);
  if( wrote<amt ) return SQLITE_FULL;
  return SQLITE_OK;
#endif
#if OS_WIN
  DWORD wrote;
  SimulateIOError(SQLITE_IOERR);
................................................................................
#endif
}

/*
** Move the read/write pointer in a file.
*/
int sqliteOsSeek(OsFile *id, int offset){
  SEEK(offset/1024 + 1);
#if OS_UNIX
  lseek(id->fd, offset, SEEK_SET);
  return SQLITE_OK;
#endif
#if OS_WIN
  SetFilePointer(id->h, offset, 0, FILE_BEGIN);
  return SQLITE_OK;
................................................................................
}

/*
** Make sure all writes to a particular file are committed to disk.
*/
int sqliteOsSync(OsFile *id){
  SimulateIOError(SQLITE_IOERR);
  TRACE1("SYNC\n");
#if OS_UNIX
  return fsync(id->fd)==0 ? SQLITE_OK : SQLITE_IOERR;
#endif
#if OS_WIN
  return FlushFileBuffers(id->h) ? SQLITE_OK : SQLITE_IOERR;
#endif
}

Changes to src/pager.c.

14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
..
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
...
431
432
433
434
435
436
437






438
439
440
441
442
443
444
...
796
797
798
799
800
801
802
803
804
805

806
807
808
809
810
811
812
...
814
815
816
817
818
819
820
821
822
823
824
825
826
827
828
....
1077
1078
1079
1080
1081
1082
1083
1084
1085
1086
1087
1088
1089
1090
1091
....
1205
1206
1207
1208
1209
1210
1211
1212


1213
1214
1215
1216
1217
1218
1219
** 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.41 2002/03/02 20:41:59 drh Exp $
*/
#include "sqliteInt.h"
#include "pager.h"
#include "os.h"
#include <assert.h>
#include <string.h>

................................................................................
#define DATA_TO_PGHDR(D)  (&((PgHdr*)(D))[-1])
#define PGHDR_TO_EXTRA(P) ((void*)&((char*)(&(P)[1]))[SQLITE_PAGE_SIZE])

/*
** How big to make the hash table used for locating in-memory pages
** by page number.  Knuth says this should be a prime number.
*/
#define N_PG_HASH 373

/*
** A open page cache is an instance of the following structure.
*/
struct Pager {
  char *zFilename;            /* Name of the database file */
  char *zJournal;             /* Name of the journal file */
  OsFile fd, jfd;             /* File descriptors for database and journal */
  OsFile cpfd;                /* File descriptor for the checkpoint journal */
  int journalOpen;            /* True if journal file descriptors is valid */
  int ckptOpen;               /* True if the checkpoint journal is open */
  int dbSize;                 /* Number of pages in the file */
  int origDbSize;             /* dbSize before the current change */
  int ckptSize, ckptJSize;    /* Size of database and journal at ckpt_begin() */
  int nExtra;                 /* Add this many bytes to each in-memory page */
  void (*xDestructor)(void*); /* Call this routine when freeing pages */
  int nPage;                  /* Total number of in-memory pages */
  int nRef;                   /* Number of in-memory pages with PgHdr.nRef>0 */
  int mxPage;                 /* Maximum number of pages to hold in cache */
  int nHit, nMiss, nOvfl;     /* Cache hits, missing, and LRU overflows */



  unsigned char state;        /* SQLITE_UNLOCK, _READLOCK or _WRITELOCK */
  unsigned char errMask;      /* One of several kinds of errors */
  unsigned char tempFile;     /* zFilename is a temporary file */
  unsigned char readOnly;     /* True for a read-only database */
  unsigned char needSync;     /* True if an fsync() is needed on the journal */
  unsigned char *aInJournal;  /* One bit for each page in the database file */
  unsigned char *aInCkpt;     /* One bit for each page in the database */
  PgHdr *pFirst, *pLast;      /* List of free pages */
  PgHdr *pAll;                /* List of all pages */
  PgHdr *aHash[N_PG_HASH];    /* Hash table to map page number of PgHdr */
};

/*
** These are bits that can be set in Pager.errMask.
................................................................................
  return rc;
}

/*
** Change the maximum number of in-memory pages that are allowed.
*/
void sqlitepager_set_cachesize(Pager *pPager, int mxPage){






  if( mxPage>10 ){
    pPager->mxPage = mxPage;
  }
}

/*
** Open a temporary file.  Write the name of the file into zName
................................................................................
      pPg->pPrevAll = 0;
      pPager->pAll = pPg;
      pPager->nPage++;
    }else{
      /* Recycle an older page.  First locate the page to be recycled.
      ** Try to find one that is not dirty and is near the head of
      ** of the free list */
      int cnt = pPager->mxPage/2;
      pPg = pPager->pFirst;
      while( pPg->dirty && 0<cnt-- && pPg->pNextFree ){

        pPg = pPg->pNextFree;
      }

      /* If we could not find a page that has not been used recently
      ** and which is not dirty, then sync the journal and write all
      ** dirty free pages into the database file, thus making them
      ** clean pages and available for recycling.
................................................................................
      ** We have to sync the journal before writing a page to the main
      ** database.  But syncing is a very slow operation.  So after a
      ** sync, it is best to write everything we can back to the main
      ** database to minimize the risk of having to sync again in the
      ** near future.  That is way we write all dirty pages after a
      ** sync.
      */
      if( pPg==0 || pPg->dirty ){
        int rc = syncAllPages(pPager);
        if( rc!=0 ){
          sqlitepager_rollback(pPager);
          *ppPage = 0;
          return SQLITE_IOERR;
        }
        pPg = pPager->pFirst;
................................................................................
    if( rc!=SQLITE_OK ){
      sqlitepager_rollback(pPager);
      pPager->errMask |= PAGER_ERR_FULL;
      return rc;
    }
    assert( pPager->aInJournal!=0 );
    pPager->aInJournal[pPg->pgno/8] |= 1<<(pPg->pgno&7);
    pPager->needSync = 1;
    pPg->inJournal = 1;
    if( pPager->ckptOpen ){
      pPager->aInCkpt[pPg->pgno/8] |= 1<<(pPg->pgno&7);
      pPg->inCkpt = 1;
    }
  }

................................................................................
  for(pPg=pPager->pAll; pPg; pPg=pPg->pNextAll){
    if( pPg->dirty==0 ) continue;
    rc = sqliteOsSeek(&pPager->fd, (pPg->pgno-1)*SQLITE_PAGE_SIZE);
    if( rc!=SQLITE_OK ) goto commit_abort;
    rc = sqliteOsWrite(&pPager->fd, PGHDR_TO_DATA(pPg), SQLITE_PAGE_SIZE);
    if( rc!=SQLITE_OK ) goto commit_abort;
  }
  if( sqliteOsSync(&pPager->fd)!=SQLITE_OK ) goto commit_abort;


  rc = pager_unwritelock(pPager);
  pPager->dbSize = -1;
  return rc;

  /* Jump here if anything goes wrong during the commit process.
  */
commit_abort:







|







 







|









<
<









>
>
>
|
|
|
|
|
|
|







 







>
>
>
>
>
>







 







<

<
>







 







|







 







|







 







|
>
>







14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
..
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
...
432
433
434
435
436
437
438
439
440
441
442
443
444
445
446
447
448
449
450
451
...
803
804
805
806
807
808
809

810

811
812
813
814
815
816
817
818
...
820
821
822
823
824
825
826
827
828
829
830
831
832
833
834
....
1083
1084
1085
1086
1087
1088
1089
1090
1091
1092
1093
1094
1095
1096
1097
....
1211
1212
1213
1214
1215
1216
1217
1218
1219
1220
1221
1222
1223
1224
1225
1226
1227
** 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.42 2002/03/05 01:11:14 drh Exp $
*/
#include "sqliteInt.h"
#include "pager.h"
#include "os.h"
#include <assert.h>
#include <string.h>

................................................................................
#define DATA_TO_PGHDR(D)  (&((PgHdr*)(D))[-1])
#define PGHDR_TO_EXTRA(P) ((void*)&((char*)(&(P)[1]))[SQLITE_PAGE_SIZE])

/*
** How big to make the hash table used for locating in-memory pages
** by page number.  Knuth says this should be a prime number.
*/
#define N_PG_HASH 2003

/*
** A open page cache is an instance of the following structure.
*/
struct Pager {
  char *zFilename;            /* Name of the database file */
  char *zJournal;             /* Name of the journal file */
  OsFile fd, jfd;             /* File descriptors for database and journal */
  OsFile cpfd;                /* File descriptor for the checkpoint journal */


  int dbSize;                 /* Number of pages in the file */
  int origDbSize;             /* dbSize before the current change */
  int ckptSize, ckptJSize;    /* Size of database and journal at ckpt_begin() */
  int nExtra;                 /* Add this many bytes to each in-memory page */
  void (*xDestructor)(void*); /* Call this routine when freeing pages */
  int nPage;                  /* Total number of in-memory pages */
  int nRef;                   /* Number of in-memory pages with PgHdr.nRef>0 */
  int mxPage;                 /* Maximum number of pages to hold in cache */
  int nHit, nMiss, nOvfl;     /* Cache hits, missing, and LRU overflows */
  u8 journalOpen;             /* True if journal file descriptors is valid */
  u8 ckptOpen;                /* True if the checkpoint journal is open */
  u8 noSync;                  /* Do not sync the journal if true */
  u8 state;                   /* SQLITE_UNLOCK, _READLOCK or _WRITELOCK */
  u8 errMask;                 /* One of several kinds of errors */
  u8 tempFile;                /* zFilename is a temporary file */
  u8 readOnly;                /* True for a read-only database */
  u8 needSync;                /* True if an fsync() is needed on the journal */
  u8 *aInJournal;             /* One bit for each page in the database file */
  u8 *aInCkpt;                /* One bit for each page in the database */
  PgHdr *pFirst, *pLast;      /* List of free pages */
  PgHdr *pAll;                /* List of all pages */
  PgHdr *aHash[N_PG_HASH];    /* Hash table to map page number of PgHdr */
};

/*
** These are bits that can be set in Pager.errMask.
................................................................................
  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;
  }
  if( mxPage>10 ){
    pPager->mxPage = mxPage;
  }
}

/*
** Open a temporary file.  Write the name of the file into zName
................................................................................
      pPg->pPrevAll = 0;
      pPager->pAll = pPg;
      pPager->nPage++;
    }else{
      /* Recycle an older page.  First locate the page to be recycled.
      ** Try to find one that is not dirty and is near the head of
      ** of the free list */

      pPg = pPager->pFirst;

      while( pPg && pPg->dirty ){
        pPg = pPg->pNextFree;
      }

      /* If we could not find a page that has not been used recently
      ** and which is not dirty, then sync the journal and write all
      ** dirty free pages into the database file, thus making them
      ** clean pages and available for recycling.
................................................................................
      ** We have to sync the journal before writing a page to the main
      ** database.  But syncing is a very slow operation.  So after a
      ** sync, it is best to write everything we can back to the main
      ** database to minimize the risk of having to sync again in the
      ** near future.  That is way we write all dirty pages after a
      ** sync.
      */
      if( pPg==0 ){
        int rc = syncAllPages(pPager);
        if( rc!=0 ){
          sqlitepager_rollback(pPager);
          *ppPage = 0;
          return SQLITE_IOERR;
        }
        pPg = pPager->pFirst;
................................................................................
    if( rc!=SQLITE_OK ){
      sqlitepager_rollback(pPager);
      pPager->errMask |= PAGER_ERR_FULL;
      return rc;
    }
    assert( pPager->aInJournal!=0 );
    pPager->aInJournal[pPg->pgno/8] |= 1<<(pPg->pgno&7);
    pPager->needSync = !pPager->noSync;
    pPg->inJournal = 1;
    if( pPager->ckptOpen ){
      pPager->aInCkpt[pPg->pgno/8] |= 1<<(pPg->pgno&7);
      pPg->inCkpt = 1;
    }
  }

................................................................................
  for(pPg=pPager->pAll; pPg; pPg=pPg->pNextAll){
    if( pPg->dirty==0 ) continue;
    rc = sqliteOsSeek(&pPager->fd, (pPg->pgno-1)*SQLITE_PAGE_SIZE);
    if( rc!=SQLITE_OK ) goto commit_abort;
    rc = sqliteOsWrite(&pPager->fd, PGHDR_TO_DATA(pPg), SQLITE_PAGE_SIZE);
    if( rc!=SQLITE_OK ) goto commit_abort;
  }
  if( !pPager->noSync && sqliteOsSync(&pPager->fd)!=SQLITE_OK ){
    goto commit_abort;
  }
  rc = pager_unwritelock(pPager);
  pPager->dbSize = -1;
  return rc;

  /* Jump here if anything goes wrong during the commit process.
  */
commit_abort:

Changes to src/parse.y.

10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
...
577
578
579
580
581
582
583
584
585
586
587
588
589
590
**
*************************************************************************
** This file contains SQLite's grammar for SQL.  Process this file
** using the lemon parser generator to generate C code that runs
** the parser.  Lemon will also generate a header file containing
** numeric codes for all of the tokens.
**
** @(#) $Id: parse.y,v 1.55 2002/03/03 23:06:01 drh Exp $
*/
%token_prefix TK_
%token_type {Token}
%default_type {Token}
%extra_argument {Parse *pParse}
%syntax_error {
  sqliteSetString(&pParse->zErrMsg,"syntax error",0);
................................................................................
///////////////////////////// The PRAGMA command /////////////////////////////
//
cmd ::= PRAGMA ids(X) EQ ids(Y).         {sqlitePragma(pParse,&X,&Y,0);}
cmd ::= PRAGMA ids(X) EQ ON(Y).          {sqlitePragma(pParse,&X,&Y,0);}
cmd ::= PRAGMA ids(X) EQ plus_num(Y).    {sqlitePragma(pParse,&X,&Y,0);}
cmd ::= PRAGMA ids(X) EQ minus_num(Y).   {sqlitePragma(pParse,&X,&Y,1);}
cmd ::= PRAGMA ids(X) LP ids(Y) RP.      {sqlitePragma(pParse,&X,&Y,0);}
cmd ::= PRAGMA(Y) ids(X).                {sqlitePragma(pParse,&X,&Y,0);}
plus_num(A) ::= plus_opt number(X).   {A = X;}
minus_num(A) ::= MINUS number(X).     {A = X;}
number(A) ::= INTEGER(X).  {A = X;}
number(A) ::= FLOAT(X).    {A = X;}
plus_opt ::= PLUS.
plus_opt ::= .







|







 







|






10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
...
577
578
579
580
581
582
583
584
585
586
587
588
589
590
**
*************************************************************************
** This file contains SQLite's grammar for SQL.  Process this file
** using the lemon parser generator to generate C code that runs
** the parser.  Lemon will also generate a header file containing
** numeric codes for all of the tokens.
**
** @(#) $Id: parse.y,v 1.56 2002/03/05 01:11:14 drh Exp $
*/
%token_prefix TK_
%token_type {Token}
%default_type {Token}
%extra_argument {Parse *pParse}
%syntax_error {
  sqliteSetString(&pParse->zErrMsg,"syntax error",0);
................................................................................
///////////////////////////// The PRAGMA command /////////////////////////////
//
cmd ::= PRAGMA ids(X) EQ ids(Y).         {sqlitePragma(pParse,&X,&Y,0);}
cmd ::= PRAGMA ids(X) EQ ON(Y).          {sqlitePragma(pParse,&X,&Y,0);}
cmd ::= PRAGMA ids(X) EQ plus_num(Y).    {sqlitePragma(pParse,&X,&Y,0);}
cmd ::= PRAGMA ids(X) EQ minus_num(Y).   {sqlitePragma(pParse,&X,&Y,1);}
cmd ::= PRAGMA ids(X) LP ids(Y) RP.      {sqlitePragma(pParse,&X,&Y,0);}
cmd ::= PRAGMA ids(X).                   {sqlitePragma(pParse,&X,&X,0);}
plus_num(A) ::= plus_opt number(X).   {A = X;}
minus_num(A) ::= MINUS number(X).     {A = X;}
number(A) ::= INTEGER(X).  {A = X;}
number(A) ::= FLOAT(X).    {A = X;}
plus_opt ::= PLUS.
plus_opt ::= .

Changes to src/sqliteInt.h.

7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
..
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
**    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.101 2002/03/03 23:06:02 drh Exp $
*/
#include "sqlite.h"
#include "hash.h"
#include "vdbe.h"
#include "parse.h"
#include "btree.h"
#include <stdio.h>
................................................................................
#include <string.h>
#include <assert.h>

/*
** The maximum number of in-memory pages to use for the main database
** table and for temporary tables.
*/
#define MAX_PAGES   100
#define TEMP_PAGES   25

/*
** Integers of known sizes.  These typedefs might change for architectures
** where the sizes very.  Preprocessor macros are available so that the
** types can be conveniently redefined at compile-type.  Like this:
**
**         cc '-DUINTPTR_TYPE=long long int' ...







|







 







|
|







7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
..
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
**    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>
................................................................................
#include <string.h>
#include <assert.h>

/*
** The maximum number of in-memory pages to use for the main database
** table and for temporary tables.
*/
#define MAX_PAGES   2000
#define TEMP_PAGES   500

/*
** Integers of known sizes.  These typedefs might change for architectures
** where the sizes very.  Preprocessor macros are available so that the
** types can be conveniently redefined at compile-type.  Like this:
**
**         cc '-DUINTPTR_TYPE=long long int' ...

Changes to src/vdbe.c.

26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
....
2551
2552
2553
2554
2555
2556
2557

2558
2559
2560
2561
2562
2563
2564
2565
....
2568
2569
2570
2571
2572
2573
2574
2575
2576
2577
2578

2579
2580
2581
2582
2583
2584
2585
2586
2587
2588
2589
2590
2591


2592
2593
2594
2595
2596

2597
2598
2599
2600
2601
2602
2603
** 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.130 2002/03/03 02:49:51 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
................................................................................
** number for the database schema.  Everytime the schema changes, the
** cookie changes to a new random value.  This opcode is used during
** initialization to read the initial cookie value so that subsequent
** database accesses can verify that the cookie has not changed.
**
** If P2>0, then read global database parameter number P2.  There is
** a small fixed number of global database parameters.  P2==1 is the

** database version number.  Other parameters are currently unused.
**
** There must be a read-lock on the database (either a transaction
** must be started or there must be an open cursor) before
** executing this instruction.
*/
case OP_ReadCookie: {
  int i = ++p->tos;
................................................................................
  VERIFY( if( NeedStack(p, p->tos) ) goto no_mem; )
  rc = sqliteBtreeGetMeta(pBt, aMeta);
  aStack[i].i = aMeta[1+pOp->p2];
  aStack[i].flags = STK_Int;
  break;
}

/* Opcode: SetCookie P1 P2 *
**
** When P2==0,
** this operation changes the value of the schema cookie on the database.

** The new value is P1.  When P2>0, the value of global database parameter
** number P2 is changed.  See ReadCookie for more information about
** global database parametes.
**
** The schema cookie changes its value whenever the database schema changes.
** That way, other processes can recognize when the schema has changed
** and reread it.
**
** A transaction must be started before executing this opcode.
*/
case OP_SetCookie: {
  int aMeta[SQLITE_N_BTREE_META];
  assert( pOp->p2<SQLITE_N_BTREE_META );


  rc = sqliteBtreeGetMeta(pBt, aMeta);
  if( rc==SQLITE_OK ){
    aMeta[1+pOp->p2] = pOp->p1;
    rc = sqliteBtreeUpdateMeta(pBt, aMeta);
  }

  break;
}

/* Opcode: VerifyCookie P1 P2 *
**
** Check the value of global database parameter number P2 and make
** sure it is equal to P1.  P2==0 is the schema cookie.  P1==1 is







|







 







>
|







 







|



>
|












>
>


|


>







26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
....
2551
2552
2553
2554
2555
2556
2557
2558
2559
2560
2561
2562
2563
2564
2565
2566
....
2569
2570
2571
2572
2573
2574
2575
2576
2577
2578
2579
2580
2581
2582
2583
2584
2585
2586
2587
2588
2589
2590
2591
2592
2593
2594
2595
2596
2597
2598
2599
2600
2601
2602
2603
2604
2605
2606
2607
2608
** 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.131 2002/03/05 01:11:14 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
................................................................................
** number for the database schema.  Everytime the schema changes, the
** cookie changes to a new random value.  This opcode is used during
** initialization to read the initial cookie value so that subsequent
** database accesses can verify that the cookie has not changed.
**
** If P2>0, then read global database parameter number P2.  There is
** a small fixed number of global database parameters.  P2==1 is the
** database version number.  P2==2 is the recommended pager cache size.
** Other parameters are currently unused.
**
** There must be a read-lock on the database (either a transaction
** must be started or there must be an open cursor) before
** executing this instruction.
*/
case OP_ReadCookie: {
  int i = ++p->tos;
................................................................................
  VERIFY( if( NeedStack(p, p->tos) ) goto no_mem; )
  rc = sqliteBtreeGetMeta(pBt, aMeta);
  aStack[i].i = aMeta[1+pOp->p2];
  aStack[i].flags = STK_Int;
  break;
}

/* Opcode: SetCookie * P2 *
**
** When P2==0,
** this operation changes the value of the schema cookie on the database.
** The new value is top of the stack.
** When P2>0, the value of global database parameter
** number P2 is changed.  See ReadCookie for more information about
** global database parametes.
**
** The schema cookie changes its value whenever the database schema changes.
** That way, other processes can recognize when the schema has changed
** and reread it.
**
** A transaction must be started before executing this opcode.
*/
case OP_SetCookie: {
  int aMeta[SQLITE_N_BTREE_META];
  assert( pOp->p2<SQLITE_N_BTREE_META );
  VERIFY( if( p->tos<0 ) goto not_enough_stack; )
  Integerify(p, p->tos)
  rc = sqliteBtreeGetMeta(pBt, aMeta);
  if( rc==SQLITE_OK ){
    aMeta[1+pOp->p2] = aStack[p->tos].i;
    rc = sqliteBtreeUpdateMeta(pBt, aMeta);
  }
  POPSTACK;
  break;
}

/* Opcode: VerifyCookie P1 P2 *
**
** Check the value of global database parameter number P2 and make
** sure it is equal to P1.  P2==0 is the schema cookie.  P1==1 is

Added tool/speedtest.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
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
#!/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 0
proc runtest {title sqlfile} {
  global cnt
  incr cnt
  puts "<h2>Test $cnt: $title</h2>"
  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=5>"
  set format {<tr><td>%s</td><td align="right">%.3f</td></tr>}
  set t [time "exec psql drh <$sqlfile" 1]
  set t [expr {[lindex $t 0]/1000000.0}]
  puts [format $format PostgreSQL: $t]
  set t [time "exec mysql 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]
  set t [time "exec ./sqlite240 s2k.db <$sqlfile" 1]
  set t [expr {[lindex $t 0]/1000000.0}]
  puts [format $format {SQLite 2.4 (cache=2000):} $t]
  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]
  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 cache_size=2000; PRAGMA synchronous=on;}
close $fd
exec ./sqlite240 s2k.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} {
  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>19} {
    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
}

# TEST 1
#
set fd [open test1.sql w]
puts $fd "CREATE TABLE t1(a INTEGER, b INTEGER, c VARCHAR(100));"
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]');"
}
close $fd
runtest {1000 INSERTs} test1.sql

# TEST 2
#
set fd [open test2.sql w]
puts $fd "BEGIN;"
puts $fd "CREATE TABLE t2(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 t2 VALUES($i,$r,'[number_name $r]');"
}
puts $fd "COMMIT;"
close $fd
runtest {25000 INSERTs in a transaction} test2.sql

# TEST 3
#
set fd [open test3.sql w]
for {set i 0} {$i<100} {incr i} {
  set lwr [expr {$i*100}]
  set upr [expr {($i+10)*100}]
  puts $fd "SELECT count(*), avg(b) FROM t2 WHERE b>=$lwr AND b<$upr;"
}
close $fd
runtest {100 SELECTs without an index} test3.sql

# TEST 4
#
set fd [open test4.sql w]
puts $fd {CREATE INDEX i2a ON t2(a);}
puts $fd {CREATE INDEX i2b ON t2(b);}
close $fd
runtest {Creating an index} test4.sql

# TEST 5
#
set fd [open test5.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} test5.sql

# TEST 6
#
set fd [open test6.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} test6.sql


# TEST 7
set fd [open test7.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} test7.sql

# TEST 8
set fd [open test8.sql w]
puts $fd "BEGIN;"
puts $fd "INSERT INTO t1 SELECT * FROM t2;"
puts $fd "INSERT INTO t2 SELECT * FROM t1;"
puts $fd "COMMIT;"
close $fd
runtest {INSERTs from a SELECT} test8.sql

# TEST 9
#
set fd [open test9.sql w]
puts $fd {DELETE FROM t2 WHERE c LIKE '%fifty%';}
close $fd
runtest {DELETE without an index} test9.sql

# TEST 10
#
set fd [open test10.sql w]
puts $fd {DELETE FROM t2 WHERE a>10 AND a<20000;}
close $fd
runtest {DELETE with an index} test10.sql

# TEST 11
#
set fd [open test11.sql w]
puts $fd {INSERT INTO t2 SELECT * FROM t1;}
close $fd
runtest {A big INSERT after a big DELETE} test11.sql

# TEST 12
#
set fd [open test12.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} test12.sql

# TEST 13
#
set fd [open test13.sql w]
puts $fd {DROP TABLE t1;}
puts $fd {DROP TABLE t2;}
close $fd
runtest {DROP TABLE} test13.sql

Changes to www/changes.tcl.

34
35
36
37
38
39
40


41
42
43
44
45
46
47
<li>Added the subquery flattening optimizer.</li>
<li>Modified the B-Tree and Pager modules so that disk pages that do not
    contain real data (free pages) are not journalled and are not
    written from memory back to the disk when they change.  This does not 
    impact database integrity, since the
    pages contain no real data, but it does make large INSERT operations
    about 2.5 times faster and large DELETEs about 5 times faster.</li>


}

chng {2002 Feb 18 (2.3.3)} {
<li>Allow identifiers to be quoted in square brackets, for compatibility
    with MS-Access.</li>
<li>Added support for sub-queries in the FROM clause of a SELECT.</li>
<li>More efficient implementation of sqliteFileExists() under Windows.







>
>







34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
<li>Added the subquery flattening optimizer.</li>
<li>Modified the B-Tree and Pager modules so that disk pages that do not
    contain real data (free pages) are not journalled and are not
    written from memory back to the disk when they change.  This does not 
    impact database integrity, since the
    pages contain no real data, but it does make large INSERT operations
    about 2.5 times faster and large DELETEs about 5 times faster.</li>
<li>Made the CACHE_SIZE pragma persistent</li>
<li>Added the SYNCHRONOUS pragma</li>
}

chng {2002 Feb 18 (2.3.3)} {
<li>Allow identifiers to be quoted in square brackets, for compatibility
    with MS-Access.</li>
<li>Added support for sub-queries in the FROM clause of a SELECT.</li>
<li>More efficient implementation of sqliteFileExists() under Windows.