sqlite/test/memdb1.test

# 2018-01-02
#
# The author disclaims copyright to this source code.  In place of
# a legal notice, here is a blessing:
#
#    May you do good and not evil.
#    May you find forgiveness for yourself and forgive others.
#    May you share freely, never taking more than you give.
#
#***********************************************************************
# This file implements regression tests for SQLite library.  The
# focus of this file is the "memdb" VFS
#

set testdir [file dirname $argv0]
source $testdir/tester.tcl
set testprefix memdb1
do_not_use_codec

ifcapable !deserialize {
  finish_test
  return
}

# Create a MEMDB and populate it with some dummy data.
# Then extract the database into the $::db1 variable.
# Verify that the size of $::db1 is the same as the size of
# the database.
#
unset -nocomplain db1
unset -nocomplain sz1
unset -nocomplain pgsz
do_test 100 {
  db eval {
    CREATE TABLE t1(a,b);
    INSERT INTO t1 VALUES(1,2);
  }
  set ::pgsz [db one {PRAGMA page_size}]
  set ::sz1 [expr {$::pgsz*[db one {PRAGMA page_count}]}]
  set ::db1 [db serialize]
  expr {[string length $::db1]==$::sz1}
} 1
set fd [open db1.db wb]
puts -nonewline $fd $db1
close $fd

# Create a new MEMDB and initialize it to the content of $::db1
# Verify that the content is the same.
#
db close
sqlite3 db
db deserialize $db1
do_execsql_test 110 {
  SELECT * FROM t1;
} {1 2}

# What happens when we try to VACUUM a MEMDB database?
#
do_execsql_test 120 {
  PRAGMA auto_vacuum = off;
  VACUUM;
} {}
do_execsql_test 130 {
  CREATE TABLE t2(x, y);
  WITH RECURSIVE c(x) AS (VALUES(1) UNION ALL SELECT x+1 FROM c WHERE x<100)
   INSERT INTO t2(x, y) SELECT x, randomblob(1000) FROM c;
  DROP TABLE t2;
  PRAGMA page_count;
} {116}
do_execsql_test 140 {
  VACUUM;
  PRAGMA page_count;
} {2}

do_test 150 {
  catch {db deserialize -unknown 1 $db1} msg
  set msg
} {unknown option: -unknown}
do_test 151 {
  db deserialize -readonly 1 $db1
  db eval {SELECT * FROM t1}
} {1 2}
do_test 152 {
  catchsql {INSERT INTO t1 VALUES(3,4);}
} {1 {attempt to write a readonly database}}

do_test 160 {
  db deserialize -maxsize 32768 $db1
  db eval {SELECT * FROM t1}
} {1 2}
do_test 161 {
  db eval {INSERT INTO t1 VALUES(3,4); SELECT * FROM t1}
} {1 2 3 4}
do_test 162 {
  catchsql {INSERT INTO t1 VALUES(5,randomblob(100000))}
} {1 {database or disk is full}}


# Build a largish on-disk database and serialize it.  Verify that the
# serialization works.
#
db close
forcedelete test.db
sqlite3 db test.db
do_execsql_test 200 {
  CREATE TABLE t3(x, y);
  WITH RECURSIVE c(x) AS (VALUES(1) UNION ALL SELECT x+1 FROM c WHERE x<400)
   INSERT INTO t3(x, y) SELECT x, randomblob(1000) FROM c;
  PRAGMA quick_check;
} {ok}
set fd [open test.db rb]
unset -nocomplain direct
set direct [read $fd]
close $fd
do_test 210 {
  string length [db serialize]
} [string length $direct]
do_test 220 {
  db eval {ATTACH ':memory:' AS aux1}
  db deserialize aux1 $::direct
  db eval {
     SELECT x, y FROM main.t3 EXCEPT SELECT x, y FROM aux1.t3;
  }
} {}
unset -nocomplain direct

# Do the same with a :memory: database.
#
db close
sqlite3 db :memory:
do_execsql_test 300 {
  CREATE TABLE t3(x, y);
  WITH RECURSIVE c(x) AS (VALUES(1) UNION ALL SELECT x+1 FROM c WHERE x<400)
   INSERT INTO t3(x, y) SELECT x, randomblob(1000) FROM c;
  PRAGMA quick_check;
} {ok}
do_test 310 {
  db eval {ATTACH ':memory:' AS aux1}
  db deserialize aux1 [db serialize main]
  db eval {
     SELECT x, y FROM main.t3 EXCEPT SELECT x, y FROM aux1.t3;
  }
} {}

# Deserialize an empty database
#
db close
sqlite3 db
db deserialize {}
do_execsql_test 400 {
  PRAGMA integrity_check;
} {ok}
do_execsql_test 410 {
  CREATE TABLE t4(a,b);
  INSERT INTO t4 VALUES('hello','world!');
  PRAGMA integrity_check;
  SELECT * FROM t4;
} {ok hello world!}
do_execsql_test 420 {
  PRAGMA journal_mode=TRUNCATE;
  PRAGMA journal_mode=OFF;
  PRAGMA journal_mode=DELETE;
  PRAGMA journal_mode=WAL;
  PRAGMA journal_mode=PERSIST;
  PRAGMA journal_mode=MEMORY;
  PRAGMA journal_mode=OFF;
  PRAGMA journal_mode=DELETE;
} {truncate off delete delete persist memory off delete}

# Deserialize something that is not a database.
#
db close
sqlite3 db
do_test 500 {
  set rc [catch {db deserialize not-a-database} msg]
  lappend rc $msg
} {0 {}}
do_catchsql_test 510 {
  PRAGMA integrity_check;
} {1 {file is not a database}}

# Abuse the serialize and deserialize commands.  Make sure errors are caught.
#
do_test 600 {
  set rc [catch {db deserialize} msg]
  lappend rc $msg
} {1 {wrong # args: should be "db deserialize ?DATABASE? VALUE"}}
do_test 610 {
  set rc [catch {db deserialize a b c} msg]
  lappend rc $msg
} {1 {unknown option: a}}
do_test 620 {
  set rc [catch {db serialize a b} msg]
  lappend rc $msg
} {1 {wrong # args: should be "db serialize ?DATABASE?"}}

# 2021-07-19 https://sqlite.org/forum/forumpost/e1cbb5f450b98aa6
# The TEMP database cannot participate in serialization or
# deserialization.
#
reset_db
do_test 650 {
  db eval {
    CREATE TEMP TABLE t0(a);
    CREATE TABLE t1(x);
    WITH RECURSIVE c(x) AS (VALUES(1) UNION ALL SELECT x+1 FROM c WHERE x<1000)
    INSERT INTO t1(x) SELECT random() FROM c;
  }
  set rc [catch {db deserialize temp [db serialize main]} err]
  lappend rc err
} {1 err}

#-------------------------------------------------------------------------
ifcapable vtab {
  reset_db
  do_execsql_test 700 {
    CREATE TABLE t1(a, b);
    PRAGMA schema_version = 0;
  }
  do_test 710 {
    set ser [db serialize main]
    db close
    sqlite3 db
    db deserialize main $ser
    catchsql {
      CREATE VIRTUAL TABLE t1 USING rtree(id, a, b, c, d);
    }
  } {1 {table t1 already exists}}
}


#-------------------------------------------------------------------------
# dbsqlfuzz  0a13dfb474d4f2f11a48a2ea57075c96fb456dd7
#
if {[wal_is_capable]} {
  reset_db
  do_execsql_test 800 {
    PRAGMA auto_vacuum = 0;
    PRAGMA page_size = 8192;
    PRAGMA journal_mode = wal;
    CREATE TABLE t1(x, y);
    INSERT INTO t1 VALUES(1, 2);
    CREATE TABLE t2(x, y);
  } {wal}
  db close
  
  set fd [open test.db]
  fconfigure $fd -translation binary
  set data [read $fd [expr 20*1024]]
  close $fd
  
  sqlite3 db ""
  db deserialize $data
  
  do_execsql_test 810 {
    PRAGMA locking_mode = exclusive;
    SELECT * FROM t1
  } {exclusive 1 2}
  
  do_execsql_test 820 {
    INSERT INTO t1 VALUES(3, 4);
    SELECT * FROM t1;
  } {1 2 3 4}
  
  do_catchsql_test 830 {
    PRAGMA wal_checkpoint;
  } {1 {database disk image is malformed}}
}

# 2024-01-20
# https://sqlite.org/forum/forumpost/498777780e16880a
#
# Make sure a database is initialized before serializing it.
#
reset_db
sqlite3 dbempty :memory:
do_test 900 {
  set len [string length [dbempty serialize]]
  expr {$len>0}
} 1
dbempty close


#-------------------------------------------------------------------------
# Make sure a database cannot be overwritten by deserialize() if 
# there is an open transaction on it. 
#
do_execsql_test 1000 {
  CREATE TABLE t(x); 
  INSERT INTO t VALUES(1),(2);
}

set blob [db serialize main]

do_test 1010 {
  set seen 0
  list [catch {
    db eval {SELECT x FROM t} {
      incr seen
      if {$seen == 1} {
        db deserialize main $blob
      }
    }
  } msg] $msg
} {1 {unable to set MEMDB content}}

forcedelete test.db2
sqlite3 db2 test.db2
do_test 1020 {
  set seen 0
  sqlite3_backup B db2 main db main
  B step 2
  set res [list [catch {
    db deserialize main $blob
  } msg] $msg]
  B finish
  set res
} {1 {unable to set MEMDB content}}
db2 close

#-------------------------------------------------------------------------
# Check that things work if the destination database of a backup is
# overwritten using sqlite3_deserialize() between sqlite3_backup_init()
# and the first call to sqlite3_backup_step(). Forum post 15d82885e2.
#
reset_db
do_execsql_test 1100 {
  CREATE TABLE t(x); 
  INSERT INTO t VALUES(1),(2);
}

set blob [db serialize main]

forcedelete test.db2
sqlite3 db2 test.db2
do_test 1110 {
  set seen 0
  sqlite3_backup B db2 main db main
  db2 deserialize main $blob
  B step 2
  B finish
} {SQLITE_OK}

#-------------------------------------------------------------------------
# Check that deserializing into the main db does not leave existing
# eponymous virtual tables with a dangling schema pointer. 
#
# Report: https://sqlite.org/bugs/forumpost/2fbe01f58f
#
ifcapable vtab {
  reset_db
  sqlite3 db :memory:
  
  do_execsql_test 1210 {
    CREATE TABLE t(x); 
    SELECT * FROM json_each('[1]');
  } {
    0 1 integer 1 1 {} {$[0]} {$}
  }
  
  set blob [db serialize main]
  db deserialize main $blob
  
  do_execsql_test 1220 {
    SELECT  *  FROM json_each('[1]');
  } {
    0 1 integer 1 1 {} {$[0]} {$}
  }
  
  # Attempt to deserialize while a statement that uses an eponymous
  # virtual table is still active. The deserialize should fail.
  #
  do_test 1230 {
    set res [list]
    set msg ""
    db eval { SELECT value FROM json_each('[1,2,3]') } {
      if {$msg==""} {
        catch { db deserialize main $blob } msg
      }
      lappend res $value
    }
    set res
  } {1 2 3}
  do_test 1240 {
    set msg
  } {unable to set MEMDB content}
  do_execsql_test 1250 {
    SELECT value FROM json_each('[4,5]');
  } {4 5}
}

#-------------------------------------------------------------------------
# Test that deserializing expires all prepared statements.
#
reset_db
do_execsql_test 1300 {
  CREATE TABLE t1(a, b);
  CREATE INDEX i1 ON t1(a);
  INSERT INTO t1 VALUES(1, 1), (2, 2);
}

set blob [db serialize main]

reset_db
do_execsql_test 1310 {
  CREATE TABLE t2(a, b);
  CREATE TABLE t1(c, d);
  INSERT INTO t1 VALUES(1, 1), (2, 2);
}

do_execsql_test 1320 {
  SELECT * FROM t1;
} {1 1 2 2}

do_test 1330 {
  db deserialize main $blob
} {}

do_execsql_test 1340 {
  SELECT * FROM t1;
} {1 1 2 2}

# Also test that deserializing does not interrupt statements reading 
# from other databases. 
do_execsql_test 1350 {
  ATTACH '' AS aux;
  CREATE TABLE aux.t3(x);
  INSERT INTO aux.t3 VALUES(1), (2), (3);
}
do_test 1360 {
  set res [list]
  db eval { SELECT (SELECT x FROM t3 WHERE t3.x=o.x) AS a FROM t3 AS o } {
    db deserialize main $blob
    lappend res $a
  }
  set res
} {1 2 3}

#-------------------------------------------------------------------------
# A TEMP trigger on a table in a database overwritten by a deserialize() 
# is orphaned in the same way as if the db was DETACHed.
#
reset_db
do_execsql_test 1400 {
  CREATE TABLE t1(x);
}
set blob [db serialize main]

do_execsql_test 1410 {
  CREATE TEMP TABLE tlog(y);
  CREATE TEMP TRIGGER tr AFTER INSERT ON main.t1 BEGIN
    INSERT INTO tlog VALUES(new.x);
  END;
  INSERT INTO t1 VALUES(1);
  SELECT * FROM tlog;
} {1}

do_test 1420 { db deserialize main $blob } {}

do_execsql_test 1430 {
  INSERT INTO t1 VALUES(2);
  SELECT * FROM tlog;
} {1}

do_execsql_test 1440 {
  SELECT name, tbl_name FROM temp.sqlite_schema WHERE type='trigger';
} {tr t1}

do_execsql_test 1450 {
  CREATE TEMP TABLE t1(x);
  INSERT INTO temp.t1 VALUES(3);
  SELECT * FROM tlog;
} {1}

do_execsql_test 1460 {
  DROP TRIGGER temp.tr;
  SELECT name FROM temp.sqlite_schema WHERE type='trigger';
} {}

finish_test