/ Check-in [2954ab50]
Login
☰Home Timeline Branches

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

Overview
Comment:Add an experimental extension for applying bulk updates to databases.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | ota-update
Files: files | file ages | folders
SHA1: 2954ab501049968430011b63d046eb42ff37a56c
User & Date: dan 2014-09-02 19:59:40
Context
2014-09-03
08:25
Add a command line program that uses the extension. This serves as example code and is also useful for performance testing. (check-in: ffa1524e user: dan tags: ota-update)
2014-09-02
19:59
Add an experimental extension for applying bulk updates to databases. (check-in: 2954ab50 user: dan tags: ota-update)
2014-09-01
23:06
Update comments in the ANALYZE command that describe how the Stat4Accum objecct is passed around within the VDBE. No changes to functional code. (check-in: 9779c7a9 user: drh tags: trunk)
Changes
Hide Diffs Side-by-Side Diffs Ignore Whitespace Patch

Added ext/ota/ota1.test. 

            1  +# 2014 August 30
            2  +#
            3  +# The author disclaims copyright to this source code.  In place of
            4  +# a legal notice, here is a blessing:
            5  +#
            6  +#    May you do good and not evil.
            7  +#    May you find forgiveness for yourself and forgive others.
            8  +#    May you share freely, never taking more than you give.
            9  +#
           10  +#***********************************************************************
           11  +#
           12  +
           13  +set testdir [file join [file dirname $argv0] .. .. test]
           14  +source $testdir/tester.tcl
           15  +set ::testprefix ota1
           16  +
           17  +
           18  +# Create a simple OTA database. That expects to write to a table:
           19  +#
           20  +#   CREATE TABLE t1(a INTEGER PRIMARY KEY, b, c);
           21  +#
           22  +proc create_ota1 {filename} {
           23  +  forcedelete $filename
           24  +  sqlite3 ota1 $filename  
           25  +  ota1 eval {
           26  +    CREATE TABLE data_t1(a, b, c, ota_control);
           27  +    INSERT INTO data_t1 VALUES(1, 2, 3, 0);
           28  +    INSERT INTO data_t1 VALUES(2, 'two', 'three', 0);
           29  +    INSERT INTO data_t1 VALUES(3, NULL, 8.2, 0);
           30  +  }
           31  +  ota1 close
           32  +  return $filename
           33  +}
           34  +
           35  +# Create an empty target database suitable for the OTA created by 
           36  +# [create_ota1].
           37  +#
           38  +#   CREATE TABLE t1(a INTEGER PRIMARY KEY, b, c);
           39  +#
           40  +proc create_target1 {filename} {
           41  +  forcedelete $filename
           42  +  sqlite3 target1 $filename  
           43  +  target1 eval { CREATE TABLE t1(a INTEGER PRIMARY KEY, b, c) }
           44  +  target1 close
           45  +  return $filename
           46  +}
           47  +
           48  +# Run the OTA in file $ota on target database $target until completion.
           49  +#
           50  +proc run_ota {target ota} {
           51  +  sqlite3ota ota $target $ota
           52  +  while { [ota step]=="SQLITE_OK" } {}
           53  +  ota close
           54  +}
           55  +
           56  +proc step_ota {target ota} {
           57  +  while 1 {
           58  +    sqlite3ota ota $target $ota
           59  +    set rc [ota step]
           60  +    ota close
           61  +    if {$rc != "SQLITE_OK"} break
           62  +  }
           63  +  set rc
           64  +}
           65  +
           66  +foreach {tn2 cmd} {1 step_ota 2 run_ota} {
           67  +  foreach {tn schema} {
           68  +    1 {
           69  +      CREATE TABLE t1(a INTEGER PRIMARY KEY, b, c);
           70  +    }
           71  +    2 { 
           72  +      CREATE TABLE t1(a INTEGER PRIMARY KEY, b, c);
           73  +      CREATE INDEX i1 ON t1(b);
           74  +    }
           75  +    3 { 
           76  +      CREATE TABLE t1(a PRIMARY KEY, b, c) WITHOUT ROWID;
           77  +    }
           78  +    4 { 
           79  +      CREATE TABLE t1(a PRIMARY KEY, b, c) WITHOUT ROWID;
           80  +      CREATE INDEX i1 ON t1(b);
           81  +    }
           82  +    5 { 
           83  +      CREATE TABLE t1(a, b, c, PRIMARY KEY(a, c)) WITHOUT ROWID;
           84  +      CREATE INDEX i1 ON t1(b);
           85  +    }
           86  +    6 { 
           87  +      CREATE TABLE t1(a, b, c, PRIMARY KEY(c)) WITHOUT ROWID;
           88  +      CREATE INDEX i1 ON t1(b, a);
           89  +    }
           90  +    7 { 
           91  +      CREATE TABLE t1(a INTEGER PRIMARY KEY, b, c);
           92  +      CREATE INDEX i1 ON t1(b, c);
           93  +      CREATE INDEX i2 ON t1(c, b);
           94  +      CREATE INDEX i3 ON t1(a, b, c, a, b, c);
           95  +    }
           96  +  } {
           97  +    reset_db
           98  +    execsql $schema
           99  +  
          100  +    do_test 1.$tn2.$tn.1 {
          101  +      create_ota1 ota.db
          102  +      $cmd test.db ota.db
          103  +    } {SQLITE_DONE}
          104  +    
          105  +    do_execsql_test 1.$tn2.$tn.2 {
          106  +      SELECT * FROM t1 ORDER BY a ASC;
          107  +    } {
          108  +      1 2 3 
          109  +      2 two three 
          110  +      3 {} 8.2
          111  +    }
          112  +  
          113  +    do_execsql_test 1.$tn2.$tn.3 { PRAGMA integrity_check } ok
          114  +  }
          115  +}
          116  +
          117  +#-------------------------------------------------------------------------
          118  +# Check that an OTA cannot be applied to a table that has no PK.
          119  +#
          120  +reset_db
          121  +create_ota1 ota.db
          122  +do_execsql_test 2.1 { CREATE TABLE t1(a, b, c) }
          123  +do_test 2.2 {
          124  +  sqlite3ota ota test.db ota.db
          125  +  ota step
          126  +} {SQLITE_ERROR}
          127  +do_test 2.3 {
          128  +  list [catch { ota close } msg] $msg
          129  +} {1 {SQLITE_ERROR - table t1 has no PRIMARY KEY}}
          130  +
          131  +reset_db
          132  +do_execsql_test 2.4 { CREATE TABLE t1(a PRIMARY KEY, b, c) }
          133  +do_test 2.5 {
          134  +  sqlite3ota ota test.db ota.db
          135  +  ota step
          136  +} {SQLITE_ERROR}
          137  +do_test 2.6 {
          138  +  list [catch { ota close } msg] $msg
          139  +} {1 {SQLITE_ERROR - table t1 has no PRIMARY KEY}}
          140  +
          141  +#-------------------------------------------------------------------------
          142  +# Check that if a UNIQUE constraint is violated the current and all 
          143  +# subsequent [ota step] calls return SQLITE_CONSTRAINT. And that the OTA 
          144  +# transaction is rolled back by the [ota close] that deletes the ota 
          145  +# handle.
          146  +#
          147  +foreach {tn errcode errmsg schema} {
          148  +  1 SQLITE_CONSTRAINT "UNIQUE constraint failed: t1.a" {
          149  +    CREATE TABLE t1(a INTEGER PRIMARY KEY, b, c);
          150  +    INSERT INTO t1 VALUES(3, 2, 1);
          151  +  } 
          152  +
          153  +  2 SQLITE_CONSTRAINT "UNIQUE constraint failed: t1.c" {
          154  +    CREATE TABLE t1(a INTEGER PRIMARY KEY, b, c UNIQUE);
          155  +    INSERT INTO t1 VALUES(4, 2, 'three');
          156  +  } 
          157  +
          158  +} {
          159  +  reset_db
          160  +  execsql $schema
          161  +  set cksum [dbcksum db main]
          162  +
          163  +  do_test 3.$tn.1 {
          164  +    create_ota1 ota.db
          165  +    sqlite3ota ota test.db ota.db
          166  +    while {[set res [ota step]]=="SQLITE_OK"} {}
          167  +    set res
          168  +  } $errcode
          169  +
          170  +  do_test 3.$tn.2 { ota step } $errcode
          171  +
          172  +  do_test 3.$tn.3 { 
          173  +    list [catch { ota close } msg] $msg
          174  +  } [list 1 "$errcode - $errmsg"]
          175  +
          176  +  do_test 3.$tn.4 { dbcksum db main } $cksum
          177  +}
          178  +
          179  +
          180  +finish_test
          181  +

Added ext/ota/ota2.test. 

            1  +# 2014 August 30
            2  +#
            3  +# The author disclaims copyright to this source code.  In place of
            4  +# a legal notice, here is a blessing:
            5  +#
            6  +#    May you do good and not evil.
            7  +#    May you find forgiveness for yourself and forgive others.
            8  +#    May you share freely, never taking more than you give.
            9  +#
           10  +#***********************************************************************
           11  +#
           12  +
           13  +set testdir [file join [file dirname $argv0] .. .. test]
           14  +source $testdir/tester.tcl
           15  +set ::testprefix ota2
           16  +
           17  +
           18  +do_execsql_test 1.0 {
           19  +  PRAGMA ota_mode = 1;
           20  +  PRAGMA journal_mode = wal;
           21  +  CREATE TABLE t1(a, b);
           22  +  BEGIN;
           23  +    INSERT INTO t1 VALUES(1, 2);
           24  +} {wal}
           25  +
           26  +do_test 1.1 {
           27  +  set state [sqlite3_transaction_save db]
           28  +  db close
           29  +  file exists test.db-wal
           30  +} {1}
           31  +
           32  +do_test 1.2 {
           33  +  sqlite3 db test.db
           34  +  db eval {SELECT * FROM t1}
           35  +} {}
           36  +
           37  +do_test 1.3 {
           38  +  execsql {BEGIN IMMEDIATE}
           39  +  sqlite3_transaction_restore db $::state
           40  +  db eval {SELECT * FROM t1}
           41  +} {1 2}
           42  +
           43  +do_test 1.4 {
           44  +  execsql {
           45  +    INSERT INTO t1 VALUES(3, 4);
           46  +    COMMIT;
           47  +    SELECT * FROM t1;
           48  +  }
           49  +} {1 2 3 4}
           50  +
           51  +do_test 1.5 {
           52  +  db close
           53  +  file exists test.db-wal
           54  +} {0}
           55  +
           56  +do_test 1.5 {
           57  +  sqlite3 db test.db
           58  +  db eval {SELECT * FROM t1}
           59  +} {1 2 3 4}
           60  +
           61  +finish_test
           62  +

Added ext/ota/sqlite3ota.c. 

            1  +/*
            2  +** 2014 August 30
            3  +**
            4  +** The author disclaims copyright to this source code.  In place of
            5  +** a legal notice, here is a blessing:
            6  +**
            7  +**    May you do good and not evil.
            8  +**    May you find forgiveness for yourself and forgive others.
            9  +**    May you share freely, never taking more than you give.
           10  +**
           11  +*************************************************************************
           12  +*/
           13  +
           14  +#include <assert.h>
           15  +#include <string.h>
           16  +
           17  +#include "sqlite3.h"
           18  +#include "sqlite3ota.h"
           19  +
           20  +
           21  +/*
           22  +** The ota_state table is used to save the state of a partially applied
           23  +** update so that it can be resumed later. The table contains at most a
           24  +** single row:
           25  +**
           26  +**   "wal_state" -> Blob to use with sqlite3_transaction_restore().
           27  +**
           28  +**   "tbl"       -> Table currently being written (target database names).
           29  +**
           30  +**   "idx"       -> Index currently being written (target database names).
           31  +**                  Or, if the main table is being written, a NULL value.
           32  +**
           33  +**   "row"       -> Last rowid processed from ota database table (i.e. data_%).
           34  +**
           35  +**   "progress"  -> total number of key/value b-tree operations performed
           36  +**                  so far as part of this ota update.
           37  +*/
           38  +#define OTA_CREATE_STATE "CREATE TABLE IF NOT EXISTS ota_state"        \
           39  +                             "(wal_state, tbl, idx, row, progress)"
           40  +
           41  +typedef struct OtaTblIter OtaTblIter;
           42  +typedef struct OtaIdxIter OtaIdxIter;
           43  +
           44  +/*
           45  +** Iterator used to iterate through all data tables in the OTA. As follows:
           46  +**
           47  +**   OtaTblIter iter;
           48  +**   for(rc=tblIterFirst(db, &iter); 
           49  +**       rc==SQLITE_OK && iter.zTarget; 
           50  +**       rc=tblIterNext(&iter)
           51  +**   ){
           52  +**   }
           53  +*/
           54  +struct OtaTblIter {
           55  +  sqlite3_stmt *pTblIter;         /* Iterate through tables */
           56  +  int iEntry;                     /* Index of current entry (from 1) */
           57  +
           58  +  /* Output varibles. zTarget==0 implies EOF. */
           59  +  const char *zTarget;            /* Name of target table */
           60  +  const char *zSource;            /* Name of source table */
           61  +
           62  +  /* Useful things populated by a call to tblIterPrepareAll() */
           63  +  int nCol;                       /* Number of columns in this table */
           64  +  char **azCol;                   /* Array of quoted column names */
           65  +  sqlite3_stmt *pSelect;          /* PK b-tree SELECT statement */
           66  +  sqlite3_stmt *pInsert;          /* PK b-tree INSERT statement */
           67  +};
           68  +
           69  +/*
           70  +** API is:
           71  +**
           72  +**     idxIterFirst()
           73  +**     idxIterNext()
           74  +**     idxIterFinalize()
           75  +**     idxIterPrepareAll()
           76  +*/
           77  +struct OtaIdxIter {
           78  +  sqlite3_stmt *pIdxIter;         /* Iterate through indexes */
           79  +  int iEntry;                     /* Index of current entry (from 1) */
           80  +
           81  +  /* Output varibles. zTarget==0 implies EOF. */
           82  +  const char *zIndex;             /* Name of index */
           83  +
           84  +  int nCol;                       /* Number of columns in index */
           85  +  int *aiCol;                     /* Array of column indexes */
           86  +  sqlite3_stmt *pWriter;          /* Index writer */
           87  +  sqlite3_stmt *pSelect;          /* Select to read values in index order */
           88  +};
           89  +
           90  +
           91  +struct sqlite3ota {
           92  +  sqlite3 *dbDest;                /* Target db */
           93  +  sqlite3 *dbOta;                 /* Ota db */
           94  +
           95  +  int rc;                         /* Value returned by last ota_step() call */
           96  +  char *zErrmsg;                  /* Error message if rc!=SQLITE_OK */
           97  +
           98  +  OtaTblIter tbliter;             /* Used to iterate through tables */
           99  +  OtaIdxIter idxiter;             /* Used to iterate through indexes */
          100  +};
          101  +
          102  +static int prepareAndCollectError(
          103  +  sqlite3 *db, 
          104  +  const char *zSql, 
          105  +  sqlite3_stmt **ppStmt,
          106  +  char **pzErrmsg
          107  +){
          108  +  int rc = sqlite3_prepare_v2(db, zSql, -1, ppStmt, 0);
          109  +  if( rc!=SQLITE_OK ){
          110  +    *pzErrmsg = sqlite3_mprintf("%s", sqlite3_errmsg(db));
          111  +    *ppStmt = 0;
          112  +  }
          113  +  return rc;
          114  +}
          115  +
          116  +/*
          117  +** Unless it is NULL, argument zSql points to a buffer allocated using
          118  +** sqlite3_malloc containing an SQL statement. This function prepares the SQL
          119  +** statement against database db and frees the buffer. If statement 
          120  +** compilation is successful, *ppStmt is set to point to the new statement 
          121  +** handle and SQLITE_OK is returned. 
          122  +**
          123  +** Otherwise, if an error occurs, *ppStmt is set to NULL and an error code
          124  +** returned. In this case, *pzErrmsg may also be set to point to an error
          125  +** message. It is the responsibility of the caller to free this error message
          126  +** buffer using sqlite3_free().
          127  +**
          128  +** If argument zSql is NULL, this function assumes that an OOM has occurred.
          129  +** In this case SQLITE_NOMEM is returned and *ppStmt set to NULL.
          130  +*/
          131  +static int prepareFreeAndCollectError(
          132  +  sqlite3 *db, 
          133  +  char *zSql, 
          134  +  sqlite3_stmt **ppStmt,
          135  +  char **pzErrmsg
          136  +){
          137  +  int rc;
          138  +  assert( *pzErrmsg==0 );
          139  +  if( zSql==0 ){
          140  +    rc = SQLITE_NOMEM;
          141  +    *ppStmt = 0;
          142  +  }else{
          143  +    rc = prepareAndCollectError(db, zSql, ppStmt, pzErrmsg);
          144  +    sqlite3_free(zSql);
          145  +  }
          146  +  return rc;
          147  +}
          148  +
          149  +static char *quoteSqlName(const char *zName){
          150  +  int nName = strlen(zName);
          151  +  char *zRet = sqlite3_malloc(nName * 2 + 2 + 1);
          152  +  if( zRet ){
          153  +    int i;
          154  +    char *p = zRet;
          155  +    *p++ = '"';
          156  +    for(i=0; i<nName; i++){
          157  +      if( zName[i]=='"' ) *p++ = '"';
          158  +      *p++ = zName[i];
          159  +    }
          160  +    *p++ = '"';
          161  +    *p++ = '\0';
          162  +  }
          163  +  return zRet;
          164  +}
          165  +
          166  +static int tblIterPrepareAll(sqlite3ota *p){
          167  +  OtaTblIter *pIter = &p->tbliter;
          168  +  int rc = SQLITE_OK;
          169  +  char *zCol = 0;
          170  +  char *zBindings = 0;
          171  +  char *zSql;
          172  +  sqlite3_stmt *pPragma = 0;
          173  +  int i;
          174  +  int bSeenPk = 0;                /* Set to true once PK column seen */
          175  +
          176  +  /* Allocate and populate the azCol[] array */
          177  +  zSql = sqlite3_mprintf("PRAGMA main.table_info(%Q)", pIter->zTarget);
          178  +  rc = prepareFreeAndCollectError(p->dbDest, zSql, &pPragma, &p->zErrmsg);
          179  +  pIter->nCol = 0;
          180  +  if( rc==SQLITE_OK ){
          181  +    while( SQLITE_ROW==sqlite3_step(pPragma) ){
          182  +      const char *zName = (const char*)sqlite3_column_text(pPragma, 1);
          183  +      if( (pIter->nCol % 4)==0 ){
          184  +        int nByte = sizeof(char*) * (pIter->nCol+4);
          185  +        char **azNew = (char**)sqlite3_realloc(pIter->azCol, nByte);
          186  +        if( azNew==0 ){
          187  +          rc = SQLITE_NOMEM;
          188  +          break;
          189  +        }
          190  +        pIter->azCol = azNew;
          191  +      }
          192  +      pIter->azCol[pIter->nCol] = quoteSqlName(zName);
          193  +      if( pIter->azCol[pIter->nCol]==0 ){
          194  +        rc = SQLITE_NOMEM;
          195  +        break;
          196  +      }
          197  +      pIter->nCol++;
          198  +      if( sqlite3_column_int(pPragma, 5) ) bSeenPk = 1;
          199  +    }
          200  +    if( rc==SQLITE_OK ){
          201  +      rc = sqlite3_finalize(pPragma);
          202  +    }else{
          203  +      sqlite3_finalize(pPragma);
          204  +    }
          205  +  }
          206  +
          207  +  /* If the table has no PRIMARY KEY, throw an exception. */
          208  +  if( bSeenPk==0 ){
          209  +    p->zErrmsg = sqlite3_mprintf("table %s has no PRIMARY KEY", pIter->zTarget);
          210  +    rc = SQLITE_ERROR;
          211  +  }
          212  +
          213  +  /* Populate the zCol variable */
          214  +  for(i=0; rc==SQLITE_OK && i<pIter->nCol; i++){
          215  +    zCol = sqlite3_mprintf("%z%s%s", zCol, (i==0?"":", "), pIter->azCol[i]);
          216  +    if( zCol==0 ){
          217  +      rc = SQLITE_NOMEM;
          218  +    }
          219  +  }
          220  +
          221  +  /* Allocate and populate zBindings */
          222  +  if( rc==SQLITE_OK ){
          223  +    zBindings = (char*)sqlite3_malloc(pIter->nCol * 2);
          224  +    if( zBindings==0 ){
          225  +      rc = SQLITE_NOMEM;
          226  +    }else{
          227  +      int i;
          228  +      for(i=0; i<pIter->nCol; i++){
          229  +        zBindings[i*2] = '?';
          230  +        zBindings[i*2+1] = ',';
          231  +      }
          232  +      zBindings[pIter->nCol*2-1] = '\0';
          233  +    }
          234  +  }
          235  +
          236  +  /* Create OtaTblIter.pSelect */
          237  +  if( rc==SQLITE_OK ){
          238  +    zSql = sqlite3_mprintf("SELECT rowid, %s FROM %Q", zCol, pIter->zSource);
          239  +    rc = prepareFreeAndCollectError(p->dbOta,zSql,&pIter->pSelect, &p->zErrmsg);
          240  +  }
          241  +
          242  +  /* Create OtaTblIter.pInsert */
          243  +  if( rc==SQLITE_OK ){
          244  +    zSql = sqlite3_mprintf("INSERT INTO %Q(%s) VALUES(%s)", 
          245  +        pIter->zTarget, zCol, zBindings
          246  +    );
          247  +    rc = prepareFreeAndCollectError(p->dbDest,zSql,&pIter->pInsert,&p->zErrmsg);
          248  +  }
          249  +
          250  +  sqlite3_free(zCol);
          251  +  sqlite3_free(zBindings);
          252  +  return rc;
          253  +}
          254  +
          255  +static void tblIterFreeAll(OtaTblIter *pIter){
          256  +  int i;
          257  +
          258  +  sqlite3_finalize(pIter->pSelect);
          259  +  sqlite3_finalize(pIter->pInsert);
          260  +  for(i=0; i<pIter->nCol; i++) sqlite3_free(pIter->azCol[i]);
          261  +  sqlite3_free(pIter->azCol);
          262  +  pIter->azCol = 0;
          263  +  pIter->pSelect = 0;
          264  +  pIter->pInsert = 0;
          265  +  pIter->nCol = 0;
          266  +}
          267  +
          268  +static int tblIterNext(OtaTblIter *pIter){
          269  +  int rc;
          270  +
          271  +  tblIterFreeAll(pIter);
          272  +  assert( pIter->pTblIter );
          273  +  rc = sqlite3_step(pIter->pTblIter);
          274  +  if( rc==SQLITE_ROW ){
          275  +    pIter->zSource = (const char*)sqlite3_column_text(pIter->pTblIter, 0);
          276  +    pIter->zTarget = &pIter->zSource[5]; assert( 5==strlen("data_") );
          277  +    pIter->iEntry++;
          278  +  }else{
          279  +    pIter->zSource = 0;
          280  +    pIter->zTarget = 0;
          281  +  }
          282  +
          283  +  if( rc==SQLITE_ROW || rc==SQLITE_DONE ) rc = SQLITE_OK;
          284  +  return rc;
          285  +}
          286  +
          287  +static int tblIterFirst(sqlite3 *db, OtaTblIter *pIter){
          288  +  int rc;                         /* return code */
          289  +  memset(pIter, 0, sizeof(OtaTblIter));
          290  +  rc = sqlite3_prepare_v2(db, 
          291  +      "SELECT name FROM sqlite_master "
          292  +      "WHERE type='table' AND name LIKE 'data_%'", -1, &pIter->pTblIter, 0
          293  +  );
          294  +  if( rc==SQLITE_OK ){
          295  +    rc = tblIterNext(pIter);
          296  +  }
          297  +  return rc;
          298  +}
          299  +
          300  +
          301  +static void tblIterFinalize(OtaTblIter *pIter){
          302  +  tblIterFreeAll(pIter);
          303  +  sqlite3_finalize(pIter->pTblIter);
          304  +  memset(pIter, 0, sizeof(OtaTblIter));
          305  +}
          306  +
          307  +static void idxIterFreeAll(OtaIdxIter *pIter){
          308  +  sqlite3_finalize(pIter->pWriter);
          309  +  sqlite3_finalize(pIter->pSelect);
          310  +  pIter->pWriter = 0;
          311  +  pIter->pSelect = 0;
          312  +  pIter->aiCol = 0;
          313  +  pIter->nCol = 0;
          314  +}
          315  +
          316  +static int idxIterPrepareAll(sqlite3ota *p){
          317  +  int rc;
          318  +  int i;                          /* Iterator variable */
          319  +  char *zSql = 0;
          320  +  char *zCols = 0;                /* Columns list */
          321  +  OtaIdxIter *pIter = &p->idxiter;
          322  +
          323  +  /* Prepare the writer statement to write (insert) entries into the index. */
          324  +  rc = sqlite3_index_writer(
          325  +      p->dbDest, 0, pIter->zIndex, &pIter->pWriter, &pIter->aiCol, &pIter->nCol
          326  +  );
          327  +
          328  +  /* Prepare a SELECT statement to read values from the source table in 
          329  +  ** the same order as they are stored in the current index. The statement 
          330  +  ** is:
          331  +  **
          332  +  **     SELECT rowid, <cols> FROM data_<tbl> ORDER BY <cols>
          333  +  */
          334  +  for(i=0; rc==SQLITE_OK && i<pIter->nCol; i++){
          335  +    const char *zQuoted = p->tbliter.azCol[ pIter->aiCol[i] ];
          336  +    zCols = sqlite3_mprintf("%z%s%s", zCols, zCols?", ":"", zQuoted);
          337  +    if( !zCols ){
          338  +      rc = SQLITE_NOMEM;
          339  +    }
          340  +  }
          341  +  if( rc==SQLITE_OK ){
          342  +    const char *zFmt = "SELECT rowid, %s FROM %Q ORDER BY %s";
          343  +    zSql = sqlite3_mprintf(zFmt, zCols, p->tbliter.zSource, zCols);
          344  +    if( zSql ){
          345  +      sqlite3_stmt **pp = &p->idxiter.pSelect;
          346  +      rc = prepareFreeAndCollectError(p->dbOta, zSql, pp, &p->zErrmsg);
          347  +    }else{
          348  +      rc = SQLITE_NOMEM;
          349  +    }
          350  +  }
          351  +
          352  +  sqlite3_free(zCols);
          353  +  return rc;
          354  +}
          355  +
          356  +static int idxIterNext(OtaIdxIter *pIter){
          357  +  int rc;
          358  +
          359  +  idxIterFreeAll(pIter);
          360  +  assert( pIter->pIdxIter );
          361  +  rc = sqlite3_step(pIter->pIdxIter);
          362  +  if( rc==SQLITE_ROW ){
          363  +    pIter->zIndex = (const char*)sqlite3_column_text(pIter->pIdxIter, 0);
          364  +    pIter->iEntry++;
          365  +  }else{
          366  +    pIter->zIndex = 0;
          367  +    rc = sqlite3_finalize(pIter->pIdxIter);
          368  +    pIter->pIdxIter = 0;
          369  +  }
          370  +
          371  +  if( rc==SQLITE_ROW ) rc = SQLITE_OK;
          372  +  return rc;
          373  +}
          374  +
          375  +static int idxIterFirst(sqlite3 *db, const char *zTable, OtaIdxIter *pIter){
          376  +  int rc;                         /* return code */
          377  +  memset(pIter, 0, sizeof(OtaIdxIter));
          378  +  rc = sqlite3_prepare_v2(db, 
          379  +      "SELECT name FROM sqlite_master "
          380  +      "WHERE type='index' AND tbl_name = ?", -1, &pIter->pIdxIter, 0
          381  +  );
          382  +  if( rc==SQLITE_OK ){
          383  +    rc = sqlite3_bind_text(pIter->pIdxIter, 1, zTable, -1, SQLITE_TRANSIENT);
          384  +  }
          385  +  if( rc==SQLITE_OK ){
          386  +    rc = idxIterNext(pIter);
          387  +  }
          388  +  return rc;
          389  +}
          390  +
          391  +static void idxIterFinalize(OtaIdxIter *pIter){
          392  +  idxIterFreeAll(pIter);
          393  +  sqlite3_finalize(pIter->pIdxIter);
          394  +  memset(pIter, 0, sizeof(OtaIdxIter));
          395  +}
          396  +
          397  +/*
          398  +** Call sqlite3_reset() on the SQL statement passed as the second argument.
          399  +** If it returns anything other than SQLITE_OK, store the error code and
          400  +** error message in the OTA handle.
          401  +*/
          402  +static void otaResetStatement(sqlite3ota *p, sqlite3_stmt *pStmt){
          403  +  assert( p->rc==SQLITE_OK );
          404  +  assert( p->zErrmsg==0 );
          405  +  p->rc = sqlite3_reset(pStmt);
          406  +  if( p->rc!=SQLITE_OK ){
          407  +    sqlite3 *db = sqlite3_db_handle(pStmt);
          408  +    p->zErrmsg = sqlite3_mprintf("%s", sqlite3_errmsg(db));
          409  +  }
          410  +}
          411  +
          412  +/* 
          413  +** Check that all SQL statements required to process the current 
          414  +** table and index have been prepared. If not, prepare them. If
          415  +** an error occurs, store the error code and message in the OTA
          416  +** handle before returning.
          417  +*/
          418  +static int otaPrepareAll(sqlite3ota *p){
          419  +  assert( p->rc==SQLITE_OK );
          420  +  assert( p->zErrmsg==0 );
          421  +  assert( p->tbliter.zTarget );
          422  +
          423  +  if( p->tbliter.pSelect==0 ){
          424  +    p->rc = tblIterPrepareAll(p);
          425  +  }
          426  +  if( p->rc==SQLITE_OK && p->idxiter.zIndex && 0==p->idxiter.pSelect ){
          427  +    p->rc = idxIterPrepareAll(p);
          428  +  }
          429  +  return p->rc;
          430  +}
          431  +
          432  +int sqlite3ota_step(sqlite3ota *p){
          433  +  if( p ){
          434  +    while( p && p->rc==SQLITE_OK && p->tbliter.zTarget ){
          435  +      sqlite3_stmt *pSelect;
          436  +      int i;
          437  +
          438  +      otaPrepareAll(p);
          439  +      pSelect = (p->idxiter.zIndex ? p->idxiter.pSelect : p->tbliter.pSelect);
          440  +
          441  +      /* Advance to the next input row. */
          442  +      if( p->rc==SQLITE_OK ){
          443  +        int rc = sqlite3_step(pSelect);
          444  +        if( rc!=SQLITE_ROW ){
          445  +          otaResetStatement(p, pSelect);
          446  +
          447  +          /* Go to the next index. */
          448  +          if( p->rc==SQLITE_OK ){
          449  +            if( p->idxiter.zIndex ){
          450  +              p->rc = idxIterNext(&p->idxiter);
          451  +            }else{
          452  +              p->rc = idxIterFirst(p->dbDest, p->tbliter.zTarget, &p->idxiter);
          453  +            }
          454  +          }
          455  +
          456  +          /* If there is no next index, go to the next table. */
          457  +          if( p->rc==SQLITE_OK && p->idxiter.zIndex==0 ){
          458  +            p->rc = tblIterNext(&p->tbliter);
          459  +          }
          460  +          continue;
          461  +        }
          462  +      }
          463  +
          464  +      /* Update the target database PK table according to the row that 
          465  +      ** tbliter.pSelect currently points to. 
          466  +      **
          467  +      ** todo: For now, we assume all rows are INSERT commands - this will 
          468  +      ** change.  */
          469  +      if( p->rc==SQLITE_OK ){
          470  +        sqlite3_stmt *pInsert;
          471  +        int nCol;
          472  +        if( p->idxiter.zIndex ){
          473  +          pInsert = p->idxiter.pWriter;
          474  +          nCol = p->idxiter.nCol;
          475  +        }else{
          476  +          pInsert = p->tbliter.pInsert;
          477  +          nCol = p->tbliter.nCol;
          478  +        }
          479  +
          480  +        for(i=0; i<nCol; i++){
          481  +          sqlite3_value *pVal = sqlite3_column_value(pSelect, i+1);
          482  +          sqlite3_bind_value(pInsert, i+1, pVal);
          483  +        }
          484  +
          485  +        sqlite3_step(pInsert);
          486  +        otaResetStatement(p, pInsert);
          487  +      }
          488  +      
          489  +      break;
          490  +    }
          491  +
          492  +    if( p->rc==SQLITE_OK && p->tbliter.zTarget==0 ) p->rc = SQLITE_DONE;
          493  +  }
          494  +
          495  +  return (p ? p->rc : SQLITE_NOMEM);
          496  +}
          497  +
          498  +static void otaOpenDatabase(sqlite3ota *p, sqlite3 **pDb, const char *zFile){
          499  +  if( p->rc==SQLITE_OK ){
          500  +    p->rc = sqlite3_open(zFile, pDb);
          501  +    if( p->rc ){
          502  +      const char *zErr = sqlite3_errmsg(*pDb);
          503  +      p->zErrmsg = sqlite3_mprintf("sqlite3_open(): %s", zErr);
          504  +    }
          505  +  }
          506  +}
          507  +
          508  +static void otaSaveTransactionState(sqlite3ota *p){
          509  +  sqlite3_stmt *pStmt = 0;
          510  +  void *pWalState = 0;
          511  +  int nWalState = 0;
          512  +  int rc;
          513  +
          514  +  const char *zInsert = 
          515  +    "INSERT INTO ota_state(wal_state, tbl, idx, row, progress)"
          516  +    "VALUES(:wal_state, :tbl, :idx, :row, :progress)";
          517  +
          518  +  rc = sqlite3_transaction_save(p->dbDest, &pWalState, &nWalState);
          519  +  if( rc==SQLITE_OK ){
          520  +    rc = sqlite3_exec(p->dbOta, "DELETE FROM ota_state", 0, 0, 0);
          521  +  }
          522  +  if( rc==SQLITE_OK ){
          523  +    rc = prepareAndCollectError(p->dbOta, zInsert, &pStmt, &p->zErrmsg);
          524  +  }
          525  +  if( rc==SQLITE_OK ){
          526  +    sqlite3_stmt *pSelect;
          527  +    pSelect = (p->idxiter.zIndex ? p->idxiter.pSelect : p->tbliter.pSelect);
          528  +    sqlite3_bind_blob(pStmt, 1, pWalState, nWalState, SQLITE_STATIC);
          529  +    sqlite3_bind_text(pStmt, 2, p->tbliter.zTarget, -1, SQLITE_STATIC);
          530  +    if( p->idxiter.zIndex ){
          531  +      sqlite3_bind_text(pStmt, 3, p->idxiter.zIndex, -1, SQLITE_STATIC);
          532  +    }
          533  +    sqlite3_bind_int64(pStmt, 4, sqlite3_column_int64(pSelect, 0));
          534  +    sqlite3_step(pStmt);
          535  +    rc = sqlite3_finalize(pStmt);
          536  +    if( rc==SQLITE_OK ){
          537  +      rc = sqlite3_exec(p->dbOta, "COMMIT", 0, 0, 0);
          538  +    }
          539  +    if( rc!=SQLITE_OK ){
          540  +      p->zErrmsg = sqlite3_mprintf("%s", sqlite3_errmsg(p->dbOta));
          541  +    }
          542  +  }
          543  +  sqlite3_free(pWalState);
          544  +  assert( p->rc==SQLITE_OK );
          545  +  p->rc = rc;
          546  +}
          547  +
          548  +static void otaLoadTransactionState(sqlite3ota *p){
          549  +  sqlite3_stmt *pStmt = 0;
          550  +  int rc;
          551  +
          552  +  const char *zSelect = 
          553  +    "SELECT wal_state, tbl, idx, row, progress FROM ota_state";
          554  +
          555  +  rc = prepareAndCollectError(p->dbOta, zSelect, &pStmt, &p->zErrmsg);
          556  +  if( rc==SQLITE_OK ){
          557  +    if( SQLITE_ROW==sqlite3_step(pStmt) ){
          558  +      const void *pWalState = 0;
          559  +      int nWalState = 0;
          560  +      const char *zTbl;
          561  +      const char *zIdx;
          562  +      sqlite3_int64 iRowid;
          563  +
          564  +      pWalState = sqlite3_column_blob(pStmt, 0);
          565  +      nWalState = sqlite3_column_bytes(pStmt, 0);
          566  +      zTbl = (const char*)sqlite3_column_text(pStmt, 1);
          567  +      zIdx = (const char*)sqlite3_column_text(pStmt, 2);
          568  +      iRowid = sqlite3_column_int64(pStmt, 3);
          569  +      rc = sqlite3_transaction_restore(p->dbDest, pWalState, nWalState);
          570  +
          571  +      while( rc==SQLITE_OK 
          572  +          && p->tbliter.zTarget 
          573  +          && sqlite3_stricmp(p->tbliter.zTarget, zTbl) 
          574  +      ){
          575  +        rc = tblIterNext(&p->tbliter);
          576  +      }
          577  +      if( rc==SQLITE_OK && !p->tbliter.zTarget ){
          578  +        rc = SQLITE_ERROR;
          579  +        p->zErrmsg = sqlite3_mprintf("ota_state mismatch error");
          580  +      }
          581  +
          582  +      if( rc==SQLITE_OK && zIdx ){
          583  +        rc = idxIterFirst(p->dbDest, p->tbliter.zTarget, &p->idxiter);
          584  +        while( rc==SQLITE_OK 
          585  +            && p->idxiter.zIndex 
          586  +            && sqlite3_stricmp(p->idxiter.zIndex, zIdx) 
          587  +        ){
          588  +          rc = idxIterNext(&p->idxiter);
          589  +        }
          590  +        if( rc==SQLITE_OK && !p->idxiter.zIndex ){
          591  +          rc = SQLITE_ERROR;
          592  +          p->zErrmsg = sqlite3_mprintf("ota_state mismatch error");
          593  +        }
          594  +      }
          595  +
          596  +      if( rc==SQLITE_OK ){
          597  +        rc = otaPrepareAll(p);
          598  +      }
          599  +
          600  +      if( rc==SQLITE_OK ){
          601  +        sqlite3_stmt *pSelect;
          602  +        pSelect = (p->idxiter.zIndex ? p->idxiter.pSelect : p->tbliter.pSelect);
          603  +        while( sqlite3_column_int64(pSelect, 0)!=iRowid ){
          604  +          rc = sqlite3_step(pSelect);
          605  +          if( rc!=SQLITE_ROW ) break;
          606  +        }
          607  +        if( rc==SQLITE_ROW ){
          608  +          rc = SQLITE_OK;
          609  +        }else{
          610  +          rc = SQLITE_ERROR;
          611  +          p->zErrmsg = sqlite3_mprintf("ota_state mismatch error");
          612  +        }
          613  +      }
          614  +    }
          615  +    if( rc==SQLITE_OK ){
          616  +      rc = sqlite3_finalize(pStmt);
          617  +    }else{
          618  +      sqlite3_finalize(pStmt);
          619  +    }
          620  +  }
          621  +  p->rc = rc;
          622  +}
          623  +
          624  +
          625  +/*
          626  +** Open and return a new OTA handle. 
          627  +*/
          628  +sqlite3ota *sqlite3ota_open(const char *zTarget, const char *zOta){
          629  +  sqlite3ota *p;
          630  +
          631  +  p = (sqlite3ota*)sqlite3_malloc(sizeof(sqlite3ota));
          632  +  if( p ){
          633  +
          634  +    /* Open the target database */
          635  +    memset(p, 0, sizeof(sqlite3ota));
          636  +    otaOpenDatabase(p, &p->dbDest, zTarget);
          637  +    otaOpenDatabase(p, &p->dbOta, zOta);
          638  +
          639  +    /* If it has not already been created, create the ota_state table */
          640  +    if( p->rc==SQLITE_OK ){
          641  +      p->rc = sqlite3_exec(p->dbOta, OTA_CREATE_STATE, 0, 0, &p->zErrmsg);
          642  +    }
          643  +
          644  +    if( p->rc==SQLITE_OK ){
          645  +      const char *zScript = 
          646  +        "PRAGMA ota_mode=1;"
          647  +        "PRAGMA journal_mode=wal;"
          648  +        "BEGIN IMMEDIATE;"
          649  +      ;
          650  +      p->rc = sqlite3_exec(p->dbDest, zScript, 0, 0, &p->zErrmsg);
          651  +    }
          652  +
          653  +    if( p->rc==SQLITE_OK ){
          654  +      const char *zScript = "BEGIN IMMEDIATE";
          655  +      p->rc = sqlite3_exec(p->dbOta, zScript, 0, 0, &p->zErrmsg);
          656  +    }
          657  +
          658  +    /* Point the table iterator at the first table */
          659  +    if( p->rc==SQLITE_OK ){
          660  +      p->rc = tblIterFirst(p->dbOta, &p->tbliter);
          661  +    }
          662  +
          663  +    if( p->rc==SQLITE_OK ){
          664  +      otaLoadTransactionState(p);
          665  +    }
          666  +  }
          667  +
          668  +  return p;
          669  +}
          670  +
          671  +static void otaCloseHandle(sqlite3 *db){
          672  +  int rc = sqlite3_close(db);
          673  +  assert( rc==SQLITE_OK );
          674  +}
          675  +
          676  +int sqlite3ota_close(sqlite3ota *p, char **pzErrmsg){
          677  +  int rc;
          678  +  if( p ){
          679  +
          680  +    /* If the update has not been fully applied, save the state in 
          681  +    ** the ota db. If successful, this call also commits the open 
          682  +    ** transaction on the ota db. */
          683  +    assert( p->rc!=SQLITE_ROW );
          684  +    if( p->rc==SQLITE_OK ){
          685  +      assert( p->zErrmsg==0 );
          686  +      otaSaveTransactionState(p);
          687  +    }
          688  +
          689  +    /* Close all open statement handles. */
          690  +    tblIterFinalize(&p->tbliter);
          691  +    idxIterFinalize(&p->idxiter);
          692  +
          693  +    /* If the ota update has been fully applied, commit the transaction
          694  +    ** on the target database. */
          695  +    if( p->rc==SQLITE_DONE ){
          696  +      rc = sqlite3_exec(p->dbDest, "COMMIT", 0, 0, &p->zErrmsg);
          697  +      if( rc!=SQLITE_OK ) p->rc = rc;
          698  +    }
          699  +
          700  +    rc = p->rc;
          701  +    *pzErrmsg = p->zErrmsg;
          702  +    otaCloseHandle(p->dbDest);
          703  +    otaCloseHandle(p->dbOta);
          704  +    sqlite3_free(p);
          705  +  }else{
          706  +    rc = SQLITE_NOMEM;
          707  +    *pzErrmsg = 0;
          708  +  }
          709  +  return rc;
          710  +}
          711  +
          712  +
          713  +/**************************************************************************/
          714  +
          715  +#ifdef SQLITE_TEST 
          716  +
          717  +#include <tcl.h>
          718  +
          719  +/* From main.c (apparently...) */
          720  +extern const char *sqlite3ErrName(int);
          721  +
          722  +static int test_sqlite3ota_cmd(
          723  +  ClientData clientData,
          724  +  Tcl_Interp *interp,
          725  +  int objc,
          726  +  Tcl_Obj *CONST objv[]
          727  +){
          728  +  int ret = TCL_OK;
          729  +  sqlite3ota *pOta = (sqlite3ota*)clientData;
          730  +  const char *azMethod[] = { "step", "close", 0 };
          731  +  int iMethod;
          732  +
          733  +  if( objc!=2 ){
          734  +    Tcl_WrongNumArgs(interp, 1, objv, "METHOD");
          735  +    return TCL_ERROR;
          736  +  }
          737  +  if( Tcl_GetIndexFromObj(interp, objv[1], azMethod, "method", 0, &iMethod) ){
          738  +    return TCL_ERROR;
          739  +  }
          740  +
          741  +  switch( iMethod ){
          742  +    case 0: /* step */ {
          743  +      int rc = sqlite3ota_step(pOta);
          744  +      Tcl_SetObjResult(interp, Tcl_NewStringObj(sqlite3ErrName(rc), -1));
          745  +      break;
          746  +    }
          747  +
          748  +    case 1: /* close */ {
          749  +      char *zErrmsg = 0;
          750  +      int rc;
          751  +      Tcl_DeleteCommand(interp, Tcl_GetString(objv[0]));
          752  +      rc = sqlite3ota_close(pOta, &zErrmsg);
          753  +      if( rc==SQLITE_OK || rc==SQLITE_DONE ){
          754  +        Tcl_SetObjResult(interp, Tcl_NewStringObj(sqlite3ErrName(rc), -1));
          755  +        assert( zErrmsg==0 );
          756  +      }else{
          757  +        Tcl_SetObjResult(interp, Tcl_NewStringObj(sqlite3ErrName(rc), -1));
          758  +        if( zErrmsg ){
          759  +          Tcl_AppendResult(interp, " - ", zErrmsg, 0);
          760  +          sqlite3_free(zErrmsg);
          761  +        }
          762  +        ret = TCL_ERROR;
          763  +      }
          764  +      break;
          765  +    }
          766  +
          767  +    default: /* seems unlikely */
          768  +      assert( !"cannot happen" );
          769  +      break;
          770  +  }
          771  +
          772  +  return ret;
          773  +}
          774  +
          775  +/*
          776  +** Tclcmd: sqlite3ota CMD <target-db> <ota-db>
          777  +*/
          778  +static int test_sqlite3ota(
          779  +  ClientData clientData,
          780  +  Tcl_Interp *interp,
          781  +  int objc,
          782  +  Tcl_Obj *CONST objv[]
          783  +){
          784  +  sqlite3ota *pOta = 0;
          785  +  const char *zCmd;
          786  +  const char *zTarget;
          787  +  const char *zOta;
          788  +
          789  +  if( objc!=4 ){
          790  +    Tcl_WrongNumArgs(interp, 1, objv, "NAME TARGET-DB OTA-DB");
          791  +    return TCL_ERROR;
          792  +  }
          793  +  zCmd = Tcl_GetString(objv[1]);
          794  +  zTarget = Tcl_GetString(objv[2]);
          795  +  zOta = Tcl_GetString(objv[3]);
          796  +
          797  +  pOta = sqlite3ota_open(zTarget, zOta);
          798  +  Tcl_CreateObjCommand(interp, zCmd, test_sqlite3ota_cmd, (ClientData)pOta, 0);
          799  +  Tcl_SetObjResult(interp, objv[1]);
          800  +  return TCL_OK;
          801  +}
          802  +
          803  +int SqliteOta_Init(Tcl_Interp *interp){ 
          804  +  Tcl_CreateObjCommand(interp, "sqlite3ota", test_sqlite3ota, 0, 0);
          805  +  return TCL_OK;
          806  +}
          807  +
          808  +#endif                  /* ifdef SQLITE_TEST */
          809  +
          810  +
          811  +

Added ext/ota/sqlite3ota.h. 

            1  +/*
            2  +** 2014 August 30
            3  +**
            4  +** The author disclaims copyright to this source code.  In place of
            5  +** a legal notice, here is a blessing:
            6  +**
            7  +**    May you do good and not evil.
            8  +**    May you find forgiveness for yourself and forgive others.
            9  +**    May you share freely, never taking more than you give.
           10  +**
           11  +*************************************************************************
           12  +**
           13  +** This file contains the public interface for the OTA extension. 
           14  +*/
           15  +
           16  +/*
           17  +** SUMMARY
           18  +**
           19  +** Writing a transaction containing a large number of operations on 
           20  +** b-tree indexes that are collectively larger than the available cache
           21  +** memory can be very inefficient. 
           22  +**
           23  +** The problem is that in order to update a b-tree, the leaf page (at least)
           24  +** containing the entry being inserted or deleted must be modified. If the
           25  +** working set of leaves is larger than the available cache memory, then a 
           26  +** single leaf that is modified more than once as part of the transaction 
           27  +** may be loaded from or written to the persistent media more than once. 
           28  +** Additionally, because the index updates are likely to be applied in
           29  +** random order, access to pages within the databse is also likely to be in 
           30  +** random order, which is itself quite inefficient.
           31  +**
           32  +** One way to improve the situation is to sort the operations on each index
           33  +** by index key before applying them to the b-tree. This leads to an IO
           34  +** pattern that resembles a single linear scan through the index b-tree,
           35  +** and all but guarantees each modified leaf page is loaded and stored 
           36  +** exactly once. SQLite uses this trick to improve the performance of
           37  +** CREATE INDEX commands. This extension allows it to be used to improve
           38  +** the performance of large transactions on existing databases.
           39  +**
           40  +** Additionally, this extension allows the work involved in writing the 
           41  +** large transaction to be broken down into sub-transactions performed 
           42  +** sequentially by separate processes. This is useful if the system cannot 
           43  +** guarantee that a single update process may run for long enough to apply 
           44  +** the entire update, for example because the update is running on a mobile
           45  +** device that is frequently rebooted. Even after the writer process has 
           46  +** committed one or more sub-transactions, other database clients continue
           47  +** to read from the original database snapshot. In other words, partially 
           48  +** applied transactions are not visible to other clients. 
           49  +**
           50  +** "OTA" stands for "Over The Air" update. As in a large database update
           51  +** transmitted via a wireless network to a mobile device. A transaction
           52  +** applied using this extension is hence refered to as an "OTA update".
           53  +**
           54  +**
           55  +** LIMITATIONS
           56  +**
           57  +** An "OTA update" transaction is subject to the following limitations:
           58  +**
           59  +**   * The transaction must consist of INSERT, UPDATE and DELETE operations
           60  +**     only.
           61  +**
           62  +**   * INSERT statements may not use any default values.
           63  +**
           64  +**   * UPDATE and DELETE statements must identify their target rows by
           65  +**     real PRIMARY KEY values - i.e. INTEGER PRIMARY KEY columns or 
           66  +**     by the PRIMARY KEY columns of WITHOUT ROWID tables.
           67  +**
           68  +**   * UPDATE statements may not modify real PRIMARY KEY columns.
           69  +**
           70  +**   * No triggers will be fired.
           71  +**
           72  +**   * No foreign key violations are detected or reported.
           73  +**
           74  +**   * No constraint handling mode except for "OR ROLLBACK" is supported.
           75  +**
           76  +**
           77  +** PREPARATION
           78  +**
           79  +** An "OTA update" is stored as a separate SQLite database. A database
           80  +** containing an OTA update is an "OTA database". For each table in the 
           81  +** target database to be updated, the OTA database should contain a table
           82  +** named "data_<target name>" containing the same set of columns as the
           83  +** target table, and one more - "ota_control". The data_% table should 
           84  +** have no PRIMARY KEY or UNIQUE constraints, but each column should have
           85  +** the same type as the corresponding column in the target database.
           86  +** The "ota_control" column should have no type at all. For example, if
           87  +** the target database contains:
           88  +**
           89  +**   CREATE TABLE t1(a INTEGER PRIMARY KEY, b TEXT, c UNIQUE);
           90  +**
           91  +** Then the OTA database should contain:
           92  +**
           93  +**   CREATE TABLE data_t1(a INTEGER, b TEXT, c, ota_control);
           94  +**
           95  +** The order of the columns in the data_% table does not matter.
           96  +**
           97  +** For each row to INSERT into the target database as part of the OTA 
           98  +** update, the corresponding data_% table should contain a single record
           99  +** with the "ota_control" column set to contain integer value 0. The
          100  +** other columns should be set to the values that make up the new record 
          101  +** to insert.
          102  +**
          103  +** For each row to DELETE from the target database as part of the OTA 
          104  +** update, the corresponding data_% table should contain a single record
          105  +** with the "ota_control" column set to contain integer value 1. The
          106  +** real primary key values of the row to delete should be stored in the
          107  +** corresponding columns of the data_% table. The values stored in the
          108  +** other columns are not used.
          109  +**
          110  +** For each row to DELETE from the target database as part of the OTA 
          111  +** update, the corresponding data_% table should contain a single record
          112  +** with the "ota_control" column set to contain a value of type text.
          113  +** The real primary key values identifying the row to update should be 
          114  +** stored in the corresponding columns of the data_% table row, as should
          115  +** the new values of all columns being update. The text value in the 
          116  +** "ota_control" column must contain the same number of characters as
          117  +** there are column in the target database table, and must consist entirely
          118  +** of "x" and "." characters. For each column that is being updated,
          119  +** the corresponding character is set to "x". For those that remain as
          120  +** they are, the corresponding character of the ota_control value should
          121  +** be set to ".". For example, given the tables above, the update 
          122  +** statement:
          123  +**
          124  +**   UPDATE t1 SET c = 'usa' WHERE a = 4;
          125  +**
          126  +** is represented by the data_t1 row created by:
          127  +**
          128  +**   INSERT INTO data_t1(a, b, c, ota_control) VALUES(4, NULL, 'usa', '..x');
          129  +**
          130  +**
          131  +** USAGE
          132  +**
          133  +** The API declared below allows an application to apply an OTA update 
          134  +** stored on disk to an existing target database. Essentially, the 
          135  +** application:
          136  +**
          137  +**     1) Opens an OTA handle using the sqlite3ota_open() function.
          138  +**
          139  +**     2) Calls the sqlite3ota_step() function one or more times on
          140  +**        the new handle. Each call to sqlite3ota_step() performs a single
          141  +**        b-tree operation, so thousands of calls may be required to apply 
          142  +**        a complete update.
          143  +**
          144  +**     3) Calls sqlite3ota_close() to close the OTA update handle. If
          145  +**        sqlite3ota_step() has been called enough times to completely
          146  +**        apply the update to the target database, then it is committed
          147  +**        and made visible to other database clients at this point. 
          148  +**        Otherwise, the state of the OTA update application is saved
          149  +**        in the OTA database for later resumption.
          150  +**
          151  +** See comments below for more detail on APIs.
          152  +**
          153  +** If an update is only partially applied to the target database by the
          154  +** time sqlite3ota_close() is called, various state information is saved 
          155  +** within the OTA database. This allows subsequent processes to automatically
          156  +** resume the OTA update from where it left off.
          157  +**
          158  +** To remove all OTA extension state information, returning an OTA database 
          159  +** to its original contents, it is sufficient to drop all tables that begin
          160  +** with the prefix "ota_"
          161  +*/
          162  +
          163  +#ifndef _SQLITE3OTA_H
          164  +#define _SQLITE3OTA_H
          165  +
          166  +typedef struct sqlite3ota sqlite3ota;
          167  +
          168  +/*
          169  +** Open an OTA handle.
          170  +**
          171  +** Argument zTarget is the path to the target database. Argument zOta is
          172  +** the path to the OTA database. Each call to this function must be matched
          173  +** by a call to sqlite3ota_close().
          174  +*/
          175  +sqlite3ota *sqlite3ota_open(const char *zTarget, const char *zOta);
          176  +
          177  +/*
          178  +** Do some work towards applying the OTA update to the target db. 
          179  +**
          180  +** Return SQLITE_DONE if the update has been completely applied, or 
          181  +** SQLITE_OK if no error occurs but there remains work to do to apply
          182  +** the OTA update. If an error does occur, some other error code is 
          183  +** returned. 
          184  +**
          185  +** Once a call to sqlite3ota_step() has returned a value other than
          186  +** SQLITE_OK, all subsequent calls on the same OTA handle are no-ops
          187  +** that immediately return the same value.
          188  +*/
          189  +int sqlite3ota_step(sqlite3ota *pOta);
          190  +
          191  +/*
          192  +** Close an OTA handle. 
          193  +**
          194  +** If the OTA update has been completely applied, commit it to the target 
          195  +** database. Otherwise, assuming no error has occurred, save the current 
          196  +** state of the OTA update appliation to the OTA database.
          197  +**
          198  +** If an error has already occurred as part of an sqlite3ota_step()
          199  +** or sqlite3ota_open() call, or if one occurs within this function, an
          200  +** SQLite error code is returned. Additionally, *pzErrmsg may be set to
          201  +** point to a buffer containing a utf-8 formatted English language error
          202  +** message. It is the responsibility of the caller to eventually free any 
          203  +** such buffer using sqlite3_free().
          204  +**
          205  +** Otherwise, if no error occurs, this function returns SQLITE_OK if the
          206  +** update has been partially applied, or SQLITE_DONE if it has been 
          207  +** completely applied.
          208  +*/
          209  +int sqlite3ota_close(sqlite3ota *pOta, char **pzErrmsg);
          210  +
          211  +#endif /* _SQLITE3OTA_H */
          212  +

Changes to main.mk. 

   327    327     $(TOP)/src/where.c \
   328    328     parse.c \
   329    329     $(TOP)/ext/fts3/fts3.c \
   330    330     $(TOP)/ext/fts3/fts3_aux.c \
   331    331     $(TOP)/ext/fts3/fts3_expr.c \
   332    332     $(TOP)/ext/fts3/fts3_tokenizer.c \
   333    333     $(TOP)/ext/fts3/fts3_write.c \
   334         -  $(TOP)/ext/async/sqlite3async.c
          334  +  $(TOP)/ext/async/sqlite3async.c \
          335  +  $(TOP)/ext/ota/sqlite3ota.c
   335    336   
   336    337   # Header files used by all library source files.
   337    338   #
   338    339   HDR = \
   339    340      $(TOP)/src/btree.h \
   340    341      $(TOP)/src/btreeInt.h \
   341    342      $(TOP)/src/hash.h \

Changes to src/btree.c. 

   147    147     Pgno iTab = 0;
   148    148     BtLock *pLock;
   149    149   
   150    150     /* If this database is not shareable, or if the client is reading
   151    151     ** and has the read-uncommitted flag set, then no lock is required. 
   152    152     ** Return true immediately.
   153    153     */
   154         -  if( (pBtree->sharable==0)
          154  +  if( (pBtree->db->flags & SQLITE_OtaMode)
          155  +   || (pBtree->sharable==0)
   155    156      || (eLockType==READ_LOCK && (pBtree->db->flags & SQLITE_ReadUncommitted))
   156    157     ){
   157    158       return 1;
   158    159     }
   159    160   
   160    161     /* If the client is reading  or writing an index and the schema is
   161    162     ** not loaded, then it is too difficult to actually check to see if
................................................................................
  2041   2042     }
  2042   2043   #endif
  2043   2044     *ppBtree = p;
  2044   2045   
  2045   2046   btree_open_out:
  2046   2047     if( rc!=SQLITE_OK ){
  2047   2048       if( pBt && pBt->pPager ){
  2048         -      sqlite3PagerClose(pBt->pPager);
         2049  +      sqlite3PagerClose(pBt->pPager, 0);
  2049   2050       }
  2050   2051       sqlite3_free(pBt);
  2051   2052       sqlite3_free(p);
  2052   2053       *ppBtree = 0;
  2053   2054     }else{
  2054   2055       /* If the B-Tree was successfully opened, set the pager-cache size to the
  2055   2056       ** default value. Except, when opening on an existing shared pager-cache,
................................................................................
  2170   2171     if( !p->sharable || removeFromSharingList(pBt) ){
  2171   2172       /* The pBt is no longer on the sharing list, so we can access
  2172   2173       ** it without having to hold the mutex.
  2173   2174       **
  2174   2175       ** Clean out and delete the BtShared object.
  2175   2176       */
  2176   2177       assert( !pBt->pCursor );
  2177         -    sqlite3PagerClose(pBt->pPager);
         2178  +    sqlite3PagerClose(pBt->pPager, (p->db->flags & SQLITE_OtaMode)!=0);
  2178   2179       if( pBt->xFreeSchema && pBt->pSchema ){
  2179   2180         pBt->xFreeSchema(pBt->pSchema);
  2180   2181       }
  2181   2182       sqlite3DbFree(0, pBt->pSchema);
  2182   2183       freeTempSpace(pBt);
  2183   2184       sqlite3_free(pBt);
  2184   2185     }

Changes to src/insert.c. 

  1360   1360     ** WITHOUT ROWID table.
  1361   1361     */
  1362   1362     for(ix=0, pIdx=pTab->pIndex; pIdx; pIdx=pIdx->pNext, ix++){
  1363   1363       int regIdx;          /* Range of registers hold conent for pIdx */
  1364   1364       int regR;            /* Range of registers holding conflicting PK */
  1365   1365       int iThisCur;        /* Cursor for this UNIQUE index */
  1366   1366       int addrUniqueOk;    /* Jump here if the UNIQUE constraint is satisfied */
         1367  +
         1368  +    /* If the "ota_mode" flag is set, ignore all indexes except the PK 
         1369  +    ** index of WITHOUT ROWID tables.  */
         1370  +    if( (db->flags & SQLITE_OtaMode) && pIdx!=pPk) continue;
  1367   1371   
  1368   1372       if( aRegIdx[ix]==0 ) continue;  /* Skip indices that do not change */
  1369   1373       if( bAffinityDone==0 ){
  1370   1374         sqlite3TableAffinity(v, pTab, regNewData+1);
  1371   1375         bAffinityDone = 1;
  1372   1376       }
  1373   1377       iThisCur = iIdxCur+ix;
................................................................................
  1552   1556     u8 bAffinityDone = 0; /* True if OP_Affinity has been run already */
  1553   1557   
  1554   1558     v = sqlite3GetVdbe(pParse);
  1555   1559     assert( v!=0 );
  1556   1560     assert( pTab->pSelect==0 );  /* This table is not a VIEW */
  1557   1561     for(i=0, pIdx=pTab->pIndex; pIdx; pIdx=pIdx->pNext, i++){
  1558   1562       if( aRegIdx[i]==0 ) continue;
         1563  +
         1564  +    /* If the "ota_mode" flag is set, ignore all indexes except the PK 
         1565  +    ** index of WITHOUT ROWID tables.  */
         1566  +    if( (pParse->db->flags & SQLITE_OtaMode) 
         1567  +     && (pTab->iPKey>=0 || pIdx->idxType!=SQLITE_IDXTYPE_PRIMARYKEY) 
         1568  +    ){
         1569  +      continue;
         1570  +    }
         1571  +
  1559   1572       bAffinityDone = 1;
  1560   1573       if( pIdx->pPartIdxWhere ){
  1561   1574         sqlite3VdbeAddOp2(v, OP_IsNull, aRegIdx[i], sqlite3VdbeCurrentAddr(v)+2);
  1562   1575         VdbeCoverage(v);
  1563   1576       }
  1564   1577       sqlite3VdbeAddOp2(v, OP_IdxInsert, iIdxCur+i, aRegIdx[i]);
  1565   1578       pik_flags = 0;

Changes to src/main.c. 

  3466   3466   ** Return 1 if database is read-only or 0 if read/write.  Return -1 if
  3467   3467   ** no such database exists.
  3468   3468   */
  3469   3469   int sqlite3_db_readonly(sqlite3 *db, const char *zDbName){
  3470   3470     Btree *pBt = sqlite3DbNameToBtree(db, zDbName);
  3471   3471     return pBt ? sqlite3BtreeIsReadonly(pBt) : -1;
  3472   3472   }
         3473  +
         3474  +int sqlite3_transaction_save(sqlite3 *db, void **ppState, int *pnState){
         3475  +  Pager *pPager = sqlite3BtreePager(db->aDb[0].pBt);
         3476  +  return sqlite3PagerSaveState(pPager, ppState, pnState);
         3477  +}
         3478  +
         3479  +int sqlite3_transaction_restore(sqlite3 *db, const void *pState, int nState){
         3480  +  Pager *pPager = sqlite3BtreePager(db->aDb[0].pBt);
         3481  +  return sqlite3PagerRestoreState(pPager, pState, nState);
         3482  +}
         3483  +
         3484  +

Changes to src/pager.c. 

  3943   3943   ** result in a coredump.
  3944   3944   **
  3945   3945   ** This function always succeeds. If a transaction is active an attempt
  3946   3946   ** is made to roll it back. If an error occurs during the rollback 
  3947   3947   ** a hot journal may be left in the filesystem but no error is returned
  3948   3948   ** to the caller.
  3949   3949   */
  3950         -int sqlite3PagerClose(Pager *pPager){
         3950  +int sqlite3PagerClose(Pager *pPager, int bOtaMode){
  3951   3951     u8 *pTmp = (u8 *)pPager->pTmpSpace;
  3952   3952   
  3953   3953     assert( assert_pager_state(pPager) );
  3954   3954     disable_simulated_io_errors();
  3955   3955     sqlite3BeginBenignMalloc();
  3956   3956     pagerFreeMapHdrs(pPager);
  3957   3957     /* pPager->errCode = 0; */
  3958   3958     pPager->exclusiveMode = 0;
  3959   3959   #ifndef SQLITE_OMIT_WAL
  3960         -  sqlite3WalClose(pPager->pWal, pPager->ckptSyncFlags, pPager->pageSize, pTmp);
         3960  +  sqlite3WalClose(
         3961  +      pPager->pWal, pPager->ckptSyncFlags, pPager->pageSize, (bOtaMode?0:pTmp)
         3962  +  );
  3961   3963     pPager->pWal = 0;
  3962   3964   #endif
  3963   3965     pager_reset(pPager);
  3964   3966     if( MEMDB ){
  3965   3967       pager_unlock(pPager);
  3966   3968     }else{
  3967   3969       /* If it is open, sync the journal file before calling UnlockAndRollback.
................................................................................
  7210   7212       if( rc==SQLITE_OK ){
  7211   7213         rc = sqlite3WalClose(pPager->pWal, pPager->ckptSyncFlags,
  7212   7214                              pPager->pageSize, (u8*)pPager->pTmpSpace);
  7213   7215         pPager->pWal = 0;
  7214   7216         pagerFixMaplimit(pPager);
  7215   7217       }
  7216   7218     }
         7219  +  return rc;
         7220  +}
         7221  +
         7222  +int sqlite3PagerSaveState(Pager *pPager, void **ppState, int *pnState){
         7223  +  int rc = SQLITE_OK;
         7224  +  *ppState = 0;
         7225  +  *pnState = 0;
         7226  +  if( pPager->pWal==0 || pPager->eState<PAGER_WRITER_LOCKED ){
         7227  +    rc = SQLITE_ERROR;
         7228  +  }else{
         7229  +    /* Flush all dirty pages to the wal. */
         7230  +    PgHdr *pList = sqlite3PcacheDirtyList(pPager->pPCache);
         7231  +    rc = sqlite3WalFrames(pPager->pWal, 
         7232  +        pPager->pageSize, pList, 0, 0, pPager->walSyncFlags
         7233  +    );
         7234  +    if( rc==SQLITE_OK ){
         7235  +      rc = sqlite3WalSaveState(pPager->pWal, ppState, pnState);
         7236  +    }
         7237  +  }
         7238  +  return rc;
         7239  +}
         7240  +
         7241  +int sqlite3PagerRestoreState(Pager *pPager, const void *pState, int nState){
         7242  +  int rc = SQLITE_OK;
         7243  +  if( pPager->pWal==0 
         7244  +   || pPager->eState<PAGER_WRITER_LOCKED 
         7245  +   || sqlite3PcacheDirtyList(pPager->pPCache)
         7246  +  ){
         7247  +    rc = SQLITE_ERROR;
         7248  +  }else{
         7249  +    sqlite3PcacheTruncate(pPager->pPCache, 1);
         7250  +    rc = sqlite3WalRestoreState(pPager->pWal, pState, nState);
         7251  +    pPager->eState = PAGER_WRITER_CACHEMOD;
         7252  +  }
         7253  +
  7217   7254     return rc;
  7218   7255   }
  7219   7256   
  7220   7257   #endif /* !SQLITE_OMIT_WAL */
  7221   7258   
  7222   7259   #ifdef SQLITE_ENABLE_ZIPVFS
  7223   7260   /*

Changes to src/pager.h. 

   108    108     Pager **ppPager,
   109    109     const char*,
   110    110     int,
   111    111     int,
   112    112     int,
   113    113     void(*)(DbPage*)
   114    114   );
   115         -int sqlite3PagerClose(Pager *pPager);
          115  +int sqlite3PagerClose(Pager *pPager, int);
   116    116   int sqlite3PagerReadFileheader(Pager*, int, unsigned char*);
   117    117   
   118    118   /* Functions used to configure a Pager object. */
   119    119   void sqlite3PagerSetBusyhandler(Pager*, int(*)(void *), void *);
   120    120   int sqlite3PagerSetPagesize(Pager*, u32*, int);
   121    121   int sqlite3PagerMaxPageCount(Pager*, int);
   122    122   void sqlite3PagerSetCachesize(Pager*, int);
................................................................................
   203    203     void disable_simulated_io_errors(void);
   204    204     void enable_simulated_io_errors(void);
   205    205   #else
   206    206   # define disable_simulated_io_errors()
   207    207   # define enable_simulated_io_errors()
   208    208   #endif
   209    209   
          210  +int sqlite3PagerSaveState(Pager *pPager, void **ppState, int *pnState);
          211  +int sqlite3PagerRestoreState(Pager *pPager, const void *pState, int nState);
          212  +
   210    213   #endif /* _PAGER_H_ */

Changes to src/pragma.c. 

   304    304       /* ePragTyp:  */ PragTyp_PAGE_COUNT,
   305    305       /* ePragFlag: */ PragFlag_NeedSchema,
   306    306       /* iArg:      */ 0 },
   307    307     { /* zName:     */ "mmap_size",
   308    308       /* ePragTyp:  */ PragTyp_MMAP_SIZE,
   309    309       /* ePragFlag: */ 0,
   310    310       /* iArg:      */ 0 },
          311  +#endif
          312  +  { /* zName:     */ "ota_mode",
          313  +    /* ePragTyp:  */ PragTyp_FLAG,
          314  +    /* ePragFlag: */ 0,
          315  +    /* iArg:      */ SQLITE_OtaMode },
          316  +#if !defined(SQLITE_OMIT_PAGER_PRAGMAS)
   311    317     { /* zName:     */ "page_count",
   312    318       /* ePragTyp:  */ PragTyp_PAGE_COUNT,
   313    319       /* ePragFlag: */ PragFlag_NeedSchema,
   314    320       /* iArg:      */ 0 },
   315    321     { /* zName:     */ "page_size",
   316    322       /* ePragTyp:  */ PragTyp_PAGE_SIZE,
   317    323       /* ePragFlag: */ 0,
................................................................................
  1463   1469             sqlite3VdbeAddOp2(v, OP_Null, 0, 5);
  1464   1470           }
  1465   1471           if( (pCol->colFlags & COLFLAG_PRIMKEY)==0 ){
  1466   1472             k = 0;
  1467   1473           }else if( pPk==0 ){
  1468   1474             k = 1;
  1469   1475           }else{
  1470         -          for(k=1; ALWAYS(k<=pTab->nCol) && pPk->aiColumn[k-1]!=i; k++){}
         1476  +          if( (db->flags & SQLITE_OtaMode) && HasRowid(pTab) ){
         1477  +            k = 0;
         1478  +          }else{
         1479  +            for(k=1; ALWAYS(k<=pTab->nCol) && pPk->aiColumn[k-1]!=i; k++){}
         1480  +          }
  1471   1481           }
  1472   1482           sqlite3VdbeAddOp2(v, OP_Integer, k, 6);
  1473   1483           sqlite3VdbeAddOp2(v, OP_ResultRow, 1, 6);
  1474   1484         }
  1475   1485       }
  1476   1486     }
  1477   1487     break;

Changes to src/prepare.c. 

   791    791     const char **pzTail       /* OUT: End of parsed string */
   792    792   ){
   793    793     int rc;
   794    794     rc = sqlite3LockAndPrepare(db,zSql,nBytes,1,0,ppStmt,pzTail);
   795    795     assert( rc==SQLITE_OK || ppStmt==0 || *ppStmt==0 );  /* VERIFY: F13021 */
   796    796     return rc;
   797    797   }
   798         -
   799    798   
   800    799   #ifndef SQLITE_OMIT_UTF16
   801    800   /*
   802    801   ** Compile the UTF-16 encoded SQL statement zSql into a statement handle.
   803    802   */
   804    803   static int sqlite3Prepare16(
   805    804     sqlite3 *db,              /* Database handle. */

Changes to src/sqlite.h.in. 

  7359   7359   */
  7360   7360   #define SQLITE_ROLLBACK 1
  7361   7361   /* #define SQLITE_IGNORE 2 // Also used by sqlite3_authorizer() callback */
  7362   7362   #define SQLITE_FAIL     3
  7363   7363   /* #define SQLITE_ABORT 4  // Also an error code */
  7364   7364   #define SQLITE_REPLACE  5
  7365   7365   
         7366  +/*
         7367  +** Allocate a statement handle that may be used to write directly to an
         7368  +** index b-tree. This allows the user to create a corrupt database. Once
         7369  +** the statement handle is allocated, it may be used with the same APIs
         7370  +** as any statement handle created with sqlite3_prepare().
         7371  +**
         7372  +** The statement writes to the index specified by parameter zIndex, which
         7373  +** must be in the "main" database. If argument bDelete is false, then each
         7374  +** time the statement is sqlite3_step()ed, an entry is inserted into the
         7375  +** b-tree index. If it is true, then an entry may be deleted (or may not, if 
         7376  +** the specified key is not found) each time the statement is 
         7377  +** sqlite3_step()ed.
         7378  +**
         7379  +** If statement compilation is successful, *ppStmt is set to point to the 
         7380  +** new statement handle and SQLITE_OK is returned. Otherwise, if an error
         7381  +** occurs, *ppStmt is set to NULL and an error code returned. An error
         7382  +** message may be left in the database handle in this case.
         7383  +**
         7384  +** If statement compilation succeeds, output variable *pnCol is set to the
         7385  +** total number of columns in the index, including the primary key columns
         7386  +** at the end. Variable *paiCol is set to point to an array *pnCol entries 
         7387  +** in size. Each entry is the table column index, numbered from zero from left 
         7388  +** to right, of the corresponding index column. For example, if:
         7389  +**
         7390  +**       CREATE TABLE t1(a, b, c, d);
         7391  +**       CREATE INDEX i1 ON t1(b, c);
         7392  +**
         7393  +** then *pnCol is 3 and *paiCol points to an array containing {1, 2, -1}.
         7394  +** If table t1 had an explicit INTEGER PRIMARY KEY, then the "-1" in the
         7395  +** *paiCol array would be replaced by its column index. Or if:
         7396  +**
         7397  +**       CREATE TABLE t2(a, b, c, d, PRIMARY KEY(d, c)) WITHOUT ROWID;
         7398  +**       CREATE INDEX i2 ON t2(a);
         7399  +**
         7400  +** then (*pnCol) is 3 and *paiCol points to an array containing {0, 3, 2}.
         7401  +**
         7402  +** The lifetime of the array is the same as that of the statement handle -
         7403  +** it is automatically freed when the statement handle is passed to
         7404  +** sqlite3_finalize().
         7405  +**
         7406  +** The statement has (*pnCol) SQL variables that values may be bound to.
         7407  +** They correspond to the values used to create the index key that is
         7408  +** inserted or deleted when the statement is stepped.
         7409  +**
         7410  +** If the index is a UNIQUE index, the usual checking and error codes apply
         7411  +** to insert operations.
         7412  +*/
         7413  +int sqlite3_index_writer(
         7414  +  sqlite3 *db, 
         7415  +  int bDelete,                    /* Zero for insert, non-zero for delete */
         7416  +  const char *zIndex,             /* Index to write to */
         7417  +  sqlite3_stmt**,                 /* OUT: New statement handle */
         7418  +  int **paiCol, int *pnCol        /* OUT: See above */
         7419  +);
         7420  +
         7421  +/*
         7422  +** This function is used to save the state of an ongoing WAL mode write 
         7423  +** transaction on the "main" database of the supplied database handle.
         7424  +**
         7425  +** If successful, SQLITE_OK is returned and output variable (*ppState)
         7426  +** is set to point to a buffer containing the transaction state data. 
         7427  +** (*pnState) is set to the size of that buffer in bytes. Otherwise, if
         7428  +** an error occurs, an SQLite error code is returned and both output
         7429  +** variables are zeroed.
         7430  +**
         7431  +** A transaction state may be saved if: 
         7432  +**
         7433  +**   * the transaction does not contain any schema modifications.
         7434  +**   * there are no open sub-transactions.
         7435  +*/
         7436  +int sqlite3_transaction_save(sqlite3 *db, void **ppState, int *pnState);
  7366   7437   
         7438  +int sqlite3_transaction_restore(sqlite3 *db, const void *pState, int nState);
  7367   7439   
  7368   7440   /*
  7369   7441   ** Undo the hack that converts floating point types to integer for
  7370   7442   ** builds on processors without floating point support.
  7371   7443   */
  7372   7444   #ifdef SQLITE_OMIT_FLOATING_POINT
  7373   7445   # undef double
  7374   7446   #endif
  7375   7447   
  7376   7448   #ifdef __cplusplus
  7377   7449   }  /* End of the 'extern "C"' block */
  7378   7450   #endif
  7379   7451   #endif /* _SQLITE3_H_ */

Changes to src/sqliteInt.h. 

  1137   1137   #define SQLITE_PreferBuiltin  0x00200000  /* Preference to built-in funcs */
  1138   1138   #define SQLITE_LoadExtension  0x00400000  /* Enable load_extension */
  1139   1139   #define SQLITE_EnableTrigger  0x00800000  /* True to enable triggers */
  1140   1140   #define SQLITE_DeferFKs       0x01000000  /* Defer all FK constraints */
  1141   1141   #define SQLITE_QueryOnly      0x02000000  /* Disable database changes */
  1142   1142   #define SQLITE_VdbeEQP        0x04000000  /* Debug EXPLAIN QUERY PLAN */
  1143   1143   
         1144  +#define SQLITE_OtaMode        0x08000000  /* True in "ota mode" */
         1145  +
  1144   1146   
  1145   1147   /*
  1146   1148   ** Bits of the sqlite3.dbOptFlags field that are used by the
  1147   1149   ** sqlite3_test_control(SQLITE_TESTCTRL_OPTIMIZATIONS,...) interface to
  1148   1150   ** selectively disable various optimizations.
  1149   1151   */
  1150   1152   #define SQLITE_QueryFlattener 0x0001   /* Query flattening */
................................................................................
  3721   3723   # define sqlite3MemdebugNoType(X,Y)   1
  3722   3724   #endif
  3723   3725   #define MEMTYPE_HEAP       0x01  /* General heap allocations */
  3724   3726   #define MEMTYPE_LOOKASIDE  0x02  /* Might have been lookaside memory */
  3725   3727   #define MEMTYPE_SCRATCH    0x04  /* Scratch allocations */
  3726   3728   #define MEMTYPE_PCACHE     0x08  /* Page cache allocations */
  3727   3729   #define MEMTYPE_DB         0x10  /* Uses sqlite3DbMalloc, not sqlite_malloc */
         3730  +
  3728   3731   
  3729   3732   /*
  3730   3733   ** Threading interface
  3731   3734   */
  3732   3735   #if SQLITE_MAX_WORKER_THREADS>0
  3733   3736   int sqlite3ThreadCreate(SQLiteThread**,void*(*)(void*),void*);
  3734   3737   int sqlite3ThreadJoin(SQLiteThread*, void**);
  3735   3738   #endif
  3736   3739   
  3737   3740   #endif /* _SQLITEINT_H_ */

Changes to src/tclsqlite.c. 

  3694   3694       extern int Sqlitetestintarray_Init(Tcl_Interp*);
  3695   3695       extern int Sqlitetestvfs_Init(Tcl_Interp *);
  3696   3696       extern int Sqlitetestrtree_Init(Tcl_Interp*);
  3697   3697       extern int Sqlitequota_Init(Tcl_Interp*);
  3698   3698       extern int Sqlitemultiplex_Init(Tcl_Interp*);
  3699   3699       extern int SqliteSuperlock_Init(Tcl_Interp*);
  3700   3700       extern int SqlitetestSyscall_Init(Tcl_Interp*);
         3701  +
         3702  +    extern int SqliteOta_Init(Tcl_Interp*);
  3701   3703   
  3702   3704   #if defined(SQLITE_ENABLE_FTS3) || defined(SQLITE_ENABLE_FTS4)
  3703   3705       extern int Sqlitetestfts3_Init(Tcl_Interp *interp);
  3704   3706   #endif
  3705   3707   
  3706   3708   #ifdef SQLITE_ENABLE_ZIPVFS
  3707   3709       extern int Zipvfs_Init(Tcl_Interp*);
................................................................................
  3736   3738       Sqlitetestintarray_Init(interp);
  3737   3739       Sqlitetestvfs_Init(interp);
  3738   3740       Sqlitetestrtree_Init(interp);
  3739   3741       Sqlitequota_Init(interp);
  3740   3742       Sqlitemultiplex_Init(interp);
  3741   3743       SqliteSuperlock_Init(interp);
  3742   3744       SqlitetestSyscall_Init(interp);
         3745  +    SqliteOta_Init(interp);
  3743   3746   
  3744   3747   #if defined(SQLITE_ENABLE_FTS3) || defined(SQLITE_ENABLE_FTS4)
  3745   3748       Sqlitetestfts3_Init(interp);
  3746   3749   #endif
  3747   3750   
  3748   3751       Tcl_CreateObjCommand(
  3749   3752           interp, "load_testfixture_extensions", init_all_cmd, 0, 0

Changes to src/test1.c. 

  6492   6492   
  6493   6493     return TCL_OK;
  6494   6494    sql_error:
  6495   6495     Tcl_AppendResult(interp, "sql error: ", sqlite3_errmsg(db), 0);
  6496   6496     return TCL_ERROR;
  6497   6497   }
  6498   6498   
         6499  +
         6500  +/*
         6501  +** tclcmd: sqlite3_transaction_save DB
         6502  +*/
         6503  +static int testTransactionSave(
         6504  +  void * clientData,
         6505  +  Tcl_Interp *interp,
         6506  +  int objc,
         6507  +  Tcl_Obj *CONST objv[]
         6508  +){
         6509  +  void *pState;
         6510  +  int nState;
         6511  +  sqlite3 *db;
         6512  +  int rc;
         6513  +
         6514  +  if( objc!=2 ){
         6515  +    Tcl_WrongNumArgs(interp, 1, objv, "DB");
         6516  +    return TCL_ERROR;
         6517  +  }
         6518  +  if( getDbPointer(interp, Tcl_GetString(objv[1]), &db) ) return TCL_ERROR;
         6519  +
         6520  +  rc = sqlite3_transaction_save(db, &pState, &nState);
         6521  +  if( rc==SQLITE_OK ){
         6522  +    Tcl_SetObjResult(interp, Tcl_NewByteArrayObj(pState, nState));
         6523  +  }else{
         6524  +    Tcl_SetObjResult(interp, Tcl_NewStringObj(sqlite3ErrName(rc), -1));
         6525  +    return TCL_ERROR;
         6526  +  }
         6527  +
         6528  +  sqlite3_free(pState);
         6529  +  return TCL_OK;
         6530  +}
         6531  +
         6532  +/*
         6533  +** tclcmd: sqlite3_transaction_restore DB BLOB
         6534  +*/
         6535  +static int testTransactionRestore(
         6536  +  void * clientData,
         6537  +  Tcl_Interp *interp,
         6538  +  int objc,
         6539  +  Tcl_Obj *CONST objv[]
         6540  +){
         6541  +  void *pState;
         6542  +  int nState;
         6543  +  sqlite3 *db;
         6544  +  int rc;
         6545  +
         6546  +  if( objc!=3 ){
         6547  +    Tcl_WrongNumArgs(interp, 1, objv, "DB BLOB");
         6548  +    return TCL_ERROR;
         6549  +  }
         6550  +  if( getDbPointer(interp, Tcl_GetString(objv[1]), &db) ) return TCL_ERROR;
         6551  +  pState = (void*)Tcl_GetByteArrayFromObj(objv[2], &nState);
         6552  +
         6553  +  rc = sqlite3_transaction_restore(db, pState, nState);
         6554  +  if( rc==SQLITE_OK ){
         6555  +    Tcl_ResetResult(interp);
         6556  +  }else{
         6557  +    Tcl_SetObjResult(interp, Tcl_NewStringObj(sqlite3ErrName(rc), -1));
         6558  +    return TCL_ERROR;
         6559  +  }
         6560  +
         6561  +  return TCL_OK;
         6562  +}
  6499   6563   
  6500   6564   /*
  6501   6565   ** Register commands with the TCL interpreter.
  6502   6566   */
  6503   6567   int Sqlitetest1_Init(Tcl_Interp *interp){
  6504   6568     extern int sqlite3_search_count;
  6505   6569     extern int sqlite3_found_count;
................................................................................
  6730   6794        { "sqlite3_test_control", test_test_control },
  6731   6795   #if SQLITE_OS_UNIX
  6732   6796        { "getrusage", test_getrusage },
  6733   6797   #endif
  6734   6798        { "load_static_extension", tclLoadStaticExtensionCmd },
  6735   6799        { "sorter_test_fakeheap", sorter_test_fakeheap },
  6736   6800        { "sorter_test_sort4_helper", sorter_test_sort4_helper },
         6801  +     { "sqlite3_transaction_save",    testTransactionSave },
         6802  +     { "sqlite3_transaction_restore", testTransactionRestore },
  6737   6803     };
  6738   6804     static int bitmask_size = sizeof(Bitmask)*8;
  6739   6805     int i;
  6740   6806     extern int sqlite3_sync_count, sqlite3_fullsync_count;
  6741   6807     extern int sqlite3_opentemp_count;
  6742   6808     extern int sqlite3_like_count;
  6743   6809     extern int sqlite3_xferopt_count;

Changes to src/test2.c. 

    85     85     int rc;
    86     86     if( argc!=2 ){
    87     87       Tcl_AppendResult(interp, "wrong # args: should be \"", argv[0],
    88     88          " ID\"", 0);
    89     89       return TCL_ERROR;
    90     90     }
    91     91     pPager = sqlite3TestTextToPtr(argv[1]);
    92         -  rc = sqlite3PagerClose(pPager);
           92  +  rc = sqlite3PagerClose(pPager, 0);
    93     93     if( rc!=SQLITE_OK ){
    94     94       Tcl_AppendResult(interp, sqlite3ErrName(rc), 0);
    95     95       return TCL_ERROR;
    96     96     }
    97     97     return TCL_OK;
    98     98   }
    99     99

Changes to src/vdbeblob.c. 

   458    458     }
   459    459   
   460    460     rc = sqlite3ApiExit(db, rc);
   461    461     assert( rc==SQLITE_OK || p->pStmt==0 );
   462    462     sqlite3_mutex_leave(db->mutex);
   463    463     return rc;
   464    464   }
          465  +
          466  +int sqlite3_index_writer(
          467  +  sqlite3 *db, 
          468  +  int bDelete,
          469  +  const char *zIndex, 
          470  +  sqlite3_stmt **ppStmt,
          471  +  int **paiCol, int *pnCol
          472  +){
          473  +  int rc = SQLITE_OK;
          474  +  Parse *pParse = 0;
          475  +  Index *pIdx = 0;                /* The index to write to */
          476  +  Table *pTab;
          477  +  int i;                          /* Used to iterate through index columns */
          478  +  Vdbe *v = 0;
          479  +  int regRec;                     /* Register to assemble record in */
          480  +  int *aiCol = 0;
          481  +
          482  +  sqlite3_mutex_enter(db->mutex);
          483  +  sqlite3BtreeEnterAll(db);
          484  +
          485  +  /* Allocate the parse context */
          486  +  pParse = sqlite3StackAllocRaw(db, sizeof(*pParse));
          487  +  if( !pParse ) goto index_writer_out;
          488  +  memset(pParse, 0, sizeof(Parse));
          489  +  pParse->db = db;
          490  +
          491  +  /* Allocate the Vdbe */
          492  +  v = sqlite3GetVdbe(pParse);
          493  +  if( v==0 ) goto index_writer_out;
          494  +
          495  +  /* Find the index to write to */
          496  +  pIdx = sqlite3FindIndex(db, zIndex, "main");
          497  +  if( pIdx==0 ){
          498  +    sqlite3ErrorMsg(pParse, "no such index: %s", zIndex);
          499  +    goto index_writer_out;
          500  +  }
          501  +  pTab = pIdx->pTable;
          502  +
          503  +  /* Populate the two output variables, *pnCol and *pnAiCol. */
          504  +  *pnCol = pIdx->nColumn;
          505  +  *paiCol = aiCol = sqlite3DbMallocZero(db, sizeof(int) * pIdx->nColumn);
          506  +  if( aiCol==0 ){
          507  +    rc = SQLITE_NOMEM;
          508  +    goto index_writer_out;
          509  +  }
          510  +  for(i=0; i<pIdx->nKeyCol; i++){
          511  +    aiCol[i] = pIdx->aiColumn[i];
          512  +  }
          513  +  if( !HasRowid(pTab) ){
          514  +    Index *pPk = sqlite3PrimaryKeyIndex(pIdx->pTable);
          515  +    assert( pIdx->nColumn==pIdx->nKeyCol+pPk->nKeyCol );
          516  +    if( pPk==pIdx ){
          517  +      rc = SQLITE_ERROR;
          518  +      goto index_writer_out;
          519  +    }
          520  +    for(i=0; i<pPk->nKeyCol; i++){
          521  +      aiCol[pIdx->nKeyCol+i] = pPk->aiColumn[i];
          522  +    }
          523  +  }else{
          524  +    assert( pIdx->nColumn==pIdx->nKeyCol+1 );
          525  +    aiCol[i] = pTab->iPKey;
          526  +  }
          527  +
          528  +  /* Add an OP_Noop to the VDBE program. Then store a pointer to the 
          529  +  ** output array *paiCol as its P4 value. This is so that the array
          530  +  ** is automatically deleted when the user finalizes the statement. The
          531  +  ** OP_Noop serves no other purpose. */
          532  +  sqlite3VdbeAddOp0(v, OP_Noop);
          533  +  sqlite3VdbeChangeP4(v, -1, (const char*)aiCol, P4_INTARRAY);
          534  +
          535  +  sqlite3BeginWriteOperation(pParse, 0, 0);
          536  +
          537  +  /* Open a write cursor on the index */
          538  +  pParse->nTab = 1;
          539  +  sqlite3VdbeAddOp3(v, OP_OpenWrite, 0, pIdx->tnum, 0);
          540  +  sqlite3VdbeSetP4KeyInfo(pParse, pIdx);
          541  +
          542  +  /* Create the record to insert into the index. Store it in register regRec. */
          543  +  pParse->nVar = pIdx->nColumn;
          544  +  pParse->nMem = pIdx->nColumn;
          545  +  for(i=1; i<=pIdx->nColumn; i++){
          546  +    sqlite3VdbeAddOp2(v, OP_Variable, i, i);
          547  +  }
          548  +  regRec = ++pParse->nMem;
          549  +  sqlite3VdbeAddOp3(v, OP_MakeRecord, 1, pIdx->nColumn, regRec);
          550  +
          551  +  /* If this is a UNIQUE index, check the constraint. */
          552  +  if( pIdx->onError ){
          553  +    int addr = sqlite3VdbeAddOp4Int(v, OP_NoConflict, 0, 0, 1, pIdx->nKeyCol);
          554  +    sqlite3UniqueConstraint(pParse, SQLITE_ABORT, pIdx);
          555  +    sqlite3VdbeJumpHere(v, addr);
          556  +  }
          557  +
          558  +  /* Code the IdxInsert to write to the b-tree index. */
          559  +  sqlite3VdbeAddOp2(v, OP_IdxInsert, 0, regRec);
          560  +  sqlite3FinishCoding(pParse);
          561  +
          562  +index_writer_out:
          563  +  if( rc==SQLITE_OK && db->mallocFailed==0 ){
          564  +    *ppStmt = (sqlite3_stmt*)v;
          565  +  }else{
          566  +    *ppStmt = 0;
          567  +    if( v ) sqlite3VdbeFinalize(v);
          568  +  }
          569  +
          570  +  sqlite3ParserReset(pParse);
          571  +  sqlite3StackFree(db, pParse);
          572  +  sqlite3BtreeLeaveAll(db);
          573  +  rc = sqlite3ApiExit(db, rc);
          574  +  sqlite3_mutex_leave(db->mutex);
          575  +  return rc;
          576  +}
   465    577   
   466    578   #endif /* #ifndef SQLITE_OMIT_INCRBLOB */

Changes to src/wal.c. 

  1042   1042   #endif /* SQLITE_ENABLE_EXPENSIVE_ASSERT */
  1043   1043     }
  1044   1044   
  1045   1045   
  1046   1046     return rc;
  1047   1047   }
  1048   1048   
         1049  +static int walFileReadHdr(Wal *pWal, int *pbValid){
         1050  +  u8 aBuf[WAL_HDRSIZE];           /* Buffer to load WAL header into */
         1051  +  int rc;                         /* Return code */
         1052  +  u32 magic;                      /* Magic value read from WAL header */
         1053  +  int szPage;                     /* Page size according to the log */
         1054  +  u32 version;                    /* Magic value read from WAL header */
         1055  +
         1056  +  *pbValid = 0;
         1057  +
         1058  +  /* Read in the WAL header. */
         1059  +  rc = sqlite3OsRead(pWal->pWalFd, aBuf, WAL_HDRSIZE, 0);
         1060  +  if( rc!=SQLITE_OK ){
         1061  +    return rc;
         1062  +  }
         1063  +
         1064  +  /* If the database page size is not a power of two, or is greater than
         1065  +  ** SQLITE_MAX_PAGE_SIZE, conclude that the WAL file contains no valid 
         1066  +  ** data. Similarly, if the 'magic' value is invalid, ignore the whole
         1067  +  ** WAL file.
         1068  +  */
         1069  +  magic = sqlite3Get4byte(&aBuf[0]);
         1070  +  szPage = sqlite3Get4byte(&aBuf[8]);
         1071  +  if( (magic&0xFFFFFFFE)!=WAL_MAGIC 
         1072  +      || szPage&(szPage-1) 
         1073  +      || szPage>SQLITE_MAX_PAGE_SIZE 
         1074  +      || szPage<512 
         1075  +  ){
         1076  +    return SQLITE_OK;
         1077  +  }
         1078  +
         1079  +  pWal->hdr.bigEndCksum = (u8)(magic&0x00000001);
         1080  +  pWal->szPage = szPage;
         1081  +  pWal->nCkpt = sqlite3Get4byte(&aBuf[12]);
         1082  +  memcpy(&pWal->hdr.aSalt, &aBuf[16], 8);
         1083  +
         1084  +  /* Verify that the WAL header checksum is correct */
         1085  +  walChecksumBytes(pWal->hdr.bigEndCksum==SQLITE_BIGENDIAN, 
         1086  +      aBuf, WAL_HDRSIZE-2*4, 0, pWal->hdr.aFrameCksum
         1087  +  );
         1088  +  if( pWal->hdr.aFrameCksum[0]!=sqlite3Get4byte(&aBuf[24])
         1089  +      || pWal->hdr.aFrameCksum[1]!=sqlite3Get4byte(&aBuf[28])
         1090  +  ){
         1091  +    return SQLITE_OK;
         1092  +  }
         1093  +
         1094  +  /* Verify that the version number on the WAL format is one that
         1095  +  ** are able to understand */
         1096  +  version = sqlite3Get4byte(&aBuf[4]);
         1097  +  if( version!=WAL_MAX_VERSION ){
         1098  +    return SQLITE_CANTOPEN_BKPT;
         1099  +  }
         1100  +
         1101  +  *pbValid = 1;
         1102  +  return SQLITE_OK;
         1103  +}
         1104  +
  1049   1105   
  1050   1106   /*
  1051   1107   ** Recover the wal-index by reading the write-ahead log file. 
  1052   1108   **
  1053   1109   ** This routine first tries to establish an exclusive lock on the
  1054   1110   ** wal-index to prevent other threads/processes from doing anything
  1055   1111   ** with the WAL or wal-index while recovery is running.  The
................................................................................
  1086   1142   
  1087   1143     rc = sqlite3OsFileSize(pWal->pWalFd, &nSize);
  1088   1144     if( rc!=SQLITE_OK ){
  1089   1145       goto recovery_error;
  1090   1146     }
  1091   1147   
  1092   1148     if( nSize>WAL_HDRSIZE ){
  1093         -    u8 aBuf[WAL_HDRSIZE];         /* Buffer to load WAL header into */
  1094   1149       u8 *aFrame = 0;               /* Malloc'd buffer to load entire frame */
  1095   1150       int szFrame;                  /* Number of bytes in buffer aFrame[] */
  1096   1151       u8 *aData;                    /* Pointer to data part of aFrame buffer */
  1097   1152       int iFrame;                   /* Index of last frame read */
  1098   1153       i64 iOffset;                  /* Next offset to read from log file */
  1099   1154       int szPage;                   /* Page size according to the log */
  1100         -    u32 magic;                    /* Magic value read from WAL header */
  1101         -    u32 version;                  /* Magic value read from WAL header */
  1102   1155       int isValid;                  /* True if this frame is valid */
  1103   1156   
  1104         -    /* Read in the WAL header. */
  1105         -    rc = sqlite3OsRead(pWal->pWalFd, aBuf, WAL_HDRSIZE, 0);
  1106         -    if( rc!=SQLITE_OK ){
  1107         -      goto recovery_error;
  1108         -    }
  1109         -
  1110         -    /* If the database page size is not a power of two, or is greater than
  1111         -    ** SQLITE_MAX_PAGE_SIZE, conclude that the WAL file contains no valid 
  1112         -    ** data. Similarly, if the 'magic' value is invalid, ignore the whole
  1113         -    ** WAL file.
  1114         -    */
  1115         -    magic = sqlite3Get4byte(&aBuf[0]);
  1116         -    szPage = sqlite3Get4byte(&aBuf[8]);
  1117         -    if( (magic&0xFFFFFFFE)!=WAL_MAGIC 
  1118         -     || szPage&(szPage-1) 
  1119         -     || szPage>SQLITE_MAX_PAGE_SIZE 
  1120         -     || szPage<512 
  1121         -    ){
  1122         -      goto finished;
  1123         -    }
  1124         -    pWal->hdr.bigEndCksum = (u8)(magic&0x00000001);
  1125         -    pWal->szPage = szPage;
  1126         -    pWal->nCkpt = sqlite3Get4byte(&aBuf[12]);
  1127         -    memcpy(&pWal->hdr.aSalt, &aBuf[16], 8);
  1128         -
  1129         -    /* Verify that the WAL header checksum is correct */
  1130         -    walChecksumBytes(pWal->hdr.bigEndCksum==SQLITE_BIGENDIAN, 
  1131         -        aBuf, WAL_HDRSIZE-2*4, 0, pWal->hdr.aFrameCksum
  1132         -    );
  1133         -    if( pWal->hdr.aFrameCksum[0]!=sqlite3Get4byte(&aBuf[24])
  1134         -     || pWal->hdr.aFrameCksum[1]!=sqlite3Get4byte(&aBuf[28])
  1135         -    ){
  1136         -      goto finished;
  1137         -    }
  1138         -
  1139         -    /* Verify that the version number on the WAL format is one that
  1140         -    ** are able to understand */
  1141         -    version = sqlite3Get4byte(&aBuf[4]);
  1142         -    if( version!=WAL_MAX_VERSION ){
  1143         -      rc = SQLITE_CANTOPEN_BKPT;
  1144         -      goto finished;
  1145         -    }
         1157  +    rc = walFileReadHdr(pWal, &isValid);
         1158  +    if( rc!=SQLITE_OK ) goto recovery_error;
         1159  +    if( isValid==0 ) goto finished;
         1160  +    szPage = pWal->szPage;
  1146   1161   
  1147   1162       /* Malloc a buffer to read frames into. */
  1148   1163       szFrame = szPage + WAL_FRAME_HDRSIZE;
  1149   1164       aFrame = (u8 *)sqlite3_malloc(szFrame);
  1150   1165       if( !aFrame ){
  1151   1166         rc = SQLITE_NOMEM;
  1152   1167         goto recovery_error;
................................................................................
  1833   1848       ** ordinary, rollback-mode locking methods, this guarantees that the
  1834   1849       ** connection associated with this log file is the only connection to
  1835   1850       ** the database. In this case checkpoint the database and unlink both
  1836   1851       ** the wal and wal-index files.
  1837   1852       **
  1838   1853       ** The EXCLUSIVE lock is not released before returning.
  1839   1854       */
  1840         -    rc = sqlite3OsLock(pWal->pDbFd, SQLITE_LOCK_EXCLUSIVE);
  1841         -    if( rc==SQLITE_OK ){
  1842         -      if( pWal->exclusiveMode==WAL_NORMAL_MODE ){
  1843         -        pWal->exclusiveMode = WAL_EXCLUSIVE_MODE;
  1844         -      }
  1845         -      rc = sqlite3WalCheckpoint(
  1846         -          pWal, SQLITE_CHECKPOINT_PASSIVE, 0, 0, sync_flags, nBuf, zBuf, 0, 0
  1847         -      );
         1855  +    if( zBuf ){
         1856  +      rc = sqlite3OsLock(pWal->pDbFd, SQLITE_LOCK_EXCLUSIVE);
  1848   1857         if( rc==SQLITE_OK ){
  1849         -        int bPersist = -1;
  1850         -        sqlite3OsFileControlHint(
  1851         -            pWal->pDbFd, SQLITE_FCNTL_PERSIST_WAL, &bPersist
         1858  +        if( pWal->exclusiveMode==WAL_NORMAL_MODE ){
         1859  +          pWal->exclusiveMode = WAL_EXCLUSIVE_MODE;
         1860  +        }
         1861  +        rc = sqlite3WalCheckpoint(
         1862  +            pWal, SQLITE_CHECKPOINT_PASSIVE, 0, 0, sync_flags, nBuf, zBuf, 0, 0
  1852   1863           );
  1853         -        if( bPersist!=1 ){
  1854         -          /* Try to delete the WAL file if the checkpoint completed and
  1855         -          ** fsyned (rc==SQLITE_OK) and if we are not in persistent-wal
  1856         -          ** mode (!bPersist) */
  1857         -          isDelete = 1;
  1858         -        }else if( pWal->mxWalSize>=0 ){
  1859         -          /* Try to truncate the WAL file to zero bytes if the checkpoint
  1860         -          ** completed and fsynced (rc==SQLITE_OK) and we are in persistent
  1861         -          ** WAL mode (bPersist) and if the PRAGMA journal_size_limit is a
  1862         -          ** non-negative value (pWal->mxWalSize>=0).  Note that we truncate
  1863         -          ** to zero bytes as truncating to the journal_size_limit might
  1864         -          ** leave a corrupt WAL file on disk. */
  1865         -          walLimitSize(pWal, 0);
         1864  +        if( rc==SQLITE_OK ){
         1865  +          int bPersist = -1;
         1866  +          sqlite3OsFileControlHint(
         1867  +              pWal->pDbFd, SQLITE_FCNTL_PERSIST_WAL, &bPersist
         1868  +          );
         1869  +          if( bPersist!=1 ){
         1870  +            /* Try to delete the WAL file if the checkpoint completed and
         1871  +            ** fsyned (rc==SQLITE_OK) and if we are not in persistent-wal
         1872  +            ** mode (!bPersist) */
         1873  +            isDelete = 1;
         1874  +          }else if( pWal->mxWalSize>=0 ){
         1875  +            /* Try to truncate the WAL file to zero bytes if the checkpoint
         1876  +            ** completed and fsynced (rc==SQLITE_OK) and we are in persistent
         1877  +            ** WAL mode (bPersist) and if the PRAGMA journal_size_limit is a
         1878  +            ** non-negative value (pWal->mxWalSize>=0).  Note that we truncate
         1879  +            ** to zero bytes as truncating to the journal_size_limit might
         1880  +            ** leave a corrupt WAL file on disk. */
         1881  +            walLimitSize(pWal, 0);
         1882  +          }
  1866   1883           }
  1867   1884         }
  1868   1885       }
  1869   1886   
  1870   1887       walIndexClose(pWal, isDelete);
  1871   1888       sqlite3OsClose(pWal->pWalFd);
  1872   1889       if( isDelete ){
................................................................................
  3074   3091   ** Return true if the argument is non-NULL and the WAL module is using
  3075   3092   ** heap-memory for the wal-index. Otherwise, if the argument is NULL or the
  3076   3093   ** WAL module is using shared-memory, return false. 
  3077   3094   */
  3078   3095   int sqlite3WalHeapMemory(Wal *pWal){
  3079   3096     return (pWal && pWal->exclusiveMode==WAL_HEAPMEMORY_MODE );
  3080   3097   }
         3098  +
         3099  +/*
         3100  +** Save current transaction state.
         3101  +**
         3102  +** The transaction state consists of a series of 32-bit big-endian integers:
         3103  +**
         3104  +**     * initial number of frames in WAL file.
         3105  +**     * initial checksum values (2 integers).
         3106  +**     * current number of frames.
         3107  +**     * current checksum values (2 integers).
         3108  +*/
         3109  +int sqlite3WalSaveState(Wal *pWal, void **ppState, int *pnState){
         3110  +  int rc = SQLITE_OK;
         3111  +
         3112  +  *ppState = 0;
         3113  +  *pnState = 0;
         3114  +  if( pWal->writeLock==0 ){
         3115  +    /* Must be in a write transaction to call this function. */
         3116  +    rc = SQLITE_ERROR;
         3117  +  }else{
         3118  +    WalIndexHdr *pOrig = (WalIndexHdr*)walIndexHdr(pWal);
         3119  +    int nBuf = 6 * 4;             /* Bytes of space to allocate */
         3120  +    u8 *aBuf;
         3121  +
         3122  +    aBuf = sqlite3_malloc(nBuf);
         3123  +    if( aBuf==0 ){
         3124  +      rc = SQLITE_NOMEM;
         3125  +    }else{
         3126  +      sqlite3Put4byte(&aBuf[0], pOrig->mxFrame);
         3127  +      sqlite3Put4byte(&aBuf[4], pOrig->aFrameCksum[0]);
         3128  +      sqlite3Put4byte(&aBuf[8], pOrig->aFrameCksum[1]);
         3129  +      sqlite3Put4byte(&aBuf[12], pWal->hdr.mxFrame);
         3130  +      sqlite3Put4byte(&aBuf[16], pWal->hdr.aFrameCksum[0]);
         3131  +      sqlite3Put4byte(&aBuf[20], pWal->hdr.aFrameCksum[1]);
         3132  +      *ppState = (void*)aBuf;
         3133  +      *pnState = nBuf;
         3134  +    }
         3135  +  }
         3136  +
         3137  +  return rc;
         3138  +}
         3139  +
         3140  +static int walUndoNoop(void *pUndoCtx, Pgno pgno){
         3141  +  UNUSED_PARAMETER(pUndoCtx);
         3142  +  UNUSED_PARAMETER(pgno);
         3143  +  return SQLITE_OK;
         3144  +}
         3145  +
         3146  +/*
         3147  +** If possible, restore the state of the curent transaction to that 
         3148  +** described by the second and third arguments.
         3149  +*/
         3150  +int sqlite3WalRestoreState(Wal *pWal, const void *pState, int nState){
         3151  +  int rc = SQLITE_OK;
         3152  +
         3153  +  if( pWal->writeLock==0 ){
         3154  +    /* Must have opened a write transaction to call this */
         3155  +    rc = SQLITE_ERROR;
         3156  +  }else{
         3157  +    u8 *aBuf = (u8*)pState;
         3158  +    int szFrame;                    /* Size of each frame in WAL file */
         3159  +    u8 *aFrame = 0;                 /* Buffer to read data into */
         3160  +    u8 *aData;                      /* Data part of aFrame[] buffer */
         3161  +    u32 mxFrame;                    /* Maximum frame following restoration */
         3162  +    int i;                          /* Iterator variable */
         3163  +
         3164  +    WalIndexHdr *pOrig = (WalIndexHdr*)walIndexHdr(pWal);
         3165  +
         3166  +    /* Check that no dirty pages have been written to the WAL file since
         3167  +    ** the current transaction was opened.  */
         3168  +    if( pOrig->mxFrame!=pWal->hdr.mxFrame 
         3169  +     || pOrig->aFrameCksum[0]!=pWal->hdr.aFrameCksum[0] 
         3170  +     || pOrig->aFrameCksum[1]!=pWal->hdr.aFrameCksum[1] 
         3171  +    ){
         3172  +      rc = SQLITE_ERROR;
         3173  +    }
         3174  +
         3175  +    /* Check that the WAL file is in the same state that it was when the
         3176  +    ** transaction was saved. If not, return SQLITE_MISMATCH - cannot 
         3177  +    ** resume this transaction  */
         3178  +    if( rc==SQLITE_OK && (
         3179  +          pWal->hdr.mxFrame!=sqlite3Get4byte(&aBuf[0])
         3180  +       || pWal->hdr.aFrameCksum[0]!=sqlite3Get4byte(&aBuf[4])
         3181  +       || pWal->hdr.aFrameCksum[1]!=sqlite3Get4byte(&aBuf[8])
         3182  +    )){
         3183  +      rc = SQLITE_MISMATCH;
         3184  +    }
         3185  +
         3186  +    if( rc==SQLITE_OK && pWal->readLock==0 ){
         3187  +      int cnt = 0;
         3188  +      walUnlockShared(pWal, WAL_READ_LOCK(0));
         3189  +      pWal->readLock = -1;
         3190  +      do{
         3191  +        int notUsed;
         3192  +        rc = walTryBeginRead(pWal, &notUsed, 1, ++cnt);
         3193  +      }while( rc==WAL_RETRY );
         3194  +      
         3195  +      if( rc==SQLITE_OK ){
         3196  +        int bValid;
         3197  +        rc = walFileReadHdr(pWal, &bValid);
         3198  +        if( rc==SQLITE_OK && bValid==0 ) rc = SQLITE_MISMATCH;
         3199  +        pWal->hdr.szPage = (u16)((pWal->szPage&0xff00) | (pWal->szPage>>16));
         3200  +      }
         3201  +    }
         3202  +
         3203  +    /* Malloc a buffer to read frames into. */
         3204  +    if( rc==SQLITE_OK ){
         3205  +      szFrame = pWal->szPage + WAL_FRAME_HDRSIZE;
         3206  +      aFrame = (u8*)sqlite3_malloc(szFrame);
         3207  +      if( !aFrame ){
         3208  +        rc = SQLITE_NOMEM;
         3209  +      }else{
         3210  +        aData = &aFrame[WAL_FRAME_HDRSIZE];
         3211  +      }
         3212  +    }
         3213  +
         3214  +    mxFrame = sqlite3Get4byte(&aBuf[12]);
         3215  +    for(i=pWal->hdr.mxFrame+1; rc==SQLITE_OK && i<=mxFrame; i++){
         3216  +      sqlite3_int64 iOff = walFrameOffset(i, pWal->szPage);
         3217  +      rc = sqlite3OsRead(pWal->pWalFd, aFrame, szFrame, iOff);
         3218  +      if( rc==SQLITE_OK ){
         3219  +        u32 iPg;
         3220  +        u32 dummy;
         3221  +        if( 0==walDecodeFrame(pWal, &iPg, &dummy, aData, aFrame) ){
         3222  +          rc = SQLITE_MISMATCH;
         3223  +        }else{
         3224  +          rc = walIndexAppend(pWal, i, iPg);
         3225  +          if( iPg>pWal->hdr.nPage ) pWal->hdr.nPage = iPg;
         3226  +        }
         3227  +        pWal->hdr.mxFrame = i;
         3228  +      }
         3229  +    }
         3230  +    sqlite3_free(aFrame);
         3231  +
         3232  +    if( rc==SQLITE_OK ){
         3233  +      assert( pWal->hdr.mxFrame==mxFrame );
         3234  +      if( pWal->hdr.aFrameCksum[0]!=sqlite3Get4byte(&aBuf[16])
         3235  +       || pWal->hdr.aFrameCksum[1]!=sqlite3Get4byte(&aBuf[20])
         3236  +      ){
         3237  +        rc = SQLITE_MISMATCH;
         3238  +      }
         3239  +    }
         3240  +
         3241  +
         3242  +    if( rc!=SQLITE_OK ){
         3243  +      sqlite3WalUndo(pWal, walUndoNoop, 0);
         3244  +    }
         3245  +  }
         3246  +
         3247  +  return rc;
         3248  +}
  3081   3249   
  3082   3250   #ifdef SQLITE_ENABLE_ZIPVFS
  3083   3251   /*
  3084   3252   ** If the argument is not NULL, it points to a Wal object that holds a
  3085   3253   ** read-lock. This function returns the database page-size if it is known,
  3086   3254   ** or zero if it is not (or if pWal is NULL).
  3087   3255   */

Changes to src/wal.h. 

   122    122   
   123    123   /* Return true if the argument is non-NULL and the WAL module is using
   124    124   ** heap-memory for the wal-index. Otherwise, if the argument is NULL or the
   125    125   ** WAL module is using shared-memory, return false. 
   126    126   */
   127    127   int sqlite3WalHeapMemory(Wal *pWal);
   128    128   
          129  +int sqlite3WalSaveState(Wal *pWal, void **ppState, int *pnState);
          130  +int sqlite3WalRestoreState(Wal *pWal, const void *pState, int nState);
          131  +
   129    132   #ifdef SQLITE_ENABLE_ZIPVFS
   130    133   /* If the WAL file is not empty, return the number of bytes of content
   131    134   ** stored in each frame (i.e. the db page-size when the WAL was created).
   132    135   */
   133    136   int sqlite3WalFramesize(Wal *pWal);
   134    137   #endif
   135    138   
   136    139   #endif /* ifndef SQLITE_OMIT_WAL */
   137    140   #endif /* _WAL_H_ */

This page was generated in about 0.104s by Fossil 2.12 [018db17c54] 2020-05-29 17:57:16