# 2006 June 10

#

# 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.

#

# $Id: vtab5.test,v 1.8 2008/07/12 14:52:21 drh Exp $

set testdir [file dirname $argv0]

source $testdir/tester.tcl

ifcapable !vtab {

  finish_test

  return

}

# The following tests - vtab5-1.* - ensure that an INSERT, DELETE or UPDATE

# statement can be executed immediately after a CREATE or schema reload. The

# point here is testing that the parser always calls xConnect() before the

# schema of a virtual table is used.

#

register_echo_module [sqlite3_connection_pointer db]

do_test vtab5-1.1 {

  execsql {

    CREATE TABLE treal(a VARCHAR(16), b INTEGER, c FLOAT);

    INSERT INTO treal VALUES('a', 'b', 'c');

    CREATE VIRTUAL TABLE techo USING echo(treal);

  }

} {}

do_test vtab5.1.2 {

  execsql {

    SELECT * FROM techo;

  }

} {a b c}

do_test vtab5.1.3 {

  db close

  sqlite3 db test.db

  register_echo_module [sqlite3_connection_pointer db]

  execsql {

    INSERT INTO techo VALUES('c', 'd', 'e');

    SELECT * FROM techo;

  }

} {a b c c d e}

do_test vtab5.1.4 {

  db close

  sqlite3 db test.db

  register_echo_module [sqlite3_connection_pointer db]

  execsql {

    UPDATE techo SET a = 10;

    SELECT * FROM techo;

  }

} {10 b c 10 d e}

do_test vtab5.1.5 {

  db close

  sqlite3 db test.db

  register_echo_module [sqlite3_connection_pointer db]

  execsql {

    DELETE FROM techo WHERE b > 'c';

    SELECT * FROM techo;

  }

} {10 b c}

do_test vtab5.1.X {

  execsql {

    DROP TABLE techo;

    DROP TABLE treal;

  }

} {}

# The following tests - vtab5-2.* - ensure that collation sequences

# assigned to virtual table columns via the "CREATE TABLE" statement 

# passed to sqlite3_declare_vtab() are used correctly.

#

do_test vtab5.2.1 {

  execsql {

    CREATE TABLE strings(str COLLATE NOCASE);

    INSERT INTO strings VALUES('abc1');

    INSERT INTO strings VALUES('Abc3');

    INSERT INTO strings VALUES('ABc2');

    INSERT INTO strings VALUES('aBc4');

    SELECT str FROM strings ORDER BY 1;

  }

} {abc1 ABc2 Abc3 aBc4}

do_test vtab5.2.2 {

  execsql {

    CREATE VIRTUAL TABLE echo_strings USING echo(strings);

    SELECT str FROM echo_strings ORDER BY 1;

  }

} {abc1 ABc2 Abc3 aBc4}

do_test vtab5.2.3 {

  execsql {

    SELECT str||'' FROM echo_strings ORDER BY 1;

  }

} {ABc2 Abc3 aBc4 abc1}

# Test that it is impossible to create a triggger on a virtual table.

#

ifcapable trigger {

  do_test vtab5.3.1 {

    catchsql {

      CREATE TRIGGER trig INSTEAD OF INSERT ON echo_strings BEGIN

        SELECT 1, 2, 3;

      END;

    }

  } {1 {cannot create triggers on virtual tables}}

  do_test vtab5.3.2 {

    catchsql {

      CREATE TRIGGER trig AFTER INSERT ON echo_strings BEGIN

        SELECT 1, 2, 3;

      END;

    }

  } {1 {cannot create triggers on virtual tables}}

  do_test vtab5.3.2 {

    catchsql {

      CREATE TRIGGER trig BEFORE INSERT ON echo_strings BEGIN

        SELECT 1, 2, 3;

      END;

    }

  } {1 {cannot create triggers on virtual tables}}

}

# Test that it is impossible to create an index on a virtual table.

#

do_test vtab5.4.1 {

  catchsql {

    CREATE INDEX echo_strings_i ON echo_strings(str);

  }

} {1 {virtual tables may not be indexed}}

# Test that it is impossible to add a column to a virtual table.

#

do_test vtab5.4.2 {

  catchsql {

    ALTER TABLE echo_strings ADD COLUMN col2;

  }

} {1 {virtual tables may not be altered}}

# Test that it is impossible to rename a virtual table.

# UPDATE: It is now possible.

#

# do_test vtab5.4.3 {

#   catchsql {

#     ALTER TABLE echo_strings RENAME TO echo_strings2;

#   }

# } {1 {virtual tables may not be altered}}

finish_test