sqlite/ext/fts5/test/fts5secure8.test

# 2023 Nov 23
#
# 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]
ifcapable !fts5 { finish_test ; return }
set ::testprefix fts5secure8

proc sql_repeat {txt n} {
  string repeat $txt $n
}
db func repeat sql_repeat

do_execsql_test 1.0 {
  CREATE VIRTUAL TABLE ft USING fts5(x);

  INSERT INTO ft(ft, rank) VALUES('pgsz', 64);

  INSERT INTO ft(rowid, x) VALUES(100, 'hello world');
  INSERT INTO ft(rowid, x) VALUES(200, 'one day');

  BEGIN;
    INSERT INTO ft(rowid, x) VALUES(45, 'one two three');
    UPDATE ft SET x = repeat('hello world ', 500) WHERE rowid=100;
  COMMIT
}

do_execsql_test 1.1 {
  INSERT INTO ft(ft, rank) VALUES('secure-delete', 1);
  DELETE FROM ft WHERE rowid=100;
}

do_execsql_test 1.2 {
  PRAGMA integrity_check;
} {ok}

do_execsql_test 2.0 {
  CREATE VIRTUAL TABLE xyz USING fts5 (
  	name,
        content=''
  );

  INSERT INTO xyz(xyz, rank) VALUES('secure-delete', 1);
  INSERT INTO xyz (rowid, name) VALUES(1, 'A');
  INSERT INTO xyz (rowid, name) VALUES(2, 'A');
  INSERT INTO xyz(xyz, rowid, name) VALUES('delete', 2, 'A');
}

do_execsql_test 2.1 {
  pragma quick_check;
} {ok}

do_catchsql_test 2.2 {
  INSERT INTO xyz(xyz, rank) VALUES('secure-delete', 'hello world');
} {1 {SQL logic error}}

#-------------------------------------------------------------------------
#
reset_db
do_execsql_test 3.0 {
  CREATE VIRTUAL TABLE t1 USING fts5(c, detail='none');
  INSERT INTO t1(t1, rank) VALUES('secure-delete', 1);
  INSERT INTO t1(rowid, c) VALUES(1, 'a');
}

do_execsql_test 3.1 {
  BEGIN;
    DELETE FROM t1 WHERE rowid = 1;
    INSERT INTO t1(rowid, c) VALUES(1, 'a');
}

do_execsql_test 3.2 {
    INSERT INTO t1(rowid, c) VALUES(2, 'a');
  COMMIT;
}

do_execsql_test 3.3 {
  SELECT rowid FROM t1 ORDER BY rowid;
} {1 2}

# Expected {1 2}.  Observed {1}.
do_execsql_test 3.4 {
  SELECT rowid FROM t1 WHERE t1 MATCH 'a' ORDER BY rowid;
} {1 2}

# Expected {ok}.  Observed: SQLITE_CORRUPT_VTAB, "checksum mismatch".
do_execsql_test 3.5 {
  INSERT INTO t1(t1) VALUES('integrity-check');
} {}

# control: the same script without secure-delete is correct
do_execsql_test 3.6 {
  CREATE VIRTUAL TABLE t1b USING fts5(c, detail='none');
  INSERT INTO t1b(rowid, c) VALUES(1, 'a');
  INSERT OR REPLACE INTO t1b(rowid, c) VALUES(1, 'a'), (2, 'a');
  SELECT rowid FROM t1b WHERE t1b MATCH 'a' ORDER BY rowid;
} {1 2}

# control: the same script with detail=full is correct
do_execsql_test 3.7 {
  CREATE VIRTUAL TABLE t1c USING fts5(c, detail='full');
  INSERT INTO t1c(t1c, rank) VALUES('secure-delete', 1);
  INSERT INTO t1c(rowid, c) VALUES(1, 'a');
  INSERT OR REPLACE INTO t1c(rowid, c) VALUES(1, 'a'), (2, 'a');
  SELECT rowid FROM t1c WHERE t1c MATCH 'a' ORDER BY rowid;
} {1 2}




finish_test