AryaWu/sqlite

1# 2011 October 182#3# The author disclaims copyright to this source code.  In place of4# 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 12set testdir [file dirname $argv0]13source $testdir/tester.tcl14source $testdir/malloc_common.tcl15 16ifcapable !fts3 {17  finish_test18  return19}20 21set testprefix fts3first22 23proc lreverse {L} {24  set res [list]25  for {set ii [expr [llength $L]-1]} {$ii>=0} {incr ii -1} {26    lappend res [lindex $L $ii]27  }28  set res29}30 31proc mit {blob} {32  set scan(littleEndian) i*33  set scan(bigEndian) I*34  binary scan $blob $scan($::tcl_platform(byteOrder)) r35  return $r36}37db func mit mit38 39do_execsql_test 1.0 {40  CREATE VIRTUAL TABLE x1 USING FTS4(a, b, c);41  INSERT INTO x1(docid,a,b,c) VALUES(0, 'K H D S T', 'V M N Y K', 'S Z N Q S');42  INSERT INTO x1(docid,a,b,c) VALUES(1, 'K N J L W', 'S Z W J Q', 'D U W S E');43  INSERT INTO x1(docid,a,b,c) VALUES(2, 'B P M O I', 'R P H W S', 'R J L L E');44  INSERT INTO x1(docid,a,b,c) VALUES(3, 'U R Q M L', 'M J K A V', 'Q W J T J');45  INSERT INTO x1(docid,a,b,c) VALUES(4, 'N J C Y N', 'R U D X V', 'B O U A Q');46  INSERT INTO x1(docid,a,b,c) VALUES(5, 'Q L X L U', 'I F N X S', 'U Q A N Y');47  INSERT INTO x1(docid,a,b,c) VALUES(6, 'M R G U T', 'U V I Q P', 'X Y D L S');48  INSERT INTO x1(docid,a,b,c) VALUES(7, 'D Y P O I', 'X J P K R', 'V O T H V');49  INSERT INTO x1(docid,a,b,c) VALUES(8, 'R Y D L R', 'U U E S J', 'N W L M R');50  INSERT INTO x1(docid,a,b,c) VALUES(9, 'Z P F N P', 'W A X D U', 'V A E Q A');51  INSERT INTO x1(docid,a,b,c) VALUES(10, 'Q I A Q M', 'N D K H C', 'A H T Q Z');52  INSERT INTO x1(docid,a,b,c) VALUES(11, 'T E R Q B', 'C I B C B', 'F Z U W R');53  INSERT INTO x1(docid,a,b,c) VALUES(12, 'E S V U W', 'T P F W H', 'A M D J Q');54  INSERT INTO x1(docid,a,b,c) VALUES(13, 'X S B X Y', 'U D N D P', 'X Z Y G F');55  INSERT INTO x1(docid,a,b,c) VALUES(14, 'K H A B L', 'S R C C Z', 'D W E H J');56  INSERT INTO x1(docid,a,b,c) VALUES(15, 'C E U C C', 'W F M N M', 'T Z U X T');57  INSERT INTO x1(docid,a,b,c) VALUES(16, 'Q G C G H', 'H N N B H', 'B Q I H Y');58  INSERT INTO x1(docid,a,b,c) VALUES(17, 'Q T S K B', 'W B D Y N', 'V J P E C');59  INSERT INTO x1(docid,a,b,c) VALUES(18, 'A J M O Q', 'L G Y Y A', 'G N M R N');60  INSERT INTO x1(docid,a,b,c) VALUES(19, 'T R Y P Y', 'N V Y B X', 'L Z T N T');61 62  CREATE VIRTUAL TABLE x2 USING FTS4(a, b, c, order=DESC);63  INSERT INTO x2(docid, a, b, c) SELECT docid, a, b, c FROM x1;64}65 66 67# Test queries.68#69foreach x {1 2} {70  foreach {tn match res} {71    1  "^K"              {0 1 14}72    2  "^S"              {0 1 14}73    3  "^W"              {9 15 17}74    4  "^J"              {}75    5  "^E"              {12}76    6  "V ^-E"           {0 3 4 6 7 9 17 19}77    7  "V -^E"           {0 3 4 6 7 9 17 19}78    8  "^-E V"           {0 3 4 6 7 9 17 19}79    9  "-^E V"           {0 3 4 6 7 9 17 19}80    10 "V"               {0 3 4 6 7 9 12 17 19}81 82    11 {"^K H"}          {0 14}83    12 {"K H"}           {0 10 14}84    13 {"K ^H"}          {}85  } {86    set rev [lreverse $res]87    do_execsql_test 1.$x.$tn.1 {SELECT docid FROM x1 WHERE x1 MATCH $match} $res88    do_execsql_test 1.$x.$tn.2 {SELECT docid FROM x2 WHERE x2 MATCH $match} $rev89  }90 91  do_execsql_test 1.$x.[expr $tn+1] { 92    INSERT INTO x1(x1) VALUES('optimize');93    INSERT INTO x2(x2) VALUES('optimize');94  } {}95}96 97# Test the snippet() function.98#99foreach {tn match res} {100  1  {^K}    {{[K] H D S T} {[K] N J L W} {[K] H A B L}}101  2  {^X}    {{[X] Y D L S} {[X] J P K R} {[X] S B X Y}}102  3  {^X Y}  {{[X] [Y] D L S} {D [Y] P O I...[X] J P K R} {[X] S B X [Y]}}103} {104  set rev [lreverse $res]105 106  do_execsql_test 1.3.$tn.1 {107    SELECT snippet(x1, '[', ']', '...') FROM x1 WHERE x1 MATCH $match108  } $res109 110  do_execsql_test 1.3.$tn.2 {111    SELECT snippet(x2, '[', ']', '...') FROM x2 WHERE x2 MATCH $match112  } $rev113}114 115# Test matchinfo().116#117foreach {tn match res} {118  1  {^K}    {119                {1 3 3 0 0 0 0 0 0}120                {1 3 3 0 0 0 0 0 0}121                {1 3 3 0 0 0 0 0 0}122             }123  2  {^X}    {124                {0 1 1 0 1 1 1 2 2}125                {0 1 1 1 1 1 0 2 2}126                {1 1 1 0 1 1 1 2 2}127             }128  3  {^X Y}  {129                {0 1 1 0 1 1 1 2 2 0 6 5 0 5 4 1 4 4} 130                {0 1 1 1 1 1 0 2 2 1 6 5 0 5 4 0 4 4} 131                {1 1 1 0 1 1 1 2 2 1 6 5 0 5 4 1 4 4}132             }133} {134  set rev [lreverse $res]135 136  do_execsql_test 1.3.$tn.1 {137    SELECT mit(matchinfo(x1, 'x')) FROM x1 WHERE x1 MATCH $match138  } $res139  do_execsql_test 1.3.$tn.2 {140    SELECT mit(matchinfo(x2, 'x')) FROM x2 WHERE x2 MATCH $match141  } $rev142}143 144# Test that ^ is ignored for FTS3 tables.145#146do_execsql_test 2.1 {147  CREATE VIRTUAL TABLE x3 USING fts3;148  INSERT INTO x3 VALUES('A B C');149  INSERT INTO x3 VALUES('B A C');150 151  CREATE VIRTUAL TABLE x4 USING fts4;152  INSERT INTO x4 VALUES('A B C');153  INSERT INTO x4 VALUES('B A C');154}155 156do_execsql_test 2.2.1 {157  SELECT * FROM x3 WHERE x3 MATCH '^A';158} {{A B C} {B A C}}159do_execsql_test 2.2.2 {160  SELECT * FROM x4 WHERE x4 MATCH '^A';161} {{A B C}}162 163finish_test164