1# vi:filetype=
2
3use lib 'lib';
4use Test::Nginx::Socket;
5
6#repeat_each(10);
7no_shuffle();
8
9repeat_each(1);
10
11plan tests => repeat_each() * 2 * blocks() + 2 * repeat_each() * 3;
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();
23#no_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        drizzle_buffer_size 1;
40    }
41--- request
42GET /mysql
43--- response_headers_like
44X-Resty-DBD-Module: ngx_drizzle \d+\.\d+\.\d+
45Content-Type: text/csv; header=absence
46--- response_body
472,
483,bob
49--- timeout: 15
50
51
52
53=== TEST 2: keep-alive
54--- http_config eval: $::http_config
55--- config
56    location /mysql {
57        drizzle_pass backend;
58        #drizzle_dbname $dbname;
59        drizzle_query 'select * from cats';
60        rds_csv on;
61        rds_csv_row_terminator "\n";
62        drizzle_buffer_size 1;
63    }
64--- request
65GET /mysql
66--- response_body
67id,name
682,
693,bob
70
71
72
73=== TEST 3: update
74--- http_config eval: $::http_config
75--- config
76    location /mysql {
77        drizzle_pass backend;
78        #drizzle_dbname $dbname;
79        drizzle_query "update cats set name='bob' where name='bob'";
80        rds_csv on;
81        rds_csv_row_terminator "\n";
82        drizzle_buffer_size 1;
83    }
84--- request
85GET /mysql
86--- response_body
87errcode,errstr,insert_id,affected_rows
880,Rows matched: 1  Changed: 0  Warnings: 0,0,0
89
90
91
92=== TEST 4: select empty result
93--- http_config eval: $::http_config
94--- config
95    location /mysql {
96        drizzle_pass backend;
97        drizzle_query "select * from cats where name='tom'";
98        rds_csv on;
99        drizzle_buffer_size 1;
100    }
101--- request
102GET /mysql
103--- response_body eval
104"id,name\r
105"
106
107
108
109=== TEST 5: update & no module header
110--- http_config eval: $::http_config
111--- config
112    location /mysql {
113        if ($arg_name ~ '[^A-Za-z0-9]') {
114            return 400;
115        }
116
117        drizzle_pass backend;
118        drizzle_module_header off;
119        drizzle_query "update cats set name='$arg_name' where name='$arg_name'";
120
121        rds_csv on;
122        drizzle_buffer_size 1;
123    }
124--- request
125GET /mysql?name=bob
126--- response_headers
127X-Resty-DBD-Module:
128Content-Type: text/csv; header=presence
129--- response_body eval
130qq{errcode,errstr,insert_id,affected_rows\r
1310,Rows matched: 1  Changed: 0  Warnings: 0,0,0\r
132}
133
134
135
136=== TEST 6: invalid SQL
137--- http_config eval: $::http_config
138--- config
139    location /mysql {
140        drizzle_pass backend;
141        drizzle_module_header off;
142        drizzle_query "select '32";
143        rds_csv on;
144        rds_csv_row_terminator "\n";
145        drizzle_buffer_size 1;
146    }
147--- response_headers
148X-Resty-DBD-Module:
149Content-Type: text/html
150--- request
151GET /mysql
152--- error_code: 500
153--- response_body_like: 500 Internal Server Error
154
155
156
157=== TEST 7: single row, single col
158--- http_config eval: $::http_config
159--- config
160    location /test {
161        echo_location /mysql "drop table if exists singles";
162        echo_location /mysql "create table singles (name varchar(15));";
163        echo_location /mysql "insert into singles values ('marry');";
164        echo_location /mysql "select * from singles;";
165    }
166    location /mysql {
167        drizzle_pass backend;
168        drizzle_module_header off;
169        drizzle_query $query_string;
170        rds_csv on;
171        rds_csv_row_terminator "\n";
172        drizzle_buffer_size 1;
173    }
174--- request
175GET /test
176--- response_body
177errcode,errstr,insert_id,affected_rows
1780,,0,0
179errcode,errstr,insert_id,affected_rows
1800,,0,0
181errcode,errstr,insert_id,affected_rows
1820,,0,1
183name
184marry
185--- skip_nginx: 2: < 0.7.46
186--- timeout: 5
187
188
189
190=== TEST 8: floating number and insert id
191--- http_config eval: $::http_config
192--- config
193    location /test {
194        echo_location /mysql "drop table if exists foo";
195        echo_location /mysql "create table foo (id serial not null, primary key (id), val real);";
196        echo_location /mysql "insert into foo (val) values (3.1415926);";
197        echo_location /mysql "select * from foo;";
198    }
199    location /mysql {
200        drizzle_pass backend;
201        drizzle_module_header off;
202        drizzle_query $query_string;
203        rds_csv on;
204        rds_csv_row_terminator "\n";
205        drizzle_buffer_size 1;
206    }
207--- request
208GET /test
209--- response_body
210errcode,errstr,insert_id,affected_rows
2110,,0,0
212errcode,errstr,insert_id,affected_rows
2130,,0,0
214errcode,errstr,insert_id,affected_rows
2150,,1,1
216id,val
2171,3.1415926
218--- skip_nginx: 2: < 0.7.46
219
220
221
222=== TEST 9: text blob field
223--- http_config eval: $::http_config
224--- config
225    location /test {
226        echo_location /mysql "drop table if exists foo";
227        echo_location /mysql "create table foo (id serial, body text);";
228        echo_location /mysql "insert into foo (body) values ('hello');";
229        echo_location /mysql "select * from foo;";
230    }
231    location /mysql {
232        drizzle_pass backend;
233        drizzle_module_header off;
234        drizzle_query $query_string;
235        rds_csv on;
236        rds_csv_row_terminator "\n";
237        drizzle_buffer_size 1;
238    }
239--- request
240GET /test
241--- response_body
242errcode,errstr,insert_id,affected_rows
2430,,0,0
244errcode,errstr,insert_id,affected_rows
2450,,0,0
246errcode,errstr,insert_id,affected_rows
2470,,1,1
248id,body
2491,hello
250--- skip_nginx: 2: < 0.7.46
251
252
253
254=== TEST 10: bool blob field
255--- http_config eval: $::http_config
256--- config
257    location /test {
258        echo_location /mysql "drop table if exists foo";
259        echo_location /mysql "create table foo (id serial, flag bool);";
260        echo_location /mysql "insert into foo (flag) values (true);";
261        echo_location /mysql "insert into foo (flag) values (false);";
262        echo_location /mysql "select * from foo order by id;";
263    }
264    location /mysql {
265        drizzle_pass backend;
266        drizzle_module_header off;
267        drizzle_query $query_string;
268        rds_csv on;
269        rds_csv_row_terminator "\n";
270        drizzle_buffer_size 1;
271    }
272--- request
273GET /test
274--- response_body eval
275qq{errcode,errstr,insert_id,affected_rows
2760,,0,0
277errcode,errstr,insert_id,affected_rows
2780,,0,0
279errcode,errstr,insert_id,affected_rows
2800,,1,1
281errcode,errstr,insert_id,affected_rows
2820,,2,1
283id,flag
2841,1
2852,0
286}
287--- skip_nginx: 2: < 0.7.46
288--- timeout: 10
289
290
291
292=== TEST 11: bit field
293--- http_config eval: $::http_config
294--- config
295    location /test {
296        echo_location /mysql "drop table if exists foo";
297        echo_location /mysql "create table foo (id serial, flag bit);";
298        echo_location /mysql "insert into foo (flag) values (1);";
299        echo_location /mysql "insert into foo (flag) values (0);";
300        echo_location /mysql "select * from foo order by id;";
301    }
302    location /mysql {
303        drizzle_pass backend;
304        drizzle_module_header off;
305        drizzle_query $query_string;
306        rds_csv on;
307        rds_csv_row_terminator "\n";
308        drizzle_buffer_size 1;
309    }
310--- request
311GET /test
312--- response_body eval
313qq{errcode,errstr,insert_id,affected_rows
3140,,0,0
315errcode,errstr,insert_id,affected_rows
3160,,0,0
317errcode,errstr,insert_id,affected_rows
3180,,1,1
319errcode,errstr,insert_id,affected_rows
3200,,2,1
321id,flag
3221,\x01
3232,\x00
324}
325--- skip_nginx: 2: < 0.7.46
326--- timeout: 10
327
328
329
330=== TEST 12: date type
331--- http_config eval: $::http_config
332--- config
333    location /test {
334        echo_location /mysql "drop table if exists foo";
335        echo_location /mysql "create table foo (id serial, created date);";
336        echo_location /mysql "insert into foo (created) values ('2007-05-24');";
337        echo_location /mysql "select * from foo";
338    }
339    location /mysql {
340        drizzle_pass backend;
341        drizzle_module_header off;
342        drizzle_query $query_string;
343        rds_csv on;
344        drizzle_buffer_size 1;
345    }
346--- request
347GET /test
348--- response_body eval
349qq{errcode,errstr,insert_id,affected_rows\r
3500,,0,0\r
351errcode,errstr,insert_id,affected_rows\r
3520,,0,0\r
353errcode,errstr,insert_id,affected_rows\r
3540,,1,1\r
355id,created\r
3561,2007-05-24\r
357}
358
359
360
361=== TEST 13: strings need to be escaped (forcing utf8)
362--- http_config
363    upstream backend {
364        drizzle_server 127.0.0.1:$TEST_NGINX_MYSQL_PORT protocol=mysql
365                       dbname=ngx_test user=ngx_test password=ngx_test
366                       charset=utf8;
367    }
368
369--- config
370    location /test {
371        echo_location /mysql "drop table if exists foo";
372        echo_location /mysql "create table foo (id serial, body char(25));";
373        echo_location /mysql "insert into foo (body) values ('a\\r\\nb\\b你好\Z');";
374        echo_location /mysql "select * from foo";
375    }
376    location /mysql {
377        drizzle_pass backend;
378        drizzle_module_header off;
379        drizzle_query $query_string;
380        rds_csv on;
381        drizzle_buffer_size 1;
382    }
383--- request
384GET /test
385--- response_body eval
386qq{errcode,errstr,insert_id,affected_rows\r
3870,,0,0\r
388errcode,errstr,insert_id,affected_rows\r
3890,,0,0\r
390errcode,errstr,insert_id,affected_rows\r
3910,,1,1\r
392id,body\r
3931,"a\r
394b\b??\cZ"\r
395}
396--- timeout: 5
397
398
399
400=== TEST 14: strings need to be escaped
401--- SKIP
402--- http_config eval: $::http_config
403--- config
404    location /test {
405        echo_location /mysql "drop table if exists foo";
406        echo_location /mysql "create table foo (id serial, body char(25));";
407        echo_location /mysql "insert into foo (body) values ('a\\r\\nb\\b你好\Z');";
408        echo_location /mysql "select * from foo";
409    }
410    location /mysql {
411        drizzle_pass backend;
412        drizzle_module_header off;
413        drizzle_query $query_string;
414        rds_csv on;
415        drizzle_buffer_size 1;
416    }
417--- request
418GET /test
419--- response_body eval
420qq{errcode,errstr,insert_id,affected_rows\r
4210,,0,0\r
422errcode,errstr,insert_id,affected_rows\r
4230,,0,0\r
424errcode,errstr,insert_id,affected_rows\r
4250,,1,1\r
426id,body\r
4271,"a\r
428b\b你好\x1a"\r
429}
430
431
432
433=== TEST 15: null values
434--- http_config eval: $::http_config
435--- config
436    location /test {
437        echo_location /mysql "drop table if exists foo";
438        echo_location /mysql "create table foo (id serial, name char(10), age integer);";
439        echo_location /mysql "insert into foo (name, age) values ('', null);";
440        echo_location /mysql "insert into foo (name, age) values (null, 0);";
441        echo_location /mysql "select * from foo order by id";
442    }
443    location /mysql {
444        drizzle_pass backend;
445        drizzle_module_header off;
446        drizzle_query $query_string;
447        rds_csv on;
448        drizzle_buffer_size 1;
449    }
450--- request
451GET /test
452--- response_body eval
453qq{errcode,errstr,insert_id,affected_rows\r
4540,,0,0\r
455errcode,errstr,insert_id,affected_rows\r
4560,,0,0\r
457errcode,errstr,insert_id,affected_rows\r
4580,,1,1\r
459errcode,errstr,insert_id,affected_rows\r
4600,,2,1\r
461id,name,age\r
4621,,\r
4632,,0\r
464}
465--- timeout: 10
466
467