simple/example.sql at master · wangfenjin/simple · GitHub

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
-- Examples to use simple tokenizer

-- load so file
.load libsimple

select '自定义拼音词典示例（只包含 周/伦 的拼音）:';
-- 在本仓库里从 output/bin 运行本例时，路径可使用 ../../contrib/pinyin-mini.txt
select pinyin_dict('../../contrib/pinyin-mini.txt');
CREATE VIRTUAL TABLE t0 USING fts5(x, tokenize = 'simple');
insert into t0(x) values ('周杰伦');
select '    搜索 zhou，命中数量（预期 1）:', count(*) from t0 where x match simple_query('zhou');
select '    搜索 lun，命中数量（预期 1）:', count(*) from t0 where x match simple_query('lun');
select '    搜索 jie，命中数量（预期 0）:', count(*) from t0 where x match simple_query('jie');
select '    搜索 zhou lun，命中数量（预期 1）:', count(*) from t0 where x match simple_query('zhou lun');
drop table t0;
-- 切回默认词典，保证下面示例行为不变
select pinyin_dict('../../contrib/pinyin.txt');

select '启用拼音分词：';
-- set tokenize to simple
CREATE VIRTUAL TABLE t1 USING fts5(x, tokenize = 'simple');

-- add some values into the table
insert into t1(x) values ('周杰伦 Jay Chou:最美的不是下雨天，是曾与你躲过雨的屋檐'),
                         ('I love China! 我爱中国!'),
                         ('@English &special _characters."''bacon-&and''-eggs%');
select '所有数据:';
select '    ', * from t1;

select '特殊字符:';
select '    ', simple_highlight(t1, 0, '[', ']') from t1 where x match simple_query('@"''._-&%');
select '    ', simple_highlight(t1, 0, '[', ']') from t1 where x match '"''"';

select '搜索 杰伦:';
select '    ', simple_highlight(t1, 0, '[', ']') from t1 where x match simple_query('杰伦');
select '    ', simple_snippet(t1, 0, '[', ']', '...', 1) from t1 where x match simple_query('杰伦');
select '    ', simple_snippet(t1, 0, '[', ']', '...', 2) from t1 where x match simple_query('杰伦');
select '    ', simple_snippet(t1, 0, '[', ']', '...', 3) from t1 where x match simple_query('杰伦');
select '    ', simple_snippet(t1, 0, '[', ']', '...', 4) from t1 where x match simple_query('杰伦');
select '    ', simple_snippet(t1, 0, '[', ']', '...', 5) from t1 where x match simple_query('杰伦');
select '    ', simple_snippet(t1, 0, '[', ']', '...', 10) from t1 where x match simple_query('杰伦');
select '    ', simple_snippet(t1, 0, '[', ']', '...', 20) from t1 where x match simple_query('杰伦');
select '    ', simple_snippet(t1, 0, '[', ']', '...', 100) from t1 where x match simple_query('杰伦');
select '    ', simple_highlight_pos(t1, 0) from t1 where x match simple_query('杰伦');

select '搜索 雨天:';
select '    ', simple_snippet(t1, 0, '[', ']', '...', 10) from t1 where x match simple_query('雨天');

select '搜索 zhoujiel:';
select '    ', simple_highlight(t1, 0, '[', ']') from t1 where x match simple_query('zhoujiel');
select '    ', simple_highlight_pos(t1, 0) from t1 where x match simple_query('zhoujiel');

select '搜索 zhoujie:';
select '    ', simple_highlight(t1, 0, '[', ']') from t1 where x match simple_query('zhoujie');
-- will not match
select '    !!!!! should not match', simple_highlight_pos(t1, 0) from t1 where x match simple_query('jiezhou');


select '搜索 zjl:';
select '    ', simple_highlight(t1, 0, '[', ']') from t1 where x match simple_query('zjl');
select '    ', simple_highlight_pos(t1, 0) from t1 where x match simple_query('zjl');

select '搜索 ZHOUJi:';
select '    ', simple_highlight(t1, 0, '[', ']') from t1 where x match simple_query('ZHOUJi');
select '    ', simple_highlight_pos(t1, 0) from t1 where x match simple_query('ZHOUJi');

select '搜索 love zg:';
select '    ', simple_highlight(t1, 0, '[', ']') from t1 where x match simple_query('love zg');
select '    ', simple_highlight_pos(t1, 0) from t1 where x match simple_query('love zg');

select '';
select '';
select '--------------------------------------------------------------------------------';
select '禁用拼音分词：';
-- set tokenize to simple, 0 means disable pinyin
CREATE VIRTUAL TABLE t2 USING fts5(x, tokenize = 'simple 0');

-- add some values into the table
insert into t2(x) values ('周杰伦 Jay Chou:最美的不是下雨天，是曾与你躲过雨的屋檐'),
                      ('I love China! 我爱中国!') ;
select '所有数据:';
select '    ', * from t2;

select '搜索 杰伦:';
-- in simple_query, we accept a second params, '0' means disable pinyin split
select '    ', simple_highlight(t2, 0, '[', ']') from t2 where x match simple_query('杰伦', '0');
select '    ', simple_snippet(t2, 0, '[', ']', '...', 1) from t2 where x match simple_query('杰伦', '0');
select '    ', simple_snippet(t2, 0, '[', ']', '...', 2) from t2 where x match simple_query('杰伦', '0');
select '    ', simple_snippet(t2, 0, '[', ']', '...', 3) from t2 where x match simple_query('杰伦', '0');
select '    ', simple_snippet(t2, 0, '[', ']', '...', 4) from t2 where x match simple_query('杰伦', '0');
select '    ', simple_snippet(t2, 0, '[', ']', '...', 5) from t2 where x match simple_query('杰伦', '0');
select '    ', simple_snippet(t2, 0, '[', ']', '...', 10) from t2 where x match simple_query('杰伦', '0');
select '    ', simple_snippet(t2, 0, '[', ']', '...', 20) from t2 where x match simple_query('杰伦', '0');
select '    ', simple_snippet(t2, 0, '[', ']', '...', 100) from t2 where x match simple_query('杰伦', '0');
select '    ', simple_highlight_pos(t2, 0) from t2 where x match simple_query('杰伦', '0');

select '搜索 雨天:';
select '    ', simple_snippet(t2, 0, '[', ']', '...', 10) from t2 where x match simple_query('雨天', '0');

select '搜索 zhoujiel:';
select '    ', simple_highlight(t2, 0, '[', ']') from t2 where x match simple_query('zhoujiel', '0');
select '    ', simple_highlight_pos(t2, 0) from t2 where x match simple_query('zhoujiel', '0');
select '    !!!!! should not match', simple_highlight_pos(t1, 0) from t1 where x match simple_query('jiezhou', '0');

select '搜索 zjl:';
select '    ', simple_highlight(t2, 0, '[', ']') from t2 where x match simple_query('zjl', '0');
select '    ', simple_highlight_pos(t2, 0) from t2 where x match simple_query('zjl', '0');

select '搜索 ZHOUJi:';
select '    ', simple_highlight(t2, 0, '[', ']') from t2 where x match simple_query('ZHOUJi', '0');
select '    ', simple_highlight_pos(t2, 0) from t2 where x match simple_query('ZHOUJi', '0');

select '搜索 love zg:';
select '    ', simple_highlight(t2, 0, '[', ']') from t2 where x match simple_query('love zg', '0');
select '    ', simple_highlight_pos(t2, 0) from t2 where x match simple_query('love zg', '0');