1#!/usr/bin/perl -w 2 3use strict; 4 5use Test::More; 6use MySQL::Diff; 7use MySQL::Diff::Database; 8 9my $TEST_USER = 'test'; 10my @VALID_ENGINES = qw(MyISAM InnoDB); 11my $VALID_ENGINES = join '|', @VALID_ENGINES; 12 13my %tables = ( 14 foo1 => ' 15CREATE TABLE foo ( 16 id INT(11) NOT NULL auto_increment, 17 foreign_id INT(11) NOT NULL, 18 PRIMARY KEY (id) 19) DEFAULT CHARACTER SET utf8; 20', 21 22 foo2 => ' 23# here be a comment 24 25CREATE TABLE foo ( 26 id INT(11) NOT NULL auto_increment, 27 foreign_id INT(11) NOT NULL, # another random comment 28 field BLOB, 29 PRIMARY KEY (id) 30) DEFAULT CHARACTER SET utf8; 31', 32 33 foo3 => ' 34CREATE TABLE foo ( 35 id INT(11) NOT NULL auto_increment, 36 foreign_id INT(11) NOT NULL, 37 field TINYBLOB, 38 PRIMARY KEY (id) 39) DEFAULT CHARACTER SET utf8; 40', 41 42 foo4 => ' 43CREATE TABLE foo ( 44 id INT(11) NOT NULL auto_increment, 45 foreign_id INT(11) NOT NULL, 46 field TINYBLOB, 47 PRIMARY KEY (id, foreign_id) 48) DEFAULT CHARACTER SET utf8; 49', 50 51 bar1 => ' 52CREATE TABLE bar ( 53 id INT AUTO_INCREMENT NOT NULL PRIMARY KEY, 54 ctime DATETIME, 55 utime DATETIME, 56 name CHAR(16), 57 age INT 58) DEFAULT CHARACTER SET utf8; 59', 60 61 bar2 => ' 62CREATE TABLE bar ( 63 id INT AUTO_INCREMENT NOT NULL PRIMARY KEY, 64 ctime DATETIME, 65 utime DATETIME, # FOO! 66 name CHAR(16), 67 age INT, 68 UNIQUE (name, age) 69) DEFAULT CHARACTER SET utf8; 70', 71 72 bar3 => ' 73CREATE TABLE bar ( 74 id INT AUTO_INCREMENT NOT NULL PRIMARY KEY, 75 ctime DATETIME, 76 utime DATETIME, 77 name CHAR(16), 78 age INT, 79 UNIQUE (id, name, age) 80) DEFAULT CHARACTER SET utf8; 81', 82 83 baz1 => ' 84CREATE TABLE baz ( 85 firstname CHAR(16), 86 surname CHAR(16) 87) DEFAULT CHARACTER SET utf8; 88', 89 90 baz2 => ' 91CREATE TABLE baz ( 92 firstname CHAR(16), 93 surname CHAR(16), 94 UNIQUE (firstname, surname) 95) DEFAULT CHARACTER SET utf8; 96', 97 98 baz3 => ' 99CREATE TABLE baz ( 100 firstname CHAR(16), 101 surname CHAR(16), 102 KEY (firstname, surname) 103) DEFAULT CHARACTER SET utf8; 104', 105 106 qux1 => ' 107CREATE TABLE qux ( 108 age INT 109) DEFAULT CHARACTER SET utf8; 110', 111 112 qux2 => ' 113CREATE TABLE qux ( 114 id INT NOT NULL AUTO_INCREMENT, 115 age INT, 116 PRIMARY KEY (id) 117) DEFAULT CHARACTER SET utf8; 118', 119 120 qux3 => ' 121CREATE TABLE qux ( 122 id INT NOT NULL AUTO_INCREMENT, 123 age INT, 124 UNIQUE KEY (id) 125) DEFAULT CHARACTER SET utf8; 126', 127 128); 129 130my %tests = ( 131 'add column' => 132 [ 133 {}, 134 @tables{qw/foo1 foo2/}, 135 '## mysqldiff <VERSION> 136## 137## Run on <DATE> 138## 139## --- file: tmp.db1 140## +++ file: tmp.db2 141 142ALTER TABLE foo ADD COLUMN field blob; 143', 144 ], 145 146 'drop column' => 147 [ 148 {}, 149 @tables{qw/foo2 foo1/}, 150 '## mysqldiff <VERSION> 151## 152## Run on <DATE> 153## 154## --- file: tmp.db1 155## +++ file: tmp.db2 156 157ALTER TABLE foo DROP COLUMN field; # was blob 158', 159 ], 160 161 'change column' => 162 [ 163 {}, 164 @tables{qw/foo2 foo3/}, 165 '## mysqldiff <VERSION> 166## 167## Run on <DATE> 168## 169## --- file: tmp.db1 170## +++ file: tmp.db2 171 172ALTER TABLE foo CHANGE COLUMN field field tinyblob; # was blob 173' 174 ], 175 176 'no-old-defs' => 177 [ 178 { 'no-old-defs' => 1 }, 179 @tables{qw/foo2 foo1/}, 180 '## mysqldiff <VERSION> 181## 182## Run on <DATE> 183## Options: no-old-defs 184## 185## --- file: tmp.db1 186## +++ file: tmp.db2 187 188ALTER TABLE foo DROP COLUMN field; 189', 190 ], 191 192 'add table' => 193 [ 194 { }, 195 $tables{foo1}, $tables{foo2} . $tables{bar1}, 196 '## mysqldiff <VERSION> 197## 198## Run on <DATE> 199## 200## --- file: tmp.db1 201## +++ file: tmp.db2 202 203ALTER TABLE foo ADD COLUMN field blob; 204CREATE TABLE bar ( 205 id int(11) NOT NULL auto_increment, 206 ctime datetime default NULL, 207 utime datetime default NULL, 208 name char(16) default NULL, 209 age int(11) default NULL, 210 PRIMARY KEY (id) 211) ENGINE=InnoDB DEFAULT CHARSET=utf8; 212 213', 214 ], 215 216 'drop table' => 217 [ 218 { }, 219 $tables{foo1} . $tables{bar1}, $tables{foo2}, 220 '## mysqldiff <VERSION> 221## 222## Run on <DATE> 223## 224## --- file: tmp.db1 225## +++ file: tmp.db2 226 227DROP TABLE bar; 228 229ALTER TABLE foo ADD COLUMN field blob; 230', 231 ], 232 233 'only-both' => 234 [ 235 { 'only-both' => 1 }, 236 $tables{foo1} . $tables{bar1}, $tables{foo2}, 237 '## mysqldiff <VERSION> 238## 239## Run on <DATE> 240## Options: only-both 241## 242## --- file: tmp.db1 243## +++ file: tmp.db2 244 245ALTER TABLE foo ADD COLUMN field blob; 246', 247 ], 248 249 'keep-old-tables' => 250 [ 251 { 'keep-old-tables' => 1 }, 252 $tables{foo1} . $tables{bar1}, $tables{foo2}, 253 '## mysqldiff <VERSION> 254## 255## Run on <DATE> 256## Options: keep-old-tables 257## 258## --- file: tmp.db1 259## +++ file: tmp.db2 260 261ALTER TABLE foo ADD COLUMN field blob; 262', 263 ], 264 265 'table-re' => 266 [ 267 { 'table-re' => 'ba' }, 268 $tables{foo1} . $tables{bar1} . $tables{baz1}, 269 $tables{foo2} . $tables{bar2} . $tables{baz2}, 270 '## mysqldiff <VERSION> 271## 272## Run on <DATE> 273## Options: table-re=ba 274## 275## --- file: tmp.db1 276## +++ file: tmp.db2 277 278ALTER TABLE bar ADD UNIQUE name (name,age); 279ALTER TABLE baz ADD UNIQUE firstname (firstname,surname); 280', 281 ], 282 283 'single-transaction' => 284 [ 285 { 'single-transaction' => 'ba' }, 286 $tables{foo1} . $tables{bar1} . $tables{baz1}, 287 $tables{foo2} . $tables{bar2} . $tables{baz2}, 288 '## mysqldiff <VERSION> 289## 290## Run on <DATE> 291## Options: single-transaction=ba 292## 293## --- file: tmp.db1 294## +++ file: tmp.db2 295 296ALTER TABLE bar ADD UNIQUE name (name,age); 297ALTER TABLE baz ADD UNIQUE firstname (firstname,surname); 298ALTER TABLE foo ADD COLUMN field blob; 299', 300 ], 301 302 'drop primary key with auto weirdness' => 303 [ 304 {}, 305 $tables{foo3}, 306 $tables{foo4}, 307 '## mysqldiff <VERSION> 308## 309## Run on <DATE> 310## 311## --- file: tmp.db1 312## +++ file: tmp.db2 313 314ALTER TABLE foo ADD INDEX (id); # auto columns must always be indexed 315ALTER TABLE foo DROP PRIMARY KEY; # was (id) 316ALTER TABLE foo ADD PRIMARY KEY (id,foreign_id); 317ALTER TABLE foo DROP INDEX id; 318', 319 ], 320 321 'drop additional primary key' => 322 [ 323 {}, 324 $tables{foo4}, 325 $tables{foo3}, 326 '## mysqldiff <VERSION> 327## 328## Run on <DATE> 329## 330## --- file: tmp.db1 331## +++ file: tmp.db2 332 333ALTER TABLE foo ADD INDEX (id); # auto columns must always be indexed 334ALTER TABLE foo DROP PRIMARY KEY; # was (id,foreign_id) 335ALTER TABLE foo ADD PRIMARY KEY (id); 336ALTER TABLE foo DROP INDEX id; 337', 338 ], 339 340 'unique changes' => 341 [ 342 {}, 343 $tables{bar1}, 344 $tables{bar2}, 345 '## mysqldiff <VERSION> 346## 347## Run on <DATE> 348## 349## --- file: tmp.db1 350## +++ file: tmp.db2 351 352ALTER TABLE bar ADD UNIQUE name (name,age); 353', 354 ], 355 356 'drop index' => 357 [ 358 {}, 359 $tables{bar2}, 360 $tables{bar1}, 361 '## mysqldiff <VERSION> 362## 363## Run on <DATE> 364## 365## --- file: tmp.db1 366## +++ file: tmp.db2 367 368ALTER TABLE bar DROP INDEX name; # was UNIQUE (name,age) 369', 370 ], 371 372 'alter indices' => 373 [ 374 {}, 375 $tables{bar2}, 376 $tables{bar3}, 377 '## mysqldiff <VERSION> 378## 379## Run on <DATE> 380## 381## --- file: tmp.db1 382## +++ file: tmp.db2 383 384ALTER TABLE bar DROP INDEX name; # was UNIQUE (name,age) 385ALTER TABLE bar ADD UNIQUE id (id,name,age); 386', 387 ], 388 389 'alter indices 2' => 390 [ 391 {}, 392 $tables{bar3}, 393 $tables{bar2}, 394 '## mysqldiff <VERSION> 395## 396## Run on <DATE> 397## 398## --- file: tmp.db1 399## +++ file: tmp.db2 400 401ALTER TABLE bar DROP INDEX id; # was UNIQUE (id,name,age) 402ALTER TABLE bar ADD UNIQUE name (name,age); 403', 404 ], 405 406 'add unique index' => 407 [ 408 {}, 409 $tables{bar1}, 410 $tables{bar3}, 411 '## mysqldiff <VERSION> 412## 413## Run on <DATE> 414## 415## --- file: tmp.db1 416## +++ file: tmp.db2 417 418ALTER TABLE bar ADD UNIQUE id (id,name,age); 419', 420 ], 421 422 'drop unique index' => 423 [ 424 {}, 425 $tables{bar3}, 426 $tables{bar1}, 427 '## mysqldiff <VERSION> 428## 429## Run on <DATE> 430## 431## --- file: tmp.db1 432## +++ file: tmp.db2 433 434ALTER TABLE bar DROP INDEX id; # was UNIQUE (id,name,age) 435', 436 ], 437 438 'alter unique index' => 439 [ 440 {}, 441 $tables{baz2}, 442 $tables{baz3}, 443 '## mysqldiff <VERSION> 444## 445## Run on <DATE> 446## 447## --- file: tmp.db1 448## +++ file: tmp.db2 449 450ALTER TABLE baz DROP INDEX firstname; # was UNIQUE (firstname,surname) 451ALTER TABLE baz ADD INDEX firstname (firstname,surname); 452', 453 ], 454 455 'alter unique index 2' => 456 [ 457 {}, 458 $tables{baz3}, 459 $tables{baz2}, 460 '## mysqldiff <VERSION> 461## 462## Run on <DATE> 463## 464## --- file: tmp.db1 465## +++ file: tmp.db2 466 467ALTER TABLE baz DROP INDEX firstname; # was INDEX (firstname,surname) 468ALTER TABLE baz ADD UNIQUE firstname (firstname,surname); 469', 470 ], 471 472 'add auto increment primary key' => 473 [ 474 {}, 475 $tables{qux1}, 476 $tables{qux2}, 477 '## mysqldiff <VERSION> 478## 479## Run on <DATE> 480## 481## --- file: tmp.db1 482## +++ file: tmp.db2 483 484ALTER TABLE qux ADD COLUMN id int(11) NOT NULL AUTO_INCREMENT PRIMARY KEY; 485', 486 ], 487 488 'add auto increment unique key' => 489 [ 490 {}, 491 $tables{qux1}, 492 $tables{qux3}, 493 '## mysqldiff <VERSION> 494## 495## Run on <DATE> 496## 497## --- file: tmp.db1 498## +++ file: tmp.db2 499 500ALTER TABLE qux ADD COLUMN id int(11) NOT NULL AUTO_INCREMENT UNIQUE KEY; 501', 502 ], 503); 504 505my $BAIL = check_setup(); 506plan skip_all => $BAIL if($BAIL); 507 508my $total = scalar(keys %tests) * 5; 509plan tests => $total; 510 511use Data::Dumper; 512 513my @tests = (keys %tests); #keys %tests 514 515{ 516 my %debug = ( debug_file => 'debug.log', debug => 9 ); 517 unlink $debug{debug_file}; 518 519 for my $test (@tests) { 520 note( "Testing $test\n" ); 521 522 my ($opts, $db1_defs, $db2_defs, $expected) = @{$tests{$test}}; 523 524 note("test=".Dumper($tests{$test})); 525 526 my $diff = MySQL::Diff->new(%$opts, %debug); 527 isa_ok($diff,'MySQL::Diff'); 528 529 my $db1 = get_db($db1_defs, 1, $opts->{'table-re'}, $opts->{'single_transaction'}); 530 my $db2 = get_db($db2_defs, 2, $opts->{'table-re'}, $opts->{'single_transaction'}); 531 532 my $d1 = $diff->register_db($db1, 1); 533 my $d2 = $diff->register_db($db2, 2); 534 note("d1=" . Dumper($d1)); 535 note("d2=" . Dumper($d2)); 536 537 isa_ok($d1, 'MySQL::Diff::Database'); 538 isa_ok($d2, 'MySQL::Diff::Database'); 539 540 my $diffs = $diff->diff(); 541 $diffs =~ s/^## mysqldiff [\d.]+/## mysqldiff <VERSION>/m; 542 $diffs =~ s/^## Run on .*/## Run on <DATE>/m; 543 $diffs =~ s{/\*!40\d{3} .*? \*/;\n*}{}m; 544 $diffs =~ s/ *$//gm; 545 for ($diffs, $expected) { 546 s/ default\b/ DEFAULT/gi; 547 s/PRIMARY KEY +\(/PRIMARY KEY (/g; 548 s/auto_increment/AUTO_INCREMENT/gi; 549 } 550 551 my $engine = 'InnoDB'; 552 my $ENGINE_RE = qr/ENGINE=($VALID_ENGINES)/; 553 if ($diffs =~ $ENGINE_RE) { 554 $engine = $1; 555 $expected =~ s/$ENGINE_RE/ENGINE=$engine/g; 556 } 557 558 note("diffs = " . Dumper($diffs)); 559 note("expected = " . Dumper($expected)); 560 561 is_deeply($diffs, $expected, ".. expected differences for $test"); 562 563 # Now test that $diffs correctly patches $db1_defs to $db2_defs. 564 my $patched = get_db($db1_defs . "\n" . $diffs, 1, $opts->{'table-re'}, $opts->{'single-transaction'}); 565 $diff->register_db($patched, 1); 566 is_deeply($diff->diff(), '', ".. patched differences for $test"); 567 } 568} 569 570 571sub get_db { 572 my ($defs, $num, $table_re, $single_transaction) = @_; 573 574 note("defs=$defs"); 575 576 my $file = "tmp.db$num"; 577 open(TMP, ">$file") or die "open: $!"; 578 print TMP $defs; 579 close(TMP); 580 my $db = MySQL::Diff::Database->new(file => $file, auth => { user => $TEST_USER }, 'table-re' => $table_re, 'single-transaction' => $single_transaction); 581 unlink $file; 582 return $db; 583} 584 585sub check_setup { 586 my $failure_string = "Cannot proceed with tests without "; 587 _output_matches("mysql --help", qr/--password/) or 588 return $failure_string . 'a MySQL client'; 589 _output_matches("mysqldump --help", qr/--password/) or 590 return $failure_string . 'mysqldump'; 591 _output_matches("echo status | mysql -u $TEST_USER 2>&1", qr/Connection id:/) or 592 return $failure_string . 'a valid connection'; 593 return ''; 594} 595 596sub _output_matches { 597 my ($cmd, $re) = @_; 598 my ($exit, $out) = _run($cmd); 599 600 my $issue; 601 if (defined $exit) { 602 if ($exit == 0) { 603 $issue = "Output from '$cmd' didn't match /$re/:\n$out" if $out !~ $re; 604 } 605 else { 606 $issue = "'$cmd' exited with status code $exit"; 607 } 608 } 609 else { 610 $issue = "Failed to execute '$cmd'"; 611 } 612 613 if ($issue) { 614 warn $issue, "\n"; 615 return 0; 616 } 617 return 1; 618} 619 620sub _run { 621 my ($cmd) = @_; 622 unless (open(CMD, "$cmd|")) { 623 return (undef, "Failed to execute '$cmd': $!\n"); 624 } 625 my $out = join '', <CMD>; 626 close(CMD); 627 return ($?, $out); 628} 629