Debian Codegraph @

mysql-test/suite/innodb_fts/t/plugin.test

raw
1 --source include/have_simple_parser.inc
2
3 # Install fts parser plugin
4 --replace_regex /\.dll/.so/
5 eval INSTALL PLUGIN simple_parser SONAME '$SIMPLE_PARSER';
6
7 -- echo # Test Part 1: Grammar Test
8 # Create a myisam table and alter it to innodb table
9 CREATE TABLE articles (
10 id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
11 title VARCHAR(200),
12 FULLTEXT (title) WITH PARSER simple_parser
13 ) ENGINE=MyISAM;
14
15 ALTER TABLE articles ENGINE=InnoDB;
16
17 DROP TABLE articles;
18
19 # Create a table having a full text index with parser
20 CREATE TABLE articles (
21 id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
22 title VARCHAR(200),
23 body TEXT,
24 comment TEXT,
25 FULLTEXT (title) WITH PARSER simple_parser
26 ) ENGINE=InnoDB;
27
28 # Alter table to add a full text index with parser
29 ALTER TABLE articles ADD FULLTEXT INDEX (body) WITH PARSER simple_parser;
30
31 # Create a full text index with parser
32 CREATE FULLTEXT INDEX ft_index ON articles(comment) WITH PARSER simple_parser;
33
34 DROP TABLE articles;
35
36 -- echo # Test Part 2: Create Index Test(CREATE TABLE WITH FULLTEXT INDEX)
37 CREATE TABLE articles (
38 id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
39 title VARCHAR(200),
40 body TEXT,
41 FULLTEXT (title, body) WITH PARSER simple_parser
42 ) ENGINE=InnoDB;
43
44 INSERT INTO articles (title, body) VALUES
45 ('MySQL Tutorial','DBMS stands for MySQL DataBase ...'),
46 ('How To Use MySQL Well','After you went through a ...'),
47 ('Optimizing MySQL','In this tutorial we will show ...'),
48 ('1001 MySQL Tricks','How to use full-text search engine'),
49 ('Go MySQL Tricks','How to use full text search engine');
50
51 # Simple term search
52 SELECT * FROM articles WHERE
53 MATCH(title, body) AGAINST('mysql');
54
55 # Test stopword and word len less than fts_min_token_size
56 SELECT * FROM articles WHERE
57 MATCH(title, body) AGAINST('will go');
58
59 -- echo # Test plugin parser tokenizer difference
60 SELECT * FROM articles WHERE
61 MATCH(title, body) AGAINST('full-text');
62
63 SELECT * FROM articles WHERE
64 MATCH(title, body) AGAINST('full text');
65
66 # No result here, we get '"mysql' 'database"' by simple parser
67 SELECT * FROM articles WHERE
68 MATCH(title, body) AGAINST('"mysql database"' IN BOOLEAN MODE);
69
70 DROP TABLE articles;
71
72 -- echo # Test Part 3: Row Merge Create Index Test(ALTER TABLE ADD FULLTEXT INDEX)
73 CREATE TABLE articles (
74 id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
75 title VARCHAR(200),
76 body TEXT
77 ) ENGINE=InnoDB;
78
79 INSERT INTO articles (title, body) VALUES
80 ('MySQL Tutorial','DBMS stands for MySQL DataBase ...'),
81 ('How To Use MySQL Well','After you went through a ...'),
82 ('Optimizing MySQL','In this tutorial we will show ...'),
83 ('1001 MySQL Tricks','How to use full-text search engine'),
84 ('Go MySQL Tricks','How to use full text search engine');
85
86 # Create fulltext index
87 ALTER TABLE articles ADD FULLTEXT INDEX (title, body) WITH PARSER simple_parser;
88
89 # Simple term search
90 SELECT * FROM articles WHERE
91 MATCH(title, body) AGAINST('mysql');
92
93 # Test stopword and word len less than fts_min_token_size
94 SELECT * FROM articles WHERE
95 MATCH(title, body) AGAINST('will go');
96
97 -- echo # Test plugin parser tokenizer difference
98 SELECT * FROM articles WHERE
99 MATCH(title, body) AGAINST('full-text');
100
101 SELECT * FROM articles WHERE
102 MATCH(title, body) AGAINST('full text');
103
104 # Test query expansion
105 --sorted_result
106 SELECT * FROM articles WHERE
107 MATCH(title, body) AGAINST('full-text' WITH QUERY EXPANSION);
108
109 --sorted_result
110 SELECT * FROM articles WHERE
111 MATCH(title, body) AGAINST('full text' WITH QUERY EXPANSION);
112
113 # No result here, we get '"mysql' 'database"' by simple parser
114 SELECT * FROM articles WHERE
115 MATCH(title, body) AGAINST('"mysql database"' IN BOOLEAN MODE);
116
117 DROP TABLE articles;
118 -- echo # Test Part 3 END
119
120 -- echo # Test Part 4:crash on commit(before/after)
121 CREATE TABLE articles (
122 id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
123 title VARCHAR(200),
124 body TEXT,
125 FULLTEXT (title, body) WITH PARSER simple_parser
126 ) ENGINE=InnoDB;
127
128 BEGIN;
129 INSERT INTO articles (title, body) VALUES
130 ('MySQL Tutorial','DBMS stands for MySQL DataBase ...'),
131 ('How To Use MySQL Well','After you went through a ...'),
132 ('Optimizing MySQL','In this tutorial we will show ...'),
133 ('1001 MySQL Tricks','How to use full-text search engine'),
134 ('Go MySQL Tricks','How to use full text search engine');
135
136 --source include/kill_and_restart_mysqld.inc
137
138 SELECT COUNT(*) FROM articles;
139 # Simple term search - no records expected
140 SELECT title, body FROM articles WHERE
141 MATCH(title, body) AGAINST('mysql');
142
143 INSERT INTO articles (title, body) VALUES
144 ('MySQL Tutorial','DBMS stands for MySQL DataBase ...'),
145 ('How To Use MySQL Well','After you went through a ...'),
146 ('Optimizing MySQL','In this tutorial we will show ...'),
147 ('1001 MySQL Tricks','How to use full-text search engine'),
148 ('Go MySQL Tricks','How to use full text search engine');
149
150 --source include/kill_and_restart_mysqld.inc
151
152 # Simple term search - 4 records expected
153 SELECT title, body FROM articles WHERE
154 MATCH(title, body) AGAINST('Tricks');
155 SELECT COUNT(*) FROM articles;
156 DROP TABLE articles;
157
158 -- echo # Test Part 5: Test Uninstall Plugin After Index is Built
159 # Note: this test should be the last one because we uninstall plugin
160 CREATE TABLE articles (
161 id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
162 title VARCHAR(200),
163 body TEXT,
164 FULLTEXT (title, body) WITH PARSER simple_parser
165 ) ENGINE=InnoDB;
166
167 # Uninstall plugin
168 UNINSTALL PLUGIN simple_parser;
169
170 -- error ER_PLUGIN_IS_NOT_LOADED
171 INSERT INTO articles (title, body) VALUES
172 ('MySQL Tutorial','DBMS stands for MySQL DataBase ...');
173
174 # Reinstall plugin
175 --replace_regex /\.dll/.so/
176 eval INSTALL PLUGIN simple_parser SONAME '$SIMPLE_PARSER';
177
178 INSERT INTO articles (title, body) VALUES
179 ('MySQL Tutorial','DBMS stands for MySQL DataBase ...'),
180 ('How To Use MySQL Well','After you went through a ...'),
181 ('Optimizing MySQL','In this tutorial we will show ...'),
182 ('1001 MySQL Tricks','How to use full-text search engine'),
183 ('Go MySQL Tricks','How to use full text search engine');
184
185 # Get warning here
186 UNINSTALL PLUGIN simple_parser;
187
188 # Simple term search
189 SELECT * FROM articles WHERE
190 MATCH(title, body) AGAINST('mysql');
191
192 # Test stopword and word len less than fts_min_token_size
193 SELECT * FROM articles WHERE
194 MATCH(title, body) AGAINST('will go');
195
196 -- echo # Test plugin parser tokenizer difference
197 SELECT * FROM articles WHERE
198 MATCH(title, body) AGAINST('full-text');
199
200 SELECT * FROM articles WHERE
201 MATCH(title, body) AGAINST('full text');
202
203 -- error ER_FUNCTION_NOT_DEFINED
204 CREATE TABLE articles2 (
205 id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
206 title VARCHAR(200),
207 body TEXT,
208 FULLTEXT (title, body) WITH PARSER simple_parser
209 ) ENGINE=InnoDB;
210
211 DROP TABLE articles;
212 # Uninstall plugin
213 -- error ER_SP_DOES_NOT_EXIST
214 UNINSTALL PLUGIN simple_parser;