| #!/usr/bin/perl -w |
| ############################################################################### |
| # $Id$ |
| ############################################################################### |
| # Licensed to the Apache Software Foundation (ASF) under one or more |
| # contributor license agreements. See the NOTICE file distributed with |
| # this work for additional information regarding copyright ownership. |
| # The ASF licenses this file to You under the Apache License, Version 2.0 |
| # (the "License"); you may not use this file except in compliance with |
| # the License. You may obtain a copy of the License at |
| # |
| # http://www.apache.org/licenses/LICENSE-2.0 |
| # |
| # Unless required by applicable law or agreed to in writing, software |
| # distributed under the License is distributed on an "AS IS" BASIS, |
| # WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. |
| # See the License for the specific language governing permissions and |
| # limitations under the License. |
| ############################################################################### |
| |
| =head1 NAME |
| |
| VCL::vcld - VCL upgrade_database |
| |
| =head1 SYNOPSIS |
| |
| perl ./upgrade_database.pl |
| |
| =head1 DESCRIPTION |
| |
| |
| =cut |
| |
| ############################################################################## |
| package VCL::upgrade_database; |
| use strict; |
| use warnings; |
| |
| # Specify the lib path using FindBin |
| use FindBin; |
| use lib "$FindBin::Bin/../lib"; |
| |
| # Specify the version of this module |
| our $VERSION = '2.5'; |
| |
| # Specify the version of Perl to use |
| use 5.008000; |
| |
| use Cwd qw(abs_path); |
| use File::Basename qw(fileparse); |
| use File::Temp qw(tempfile); |
| use Getopt::Long; |
| use Storable qw(store retrieve); |
| |
| use VCL::utils; |
| use VCL::Module; |
| |
| |
| ############################################################################## |
| |
| my $DEBUG = 0; |
| GetOptions(\%OPTIONS, |
| 'debug!' => \$DEBUG, |
| ); |
| |
| $| = 1; |
| |
| $VERBOSE = 1; |
| $DAEMON_MODE = 0; |
| |
| my $DATABASE_SERVER = $SERVER; |
| my $DATABASE_USERNAME = $WRTUSER; |
| my $DATABASE_PASSWORD = $WRTPASS; |
| |
| my $RENAME_COLUMNS = { |
| 'vmprofile' => { |
| 'eth0generated' => 'vmware_mac_eth0_generated', |
| 'eth1generated' => 'vmware_mac_eth1_generated', |
| } |
| }; |
| |
| my $VCL_SCHEMA_PATHS = { |
| 'vclimport' => 'https://svn.apache.org/repos/asf/vcl/tags/import/mysql/vcl.sql', |
| 'vcl20' => 'https://svn.apache.org/repos/asf/vcl/tags/VCL-2.0.0/mysql/vcl.sql', |
| 'vcl21' => 'https://svn.apache.org/repos/asf/vcl/tags/release-2.1/mysql/vcl.sql', |
| 'vcl22' => 'https://svn.apache.org/repos/asf/vcl/tags/release-2.2/mysql/vcl.sql', |
| 'vcl23' => 'https://svn.apache.org/repos/asf/vcl/tags/release-2.3/mysql/vcl.sql', |
| 'vcl231' => 'https://svn.apache.org/repos/asf/vcl/tags/release-2.3.1/mysql/vcl.sql', |
| 'vcltrunk' => 'https://svn.apache.org/repos/asf/vcl/trunk/mysql/vcl.sql', |
| }; |
| |
| my $timestamp = convert_to_datetime(); |
| $timestamp =~ s/:/-/g; |
| $timestamp =~ s/\s/_/g; |
| |
| #------------------------------------------------------------------------------ |
| |
| # Preliminary checks |
| if (!get_mn_os()) { |
| setup_print_error("Failed to initialize object to interact with this management node's operating system. Check log file for more information:\n" . abs_path($LOGFILE)); |
| exit 1; |
| } |
| |
| setup(); |
| |
| #create_test_databases() || exit; |
| #upgrade_test_databases() || exit; |
| |
| exit; |
| |
| #///////////////////////////////////////////////////////////////////////////// |
| |
| =head2 get_mn_os |
| |
| Parameters : none |
| Returns : OS module object reference |
| Description : Retrieves an OS module object used to interact with the |
| management node. |
| |
| =cut |
| |
| sub get_mn_os { |
| if (defined($ENV{mn_os})) { |
| return $ENV{mn_os}; |
| } |
| |
| # Create an OS object to control this management node |
| my $mn_os = VCL::Module::create_mn_os_object(); |
| if (!$mn_os) { |
| setup_print_error("failed to create OS object to control this management node"); |
| exit 1; |
| } |
| $ENV{mn_os} = $mn_os; |
| return $mn_os; |
| } |
| |
| #///////////////////////////////////////////////////////////////////////////// |
| |
| =head2 verify_vcl_sql_file |
| |
| Parameters : $source_sql_file_path |
| Returns : boolean |
| Description : Performs various checks on an SQL file used to upgrade a VCL |
| database. The file must: |
| - exist |
| - be named 'vcl.sql' |
| - contain a 'Version:' line |
| - the version line must match the $VERSION variable of this |
| script |
| |
| =cut |
| |
| sub verify_vcl_sql_file { |
| my ($source_sql_file_path) = @_; |
| if (!$source_sql_file_path) { |
| notify($ERRORS{'WARNING'}, 0, "source VCL SQL file path argument was not supplied"); |
| return; |
| } |
| |
| my $mn_os = get_mn_os(); |
| |
| if ($source_sql_file_path !~ m|/vcl.sql$|) { |
| setup_print_warning("file must be named vcl.sql"); |
| return; |
| } |
| |
| if (!$mn_os->file_exists($source_sql_file_path)) { |
| setup_print_warning("file does not exist: $source_sql_file_path"); |
| return; |
| } |
| |
| my @lines = $mn_os->get_file_contents($source_sql_file_path); |
| if (!@lines) { |
| setup_print_error("unable to retrieve contents of file: $source_sql_file_path"); |
| return; |
| } |
| |
| #-- Version: x.x |
| my ($version_line) = grep(/--\s+Version:/, @lines); |
| if (!$version_line) { |
| setup_print_error("unable to verify file: $source_sql_file_path, it does not contain a 'Version:' line"); |
| return; |
| } |
| |
| my ($version) = $version_line =~ /Version:\s+([\d\.]+)/s; |
| if (!$version) { |
| setup_print_error("unable to verify file: $source_sql_file_path, version line could not be parsed:\n$version_line"); |
| return; |
| } |
| elsif ($version ne $VERSION) { |
| setup_print_error("unable to verify file: $source_sql_file_path, version inside the file '$version' does not match the version of this script '$VERSION'"); |
| return; |
| } |
| |
| print "verified VCL $VERSION database schema file: $source_sql_file_path\n"; |
| return 1; |
| } |
| |
| #///////////////////////////////////////////////////////////////////////////// |
| |
| =head2 create_database |
| |
| Parameters : $database_name |
| Returns : boolean |
| Description : Creates a database using the credentials and database server |
| specified in vcld.conf. |
| |
| =cut |
| |
| sub create_database { |
| my ($database_name) = @_; |
| if (!$database_name) { |
| notify($ERRORS{'WARNING'}, 0, "database name argument was not specified"); |
| return; |
| } |
| |
| print "attempting to create '$database_name' database on $DATABASE_SERVER\n"; |
| |
| my $mn_os = get_mn_os(); |
| if (!$mn_os) { |
| setup_print_error("unable to create database, failed to retrieve OS object to control this management node"); |
| return; |
| } |
| |
| my $command = "mysql -h $DATABASE_SERVER -u $DATABASE_USERNAME --password='$DATABASE_PASSWORD' -e 'CREATE DATABASE $database_name;'"; |
| my ($exit_status, $output) = $mn_os->execute($command); |
| if (!defined($output)) { |
| setup_print_error("failed to execute command to create database on $DATABASE_SERVER: $command"); |
| return; |
| } |
| |
| # Check for access denied error: |
| # ERROR 1044 (42000) at line 1: Access denied for user '<username>'@'<IP address>' to database '<database name>' |
| elsif (my ($access_denied_line) = grep(/Access denied/i, @$output)) { |
| setup_print_error("failed to create '$database_name' database on database server $DATABASE_SERVER because the database user does not have the CREATE privilege."); |
| my ($username, $source_host) = $access_denied_line =~ /'([^']+)'\@'([^']+)'/; |
| if ($username && $source_host) { |
| print "\nexecute the following command on the database server:\n"; |
| print "mysql -e \"GRANT CREATE ON *.* TO '$username'\@'$source_host';\"\n"; |
| } |
| return; |
| } |
| |
| # Check for database already exists error |
| # ERROR 1007 (HY000) at line 1: Can't create database '<database name>'; database exists |
| elsif (grep(/database exists/i, @$output)) { |
| setup_print_error("failed to create '$database_name' database on $DATABASE_SERVER because a database with this name already exists"); |
| return 0; |
| } |
| |
| elsif ($exit_status != 0 || grep(/ERROR/i, @$output)) { |
| setup_print_error("failed to create '$database_name' database on $DATABASE_SERVER, exit status: $exit_status, output:\n" . join("\n", @$output) . ""); |
| return 0; |
| } |
| else { |
| print "created '$database_name' database on $DATABASE_SERVER\n"; |
| return 1; |
| } |
| } |
| |
| #///////////////////////////////////////////////////////////////////////////// |
| |
| =head2 drop_database |
| |
| Parameters : $database_name |
| Returns : boolean |
| Description : Drops a database using the credentials and database server |
| specified in vcld.conf. |
| |
| =cut |
| |
| sub drop_database { |
| my ($database_name) = @_; |
| if (!$database_name) { |
| notify($ERRORS{'WARNING'}, 0, "database name argument was not specified"); |
| return; |
| } |
| |
| print "attempting to drop '$database_name' database on $DATABASE_SERVER\n"; |
| |
| my $mn_os = get_mn_os(); |
| if (!$mn_os) { |
| setup_print_error("unable to drop database, failed to retrieve OS object to control this management node"); |
| return; |
| } |
| |
| my $command = "mysql -h $DATABASE_SERVER -u $DATABASE_USERNAME --password='$DATABASE_PASSWORD' -e 'drop DATABASE $database_name'"; |
| my ($exit_status, $output) = $mn_os->execute($command); |
| if (!defined($output)) { |
| setup_print_error("failed to execute command to drop database on $DATABASE_SERVER: $command"); |
| return; |
| } |
| # Check for access denied error: |
| # ERROR 1044 (42000) at line 1: Access denied for user '<username>'@'<IP address>' to database '<database>' |
| elsif (my ($access_denied_line) = grep(/Access denied/i, @$output)) { |
| setup_print_error("failed to drop '$database_name' database on database server $DATABASE_SERVER because the database user does not have the DROP privilege."); |
| my ($username, $source_host) = $access_denied_line =~ /'([^']+)'\@'([^']+)'/; |
| if ($username && $source_host) { |
| print "\nexecute the following command on the database server:\n"; |
| print "mysql -e \"GRANT DROP ON $database_name.* TO '$username'\@'$source_host';\"\n"; |
| } |
| return; |
| } |
| #ERROR 1008 (HY000) at line 1: Can't drop database 'vcl_import'; database doesn't exist |
| elsif (grep(/ERROR 1008/i, @$output)) { |
| print "'$database_name' database does not exist on database server $DATABASE_SERVER\n"; |
| return 1; |
| } |
| |
| else { |
| print "dropped '$database_name' database on $DATABASE_SERVER\n"; |
| return 1; |
| } |
| } |
| |
| #///////////////////////////////////////////////////////////////////////////// |
| |
| =head2 import_sql_file |
| |
| Parameters : $database_name, $sql_file_path |
| Returns : boolean |
| Description : Imports an SQL file into a database. |
| |
| =cut |
| |
| sub import_sql_file { |
| my ($database_name, $sql_file_path) = @_; |
| if (!$database_name || !$sql_file_path) { |
| notify($ERRORS{'WARNING'}, 0, "database name and SQL file path arguments were not specified"); |
| return; |
| } |
| |
| my $mn_os = get_mn_os(); |
| if (!$mn_os) { |
| setup_print_error("unable to import SQL file, failed to retrieve OS object to control this management node"); |
| return; |
| } |
| |
| my $command = "mysql -h $DATABASE_SERVER -u $DATABASE_USERNAME --password='$DATABASE_PASSWORD' $database_name < $sql_file_path"; |
| print "attempting to import $sql_file_path into '$database_name' database\n"; |
| my ($exit_status, $output) = $mn_os->execute($command); |
| if (!defined($output)) { |
| setup_print_error("failed to execute command to import $sql_file_path into '$database_name' database on $DATABASE_SERVER"); |
| print "command:\n$command\n"; |
| return; |
| } |
| elsif ($exit_status != 0 || grep(/ERROR/i, @$output)) { |
| setup_print_error("failed to import import $sql_file_path into '$database_name' database on $DATABASE_SERVER, output:"); |
| print join("\n", @$output) . "\n"; |
| return 0; |
| } |
| else { |
| print "imported $sql_file_path into '$database_name' database\n"; |
| return 1; |
| } |
| } |
| |
| #///////////////////////////////////////////////////////////////////////////// |
| |
| =head2 create_test_databases |
| |
| Parameters : @database_keys (optional) |
| Returns : boolean |
| Description : |
| |
| =cut |
| |
| sub create_test_databases { |
| my $mn_os = get_mn_os(); |
| if (!$mn_os) { |
| setup_print_error("failed to retrieve OS object to control this management node"); |
| return; |
| } |
| |
| my @database_keys = @_; |
| if (!@database_keys) { |
| @database_keys = keys %$VCL_SCHEMA_PATHS |
| } |
| |
| for my $database_name (@database_keys) { |
| setup_print_break(); |
| print "creating test database: $database_name\n\n"; |
| |
| my $sql_file_url = $VCL_SCHEMA_PATHS->{$database_name}; |
| my $sql_temp_file_path = '/tmp/vcl.sql'; |
| my $sql_file_path = "/tmp/$database_name.sql"; |
| |
| if ($mn_os->file_exists($sql_file_path)) { |
| $mn_os->delete_file($sql_file_path); |
| } |
| |
| print "downloading VCL schema file: $sql_file_url\n"; |
| |
| my $wget_command = "wget -N -P /tmp $sql_file_url"; |
| my ($wget_exit_status, $wget_output) = $mn_os->execute($wget_command); |
| if (!defined($wget_output)) { |
| setup_print_error("failed to execute command to download VCL schema file: $wget_command"); |
| return; |
| } |
| elsif ($wget_exit_status ne '0') { |
| setup_print_error("failed to download VCL schema file, exit status: $wget_exit_status\n"); |
| print "command: $wget_command\n"; |
| print "output:\n" . join("\n", @$wget_output) . "\n"; |
| return; |
| } |
| else { |
| print "downloaded VCL schema file: $sql_file_url --> $sql_temp_file_path\n"; |
| } |
| |
| if ($mn_os->move_file($sql_temp_file_path, $sql_file_path)) { |
| print "renamed file: $sql_temp_file_path --> $sql_file_path\n"; |
| } |
| else { |
| setup_print_error("failed to rename file: $sql_temp_file_path --> $sql_file_path"); |
| return; |
| } |
| |
| print "\n"; |
| if (!drop_database($database_name)) { |
| return; |
| } |
| |
| print "\n"; |
| if (!create_database($database_name)) { |
| return; |
| } |
| |
| print "\n"; |
| if (!import_sql_file($database_name, $sql_file_path)) { |
| return; |
| } |
| } |
| |
| return 1; |
| } |
| |
| #///////////////////////////////////////////////////////////////////////////// |
| |
| =head2 upgrade_test_databases |
| |
| Parameters : $reference_sql_file_path, @database_keys (optional) |
| Returns : boolean |
| Description : |
| |
| =cut |
| |
| sub upgrade_test_databases { |
| my $mn_os = get_mn_os(); |
| if (!$mn_os) { |
| setup_print_error("failed to retrieve OS object to control this management node"); |
| return; |
| } |
| |
| my ($reference_sql_file_path, @database_keys) = @_; |
| if (!$reference_sql_file_path) { |
| setup_print_error("reference SQL file path argument was not provided"); |
| return; |
| } |
| |
| if (!@database_keys) { |
| @database_keys = keys %$VCL_SCHEMA_PATHS |
| } |
| |
| my $dumped_trunk = 0; |
| my @diff_commands; |
| my @diff_sort_file_paths; |
| for my $database_name ('vcltrunk', @database_keys) { |
| if ($database_name eq 'vcltrunk' && $dumped_trunk) { |
| next; |
| } |
| |
| setup_print_break(); |
| |
| if ($database_name ne 'vcltrunk') { |
| print "upgrading test database: $database_name\n"; |
| setup_upgrade_database($database_name, $reference_sql_file_path) || return; |
| print "upgraded test database: $database_name\n"; |
| } |
| |
| my $database_dump_sql_file_path = "/tmp/$database_name\_dump.sql"; |
| dump_database_to_file($database_name, $database_dump_sql_file_path, '--no-data'); |
| |
| # Remove comments from dumped file - makes it easier to diff |
| `sed -i 's/\\/\\*.*//' $database_dump_sql_file_path`; |
| `sed -i 's/AUTO_INCREMENT=[0-9]* //' $database_dump_sql_file_path`; |
| |
| `sort $database_dump_sql_file_path > $database_dump_sql_file_path.sort`; |
| `sed -i -e 's/,\$//' $database_dump_sql_file_path.sort`; |
| `sed -i -e 's/^USE.*//' $database_dump_sql_file_path.sort`; |
| `sed -i -e 's/^mysqldump.*//' $database_dump_sql_file_path.sort`; |
| |
| if ($database_name eq 'vcltrunk') { |
| $dumped_trunk = 1; |
| next; |
| } |
| |
| my $diff_file_path = "$database_dump_sql_file_path.diff"; |
| my $diff_command = "diff -W 200 -w -B --side-by-side --suppress-common-lines $database_dump_sql_file_path /tmp/vcltrunk_dump.sql"; |
| push @diff_commands, $diff_command, |
| `$diff_command > $diff_file_path`; |
| |
| my $sort_diff_command = "diff -W 200 -w -B --side-by-side --suppress-common-lines $database_dump_sql_file_path.sort /tmp/vcltrunk_dump.sql.sort"; |
| push @diff_commands, $sort_diff_command, |
| `$sort_diff_command > $diff_file_path.sort`; |
| push @diff_sort_file_paths, "$diff_file_path.sort"; |
| } |
| |
| print join("\n", @diff_commands) . "\n\n"; |
| |
| for my $diff_sort_file_path (@diff_sort_file_paths) { |
| print "\n$diff_sort_file_path\n"; |
| print `cat $diff_sort_file_path`; |
| } |
| |
| return 1; |
| } |
| |
| #///////////////////////////////////////////////////////////////////////////// |
| |
| =head2 parse_sql_file |
| |
| Parameters : $sql_file_path |
| Returns : hash reference |
| Description : Parses the statements in an SQL file and generates a hash. |
| |
| =cut |
| |
| sub parse_sql_file { |
| my ($sql_file_path) = @_; |
| if (!$sql_file_path) { |
| notify($ERRORS{'WARNING'}, 0, "source VCL SQL file path argument was not supplied"); |
| return; |
| } |
| |
| my $mn_os = get_mn_os(); |
| |
| # Get the contents of the .sql file |
| my $sql_string = $mn_os->get_file_contents($sql_file_path); |
| if (!$sql_string) { |
| setup_print_error("Failed to retrieve contents of SQL file: $sql_file_path"); |
| return; |
| } |
| |
| print "Parsing SQL file: $sql_file_path\n"; |
| |
| # Remove comments |
| $sql_string =~ s/^--.*[\r\n]+//mg; |
| $sql_string =~ s/^#.*//mg; |
| |
| my $comment_regex = ' |
| ( |
| /\* (?# Beginning of comment) |
| (?: |
| [^*] (?# Match anything other than a *) |
| | |
| [*][^/] (?# Match * only if not followed by a /) |
| )* |
| \*/[\s;]* (?# End of comment) |
| ) |
| '; |
| $sql_string =~ s/$comment_regex//sxg; |
| |
| my $sql_info = {}; |
| |
| my @insert_statements; |
| $sql_info->{CREATE_TABLE} = {}; |
| $sql_info->{ALTER_TABLE} = {}; |
| $sql_info->{INSERT} = []; |
| |
| my @statements = split(/;\n/, $sql_string); |
| my $statement_count = scalar(@statements); |
| for (my $i=0; $i<$statement_count; $i++) { |
| my $statement = $statements[$i]; |
| |
| # Collapse statement into a single line |
| $statement =~ s/\n/ /gs; |
| |
| # Remove any spaces from the beginning of the statement and consecutive spaces |
| $statement =~ s/(^|\s)\s+/$1/gs; |
| |
| if ($statement =~ /^CREATE TABLE/ ) { |
| my $create_table = parse_create_table_statement($statement) || return; |
| $sql_info->{CREATE_TABLE} = {%{$sql_info->{CREATE_TABLE}}, %$create_table}; |
| } |
| elsif ($statement =~ /^ALTER TABLE/ ) { |
| my $alter_table_info = parse_alter_table_statement($statement); |
| if (!$alter_table_info) { |
| setup_print_error("failed to parse ALTER TABLE statement:"); |
| print "$statement\n"; |
| return; |
| } |
| |
| #setup_print_break(); |
| #print format_data($alter_table_info) . "\n\n"; |
| |
| # Merge info with previously retrieved ALTER TABLE info |
| for my $table_name (keys %$alter_table_info) { |
| for my $statement_type (keys %{$alter_table_info->{$table_name}}) { |
| for my $key (keys %{$alter_table_info->{$table_name}{$statement_type}}) { |
| if (!defined($sql_info->{ALTER_TABLE}{$table_name}{$statement_type}{$key})) { |
| $sql_info->{ALTER_TABLE}{$table_name}{$statement_type}{$key} = $alter_table_info->{$table_name}{$statement_type}{$key}; |
| } |
| else { |
| setup_print_error("SQL file contains duplicate ALTER TABLE $statement_type $key statements"); |
| return; |
| } |
| } |
| } |
| } |
| } |
| elsif ($statement =~ /^INSERT/) { |
| $statement =~ s/INSERT INTO/INSERT IGNORE INTO/; |
| push @{$sql_info->{INSERT}}, $statement; |
| } |
| elsif ($statement =~ /^UPDATE/) { |
| push @{$sql_info->{UPDATE}}, $statement; |
| } |
| elsif ($statement =~ /^DROP TABLE/) { |
| my $table_name = parse_drop_table_statement($statement); |
| push @{$sql_info->{DROP_TABLE}}, $table_name; |
| } |
| elsif ($statement =~ /^CREATE DATABASE/ ) { |
| } |
| elsif ($statement =~ /^SET/) { |
| } |
| else { |
| setup_print_warning("SQL statement is not supported:\n$statement"); |
| return; |
| } |
| } |
| print "Done. (statement count: $statement_count)\n"; |
| |
| for my $table_info ($sql_info->{CREATE_TABLE}, $sql_info->{ALTER_TABLE}) { |
| for my $table_name (keys %$table_info) { |
| for my $constraint_name (keys %{$table_info->{$table_name}{CONSTRAINT}}) { |
| my $constraint = $table_info->{$table_name}{CONSTRAINT}{$constraint_name}; |
| $constraint->{index_table} = $table_name; |
| my $index_column = $constraint->{index_column}; |
| my $parent_table = $constraint->{parent_table}; |
| my $parent_column = $constraint->{parent_column}; |
| |
| $sql_info->{CONSTRAINTS}{$constraint_name} = $constraint; |
| push @{$sql_info->{REFERENCED_CONSTRAINTS}{$parent_table}{$parent_column}}, $constraint; |
| $sql_info->{REFERENCING_CONSTRAINTS}{$table_name}{$index_column} = $constraint; |
| } |
| |
| for my $column_name (keys %{$table_info->{$table_name}{ADD}}) { |
| my $column_info = $table_info->{$table_name}{ADD}{$column_name}; |
| $sql_info->{ADD_COLUMN}{$table_name}{$column_name} = $column_info; |
| } |
| |
| for my $column_name (keys %{$table_info->{$table_name}{DROP}}) { |
| my $column_info = $table_info->{$table_name}{DROP}{$column_name}; |
| $sql_info->{DROP_COLUMN}{$table_name}{$column_name} = $column_info; |
| |
| ###if (defined($sql_info->{ADD_COLUMN}{$table_name}{$column_name})) { |
| ### delete $sql_info->{ADD_COLUMN}{$table_name}{$column_name}; |
| ###} |
| } |
| } |
| } |
| |
| if ($DEBUG) { |
| setup_print_break('='); |
| print "REFERENCED_CONSTRAINTS:\n\n"; |
| for my $parent_table (sort { lc($a) cmp lc($b) } keys %{$sql_info->{REFERENCED_CONSTRAINTS}}) { |
| for my $parent_column (sort { lc($a) cmp lc($b) } keys %{$sql_info->{REFERENCED_CONSTRAINTS}{$parent_table}}) { |
| my @constraints = @{$sql_info->{REFERENCED_CONSTRAINTS}{$parent_table}{$parent_column}}; |
| |
| for my $constraint (@constraints) { |
| my $index_table = $constraint->{index_table}; |
| my $index_column = $constraint->{index_column}; |
| print "$index_table.$index_column\n"; |
| } |
| print "--> $parent_table.$parent_column\n\n"; |
| } |
| } |
| |
| setup_print_break('='); |
| print "REFERENCING_CONSTRAINTS:\n\n"; |
| for my $index_table (sort { lc($a) cmp lc($b) } keys %{$sql_info->{REFERENCING_CONSTRAINTS}}) { |
| for my $index_column (sort { lc($a) cmp lc($b) } keys %{$sql_info->{REFERENCING_CONSTRAINTS}{$index_table}}) { |
| my $parent_table = $sql_info->{REFERENCING_CONSTRAINTS}{$index_table}{$index_column}{parent_table}; |
| my $parent_column = $sql_info->{REFERENCING_CONSTRAINTS}{$index_table}{$index_column}{parent_column}; |
| print "$index_table.$index_column --> $parent_table.$parent_column\n"; |
| } |
| } |
| } |
| |
| return $sql_info; |
| } |
| |
| #///////////////////////////////////////////////////////////////////////////// |
| |
| =head2 parse_create_table_statement |
| |
| Parameters : |
| Returns : |
| Description : |
| |
| =cut |
| |
| sub parse_create_table_statement { |
| my ($statement) = @_; |
| if (!$statement) { |
| notify($ERRORS{'WARNING'}, 0, "SQL statement argument was not supplied"); |
| return; |
| } |
| |
| my $table_info = {}; |
| |
| my ($table_name, $table_definition, $table_options) = $statement =~ /CREATE TABLE(?: IF NOT EXISTS)? `?([\w_\$]+)`? \(\s*(.+)\s*\)\s*(.*)\s*$/g; |
| if (!$table_name) { |
| setup_print_error("failed to determine table name:\n\n$statement\n\n" . string_to_ascii($statement) . ""); |
| return; |
| } |
| elsif (!$table_definition) { |
| setup_print_error("failed to determine table definition:\n\n$statement\n\n" . string_to_ascii($statement) . ""); |
| return; |
| } |
| $table_options = '' if !defined($table_options); |
| |
| $table_info->{$table_name}{STATEMENT} = $statement; |
| |
| #.......... |
| |
| # Extract the CONSTRAINT definitions |
| my $constraint_regex = ' |
| \s* (?# omit leading spaces) |
| ( |
| CONSTRAINT (?# must contain KEY) |
| [^,]+ (?# any character except commas) |
| ) |
| [,\s]* (?# omit trailing comma and spaces) |
| '; |
| my @constraint_definitions = $table_definition =~ /$constraint_regex/gx; |
| my $constraint_definition_count = scalar(@constraint_definitions) || 0; |
| |
| for (my $i=0; $i<$constraint_definition_count; $i++) { |
| my $constraint_definition = $constraint_definitions[$i]; |
| my $constraint_info = parse_constraint_definition($constraint_definition); |
| my $constraint_name = $constraint_info->{name}; |
| $table_info->{$table_name}{CONSTRAINT}{$constraint_name} = $constraint_info; |
| } |
| |
| # Remove the CONSTRAINT definitions |
| $table_definition =~ s/$constraint_regex//gx; |
| |
| #.......... |
| |
| # Extract the KEY definitions |
| my $key_regex = ' |
| \s* (?# omit leading spaces) |
| ( |
| [\w\s]* (?# words preceding key such as PRIMARY, UNIQUE, etc) |
| KEY (?# must contain KEY) |
| [^\)]+ (?# any character except closing parenthesis, all key definitions of a set of parenthesis) |
| \) (?# closing parenthesis) |
| (?: |
| [^,]*[^,\s] (?# index options may exist after the closing parenthesis, make sure this ends with a non-space character) |
| )? |
| ) |
| [,\s]* (?# omit trailing comma and spaces) |
| '; |
| my @key_definitions = $table_definition =~ /$key_regex/gx; |
| my $key_definition_count = scalar(@key_definitions); |
| |
| for (my $i=0; $i<$key_definition_count; $i++) { |
| my $key_definition = $key_definitions[$i]; |
| |
| my $key_definition_regex = ' |
| ( |
| \w* (?# key type: PRIMARY, UNIQUE) |
| )? |
| \s? |
| KEY |
| \s |
| `? |
| ( |
| [\w_\$]+ (?# key name) |
| )? (?# key name is not set for primary keys) |
| `? |
| \s* |
| \( (?# opening parenthesis) |
| ([^\)]+) |
| \) (?# closing parenthesis) |
| [,\s]* (?# omit trailing comma and spaces) |
| '; |
| |
| my ($key_type, $key_name, $column_list) = $key_definition =~ /$key_definition_regex/x; |
| if (!defined($key_type) && !defined($key_name)) { |
| setup_print_error("failed to determine key type or name:\n\n$key_definition\n\n" . string_to_ascii($key_definition) . ""); |
| return; |
| } |
| elsif (!defined($column_list)) { |
| setup_print_error("failed to determine column list:\n\n$key_definition\n\n" . string_to_ascii($key_definition) . ""); |
| return; |
| } |
| $key_type = 'INDEX' if (!$key_type); |
| $key_name = 'PRIMARY' if !($key_name); |
| |
| $column_list =~ s/[`\s]//g; |
| my @columns = split(/,/, $column_list); |
| |
| my $key_info = {}; |
| $key_info->{STATEMENT} = $key_definition; |
| $key_info->{TYPE} = $key_type; |
| $key_info->{name} = $key_name; |
| %{$key_info->{COLUMNS}} = map { $_ => 1 } @columns; |
| |
| $table_info->{$table_name}{INDEXES}{$key_name} = $key_info; |
| } |
| |
| # Error check, make sure number of times 'KEY' appears in original statement matches number of keys found |
| my @statement_keys = $statement =~ /KEY/g; |
| my $statement_key_count = scalar(@statement_keys); |
| if ($statement_key_count ne ($key_definition_count + $constraint_definition_count)) { |
| setup_print_error("statement KEY count ($statement_key_count) does not match the number of keys parsed ($key_definition_count) + constraints ($constraint_definition_count)"); |
| return; |
| } |
| |
| # Remove the KEY definitions |
| $table_definition =~ s/$key_regex//gx; |
| |
| #.......... |
| |
| # Retrieve the column definitions |
| my $column_regex = ' |
| \s* (?# omit leading spaces) |
| ( |
| (?: |
| [^,\(]+ (?# any character execept for comma and opening parenthesis) |
| | (?# -or-) |
| \( (?# opening parenthesis) |
| [^\)]+ (?# any character execept for closing parenthesis) |
| \) (?# closing parenthesis) |
| )+ (?# match either case multiple times because normal charcters can come after the closing parenthesis) |
| ) |
| [,\s]* (?# omit trailing comma and spaces) |
| '; |
| my @column_definitions = $table_definition =~ /$column_regex/gx; |
| |
| for (my $i=0; $i<scalar(@column_definitions); $i++) { |
| my $column_definition = $column_definitions[$i]; |
| |
| my $column_info = parse_column_definition($column_definition); |
| if (!$column_info) { |
| setup_print_error("failed to parse $table_name definition, column definition could not be parsed:\n$column_definition"); |
| return; |
| } |
| |
| my $column_name = $column_info->{name}; |
| $table_info->{$table_name}{COLUMNS}{$column_name} = $column_info; |
| |
| push @{$table_info->{$table_name}{COLUMN_ORDER}}, $table_info->{$table_name}{COLUMNS}{$column_name}; |
| } |
| |
| #.......... |
| |
| # Parse the table options |
| my $table_options_patterns = { |
| 'AUTO_INCREMENT' => 'AUTO_INCREMENT', |
| '(?:DEFAULT\s*)?(?:CHARSET|CHARACTER SET)' => 'CHARSET', |
| 'CHECKSUM' => 'CHECKSUM', |
| 'COMMENT' => 'COMMENT', |
| 'CONNECTION' => 'CONNECTION', |
| '(?:DEFAULT\s*)?COLLATE' => 'COLLATE', |
| 'DATA DIRECTORY' => 'DATA_DIRECTORY', |
| 'DELAY_KEY_WRITE' => 'DELAY_KEY_WRITE', |
| '(ENGINE|TYPE)' => 'ENGINE', |
| 'INDEX DIRECTORY' => 'INDEX_DIRECTORY', |
| 'INSERT_METHOD' => 'INSERT_METHOD', |
| 'KEY_BLOCK_SIZE' => 'KEY_BLOCK_SIZE', |
| 'MAX_ROWS' => 'MAX_ROWS', |
| 'MIN_ROWS' => 'MIN_ROWS', |
| 'PACK_KEYS' => 'PACK_KEYS', |
| 'PASSWORD' => 'PASSWORD', |
| 'RAID_TYPE' => 'RAID_TYPE', |
| 'RAID_CHUNKS' => 'RAID_CHUNKS', |
| 'RAID_CHUNKSIZE' => 'RAID_CHUNKSIZE', |
| 'ROW_FORMAT' => 'ROW_FORMAT', |
| 'TABLESPACE' => 'TABLESPACE', |
| 'UNION' => 'UNION', |
| }; |
| |
| for my $table_option_pattern (keys %$table_options_patterns) { |
| my $synonym = $table_options_patterns->{$table_option_pattern}; |
| my $table_option_regex = $table_option_pattern . '\s*=\s*(\'[^\']+\'|[^\s]+)\s*'; |
| my ($value) = $table_options =~ /$table_option_regex/gx; |
| if ($value) { |
| $value =~ s/(^'|'$)//g; |
| $table_options =~ s/$table_option_regex//gx; |
| $table_info->{$table_name}{OPTIONS}{$synonym} = $value; |
| } |
| } |
| if ($table_options =~ /\S/) { |
| print "WARNING: $table_name table options not recognized: '$table_options'\n"; |
| } |
| |
| #print "\n" . format_data($table_info) . "\n"; |
| return $table_info; |
| } |
| |
| #///////////////////////////////////////////////////////////////////////////// |
| |
| =head2 parse_column_definition |
| |
| Parameters : $table_name, $column_definition |
| Returns : |
| Description : |
| |
| =cut |
| |
| sub parse_column_definition { |
| my ($column_definition) = @_; |
| if (!$column_definition) { |
| notify($ERRORS{'WARNING'}, 0, "column definition argument was not provided"); |
| return; |
| } |
| |
| my $column_definition_regex = ' |
| ^ |
| `? |
| ( (?# begin column name match) |
| [\w_\$]+ (?# column name) |
| ) (?# end column name match) |
| `? |
| \s+ (?# space after column name) |
| ( (?# begin data type match) |
| (?: |
| \w[^\s\(]+ (?# beginning of data type, must start with a letter, continue until space or opening parenthesis if found) |
| | |
| \( (?# opening parenthesis) |
| [^\)]+ (?# any character execept for closing parenthesis) |
| \) (?# closing parenthesis) |
| )+ |
| ) (?# end data type match) |
| \s* |
| ( |
| .* (?# column options) |
| ) |
| '; |
| |
| my ($column_name, $data_type, $column_options) = $column_definition =~ /$column_definition_regex/x; |
| if (!defined($column_name)) { |
| setup_print_error("failed to determine column name from column definition:\n$column_definition\n"); |
| return; |
| } |
| elsif (!defined($data_type)) { |
| setup_print_error("failed to determine data type from column definition:\n$column_definition"); |
| return; |
| } |
| $column_options = '' if !defined($column_options); |
| |
| my $column_info = { |
| 'name' => $column_name, |
| 'STATEMENT' => $column_definition, |
| 'DATA_TYPE' => $data_type, |
| 'OPTIONS' => {}, |
| }; |
| |
| if ($column_options =~ s/unsigned//i) { |
| $column_info->{OPTIONS}{unsigned} = 1; |
| } |
| |
| if ($column_options =~ s/AUTO_INCREMENT//i) { |
| $column_info->{OPTIONS}{AUTO_INCREMENT} = 1; |
| } |
| |
| my ($comment) = $column_options =~ /COMMENT '([^']+)'/i; |
| if (defined($comment)) { |
| $column_info->{OPTIONS}{COMMENT} = $comment; |
| } |
| $column_options =~ s/COMMENT '([^']+)'//i; |
| |
| # `column` varchar(xx) NOT NULL DEFAULT 'xxx', |
| # `column` varchar(xx) DEFAULT NULL, |
| # `column` varchar(xx) NULL default NULL, |
| # `column` varchar(xx) NULL default 'xxx',a |
| |
| my ($default) = $column_options =~ /DEFAULT '?(NULL|[^']*)'?/i; |
| if (defined($default)) { |
| $column_info->{OPTIONS}{DEFAULT} = $default; |
| } |
| else { |
| $default = ''; |
| } |
| $column_options =~ s/DEFAULT '?(NULL|[^']*)'?//i; |
| |
| my $not_null = $column_options =~ s/NOT NULL//i; |
| if ($not_null) { |
| $column_info->{OPTIONS}{'NOT_NULL'} = 1; |
| } |
| else { |
| $column_info->{OPTIONS}{'NOT_NULL'} = 0; |
| } |
| |
| # Check if column does not have NOT NULL set and no default value: |
| # `column` text |
| if (!$not_null && $default eq '') { |
| $default = 'NULL'; |
| $column_info->{OPTIONS}{DEFAULT} = $default; |
| } |
| |
| # Remove 'NULL' from the column options, it won't get removed from the above statements for this case: |
| # `column` varchar(xx) NULL default NULL |
| if (!$not_null || $default =~ /null/i) { |
| $column_options =~ s/NULL\s*//i; |
| } |
| |
| if ($column_options =~ /[^\ ]/) { |
| setup_print_warning("$column_name column options not recognized: '$column_options'"); |
| } |
| |
| return $column_info; |
| } |
| |
| #///////////////////////////////////////////////////////////////////////////// |
| |
| =head2 parse_alter_table_statement |
| |
| Parameters : |
| Returns : |
| Description : |
| |
| =cut |
| |
| sub parse_alter_table_statement { |
| my ($statement) = @_; |
| if (!$statement) { |
| notify($ERRORS{'WARNING'}, 0, "SQL statement argument was not supplied"); |
| return; |
| } |
| |
| my ($table_name, $table_definition) = $statement =~ /ALTER TABLE `?([\w_\$]+)`?\s*(.+)\s*$/g; |
| if (!$table_name) { |
| setup_print_error("failed to determine table name:\n\n$statement\n\n" . string_to_ascii($statement) . ""); |
| return; |
| } |
| elsif (!$table_definition) { |
| setup_print_error("failed to determine table definition:\n\n$statement\n\n" . string_to_ascii($statement) . ""); |
| return; |
| } |
| |
| my $alter_table_info = {}; |
| |
| #.......... |
| |
| # Extract the CONSTRAINT definitions |
| my $constraint_regex = ' |
| \s* (?# omit leading spaces) |
| ( |
| ADD\sCONSTRAINT |
| [^,]+ (?# any character except commas) |
| ) |
| [,\s]* (?# omit trailing comma and spaces) |
| '; |
| my @constraint_definitions = $table_definition =~ /$constraint_regex/gx; |
| my $constraint_definition_count = scalar(@constraint_definitions) || 0; |
| |
| for (my $i=0; $i<$constraint_definition_count; $i++) { |
| my $constraint_definition = $constraint_definitions[$i]; |
| my $constraint_info = parse_constraint_definition($constraint_definition); |
| my $constraint_name = $constraint_info->{name}; |
| $alter_table_info->{$table_name}{'CONSTRAINT'}{$constraint_name} = $constraint_info; |
| } |
| |
| # Remove the CONSTRAINT definitions |
| $table_definition =~ s/$constraint_regex//gx; |
| |
| #.......... |
| |
| # ADD `<column name>` bit(1) NULL default NULL |
| # Extract the ADD definitions |
| my $add_regex = ' |
| \s* (?# omit leading spaces) |
| ADD\s |
| ( |
| [^,]+ (?# any character except commas) |
| ) |
| [,\s]* (?# omit trailing comma and spaces) |
| '; |
| |
| my @add_definitions = $table_definition =~ /$add_regex/gx; |
| my $add_definition_count = scalar(@add_definitions) || 0; |
| |
| for (my $i=0; $i<$add_definition_count; $i++) { |
| my $add_definition = $add_definitions[$i]; |
| my $column_info = parse_column_definition($add_definition); |
| if (!$column_info) { |
| setup_print_error("failed to parse alter table statement:\n$statement\nADD definition:\n$add_definition"); |
| return; |
| } |
| |
| my $column_name = $column_info->{name}; |
| $alter_table_info->{$table_name}{ADD}{$column_name} = $column_info; |
| } |
| |
| $table_definition =~ s/$add_regex//gx; |
| |
| #.......... |
| |
| # Extract the DROP definitions |
| my $drop_regex = ' |
| \s* (?# omit leading spaces) |
| DROP\s |
| `? |
| ( |
| [\w_\$]+ (?# any character except commas) |
| ) |
| `? |
| [,\s]* (?# omit trailing comma and spaces) |
| '; |
| |
| my @drop_column_names = $table_definition =~ /$drop_regex/gx; |
| my $drop_column_count = scalar(@drop_column_names) || 0; |
| |
| for (my $i=0; $i<$drop_column_count; $i++) { |
| my $drop_column_name = $drop_column_names[$i]; |
| $alter_table_info->{$table_name}{DROP}{$drop_column_name} = 1; |
| } |
| |
| $table_definition =~ s/$drop_regex//gx; |
| |
| #.......... |
| |
| if ($table_definition =~ /\S/) { |
| setup_print_warning("part of alter $table_name table definition was not handled:"); |
| print "table definition:\n$table_definition\n\n"; |
| print "statement:\n$statement\n\n"; |
| return; |
| } |
| |
| return $alter_table_info; |
| } |
| |
| #///////////////////////////////////////////////////////////////////////////// |
| |
| =head2 parse_drop_table_statement |
| |
| Parameters : |
| Returns : |
| Description : |
| |
| =cut |
| |
| sub parse_drop_table_statement { |
| my ($statement) = @_; |
| if (!$statement) { |
| notify($ERRORS{'WARNING'}, 0, "SQL statement argument was not supplied"); |
| return; |
| } |
| |
| # DROP TABLE IF EXISTS `<table name>`; |
| my $drop_table_regex = ' |
| DROP\sTABLE\s |
| (?:IF\sEXISTS\s)? |
| `? |
| ([\w_\$]+) (?# table name) |
| `? |
| '; |
| |
| my ($table_name) = $statement =~ /$drop_table_regex/gx; |
| if ($table_name) { |
| return $table_name; |
| } |
| else { |
| setup_print_error("failed to determine table name from statement: $statement"); |
| return; |
| } |
| } |
| |
| #///////////////////////////////////////////////////////////////////////////// |
| |
| =head2 parse_insert_statements |
| |
| Parameters : |
| Returns : |
| Description : |
| |
| =cut |
| |
| sub parse_insert_statements { |
| my @statements = @_; |
| if (!@statements) { |
| notify($ERRORS{'WARNING'}, 0, "SQL statement argument was not supplied"); |
| return; |
| } |
| |
| # INSERT IGNORE INTO `usergroupprivtype` (`id`, `name`, `help`) VALUES (1, 'xxx', 'yyy'), |
| my $insert_regex = ' |
| INSERT\s |
| (?:IGNORE\s)? |
| (?:INTO\s)? |
| `? |
| ([\w_\$]+) (?# table name) |
| `?\s |
| '; |
| |
| my $insert_info = {}; |
| for my $statement (@statements) { |
| my ($table_name) = $statement =~ /$insert_regex/gx; |
| if (!$table_name) { |
| setup_print_error("failed to determine table name:\n\n$statement"); |
| return; |
| } |
| |
| if (!defined($insert_info->{$table_name})) { |
| $insert_info->{$table_name} = []; |
| } |
| push @{$insert_info->{$table_name}}, $statement; |
| } |
| |
| return $insert_info; |
| } |
| |
| #///////////////////////////////////////////////////////////////////////////// |
| |
| =head2 parse_update_statement |
| |
| Parameters : |
| Returns : |
| Description : |
| |
| =cut |
| |
| sub parse_update_statement { |
| my ($statement) = @_; |
| if (!$statement) { |
| notify($ERRORS{'WARNING'}, 0, "SQL statement argument was not supplied"); |
| return; |
| } |
| |
| # UPDATE <table_name> SET <column_name> = <value> WHERE ...; |
| my $insert_regex = ' |
| UPDATE\s |
| (?:IGNORE\s)? |
| `? |
| ([\w_\$]+) (?# table name) |
| `?\s |
| SET\s |
| `? |
| ([\w_\$]+) (?# column name) |
| `? |
| \s=\s |
| ([\S]+) (?# value) |
| '; |
| |
| my ($table_name, $column_name, $value) = $statement =~ /$insert_regex/gx; |
| if (!defined($table_name)) { |
| setup_print_error("failed to determine table name from statement:\n$statement"); |
| return; |
| } |
| |
| my $update_info = { |
| 'table' => $table_name, |
| 'column' => $column_name, |
| 'value' => $value, |
| 'STATEMENT' => $statement, |
| }; |
| |
| return $update_info; |
| } |
| |
| #///////////////////////////////////////////////////////////////////////////// |
| |
| =head2 parse_constraint_definition |
| |
| Parameters : |
| Returns : |
| Description : |
| |
| =cut |
| |
| sub parse_constraint_definition { |
| my ($constraint_definition) = @_; |
| if (!$constraint_definition) { |
| notify($ERRORS{'WARNING'}, 0, "constraint definition argument was not supplied"); |
| return; |
| } |
| |
| # Remove text before "CONSTRAINT" for consistency |
| $constraint_definition =~ s/.*(CONSTRAINT)/$1/; |
| |
| # CONSTRAINT `constraint_name` FOREIGN KEY (`this_column`) REFERENCES `that_table` (`that_column`) ON DELETE SET NULL ON UPDATE CASCADE |
| my $constraint_definition_regex = ' |
| CONSTRAINT\s |
| `?([\w_\$]+)`?\s (?# constraint name) |
| FOREIGN\sKEY\s |
| \( (?# opening parenthesis) |
| `?([^\)`]+)`? (?# column name for this table) |
| \)\s (?# closing parenthesis) |
| REFERENCES\s |
| `?([^\)`\s]+)`?\s (?# other table) |
| \( (?# opening parenthesis) |
| `?([^\)`\s]+)`? (?# column name for other table) |
| \)\s? (?# closing parenthesis) |
| (?: |
| ([^,]*[^,\s])? (?# options) |
| )? |
| '; |
| |
| my ($constraint_name, $index_column_name, $parent_table_name, $parent_column_name, $constraint_options) = $constraint_definition =~ /$constraint_definition_regex/gx; |
| if (!defined($constraint_name)) { |
| setup_print_error("failed to parse constraint name"); |
| return; |
| } |
| |
| #print "name: $constraint_name\n"; |
| #print "index column: $index_column_name\n"; |
| #print "parent table: $parent_table_name\n"; |
| #print "parent column: $parent_column_name\n"; |
| |
| my $constraint_info = {}; |
| $constraint_info->{name} = $constraint_name; |
| $constraint_info->{index_column} = $index_column_name; |
| $constraint_info->{parent_table} = $parent_table_name; |
| $constraint_info->{parent_column} = $parent_column_name; |
| $constraint_info->{STATEMENT} = $constraint_definition; |
| |
| if ($constraint_options) { |
| #print "constraint options: '$constraint_options'\n"; |
| |
| my $on_update_regex = 'ON UPDATE ((?:SET|NO)?\s?[\w]+)'; |
| my $on_delete_regex = 'ON DELETE ((?:SET|NO)?\s?[\w]+)'; |
| my ($on_update_value) = $constraint_options =~ /$on_update_regex/ig; |
| my ($on_delete_value) = $constraint_options =~ /$on_delete_regex/ig; |
| |
| if ($on_update_value) { |
| #print "ON UPDATE: '$on_update_value'\n" if $on_update_value; |
| $constraint_info->{OPTIONS}{ON_UPDATE} = $on_update_value; |
| } |
| |
| if ($on_delete_value) { |
| #print "ON DELETE: '$on_delete_value'\n" if $on_delete_value; |
| $constraint_info->{OPTIONS}{ON_DELETE} = $on_delete_value; |
| } |
| |
| # Check for remaining constraint options |
| $constraint_options =~ s/$on_update_regex//ig; |
| $constraint_options =~ s/$on_delete_regex//ig; |
| if ($constraint_options =~ /\w/) { |
| print "WARNING: $index_column_name --> $parent_table_name.$parent_column_name constraint options not recognized: '$constraint_options'\n"; |
| } |
| } |
| |
| #print "constraint info:\n" . format_data($constraint_info) . "\n"; |
| return $constraint_info; |
| } |
| |
| #///////////////////////////////////////////////////////////////////////////// |
| |
| =head2 compare_database_to_reference |
| |
| Parameters : |
| Returns : |
| Description : |
| |
| =cut |
| |
| sub compare_database_to_reference { |
| my ($database_info, $reference_info) = @_; |
| if (!$database_info || !$reference_info) { |
| notify($ERRORS{'WARNING'}, 0, "database and reference schema arguments were not supplied"); |
| return; |
| } |
| |
| print "comparing schemas\n\n"; |
| |
| my $changes = { |
| ADD_COLUMN => [], |
| ADD_INDEX => [], |
| ADD_FOREIGN_KEY => [], |
| ALTER_INDEX => [], |
| CHANGE_COLUMN => [], |
| CREATE_TABLE => [], |
| DROP_COLUMN => [], |
| DROP_FOREIGN_KEY => [], |
| DROP_TABLE => [], |
| INSERT => [], |
| }; |
| |
| for my $table_name (keys %$RENAME_COLUMNS) { |
| for my $new_column_name (keys %{$RENAME_COLUMNS->{$table_name}}) { |
| my $original_column_name = $RENAME_COLUMNS->{$table_name}{$new_column_name}; |
| if (!defined($database_info->{CREATE_TABLE}{$table_name}{COLUMNS}{$original_column_name})) { |
| print "$table_name.$original_column_name won't be renamed to $new_column_name because $original_column_name column does not exist\n" if $DEBUG; |
| next; |
| } |
| elsif (defined($database_info->{CREATE_TABLE}{$table_name}{COLUMNS}{$new_column_name})) { |
| setup_print_warning("$table_name.$original_column_name won't be renamed to $new_column_name because $new_column_name column already exists"); |
| next; |
| } |
| |
| my $reference_column = $reference_info->{CREATE_TABLE}{$table_name}{COLUMNS}{$new_column_name}; |
| if (!$reference_column) { |
| setup_print_error("$table_name.$new_column_name definition does not exist in vcl.sql"); |
| print format_data($reference_info->{CREATE_TABLE}{$table_name}{COLUMNS}); |
| next; |
| } |
| |
| print "column will be renamed: $table_name.$original_column_name --> $new_column_name\n"; |
| push @{$changes->{CHANGE_COLUMN}}, "ALTER TABLE `$table_name` CHANGE `$original_column_name` $reference_column->{STATEMENT};"; |
| } |
| } |
| |
| my $drop_indexes = {}; |
| REFERENCE_TABLE: for my $table_name (sort { lc($a) cmp lc($b) } keys %{$reference_info->{CREATE_TABLE}}) { |
| my $reference_table = $reference_info->{CREATE_TABLE}{$table_name}; |
| #if ($DEBUG) { |
| # print format_data($reference_table) . "\n"; |
| # setup_print_break('.'); |
| #} |
| |
| my $database_table = $database_info->{CREATE_TABLE}{$table_name}; |
| if (!defined($database_table)) { |
| print "table exists in reference but not database: $table_name\n" if $DEBUG; |
| push @{$changes->{CREATE_TABLE}}, $reference_table->{STATEMENT}; |
| #$changes->{CREATE_TABLE}{$table_name} = $reference_table; |
| } |
| else { |
| my $reference_columns = $reference_table->{COLUMNS}; |
| my $database_columns = $database_table->{COLUMNS}; |
| |
| REFERENCE_COLUMN: for my $column_name (sort { lc($a) cmp lc($b) } keys %$reference_columns) { |
| my $reference_column = $reference_columns->{$column_name}; |
| my $database_column = $database_columns->{$column_name}; |
| if (!defined($database_column)) { |
| if (my $original_column_name = $RENAME_COLUMNS->{$table_name}{$column_name}) { |
| if ($database_columns->{$original_column_name}) { |
| print "column will not be added because it is being renamed: $table_name.$original_column_name --> $column_name\n" if $DEBUG; |
| next REFERENCE_COLUMN; |
| } |
| } |
| |
| print "column exists in reference but not database: $table_name.$column_name\n" if $DEBUG; |
| push @{$changes->{ADD_COLUMN}}, "ALTER TABLE `$table_name` ADD $reference_column->{STATEMENT};"; |
| #$changes->{ADD_COLUMN}{$table_name}{$column_name} = $reference_column; |
| |
| next REFERENCE_COLUMN; |
| } |
| |
| my $reference_data_type = $reference_column->{DATA_TYPE}; |
| my $database_data_type = $database_column->{DATA_TYPE}; |
| if (lc($reference_data_type) ne lc($database_data_type)) { |
| print "$table_name.$column_name data type will be changed: $database_data_type --> $reference_data_type\n"; |
| push @{$changes->{CHANGE_COLUMN}}, "ALTER TABLE `$table_name` CHANGE `$column_name` $reference_column->{STATEMENT};"; |
| } |
| |
| REFERENCE_COLUMN_OPTION: for my $option_name (sort { lc($a) cmp lc($b) } keys %{$reference_column->{OPTIONS}}) { |
| my $reference_column_value = $reference_column->{OPTIONS}{$option_name}; |
| my $database_column_value = $database_column->{OPTIONS}{$option_name}; |
| |
| if (!defined($database_column_value)) { |
| print "$table_name.$column_name '$option_name' is set to '$reference_column_value' in reference, undefined in database\n" if $DEBUG; |
| print "reference : $reference_column->{STATEMENT}\n"; |
| print "database : $database_column->{STATEMENT}\n"; |
| } |
| elsif (lc($reference_column_value) ne lc($database_column_value)) { |
| print "$table_name.$column_name '$option_name' different, reference: '$reference_column_value', database: '$database_column_value'\n" if $DEBUG; |
| } |
| else { |
| next REFERENCE_COLUMN_OPTION; |
| } |
| |
| push @{$changes->{CHANGE_COLUMN}}, "ALTER TABLE `$table_name` CHANGE `$column_name` $reference_column->{STATEMENT};"; |
| #$changes->{CHANGE_COLUMN}{$table_name}{$column_name} = $reference_column; |
| } |
| } |
| |
| my $reference_table_indexes = $reference_table->{INDEXES}; |
| #print format_data($reference_table_indexes) . "\n"; |
| |
| my $database_table_indexes = $database_table->{INDEXES}; |
| REFERENCE_TABLE_INDEX: for my $reference_index_name (keys %$reference_table_indexes) { |
| my $reference_index = $reference_table_indexes->{$reference_index_name}; |
| my $reference_index_type = $reference_index->{TYPE}; |
| my @reference_index_column_names = sort { lc($a) cmp lc($b) } keys %{$reference_index->{COLUMNS}}; |
| my $reference_index_statement = $reference_index->{STATEMENT}; |
| |
| # Check if database table contains an index with the same name |
| if (!defined($database_table_indexes->{$reference_index_name})) { |
| print "$table_name table '$reference_index_name' index does not exist in database\n" if $DEBUG; |
| push @{$changes->{ADD_INDEX}}, "ALTER TABLE `$table_name` ADD $reference_index_statement;"; |
| #$changes->{ADD_INDEX}{$table_name}{$reference_index_name} = $reference_index; |
| next REFERENCE_TABLE_INDEX; |
| } |
| else { |
| #print "$table_name table '$reference_index_name' index exists in database\n" if $DEBUG; |
| } |
| |
| # Index with same name exists, compare them |
| my $database_table_index = $database_table_indexes->{$reference_index_name}; |
| my $database_table_index_type = $database_table_index->{TYPE}; |
| my @compare_table_index_column_names = sort { lc($a) cmp lc($b) } keys %{$database_table_index->{COLUMNS}}; |
| my $database_table_index_statement = $database_table_index->{STATEMENT}; |
| |
| |
| my $different = 0; |
| if ($reference_index_type ne $database_table_index_type) { |
| $different = 1; |
| if ($DEBUG) { |
| print "$table_name table '$reference_index_name' index type is different\n"; |
| print "reference : $reference_index_type\n"; |
| print "database : $database_table_index_type\n"; |
| } |
| } |
| elsif (!compare_array_elements(\@reference_index_column_names, \@compare_table_index_column_names)) { |
| $different = 1; |
| if ($DEBUG) { |
| print "$table_name table '$reference_index_name' index contains different columns:\n"; |
| print "reference : " . join(', ', @reference_index_column_names) . "\n"; |
| print "database : " . join(', ', @compare_table_index_column_names) . "\n"; |
| } |
| } |
| |
| if ($different) { |
| $drop_indexes->{$reference_index_name} = 1; |
| push @{$changes->{ALTER_INDEX}}, "ALTER TABLE `$table_name` DROP INDEX `$reference_index_name` , ADD $reference_index_statement;"; |
| #$changes->{ALTER_INDEX}{$table_name}{$reference_index_name} = $reference_index; |
| } |
| } # reference table index |
| } # database table defined |
| |
| my @column_order = @{$reference_table->{COLUMN_ORDER}}; |
| for (my $i=1; $i<scalar(@column_order); $i++) { |
| my $column = $column_order[$i]; |
| my $previous_column = $column_order[$i-1]; |
| push @{$changes->{MODIFY_COLUMN}}, "ALTER TABLE `$table_name` MODIFY COLUMN $column->{STATEMENT} AFTER `$previous_column->{name}`;"; |
| } |
| |
| } # reference table |
| |
| # Check for explicit "DROP TABLE" statements |
| for my $table_name (sort { lc($a) cmp lc($b) } @{$reference_info->{DROP_TABLE}}) { |
| if (defined($database_info->{CREATE_TABLE}{$table_name})) { |
| push @{$changes->{DROP_TABLE}}, "DROP TABLE IF EXISTS `$table_name`;"; |
| } |
| } |
| |
| my @insert_statements = @{$reference_info->{INSERT}}; |
| for my $insert_statement (@insert_statements) { |
| push @{$changes->{INSERT}}, $insert_statement; |
| } |
| |
| ## Check for explicit "ADD COLUMN" statements |
| #my $add_columns = {}; |
| #for my $table_name (sort { lc($a) cmp lc($b) } keys %{$reference_info->{ADD_COLUMN}}) { |
| # for my $column_name (sort { lc($a) cmp lc($b) } keys %{$reference_info->{ADD_COLUMN}{$table_name}}) { |
| # if ($database_info->{CREATE_TABLE}{$table_name}{COLUMNS}{$column_name}) { |
| # print "$table_name.$column_name already exists in database\n"; |
| # next; |
| # } |
| # else { |
| # print "$table_name.$column_name will be added\n" if $DEBUG; |
| # my $reference_column = $reference_info->{ADD_COLUMN}{$table_name}{$column_name}; |
| # push @{$changes->{ADD_COLUMN}}, "ALTER TABLE `$table_name` ADD $reference_column->{STATEMENT}"; |
| # $add_columns->{$table_name}{$column_name} = 1; |
| # } |
| # } |
| #} |
| |
| |
| |
| # Check for explicit "DROP COLUMN" statements |
| my $drop_columns = {}; |
| for my $table_name (sort { lc($a) cmp lc($b) } keys %{$reference_info->{DROP_COLUMN}}) { |
| for my $column_name (sort { lc($a) cmp lc($b) } keys %{$reference_info->{DROP_COLUMN}{$table_name}}) { |
| if (defined($database_info->{CREATE_TABLE}{$table_name}{COLUMNS}{$column_name})) { |
| print "column exists in database, it will be dropped: $table_name.$column_name\n" if $DEBUG; |
| } |
| else { |
| print "column does not exist in database and won't be added, it won't be dropped: $table_name.$column_name\n" if $DEBUG; |
| next; |
| } |
| |
| push @{$changes->{DROP_COLUMN}}, "ALTER TABLE `$table_name` DROP `$column_name`"; |
| $drop_columns->{$table_name}{$column_name} = 1; |
| |
| my $referenced_constraints = $database_info->{REFERENCED_CONSTRAINTS}{$table_name}{$column_name}; |
| if ($referenced_constraints) { |
| #print "referenced constraints:\n" . format_data($referenced_constraints) . "\n"; |
| } |
| |
| my $referencing_constraint = $database_info->{REFERENCING_CONSTRAINTS}{$table_name}{$column_name}; |
| if ($referencing_constraint) { |
| #print "referencing constraints:\n" . format_data($referencing_constraint) . "\n"; |
| push @{$changes->{DROP_FOREIGN_KEY}}, "ALTER TABLE `$table_name` DROP FOREIGN KEY `$referencing_constraint->{name}`;"; |
| } |
| |
| my $database_table_indexes = $database_info->{CREATE_TABLE}{$table_name}{INDEXES}; |
| for my $database_index_name (keys %$database_table_indexes) { |
| my $database_index = $database_table_indexes->{$database_index_name}; |
| my @database_index_column_names = sort { lc($a) cmp lc($b) } keys %{$database_index->{COLUMNS}}; |
| if (grep { $_ eq $column_name } @database_index_column_names) { |
| if (scalar(@database_index_column_names) == 1) { |
| print "'$database_index_name' index will be dropped automatically when $table_name.$column_name column is dropped\n" if $DEBUG; |
| } |
| elsif ($drop_indexes->{$database_index_name}) { |
| print "'$database_index_name' index will be replaced\n" if $DEBUG; |
| } |
| else { |
| print "'$database_index_name' index will be dropped\n" if $DEBUG; |
| push @{$changes->{ALTER_INDEX}}, "ALTER TABLE `$table_name` DROP INDEX `$database_index_name`;"; |
| } |
| } |
| } |
| |
| } |
| } |
| |
| REFERENCE_CONSTRAINT: for my $constraint_name (sort { lc($a) cmp lc($b) } keys %{$reference_info->{CONSTRAINTS}}) { |
| my $reference_constraint = $reference_info->{CONSTRAINTS}{$constraint_name}; |
| my $reference_index_table = $reference_constraint->{index_table}; |
| my $reference_index_column = $reference_constraint->{index_column}; |
| my $reference_parent_table = $reference_constraint->{parent_table}; |
| my $reference_parent_column = $reference_constraint->{parent_column}; |
| my $reference_statement = $reference_constraint->{STATEMENT}; |
| my $reference_on_update = $reference_constraint->{OPTIONS}{ON_UPDATE} || ''; |
| my $reference_on_delete = $reference_constraint->{OPTIONS}{ON_DELETE} || ''; |
| |
| my $database_constraint = $database_info->{CONSTRAINTS}{$constraint_name}; |
| if ($database_constraint) { |
| my $database_index_table = $database_constraint->{index_table}; |
| my $database_index_column = $database_constraint->{index_column}; |
| my $database_parent_table = $database_constraint->{parent_table}; |
| my $database_parent_column = $database_constraint->{parent_column}; |
| my $database_statement = $database_constraint->{STATEMENT}; |
| my $database_on_update = $database_constraint->{OPTIONS}{ON_UPDATE} || ''; |
| my $database_on_delete = $database_constraint->{OPTIONS}{ON_DELETE} || ''; |
| |
| if ($reference_index_table ne $database_index_table || |
| $reference_index_column ne $database_index_column || |
| $reference_parent_table ne $database_parent_table || |
| $reference_parent_column ne $database_parent_column || |
| $reference_on_update ne $database_on_update || |
| $database_on_delete ne $database_on_delete) { |
| |
| if ($DEBUG) { |
| print "constraints are different:\n"; |
| print "reference : $reference_statement\n"; |
| print "database : $database_statement\n"; |
| } |
| |
| push @{$changes->{DROP_FOREIGN_KEY}}, "ALTER TABLE `$database_index_table` DROP FOREIGN KEY `$constraint_name`;"; |
| #$changes->{DROP_FOREIGN_KEY}{$database_index_table}{$database_index_column} = $database_constraint; |
| } |
| else { |
| next REFERENCE_CONSTRAINT; |
| } |
| } |
| else { |
| print "constraint does not exist in database: $reference_statement\n" if ($DEBUG); |
| } |
| |
| $reference_constraint->{STATEMENT} = "ALTER TABLE `$reference_index_table` ADD $reference_statement;"; |
| push @{$changes->{ADD_FOREIGN_KEY}}, $reference_constraint; |
| #push @{$changes->{ADD_FOREIGN_KEY}}, "ALTER TABLE `$reference_index_table` ADD $reference_statement;"; |
| #$changes->{ADD_FOREIGN_KEY}{$reference_index_table}{$reference_index_column} = $reference_constraint; |
| } |
| |
| # Check for extra constraints in database not in reference |
| for my $constraint_name (sort { lc($a) cmp lc($b) } keys %{$database_info->{CONSTRAINTS}}) { |
| my $database_constraint = $database_info->{CONSTRAINTS}{$constraint_name}; |
| my $database_index_table = $database_constraint->{index_table}; |
| my $database_index_column = $database_constraint->{index_column}; |
| if (!defined($reference_info->{CONSTRAINTS}{$constraint_name})) { |
| if ($DEBUG) { |
| print "constraint exists in database but not reference:\n" . format_data($database_constraint) . "\n" if ($DEBUG); |
| } |
| #$changes->{DROP_FOREIGN_KEY}{$constraint_name} = 1; |
| #$changes->{DROP_FOREIGN_KEY}{$database_index_table}{$database_index_column} = $database_constraint; |
| push @{$changes->{DROP_FOREIGN_KEY}}, "ALTER TABLE `$database_index_table` DROP FOREIGN KEY `$constraint_name`;"; |
| } |
| } |
| |
| ## Check compare table for columns not defined in base |
| #for my $database_column_name (keys %$database_columns) { |
| # if (!defined($reference_columns->{$database_column_name})) { |
| # |
| # my @referenced_constraints = get_referenced_constraints($reference_database_name, $table_name, $database_column_name); |
| # for my $constraint (@referenced_constraints) { |
| # my $constraint_name = $constraint->{CONSTRAINT_NAME}; |
| # #push @{$changes->{DROP_FOREIGN_KEY}}, "ALTER IGNORE TABLE `$table_name` DROP FOREIGN KEY `$constraint_name`;"; |
| # $changes->{DROP_FOREIGN_KEY}{$table_name}{$constraint_name} = 1; |
| # } |
| # |
| # my $referencing_constraint = get_referencing_constraint($reference_database_name, $table_name, $database_column_name); |
| # if ($referencing_constraint) { |
| # my $constraint_name = $referencing_constraint->{CONSTRAINT_NAME}; |
| # #push @{$changes->{DROP_FOREIGN_KEY}}, "ALTER IGNORE TABLE `$table_name` DROP FOREIGN KEY `$constraint_name`;"; |
| # $changes->{DROP_FOREIGN_KEY}{$table_name}{$constraint_name} = 1; |
| # } |
| # |
| # print "$table_name table '$database_column_name' column does not exist in reference\n"; |
| # # ALTER TABLE `table_name` DROP `column_name` |
| # push @{$changes->{DROP_COLUMN}}, "ALTER TABLE `$table_name` DROP `$database_column_name`;"; |
| # } |
| #} |
| |
| return $changes; |
| } |
| |
| #///////////////////////////////////////////////////////////////////////////// |
| |
| =head2 compare_array_elements |
| |
| Parameters : $array_ref_1, $array_ref_2 |
| Returns : boolean (0 if different, 1 if identical, undef if error) |
| Description : Compares the elements of 2 arrays. Arrays are considered |
| identical if the number of elements in each array is identical |
| and each array contains all of the elements of the other array. |
| This subroutine does not consider order. It only compares arrays |
| containing scalar elements. If an array element is a reference |
| undef will be returned. |
| |
| =cut |
| |
| sub compare_array_elements { |
| my ($array_ref_1, $array_ref_2) = @_; |
| if (!defined($array_ref_1) || !defined($array_ref_2)) { |
| notify($ERRORS{'WARNING'}, 0, "array reference arguments were not supplied"); |
| return; |
| } |
| my $type_1 = ref($array_ref_1); |
| my $type_2 = ref($array_ref_2); |
| if (!$type_1) { |
| notify($ERRORS{'WARNING'}, 0, "both arguments must be array references, 1st argument is not a reference"); |
| } |
| elsif (!$type_2) { |
| notify($ERRORS{'WARNING'}, 0, "both arguments must be array references, 2nd argument is not a reference"); |
| } |
| elsif ($type_1 ne 'ARRAY') { |
| notify($ERRORS{'WARNING'}, 0, "both arguments must be array references, 1st argument reference type: $type_1"); |
| } |
| elsif ($type_2 ne 'ARRAY') { |
| notify($ERRORS{'WARNING'}, 0, "both arguments must be array references, 2nd argument reference type: $type_2"); |
| } |
| |
| my @array_1 = @$array_ref_1; |
| my @array_2 = @$array_ref_2; |
| |
| my $array_size_1 = scalar(@array_1); |
| my $array_size_2 = scalar(@array_2); |
| if ($array_size_1 != $array_size_2) { |
| notify($ERRORS{'DEBUG'}, 0, "arrays sizes are different, 1st array: $array_size_1, 2nd array: $array_size_2"); |
| return 0; |
| } |
| |
| if (grep { ref($_) } @array_1) { |
| notify($ERRORS{'WARNING'}, 0, "unable to compare arrays, 1st array contains a reference value"); |
| return; |
| } |
| elsif (grep { ref($_) } @array_2) { |
| notify($ERRORS{'WARNING'}, 0, "unable to compare arrays, 2nd array contains a reference value"); |
| return; |
| } |
| |
| my %hash_1 = map { $_ => 1 } @array_1; |
| my %hash_2 = map { $_ => 1 } @array_2; |
| |
| for my $key (keys %hash_1) { |
| if (!defined($hash_2{$key})) { |
| notify($ERRORS{'DEBUG'}, 0, "array elements are different, 1st array has element containing '$key', 2nd array does not"); |
| return 0; |
| } |
| } |
| |
| for my $key (keys %hash_2) { |
| if (!defined($hash_1{$key})) { |
| notify($ERRORS{'DEBUG'}, 0, "array elements are different, 2nd array has element containing '$key', 1st array does not"); |
| return 0; |
| } |
| } |
| |
| notify($ERRORS{'DEBUG'}, 0, "arrays contain identical elements"); |
| return 1; |
| } |
| |
| #///////////////////////////////////////////////////////////////////////////// |
| |
| =head2 update_database |
| |
| Parameters : |
| Returns : |
| Description : |
| |
| =cut |
| |
| sub update_database { |
| my ($database_name, $changes) = @_; |
| if (!$database_name || !$changes) { |
| notify($ERRORS{'WARNING'}, 0, "database name and schema change hash arguments were not supplied"); |
| return; |
| } |
| |
| my $mn_os = get_mn_os(); |
| if (!$mn_os) { |
| setup_print_error("unable to create database, failed to retrieve OS object to control this management node"); |
| return; |
| } |
| |
| # Check for duplicate drop foreign key statements |
| my @drop_foreign_key_statements; |
| my $foreign_key_hash = {}; |
| for my $foreign_key_statement (@{$changes->{DROP_FOREIGN_KEY}}) { |
| my ($foreign_key) = $foreign_key_statement =~ /FOREIGN KEY `?([\w_\$]+)/; |
| if (!$foreign_key) { |
| setup_print_warning("failed to parse foreign key statement: $foreign_key_statement\n"); |
| } |
| if (!defined($foreign_key_hash->{$foreign_key})) { |
| $foreign_key_hash->{$foreign_key} = 1; |
| push @drop_foreign_key_statements, $foreign_key_statement; |
| } |
| else { |
| print "duplicate drop foreign key: $foreign_key\n"; |
| } |
| } |
| $changes->{DROP_FOREIGN_KEY} = \@drop_foreign_key_statements; |
| |
| |
| my @operations = ( |
| 'DROP_FOREIGN_KEY', |
| 'CREATE_TABLE', |
| 'ADD_COLUMN', |
| 'CHANGE_COLUMN', |
| 'INSERT', |
| #'UPDATE', |
| 'DROP_COLUMN', |
| 'DROP_TABLE', |
| 'ADD_INDEX', |
| 'ALTER_INDEX', |
| #'MODIFY_COLUMN', |
| ); |
| |
| for my $operation (@operations) { |
| my $temp_sql_file_path = "/tmp/$database_name\_$timestamp\_$operation.sql"; |
| |
| my $statements = $changes->{$operation}; |
| next unless $statements; |
| |
| my $statement_count = scalar(@$statements); |
| if (!$statement_count) { |
| next; |
| } |
| |
| |
| for my $statement (@$statements) { |
| if (database_execute($statement, $database_name)) { |
| print "executed statement: " . substr($statement, 0, 97); |
| if (length($statement) > 97) { |
| print "..."; |
| } |
| print "\n"; |
| } |
| else { |
| setup_print_warning("failed to execute statement:"); |
| print "$statement\n\n"; |
| exit; |
| } |
| } |
| } |
| |
| my $temp_sql_file_path = "/tmp/$database_name\_$timestamp\_ADD_FOREIGN_KEY.sql"; |
| my $add_constraint_count = 0; |
| CONSTRAINT: for my $constraint (@{$changes->{ADD_FOREIGN_KEY}}) { |
| my $index_table = $constraint->{index_table}; |
| my $index_column = $constraint->{index_column}; |
| my $parent_table = $constraint->{parent_table}; |
| my $parent_column = $constraint->{parent_column}; |
| |
| my $select_statement = <<EOF; |
| SELECT DISTINCT |
| $index_table.$index_column |
| FROM |
| $index_table |
| WHERE |
| $index_table.$index_column IS NOT NULL |
| AND NOT EXISTS ( |
| SELECT |
| $parent_table.$parent_column |
| FROM |
| $parent_table |
| WHERE |
| $parent_table.$parent_column = $index_table.$index_column |
| ) |
| EOF |
| |
| my @rows = database_select($select_statement, $database_name); |
| if (@rows) { |
| setup_print_warning("\nunable to add constraint: $index_table.$index_column --> $parent_table.$parent_column"); |
| setup_print_wrap("$index_table.$index_column contains the following values which do not have a corresponding $parent_table.$parent_column value:"); |
| |
| for my $row (@rows) { |
| print "$index_table.$index_column=" . $row->{$index_column} . "\n"; |
| } |
| print "\n"; |
| #print format_data($constraint) . "\n\n"; |
| next CONSTRAINT; |
| } |
| |
| my $statement = $constraint->{STATEMENT}; |
| if (database_execute($statement, $database_name)) { |
| print "added constraint: $index_table.$index_column --> $parent_table.$parent_column\n"; |
| } |
| else { |
| setup_print_warning("failed to add constraint:"); |
| print "$statement\n\n"; |
| exit; |
| } |
| } |
| |
| return 1; |
| } |
| |
| #////////////////////////////////////////////////////////////////////////////// |
| |
| =head2 dump_database_to_file |
| |
| Parameters : $database_name, $sql_file_path, @mysqldump_options (optional) |
| Returns : boolean |
| Description : Uses mysqldump to dump a database to a file. |
| |
| =cut |
| |
| sub dump_database_to_file { |
| my $database_name = shift; |
| my $sql_file_path = shift; |
| if (!$database_name || !$sql_file_path) { |
| notify($ERRORS{'WARNING'}, 0, "database name and SQL file path arguments were not specified"); |
| return; |
| } |
| |
| my @mysqldump_options_argument = @_; |
| |
| my $mn_os = get_mn_os(); |
| |
| my @options = ( |
| "host=$DATABASE_SERVER", |
| "user=$DATABASE_USERNAME", |
| "password='$DATABASE_PASSWORD'", |
| #"result-file=$sql_file_path", |
| "databases $database_name", |
| "insert-ignore", |
| "order-by-primary", |
| "allow-keywords", # Allow creation of column names that are keywords |
| "flush-privileges", # Emit a FLUSH PRIVILEGES statement after dumping the mysql database |
| "skip-lock-tables", # Do not lock all tables to be dumped before dumping them |
| "skip-add-drop-table", # Do not add a DROP TABLE statement before each CREATE TABLE statement |
| "skip-add-locks", # Do not surround each table dump with LOCK TABLES and UNLOCK TABLES statements |
| "skip-comments", # Do not write additional information in the dump file such as program version, server version, and host |
| "skip-disable-keys", # Do not surround the INSERT statements with /*!40000 ALTER TABLE tbl_name DISABLE KEYS */; |
| "skip-set-charset", # Do not add SET NAMES default_character_set to the output. |
| "skip-triggers", # Do not include triggers for each dumped table in the output |
| "skip-extended-insert", # Use single-row INSERT statements |
| "complete-insert", # Use complete INSERT statements that include column names |
| ); |
| |
| my $command = "mysqldump"; |
| for my $option (@options, @mysqldump_options_argument) { |
| $command .= " "; |
| if ($option !~ /^-/) { |
| $command .= "--"; |
| } |
| $command .= $option; |
| } |
| $command .= " > $sql_file_path"; |
| |
| print "\ndumping $database_name database to $sql_file_path..."; |
| my ($exit_status, $output) = $mn_os->execute($command); |
| print "\n"; |
| if (!defined($output)) { |
| setup_print_error("failed to execute command to dump $database_name database:\n$command"); |
| return; |
| } |
| elsif ($exit_status ne '0') { |
| setup_print_error("failed to dump $database_name database to $sql_file_path, exit status: $exit_status\n\ncommand:\n$command\n\noutput:\n" . join("\n", @$output) . "\n"); |
| |
| # Check for access denied error: |
| # ERROR 1044 (42000) at line 1: Access denied for user '<username>'@'<IP address>' to database '<database>' |
| # mysqldump: Got error: 1044: Access denied for user '<username>'@'<IP address>' to database '<database>' when selecting the database |
| if (my ($access_denied_line) = grep(/Access denied/i, @$output)) { |
| my ($source_host) = $access_denied_line =~ /\@'([^']+)'/; |
| $source_host = '*' if !defined($source_host); |
| print "\nexecute the following command on database server $DATABASE_SERVER:\n"; |
| print "mysql -e \"GRANT SELECT,INSERT,UPDATE,DELETE,CREATE TEMPORARY TABLES ON $database_name.* TO '$DATABASE_USERNAME'\@'$source_host' IDENTIFIED BY '$DATABASE_PASSWORD';\"\n"; |
| } |
| return; |
| } |
| else { |
| print "done.\n"; |
| } |
| |
| # Add the command used to the output file |
| $mn_os->append_text_file($sql_file_path, "/*\n$command\n*/\n"); |
| |
| print "\n"; |
| return 1; |
| } |
| |
| #////////////////////////////////////////////////////////////////////////////// |
| |
| =head2 setup |
| |
| Parameters : none |
| Returns : |
| Description : |
| |
| =cut |
| |
| sub setup { |
| push @{$ENV{setup_path}}, 'Database Management'; |
| |
| while (1) { |
| setup_print_break('='); |
| my $menu = setup_get_menu(); |
| my $choice = setup_get_menu_choice($menu); |
| last unless defined $choice; |
| |
| my $choice_name = $choice->{name}; |
| my $choice_sub_ref = $choice->{sub_ref}; |
| my $choice_parent_menu_names = $choice->{parent_menu_names}; |
| |
| push @{$ENV{setup_path}}, $choice_name; |
| |
| my $package_name = get_code_ref_package_name($choice_sub_ref); |
| my $subroutine_name = get_code_ref_subroutine_name($choice_sub_ref); |
| |
| setup_print_break('.'); |
| &$choice_sub_ref(); |
| |
| pop @{$ENV{setup_path}}; |
| } |
| } |
| |
| #////////////////////////////////////////////////////////////////////////////// |
| |
| =head2 setup_get_menu |
| |
| Parameters : none |
| Returns : |
| Description : |
| |
| =cut |
| |
| sub setup_get_menu { |
| my $menu = { |
| 'Database Management' => { |
| 'Upgrade Database' => \&setup_upgrade_database, |
| 'Backup Database' => \&setup_backup_database, |
| }, |
| }; |
| |
| return $menu; |
| } |
| |
| #////////////////////////////////////////////////////////////////////////////// |
| |
| =head2 setup_select_database |
| |
| Parameters : $message (optional) |
| Returns : string |
| Description : |
| |
| =cut |
| |
| sub setup_select_database { |
| my ($message) = @_; |
| |
| my @database_names = get_database_names(); |
| if (!@database_names) { |
| setup_print_error("failed to retrieve database names from database server"); |
| return; |
| } |
| |
| # Remove special databases from array |
| @database_names = grep(!/^(mysql|information_schema)$/i, @database_names); |
| |
| if ($message) { |
| print "\n$message:\n"; |
| } |
| else { |
| print "\nSelect database:\n"; |
| } |
| |
| my $choice_index = setup_get_array_choice(@database_names); |
| return unless defined($choice_index); |
| |
| my $database_name = $database_names[$choice_index]; |
| print "Selected database: $database_name\n"; |
| |
| return $database_name; |
| } |
| |
| #////////////////////////////////////////////////////////////////////////////// |
| |
| =head2 setup_backup_database |
| |
| Parameters : none |
| Returns : |
| Description : |
| |
| =cut |
| |
| sub setup_backup_database { |
| my @database_names = get_database_names(); |
| if (!@database_names) { |
| setup_print_error("failed to retrieve database names from database server"); |
| return; |
| } |
| |
| my $database_name = setup_select_database('Select database to backup') || return; |
| |
| my $default_backup_file_name = "$database_name\_$timestamp.sql"; |
| my $default_backup_file_path = "/root/$default_backup_file_name"; |
| |
| my $backup_file_path; |
| while (1) { |
| $backup_file_path = setup_get_input_file_path("\nEnter database backup file path\n", $default_backup_file_path); |
| return unless defined($backup_file_path); |
| |
| if (!$backup_file_path) { |
| $backup_file_path = $default_backup_file_path; |
| } |
| elsif (-d $backup_file_path) { |
| my $backup_directory_path = $backup_file_path; |
| $backup_directory_path =~ s/[\/\\]*$//g; |
| $backup_file_path = "$backup_directory_path/$default_backup_file_name"; |
| print "\nPath entered is a directory: $backup_directory_path\n"; |
| |
| if (!setup_confirm("Use default file name? $backup_file_path", 'y')) { |
| next; |
| } |
| } |
| elsif (-e $backup_file_path) { |
| print "File already exists: $backup_file_path\n"; |
| next; |
| } |
| elsif ($backup_file_path !~ /\.sql$/i) { |
| if (setup_confirm("Database backup file path does not end with '.sql', append this extension to file path? ($backup_file_path.sql)")) { |
| $backup_file_path .= '.sql'; |
| } |
| } |
| last; |
| } |
| |
| my @ignored_tables; |
| if (setup_confirm("\nDo you want any tables to be ignored?", 'n')) { |
| my @database_tables = get_database_table_names($database_name); |
| if (!@database_tables) { |
| setup_print_error("failed to retrieve table names from $database_name database"); |
| return; |
| } |
| |
| my %database_table_hash = map { $_ => {'name' => $_, 'ignored' => ''} } @database_tables; |
| #print format_data(\%database_table_hash) . "\n"; |
| |
| IGNORE_TABLE: while (1) { |
| print "\nSelect tables to ignore:\n"; |
| my $table_name_choice = setup_get_hash_choice(\%database_table_hash, 'name', 'ignored'); |
| last IGNORE_TABLE if !defined($table_name_choice); |
| |
| my $table_ignored = $database_table_hash{$table_name_choice}{ignored}; |
| if ($table_ignored) { |
| $database_table_hash{$table_name_choice}{ignored} = ''; |
| } |
| else { |
| $database_table_hash{$table_name_choice}{ignored} = '*'; |
| } |
| } |
| |
| for my $table_name (sort { lc($a) cmp lc($b) } keys %database_table_hash) { |
| if ($database_table_hash{$table_name}{ignored}) { |
| push @ignored_tables, $table_name; |
| } |
| } |
| } |
| |
| print "\n$database_name database will be backed up to $backup_file_path\n"; |
| print "Tables ignored: " . (@ignored_tables ? "\n " . join("\n ", @ignored_tables) : '<none>') . "\n"; |
| return unless setup_confirm("Confirm"); |
| |
| my @mysqldump_options = map { "--ignore-table=$database_name.$_" } @ignored_tables; |
| return dump_database_to_file($database_name, $backup_file_path, @mysqldump_options); |
| } |
| |
| #////////////////////////////////////////////////////////////////////////////// |
| |
| =head2 setup_upgrade_database |
| |
| Parameters : |
| Returns : |
| Description : |
| |
| =cut |
| |
| sub setup_upgrade_database { |
| my ($database_name, $reference_sql_file_path) = @_; |
| |
| if (!$database_name) { |
| my @database_names = get_database_names(); |
| if (!@database_names) { |
| setup_print_error("failed to retrieve database names from database server"); |
| return; |
| } |
| $database_name = setup_select_database('Select database to upgrade'); |
| if (!$database_name) { |
| print "database not selected\n"; |
| return; |
| } |
| } |
| |
| my ($database_sql_file_handle, $database_sql_file_path) = tempfile(CLEANUP => 1, SUFFIX => '.sql'); |
| if (!dump_database_to_file($database_name, $database_sql_file_path, '--no-data')) { |
| setup_print_error("failed to dump '$database_name' database to file: $database_sql_file_path"); |
| return; |
| } |
| |
| my $database_info = parse_sql_file($database_sql_file_path); |
| if (!$database_info) { |
| setup_print_error("failed to parse SQL file: $database_sql_file_path"); |
| return; |
| } |
| unlink $database_sql_file_path; |
| print "\n"; |
| |
| # Check if it looks like this scripts resides in complete copy of extracted VCL source |
| if (!defined($reference_sql_file_path)) { |
| # Get the path to this script and its parent directory |
| my $current_file_path = abs_path(__FILE__); |
| my ($current_file_name, $current_directory_path) = fileparse($current_file_path); |
| $current_directory_path =~ s/\/$//g; |
| |
| if ($current_file_path =~ m|^(.+)/managementnode/bin/$current_file_name$|) { |
| my $reference_directory_path = $1; |
| $reference_sql_file_path = "$reference_directory_path/mysql/vcl.sql"; |
| |
| if (!verify_vcl_sql_file($reference_sql_file_path)) { |
| $reference_sql_file_path = undef; |
| } |
| } |
| if (!$reference_sql_file_path) { |
| my $sql_file_location_message; |
| $sql_file_location_message .= "Please enter the path to the vcl.sql file which was included with the VCL $VERSION source code."; |
| $sql_file_location_message .= " This should be located where you extracted the source code in a directory named 'sql'."; |
| $sql_file_location_message .= " The path to this file most likely ends with 'apache-VCL-$VERSION/mysql/vcl.sql'\n"; |
| setup_print_wrap($sql_file_location_message); |
| |
| while (!$reference_sql_file_path) { |
| $reference_sql_file_path = setup_get_input_file_path("Enter path to vcl.sql file"); |
| return unless defined($reference_sql_file_path); |
| |
| print "\n"; |
| if (!verify_vcl_sql_file($reference_sql_file_path)) { |
| $reference_sql_file_path = undef; |
| return; |
| } |
| } |
| } |
| } |
| |
| my $reference_info = parse_sql_file($reference_sql_file_path); |
| if (!$reference_info) { |
| return; |
| } |
| |
| my $changes = compare_database_to_reference($database_info, $reference_info); |
| if (!$changes) { |
| return; |
| } |
| |
| return update_database($database_name, $changes); |
| } |
| |
| #////////////////////////////////////////////////////////////////////////////// |
| |
| 1; |