/ Check-in [7a24336d]
Login

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

Overview
Comment:Once it is opened, leave the checkpoint journal file open for the duration of a transaction, rather than closing it and reopening it for each statement. (Ticket #53) (CVS 599)
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1: 7a24336d50e72006b2cc0e4feb292b946e79d5f3
User & Date: drh 2002-05-30 12:27:03
Context
2002-05-31
15:51
Refinements to NULL processing: NULLs are indistinct for DISTINCT and UNION. Multiplying a NULL by zero yields zero. In a CASE expression, a NULL comparison is considered false, not NULL. With these changes, NULLs in SQLite now work the same as in PostgreSQL and in Oracle. (CVS 600) check-in: da61aa1d user: drh tags: trunk
2002-05-30
12:27
Once it is opened, leave the checkpoint journal file open for the duration of a transaction, rather than closing it and reopening it for each statement. (Ticket #53) (CVS 599) check-in: 7a24336d user: drh tags: trunk
02:35
Bug fix: bad code was generated for when the first operand of a CASE was NULL. (CVS 598) check-in: 4debc8db user: drh tags: trunk
Changes
Hide Diffs Side-by-Side Diffs Ignore Whitespace Patch

Changes to src/pager.c.

    14     14   ** The pager is used to access a database disk file.  It implements
    15     15   ** atomic commit and rollback through the use of a journal file that
    16     16   ** is separate from the database file.  The pager also implements file
    17     17   ** locking to prevent two processes from writing the same database
    18     18   ** file simultaneously, or one process from reading the database while
    19     19   ** another is writing.
    20     20   **
    21         -** @(#) $Id: pager.c,v 1.45 2002/04/18 01:56:58 drh Exp $
           21  +** @(#) $Id: pager.c,v 1.46 2002/05/30 12:27:03 drh Exp $
    22     22   */
    23     23   #include "sqliteInt.h"
    24     24   #include "pager.h"
    25     25   #include "os.h"
    26     26   #include <assert.h>
    27     27   #include <string.h>
    28     28   
................................................................................
   110    110     void (*xDestructor)(void*); /* Call this routine when freeing pages */
   111    111     int nPage;                  /* Total number of in-memory pages */
   112    112     int nRef;                   /* Number of in-memory pages with PgHdr.nRef>0 */
   113    113     int mxPage;                 /* Maximum number of pages to hold in cache */
   114    114     int nHit, nMiss, nOvfl;     /* Cache hits, missing, and LRU overflows */
   115    115     u8 journalOpen;             /* True if journal file descriptors is valid */
   116    116     u8 ckptOpen;                /* True if the checkpoint journal is open */
          117  +  u8 ckptInUse;               /* True we are in a checkpoint */
   117    118     u8 noSync;                  /* Do not sync the journal if true */
   118    119     u8 state;                   /* SQLITE_UNLOCK, _READLOCK or _WRITELOCK */
   119    120     u8 errMask;                 /* One of several kinds of errors */
   120    121     u8 tempFile;                /* zFilename is a temporary file */
   121    122     u8 readOnly;                /* True for a read-only database */
   122    123     u8 needSync;                /* True if an fsync() is needed on the journal */
   123    124     u8 dirtyFile;               /* True if database file has changed in any way */
................................................................................
   239    240   ** is deleted and closed.
   240    241   */
   241    242   static int pager_unwritelock(Pager *pPager){
   242    243     int rc;
   243    244     PgHdr *pPg;
   244    245     if( pPager->state<SQLITE_WRITELOCK ) return SQLITE_OK;
   245    246     sqlitepager_ckpt_commit(pPager);
          247  +  if( pPager->ckptOpen ){
          248  +    sqliteOsClose(&pPager->cpfd);
          249  +    pPager->ckptOpen = 0;
          250  +  }
   246    251     sqliteOsClose(&pPager->jfd);
   247    252     pPager->journalOpen = 0;
   248    253     sqliteOsDelete(pPager->zJournal);
   249    254     rc = sqliteOsReadLock(&pPager->fd);
   250    255     assert( rc==SQLITE_OK );
   251    256     sqliteFree( pPager->aInJournal );
   252    257     pPager->aInJournal = 0;
................................................................................
   387    392     /* Truncate the database back to its original size.
   388    393     */
   389    394     rc = sqliteOsTruncate(&pPager->fd, pPager->ckptSize*SQLITE_PAGE_SIZE);
   390    395     pPager->dbSize = pPager->ckptSize;
   391    396   
   392    397     /* Figure out how many records are in the checkpoint journal.
   393    398     */
   394         -  assert( pPager->ckptOpen && pPager->journalOpen );
          399  +  assert( pPager->ckptInUse && pPager->journalOpen );
   395    400     sqliteOsSeek(&pPager->cpfd, 0);
   396    401     rc = sqliteOsFileSize(&pPager->cpfd, &nRec);
   397    402     if( rc!=SQLITE_OK ){
   398    403       goto end_ckpt_playback;
   399    404     }
   400    405     nRec /= sizeof(PageRecord);
   401    406     
................................................................................
   524    529     pPager->zJournal = &pPager->zFilename[nameLen+1];
   525    530     strcpy(pPager->zFilename, zFilename);
   526    531     strcpy(pPager->zJournal, zFilename);
   527    532     strcpy(&pPager->zJournal[nameLen], "-journal");
   528    533     pPager->fd = fd;
   529    534     pPager->journalOpen = 0;
   530    535     pPager->ckptOpen = 0;
          536  +  pPager->ckptInUse = 0;
   531    537     pPager->nRef = 0;
   532    538     pPager->dbSize = -1;
   533    539     pPager->ckptSize = 0;
   534    540     pPager->ckptJSize = 0;
   535    541     pPager->nPage = 0;
   536    542     pPager->mxPage = mxPage>5 ? mxPage : 10;
   537    543     pPager->state = SQLITE_UNLOCK;
................................................................................
   610    616     }
   611    617     for(pPg=pPager->pAll; pPg; pPg=pNext){
   612    618       pNext = pPg->pNextAll;
   613    619       sqliteFree(pPg);
   614    620     }
   615    621     sqliteOsClose(&pPager->fd);
   616    622     assert( pPager->journalOpen==0 );
   617         -  if( pPager->tempFile ){
   618         -    /* sqliteOsDelete(pPager->zFilename); */
   619         -  }
          623  +  /* Temp files are automatically deleted by the OS
          624  +  ** if( pPager->tempFile ){
          625  +  **   sqliteOsDelete(pPager->zFilename);
          626  +  ** }
          627  +  */
   620    628     sqliteFree(pPager);
   621    629     return SQLITE_OK;
   622    630   }
   623    631   
   624    632   /*
   625    633   ** Return the page number for the given page data.
   626    634   */
................................................................................
  1086   1094       return SQLITE_PERM;
  1087   1095     }
  1088   1096   
  1089   1097     /* Mark the page as dirty.  If the page has already been written
  1090   1098     ** to the journal then we can return right away.
  1091   1099     */
  1092   1100     pPg->dirty = 1;
  1093         -  if( pPg->inJournal && (pPg->inCkpt || pPager->ckptOpen==0) ){
         1101  +  if( pPg->inJournal && (pPg->inCkpt || pPager->ckptInUse==0) ){
  1094   1102       pPager->dirtyFile = 1;
  1095   1103       return SQLITE_OK;
  1096   1104     }
  1097   1105   
  1098   1106     /* If we get this far, it means that the page needs to be
  1099   1107     ** written to the transaction journal or the ckeckpoint journal
  1100   1108     ** or both.
................................................................................
  1123   1131         pPager->errMask |= PAGER_ERR_FULL;
  1124   1132         return rc;
  1125   1133       }
  1126   1134       assert( pPager->aInJournal!=0 );
  1127   1135       pPager->aInJournal[pPg->pgno/8] |= 1<<(pPg->pgno&7);
  1128   1136       pPager->needSync = !pPager->noSync;
  1129   1137       pPg->inJournal = 1;
  1130         -    if( pPager->ckptOpen ){
         1138  +    if( pPager->ckptInUse ){
  1131   1139         pPager->aInCkpt[pPg->pgno/8] |= 1<<(pPg->pgno&7);
  1132   1140         pPg->inCkpt = 1;
  1133   1141       }
  1134   1142     }
  1135   1143   
  1136   1144     /* If the checkpoint journal is open and the page is not in it,
  1137   1145     ** then write the current page to the checkpoint journal.
  1138   1146     */
  1139         -  if( pPager->ckptOpen && !pPg->inCkpt && (int)pPg->pgno<=pPager->ckptSize ){
         1147  +  if( pPager->ckptInUse && !pPg->inCkpt && (int)pPg->pgno<=pPager->ckptSize ){
  1140   1148       assert( pPg->inJournal || (int)pPg->pgno>pPager->origDbSize );
  1141   1149       rc = sqliteOsWrite(&pPager->cpfd, &pPg->pgno, sizeof(Pgno));
  1142   1150       if( rc==SQLITE_OK ){
  1143   1151         rc = sqliteOsWrite(&pPager->cpfd, pData, SQLITE_PAGE_SIZE);
  1144   1152       }
  1145   1153       if( rc!=SQLITE_OK ){
  1146   1154         sqlitepager_rollback(pPager);
................................................................................
  1202   1210     Pager *pPager = pPg->pPager;
  1203   1211   
  1204   1212     if( pPager->state!=SQLITE_WRITELOCK || pPager->journalOpen==0 ) return;
  1205   1213     if( !pPg->inJournal && (int)pPg->pgno <= pPager->origDbSize ){
  1206   1214       assert( pPager->aInJournal!=0 );
  1207   1215       pPager->aInJournal[pPg->pgno/8] |= 1<<(pPg->pgno&7);
  1208   1216       pPg->inJournal = 1;
  1209         -    if( pPager->ckptOpen ){
         1217  +    if( pPager->ckptInUse ){
  1210   1218         pPager->aInCkpt[pPg->pgno/8] |= 1<<(pPg->pgno&7);
  1211   1219         pPg->inCkpt = 1;
  1212   1220       }
  1213   1221     }
  1214         -  if( pPager->ckptOpen && !pPg->inCkpt && (int)pPg->pgno<=pPager->ckptSize ){
         1222  +  if( pPager->ckptInUse && !pPg->inCkpt && (int)pPg->pgno<=pPager->ckptSize ){
  1215   1223       assert( pPg->inJournal || (int)pPg->pgno>pPager->origDbSize );
  1216   1224       assert( pPager->aInCkpt!=0 );
  1217   1225       pPager->aInCkpt[pPg->pgno/8] |= 1<<(pPg->pgno&7);
  1218   1226       pPg->inCkpt = 1;
  1219   1227     }
  1220   1228   }
  1221   1229   
................................................................................
  1341   1349   ** open.  A new checkpoint journal is created that can be used to rollback
  1342   1350   ** changes of a single SQL command within a larger transaction.
  1343   1351   */
  1344   1352   int sqlitepager_ckpt_begin(Pager *pPager){
  1345   1353     int rc;
  1346   1354     char zTemp[SQLITE_TEMPNAME_SIZE];
  1347   1355     assert( pPager->journalOpen );
  1348         -  assert( !pPager->ckptOpen );
         1356  +  assert( !pPager->ckptInUse );
  1349   1357     pPager->aInCkpt = sqliteMalloc( pPager->dbSize/8 + 1 );
  1350   1358     if( pPager->aInCkpt==0 ){
  1351   1359       sqliteOsReadLock(&pPager->fd);
  1352   1360       return SQLITE_NOMEM;
  1353   1361     }
  1354   1362     rc = sqliteOsFileSize(&pPager->jfd, &pPager->ckptJSize);
  1355   1363     if( rc ) goto ckpt_begin_failed;
  1356   1364     pPager->ckptSize = pPager->dbSize;
  1357         -  rc = sqlitepager_opentemp(zTemp, &pPager->cpfd);
  1358         -  if( rc ) goto ckpt_begin_failed;
  1359         -  pPager->ckptOpen = 1;
         1365  +  if( !pPager->ckptOpen ){
         1366  +    rc = sqlitepager_opentemp(zTemp, &pPager->cpfd);
         1367  +    if( rc ) goto ckpt_begin_failed;
         1368  +    pPager->ckptOpen = 1;
         1369  +  }
         1370  +  pPager->ckptInUse = 1;
  1360   1371     return SQLITE_OK;
  1361   1372    
  1362   1373   ckpt_begin_failed:
  1363   1374     if( pPager->aInCkpt ){
  1364   1375       sqliteFree(pPager->aInCkpt);
  1365   1376       pPager->aInCkpt = 0;
  1366   1377     }
................................................................................
  1367   1378     return rc;
  1368   1379   }
  1369   1380   
  1370   1381   /*
  1371   1382   ** Commit a checkpoint.
  1372   1383   */
  1373   1384   int sqlitepager_ckpt_commit(Pager *pPager){
  1374         -  if( pPager->ckptOpen ){
         1385  +  if( pPager->ckptInUse ){
  1375   1386       PgHdr *pPg;
  1376         -    sqliteOsClose(&pPager->cpfd);
  1377         -    pPager->ckptOpen = 0;
         1387  +    sqliteOsTruncate(&pPager->cpfd, 0);
         1388  +    pPager->ckptInUse = 0;
  1378   1389       sqliteFree( pPager->aInCkpt );
  1379   1390       pPager->aInCkpt = 0;
  1380   1391       for(pPg=pPager->pAll; pPg; pPg=pPg->pNextAll){
  1381   1392         pPg->inCkpt = 0;
  1382   1393       }
  1383   1394     }
  1384   1395     return SQLITE_OK;
................................................................................
  1385   1396   }
  1386   1397   
  1387   1398   /*
  1388   1399   ** Rollback a checkpoint.
  1389   1400   */
  1390   1401   int sqlitepager_ckpt_rollback(Pager *pPager){
  1391   1402     int rc;
  1392         -  if( pPager->ckptOpen ){
         1403  +  if( pPager->ckptInUse ){
  1393   1404       rc = pager_ckpt_playback(pPager);
  1394   1405       sqlitepager_ckpt_commit(pPager);
  1395   1406     }else{
  1396   1407       rc = SQLITE_OK;
  1397   1408     }
  1398   1409     return rc;
  1399   1410   }

Changes to test/expr.test.

     7      7   #    May you find forgiveness for yourself and forgive others.
     8      8   #    May you share freely, never taking more than you give.
     9      9   #
    10     10   #***********************************************************************
    11     11   # This file implements regression tests for SQLite library.  The
    12     12   # focus of this file is testing expressions.
    13     13   #
    14         -# $Id: expr.test,v 1.22 2002/05/30 02:35:12 drh Exp $
           14  +# $Id: expr.test,v 1.23 2002/05/30 12:27:03 drh Exp $
    15     15   
    16     16   set testdir [file dirname $argv0]
    17     17   source $testdir/tester.tcl
    18     18   
    19     19   # Create a table to work with.
    20     20   #
    21     21   execsql {CREATE TABLE test1(i1 int, i2 int, r1 real, r2 real, t1 text, t2 text)}
................................................................................
   350    350   	{CASE i1 WHEN 1 THEN 'one' WHEN NULL THEN 'two' ELSE 'error' END} {{}}
   351    351   test_expr expr-case.9 {i1=3} \
   352    352   	{CASE i1 WHEN 1 THEN 'one' WHEN 2 THEN 'two' ELSE 'error' END} error
   353    353   test_expr expr-case.10 {i1=3} \
   354    354   	{CASE i1 WHEN 1 THEN 'one' WHEN 2 THEN 'two' END} {{}}
   355    355   test_expr expr-case.11 {i1=null} \
   356    356   	{CASE i1 WHEN 1 THEN 'one' WHEN 2 THEN 'two' ELSE 3 END} {{}}
   357         -test_expr expr-case.12 {i1=7} \
          357  +test_expr expr-case.12 {i1=1} \
          358  +	{CASE i1 WHEN 1 THEN null WHEN 2 THEN 'two' ELSE 3 END} {{}}
          359  +test_expr expr-case.13 {i1=7} \
   358    360   	{ CASE WHEN i1 < 5 THEN 'low' 
   359    361   	       WHEN i1 < 10 THEN 'medium' 
   360    362                  WHEN i1 < 15 THEN 'high' ELSE 'error' END} medium
   361    363   
   362    364   
   363    365   # The sqliteExprIfFalse and sqliteExprIfTrue routines are only
   364    366   # executed as part of a WHERE clause.  Create a table suitable