0
0
mirror of https://github.com/tursodatabase/libsql.git synced 2024-12-15 03:59:46 +00:00
libsql/libsql-sqlite3/test/fts4growth.test
2023-10-16 13:58:16 +02:00

442 lines
13 KiB
Plaintext
Raw Permalink Blame History

This file contains ambiguous Unicode characters

This file contains Unicode characters that might be confused with other characters. If you think that this is intentional, you can safely ignore this warning. Use the Escape button to reveal them.

# 2014 May 12
#
# The author disclaims copyright to this source code. In place of
# a legal notice, here is a blessing:
#
# May you do good and not evil.
# May you find forgiveness for yourself and forgive others.
# May you share freely, never taking more than you give.
#
#*************************************************************************
# This file implements regression tests for SQLite library. The
# focus of this script is testing the FTS4 module.
#
#
set testdir [file dirname $argv0]
source $testdir/tester.tcl
set testprefix fts4growth
# If SQLITE_ENABLE_FTS3 is defined, omit this file.
ifcapable !fts3 {
finish_test
return
}
source $testdir/genesis.tcl
sqlite3_db_config db DEFENSIVE 0
do_execsql_test 1.1 { CREATE VIRTUAL TABLE x1 USING fts3; }
do_test 1.2 {
foreach L {
{"See here, young man," said Mulga Bill, "from Walgett to the sea,}
{From Conroy's Gap to Castlereagh, there's none can ride like me.}
{I'm good all round at everything as everybody knows,}
{Although I'm not the one to talk -- I hate a man that blows.}
} {
execsql { INSERT INTO x1 VALUES($L) }
}
execsql { SELECT end_block, length(root) FROM x1_segdir }
} {{0 114} 114 {0 118} 118 {0 95} 95 {0 115} 115}
do_execsql_test 1.3 {
INSERT INTO x1(x1) VALUES('optimize');
SELECT level, end_block, length(root) FROM x1_segdir;
} {0 {0 394} 394}
do_test 1.4 {
foreach L {
{But riding is my special gift, my chiefest, sole delight;}
{Just ask a wild duck can it swim, a wildcat can it fight.}
{There's nothing clothed in hair or hide, or built of flesh or steel,}
{There's nothing walks or jumps, or runs, on axle, hoof, or wheel,}
{But what I'll sit, while hide will hold and girths and straps are tight:}
{I'll ride this here two-wheeled concern right straight away at sight."}
} {
execsql { INSERT INTO x1 VALUES($L) }
}
execsql {
INSERT INTO x1(x1) VALUES('merge=4,4');
SELECT level, end_block, length(root) FROM x1_segdir;
}
} {1 {224 921} 2}
do_execsql_test 1.5 {
SELECT length(block) FROM x1_segments;
} {921 {}}
do_test 1.6 {
foreach L {
{'Twas Mulga Bill, from Eaglehawk, that sought his own abode,}
{That perched above Dead Man's Creek, beside the mountain road.}
{He turned the cycle down the hill and mounted for the fray,}
{But 'ere he'd gone a dozen yards it bolted clean away.}
{It left the track, and through the trees, just like a silver steak,}
{It whistled down the awful slope towards the Dead Man's Creek.}
{It shaved a stump by half an inch, it dodged a big white-box:}
{The very wallaroos in fright went scrambling up the rocks,}
{The wombats hiding in their caves dug deeper underground,}
{As Mulga Bill, as white as chalk, sat tight to every bound.}
{It struck a stone and gave a spring that cleared a fallen tree,}
{It raced beside a precipice as close as close could be;}
{And then as Mulga Bill let out one last despairing shriek}
{It made a leap of twenty feet into the Dead Man's Creek.}
{It shaved a stump by half an inch, it dodged a big white-box:}
{The very wallaroos in fright went scrambling up the rocks,}
{The wombats hiding in their caves dug deeper underground,}
} {
execsql { INSERT INTO x1 VALUES($L) }
}
execsql {
SELECT level, end_block, length(root) FROM x1_segdir;
}
} {1 {224 921} 2 1 {226 1230} 7 0 {0 98} 98}
do_execsql_test 1.7 {
SELECT sum(length(block)) FROM x1_segments WHERE blockid IN (224,225,226)
} {1230}
#-------------------------------------------------------------------------
#
do_execsql_test 2.1 {
CREATE TABLE t1(docid, words);
CREATE VIRTUAL TABLE x2 USING fts4;
}
fts_kjv_genesis
do_test 2.2 {
foreach id [db eval {SELECT docid FROM t1}] {
execsql {
INSERT INTO x2(docid, content) SELECT $id, words FROM t1 WHERE docid=$id
}
}
foreach id [db eval {SELECT docid FROM t1}] {
execsql {
INSERT INTO x2(docid, content) SELECT NULL, words FROM t1 WHERE docid=$id
}
if {[db one {SELECT count(*) FROM x2_segdir WHERE level<2}]==2} break
}
} {}
do_execsql_test 2.3 {
SELECT count(*) FROM x2_segdir WHERE level=2;
SELECT count(*) FROM x2_segdir WHERE level=3;
} {6 0}
do_execsql_test 2.4 {
INSERT INTO x2(x2) VALUES('merge=4,4');
SELECT count(*) FROM x2_segdir WHERE level=2;
SELECT count(*) FROM x2_segdir WHERE level=3;
} {6 1}
do_execsql_test 2.5 {
SELECT end_block FROM x2_segdir WHERE level=3;
INSERT INTO x2(x2) VALUES('merge=4,4');
SELECT end_block FROM x2_segdir WHERE level=3;
INSERT INTO x2(x2) VALUES('merge=4,4');
SELECT end_block FROM x2_segdir WHERE level=3;
} {{5588 -3950} {5588 -11766} {5588 -15541}}
do_execsql_test 2.6 {
SELECT sum(length(block)) FROM x2_segdir, x2_segments WHERE
blockid BETWEEN start_block AND leaves_end_block
AND level=3
} {15541}
do_execsql_test 2.7 {
INSERT INTO x2(x2) VALUES('merge=1000,4');
SELECT end_block FROM x2_segdir WHERE level=3;
} {{5588 127563}}
do_execsql_test 2.8 {
SELECT sum(length(block)) FROM x2_segdir, x2_segments WHERE
blockid BETWEEN start_block AND leaves_end_block
AND level=3
} {127563}
#--------------------------------------------------------------------------
# Test that delete markers are removed from FTS segments when possible.
# It is only possible to remove delete markers when the output of the
# merge operation will become the oldest segment in the index.
#
# 3.1 - when the oldest segment is created by an 'optimize'.
# 3.2 - when the oldest segment is created by an incremental merge.
# 3.3 - by a crisis merge.
#
proc insert_doc {args} {
foreach iDoc $args {
set L [lindex {
{In your eagerness to engage the Trojans,}
{dont any of you charge ahead of others,}
{trusting in your strength and horsemanship.}
{And dont lag behind. That will hurt our charge.}
{Any man whose chariot confronts an enemys}
{should thrust with his spear at him from there.}
{Thats the most effective tactic, the way}
{men wiped out city strongholds long ago —}
{their chests full of that style and spirit.}
} [expr $iDoc%9]]
execsql { REPLACE INTO x3(docid, content) VALUES($iDoc, $L) }
}
}
proc delete_doc {args} {
foreach iDoc $args {
execsql { DELETE FROM x3 WHERE docid = $iDoc }
}
}
proc second {x} { lindex $x 1 }
db func second second
do_execsql_test 3.0 { CREATE VIRTUAL TABLE x3 USING fts4 }
do_test 3.1.1 {
db transaction { insert_doc 1 2 3 4 5 6 }
execsql { SELECT level, idx, second(end_block) FROM x3_segdir }
} {0 0 412}
do_test 3.1.2 {
delete_doc 1 2 3 4 5 6
execsql { SELECT count(*) FROM x3_segdir }
} {0}
do_test 3.1.3 {
db transaction {
insert_doc 1 2 3 4 5 6 7 8 9
delete_doc 9 8 7
}
execsql { SELECT level, idx, second(end_block) FROM x3_segdir }
} {0 0 591 0 1 65 0 2 72 0 3 76}
do_test 3.1.4 {
execsql { INSERT INTO x3(x3) VALUES('optimize') }
execsql { SELECT level, idx, second(end_block) FROM x3_segdir }
} {0 0 412}
do_test 3.2.1 {
execsql { DELETE FROM x3 }
insert_doc 8 7 6 5 4 3 2 1
delete_doc 7 8
execsql { SELECT count(*) FROM x3_segdir }
} {10}
do_test 3.2.2 {
execsql { INSERT INTO x3(x3) VALUES('merge=500,10') }
execsql { SELECT level, idx, second(end_block) FROM x3_segdir }
} {1 0 412}
# This assumes the crisis merge happens when there are already 16
# segments and one more is added.
#
do_test 3.3.1 {
execsql { DELETE FROM x3 }
insert_doc 1 2 3 4 5 6 7 8 9 10 11
delete_doc 11 10 9 8 7
execsql { SELECT count(*) FROM x3_segdir }
} {16}
do_test 3.3.2 {
insert_doc 12
execsql { SELECT level, idx, second(end_block) FROM x3_segdir WHERE level=1 }
} {1 0 412}
#--------------------------------------------------------------------------
# Check a theory on a bug in fts4 - that segments with idx==0 were not
# being incrementally merged correctly. Theory turned out to be false.
#
do_execsql_test 4.1 {
DROP TABLE IF EXISTS x4;
DROP TABLE IF EXISTS t1;
CREATE TABLE t1(docid, words);
CREATE VIRTUAL TABLE x4 USING fts4(words);
}
do_test 4.2 {
fts_kjv_genesis
execsql { INSERT INTO x4 SELECT words FROM t1 }
execsql { INSERT INTO x4 SELECT words FROM t1 }
} {}
do_execsql_test 4.3 {
SELECT level, idx, second(end_block) FROM x4_segdir
} {0 0 117483 0 1 118006}
do_execsql_test 4.4 {
INSERT INTO x4(x4) VALUES('merge=10,2');
SELECT count(*) FROM x4_segdir;
} {3}
do_execsql_test 4.5 {
INSERT INTO x4(x4) VALUES('merge=10,2');
SELECT count(*) FROM x4_segdir;
} {3}
do_execsql_test 4.6 {
INSERT INTO x4(x4) VALUES('merge=1000,2');
SELECT count(*) FROM x4_segdir;
} {1}
#--------------------------------------------------------------------------
# Check that segments are not promoted if the "end_block" field does not
# contain a size.
#
do_execsql_test 5.1 {
DROP TABLE IF EXISTS x2;
DROP TABLE IF EXISTS t1;
CREATE TABLE t1(docid, words);
CREATE VIRTUAL TABLE x2 USING fts4;
}
fts_kjv_genesis
proc first {L} {lindex $L 0}
db func first first
do_test 5.2 {
foreach r [db eval { SELECT rowid FROM t1 }] {
execsql {
INSERT INTO x2(docid, content) SELECT docid, words FROM t1 WHERE rowid=$r
}
}
foreach d [db eval { SELECT docid FROM t1 LIMIT -1 OFFSET 20 }] {
execsql { DELETE FROM x2 WHERE docid = $d }
}
execsql {
INSERT INTO x2(x2) VALUES('optimize');
SELECT level, idx, end_block FROM x2_segdir
}
} {2 0 {752 1926}}
do_execsql_test 5.3 {
UPDATE x2_segdir SET end_block = CAST( first(end_block) AS INTEGER );
SELECT end_block, typeof(end_block) FROM x2_segdir;
} {752 integer}
do_execsql_test 5.4 {
INSERT INTO x2 SELECT words FROM t1 LIMIT 50;
SELECT level, idx, end_block FROM x2_segdir
} {2 0 752 0 0 {758 5174}}
do_execsql_test 5.5 {
UPDATE x2_segdir SET end_block = end_block || ' 1926' WHERE level=2;
INSERT INTO x2 SELECT words FROM t1 LIMIT 40;
SELECT level, idx, end_block FROM x2_segdir
} {0 0 {752 1926} 0 1 {758 5174} 0 2 {763 4170}}
proc t1_to_x2 {} {
foreach id [db eval {SELECT docid FROM t1 LIMIT 2}] {
execsql {
DELETE FROM x2 WHERE docid=$id;
INSERT INTO x2(docid, content) SELECT $id, words FROM t1 WHERE docid=$id;
}
}
}
#--------------------------------------------------------------------------
# Check that segments created by auto-merge are not promoted until they
# are completed.
#
do_execsql_test 6.1 {
CREATE VIRTUAL TABLE x5 USING fts4;
INSERT INTO x5 SELECT words FROM t1 LIMIT 100 OFFSET 0;
INSERT INTO x5 SELECT words FROM t1 LIMIT 100 OFFSET 25;
INSERT INTO x5 SELECT words FROM t1 LIMIT 100 OFFSET 50;
INSERT INTO x5 SELECT words FROM t1 LIMIT 100 OFFSET 75;
SELECT count(*) FROM x5_segdir
} {4}
do_execsql_test 6.2 {
INSERT INTO x5(x5) VALUES('merge=2,4');
SELECT level, idx, end_block FROM x5_segdir;
} {0 0 {10 9216} 0 1 {21 9330} 0 2 {31 8850} 0 3 {40 8689} 1 0 {1320 -3117}}
do_execsql_test 6.3 {
INSERT INTO x5 SELECT words FROM t1 LIMIT 100 OFFSET 100;
SELECT level, idx, end_block FROM x5_segdir;
} {
0 0 {10 9216} 0 1 {21 9330} 0 2 {31 8850}
0 3 {40 8689} 1 0 {1320 -3117} 0 4 {1329 8297}
}
do_execsql_test 6.4 {
INSERT INTO x5(x5) VALUES('merge=200,4');
SELECT level, idx, end_block FROM x5_segdir;
} {0 0 {1329 8297} 1 0 {1320 28009}}
do_execsql_test 6.5 {
INSERT INTO x5 SELECT words FROM t1;
SELECT level, idx, end_block FROM x5_segdir;
} {
0 1 {1329 8297} 0 0 {1320 28009} 0 2 {1449 118006}
}
#--------------------------------------------------------------------------
# Ensure that if part of an incremental merge is performed by an old
# version that does not support storing segment sizes in the end_block
# field, no size is stored in the final segment (as it would be incorrect).
#
do_execsql_test 7.1 {
CREATE VIRTUAL TABLE x6 USING fts4;
INSERT INTO x6 SELECT words FROM t1;
INSERT INTO x6 SELECT words FROM t1;
INSERT INTO x6 SELECT words FROM t1;
INSERT INTO x6 SELECT words FROM t1;
INSERT INTO x6 SELECT words FROM t1;
INSERT INTO x6 SELECT words FROM t1;
SELECT level, idx, end_block FROM x6_segdir;
} {
0 0 {118 117483} 0 1 {238 118006} 0 2 {358 118006}
0 3 {478 118006} 0 4 {598 118006} 0 5 {718 118006}
}
do_execsql_test 7.2 {
INSERT INTO x6(x6) VALUES('merge=25,4');
SELECT level, idx, end_block FROM x6_segdir;
} {
0 0 {118 117483} 0 1 {238 118006} 0 2 {358 118006}
0 3 {478 118006} 0 4 {598 118006} 0 5 {718 118006}
1 0 {23694 -69477}
}
do_execsql_test 7.3 {
UPDATE x6_segdir SET end_block = first(end_block) WHERE level=1;
SELECT level, idx, end_block FROM x6_segdir;
} {
0 0 {118 117483} 0 1 {238 118006} 0 2 {358 118006}
0 3 {478 118006} 0 4 {598 118006} 0 5 {718 118006}
1 0 23694
}
do_execsql_test 7.4 {
INSERT INTO x6(x6) VALUES('merge=25,4');
SELECT level, idx, end_block FROM x6_segdir;
} {
0 0 {118 117483} 0 1 {238 118006} 0 2 {358 118006}
0 3 {478 118006} 0 4 {598 118006} 0 5 {718 118006}
1 0 23694
}
do_execsql_test 7.5 {
INSERT INTO x6(x6) VALUES('merge=2500,4');
SELECT level, idx, start_block, leaves_end_block, end_block FROM x6_segdir;
} {
1 0 719 1171 23694
}
do_execsql_test 7.6 {
INSERT INTO x6(x6) VALUES('merge=2500,2');
SELECT level, idx, start_block, leaves_end_block, end_block FROM x6_segdir;
} {
1 0 719 1171 23694
}
do_execsql_test 7.7 {
SELECT sum(length(block)) FROM x6_segments
} {635247}
finish_test