From 5d3bfc5d5b7f61b287391a6871f080ed63ced246 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>
Signed-off-by: Tomas Cohen Arazi <tomascohen@theke.io>
---
 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.39.5