Many hyperlinks are disabled.
Use anonymous login
to enable hyperlinks.
Overview
| Comment: | More tests for SQLITE_OMIT_UNIQUE_ENFORCEMENT and minor change to implementation. |
|---|---|
| Downloads: | Tarball | ZIP archive | SQL archive |
| Timelines: | family | ancestors | descendants | both | trunk |
| Files: | files | file ages | folders |
| SHA1: |
b86999436ec2414c990ba720441fe316 |
| User & Date: | shaneh 2011-03-12 04:58:55 |
References
|
2011-03-12
| ||
| 05:55 | Backport the SQLITE_OMIT_UNIQUE_ENFORCEMENT change from check-in [b86999436e] on the trunk. (check-in: 668b91db user: shaneh tags: branch-3.7.2) | |
Context
|
2011-03-14
| ||
| 13:54 | Merge the unix-excl VFS into the trunk. This merge also adds the -vfs option to the command-line shell. (check-in: 3934b004 user: drh tags: trunk) | |
|
2011-03-12
| ||
| 17:02 | Add the new optional "unix-excl" VFS. This VFS grabs an exclusive lock on the database preventing other processes from accessing it, but continues to allow other database connections from the same process. (check-in: 00051c32 user: drh tags: unix-excl) | |
| 04:58 | More tests for SQLITE_OMIT_UNIQUE_ENFORCEMENT and minor change to implementation. (check-in: b8699943 user: shaneh tags: trunk) | |
|
2011-03-11
| ||
| 16:15 | Remove an unused field from the unix sqlite3_file object. (check-in: f957f23a user: drh tags: trunk) | |
Changes
Changes to src/ctime.c.
297 297 "OMIT_TRACE", 298 298 #endif 299 299 #ifdef SQLITE_OMIT_TRIGGER 300 300 "OMIT_TRIGGER", 301 301 #endif 302 302 #ifdef SQLITE_OMIT_TRUNCATE_OPTIMIZATION 303 303 "OMIT_TRUNCATE_OPTIMIZATION", 304 +#endif 305 +#ifdef SQLITE_OMIT_UNIQUE_ENFORCEMENT 306 + "OMIT_UNIQUE_ENFORCEMENT", 304 307 #endif 305 308 #ifdef SQLITE_OMIT_UTF16 306 309 "OMIT_UTF16", 307 310 #endif 308 311 #ifdef SQLITE_OMIT_VACUUM 309 312 "OMIT_VACUUM", 310 313 #endif
Changes to src/insert.c.
1327 1327 } 1328 1328 sqlite3VdbeAddOp2(v, OP_SCopy, regRowid, regIdx+i); 1329 1329 sqlite3VdbeAddOp3(v, OP_MakeRecord, regIdx, pIdx->nColumn+1, aRegIdx[iCur]); 1330 1330 sqlite3VdbeChangeP4(v, -1, sqlite3IndexAffinityStr(v, pIdx), 0); 1331 1331 sqlite3ExprCacheAffinityChange(pParse, regIdx, pIdx->nColumn+1); 1332 1332 1333 1333 #ifdef SQLITE_OMIT_UNIQUE_ENFORCEMENT 1334 - pIdx->onError = OE_None; 1335 1334 sqlite3ReleaseTempRange(pParse, regIdx, pIdx->nColumn+1); 1336 1335 continue; /* Treat pIdx as if it is not a UNIQUE index */ 1337 1336 #else 1338 1337 1339 1338 /* Find out what action to take in case there is an indexing conflict */ 1340 1339 onError = pIdx->onError; 1341 1340 if( onError==OE_None ){
Changes to test/omitunique.test.
18 18 19 19 set uniq_enforced 1 20 20 ifcapable !unique_enforcement { 21 21 set uniq_enforced 0 22 22 } 23 23 24 24 # table with UNIQUE keyword on column 25 -do_test omitunique-1.1.1 { 26 - catchsql {CREATE TABLE t1(a TEXT UNIQUE); } 25 +do_test omitunique-1.1 { 26 + catchsql { CREATE TABLE t1(a TEXT UNIQUE); } 27 27 } {0 {}} 28 -do_test omitunique-1.1.2 { 29 - catchsql {INSERT INTO t1(a) VALUES('abc'); } 30 -} {0 {}} 31 -do_test omitunique-1.1.3 { 32 - catchsql {INSERT INTO t1(a) VALUES('123'); } 28 + 29 +# table with UNIQUE clause on column 30 +do_test omitunique-1.2 { 31 + catchsql { CREATE TABLE t2(a TEXT, UNIQUE(a)); } 33 32 } {0 {}} 34 33 35 34 # table with UNIQUE index on column 36 -do_test omitunique-1.2.1 { 35 +do_test omitunique-1.3 { 37 36 catchsql { 38 - CREATE TABLE t2(a TEXT); 39 - CREATE UNIQUE INDEX t2a ON t2(a); 37 + CREATE TABLE t3(a TEXT); 38 + CREATE UNIQUE INDEX t3a ON t3(a); 40 39 } 41 40 } {0 {}} 42 -do_test omitunique-1.2.2 { 43 - catchsql {INSERT INTO t2(a) VALUES('abc'); } 44 -} {0 {}} 45 -do_test omitunique-1.2.3 { 46 - catchsql {INSERT INTO t2(a) VALUES('123'); } 47 -} {0 {}} 48 41 49 42 # table with regular index on column 50 -do_test omitunique-1.3.1 { 43 +do_test omitunique-1.4 { 51 44 catchsql { 52 - CREATE TABLE t3(a TEXT); 53 - CREATE INDEX t3a ON t3(a); 45 + CREATE TABLE t4(a TEXT); 46 + CREATE INDEX t4a ON t4(a); 54 47 } 55 48 } {0 {}} 56 -do_test omitunique-1.3.2 { 57 - catchsql {INSERT INTO t3(a) VALUES('abc'); } 58 -} {0 {}} 59 -do_test omitunique-1.3.3 { 60 - catchsql {INSERT INTO t3(a) VALUES('123'); } 61 -} {0 {}} 62 49 63 50 # table with no index on column 64 -do_test omitunique-1.4.1 { 65 - catchsql { 66 - CREATE TABLE t4(a TEXT); 67 - } 68 -} {0 {}} 69 -do_test omitunique-1.4.2 { 70 - catchsql {INSERT INTO t4(a) VALUES('abc'); } 71 -} {0 {}} 72 -do_test omitunique-1.4.3 { 73 - catchsql {INSERT INTO t4(a) VALUES('123'); } 51 +do_test omitunique-1.5 { 52 + catchsql { CREATE TABLE t5(a TEXT); } 74 53 } {0 {}} 75 54 76 55 # run our tests using several table/index forms 77 -foreach {j tbl uniq cnt_enforce cnt_omit qp_est} { 78 -1 {t1} 1 1 9 1 79 -2 {t2} 1 1 9 1 80 -3 {t3} 0 9 9 10 81 -4 {t4} 0 9 9 100000 56 +foreach {j tbl uniq cnt qp_est stat_enforce stat_omit } { 57 +1 {t1} 1 1 1 {2 1} {9 9} 58 +2 {t2} 1 1 1 {2 1} {9 9} 59 +3 {t3} 1 1 1 {2 1} {9 9} 60 +4 {t4} 0 9 10 {9 9} {9 9} 61 +5 {t5} 0 9 100000 9 9 82 62 } { 63 + 64 + do_test omitunique-2.0.$j.1 { 65 + catchsql [ subst {INSERT INTO $tbl (a) VALUES('abc'); }] 66 + } {0 {}} 67 + do_test omitunique-2.0.$j.2 { 68 + catchsql [ subst {INSERT INTO $tbl (a) VALUES('123'); }] 69 + } {0 {}} 83 70 84 71 # check various INSERT commands 85 72 foreach {i cmd err} { 86 73 1 {INSERT} 1 87 74 2 {INSERT OR IGNORE} 0 88 75 3 {INSERT OR REPLACE} 0 89 76 4 {REPLACE} 0 90 77 5 {INSERT OR FAIL} 1 91 78 6 {INSERT OR ABORT} 1 92 79 7 {INSERT OR ROLLBACK} 1 93 80 } { 94 81 95 82 ifcapable explain { 83 + set x [execsql [ subst { EXPLAIN $cmd INTO $tbl (a) VALUES('abc'); }]] 96 84 ifcapable unique_enforcement { 97 85 do_test omitunique-2.1.$j.$i.1 { 98 - set x [execsql [ subst {EXPLAIN $cmd INTO $tbl (a) VALUES('abc')}]] 99 86 regexp { IsUnique } $x 100 87 } $uniq 101 88 } 102 89 ifcapable !unique_enforcement { 103 90 do_test omitunique-2.1.$j.$i.1 { 104 - set x [execsql [ subst {EXPLAIN $cmd INTO $tbl (a) VALUES('abc')}]] 105 91 regexp { IsUnique } $x 106 92 } {0} 107 93 } 108 - do_test omitunique-2.1.$j.2 { 109 - set x [execsql [ subst {EXPLAIN $cmd INTO $tbl (a) VALUES('abc')}]] 110 - regexp { Next } $x 111 - } {0} 112 94 } 113 95 114 96 if { $uniq_enforced==0 || $uniq==0 || $err==0 } { 115 97 set msg {0 {}} 116 98 } { 117 99 set msg {1 {column a is not unique}} 118 100 } 119 101 do_test omitunique-2.1.$j.$i.3 { 120 - catchsql [ subst {$cmd INTO $tbl (a) VALUES('abc')}] 102 + catchsql [ subst {$cmd INTO $tbl (a) VALUES('abc'); }] 121 103 } $msg 122 104 123 105 } 124 106 # end foreach cmd 125 107 126 108 # check UPDATE command 127 109 ifcapable explain { 110 + set x [execsql [ subst { EXPLAIN UPDATE $tbl SET a='abc'; }]] 128 111 ifcapable unique_enforcement { 129 112 do_test omitunique-2.2.$j.1 { 130 - set x [execsql [ subst {EXPLAIN UPDATE $tbl SET a='abc'}]] 131 113 regexp { IsUnique } $x 132 114 } $uniq 133 115 } 134 116 ifcapable !unique_enforcement { 135 117 do_test omitunique-2.2.$j.1 { 136 - set x [execsql [ subst {EXPLAIN UPDATE $tbl SET a='abc'}]] 137 118 regexp { IsUnique } $x 138 119 } {0} 139 120 } 140 -# comment out for now 141 -# do_test omitunique-2.2.$j.2 { 142 -# set x [execsql [ subst {EXPLAIN UPDATE $tbl SET a='abc' WHERE a<>'abc'}]] 143 -# regexp { Next } $x 144 -# } {0} 145 121 } 146 122 if { $uniq_enforced==0 || $uniq==0 } { 147 123 set msg {0 {}} 148 124 } { 149 125 set msg {1 {column a is not unique}} 150 126 } 151 127 do_test omitunique-2.2.$j.3 { 152 - catchsql [ subst {UPDATE $tbl SET a='abc'}] 128 + catchsql [ subst { UPDATE $tbl SET a='abc'; }] 153 129 } $msg 154 130 155 131 # check record counts 156 - ifcapable unique_enforcement { 157 - do_test omitunique-2.3.$j { 158 - execsql [ subst {SELECT count(*) FROM $tbl WHERE a='abc' }] 159 - } $cnt_enforce 160 - } 161 - ifcapable !unique_enforcement { 162 - do_test omitunique-2.3.$j { 163 - execsql [ subst {SELECT count(*) FROM $tbl WHERE a='abc' }] 164 - } $cnt_omit 165 - } 132 + do_test omitunique-2.3.$j { 133 + execsql [ subst { SELECT count(*) FROM $tbl WHERE a='abc'; }] 134 + } $cnt 166 135 167 136 # make sure the query planner row estimate not affected because of omit enforcement 168 137 ifcapable explain { 169 138 do_test omitunique-2.4.$j { 170 - set x [ execsql [ subst {EXPLAIN QUERY PLAN SELECT count(*) FROM $tbl WHERE a='abc' }]] 139 + set x [ execsql [ subst { EXPLAIN QUERY PLAN SELECT count(*) FROM $tbl WHERE a='abc'; }]] 171 140 set y [ subst {~$qp_est row} ] 172 141 regexp $y $x 173 142 } {1} 174 143 } 144 + 145 + # make sure we omit extra OP_Next opcodes when the UNIQUE constraints 146 + # mean there will only be a single pass through the code 147 + ifcapable explain { 148 + set x [execsql [ subst { EXPLAIN SELECT * FROM $tbl WHERE a='abc'; }]] 149 + do_test omitunique-2.5.$j { 150 + if { [ regexp { Next } $x ] } { expr { 0 } } { expr { 1 } } 151 + } $uniq 152 + } 153 + 154 + # make sure analyze index stats correct 155 + ifcapable analyze { 156 + if { $uniq_enforced==0 } { 157 + set msg [ list $stat_omit ] 158 + } { 159 + set msg [ list $stat_enforce ] 160 + } 161 + do_test omitunique-2.6.$j { 162 + execsql [ subst { ANALYZE $tbl; } ] 163 + execsql [ subst { SELECT stat FROM sqlite_stat1 WHERE tbl='$tbl'; } ] 164 + } $msg 165 + } 175 166 176 167 } 177 168 # end foreach tbl 178 169 179 170 finish_test
Changes to tool/omittest.tcl.
198 198 SQLITE_OMIT_SHARED_CACHE \ 199 199 SQLITE_OMIT_SUBQUERY \ 200 200 SQLITE_OMIT_TCL_VARIABLE \ 201 201 SQLITE_OMIT_TEMPDB \ 202 202 SQLITE_OMIT_TRACE \ 203 203 SQLITE_OMIT_TRIGGER \ 204 204 SQLITE_OMIT_TRUNCATE_OPTIMIZATION \ 205 + SQLITE_OMIT_UNIQUE_ENFORCEMENT \ 205 206 SQLITE_OMIT_UTF16 \ 206 207 SQLITE_OMIT_VACUUM \ 207 208 SQLITE_OMIT_VIEW \ 208 209 SQLITE_OMIT_VIRTUALTABLE \ 209 210 SQLITE_OMIT_WAL \ 210 211 SQLITE_OMIT_WSD \ 211 212 SQLITE_OMIT_XFER_OPT \