| # 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 {recover table must be in temp database}} |
| |
| # 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 {unable to find backing table}} |
| |
| # 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 {unable to find backing table}} |
| |
| # 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 {unable to find backing table}} |
| |
| # Database only fails. |
| do_test recover-syntax-2.3 { |
| db eval {DROP TABLE IF EXISTS temp.syntax;} |
| catchsql { |
| CREATE VIRTUAL TABLE temp.syntax USING recover( |
| main., |
| t TEXT |
| ); |
| } |
| } {1 {ill-formed table specifier}} |
| |
| # Table only fails. |
| do_test recover-syntax-2.4 { |
| db eval {DROP TABLE IF EXISTS temp.syntax;} |
| catchsql { |
| CREATE VIRTUAL TABLE temp.syntax USING recover( |
| .backing, |
| t TEXT |
| ); |
| } |
| } {1 {ill-formed table specifier}} |
| |
| # 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 {unable to parse column 0}} |
| |
| # 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 {unable to parse column 0}} |
| |
| # 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 {unable to parse column 0}} |
| |
| # 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 {unable to parse column 0}} |
| |
| # 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 {unable to parse column 0}} |
| |
| # 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 {unable to parse column 0}} |
| |
| finish_test |