View | Details | Raw Unified | Return to bug 32334
Collapse All | Expand All

(-)a/Koha/Database/Commenter.pm (+279 lines)
Line 0 Link Here
1
package Koha::Database::Commenter;
2
3
# Copyright 2022 Rijksmuseum, Koha development team
4
#
5
# This file is part of Koha.
6
#
7
# Koha is free software; you can redistribute it and/or modify it
8
# under the terms of the GNU General Public License as published by
9
# the Free Software Foundation; either version 3 of the License, or
10
# (at your option) any later version.
11
#
12
# Koha is distributed in the hope that it will be useful, but
13
# WITHOUT ANY WARRANTY; without even the implied warranty of
14
# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
15
# GNU General Public License for more details.
16
#
17
# You should have received a copy of the GNU General Public License
18
# along with Koha; if not, see <http://www.gnu.org/licenses>.
19
20
use Modern::Perl;
21
use Data::Dumper qw(Dumper);
22
use File::Slurp qw(read_file);
23
24
use Koha::Exceptions;
25
26
use constant KOHA_STRUCTURE => 'installer/data/mysql/kohastructure.sql';
27
use constant DBI_HANDLE_CLASS => 'DBI::db';
28
29
=head1 NAME
30
31
Koha::Database::Commenter - Manage column comments in database
32
33
=head1 SYNOPSIS
34
35
    use Koha::Database::Commenter;
36
    $mgr = Koha::Database::Commenter->new({ dbh => $dbh });
37
38
    $mgr->reset_to_schema;
39
    # OR:
40
    $mgr->clear;
41
42
=head1 DESCRIPTION
43
44
    This object helps you to keep column comments in your database in sync
45
    with the Koha schema. It also allows you to clear all comments.
46
47
    The advantage of keeping in sync is that you can easily track differences
48
    between schema and database with the maintenance script
49
    update_dbix_class_files.pl.
50
51
    Tip: make a backup of your database before running this script.
52
53
=head1 METHODS
54
55
=head2 new
56
57
    $mgr = Koha::Database::Commenter->new({
58
        dbh => $dbh, database => $d, fh => $fh, schema_file => $s
59
    });
60
61
    Object constructor.
62
    Param dbh is mandatory. Params database, fh and schema_file are
63
    optional.
64
    Param database can be used to move away from current database of
65
    db handle.
66
    Param fh can be used to redirect output.
67
    Param schema_file is needed for resetting to schema. Falls back to
68
    the constant for Koha structure file.
69
70
=cut
71
72
sub new {
73
    my ( $class, $params ) = @_; # params: database, dbh, fh, schema_file
74
    my $self = bless $params // {}, $class;
75
76
    Koha::Exceptions::MissingParameter->throw( parameter => 'dbh' ) unless $self->{dbh};
77
    Koha::Exceptions::WrongParameter->throw( name => 'dbh', type => ref($self->{dbh}) )
78
        unless ref($self->{dbh}) eq DBI_HANDLE_CLASS;
79
80
    $self->{database} //= ( $self->{dbh}->selectrow_array('SELECT DATABASE()') )[0];
81
    $self->{fh} //= *STDOUT;
82
    $self->{schema_file} //= KOHA_STRUCTURE;
83
    $self->{schema_info} = {};
84
85
    return $self;
86
}
87
88
=head2 clear
89
90
    $object->clear({ dry_run => 0, table => $table, verbose => 0 });
91
92
    Clears all current column comments in storage.
93
    If table is passed, only that table is changed.
94
    Dry run only prints sql statements.
95
96
=cut
97
98
sub clear {
99
    my ( $self, $params ) = @_; # dry_run, table, verbose
100
    my $cols = $self->_fetch_stored_comments($params);
101
    foreach my $col ( @$cols ) {
102
        next if !$col->{column_comment};
103
        next if $params->{table} && $col->{table_name} ne $params->{table};
104
        $self->_change_column( $col->{table_name}, $col->{column_name}, undef, $params ); # undef clears
105
    }
106
}
107
108
=head2 reset_to_schema
109
110
    $object->reset_to_schema({ dry_run => 0, table => $table, verbose => 0 });
111
112
    Resets column comments in storage to schema definition.
113
    Other column comments are cleared.
114
    When you pass table, only that table is changed.
115
    Dry run only prints sql statements.
116
117
=cut
118
119
sub reset_to_schema {
120
    my ( $self, $params ) = @_; # dry_run, table, verbose
121
    $self->clear($params);
122
    my $schema_comments = $self->_fetch_schema_comments;
123
    foreach my $table ( sort keys %$schema_comments ) {
124
        next if $params->{table} && $table ne $params->{table};
125
        foreach my $col ( sort keys %{$schema_comments->{$table}} ) {
126
            $self->_change_column( $table, $col, $schema_comments->{$table}->{$col}, $params );
127
        }
128
    }
129
}
130
131
=head2 renumber
132
133
    $object->renumber({ dry_run => 0, table => $table, verbose => 0 });
134
135
    This is primarily meant for testing purposes (verifying results across
136
    whole database).
137
    It adds comments like Comment_1, Comment_2 etc.
138
    When you pass table, only that table is changed. Otherwise all tables
139
    are affected; note that the column counter does not reset by table.
140
    Dry run only prints sql statements.
141
142
=cut
143
144
sub renumber {
145
    my ( $self, $params ) = @_; # dry_run, table, verbose
146
    my $cols = $self->_fetch_stored_comments($params);
147
    my $i = 0;
148
    foreach my $col ( @$cols ) {
149
        next if $params->{table} && $col->{table_name} ne $params->{table};
150
        $i++;
151
        $self->_change_column( $col->{table_name}, $col->{column_name}, "Column_$i", $params );
152
    }
153
}
154
155
=head1 INTERNAL ROUTINES
156
157
=cut
158
159
=head2 _fetch_schema_comments
160
161
=cut
162
163
sub _fetch_schema_comments {
164
# Wish we had a DBIC function for this, showing comments too ;) Now using kohastructure as source of truth.
165
    my ( $self ) = @_;
166
    my $file = $self->{schema_file};
167
    Koha::Exceptions::FileNotFound->throw( filename => $file ) unless -e $file;
168
169
    return $self->{schema_info} if keys %{$self->{schema_info}};
170
171
    my @schema_lines = read_file( $file );
172
    my $info = {};
173
    my $current_table = q{};
174
    foreach my $line ( @schema_lines ) {
175
        if( $line =~ /^CREATE TABLE `?(\w+)`?/ ) {
176
            $current_table = $1;
177
        } elsif( $line =~ /^\s+`?(\w+)`?.*COMMENT ['"](.+)['"][,)]?$/ ) {
178
            my ( $col, $comment ) = ( $1, $2 );
179
            $comment =~ s/''/'/g; # we call quote later on
180
            $info->{$current_table}->{$col} = $comment;
181
        }
182
    }
183
    return $self->{schema_info} = $info;
184
}
185
186
=head2 _fetch_stored_comments
187
188
=cut
189
190
sub _fetch_stored_comments {
191
    my ( $self, $params ) = @_; # params: table
192
    my $sql = q|
193
SELECT table_name, column_name, column_comment FROM information_schema.columns
194
WHERE table_schema=? AND table_name=?
195
ORDER BY table_name, column_name|;
196
    $sql =~ s/AND table_name=\?// unless $params->{table};
197
    return $self->{dbh}->selectall_arrayref( $sql, { Slice => {} }, $self->{database}, $params->{table} || () );
198
}
199
200
=head2 _change_column
201
202
=cut
203
204
sub _change_column {
205
# NOTE: We do not want to use DBIx schema here, but we use stored structure,
206
# since we only want to change comment not actual table structure.
207
    my ( $self, $table_name, $column_name, $comment, $params ) = @_; # params: dry_run, verbose
208
    $params //= {};
209
210
    my $dbh = $self->{dbh};
211
    my $info = $self->_columns_info( $table_name )->{$column_name};
212
213
    # datatype; nullable, collation
214
    my $rv = qq|ALTER TABLE $self->{database}.$table_name MODIFY COLUMN `$column_name` $info->{Type} |;
215
    $rv .= 'NOT NULL ' if $info->{Null} eq 'NO';
216
    $rv .= "COLLATE $info->{Collation} " if $info->{Collation};
217
218
    # Default - needs a bit of tweaking
219
    if( !defined $info->{Default} && $info->{Null} eq 'NO' ) {
220
        # Do not provide a default
221
    } elsif( $info->{Type} =~ /char|text|enum/i ) {
222
        if( !defined $info->{Default} ) {
223
            $rv .= "DEFAULT NULL ";
224
        } else {      #includes: $info->{Default} eq '' || $info->{Default} eq '0'
225
            $rv .= "DEFAULT ". $dbh->quote($info->{Default}). " ";
226
        }
227
    } elsif( !$info->{Default} && $info->{Type} =~ /timestamp/ ) { # Peculiar correction for nullable timestamps
228
        $rv .= 'NULL DEFAULT NULL ' if $info->{Null} eq 'YES';
229
    } else {
230
        $rv .= "DEFAULT ". ( $info->{Default} // 'NULL' ). " ";
231
    }
232
233
    # Extra (like autoincrement)
234
    $rv .= $info->{Extra}. ' ' if $info->{Extra};
235
236
    # Comment if passed; not passing means clearing actually.
237
    if( $comment ) {
238
        $comment = $dbh->quote($comment) unless $comment =~ /\\'/; # Prevent quoting twice
239
        $rv .= "COMMENT ". $comment;
240
    }
241
    $rv =~ s/\s+$//; # remove trailing spaces
242
243
    # Print
244
    if( $params->{dry_run} ) {
245
        print { $self->{fh} } "$rv;\n";
246
        return;
247
    }
248
    # Deploy
249
    eval { $dbh->do($rv) };
250
    if( $@ ) {
251
        warn "Failure for $table_name:$column_name";
252
        print { $self->{fh} } "-- FAILED: $rv;\n";
253
    } elsif( $params->{verbose} ) {
254
        print { $self->{fh} } "$rv;\n";
255
    }
256
}
257
258
sub _columns_info {
259
    my ( $self, $table ) = @_;
260
    return $self->{dbh}->selectall_hashref( 'SHOW FULL COLUMNS FROM '. $self->{database}. '.'. $table, 'Field' );
261
}
262
263
1;
264
__END__
265
266
=head1 ADDITIONAL COMMENTS
267
268
    The module contains the core code for the options of the maintenance
269
    script sync_db_comments.pl.
270
271
    It can be tested additionally with Commenter.t, but note that since
272
    SQL DDL statements - as generated by this module - implicitly commit,
273
    we are not modifying actual Koha tables in that test.
274
275
=head1 AUTHOR
276
277
    Marcel de Rooy, Rijksmuseum Amsterdam, The Netherlands
278
279
=cut
(-)a/t/db_dependent/Koha/Database/Commenter.t (-1 / +114 lines)
Line 0 Link Here
0
- 
1
#!/usr/bin/perl
2
3
use Modern::Perl;
4
use Data::Dumper qw/Dumper/;
5
use File::Temp qw();
6
#use Test::MockModule;
7
use Test::More tests => 2;
8
use Test::Exception;
9
10
#use t::lib::Mocks;
11
12
use C4::Context;
13
use Koha::Database;
14
use Koha::Database::Commenter;
15
16
our $schema = Koha::Database->new->schema;
17
our $dbh = C4::Context->dbh;
18
19
our $mgr;
20
21
subtest '->new, dry_run' => sub {
22
    plan tests => 6;
23
    $schema->storage->txn_begin; # just cosmetic, no changes expected in a dry run
24
25
    # Two exceptions on dbh parameter
26
    throws_ok { Koha::Database::Commenter->new } 'Koha::Exceptions::MissingParameter', 'No dbh';
27
    throws_ok { Koha::Database::Commenter->new({ dbh => 'dbh' }) } 'Koha::Exceptions::WrongParameter', 'dbh no string';
28
29
    # Another exception: delete schema file and reset should raise exception
30
    my $filename = create_schema_file();
31
    my $stdout;
32
    open my $fh, '>', \$stdout;
33
    $mgr = Koha::Database::Commenter->new({ dbh => $dbh, schema_file => $filename, fh => $fh });
34
    unlink $filename;
35
    throws_ok { $mgr->reset_to_schema({ dry_run => 1, table => 'biblio' }) } 'Koha::Exceptions::FileNotFound', 'Schema deleted';
36
37
    # Clear comments for article_requests in dry run mode
38
    $stdout = q{};
39
    $mgr->clear({ table => 'article_requests', dry_run => 1 });
40
    like( $stdout, qr/COLUMN `toc_request`.*DEFAULT 0;$/m, 'No comment for toc_request' );
41
42
    # Renumber this field in dry run mode
43
    $stdout = q{};
44
    $mgr->renumber({ table => 'article_requests', dry_run => 1 });
45
    like( $stdout, qr/COLUMN `toc_request`.*COMMENT 'Column_\d+';$/m, 'Numbered comment for toc_request' );
46
47
    # Reset in dry run mode, first fix schema file again
48
    # Our fake schema contains only one column for article_requests now.
49
    $filename = create_schema_file();
50
    $mgr = Koha::Database::Commenter->new({ dbh => $dbh, schema_file => $filename, fh => $fh });
51
    $stdout = q{};
52
    $mgr->reset_to_schema({ table => 'article_requests', dry_run => 1 });
53
    # We expect an ALTER clearing toc_request first, followed by an ALTER adding comment.
54
    # Note: This is based on the fair assumption that toc_request had a comment! This test could fail if it had not.
55
    like( $stdout, qr/ALTER.*toc_request.*DEFAULT 0;(.*\n)+ALTER.*toc_request.*COMMENT.*$/m, 'Reset for one-columned article_requests' );
56
57
    $schema->storage->txn_rollback;
58
};
59
60
subtest '->clear, ->reset, ->renumber' => sub {
61
    plan tests => 6;
62
    #$schema->storage->txn_begin; # commented: DDL statements implicitly commit; we are testing only 1 new table here btw
63
64
    create_test_table1($dbh);
65
    $mgr->clear({ table => 'database_commenter_1' });
66
    my $info = $mgr->_columns_info( 'database_commenter_1' );
67
    is( $info->{sometext}->{Comment}, q{}, 'Found no comment for sometext' );
68
    is( $info->{anotherint}->{Comment}, q{}, 'Found no comment for anotherint' );
69
70
    # Created temporary file serves as schema/kohastructure.sql now
71
    $mgr->reset_to_schema({ table => 'database_commenter_1' });
72
    $info = $mgr->_columns_info( 'database_commenter_1' );
73
    is( $info->{sometext}->{Comment}, 'some nice quote\'s', 'Found comment for sometext' );
74
    is( $info->{anotherint}->{Comment}, 'my int', 'Found comment for anotherint' );
75
76
    # Renumber follows alphabetical order
77
    $mgr->renumber({ table => 'database_commenter_1' });
78
    $info = $mgr->_columns_info( 'database_commenter_1' );
79
    is( $info->{anotherint}->{Comment}, 'Column_1', 'Found Column_1' );
80
    is( $info->{timestamp2}->{Comment}, 'Column_5', 'Found Column_5' );
81
82
    eval { $dbh->do('DROP TABLE database_commenter_1') };
83
    #$schema->storage->txn_rollback; # commented: DDL statements implicitly commit
84
};
85
86
sub create_schema_file {
87
    my ( $fh, $filename ) = File::Temp::tempfile();
88
    print $fh schema_table1();
89
    print $fh "\n";
90
    print $fh schema_table2(); # fragment of Koha table
91
    close $fh;
92
    return $filename;
93
}
94
95
sub schema_table1 {
96
    return q|CREATE TABLE database_commenter_1 (
97
    id int AUTO_INCREMENT,
98
    sometext varchar(80) NOT NULL DEFAULT '' COMMENT 'some nice quote''s',
99
    anotherint int NULL COMMENT 'my int',
100
    date1 DATE NOT NULL,
101
    timestamp2 timestamp NOT NULL DEFAULT current_timestamp(),
102
    PRIMARY KEY (id) );|;
103
}
104
105
sub schema_table2 { # only a fragment
106
    return q|CREATE TABLE article_requests (
107
    `toc_request` tinyint(4) NOT NULL DEFAULT 0 COMMENT 'borrower requested table of contents')|;
108
}
109
110
sub create_test_table1 {
111
    my ( $dbh ) = @_;
112
    eval { $dbh->do('DROP TABLE database_commenter_1') };
113
    $dbh->do( schema_table1() );
114
}

Return to bug 32334