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
Side-by-Side Diff Ignore Whitespace Patch
Changes to src/ctime.c.
︙
297
298
299
300
301
302
303



304
305
306
307
308
309
310

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

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
    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 ){
︙
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

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.1 {
  catchsql {CREATE TABLE t1(a TEXT UNIQUE);  }
do_test omitunique-1.1 {
  catchsql { CREATE TABLE t1(a TEXT UNIQUE); }
} {0 {}}
do_test omitunique-1.1.2 {
  catchsql {INSERT INTO t1(a) VALUES('abc'); }

# table with UNIQUE clause on column
} {0 {}}
do_test omitunique-1.1.3 {
  catchsql {INSERT INTO t1(a) VALUES('123'); }
do_test omitunique-1.2 {
  catchsql { CREATE TABLE t2(a TEXT, UNIQUE(a)); }
} {0 {}}

# table with UNIQUE index on column
do_test omitunique-1.2.1 {
do_test omitunique-1.3 {
  catchsql {
    CREATE TABLE t2(a TEXT);
    CREATE UNIQUE INDEX t2a ON t2(a);
    CREATE TABLE t3(a TEXT);
    CREATE UNIQUE INDEX t3a ON t3(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 {
do_test omitunique-1.4 {
  catchsql {
    CREATE TABLE t3(a TEXT);
    CREATE INDEX t3a ON t3(a);
    CREATE TABLE t4(a TEXT);
    CREATE INDEX t4a ON t4(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 {
do_test omitunique-1.5 {
  catchsql { CREATE TABLE t5(a TEXT); }
    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
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 {
            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')}]
      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 {
          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'}]
    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
  do_test omitunique-2.3.$j {
    execsql [ subst { SELECT count(*) FROM $tbl WHERE a='abc'; }]
  } $cnt
  }
  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 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

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 \
︙