| [ Index ] |
|
Code source de osCommerce 2.2ms2-060817 |
1 #!/usr/bin/perl -w 2 # 3 # mysqldiff 4 # 5 # Utility to compare table definitions in two MySQL databases, 6 # and output a patch in the format of ALTER TABLE statements 7 # which converts the first database structure into in the second. 8 # 9 # Developed as part of the http://www.guideguide.com/ project. 10 # If you like hacking Perl in a cool environment, come and work for us! 11 # 12 # See http://www.new.ox.ac.uk/~adam/computing/mysqldiff/ for the 13 # latest version. 14 # 15 # Copyright (c) 2000 Adam Spiers <adam@spiers.net>. All rights 16 # reserved. This program is free software; you can redistribute it 17 # and/or modify it under the same terms as Perl itself. 18 # 19 20 use strict; 21 22 use vars qw($VERSION); 23 $VERSION = '0.25'; 24 25 require 5.004; 26 27 use Carp qw(:DEFAULT cluck); 28 use FindBin qw($Script); 29 use Getopt::Long; 30 31 my %opts = (); 32 GetOptions(\%opts, "help|?", "debug|d:i", 33 "no-old-defs|n", "only-both|o", "table-re|t=s", 34 "host|h=s", "user|u=s", "password|p:s", 35 "host1|h1=s", "user1|u1=s", "password1|p1:s", 36 "host2|h2=s", "user2|u2=s", "password2|p2:s", 37 "tolerant|i" 38 ); 39 40 if (@ARGV != 2 or $opts{help}) { 41 usage(); 42 exit 1; 43 } 44 45 $opts{debug}++ if exists $opts{debug} && $opts{debug} == 0; 46 my $debug = $opts{debug} || 0; 47 48 my $table_re; 49 $table_re = qr/$opts{'table-re'}/ if $opts{'table-re'}; 50 51 my @db = (); 52 for my $num (0, 1) { 53 $db[$num] = parse_arg($ARGV[$num], $num); 54 } 55 56 diff_dbs(@db); 57 58 exit 0; 59 60 ############################################################################## 61 # 62 63 sub usage { 64 print STDERR @_, "\n" if @_; 65 die <<EOF; 66 Usage: $Script [ options ] <database1> <database2> 67 68 Options: 69 -?, --help show this help 70 -d, --debug[=N] enable debugging [level N, default 1] 71 -o, --only-both only output changes for tables in both databases 72 -n, --no-old-defs suppress comments describing old definitions 73 -t, --table-re=REGEXP restrict comparisons to tables matching REGEXP 74 -i, --tolerant ignore DEFAULT and formatting changes 75 76 -h, --host=... connect to host 77 -u, --user=... user for login if not current user 78 -p, --password[=...] password to use when connecting to server 79 80 for <databaseN> only, where N == 1 or 2, 81 -hN, --hostN=... connect to host 82 -uN, --userN=... user for login if not current user 83 -pN, --passwordN[=...] password to use when connecting to server 84 85 Databases can be either files or database names. 86 If there is an ambiguity, the file will be preferred; 87 to prevent this prefix the database argument with `db:'. 88 EOF 89 } 90 91 sub diff_dbs { 92 my @db = @_; 93 94 debug(1, "comparing databases\n"); 95 96 my @changes = (); 97 98 foreach my $table1 ($db[0]->tables()) { 99 my $name = $table1->name(); 100 if ($table_re && $name !~ $table_re) { 101 debug(2, " table `$name' didn't match $opts{'table-re'}; ignoring\n"); 102 next; 103 } 104 debug(2, " looking at tables called `$name'\n"); 105 if (my $table2 = $db[1]->table_by_name($name)) { 106 debug(4, " comparing tables called `$name'\n"); 107 push @changes, diff_tables($table1, $table2); 108 } 109 else { 110 debug(3, " table `$name' dropped\n"); 111 push @changes, "DROP TABLE $name;\n\n" 112 unless $opts{'only-both'}; 113 } 114 } 115 116 foreach my $table2 ($db[1]->tables()) { 117 my $name = $table2->name(); 118 if ($table_re && $name !~ $table_re) { 119 debug(2, " table `$name' matched $opts{'table-re'}; ignoring\n"); 120 next; 121 } 122 if (! $db[0]->table_by_name($name)) { 123 debug(3, " table `$name' added\n"); 124 push @changes, $table2->def() . "\n" 125 unless $opts{'only-both'}; 126 } 127 } 128 129 if (@changes) { 130 diff_banner(@db); 131 print @changes; 132 } 133 } 134 135 sub diff_banner { 136 my @db = @_; 137 138 my $summary1 = $db[0]->summary(); 139 my $summary2 = $db[1]->summary(); 140 141 my $now = scalar localtime(); 142 print <<EOF; 143 ## mysqldiff $VERSION 144 ## 145 ## run on $now 146 ## 147 ## --- $summary1 148 ## +++ $summary2 149 150 EOF 151 } 152 153 sub diff_tables { 154 my @changes = (diff_fields(@_), 155 diff_indices(@_), 156 diff_primary_key(@_)); 157 if (@changes) { 158 $changes[-1] .= "\n"; 159 } 160 return @changes; 161 } 162 163 sub diff_fields { 164 my ($table1, $table2) = @_; 165 166 my $name1 = $table1->name(); 167 168 my %fields1 = %{ $table1->fields() }; 169 my %fields2 = %{ $table2->fields() }; 170 171 my @changes = (); 172 173 foreach my $field (keys %fields1) { 174 my $f1 = $fields1{$field}; 175 if (my $f2 = $fields2{$field}) { 176 if ($f1 ne $f2) { 177 if (not $opts{tolerant} or (($f1 !~ m/$f2\(\d+,\d+\)/) and 178 ($f1 ne "$f2 DEFAULT '' NOT NULL") and 179 ($f1 ne "$f2 NOT NULL") 180 )) 181 { 182 debug(4, " field `$field' changed\n"); 183 184 my $change = "ALTER TABLE $name1 CHANGE COLUMN $field $field $f2;"; 185 $change .= " # was $f1" unless $opts{'no-old-defs'}; 186 $change .= "\n"; 187 push @changes, $change; 188 } 189 } 190 } 191 else { 192 debug(4, " field `$field' removed\n"); 193 my $change = "ALTER TABLE $name1 DROP COLUMN $field;"; 194 $change .= " # was $fields1{$field}" unless $opts{'no-old-defs'}; 195 $change .= "\n"; 196 push @changes, $change; 197 } 198 } 199 200 foreach my $field (keys %fields2) { 201 if (! $fields1{$field}) { 202 debug(4, " field `$field' added\n"); 203 push @changes, "ALTER TABLE $name1 ADD COLUMN $field $fields2{$field};\n"; 204 } 205 } 206 207 return @changes; 208 } 209 210 sub diff_indices { 211 my ($table1, $table2) = @_; 212 213 my $name1 = $table1->name(); 214 215 my %indices1 = %{ $table1->indices() }; 216 my %indices2 = %{ $table2->indices() }; 217 218 my @changes = (); 219 220 foreach my $index (keys %indices1) { 221 my $old_type = $table1->is_unique_index($index) ? 'UNIQUE' : 'INDEX'; 222 223 if ($indices2{$index}) { 224 if ($indices1{$index} ne $indices2{$index} || 225 ($table1->is_unique_index($index) 226 xor 227 $table2->is_unique_index($index))) 228 { 229 debug(4, " index `$index' changed\n"); 230 my $new_type = $table2->is_unique_index($index) ? 'UNIQUE' : 'INDEX'; 231 232 my $changes = ''; 233 if ($indices1{$index}) { 234 $changes .= "ALTER TABLE $name1 DROP INDEX $index;"; 235 $changes .= " # was $old_type ($indices1{$index})" unless $opts{'no-old-defs'}; 236 $changes .= "\n"; 237 } 238 239 $changes .= <<EOF; 240 ALTER TABLE $name1 ADD $new_type $index ($indices2{$index}); 241 EOF 242 push @changes, $changes; 243 } 244 } 245 else { 246 debug(4, " index `$index' removed\n"); 247 my $change = "ALTER TABLE $name1 DROP INDEX $index;"; 248 $change .= " # was $old_type ($indices1{$index})" unless $opts{'no-old-defs'}; 249 $change .= "\n"; 250 push @changes, $change; 251 } 252 } 253 254 foreach my $index (keys %indices2) { 255 if (! $indices1{$index}) { 256 debug(4, " index `$index' added\n"); 257 push @changes, 258 "ALTER TABLE $name1 ADD INDEX $index ($indices2{$index});\n"; 259 } 260 } 261 262 return @changes; 263 } 264 265 sub diff_primary_key { 266 my ($table1, $table2) = @_; 267 268 my $name1 = $table1->name(); 269 270 my $primary1 = $table1->primary_key(); 271 my $primary2 = $table2->primary_key(); 272 273 my @changes = (); 274 if (($primary1 xor $primary2) || ($primary1 && ($primary1 ne $primary2))) { 275 debug(4, " primary key changed\n"); 276 my $change = "ALTER TABLE $name1 DROP PRIMARY KEY;"; 277 $change .= " # was ($primary1)" unless $opts{'no-old-defs'}; 278 $change .= <<EOF; 279 280 ALTER TABLE $name1 ADD PRIMARY KEY ($primary2); 281 EOF 282 push @changes, $change; 283 } 284 285 return @changes; 286 } 287 288 ############################################################################## 289 290 sub auth_args { 291 my %auth = @_; 292 my $args = ''; 293 for my $arg (qw/host user password/) { 294 $args .= " --$arg=$auth{$arg}" if $auth{$arg}; 295 } 296 return $args; 297 } 298 299 sub available_dbs { 300 my %auth = @_; 301 my $args = auth_args(%auth); 302 303 # evil but we don't use DBI because I don't want to implement -p properly 304 # not that this works with -p anyway ... 305 open(MYSQLSHOW, "mysqlshow$args |") 306 or die "Couldn't execute `mysqlshow$args': $!\n"; 307 my @dbs = (); 308 while (<MYSQLSHOW>) { 309 next unless /^\| (\w+)/; 310 push @dbs, $1; 311 } 312 close(MYSQLSHOW); 313 314 return map { $_ => 1 } @dbs; 315 } 316 317 sub parse_arg { 318 my ($arg, $num) = @_; 319 320 debug(1, "parsing arg $num: `$arg'\n"); 321 322 my $authnum = $num + 1; 323 324 my %auth = (); 325 for my $auth (qw/host user password/) { 326 $auth{$auth} = $opts{"$auth$authnum"} || $opts{$auth}; 327 delete $auth{$auth} unless $auth{$auth}; 328 } 329 330 if ($arg =~ /^db:(.*)/) { 331 return new MySQL::Database(db => $1, %auth); 332 } 333 334 if ($opts{"host$authnum"} || 335 $opts{"user$authnum"} || 336 $opts{"password$authnum"}) 337 { 338 return new MySQL::Database(db => $arg, %auth); 339 } 340 341 if (-e $arg) { 342 return new MySQL::Database(file => $arg, %auth); 343 } 344 345 my %dbs = available_dbs(%auth); 346 debug(2, " available databases: ", (join ', ', keys %dbs), "\n"); 347 348 if ($dbs{$arg}) { 349 return new MySQL::Database(db => $arg, %auth); 350 } 351 352 usage("`$arg' is not a valid file or database.\n"); 353 exit 1; 354 } 355 356 sub debug { 357 my $level = shift; 358 print STDERR @_ if $debug >= $level && @_; 359 } 360 361 ############################################################################## 362 # 363 364 package MySQL::Database; 365 366 use Carp qw(:DEFAULT cluck); 367 368 sub debug { &::debug } 369 370 sub new { 371 my $class = shift; 372 my %p = @_; 373 my $self = {}; 374 bless $self, ref $class || $class; 375 376 debug(2, " constructing new MySQL::Database\n"); 377 378 my $args = &::auth_args(%p); 379 debug(3, " auth args: $args\n"); 380 381 if ($p{file}) { 382 $self->{_source} = { file => $p{file} }; 383 debug(3, " fetching table defs from file $p{file}\n"); 384 385 # FIXME: option to avoid create-and-dump bit 386 # create a temporary database using defs from file ... 387 # hopefully the temp db is unique! 388 my $temp_db = sprintf "test_mysqldiff_temp_%d_%d", time(), $$; 389 debug(3, " creating temporary database $temp_db\n"); 390 391 open(DEFS, $p{file}) 392 or die "Couldn't open `$p{file}': $!\n"; 393 open(MYSQL, "| mysql $args") 394 or die "Couldn't execute `mysql$args': $!\n"; 395 print MYSQL <<EOF; 396 CREATE DATABASE $temp_db; 397 USE $temp_db; 398 EOF 399 print MYSQL <DEFS>; 400 close(DEFS); 401 close(MYSQL); 402 403 # ... and then retrieve defs from mysqldump. Hence we've used 404 # MySQL to massage the defs file into canonical form. 405 $self->_get_defs($temp_db, $args); 406 407 debug(3, " dropping temporary database $temp_db\n"); 408 open(MYSQL, "| mysql $args") 409 or die "Couldn't execute `mysql$args': $!\n"; 410 print MYSQL "DROP DATABASE $temp_db;\n"; 411 close(MYSQL); 412 } 413 elsif ($p{db}) { 414 $self->{_source} = { db => $p{db}, auth => $args }; 415 debug(3, " fetching table defs from db $p{db}\n"); 416 $self->_get_defs($p{db}, $args); 417 } 418 else { 419 confess "MySQL::Database::new called without db or file params"; 420 } 421 422 $self->_parse_defs(); 423 424 return $self; 425 } 426 427 sub _get_defs { 428 my $self = shift; 429 my ($db, $args) = @_; 430 431 open(MYSQLDUMP, "mysqldump -d $args $db |") 432 or die "Couldn't read $db}'s table defs via mysqldump: $!\n"; 433 debug(3, " running mysqldump -d $args $db\n"); 434 $self->{_defs} = [ <MYSQLDUMP> ]; 435 close(MYSQLDUMP); 436 } 437 438 sub _parse_defs { 439 my $self = shift; 440 441 return if $self->{_tables}; 442 443 debug(3, " parsing table defs\n"); 444 my $defs = join '', grep ! /^\s*(--|\#)/, @{$self->{_defs}}; 445 my @tables = split /(?=^\s*create\s+table\s+)/im, $defs; 446 foreach my $table (@tables) { 447 next unless $table =~ /create\s+table/i; 448 my $obj = MySQL::Table->new(source => $self->{_source}, 449 def => $table); 450 push @{$self->{_tables}}, $obj; 451 $self->{_by_name}{$obj->name()} = $obj; 452 } 453 } 454 455 sub tables { 456 return @{$_[0]->{_tables}}; 457 } 458 459 sub table_by_name { 460 my $self = shift; 461 my ($name) = @_; 462 return $self->{_by_name}{$name}; 463 } 464 465 sub summary { 466 my $self = shift; 467 468 if ($self->{_source}{file}) { 469 return "file: " . $self->{_source}{file}; 470 } 471 elsif ($self->{_source}{db}) { 472 my $args = $self->{_source}{auth}; 473 $args =~ tr/-//d; 474 $args =~ s/\bpassword=\S+//; 475 $args =~ s/^\s*(.*?)\s*$/$1/; 476 my $summary = " db: " . $self->{_source}{db}; 477 $summary .= " ($args)" if $args; 478 return $summary; 479 } 480 else { 481 return 'unknown'; 482 } 483 } 484 485 ############################################################################## 486 # 487 488 package MySQL::Table; 489 490 use Carp qw(:DEFAULT cluck); 491 492 sub debug { &::debug } 493 494 sub new { 495 my $class = shift; 496 my %p = @_; 497 my $self = {}; 498 bless $self, ref $class || $class; 499 500 debug(4, " constructing new MySQL::Table\n"); 501 502 if (! $p{def}) { 503 croak "MySQL::Table::new called without def params"; 504 } 505 506 $self->parse($p{def}); 507 508 $self->{_source} = $p{source}; 509 510 return $self; 511 } 512 513 sub parse { 514 my $self = shift; 515 my ($def) = @_; 516 517 $def =~ s/\n+/\n/; 518 $self->{_def} = $def; 519 $self->{_lines} = [ grep ! /^\s*$/, split /(?=^)/m, $def ]; 520 my @lines = @{$self->{_lines}}; 521 522 debug(5, " parsing table def\n"); 523 524 my $name; 525 if ($lines[0] =~ /^\s*create\s+table\s+(\S+)\s+\(\s*$/i) { 526 $name = $self->{_name} = $1; 527 debug(5, " got table name `$name'\n"); 528 shift @lines; 529 } 530 else { 531 croak "couldn't figure out table name"; 532 } 533 534 while (@lines) { 535 $_ = shift @lines; 536 s/^\s*(.*?),?\s*$/$1/; # trim whitespace and trailing commas 537 if (/^\);$/) { 538 last; 539 } 540 541 if (/^PRIMARY\s+KEY\s+(.+)$/) { 542 my $primary = $1; 543 croak "two primary keys in table `$name': `$primary', `", 544 $self->{_primary_key}, "'\n" 545 if $self->{_primary_key}; 546 $self->{_primary_key} = $primary; 547 debug(6, " got primary key `$primary'\n"); 548 next; 549 } 550 551 if (/^(KEY|UNIQUE)\s+(\S+?)\s+\((.*)\)$/) { 552 my ($type, $key, $val) = ($1, $2, $3); 553 croak "index `$key' duplicated in table `$name'\n" 554 if $self->{_indices}{$key}; 555 $self->{_indices}{$key} = $val; 556 $self->{_unique_index}{$key} = ($type =~ /unique/i) ? 1 : 0; 557 debug(6, " got ", 558 ($type =~ /unique/i) ? 'unique ' : '', 559 "index key `$key': ($val)\n"); 560 next; 561 } 562 563 if (/^(\S+)\s*(.*)/) { 564 my ($field, $def) = ($1, $2); 565 croak "definition for field `$field' duplicated in table `$name'\n" 566 if $self->{_fields}{$field}; 567 $self->{_fields}{$field} = $def; 568 debug(6, " got field def `$field': $def\n"); 569 next; 570 } 571 572 croak "unparsable line in definition for table `$name':\n$_"; 573 } 574 575 if (@lines) { 576 my $name = $self->name(); 577 warn "table `$name' had trailing garbage:\n", join '', @lines; 578 } 579 } 580 581 sub def { $_[0]->{_def} } 582 sub name { $_[0]->{_name} } 583 sub source { $_[0]->{_source} } 584 sub fields { $_[0]->{_fields} || {} } 585 sub indices { $_[0]->{_indices} || {} } 586 sub primary_key { $_[0]->{_primary_key} } 587 sub is_unique_index { $_[0]->{_unique_index}{$_[1]} }
titre
Description
Corps
titre
Description
Corps
titre
Description
Corps
titre
Corps
| Généré le : Mon Nov 26 19:48:25 2007 | par Balluche grâce à PHPXref 0.7 |
|
|