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

(-)a/Koha/Database/Commenter.pm (-24 / +23 lines)
Lines 54-75 Koha::Database::Commenter - Manage column comments in database Link Here
54
=head2 new
54
=head2 new
55
55
56
    $mgr = Koha::Database::Commenter->new({
56
    $mgr = Koha::Database::Commenter->new({
57
        dbh => $dbh, database => $d, fh => $fh, schema_file => $s
57
        dbh => $dbh, database => $d, schema_file => $s
58
    });
58
    });
59
59
60
    Object constructor.
60
    Object constructor.
61
    Param dbh is mandatory. Params database, fh and schema_file are
61
    Param dbh is mandatory. Params database and schema_file are
62
    optional.
62
    optional.
63
    Param database can be used to move away from current database of
63
    Param database can be used to move away from current database of
64
    db handle.
64
    db handle.
65
    Param fh can be used to redirect output.
66
    Param schema_file is needed for resetting to schema. Falls back to
65
    Param schema_file is needed for resetting to schema. Falls back to
67
    the constant for Koha structure file.
66
    the constant for Koha structure file.
68
67
69
=cut
68
=cut
70
69
71
sub new {
70
sub new {
72
    my ( $class, $params ) = @_; # params: database, dbh, fh, schema_file
71
    my ( $class, $params ) = @_; # params: database, dbh, schema_file
73
    my $self = bless $params // {}, $class;
72
    my $self = bless $params // {}, $class;
74
73
75
    Koha::Exceptions::MissingParameter->throw( parameter => 'dbh' ) unless $self->{dbh};
74
    Koha::Exceptions::MissingParameter->throw( parameter => 'dbh' ) unless $self->{dbh};
Lines 77-83 sub new { Link Here
77
        unless ref($self->{dbh}) eq DBI_HANDLE_CLASS;
76
        unless ref($self->{dbh}) eq DBI_HANDLE_CLASS;
78
77
79
    $self->{database} //= ( $self->{dbh}->selectrow_array('SELECT DATABASE()') )[0];
78
    $self->{database} //= ( $self->{dbh}->selectrow_array('SELECT DATABASE()') )[0];
80
    $self->{fh} //= *STDOUT;
81
    $self->{schema_file} //= KOHA_STRUCTURE;
79
    $self->{schema_file} //= KOHA_STRUCTURE;
82
    $self->{schema_info} = {};
80
    $self->{schema_info} = {};
83
81
Lines 86-153 sub new { Link Here
86
84
87
=head2 clear
85
=head2 clear
88
86
89
    $object->clear({ dry_run => 0, table => $table, verbose => 0 });
87
    $object->clear({ dry_run => 0, table => $table }, $messages );
90
88
91
    Clears all current column comments in storage.
89
    Clears all current column comments in storage.
92
    If table is passed, only that table is changed.
90
    If table is passed, only that table is changed.
93
    Dry run only prints sql statements.
91
    Dry run only returns sql statements in $messages (arrayref).
94
92
95
=cut
93
=cut
96
94
97
sub clear {
95
sub clear {
98
    my ( $self, $params ) = @_; # dry_run, table, verbose
96
    my ( $self, $params, $messages ) = @_; # dry_run, table
99
    my $cols = $self->_fetch_stored_comments($params);
97
    my $cols = $self->_fetch_stored_comments($params);
100
    foreach my $col ( @$cols ) {
98
    foreach my $col ( @$cols ) {
101
        next if !$col->{column_comment};
99
        next if !$col->{column_comment};
102
        next if $params->{table} && $col->{table_name} ne $params->{table};
100
        next if $params->{table} && $col->{table_name} ne $params->{table};
103
        $self->_change_column( $col->{table_name}, $col->{column_name}, undef, $params ); # undef clears
101
        $self->_change_column( $col->{table_name}, $col->{column_name}, undef, $params, $messages ); # undef clears
104
    }
102
    }
105
}
103
}
106
104
107
=head2 reset_to_schema
105
=head2 reset_to_schema
108
106
109
    $object->reset_to_schema({ dry_run => 0, table => $table, verbose => 0 });
107
    $object->reset_to_schema({ dry_run => 0, table => $table }, $messages );
110
108
111
    Resets column comments in storage to schema definition.
109
    Resets column comments in storage to schema definition.
112
    Other column comments are cleared.
110
    Other column comments are cleared.
113
    When you pass table, only that table is changed.
111
    When you pass table, only that table is changed.
114
    Dry run only prints sql statements.
112
    Dry run only returns sql statements in $messages (arrayref).
115
113
116
=cut
114
=cut
117
115
118
sub reset_to_schema {
116
sub reset_to_schema {
119
    my ( $self, $params ) = @_; # dry_run, table, verbose
117
    my ( $self, $params, $messages ) = @_; # dry_run, table
120
    $self->clear($params);
118
    $self->clear( $params, $messages );
121
    my $schema_comments = $self->_fetch_schema_comments;
119
    my $schema_comments = $self->_fetch_schema_comments;
122
    foreach my $table ( sort keys %$schema_comments ) {
120
    foreach my $table ( sort keys %$schema_comments ) {
123
        next if $params->{table} && $table ne $params->{table};
121
        next if $params->{table} && $table ne $params->{table};
124
        foreach my $col ( sort keys %{$schema_comments->{$table}} ) {
122
        foreach my $col ( sort keys %{$schema_comments->{$table}} ) {
125
            $self->_change_column( $table, $col, $schema_comments->{$table}->{$col}, $params );
123
            $self->_change_column( $table, $col, $schema_comments->{$table}->{$col}, $params, $messages );
126
        }
124
        }
127
    }
125
    }
128
}
126
}
129
127
130
=head2 renumber
128
=head2 renumber
131
129
132
    $object->renumber({ dry_run => 0, table => $table, verbose => 0 });
130
    $object->renumber({ dry_run => 0, table => $table }, $messages );
133
131
134
    This is primarily meant for testing purposes (verifying results across
132
    This is primarily meant for testing purposes (verifying results across
135
    whole database).
133
    whole database).
136
    It adds comments like Comment_1, Comment_2 etc.
134
    It adds comments like Comment_1, Comment_2 etc.
137
    When you pass table, only that table is changed. Otherwise all tables
135
    When you pass table, only that table is changed. Otherwise all tables
138
    are affected; note that the column counter does not reset by table.
136
    are affected; note that the column counter does not reset by table.
139
    Dry run only prints sql statements.
137
    Dry run only returns sql statements in $messages (arrayref).
140
138
141
=cut
139
=cut
142
140
143
sub renumber {
141
sub renumber {
144
    my ( $self, $params ) = @_; # dry_run, table, verbose
142
    my ( $self, $params, $messages ) = @_; # dry_run, table
145
    my $cols = $self->_fetch_stored_comments($params);
143
    my $cols = $self->_fetch_stored_comments($params);
146
    my $i = 0;
144
    my $i = 0;
147
    foreach my $col ( @$cols ) {
145
    foreach my $col ( @$cols ) {
148
        next if $params->{table} && $col->{table_name} ne $params->{table};
146
        next if $params->{table} && $col->{table_name} ne $params->{table};
149
        $i++;
147
        $i++;
150
        $self->_change_column( $col->{table_name}, $col->{column_name}, "Column_$i", $params );
148
        $self->_change_column( $col->{table_name}, $col->{column_name}, "Column_$i", $params, $messages );
151
    }
149
    }
152
}
150
}
153
151
Lines 203-209 ORDER BY table_name, column_name|; Link Here
203
sub _change_column {
201
sub _change_column {
204
# NOTE: We do not want to use DBIx schema here, but we use stored structure,
202
# NOTE: We do not want to use DBIx schema here, but we use stored structure,
205
# since we only want to change comment not actual table structure.
203
# since we only want to change comment not actual table structure.
206
    my ( $self, $table_name, $column_name, $comment, $params ) = @_; # params: dry_run, verbose
204
    my ( $self, $table_name, $column_name, $comment, $params, $messages ) = @_; # params: dry_run
207
    $params //= {};
205
    $params //= {};
208
206
209
    my $dbh = $self->{dbh};
207
    my $dbh = $self->{dbh};
Lines 239-256 sub _change_column { Link Here
239
    }
237
    }
240
    $rv =~ s/\s+$//; # remove trailing spaces
238
    $rv =~ s/\s+$//; # remove trailing spaces
241
239
242
    # Print
240
    # Dry run
243
    if( $params->{dry_run} ) {
241
    if( $params->{dry_run} ) {
244
        print { $self->{fh} } "$rv;\n";
242
        push @$messages, "$rv;" if $messages;
245
        return;
243
        return;
246
    }
244
    }
245
247
    # Deploy
246
    # Deploy
248
    eval { $dbh->do($rv) };
247
    eval { $dbh->do($rv) };
249
    if( $@ ) {
248
    if( $@ ) {
250
        warn "Failure for $table_name:$column_name";
249
        warn "Failure for $table_name:$column_name";
251
        print { $self->{fh} } "-- FAILED: $rv;\n";
250
        push @$messages, "-- FAILED: $rv;" if $messages;
252
    } elsif( $params->{verbose} ) {
251
    } else {
253
        print { $self->{fh} } "$rv;\n";
252
        push @$messages, "$rv;" if $messages;
254
    }
253
    }
255
}
254
}
256
255
(-)a/misc/maintenance/sync_db_comments.pl (-5 / +7 lines)
Lines 26-31 use Pod::Usage qw( pod2usage ); Link Here
26
use C4::Context;
26
use C4::Context;
27
use Koha::Database::Commenter;
27
use Koha::Database::Commenter;
28
28
29
sub alert_dry_run { print "-- DRY RUN\n" if $_[0]->{dry_run}; }
30
29
my $cmd_args = {};
31
my $cmd_args = {};
30
GetOptions(
32
GetOptions(
31
  'clear'      => \$cmd_args->{clear},
33
  'clear'      => \$cmd_args->{clear},
Lines 40-63 GetOptions( Link Here
40
$cmd_args->{dry_run} = !$cmd_args->{commit};
42
$cmd_args->{dry_run} = !$cmd_args->{commit};
41
43
42
my $commenter = Koha::Database::Commenter->new({ database => delete $cmd_args->{database}, dbh => C4::Context->dbh });
44
my $commenter = Koha::Database::Commenter->new({ database => delete $cmd_args->{database}, dbh => C4::Context->dbh });
45
my $messages = $cmd_args->{verbose} || $cmd_args->{dry_run} ? [] : undef;
43
if( $cmd_args->{help} ) {
46
if( $cmd_args->{help} ) {
44
    pod2usage( -verbose => 2 );
47
    pod2usage( -verbose => 2 );
45
} elsif( ($cmd_args->{clear}||0) + ($cmd_args->{renumber}||0) + ($cmd_args->{reset}||0) > 1 ) {
48
} elsif( ($cmd_args->{clear}||0) + ($cmd_args->{renumber}||0) + ($cmd_args->{reset}||0) > 1 ) {
46
    print "You cannot pass the clear, renumber and reset flags together\n";
49
    print "You cannot pass the clear, renumber and reset flags together\n";
47
} elsif( delete $cmd_args->{clear} ) {
50
} elsif( delete $cmd_args->{clear} ) {
48
    alert_dry_run( $cmd_args );
51
    alert_dry_run( $cmd_args );
49
    $commenter->clear( $cmd_args );
52
    $commenter->clear( $cmd_args, $messages );
50
} elsif( delete $cmd_args->{reset} ) {
53
} elsif( delete $cmd_args->{reset} ) {
51
    alert_dry_run( $cmd_args );
54
    alert_dry_run( $cmd_args );
52
    $commenter->reset_to_schema( $cmd_args );
55
    $commenter->reset_to_schema( $cmd_args, $messages );
53
} elsif( delete $cmd_args->{renumber} ) {
56
} elsif( delete $cmd_args->{renumber} ) {
54
    alert_dry_run( $cmd_args );
57
    alert_dry_run( $cmd_args );
55
    $commenter->renumber( $cmd_args );
58
    $commenter->renumber( $cmd_args, $messages );
56
} else {
59
} else {
57
    pod2usage( -verbose => 1 );
60
    pod2usage( -verbose => 1 );
58
}
61
}
59
62
print join("\n", @$messages), "\n" if $messages && @$messages;
60
sub alert_dry_run { print "-- DRY RUN\n" if $_[0]->{dry_run}; }
61
63
62
__END__
64
__END__
63
65
(-)a/t/db_dependent/Koha/Database/Commenter.t (-14 / +11 lines)
Lines 24-54 subtest '->new, dry_run' => sub { Link Here
24
24
25
    # Another exception: delete schema file and reset should raise exception
25
    # Another exception: delete schema file and reset should raise exception
26
    my $filename = create_schema_file();
26
    my $filename = create_schema_file();
27
    my $stdout;
27
    $mgr = Koha::Database::Commenter->new({ dbh => $dbh, schema_file => $filename });
28
    open my $fh, '>', \$stdout;
29
    $mgr = Koha::Database::Commenter->new({ dbh => $dbh, schema_file => $filename, fh => $fh });
30
    unlink $filename;
28
    unlink $filename;
31
    throws_ok { $mgr->reset_to_schema({ dry_run => 1, table => 'biblio' }) } 'Koha::Exceptions::FileNotFound', 'Schema deleted';
29
    throws_ok { $mgr->reset_to_schema({ dry_run => 1, table => 'biblio' }) } 'Koha::Exceptions::FileNotFound', 'Schema deleted';
32
30
33
    # Clear comments for article_requests in dry run mode
31
    # Clear comments for article_requests in dry run mode
34
    $stdout = q{};
32
    my $messages = [];
35
    $mgr->clear({ table => 'article_requests', dry_run => 1 });
33
    $mgr->clear( { table => 'article_requests', dry_run => 1 }, $messages );
36
    like( $stdout, qr/COLUMN `toc_request`.*DEFAULT 0;$/m, 'No comment for toc_request' );
34
    is( grep( { /COLUMN `toc_request`.*DEFAULT 0;$/m } @$messages ), 1, 'No comment for toc_request' );
37
35
38
    # Renumber this field in dry run mode
36
    # Renumber this field in dry run mode
39
    $stdout = q{};
37
    $messages = [];
40
    $mgr->renumber({ table => 'article_requests', dry_run => 1 });
38
    $mgr->renumber( { table => 'article_requests', dry_run => 1 }, $messages );
41
    like( $stdout, qr/COLUMN `toc_request`.*COMMENT 'Column_\d+';$/m, 'Numbered comment for toc_request' );
39
    is( grep( { /COLUMN `toc_request`.*COMMENT 'Column_\d+';$/m } @$messages ), 1, 'Numbered comment for toc_request' );
42
40
43
    # Reset in dry run mode, first fix schema file again
41
    # Reset in dry run mode, first fix schema file again
44
    # Our fake schema contains only one column for article_requests now.
42
    # Our fake schema contains only one column for article_requests now.
45
    $filename = create_schema_file();
43
    $filename = create_schema_file();
46
    $mgr = Koha::Database::Commenter->new({ dbh => $dbh, schema_file => $filename, fh => $fh });
44
    $mgr = Koha::Database::Commenter->new({ dbh => $dbh, schema_file => $filename });
47
    $stdout = q{};
45
    $messages = [];
48
    $mgr->reset_to_schema({ table => 'article_requests', dry_run => 1 });
46
    $mgr->reset_to_schema( { table => 'article_requests', dry_run => 1 }, $messages );
49
    # We expect an ALTER clearing toc_request first, followed by an ALTER adding comment.
47
    # We expect an ALTER clearing toc_request first, followed by an ALTER adding comment.
50
    # Note: This is based on the fair assumption that toc_request had a comment! This test could fail if it had not.
48
    # Note: This is based on the fair assumption that toc_request had a comment! This test could fail if it had not.
51
    like( $stdout, qr/ALTER.*toc_request.*DEFAULT 0;(.*\n)+ALTER.*toc_request.*COMMENT.*$/m, 'Reset for one-columned article_requests' );
49
    like( join("\n", @$messages), qr/ALTER.*toc_request.*DEFAULT 0;(.*\n)+ALTER.*toc_request.*COMMENT.*$/m, 'Reset for one-columned article_requests' );
52
50
53
    $schema->storage->txn_rollback;
51
    $schema->storage->txn_rollback;
54
};
52
};
55
- 

Return to bug 32334