/ Check-in [b8699943]
Login

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:b86999436ec2414c990ba720441fe316f647eef6
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
Hide Diffs Side-by-Side Diffs Ignore Whitespace Patch

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 \