Bugzilla – Attachment 149261 Details for
Bug 32334
Sync comments in database with schema
Home
|
New
|
Browse
|
Search
|
[?]
|
Reports
|
Help
|
New Account
|
Log In
[x]
|
Forgot Password
Login:
[x]
[patch]
Bug 32334: Introduce Koha::Database::Commenter
Bug-32334-Introduce-KohaDatabaseCommenter.patch (text/plain), 14.77 KB, created by
Jonathan Druart
on 2023-04-07 11:49:04 UTC
(
hide
)
Description:
Bug 32334: Introduce Koha::Database::Commenter
Filename:
MIME Type:
Creator:
Jonathan Druart
Created:
2023-04-07 11:49:04 UTC
Size:
14.77 KB
patch
obsolete
>From 4e6734c046c37e3ea81d0148d50067c2d1d88d2a Mon Sep 17 00:00:00 2001 >From: Marcel de Rooy <m.de.rooy@rijksmuseum.nl> >Date: Wed, 23 Nov 2022 13:19:35 +0100 >Subject: [PATCH] Bug 32334: Introduce Koha::Database::Commenter > >Test plan: >[1] Make a backup of your database. Just for precaution. >[2] Run t/db_dependent/Koha/Database/Commenter.t. >* Note: This test script adds and removes a test table. > >Signed-off-by: Marcel de Rooy <m.de.rooy@rijksmuseum.nl> >Signed-off-by: David Nind <david@davidnind.com> > >Signed-off-by: Jonathan Druart <jonathan.druart@bugs.koha-community.org> >--- > Koha/Database/Commenter.pm | 279 +++++++++++++++++++++++ > t/db_dependent/Koha/Database/Commenter.t | 114 +++++++++ > 2 files changed, 393 insertions(+) > create mode 100644 Koha/Database/Commenter.pm > create mode 100755 t/db_dependent/Koha/Database/Commenter.t > >diff --git a/Koha/Database/Commenter.pm b/Koha/Database/Commenter.pm >new file mode 100644 >index 00000000000..c70b0add940 >--- /dev/null >+++ b/Koha/Database/Commenter.pm >@@ -0,0 +1,279 @@ >+package Koha::Database::Commenter; >+ >+# Copyright 2022 Rijksmuseum, Koha development team >+# >+# This file is part of Koha. >+# >+# Koha is free software; you can redistribute it and/or modify it >+# under the terms of the GNU General Public License as published by >+# the Free Software Foundation; either version 3 of the License, or >+# (at your option) any later version. >+# >+# Koha is distributed in the hope that it will be useful, but >+# WITHOUT ANY WARRANTY; without even the implied warranty of >+# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the >+# GNU General Public License for more details. >+# >+# You should have received a copy of the GNU General Public License >+# along with Koha; if not, see <http://www.gnu.org/licenses>. >+ >+use Modern::Perl; >+use Data::Dumper qw(Dumper); >+use File::Slurp qw(read_file); >+ >+use Koha::Exceptions; >+ >+use constant KOHA_STRUCTURE => 'installer/data/mysql/kohastructure.sql'; >+use constant DBI_HANDLE_CLASS => 'DBI::db'; >+ >+=head1 NAME >+ >+Koha::Database::Commenter - Manage column comments in database >+ >+=head1 SYNOPSIS >+ >+ use Koha::Database::Commenter; >+ $mgr = Koha::Database::Commenter->new({ dbh => $dbh }); >+ >+ $mgr->reset_to_schema; >+ # OR: >+ $mgr->clear; >+ >+=head1 DESCRIPTION >+ >+ This object helps you to keep column comments in your database in sync >+ with the Koha schema. It also allows you to clear all comments. >+ >+ The advantage of keeping in sync is that you can easily track differences >+ between schema and database with the maintenance script >+ update_dbix_class_files.pl. >+ >+ Tip: make a backup of your database before running this script. >+ >+=head1 METHODS >+ >+=head2 new >+ >+ $mgr = Koha::Database::Commenter->new({ >+ dbh => $dbh, database => $d, fh => $fh, schema_file => $s >+ }); >+ >+ Object constructor. >+ Param dbh is mandatory. Params database, fh and schema_file are >+ optional. >+ Param database can be used to move away from current database of >+ db handle. >+ Param fh can be used to redirect output. >+ Param schema_file is needed for resetting to schema. Falls back to >+ the constant for Koha structure file. >+ >+=cut >+ >+sub new { >+ my ( $class, $params ) = @_; # params: database, dbh, fh, schema_file >+ my $self = bless $params // {}, $class; >+ >+ Koha::Exceptions::MissingParameter->throw( parameter => 'dbh' ) unless $self->{dbh}; >+ Koha::Exceptions::WrongParameter->throw( name => 'dbh', type => ref($self->{dbh}) ) >+ unless ref($self->{dbh}) eq DBI_HANDLE_CLASS; >+ >+ $self->{database} //= ( $self->{dbh}->selectrow_array('SELECT DATABASE()') )[0]; >+ $self->{fh} //= *STDOUT; >+ $self->{schema_file} //= KOHA_STRUCTURE; >+ $self->{schema_info} = {}; >+ >+ return $self; >+} >+ >+=head2 clear >+ >+ $object->clear({ dry_run => 0, table => $table, verbose => 0 }); >+ >+ Clears all current column comments in storage. >+ If table is passed, only that table is changed. >+ Dry run only prints sql statements. >+ >+=cut >+ >+sub clear { >+ my ( $self, $params ) = @_; # dry_run, table, verbose >+ my $cols = $self->_fetch_stored_comments($params); >+ foreach my $col ( @$cols ) { >+ next if !$col->{column_comment}; >+ next if $params->{table} && $col->{table_name} ne $params->{table}; >+ $self->_change_column( $col->{table_name}, $col->{column_name}, undef, $params ); # undef clears >+ } >+} >+ >+=head2 reset_to_schema >+ >+ $object->reset_to_schema({ dry_run => 0, table => $table, verbose => 0 }); >+ >+ Resets column comments in storage to schema definition. >+ Other column comments are cleared. >+ When you pass table, only that table is changed. >+ Dry run only prints sql statements. >+ >+=cut >+ >+sub reset_to_schema { >+ my ( $self, $params ) = @_; # dry_run, table, verbose >+ $self->clear($params); >+ my $schema_comments = $self->_fetch_schema_comments; >+ foreach my $table ( sort keys %$schema_comments ) { >+ next if $params->{table} && $table ne $params->{table}; >+ foreach my $col ( sort keys %{$schema_comments->{$table}} ) { >+ $self->_change_column( $table, $col, $schema_comments->{$table}->{$col}, $params ); >+ } >+ } >+} >+ >+=head2 renumber >+ >+ $object->renumber({ dry_run => 0, table => $table, verbose => 0 }); >+ >+ This is primarily meant for testing purposes (verifying results across >+ whole database). >+ It adds comments like Comment_1, Comment_2 etc. >+ When you pass table, only that table is changed. Otherwise all tables >+ are affected; note that the column counter does not reset by table. >+ Dry run only prints sql statements. >+ >+=cut >+ >+sub renumber { >+ my ( $self, $params ) = @_; # dry_run, table, verbose >+ my $cols = $self->_fetch_stored_comments($params); >+ my $i = 0; >+ foreach my $col ( @$cols ) { >+ next if $params->{table} && $col->{table_name} ne $params->{table}; >+ $i++; >+ $self->_change_column( $col->{table_name}, $col->{column_name}, "Column_$i", $params ); >+ } >+} >+ >+=head1 INTERNAL ROUTINES >+ >+=cut >+ >+=head2 _fetch_schema_comments >+ >+=cut >+ >+sub _fetch_schema_comments { >+# Wish we had a DBIC function for this, showing comments too ;) Now using kohastructure as source of truth. >+ my ( $self ) = @_; >+ my $file = $self->{schema_file}; >+ Koha::Exceptions::FileNotFound->throw( filename => $file ) unless -e $file; >+ >+ return $self->{schema_info} if keys %{$self->{schema_info}}; >+ >+ my @schema_lines = read_file( $file ); >+ my $info = {}; >+ my $current_table = q{}; >+ foreach my $line ( @schema_lines ) { >+ if( $line =~ /^CREATE TABLE `?(\w+)`?/ ) { >+ $current_table = $1; >+ } elsif( $line =~ /^\s+`?(\w+)`?.*COMMENT ['"](.+)['"][,)]?$/ ) { >+ my ( $col, $comment ) = ( $1, $2 ); >+ $comment =~ s/''/'/g; # we call quote later on >+ $info->{$current_table}->{$col} = $comment; >+ } >+ } >+ return $self->{schema_info} = $info; >+} >+ >+=head2 _fetch_stored_comments >+ >+=cut >+ >+sub _fetch_stored_comments { >+ my ( $self, $params ) = @_; # params: table >+ my $sql = q| >+SELECT table_name, column_name, column_comment FROM information_schema.columns >+WHERE table_schema=? AND table_name=? >+ORDER BY table_name, column_name|; >+ $sql =~ s/AND table_name=\?// unless $params->{table}; >+ return $self->{dbh}->selectall_arrayref( $sql, { Slice => {} }, $self->{database}, $params->{table} || () ); >+} >+ >+=head2 _change_column >+ >+=cut >+ >+sub _change_column { >+# NOTE: We do not want to use DBIx schema here, but we use stored structure, >+# since we only want to change comment not actual table structure. >+ my ( $self, $table_name, $column_name, $comment, $params ) = @_; # params: dry_run, verbose >+ $params //= {}; >+ >+ my $dbh = $self->{dbh}; >+ my $info = $self->_columns_info( $table_name )->{$column_name}; >+ >+ # datatype; nullable, collation >+ my $rv = qq|ALTER TABLE $self->{database}.$table_name MODIFY COLUMN `$column_name` $info->{Type} |; >+ $rv .= 'NOT NULL ' if $info->{Null} eq 'NO'; >+ $rv .= "COLLATE $info->{Collation} " if $info->{Collation}; >+ >+ # Default - needs a bit of tweaking >+ if( !defined $info->{Default} && $info->{Null} eq 'NO' ) { >+ # Do not provide a default >+ } elsif( $info->{Type} =~ /char|text|enum/i ) { >+ if( !defined $info->{Default} ) { >+ $rv .= "DEFAULT NULL "; >+ } else { #includes: $info->{Default} eq '' || $info->{Default} eq '0' >+ $rv .= "DEFAULT ". $dbh->quote($info->{Default}). " "; >+ } >+ } elsif( !$info->{Default} && $info->{Type} =~ /timestamp/ ) { # Peculiar correction for nullable timestamps >+ $rv .= 'NULL DEFAULT NULL ' if $info->{Null} eq 'YES'; >+ } else { >+ $rv .= "DEFAULT ". ( $info->{Default} // 'NULL' ). " "; >+ } >+ >+ # Extra (like autoincrement) >+ $rv .= $info->{Extra}. ' ' if $info->{Extra}; >+ >+ # Comment if passed; not passing means clearing actually. >+ if( $comment ) { >+ $comment = $dbh->quote($comment) unless $comment =~ /\\'/; # Prevent quoting twice >+ $rv .= "COMMENT ". $comment; >+ } >+ $rv =~ s/\s+$//; # remove trailing spaces >+ >+ # Print >+ if( $params->{dry_run} ) { >+ print { $self->{fh} } "$rv;\n"; >+ return; >+ } >+ # Deploy >+ eval { $dbh->do($rv) }; >+ if( $@ ) { >+ warn "Failure for $table_name:$column_name"; >+ print { $self->{fh} } "-- FAILED: $rv;\n"; >+ } elsif( $params->{verbose} ) { >+ print { $self->{fh} } "$rv;\n"; >+ } >+} >+ >+sub _columns_info { >+ my ( $self, $table ) = @_; >+ return $self->{dbh}->selectall_hashref( 'SHOW FULL COLUMNS FROM '. $self->{database}. '.'. $table, 'Field' ); >+} >+ >+1; >+__END__ >+ >+=head1 ADDITIONAL COMMENTS >+ >+ The module contains the core code for the options of the maintenance >+ script sync_db_comments.pl. >+ >+ It can be tested additionally with Commenter.t, but note that since >+ SQL DDL statements - as generated by this module - implicitly commit, >+ we are not modifying actual Koha tables in that test. >+ >+=head1 AUTHOR >+ >+ Marcel de Rooy, Rijksmuseum Amsterdam, The Netherlands >+ >+=cut >diff --git a/t/db_dependent/Koha/Database/Commenter.t b/t/db_dependent/Koha/Database/Commenter.t >new file mode 100755 >index 00000000000..09ad771c513 >--- /dev/null >+++ b/t/db_dependent/Koha/Database/Commenter.t >@@ -0,0 +1,114 @@ >+#!/usr/bin/perl >+ >+use Modern::Perl; >+use Data::Dumper qw/Dumper/; >+use File::Temp qw(); >+#use Test::MockModule; >+use Test::More tests => 2; >+use Test::Exception; >+ >+#use t::lib::Mocks; >+ >+use C4::Context; >+use Koha::Database; >+use Koha::Database::Commenter; >+ >+our $schema = Koha::Database->new->schema; >+our $dbh = C4::Context->dbh; >+ >+our $mgr; >+ >+subtest '->new, dry_run' => sub { >+ plan tests => 6; >+ $schema->storage->txn_begin; # just cosmetic, no changes expected in a dry run >+ >+ # Two exceptions on dbh parameter >+ throws_ok { Koha::Database::Commenter->new } 'Koha::Exceptions::MissingParameter', 'No dbh'; >+ throws_ok { Koha::Database::Commenter->new({ dbh => 'dbh' }) } 'Koha::Exceptions::WrongParameter', 'dbh no string'; >+ >+ # Another exception: delete schema file and reset should raise exception >+ my $filename = create_schema_file(); >+ my $stdout; >+ open my $fh, '>', \$stdout; >+ $mgr = Koha::Database::Commenter->new({ dbh => $dbh, schema_file => $filename, fh => $fh }); >+ unlink $filename; >+ throws_ok { $mgr->reset_to_schema({ dry_run => 1, table => 'biblio' }) } 'Koha::Exceptions::FileNotFound', 'Schema deleted'; >+ >+ # Clear comments for article_requests in dry run mode >+ $stdout = q{}; >+ $mgr->clear({ table => 'article_requests', dry_run => 1 }); >+ like( $stdout, qr/COLUMN `toc_request`.*DEFAULT 0;$/m, 'No comment for toc_request' ); >+ >+ # Renumber this field in dry run mode >+ $stdout = q{}; >+ $mgr->renumber({ table => 'article_requests', dry_run => 1 }); >+ like( $stdout, qr/COLUMN `toc_request`.*COMMENT 'Column_\d+';$/m, 'Numbered comment for toc_request' ); >+ >+ # Reset in dry run mode, first fix schema file again >+ # Our fake schema contains only one column for article_requests now. >+ $filename = create_schema_file(); >+ $mgr = Koha::Database::Commenter->new({ dbh => $dbh, schema_file => $filename, fh => $fh }); >+ $stdout = q{}; >+ $mgr->reset_to_schema({ table => 'article_requests', dry_run => 1 }); >+ # We expect an ALTER clearing toc_request first, followed by an ALTER adding comment. >+ # Note: This is based on the fair assumption that toc_request had a comment! This test could fail if it had not. >+ like( $stdout, qr/ALTER.*toc_request.*DEFAULT 0;(.*\n)+ALTER.*toc_request.*COMMENT.*$/m, 'Reset for one-columned article_requests' ); >+ >+ $schema->storage->txn_rollback; >+}; >+ >+subtest '->clear, ->reset, ->renumber' => sub { >+ plan tests => 6; >+ #$schema->storage->txn_begin; # commented: DDL statements implicitly commit; we are testing only 1 new table here btw >+ >+ create_test_table1($dbh); >+ $mgr->clear({ table => 'database_commenter_1' }); >+ my $info = $mgr->_columns_info( 'database_commenter_1' ); >+ is( $info->{sometext}->{Comment}, q{}, 'Found no comment for sometext' ); >+ is( $info->{anotherint}->{Comment}, q{}, 'Found no comment for anotherint' ); >+ >+ # Created temporary file serves as schema/kohastructure.sql now >+ $mgr->reset_to_schema({ table => 'database_commenter_1' }); >+ $info = $mgr->_columns_info( 'database_commenter_1' ); >+ is( $info->{sometext}->{Comment}, 'some nice quote\'s', 'Found comment for sometext' ); >+ is( $info->{anotherint}->{Comment}, 'my int', 'Found comment for anotherint' ); >+ >+ # Renumber follows alphabetical order >+ $mgr->renumber({ table => 'database_commenter_1' }); >+ $info = $mgr->_columns_info( 'database_commenter_1' ); >+ is( $info->{anotherint}->{Comment}, 'Column_1', 'Found Column_1' ); >+ is( $info->{timestamp2}->{Comment}, 'Column_5', 'Found Column_5' ); >+ >+ eval { $dbh->do('DROP TABLE database_commenter_1') }; >+ #$schema->storage->txn_rollback; # commented: DDL statements implicitly commit >+}; >+ >+sub create_schema_file { >+ my ( $fh, $filename ) = File::Temp::tempfile(); >+ print $fh schema_table1(); >+ print $fh "\n"; >+ print $fh schema_table2(); # fragment of Koha table >+ close $fh; >+ return $filename; >+} >+ >+sub schema_table1 { >+ return q|CREATE TABLE database_commenter_1 ( >+ id int AUTO_INCREMENT, >+ sometext varchar(80) NOT NULL DEFAULT '' COMMENT 'some nice quote''s', >+ anotherint int NULL COMMENT 'my int', >+ date1 DATE NOT NULL, >+ timestamp2 timestamp NOT NULL DEFAULT current_timestamp(), >+ PRIMARY KEY (id) );|; >+} >+ >+sub schema_table2 { # only a fragment >+ return q|CREATE TABLE article_requests ( >+ `toc_request` tinyint(4) NOT NULL DEFAULT 0 COMMENT 'borrower requested table of contents')|; >+} >+ >+sub create_test_table1 { >+ my ( $dbh ) = @_; >+ eval { $dbh->do('DROP TABLE database_commenter_1') }; >+ $dbh->do( schema_table1() ); >+} >-- >2.25.1
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 32334
:
144391
|
144392
|
144393
|
144394
|
146712
|
146713
|
146714
|
146715
|
146716
|
146717
|
146718
|
146719
|
146720
|
148266
|
148299
|
148335
|
148338
|
148339
|
148340
|
148341
|
148342
|
148343
|
148344
|
148345
|
148346
|
148347
|
148348
|
149259
|
149260
| 149261 |
149262
|
149263
|
149264
|
149265
|
149266
|
149267
|
149268
|
149269
|
149270