/ Check-in [ccee9996]
Login

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

Overview
Comment:Update ota so that the hidden columns of virtual tables may be written.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | ota-update
Files: files | file ages | folders
SHA1:ccee999649d0fa1d48e53847542f4cbe05e3d694
User & Date: dan 2014-11-27 18:09:46
Context
2014-12-06
19:30
Allow the ota extension to write to tables with no PRIMARY KEY declaration. check-in: ba59a7e2 user: dan tags: ota-update
2014-11-27
18:09
Update ota so that the hidden columns of virtual tables may be written. check-in: ccee9996 user: dan tags: ota-update
2014-11-22
09:09
Add SQLITE_ENABLE_OTA pre-processor directives so that this branch may be compiled with or without OTA. check-in: 600cefdd user: dan tags: ota-update
Changes
Hide Diffs Side-by-Side Diffs Ignore Whitespace Patch

Added ext/ota/ota10.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  +if {![info exists testdir]} {
           14  +  set testdir [file join [file dirname [info script]] .. .. test]
           15  +}
           16  +source $testdir/tester.tcl
           17  +set ::testprefix ota10
           18  +
           19  +
           20  +#--------------------------------------------------------------------
           21  +# Test that UPDATE commands work even if the input columns are in a 
           22  +# different order to the output columns. 
           23  +#
           24  +do_execsql_test 1.0 {
           25  +  CREATE TABLE t1(a INTEGER PRIMARY KEY, b, c);
           26  +  INSERT INTO t1 VALUES(1, 'b', 'c');
           27  +}
           28  +
           29  +proc apply_ota {sql} {
           30  +  forcedelete ota.db
           31  +  sqlite3 db2 ota.db
           32  +  db2 eval $sql
           33  +  db2 close
           34  +  sqlite3ota ota test.db ota.db
           35  +  while { [ota step]=="SQLITE_OK" } {}
           36  +  ota close
           37  +}
           38  +
           39  +do_test 1.1 {
           40  +  apply_ota {
           41  +    CREATE TABLE data_t1(a, c, b, ota_control);
           42  +    INSERT INTO data_t1 VALUES(1, 'xxx', NULL, '.x.');
           43  +  }
           44  +  db eval { SELECT * FROM t1 }
           45  +} {1 b xxx}
           46  +
           47  +#--------------------------------------------------------------------
           48  +# Test that the hidden languageid column of an fts4 table can be 
           49  +# written.
           50  +#
           51  +ifcapable fts3 {
           52  +  do_execsql_test 2.0 {
           53  +    CREATE VIRTUAL TABLE ft USING fts4(a, b, languageid='langid');
           54  +  }
           55  +  do_test 2.1 {
           56  +    apply_ota {
           57  +      CREATE TABLE data_ft(a, b, ota_rowid, langid, ota_control);
           58  +      INSERT INTO data_ft VALUES('a', 'b', 22, 1, 0);    -- insert
           59  +      INSERT INTO data_ft VALUES('a', 'b', 23, 10, 0);   -- insert
           60  +      INSERT INTO data_ft VALUES('a', 'b', 24, 100, 0);  -- insert
           61  +    }
           62  +    db eval { SELECT a, b, rowid, langid FROM ft }
           63  +  } [list {*}{
           64  +    a b 22 1
           65  +    a b 23 10
           66  +    a b 24 100
           67  +  }]
           68  +  
           69  +  # Or not - this data_xxx table has no langid column, so langid 
           70  +  # defaults to 0.
           71  +  #
           72  +  do_test 2.2 {
           73  +    apply_ota {
           74  +      CREATE TABLE data_ft(a, b, ota_rowid, ota_control);
           75  +      INSERT INTO data_ft VALUES('a', 'b', 25, 0);    -- insert
           76  +    }
           77  +    db eval { SELECT a, b, rowid, langid FROM ft }
           78  +  } [list {*}{
           79  +    a b 22 1
           80  +    a b 23 10
           81  +    a b 24 100
           82  +    a b 25 0
           83  +  }]
           84  +  
           85  +  # Update langid.
           86  +  #
           87  +  do_test 2.3 {
           88  +    apply_ota {
           89  +      CREATE TABLE data_ft(a, b, ota_rowid, langid, ota_control);
           90  +      INSERT INTO data_ft VALUES(NULL, NULL, 23, 50, '..x');
           91  +      INSERT INTO data_ft VALUES(NULL, NULL, 25, 500, '..x');
           92  +    }
           93  +    db eval { SELECT a, b, rowid, langid FROM ft }
           94  +  } [list {*}{
           95  +    a b 22 1
           96  +    a b 23 50
           97  +    a b 24 100
           98  +    a b 25 500
           99  +  }]
          100  +}
          101  +
          102  +#--------------------------------------------------------------------
          103  +# Test that if writing a hidden virtual table column is an error, 
          104  +# attempting to do so via ota is also an error.
          105  +#
          106  +ifcapable fts3 {
          107  +  do_execsql_test 3.0 {
          108  +    CREATE VIRTUAL TABLE xt USING fts4(a);
          109  +  }
          110  +  do_test 3.1 {
          111  +    list [catch {
          112  +      apply_ota {
          113  +        CREATE TABLE data_xt(a, xt, ota_rowid, ota_control);
          114  +        INSERT INTO data_xt VALUES('a', 'b', 1, 0);
          115  +      }
          116  +    } msg] $msg
          117  +  } {1 {SQLITE_ERROR - SQL logic error or missing database}}
          118  +}
          119  +
          120  +
          121  +
          122  +finish_test

Changes to ext/ota/ota3.test.

    92     92     sqlite3 db2 ota.db
    93     93     db2 eval {
    94     94       CREATE TABLE data_x1(a, b, ota_control);
    95     95       INSERT INTO data_x1 VALUES(1, 'a', 0);
    96     96     }
    97     97     db2 close
    98     98     list [catch { run_ota test.db ota.db } msg] $msg
    99         -} {1 {SQLITE_ERROR - no such column: c}}
           99  +} {1 {SQLITE_ERROR - column missing from data_x1: c}}
   100    100   
   101    101   do_execsql_test 2.2 {
   102    102     PRAGMA integrity_check;
   103    103   } {ok}
   104    104   
   105    105   finish_test

Changes to ext/ota/sqlite3ota.c.

   219    219   */
   220    220   static void otaObjIterFreeCols(OtaObjIter *pIter){
   221    221     int i;
   222    222     for(i=0; i<pIter->nTblCol; i++){
   223    223       sqlite3_free(pIter->azTblCol[i]);
   224    224     }
   225    225     sqlite3_free(pIter->azTblCol);
   226         -  sqlite3_free(pIter->abTblPk);
   227    226     pIter->azTblCol = 0;
   228    227     pIter->abTblPk = 0;
   229    228     pIter->nTblCol = 0;
   230    229     sqlite3_free(pIter->zMask);
   231    230     pIter->zMask = 0;
   232    231     pIter->bRowid = 0;
   233    232     pIter->bVtab = 0;
................................................................................
   364    363         *p++ = zName[i];
   365    364       }
   366    365       *p++ = '`';
   367    366       *p++ = '\0';
   368    367     }
   369    368     return zRet;
   370    369   }
          370  +
          371  +/*
          372  +** Argument zName points to a column name. Argument zQuoted also points
          373  +** to a column name, but one that has been quoted using otaQuoteName().
          374  +** Return true if the column names are the same, or false otherwise.
          375  +*/
          376  +static int otaMatchName(const char *zName, const char *zQuoted){
          377  +  const char *p = zName;
          378  +  const char *q = &zQuoted[1];
          379  +  while( 1 ){
          380  +    if( *q=='`' ) q++;
          381  +    if( sqlite3_strnicmp(q, p, 1) ) return 0;
          382  +    if( !*q ) break;
          383  +    p++;
          384  +    q++;
          385  +  }
          386  +  return 1;
          387  +}
   371    388   
   372    389   /*
   373    390   ** Argument zFmt is a sqlite3_mprintf() style format string. The trailing
   374    391   ** arguments are the usual subsitution values. This function performs
   375    392   ** the printf() style substitutions and executes the result as an SQL
   376    393   ** statement on the OTA handles database.
   377    394   **
................................................................................
   392    409       }
   393    410     }
   394    411     va_end(ap);
   395    412     return p->rc;
   396    413   }
   397    414   
   398    415   /*
   399         -** Increase the size of the pIter->azTblCol[] and abTblPk[] arrays so that
          416  +** Allocate and zero the pIter->azTblCol[] and abTblPk[] arrays so that
   400    417   ** there is room for at least nCol elements. If an OOM occurs, store an
   401    418   ** error code in the OTA handle passed as the first argument.
   402    419   */
   403         -static void otaExtendIterArrays(sqlite3ota *p, OtaObjIter *pIter, int nCol){
          420  +static void otaAllocateIterArrays(sqlite3ota *p, OtaObjIter *pIter, int nCol){
          421  +  int nByte = sizeof(char*) * nCol + sizeof(unsigned char*) * nCol;
          422  +  char **azNew;
          423  +
   404    424     assert( p->rc==SQLITE_OK );
   405         -  if( (nCol % 8)==0 ){
   406         -    unsigned char *abNew;
   407         -    int nByte = sizeof(char*) * (nCol+8);
   408         -    char **azNew = (char**)sqlite3_realloc(pIter->azTblCol, nByte);
   409         -    abNew = (unsigned char*)sqlite3_realloc(pIter->abTblPk, nCol+8);
   410         -
   411         -    if( azNew ) pIter->azTblCol = azNew;
   412         -    if( abNew ) pIter->abTblPk = abNew;
   413         -    if( azNew==0 || abNew==0 ) p->rc = SQLITE_NOMEM;
          425  +  azNew = (char**)sqlite3_malloc(nByte);
          426  +  if( azNew ){
          427  +    memset(azNew, 0, nByte);
          428  +    pIter->azTblCol = azNew;
          429  +    pIter->abTblPk = (unsigned char*)&pIter->azTblCol[nCol];
          430  +  }else{
          431  +    p->rc = SQLITE_NOMEM;
   414    432     }
   415    433   }
   416    434   
   417    435   /*
   418    436   ** Return true if zTab is the name of a virtual table within the target
   419    437   ** database.
   420    438   */
................................................................................
   447    465   **
   448    466   ** Return SQLITE_OK if successful, or an SQLite error code otherwise. If
   449    467   ** an error does occur, an error code and error message are also left in 
   450    468   ** the OTA handle.
   451    469   */
   452    470   static int otaObjIterGetCols(sqlite3ota *p, OtaObjIter *pIter){
   453    471     if( pIter->azTblCol==0 ){
   454         -    sqlite3_stmt *pStmt;
   455         -    char *zSql;
          472  +    sqlite3_stmt *pStmt = 0;
   456    473       int nCol = 0;
   457    474       int bSeenPk = 0;
          475  +    int i;                        /* for() loop iterator variable */
   458    476       int rc2;                      /* sqlite3_finalize() return value */
   459    477   
   460    478       assert( pIter->bRowid==0 && pIter->bVtab==0 );
   461    479   
   462         -    zSql = sqlite3_mprintf("PRAGMA main.table_info(%Q)", pIter->zTbl);
   463         -    p->rc = prepareFreeAndCollectError(p->db, &pStmt, &p->zErrmsg, zSql);
          480  +    /* Populate the azTblCol[] and nTblCol variables based on the columns
          481  +    ** of the input table. Ignore any input table columns that begin with
          482  +    ** "ota_".  */
          483  +    p->rc = prepareFreeAndCollectError(p->db, &pStmt, &p->zErrmsg, 
          484  +        sqlite3_mprintf("SELECT * FROM 'data_%q'", pIter->zTbl)
          485  +    );
          486  +    if( p->rc==SQLITE_OK ){
          487  +      nCol = sqlite3_column_count(pStmt);
          488  +      otaAllocateIterArrays(p, pIter, nCol);
          489  +    }
          490  +    for(i=0; p->rc==SQLITE_OK && i<nCol; i++){
          491  +      const char *zName = (const char*)sqlite3_column_name(pStmt, i);
          492  +      if( sqlite3_strnicmp("ota_", zName, 4) ){
          493  +        char *zCopy = otaQuoteName(zName);
          494  +        pIter->azTblCol[pIter->nTblCol++] = zCopy;
          495  +        if( zCopy==0 ) p->rc = SQLITE_NOMEM;
          496  +      }
          497  +    }
          498  +    sqlite3_finalize(pStmt);
          499  +    pStmt = 0;
          500  +
          501  +    /* Check that all non-HIDDEN columns in the destination table are also
          502  +    ** present in the input table. Populate the abTblPk[] array at the
          503  +    ** same time.  */
          504  +    if( p->rc==SQLITE_OK ){
          505  +      p->rc = prepareFreeAndCollectError(p->db, &pStmt, &p->zErrmsg, 
          506  +          sqlite3_mprintf("PRAGMA main.table_info(%Q)", pIter->zTbl)
          507  +      );
          508  +    }
   464    509       while( p->rc==SQLITE_OK && SQLITE_ROW==sqlite3_step(pStmt) ){
   465         -      otaExtendIterArrays(p, pIter, nCol);
   466         -      if( p->rc==SQLITE_OK ){
   467         -        const char *zName = (const char*)sqlite3_column_text(pStmt, 1);
          510  +      const char *zName = (const char*)sqlite3_column_text(pStmt, 1);
          511  +      for(i=0; i<pIter->nTblCol; i++){
          512  +        if( otaMatchName(zName, pIter->azTblCol[i]) ) break;
          513  +      }
          514  +      if( i==pIter->nTblCol ){
          515  +        p->rc = SQLITE_ERROR;
          516  +        p->zErrmsg = sqlite3_mprintf("column missing from data_%q: %s",
          517  +            pIter->zTbl, zName
          518  +        );
          519  +      }else{
   468    520           int iPk = sqlite3_column_int(pStmt, 5);
   469         -        pIter->abTblPk[nCol] = (iPk!=0);
          521  +        pIter->abTblPk[i] = (iPk!=0);
   470    522           if( iPk ) bSeenPk = 1;
   471    523           if( iPk<0 ) pIter->bRowid = 1;
   472         -        pIter->azTblCol[nCol] = otaQuoteName(zName);
   473         -        if( pIter->azTblCol[nCol]==0 ) p->rc = SQLITE_NOMEM;
   474         -        nCol++;
   475    524         }
   476    525       }
   477         -    pIter->nTblCol = nCol;
   478    526       rc2 = sqlite3_finalize(pStmt);
   479    527       if( p->rc==SQLITE_OK ) p->rc = rc2;
   480    528   
   481    529       if( p->rc==SQLITE_OK && bSeenPk==0 ){
   482    530         const char *zTab = pIter->zTbl;
   483    531         if( otaIsVtab(p, zTab) ){
   484    532           pIter->bVtab = 1;

Changes to ext/ota/sqlite3ota.h.

   101    101   ** can not be updated using OTA. For example, if the target db contains:
   102    102   **
   103    103   **   CREATE VIRTUAL TABLE ft1 USING fts3(a, b);
   104    104   **
   105    105   ** then the OTA database should contain:
   106    106   **
   107    107   **   CREATE TABLE data_ft1(a, b, ota_rowid, ota_control);
          108  +**
          109  +** All non-hidden columns (i.e. all columns matched by "SELECT *") of the
          110  +** target table must be present in the input table. For virtual tables,
          111  +** hidden columns are optional - they are updated by OTA if present in
          112  +** the input table, or not otherwise. For example, to write to an fts4
          113  +** table with a hidden languageid column such as:
          114  +**
          115  +**   CREATE VIRTUAL TABLE ft1 USING fts4(a, b, languageid='langid');
          116  +**
          117  +** Either of the following input table schemas may be used:
          118  +**
          119  +**   CREATE TABLE data_ft1(a, b, langid, ota_rowid, ota_control);
          120  +**   CREATE TABLE data_ft1(a, b, ota_rowid, ota_control);
   108    121   **
   109    122   ** For each row to INSERT into the target database as part of the OTA 
   110    123   ** update, the corresponding data_% table should contain a single record
   111    124   ** with the "ota_control" column set to contain integer value 0. The
   112    125   ** other columns should be set to the values that make up the new record 
   113    126   ** to insert. 
   114    127   **