Many hyperlinks are disabled.
Use anonymous login
to enable hyperlinks.
Overview
Comment: | Clarify handling of NULL values in PK columns in sqlite3session.h. Add tests and fixes for the same. |
---|---|
Downloads: | Tarball | ZIP archive |
Timelines: | family | ancestors | descendants | both | sessions |
Files: | files | file ages | folders |
SHA1: |
aed4273054cbd150c86b36ea951d17c9 |
User & Date: | dan 2011-03-21 11:55:07.000 |
Context
2011-03-21
| ||
16:17 | Add start of fault-injection tests for session module. Fix some bugs related to the same. (check-in: 32e95164d1 user: dan tags: sessions) | |
11:55 | Clarify handling of NULL values in PK columns in sqlite3session.h. Add tests and fixes for the same. (check-in: aed4273054 user: dan tags: sessions) | |
11:03 | Fix session module problems with real (floating point) values. (check-in: a192d04f4e user: dan tags: sessions) | |
Changes
Changes to ext/session/session2.test.
︙ | ︙ | |||
106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 | {INSERT t1 {} {t ii t two}} } do_iterator_test 1.1 t1 { INSERT INTO t1 VALUES(1.5, 99.9) } { {INSERT t1 {} {f 1.5 f 99.9}} } test_reset do_common_sql { CREATE TABLE t1(a PRIMARY KEY, b); CREATE TABLE t2(a, b INTEGER PRIMARY KEY); CREATE TABLE t3(a, b, c, PRIMARY KEY(a, b)); } | > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > < < < < < | < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < | 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 194 195 196 197 | {INSERT t1 {} {t ii t two}} } do_iterator_test 1.1 t1 { INSERT INTO t1 VALUES(1.5, 99.9) } { {INSERT t1 {} {f 1.5 f 99.9}} } # Execute each of the following blocks of SQL on database [db1]. Collect # changes using a session object. Apply the resulting changeset to # database [db2]. Then check that the contents of the two databases are # identical. # set set_of_tests { 1 { INSERT INTO %T1% VALUES(1, 2) } 2 { INSERT INTO %T2% VALUES(1, NULL); INSERT INTO %T2% VALUES(2, NULL); INSERT INTO %T2% VALUES(3, NULL); DELETE FROM %T2% WHERE a = 2; INSERT INTO %T2% VALUES(4, NULL); UPDATE %T2% SET b=0 WHERE b=1; } 3 { INSERT INTO %T3% SELECT *, NULL FROM %T2% } 4 { INSERT INTO %T3% SELECT a||a, b||b, NULL FROM %T3%; DELETE FROM %T3% WHERE rowid%2; } 5 { UPDATE %T3% SET c = a||b } 6 { UPDATE %T1% SET a = 32 } 7 { INSERT INTO %T1% SELECT randomblob(32), randomblob(32) FROM %T1%; INSERT INTO %T1% SELECT randomblob(32), randomblob(32) FROM %T1%; INSERT INTO %T1% SELECT randomblob(32), randomblob(32) FROM %T1%; INSERT INTO %T1% SELECT randomblob(32), randomblob(32) FROM %T1%; INSERT INTO %T1% SELECT randomblob(32), randomblob(32) FROM %T1%; INSERT INTO %T1% SELECT randomblob(32), randomblob(32) FROM %T1%; INSERT INTO %T1% SELECT randomblob(32), randomblob(32) FROM %T1%; INSERT INTO %T1% SELECT randomblob(32), randomblob(32) FROM %T1%; INSERT INTO %T1% SELECT randomblob(32), randomblob(32) FROM %T1%; INSERT INTO %T1% SELECT randomblob(32), randomblob(32) FROM %T1%; INSERT INTO %T1% SELECT randomblob(32), randomblob(32) FROM %T1%; DELETE FROM %T1% WHERE (rowid%3)==0; } 8 { BEGIN; INSERT INTO %T1% SELECT randomblob(32), randomblob(32) FROM %T1%; ROLLBACK; } 9 { BEGIN; UPDATE %T1% SET b = 'xxx'; ROLLBACK; } 10 { BEGIN; DELETE FROM %T1% WHERE 1; ROLLBACK; } 11 { INSERT INTO %T1% VALUES(randomblob(21000), randomblob(0)); INSERT INTO %T1% VALUES(1.5, 1.5); INSERT INTO %T1% VALUES(4.56, -99.999999999999999999999); } 12 { INSERT INTO %T2% VALUES(NULL, NULL); } } test_reset do_common_sql { CREATE TABLE t1(a PRIMARY KEY, b); CREATE TABLE t2(a, b INTEGER PRIMARY KEY); CREATE TABLE t3(a, b, c, PRIMARY KEY(a, b)); } foreach {tn sql} [string map {%T1% t1 %T2% t2 %T3% t3} $set_of_tests] { do_then_apply_sql $sql do_test 1.$tn { compare_db db db2 } {} } # The following block of tests is similar to the last, except that the # session object is recording changes made to an attached database. The # main database contains a table of the same name as the table being |
︙ | ︙ | |||
205 206 207 208 209 210 211 | CREATE TABLE t2(a, b INTEGER PRIMARY KEY); CREATE TABLE t3(a, b, c, PRIMARY KEY(a, b)); } db2 } {} proc xTrace {args} { puts $args } | | | < < | < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > | 216 217 218 219 220 221 222 223 224 225 226 227 228 229 230 231 232 233 234 235 236 237 238 239 240 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 | CREATE TABLE t2(a, b INTEGER PRIMARY KEY); CREATE TABLE t3(a, b, c, PRIMARY KEY(a, b)); } db2 } {} proc xTrace {args} { puts $args } foreach {tn sql} [ string map {%T1% aux.t1 %T2% aux.t2 %T3% aux.t3} $set_of_tests ] { do_then_apply_sql $sql aux do_test 2.$tn { compare_db db3 db2 } {} } catch {db3 close} #------------------------------------------------------------------------- # The following tests verify that NULL values in primary key columns are # handled correctly by the session module. # test_reset do_execsql_test 3.0 { CREATE TABLE t1(a PRIMARY KEY); CREATE TABLE t2(a, b, c, PRIMARY KEY(c, b)); CREATE TABLE t3(a, b INTEGER PRIMARY KEY); } foreach {tn sql changeset} { 1 { INSERT INTO t1 VALUES(123); INSERT INTO t1 VALUES(NULL); INSERT INTO t1 VALUES(456); } { {INSERT t1 {} {i 456}} {INSERT t1 {} {i 123}} } 2 { UPDATE t1 SET a = NULL; } { {DELETE t1 {i 456} {}} {DELETE t1 {i 123} {}} } 3 { DELETE FROM t1 } { } 4 { INSERT INTO t3 VALUES(NULL, NULL) } { {INSERT t3 {} {n {} i 1}} } 5 { INSERT INTO t2 VALUES(1, 2, NULL) } { } 6 { INSERT INTO t2 VALUES(1, NULL, 3) } { } 7 { INSERT INTO t2 VALUES(1, NULL, NULL) } { } 8 { INSERT INTO t2 VALUES(1, 2, 3) } { {INSERT t2 {} {i 1 i 2 i 3}} } 9 { DELETE FROM t2 WHERE 1 } { {DELETE t2 {i 1 i 2 i 3} {}} } } { do_iterator_test 3.$tn {t1 t2 t3} $sql $changeset } finish_test |
Changes to ext/session/sqlite3session.c.
︙ | ︙ | |||
282 283 284 285 286 287 288 | ** new.* row currently available. The value returned is guaranteed to ** be less than pTab->nBucket. */ static unsigned int sessionPreupdateHash( sqlite3 *db, /* Database handle */ SessionTable *pTab, /* Session table handle */ int bNew, /* True to hash the new.* PK */ | | > > | 282 283 284 285 286 287 288 289 290 291 292 293 294 295 296 297 298 299 300 301 302 | ** new.* row currently available. The value returned is guaranteed to ** be less than pTab->nBucket. */ static unsigned int sessionPreupdateHash( sqlite3 *db, /* Database handle */ SessionTable *pTab, /* Session table handle */ int bNew, /* True to hash the new.* PK */ int *piHash, /* OUT: Hash value */ int *pbNullPK ){ unsigned int h = 0; /* Hash value to return */ int i; /* Used to iterate through columns */ assert( *pbNullPK==0 ); assert( pTab->nCol==sqlite3_preupdate_count(db) ); for(i=0; i<pTab->nCol; i++){ if( pTab->abPK[i] ){ int rc; int eType; sqlite3_value *pVal; |
︙ | ︙ | |||
325 326 327 328 329 330 331 332 333 334 335 336 337 338 | case SQLITE_BLOB: { int n = sqlite3_value_bytes(pVal); const u8 *z = eType==SQLITE_TEXT ? sqlite3_value_text(pVal) : sqlite3_value_blob(pVal); h = sessionHashAppendBlob(h, n, z); break; } } } } *piHash = (h % pTab->nChange); return SQLITE_OK; } | > > > > > | 327 328 329 330 331 332 333 334 335 336 337 338 339 340 341 342 343 344 345 | case SQLITE_BLOB: { int n = sqlite3_value_bytes(pVal); const u8 *z = eType==SQLITE_TEXT ? sqlite3_value_text(pVal) : sqlite3_value_blob(pVal); h = sessionHashAppendBlob(h, n, z); break; } default: assert( eType==SQLITE_NULL ); *pbNullPK = 1; return SQLITE_OK; } } } *piHash = (h % pTab->nChange); return SQLITE_OK; } |
︙ | ︙ | |||
352 353 354 355 356 357 358 359 | unsigned int h = 0; /* Value to return */ int i; /* Used to iterate through columns */ u8 *a = pChange->aRecord; /* Used to iterate through change record */ for(i=0; i<pTab->nCol; i++){ int eType = *a++; int isPK = pTab->abPK[i]; | < | > > | > > | | < > | < | < | < < | | < | < | < < | 359 360 361 362 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 | unsigned int h = 0; /* Value to return */ int i; /* Used to iterate through columns */ u8 *a = pChange->aRecord; /* Used to iterate through change record */ for(i=0; i<pTab->nCol; i++){ int eType = *a++; int isPK = pTab->abPK[i]; /* It is not possible for eType to be SQLITE_NULL here. The session ** module does not record changes for rows with NULL values stored in ** primary key columns. */ assert( eType==SQLITE_INTEGER || eType==SQLITE_FLOAT || eType==SQLITE_TEXT || eType==SQLITE_BLOB ); if( isPK ) h = HASH_APPEND(h, eType); if( eType==SQLITE_INTEGER || eType==SQLITE_FLOAT ){ if( isPK ) h = sessionHashAppendI64(h, sessionGetI64(a)); a += 8; }else{ int n; a += sessionVarintGet(a, &n); if( isPK ) h = sessionHashAppendBlob(h, n, a); a += n; } } return (h % nBucket); } static int sessionPreupdateEqual( sqlite3 *db, |
︙ | ︙ | |||
661 662 663 664 665 666 667 668 669 670 671 672 673 674 675 676 677 678 | sqlite3_session *pSession, SessionTable *pTab ){ sqlite3 *db = pSession->db; SessionChange *pChange; SessionChange *pC; int iHash; int rc = SQLITE_OK; if( pSession->rc ) return; /* Load table details if required */ if( sessionInitTable(pSession, pTab) ) return; /* Grow the hash table if required */ if( sessionGrowHash(pSession, pTab) ) return; /* Search the hash table for an existing entry for rowid=iKey2. If | > | | | | > | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | > | 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 709 710 711 712 713 714 715 716 717 718 719 720 721 722 723 724 725 726 727 728 729 730 731 732 733 734 735 736 737 738 739 740 741 742 743 744 745 746 747 748 749 750 751 752 753 754 755 | sqlite3_session *pSession, SessionTable *pTab ){ sqlite3 *db = pSession->db; SessionChange *pChange; SessionChange *pC; int iHash; int bNullPk = 0; int rc = SQLITE_OK; if( pSession->rc ) return; /* Load table details if required */ if( sessionInitTable(pSession, pTab) ) return; /* Grow the hash table if required */ if( sessionGrowHash(pSession, pTab) ) return; /* Search the hash table for an existing entry for rowid=iKey2. If ** one is found, store a pointer to it in pChange and unlink it from ** the hash table. Otherwise, set pChange to NULL. */ rc = sessionPreupdateHash(db, pTab, op==SQLITE_INSERT, &iHash, &bNullPk); if( bNullPk==0 ){ for(pC=pTab->apChange[iHash]; rc==SQLITE_OK && pC; pC=pC->pNext){ int bEqual; rc = sessionPreupdateEqual(db, pTab, pC, op==SQLITE_INSERT, &bEqual); if( bEqual ) break; } if( pC==0 ){ /* Create a new change object containing all the old values (if ** this is an SQLITE_UPDATE or SQLITE_DELETE), or just the PK ** values (if this is an INSERT). */ int nByte; /* Number of bytes to allocate */ int i; /* Used to iterate through columns */ pTab->nEntry++; /* Figure out how large an allocation is required */ nByte = sizeof(SessionChange); for(i=0; i<pTab->nCol && rc==SQLITE_OK; i++){ sqlite3_value *p = 0; if( op!=SQLITE_INSERT ){ rc = sqlite3_preupdate_old(pSession->db, i, &p); }else if( 1 || pTab->abPK[i] ){ rc = sqlite3_preupdate_new(pSession->db, i, &p); } if( p && rc==SQLITE_OK ){ rc = sessionSerializeValue(0, p, &nByte); } } /* Allocate the change object */ pChange = (SessionChange *)sqlite3_malloc(nByte); if( !pChange ){ rc = SQLITE_NOMEM; }else{ memset(pChange, 0, sizeof(SessionChange)); pChange->aRecord = (u8 *)&pChange[1]; } /* Populate the change object */ nByte = 0; for(i=0; i<pTab->nCol && rc==SQLITE_OK; i++){ sqlite3_value *p = 0; if( op!=SQLITE_INSERT ){ rc = sqlite3_preupdate_old(pSession->db, i, &p); }else if( 1 || pTab->abPK[i] ){ rc = sqlite3_preupdate_new(pSession->db, i, &p); } if( p && rc==SQLITE_OK ){ rc = sessionSerializeValue(&pChange->aRecord[nByte], p, &nByte); } } pChange->nRecord = nByte; /* If an error has occurred, mark the session object as failed. */ if( rc!=SQLITE_OK ){ sqlite3_free(pChange); pSession->rc = rc; }else{ /* Add the change back to the hash-table */ pChange->bInsert = (op==SQLITE_INSERT); pChange->pNext = pTab->apChange[iHash]; pTab->apChange[iHash] = pChange; } } } } /* ** The 'pre-update' hook registered by this module with SQLite databases. */ |
︙ | ︙ |
Changes to ext/session/sqlite3session.h.
︙ | ︙ | |||
101 102 103 104 105 106 107 108 109 110 111 112 113 114 | ** defined as part of their CREATE TABLE statement. It does not matter if the ** PRIMARY KEY is an "INTEGER PRIMARY KEY" (rowid alias) or not. The PRIMARY ** KEY may consist of a single column, or may be a composite key. ** ** It is not an error if the named table does not exist in the database. Nor ** is it an error if the named table does not have a PRIMARY KEY. However, ** no changes will be recorded in either of these scenarios. ** ** SQLITE_OK is returned if the table is successfully attached to the session ** object. Or, if an error occurs, an SQLite error code (e.g. SQLITE_NOMEM) ** is returned. */ int sqlite3session_attach( sqlite3_session *pSession, /* Session object */ | > > > | 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 | ** defined as part of their CREATE TABLE statement. It does not matter if the ** PRIMARY KEY is an "INTEGER PRIMARY KEY" (rowid alias) or not. The PRIMARY ** KEY may consist of a single column, or may be a composite key. ** ** It is not an error if the named table does not exist in the database. Nor ** is it an error if the named table does not have a PRIMARY KEY. However, ** no changes will be recorded in either of these scenarios. ** ** Changes are not recorded for individual rows that have NULL values stored ** in one or more of their PRIMARY KEY columns. ** ** SQLITE_OK is returned if the table is successfully attached to the session ** object. Or, if an error occurs, an SQLite error code (e.g. SQLITE_NOMEM) ** is returned. */ int sqlite3session_attach( sqlite3_session *pSession, /* Session object */ |
︙ | ︙ | |||
130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 | ** change contains the values of each field of a new database row. A DELETE ** contains the original values of each field of a deleted database row. An ** UPDATE change contains the original values of each field of an updated ** database row along with the updated values for each updated non-primary-key ** column. It is not possible for an UPDATE change to represent a change that ** modifies the values of primary key columns. If such a change is made, it ** is represented in a changeset as a DELETE followed by an INSERT. ** ** The contents of a changeset may be traversed using an iterator created ** using the [sqlite3changeset_start()] API. A changeset may be applied to ** a database with a compatible schema using the [sqlite3changeset_apply()] ** API. ** ** Following a successful call to this function, it is the responsibility of ** the caller to eventually free the buffer that *ppChangeset points to using ** [sqlite3_free()]. ** ** <h3>Changeset Generation</h3> ** ** Once a table has been attached to a session object, the session object ** records the primary key values of all new rows inserted into the table. ** It also records the original primary key and other column values of any ** deleted or updated rows. For each unique primary key value, data is only ** recorded once - the first time a row with said primary key is inserted, ** updated or deleted in the lifetime of the session. ** ** The session object therefore accumulates two types of records - those ** that consist of primary key values only (created when the user inserts ** a new record) and those that consist of the primary key values and the ** original values of other table columns (created when the users deletes ** or updates a record). ** | > > > > > > > > > > > > > > | 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 | ** change contains the values of each field of a new database row. A DELETE ** contains the original values of each field of a deleted database row. An ** UPDATE change contains the original values of each field of an updated ** database row along with the updated values for each updated non-primary-key ** column. It is not possible for an UPDATE change to represent a change that ** modifies the values of primary key columns. If such a change is made, it ** is represented in a changeset as a DELETE followed by an INSERT. ** ** Changes are not recorded for rows that have NULL values stored in one or ** more of their PRIMARY KEY columns. If such a row is inserted or deleted, ** no corresponding change is present in the changesets returned by this ** function. If an existing row with one or more NULL values stored in ** PRIMARY KEY columns is updated so that all PRIMARY KEY columns are non-NULL, ** only an INSERT is appears in the changeset. Similarly, if an existing row ** with non-NULL PRIMARY KEY values is updated so that one or more of its ** PRIMARY KEY columns are set to NULL, the resulting changeset contains a ** DELETE change only. ** ** The contents of a changeset may be traversed using an iterator created ** using the [sqlite3changeset_start()] API. A changeset may be applied to ** a database with a compatible schema using the [sqlite3changeset_apply()] ** API. ** ** Following a successful call to this function, it is the responsibility of ** the caller to eventually free the buffer that *ppChangeset points to using ** [sqlite3_free()]. ** ** <h3>Changeset Generation</h3> ** ** Once a table has been attached to a session object, the session object ** records the primary key values of all new rows inserted into the table. ** It also records the original primary key and other column values of any ** deleted or updated rows. For each unique primary key value, data is only ** recorded once - the first time a row with said primary key is inserted, ** updated or deleted in the lifetime of the session. ** ** There is one exception to the previous paragraph: when a row is inserted, ** updated or deleted, if one or more of its primary key columns contains a ** NULL value, no record of the change is made. ** ** The session object therefore accumulates two types of records - those ** that consist of primary key values only (created when the user inserts ** a new record) and those that consist of the primary key values and the ** original values of other table columns (created when the users deletes ** or updates a record). ** |
︙ | ︙ |