/ Check-in [baee3556]
Login

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

Overview
Comment:Have OTA maintain a small LRU cache of UPDATE statements. This reduces the amount of time it spends compiling UPDATE if a single data_xxx table contains many different ota_control strings.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | ota-update
Files: files | file ages | folders
SHA1: baee3556ea10d96f1623cf4dce112fa1a1070820
User & Date: dan 2015-04-23 18:14:21
Context
2015-04-23
19:18
Fix a performance problem in calls to sqlite3ota_close() made after the OTA update has been completely applied and checkpointed. check-in: fa62093b user: dan tags: ota-update
18:14
Have OTA maintain a small LRU cache of UPDATE statements. This reduces the amount of time it spends compiling UPDATE if a single data_xxx table contains many different ota_control strings. check-in: baee3556 user: dan tags: ota-update
2015-04-22
11:34
Add comments related to database locking to sqlite3ota.h. check-in: 77242965 user: dan tags: ota-update
Changes
Hide Diffs Side-by-Side Diffs Ignore Whitespace Patch

Added ext/ota/ota13.test.

            1  +# 2015 February 16
            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  +# Test an OTA update that features lots of different ota_control strings
           13  +# for UPDATE statements. This tests OTA's internal UPDATE statement cache.
           14  +#
           15  +
           16  +if {![info exists testdir]} {
           17  +  set testdir [file join [file dirname [info script]] .. .. test]
           18  +}
           19  +source $testdir/tester.tcl
           20  +source $testdir/lock_common.tcl
           21  +set ::testprefix ota13
           22  +
           23  +do_execsql_test 1.0 {
           24  +  CREATE TABLE t1(a PRIMARY KEY, b, c, d, e, f, g, h);
           25  +  WITH ii(i) AS (SELECT 0 UNION ALL SELECT i+1 FROM ii WHERE i<127)
           26  +  INSERT INTO t1 SELECT i, 0, 0, 0, 0, 0, 0, 0 FROM ii;
           27  +}
           28  +
           29  +forcedelete ota.db
           30  +do_execsql_test 1.1 {
           31  +  ATTACH 'ota.db' AS ota;
           32  +  CREATE TABLE ota.data_t1(a, b, c, d, e, f, g, h, ota_control);
           33  +}
           34  +
           35  +do_test 1.2 {
           36  +  for {set i 0} {$i<128} {incr i} {
           37  +    set control "."
           38  +    for {set bit 6} {$bit>=0} {incr bit -1} {
           39  +      if { $i & (1<<$bit) } {
           40  +        append control "x"
           41  +      } else {
           42  +        append control "."
           43  +      }
           44  +    }
           45  +    execsql { INSERT INTO data_t1 VALUES($i, 1, 1, 1, 1, 1, 1, 1, $control) }
           46  +  }
           47  +} {}
           48  +
           49  +do_test 1.3 {
           50  +  sqlite3ota ota test.db ota.db
           51  +  while 1 {
           52  +    set rc [ota step]
           53  +    if {$rc!="SQLITE_OK"} break
           54  +  }
           55  +  ota close
           56  +} {SQLITE_DONE}
           57  +
           58  +do_execsql_test 1.4 {
           59  +  SELECT count(*) FROM t1 WHERE
           60  +  a == ( (b<<6) + (c<<5) + (d<<4) + (e<<3) + (f<<2) + (g<<1) + (h<<0) )
           61  +} {128}
           62  +
           63  +
           64  +finish_test
           65  +

Changes to ext/ota/sqlite3ota.c.

    86     86   #include <unistd.h>
    87     87   
    88     88   #include "sqlite3.h"
    89     89   
    90     90   #if !defined(SQLITE_CORE) || defined(SQLITE_ENABLE_OTA)
    91     91   #include "sqlite3ota.h"
    92     92   
           93  +/* Maximum number of prepared UPDATE statements held by this module */
           94  +#define SQLITE_OTA_UPDATE_CACHESIZE 16
           95  +
    93     96   /*
    94     97   ** Swap two objects of type TYPE.
    95     98   */
    96     99   #if !defined(SQLITE_AMALGAMATION)
    97    100   # define SWAP(TYPE,A,B) {TYPE t=A; A=B; B=t;}
    98    101   #endif
    99    102   
................................................................................
   161    164                                "(k INTEGER PRIMARY KEY, v)"
   162    165   
   163    166   typedef struct OtaFrame OtaFrame;
   164    167   typedef struct OtaObjIter OtaObjIter;
   165    168   typedef struct OtaState OtaState;
   166    169   typedef struct ota_vfs ota_vfs;
   167    170   typedef struct ota_file ota_file;
          171  +typedef struct OtaUpdateStmt OtaUpdateStmt;
   168    172   
   169    173   #if !defined(SQLITE_AMALGAMATION)
   170    174   typedef unsigned int u32;
   171    175   typedef unsigned char u8;
   172    176   typedef sqlite3_int64 i64;
   173    177   #endif
   174    178   
................................................................................
   190    194     char *zIdx;
   191    195     i64 iWalCksum;
   192    196     int nRow;
   193    197     i64 nProgress;
   194    198     u32 iCookie;
   195    199     i64 iOalSz;
   196    200   };
          201  +
          202  +struct OtaUpdateStmt {
          203  +  char *zMask;                    /* Copy of update mask used with pUpdate */
          204  +  sqlite3_stmt *pUpdate;          /* Last update statement (or NULL) */
          205  +  OtaUpdateStmt *pNext;
          206  +};
   197    207   
   198    208   /*
   199    209   ** An iterator of this type is used to iterate through all objects in
   200    210   ** the target database that require updating. For each such table, the
   201    211   ** iterator visits, in order:
   202    212   **
   203    213   **     * the table itself, 
................................................................................
   235    245     int nCol;                       /* Number of columns in current object */
   236    246     sqlite3_stmt *pSelect;          /* Source data */
   237    247     sqlite3_stmt *pInsert;          /* Statement for INSERT operations */
   238    248     sqlite3_stmt *pDelete;          /* Statement for DELETE ops */
   239    249     sqlite3_stmt *pTmpInsert;       /* Insert into ota_tmp_$zTbl */
   240    250   
   241    251     /* Last UPDATE used (for PK b-tree updates only), or NULL. */
   242         -  char *zMask;                    /* Copy of update mask used with pUpdate */
   243         -  sqlite3_stmt *pUpdate;          /* Last update statement (or NULL) */
          252  +  OtaUpdateStmt *pOtaUpdate;
   244    253   };
   245    254   
   246    255   /*
   247    256   ** Values for OtaObjIter.eType
   248    257   **
   249    258   **     0: Table does not exist (error)
   250    259   **     1: Table has an implicit rowid.
................................................................................
   432    441     sqlite3_free(pIter->azTblCol);
   433    442     pIter->azTblCol = 0;
   434    443     pIter->azTblType = 0;
   435    444     pIter->aiSrcOrder = 0;
   436    445     pIter->abTblPk = 0;
   437    446     pIter->abNotNull = 0;
   438    447     pIter->nTblCol = 0;
   439         -  sqlite3_free(pIter->zMask);
   440         -  pIter->zMask = 0;
   441    448     pIter->eType = 0;               /* Invalid value */
   442    449   }
   443    450   
   444    451   /*
   445    452   ** Finalize all statements and free all allocations that are specific to
   446    453   ** the current object (table/index pair).
   447    454   */
   448    455   static void otaObjIterClearStatements(OtaObjIter *pIter){
          456  +  OtaUpdateStmt *pUp;
          457  +
   449    458     sqlite3_finalize(pIter->pSelect);
   450    459     sqlite3_finalize(pIter->pInsert);
   451    460     sqlite3_finalize(pIter->pDelete);
   452         -  sqlite3_finalize(pIter->pUpdate);
   453    461     sqlite3_finalize(pIter->pTmpInsert);
          462  +  pUp = pIter->pOtaUpdate;
          463  +  while( pUp ){
          464  +    OtaUpdateStmt *pTmp = pUp->pNext;
          465  +    sqlite3_finalize(pUp->pUpdate);
          466  +    sqlite3_free(pUp);
          467  +    pUp = pTmp;
          468  +  }
          469  +  
   454    470     pIter->pSelect = 0;
   455    471     pIter->pInsert = 0;
   456    472     pIter->pDelete = 0;
   457         -  pIter->pUpdate = 0;
          473  +  pIter->pOtaUpdate = 0;
   458    474     pIter->pTmpInsert = 0;
   459    475     pIter->nCol = 0;
   460    476   }
   461    477   
   462    478   /*
   463    479   ** Clean up any resources allocated as part of the iterator object passed
   464    480   ** as the only argument.
................................................................................
  1705   1721                 zWrite, zTbl, zNewlist
  1706   1722             );
  1707   1723           }
  1708   1724   
  1709   1725           otaObjIterPrepareTmpInsert(p, pIter, zCollist, zOtaRowid);
  1710   1726         }
  1711   1727   
  1712         -      /* Allocate space required for the zMask field. */
  1713         -      pIter->zMask = (char*)otaMalloc(p, pIter->nTblCol+1);
  1714         -
  1715   1728         sqlite3_free(zWhere);
  1716   1729         sqlite3_free(zOldlist);
  1717   1730         sqlite3_free(zNewlist);
  1718   1731         sqlite3_free(zBindings);
  1719   1732       }
  1720   1733       sqlite3_free(zCollist);
  1721   1734       sqlite3_free(zLimit);
................................................................................
  1725   1738   }
  1726   1739   
  1727   1740   /*
  1728   1741   ** Set output variable *ppStmt to point to an UPDATE statement that may
  1729   1742   ** be used to update the imposter table for the main table b-tree of the
  1730   1743   ** table object that pIter currently points to, assuming that the 
  1731   1744   ** ota_control column of the data_xyz table contains zMask.
         1745  +** 
         1746  +** If the zMask string does not specify any columns to update, then this
         1747  +** is not an error. Output variable *ppStmt is set to NULL in this case.
  1732   1748   */
  1733   1749   static int otaGetUpdateStmt(
  1734   1750     sqlite3ota *p,                  /* OTA handle */
  1735   1751     OtaObjIter *pIter,              /* Object iterator */
  1736   1752     const char *zMask,              /* ota_control value ('x.x.') */
  1737   1753     sqlite3_stmt **ppStmt           /* OUT: UPDATE statement handle */
  1738   1754   ){
  1739         -  if( pIter->pUpdate && strcmp(zMask, pIter->zMask)==0 ){
  1740         -    *ppStmt = pIter->pUpdate;
         1755  +  OtaUpdateStmt **pp;
         1756  +  OtaUpdateStmt *pUp = 0;
         1757  +  int nUp = 0;
         1758  +
         1759  +  /* In case an error occurs */
         1760  +  *ppStmt = 0;
         1761  +
         1762  +  /* Search for an existing statement. If one is found, shift it to the front
         1763  +  ** of the LRU queue and return immediately. Otherwise, leave nUp pointing
         1764  +  ** to the number of statements currently in the cache and pUp to the
         1765  +  ** last object in the list.  */
         1766  +  for(pp=&pIter->pOtaUpdate; *pp; pp=&((*pp)->pNext)){
         1767  +    pUp = *pp;
         1768  +    if( strcmp(pUp->zMask, zMask)==0 ){
         1769  +      *pp = pUp->pNext;
         1770  +      pUp->pNext = pIter->pOtaUpdate;
         1771  +      pIter->pOtaUpdate = pUp;
         1772  +      *ppStmt = pUp->pUpdate; 
         1773  +      return SQLITE_OK;
         1774  +    }
         1775  +    nUp++;
         1776  +  }
         1777  +  assert( pUp==0 || pUp->pNext==0 );
         1778  +
         1779  +  if( nUp>=SQLITE_OTA_UPDATE_CACHESIZE ){
         1780  +    for(pp=&pIter->pOtaUpdate; *pp!=pUp; pp=&((*pp)->pNext));
         1781  +    *pp = 0;
         1782  +    sqlite3_finalize(pUp->pUpdate);
         1783  +    pUp->pUpdate = 0;
  1741   1784     }else{
         1785  +    pUp = (OtaUpdateStmt*)otaMalloc(p, sizeof(OtaUpdateStmt)+pIter->nTblCol+1);
         1786  +  }
         1787  +
         1788  +  if( pUp ){
  1742   1789       char *zWhere = otaObjIterGetWhere(p, pIter);
  1743   1790       char *zSet = otaObjIterGetSetlist(p, pIter, zMask);
  1744   1791       char *zUpdate = 0;
  1745         -    sqlite3_finalize(pIter->pUpdate);
  1746         -    pIter->pUpdate = 0;
  1747         -    if( p->rc==SQLITE_OK ){
         1792  +
         1793  +    pUp->zMask = (char*)&pUp[1];
         1794  +    memcpy(pUp->zMask, zMask, pIter->nTblCol);
         1795  +    pUp->pNext = pIter->pOtaUpdate;
         1796  +    pIter->pOtaUpdate = pUp;
         1797  +
         1798  +    if( zSet ){
  1748   1799         const char *zPrefix = "";
  1749   1800   
  1750   1801         if( pIter->eType!=OTA_PK_VTAB ) zPrefix = "ota_imp_";
  1751   1802         zUpdate = sqlite3_mprintf("UPDATE \"%s%w\" SET %s WHERE %s", 
  1752   1803             zPrefix, pIter->zTbl, zSet, zWhere
  1753   1804         );
  1754   1805         p->rc = prepareFreeAndCollectError(
  1755         -          p->dbMain, &pIter->pUpdate, &p->zErrmsg, zUpdate
         1806  +          p->dbMain, &pUp->pUpdate, &p->zErrmsg, zUpdate
  1756   1807         );
  1757         -      *ppStmt = pIter->pUpdate;
  1758         -    }
  1759         -    if( p->rc==SQLITE_OK ){
  1760         -      memcpy(pIter->zMask, zMask, pIter->nTblCol);
         1808  +      *ppStmt = pUp->pUpdate;
  1761   1809       }
  1762   1810       sqlite3_free(zWhere);
  1763   1811       sqlite3_free(zSet);
  1764   1812     }
         1813  +
  1765   1814     return p->rc;
  1766   1815   }
  1767   1816   
  1768   1817   static sqlite3 *otaOpenDbhandle(sqlite3ota *p, const char *zName){
  1769   1818     sqlite3 *db = 0;
  1770   1819     if( p->rc==SQLITE_OK ){
  1771   1820       const int flags = SQLITE_OPEN_READWRITE|SQLITE_OPEN_CREATE|SQLITE_OPEN_URI;
................................................................................
  1853   1902   ** Return the current wal-index header checksum for the target database 
  1854   1903   ** as a 64-bit integer.
  1855   1904   **
  1856   1905   ** The checksum is store in the first page of xShmMap memory as an 8-byte 
  1857   1906   ** blob starting at byte offset 40.
  1858   1907   */
  1859   1908   static i64 otaShmChecksum(sqlite3ota *p){
  1860         -  i64 iRet;
         1909  +  i64 iRet = 0;
  1861   1910     if( p->rc==SQLITE_OK ){
  1862   1911       sqlite3_file *pDb = p->pTargetFd->pReal;
  1863   1912       u32 volatile *ptr;
  1864   1913       p->rc = pDb->pMethods->xShmMap(pDb, 0, 32*1024, 0, (void volatile**)&ptr);
  1865   1914       if( p->rc==SQLITE_OK ){
  1866   1915         iRet = ((i64)ptr[10] << 32) + ptr[11];
  1867   1916       }