wasCSharpSQLite – Blame information for rev 1

Subversion Repositories:
Rev:
Rev Author Line No. Line
1 office 1 # 2007 May 1
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: incrblob.test,v 1.24 2009/06/19 22:23:42 drh Exp $
13 #
14  
15 set testdir [file dirname $argv0]
16 source $testdir/tester.tcl
17  
18 ifcapable {!autovacuum || !pragma || !incrblob} {
19 finish_test
20 return
21 }
22  
23 do_test incrblob-1.1 {
24 execsql {
25 CREATE TABLE blobs(k PRIMARY KEY, v BLOB);
26 INSERT INTO blobs VALUES('one', X'0102030405060708090A');
27 INSERT INTO blobs VALUES('two', X'0A090807060504030201');
28 }
29 } {}
30  
31 do_test incrblob-1.2.1 {
32 set ::blob [db incrblob blobs v 1]
33 string match incrblob_* $::blob
34 } {1}
35 unset -nocomplain data
36 do_test incrblob-1.2.2 {
37 binary scan [read $::blob] c* data
38 set data
39 } {1 2 3 4 5 6 7 8 9 10}
40 do_test incrblob-1.2.3 {
41 seek $::blob 0
42 puts -nonewline $::blob "1234567890"
43 flush $::blob
44 } {}
45 do_test incrblob-1.2.4 {
46 seek $::blob 0
47 binary scan [read $::blob] c* data
48 set data
49 } {49 50 51 52 53 54 55 56 57 48}
50 do_test incrblob-1.2.5 {
51 close $::blob
52 } {}
53 do_test incrblob-1.2.6 {
54 execsql {
55 SELECT v FROM blobs WHERE rowid = 1;
56 }
57 } {1234567890}
58  
59 #--------------------------------------------------------------------
60 # Test cases incrblob-1.3.X check that it is possible to read and write
61 # regions of a blob that lie on overflow pages.
62 #
63 do_test incrblob-1.3.1 {
64 set ::str "[string repeat . 10000]"
65 execsql {
66 INSERT INTO blobs(rowid, k, v) VALUES(3, 'three', $::str);
67 }
68 } {}
69  
70 do_test incrblob-1.3.2 {
71 set ::blob [db incrblob blobs v 3]
72 seek $::blob 8500
73 read $::blob 10
74 } {..........}
75 do_test incrblob-1.3.3 {
76 seek $::blob 8500
77 puts -nonewline $::blob 1234567890
78 } {}
79 do_test incrblob-1.3.4 {
80 seek $::blob 8496
81 read $::blob 10
82 } {....123456}
83 do_test incrblob-1.3.10 {
84 close $::blob
85 } {}
86  
87 #------------------------------------------------------------------------
88 # incrblob-2.*:
89 #
90 # Test that the following operations use ptrmap pages to reduce
91 # unnecessary reads:
92 #
93 # * Reading near the end of a blob,
94 # * Writing near the end of a blob, and
95 # * SELECT a column value that is located on an overflow page.
96 #
97 proc nRead {db} {
98 set bt [btree_from_db $db]
99 db_enter $db
100 array set stats [btree_pager_stats $bt]
101 db_leave $db
102 return $stats(read)
103 }
104 proc nWrite {db} {
105 set bt [btree_from_db $db]
106 db_enter $db
107 array set stats [btree_pager_stats $bt]
108 db_leave $db
109 return $stats(write)
110 }
111  
112 sqlite3_soft_heap_limit 0
113  
114 foreach AutoVacuumMode [list 0 1] {
115  
116 if {$AutoVacuumMode>0} {
117 ifcapable !autovacuum {
118 break
119 }
120 }
121  
122 db close
123 file delete -force test.db test.db-journal
124  
125 sqlite3 db test.db
126 execsql "PRAGMA auto_vacuum = $AutoVacuumMode"
127  
128 do_test incrblob-2.$AutoVacuumMode.1 {
129 set ::str [string repeat abcdefghij 2900]
130 execsql {
131 BEGIN;
132 CREATE TABLE blobs(k PRIMARY KEY, v BLOB, i INTEGER);
133 DELETE FROM blobs;
134 INSERT INTO blobs VALUES('one', $::str || randstr(500,500), 45);
135 COMMIT;
136 }
137 expr [file size test.db]/1024
138 } [expr 31 + $AutoVacuumMode]
139  
140 ifcapable autovacuum {
141 do_test incrblob-2.$AutoVacuumMode.2 {
142 execsql {
143 PRAGMA auto_vacuum;
144 }
145 } $AutoVacuumMode
146 }
147  
148 do_test incrblob-2.$AutoVacuumMode.3 {
149 # Open and close the db to make sure the page cache is empty.
150 db close
151 sqlite3 db test.db
152  
153 # Read the last 20 bytes of the blob via a blob handle.
154 set ::blob [db incrblob blobs v 1]
155 seek $::blob -20 end
156 set ::fragment [read $::blob]
157 close $::blob
158  
159 # If the database is not in auto-vacuum mode, the whole of
160 # the overflow-chain must be scanned. In auto-vacuum mode,
161 # sqlite uses the ptrmap pages to avoid reading the other pages.
162 #
163 nRead db
164 } [expr $AutoVacuumMode ? 4 : 30]
165  
166 do_test incrblob-2.$AutoVacuumMode.4 {
167 string range [db one {SELECT v FROM blobs}] end-19 end
168 } $::fragment
169  
170 do_test incrblob-2.$AutoVacuumMode.5 {
171 # Open and close the db to make sure the page cache is empty.
172 db close
173 sqlite3 db test.db
174  
175 # Write the second-to-last 20 bytes of the blob via a blob handle.
176 #
177 set ::blob [db incrblob blobs v 1]
178 seek $::blob -40 end
179 puts -nonewline $::blob "1234567890abcdefghij"
180 flush $::blob
181  
182 # If the database is not in auto-vacuum mode, the whole of
183 # the overflow-chain must be scanned. In auto-vacuum mode,
184 # sqlite uses the ptrmap pages to avoid reading the other pages.
185 #
186 nRead db
187 } [expr $AutoVacuumMode ? 4 : 30]
188  
189 # Pages 1 (the write-counter) and 32 (the blob data) were written.
190 do_test incrblob-2.$AutoVacuumMode.6 {
191 close $::blob
192 nWrite db
193 } 2
194  
195 do_test incrblob-2.$AutoVacuumMode.7 {
196 string range [db one {SELECT v FROM blobs}] end-39 end-20
197 } "1234567890abcdefghij"
198  
199 do_test incrblob-2.$AutoVacuumMode.8 {
200 # Open and close the db to make sure the page cache is empty.
201 db close
202 sqlite3 db test.db
203  
204 execsql { SELECT i FROM blobs }
205 } {45}
206  
207 do_test incrblob-2.$AutoVacuumMode.9 {
208 nRead db
209 } [expr $AutoVacuumMode ? 4 : 30]
210 }
211 sqlite3_soft_heap_limit $cmdlinearg(soft-heap-limit)
212  
213 #------------------------------------------------------------------------
214 # incrblob-3.*:
215 #
216 # Test the outcome of trying to write to a read-only blob handle.
217 #
218 do_test incrblob-3.1 {
219 set ::blob [db incrblob -readonly blobs v 1]
220 seek $::blob -40 end
221 read $::blob 20
222 } "1234567890abcdefghij"
223 do_test incrblob-3.2 {
224 seek $::blob 0
225 set rc [catch {
226 puts -nonewline $::blob "helloworld"
227 } msg]
228 close $::blob
229 list $rc $msg
230 } "1 {channel \"$::blob\" wasn't opened for writing}"
231  
232 do_test incrblob-3.3 {
233 set ::blob [db incrblob -readonly blobs v 1]
234 seek $::blob -40 end
235 read $::blob 20
236 } "1234567890abcdefghij"
237 do_test incrblob-3.4 {
238 set rc [catch {
239 sqlite3_blob_write $::blob 20 "qwertyuioplkjhgfds"
240 } msg]
241 list $rc $msg
242 } {1 SQLITE_READONLY}
243 catch {close $::blob}
244  
245 #------------------------------------------------------------------------
246 # incrblob-4.*:
247 #
248 # Try a couple of error conditions:
249 #
250 # 4.1 - Attempt to open a row that does not exist.
251 # 4.2 - Attempt to open a column that does not exist.
252 # 4.3 - Attempt to open a table that does not exist.
253 # 4.4 - Attempt to open a database that does not exist.
254 #
255 # 4.5 - Attempt to open an integer
256 # 4.6 - Attempt to open a real value
257 # 4.7 - Attempt to open an SQL null
258 #
259 # 4.8 - Attempt to open an indexed column for writing
260 # 4.9 - Attempt to open an indexed column for reading (this works)
261 #
262 # 4.11 - Attempt to open a column of a view.
263 # 4.12 - Attempt to open a column of a virtual table.
264 #
265 do_test incrblob-4.1 {
266 set rc [catch {
267 set ::blob [db incrblob blobs v 2]
268 } msg ]
269 list $rc $msg
270 } {1 {no such rowid: 2}}
271 do_test incrblob-4.2 {
272 set rc [catch {
273 set ::blob [db incrblob blobs blue 1]
274 } msg ]
275 list $rc $msg
276 } {1 {no such column: "blue"}}
277 do_test incrblob-4.3 {
278 set rc [catch {
279 set ::blob [db incrblob nosuchtable blue 1]
280 } msg ]
281 list $rc $msg
282 } {1 {no such table: main.nosuchtable}}
283 do_test incrblob-4.4 {
284 set rc [catch {
285 set ::blob [db incrblob nosuchdb blobs v 1]
286 } msg ]
287 list $rc $msg
288 } {1 {no such table: nosuchdb.blobs}}
289  
290 do_test incrblob-4.5 {
291 set rc [catch {
292 set ::blob [db incrblob blobs i 1]
293 } msg ]
294 list $rc $msg
295 } {1 {cannot open value of type integer}}
296 do_test incrblob-4.6 {
297 execsql {
298 INSERT INTO blobs(k, v, i) VALUES(123, 567.765, NULL);
299 }
300 set rc [catch {
301 set ::blob [db incrblob blobs v 2]
302 } msg ]
303 list $rc $msg
304 } {1 {cannot open value of type real}}
305 do_test incrblob-4.7 {
306 set rc [catch {
307 set ::blob [db incrblob blobs i 2]
308 } msg ]
309 list $rc $msg
310 } {1 {cannot open value of type null}}
311  
312 do_test incrblob-4.8.1 {
313 execsql {
314 INSERT INTO blobs(k, v, i) VALUES(X'010203040506070809', 'hello', 'world');
315 }
316 set rc [catch {
317 set ::blob [db incrblob blobs k 3]
318 } msg ]
319 list $rc $msg
320 } {1 {cannot open indexed column for writing}}
321 do_test incrblob-4.8.2 {
322 execsql {
323 CREATE TABLE t3(a INTEGER PRIMARY KEY, b);
324 INSERT INTO t3 VALUES(1, 2);
325 }
326 set rc [catch {
327 set ::blob [db incrblob -readonly t3 a 1]
328 } msg ]
329 list $rc $msg
330 } {1 {cannot open value of type null}}
331 do_test incrblob-4.8.3 {
332 set rc [catch {
333 set ::blob [db incrblob -readonly t3 rowid 1]
334 } msg ]
335 list $rc $msg
336 } {1 {no such column: "rowid"}}
337  
338 do_test incrblob-4.9.1 {
339 set rc [catch {
340 set ::blob [db incrblob -readonly blobs k 3]
341 } msg]
342 } {0}
343 do_test incrblob-4.9.2 {
344 binary scan [read $::blob] c* c
345 close $::blob
346 set c
347 } {1 2 3 4 5 6 7 8 9}
348  
349 do_test incrblob-4.10 {
350 set ::blob [db incrblob -readonly blobs k 3]
351 set rc [catch { sqlite3_blob_read $::blob 10 100 } msg]
352 list $rc $msg
353 } {1 SQLITE_ERROR}
354 do_test incrblob-4.10.2 {
355 close $::blob
356 } {}
357  
358 ifcapable view {
359 do_test incrblob-4.11 {
360 execsql { CREATE VIEW blobs_view AS SELECT k, v, i FROM blobs }
361 set rc [catch { db incrblob blobs_view v 3 } msg]
362 list $rc $msg
363 } {1 {cannot open view: blobs_view}}
364 }
365 ifcapable vtab {
366 register_echo_module [sqlite3_connection_pointer db]
367 do_test incrblob-4.12 {
368 execsql { CREATE VIRTUAL TABLE blobs_echo USING echo(blobs) }
369 set rc [catch { db incrblob blobs_echo v 3 } msg]
370 list $rc $msg
371 } {1 {cannot open virtual table: blobs_echo}}
372 }
373  
374  
375 #------------------------------------------------------------------------
376 # incrblob-5.*:
377 #
378 # Test that opening a blob in an attached database works.
379 #
380 ifcapable attach {
381 do_test incrblob-5.1 {
382 file delete -force test2.db test2.db-journal
383 set ::size [expr [file size [info script]]]
384 execsql {
385 ATTACH 'test2.db' AS aux;
386 CREATE TABLE aux.files(name, text);
387 INSERT INTO aux.files VALUES('this one', zeroblob($::size));
388 }
389 set fd [db incrblob aux files text 1]
390 fconfigure $fd -translation binary
391 set fd2 [open [info script]]
392 fconfigure $fd2 -translation binary
393 puts -nonewline $fd [read $fd2]
394 close $fd
395 close $fd2
396 set ::text [db one {select text from aux.files}]
397 string length $::text
398 } [file size [info script]]
399 do_test incrblob-5.2 {
400 set fd2 [open [info script]]
401 fconfigure $fd2 -translation binary
402 set ::data [read $fd2]
403 close $fd2
404 set ::data
405 } $::text
406 }
407  
408 # free memory
409 unset -nocomplain ::data
410 unset -nocomplain ::text
411  
412 #------------------------------------------------------------------------
413 # incrblob-6.*:
414 #
415 # Test that opening a blob for write-access is impossible if
416 # another connection has the database RESERVED lock.
417 #
418 # Then test that blob writes that take place inside of a
419 # transaction are not visible to external connections until
420 # after the transaction is commited and the blob channel
421 # closed.
422 #
423 # This test does not work with the "memsubsys1" configuration.
424 # Permutation memsubsys1 configures a very small static allocation
425 # for use as page-cache memory. This causes SQLite to upgrade
426 # to an exclusive lock when writing earlier than usual, which
427 # makes some of these tests fail.
428 #
429 sqlite3_soft_heap_limit 0
430 if {[permutation] != "memsubsys1"} {
431 do_test incrblob-6.1 {
432 sqlite3 db2 test.db
433 execsql {
434 BEGIN;
435 INSERT INTO blobs(k, v, i) VALUES('a', 'different', 'connection');
436 } db2
437 } {}
438 do_test incrblob-6.2 {
439 execsql {
440 SELECT rowid FROM blobs
441 }
442 } {1 2 3}
443 do_test incrblob-6.3 {
444 set rc [catch {
445 db incrblob blobs v 1
446 } msg]
447 list $rc $msg
448 } {1 {database is locked}}
449 do_test incrblob-6.4 {
450 set rc [catch {
451 db incrblob blobs v 3
452 } msg]
453 list $rc $msg
454 } {1 {database is locked}}
455 do_test incrblob-6.5 {
456 set ::blob [db incrblob -readonly blobs v 3]
457 read $::blob
458 } {hello}
459 do_test incrblob-6.6 {
460 close $::blob
461 } {}
462  
463 do_test incrblob-6.7 {
464 set ::blob [db2 incrblob blobs i 4]
465 gets $::blob
466 } {connection}
467 do_test incrblob-6.8 {
468 tell $::blob
469 } {10}
470 do_test incrblob-6.9 {
471 seek $::blob 0
472 puts -nonewline $::blob "invocation"
473 flush $::blob
474 } {}
475  
476 # At this point rollback should be illegal (because
477 # there is an open blob channel). But commit is also illegal because
478 # the open blob is read-write.
479 #
480 do_test incrblob-6.10 {
481 catchsql {
482 ROLLBACK;
483 } db2
484 } {1 {cannot rollback transaction - SQL statements in progress}}
485 do_test incrblob-6.11 {
486 catchsql {
487 COMMIT;
488 } db2
489 } {1 {cannot commit transaction - SQL statements in progress}}
490  
491 do_test incrblob-6.12 {
492 execsql {
493 SELECT * FROM blobs WHERE rowid = 4;
494 }
495 } {}
496 do_test incrblob-6.13 {
497 close $::blob
498 } {}
499 do_test incrblob-6.14 {
500 catchsql {
501 COMMIT;
502 } db2
503 } {0 {}}
504 do_test incrblob-6.15 {
505 execsql {
506 SELECT * FROM blobs WHERE rowid = 4;
507 }
508 } {a different invocation}
509 db2 close
510 }
511 sqlite3_soft_heap_limit $cmdlinearg(soft-heap-limit)
512  
513 #-----------------------------------------------------------------------
514 # The following tests verify the behaviour of the incremental IO
515 # APIs in the following cases:
516 #
517 # 7.1 A row that containing an open blob is modified.
518 #
519 # 7.2 A CREATE TABLE requires that an overflow page that is part
520 # of an open blob is moved.
521 #
522 # 7.3 An INCREMENTAL VACUUM moves an overflow page that is part
523 # of an open blob.
524 #
525 # In the first case above, correct behaviour is for all subsequent
526 # read/write operations on the blob-handle to return SQLITE_ABORT.
527 # More accurately, blob-handles are invalidated whenever the table
528 # they belong to is written to.
529 #
530 # The second two cases have no external effect. They are testing
531 # that the internal cache of overflow page numbers is correctly
532 # invalidated.
533 #
534 do_test incrblob-7.1.0 {
535 execsql {
536 BEGIN;
537 DROP TABLE blobs;
538 CREATE TABLE t1 (a, b, c, d BLOB);
539 INSERT INTO t1(a, b, c, d) VALUES(1, 2, 3, 4);
540 COMMIT;
541 }
542 } {}
543  
544 foreach {tn arg} {1 "" 2 -readonly} {
545  
546 execsql {
547 UPDATE t1 SET d = zeroblob(10000);
548 }
549  
550 do_test incrblob-7.1.$tn.1 {
551 set ::b [eval db incrblob $arg t1 d 1]
552 binary scan [sqlite3_blob_read $::b 5000 5] c* c
553 set c
554 } {0 0 0 0 0}
555 do_test incrblob-7.1.$tn.2 {
556 execsql {
557 UPDATE t1 SET d = 15;
558 }
559 } {}
560 do_test incrblob-7.1.$tn.3 {
561 set rc [catch { sqlite3_blob_read $::b 5000 5 } msg]
562 list $rc $msg
563 } {1 SQLITE_ABORT}
564 do_test incrblob-7.1.$tn.4 {
565 execsql {
566 SELECT d FROM t1;
567 }
568 } {15}
569 do_test incrblob-7.1.$tn.5 {
570 set rc [catch { close $::b } msg]
571 list $rc $msg
572 } {0 {}}
573 do_test incrblob-7.1.$tn.6 {
574 execsql {
575 SELECT d FROM t1;
576 }
577 } {15}
578  
579 }
580  
581 set fd [open [info script]]
582 fconfigure $fd -translation binary
583 set ::data [read $fd 14000]
584 close $fd
585  
586 db close
587 file delete -force test.db test.db-journal
588 sqlite3 db test.db
589  
590 do_test incrblob-7.2.1 {
591 execsql {
592 PRAGMA auto_vacuum = "incremental";
593 CREATE TABLE t1(a INTEGER PRIMARY KEY, b); -- root@page3
594 INSERT INTO t1 VALUES(123, $::data);
595 }
596 set ::b [db incrblob -readonly t1 b 123]
597 fconfigure $::b -translation binary
598 read $::b
599 } $::data
600 do_test incrblob-7.2.2 {
601 execsql {
602 CREATE TABLE t2(a INTEGER PRIMARY KEY, b); -- root@page4
603 }
604 seek $::b 0
605 read $::b
606 } $::data
607 do_test incrblob-7.2.3 {
608 close $::b
609 execsql {
610 SELECT rootpage FROM sqlite_master;
611 }
612 } {3 4}
613  
614 set ::otherdata "[string range $::data 0 1000][string range $::data 1001 end]"
615 do_test incrblob-7.3.1 {
616 execsql {
617 INSERT INTO t2 VALUES(456, $::otherdata);
618 }
619 set ::b [db incrblob -readonly t2 b 456]
620 fconfigure $::b -translation binary
621 read $::b
622 } $::otherdata
623 do_test incrblob-7.3.2 {
624 expr [file size test.db]/1024
625 } 30
626 do_test incrblob-7.3.3 {
627 execsql {
628 DELETE FROM t1 WHERE a = 123;
629 PRAGMA INCREMENTAL_VACUUM(0);
630 }
631 seek $::b 0
632 read $::b
633 } $::otherdata
634  
635 # Attempt to write on a read-only blob. Make sure the error code
636 # gets set. Ticket #2464.
637 #
638 do_test incrblob-7.4 {
639 set rc [catch {sqlite3_blob_write $::b 10 HELLO} msg]
640 lappend rc $msg
641 } {1 SQLITE_READONLY}
642 do_test incrblob-7.5 {
643 sqlite3_errcode db
644 } {SQLITE_READONLY}
645 do_test incrblob-7.6 {
646 sqlite3_errmsg db
647 } {attempt to write a readonly database}
648  
649 # Test that if either the "offset" or "amount" arguments to
650 # sqlite3_blob_write() are less than zero, SQLITE_ERROR is returned.
651 #
652 do_test incrblob-8.1 {
653 execsql { INSERT INTO t1 VALUES(314159, 'sqlite') }
654 set ::b [db incrblob t1 b 314159]
655 fconfigure $::b -translation binary
656 set rc [catch {sqlite3_blob_write $::b 10 HELLO -1} msg]
657 lappend rc $msg
658 } {1 SQLITE_ERROR}
659 do_test incrblob-8.2 {
660 sqlite3_errcode db
661 } {SQLITE_ERROR}
662 do_test incrblob-8.3 {
663 set rc [catch {sqlite3_blob_write $::b -1 HELLO 5} msg]
664 lappend rc $msg
665 } {1 SQLITE_ERROR}
666 do_test incrblob-8.4 {
667 sqlite3_errcode db
668 } {SQLITE_ERROR}
669 do_test incrblob-8.5 {
670 execsql {SELECT b FROM t1 WHERE a = 314159}
671 } {sqlite}
672 do_test incrblob-8.6 {
673 set rc [catch {sqlite3_blob_write $::b 0 etilqs 6} msg]
674 lappend rc $msg
675 } {0 {}}
676 do_test incrblob-8.7 {
677 execsql {SELECT b FROM t1 WHERE a = 314159}
678 } {etilqs}
679  
680 # The following test case exposes an instance in the blob code where
681 # an error message was set using a call similar to sqlite3_mprintf(zErr),
682 # where zErr is an arbitrary string. This is no good if the string contains
683 # characters that can be mistaken for printf() formatting directives.
684 #
685 do_test incrblob-9.1 {
686 list [catch { db incrblob t1 "A tricky column name %s%s" 1 } msg] $msg
687 } {1 {no such column: "A tricky column name %s%s"}}
688  
689  
690 finish_test