Annotation of embedaddon/sqlite3/test/incrblob.test, revision 1.1.1.1
1.1 misho 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: forcedelete 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: forcedelete 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: forcedelete 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
FreeBSD-CVSweb <freebsd-cvsweb@FreeBSD.org>
![[BACK]](/icons/cvsweb/back.gif){kind=icon}
Return to incrblob.test CVS log ![[TXT]](/icons/cvsweb/text.gif){kind=icon}
![[DIR]](/icons/cvsweb/dir.gif){kind=icon}
Up to [ELWIX - Embedded LightWeight unIX -] / embedaddon / sqlite3 / test