SQLite

Check-in [b86999436e]
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
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1: b86999436ec2414c990ba720441fe316f647eef6
User & Date: shaneh 2011-03-12 04:58:55.547
References
2011-03-12
05:55
Backport the SQLITE_OMIT_UNIQUE_ENFORCEMENT change from check-in [b86999436e] on the trunk. (check-in: 668b91dbff 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: 3934b004e9 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: 00051c3296 user: drh tags: unix-excl)
04:58
More tests for SQLITE_OMIT_UNIQUE_ENFORCEMENT and minor change to implementation. (check-in: b86999436e user: shaneh tags: trunk)
2011-03-11
16:15
Remove an unused field from the unix sqlite3_file object. (check-in: f957f23a8a user: drh tags: trunk)
Changes
Unified Diff Ignore Whitespace Patch
Changes to src/ctime.c.
︙︙
297
298
299
300
301
302
303



304
305
306
307
308
309
310

  "OMIT_TRACE",
#endif
#ifdef SQLITE_OMIT_TRIGGER
  "OMIT_TRIGGER",
#endif
#ifdef SQLITE_OMIT_TRUNCATE_OPTIMIZATION
  "OMIT_TRUNCATE_OPTIMIZATION",



#endif
#ifdef SQLITE_OMIT_UTF16
  "OMIT_UTF16",
#endif
#ifdef SQLITE_OMIT_VACUUM
  "OMIT_VACUUM",
#endif








>
>
>








297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313

  "OMIT_TRACE",
#endif
#ifdef SQLITE_OMIT_TRIGGER
  "OMIT_TRIGGER",
#endif
#ifdef SQLITE_OMIT_TRUNCATE_OPTIMIZATION
  "OMIT_TRUNCATE_OPTIMIZATION",
#endif
#ifdef SQLITE_OMIT_UNIQUE_ENFORCEMENT
  "OMIT_UNIQUE_ENFORCEMENT",
#endif
#ifdef SQLITE_OMIT_UTF16
  "OMIT_UTF16",
#endif
#ifdef SQLITE_OMIT_VACUUM
  "OMIT_VACUUM",
#endif
︙︙
Changes to src/insert.c.
︙︙
1327
1328
1329
1330
1331
1332
1333
1334
1335
1336
1337
1338
1339
1340
1341

    }
    sqlite3VdbeAddOp2(v, OP_SCopy, regRowid, regIdx+i);
    sqlite3VdbeAddOp3(v, OP_MakeRecord, regIdx, pIdx->nColumn+1, aRegIdx[iCur]);
    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 ){ 








<








1327
1328
1329
1330
1331
1332
1333

1334
1335
1336
1337
1338
1339
1340

    }
    sqlite3VdbeAddOp2(v, OP_SCopy, regRowid, regIdx+i);
    sqlite3VdbeAddOp3(v, OP_MakeRecord, regIdx, pIdx->nColumn+1, aRegIdx[iCur]);
    sqlite3VdbeChangeP4(v, -1, sqlite3IndexAffinityStr(v, pIdx), 0);
    sqlite3ExprCacheAffinityChange(pParse, regIdx, pIdx->nColumn+1);

#ifdef SQLITE_OMIT_UNIQUE_ENFORCEMENT

    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 ){
︙︙
Changes to test/omitunique.test.
︙︙
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


set uniq_enforced 1
ifcapable !unique_enforcement {
  set uniq_enforced 0
}

# 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
  } {

    ifcapable explain {

      ifcapable unique_enforcement {
          do_test omitunique-2.1.$j.$i.1 {
            set x [execsql [ subst {EXPLAIN $cmd INTO $tbl (a) VALUES('abc')}]]
            regexp { IsUnique } $x
          } $uniq
      }
      ifcapable !unique_enforcement {
          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.2 {
        set x [execsql [ subst {EXPLAIN $cmd INTO $tbl (a) VALUES('abc')}]]
        regexp { Next } $x
      } {0}
    }

    if { $uniq_enforced==0 || $uniq==0 || $err==0 } { 
      set msg {0 {}}
    } {
      set msg {1 {column a is not unique}}
    }
    do_test omitunique-2.1.$j.$i.3 {
      catchsql [ subst {$cmd INTO $tbl (a) VALUES('abc')}]
    } $msg

  }
  # end foreach cmd

  # check UPDATE command
  ifcapable explain {

    ifcapable unique_enforcement {
        do_test omitunique-2.2.$j.1 {
          set x [execsql [ subst {EXPLAIN UPDATE $tbl SET a='abc'}]]
          regexp { IsUnique } $x
        } $uniq
    }
    ifcapable !unique_enforcement {
        do_test omitunique-2.2.$j.1 {
          set x [execsql [ subst {EXPLAIN UPDATE $tbl SET a='abc'}]]
          regexp { IsUnique } $x
        } {0}
    }
# comment out for now
#    do_test omitunique-2.2.$j.2 {
#      set x [execsql [ subst {EXPLAIN UPDATE $tbl SET a='abc' WHERE a<>'abc'}]]
#      regexp { Next } $x
#    } {0}
  }
  if { $uniq_enforced==0 || $uniq==0 } { 
    set msg {0 {}}
  } {
    set msg {1 {column a is not unique}}
  }
  do_test omitunique-2.2.$j.3 {
    catchsql [ subst {UPDATE $tbl SET a='abc'}]
  } $msg

  # 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








|
|

|
|
<
|
|



|

|
|

<
<
<
<
<
<



|

|
|


<
<
<
<
<
<


|
|
<
<
<
<
<
<
<
<



|
|
|
|
|
>

>
>
>
>
>
>
>













>


<





<



<
<
<
<








|







>


<





<



<
<
<
<
<







|



<
|
|
|
<
<
<
<
<
<




|




>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>






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


set uniq_enforced 1
ifcapable !unique_enforcement {
  set uniq_enforced 0
}

# table with UNIQUE keyword on column
do_test omitunique-1.1 {
  catchsql { CREATE TABLE t1(a TEXT UNIQUE); }
} {0 {}}

# table with UNIQUE clause on column

do_test omitunique-1.2 {
  catchsql { CREATE TABLE t2(a TEXT, UNIQUE(a)); }
} {0 {}}

# table with UNIQUE index on column
do_test omitunique-1.3 {
  catchsql {
    CREATE TABLE t3(a TEXT);
    CREATE UNIQUE INDEX t3a ON t3(a);
  }






} {0 {}}

# table with regular index on column
do_test omitunique-1.4 {
  catchsql {
    CREATE TABLE t4(a TEXT);
    CREATE INDEX t4a ON t4(a);
  }
} {0 {}}







# table with no index on column
do_test omitunique-1.5 {
  catchsql { CREATE TABLE t5(a TEXT); }








} {0 {}}

# run our tests using several table/index forms
foreach {j tbl uniq cnt qp_est stat_enforce stat_omit } {
1 {t1} 1 1 1      {2 1} {9 9}
2 {t2} 1 1 1      {2 1} {9 9}
3 {t3} 1 1 1      {2 1} {9 9}
4 {t4} 0 9 10     {9 9} {9 9}
5 {t5} 0 9 100000 9     9
} {

  do_test omitunique-2.0.$j.1 {
    catchsql [ subst {INSERT INTO $tbl (a) VALUES('abc'); }]
  } {0 {}}
  do_test omitunique-2.0.$j.2 {
    catchsql [ subst {INSERT INTO $tbl (a) VALUES('123'); }]
  } {0 {}}

  # 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
  } {

    ifcapable explain {
      set x [execsql [ subst { EXPLAIN $cmd INTO $tbl (a) VALUES('abc'); }]]
      ifcapable unique_enforcement {
          do_test omitunique-2.1.$j.$i.1 {

            regexp { IsUnique } $x
          } $uniq
      }
      ifcapable !unique_enforcement {
          do_test omitunique-2.1.$j.$i.1 {

            regexp { IsUnique } $x
          } {0}
      }




    }

    if { $uniq_enforced==0 || $uniq==0 || $err==0 } { 
      set msg {0 {}}
    } {
      set msg {1 {column a is not unique}}
    }
    do_test omitunique-2.1.$j.$i.3 {
      catchsql [ subst {$cmd INTO $tbl (a) VALUES('abc'); }]
    } $msg

  }
  # end foreach cmd

  # check UPDATE command
  ifcapable explain {
    set x [execsql [ subst { EXPLAIN UPDATE $tbl SET a='abc'; }]]
    ifcapable unique_enforcement {
        do_test omitunique-2.2.$j.1 {

          regexp { IsUnique } $x
        } $uniq
    }
    ifcapable !unique_enforcement {
        do_test omitunique-2.2.$j.1 {

          regexp { IsUnique } $x
        } {0}
    }





  }
  if { $uniq_enforced==0 || $uniq==0 } { 
    set msg {0 {}}
  } {
    set msg {1 {column a is not unique}}
  }
  do_test omitunique-2.2.$j.3 {
    catchsql [ subst { UPDATE $tbl SET a='abc'; }]
  } $msg

  # check record counts

  do_test omitunique-2.3.$j {
    execsql [ subst { SELECT count(*) FROM $tbl WHERE a='abc'; }]
  } $cnt







  # 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}
  }

  # make sure we omit extra OP_Next opcodes when the UNIQUE constraints 
  # mean there will only be a single pass through the code 
  ifcapable explain {
    set x [execsql [ subst { EXPLAIN SELECT * FROM $tbl WHERE a='abc'; }]]
    do_test omitunique-2.5.$j {
      if { [ regexp { Next } $x ] } { expr { 0 } } { expr { 1 } }
    } $uniq
  }

  # make sure analyze index stats correct
  ifcapable analyze {
    if { $uniq_enforced==0 } { 
      set msg [ list $stat_omit ]
    } {
      set msg [ list $stat_enforce ]
    }
    do_test omitunique-2.6.$j {
      execsql [ subst { ANALYZE $tbl; } ]
      execsql [ subst { SELECT stat FROM sqlite_stat1 WHERE tbl='$tbl'; } ]
    } $msg
  }

}
# end foreach tbl

finish_test
Changes to tool/omittest.tcl.
︙︙
198
199
200
201
202
203
204

205
206
207
208
209
210
211

    SQLITE_OMIT_SHARED_CACHE \
    SQLITE_OMIT_SUBQUERY \
    SQLITE_OMIT_TCL_VARIABLE \
    SQLITE_OMIT_TEMPDB \
    SQLITE_OMIT_TRACE \
    SQLITE_OMIT_TRIGGER \
    SQLITE_OMIT_TRUNCATE_OPTIMIZATION \

    SQLITE_OMIT_UTF16 \
    SQLITE_OMIT_VACUUM \
    SQLITE_OMIT_VIEW \
    SQLITE_OMIT_VIRTUALTABLE \
    SQLITE_OMIT_WAL \
    SQLITE_OMIT_WSD \
    SQLITE_OMIT_XFER_OPT \








>








198
199
200
201
202
203
204
205
206
207
208
209
210
211
212

    SQLITE_OMIT_SHARED_CACHE \
    SQLITE_OMIT_SUBQUERY \
    SQLITE_OMIT_TCL_VARIABLE \
    SQLITE_OMIT_TEMPDB \
    SQLITE_OMIT_TRACE \
    SQLITE_OMIT_TRIGGER \
    SQLITE_OMIT_TRUNCATE_OPTIMIZATION \
    SQLITE_OMIT_UNIQUE_ENFORCEMENT \
    SQLITE_OMIT_UTF16 \
    SQLITE_OMIT_VACUUM \
    SQLITE_OMIT_VIEW \
    SQLITE_OMIT_VIRTUALTABLE \
    SQLITE_OMIT_WAL \
    SQLITE_OMIT_WSD \
    SQLITE_OMIT_XFER_OPT \
︙︙