/ Check-in [ba85bf8c]
Login

Many hyperlinks are disabled.
Use anonymous login to enable hyperlinks.

Overview
Comment:Skip unique constraint enforcement if compiled with SQLITE_OMIT_UNIQUE_ENFORCEMENT.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1:ba85bf8cb88f7ae220d919f5c23f51d9dcedc843
User & Date: shaneh 2011-03-10 21:13:18
Context
2011-03-10
21:48
Simplification of tests and more added for SQLITE_OMIT_UNIQUE_ENFORCEMENT tests. check-in: 75a38411 user: shaneh tags: trunk
21:13
Skip unique constraint enforcement if compiled with SQLITE_OMIT_UNIQUE_ENFORCEMENT. check-in: ba85bf8c user: shaneh tags: trunk
03:54
Minor clean-up of previous mem5 allocator fix. check-in: 36438423 user: shaneh tags: trunk
Changes
Hide Diffs Side-by-Side Diffs Ignore Whitespace Patch

Changes to src/insert.c.

  1306   1306   
  1307   1307     /* Test all UNIQUE constraints by creating entries for each UNIQUE
  1308   1308     ** index and making sure that duplicate entries do not already exist.
  1309   1309     ** Add the new records to the indices as we go.
  1310   1310     */
  1311   1311     for(iCur=0, pIdx=pTab->pIndex; pIdx; pIdx=pIdx->pNext, iCur++){
  1312   1312       int regIdx;
         1313  +#ifndef SQLITE_OMIT_UNIQUE_ENFORCEMENT
  1313   1314       int regR;
  1314         -
         1315  +#endif
  1315   1316       if( aRegIdx[iCur]==0 ) continue;  /* Skip unused indices */
  1316   1317   
  1317   1318       /* Create a key for accessing the index entry */
  1318   1319       regIdx = sqlite3GetTempRange(pParse, pIdx->nColumn+1);
  1319   1320       for(i=0; i<pIdx->nColumn; i++){
  1320   1321         int idx = pIdx->aiColumn[i];
  1321   1322         if( idx==pTab->iPKey ){
................................................................................
  1324   1325           sqlite3VdbeAddOp2(v, OP_SCopy, regData+idx, regIdx+i);
  1325   1326         }
  1326   1327       }
  1327   1328       sqlite3VdbeAddOp2(v, OP_SCopy, regRowid, regIdx+i);
  1328   1329       sqlite3VdbeAddOp3(v, OP_MakeRecord, regIdx, pIdx->nColumn+1, aRegIdx[iCur]);
  1329   1330       sqlite3VdbeChangeP4(v, -1, sqlite3IndexAffinityStr(v, pIdx), 0);
  1330   1331       sqlite3ExprCacheAffinityChange(pParse, regIdx, pIdx->nColumn+1);
         1332  +
         1333  +#ifdef SQLITE_OMIT_UNIQUE_ENFORCEMENT
         1334  +    pIdx->onError = OE_None;
         1335  +    sqlite3ReleaseTempRange(pParse, regIdx, pIdx->nColumn+1);
         1336  +    continue;  /* Treat pIdx as if it is not a UNIQUE index */
         1337  +#else
  1331   1338   
  1332   1339       /* Find out what action to take in case there is an indexing conflict */
  1333   1340       onError = pIdx->onError;
  1334   1341       if( onError==OE_None ){ 
  1335   1342         sqlite3ReleaseTempRange(pParse, regIdx, pIdx->nColumn+1);
  1336   1343         continue;  /* pIdx is not a UNIQUE index */
  1337   1344       }
................................................................................
  1398   1405           );
  1399   1406           seenReplace = 1;
  1400   1407           break;
  1401   1408         }
  1402   1409       }
  1403   1410       sqlite3VdbeJumpHere(v, j3);
  1404   1411       sqlite3ReleaseTempReg(pParse, regR);
         1412  +#endif
  1405   1413     }
  1406   1414     
  1407   1415     if( pbMayReplace ){
  1408   1416       *pbMayReplace = seenReplace;
  1409   1417     }
  1410   1418   }
  1411   1419   

Changes to src/test_config.c.

   470    470   #endif
   471    471   
   472    472   #ifdef SQLITE_OMIT_TRUNCATE_OPTIMIZATION
   473    473     Tcl_SetVar2(interp, "sqlite_options", "truncate_opt", "0", TCL_GLOBAL_ONLY);
   474    474   #else
   475    475     Tcl_SetVar2(interp, "sqlite_options", "truncate_opt", "1", TCL_GLOBAL_ONLY);
   476    476   #endif
          477  +
          478  +#ifdef SQLITE_OMIT_UNIQUE_ENFORCEMENT
          479  +  Tcl_SetVar2(interp, "sqlite_options", "unique_enforcement", "0", TCL_GLOBAL_ONLY);
          480  +#else
          481  +  Tcl_SetVar2(interp, "sqlite_options", "unique_enforcement", "1", TCL_GLOBAL_ONLY);
          482  +#endif
   477    483   
   478    484   #ifdef SQLITE_OMIT_UTF16
   479    485     Tcl_SetVar2(interp, "sqlite_options", "utf16", "0", TCL_GLOBAL_ONLY);
   480    486   #else
   481    487     Tcl_SetVar2(interp, "sqlite_options", "utf16", "1", TCL_GLOBAL_ONLY);
   482    488   #endif
   483    489   

Added test/omitunique.test.

            1  +# 2011 March 10
            2  +#
            3  +# The author disclaims copyright to this source code.  In place of
            4  +# a legal notice, here is a blessing:
            5  +#
            6  +#    May you do good and not evil.
            7  +#    May you find forgiveness for yourself and forgive others.
            8  +#    May you share freely, never taking more than you give.
            9  +#
           10  +#***********************************************************************
           11  +# This file implements regression tests for SQLite library.  The
           12  +# focus of this file is testing the SQLITE_OMIT_UNIQUE_ENFORCEMENT
           13  +# compiler option.
           14  +#
           15  +
           16  +set testdir [file dirname $argv0]
           17  +source $testdir/tester.tcl
           18  +
           19  +# table with UNIQUE keyword on column
           20  +do_test omitunique-1.1.1 {
           21  +  catchsql {CREATE TABLE t1(a TEXT UNIQUE);  }
           22  +} {0 {}}
           23  +do_test omitunique-1.1.2 {
           24  +  catchsql {INSERT INTO t1(a) VALUES('abc'); }
           25  +} {0 {}}
           26  +do_test omitunique-1.1.3 {
           27  +  catchsql {INSERT INTO t1(a) VALUES('123'); }
           28  +} {0 {}}
           29  +
           30  +# table with UNIQUE index on column
           31  +do_test omitunique-1.2.1 {
           32  +  catchsql {
           33  +    CREATE TABLE t2(a TEXT);
           34  +    CREATE UNIQUE INDEX t2a ON t2(a);
           35  +  }
           36  +} {0 {}}
           37  +do_test omitunique-1.2.2 {
           38  +  catchsql {INSERT INTO t2(a) VALUES('abc'); }
           39  +} {0 {}}
           40  +do_test omitunique-1.2.3 {
           41  +  catchsql {INSERT INTO t2(a) VALUES('123'); }
           42  +} {0 {}}
           43  +
           44  +# table with regular index on column
           45  +do_test omitunique-1.3.1 {
           46  +  catchsql {
           47  +    CREATE TABLE t3(a TEXT);
           48  +    CREATE INDEX t3a ON t3(a);
           49  +  }
           50  +} {0 {}}
           51  +do_test omitunique-1.3.2 {
           52  +  catchsql {INSERT INTO t3(a) VALUES('abc'); }
           53  +} {0 {}}
           54  +do_test omitunique-1.3.3 {
           55  +  catchsql {INSERT INTO t3(a) VALUES('123'); }
           56  +} {0 {}}
           57  +
           58  +# table with no index on column
           59  +do_test omitunique-1.4.1 {
           60  +  catchsql {
           61  +    CREATE TABLE t4(a TEXT);
           62  +  }
           63  +} {0 {}}
           64  +do_test omitunique-1.4.2 {
           65  +  catchsql {INSERT INTO t4(a) VALUES('abc'); }
           66  +} {0 {}}
           67  +do_test omitunique-1.4.3 {
           68  +  catchsql {INSERT INTO t4(a) VALUES('123'); }
           69  +} {0 {}}
           70  +
           71  +# run our tests using several table/index forms
           72  +foreach {j tbl uniq cnt_enforce cnt_omit qp_est} {
           73  +1 {t1} 1 1 9 1
           74  +2 {t2} 1 1 9 1
           75  +3 {t3} 0 9 9 10
           76  +4 {t4} 0 9 9 100000
           77  +} {
           78  +
           79  +  # check various INSERT commands
           80  +  foreach {i cmd err} {
           81  +    1 {INSERT}             1   
           82  +    2 {INSERT OR IGNORE}   0 
           83  +    3 {INSERT OR REPLACE}  0
           84  +    4 {REPLACE}            0
           85  +    5 {INSERT OR FAIL}     1
           86  +    6 {INSERT OR ABORT}    1
           87  +    7 {INSERT OR ROLLBACK} 1
           88  +  } {
           89  +
           90  +    if { $uniq==0 || $err==0 } { 
           91  +      set msg {0 {}} 
           92  +    } {
           93  +      set msg {1 {column a is not unique}}
           94  +    }
           95  +
           96  +    ifcapable unique_enforcement {
           97  +      ifcapable explain {
           98  +        do_test omitunique-2.1.$j.$i.1 {
           99  +          set x [execsql [ subst {EXPLAIN $cmd INTO $tbl (a) VALUES('abc')}]]
          100  +          regexp { IsUnique } $x
          101  +        } $uniq
          102  +      }
          103  +      do_test omitunique-2.1.$j.$i.2 {
          104  +        catchsql [ subst {$cmd INTO $tbl (a) VALUES('abc')}]
          105  +      } $msg
          106  +    }
          107  +    ifcapable !unique_enforcement {
          108  +      ifcapable explain {
          109  +        do_test omitunique-2.1.$j.$i.1 {
          110  +          set x [execsql [ subst {EXPLAIN $cmd INTO $tbl (a) VALUES('abc')}]]
          111  +          regexp { IsUnique } $x
          112  +        } {0}
          113  +      }
          114  +      do_test omitunique-2.1.$j.$i.2 {
          115  +        catchsql [ subst {$cmd INTO $tbl (a) VALUES('abc')}]
          116  +      } {0 {}}
          117  +    }
          118  +
          119  +  }
          120  +  # end foreach cmd
          121  +
          122  +  # check UPDATE command
          123  +  ifcapable unique_enforcement {
          124  +    ifcapable explain {
          125  +      do_test omitunique-2.2.$j.1 {
          126  +        set x [execsql [ subst {EXPLAIN UPDATE $tbl SET a='abc'}]]
          127  +        regexp { IsUnique } $x
          128  +      } $uniq
          129  +    }
          130  +    do_test omitunique-2.2.$j.2 {
          131  +      catchsql [ subst {UPDATE $tbl SET a='abc'}]
          132  +    } $msg
          133  +  }
          134  +  ifcapable !unique_enforcement {
          135  +    ifcapable explain {
          136  +      do_test omitunique-2.2.$j.1 {
          137  +        set x [execsql [ subst {EXPLAIN UPDATE $tbl SET a='abc'}]]
          138  +        regexp { IsUnique } $x
          139  +      } {0}
          140  +    }
          141  +    do_test omitunique-2.2.$j.2 {
          142  +      catchsql [ subst {UPDATE $tbl SET a='abc' }]
          143  +    } {0 {}}
          144  +  }
          145  +
          146  +  # check record counts
          147  +  ifcapable unique_enforcement {
          148  +    do_test omitunique-2.3.$j {
          149  +      execsql [ subst {SELECT count(*) FROM $tbl WHERE a='abc' }]
          150  +    } $cnt_enforce
          151  +  }
          152  +  ifcapable !unique_enforcement {
          153  +    do_test omitunique-2.3.$j {
          154  +      execsql [ subst {SELECT count(*) FROM $tbl WHERE a='abc' }]
          155  +    } $cnt_omit
          156  +  }
          157  +
          158  +  # make sure the query planner row estimate not affected because of omit enforcement
          159  +  ifcapable explain {
          160  +    do_test omitunique-2.4.$j {
          161  +      set x [execsql [ subst {EXPLAIN QUERY PLAN SELECT count(*) FROM $tbl WHERE a='abc' }]]
          162  +      set y [ subst {~$qp_est row} ]
          163  +      regexp $y $x
          164  +    } {1}
          165  +  }
          166  +
          167  +}
          168  +# end foreach tbl
          169  +
          170  +finish_test