gitlab.com/CoiaPrant/sqlite3@v1.19.1/testdata/tcl/memdb1.test (about) 1 # 2018-01-02 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 # This file implements regression tests for SQLite library. The 12 # focus of this file is the "memdb" VFS 13 # 14 15 set testdir [file dirname $argv0] 16 source $testdir/tester.tcl 17 set testprefix memdb1 18 do_not_use_codec 19 20 ifcapable !deserialize { 21 finish_test 22 return 23 } 24 25 # Create a MEMDB and populate it with some dummy data. 26 # Then extract the database into the $::db1 variable. 27 # Verify that the size of $::db1 is the same as the size of 28 # the database. 29 # 30 unset -nocomplain db1 31 unset -nocomplain sz1 32 unset -nocomplain pgsz 33 do_test 100 { 34 db eval { 35 CREATE TABLE t1(a,b); 36 INSERT INTO t1 VALUES(1,2); 37 } 38 set ::pgsz [db one {PRAGMA page_size}] 39 set ::sz1 [expr {$::pgsz*[db one {PRAGMA page_count}]}] 40 set ::db1 [db serialize] 41 expr {[string length $::db1]==$::sz1} 42 } 1 43 set fd [open db1.db wb] 44 puts -nonewline $fd $db1 45 close $fd 46 47 # Create a new MEMDB and initialize it to the content of $::db1 48 # Verify that the content is the same. 49 # 50 db close 51 sqlite3 db 52 db deserialize $db1 53 do_execsql_test 110 { 54 SELECT * FROM t1; 55 } {1 2} 56 57 # What happens when we try to VACUUM a MEMDB database? 58 # 59 do_execsql_test 120 { 60 PRAGMA auto_vacuum = off; 61 VACUUM; 62 } {} 63 do_execsql_test 130 { 64 CREATE TABLE t2(x, y); 65 WITH RECURSIVE c(x) AS (VALUES(1) UNION ALL SELECT x+1 FROM c WHERE x<100) 66 INSERT INTO t2(x, y) SELECT x, randomblob(1000) FROM c; 67 DROP TABLE t2; 68 PRAGMA page_count; 69 } {116} 70 do_execsql_test 140 { 71 VACUUM; 72 PRAGMA page_count; 73 } {2} 74 75 do_test 150 { 76 catch {db deserialize -unknown 1 $db1} msg 77 set msg 78 } {unknown option: -unknown} 79 do_test 151 { 80 db deserialize -readonly 1 $db1 81 db eval {SELECT * FROM t1} 82 } {1 2} 83 do_test 152 { 84 catchsql {INSERT INTO t1 VALUES(3,4);} 85 } {1 {attempt to write a readonly database}} 86 87 breakpoint 88 do_test 160 { 89 db deserialize -maxsize 32768 $db1 90 db eval {SELECT * FROM t1} 91 } {1 2} 92 do_test 161 { 93 db eval {INSERT INTO t1 VALUES(3,4); SELECT * FROM t1} 94 } {1 2 3 4} 95 do_test 162 { 96 catchsql {INSERT INTO t1 VALUES(5,randomblob(100000))} 97 } {1 {database or disk is full}} 98 99 100 # Build a largish on-disk database and serialize it. Verify that the 101 # serialization works. 102 # 103 db close 104 forcedelete test.db 105 sqlite3 db test.db 106 do_execsql_test 200 { 107 CREATE TABLE t3(x, y); 108 WITH RECURSIVE c(x) AS (VALUES(1) UNION ALL SELECT x+1 FROM c WHERE x<400) 109 INSERT INTO t3(x, y) SELECT x, randomblob(1000) FROM c; 110 PRAGMA quick_check; 111 } {ok} 112 set fd [open test.db rb] 113 unset -nocomplain direct 114 set direct [read $fd] 115 close $fd 116 do_test 210 { 117 string length [db serialize] 118 } [string length $direct] 119 do_test 220 { 120 db eval {ATTACH ':memory:' AS aux1} 121 db deserialize aux1 $::direct 122 db eval { 123 SELECT x, y FROM main.t3 EXCEPT SELECT x, y FROM aux1.t3; 124 } 125 } {} 126 unset -nocomplain direct 127 128 # Do the same with a :memory: database. 129 # 130 db close 131 sqlite3 db :memory: 132 do_execsql_test 300 { 133 CREATE TABLE t3(x, y); 134 WITH RECURSIVE c(x) AS (VALUES(1) UNION ALL SELECT x+1 FROM c WHERE x<400) 135 INSERT INTO t3(x, y) SELECT x, randomblob(1000) FROM c; 136 PRAGMA quick_check; 137 } {ok} 138 do_test 310 { 139 db eval {ATTACH ':memory:' AS aux1} 140 db deserialize aux1 [db serialize main] 141 db eval { 142 SELECT x, y FROM main.t3 EXCEPT SELECT x, y FROM aux1.t3; 143 } 144 } {} 145 146 # Deserialize an empty database 147 # 148 db close 149 sqlite3 db 150 db deserialize {} 151 do_execsql_test 400 { 152 PRAGMA integrity_check; 153 } {ok} 154 do_execsql_test 410 { 155 CREATE TABLE t4(a,b); 156 INSERT INTO t4 VALUES('hello','world!'); 157 PRAGMA integrity_check; 158 SELECT * FROM t4; 159 } {ok hello world!} 160 do_execsql_test 420 { 161 PRAGMA journal_mode=TRUNCATE; 162 PRAGMA journal_mode=OFF; 163 PRAGMA journal_mode=DELETE; 164 PRAGMA journal_mode=WAL; 165 PRAGMA journal_mode=PERSIST; 166 PRAGMA journal_mode=MEMORY; 167 PRAGMA journal_mode=OFF; 168 PRAGMA journal_mode=DELETE; 169 } {truncate off delete delete persist memory off delete} 170 171 # Deserialize something that is not a database. 172 # 173 db close 174 sqlite3 db 175 do_test 500 { 176 set rc [catch {db deserialize not-a-database} msg] 177 lappend rc $msg 178 } {0 {}} 179 do_catchsql_test 510 { 180 PRAGMA integrity_check; 181 } {1 {file is not a database}} 182 183 # Abuse the serialize and deserialize commands. Make sure errors are caught. 184 # 185 do_test 600 { 186 set rc [catch {db deserialize} msg] 187 lappend rc $msg 188 } {1 {wrong # args: should be "db deserialize ?DATABASE? VALUE"}} 189 do_test 610 { 190 set rc [catch {db deserialize a b c} msg] 191 lappend rc $msg 192 } {1 {unknown option: a}} 193 do_test 620 { 194 set rc [catch {db serialize a b} msg] 195 lappend rc $msg 196 } {1 {wrong # args: should be "db serialize ?DATABASE?"}} 197 198 # 2021-07-19 https://sqlite.org/forum/forumpost/e1cbb5f450b98aa6 199 # The TEMP database cannot participate in serialization or 200 # deserialization. 201 # 202 reset_db 203 do_test 650 { 204 db eval { 205 CREATE TEMP TABLE t0(a); 206 CREATE TABLE t1(x); 207 WITH RECURSIVE c(x) AS (VALUES(1) UNION ALL SELECT x+1 FROM c WHERE x<1000) 208 INSERT INTO t1(x) SELECT random() FROM c; 209 } 210 set rc [catch {db deserialize temp [db serialize main]} err] 211 lappend rc err 212 } {1 err} 213 214 #------------------------------------------------------------------------- 215 ifcapable vtab { 216 reset_db 217 do_execsql_test 700 { 218 CREATE TABLE t1(a, b); 219 PRAGMA schema_version = 0; 220 } 221 do_test 710 { 222 set ser [db serialize main] 223 db close 224 sqlite3 db 225 db deserialize main $ser 226 catchsql { 227 CREATE VIRTUAL TABLE t1 USING rtree(id, a, b, c, d); 228 } 229 } {1 {table t1 already exists}} 230 } 231 232 233 #------------------------------------------------------------------------- 234 # dbsqlfuzz 0a13dfb474d4f2f11a48a2ea57075c96fb456dd7 235 # 236 if {[wal_is_capable]} { 237 reset_db 238 do_execsql_test 800 { 239 PRAGMA auto_vacuum = 0; 240 PRAGMA page_size = 8192; 241 PRAGMA journal_mode = wal; 242 CREATE TABLE t1(x, y); 243 INSERT INTO t1 VALUES(1, 2); 244 CREATE TABLE t2(x, y); 245 } {wal} 246 db close 247 248 set fd [open test.db] 249 fconfigure $fd -translation binary -encoding binary 250 set data [read $fd [expr 20*1024]] 251 252 sqlite3 db "" 253 db deserialize $data 254 255 do_execsql_test 810 { 256 PRAGMA locking_mode = exclusive; 257 SELECT * FROM t1 258 } {exclusive 1 2} 259 260 do_execsql_test 820 { 261 INSERT INTO t1 VALUES(3, 4); 262 SELECT * FROM t1; 263 } {1 2 3 4} 264 265 do_catchsql_test 830 { 266 PRAGMA wal_checkpoint; 267 } {1 {database disk image is malformed}} 268 } 269 270 finish_test