Return to fts3b.test CVS log

Up to [ELWIX - Embedded LightWeight unIX -] / embedaddon / sqlite3 / test

sqlite3

    1: # 2007 August 20
    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.  This
   12: # script tests for the fts2 rowid-versus-vacuum problem (ticket #2566).
   13: #
   14: # $Id: fts3b.test,v 1.1.1.1 2012/02/21 17:04:16 misho Exp $
   15: #
   16: 
   17: set testdir [file dirname $argv0]
   18: source $testdir/tester.tcl
   19: 
   20: # If SQLITE_ENABLE_FTS3 is not defined, omit this file.
   21: ifcapable !fts3 {
   22:   finish_test
   23:   return
   24: }
   25: 
   26: db eval {
   27:   CREATE VIRTUAL TABLE t1 USING fts3(c);
   28:   INSERT INTO t1 (c) VALUES('this is a test');
   29:   INSERT INTO t1 (c) VALUES('that was a test');
   30:   INSERT INTO t1 (c) VALUES('this is fun');
   31:   DELETE FROM t1 WHERE c = 'that was a test';
   32: }
   33: 
   34: # Baseline test.
   35: do_test fts3b-1.1 {
   36:   execsql {
   37:     SELECT rowid FROM t1 WHERE c MATCH 'this';
   38:   }
   39: } {1 3}
   40: 
   41: db eval {VACUUM}
   42: 
   43: # The VACUUM renumbered the t1_content table in fts2, which breaks
   44: # this.
   45: do_test fts3b-1.2 {
   46:   execsql {
   47:     SELECT rowid FROM t1 WHERE c MATCH 'this';
   48:   }
   49: } {1 3}
   50: 
   51: # The t2 table is unfortunately pretty contrived.  We need documents
   52: # that are bigger than ROOT_MAX (1024) to force segments out of the
   53: # segdir and into %_segments.  We also need to force segment merging
   54: # to generate a hole in the %_segments table, which needs more than 16
   55: # docs.  Beyond that, to test correct operation of BLOCK_SELECT_STMT,
   56: # we need to merge a mult-level tree, which is where the 10,000 comes
   57: # from.  Which is slow, thus the set of transactions, with the 500
   58: # being a number such that 10,000/500 > 16.
   59: set text {
   60:   Lorem ipsum dolor sit amet, consectetuer adipiscing elit. Maecenas
   61:   iaculis mollis ipsum. Praesent rhoncus placerat justo. Duis non quam
   62:   sed turpis posuere placerat. Curabitur et lorem in lorem porttitor
   63:   aliquet. Pellentesque bibendum tincidunt diam. Vestibulum blandit
   64:   ante nec elit. In sapien diam, facilisis eget, dictum sed, viverra
   65:   at, felis. Vestibulum magna. Sed magna dolor, vestibulum rhoncus,
   66:   ornare vel, vulputate sit amet, felis. Integer malesuada, tellus at
   67:   luctus gravida, diam nunc porta nibh, nec imperdiet massa metus eu
   68:   lectus. Aliquam nisi. Nunc fringilla nulla at lectus. Suspendisse
   69:   potenti. Cum sociis natoque penatibus et magnis dis parturient
   70:   montes, nascetur ridiculus mus. Pellentesque odio nulla, feugiat eu,
   71:   suscipit nec, consequat quis, risus.
   72: }
   73: append text $text
   74: 
   75: db eval {CREATE VIRTUAL TABLE t2 USING fts3(c)}
   76: set res {}
   77: db eval {BEGIN}
   78: for {set ii 0} {$ii<10000} {incr ii} {
   79:   db eval {INSERT INTO t2 (c) VALUES ($text)}
   80:   lappend res [expr {$ii+1}]
   81:   if {($ii%500)==0} {
   82:     db eval {
   83:       COMMIT;
   84:       BEGIN;
   85:     }
   86:   }
   87: }
   88: db eval {COMMIT}
   89: 
   90: do_test fts3b-2.1 {
   91:   execsql {
   92:     SELECT rowid FROM t2 WHERE c MATCH 'lorem';
   93:   }
   94: } $res
   95: 
   96: db eval {VACUUM}
   97: 
   98: # The VACUUM renumbered the t2_segment table in fts2, which would
   99: # break the following.
  100: do_test fts3b-2.2 {
  101:   execsql {
  102:     SELECT rowid FROM t2 WHERE c MATCH 'lorem';
  103:   }
  104: } $res
  105: 
  106: # Since fts3 is already an API break, I've marked the table-named
  107: # column HIDDEN.
  108: 
  109: db eval {
  110:   CREATE VIRTUAL TABLE t3 USING fts3(c);
  111:   INSERT INTO t3 (c) VALUES('this is a test');
  112:   INSERT INTO t3 (c) VALUES('that was a test');
  113:   INSERT INTO t3 (c) VALUES('this is fun');
  114:   DELETE FROM t3 WHERE c = 'that was a test';
  115: }
  116: 
  117: # Test that the table-named column still works.
  118: do_test fts3b-3.1 {
  119:   execsql {
  120:     SELECT snippet(t3) FROM t3 WHERE t3 MATCH 'test';
  121:   }
  122: } {{this is a <b>test</b>}}
  123: 
  124: # Test that the column doesn't appear when selecting all columns.
  125: do_test fts3b-3.2 {
  126:   execsql {
  127:     SELECT * FROM t3 WHERE rowid = 1;
  128:   }
  129: } {{this is a test}}
  130: 
  131: # Test that the column doesn't conflict with inserts that don't name
  132: # columns.
  133: do_test fts3b-3.3 {
  134:   execsql {
  135:     INSERT INTO t3 VALUES ('another test');
  136:   }
  137: } {}
  138: 
  139: # fts3 adds a new implicit column, docid, which acts as an alias for
  140: # rowid.
  141: 
  142: db eval {
  143:   CREATE VIRTUAL TABLE t4 USING fts3(c);
  144:   INSERT INTO t4 (c) VALUES('this is a test');
  145:   INSERT INTO t4 (c) VALUES('that was a test');
  146:   INSERT INTO t4 (c) VALUES('this is fun');
  147:   DELETE FROM t4 WHERE c = 'that was a test';
  148: }
  149: 
  150: # Test that docid is present and identical to rowid.
  151: do_test fts3b-4.1 {
  152:   execsql {
  153:     SELECT rowid FROM t4 WHERE rowid <> docid;
  154:   }
  155: } {}
  156: 
  157: # Test that docid is hidden.
  158: do_test fts3b-4.2 {
  159:   execsql {
  160:     SELECT * FROM t4 WHERE rowid = 1;
  161:   }
  162: } {{this is a test}}
  163: 
  164: # Test that docid can be selected.
  165: do_test fts3b-4.3 {
  166:   execsql {
  167:     SELECT docid, * FROM t4 WHERE rowid = 1;
  168:   }
  169: } {1 {this is a test}}
  170: 
  171: # Test that docid can be used in WHERE.
  172: do_test fts3b-4.4 {
  173:   execsql {
  174:     SELECT docid, * FROM t4 WHERE docid = 1;
  175:   }
  176: } {1 {this is a test}}
  177: 
  178: # Test that the column doesn't conflict with inserts that don't name
  179: # columns.  [Yes, this is the same as fts3b-3.3, here just in case the
  180: # goals of that test change.]
  181: do_test fts3b-4.5 {
  182:   execsql {
  183:     INSERT INTO t4 VALUES ('another test');
  184:   }
  185: } {}
  186: 
  187: # Test that the docid can be forced on insert.
  188: do_test fts3b-4.6 {
  189:   execsql {
  190:     INSERT INTO t4 (docid, c) VALUES (10, 'yet another test');
  191:     SELECT * FROM t4 WHERE docid = 10;
  192:   }
  193: } {{yet another test}}
  194: 
  195: # Test that rowid can also be forced.
  196: do_test fts3b-4.7 {
  197:   execsql {
  198:     INSERT INTO t4 (docid, c) VALUES (12, 'still testing');
  199:     SELECT * FROM t4 WHERE docid = 12;
  200:   }
  201: } {{still testing}}
  202: 
  203: # If an insert tries to set both docid and rowid, require an error.
  204: do_test fts3b-4.8 {
  205:   catchsql {
  206:     INSERT INTO t4 (rowid, docid, c) VALUES (14, 15, 'bad test');
  207:     SELECT * FROM t4 WHERE docid = 14;
  208:   }
  209: } {1 {SQL logic error or missing database}}
  210: 
  211: do_test fts3b-4.9 {
  212:   execsql { SELECT docid FROM t4 WHERE t4 MATCH 'testing' }
  213: } {12}
  214: do_test fts3b-4.10 {
  215:   execsql { 
  216:     UPDATE t4 SET docid = 14 WHERE docid = 12;
  217:     SELECT docid FROM t4 WHERE t4 MATCH 'testing';
  218:   }
  219: } {14}
  220: do_test fts3b-4.11 {
  221:   execsql { SELECT * FROM t4 WHERE rowid = 14; }
  222: } {{still testing}}
  223: do_test fts3b-4.12 {
  224:   execsql { SELECT * FROM t4 WHERE rowid = 12; }
  225: } {}
  226: do_test fts3b-4.13 {
  227:   execsql { SELECT docid FROM t4 WHERE t4 MATCH 'still'; }
  228: } {14}
  229: 
  230: finish_test