src/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 {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