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 15set testdir [file dirname $argv0] 16source $testdir/tester.tcl 17set testprefix memdb1 18do_not_use_codec 19 20ifcapable !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# 30unset -nocomplain db1 31unset -nocomplain sz1 32unset -nocomplain pgsz 33do_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 43set fd [open db1.db wb] 44puts -nonewline $fd $db1 45close $fd 46 47# Create a new MEMDB and initialize it to the content of $::db1 48# Verify that the content is the same. 49# 50db close 51sqlite3 db 52db deserialize $db1 53do_execsql_test 110 { 54 SELECT * FROM t1; 55} {1 2} 56 57# What happens when we try to VACUUM a MEMDB database? 58# 59do_execsql_test 120 { 60 PRAGMA auto_vacuum = off; 61 VACUUM; 62} {} 63do_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} 70do_execsql_test 140 { 71 VACUUM; 72 PRAGMA page_count; 73} {2} 74 75do_test 150 { 76 catch {db deserialize -unknown 1 $db1} msg 77 set msg 78} {unknown option: -unknown} 79do_test 151 { 80 db deserialize -readonly 1 $db1 81 db eval {SELECT * FROM t1} 82} {1 2} 83do_test 152 { 84 catchsql {INSERT INTO t1 VALUES(3,4);} 85} {1 {attempt to write a readonly database}} 86 87breakpoint 88do_test 160 { 89 db deserialize -maxsize 32768 $db1 90 db eval {SELECT * FROM t1} 91} {1 2} 92do_test 161 { 93 db eval {INSERT INTO t1 VALUES(3,4); SELECT * FROM t1} 94} {1 2 3 4} 95do_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# 103db close 104forcedelete test.db 105sqlite3 db test.db 106do_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} 112set fd [open test.db rb] 113unset -nocomplain direct 114set direct [read $fd] 115close $fd 116do_test 210 { 117 string length [db serialize] 118} [string length $direct] 119do_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} {} 126unset -nocomplain direct 127 128# Do the same with a :memory: database. 129# 130db close 131sqlite3 db :memory: 132do_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} 138do_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# 148db close 149sqlite3 db 150db deserialize {} 151do_execsql_test 400 { 152 PRAGMA integrity_check; 153} {ok} 154do_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!} 160do_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# 173db close 174sqlite3 db 175do_test 500 { 176 set rc [catch {db deserialize not-a-database} msg] 177 lappend rc $msg 178} {0 {}} 179do_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# 185do_test 600 { 186 set rc [catch {db deserialize} msg] 187 lappend rc $msg 188} {1 {wrong # args: should be "db deserialize ?DATABASE? VALUE"}} 189do_test 610 { 190 set rc [catch {db deserialize a b c} msg] 191 lappend rc $msg 192} {1 {unknown option: a}} 193do_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# 202reset_db 203do_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#------------------------------------------------------------------------- 215ifcapable 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# 236if {[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 270finish_test 271