wasCSharpSQLite – Blame information for rev 7
?pathlinks?
Rev | Author | Line No. | Line |
---|---|---|---|
1 | office | 1 | # 2008 August 27 |
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 | # |
||
12 | # This file implements regression tests for SQLite library. The |
||
13 | # focus of this script is transactions |
||
14 | # |
||
15 | # $Id: trans2.test,v 1.1 2008/08/27 18:56:36 drh Exp $ |
||
16 | # |
||
17 | set testdir [file dirname $argv0] |
||
18 | source $testdir/tester.tcl |
||
19 | |||
20 | # A procedure to scramble the elements of list $inlist into a random order. |
||
21 | # |
||
22 | proc scramble {inlist} { |
||
23 | set y {} |
||
24 | foreach x $inlist { |
||
25 | lappend y [list [expr {rand()}] $x] |
||
26 | } |
||
27 | set y [lsort $y] |
||
28 | set outlist {} |
||
29 | foreach x $y { |
||
30 | lappend outlist [lindex $x 1] |
||
31 | } |
||
32 | return $outlist |
||
33 | } |
||
34 | |||
35 | # Generate a UUID using randomness. |
||
36 | # |
||
37 | expr srand(1) |
||
38 | proc random_uuid {} { |
||
39 | set u {} |
||
40 | for {set i 0} {$i<5} {incr i} { |
||
41 | append u [format %06x [expr {int(rand()*16777216)}]] |
||
42 | } |
||
43 | return $u |
||
44 | } |
||
45 | |||
46 | # Compute hashes on the u1 and u2 fields of the sample data. |
||
47 | # |
||
48 | proc hash1 {} { |
||
49 | global data |
||
50 | set x "" |
||
51 | foreach rec [lsort -integer -index 0 $data] { |
||
52 | append x [lindex $rec 1] |
||
53 | } |
||
54 | return [md5 $x] |
||
55 | } |
||
56 | proc hash2 {} { |
||
57 | global data |
||
58 | set x "" |
||
59 | foreach rec [lsort -integer -index 0 $data] { |
||
60 | append x [lindex $rec 3] |
||
61 | } |
||
62 | return [md5 $x] |
||
63 | } |
||
64 | |||
65 | # Create the initial data set |
||
66 | # |
||
67 | unset -nocomplain data i max_rowid todel n rec max1 id origres newres |
||
68 | unset -nocomplain inssql modsql s j z |
||
69 | set data {} |
||
70 | for {set i 0} {$i<400} {incr i} { |
||
71 | set rec [list $i [random_uuid] [expr {int(rand()*5000)+1000}] [random_uuid]] |
||
72 | lappend data $rec |
||
73 | } |
||
74 | set max_rowid [expr {$i-1}] |
||
75 | |||
76 | # Create the T1 table used to hold test data. Populate that table with |
||
77 | # the initial data set and check hashes to make sure everything is correct. |
||
78 | # |
||
79 | do_test trans2-1.1 { |
||
80 | execsql { |
||
81 | PRAGMA cache_size=100; |
||
82 | CREATE TABLE t1( |
||
83 | id INTEGER PRIMARY KEY, |
||
84 | u1 TEXT UNIQUE, |
||
85 | z BLOB NOT NULL, |
||
86 | u2 TEXT UNIQUE |
||
87 | ); |
||
88 | } |
||
89 | foreach rec [scramble $data] { |
||
90 | foreach {id u1 z u2} $rec break |
||
91 | db eval {INSERT INTO t1 VALUES($id,$u1,zeroblob($z),$u2)} |
||
92 | } |
||
93 | db eval {SELECT md5sum(u1), md5sum(u2) FROM t1 ORDER BY id} |
||
94 | } [list [hash1] [hash2]] |
||
95 | |||
96 | # Repeat the main test loop multiple times. |
||
97 | # |
||
98 | for {set i 2} {$i<=30} {incr i} { |
||
99 | # Delete one row out of every 10 in the database. This will add |
||
100 | # many pages to the freelist. |
||
101 | # |
||
102 | set todel {} |
||
103 | set n [expr {[llength $data]/10}] |
||
104 | set data [scramble $data] |
||
105 | foreach rec [lrange $data 0 $n] { |
||
106 | lappend todel [lindex $rec 0] |
||
107 | } |
||
108 | set data [lrange $data [expr {$n+1}] end] |
||
109 | set max1 [lindex [lindex $data 0] 0] |
||
110 | foreach rec $data { |
||
111 | set id [lindex $rec 0] |
||
112 | if {$id>$max1} {set max1 $id} |
||
113 | } |
||
114 | set origres [list [hash1] [hash2]] |
||
115 | do_test trans2-$i.1 { |
||
116 | db eval "DELETE FROM t1 WHERE id IN ([join $todel ,])" |
||
117 | db eval {SELECT md5sum(u1), md5sum(u2) FROM t1 ORDER BY id} |
||
118 | } $origres |
||
119 | integrity_check trans2-$i.2 |
||
120 | |||
121 | # Begin a transaction and insert many new records. |
||
122 | # |
||
123 | set newdata {} |
||
124 | foreach id $todel { |
||
125 | set rec [list $id [random_uuid] \ |
||
126 | [expr {int(rand()*5000)+1000}] [random_uuid]] |
||
127 | lappend newdata $rec |
||
128 | lappend data $rec |
||
129 | } |
||
130 | for {set j 1} {$j<50} {incr j} { |
||
131 | set id [expr {$max_rowid+$j}] |
||
132 | lappend todel $id |
||
133 | set rec [list $id [random_uuid] \ |
||
134 | [expr {int(rand()*5000)+1000}] [random_uuid]] |
||
135 | lappend newdata $rec |
||
136 | lappend data $rec |
||
137 | } |
||
138 | set max_rowid [expr {$max_rowid+$j-1}] |
||
139 | set modsql {} |
||
140 | set inssql {} |
||
141 | set newres [list [hash1] [hash2]] |
||
142 | do_test trans2-$i.3 { |
||
143 | db eval BEGIN |
||
144 | foreach rec [scramble $newdata] { |
||
145 | foreach {id u1 z u2} $rec break |
||
146 | set s "INSERT INTO t1 VALUES($id,'$u1',zeroblob($z),'$u2');" |
||
147 | append modsql $s\n |
||
148 | append inssql $s\n |
||
149 | db eval $s |
||
150 | } |
||
151 | db eval {SELECT md5sum(u1), md5sum(u2) FROM t1 ORDER BY id} |
||
152 | } $newres |
||
153 | integrity_check trans2-$i.4 |
||
154 | |||
155 | # Do a large update that aborts do to a constraint failure near |
||
156 | # the end. This stresses the statement journal mechanism. |
||
157 | # |
||
158 | do_test trans2-$i.10 { |
||
159 | catchsql { |
||
160 | UPDATE t1 SET u1=u1||'x', |
||
161 | z = CASE WHEN id<$max_rowid |
||
162 | THEN zeroblob((random()&65535)%5000 + 1000) END; |
||
163 | } |
||
164 | } {1 {t1.z may not be NULL}} |
||
165 | do_test trans2-$i.11 { |
||
166 | db eval {SELECT md5sum(u1), md5sum(u2) FROM t1 ORDER BY id} |
||
167 | } $newres |
||
168 | |||
169 | # Delete all of the newly inserted records. Verify that the database |
||
170 | # is back to its original state. |
||
171 | # |
||
172 | do_test trans2-$i.20 { |
||
173 | set s "DELETE FROM t1 WHERE id IN ([join $todel ,]);" |
||
174 | append modsql $s\n |
||
175 | db eval $s |
||
176 | db eval {SELECT md5sum(u1), md5sum(u2) FROM t1 ORDER BY id} |
||
177 | } $origres |
||
178 | |||
179 | # Do another large update that aborts do to a constraint failure near |
||
180 | # the end. This stresses the statement journal mechanism. |
||
181 | # |
||
182 | do_test trans2-$i.30 { |
||
183 | catchsql { |
||
184 | UPDATE t1 SET u1=u1||'x', |
||
185 | z = CASE WHEN id<$max1 |
||
186 | THEN zeroblob((random()&65535)%5000 + 1000) END; |
||
187 | } |
||
188 | } {1 {t1.z may not be NULL}} |
||
189 | do_test trans2-$i.31 { |
||
190 | db eval {SELECT md5sum(u1), md5sum(u2) FROM t1 ORDER BY id} |
||
191 | } $origres |
||
192 | |||
193 | # Redo the inserts |
||
194 | # |
||
195 | do_test trans2-$i.40 { |
||
196 | db eval $inssql |
||
197 | append modsql $inssql |
||
198 | db eval {SELECT md5sum(u1), md5sum(u2) FROM t1 ORDER BY id} |
||
199 | } $newres |
||
200 | |||
201 | # Rollback the transaction. Verify that the content is restored. |
||
202 | # |
||
203 | do_test trans2-$i.90 { |
||
204 | db eval ROLLBACK |
||
205 | db eval {SELECT md5sum(u1), md5sum(u2) FROM t1 ORDER BY id} |
||
206 | } $origres |
||
207 | integrity_check trans2-$i.91 |
||
208 | |||
209 | # Repeat all the changes, but this time commit. |
||
210 | # |
||
211 | do_test trans2-$i.92 { |
||
212 | db eval BEGIN |
||
213 | catchsql { |
||
214 | UPDATE t1 SET u1=u1||'x', |
||
215 | z = CASE WHEN id<$max1 |
||
216 | THEN zeroblob((random()&65535)%5000 + 1000) END; |
||
217 | } |
||
218 | db eval $modsql |
||
219 | catchsql { |
||
220 | UPDATE t1 SET u1=u1||'x', |
||
221 | z = CASE WHEN id<$max1 |
||
222 | THEN zeroblob((random()&65535)%5000 + 1000) END; |
||
223 | } |
||
224 | db eval COMMIT |
||
225 | db eval {SELECT md5sum(u1), md5sum(u2) FROM t1 ORDER BY id} |
||
226 | } $newres |
||
227 | integrity_check trans2-$i.93 |
||
228 | } |
||
229 | |||
230 | unset -nocomplain data i max_rowid todel n rec max1 id origres newres |
||
231 | unset -nocomplain inssql modsql s j z |
||
232 | finish_test |