1#!/usr/bin/perl 2 3BEGIN { 4 die "The PERCONA_TOOLKIT_BRANCH environment variable is not set.\n" 5 unless $ENV{PERCONA_TOOLKIT_BRANCH} && -d $ENV{PERCONA_TOOLKIT_BRANCH}; 6 unshift @INC, "$ENV{PERCONA_TOOLKIT_BRANCH}/lib"; 7}; 8 9use strict; 10use warnings FATAL => 'all'; 11use English qw(-no_match_vars); 12use Test::More tests => 26; 13 14use TableParser; 15use TableNibbler; 16use Quoter; 17use PerconaTest; 18 19my $q = new Quoter(); 20my $tp = new TableParser(Quoter => $q); 21my $n = new TableNibbler( 22 TableParser => $tp, 23 Quoter => $q, 24); 25 26my $t; 27 28$t = $tp->parse( load_file('t/lib/samples/sakila.film.sql') ); 29 30is_deeply( 31 $n->generate_asc_stmt ( 32 tbl_struct => $t, 33 cols => $t->{cols}, 34 index => 'PRIMARY', 35 ), 36 { 37 cols => [qw(film_id title description release_year language_id 38 original_language_id rental_duration rental_rate 39 length replacement_cost rating special_features 40 last_update)], 41 index => 'PRIMARY', 42 where => '((`film_id` >= ?))', 43 slice => [0], 44 scols => [qw(film_id)], 45 boundaries => { 46 '>=' => '((`film_id` >= ?))', 47 '>' => '((`film_id` > ?))', 48 '<=' => '((`film_id` <= ?))', 49 '<' => '((`film_id` < ?))', 50 }, 51 }, 52 'asc stmt on sakila.film', 53); 54 55is_deeply( 56 $n->generate_del_stmt ( 57 tbl_struct => $t, 58 ), 59 { 60 cols => [qw(film_id)], 61 index => 'PRIMARY', 62 where => '(`film_id` = ?)', 63 slice => [0], 64 scols => [qw(film_id)], 65 }, 66 'del stmt on sakila.film', 67); 68 69is_deeply( 70 $n->generate_asc_stmt ( 71 tbl_struct => $t, 72 index => 'PRIMARY', 73 ), 74 { 75 cols => [qw(film_id title description release_year language_id 76 original_language_id rental_duration rental_rate 77 length replacement_cost rating special_features 78 last_update)], 79 index => 'PRIMARY', 80 where => '((`film_id` >= ?))', 81 slice => [0], 82 scols => [qw(film_id)], 83 boundaries => { 84 '>=' => '((`film_id` >= ?))', 85 '>' => '((`film_id` > ?))', 86 '<=' => '((`film_id` <= ?))', 87 '<' => '((`film_id` < ?))', 88 }, 89 }, 90 'defaults to all columns', 91); 92 93throws_ok( 94 sub { 95 $n->generate_asc_stmt ( 96 tbl_struct => $t, 97 cols => $t->{cols}, 98 index => 'title', 99 ) 100 }, 101 qr/Index 'title' does not exist in table/, 102 'Error on nonexistent index', 103); 104 105is_deeply( 106 $n->generate_asc_stmt ( 107 tbl_struct => $t, 108 cols => $t->{cols}, 109 index => 'idx_title', 110 ), 111 { 112 cols => [qw(film_id title description release_year language_id 113 original_language_id rental_duration rental_rate 114 length replacement_cost rating special_features 115 last_update)], 116 index => 'idx_title', 117 where => '((`title` >= ?))', 118 slice => [1], 119 scols => [qw(title)], 120 boundaries => { 121 '>=' => '((`title` >= ?))', 122 '>' => '((`title` > ?))', 123 '<=' => '((`title` <= ?))', 124 '<' => '((`title` < ?))', 125 }, 126 }, 127 'asc stmt on sakila.film with different index', 128); 129 130is_deeply( 131 $n->generate_del_stmt ( 132 tbl_struct => $t, 133 index => 'idx_title', 134 cols => [qw(film_id)], 135 ), 136 { 137 cols => [qw(film_id title description release_year language_id original_language_id rental_duration rental_rate length replacement_cost rating special_features last_update)], 138 index => 'idx_title', 139 where => '(`film_id` = ? AND `title` = ? AND ((? IS NULL AND `description` IS NULL) OR (`description` = ?)) AND ((? IS NULL AND `release_year` IS NULL) OR (`release_year` = ?)) AND `language_id` = ? AND ((? IS NULL AND `original_language_id` IS NULL) OR (`original_language_id` = ?)) AND `rental_duration` = ? AND `rental_rate` = ? AND ((? IS NULL AND `length` IS NULL) OR (`length` = ?)) AND `replacement_cost` = ? AND ((? IS NULL AND `rating` IS NULL) OR (`rating` = ?)) AND ((? IS NULL AND `special_features` IS NULL) OR (`special_features` = ?)) AND `last_update` = ?)', 140 slice => [ 0, 1, 2, 2, 3, 3, 4, 5, 5, 6, 7, 8, 8, 9, 10, 10, 11, 11, 12 ], 141 scols => [qw( film_id title description description release_year release_year language_id original_language_id original_language_id 142 rental_duration rental_rate length length replacement_cost rating rating special_features special_features last_update)], 143 144 }, 145 'del stmt on sakila.film with different index and extra column', 146); 147 148# TableParser::find_best_index() is case-insensitive, returning the 149# correct case even if the wrong case is given. But generate_asc_stmt() 150# no longer calls find_best_index() so this test is a moot point. 151is_deeply( 152 $n->generate_asc_stmt ( 153 tbl_struct => $t, 154 cols => $t->{cols}, 155 index => 'idx_title', 156 ), 157 { 158 cols => [qw(film_id title description release_year language_id 159 original_language_id rental_duration rental_rate 160 length replacement_cost rating special_features 161 last_update)], 162 index => 'idx_title', 163 where => '((`title` >= ?))', 164 slice => [1], 165 scols => [qw(title)], 166 boundaries => { 167 '>=' => '((`title` >= ?))', 168 '>' => '((`title` > ?))', 169 '<=' => '((`title` <= ?))', 170 '<' => '((`title` < ?))', 171 }, 172 }, 173 'Index returned in correct lettercase', 174); 175 176is_deeply( 177 $n->generate_asc_stmt ( 178 tbl_struct => $t, 179 cols => [qw(title)], 180 index => 'PRIMARY', 181 ), 182 { 183 cols => [qw(title film_id)], 184 index => 'PRIMARY', 185 where => '((`film_id` >= ?))', 186 slice => [1], 187 scols => [qw(film_id)], 188 boundaries => { 189 '>=' => '((`film_id` >= ?))', 190 '>' => '((`film_id` > ?))', 191 '<=' => '((`film_id` <= ?))', 192 '<' => '((`film_id` < ?))', 193 }, 194 }, 195 'Required columns added to SELECT list', 196); 197 198# ########################################################################## 199# Switch to the rental table 200# ########################################################################## 201$t = $tp->parse( load_file('t/lib/samples/sakila.rental.sql') ); 202 203is_deeply( 204 $n->generate_asc_stmt( 205 tbl_struct => $t, 206 cols => $t->{cols}, 207 index => 'rental_date', 208 ), 209 { 210 cols => [qw(rental_id rental_date inventory_id customer_id 211 return_date staff_id last_update)], 212 index => 'rental_date', 213 where => '((`rental_date` > ?) OR (`rental_date` = ? AND `inventory_id` > ?)' 214 . ' OR (`rental_date` = ? AND `inventory_id` = ? AND `customer_id` >= ?))', 215 slice => [1, 1, 2, 1, 2, 3], 216 scols => [qw(rental_date rental_date inventory_id rental_date inventory_id customer_id)], 217 boundaries => { 218 '>=' => '((`rental_date` > ?) OR (`rental_date` = ? AND ' 219 . '`inventory_id` > ?) OR (`rental_date` = ? AND `inventory_id` ' 220 . '= ? AND `customer_id` >= ?))', 221 '>' => '((`rental_date` > ?) OR (`rental_date` = ? AND ' 222 . '`inventory_id` > ?) OR (`rental_date` = ? AND `inventory_id` ' 223 . '= ? AND `customer_id` > ?))', 224 '<=' => '((`rental_date` < ?) OR (`rental_date` = ? AND ' 225 . '`inventory_id` < ?) OR (`rental_date` = ? AND `inventory_id` ' 226 . '= ? AND `customer_id` <= ?))', 227 '<' => '((`rental_date` < ?) OR (`rental_date` = ? AND ' 228 . '`inventory_id` < ?) OR (`rental_date` = ? AND `inventory_id` ' 229 . '= ? AND `customer_id` < ?))', 230 }, 231 }, 232 'Alternate index on sakila.rental', 233); 234 235is_deeply( 236 $n->generate_del_stmt ( 237 tbl_struct => $t, 238 index => 'rental_date', 239 ), 240 { 241 cols => [qw(rental_date inventory_id customer_id)], 242 index => 'rental_date', 243 where => '(`rental_date` = ? AND `inventory_id` = ? AND `customer_id` = ?)', 244 slice => [0, 1, 2], 245 scols => [qw(rental_date inventory_id customer_id)], 246 }, 247 'Alternate index on sakila.rental delete statement', 248); 249 250# Check that I can select from one table and insert into another OK 251my $f = $tp->parse( load_file('t/lib/samples/sakila.film.sql') ); 252is_deeply( 253 $n->generate_ins_stmt( 254 ins_tbl => $f, 255 sel_cols => $t->{cols}, 256 ), 257 { 258 cols => [qw(last_update)], 259 slice => [6], 260 }, 261 'Generated an INSERT statement from film into rental', 262); 263 264my $sel_tbl = $tp->parse( load_file('t/lib/samples/issue_131_sel.sql') ); 265my $ins_tbl = $tp->parse( load_file('t/lib/samples/issue_131_ins.sql') ); 266is_deeply( 267 $n->generate_ins_stmt( 268 ins_tbl => $ins_tbl, 269 sel_cols => $sel_tbl->{cols}, 270 ), 271 { 272 cols => [qw(id name)], 273 slice => [0, 2], 274 }, 275 'INSERT stmt with different col order and a missing ins col' 276); 277 278is_deeply( 279 $n->generate_asc_stmt( 280 tbl_struct => $t, 281 cols => $t->{cols}, 282 index => 'rental_date', 283 asc_first => 1, 284 ), 285 { 286 cols => [qw(rental_id rental_date inventory_id customer_id 287 return_date staff_id last_update)], 288 index => 'rental_date', 289 where => '((`rental_date` >= ?))', 290 slice => [1], 291 scols => [qw(rental_date)], 292 boundaries => { 293 '>=' => '((`rental_date` >= ?))', 294 '>' => '((`rental_date` > ?))', 295 '<=' => '((`rental_date` <= ?))', 296 '<' => '((`rental_date` < ?))', 297 }, 298 }, 299 'Alternate index with asc_first on sakila.rental', 300); 301 302is_deeply( 303 $n->generate_asc_stmt( 304 tbl_struct => $t, 305 cols => $t->{cols}, 306 index => 'rental_date', 307 n_index_cols => 2, 308 ), 309 { 310 cols => [qw(rental_id rental_date inventory_id customer_id 311 return_date staff_id last_update)], 312 index => 'rental_date', 313 where => '((`rental_date` > ?) OR (`rental_date` = ? AND `inventory_id` >= ?))', 314 slice => [qw(1 1 2)], 315 scols => [qw(rental_date rental_date inventory_id)], 316 boundaries => { 317 '<' => 318 '((`rental_date` < ?) OR (`rental_date` = ? AND `inventory_id` < ?))', 319 '<=' => 320 '((`rental_date` < ?) OR (`rental_date` = ? AND `inventory_id` <= ?))', 321 '>' => 322 '((`rental_date` > ?) OR (`rental_date` = ? AND `inventory_id` > ?))', 323 '>=' => 324 '((`rental_date` > ?) OR (`rental_date` = ? AND `inventory_id` >= ?))' 325 }, 326 }, 327 'Use only N left-most columns of the index', 328); 329 330is_deeply( 331 $n->generate_asc_stmt( 332 tbl_struct => $t, 333 cols => $t->{cols}, 334 index => 'rental_date', 335 n_index_cols => 5, 336 ), 337 { 338 cols => [qw(rental_id rental_date inventory_id customer_id 339 return_date staff_id last_update)], 340 index => 'rental_date', 341 where => '((`rental_date` > ?) OR (`rental_date` = ? AND `inventory_id` > ?)' 342 . ' OR (`rental_date` = ? AND `inventory_id` = ? AND `customer_id` >= ?))', 343 slice => [1, 1, 2, 1, 2, 3], 344 scols => [qw(rental_date rental_date inventory_id rental_date inventory_id customer_id)], 345 boundaries => { 346 '>=' => '((`rental_date` > ?) OR (`rental_date` = ? AND ' 347 . '`inventory_id` > ?) OR (`rental_date` = ? AND `inventory_id` ' 348 . '= ? AND `customer_id` >= ?))', 349 '>' => '((`rental_date` > ?) OR (`rental_date` = ? AND ' 350 . '`inventory_id` > ?) OR (`rental_date` = ? AND `inventory_id` ' 351 . '= ? AND `customer_id` > ?))', 352 '<=' => '((`rental_date` < ?) OR (`rental_date` = ? AND ' 353 . '`inventory_id` < ?) OR (`rental_date` = ? AND `inventory_id` ' 354 . '= ? AND `customer_id` <= ?))', 355 '<' => '((`rental_date` < ?) OR (`rental_date` = ? AND ' 356 . '`inventory_id` < ?) OR (`rental_date` = ? AND `inventory_id` ' 357 . '= ? AND `customer_id` < ?))', 358 }, 359 }, 360 "Don't crash if N > number of index columns" 361); 362 363is_deeply( 364 $n->generate_asc_stmt( 365 tbl_struct => $t, 366 cols => $t->{cols}, 367 index => 'rental_date', 368 asc_only => 1, 369 ), 370 { 371 cols => [qw(rental_id rental_date inventory_id customer_id 372 return_date staff_id last_update)], 373 index => 'rental_date', 374 where => '((`rental_date` > ?) OR (`rental_date` = ? AND `inventory_id` > ?)' 375 . ' OR (`rental_date` = ? AND `inventory_id` = ? AND `customer_id` > ?))', 376 slice => [1, 1, 2, 1, 2, 3], 377 scols => [qw(rental_date rental_date inventory_id rental_date inventory_id customer_id)], 378 boundaries => { 379 '>=' => '((`rental_date` > ?) OR (`rental_date` = ? AND ' 380 . '`inventory_id` > ?) OR (`rental_date` = ? AND `inventory_id` ' 381 . '= ? AND `customer_id` >= ?))', 382 '>' => '((`rental_date` > ?) OR (`rental_date` = ? AND ' 383 . '`inventory_id` > ?) OR (`rental_date` = ? AND `inventory_id` ' 384 . '= ? AND `customer_id` > ?))', 385 '<=' => '((`rental_date` < ?) OR (`rental_date` = ? AND ' 386 . '`inventory_id` < ?) OR (`rental_date` = ? AND `inventory_id` ' 387 . '= ? AND `customer_id` <= ?))', 388 '<' => '((`rental_date` < ?) OR (`rental_date` = ? AND ' 389 . '`inventory_id` < ?) OR (`rental_date` = ? AND `inventory_id` ' 390 . '= ? AND `customer_id` < ?))', 391 }, 392 }, 393 'Alternate index on sakila.rental with strict ascending', 394); 395 396# ########################################################################## 397# Switch to the rental table with customer_id nullable 398# ########################################################################## 399$t = $tp->parse( load_file('t/lib/samples/sakila.rental.null.sql') ); 400 401is_deeply( 402 $n->generate_asc_stmt( 403 tbl_struct => $t, 404 cols => $t->{cols}, 405 index => 'rental_date', 406 ), 407 { 408 cols => [qw(rental_id rental_date inventory_id customer_id 409 return_date staff_id last_update)], 410 index => 'rental_date', 411 where => '((`rental_date` > ?) OR (`rental_date` = ? AND `inventory_id` > ?)' 412 . ' OR (`rental_date` = ? AND `inventory_id` = ? AND ' 413 . '(? IS NULL OR `customer_id` >= ?)))', 414 slice => [1, 1, 2, 1, 2, 3, 3], 415 scols => [qw(rental_date rental_date inventory_id rental_date inventory_id customer_id customer_id)], 416 boundaries => { 417 '>=' => '((`rental_date` > ?) OR (`rental_date` = ? AND ' 418 . '`inventory_id` > ?) OR (`rental_date` = ? AND `inventory_id` ' 419 . '= ? AND (? IS NULL OR `customer_id` >= ?)))', 420 '>' => '((`rental_date` > ?) OR (`rental_date` = ? AND `inventory_id` > ?) OR ' 421 . '(`rental_date` = ? AND `inventory_id` = ? AND ((? IS NULL AND `customer_id` IS NOT NULL) ' 422 . 'OR (`customer_id` > ?)))', 423 # '((`rental_date` > ?) OR (`rental_date` = ? AND ' 424 #. '`inventory_id` > ?) OR (`rental_date` = ? AND `inventory_id` ' 425 # . '= ? AND ((? IS NULL AND `customer_id` IS NOT NULL) ' 426 # . 'OR (`customer_id` > ?))))', 427 '<=' => '((`rental_date` < ?) OR (`rental_date` = ? AND ' 428 . '`inventory_id` < ?) OR (`rental_date` = ? AND `inventory_id` ' 429 . '= ? AND (? IS NULL OR `customer_id` <= ?)))', 430 '<' => '((`rental_date` < ?) OR (`rental_date` = ? AND `inventory_id` < ?) OR ' 431 . '((? IS NOT NULL AND `customer_id` IS NULL) OR (`customer_id` < ?)) OR ' 432 . '(`rental_date` = ? AND `inventory_id` = ?))', 433 434 # '((`rental_date` < ?) OR (`rental_date` = ? AND ' 435 #. '`inventory_id` < ?) OR (`rental_date` = ? AND `inventory_id` ' 436 #. '= ? AND ((? IS NOT NULL AND `customer_id` IS NULL) ' 437 #. 'OR (`customer_id` < ?))))', 438 }, 439 }, 440 'Alternate index on sakila.rental with nullable customer_id', 441); 442 443is_deeply( 444 $n->generate_del_stmt ( 445 tbl_struct => $t, 446 index => 'rental_date', 447 ), 448 { 449 cols => [qw(rental_date inventory_id customer_id)], 450 index => 'rental_date', 451 where => '(`rental_date` = ? AND `inventory_id` = ? AND ' 452 . '((? IS NULL AND `customer_id` IS NULL) OR (`customer_id` = ?)))', 453 slice => [0, 1, 2, 2], 454 scols => [qw(rental_date inventory_id customer_id customer_id)], 455 }, 456 'Alternate index on sakila.rental delete statement with nullable customer_id', 457); 458 459is_deeply( 460 $n->generate_asc_stmt( 461 tbl_struct => $t, 462 cols => $t->{cols}, 463 index => 'rental_date', 464 asc_only => 1, 465 ), 466 { 467 cols => [qw(rental_id rental_date inventory_id customer_id 468 return_date staff_id last_update)], 469 index => 'rental_date', 470 where => '((`rental_date` > ?) OR (`rental_date` = ? AND `inventory_id` > ?) OR ' 471 . '(`rental_date` = ? AND `inventory_id` = ? AND ((? IS NULL AND `customer_id` IS NOT NULL) ' 472 . 'OR (`customer_id` > ?)))', 473 # '((`rental_date` > ?) OR (`rental_date` = ? AND `inventory_id` > ?)' 474 #. ' OR (`rental_date` = ? AND `inventory_id` = ? AND ' 475 #. '((? IS NULL AND `customer_id` IS NOT NULL) OR (`customer_id` > ?))))', 476 slice => [1, 1, 2, 1, 2, 3, 3], 477 scols => [qw(rental_date rental_date inventory_id rental_date inventory_id customer_id customer_id)], 478 boundaries => { 479 '>=' => '((`rental_date` > ?) OR (`rental_date` = ? AND ' 480 . '`inventory_id` > ?) OR (`rental_date` = ? AND `inventory_id` ' 481 . '= ? AND (? IS NULL OR `customer_id` >= ?)))', 482 '>' => '((`rental_date` > ?) OR (`rental_date` = ? AND `inventory_id` > ?) OR ' 483 . '(`rental_date` = ? AND `inventory_id` = ? AND ((? IS NULL AND `customer_id` IS NOT NULL) OR ' 484 . '(`customer_id` > ?)))', 485 # '((`rental_date` > ?) OR (`rental_date` = ? AND ' 486 #. '`inventory_id` > ?) OR (`rental_date` = ? AND `inventory_id` ' 487 #. '= ? AND ((? IS NULL AND `customer_id` IS NOT NULL) ' 488 #. 'OR (`customer_id` > ?))))', 489 '<=' => '((`rental_date` < ?) OR (`rental_date` = ? AND ' 490 . '`inventory_id` < ?) OR (`rental_date` = ? AND `inventory_id` ' 491 . '= ? AND (? IS NULL OR `customer_id` <= ?)))', 492 '<' => '((`rental_date` < ?) OR (`rental_date` = ? AND `inventory_id` < ?) OR ' 493 . '((? IS NOT NULL AND `customer_id` IS NULL) OR (`customer_id` < ?)) OR ' 494 . '(`rental_date` = ? AND `inventory_id` = ?))', 495 # '((`rental_date` < ?) OR (`rental_date` = ? AND ' 496 #. '`inventory_id` < ?) OR (`rental_date` = ? AND `inventory_id` ' 497 #. '= ? AND ((? IS NOT NULL AND `customer_id` IS NULL) ' 498 #. 'OR (`customer_id` < ?))))', 499 }, 500 }, 501 'Alternate index on sakila.rental with nullable customer_id and strict ascending', 502); 503 504# ########################################################################## 505# Switch to the rental table with inventory_id nullable 506# ########################################################################## 507$t = $tp->parse( load_file('t/lib/samples/sakila.rental.null2.sql') ); 508 509is_deeply( 510 $n->generate_asc_stmt( 511 tbl_struct => $t, 512 cols => $t->{cols}, 513 index => 'rental_date', 514 ), 515 { 516 cols => [qw(rental_id rental_date inventory_id customer_id 517 return_date staff_id last_update)], 518 index => 'rental_date', 519 where => '((`rental_date` > ?) OR (`rental_date` = ? AND ((? IS NULL AND `inventory_id` IS NOT NULL)' 520 . ' OR (`inventory_id` > ?)) OR (`rental_date` = ? AND ((? IS NULL AND `inventory_id` IS NULL) OR ' 521 . '(`inventory_id` = ?)) AND `customer_id` >= ?))', 522 # '((`rental_date` > ?) OR ' 523 #. '(`rental_date` = ? AND ((? IS NULL AND `inventory_id` IS NOT NULL) OR (`inventory_id` > ?)))' 524 #. ' OR (`rental_date` = ? AND ((? IS NULL AND `inventory_id` IS NULL) ' 525 #. 'OR (`inventory_id` = ?)) AND `customer_id` >= ?))', 526 slice => [1, 1, 2, 2, 1, 2, 2, 3], 527 scols => [qw(rental_date rental_date inventory_id inventory_id 528 rental_date inventory_id inventory_id customer_id)], 529 boundaries => { 530 '>=' => '((`rental_date` > ?) OR (`rental_date` = ? AND ((? IS NULL AND `inventory_id` IS NOT NULL) OR ' 531 . '(`inventory_id` > ?)) OR (`rental_date` = ? AND ((? IS NULL AND `inventory_id` IS NULL) OR ' 532 . '(`inventory_id` = ?)) AND `customer_id` >= ?))', 533 # '((`rental_date` > ?) OR (`rental_date` = ? AND ' 534 #. '((? IS NULL AND `inventory_id` IS NOT NULL) OR (`inventory_id` ' 535 #. '> ?))) OR (`rental_date` = ? AND ((? IS NULL AND `inventory_id` ' 536 #. 'IS NULL) OR (`inventory_id` = ?)) AND `customer_id` >= ?))', 537 '>' => '((`rental_date` > ?) OR (`rental_date` = ? AND ((? IS NULL AND `inventory_id` IS NOT NULL) OR ' 538 . '(`inventory_id` > ?)) OR (`rental_date` = ? AND ((? IS NULL AND `inventory_id` IS NULL) OR ' 539 . '(`inventory_id` = ?)) AND `customer_id` > ?))', 540 # '((`rental_date` > ?) OR (`rental_date` = ? AND ((? IS NULL ' 541 #. 'AND `inventory_id` IS NOT NULL) OR (`inventory_id` > ?))) OR ' 542 #. '(`rental_date` = ? AND ((? IS NULL AND `inventory_id` IS NULL) ' 543 #. 'OR (`inventory_id` = ?)) AND `customer_id` > ?))', 544 '<=' => '((`rental_date` < ?) OR ((? IS NOT NULL AND `inventory_id` IS NULL) OR (`inventory_id` < ?)) ' 545 . 'OR (`rental_date` = ?) OR (`rental_date` = ? AND ((? IS NULL AND `inventory_id` IS NULL) OR ' 546 . '(`inventory_id` = ?)) AND `customer_id` <= ?))', 547 # '((`rental_date` < ?) OR (`rental_date` = ? AND ((? IS NOT ' 548 #. 'NULL AND `inventory_id` IS NULL) OR (`inventory_id` < ?))) OR ' 549 #. '(`rental_date` = ? AND ((? IS NULL AND `inventory_id` IS NULL) ' 550 #. 'OR (`inventory_id` = ?)) AND `customer_id` <= ?))', 551 '<' => '((`rental_date` < ?) OR ((? IS NOT NULL AND `inventory_id` IS NULL) OR (`inventory_id` < ?)) ' 552 . 'OR (`rental_date` = ?) OR (`rental_date` = ? AND ((? IS NULL AND `inventory_id` IS NULL) OR ' 553 . '(`inventory_id` = ?)) AND `customer_id` < ?))', 554 # '((`rental_date` < ?) OR (`rental_date` = ? AND ((? IS NOT ' 555 #. 'NULL AND `inventory_id` IS NULL) OR (`inventory_id` < ?))) ' 556 #. 'OR (`rental_date` = ? AND ((? IS NULL AND `inventory_id` IS ' 557 #. 'NULL) OR (`inventory_id` = ?)) AND `customer_id` < ?))', 558 }, 559 }, 560 'Alternate index on sakila.rental with nullable inventory_id', 561); 562 563is_deeply( 564 $n->generate_asc_stmt( 565 tbl_struct => $t, 566 cols => $t->{cols}, 567 index => 'rental_date', 568 asc_only => 1, 569 ), 570 { 571 cols => [qw(rental_id rental_date inventory_id customer_id 572 return_date staff_id last_update)], 573 index => 'rental_date', 574 where => '((`rental_date` > ?) OR (`rental_date` = ? AND ((? IS NULL AND `inventory_id` IS NOT NULL) OR ' 575 . '(`inventory_id` > ?)) OR (`rental_date` = ? AND ((? IS NULL AND `inventory_id` IS NULL) OR ' 576 . '(`inventory_id` = ?)) AND `customer_id` > ?))', 577 # '((`rental_date` > ?) OR ' 578 #. '(`rental_date` = ? AND ((? IS NULL AND `inventory_id` IS NOT NULL) OR (`inventory_id` > ?)))' 579 #. ' OR (`rental_date` = ? AND ((? IS NULL AND `inventory_id` IS NULL) ' 580 #. 'OR (`inventory_id` = ?)) AND `customer_id` > ?))', 581 slice => [1, 1, 2, 2, 1, 2, 2, 3], 582 scols => [qw(rental_date rental_date inventory_id inventory_id 583 rental_date inventory_id inventory_id customer_id)], 584 boundaries => { 585 '>=' => '((`rental_date` > ?) OR (`rental_date` = ? AND ((? IS NULL AND `inventory_id` IS NOT NULL) OR ' 586 . '(`inventory_id` > ?)) OR (`rental_date` = ? AND ((? IS NULL AND `inventory_id` IS NULL) OR ' 587 . '(`inventory_id` = ?)) AND `customer_id` >= ?))', 588 # '((`rental_date` > ?) OR (`rental_date` = ? AND ' 589 #. '((? IS NULL AND `inventory_id` IS NOT NULL) OR (`inventory_id` ' 590 #. '> ?))) OR (`rental_date` = ? AND ((? IS NULL AND `inventory_id` ' 591 #. 'IS NULL) OR (`inventory_id` = ?)) AND `customer_id` >= ?))', 592 '>' => '((`rental_date` > ?) OR (`rental_date` = ? AND ((? IS NULL AND `inventory_id` IS NOT NULL) OR ' 593 . '(`inventory_id` > ?)) OR (`rental_date` = ? AND ((? IS NULL AND `inventory_id` IS NULL) OR ' 594 . '(`inventory_id` = ?)) AND `customer_id` > ?))', 595 # '((`rental_date` > ?) OR (`rental_date` = ? AND ((? IS NULL ' 596 #. 'AND `inventory_id` IS NOT NULL) OR (`inventory_id` > ?))) OR ' 597 #. '(`rental_date` = ? AND ((? IS NULL AND `inventory_id` IS NULL) ' 598 #. 'OR (`inventory_id` = ?)) AND `customer_id` > ?))', 599 '<=' => '((`rental_date` < ?) OR ((? IS NOT NULL AND `inventory_id` IS NULL) OR (`inventory_id` < ?)) OR ' 600 . '(`rental_date` = ?) OR (`rental_date` = ? AND ((? IS NULL AND `inventory_id` IS NULL) OR ' 601 . '(`inventory_id` = ?)) AND `customer_id` <= ?))', 602 # '((`rental_date` < ?) OR (`rental_date` = ? AND ((? IS NOT ' 603 #. 'NULL AND `inventory_id` IS NULL) OR (`inventory_id` < ?))) OR ' 604 #. '(`rental_date` = ? AND ((? IS NULL AND `inventory_id` IS NULL) ' 605 #. 'OR (`inventory_id` = ?)) AND `customer_id` <= ?))', 606 '<' => '((`rental_date` < ?) OR ((? IS NOT NULL AND `inventory_id` IS NULL) OR (`inventory_id` < ?)) OR ' 607 . '(`rental_date` = ?) OR (`rental_date` = ? AND ((? IS NULL AND `inventory_id` IS NULL) OR ' 608 . '(`inventory_id` = ?)) AND `customer_id` < ?))', 609 #'((`rental_date` < ?) OR (`rental_date` = ? AND ((? IS NOT ' 610 #. 'NULL AND `inventory_id` IS NULL) OR (`inventory_id` < ?))) ' 611 #. 'OR (`rental_date` = ? AND ((? IS NULL AND `inventory_id` IS ' 612 #. 'NULL) OR (`inventory_id` = ?)) AND `customer_id` < ?))', 613 }, 614 }, 615 'Alternate index on sakila.rental with nullable inventory_id and strict ascending', 616); 617 618# ########################################################################## 619# Switch to the rental table with cols in a different order. 620# ########################################################################## 621$t = $tp->parse( load_file('t/lib/samples/sakila.rental.remix.sql') ); 622 623is_deeply( 624 $n->generate_asc_stmt( 625 tbl_struct => $t, 626 index => 'rental_date', 627 ), 628 { 629 cols => [qw(rental_id rental_date customer_id inventory_id 630 return_date staff_id last_update)], 631 index => 'rental_date', 632 where => '((`rental_date` > ?) OR (`rental_date` = ? AND `inventory_id` > ?)' 633 . ' OR (`rental_date` = ? AND `inventory_id` = ? AND `customer_id` >= ?))', 634 slice => [1, 1, 3, 1, 3, 2], 635 scols => [qw(rental_date rental_date inventory_id rental_date inventory_id customer_id)], 636 boundaries => { 637 '>=' => '((`rental_date` > ?) OR (`rental_date` = ? AND ' 638 . '`inventory_id` > ?) OR (`rental_date` = ? AND `inventory_id` ' 639 . '= ? AND `customer_id` >= ?))', 640 '>' => '((`rental_date` > ?) OR (`rental_date` = ? AND ' 641 . '`inventory_id` > ?) OR (`rental_date` = ? AND `inventory_id` ' 642 . '= ? AND `customer_id` > ?))', 643 '<=' => '((`rental_date` < ?) OR (`rental_date` = ? AND ' 644 . '`inventory_id` < ?) OR (`rental_date` = ? AND `inventory_id` ' 645 . '= ? AND `customer_id` <= ?))', 646 '<' => '((`rental_date` < ?) OR (`rental_date` = ? AND ' 647 . '`inventory_id` < ?) OR (`rental_date` = ? AND `inventory_id` ' 648 . '= ? AND `customer_id` < ?))', 649 }, 650 }, 651 'Out-of-order index on sakila.rental', 652); 653 654# ########################################################################## 655# Switch to table without any indexes 656# ########################################################################## 657$t = $tp->parse( load_file('t/lib/samples/t1.sql') ); 658 659# This test is no longer needed because TableSyncNibble shouldn't 660# ask TableNibbler to asc an indexless table. 661# throws_ok( 662# sub { 663# $n->generate_asc_stmt ( 664# tbl_struct => $t, 665# ) 666# }, 667# qr/Cannot find an ascendable index in table/, 668# 'Error when no good index', 669# ); 670 671is_deeply( 672 $n->generate_cmp_where( 673 cols => [qw(a b c d)], 674 slice => [0, 3], 675 is_nullable => {}, 676 type => '>=', 677 ), 678 { 679 scols => [qw(a a d)], 680 slice => [0, 0, 3], 681 where => '((`a` > ?) OR (`a` = ? AND `d` >= ?))', 682 }, 683 'WHERE for >=', 684); 685 686is_deeply( 687 $n->generate_cmp_where( 688 cols => [qw(a b c d)], 689 slice => [0, 3], 690 is_nullable => {}, 691 type => '>', 692 ), 693 { 694 scols => [qw(a a d)], 695 slice => [0, 0, 3], 696 where => '((`a` > ?) OR (`a` = ? AND `d` > ?))', 697 }, 698 'WHERE for >', 699); 700 701is_deeply( 702 $n->generate_cmp_where( 703 cols => [qw(a b c d)], 704 slice => [0, 3], 705 is_nullable => {}, 706 type => '<=', 707 ), 708 { 709 scols => [qw(a a d)], 710 slice => [0, 0, 3], 711 where => '((`a` < ?) OR (`a` = ? AND `d` <= ?))', 712 }, 713 'WHERE for <=', 714); 715 716is_deeply( 717 $n->generate_cmp_where( 718 cols => [qw(a b c d)], 719 slice => [0, 3], 720 is_nullable => {}, 721 type => '<', 722 ), 723 { 724 scols => [qw(a a d)], 725 slice => [0, 0, 3], 726 where => '((`a` < ?) OR (`a` = ? AND `d` < ?))', 727 }, 728 'WHERE for <', 729); 730 731 732# ############################################################################# 733# Done. 734# ############################################################################# 735exit; 736