gitlab.com/CoiaPrant/sqlite3@v1.19.1/testdata/tcl/fts3ab.test (about)

     1  # 2006 September 13
     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  # This file implements regression tests for SQLite library.  The
    12  # focus of this script is testing the FTS3 module.
    13  #
    14  # $Id: fts3ab.test,v 1.1 2007/08/20 17:38:42 shess Exp $
    15  #
    16  
    17  set testdir [file dirname $argv0]
    18  source $testdir/tester.tcl
    19  
    20  # If SQLITE_ENABLE_FTS3 is defined, omit this file.
    21  ifcapable !fts3 {
    22    finish_test
    23    return
    24  }
    25  
    26  # Fill the full-text index "t1" with phrases in english, spanish,
    27  # and german.  For the i-th row, fill in the names for the bits
    28  # that are set in the value of i.  The least significant bit is
    29  # 1.  For example,  the value 5 is 101 in binary which will be
    30  # converted to "one three" in english.
    31  #
    32  proc fill_multilanguage_fulltext_t1 {} {
    33    set english {one two three four five}
    34    set spanish {un dos tres cuatro cinco}
    35    set german {eine zwei drei vier funf}
    36    
    37    for {set i 1} {$i<=31} {incr i} {
    38      set cmd "INSERT INTO t1 VALUES"
    39      set vset {}
    40      foreach lang {english spanish german} {
    41        set words {}
    42        for {set j 0; set k 1} {$j<5} {incr j; incr k $k} {
    43          if {$k&$i} {lappend words [lindex [set $lang] $j]}
    44        }
    45        lappend vset "'$words'"
    46      }
    47      set sql "INSERT INTO t1(english,spanish,german) VALUES([join $vset ,])"
    48      # puts $sql
    49      db eval $sql
    50    }
    51  }
    52  
    53  # Construct a full-text search table containing five keywords:
    54  # one, two, three, four, and five, in various combinations.  The
    55  # rowid for each will be a bitmask for the elements it contains.
    56  #
    57  db eval {
    58    CREATE VIRTUAL TABLE t1 USING fts3(english,spanish,german);
    59  }
    60  fill_multilanguage_fulltext_t1
    61  
    62  do_test fts3ab-1.1 {
    63    execsql {SELECT rowid FROM t1 WHERE english MATCH 'one'}
    64  } {1 3 5 7 9 11 13 15 17 19 21 23 25 27 29 31}
    65  do_test fts3ab-1.2 {
    66    execsql {SELECT rowid FROM t1 WHERE spanish MATCH 'one'}
    67  } {}
    68  do_test fts3ab-1.3 {
    69    execsql {SELECT rowid FROM t1 WHERE german MATCH 'one'}
    70  } {}
    71  do_test fts3ab-1.4 {
    72    execsql {SELECT rowid FROM t1 WHERE t1 MATCH 'one'}
    73  } {1 3 5 7 9 11 13 15 17 19 21 23 25 27 29 31}
    74  do_test fts3ab-1.5 {
    75    execsql {SELECT rowid FROM t1 WHERE t1 MATCH 'one dos drei'}
    76  } {7 15 23 31}
    77  do_test fts3ab-1.6 {
    78    execsql {SELECT english, spanish, german FROM t1 WHERE rowid=1}
    79  } {one un eine}
    80  do_test fts3ab-1.7 {
    81    execsql {SELECT rowid FROM t1 WHERE t1 MATCH '"one un"'}
    82  } {}
    83  
    84  do_test fts3ab-2.1 {
    85    execsql {
    86      CREATE VIRTUAL TABLE t2 USING fts3(from,to);
    87      INSERT INTO t2([from],[to]) VALUES ('one two three', 'four five six');
    88      SELECT [from], [to] FROM t2
    89    }
    90  } {{one two three} {four five six}}
    91  
    92  
    93  # Compute an SQL string that contains the words one, two, three,... to
    94  # describe bits set in the value $i.  Only the lower 5 bits are examined.
    95  #
    96  proc wordset {i} {
    97    set x {}
    98    for {set j 0; set k 1} {$j<5} {incr j; incr k $k} {
    99      if {$k&$i} {lappend x [lindex {one two three four five} $j]}
   100    }
   101    return '$x'
   102  }
   103  
   104  # Create a new FTS table with three columns:
   105  #
   106  #    norm:      words for the bits of rowid
   107  #    plusone:   words for the bits of rowid+1
   108  #    invert:    words for the bits of ~rowid
   109  #
   110  db eval {
   111     CREATE VIRTUAL TABLE t4 USING fts3([norm],'plusone',"invert");
   112  }
   113  for {set i 1} {$i<=15} {incr i} {
   114    set vset [list [wordset $i] [wordset [expr {$i+1}]] [wordset [expr {~$i}]]]
   115    db eval "INSERT INTO t4(norm,plusone,invert) VALUES([join $vset ,]);"
   116  }
   117  
   118  do_test fts3ab-4.1 {
   119    execsql {SELECT rowid FROM t4 WHERE t4 MATCH 'norm:one'}
   120  } {1 3 5 7 9 11 13 15}
   121  do_test fts3ab-4.2 {
   122    execsql {SELECT rowid FROM t4 WHERE norm MATCH 'one'}
   123  } {1 3 5 7 9 11 13 15}
   124  do_test fts3ab-4.3 {
   125    execsql {SELECT rowid FROM t4 WHERE t4 MATCH 'one'}
   126  } {1 2 3 4 5 6 7 8 9 10 11 12 13 14 15}
   127  do_test fts3ab-4.4 {
   128    execsql {SELECT rowid FROM t4 WHERE t4 MATCH 'plusone:one'}
   129  } {2 4 6 8 10 12 14}
   130  do_test fts3ab-4.5 {
   131    execsql {SELECT rowid FROM t4 WHERE plusone MATCH 'one'}
   132  } {2 4 6 8 10 12 14}
   133  do_test fts3ab-4.6 {
   134    execsql {SELECT rowid FROM t4 WHERE t4 MATCH 'norm:one plusone:two'}
   135  } {1 5 9 13}
   136  do_test fts3ab-4.7 {
   137    execsql {SELECT rowid FROM t4 WHERE t4 MATCH 'norm:one two'}
   138  } {1 3 5 7 9 11 13 15}
   139  do_test fts3ab-4.8 {
   140    execsql {SELECT rowid FROM t4 WHERE t4 MATCH 'plusone:two norm:one'}
   141  } {1 5 9 13}
   142  do_test fts3ab-4.9 {
   143    execsql {SELECT rowid FROM t4 WHERE t4 MATCH 'two norm:one'}
   144  } {1 3 5 7 9 11 13 15}
   145  
   146  
   147  finish_test