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 |
Timelines: | family | ancestors | descendants | both | ota-update |
Files: | files | file ages | folders |
SHA1: |
baee3556ea10d96f1623cf4dce112fa1 |
User & Date: | dan 2015-04-23 18:14:21.590 |
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: fa62093b25 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: baee3556ea user: dan tags: ota-update) | |
2015-04-22
| ||
11:34 | Add comments related to database locking to sqlite3ota.h. (check-in: 77242965e7 user: dan tags: ota-update) | |
Changes
Added ext/ota/ota13.test.
> > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > | 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 | # 2015 February 16 # # The author disclaims copyright to this source code. In place of # a legal notice, here is a blessing: # # May you do good and not evil. # May you find forgiveness for yourself and forgive others. # May you share freely, never taking more than you give. # #*********************************************************************** # # Test an OTA update that features lots of different ota_control strings # for UPDATE statements. This tests OTA's internal UPDATE statement cache. # if {![info exists testdir]} { set testdir [file join [file dirname [info script]] .. .. test] } source $testdir/tester.tcl source $testdir/lock_common.tcl set ::testprefix ota13 do_execsql_test 1.0 { CREATE TABLE t1(a PRIMARY KEY, b, c, d, e, f, g, h); WITH ii(i) AS (SELECT 0 UNION ALL SELECT i+1 FROM ii WHERE i<127) INSERT INTO t1 SELECT i, 0, 0, 0, 0, 0, 0, 0 FROM ii; } forcedelete ota.db do_execsql_test 1.1 { ATTACH 'ota.db' AS ota; CREATE TABLE ota.data_t1(a, b, c, d, e, f, g, h, ota_control); } do_test 1.2 { for {set i 0} {$i<128} {incr i} { set control "." for {set bit 6} {$bit>=0} {incr bit -1} { if { $i & (1<<$bit) } { append control "x" } else { append control "." } } execsql { INSERT INTO data_t1 VALUES($i, 1, 1, 1, 1, 1, 1, 1, $control) } } } {} do_test 1.3 { sqlite3ota ota test.db ota.db while 1 { set rc [ota step] if {$rc!="SQLITE_OK"} break } ota close } {SQLITE_DONE} do_execsql_test 1.4 { SELECT count(*) FROM t1 WHERE a == ( (b<<6) + (c<<5) + (d<<4) + (e<<3) + (f<<2) + (g<<1) + (h<<0) ) } {128} finish_test |
Changes to ext/ota/sqlite3ota.c.
︙ | ︙ | |||
86 87 88 89 90 91 92 93 94 95 96 97 98 99 | #include <unistd.h> #include "sqlite3.h" #if !defined(SQLITE_CORE) || defined(SQLITE_ENABLE_OTA) #include "sqlite3ota.h" /* ** Swap two objects of type TYPE. */ #if !defined(SQLITE_AMALGAMATION) # define SWAP(TYPE,A,B) {TYPE t=A; A=B; B=t;} #endif | > > > | 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 | #include <unistd.h> #include "sqlite3.h" #if !defined(SQLITE_CORE) || defined(SQLITE_ENABLE_OTA) #include "sqlite3ota.h" /* Maximum number of prepared UPDATE statements held by this module */ #define SQLITE_OTA_UPDATE_CACHESIZE 16 /* ** Swap two objects of type TYPE. */ #if !defined(SQLITE_AMALGAMATION) # define SWAP(TYPE,A,B) {TYPE t=A; A=B; B=t;} #endif |
︙ | ︙ | |||
161 162 163 164 165 166 167 168 169 170 171 172 173 174 | "(k INTEGER PRIMARY KEY, v)" typedef struct OtaFrame OtaFrame; typedef struct OtaObjIter OtaObjIter; typedef struct OtaState OtaState; typedef struct ota_vfs ota_vfs; typedef struct ota_file ota_file; #if !defined(SQLITE_AMALGAMATION) typedef unsigned int u32; typedef unsigned char u8; typedef sqlite3_int64 i64; #endif | > | 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 | "(k INTEGER PRIMARY KEY, v)" typedef struct OtaFrame OtaFrame; typedef struct OtaObjIter OtaObjIter; typedef struct OtaState OtaState; typedef struct ota_vfs ota_vfs; typedef struct ota_file ota_file; typedef struct OtaUpdateStmt OtaUpdateStmt; #if !defined(SQLITE_AMALGAMATION) typedef unsigned int u32; typedef unsigned char u8; typedef sqlite3_int64 i64; #endif |
︙ | ︙ | |||
190 191 192 193 194 195 196 197 198 199 200 201 202 203 | char *zIdx; i64 iWalCksum; int nRow; i64 nProgress; u32 iCookie; i64 iOalSz; }; /* ** An iterator of this type is used to iterate through all objects in ** the target database that require updating. For each such table, the ** iterator visits, in order: ** ** * the table itself, | > > > > > > | 194 195 196 197 198 199 200 201 202 203 204 205 206 207 208 209 210 211 212 213 | char *zIdx; i64 iWalCksum; int nRow; i64 nProgress; u32 iCookie; i64 iOalSz; }; struct OtaUpdateStmt { char *zMask; /* Copy of update mask used with pUpdate */ sqlite3_stmt *pUpdate; /* Last update statement (or NULL) */ OtaUpdateStmt *pNext; }; /* ** An iterator of this type is used to iterate through all objects in ** the target database that require updating. For each such table, the ** iterator visits, in order: ** ** * the table itself, |
︙ | ︙ | |||
235 236 237 238 239 240 241 | int nCol; /* Number of columns in current object */ sqlite3_stmt *pSelect; /* Source data */ sqlite3_stmt *pInsert; /* Statement for INSERT operations */ sqlite3_stmt *pDelete; /* Statement for DELETE ops */ sqlite3_stmt *pTmpInsert; /* Insert into ota_tmp_$zTbl */ /* Last UPDATE used (for PK b-tree updates only), or NULL. */ | < | | 245 246 247 248 249 250 251 252 253 254 255 256 257 258 259 | int nCol; /* Number of columns in current object */ sqlite3_stmt *pSelect; /* Source data */ sqlite3_stmt *pInsert; /* Statement for INSERT operations */ sqlite3_stmt *pDelete; /* Statement for DELETE ops */ sqlite3_stmt *pTmpInsert; /* Insert into ota_tmp_$zTbl */ /* Last UPDATE used (for PK b-tree updates only), or NULL. */ OtaUpdateStmt *pOtaUpdate; }; /* ** Values for OtaObjIter.eType ** ** 0: Table does not exist (error) ** 1: Table has an implicit rowid. |
︙ | ︙ | |||
432 433 434 435 436 437 438 | sqlite3_free(pIter->azTblCol); pIter->azTblCol = 0; pIter->azTblType = 0; pIter->aiSrcOrder = 0; pIter->abTblPk = 0; pIter->abNotNull = 0; pIter->nTblCol = 0; | < < > > | > > > | > > > > | | 441 442 443 444 445 446 447 448 449 450 451 452 453 454 455 456 457 458 459 460 461 462 463 464 465 466 467 468 469 470 471 472 473 474 475 476 477 478 479 480 | sqlite3_free(pIter->azTblCol); pIter->azTblCol = 0; pIter->azTblType = 0; pIter->aiSrcOrder = 0; pIter->abTblPk = 0; pIter->abNotNull = 0; pIter->nTblCol = 0; pIter->eType = 0; /* Invalid value */ } /* ** Finalize all statements and free all allocations that are specific to ** the current object (table/index pair). */ static void otaObjIterClearStatements(OtaObjIter *pIter){ OtaUpdateStmt *pUp; sqlite3_finalize(pIter->pSelect); sqlite3_finalize(pIter->pInsert); sqlite3_finalize(pIter->pDelete); sqlite3_finalize(pIter->pTmpInsert); pUp = pIter->pOtaUpdate; while( pUp ){ OtaUpdateStmt *pTmp = pUp->pNext; sqlite3_finalize(pUp->pUpdate); sqlite3_free(pUp); pUp = pTmp; } pIter->pSelect = 0; pIter->pInsert = 0; pIter->pDelete = 0; pIter->pOtaUpdate = 0; pIter->pTmpInsert = 0; pIter->nCol = 0; } /* ** Clean up any resources allocated as part of the iterator object passed ** as the only argument. |
︙ | ︙ | |||
1705 1706 1707 1708 1709 1710 1711 | zWrite, zTbl, zNewlist ); } otaObjIterPrepareTmpInsert(p, pIter, zCollist, zOtaRowid); } | < < < > > > > > > | > | > > > > > > > > > > > > > > > > > > > > > > > > > > > | > > > | | > | | < < < > | 1721 1722 1723 1724 1725 1726 1727 1728 1729 1730 1731 1732 1733 1734 1735 1736 1737 1738 1739 1740 1741 1742 1743 1744 1745 1746 1747 1748 1749 1750 1751 1752 1753 1754 1755 1756 1757 1758 1759 1760 1761 1762 1763 1764 1765 1766 1767 1768 1769 1770 1771 1772 1773 1774 1775 1776 1777 1778 1779 1780 1781 1782 1783 1784 1785 1786 1787 1788 1789 1790 1791 1792 1793 1794 1795 1796 1797 1798 1799 1800 1801 1802 1803 1804 1805 1806 1807 1808 1809 1810 1811 1812 1813 1814 1815 1816 1817 1818 1819 1820 | zWrite, zTbl, zNewlist ); } otaObjIterPrepareTmpInsert(p, pIter, zCollist, zOtaRowid); } sqlite3_free(zWhere); sqlite3_free(zOldlist); sqlite3_free(zNewlist); sqlite3_free(zBindings); } sqlite3_free(zCollist); sqlite3_free(zLimit); } return p->rc; } /* ** Set output variable *ppStmt to point to an UPDATE statement that may ** be used to update the imposter table for the main table b-tree of the ** table object that pIter currently points to, assuming that the ** ota_control column of the data_xyz table contains zMask. ** ** If the zMask string does not specify any columns to update, then this ** is not an error. Output variable *ppStmt is set to NULL in this case. */ static int otaGetUpdateStmt( sqlite3ota *p, /* OTA handle */ OtaObjIter *pIter, /* Object iterator */ const char *zMask, /* ota_control value ('x.x.') */ sqlite3_stmt **ppStmt /* OUT: UPDATE statement handle */ ){ OtaUpdateStmt **pp; OtaUpdateStmt *pUp = 0; int nUp = 0; /* In case an error occurs */ *ppStmt = 0; /* Search for an existing statement. If one is found, shift it to the front ** of the LRU queue and return immediately. Otherwise, leave nUp pointing ** to the number of statements currently in the cache and pUp to the ** last object in the list. */ for(pp=&pIter->pOtaUpdate; *pp; pp=&((*pp)->pNext)){ pUp = *pp; if( strcmp(pUp->zMask, zMask)==0 ){ *pp = pUp->pNext; pUp->pNext = pIter->pOtaUpdate; pIter->pOtaUpdate = pUp; *ppStmt = pUp->pUpdate; return SQLITE_OK; } nUp++; } assert( pUp==0 || pUp->pNext==0 ); if( nUp>=SQLITE_OTA_UPDATE_CACHESIZE ){ for(pp=&pIter->pOtaUpdate; *pp!=pUp; pp=&((*pp)->pNext)); *pp = 0; sqlite3_finalize(pUp->pUpdate); pUp->pUpdate = 0; }else{ pUp = (OtaUpdateStmt*)otaMalloc(p, sizeof(OtaUpdateStmt)+pIter->nTblCol+1); } if( pUp ){ char *zWhere = otaObjIterGetWhere(p, pIter); char *zSet = otaObjIterGetSetlist(p, pIter, zMask); char *zUpdate = 0; pUp->zMask = (char*)&pUp[1]; memcpy(pUp->zMask, zMask, pIter->nTblCol); pUp->pNext = pIter->pOtaUpdate; pIter->pOtaUpdate = pUp; if( zSet ){ const char *zPrefix = ""; if( pIter->eType!=OTA_PK_VTAB ) zPrefix = "ota_imp_"; zUpdate = sqlite3_mprintf("UPDATE \"%s%w\" SET %s WHERE %s", zPrefix, pIter->zTbl, zSet, zWhere ); p->rc = prepareFreeAndCollectError( p->dbMain, &pUp->pUpdate, &p->zErrmsg, zUpdate ); *ppStmt = pUp->pUpdate; } sqlite3_free(zWhere); sqlite3_free(zSet); } return p->rc; } static sqlite3 *otaOpenDbhandle(sqlite3ota *p, const char *zName){ sqlite3 *db = 0; if( p->rc==SQLITE_OK ){ const int flags = SQLITE_OPEN_READWRITE|SQLITE_OPEN_CREATE|SQLITE_OPEN_URI; |
︙ | ︙ | |||
1853 1854 1855 1856 1857 1858 1859 | ** Return the current wal-index header checksum for the target database ** as a 64-bit integer. ** ** The checksum is store in the first page of xShmMap memory as an 8-byte ** blob starting at byte offset 40. */ static i64 otaShmChecksum(sqlite3ota *p){ | | | 1902 1903 1904 1905 1906 1907 1908 1909 1910 1911 1912 1913 1914 1915 1916 | ** Return the current wal-index header checksum for the target database ** as a 64-bit integer. ** ** The checksum is store in the first page of xShmMap memory as an 8-byte ** blob starting at byte offset 40. */ static i64 otaShmChecksum(sqlite3ota *p){ i64 iRet = 0; if( p->rc==SQLITE_OK ){ sqlite3_file *pDb = p->pTargetFd->pReal; u32 volatile *ptr; p->rc = pDb->pMethods->xShmMap(pDb, 0, 32*1024, 0, (void volatile**)&ptr); if( p->rc==SQLITE_OK ){ iRet = ((i64)ptr[10] << 32) + ptr[11]; } |
︙ | ︙ |