github.com/jdgcs/sqlite3@v1.12.1-0.20210908114423-bc5f96e4dd51/testdata/tcl/fts3aux2.test (about)

     1  # 2011 January 27
     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  
    15  set testdir [file dirname $argv0]
    16  source $testdir/tester.tcl
    17  ifcapable !fts3 { finish_test ; return }
    18  set ::testprefix fts3aux2
    19  
    20  do_execsql_test 1.1 {
    21    CREATE VIRTUAL TABLE t1 USING fts4(a, b, languageid=l);
    22    INSERT INTO t1(a, b, l) VALUES
    23      ('zero zero', 'zero zero', 0),
    24      ('one two', 'three four', 1),
    25      ('five six', 'seven eight', 2)
    26    ;
    27    CREATE VIRTUAL TABLE terms USING fts4aux(t1);
    28  } {}
    29  
    30  do_execsql_test 1.2.1 {
    31    SELECT term, documents, occurrences, languageid FROM terms WHERE col = '*';
    32  } {zero 1 4 0}
    33  
    34  do_execsql_test 1.2.2 {
    35    SELECT * FROM terms;
    36  } {zero * 1 4 zero 0 1 2 zero 1 1 2}
    37  
    38  do_execsql_test 1.2.3 {
    39    SELECT * FROM terms WHERE languageid='';
    40  } {}
    41  
    42  do_execsql_test 1.2.4 {
    43    SELECT * FROM terms WHERE languageid=-1;
    44  } {}
    45  
    46  do_execsql_test 1.2.5 {
    47    SELECT * FROM terms WHERE languageid=9223372036854775807;
    48  } {}
    49  
    50  do_execsql_test 1.2.6 {
    51    SELECT * FROM terms WHERE languageid=-9223372036854775808;
    52  } {}
    53  
    54  do_execsql_test 1.2.7 {
    55    SELECT * FROM terms WHERE languageid=NULL;
    56  } {}
    57  
    58  do_execsql_test 1.3.1 {
    59    SELECT term, documents, occurrences, languageid 
    60    FROM terms WHERE col = '*' AND languageid=1;
    61  } {
    62    four 1 1 1 one 1 1 1 three 1 1 1 two 1 1 1 
    63  }
    64  
    65  do_execsql_test 1.3.2 {
    66    SELECT term, col, documents, occurrences, languageid 
    67    FROM terms WHERE languageid=1;
    68  } {
    69    four * 1 1 1   four 1 1 1 1 
    70    one * 1 1 1    one 0 1 1 1 
    71    three * 1 1 1  three 1 1 1 1 
    72    two * 1 1 1    two 0 1 1 1 
    73  }
    74  
    75  do_execsql_test 1.3.3 {
    76    SELECT term, col, documents, occurrences, languageid 
    77    FROM terms WHERE languageid=1 AND term='zero'
    78  } {
    79  }
    80  
    81  do_execsql_test 1.3.4 {
    82    SELECT term, col, documents, occurrences, languageid 
    83    FROM terms WHERE languageid='1' AND term='two'
    84  } {
    85    two * 1 1 1    two 0 1 1 1 
    86  }
    87  
    88  do_execsql_test 1.3.5 {
    89    SELECT term, col, documents, occurrences, languageid 
    90    FROM terms WHERE languageid='+1' AND term>'four'
    91  } {
    92    one * 1 1 1    one 0 1 1 1 
    93    three * 1 1 1  three 1 1 1 1 
    94    two * 1 1 1    two 0 1 1 1 
    95  }
    96  
    97  do_execsql_test 1.4.1 {
    98    SELECT term, documents, occurrences, languageid 
    99    FROM terms WHERE col = '*' AND languageid=2;
   100  } {
   101    eight 1 1 2 five 1 1 2 seven 1 1 2 six 1 1 2
   102  }
   103  
   104  do_execsql_test 1.4.2 {
   105    SELECT term, col, documents, occurrences, languageid 
   106    FROM terms WHERE languageid=2;
   107  } {
   108    eight * 1 1 2    eight 1 1 1 2 
   109    five * 1 1 2     five 0 1 1 2 
   110    seven * 1 1 2    seven 1 1 1 2 
   111    six * 1 1 2      six 0 1 1 2
   112  }
   113  
   114  do_execsql_test 1.4.3 {
   115    SELECT term, col, documents, occurrences, languageid 
   116    FROM terms WHERE languageid=2 AND term='five';
   117  } {
   118    five * 1 1 2     five 0 1 1 2 
   119  }
   120  
   121  do_execsql_test 1.4.4 {
   122    SELECT term, col, documents, occurrences, languageid 
   123    FROM terms WHERE term='five' AND languageid=2 
   124  } {
   125    five * 1 1 2     five 0 1 1 2 
   126  }
   127  
   128  do_execsql_test 1.4.5 {
   129    SELECT term, col, documents, occurrences, languageid 
   130    FROM terms WHERE term>='seven' AND languageid=2
   131  } {
   132    seven * 1 1 2    seven 1 1 1 2 
   133    six * 1 1 2      six 0 1 1 2
   134  }
   135  
   136  do_execsql_test 1.4.6 {
   137    SELECT term, col, documents, occurrences, languageid 
   138    FROM terms WHERE term>='e' AND term<'seven' AND languageid=2
   139  } {
   140    eight * 1 1 2    eight 1 1 1 2 
   141    five * 1 1 2     five 0 1 1 2 
   142  }
   143  
   144  #-------------------------------------------------------------------------
   145  do_execsql_test 2.0 {
   146    CREATE VIRTUAL TABLE ft USING fts3();
   147    INSERT INTO ft VALUES('a_234567890123456789');
   148    INSERT INTO ft VALUES('b_234567890123456789');
   149    INSERT INTO ft VALUES('c_234567890123456789');
   150    CREATE VIRTUAL TABLE t2 USING fts4aux(ft);
   151  }
   152  
   153  do_execsql_test 2.1 {
   154    SELECT term FROM t2 WHERE term=X'625f323334353637383930313233343536373839';
   155  }
   156  
   157  do_execsql_test 2.2 {
   158    SELECT term FROM t2 WHERE term<X'625f003334353637383930313233343536373839';
   159  } {
   160    234567890123456789 234567890123456789 a a b b
   161  }
   162  
   163  do_execsql_test 2.3 {
   164    SELECT term FROM t2 WHERE term=X'625f003334353637383930313233343536373839';
   165  }
   166  
   167  
   168  finish_test