wasCSharpSQLite – Blame information for rev 1

Subversion Repositories:
Rev:
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