From e08160bdc1160a20a27f8f466e1be0e1deda6ab5 Mon Sep 17 00:00:00 2001 From: shaneh Date: Thu, 10 Mar 2011 21:13:18 +0000 Subject: [PATCH] Skip unique constraint enforcement if compiled with SQLITE_OMIT_UNIQUE_ENFORCEMENT. FossilOrigin-Name: ba85bf8cb88f7ae220d919f5c23f51d9dcedc843 --- manifest | 15 ++-- manifest.uuid | 2 +- src/insert.c | 10 ++- src/test_config.c | 6 ++ test/omitunique.test | 170 +++++++++++++++++++++++++++++++++++++++++++ 5 files changed, 194 insertions(+), 9 deletions(-) create mode 100644 test/omitunique.test diff --git a/manifest b/manifest index f7c483dc57..b4381bea7f 100644 --- a/manifest +++ b/manifest @@ -1,5 +1,5 @@ -C Minor\sclean-up\sof\sprevious\smem5\sallocator\sfix. -D 2011-03-10T03:54:55.462 +C Skip\sunique\sconstraint\senforcement\sif\scompiled\swith\sSQLITE_OMIT_UNIQUE_ENFORCEMENT. +D 2011-03-10T21:13:18.347 F Makefile.arm-wince-mingw32ce-gcc d6df77f1f48d690bd73162294bbba7f59507c72f F Makefile.in 27701a1653595a1f2187dc61c8117e00a6c1d50f F Makefile.linux-gcc 91d710bdc4998cb015f39edf3cb314ec4f4d7e23 @@ -138,7 +138,7 @@ F src/global.c 02335177cf6946fe5525c6f0755cf181140debf3 F src/hash.c 458488dcc159c301b8e7686280ab209f1fb915af F src/hash.h 2894c932d84d9f892d4b4023a75e501f83050970 F src/hwtime.h d32741c8f4df852c7d959236615444e2b1063b08 -F src/insert.c 036cfac4be398db9dccdf3200a45e3a7260fcfb2 +F src/insert.c 06133d9ea3c17d9adf98a40c87da31dc028759f0 F src/journal.c 552839e54d1bf76fb8f7abe51868b66acacf6a0e F src/legacy.c a199d7683d60cef73089e892409113e69c23a99f F src/lempar.c 7f026423f4d71d989e719a743f98a1cbd4e6d99e @@ -198,7 +198,7 @@ F src/test_async.c 0612a752896fad42d55c3999a5122af10dcf22ad F src/test_autoext.c 30e7bd98ab6d70a62bb9ba572e4c7df347fe645e F src/test_backup.c c129c91127e9b46e335715ae2e75756e25ba27de F src/test_btree.c 47cd771250f09cdc6e12dda5bc71bc0b3abc96e2 -F src/test_config.c 9f025a7f3686c94e82dc6d6bd3cbf0f89cd67487 +F src/test_config.c 62f0f8f934b1d5c7e4cd4f506ae453a1117b47d7 F src/test_demovfs.c 0aed671636735116fc872c5b03706fd5612488b5 F src/test_devsym.c e7498904e72ba7491d142d5c83b476c4e76993bc F src/test_func.c cbdec5cededa0761daedde5baf06004a9bf416b5 @@ -584,6 +584,7 @@ F test/notify2.test 195a467e021f74197be2c4fb02d6dee644b8d8db F test/notify3.test d60923e186e0900f4812a845fcdfd8eea096e33a F test/notnull.test cc7c78340328e6112a13c3e311a9ab3127114347 F test/null.test a8b09b8ed87852742343b33441a9240022108993 +F test/omitunique.test f153c7540f2df6dc49c56b97242bda6b60e87fa6 F test/openv2.test af02ed0a9cbc0d2a61b8f35171d4d117e588e4ec F test/oserror.test d1f085bdbac20456fccdf5877f52016453654fc3 F test/pager1.test d8672fd0af5f4f9b99b06283d00f01547809bebe @@ -912,7 +913,7 @@ F tool/speedtest2.tcl ee2149167303ba8e95af97873c575c3e0fab58ff F tool/speedtest8.c 2902c46588c40b55661e471d7a86e4dd71a18224 F tool/speedtest8inst1.c 293327bc76823f473684d589a8160bde1f52c14e F tool/vdbe-compress.tcl d70ea6d8a19e3571d7ab8c9b75cba86d1173ff0f -P d7dae06fb2d57ed6b9555b774712f42077ae4155 -R 09ce3b3c494c7ecbffa2cbbcd0f16adf +P 3643842316239ff7859f0ec522736a2b9c03d22c +R b1f7b8ea9c365b449d3359fe4224fd3a U shaneh -Z 8a07294df458a912085e72ad9cffeba4 +Z c08bc88ba8510c7d87325ff4426508db diff --git a/manifest.uuid b/manifest.uuid index 953c410c6d..79f5cbdfb7 100644 --- a/manifest.uuid +++ b/manifest.uuid @@ -1 +1 @@ -3643842316239ff7859f0ec522736a2b9c03d22c \ No newline at end of file +ba85bf8cb88f7ae220d919f5c23f51d9dcedc843 \ No newline at end of file diff --git a/src/insert.c b/src/insert.c index 851f778d48..5a86484b2d 100644 --- a/src/insert.c +++ b/src/insert.c @@ -1310,8 +1310,9 @@ void sqlite3GenerateConstraintChecks( */ for(iCur=0, pIdx=pTab->pIndex; pIdx; pIdx=pIdx->pNext, iCur++){ int regIdx; +#ifndef SQLITE_OMIT_UNIQUE_ENFORCEMENT int regR; - +#endif if( aRegIdx[iCur]==0 ) continue; /* Skip unused indices */ /* Create a key for accessing the index entry */ @@ -1329,6 +1330,12 @@ void sqlite3GenerateConstraintChecks( sqlite3VdbeChangeP4(v, -1, sqlite3IndexAffinityStr(v, pIdx), 0); sqlite3ExprCacheAffinityChange(pParse, regIdx, pIdx->nColumn+1); +#ifdef SQLITE_OMIT_UNIQUE_ENFORCEMENT + pIdx->onError = OE_None; + sqlite3ReleaseTempRange(pParse, regIdx, pIdx->nColumn+1); + continue; /* Treat pIdx as if it is not a UNIQUE index */ +#else + /* Find out what action to take in case there is an indexing conflict */ onError = pIdx->onError; if( onError==OE_None ){ @@ -1402,6 +1409,7 @@ void sqlite3GenerateConstraintChecks( } sqlite3VdbeJumpHere(v, j3); sqlite3ReleaseTempReg(pParse, regR); +#endif } if( pbMayReplace ){ diff --git a/src/test_config.c b/src/test_config.c index def12a9afc..6eee524c09 100644 --- a/src/test_config.c +++ b/src/test_config.c @@ -475,6 +475,12 @@ Tcl_SetVar2(interp, "sqlite_options", "long_double", Tcl_SetVar2(interp, "sqlite_options", "truncate_opt", "1", TCL_GLOBAL_ONLY); #endif +#ifdef SQLITE_OMIT_UNIQUE_ENFORCEMENT + Tcl_SetVar2(interp, "sqlite_options", "unique_enforcement", "0", TCL_GLOBAL_ONLY); +#else + Tcl_SetVar2(interp, "sqlite_options", "unique_enforcement", "1", TCL_GLOBAL_ONLY); +#endif + #ifdef SQLITE_OMIT_UTF16 Tcl_SetVar2(interp, "sqlite_options", "utf16", "0", TCL_GLOBAL_ONLY); #else diff --git a/test/omitunique.test b/test/omitunique.test new file mode 100644 index 0000000000..633aaa07d0 --- /dev/null +++ b/test/omitunique.test @@ -0,0 +1,170 @@ +# 2011 March 10 +# +# 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. +# +#*********************************************************************** +# This file implements regression tests for SQLite library. The +# focus of this file is testing the SQLITE_OMIT_UNIQUE_ENFORCEMENT +# compiler option. +# + +set testdir [file dirname $argv0] +source $testdir/tester.tcl + +# table with UNIQUE keyword on column +do_test omitunique-1.1.1 { + catchsql {CREATE TABLE t1(a TEXT UNIQUE); } +} {0 {}} +do_test omitunique-1.1.2 { + catchsql {INSERT INTO t1(a) VALUES('abc'); } +} {0 {}} +do_test omitunique-1.1.3 { + catchsql {INSERT INTO t1(a) VALUES('123'); } +} {0 {}} + +# table with UNIQUE index on column +do_test omitunique-1.2.1 { + catchsql { + CREATE TABLE t2(a TEXT); + CREATE UNIQUE INDEX t2a ON t2(a); + } +} {0 {}} +do_test omitunique-1.2.2 { + catchsql {INSERT INTO t2(a) VALUES('abc'); } +} {0 {}} +do_test omitunique-1.2.3 { + catchsql {INSERT INTO t2(a) VALUES('123'); } +} {0 {}} + +# table with regular index on column +do_test omitunique-1.3.1 { + catchsql { + CREATE TABLE t3(a TEXT); + CREATE INDEX t3a ON t3(a); + } +} {0 {}} +do_test omitunique-1.3.2 { + catchsql {INSERT INTO t3(a) VALUES('abc'); } +} {0 {}} +do_test omitunique-1.3.3 { + catchsql {INSERT INTO t3(a) VALUES('123'); } +} {0 {}} + +# table with no index on column +do_test omitunique-1.4.1 { + catchsql { + CREATE TABLE t4(a TEXT); + } +} {0 {}} +do_test omitunique-1.4.2 { + catchsql {INSERT INTO t4(a) VALUES('abc'); } +} {0 {}} +do_test omitunique-1.4.3 { + catchsql {INSERT INTO t4(a) VALUES('123'); } +} {0 {}} + +# run our tests using several table/index forms +foreach {j tbl uniq cnt_enforce cnt_omit qp_est} { +1 {t1} 1 1 9 1 +2 {t2} 1 1 9 1 +3 {t3} 0 9 9 10 +4 {t4} 0 9 9 100000 +} { + + # check various INSERT commands + foreach {i cmd err} { + 1 {INSERT} 1 + 2 {INSERT OR IGNORE} 0 + 3 {INSERT OR REPLACE} 0 + 4 {REPLACE} 0 + 5 {INSERT OR FAIL} 1 + 6 {INSERT OR ABORT} 1 + 7 {INSERT OR ROLLBACK} 1 + } { + + if { $uniq==0 || $err==0 } { + set msg {0 {}} + } { + set msg {1 {column a is not unique}} + } + + ifcapable unique_enforcement { + ifcapable explain { + do_test omitunique-2.1.$j.$i.1 { + set x [execsql [ subst {EXPLAIN $cmd INTO $tbl (a) VALUES('abc')}]] + regexp { IsUnique } $x + } $uniq + } + do_test omitunique-2.1.$j.$i.2 { + catchsql [ subst {$cmd INTO $tbl (a) VALUES('abc')}] + } $msg + } + ifcapable !unique_enforcement { + ifcapable explain { + do_test omitunique-2.1.$j.$i.1 { + set x [execsql [ subst {EXPLAIN $cmd INTO $tbl (a) VALUES('abc')}]] + regexp { IsUnique } $x + } {0} + } + do_test omitunique-2.1.$j.$i.2 { + catchsql [ subst {$cmd INTO $tbl (a) VALUES('abc')}] + } {0 {}} + } + + } + # end foreach cmd + + # check UPDATE command + ifcapable unique_enforcement { + ifcapable explain { + do_test omitunique-2.2.$j.1 { + set x [execsql [ subst {EXPLAIN UPDATE $tbl SET a='abc'}]] + regexp { IsUnique } $x + } $uniq + } + do_test omitunique-2.2.$j.2 { + catchsql [ subst {UPDATE $tbl SET a='abc'}] + } $msg + } + ifcapable !unique_enforcement { + ifcapable explain { + do_test omitunique-2.2.$j.1 { + set x [execsql [ subst {EXPLAIN UPDATE $tbl SET a='abc'}]] + regexp { IsUnique } $x + } {0} + } + do_test omitunique-2.2.$j.2 { + catchsql [ subst {UPDATE $tbl SET a='abc' }] + } {0 {}} + } + + # check record counts + ifcapable unique_enforcement { + do_test omitunique-2.3.$j { + execsql [ subst {SELECT count(*) FROM $tbl WHERE a='abc' }] + } $cnt_enforce + } + ifcapable !unique_enforcement { + do_test omitunique-2.3.$j { + execsql [ subst {SELECT count(*) FROM $tbl WHERE a='abc' }] + } $cnt_omit + } + + # make sure the query planner row estimate not affected because of omit enforcement + ifcapable explain { + do_test omitunique-2.4.$j { + set x [execsql [ subst {EXPLAIN QUERY PLAN SELECT count(*) FROM $tbl WHERE a='abc' }]] + set y [ subst {~$qp_est row} ] + regexp $y $x + } {1} + } + +} +# end foreach tbl + +finish_test -- 2.47.2