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

third_party/sqlite/src/my.init

+-- SQLite -init file for driving some tests.
+
+DROP TABLE IF EXISTS overflow_test;
+CREATE TABLE overflow_test (value TEXT);
+INSERT INTO overflow_test VALUES (
+"Lorem ipsum dolor sit amet, consectetur adipiscing elit. Nam sagittis gravida odio vitae ultrices. Nulla facilisi. Maecenas pulvinar, tellus ut bibendum semper, nibh tellus auctor nulla, in dignissim nisi ipsum id arcu. Nullam tincidunt arcu malesuada turpis faucibus in adipiscing enim mattis. Fusce augue magna, scelerisque sollicitudin egestas in, cursus eu sapien. Pellentesque tempor risus at lectus convallis a convallis orci ornare. Integer tristique aliquam leo vel interdum.
+
+Phasellus quis dictum nisi. Curabitur at enim non felis pharetra imperdiet. Duis tempus massa eu leo varius porta. Vestibulum sodales nulla at purus tincidunt ultrices. Nam euismod posuere nibh, nec sodales magna luctus ac. Ut commodo hendrerit mauris vitae gravida. In interdum justo ut sem eleifend convallis. Donec cursus molestie elementum. Suspendisse at nisl tellus, vel consequat mauris. Nullam non justo nibh.
+
+Maecenas varius sollicitudin libero, nec feugiat turpis facilisis eget. Quisque et sem risus. Aenean a magna quis purus hendrerit mattis eu vel lorem. Aenean fringilla diam eget tortor lacinia sed mollis eros feugiat. Quisque ac purus sapien. Nullam quis tellus vel magna convallis tincidunt. Donec eget ligula at libero tincidunt congue ut ut lacus. Integer dignissim aliquet congue. Pellentesque sed risus vitae lorem porta viverra ac eu risus. Vivamus congue suscipit odio pulvinar aliquet. Aliquam porttitor nunc non sapien auctor et vehicula augue molestie.
+
+Aliquam et dui ac sem tempus dictum. Fusce arcu nulla, viverra sit amet suscipit quis, malesuada at felis. Fusce ut diam felis. Fusce id ligula non eros fermentum sodales in nec quam. Donec tempor bibendum arcu ac adipiscing. Praesent nisl lectus, tempor ut vehicula eget, mattis a justo. Mauris condimentum luctus eros a varius. Morbi mollis elit eget velit convallis eu sodales odio egestas. Cum sociis natoque penatibus et magnis dis parturient montes, nascetur ridiculus mus. Vivamus interdum, metus sit amet varius varius, lectus eros semper risus, sed sagittis ipsum libero in sapien. Nam lacinia nulla vitae magna facilisis scelerisque. Lorem ipsum dolor sit amet, consectetur adipiscing elit.
+
+Donec gravida dignissim eleifend. Aliquam vel tincidunt tortor. Curabitur massa ante, blandit a auctor at, ullamcorper sed nisl. Class aptent taciti sociosqu ad litora torquent per conubia nostra, per inceptos himenaeos. Suspendisse ut felis a eros egestas ultricies et quis mi. Vivamus ut risus massa. Donec nec ornare erat. Aliquam ornare, lorem a rhoncus aliquam, tellus diam tincidunt tellus, a mattis nunc ante ac arcu. Curabitur nec metus id risus commodo ullamcorper eu ut tortor."
+);
+SELECT "overflow_test:";
+SELECT rowid, length(value), TYPEOF(value) FROM overflow_test;
+
+SELECT "overflow_test_recover:";
+CREATE VIRTUAL TABLE temp.overflow_test_recover USING recover(
+  overflow_test,
+  value TEXT
+);
+SELECT rowid, length(value), TYPEOF(value) FROM overflow_test_recover;
+
+.exit
+
+-- FLOAT test.
+
+DROP TABLE IF EXISTS float_test;
+CREATE TABLE float_test (value FLOAT);
+INSERT INTO float_test VALUES (0);
+INSERT INTO float_test VALUES (1);
+INSERT INTO float_test VALUES (2);
+INSERT INTO float_test VALUES (1.0);
+INSERT INTO float_test VALUES (2.3);
+SELECT "float_test rows:";
+SELECT rowid, value, TYPEOF(value) FROM float_test;
+
+SELECT "float_test_recover rows:";
+CREATE VIRTUAL TABLE temp.float_test_recover USING recover(
+  float_test,
+  value FLOAT
+);
+SELECT rowid, value, TYPEOF(value) FROM float_test_recover;
+
+.schema
+
+.exit
+
+-- Basic type test.
+DROP TABLE IF EXISTS baseline;
+CREATE TABLE baseline (rowtype TEXT, value);
+INSERT INTO baseline VALUES ("NULL", NULL);
+INSERT INTO baseline VALUES ("INTEGER", 1);
+INSERT INTO baseline VALUES ("TEXT", "text");
+INSERT INTO baseline VALUES ("BLOB", CAST ("blob" AS BLOB));
+INSERT INTO baseline VALUES ("FLOAT", 10.01);
+SELECT "baseline rows:";
+SELECT rowid, rowtype, value, TYPEOF(value) FROM baseline;
+
+SELECT "null rows:";
+DROP TABLE IF EXISTS temp.baseline_recover;
+CREATE VIRTUAL TABLE temp.baseline_recover USING recover(
+  baseline,
+  rowtype TEXT,
+  value NULL
+);
+SELECT rowid, rowtype, value, TYPEOF(value) FROM baseline_recover;
+
+SELECT "integer rows:";
+DROP TABLE IF EXISTS temp.baseline_recover;
+CREATE VIRTUAL TABLE temp.baseline_recover USING recover(
+  baseline,
+  rowtype TEXT,
+  value INTEGER
+);
+SELECT rowid, rowtype, value, TYPEOF(value) FROM baseline_recover;
+
+SELECT "text rows:";
+DROP TABLE IF EXISTS temp.baseline_recover;
+CREATE VIRTUAL TABLE temp.baseline_recover USING recover(
+  baseline,
+  rowtype TEXT,
+  value TEXT
+);
+SELECT rowid, rowtype, value, TYPEOF(value) FROM baseline_recover;
+
+SELECT "blob rows:";
+DROP TABLE IF EXISTS temp.baseline_recover;
+CREATE VIRTUAL TABLE temp.baseline_recover USING recover(
+  baseline,
+  rowtype TEXT,
+  value BLOB
+);
+SELECT rowid, rowtype, value, TYPEOF(value) FROM baseline_recover;
+
+SELECT "float rows:";
+DROP TABLE IF EXISTS temp.baseline_recover;
+CREATE VIRTUAL TABLE temp.baseline_recover USING recover(
+  baseline,
+  rowtype TEXT,
+  value FLOAT
+);
+SELECT rowid, rowtype, value, TYPEOF(value) FROM baseline_recover;
+
+SELECT "all rows:";
+DROP TABLE IF EXISTS temp.baseline_recover;
+CREATE VIRTUAL TABLE temp.baseline_recover USING recover(
+  baseline,
+  rowtype TEXT,
+  value ANY
+);
+SELECT rowid, rowtype, value, TYPEOF(value) FROM baseline_recover;
+
+SELECT "all rows:";
+DROP TABLE IF EXISTS temp.baseline_recover;
+CREATE VIRTUAL TABLE temp.baseline_recover USING recover(
+  baseline,
+  rowtype TEXT,
+  value
+);
+SELECT rowid, rowtype, value, TYPEOF(value) FROM baseline_recover;
+
+SELECT "text not null rows:";
+DROP TABLE IF EXISTS temp.baseline_recover;
+CREATE VIRTUAL TABLE temp.baseline_recover USING recover(
+  baseline,
+  rowtype TEXT,
+  value TEXT NOT NULL
+);
+SELECT rowid, rowtype, value, TYPEOF(value) FROM baseline_recover;
+
+.exit
+
+-- All of 'leaf' fits in a single leaf node.
+DROP TABLE IF EXISTS temp.leaf_recover;
+CREATE VIRTUAL TABLE temp.leaf_recover USING recover(
+  leaf,
+  file TEXT,
+  lineno INT,
+  line TEXT
+);
+SELECT "leaf_recover";
+SELECT rowid, * from leaf_recover;
+SELECT "leaf_recover lineno";
+SELECT lineno from leaf_recover;
+
+-- The tree for 'interior' has root as an interior node.
+DROP TABLE IF EXISTS temp.interior_recover;
+CREATE VIRTUAL TABLE temp.interior_recover USING recover(
+  interior,
+  file TEXT,
+  lineno INT,
+  line TEXT
+);
+.schema interior_recover
+SELECT "interior count: ", COUNT(*) FROM interior;
+SELECT "interior_recover count: ", COUNT(*) FROM interior_recover;
+
+-- The tree for 'interior2' has multiple levels of interior nodes.
+DROP TABLE IF EXISTS temp.interior2_recover;
+CREATE VIRTUAL TABLE temp.interior2_recover USING recover(
+  interior2,
+  file TEXT,
+  lineno INT,
+  line TEXT
+);
+.schema interior2_recover
+SELECT "interior2 count: ", COUNT(*) FROM interior2;
+SELECT "interior2_recover count: ", COUNT(*) FROM interior2_recover;
+
+SELECT "interior2_check";
+DROP TABLE IF EXISTS temp.interior2_check;
+CREATE TABLE temp.interior2_check (r INT);
+INSERT INTO interior2_check (r) SELECT rowid FROM interior2;
+SELECT r, COUNT(*) AS c FROM interior2_check GROUP BY r HAVING c > 1;
+SELECT "interior2_recover_check";
+DROP TABLE IF EXISTS temp.interior2_recover_check;
+CREATE TABLE temp.interior2_recover_check (r INT);
+INSERT INTO interior2_recover_check (r) SELECT rowid FROM interior2_recover;
+SELECT r, COUNT(*) AS c FROM interior2_recover_check GROUP BY r HAVING c > 1;
+
+.exit