ext/fts5/test/fts5simple.test

# 2015 September 05
#
# 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.
#
#*************************************************************************
#

source [file join [file dirname [info script]] fts5_common.tcl]
set testprefix fts5simple

# If SQLITE_ENABLE_FTS5 is defined, omit this file.
ifcapable !fts5 {
  finish_test
  return
}

if 1 {
#-------------------------------------------------------------------------
#
set doc "x x [string repeat {y } 50]z z"
do_execsql_test 1.0 {
  CREATE VIRTUAL TABLE t1 USING fts5(x);
  INSERT INTO t1(t1, rank) VALUES('pgsz', 32);
  BEGIN;
    INSERT INTO t1 VALUES($doc);
  COMMIT;
}

do_execsql_test 1.1 {
  INSERT INTO t1(t1) VALUES('integrity-check');
}

#-------------------------------------------------------------------------
#
reset_db
do_execsql_test 2.0 {
  CREATE VIRTUAL TABLE t1 USING fts5(x);
  INSERT INTO t1(t1, rank) VALUES('pgsz', 32);
  INSERT INTO t1 VALUES('a b c');
  INSERT INTO t1 VALUES('d e f');
  INSERT INTO t1(t1) VALUES('optimize');
}

do_execsql_test 2.1 {
  INSERT INTO t1(t1) VALUES('integrity-check');
} {}


#-------------------------------------------------------------------------
#
reset_db
do_execsql_test 3.0 {
  CREATE VIRTUAL TABLE t1 USING fts5(x, prefix='1,2');
  INSERT INTO t1(t1, rank) VALUES('pgsz', 32);
  BEGIN;
  INSERT INTO t1 VALUES('one');
  SELECT * FROM t1 WHERE t1 MATCH 'o*';
} {one}

do_execsql_test 3.1 {
  INSERT INTO t1(t1) VALUES('integrity-check');
} {}

#-------------------------------------------------------------------------
reset_db
do_execsql_test 4.1 {
  CREATE VIRTUAL TABLE t11 USING fts5(content);
  INSERT INTO t11(t11, rank) VALUES('pgsz', 32);
  INSERT INTO t11 VALUES('another');
  INSERT INTO t11 VALUES('string');
  INSERT INTO t11 VALUES('of');
  INSERT INTO t11 VALUES('text');
}
do_test 4.2 {
  execsql { INSERT INTO t11(t11) VALUES('optimize') }
} {}
do_execsql_test 4.3 {
  INSERT INTO t11(t11) VALUES('integrity-check');
} {}

#db eval { SELECT fts5_decode(rowid, block) as x FROM t11_data } { puts $x }

#-------------------------------------------------------------------------
reset_db
set doc [string repeat "x y " 5]
do_execsql_test 5.1 {
  CREATE VIRTUAL TABLE yy USING fts5(content);
  INSERT INTO yy(yy, rank) VALUES('pgsz', 32);
  BEGIN;
    INSERT INTO yy VALUES($doc);
    INSERT INTO yy VALUES($doc);
    INSERT INTO yy VALUES($doc);
    INSERT INTO yy VALUES($doc);
    INSERT INTO yy VALUES($doc);
    INSERT INTO yy VALUES($doc);
    INSERT INTO yy VALUES($doc);
    INSERT INTO yy VALUES($doc);
  COMMIT;
}

do_execsql_test 5.2 {
  SELECT rowid FROM yy WHERE yy MATCH 'y' ORDER BY rowid ASC
} {1 2 3 4 5 6 7 8}

do_execsql_test 5.3 {
  SELECT rowid FROM yy WHERE yy MATCH 'y' ORDER BY rowid DESC
} {8 7 6 5 4 3 2 1}

#db eval { SELECT fts5_decode(rowid, block) as x FROM yy_data } { puts $x }

#-------------------------------------------------------------------------
reset_db
do_execsql_test 5.1 {
  CREATE VIRTUAL TABLE tt USING fts5(content);
  INSERT INTO tt(tt, rank) VALUES('pgsz', 32);
  INSERT INTO tt VALUES('aa');
}

do_execsql_test 5.2 {
  SELECT rowid FROM tt WHERE tt MATCH 'a*';
} {1}

do_execsql_test 5.3 {
  DELETE FROM tt;
  BEGIN;
    INSERT INTO tt VALUES('aa');
    INSERT INTO tt VALUES('ab');
  COMMIT;
} {}

do_execsql_test 5.4 {
  SELECT rowid FROM tt WHERE tt MATCH 'a*';
} {1 2}

}

do_execsql_test 5.5 {
  DELETE FROM tt;
  BEGIN;
    INSERT INTO tt VALUES('aa');
    INSERT INTO tt VALUES('ab');
    INSERT INTO tt VALUES('aa');
    INSERT INTO tt VALUES('ab');
    INSERT INTO tt VALUES('aa');
    INSERT INTO tt VALUES('ab');
    INSERT INTO tt VALUES('aa');
    INSERT INTO tt VALUES('ab');
  COMMIT;
  SELECT rowid FROM tt WHERE tt MATCH 'a*';
} {1 2 3 4 5 6 7 8}

do_execsql_test 5.6 {
  INSERT INTO tt(tt) VALUES('integrity-check');
}

reset_db
do_execsql_test 5.7 {
  CREATE VIRTUAL TABLE tt USING fts5(content);
  INSERT INTO tt(tt, rank) VALUES('pgsz', 32);
  INSERT INTO tt VALUES('aa ab ac ad ae af');
}

do_execsql_test 5.8 {
  SELECT rowid FROM tt WHERE tt MATCH 'a*';
} {1}

#-------------------------------------------------------------------------

reset_db
do_execsql_test 6.1 {
  CREATE VIRTUAL TABLE xyz USING fts5(x, y, z);
  INSERT INTO xyz VALUES('x', 'y', 'z');
}

do_catchsql_test 6.2 { 
  SELECT * FROM xyz WHERE xyz MATCH '' 
} {1 {fts5: syntax error near ""}}
do_catchsql_test 6.3 { 
  SELECT * FROM xyz WHERE xyz MATCH NULL 
} {1 {fts5: syntax error near ""}}


finish_test