modernc.org/cc@v1.0.1/v2/testdata/_sqlite/ext/fts5/test/fts5restart.test (about)

     1  # 2015 April 28
     2  #
     3  # The author disclaims copyright to this source code.  In place of
     4  # a legal notice, here is a blessing:
     5  #
     6  #    May you do good and not evil.
     7  #    May you find forgiveness for yourself and forgive others.
     8  #    May you share freely, never taking more than you give.
     9  #
    10  #***********************************************************************
    11  #
    12  # This file focuses on testing the planner (xBestIndex function).
    13  #
    14  
    15  source [file join [file dirname [info script]] fts5_common.tcl]
    16  set testprefix fts5restart
    17  
    18  # If SQLITE_ENABLE_FTS5 is defined, omit this file.
    19  ifcapable !fts5 {
    20    finish_test
    21    return
    22  }
    23  
    24  do_execsql_test 1.0 {
    25    CREATE VIRTUAL TABLE f1 USING fts5(ff);
    26  }
    27  
    28  #-------------------------------------------------------------------------
    29  # Run the 'optimize' command. Check that it does not disturb ongoing
    30  # full-text queries.
    31  #
    32  do_test 1.1 {
    33    for {set i 1} {$i < 1000} {incr i} {
    34      execsql { INSERT INTO f1 VALUES('a b c d e') }
    35      lappend lRowid $i
    36    }
    37  } {}
    38  
    39  do_execsql_test 1.2 {
    40    SELECT rowid FROM f1 WHERE f1 MATCH 'c';
    41  } $lRowid
    42  
    43  do_test 1.3 {
    44    set res [list]
    45    db eval { SELECT rowid FROM f1 WHERE f1 MATCH 'c' } {
    46      if {$rowid == 100} {
    47        execsql { INSERT INTO f1(f1) VALUES('optimize') }
    48      }
    49      lappend res $rowid
    50    }
    51    set res
    52  } $lRowid
    53  
    54  do_test 1.4.1 {
    55    sqlite3 db2 test.db
    56    set res [list]
    57    db2 eval { SELECT rowid FROM f1 WHERE f1 MATCH 'c' } {
    58      if {$rowid == 100} {
    59        set cres [catchsql { INSERT INTO f1(f1) VALUES('optimize') }]
    60      }
    61      lappend res $rowid
    62    }
    63    set res
    64  } $lRowid
    65  
    66  do_test 1.4.2 {
    67    db2 close
    68    set cres
    69  } {1 {database is locked}}
    70  
    71  #-------------------------------------------------------------------------
    72  # Open a couple of cursors. Then close them in the same order.
    73  #
    74  do_test 2.1 {
    75    set ::s1 [sqlite3_prepare db "SELECT rowid FROM f1 WHERE f1 MATCH 'b'" -1 X]
    76    set ::s2 [sqlite3_prepare db "SELECT rowid FROM f1 WHERE f1 MATCH 'c'" -1 X]
    77  
    78    sqlite3_step $::s1
    79  } {SQLITE_ROW}
    80  do_test 2.2 {
    81    sqlite3_step $::s2
    82  } {SQLITE_ROW}
    83  
    84  do_test 2.1 {
    85    sqlite3_finalize $::s1
    86    sqlite3_finalize $::s2
    87  } {SQLITE_OK}
    88  
    89  #-------------------------------------------------------------------------
    90  # Copy data between two FTS5 tables.
    91  #
    92  do_execsql_test 3.1 {
    93    CREATE VIRTUAL TABLE f2 USING fts5(gg);
    94    INSERT INTO f2 SELECT ff FROM f1 WHERE f1 MATCH 'b+c+d';
    95  }
    96  do_execsql_test 3.2 {
    97    SELECT rowid FROM f2 WHERE f2 MATCH 'a+b+c+d+e'
    98  } $lRowid
    99  
   100  #-------------------------------------------------------------------------
   101  # Remove the row that an FTS5 cursor is currently pointing to. And 
   102  # various other similar things. Check that this does not disturb 
   103  # ongoing scans.
   104  #
   105  do_execsql_test 4.0 {
   106    CREATE VIRTUAL TABLE n4 USING fts5(n);
   107    INSERT INTO n4(rowid, n) VALUES(100, '1 2 3 4 5');
   108    INSERT INTO n4(rowid, n) VALUES(200, '1 2 3 4');
   109    INSERT INTO n4(rowid, n) VALUES(300, '2 3 4');
   110    INSERT INTO n4(rowid, n) VALUES(400, '2 3');
   111    INSERT INTO n4(rowid, n) VALUES(500, '3');
   112  }
   113  
   114  do_test 4.1 {
   115    set res [list]
   116    db eval { SELECT rowid FROM n4 WHERE n4 MATCH '3' } {
   117      if {$rowid==300} {
   118        execsql { DELETE FROM n4 WHERE rowid=300 }
   119      }
   120      lappend res $rowid
   121    }
   122    set res
   123  } {100 200 300 400 500}
   124  
   125  do_test 4.2 {
   126    execsql { INSERT INTO n4(rowid, n) VALUES(300, '2 3 4') }
   127    set res [list]
   128    db eval { SELECT rowid FROM n4 WHERE n4 MATCH '3' ORDER BY rowid DESC} {
   129      if {$rowid==300} {
   130        execsql { DELETE FROM n4 WHERE rowid=300 }
   131      }
   132      lappend res $rowid
   133    }
   134    set res
   135  } {500 400 300 200 100}
   136  
   137  do_test 4.3 {
   138    execsql { INSERT INTO n4(rowid, n) VALUES(300, '2 3 4') }
   139    set res [list]
   140    db eval { SELECT rowid FROM n4 WHERE n4 MATCH '3' ORDER BY rowid DESC} {
   141      if {$rowid==300} {
   142        execsql { DELETE FROM n4  }
   143      }
   144      lappend res $rowid
   145    }
   146    set res
   147  } {500 400 300}
   148  
   149  
   150  
   151  finish_test