Many hyperlinks are disabled.
Use anonymous login
to enable hyperlinks.
Overview
Comment: | Allow sqlite3session_apply() to apply changesets to tables that have been extended using ALTER TABLE ADD COLUMN. |
---|---|
Downloads: | Tarball | ZIP archive |
Timelines: | family | ancestors | descendants | both | trunk |
Files: | files | file ages | folders |
SHA1: |
b20ff81ff9c8af500ea96e0ba9d34524 |
User & Date: | dan 2017-02-04 17:33:30.792 |
Context
2017-02-04
| ||
20:15 | Simplification to the error handling to extension loading in sqlite3_open(). (check-in: ec8ff892ac user: drh tags: trunk) | |
17:33 | Allow sqlite3session_apply() to apply changesets to tables that have been extended using ALTER TABLE ADD COLUMN. (check-in: b20ff81ff9 user: dan tags: trunk) | |
14:24 | In RTREE, use an sqlite3_blob object rather than an sqlite3_stmt object for reading content out of the %_node shadow table. (check-in: 97ccf3e4de user: drh tags: trunk) | |
Changes
Changes to ext/session/session3.test.
︙ | ︙ | |||
59 60 61 62 63 64 65 | do_test 1.2.1 { set ::log {} do_then_apply_sql { INSERT INTO t1 VALUES(5, 6); INSERT INTO t1 VALUES(7, 8); } set ::log | | > > > | 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 | do_test 1.2.1 { set ::log {} do_then_apply_sql { INSERT INTO t1 VALUES(5, 6); INSERT INTO t1 VALUES(7, 8); } set ::log } {} do_test 1.2.2 { db2 eval { SELECT * FROM t1 } } {5 6 {} 7 8 {}} do_test 1.3.0 { execsql { DROP TABLE t1; CREATE TABLE t1(a, b PRIMARY KEY); } db2 } {} |
︙ | ︙ |
Changes to ext/session/session_common.tcl.
︙ | ︙ | |||
29 30 31 32 33 34 35 | sqlite3session_foreach c [set changeset] { lappend x [set c] } set x }]] [list $r] } proc do_conflict_test {tn args} { | < < < < < < > > > > > > > | 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 | sqlite3session_foreach c [set changeset] { lappend x [set c] } set x }]] [list $r] } proc do_conflict_test {tn args} { set O(-tables) [list] set O(-sql) [list] set O(-conflicts) [list] set O(-policy) "OMIT" array set V $args foreach key [array names V] { if {![info exists O($key)]} {error "no such option: $key"} } array set O $args proc xConflict {args} [subst -nocommands { lappend ::xConflict [set args] return $O(-policy) }] proc bgerror {args} { set ::background_error $args } sqlite3session S db main foreach t $O(-tables) { S attach $t } execsql $O(-sql) set ::xConflict [list] sqlite3changeset_apply db2 [S changeset] xConflict |
︙ | ︙ |
Added ext/session/sessionat.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 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 198 199 200 201 202 203 204 205 206 207 208 209 210 211 212 213 214 215 216 | # 2017 February 04 # # 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. # #*********************************************************************** # # Tests for the sessions module. Specifically, that a changeset can # be applied after ALTER TABLE ADD COLUMN has been used to add # columns to tables. # if {![info exists testdir]} { set testdir [file join [file dirname [info script]] .. .. test] } source [file join [file dirname [info script]] session_common.tcl] source $testdir/tester.tcl ifcapable !session {finish_test; return} set testprefix sessionat db close sqlite3_shutdown test_sqlite3_log log proc log {code msg} { lappend ::log $code $msg } proc reset_test {} { catch { db close } catch { db2 close } forcedelete test.db test.db2 sqlite3 db test.db sqlite3 db2 test.db2 } # Run all tests in this file twice. Once with "WITHOUT ROWID", and once # with regular rowid tables. # # ?.1.*: Test that PK inconsistencies are detected if one or more of the PK # columns are not present in the changeset. # # ?.2.*: Test that it is not possible to apply a changeset with N columns # to a db with fewer than N columns. # # ?.3.*: Test some INSERT, UPDATE and DELETE operations that do not # require conflict handling. # # ?.4.*: Test some INSERT, UPDATE and DELETE operations that do require # conflict handling. # # ?.5.*: Test that attempting to concat two changesets with different # numbers of columns for the same table is an error. # foreach {tn trailing} { sessionat-ipk "" sessionat-wor " WITHOUT ROWID " } { eval [string map [list %WR% $trailing] { reset_test #----------------------------------------------------------------------- do_execsql_test $tn.1.0 { CREATE TABLE t1(a, b, PRIMARY KEY(a)) %WR%; } do_execsql_test -db db2 $tn.1.1 { CREATE TABLE t1(a, b, c, PRIMARY KEY(a, c)) %WR%; } do_test $tn.1.2 { set ::log {} do_then_apply_sql { INSERT INTO t1 VALUES('one', 'two') } set ::log } [list \ SQLITE_SCHEMA {sqlite3changeset_apply(): primary key mismatch for table t1} ] do_execsql_test $tn.1.3 { SELECT * FROM t1 } {one two} do_execsql_test -db db2 $tn.1.4 { SELECT * FROM t1 } {} #----------------------------------------------------------------------- do_execsql_test $tn.2.0 { CREATE TABLE t2(x, y, z, PRIMARY KEY(x)) %WR%; } do_execsql_test -db db2 $tn.2.1 { CREATE TABLE t2(x, y, PRIMARY KEY(x)) %WR%; } do_test $tn.2.2 { db cache flush set ::log {} do_then_apply_sql { INSERT INTO t2 VALUES(1, 2, 3) } set ::log } [list SQLITE_SCHEMA \ {sqlite3changeset_apply(): table t2 has 2 columns, expected 3 or more} ] do_execsql_test $tn.2.3 { SELECT * FROM t2 } {1 2 3} do_execsql_test -db db2 $tn.2.4 { SELECT * FROM t2 } {} #----------------------------------------------------------------------- do_execsql_test $tn.3.0 { CREATE TABLE t3(a, b, PRIMARY KEY(b)) %WR%; } do_execsql_test -db db2 $tn.3.1 { CREATE TABLE t3(a, b, c DEFAULT 'D', PRIMARY KEY(b)) %WR%; } do_test $tn.3.2 { do_then_apply_sql { INSERT INTO t3 VALUES(1, 2); INSERT INTO t3 VALUES(3, 4); INSERT INTO t3 VALUES(5, 6); }; db2 eval {SELECT * FROM t3} } {1 2 D 3 4 D 5 6 D} do_test $tn.3.3 { do_then_apply_sql { UPDATE t3 SET a=45 WHERE b=4; DELETE FROM t3 WHERE a=5; }; db2 eval {SELECT * FROM t3} } {1 2 D 45 4 D} #----------------------------------------------------------------------- # 4.1: INSERT statements # 4.2: DELETE statements # 4.3: UPDATE statements # do_execsql_test $tn.4.1.0 { CREATE TABLE t4(x INTEGER PRIMARY KEY, y) %WR%; } do_execsql_test -db db2 $tn.4.1.1 { CREATE TABLE t4(x INTEGER PRIMARY KEY, y, z) %WR%; INSERT INTO t4 VALUES(1, 2, 3); INSERT INTO t4 VALUES(4, 5, 6); } do_conflict_test $tn.4.1.2 -tables t4 -sql { INSERT INTO t4 VALUES(10, 20); INSERT INTO t4 VALUES(4, 11); } -conflicts { {INSERT t4 CONFLICT {i 4 i 11} {i 4 i 5}} } do_execsql_test -db db2 $tn.4.1.3 { SELECT * FROM t4 ORDER BY x } {1 2 3 4 5 6 10 20 {}} do_conflict_test $tn.4.1.4 -policy REPLACE -tables t4 -sql { INSERT INTO t4 VALUES(1, 11); } -conflicts { {INSERT t4 CONFLICT {i 1 i 11} {i 1 i 2}} } do_execsql_test -db db2 $tn.4.1.5 { SELECT * FROM t4 ORDER BY x } {1 11 {} 4 5 6 10 20 {}} do_execsql_test $tn.4.2.0 { DELETE FROM t4; INSERT INTO t4 VALUES(1, 'A'); INSERT INTO t4 VALUES(2, 'B'); INSERT INTO t4 VALUES(3, 'C'); INSERT INTO t4 VALUES(4, 'D'); } do_execsql_test -db db2 $tn.4.2.1 { DELETE FROM t4; INSERT INTO t4 VALUES(1, 'A', 'a'); INSERT INTO t4 VALUES(3, 'C', 'c'); INSERT INTO t4 VALUES(4, 'E', 'd'); } do_conflict_test $tn.4.2.2 -tables t4 -sql { DELETE FROM t4 WHERE x=2; DELETE FROM t4 WHERE x=4; } -conflicts { {DELETE t4 NOTFOUND {i 2 t B}} {DELETE t4 DATA {i 4 t D} {i 4 t E}} } do_execsql_test $tn.4.3.0 { CREATE TABLE t5(a, b, c PRIMARY KEY) %WR%; INSERT INTO t5 VALUES(1,1,1), (2,2,2), (3,3,3), (4,4,4); } do_execsql_test -db db2 $tn.4.3.1 { CREATE TABLE t5(a, b, c PRIMARY KEY, d CHECK(b!=10)) %WR%; INSERT INTO t5 VALUES (2,2,2,2), (3,8,3,3), (4,4,4,4); } do_conflict_test $tn.4.3.2 -tables t5 -sql { UPDATE t5 SET a=4 WHERE c=1; UPDATE t5 SET b=9 WHERE c=3; UPDATE t5 SET b=10 WHERE c=2; } -conflicts { {UPDATE t5 NOTFOUND {i 1 {} {} i 1} {i 4 {} {} {} {}}} {UPDATE t5 DATA {{} {} i 3 i 3} {{} {} i 9 {} {}} {i 3 i 8 i 3}} {UPDATE t5 CONSTRAINT {{} {} i 2 i 2} {{} {} i 10 {} {}}} } #----------------------------------------------------------------------- do_execsql_test $tn.5.0 { CREATE TABLE t6(a, b, c, PRIMARY KEY(a, b)) %WR%; } do_execsql_test -db db2 $tn.5.1 { CREATE TABLE t6(a, b, c, d, e, PRIMARY KEY(a, b)) %WR%; } do_test $tn.5.2 { set c1 [sql_exec_changeset db { INSERT INTO t6 VALUES(1, 1, 1); INSERT INTO t6 VALUES(2, 2, 2); }] set c2 [sql_exec_changeset db2 { INSERT INTO t6 VALUES(3, 3, 3, 3, 3); INSERT INTO t6 VALUES(4, 4, 4, 4, 4); }] list [catch { sqlite3changeset_concat $c1 $c2} msg] $msg } {1 SQLITE_SCHEMA} }] } finish_test |
Changes to ext/session/sqlite3session.c.
︙ | ︙ | |||
3024 3025 3026 3027 3028 3029 3030 | sqlite3_changeset_iter *pIter, /* Changeset iterator */ int iVal, /* Index of conflict record value to fetch */ sqlite3_value **ppValue /* OUT: Value from conflicting row */ ){ if( !pIter->pConflict ){ return SQLITE_MISUSE; } | | | 3024 3025 3026 3027 3028 3029 3030 3031 3032 3033 3034 3035 3036 3037 3038 | sqlite3_changeset_iter *pIter, /* Changeset iterator */ int iVal, /* Index of conflict record value to fetch */ sqlite3_value **ppValue /* OUT: Value from conflicting row */ ){ if( !pIter->pConflict ){ return SQLITE_MISUSE; } if( iVal<0 || iVal>=pIter->nCol ){ return SQLITE_RANGE; } *ppValue = sqlite3_column_value(pIter->pConflict, iVal); return SQLITE_OK; } /* |
︙ | ︙ | |||
3491 3492 3493 3494 3495 3496 3497 | ){ int rc = SQLITE_OK; int i; SessionBuffer buf = {0, 0, 0}; sessionAppendStr(&buf, "INSERT INTO main.", &rc); sessionAppendIdent(&buf, zTab, &rc); | | > > > > > > | 3491 3492 3493 3494 3495 3496 3497 3498 3499 3500 3501 3502 3503 3504 3505 3506 3507 3508 3509 3510 3511 | ){ int rc = SQLITE_OK; int i; SessionBuffer buf = {0, 0, 0}; sessionAppendStr(&buf, "INSERT INTO main.", &rc); sessionAppendIdent(&buf, zTab, &rc); sessionAppendStr(&buf, "(", &rc); for(i=0; i<p->nCol; i++){ if( i!=0 ) sessionAppendStr(&buf, ", ", &rc); sessionAppendIdent(&buf, p->azCol[i], &rc); } sessionAppendStr(&buf, ") VALUES(?", &rc); for(i=1; i<p->nCol; i++){ sessionAppendStr(&buf, ", ?", &rc); } sessionAppendStr(&buf, ")", &rc); if( rc==SQLITE_OK ){ rc = sqlite3_prepare_v2(db, (char *)buf.aBuf, buf.nBuf, &p->pInsert, 0); |
︙ | ︙ | |||
4037 4038 4039 4040 4041 4042 4043 4044 4045 4046 4047 4048 4049 4050 4051 4052 4053 4054 4055 | if( zTab==0 ){ rc = SQLITE_NOMEM; break; } nTab = (int)strlen(zTab); sApply.azCol = (const char **)zTab; }else{ sqlite3changeset_pk(pIter, &abPK, 0); rc = sessionTableInfo( db, "main", zNew, &sApply.nCol, &zTab, &sApply.azCol, &sApply.abPK ); if( rc!=SQLITE_OK ) break; if( sApply.nCol==0 ){ schemaMismatch = 1; sqlite3_log(SQLITE_SCHEMA, "sqlite3changeset_apply(): no such table: %s", zTab ); } | > > > > > > | | > | | > | | | | | | > | 4043 4044 4045 4046 4047 4048 4049 4050 4051 4052 4053 4054 4055 4056 4057 4058 4059 4060 4061 4062 4063 4064 4065 4066 4067 4068 4069 4070 4071 4072 4073 4074 4075 4076 4077 4078 4079 4080 4081 4082 4083 4084 4085 4086 4087 4088 4089 4090 4091 4092 4093 4094 4095 4096 4097 | if( zTab==0 ){ rc = SQLITE_NOMEM; break; } nTab = (int)strlen(zTab); sApply.azCol = (const char **)zTab; }else{ int nMinCol = 0; int i; sqlite3changeset_pk(pIter, &abPK, 0); rc = sessionTableInfo( db, "main", zNew, &sApply.nCol, &zTab, &sApply.azCol, &sApply.abPK ); if( rc!=SQLITE_OK ) break; for(i=0; i<sApply.nCol; i++){ if( sApply.abPK[i] ) nMinCol = i+1; } if( sApply.nCol==0 ){ schemaMismatch = 1; sqlite3_log(SQLITE_SCHEMA, "sqlite3changeset_apply(): no such table: %s", zTab ); } else if( sApply.nCol<nCol ){ schemaMismatch = 1; sqlite3_log(SQLITE_SCHEMA, "sqlite3changeset_apply(): table %s has %d columns, " "expected %d or more", zTab, sApply.nCol, nCol ); } else if( nCol<nMinCol || memcmp(sApply.abPK, abPK, nCol)!=0 ){ schemaMismatch = 1; sqlite3_log(SQLITE_SCHEMA, "sqlite3changeset_apply(): " "primary key mismatch for table %s", zTab ); } else{ sApply.nCol = nCol; if((rc = sessionSelectRow(db, zTab, &sApply)) || (rc = sessionUpdateRow(db, zTab, &sApply)) || (rc = sessionDeleteRow(db, zTab, &sApply)) || (rc = sessionInsertRow(db, zTab, &sApply)) ){ break; } } nTab = sqlite3Strlen30(zTab); } } /* If there is a schema mismatch on the current table, proceed to the ** next change. A log message has already been issued. */ |
︙ | ︙ |
Changes to ext/session/sqlite3session.h.
︙ | ︙ | |||
315 316 317 318 319 320 321 | ** <li> For each row (primary key) that exists in the to-table but not in ** the from-table, an INSERT record is added to the session object. ** ** <li> For each row (primary key) that exists in the to-table but not in ** the from-table, a DELETE record is added to the session object. ** ** <li> For each row (primary key) that exists in both tables, but features | | > | 315 316 317 318 319 320 321 322 323 324 325 326 327 328 329 330 | ** <li> For each row (primary key) that exists in the to-table but not in ** the from-table, an INSERT record is added to the session object. ** ** <li> For each row (primary key) that exists in the to-table but not in ** the from-table, a DELETE record is added to the session object. ** ** <li> For each row (primary key) that exists in both tables, but features ** different non-PK values in each, an UPDATE record is added to the ** session. ** </ul> ** ** To clarify, if this function is called and then a changeset constructed ** using [sqlite3session_changeset()], then after applying that changeset to ** database zFrom the contents of the two compatible tables would be ** identical. ** |
︙ | ︙ | |||
900 901 902 903 904 905 906 | ** For each table that is not excluded by the filter callback, this function ** tests that the target database contains a compatible table. A table is ** considered compatible if all of the following are true: ** ** <ul> ** <li> The table has the same name as the name recorded in the ** changeset, and | | | 901 902 903 904 905 906 907 908 909 910 911 912 913 914 915 | ** For each table that is not excluded by the filter callback, this function ** tests that the target database contains a compatible table. A table is ** considered compatible if all of the following are true: ** ** <ul> ** <li> The table has the same name as the name recorded in the ** changeset, and ** <li> The table has at least as many columns as recorded in the ** changeset, and ** <li> The table has primary key columns in the same position as ** recorded in the changeset. ** </ul> ** ** If there is no compatible table, it is not an error, but none of the ** changes associated with the table are applied. A warning message is issued |
︙ | ︙ | |||
945 946 947 948 949 950 951 | ** original row values stored in the changeset. If it does, and the values ** stored in all non-primary key columns also match the values stored in ** the changeset the row is deleted from the target database. ** ** If a row with matching primary key values is found, but one or more of ** the non-primary key fields contains a value different from the original ** row value stored in the changeset, the conflict-handler function is | | > > > > | > > | | | | | | 946 947 948 949 950 951 952 953 954 955 956 957 958 959 960 961 962 963 964 965 966 967 968 969 970 971 972 973 974 975 976 977 978 979 980 981 982 983 984 985 986 987 988 989 990 991 992 993 994 995 996 997 998 999 1000 1001 1002 1003 1004 1005 | ** original row values stored in the changeset. If it does, and the values ** stored in all non-primary key columns also match the values stored in ** the changeset the row is deleted from the target database. ** ** If a row with matching primary key values is found, but one or more of ** the non-primary key fields contains a value different from the original ** row value stored in the changeset, the conflict-handler function is ** invoked with [SQLITE_CHANGESET_DATA] as the second argument. If the ** database table has more columns than are recorded in the changeset, ** only the values of those non-primary key fields are compared against ** the current database contents - any trailing database table columns ** are ignored. ** ** If no row with matching primary key values is found in the database, ** the conflict-handler function is invoked with [SQLITE_CHANGESET_NOTFOUND] ** passed as the second argument. ** ** If the DELETE operation is attempted, but SQLite returns SQLITE_CONSTRAINT ** (which can only happen if a foreign key constraint is violated), the ** conflict-handler function is invoked with [SQLITE_CHANGESET_CONSTRAINT] ** passed as the second argument. This includes the case where the DELETE ** operation is attempted because an earlier call to the conflict handler ** function returned [SQLITE_CHANGESET_REPLACE]. ** ** <dt>INSERT Changes<dd> ** For each INSERT change, an attempt is made to insert the new row into ** the database. If the changeset row contains fewer fields than the ** database table, the trailing fields are populated with their default ** values. ** ** If the attempt to insert the row fails because the database already ** contains a row with the same primary key values, the conflict handler ** function is invoked with the second argument set to ** [SQLITE_CHANGESET_CONFLICT]. ** ** If the attempt to insert the row fails because of some other constraint ** violation (e.g. NOT NULL or UNIQUE), the conflict handler function is ** invoked with the second argument set to [SQLITE_CHANGESET_CONSTRAINT]. ** This includes the case where the INSERT operation is re-attempted because ** an earlier call to the conflict handler function returned ** [SQLITE_CHANGESET_REPLACE]. ** ** <dt>UPDATE Changes<dd> ** For each UPDATE change, this function checks if the target database ** contains a row with the same primary key value (or values) as the ** original row values stored in the changeset. If it does, and the values ** stored in all modified non-primary key columns also match the values ** stored in the changeset the row is updated within the target database. ** ** If a row with matching primary key values is found, but one or more of ** the modified non-primary key fields contains a value different from an ** original row value stored in the changeset, the conflict-handler function ** is invoked with [SQLITE_CHANGESET_DATA] as the second argument. Since ** UPDATE changes only contain values for non-primary key fields that are ** to be modified, only those fields need to match the original values to ** avoid the SQLITE_CHANGESET_DATA conflict-handler callback. ** ** If no row with matching primary key values is found in the database, ** the conflict-handler function is invoked with [SQLITE_CHANGESET_NOTFOUND] ** passed as the second argument. |
︙ | ︙ |
Changes to test/tester.tcl.
︙ | ︙ | |||
915 916 917 918 919 920 921 | proc normalize_list {L} { set L2 [list] foreach l $L {lappend L2 $l} set L2 } | > > > > > | > > > > > > > > > > > > > > > > > > > > > | > | 915 916 917 918 919 920 921 922 923 924 925 926 927 928 929 930 931 932 933 934 935 936 937 938 939 940 941 942 943 944 945 946 947 948 949 950 951 952 953 954 955 956 957 958 959 | proc normalize_list {L} { set L2 [list] foreach l $L {lappend L2 $l} set L2 } # Either: # # do_execsql_test TESTNAME SQL ?RES? # do_execsql_test -db DB TESTNAME SQL ?RES? # proc do_execsql_test {args} { set db db if {[lindex $args 0]=="-db"} { set db [lindex $args 1] set args [lrange $args 2 end] } if {[llength $args]==2} { foreach {testname sql} $args {} set result "" } elseif {[llength $args]==3} { foreach {testname sql result} $args {} } else { error [string trim { wrong # args: should be "do_execsql_test ?-db DB? testname sql ?result?" }] } fix_testname testname uplevel do_test \ [list $testname] \ [list "execsql {$sql} $db"] \ [list [list {*}$result]] } proc do_catchsql_test {testname sql result} { fix_testname testname uplevel do_test [list $testname] [list "catchsql {$sql}"] [list $result] } proc do_timed_execsql_test {testname sql {result {}}} { fix_testname testname uplevel do_test [list $testname] [list "execsql_timed {$sql}"]\ |
︙ | ︙ |