Bugzilla – Attachment 152739 Details for
Bug 34064
Compare kohastructure.sql against current database using database audit script
Home
|
New
|
Browse
|
Search
|
[?]
|
Reports
|
Help
|
New Account
|
Log In
[x]
|
Forgot Password
Login:
[x]
[patch]
Bug 34064: Add an audit script to compare database with kohastructure.sql
Bug-34064-Add-an-audit-script-to-compare-database-.patch (text/plain), 4.41 KB, created by
Martin Renvoize (ashimema)
on 2023-06-27 13:31:23 UTC
(
hide
)
Description:
Bug 34064: Add an audit script to compare database with kohastructure.sql
Filename:
MIME Type:
Creator:
Martin Renvoize (ashimema)
Created:
2023-06-27 13:31:23 UTC
Size:
4.41 KB
patch
obsolete
>From 1768852874599cef39aa5dbb1ef13eec2b603687 Mon Sep 17 00:00:00 2001 >From: David Cook <dcook@prosentient.com.au> >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 > >5a. Try using koha-foreach and note that the database name appears above >the database comparison >5b. koha-foreach "perl misc/maintenance/audit_database.pl \ > --filename /kohadevbox/koha/installer/data/mysql/kohastructure.sql" > >Signed-off-by: Emmi Takkinen <emmi.takkinen@koha-suomi.fi> >Signed-off-by: Owen Leonard <oleonard@myacpl.org> >Signed-off-by: Martin Renvoize <martin.renvoize@ptfs-europe.com> >--- > misc/maintenance/audit_database.pl | 81 ++++++++++++++++++++++++++++++ > 1 file changed, 81 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..d84d9af93f >--- /dev/null >+++ b/misc/maintenance/audit_database.pl >@@ -0,0 +1,81 @@ >+#!/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 $database_name = C4::Context->config("database"); >+ print "Parsing schema for database '$database_name'\n"; >+ 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 unnecessarily >+ 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}; >+ print "Parsing schema for file '$filename'\n"; >+ my $translator = SQL::Translator->new(); >+ $translator->parser("MySQL"); >+ my $schema = $translator->translate($filename); >+ return $schema; >+} >-- >2.41.0
You cannot view the attachment while viewing its details because your browser does not support IFRAMEs.
View the attachment on a separate page
.
View Attachment As Diff
View Attachment As Raw
Actions:
View
|
Diff
|
Splinter Review
Attachments on
bug 34064
:
152479
|
152480
|
152481
|
152482
|
152575
|
152576
| 152739 |
152740
|
153022
|
153023