1# vi:filetype=
2
3use lib 'lib';
4use Test::Nginx::Socket;
5
6#repeat_each(10);
7no_shuffle();
8
9repeat_each(2);
10
11plan tests => repeat_each() * (2 * blocks() + 7);
12
13$ENV{TEST_NGINX_MYSQL_PORT} ||= 3306;
14
15our $http_config = <<'_EOC_';
16    upstream backend {
17        drizzle_server 127.0.0.1:$TEST_NGINX_MYSQL_PORT protocol=mysql
18                       dbname=ngx_test user=ngx_test password=ngx_test;
19    }
20_EOC_
21
22no_long_string();
23no_diff();
24
25run_tests();
26
27__DATA__
28
29=== TEST 1: sanity
30--- http_config eval: $::http_config
31--- config
32    location /mysql {
33        drizzle_pass backend;
34        #drizzle_dbname $dbname;
35        drizzle_query 'select * from cats';
36        rds_csv on;
37        rds_csv_row_terminator "\n";
38        rds_csv_field_name_header off;
39    }
40--- request
41GET /mysql
42--- response_headers_like
43X-Resty-DBD-Module: ngx_drizzle \d+\.\d+\.\d+
44Content-Type: text/csv; header=absence
45--- response_body
462,
473,bob
48--- timeout: 15
49
50
51
52=== TEST 2: keep-alive
53--- http_config eval: $::http_config
54--- config
55    location /mysql {
56        drizzle_pass backend;
57        #drizzle_dbname $dbname;
58        drizzle_query 'select * from cats';
59        rds_csv on;
60        rds_csv_row_terminator "\n";
61    }
62--- request
63GET /mysql
64--- response_body
65id,name
662,
673,bob
68
69
70
71=== TEST 3: update
72--- http_config eval: $::http_config
73--- config
74    location /mysql {
75        drizzle_pass backend;
76        #drizzle_dbname $dbname;
77        drizzle_query "update cats set name='bob' where name='bob'";
78        rds_csv on;
79        rds_csv_row_terminator "\n";
80    }
81--- request
82GET /mysql
83--- response_body
84errcode,errstr,insert_id,affected_rows
850,Rows matched: 1  Changed: 0  Warnings: 0,0,0
86
87
88
89=== TEST 4: select empty result
90--- http_config eval: $::http_config
91--- config
92    location /mysql {
93        drizzle_pass backend;
94        drizzle_query "select * from cats where name='tom'";
95        rds_csv on;
96    }
97--- request
98GET /mysql
99--- response_body eval
100"id,name\r
101"
102
103
104
105=== TEST 5: update & no module header
106--- http_config eval: $::http_config
107--- config
108    location /mysql {
109        if ($arg_name ~ '[^A-Za-z0-9]') {
110            return 400;
111        }
112
113        drizzle_pass backend;
114        drizzle_module_header off;
115        drizzle_query "update cats set name='$arg_name' where name='$arg_name'";
116
117        rds_csv on;
118    }
119--- request
120GET /mysql?name=bob
121--- response_headers
122X-Resty-DBD-Module:
123Content-Type: text/csv; header=presence
124--- response_body eval
125qq{errcode,errstr,insert_id,affected_rows\r
1260,Rows matched: 1  Changed: 0  Warnings: 0,0,0\r
127}
128--- no_error_log
129[error]
130
131
132
133=== TEST 6: invalid SQL
134--- http_config eval: $::http_config
135--- config
136    location /mysql {
137        drizzle_pass backend;
138        drizzle_module_header off;
139        drizzle_query "select '32";
140        rds_csv on;
141        rds_csv_row_terminator "\n";
142    }
143--- response_headers
144X-Resty-DBD-Module:
145Content-Type: text/html
146--- request
147GET /mysql
148--- error_code: 500
149--- response_body_like: 500 Internal Server Error
150
151
152
153=== TEST 7: single row, single col
154--- http_config eval: $::http_config
155--- config
156    location /test {
157        echo_location /mysql "drop table if exists singles";
158        echo_location /mysql "create table singles (name varchar(15));";
159        echo_location /mysql "insert into singles values ('marry');";
160        echo_location /mysql "select * from singles;";
161    }
162    location /mysql {
163        drizzle_pass backend;
164        drizzle_module_header off;
165        drizzle_query $query_string;
166        rds_csv on;
167        rds_csv_row_terminator "\n";
168    }
169--- request
170GET /test
171--- response_body
172errcode,errstr,insert_id,affected_rows
1730,,0,0
174errcode,errstr,insert_id,affected_rows
1750,,0,0
176errcode,errstr,insert_id,affected_rows
1770,,0,1
178name
179marry
180--- skip_nginx: 2: < 0.7.46
181--- timeout: 5
182
183
184
185=== TEST 8: floating number and insert id
186--- http_config eval: $::http_config
187--- config
188    location /test {
189        echo_location /mysql "drop table if exists foo";
190        echo_location /mysql "create table foo (id serial not null, primary key (id), val real);";
191        echo_location /mysql "insert into foo (val) values (3.1415926);";
192        echo_location /mysql "select * from foo;";
193    }
194    location /mysql {
195        drizzle_pass backend;
196        drizzle_module_header off;
197        drizzle_query $query_string;
198        rds_csv on;
199        rds_csv_row_terminator "\n";
200    }
201--- request
202GET /test
203--- response_body
204errcode,errstr,insert_id,affected_rows
2050,,0,0
206errcode,errstr,insert_id,affected_rows
2070,,0,0
208errcode,errstr,insert_id,affected_rows
2090,,1,1
210id,val
2111,3.1415926
212--- skip_nginx: 2: < 0.7.46
213
214
215
216=== TEST 9: text blob field
217--- http_config eval: $::http_config
218--- config
219    location /test {
220        echo_location /mysql "drop table if exists foo";
221        echo_location /mysql "create table foo (id serial, body text);";
222        echo_location /mysql "insert into foo (body) values ('hello');";
223        echo_location /mysql "select * from foo;";
224    }
225    location /mysql {
226        drizzle_pass backend;
227        drizzle_module_header off;
228        drizzle_query $query_string;
229        rds_csv on;
230        rds_csv_row_terminator "\n";
231    }
232--- request
233GET /test
234--- response_body
235errcode,errstr,insert_id,affected_rows
2360,,0,0
237errcode,errstr,insert_id,affected_rows
2380,,0,0
239errcode,errstr,insert_id,affected_rows
2400,,1,1
241id,body
2421,hello
243--- skip_nginx: 2: < 0.7.46
244
245
246
247=== TEST 10: bool blob field
248--- http_config eval: $::http_config
249--- config
250    location /test {
251        echo_location /mysql "drop table if exists foo";
252        echo_location /mysql "create table foo (id serial, flag bool);";
253        echo_location /mysql "insert into foo (flag) values (true);";
254        echo_location /mysql "insert into foo (flag) values (false);";
255        echo_location /mysql "select * from foo order by id;";
256    }
257    location /mysql {
258        drizzle_pass backend;
259        drizzle_module_header off;
260        drizzle_query $query_string;
261        rds_csv on;
262        rds_csv_row_terminator "\n";
263    }
264--- request
265GET /test
266--- response_body eval
267qq{errcode,errstr,insert_id,affected_rows
2680,,0,0
269errcode,errstr,insert_id,affected_rows
2700,,0,0
271errcode,errstr,insert_id,affected_rows
2720,,1,1
273errcode,errstr,insert_id,affected_rows
2740,,2,1
275id,flag
2761,1
2772,0
278}
279--- skip_nginx: 2: < 0.7.46
280--- timeout: 10
281
282
283
284=== TEST 11: bit field
285--- http_config eval: $::http_config
286--- config
287    location /test {
288        echo_location /mysql "drop table if exists foo";
289        echo_location /mysql "create table foo (id serial, flag bit);";
290        echo_location /mysql "insert into foo (flag) values (1);";
291        echo_location /mysql "insert into foo (flag) values (0);";
292        echo_location /mysql "select * from foo order by id;";
293    }
294    location /mysql {
295        drizzle_pass backend;
296        drizzle_module_header off;
297        drizzle_query $query_string;
298        rds_csv on;
299        rds_csv_row_terminator "\n";
300    }
301--- request
302GET /test
303--- response_body eval
304qq{errcode,errstr,insert_id,affected_rows
3050,,0,0
306errcode,errstr,insert_id,affected_rows
3070,,0,0
308errcode,errstr,insert_id,affected_rows
3090,,1,1
310errcode,errstr,insert_id,affected_rows
3110,,2,1
312id,flag
3131,\x01
3142,\x00
315}
316--- skip_nginx: 2: < 0.7.46
317--- timeout: 10
318
319
320
321=== TEST 12: date type
322--- http_config eval: $::http_config
323--- config
324    location /test {
325        echo_location /mysql "drop table if exists foo";
326        echo_location /mysql "create table foo (id serial, created date);";
327        echo_location /mysql "insert into foo (created) values ('2007-05-24');";
328        echo_location /mysql "select * from foo";
329    }
330    location /mysql {
331        drizzle_pass backend;
332        drizzle_module_header off;
333        drizzle_query $query_string;
334        rds_csv on;
335    }
336--- request
337GET /test
338--- response_body eval
339qq{errcode,errstr,insert_id,affected_rows\r
3400,,0,0\r
341errcode,errstr,insert_id,affected_rows\r
3420,,0,0\r
343errcode,errstr,insert_id,affected_rows\r
3440,,1,1\r
345id,created\r
3461,2007-05-24\r
347}
348
349
350
351=== TEST 13: strings need to be escaped (forcing utf8)
352--- http_config
353    upstream backend {
354        drizzle_server 127.0.0.1:$TEST_NGINX_MYSQL_PORT protocol=mysql
355                       dbname=ngx_test user=ngx_test password=ngx_test
356                       charset=utf8;
357    }
358
359--- config
360    location /test {
361        echo_location /mysql "drop table if exists foo";
362        echo_location /mysql "create table foo (id serial, body char(25));";
363        echo_location /mysql "insert into foo (body) values ('a\\r\\nb\\b你好\Z');";
364        echo_location /mysql "select * from foo";
365    }
366    location /mysql {
367        drizzle_pass backend;
368        drizzle_module_header off;
369        drizzle_query $query_string;
370        rds_csv on;
371    }
372--- request
373GET /test
374--- response_body eval
375qq{errcode,errstr,insert_id,affected_rows\r
3760,,0,0\r
377errcode,errstr,insert_id,affected_rows\r
3780,,0,0\r
379errcode,errstr,insert_id,affected_rows\r
3800,,1,1\r
381id,body\r
3821,"a\r
383b\b??\cZ"\r
384}
385--- timeout: 5
386
387
388
389=== TEST 14: strings need to be escaped
390--- SKIP
391--- http_config eval: $::http_config
392--- config
393    location /test {
394        echo_location /mysql "drop table if exists foo";
395        echo_location /mysql "create table foo (id serial, body char(25));";
396        echo_location /mysql "insert into foo (body) values ('a\\r\\nb\\b你好\Z');";
397        echo_location /mysql "select * from foo";
398    }
399    location /mysql {
400        drizzle_pass backend;
401        drizzle_module_header off;
402        drizzle_query $query_string;
403        rds_csv on;
404    }
405--- request
406GET /test
407--- response_body eval
408qq{errcode,errstr,insert_id,affected_rows\r
4090,,0,0\r
410errcode,errstr,insert_id,affected_rows\r
4110,,0,0\r
412errcode,errstr,insert_id,affected_rows\r
4130,,1,1\r
414id,body\r
4151,"a\r
416b\b你好\x1a"\r
417}
418
419
420
421=== TEST 15: null values
422--- http_config eval: $::http_config
423--- config
424    location /test {
425        echo_location /mysql "drop table if exists foo";
426        echo_location /mysql "create table foo (id serial, name char(10), age integer);";
427        echo_location /mysql "insert into foo (name, age) values ('', null);";
428        echo_location /mysql "insert into foo (name, age) values (null, 0);";
429        echo_location /mysql "select * from foo order by id";
430    }
431    location /mysql {
432        drizzle_pass backend;
433        drizzle_module_header off;
434        drizzle_query $query_string;
435        rds_csv on;
436    }
437--- request
438GET /test
439--- response_body eval
440qq{errcode,errstr,insert_id,affected_rows\r
4410,,0,0\r
442errcode,errstr,insert_id,affected_rows\r
4430,,0,0\r
444errcode,errstr,insert_id,affected_rows\r
4450,,1,1\r
446errcode,errstr,insert_id,affected_rows\r
4470,,2,1\r
448id,name,age\r
4491,,\r
4502,,0\r
451}
452--- timeout: 10
453
454