Many hyperlinks are disabled.
Use anonymous login
to enable hyperlinks.
Overview
Comment: | Add support for update statements to sqlite3ota.c. |
---|---|
Downloads: | Tarball | ZIP archive |
Timelines: | family | ancestors | descendants | both | ota-update |
Files: | files | file ages | folders |
SHA1: |
e109b27e4d66b83e1a804e7556d9c91a |
User & Date: | dan 2014-09-08 17:50:35.158 |
Context
2014-09-15
| ||
10:44 | Add OP_Affinity opcodes to the VMs generated by sqlite3_index_writer(). (check-in: b9b38cb8e2 user: dan tags: ota-update) | |
2014-09-08
| ||
17:50 | Add support for update statements to sqlite3ota.c. (check-in: e109b27e4d user: dan tags: ota-update) | |
2014-09-06
| ||
20:19 | Add support for delete operations to the ota extension. (check-in: f988234ba5 user: dan tags: ota-update) | |
Changes
Changes to ext/ota/ota1.test.
︙ | ︙ | |||
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 | return $filename } # Create a simple OTA database. That expects to write to a table: # # CREATE TABLE t1(a INTEGER PRIMARY KEY, b, c); # proc create_ota4 {filename} { forcedelete $filename sqlite3 ota1 $filename ota1 eval { CREATE TABLE data_t1(a, b, c, ota_control); INSERT INTO data_t1 VALUES(1, 2, 3, 0); INSERT INTO data_t1 VALUES(2, NULL, 5, 1); INSERT INTO data_t1 VALUES(3, 8, 9, 0); INSERT INTO data_t1 VALUES(4, NULL, 11, 1); } ota1 close return $filename } # Run the OTA in file $ota on target database $target until completion. # proc run_ota {target ota} { sqlite3ota ota $target $ota while { [ota step]=="SQLITE_OK" } {} ota close | > > > > > > > > > > > > > > > > > > > > > | 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 66 67 68 69 70 71 72 73 74 75 76 77 78 79 | return $filename } # Create a simple OTA database. That expects to write to a table: # # CREATE TABLE t1(a INTEGER PRIMARY KEY, b, c); # # This OTA includes both insert and delete operations. # proc create_ota4 {filename} { forcedelete $filename sqlite3 ota1 $filename ota1 eval { CREATE TABLE data_t1(a, b, c, ota_control); INSERT INTO data_t1 VALUES(1, 2, 3, 0); INSERT INTO data_t1 VALUES(2, NULL, 5, 1); INSERT INTO data_t1 VALUES(3, 8, 9, 0); INSERT INTO data_t1 VALUES(4, NULL, 11, 1); } ota1 close return $filename } # Create a simple OTA database. That expects to write to a table: # # CREATE TABLE t1(a INTEGER PRIMARY KEY, b, c, d); # # This OTA includes update statements. # proc create_ota5 {filename} { forcedelete $filename sqlite3 ota5 $filename ota5 eval { CREATE TABLE data_t1(a, b, c, d, ota_control); INSERT INTO data_t1 VALUES(1, NULL, NULL, 5, '...x'); -- SET d = 5 INSERT INTO data_t1 VALUES(2, NULL, 10, 5, '..xx'); -- SET c=10, d = 5 INSERT INTO data_t1 VALUES(3, 11, NULL, NULL, '.x..'); -- SET b=11 } ota5 close return $filename } # Run the OTA in file $ota on target database $target until completion. # proc run_ota {target ota} { sqlite3ota ota $target $ota while { [ota step]=="SQLITE_OK" } {} ota close |
︙ | ︙ | |||
220 221 222 223 224 225 226 | 6 hello xyz } do_execsql_test 4.$tn2.$tn.3 { PRAGMA integrity_check } ok } } | > | > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > | 241 242 243 244 245 246 247 248 249 250 251 252 253 254 255 256 257 258 259 260 261 262 263 264 265 266 267 268 269 270 271 272 273 274 275 276 277 278 279 280 281 282 283 284 285 286 287 288 289 290 291 | 6 hello xyz } do_execsql_test 4.$tn2.$tn.3 { PRAGMA integrity_check } ok } } #------------------------------------------------------------------------- # foreach {tn2 cmd} {1 run_ota 2 step_ota} { foreach {tn schema} { 1 { CREATE TABLE t1(a INTEGER PRIMARY KEY, b, c, d); } 2 { CREATE TABLE t1(a INTEGER PRIMARY KEY, b, c, d); CREATE INDEX i1 ON t1(d); CREATE INDEX i2 ON t1(d, c); CREATE INDEX i3 ON t1(d, c, b); CREATE INDEX i4 ON t1(b); CREATE INDEX i5 ON t1(c); CREATE INDEX i6 ON t1(c, b); } } { reset_db execsql $schema execsql { INSERT INTO t1 VALUES(1, 2, 3, 4); INSERT INTO t1 VALUES(2, 5, 6, 7); INSERT INTO t1 VALUES(3, 8, 9, 10); } do_test 5.$tn2.$tn.1 { create_ota5 ota.db $cmd test.db ota.db } {SQLITE_DONE} do_execsql_test 5.$tn2.$tn.2 { SELECT * FROM t1 ORDER BY a ASC; } { 1 2 3 5 2 5 10 5 3 11 9 10 } do_execsql_test 5.$tn2.$tn.3 { PRAGMA integrity_check } ok } } finish_test |
Changes to ext/ota/sqlite3ota.c.
︙ | ︙ | |||
74 75 76 77 78 79 80 81 82 83 84 85 86 87 | int iVisit; /* Number of points visited, incl. current */ /* Statements created by otaObjIterPrepareAll() */ 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 */ }; /* ** OTA handle. */ struct sqlite3ota { sqlite3 *db; /* "main" -> target db, "ota" -> ota db */ | > > > > | 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 | int iVisit; /* Number of points visited, incl. current */ /* Statements created by otaObjIterPrepareAll() */ 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 */ /* Last UPDATE used (for PK b-tree updates only), or NULL. */ char *zMask; /* Copy of update mask used with pUpdate */ sqlite3_stmt *pUpdate; /* Last update statement (or NULL) */ }; /* ** OTA handle. */ struct sqlite3ota { sqlite3 *db; /* "main" -> target db, "ota" -> ota db */ |
︙ | ︙ | |||
175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 | sqlite3_free(pIter->azTblCol[i]); } sqlite3_free(pIter->azTblCol); sqlite3_free(pIter->abTblPk); pIter->azTblCol = 0; pIter->abTblPk = 0; pIter->nTblCol = 0; } /* ** Clean up any resources allocated as part of the iterator object passed ** as the only argument. */ static void otaObjIterFinalize(OtaObjIter *pIter){ sqlite3_finalize(pIter->pTblIter); sqlite3_finalize(pIter->pIdxIter); | > > > > > > > > > > > > > > > > > > > < < < < < < < < < | | 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 194 195 196 197 198 199 200 201 202 203 204 205 206 207 208 209 210 211 212 213 214 215 216 217 218 219 220 221 222 223 224 225 226 227 228 229 230 231 232 233 234 235 236 237 238 | sqlite3_free(pIter->azTblCol[i]); } sqlite3_free(pIter->azTblCol); sqlite3_free(pIter->abTblPk); pIter->azTblCol = 0; pIter->abTblPk = 0; pIter->nTblCol = 0; sqlite3_free(pIter->zMask); pIter->zMask = 0; } /* ** Finalize all statements and free all allocations that are specific to ** the current object (table/index pair). */ static void otaObjIterClearStatements(OtaObjIter *pIter){ sqlite3_finalize(pIter->pSelect); sqlite3_finalize(pIter->pInsert); sqlite3_finalize(pIter->pDelete); sqlite3_finalize(pIter->pUpdate); pIter->pSelect = 0; pIter->pInsert = 0; pIter->pDelete = 0; pIter->pUpdate = 0; pIter->nCol = 0; } /* ** Clean up any resources allocated as part of the iterator object passed ** as the only argument. */ static void otaObjIterFinalize(OtaObjIter *pIter){ otaObjIterClearStatements(pIter); sqlite3_finalize(pIter->pTblIter); sqlite3_finalize(pIter->pIdxIter); otaObjIterFreeCols(pIter); memset(pIter, 0, sizeof(OtaObjIter)); } /* ** Advance the iterator to the next position. ** ** If no error occurs, SQLITE_OK is returned and the iterator is left ** pointing to the next entry. Otherwise, an error code and message is ** left in the OTA handle passed as the first argument. A copy of the ** error code is returned. */ static int otaObjIterNext(sqlite3ota *p, OtaObjIter *pIter){ int rc = p->rc; if( rc==SQLITE_OK ){ /* Free any SQLite statements used while processing the previous object */ otaObjIterClearStatements(pIter); if( pIter->bCleanup ){ otaObjIterFreeCols(pIter); pIter->bCleanup = 0; rc = sqlite3_step(pIter->pTblIter); if( rc!=SQLITE_ROW ){ rc = sqlite3_reset(pIter->pTblIter); |
︙ | ︙ | |||
408 409 410 411 412 413 414 | } } return zList; } static char *otaObjIterGetOldlist( sqlite3ota *p, | | > | | | > | > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > | 422 423 424 425 426 427 428 429 430 431 432 433 434 435 436 437 438 439 440 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 481 482 483 484 485 486 487 488 489 490 491 492 493 494 495 496 497 498 499 500 501 502 503 504 505 506 507 508 509 510 511 | } } return zList; } static char *otaObjIterGetOldlist( sqlite3ota *p, OtaObjIter *pIter, const char *zObj ){ char *zList = 0; if( p->rc==SQLITE_OK ){ const char *zS = ""; int i; for(i=0; i<pIter->nTblCol; i++){ zList = sqlite3_mprintf("%z%s%s.%s", zList, zS, zObj, pIter->azTblCol[i]); zS = ", "; if( zList==0 ){ p->rc = SQLITE_NOMEM; break; } } } return zList; } static char *otaObjIterGetWhere( sqlite3ota *p, OtaObjIter *pIter ){ char *zList = 0; if( p->rc==SQLITE_OK ){ const char *zSep = ""; int i; for(i=0; i<pIter->nTblCol; i++){ if( pIter->abTblPk[i] ){ const char *zCol = pIter->azTblCol[i]; zList = sqlite3_mprintf("%z%s%s=?%d", zList, zSep, zCol, i+1); zSep = " AND "; if( zList==0 ){ p->rc = SQLITE_NOMEM; break; } } } } return zList; } /* ** The SELECT statement iterating through the keys for the current object ** (p->objiter.pSelect) currently points to a valid row. However, there ** is something wrong with the ota_control value in the ota_control value ** stored in the (p->nCol+1)'th column. Set the error code and error message ** of the OTA handle to something reflecting this. */ static void otaBadControlError(sqlite3ota *p){ p->rc = SQLITE_ERROR; p->zErrmsg = sqlite3_mprintf("Invalid ota_control value"); } static char *otaObjIterGetSetlist( sqlite3ota *p, OtaObjIter *pIter, const char *zMask ){ char *zList = 0; if( p->rc==SQLITE_OK ){ int i; if( strlen(zMask)!=pIter->nTblCol ){ otaBadControlError(p); }else{ const char *zSep = ""; for(i=0; i<pIter->nTblCol; i++){ if( zMask[i]=='x' ){ zList = sqlite3_mprintf("%z%s%s=?%d", zList, zSep, pIter->azTblCol[i], i+1 ); zSep = ", "; } } } } return zList; } static char *otaObjIterGetBindlist(sqlite3ota *p, int nBind){ char *zRet = 0; if( p->rc==SQLITE_OK ){ |
︙ | ︙ | |||
509 510 511 512 513 514 515 516 517 518 519 520 521 522 523 524 525 526 527 | /* Create the SELECT statement to read keys in sorted order */ zCollist = otaObjIterGetCollist(p, pIter, pIter->nCol, aiCol); if( p->rc==SQLITE_OK ){ p->rc = prepareFreeAndCollectError(p->db, &pIter->pSelect, pz, sqlite3_mprintf( "SELECT %s, ota_control FROM ota.'data_%q' " "UNION ALL " "SELECT %s, ota_control FROM ota.'ota_tmp_%q' " "ORDER BY %s%s", zCollist, pIter->zTbl, zCollist, pIter->zTbl, zCollist, zLimit ) ); } }else{ char *zBindings = otaObjIterGetBindlist(p, pIter->nTblCol); char *zWhere = otaObjIterGetWhere(p, pIter); | > | > | 563 564 565 566 567 568 569 570 571 572 573 574 575 576 577 578 579 580 581 582 583 584 585 586 587 588 589 590 591 | /* Create the SELECT statement to read keys in sorted order */ zCollist = otaObjIterGetCollist(p, pIter, pIter->nCol, aiCol); if( p->rc==SQLITE_OK ){ p->rc = prepareFreeAndCollectError(p->db, &pIter->pSelect, pz, sqlite3_mprintf( "SELECT %s, ota_control FROM ota.'data_%q' " "WHERE typeof(ota_control)='integer' AND ota_control!=1 " "UNION ALL " "SELECT %s, ota_control FROM ota.'ota_tmp_%q' " "ORDER BY %s%s", zCollist, pIter->zTbl, zCollist, pIter->zTbl, zCollist, zLimit ) ); } }else{ char *zBindings = otaObjIterGetBindlist(p, pIter->nTblCol); char *zWhere = otaObjIterGetWhere(p, pIter); char *zOldlist = otaObjIterGetOldlist(p, pIter, "old"); char *zNewlist = otaObjIterGetOldlist(p, pIter, "new"); zCollist = otaObjIterGetCollist(p, pIter, pIter->nTblCol, 0); pIter->nCol = pIter->nTblCol; /* Create the SELECT statement to read keys from data_xxx */ if( p->rc==SQLITE_OK ){ p->rc = prepareFreeAndCollectError(p->db, &pIter->pSelect, pz, sqlite3_mprintf( |
︙ | ︙ | |||
557 558 559 560 561 562 563 564 565 566 567 | ); } if( p->rc==SQLITE_OK ){ otaMPrintfExec(p, "CREATE TABLE IF NOT EXISTS ota.'ota_tmp_%q' AS " "SELECT * FROM ota.'data_%q' WHERE 0;" "CREATE TEMP TRIGGER ota_delete_%q BEFORE DELETE ON main.%Q " "BEGIN " " INSERT INTO 'ota_tmp_%q'(ota_control, %s) VALUES(2, %s);" "END;" | > > > > > > > > > > > > | | > > > > > > > > > > > > > > > | < | | | | | > > > > > > | | > | | > > > > > > > > > > > > > > | 613 614 615 616 617 618 619 620 621 622 623 624 625 626 627 628 629 630 631 632 633 634 635 636 637 638 639 640 641 642 643 644 645 646 647 648 649 650 651 652 653 654 655 656 657 658 659 660 661 662 663 664 665 666 667 668 669 670 671 672 673 674 675 676 677 678 679 680 681 682 683 684 685 686 687 688 689 690 691 692 693 694 695 696 697 698 699 700 701 702 703 704 705 706 707 708 | ); } if( p->rc==SQLITE_OK ){ otaMPrintfExec(p, "CREATE TABLE IF NOT EXISTS ota.'ota_tmp_%q' AS " "SELECT * FROM ota.'data_%q' WHERE 0;" "CREATE TEMP TRIGGER ota_delete_%q BEFORE DELETE ON main.%Q " "BEGIN " " INSERT INTO 'ota_tmp_%q'(ota_control, %s) VALUES(2, %s);" "END;" "CREATE TEMP TRIGGER ota_update1_%q BEFORE UPDATE ON main.%Q " "BEGIN " " INSERT INTO 'ota_tmp_%q'(ota_control, %s) VALUES(2, %s);" "END;" "CREATE TEMP TRIGGER ota_update2_%q AFTER UPDATE ON main.%Q " "BEGIN " " INSERT INTO 'ota_tmp_%q'(ota_control, %s) VALUES(3, %s);" "END;" , pIter->zTbl, pIter->zTbl, pIter->zTbl, pIter->zTbl, pIter->zTbl, zCollist, zOldlist, pIter->zTbl, pIter->zTbl, pIter->zTbl, zCollist, zOldlist, pIter->zTbl, pIter->zTbl, pIter->zTbl, zCollist, zNewlist ); } /* Allocate space required for the zMask field. */ if( p->rc==SQLITE_OK ){ int nMask = pIter->nTblCol+1; pIter->zMask = (char*)sqlite3_malloc(nMask); if( pIter->zMask==0 ){ p->rc = SQLITE_NOMEM; }else{ memset(pIter->zMask, 0, nMask); } } sqlite3_free(zWhere); sqlite3_free(zOldlist); sqlite3_free(zNewlist); sqlite3_free(zBindings); } sqlite3_free(zCollist); sqlite3_free(zLimit); } return p->rc; } #define OTA_INSERT 1 #define OTA_DELETE 2 #define OTA_IDX_DELETE 3 #define OTA_IDX_INSERT 4 #define OTA_UPDATE 5 static int otaGetUpdateStmt( sqlite3ota *p, OtaObjIter *pIter, const char *zMask, sqlite3_stmt **ppStmt ){ if( pIter->pUpdate && strcmp(zMask, pIter->zMask)==0 ){ *ppStmt = pIter->pUpdate; }else{ char *zWhere = otaObjIterGetWhere(p, pIter); char *zSet = otaObjIterGetSetlist(p, pIter, zMask); char *zUpdate = 0; sqlite3_finalize(pIter->pUpdate); pIter->pUpdate = 0; if( p->rc==SQLITE_OK ){ zUpdate = sqlite3_mprintf("UPDATE %Q SET %s WHERE %s", pIter->zTbl, zSet, zWhere ); p->rc = prepareFreeAndCollectError( p->db, &pIter->pUpdate, &p->zErrmsg, zUpdate ); *ppStmt = pIter->pUpdate; } if( p->rc==SQLITE_OK ){ memcpy(pIter->zMask, zMask, pIter->nTblCol); } sqlite3_free(zWhere); sqlite3_free(zSet); } return p->rc; } /* ** The SELECT statement iterating through the keys for the current object ** (p->objiter.pSelect) currently points to a valid row. This function ** determines the type of operation requested by this row and returns ** one of the following values to indicate the result: |
︙ | ︙ | |||
624 625 626 627 628 629 630 631 632 633 634 635 636 637 | int iVal = sqlite3_column_int(p->objiter.pSelect, iCol); if( iVal==0 ){ res = OTA_INSERT; }else if( iVal==1 ){ res = OTA_DELETE; }else if( iVal==2 ){ res = OTA_IDX_DELETE; } break; } case SQLITE_TEXT: *pzMask = (const char*)sqlite3_column_text(p->objiter.pSelect, iCol); res = OTA_UPDATE; | > > | 727 728 729 730 731 732 733 734 735 736 737 738 739 740 741 742 | int iVal = sqlite3_column_int(p->objiter.pSelect, iCol); if( iVal==0 ){ res = OTA_INSERT; }else if( iVal==1 ){ res = OTA_DELETE; }else if( iVal==2 ){ res = OTA_IDX_DELETE; }else if( iVal==3 ){ res = OTA_IDX_INSERT; } break; } case SQLITE_TEXT: *pzMask = (const char*)sqlite3_column_text(p->objiter.pSelect, iCol); res = OTA_UPDATE; |
︙ | ︙ | |||
666 667 668 669 670 671 672 | if( eType ){ assert( eType!=OTA_UPDATE || pIter->zIdx==0 ); if( pIter->zIdx==0 && eType==OTA_IDX_DELETE ){ otaBadControlError(p); } | > > > | > > > > > | > > > > > > > < | | | | < | < | | < < < > | 771 772 773 774 775 776 777 778 779 780 781 782 783 784 785 786 787 788 789 790 791 792 793 794 795 796 797 798 799 800 801 802 803 804 805 806 807 808 809 810 811 812 813 814 815 816 817 818 819 820 821 | if( eType ){ assert( eType!=OTA_UPDATE || pIter->zIdx==0 ); if( pIter->zIdx==0 && eType==OTA_IDX_DELETE ){ otaBadControlError(p); } else if( eType==OTA_INSERT || eType==OTA_DELETE || eType==OTA_IDX_DELETE || eType==OTA_IDX_INSERT ){ sqlite3_stmt *pWriter; assert( eType!=OTA_UPDATE ); assert( eType!=OTA_DELETE || pIter->zIdx==0 ); if( eType==OTA_IDX_DELETE || eType==OTA_DELETE ){ pWriter = pIter->pDelete; }else{ pWriter = pIter->pInsert; } for(i=0; i<pIter->nCol; i++){ if( eType==SQLITE_DELETE && pIter->zIdx==0 && pIter->abTblPk[i]==0 ){ continue; } sqlite3_value *pVal = sqlite3_column_value(pIter->pSelect, i); sqlite3_bind_value(pWriter, i+1, pVal); } sqlite3_step(pWriter); p->rc = resetAndCollectError(pWriter, &p->zErrmsg); }else if( eType==OTA_UPDATE ){ sqlite3_stmt *pUpdate = 0; otaGetUpdateStmt(p, pIter, zMask, &pUpdate); if( pUpdate ){ for(i=0; i<pIter->nCol; i++){ sqlite3_value *pVal = sqlite3_column_value(pIter->pSelect, i); sqlite3_bind_value(pUpdate, i+1, pVal); } sqlite3_step(pUpdate); p->rc = resetAndCollectError(pUpdate, &p->zErrmsg); } }else{ /* no-op */ assert( eType==OTA_DELETE && pIter->zIdx ); } } return p->rc; |
︙ | ︙ | |||
714 715 716 717 718 719 720 | OtaObjIter *pIter = &p->objiter; while( p && p->rc==SQLITE_OK && pIter->zTbl ){ if( pIter->bCleanup ){ /* Clean up the ota_tmp_xxx table for the previous table. It ** cannot be dropped as there are currently active SQL statements. ** But the contents can be deleted. */ | | | 829 830 831 832 833 834 835 836 837 838 839 840 841 842 843 | OtaObjIter *pIter = &p->objiter; while( p && p->rc==SQLITE_OK && pIter->zTbl ){ if( pIter->bCleanup ){ /* Clean up the ota_tmp_xxx table for the previous table. It ** cannot be dropped as there are currently active SQL statements. ** But the contents can be deleted. */ // otaMPrintfExec(p, "DELETE FROM ota.'ota_tmp_%q'", pIter->zTbl); }else{ otaObjIterPrepareAll(p, pIter, 0); /* Advance to the next row to process. */ if( p->rc==SQLITE_OK ){ int rc = sqlite3_step(pIter->pSelect); if( rc==SQLITE_ROW ){ |
︙ | ︙ |