From 41bf6261b22f1566e075792314f5bbcad46f4623 Mon Sep 17 00:00:00 2001 From: David Cook Date: Tue, 20 Jun 2023 04:18:09 +0000 Subject: [PATCH] Bug 34064: Add an audit script to compare database with kohastructure.sql This script can take a connected database handle and compare that database schema against kohastructure.sql to see what changes the database would need in order to match kohastructure.sql NOTE: It uses SQL::Translation::Diff, which is installed with DBIx::Class. WARNING: The diff doesn't seem to compare comments, so that difference won't appear in the output. If we wanted, we could easily enhance the audit_database.pl script to also compare comments. WARNING: The output is a proposed series of SQL commands. While they are useful to review, they won't always duplicate the changes done by updatedatabase.pl, so it's important to carefully analyze the output. The key purpose of this audit script is to just highlight the differences between the two. Test plan: 0. Apply patch 1. vi ./installer/data/mysql/kohastructure.sql 2. Comment out some columns, change NULL status, or whatever you like 3. perl misc/maintenance/audit_database.pl \ --filename /kohadevbox/koha/installer/data/mysql/kohastructure.sql 4. Note that the output includes SQL commands to change the database to match the new kohastructure.sql --- misc/maintenance/audit_database.pl | 78 ++++++++++++++++++++++++++++++ 1 file changed, 78 insertions(+) create mode 100755 misc/maintenance/audit_database.pl diff --git a/misc/maintenance/audit_database.pl b/misc/maintenance/audit_database.pl new file mode 100755 index 0000000000..0a91ba6870 --- /dev/null +++ b/misc/maintenance/audit_database.pl @@ -0,0 +1,78 @@ +#!/usr/bin/perl + +use Modern::Perl; +use SQL::Translator; +use SQL::Translator::Diff; +use Getopt::Long; + +use C4::Context; + +my $filename = "./installer/data/mysql/kohastructure.sql"; + +GetOptions( + "filename=s" => \$filename, +) or die("Error in command line arguments\n"); + +if ( ! -f $filename ){ + die("Filename '$filename' does not exist\n"); +} + +my $sql_schema = get_kohastructure({ filename => $filename, }); +my $db_schema = get_db(); + +if ($sql_schema && $db_schema){ + my $diff = SQL::Translator::Diff->new({ + output_db => 'MySQL', + source_schema => $db_schema, + target_schema => $sql_schema, + no_batch_alters => 1, + })->compute_differences->produce_diff_sql; + + print $diff; + print "\n"; + print "WARNING!!!\n"; + print "These commands are only suggestions! They are not a replacement for updatedatabase.pl!\n"; + print "Review the database, updatedatabase.pl, and kohastructure.sql before making any changes!\n"; + print "\n"; +} + +sub get_db { + my $dbh = C4::Context->dbh; + my $parser = SQL::Translator->new( + parser => 'DBI', + parser_args => { + dbh => $dbh, + }, + ); + my $schema = $parser->translate(); + + #NOTE: Hack the schema to remove autoincrement + #Otherwise, that difference will cause options for all tables to be reset unecessarily + my @tables = $schema->get_tables(); + foreach my $table (@tables){ + my @new_options = (); + my $replace_options = 0; + my $options = $table->{options}; + foreach my $hashref (@$options){ + if ( $hashref->{AUTO_INCREMENT} ){ + $replace_options = 1; + } + else { + push(@new_options,$hashref); + } + } + if ($replace_options){ + @{$table->{options}} = @new_options; + } + } + return $schema; +} + +sub get_kohastructure { + my ($args) = @_; + my $filename = $args->{filename}; + my $translator = SQL::Translator->new(); + $translator->parser("MySQL"); + my $schema = $translator->translate($filename); + return $schema; +} -- 2.34.1