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