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

third_party/sqlite/src/test/recover.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.
+#
+# This file implements tests for the recover module, which can read
+# through corrupt rows and pages.
+#
+# $Id$
+
+# TODO(shess): These all test that the module correctly reads good
+# data.  It would be good to implement tests of corrupt data.
+
+set testdir [file dirname $argv0]
+source $testdir/tester.tcl
+
+db eval {
+  DROP TABLE IF EXISTS altered;
+  CREATE TABLE altered (
+    c TEXT
+  );
+  INSERT INTO altered VALUES ('a');
+  INSERT INTO altered VALUES ('b');
+  INSERT INTO altered VALUES ('c');
+  ALTER TABLE altered ADD COLUMN i INTEGER NOT NULL DEFAULT 10;
+  INSERT INTO altered VALUES ('d', 5);
+}
+
+# SQLite will fill the earlier rows with the default.
+do_test recover-alter-1.0 {
+  execsql {SELECT c, i FROM altered ORDER BY rowid}
+} {a 10 b 10 c 10 d 5}
+
+# recover sees NULL for those rows.
+do_test recover-alter-1.1 {
+  db eval {
+    DROP TABLE IF EXISTS temp.altered_recover;
+    CREATE VIRTUAL TABLE temp.altered_recover USING recover(
+      altered,
+      c TEXT,
+      i INTEGER
+    );
+  }
+  execsql {SELECT c, i FROM altered_recover ORDER BY rowid}
+} {a {} b {} c {} d 5}
+
+# Can skip in the normal fashion.
+do_test recover-alter-1.2 {
+  db eval {
+    DROP TABLE IF EXISTS temp.altered_recover;
+    CREATE VIRTUAL TABLE temp.altered_recover USING recover(
+      altered,
+      c TEXT,
+      i INTEGER NOT NULL
+    );
+  }
+  execsql {SELECT c, i FROM altered_recover ORDER BY rowid}
+} {d 5}
+
+if {0} {
+# It would be neat if this could work.  I tried putting "DEFAULT ..."
+# in the schema exposed by the recover table, but it doesn't do the
+# trick.
+do_test recover-alter-1.2 {
+  db eval {
+    DROP TABLE IF EXISTS temp.altered_recover;
+    CREATE VIRTUAL TABLE temp.altered_recover USING recover(
+      altered,
+      c TEXT,
+      i INTEGER NOT NULL DEFAULT 10
+    );
+  }
+  execsql {SELECT c, i FROM altered_recover ORDER BY rowid}
+} {a 10 b 10 c 10 d 5}
+}
+
+proc generate {table base count} {
+  db eval "DROP TABLE IF EXISTS $table"
+  db transaction immediate {
+    db eval "CREATE TABLE $table (t TEXT,n INT)"
+    for {set i 0} {$i<$count} {incr i} {
+      set t [concat $base $i]
+      db eval [concat {INSERT INTO} $table {VALUES ($t, $i)}]
+    }
+  }
+}
+
+# Leaf-only database parses.
+do_test recover-leaf-1.0 {
+  db close
+  sqlite3 db test.db
+  generate "leaf" "Leaf-node-generating line " 10
+
+  db eval {DROP TABLE IF EXISTS temp.leaf_recover}
+  db eval {CREATE VIRTUAL TABLE temp.leaf_recover USING recover(leaf, t TEXT, n INTEGER)}
+  execsql {SELECT t, n FROM leaf_recover ORDER BY rowid}
+} {{Leaf-node-generating line 0} 0 {Leaf-node-generating line 1} 1 {Leaf-node-generating line 2} 2 {Leaf-node-generating line 3} 3 {Leaf-node-generating line 4} 4 {Leaf-node-generating line 5} 5 {Leaf-node-generating line 6} 6 {Leaf-node-generating line 7} 7 {Leaf-node-generating line 8} 8 {Leaf-node-generating line 9} 9}
+
+# Single level of interior node.
+do_test recover-interior-1.0 {
+  db close
+  sqlite3 db test.db
+  generate "interior" "Interior-node-generating line " 100
+
+  db eval {DROP TABLE IF EXISTS temp.interior_recover}
+  db eval {CREATE VIRTUAL TABLE temp.interior_recover USING recover(interior, t TEXT, n INTEGER)}
+  execsql {SELECT t, n FROM interior_recover WHERE (rowid%10)=0 ORDER BY rowid}
+} {{Interior-node-generating line 9} 9 {Interior-node-generating line 19} 19 {Interior-node-generating line 29} 29 {Interior-node-generating line 39} 39 {Interior-node-generating line 49} 49 {Interior-node-generating line 59} 59 {Interior-node-generating line 69} 69 {Interior-node-generating line 79} 79 {Interior-node-generating line 89} 89 {Interior-node-generating line 99} 99}
+
+# Multiple levels of interior node.
+do_test recover-interior-2.0 {
+  db close
+  sqlite3 db test.db
+  generate "interior2" "Interior-node-generating line " 5000
+
+  db eval {DROP TABLE IF EXISTS temp.interior2_recover}
+  db eval {CREATE VIRTUAL TABLE temp.interior2_recover USING recover(interior2, t TEXT, n INTEGER)}
+  execsql {SELECT t, n FROM interior2_recover WHERE (rowid%500)=0 ORDER BY rowid}
+} {{Interior-node-generating line 499} 499 {Interior-node-generating line 999} 999 {Interior-node-generating line 1499} 1499 {Interior-node-generating line 1999} 1999 {Interior-node-generating line 2499} 2499 {Interior-node-generating line 2999} 2999 {Interior-node-generating line 3499} 3499 {Interior-node-generating line 3999} 3999 {Interior-node-generating line 4499} 4499 {Interior-node-generating line 4999} 4999}
+
+finish_test