SQLite

Check-in [75a38411a8]
Login

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

Overview
Comment:Simplification of tests and more added for SQLITE_OMIT_UNIQUE_ENFORCEMENT tests.
Downloads: Tarball | ZIP archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1: 75a38411a89bd2969ec5cecca13c13a390b3d478
User & Date: shaneh 2011-03-10 21:48:02.401
Context
2011-03-11
16:15
Remove an unused field from the unix sqlite3_file object. (check-in: f957f23a8a user: drh tags: trunk)
2011-03-10
21:48
Simplification of tests and more added for SQLITE_OMIT_UNIQUE_ENFORCEMENT tests. (check-in: 75a38411a8 user: shaneh tags: trunk)
21:13
Skip unique constraint enforcement if compiled with SQLITE_OMIT_UNIQUE_ENFORCEMENT. (check-in: ba85bf8cb8 user: shaneh tags: trunk)
Changes
Unified Diff Ignore Whitespace Patch
Changes to test/omitunique.test.
︙︙
11
12
13
14
15
16
17





18
19
20
21
22
23
24

# 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'); }








>
>
>
>
>








11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29

# 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

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'); }
︙︙
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

    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








<
<
<
<
<
|
|
<
|
|
|
|

>
|
|
>
|
|
<
<
|
|
|
|
|
>
>
>
>
>
>
|
|
<
|





|
|
|
|
|
|

<
<
<
<
|
<
|
|
|
|

>
|
|
>
>
>
>
|
>
>

>
>
>
















|










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

    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