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 |
Timelines: | family | ancestors | descendants | both | ota-update |
Files: | files | file ages | folders |
SHA1: |
ccee999649d0fa1d48e53847542f4cbe |
User & Date: | dan 2014-11-27 18:09:46.630 |
Context
2014-12-06
| ||
19:30 | Allow the ota extension to write to tables with no PRIMARY KEY declaration. (check-in: ba59a7e2ba 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: ccee999649 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: 600cefdd4d user: dan tags: ota-update) | |
Changes
Added ext/ota/ota10.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 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 | # 2014 August 30 # # 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. # #*********************************************************************** # if {![info exists testdir]} { set testdir [file join [file dirname [info script]] .. .. test] } source $testdir/tester.tcl set ::testprefix ota10 #-------------------------------------------------------------------- # Test that UPDATE commands work even if the input columns are in a # different order to the output columns. # do_execsql_test 1.0 { CREATE TABLE t1(a INTEGER PRIMARY KEY, b, c); INSERT INTO t1 VALUES(1, 'b', 'c'); } proc apply_ota {sql} { forcedelete ota.db sqlite3 db2 ota.db db2 eval $sql db2 close sqlite3ota ota test.db ota.db while { [ota step]=="SQLITE_OK" } {} ota close } do_test 1.1 { apply_ota { CREATE TABLE data_t1(a, c, b, ota_control); INSERT INTO data_t1 VALUES(1, 'xxx', NULL, '.x.'); } db eval { SELECT * FROM t1 } } {1 b xxx} #-------------------------------------------------------------------- # Test that the hidden languageid column of an fts4 table can be # written. # ifcapable fts3 { do_execsql_test 2.0 { CREATE VIRTUAL TABLE ft USING fts4(a, b, languageid='langid'); } do_test 2.1 { apply_ota { CREATE TABLE data_ft(a, b, ota_rowid, langid, ota_control); INSERT INTO data_ft VALUES('a', 'b', 22, 1, 0); -- insert INSERT INTO data_ft VALUES('a', 'b', 23, 10, 0); -- insert INSERT INTO data_ft VALUES('a', 'b', 24, 100, 0); -- insert } db eval { SELECT a, b, rowid, langid FROM ft } } [list {*}{ a b 22 1 a b 23 10 a b 24 100 }] # Or not - this data_xxx table has no langid column, so langid # defaults to 0. # do_test 2.2 { apply_ota { CREATE TABLE data_ft(a, b, ota_rowid, ota_control); INSERT INTO data_ft VALUES('a', 'b', 25, 0); -- insert } db eval { SELECT a, b, rowid, langid FROM ft } } [list {*}{ a b 22 1 a b 23 10 a b 24 100 a b 25 0 }] # Update langid. # do_test 2.3 { apply_ota { CREATE TABLE data_ft(a, b, ota_rowid, langid, ota_control); INSERT INTO data_ft VALUES(NULL, NULL, 23, 50, '..x'); INSERT INTO data_ft VALUES(NULL, NULL, 25, 500, '..x'); } db eval { SELECT a, b, rowid, langid FROM ft } } [list {*}{ a b 22 1 a b 23 50 a b 24 100 a b 25 500 }] } #-------------------------------------------------------------------- # Test that if writing a hidden virtual table column is an error, # attempting to do so via ota is also an error. # ifcapable fts3 { do_execsql_test 3.0 { CREATE VIRTUAL TABLE xt USING fts4(a); } do_test 3.1 { list [catch { apply_ota { CREATE TABLE data_xt(a, xt, ota_rowid, ota_control); INSERT INTO data_xt VALUES('a', 'b', 1, 0); } } msg] $msg } {1 {SQLITE_ERROR - SQL logic error or missing database}} } finish_test |
Changes to ext/ota/ota3.test.
︙ | ︙ | |||
92 93 94 95 96 97 98 | sqlite3 db2 ota.db db2 eval { CREATE TABLE data_x1(a, b, ota_control); INSERT INTO data_x1 VALUES(1, 'a', 0); } db2 close list [catch { run_ota test.db ota.db } msg] $msg | | | 92 93 94 95 96 97 98 99 100 101 102 103 104 105 | sqlite3 db2 ota.db db2 eval { CREATE TABLE data_x1(a, b, ota_control); INSERT INTO data_x1 VALUES(1, 'a', 0); } db2 close list [catch { run_ota test.db ota.db } msg] $msg } {1 {SQLITE_ERROR - column missing from data_x1: c}} do_execsql_test 2.2 { PRAGMA integrity_check; } {ok} finish_test |
Changes to ext/ota/sqlite3ota.c.
︙ | ︙ | |||
219 220 221 222 223 224 225 | */ static void otaObjIterFreeCols(OtaObjIter *pIter){ int i; for(i=0; i<pIter->nTblCol; i++){ sqlite3_free(pIter->azTblCol[i]); } sqlite3_free(pIter->azTblCol); | < | 219 220 221 222 223 224 225 226 227 228 229 230 231 232 | */ static void otaObjIterFreeCols(OtaObjIter *pIter){ int i; for(i=0; i<pIter->nTblCol; i++){ sqlite3_free(pIter->azTblCol[i]); } sqlite3_free(pIter->azTblCol); pIter->azTblCol = 0; pIter->abTblPk = 0; pIter->nTblCol = 0; sqlite3_free(pIter->zMask); pIter->zMask = 0; pIter->bRowid = 0; pIter->bVtab = 0; |
︙ | ︙ | |||
364 365 366 367 368 369 370 371 372 373 374 375 376 377 | *p++ = zName[i]; } *p++ = '`'; *p++ = '\0'; } return zRet; } /* ** Argument zFmt is a sqlite3_mprintf() style format string. The trailing ** arguments are the usual subsitution values. This function performs ** the printf() style substitutions and executes the result as an SQL ** statement on the OTA handles database. ** | > > > > > > > > > > > > > > > > > > | 363 364 365 366 367 368 369 370 371 372 373 374 375 376 377 378 379 380 381 382 383 384 385 386 387 388 389 390 391 392 393 394 | *p++ = zName[i]; } *p++ = '`'; *p++ = '\0'; } return zRet; } /* ** Argument zName points to a column name. Argument zQuoted also points ** to a column name, but one that has been quoted using otaQuoteName(). ** Return true if the column names are the same, or false otherwise. */ static int otaMatchName(const char *zName, const char *zQuoted){ const char *p = zName; const char *q = &zQuoted[1]; while( 1 ){ if( *q=='`' ) q++; if( sqlite3_strnicmp(q, p, 1) ) return 0; if( !*q ) break; p++; q++; } return 1; } /* ** Argument zFmt is a sqlite3_mprintf() style format string. The trailing ** arguments are the usual subsitution values. This function performs ** the printf() style substitutions and executes the result as an SQL ** statement on the OTA handles database. ** |
︙ | ︙ | |||
392 393 394 395 396 397 398 | } } va_end(ap); return p->rc; } /* | | | < < < | | < > > > > | | > | | 409 410 411 412 413 414 415 416 417 418 419 420 421 422 423 424 425 426 427 428 429 430 431 432 433 434 435 436 437 438 | } } va_end(ap); return p->rc; } /* ** Allocate and zero the pIter->azTblCol[] and abTblPk[] arrays so that ** there is room for at least nCol elements. If an OOM occurs, store an ** error code in the OTA handle passed as the first argument. */ static void otaAllocateIterArrays(sqlite3ota *p, OtaObjIter *pIter, int nCol){ int nByte = sizeof(char*) * nCol + sizeof(unsigned char*) * nCol; char **azNew; assert( p->rc==SQLITE_OK ); azNew = (char**)sqlite3_malloc(nByte); if( azNew ){ memset(azNew, 0, nByte); pIter->azTblCol = azNew; pIter->abTblPk = (unsigned char*)&pIter->azTblCol[nCol]; }else{ p->rc = SQLITE_NOMEM; } } /* ** Return true if zTab is the name of a virtual table within the target ** database. */ |
︙ | ︙ | |||
447 448 449 450 451 452 453 | ** ** Return SQLITE_OK if successful, or an SQLite error code otherwise. If ** an error does occur, an error code and error message are also left in ** the OTA handle. */ static int otaObjIterGetCols(sqlite3ota *p, OtaObjIter *pIter){ if( pIter->azTblCol==0 ){ | | < > | > > | > > | > | > > > > > > > > > > > > > > > | > > > > > | > > > > > > > > > | < < < < | 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 512 513 514 515 516 517 518 519 520 521 522 523 524 525 526 527 528 529 530 531 532 | ** ** Return SQLITE_OK if successful, or an SQLite error code otherwise. If ** an error does occur, an error code and error message are also left in ** the OTA handle. */ static int otaObjIterGetCols(sqlite3ota *p, OtaObjIter *pIter){ if( pIter->azTblCol==0 ){ sqlite3_stmt *pStmt = 0; int nCol = 0; int bSeenPk = 0; int i; /* for() loop iterator variable */ int rc2; /* sqlite3_finalize() return value */ assert( pIter->bRowid==0 && pIter->bVtab==0 ); /* Populate the azTblCol[] and nTblCol variables based on the columns ** of the input table. Ignore any input table columns that begin with ** "ota_". */ p->rc = prepareFreeAndCollectError(p->db, &pStmt, &p->zErrmsg, sqlite3_mprintf("SELECT * FROM 'data_%q'", pIter->zTbl) ); if( p->rc==SQLITE_OK ){ nCol = sqlite3_column_count(pStmt); otaAllocateIterArrays(p, pIter, nCol); } for(i=0; p->rc==SQLITE_OK && i<nCol; i++){ const char *zName = (const char*)sqlite3_column_name(pStmt, i); if( sqlite3_strnicmp("ota_", zName, 4) ){ char *zCopy = otaQuoteName(zName); pIter->azTblCol[pIter->nTblCol++] = zCopy; if( zCopy==0 ) p->rc = SQLITE_NOMEM; } } sqlite3_finalize(pStmt); pStmt = 0; /* Check that all non-HIDDEN columns in the destination table are also ** present in the input table. Populate the abTblPk[] array at the ** same time. */ if( p->rc==SQLITE_OK ){ p->rc = prepareFreeAndCollectError(p->db, &pStmt, &p->zErrmsg, sqlite3_mprintf("PRAGMA main.table_info(%Q)", pIter->zTbl) ); } while( p->rc==SQLITE_OK && SQLITE_ROW==sqlite3_step(pStmt) ){ const char *zName = (const char*)sqlite3_column_text(pStmt, 1); for(i=0; i<pIter->nTblCol; i++){ if( otaMatchName(zName, pIter->azTblCol[i]) ) break; } if( i==pIter->nTblCol ){ p->rc = SQLITE_ERROR; p->zErrmsg = sqlite3_mprintf("column missing from data_%q: %s", pIter->zTbl, zName ); }else{ int iPk = sqlite3_column_int(pStmt, 5); pIter->abTblPk[i] = (iPk!=0); if( iPk ) bSeenPk = 1; if( iPk<0 ) pIter->bRowid = 1; } } rc2 = sqlite3_finalize(pStmt); if( p->rc==SQLITE_OK ) p->rc = rc2; if( p->rc==SQLITE_OK && bSeenPk==0 ){ const char *zTab = pIter->zTbl; if( otaIsVtab(p, zTab) ){ pIter->bVtab = 1; |
︙ | ︙ |
Changes to ext/ota/sqlite3ota.h.
︙ | ︙ | |||
101 102 103 104 105 106 107 108 109 110 111 112 113 114 | ** can not be updated using OTA. For example, if the target db contains: ** ** CREATE VIRTUAL TABLE ft1 USING fts3(a, b); ** ** then the OTA database should contain: ** ** CREATE TABLE data_ft1(a, b, ota_rowid, ota_control); ** ** For each row to INSERT into the target database as part of the OTA ** update, the corresponding data_% table should contain a single record ** with the "ota_control" column set to contain integer value 0. The ** other columns should be set to the values that make up the new record ** to insert. ** | > > > > > > > > > > > > > | 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 | ** can not be updated using OTA. For example, if the target db contains: ** ** CREATE VIRTUAL TABLE ft1 USING fts3(a, b); ** ** then the OTA database should contain: ** ** CREATE TABLE data_ft1(a, b, ota_rowid, ota_control); ** ** All non-hidden columns (i.e. all columns matched by "SELECT *") of the ** target table must be present in the input table. For virtual tables, ** hidden columns are optional - they are updated by OTA if present in ** the input table, or not otherwise. For example, to write to an fts4 ** table with a hidden languageid column such as: ** ** CREATE VIRTUAL TABLE ft1 USING fts4(a, b, languageid='langid'); ** ** Either of the following input table schemas may be used: ** ** CREATE TABLE data_ft1(a, b, langid, ota_rowid, ota_control); ** CREATE TABLE data_ft1(a, b, ota_rowid, ota_control); ** ** For each row to INSERT into the target database as part of the OTA ** update, the corresponding data_% table should contain a single record ** with the "ota_control" column set to contain integer value 0. The ** other columns should be set to the values that make up the new record ** to insert. ** |
︙ | ︙ |