1use strict; 2use warnings; 3 4use Test::More; 5 6use SQL::Tokenizer; 7 8use constant SPACE => ' '; 9use constant COMMA => ','; 10use constant NL => "\n"; 11 12my $query; 13my @query; 14my @tokenized; 15 16my @tests = ( 17 { 18 description => q{complex explain query}, 19 query => 20q{EXPLAIN PLAN LEFT JOIN user.name, email.address, mobile.number WHERE user.id = 100 AND email.user = user.id AND mobile.user = user.id GROUP BY mobile.country_code HAVING mobile.country_code IN ( '55', '31', '44' )}, 21 22 wanted => [ 23 'EXPLAIN', SPACE, 24 'PLAN', SPACE, 25 'LEFT', SPACE, 26 'JOIN', SPACE, 27 'user.name', COMMA, 28 SPACE, 'email.address', 29 COMMA, SPACE, 30 'mobile.number', SPACE, 31 'WHERE', SPACE, 32 'user.id', SPACE, 33 '=', SPACE, 34 '100', SPACE, 35 'AND', SPACE, 36 'email.user', SPACE, 37 '=', SPACE, 38 'user.id', SPACE, 39 'AND', SPACE, 40 'mobile.user', SPACE, 41 '=', SPACE, 42 'user.id', SPACE, 43 'GROUP', SPACE, 44 'BY', SPACE, 45 'mobile.country_code', SPACE, 46 'HAVING', SPACE, 47 'mobile.country_code', SPACE, 48 'IN', SPACE, 49 '(', SPACE, 50 q{'55'}, COMMA, 51 SPACE, q{'31'}, 52 COMMA, SPACE, 53 q{'44'}, SPACE, 54 ')' 55 ], 56 }, 57 58 { 59 description => q{SQL script}, 60 query => <<COMPLEX_SQL, 61-- drop table 62DROP TABLE test; 63-- create table 64CREATE TABLE test (id INT, name VARCHAR); 65-- insert data 66INSERT INTO test (id, name) VALUES (1, 't'); 67INSERT INTO test (id, name) VALUES (2, '''quoted'''); 68COMPLEX_SQL 69 70 wanted => [ 71 q{-- drop table}, NL, 72 'DROP', SPACE, 73 'TABLE', SPACE, 74 'test', ';', 75 NL, q{-- create table}, 76 NL, 'CREATE', 77 SPACE, 'TABLE', 78 SPACE, 'test', 79 SPACE, '(', 80 'id', SPACE, 81 'INT', COMMA, 82 SPACE, 'name', 83 SPACE, 'VARCHAR', 84 ')', ';', 85 NL, q{-- insert data}, 86 NL, 'INSERT', 87 SPACE, 'INTO', 88 SPACE, 'test', 89 SPACE, '(', 90 'id', COMMA, 91 SPACE, 'name', 92 ')', SPACE, 93 'VALUES', SPACE, 94 '(', '1', 95 COMMA, SPACE, 96 q{'t'}, ')', 97 ';', NL, 98 'INSERT', SPACE, 99 'INTO', SPACE, 100 'test', SPACE, 101 '(', 'id', 102 COMMA, SPACE, 103 'name', ')', 104 SPACE, 'VALUES', 105 SPACE, '(', 106 '2', COMMA, 107 SPACE, q{'''quoted'''}, 108 ')', ';', 109 NL, 110 ], 111 }, 112 113 { 114 description => q{really long SQL query}, 115 query => <<'EOQ', 116SELECT 117 v.veiculo_id AS veiculo_id, 118 v.imagem1 AS imagem1, 119 v.imagem2 AS imagem2, 120 v.imagem3 AS imagem3, 121 v.imagem4 AS imagem4, 122 v.combustivel AS combustivel, 123 v.modelo_nome AS modelo_nome, 124 COALESCE(m.nome,' ') AS marca, 125 COALESCE(v.versao,' ') AS versao, 126 COALESCE(v.placa,' ') AS placa, 127 COALESCE(v.ano,'0') AS ano, 128 COALESCE(v.modelo_ano,'0') AS modelo_ano, 129 COALESCE(v.cor,' ') AS cor, 130 COALESCE(v.portas,'0') AS portas, 131 COALESCE(v.preco,'0.0') AS valor, 132 c.cliente_id AS cliente_id 133FROM 134 veiculo AS v 135INNER JOIN 136 anuncio AS a ON a.veiculo_id = v.veiculo_id 137INNER JOIN 138 cliente AS c ON c.cliente_id = a.cliente_id 139LEFT JOIN 140 marca AS m ON v.marca_id = m.marca_id 141WHERE 142 1=1 AND 143 ( 144 (a.data_inicio <= '20070502' AND a.data_fim >= '20060502') OR 145 (a.data_inicio IS NULL AND a.data_fim IS NULL) 146 ) AND 147 a.ativo = 1 AND 148 v.veiculo_tipo_id = 3 AND 149 v.imagem1 IS NOT NULL AND 150 ( 151 v.imagem1 is not null OR 152 v.imagem2 is not null OR 153 v.imagem3 is not null OR 154 v.imagem4 is not null 155 ) AND 156 c.cliente_id = 12 157ORDER BY v.preco ASC 158EOQ 159 160 wanted => [ 161 'SELECT', NL, 162 ' ', 'v.veiculo_id', 163 SPACE, 'AS', 164 SPACE, 'veiculo_id', 165 COMMA, NL, 166 ' ', 'v.imagem1', 167 SPACE, 'AS', 168 SPACE, 'imagem1', 169 COMMA, NL, 170 ' ', 'v.imagem2', 171 SPACE, 'AS', 172 SPACE, 'imagem2', 173 COMMA, NL, 174 ' ', 'v.imagem3', 175 SPACE, 'AS', 176 SPACE, 'imagem3', 177 COMMA, NL, 178 ' ', 'v.imagem4', 179 SPACE, 'AS', 180 SPACE, 'imagem4', 181 COMMA, NL, 182 ' ', 'v.combustivel', 183 SPACE, 'AS', 184 SPACE, 'combustivel', 185 COMMA, NL, 186 ' ', 'v.modelo_nome', 187 SPACE, 'AS', 188 SPACE, 'modelo_nome', 189 COMMA, NL, 190 ' ', 'COALESCE', 191 '(', 'm.nome', 192 COMMA, q{' '}, 193 ')', SPACE, 194 'AS', SPACE, 195 'marca', COMMA, 196 NL, ' ', 197 'COALESCE', '(', 198 'v.versao', COMMA, 199 q{' '}, ')', 200 SPACE, 'AS', 201 SPACE, 'versao', 202 COMMA, NL, 203 ' ', 'COALESCE', 204 '(', 'v.placa', 205 COMMA, q{' '}, 206 ')', SPACE, 207 'AS', SPACE, 208 'placa', COMMA, 209 NL, ' ', 210 'COALESCE', '(', 211 'v.ano', COMMA, 212 q{'0'}, ')', 213 SPACE, 'AS', 214 SPACE, 'ano', 215 COMMA, NL, 216 ' ', 'COALESCE', 217 '(', 'v.modelo_ano', 218 COMMA, q{'0'}, 219 ')', SPACE, 220 'AS', SPACE, 221 'modelo_ano', COMMA, 222 NL, ' ', 223 'COALESCE', '(', 224 'v.cor', COMMA, 225 q{' '}, ')', 226 SPACE, 'AS', 227 SPACE, 'cor', 228 COMMA, NL, 229 ' ', 'COALESCE', 230 '(', 'v.portas', 231 COMMA, q{'0'}, 232 ')', SPACE, 233 'AS', SPACE, 234 'portas', COMMA, 235 NL, ' ', 236 'COALESCE', '(', 237 'v.preco', COMMA, 238 q{'0.0'}, ')', 239 SPACE, 'AS', 240 SPACE, 'valor', 241 COMMA, NL, 242 ' ', 'c.cliente_id', 243 SPACE, 'AS', 244 SPACE, 'cliente_id', 245 NL, 'FROM', 246 NL, ' ', 247 'veiculo', SPACE, 248 'AS', SPACE, 249 'v', NL, 250 'INNER', SPACE, 251 'JOIN', NL, 252 ' ', 'anuncio', 253 SPACE, 'AS', 254 SPACE, 'a', 255 SPACE, 'ON', 256 SPACE, 'a.veiculo_id', 257 SPACE, '=', 258 SPACE, 'v.veiculo_id', 259 NL, 'INNER', 260 SPACE, 'JOIN', 261 NL, ' ', 262 'cliente', SPACE, 263 'AS', SPACE, 264 'c', SPACE, 265 'ON', SPACE, 266 'c.cliente_id', SPACE, 267 '=', SPACE, 268 'a.cliente_id', NL, 269 'LEFT', SPACE, 270 'JOIN', NL, 271 ' ', 'marca', 272 SPACE, 'AS', 273 SPACE, 'm', 274 SPACE, 'ON', 275 SPACE, 'v.marca_id', 276 SPACE, '=', 277 SPACE, 'm.marca_id', 278 NL, 'WHERE', 279 NL, ' ', 280 '1', '=', 281 '1', SPACE, 282 'AND', NL, 283 ' ', '(', 284 NL, ' ', 285 '(', 'a.data_inicio', 286 SPACE, '<=', 287 SPACE, q{'20070502'}, 288 SPACE, 'AND', 289 SPACE, 'a.data_fim', 290 SPACE, '>=', 291 SPACE, q{'20060502'}, 292 ')', SPACE, 293 'OR', NL, 294 ' ', '(', 295 'a.data_inicio', SPACE, 296 'IS', SPACE, 297 'NULL', SPACE, 298 'AND', SPACE, 299 'a.data_fim', SPACE, 300 'IS', SPACE, 301 'NULL', ')', 302 NL, ' ', 303 ')', SPACE, 304 'AND', NL, 305 ' ', 'a.ativo', 306 SPACE, '=', 307 SPACE, '1', 308 SPACE, 'AND', 309 NL, ' ', 310 'v.veiculo_tipo_id', SPACE, 311 '=', SPACE, 312 '3', SPACE, 313 'AND', NL, 314 ' ', 'v.imagem1', 315 SPACE, 'IS', 316 SPACE, 'NOT', 317 SPACE, 'NULL', 318 SPACE, 'AND', 319 NL, ' ', 320 '(', NL, 321 ' ', 'v.imagem1', 322 SPACE, 'is', 323 SPACE, 'not', 324 SPACE, 'null', 325 SPACE, 'OR', 326 NL, ' ', 327 'v.imagem2', SPACE, 328 'is', SPACE, 329 'not', SPACE, 330 'null', SPACE, 331 'OR', NL, 332 ' ', 'v.imagem3', 333 SPACE, 'is', 334 SPACE, 'not', 335 SPACE, 'null', 336 SPACE, 'OR', 337 NL, ' ', 338 'v.imagem4', SPACE, 339 'is', SPACE, 340 'not', SPACE, 341 'null', NL, 342 ' ', ')', 343 SPACE, 'AND', 344 NL, ' ', 345 'c.cliente_id', SPACE, 346 '=', SPACE, 347 '12', NL, 348 'ORDER', SPACE, 349 'BY', SPACE, 350 'v.preco', SPACE, 351 'ASC', NL 352 ], 353 }, 354 355); 356 357plan tests => scalar @tests; 358 359foreach my $test (@tests) { 360 my @tokenized = SQL::Tokenizer->tokenize( $test->{query} ); 361 is_deeply( \@tokenized, $test->{wanted}, $test->{description} ); 362} 363