Debian Codegraph @

mysql-test/suite/memcached/t/memc278_view.test

raw
1 source include/not_valgrind.inc;
2 source include/have_memcached_plugin.inc;
3 source include/not_windows.inc;
4
5 --disable_query_log
6 CALL mtr.add_suppression("daemon-memcached-w-batch-size': unsigned");
7 CALL mtr.add_suppression("Could not obtain server's UPN to be used as target service name");
8 CALL mtr.add_suppression("Warning: MySQL is trying to drop");
9 --enable_query_log
10
11 --enable_connect_log
12 SET @transaction_isolation= @@global.transaction_isolation;
13 SET GLOBAL TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
14
15 # Create the memcached tables
16 --disable_query_log
17 source include/memcache_config.inc;
18 --enable_query_log
19
20 INSERT INTO cache_policies VALUES("cache_policy", "innodb_only",
21 "innodb_only", "innodb_only", "innodb_only");
22
23 INSERT INTO config_options VALUES("separator", "|");
24 INSERT INTO config_options VALUES("table_map_delimiter", ".");
25
26 # describe table for memcache
27 INSERT INTO containers VALUES ("desct1", "test", "t1",
28 "c1", "c2,c21", "c3", "c4", "c5", "PRIMARY");
29 INSERT INTO containers VALUES ("desct2", "test", "t2",
30 "c1", "c2", "c3", "c4", "c5", "PRIMARY");
31 INSERT INTO containers VALUES ("desc_v1", "test", "v1",
32 "vc1", "vc2,vc21", "vc3", "vc4", "vc5", "PRIMARY");
33 INSERT INTO containers VALUES ("desct3", "test", "t3",
34 "c1", "c2,c21", "c3", "c4", "c5", "PRIMARY");
35 INSERT INTO containers VALUES ("desct4", "test", "t4",
36 "c1", "c2,c21,c22", "c3", "c4", "c5", "PRIMARY");
37 INSERT INTO containers VALUES ("desct5", "test", "t5",
38 "c1", "c2,c21,c22,c23", "c3", "c4", "c5", "PRIMARY");
39
40 USE test;
41
42 --disable_warnings
43 DROP TABLE IF EXISTS t1,t2;
44 --enable_warnings
45 CREATE TABLE t1 (c1 VARCHAR(32),
46 c2 VARCHAR(1024),
47 c21 VARCHAR(1024),
48 c3 INT, c4 BIGINT UNSIGNED, c5 INT, primary key(c1))
49 ENGINE = INNODB;
50
51 CREATE TABLE t2 (c1 VARCHAR(32),
52 c2 VARCHAR(1024),
53 c3 INT, c4 BIGINT UNSIGNED, c5 INT, primary key(c1))
54 ENGINE = INNODB;
55
56 CREATE TABLE t3 (c1 VARCHAR(32),
57 c2 VARCHAR(1024),
58 c21 VARCHAR(1024),
59 c3 INT, c4 BIGINT UNSIGNED, c5 INT, primary key(c1))
60 ENGINE = INNODB;
61
62 CREATE TABLE t4 (c1 VARCHAR(32),
63 c2 VARCHAR(1024),
64 c21 VARCHAR(1024),
65 c22 VARCHAR(1024),
66 c3 INT, c4 BIGINT UNSIGNED, c5 INT, primary key(c1))
67 ENGINE = INNODB;
68
69 CREATE TABLE t5 (c1 VARCHAR(32),
70 c2 VARCHAR(1024),
71 c21 VARCHAR(1024),
72 c22 VARCHAR(1024),
73 c23 VARCHAR(1024),
74 c3 INT, c4 BIGINT UNSIGNED, c5 INT, primary key(c1))
75 ENGINE = INNODB;
76
77 INSERT INTO t1 VALUES ('D', 'Darmstadt', 'City', 0, 0, 0);
78 INSERT INTO t1 VALUES ('B', 'Berlin', 'Mitte', 0, 0, 0);
79 INSERT INTO t1 VALUES ('C', 'Cottbus', 'West', 0, 0 ,0);
80 INSERT INTO t1 VALUES ('H', 'Hamburg', 'Norderstedt', 0, 0, 0);
81
82 INSERT INTO t2 VALUES ('D', 'Dortmund', 0, 0, 0);
83 INSERT INTO t2 VALUES ('B', 'Bremen', 0, 0, 0);
84 INSERT INTO t2 VALUES ('C', 'Celle', 0, 0 ,0);
85 INSERT INTO t2 VALUES ('H', 'Hannover', 0, 0, 0);
86
87 INSERT INTO t3 VALUES ('D', 'Detmold', 'City', 0, 0, 0);
88 INSERT INTO t3 VALUES ('B', 'Bonn', 'Mitte', 0, 0, 0);
89 INSERT INTO t3 VALUES ('A', 'Aachen', 'West', 0, 0 ,0);
90 INSERT INTO t3 VALUES ('H', 'Hameln', 'Nord', 0, 0, 0);
91
92 INSERT INTO t4 VALUES ('Z', 'Zossen', 'City', '12345', 0, 0, 0);
93 INSERT INTO t4 VALUES ('B', 'Brandenburg', 'Mitte', '23456', 0, 0, 0);
94 INSERT INTO t4 VALUES ('M', 'Muenchen', 'West', '34567', 0, 0 ,0);
95 INSERT INTO t4 VALUES ('H', 'Husum', 'Nord', '45678', 0, 0, 0);
96
97 INSERT INTO t5 VALUES ('D', 'Duesseldorf', 'City', '98765', 'Hafen', 0, 0, 0);
98 INSERT INTO t5 VALUES ('O', 'Oldenburg', 'Mitte', '87654', 'Deich', 0, 0, 0);
99 INSERT INTO t5 VALUES ('K', 'Koeln', 'West', '76543', 'Rhein', 0, 0 ,0);
100 INSERT INTO t5 VALUES ('S', 'Stuttgart', 'Nord', '65432', 'Fernsehturm', 0, 0, 0);
101
102 CREATE VIEW v1 AS SELECT t1.c1 AS vc1,t1.c2 AS vc2,t3.c21 AS vc21,
103 t1.c3 AS vc3,t1.c4 AS vc4,t1.c5 AS vc5 FROM t1,t3 WHERE t1.c1=t3.c1;
104
105 # Tables must exist before plugin can be started!
106 --let $memcached_address=127.0.0.1:11278
107 --source ../include/load_daemon_memcached_expecting_success.inc
108
109 --sorted_result
110 SELECT c1,c2,c21 FROM t1;
111 --sorted_result
112 SELECT c1,c2 FROM t2;
113 --sorted_result
114 SELECT c1,c2,c21 FROM t3;
115 --sorted_result
116 SELECT c1,c2,c21,c22 FROM t4;
117 --sorted_result
118 SELECT c1,c2,c21,c22,c23 FROM t5;
119
120
121 perl;
122 use DBI;
123 use Cache::Memcached;
124 my $memd = new Cache::Memcached {
125 'servers' => [ "127.0.0.1:11278" ],
126 'connect_timeout' => 20,
127 'select_timeout' => 20
128 };
129 print "Here are the memcached results with D,B,H,C of default:\n";
130 $val = $memd->get("D");
131 if ($val) { print "$val\n"; }
132 $val = $memd->get("B");
133 if ($val) { print "$val\n"; }
134 $val = $memd->get("H");
135 if ($val) { print "$val\n"; }
136 $val = $memd->get("C");
137 if ($val) { print "$val\n"; }
138 $memd->disconnect_all;
139 EOF
140
141 perl;
142 use DBI;
143 use Cache::Memcached;
144 my $memd = new Cache::Memcached {
145 'servers' => [ "127.0.0.1:11278" ],
146 'connect_timeout' => 20,
147 'select_timeout' => 20
148 };
149 print "Here are the memcached results with D,B,H,C of t1:\n";
150 $val = $memd->get('@@desct1.D');
151 if ($val) { print "$val\n"; }
152 $val = $memd->get("\@\@desct1.B");
153 if ($val) { print "$val\n"; }
154 $val = $memd->get('@@desct1.'.'H');
155 if ($val) { print "$val\n"; }
156 $val = $memd->get('@@desct1.C');
157 if ($val) { print "$val\n"; }
158 print "Here are the memcached results with D,B,H,C of t2:\n";
159 $val = $memd->get("\@\@desct2");
160 $val = $memd->get("D");
161 if ($val) { print "$val\n"; }
162 $val = $memd->get('@@desct2.B');
163 if ($val) { print "$val\n"; }
164 $val = $memd->get('H');
165 if ($val) { print "$val\n"; }
166 $val = $memd->get('C');
167 if ($val) { print "$val\n"; }
168 print "Here are the memcached results with D,B,H,A of t3:\n";
169 $val = $memd->get("\@\@desct3.D");
170 if ($val) { print "$val\n"; }
171 $val = $memd->get('@@desct3.B');
172 if ($val) { print "$val\n"; }
173 $val = $memd->get('@@desct3.H');
174 if ($val) { print "$val\n"; }
175 $val = $memd->get('@@desct3.A');
176 if ($val) { print "$val\n"; }
177 print "Here are the memcached results with D,O,K,S of t5:\n";
178 $val = $memd->get("\@\@desct5.D");
179 if ($val) { print "$val\n"; }
180 $val = $memd->get('@@desct5.O');
181 if ($val) { print "$val\n"; }
182 $val = $memd->get('@@desct5.K');
183 if ($val) { print "$val\n"; }
184 $val = $memd->get('@@desct5.S');
185 if ($val) { print "$val\n"; }
186 print "Here are the memcached results with Z,B,H,M of t4:\n";
187 $val = $memd->get("\@\@desct4.Z");
188 if ($val) { print "$val\n"; }
189 $val = $memd->get('@@desct4.B');
190 if ($val) { print "$val\n"; }
191 $val = $memd->get('@@desct4.H');
192 if ($val) { print "$val\n"; }
193 $val = $memd->get('@@desct4.M');
194 if ($val) { print "$val\n"; }
195 print "Here are the memcached results with D,B,H,C of t2:\n";
196 $memd->get("\@\@desct2");
197 $val = $memd->get("D");
198 if ($val) { print "$val\n"; }
199 $val = $memd->get('B');
200 if ($val) { print "$val\n"; }
201 $val = $memd->get('H');
202 if ($val) { print "$val\n"; }
203 $val = $memd->get('C');
204 if ($val) { print "$val\n"; }
205 $memd->disconnect_all;
206 EOF
207
208 --sorted_result
209 SELECT c1,c2,c21 FROM t1;
210 --sorted_result
211 SELECT c1,c2 FROM t2;
212 --sorted_result
213 SELECT c1,c2,c21 FROM t3;
214 --sorted_result
215 SELECT c1,c2,c21,c22 FROM t4;
216 --sorted_result
217 SELECT c1,c2,c21,c22,c23 FROM t5;
218
219 --sorted_result
220 SELECT vc1,vc2,vc21 FROM v1;
221
222 perl;
223 use DBI;
224 use Cache::Memcached;
225 my $memd = new Cache::Memcached {
226 'servers' => [ "127.0.0.1:11278" ],
227 'connect_timeout' => 20,
228 'select_timeout' => 20
229 };
230 print "Here are the memcached results with D,B,H,C of view v1:\n";
231 $val = $memd->get("\@\@desc_v1.D");
232 if ($val) { print "$val\n"; }
233 $val = $memd->get("\@\@desc_v1.B");
234 if ($val) { print "$val\n"; }
235 $val = $memd->get('@@desc_v1.H');
236 if ($val) { print "$val\n"; }
237 $val = $memd->get('@@desc_v1.C');
238 if ($val) { print "$val\n"; }
239 $memd->disconnect_all;
240 EOF
241
242 perl;
243 use DBI;
244 use Cache::Memcached;
245 my $memd = new Cache::Memcached {
246 'servers' => [ "127.0.0.1:11278" ],
247 'connect_timeout' => 20,
248 'select_timeout' => 20
249 };
250 print "Here are the memcached results after set to view v1:\n";
251 $val = $memd->set("\@\@desc_v1.E","Essen|Ost");
252 $val = $memd->get("\@\@desc_v1.E");
253 if ($val) { print "$val\n"; }
254 $memd->disconnect_all;
255 EOF
256
257 --sorted_result
258 SELECT vc1,vc2,vc21 FROM v1;
259
260 perl;
261 use DBI;
262 use Cache::Memcached;
263 my $memd = new Cache::Memcached {
264 'servers' => [ "127.0.0.1:11278" ],
265 'connect_timeout' => 20,
266 'select_timeout' => 20
267 };
268 print "Here are the memcached results after set in default:\n";
269 $val = $memd->set("E","Essen|Ost");
270 $val = $memd->get("E");
271 if ($val) { print "$val\n"; }
272 print "Here are the memcached results after set in t2:\n";
273 $val = $memd->set("\@\@desct2.E","Essen");
274 $val = $memd->get("\@\@desct2.E");
275 if ($val) { print "$val\n"; }
276 print "Here are the memcached results after set in t5:\n";
277 $val = $memd->get("\@\@desct5");
278 $val = $memd->set("E","Essen|Mitte|111111|Mahlzeit");
279 $val = $memd->get("E");
280 if ($val) { print "$val\n"; }
281 print "Here are the memcached results after set in t3:\n";
282 $val = $memd->set('@@desct3.E',"Essen|Ost");
283 $memd->get('@@desct3');
284 $val = $memd->get("E");
285 if ($val) { print "$val\n"; }
286 print "Here are the memcached results after set in t4:\n";
287 $val = $memd->set('@@desct4.E',"Essen|Mitte|111111");
288 $val = $memd->get('@@desct4.E');
289 if ($val) { print "$val\n"; }
290 print "Here are the memcached results after set in t5:\n";
291 $val = $memd->get("\@\@desct5");
292 $val = $memd->set("\@\@desct5.E","Essen|Aussen|111111|Mahlzeit");
293 $val = $memd->get("\@\@desct5.E");
294 if ($val) { print "$val\n"; }
295 $memd->disconnect_all;
296 EOF
297
298 --sorted_result
299 SELECT vc1,vc2,vc21 FROM v1;
300
301 --sorted_result
302 SELECT c1,c2,c21 FROM t1;
303 --sorted_result
304 SELECT c1,c2 FROM t2;
305 --sorted_result
306 SELECT c1,c2,c21 FROM t3;
307 --sorted_result
308 SELECT c1,c2,c21,c22 FROM t4;
309 --sorted_result
310 SELECT c1,c2,c21,c22,c23 FROM t5;
311
312 DROP VIEW v1;
313 DROP TABLE t1,t2,t3,t4,t5;
314
315 UNINSTALL PLUGIN daemon_memcached;
316 DROP DATABASE innodb_memcache;
317
318 SET @@global.transaction_isolation= @transaction_isolation;