/ Check-in [e396184c]
Login
SQLite training in Houston TX on 2019-11-05 (details)
Part of the 2019 Tcl Conference

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

Overview
Comment:Change the journal_mode pragma to remove the "default journal mode" concept. The journal_mode pragma only effects currently attached databases and does not change the behavior of future attachments.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1: e396184cd3bdb96e29ac33af5d1f631cac553341
User & Date: drh 2010-07-08 17:40:38
Context
2010-07-08
18:32
Fix test case numbering in backup_malloc.test and issue when running under Windows. check-in: aef26438 user: shaneh tags: trunk
17:40
Change the journal_mode pragma to remove the "default journal mode" concept. The journal_mode pragma only effects currently attached databases and does not change the behavior of future attachments. check-in: e396184c user: drh tags: trunk
16:50
Merged experimental crashsql() into trunk. check-in: 01b575ff user: shaneh tags: trunk
Changes
Hide Diffs Side-by-Side Diffs Ignore Whitespace Patch

Changes to src/attach.c.

   139    139       }else if( aNew->pSchema->file_format && aNew->pSchema->enc!=ENC(db) ){
   140    140         zErrDyn = sqlite3MPrintf(db, 
   141    141           "attached databases must use the same text encoding as main database");
   142    142         rc = SQLITE_ERROR;
   143    143       }
   144    144       pPager = sqlite3BtreePager(aNew->pBt);
   145    145       sqlite3PagerLockingMode(pPager, db->dfltLockMode);
   146         -    /* journal_mode set by the OP_JournalMode opcode that will following
   147         -    ** the OP_Function opcode that invoked this function. */
   148    146       sqlite3BtreeSecureDelete(aNew->pBt,
   149    147                                sqlite3BtreeSecureDelete(db->aDb[0].pBt,-1) );
   150    148     }
   151    149     aNew->safety_level = 3;
   152    150     aNew->zName = sqlite3DbStrDup(db, zName);
   153    151     if( rc==SQLITE_OK && aNew->zName==0 ){
   154    152       rc = SQLITE_NOMEM;
................................................................................
   336    334     assert( v || db->mallocFailed );
   337    335     if( v ){
   338    336       sqlite3VdbeAddOp3(v, OP_Function, 0, regArgs+3-pFunc->nArg, regArgs+3);
   339    337       assert( pFunc->nArg==-1 || (pFunc->nArg&0xff)==pFunc->nArg );
   340    338       sqlite3VdbeChangeP5(v, (u8)(pFunc->nArg));
   341    339       sqlite3VdbeChangeP4(v, -1, (char *)pFunc, P4_FUNCDEF);
   342    340   
   343         -    if( type==SQLITE_ATTACH ){
   344         -      /* On an attach, also set the journal mode.  Note that
   345         -      ** sqlite3VdbeUsesBtree() is not call here since the iDb index
   346         -      ** will be out of range prior to the new database being attached.
   347         -      ** The OP_JournalMode opcode will all sqlite3VdbeUsesBtree() for us.
   348         -      */
   349         -      sqlite3VdbeAddOp3(v, OP_JournalMode, db->nDb, regArgs+3, 
   350         -                           db->dfltJournalMode);
   351         -      sqlite3VdbeChangeP5(v, 1);
   352         -    }
   353         -
   354    341       /* Code an OP_Expire. For an ATTACH statement, set P1 to true (expire this
   355    342       ** statement only). For DETACH, set it to false (expire all existing
   356    343       ** statements).
   357    344       */
   358    345       sqlite3VdbeAddOp1(v, OP_Expire, (type==SQLITE_ATTACH));
   359    346     }
   360    347     

Changes to src/build.c.

  3419   3419       }
  3420   3420       db->aDb[1].pBt = pBt;
  3421   3421       assert( db->aDb[1].pSchema );
  3422   3422       if( SQLITE_NOMEM==sqlite3BtreeSetPageSize(pBt, db->nextPagesize, -1, 0) ){
  3423   3423         db->mallocFailed = 1;
  3424   3424         return 1;
  3425   3425       }
  3426         -    sqlite3PagerSetJournalMode(sqlite3BtreePager(pBt), db->dfltJournalMode);
  3427   3426     }
  3428   3427     return 0;
  3429   3428   }
  3430   3429   
  3431   3430   /*
  3432   3431   ** Generate VDBE code that will verify the schema cookie and start
  3433   3432   ** a read-transaction for all named database files.

Changes to src/pragma.c.

   531    531   
   532    532     /*
   533    533     **  PRAGMA [database.]journal_mode
   534    534     **  PRAGMA [database.]journal_mode =
   535    535     **                      (delete|persist|off|truncate|memory|wal|off)
   536    536     */
   537    537     if( sqlite3StrICmp(zLeft,"journal_mode")==0 ){
   538         -    int eMode;                    /* One of the PAGER_JOURNALMODE_XXX symbols */
          538  +    int eMode;        /* One of the PAGER_JOURNALMODE_XXX symbols */
          539  +    int ii;           /* Loop counter */
   539    540   
          541  +    /* Force the schema to be loaded on all databases.  This cases all
          542  +    ** database files to be opened and the journal_modes set. */
   540    543       if( sqlite3ReadSchema(pParse) ){
   541    544         goto pragma_out;
   542    545       }
   543    546   
   544    547       sqlite3VdbeSetNumCols(v, 1);
   545    548       sqlite3VdbeSetColName(v, 0, COLNAME_NAME, "journal_mode", SQLITE_STATIC);
   546    549   
   547    550       if( zRight==0 ){
          551  +      /* If there is no "=MODE" part of the pragma, do a query for the
          552  +      ** current mode */
   548    553         eMode = PAGER_JOURNALMODE_QUERY;
   549    554       }else{
   550    555         const char *zMode;
   551    556         int n = sqlite3Strlen30(zRight);
   552    557         for(eMode=0; (zMode = sqlite3JournalModename(eMode))!=0; eMode++){
   553    558           if( sqlite3StrNICmp(zRight, zMode, n)==0 ) break;
   554    559         }
   555    560         if( !zMode ){
          561  +        /* If the "=MODE" part does not match any known journal mode,
          562  +        ** then do a query */
   556    563           eMode = PAGER_JOURNALMODE_QUERY;
   557    564         }
   558    565       }
   559         -    if( pId2->n==0 && eMode==PAGER_JOURNALMODE_QUERY ){
   560         -      /* Simple "PRAGMA journal_mode;" statement. This is a query for
   561         -      ** the current default journal mode (which may be different to
   562         -      ** the journal-mode of the main database).
   563         -      */
   564         -      eMode = db->dfltJournalMode;
   565         -      sqlite3VdbeAddOp2(v, OP_String8, 0, 1);
   566         -      sqlite3VdbeChangeP4(v, -1, sqlite3JournalModename(eMode), P4_STATIC);
   567         -    }else{
   568         -      int ii;
   569         -
   570         -      if( pId2->n==0 ){
   571         -        /* When there is no database name before the "journal_mode" keyword
   572         -        ** in the PRAGMA, then the journal-mode will be set on
   573         -        ** all attached databases, as well as the main db file.
   574         -        **
   575         -        ** Also, the sqlite3.dfltJournalMode variable is set so that
   576         -        ** any subsequently attached databases also use the specified
   577         -        ** journal mode.
   578         -        */
   579         -        db->dfltJournalMode = (u8)eMode;
   580         -      }
   581         -
   582         -      for(ii=db->nDb-1; ii>=0; ii--){
   583         -        if( db->aDb[ii].pBt && (ii==iDb || pId2->n==0) ){
   584         -          sqlite3VdbeUsesBtree(v, ii);
   585         -          sqlite3VdbeAddOp3(v, OP_JournalMode, ii, 1, eMode);
   586         -        }
          566  +    if( eMode==PAGER_JOURNALMODE_QUERY && pId2->n==0 ){
          567  +      /* Convert "PRAGMA journal_mode" into "PRAGMA main.journal_mode" */
          568  +      iDb = 0;
          569  +      pId2->n = 1;
          570  +    }
          571  +    for(ii=db->nDb-1; ii>=0; ii--){
          572  +      if( db->aDb[ii].pBt && (ii==iDb || pId2->n==0) ){
          573  +        sqlite3VdbeUsesBtree(v, ii);
          574  +        sqlite3VdbeAddOp3(v, OP_JournalMode, ii, 1, eMode);
   587    575         }
   588    576       }
   589         -
   590    577       sqlite3VdbeAddOp2(v, OP_ResultRow, 1, 1);
   591    578     }else
   592    579   
   593    580     /*
   594    581     **  PRAGMA [database.]journal_size_limit
   595    582     **  PRAGMA [database.]journal_size_limit=N
   596    583     **

Changes to src/sqliteInt.h.

   793    793     int openFlags;                /* Flags passed to sqlite3_vfs.xOpen() */
   794    794     int errCode;                  /* Most recent error code (SQLITE_*) */
   795    795     int errMask;                  /* & result codes with this before returning */
   796    796     u8 autoCommit;                /* The auto-commit flag. */
   797    797     u8 temp_store;                /* 1: file 2: memory 0: default */
   798    798     u8 mallocFailed;              /* True if we have seen a malloc failure */
   799    799     u8 dfltLockMode;              /* Default locking-mode for attached dbs */
   800         -  u8 dfltJournalMode;           /* Default journal mode for attached dbs */
   801    800     signed char nextAutovac;      /* Autovac setting after VACUUM if >=0 */
   802    801     u8 suppressErr;               /* Do not issue error messages if true */
   803    802     int nextPagesize;             /* Pagesize after VACUUM if >0 */
   804    803     int nTable;                   /* Number of tables in the database */
   805    804     CollSeq *pDfltColl;           /* The default collating sequence (BINARY) */
   806    805     i64 lastRowid;                /* ROWID of most recent insert (see above) */
   807    806     u32 magic;                    /* Magic number for detect library misuse */

Changes to src/vdbe.c.

  5161   5161   ** PAGER_JOURNALMODE_XXX values. If changing between the various rollback
  5162   5162   ** modes (delete, truncate, persist, off and memory), this is a simple
  5163   5163   ** operation. No IO is required.
  5164   5164   **
  5165   5165   ** If changing into or out of WAL mode the procedure is more complicated.
  5166   5166   **
  5167   5167   ** Write a string containing the final journal-mode to register P2.
  5168         -**
  5169         -** If an attempt to change in to or out of WAL mode fails because another
  5170         -** connection also has the same database open, then an SQLITE_BUSY error
  5171         -** is raised if P5==0, or of P5!=0 the journal mode changed is skipped
  5172         -** without signaling the error.
  5173   5168   */
  5174   5169   case OP_JournalMode: {    /* out2-prerelease */
  5175   5170     Btree *pBt;                     /* Btree to change journal mode of */
  5176   5171     Pager *pPager;                  /* Pager associated with pBt */
  5177   5172     int eNew;                       /* New journal mode */
  5178   5173     int eOld;                       /* The old journal mode */
  5179   5174     const char *zFilename;          /* Name of database file for pPager */
................................................................................
  5263   5258           rc = sqlite3BtreeSetVersion(pBt, (eNew==PAGER_JOURNALMODE_WAL ? 2 : 1));
  5264   5259         }
  5265   5260       }
  5266   5261     }
  5267   5262   #endif /* ifndef SQLITE_OMIT_WAL */
  5268   5263   
  5269   5264     if( rc ){
  5270         -    if( rc==SQLITE_BUSY && pOp->p5!=0 ) rc = SQLITE_OK;
  5271   5265       eNew = eOld;
  5272   5266     }
  5273   5267     eNew = sqlite3PagerSetJournalMode(pPager, eNew);
  5274   5268   
  5275   5269     pOut = &aMem[pOp->p2];
  5276   5270     pOut->flags = MEM_Str|MEM_Static|MEM_Term;
  5277   5271     pOut->z = (char *)sqlite3JournalModename(eNew);

Changes to test/crash8.test.

   254    254         INSERT INTO ab VALUES(2, NULL);
   255    255         INSERT INTO ab VALUES(3, NULL);
   256    256         INSERT INTO ab VALUES(4, NULL);
   257    257         INSERT INTO ab VALUES(5, NULL);
   258    258         INSERT INTO ab VALUES(6, NULL);
   259    259         UPDATE ab SET b = randstr(1000,1000);
   260    260         ATTACH 'test2.db' AS aux;
          261  +      PRAGMA aux.journal_mode = persist;
   261    262         CREATE TABLE aux.ab(a, b);
   262    263         INSERT INTO aux.ab SELECT * FROM main.ab;
   263    264   
   264    265         UPDATE aux.ab SET b = randstr(1000,1000) WHERE a>=1;
   265    266         UPDATE ab SET b = randstr(1000,1000) WHERE a>=1;
   266    267       }
   267    268       list [file exists test.db-journal] [file exists test2.db-journal]

Changes to test/jrnlmode.test.

   128    128         PRAGMA aux1.journal_mode;
   129    129       }
   130    130     } [list off [temp_journal_mode persist] memory]
   131    131     do_test jrnlmode-1.11 {
   132    132       execsql {
   133    133         PRAGMA journal_mode;
   134    134       }
   135         -  } {persist}
          135  +  } {off}
   136    136     do_test jrnlmode-1.12 {
   137    137       execsql {
   138    138         ATTACH ':memory:' as aux2;
   139    139       }
   140    140       execsql {
   141    141         PRAGMA main.journal_mode;
   142    142         PRAGMA aux1.journal_mode;
................................................................................
   313    313   
   314    314     do_test jrnlmode-5.2 {
   315    315       execsql { PRAGMA journal_size_limit }
   316    316     } {-1}
   317    317     do_test jrnlmode-5.3 {
   318    318       execsql { 
   319    319         ATTACH 'test2.db' AS aux;
          320  +      PRAGMA aux.journal_mode=persist;
   320    321         PRAGMA aux.journal_size_limit;
   321    322       }
   322         -  } {-1}
          323  +  } {persist -1}
   323    324     do_test jrnlmode-5.4.1 {
   324    325       execsql { PRAGMA aux.journal_size_limit = 999999999999 }
   325    326     } {999999999999}
   326    327     do_test jrnlmode-5.4.2 {
   327    328       execsql { PRAGMA aux.journal_size_limit = 10240 }
   328    329     } {10240}
   329    330     do_test jrnlmode-5.5 {
................................................................................
   333    334       execsql { PRAGMA journal_size_limit }
   334    335     } {20480}
   335    336     do_test jrnlmode-5.7 {
   336    337       execsql { PRAGMA aux.journal_size_limit }
   337    338     } {10240}
   338    339   
   339    340     do_test jrnlmode-5.8 {
   340         -    execsql { ATTACH 'test3.db' AS aux2 }
   341         -  } {}
          341  +    execsql {
          342  +      ATTACH 'test3.db' AS aux2;
          343  +      PRAGMA aux2.journal_mode=persist;
          344  +    }
          345  +  } {persist}
   342    346   
   343    347     do_test jrnlmode-5.9 {
   344    348       execsql {
   345    349         CREATE TABLE main.t1(a, b, c);
   346    350         CREATE TABLE aux.t2(a, b, c);
   347    351         CREATE TABLE aux2.t3(a, b, c);
   348    352       }

Changes to test/walmode.test.

   299    299     sqlite3 db test.db
   300    300     execsql {
   301    301       PRAGMA journal_mode = WAL;
   302    302       CREATE TABLE t1(a, b);
   303    303     }
   304    304   } {wal}
   305    305   foreach {tn sql result} {
   306         -  1  "PRAGMA journal_mode"                delete
          306  +  1  "PRAGMA journal_mode"                wal
   307    307     2  "PRAGMA main.journal_mode"           wal
   308    308     3  "PRAGMA journal_mode = delete"       delete
   309    309     4  "PRAGMA journal_mode"                delete
   310    310     5  "PRAGMA main.journal_mode"           delete
   311    311     6  "PRAGMA journal_mode = wal"          wal
   312         -  7  "PRAGMA journal_mode"                delete
          312  +  7  "PRAGMA journal_mode"                wal
   313    313     8  "PRAGMA main.journal_mode"           wal
   314    314   
   315         -  9  "PRAGMA journal_mode"                delete
          315  +  9  "PRAGMA journal_mode"                wal
   316    316    10  "PRAGMA main.journal_mode"           wal
   317    317    11  "PRAGMA main.journal_mode = delete"  delete
   318    318    12  "PRAGMA journal_mode"                delete
   319    319    13  "PRAGMA main.journal_mode"           delete
   320    320    14  "PRAGMA main.journal_mode = wal"     wal
   321         - 15  "PRAGMA journal_mode"                delete
          321  + 15  "PRAGMA journal_mode"                wal
   322    322    16  "PRAGMA main.journal_mode"           wal
   323    323   } {
   324    324     do_test walmode-7.$tn { 
   325    325       db close
   326    326       sqlite3 db test.db
   327    327       execsql $sql
   328    328     } $result
................................................................................
   337    337   do_execsql_test walmode-8.1 {
   338    338     CREATE TABLE t1(a, b);
   339    339     PRAGMA journal_mode = WAL;
   340    340     ATTACH 'test.db2' AS two;
   341    341     CREATE TABLE two.t2(a, b);
   342    342   } {wal}
   343    343   do_execsql_test walmode-8.2 { PRAGMA main.journal_mode }         {wal}
   344         -do_execsql_test walmode-8.3 { PRAGMA two.journal_mode  }         {wal}
          344  +do_execsql_test walmode-8.3 { PRAGMA two.journal_mode  }         {delete}
   345    345   do_execsql_test walmode-8.4 { PRAGMA two.journal_mode = DELETE } {delete}
   346    346   
   347    347   db close
   348    348   sqlite3 db test.db
   349    349   do_execsql_test walmode-8.5  { ATTACH 'test.db2' AS two }          {}
   350    350   do_execsql_test walmode-8.6  { PRAGMA main.journal_mode }          {wal}
   351    351   do_execsql_test walmode-8.7  { PRAGMA two.journal_mode  }          {delete}
   352    352   do_execsql_test walmode-8.8  { INSERT INTO two.t2 DEFAULT VALUES } {}
   353    353   do_execsql_test walmode-8.9  { PRAGMA two.journal_mode  }          {delete}
   354    354   do_execsql_test walmode-8.10 { INSERT INTO t1 DEFAULT VALUES } {}
   355    355   do_execsql_test walmode-8.11 { PRAGMA main.journal_mode  }         {wal}
   356         -do_execsql_test walmode-8.12 { PRAGMA journal_mode  }              {delete}
          356  +do_execsql_test walmode-8.12 { PRAGMA journal_mode  }              {wal}
          357  +
          358  +# Change to WAL mode on test2.db and make sure (in the tests that follow)
          359  +# that this mode change persists. 
          360  +do_test walmode-8.x1 {
          361  +  execsql {
          362  +     PRAGMA two.journal_mode=WAL;
          363  +     PRAGMA two.journal_mode;
          364  +  }
          365  +} {wal wal}
   357    366   
   358    367   db close
   359    368   sqlite3 db test.db
   360    369   do_execsql_test walmode-8.13 { PRAGMA journal_mode = WAL }         {wal}
   361    370   do_execsql_test walmode-8.14 { ATTACH 'test.db2' AS two  }         {}
   362    371   do_execsql_test walmode-8.15 { PRAGMA main.journal_mode  }         {wal}
   363    372   do_execsql_test walmode-8.16 { PRAGMA two.journal_mode   }         {wal}
................................................................................
   365    374   do_execsql_test walmode-8.18 { PRAGMA two.journal_mode   }         {wal}
   366    375    
   367    376   sqlite3 db2 test.db2
   368    377   do_test walmode-8.19 { execsql { PRAGMA main.journal_mode } db2 }  {wal}
   369    378   db2 close
   370    379   
   371    380   finish_test
   372         -