sqlite/ext/fts5/test/fts5secure4.test

# 2023 April 14
#
# 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]
return_if_no_fts5
set ::testprefix fts5secure4

#-------------------------------------------------------------------------
# Test using the 'delete' command to attempt to delete a token that 
# is not present in the index in secure-delete mode.
#
do_execsql_test 1.0 {
  CREATE VIRTUAL TABLE t1 USING fts5(a, b, content=x1);

  CREATE TABLE x1(rowid INTEGER PRIMARY KEY, a, b);
  INSERT INTO x1 VALUES
    (1, 'hello world', 'today xyz'),
    (2, 'not the day', 'crunch crumble and chomp'),
    (3, 'one', 'two');
  INSERT INTO t1(t1) VALUES('rebuild');
}

do_execsql_test 1.1 {
  INSERT INTO t1(t1, rank) VALUES('secure-delete', 1);
}

do_execsql_test 1.2 {
  INSERT INTO t1(t1, rowid, a, b) VALUES('delete', 4, 'nosuchtoken', '');
}

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

do_execsql_test 1.4 {
  INSERT INTO t1(t1, rowid, a, b) VALUES('delete', 1, 'crunch', '');
}

do_execsql_test 1.5 {
  INSERT INTO t1(t1, rowid, a, b) VALUES('delete', 3, 'crunch', '');
}

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

do_execsql_test 1.7 {
CREATE VIRTUAL TABLE y1 USING fts5(xx, prefix='1,2');
INSERT INTO y1(y1, rank) VALUES('pgsz', 64);
INSERT INTO y1(y1, rank) VALUES('secure-delete', 1);
}
do_execsql_test 1.8 {
  BEGIN;
  INSERT INTO y1(rowid, xx) VALUES(1, 'abc def');
  INSERT INTO y1(rowid, xx) VALUES(2, 'reallyreallylongtoken');
  COMMIT;
}
do_execsql_test 1.9 {
  DELETE FROM y1 WHERE rowid=1;
  INSERT INTO y1(y1) VALUES('integrity-check');
}

do_execsql_test 1.10 {
  CREATE VIRTUAL TABLE w1 USING fts5(ww, content="");
  INSERT INTO w1(rowid, ww) VALUES(123, '');
}
do_catchsql_test 1.11 {
  INSERT INTO w1(w1, rowid, ww) VALUES('delete', 123, 'xyz');
} {1 {database disk image is malformed}}
do_catchsql_test 1.12 {
  DROP TABLE w1;
  CREATE VIRTUAL TABLE w1 USING fts5(ww, content="");
  INSERT INTO w1(rowid, ww) VALUES(123, '');
  DELETE FROM w1_data WHERE id>10;
  INSERT INTO w1(w1, rowid, ww) VALUES('delete', 123, 'xyz');
} {1 {database disk image is malformed}}

#-------------------------------------------------------------------------
# Test using secure-delete with detail=none or detail=col.
#
foreach {tn d} {1 full 2 none 3 column} {
  reset_db
  do_execsql_test 2.$tn.1 "
    CREATE VIRTUAL TABLE x1 USING fts5(xx, yy, zz, detail=$d, prefix='10,20');
    INSERT INTO x1(x1, rank) VALUES('pgsz', 64);
    INSERT INTO x1(x1, rank) VALUES('secure-delete', 1);
  "

  do_execsql_test 2.$tn.2 {
    BEGIN;
      INSERT INTO x1(xx, yy, zz) VALUES('a b c', 'd e f', 'a b c');
      INSERT INTO x1(xx, yy, zz) VALUES('a b c', 'd e f', 'a b c');
      INSERT INTO x1(xx, yy, zz) VALUES('a b c', 'd e f', 'a b c');
      INSERT INTO x1(xx, yy, zz) VALUES('a b c', 'd e f', 'a b c');
      INSERT INTO x1(xx, yy, zz) VALUES('a b c', 'd e f', 'a b c');
    COMMIT;
    INSERT INTO x1(x1) VALUES('integrity-check');
  }

  do_execsql_test 2.$tn.3 {
    DELETE FROM x1 WHERE rowid IN (2, 4, 6);
    INSERT INTO x1(x1) VALUES('integrity-check');
  }

  do_execsql_test 2.$tn.4 {
    DELETE FROM x1 WHERE rowid IN (1, 3, 5);
    INSERT INTO x1(x1) VALUES('integrity-check');
  }

  do_execsql_test 2.$tn.5 {
    WITH s(i) AS (
      SELECT 1 UNION ALL SELECT i+1 FROM s WHERE i<100
    )
    INSERT INTO x1 
      SELECT 'seems to be', 'used brew to', 'everything is working' FROM s
        UNION ALL
      SELECT 'used brew to', 'everything is working', 'seems to be' FROM s
        UNION ALL
      SELECT 'everything is working', 'seems to be', 'used brew to' FROM s
        UNION ALL
      SELECT 'abc', 'zzz', 'a b c d'
        UNION ALL
      SELECT 'z', 'z', 'z' FROM s
  }

  do_test 2.$tn.6 {
    for {set i 300} {$i > 200} {incr i -1} {
      execsql {
        DELETE FROM x1 WHERE rowid=$i;
        INSERT INTO x1(x1) VALUES('integrity-check');
      }
    }
  } {}

  do_test 2.$tn.7 {
    for {set i 1} {$i < 100} {incr i} {
      execsql {
        DELETE FROM x1 WHERE rowid=$i;
        INSERT INTO x1(x1) VALUES('integrity-check');
      }
    }
  } {}

  do_test 2.$tn.8 {
    foreach i [db eval {SELECT rowid FROM x1}] {
      execsql {
        DELETE FROM x1 WHERE rowid=$i;
        INSERT INTO x1(x1) VALUES('integrity-check');
      }
    }
  } {}

  do_execsql_test 2.$tn.9 {
    SELECT * FROM x1
  } {}
}



finish_test