vtab1.test

sqlite嵌入式数据库源码

do_test vtab1-4.2 {
  set echo_module
} [list xBestIndex {SELECT rowid, * FROM 'treal' ORDER BY b ASC} \
        xFilter    {SELECT rowid, * FROM 'treal' ORDER BY b ASC} ]
do_test vtab1-4.3 {
  set echo_module ""
  cksort {
    SELECT b FROM t1 ORDER BY b DESC;
  }
} {5 2 nosort}
do_test vtab1-4.4 {
  set echo_module
} [list xBestIndex {SELECT rowid, * FROM 'treal' ORDER BY b DESC} \
        xFilter    {SELECT rowid, * FROM 'treal' ORDER BY b DESC} ]
do_test vtab1-4.3 {
  set echo_module ""
  cksort {
    SELECT b FROM t1 ORDER BY b||'';
  }
} {2 5 sort}
do_test vtab1-4.4 {
  set echo_module
} [list xBestIndex {SELECT rowid, * FROM 'treal'} \
        xFilter    {SELECT rowid, * FROM 'treal'} ]

execsql {
  DROP TABLE t1;
  DROP TABLE treal;
}

#----------------------------------------------------------------------
# Test cases vtab1-5 test SELECT queries that include joins on virtual 
# tables.

proc filter {log} {
  set out [list]
  for {set ii 0} {$ii < [llength $log]} {incr ii} {
    if {[lindex $log $ii] eq "xFilter"} {
      lappend out xFilter
      lappend out [lindex $log [expr $ii+1]]
    }
  }
  return $out
}

do_test vtab1-5-1 {
  execsql { 
    CREATE TABLE t1(a, b, c);
    CREATE TABLE t2(d, e, f);
    INSERT INTO t1 VALUES(1, 'red', 'green');
    INSERT INTO t1 VALUES(2, 'blue', 'black');
    INSERT INTO t2 VALUES(1, 'spades', 'clubs');
    INSERT INTO t2 VALUES(2, 'hearts', 'diamonds');
    CREATE VIRTUAL TABLE et1 USING echo(t1);
    CREATE VIRTUAL TABLE et2 USING echo(t2);
  }
} {}

do_test vtab1-5-2 {
  set echo_module ""
  execsql {
    SELECT * FROM et1, et2;
  }
} [list \
  1 red green 1 spades clubs     \
  1 red green 2 hearts diamonds  \
  2 blue black 1 spades clubs    \
  2 blue black 2 hearts diamonds \
]
do_test vtab1-5-3 {
  filter $echo_module
} [list \
  xFilter {SELECT rowid, * FROM 't1'} \
  xFilter {SELECT rowid, * FROM 't2'} \
  xFilter {SELECT rowid, * FROM 't2'} \
]
do_test vtab1-5-4 {
  set echo_module ""
  execsql {
    SELECT * FROM et1, et2 WHERE et2.d = 2;
  }
} [list \
  1 red green 2 hearts diamonds  \
  2 blue black 2 hearts diamonds \
]
do_test vtab1-5-5 {
  filter $echo_module
} [list \
  xFilter {SELECT rowid, * FROM 't1'} \
  xFilter {SELECT rowid, * FROM 't2'} \
  xFilter {SELECT rowid, * FROM 't2'} \
]
do_test vtab1-5-6 {
  execsql {
    CREATE INDEX i1 ON t2(d);
  }

  db close
  sqlite3 db test.db
  register_echo_module [sqlite3_connection_pointer db]

  set echo_module ""
  execsql {
    SELECT * FROM et1, et2 WHERE et2.d = 2;
  }
} [list \
  1 red green 2 hearts diamonds  \
  2 blue black 2 hearts diamonds \
]
do_test vtab1-5-7 {
  filter $echo_module
} [list \
  xFilter {SELECT rowid, * FROM 't2' WHERE d = ?} \
  xFilter {SELECT rowid, * FROM 't1'}             \
]

execsql {
  DROP TABLE t1;
  DROP TABLE t2;
  DROP TABLE et1;
  DROP TABLE et2;
}

#----------------------------------------------------------------------
# Test cases vtab1-6 test INSERT, UPDATE and DELETE operations 
# on virtual tables.
do_test vtab1-6-1 {
  execsql { SELECT sql FROM sqlite_master }
} {}
do_test vtab1-6-2 {
  execsql {
    CREATE TABLE treal(a PRIMARY KEY, b, c);
    CREATE VIRTUAL TABLE techo USING echo(treal);
    SELECT name FROM sqlite_master WHERE type = 'table';
  }
} {treal techo}
do_test vtab1-6-3 {
  execsql {
    INSERT INTO techo VALUES(1, 2, 3);
    SELECT * FROM techo;
  }
} {1 2 3}
do_test vtab1-6-4 {
  execsql {
    UPDATE techo SET a = 5;
    SELECT * FROM techo;
  }
} {5 2 3}

do_test vtab1-6-5 {
 execsql {
   UPDATE techo set a = a||b||c;
   SELECT * FROM techo;
 }
} {523 2 3}

do_test vtab1-6-6 {
  execsql {
    UPDATE techo set rowid = 10;
    SELECT rowid FROM techo;
  }
} {10}

do_test vtab1-6-7 {
  execsql {
    DELETE FROM techo;
    SELECT * FROM techo;
  }
} {}


file delete -force test2.db
file delete -force test2.db-journal
sqlite3 db2 test2.db
execsql {
  CREATE TABLE techo(a PRIMARY KEY, b, c);
} db2
proc check_echo_table {tn} {
  set ::data1 [execsql {SELECT rowid, * FROM techo}]
  set ::data2 [execsql {SELECT rowid, * FROM techo} db2]
  do_test $tn {
    string equal $::data1 $::data2
  } 1
}
set tn 0
foreach stmt [list \
  {INSERT INTO techo VALUES('abc', 'def', 'ghi')}                        \
  {INSERT INTO techo SELECT a||'.'||rowid, b, c FROM techo}              \
  {INSERT INTO techo SELECT a||'x'||rowid, b, c FROM techo}              \
  {INSERT INTO techo SELECT a||'y'||rowid, b, c FROM techo}              \
  {DELETE FROM techo WHERE (oid % 3) = 0}                                \
  {UPDATE techo set rowid = 100 WHERE rowid = 1}                         \
  {INSERT INTO techo(a, b) VALUES('hello', 'world')}                     \
  {DELETE FROM techo}                                                    \
] {
  execsql $stmt
  execsql $stmt db2
  check_echo_table vtab1-6.8.[incr tn]
}

db2 close



#----------------------------------------------------------------------
# Test cases vtab1-7 tests that the value returned by 
# sqlite3_last_insert_rowid() is set correctly when rows are inserted
# into virtual tables.
do_test vtab1.7-1 {
  execsql {
    CREATE TABLE real_abc(a PRIMARY KEY, b, c);
    CREATE VIRTUAL TABLE echo_abc USING echo(real_abc);
  }
} {}
do_test vtab1.7-2 {
  execsql {
    INSERT INTO echo_abc VALUES(1, 2, 3);
    SELECT last_insert_rowid();
  }
} {1}
do_test vtab1.7-3 {
  execsql {
    INSERT INTO echo_abc(rowid) VALUES(31427);
    SELECT last_insert_rowid();
  }
} {31427}
do_test vtab1.7-4 {
  execsql {
    INSERT INTO echo_abc SELECT a||'.v2', b, c FROM echo_abc;
    SELECT last_insert_rowid();
  }
} {31429}
do_test vtab1.7-5 {
  execsql {
    SELECT rowid, a, b, c FROM echo_abc
  }
} [list 1     1    2  3  \
        31427 {}   {} {} \
        31428 1.v2 2  3  \
        31429 {}  {} {}  \
]

# Now test that DELETE and UPDATE operations do not modify the value.
do_test vtab1.7-6 {
  execsql {
    UPDATE echo_abc SET c = 5 WHERE b = 2;
    SELECT last_insert_rowid();
  }
} {31429}
do_test vtab1.7-7 {
  execsql {
    UPDATE echo_abc SET rowid = 5 WHERE rowid = 1;
    SELECT last_insert_rowid();
  }
} {31429}
do_test vtab1.7-8 {
  execsql {
    DELETE FROM echo_abc WHERE b = 2;
    SELECT last_insert_rowid();
  }
} {31429}
do_test vtab1.7-9 {
  execsql {
    SELECT rowid, a, b, c FROM echo_abc
  }
} [list 31427 {} {} {} \
        31429 {} {} {} \
]
do_test vtab1.7-10 {
  execsql {
    DELETE FROM echo_abc WHERE b = 2;
    SELECT last_insert_rowid();
  }
} {31429}
do_test vtab1.7-11 {
  execsql {
    SELECT rowid, a, b, c FROM real_abc
  }
} [list 31427 {} {} {} \
        31429 {} {} {} \
]
do_test vtab1.7-12 {
  execsql {
    DELETE FROM echo_abc;
    SELECT last_insert_rowid();
  }
} {31429}
do_test vtab1.7-13 {
  execsql {
    SELECT rowid, a, b, c FROM real_abc
  }
} {}

do_test vtab1.8-1 {
  set echo_module ""
  execsql {
    ATTACH 'test2.db' AS aux;
    CREATE VIRTUAL TABLE aux.e2 USING echo(real_abc);
  }
  set echo_module
} [list xCreate echo aux e2 real_abc   \
        xSync   echo(real_abc)         \
        xCommit echo(real_abc)         \
]
do_test vtab1.8-2 {
  execsql {
    DROP TABLE aux.e2;
    DROP TABLE treal;
    DROP TABLE techo;
    DROP TABLE echo_abc;
    DROP TABLE real_abc;
  }
} {}

do_test vtab1.9-1 {
  set echo_module ""
  execsql {
    CREATE TABLE r(a, b, c);
    CREATE VIRTUAL TABLE e USING echo(r, e_log);
    SELECT name FROM sqlite_master;
  }
} {r e e_log}
do_test vtab1.9-2 {
  execsql {
    DROP TABLE e;
    SELECT name FROM sqlite_master;
  }
} {r}

do_test vtab1.9-3 {
  set echo_module ""
  execsql {
    CREATE VIRTUAL TABLE e USING echo(r, e_log, virtual 1 2 3 varchar(32));
  }
  set echo_module
} [list                                                        \
  xCreate echo main e r e_log {virtual 1 2 3 varchar(32)}      \
  xSync echo(r)                                                \
  xCommit echo(r)                                              \
]

do_test vtab1.10-1 {
  execsql {
    CREATE TABLE del(d);
    CREATE VIRTUAL TABLE e2 USING echo(del);
  }
  db close
  sqlite3 db test.db
  register_echo_module [sqlite3_connection_pointer db]
  execsql {
    DROP TABLE del;
  }
  catchsql {
    SELECT * FROM e2;
  }
} {1 {vtable constructor failed: e2}}
do_test vtab1.10-2 {
  set rc [catch {
    set ptr [sqlite3_connection_pointer db]
    sqlite3_declare_vtab $ptr {CREATE TABLE abc(a, b, c)}
  } msg]
  list $rc $msg
} {1 {library routine called out of sequence}}
do_test vtab1.10-3 {
  set ::echo_module_begin_fail r
  catchsql {
    INSERT INTO e VALUES(1, 2, 3);
  }
} {1 {SQL logic error or missing database}}
do_test vtab1.10-4 {
  catch {execsql {
    EXPLAIN SELECT * FROM e WHERE rowid = 2;
    EXPLAIN QUERY PLAN SELECT * FROM e WHERE rowid = 2 ORDER BY rowid;
  }}
} {0}

do_test vtab1.10-5 {
  set echo_module ""
  execsql {
    SELECT * FROM e WHERE rowid||'' MATCH 'pattern';
  }
  set echo_module
} [list \
  xBestIndex {SELECT rowid, * FROM 'r'} \
  xFilter {SELECT rowid, * FROM 'r'}    \
]
proc match_func {args} {return ""}
do_test vtab1.10-6 {
  set echo_module ""
  db function match match_func
  execsql {
    SELECT * FROM e WHERE match('pattern', rowid, 'pattern2');
  }
  set echo_module
} [list \
  xBestIndex {SELECT rowid, * FROM 'r'} \
  xFilter {SELECT rowid, * FROM 'r'}    \
]


# Testing the xFindFunction interface
#
do_test vtab1.11-1 {
  execsql {
    INSERT INTO r(a,b,c) VALUES(1,'?',99);
    INSERT INTO r(a,b,c) VALUES(2,3,99);
    SELECT a GLOB b FROM e
  }
} {1 0}
proc ::echo_glob_overload {a b} {
 return [list $b $a]
}
do_test vtab1.11-2 {
  execsql {
    SELECT a like 'b' FROM e
  }
} {0 0}
do_test vtab1.11-3 {
  execsql {
    SELECT a glob '2' FROM e
  }
} {{1 2} {2 2}}
do_test vtab1.11-4 {
  execsql {
    SELECT  glob('2',a) FROM e
  }
} {0 1}
do_test vtab1.11-5 {
  execsql {
    SELECT  glob(a,'2') FROM e
  }
} {{2 1} {2 2}}

finish_test