wasCSharpSQLite – Blame information for rev 1

Subversion Repositories:
Rev:
Rev Author Line No. Line
1 office 1 # 2009 March 04
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 # $Id: notify2.test,v 1.7 2009/03/30 11:59:31 drh Exp $
13  
14 set testdir [file dirname $argv0]
15 source $testdir/tester.tcl
16 if {[run_thread_tests]==0} { finish_test ; return }
17 ifcapable !unlock_notify||!shared_cache { finish_test ; return }
18  
19 # The tests in this file test the sqlite3_blocking_step() function in
20 # test_thread.c. sqlite3_blocking_step() is not an SQLite API function,
21 # it is just a demonstration of how the sqlite3_unlock_notify() function
22 # can be used to synchronize multi-threaded access to SQLite databases
23 # in shared-cache mode.
24 #
25 # Since the implementation of sqlite3_blocking_step() is included on the
26 # website as example code, it is important to test that it works.
27 #
28 # notify2-1.*:
29 #
30 # This test uses $nThread threads. Each thread opens the main database
31 # and attaches two other databases. Each database contains a single table.
32 #
33 # Each thread repeats transactions over and over for 20 seconds. Each
34 # transaction consists of 3 operations. Each operation is either a read
35 # or a write of one of the tables. The read operations verify an invariant
36 # to make sure that things are working as expected. If an SQLITE_LOCKED
37 # error is returned the current transaction is rolled back immediately.
38 #
39 # This exercise is repeated twice, once using sqlite3_step(), and the
40 # other using sqlite3_blocking_step(). The results are compared to ensure
41 # that sqlite3_blocking_step() resulted in higher transaction throughput.
42 #
43  
44 db close
45 set ::enable_shared_cache [sqlite3_enable_shared_cache 1]
46  
47 # Number of threads to run simultaneously.
48 #
49 set nThread 6
50 set nSecond 5
51  
52 # The Tcl script executed by each of the $nThread threads used by this test.
53 #
54 set ThreadProgram {
55  
56 # Proc used by threads to execute SQL.
57 #
58 proc execsql_blocking {db zSql} {
59 set lRes [list]
60 set rc SQLITE_OK
61  
62 set sql $zSql
63  
64 while {$rc=="SQLITE_OK" && $zSql ne ""} {
65 set STMT [$::xPrepare $db $zSql -1 zSql]
66 while {[set rc [$::xStep $STMT]] eq "SQLITE_ROW"} {
67 for {set i 0} {$i < [sqlite3_column_count $STMT]} {incr i} {
68 lappend lRes [sqlite3_column_text $STMT 0]
69 }
70 }
71 set rc [sqlite3_finalize $STMT]
72 }
73  
74 if {$rc != "SQLITE_OK"} { error "$rc $sql [sqlite3_errmsg $db]" }
75 return $lRes
76 }
77  
78 proc execsql_retry {db sql} {
79 set msg "SQLITE_LOCKED blah..."
80 while { [string match SQLITE_LOCKED* $msg] } {
81 catch { execsql_blocking $db $sql } msg
82 }
83 }
84  
85 proc select_one {args} {
86 set n [llength $args]
87 lindex $args [expr int($n*rand())]
88 }
89  
90 proc opendb {} {
91 # Open a database connection. Attach the two auxillary databases.
92 set ::DB [sqlite3_open test.db]
93 execsql_retry $::DB { ATTACH 'test2.db' AS aux2; }
94 execsql_retry $::DB { ATTACH 'test3.db' AS aux3; }
95 }
96  
97 opendb
98  
99 #after 2000
100  
101 # This loop runs for ~20 seconds.
102 #
103 set iStart [clock_seconds]
104 while { ([clock_seconds]-$iStart) < $nSecond } {
105  
106 # Each transaction does 3 operations. Each operation is either a read
107 # or write of a randomly selected table (t1, t2 or t3). Set the variables
108 # $SQL(1), $SQL(2) and $SQL(3) to the SQL commands used to implement
109 # each operation.
110 #
111 for {set ii 1} {$ii <= 3} {incr ii} {
112 foreach {tbl database} [select_one {t1 main} {t2 aux2} {t3 aux3}] {}
113  
114 set SQL($ii) [string map [list xxx $tbl yyy $database] [select_one {
115 SELECT
116 (SELECT b FROM xxx WHERE a=(SELECT max(a) FROM xxx))==total(a)
117 FROM xxx WHERE a!=(SELECT max(a) FROM xxx);
118 } {
119 DELETE FROM xxx WHERE a<(SELECT max(a)-100 FROM xxx);
120 INSERT INTO xxx SELECT NULL, total(a) FROM xxx;
121 } {
122 CREATE INDEX IF NOT EXISTS yyy.xxx_i ON xxx(b);
123 } {
124 DROP INDEX IF EXISTS yyy.xxx_i;
125 }
126 ]]
127 }
128  
129 # Execute the SQL transaction.
130 #
131 set rc [catch { execsql_blocking $::DB "
132 BEGIN;
133 $SQL(1);
134 $SQL(2);
135 $SQL(3);
136 COMMIT;
137 "
138 } msg]
139  
140 if {$rc && [string match "SQLITE_LOCKED*" $msg]
141 || [string match "SQLITE_SCHEMA*" $msg]
142 } {
143 # Hit an SQLITE_LOCKED error. Rollback the current transaction.
144 set rc [catch { execsql_blocking $::DB ROLLBACK } msg]
145 if {$rc && [string match "SQLITE_LOCKED*" $msg]} {
146 sqlite3_close $::DB
147 opendb
148 }
149 } elseif {$rc} {
150 # Hit some other kind of error. This is a malfunction.
151 error $msg
152 } else {
153 # No error occured. Check that any SELECT statements in the transaction
154 # returned "1". Otherwise, the invariant was false, indicating that
155 # some malfunction has occured.
156 foreach r $msg { if {$r != 1} { puts "Invariant check failed: $msg" } }
157 }
158 }
159  
160 # Close the database connection and return 0.
161 #
162 sqlite3_close $::DB
163 expr 0
164 }
165  
166 foreach {iTest xStep xPrepare} {
167 1 sqlite3_blocking_step sqlite3_blocking_prepare_v2
168 2 sqlite3_step sqlite3_nonblocking_prepare_v2
169 } {
170 file delete -force test.db test2.db test3.db
171  
172 set ThreadSetup "set xStep $xStep;set xPrepare $xPrepare;set nSecond $nSecond"
173  
174 # Set up the database schema used by this test. Each thread opens file
175 # test.db as the main database, then attaches files test2.db and test3.db
176 # as auxillary databases. Each file contains a single table (t1, t2 and t3, in
177 # files test.db, test2.db and test3.db, respectively).
178 #
179 do_test notify2-$iTest.1.1 {
180 sqlite3 db test.db
181 execsql {
182 ATTACH 'test2.db' AS aux2;
183 ATTACH 'test3.db' AS aux3;
184 CREATE TABLE main.t1(a INTEGER PRIMARY KEY, b);
185 CREATE TABLE aux2.t2(a INTEGER PRIMARY KEY, b);
186 CREATE TABLE aux3.t3(a INTEGER PRIMARY KEY, b);
187 INSERT INTO t1 SELECT NULL, 0;
188 INSERT INTO t2 SELECT NULL, 0;
189 INSERT INTO t3 SELECT NULL, 0;
190 }
191 } {}
192 do_test notify2-$iTest.1.2 {
193 db close
194 } {}
195  
196  
197 # Launch $nThread threads. Then wait for them to finish.
198 #
199 puts "Running $xStep test for $nSecond seconds"
200 unset -nocomplain finished
201 for {set ii 0} {$ii < $nThread} {incr ii} {
202 thread_spawn finished($ii) $ThreadSetup $ThreadProgram
203 }
204 for {set ii 0} {$ii < $nThread} {incr ii} {
205 do_test notify2-$iTest.2.$ii {
206 if {![info exists finished($ii)]} { vwait finished($ii) }
207 set finished($ii)
208 } {0}
209 }
210  
211 # Count the total number of succesful writes.
212 do_test notify2-$iTest.3.1 {
213 sqlite3 db test.db
214 execsql {
215 ATTACH 'test2.db' AS aux2;
216 ATTACH 'test3.db' AS aux3;
217 }
218 set anWrite($xStep) [execsql {
219 SELECT (SELECT max(a) FROM t1)
220 + (SELECT max(a) FROM t2)
221 + (SELECT max(a) FROM t3)
222 }]
223 db close
224 } {}
225 }
226  
227 # The following tests checks to make sure sqlite3_blocking_step() is
228 # faster than sqlite3_step(). blocking_step() is always faster on
229 # multi-core and is usually faster on single-core. But sometimes, by
230 # chance, step() will be faster on a single core, in which case the
231 # following test will fail.
232 #
233 puts "The following test seeks to demonstrate that the sqlite3_unlock_notify()"
234 puts "interface helps multi-core systems to run faster. This test sometimes"
235 puts "fails on single-core machines."
236 puts [array get anWrite]
237 do_test notify2-3 {
238 expr {$anWrite(sqlite3_blocking_step) > $anWrite(sqlite3_step)}
239 } {1}
240  
241 sqlite3_enable_shared_cache $::enable_shared_cache
242 finish_test