The complete work-in-progress SQLite recover virtual table.

third_party/sqlite/src/test/recover0.test

+# 2012 January 4 {}
+#
+# 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.
+#
+# Test recover module syntax.
+#
+# $Id$
+
+# TODO(shess): Test with attached databases.
+
+# TODO(shess): Handle column mismatches?  As things stand, the code
+# only needs to pull the root page, so that may not be completely
+# feasible.
+
+set testdir [file dirname $argv0]
+source $testdir/tester.tcl
+
+db eval {
+  DROP TABLE IF EXISTS backing;
+  CREATE TABLE backing (t TEXT);
+
+  DROP TABLE IF EXISTS backing2;
+  CREATE TABLE backing2 (id INTEGER PRIMARY KEY, t TEXT);
+}
+
+# Baseline create works.
+do_test recover-syntax-0.0 {
+  db eval {DROP TABLE IF EXISTS temp.syntax}
+  catchsql {
+    CREATE VIRTUAL TABLE temp.syntax USING recover(
+      backing,
+      t TEXT
+    );
+  }
+} {0 {}}
+
+# Can specify database.
+do_test recover-syntax-0.1 {
+  db eval {DROP TABLE IF EXISTS temp.syntax}
+  catchsql {
+    CREATE VIRTUAL TABLE temp.syntax USING recover(
+      main.backing,
+      t TEXT
+    );
+  }
+} {0 {}}
+
+# Can specify sqlite_master.
+do_test recover-syntax-0.2 {
+  db eval {DROP TABLE IF EXISTS temp.syntax}
+  catchsql {
+    CREATE VIRTUAL TABLE temp.syntax USING recover(
+      sqlite_master,
+      type TEXT,
+      name TEXT,
+      tbl_name TEXT,
+      rootpage INTEGER,
+      sql TEXT
+    );
+  }
+} {0 {}}
+
+# Fails if virtual table is not in the temp database.
+do_test recover-syntax-1.0 {
+  db eval {DROP TABLE IF EXISTS temp.syntax;}
+  catchsql {
+    CREATE VIRTUAL TABLE syntax USING recover(
+      backing,
+      t TEXT
+    );
+  }
+} {1 {vtable constructor failed: syntax}}
+
+# Fails if mentions missing table.
+do_test recover-syntax-2.0 {
+  db eval {DROP TABLE IF EXISTS temp.syntax;}
+  catchsql {
+    CREATE VIRTUAL TABLE temp.syntax USING recover(
+      snacking,
+      t TEXT
+    );
+  }
+} {1 {vtable constructor failed: syntax}}
+
+# Fails if mentions missing database.
+do_test recover-syntax-2.1 {
+  db eval {DROP TABLE IF EXISTS temp.syntax;}
+  catchsql {
+    CREATE VIRTUAL TABLE temp.syntax USING recover(
+      db.backing,
+      t TEXT
+    );
+  }
+} {1 {vtable constructor failed: syntax}}
+
+# Fails if mentions garbage backing.
+do_test recover-syntax-2.2 {
+  db eval {DROP TABLE IF EXISTS temp.syntax;}
+  catchsql {
+    CREATE VIRTUAL TABLE temp.syntax USING recover(
+      main.backing excess,
+      t TEXT
+    );
+  }
+} {1 {vtable constructor failed: syntax}}
+
+# Manifest typing.
+do_test recover-syntax-3.0 {
+  db eval {DROP TABLE IF EXISTS temp.syntax}
+  execsql {
+    CREATE VIRTUAL TABLE temp.syntax USING recover(
+      backing,
+      t
+    );
+    PRAGMA table_info(syntax);
+  }
+} {0 t {} 0 {} 0}
+
+# ANY as an alternative for manifest typing.
+do_test recover-syntax-3.1 {
+  db eval {DROP TABLE IF EXISTS temp.syntax}
+  execsql {
+    CREATE VIRTUAL TABLE temp.syntax USING recover(
+      backing,
+      t ANY
+    );
+    PRAGMA table_info(syntax);
+  }
+} {0 t {} 0 {} 0}
+
+# ANY NOT NULL
+do_test recover-syntax-3.2 {
+  db eval {DROP TABLE IF EXISTS temp.syntax}
+  execsql {
+    CREATE VIRTUAL TABLE temp.syntax USING recover(
+      backing,
+      t ANY NOT NULL
+    );
+    PRAGMA table_info(syntax);
+  }
+} {0 t {} 1 {} 0}
+
+# ANY STRICT is not sensible.
+do_test recover-syntax-3.3 {
+  db eval {DROP TABLE IF EXISTS temp.syntax}
+  catchsql {
+    CREATE VIRTUAL TABLE temp.syntax USING recover(
+      backing,
+      v ANY STRICT
+    );
+    PRAGMA table_info(syntax);
+  }
+} {1 {vtable constructor failed: syntax}}
+
+# TEXT column by type works.
+do_test recover-syntax-4.0 {
+  db eval {DROP TABLE IF EXISTS temp.syntax}
+  execsql {
+    CREATE VIRTUAL TABLE temp.syntax USING recover(
+      backing,
+      t TEXT
+    );
+    PRAGMA table_info(syntax);
+  }
+} {0 t TEXT 0 {} 0}
+
+# TEXT NOT NULL
+do_test recover-syntax-4.1 {
+  db eval {DROP TABLE IF EXISTS temp.syntax}
+  execsql {
+    CREATE VIRTUAL TABLE temp.syntax USING recover(
+      backing,
+      t TEXT NOT NULL
+    );
+    PRAGMA table_info(syntax);
+  }
+} {0 t TEXT 1 {} 0}
+
+# TEXT STRICT
+do_test recover-syntax-4.2 {
+  db eval {DROP TABLE IF EXISTS temp.syntax}
+  execsql {
+    CREATE VIRTUAL TABLE temp.syntax USING recover(
+      backing,
+      t TEXT STRICT
+    );
+    PRAGMA table_info(syntax);
+  }
+} {0 t TEXT 0 {} 0}
+
+# TEXT STRICT NOT NULL
+do_test recover-syntax-4.3 {
+  db eval {DROP TABLE IF EXISTS temp.syntax}
+  execsql {
+    CREATE VIRTUAL TABLE temp.syntax USING recover(
+      backing,
+      t TEXT STRICT NOT NULL
+    );
+    PRAGMA table_info(syntax);
+  }
+} {0 t TEXT 1 {} 0}
+
+# INTEGER
+do_test recover-syntax-5.0 {
+  db eval {DROP TABLE IF EXISTS temp.syntax}
+  execsql {
+    CREATE VIRTUAL TABLE temp.syntax USING recover(
+      backing,
+      i INTEGER
+    );
+    PRAGMA table_info(syntax);
+  }
+} {0 i INTEGER 0 {} 0}
+
+# INTEGER NOT NULL
+do_test recover-syntax-5.1 {
+  db eval {DROP TABLE IF EXISTS temp.syntax}
+  execsql {
+    CREATE VIRTUAL TABLE temp.syntax USING recover(
+      backing,
+      i INTEGER NOT NULL
+    );
+    PRAGMA table_info(syntax);
+  }
+} {0 i INTEGER 1 {} 0}
+
+# INTEGER STRICT
+do_test recover-syntax-5.2 {
+  db eval {DROP TABLE IF EXISTS temp.syntax}
+  execsql {
+    CREATE VIRTUAL TABLE temp.syntax USING recover(
+      backing,
+      i INTEGER STRICT
+    );
+    PRAGMA table_info(syntax);
+  }
+} {0 i INTEGER 0 {} 0}
+
+# INTEGER STRICT NOT NULL
+do_test recover-syntax-5.3 {
+  db eval {DROP TABLE IF EXISTS temp.syntax}
+  execsql {
+    CREATE VIRTUAL TABLE temp.syntax USING recover(
+      backing,
+      i INTEGER STRICT NOT NULL
+    );
+    PRAGMA table_info(syntax);
+  }
+} {0 i INTEGER 1 {} 0}
+
+# BLOB
+do_test recover-syntax-6.0 {
+  db eval {DROP TABLE IF EXISTS temp.syntax}
+  execsql {
+    CREATE VIRTUAL TABLE temp.syntax USING recover(
+      backing,
+      b BLOB
+    );
+    PRAGMA table_info(syntax);
+  }
+} {0 b BLOB 0 {} 0}
+
+# BLOB NOT NULL
+do_test recover-syntax-6.1 {
+  db eval {DROP TABLE IF EXISTS temp.syntax}
+  execsql {
+    CREATE VIRTUAL TABLE temp.syntax USING recover(
+      backing,
+      b BLOB NOT NULL
+    );
+    PRAGMA table_info(syntax);
+  }
+} {0 b BLOB 1 {} 0}
+
+# BLOB STRICT
+do_test recover-syntax-6.2 {
+  db eval {DROP TABLE IF EXISTS temp.syntax}
+  execsql {
+    CREATE VIRTUAL TABLE temp.syntax USING recover(
+      backing,
+      b BLOB STRICT
+    );
+    PRAGMA table_info(syntax);
+  }
+} {0 b BLOB 0 {} 0}
+
+# BLOB STRICT NOT NULL
+do_test recover-syntax-6.3 {
+  db eval {DROP TABLE IF EXISTS temp.syntax}
+  execsql {
+    CREATE VIRTUAL TABLE temp.syntax USING recover(
+      backing,
+      b BLOB STRICT NOT NULL
+    );
+    PRAGMA table_info(syntax);
+  }
+} {0 b BLOB 1 {} 0}
+
+# FLOAT
+do_test recover-syntax-7.0 {
+  db eval {DROP TABLE IF EXISTS temp.syntax}
+  execsql {
+    CREATE VIRTUAL TABLE temp.syntax USING recover(
+      backing,
+      f FLOAT
+    );
+    PRAGMA table_info(syntax);
+  }
+} {0 f FLOAT 0 {} 0}
+
+# FLOAT NOT NULL
+do_test recover-syntax-7.1 {
+  db eval {DROP TABLE IF EXISTS temp.syntax}
+  execsql {
+    CREATE VIRTUAL TABLE temp.syntax USING recover(
+      backing,
+      f FLOAT NOT NULL
+    );
+    PRAGMA table_info(syntax);
+  }
+} {0 f FLOAT 1 {} 0}
+
+# FLOAT STRICT
+do_test recover-syntax-7.2 {
+  db eval {DROP TABLE IF EXISTS temp.syntax}
+  execsql {
+    CREATE VIRTUAL TABLE temp.syntax USING recover(
+      backing,
+      f FLOAT STRICT
+    );
+    PRAGMA table_info(syntax);
+  }
+} {0 f FLOAT 0 {} 0}
+
+# FLOAT STRICT NOT NULL
+do_test recover-syntax-7.3 {
+  db eval {DROP TABLE IF EXISTS temp.syntax}
+  execsql {
+    CREATE VIRTUAL TABLE temp.syntax USING recover(
+      backing,
+      f FLOAT STRICT NOT NULL
+    );
+    PRAGMA table_info(syntax);
+  }
+} {0 f FLOAT 1 {} 0}
+
+# NUMERIC
+do_test recover-syntax-8.0 {
+  db eval {DROP TABLE IF EXISTS temp.syntax}
+  execsql {
+    CREATE VIRTUAL TABLE temp.syntax USING recover(
+      backing,
+      f NUMERIC
+    );
+    PRAGMA table_info(syntax);
+  }
+} {0 f NUMERIC 0 {} 0}
+
+# NUMERIC NOT NULL
+do_test recover-syntax-8.1 {
+  db eval {DROP TABLE IF EXISTS temp.syntax}
+  execsql {
+    CREATE VIRTUAL TABLE temp.syntax USING recover(
+      backing,
+      f NUMERIC NOT NULL
+    );
+    PRAGMA table_info(syntax);
+  }
+} {0 f NUMERIC 1 {} 0}
+
+# NUMERIC STRICT
+do_test recover-syntax-8.2 {
+  db eval {DROP TABLE IF EXISTS temp.syntax}
+  execsql {
+    CREATE VIRTUAL TABLE temp.syntax USING recover(
+      backing,
+      f NUMERIC STRICT
+    );
+    PRAGMA table_info(syntax);
+  }
+} {0 f NUMERIC 0 {} 0}
+
+# NUMERIC STRICT NOT NULL
+do_test recover-syntax-8.3 {
+  db eval {DROP TABLE IF EXISTS temp.syntax}
+  execsql {
+    CREATE VIRTUAL TABLE temp.syntax USING recover(
+      backing,
+      f NUMERIC STRICT NOT NULL
+    );
+    PRAGMA table_info(syntax);
+  }
+} {0 f NUMERIC 1 {} 0}
+
+# ROWID
+do_test recover-syntax-9.0 {
+  db eval {DROP TABLE IF EXISTS temp.syntax}
+  execsql {
+    CREATE VIRTUAL TABLE temp.syntax USING recover(
+      backing2,
+      id ROWID,
+      v
+    );
+    PRAGMA table_info(syntax);
+  }
+} {0 id INTEGER 1 {} 0 1 v {} 0 {} 0}
+
+# ROWID NOT NULL (is default)
+do_test recover-syntax-9.1 {
+  db eval {DROP TABLE IF EXISTS temp.syntax}
+  execsql {
+    CREATE VIRTUAL TABLE temp.syntax USING recover(
+      backing2,
+      id ROWID NOT NULL,
+      v
+    );
+    PRAGMA table_info(syntax);
+  }
+} {0 id INTEGER 1 {} 0 1 v {} 0 {} 0}
+
+# ROWID STRICT
+do_test recover-syntax-9.0 {
+  db eval {DROP TABLE IF EXISTS temp.syntax}
+  execsql {
+    CREATE VIRTUAL TABLE temp.syntax USING recover(
+      backing2,
+      id ROWID STRICT,
+      v
+    );
+    PRAGMA table_info(syntax);
+  }
+} {0 id INTEGER 1 {} 0 1 v {} 0 {} 0}
+
+# ROWID STRICT NOT NULL (is default)
+do_test recover-syntax-9.1 {
+  db eval {DROP TABLE IF EXISTS temp.syntax}
+  execsql {
+    CREATE VIRTUAL TABLE temp.syntax USING recover(
+      backing2,
+      id ROWID STRICT NOT NULL,
+      v
+    );
+    PRAGMA table_info(syntax);
+  }
+} {0 id INTEGER 1 {} 0 1 v {} 0 {} 0}
+
+# Invalid type info is not ignored.
+do_test recover-syntax-10.0 {
+  db eval {DROP TABLE IF EXISTS temp.syntax}
+  catchsql {
+    CREATE VIRTUAL TABLE temp.syntax USING recover(
+      backing,
+      v GARBAGE
+    );
+  }
+} {1 {vtable constructor failed: syntax}}
+
+# Extraneous type info is not ignored.
+do_test recover-syntax-10.1 {
+  db eval {DROP TABLE IF EXISTS temp.syntax}
+  catchsql {
+    CREATE VIRTUAL TABLE temp.syntax USING recover(
+      backing,
+      v INTEGER GARBAGE
+    );
+  }
+} {1 {vtable constructor failed: syntax}}
+
+# Extraneous type info is not ignored.
+do_test recover-syntax-10.2 {
+  db eval {DROP TABLE IF EXISTS temp.syntax}
+  catchsql {
+    CREATE VIRTUAL TABLE temp.syntax USING recover(
+      backing,
+      v INTEGER NOT NULL GARBAGE
+    );
+  }
+} {1 {vtable constructor failed: syntax}}
+
+# Multiple types don't work.
+do_test recover-syntax-10.3 {
+  db eval {DROP TABLE IF EXISTS temp.syntax}
+  catchsql {
+    CREATE VIRTUAL TABLE temp.syntax USING recover(
+      backing,
+      v INTEGER FLOAT BLOB
+    );
+  }
+} {1 {vtable constructor failed: syntax}}
+
+# Multiple types don't work.
+do_test recover-syntax-10.4 {
+  db eval {DROP TABLE IF EXISTS temp.syntax}
+  catchsql {
+    CREATE VIRTUAL TABLE temp.syntax USING recover(
+      backing,
+      v INTEGER NOT NULL TEXT
+    );
+  }
+} {1 {vtable constructor failed: syntax}}
+
+finish_test