98eb7b4e345c7d8988c4554c3885b4f41fcc33a2
[usit-rt.git] / etc / upgrade / upgrade-mysql-schema.pl
1 #!/usr/bin/env perl
2 # BEGIN BPS TAGGED BLOCK {{{
3 #
4 # COPYRIGHT:
5 #
6 # This software is Copyright (c) 1996-2013 Best Practical Solutions, LLC
7 #                                          <sales@bestpractical.com>
8 #
9 # (Except where explicitly superseded by other copyright notices)
10 #
11 #
12 # LICENSE:
13 #
14 # This work is made available to you under the terms of Version 2 of
15 # the GNU General Public License. A copy of that license should have
16 # been provided with this software, but in any event can be snarfed
17 # from www.gnu.org.
18 #
19 # This work is distributed in the hope that it will be useful, but
20 # WITHOUT ANY WARRANTY; without even the implied warranty of
21 # MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the GNU
22 # General Public License for more details.
23 #
24 # You should have received a copy of the GNU General Public License
25 # along with this program; if not, write to the Free Software
26 # Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA
27 # 02110-1301 or visit their web page on the internet at
28 # http://www.gnu.org/licenses/old-licenses/gpl-2.0.html.
29 #
30 #
31 # CONTRIBUTION SUBMISSION POLICY:
32 #
33 # (The following paragraph is not intended to limit the rights granted
34 # to you to modify and distribute this software under the terms of
35 # the GNU General Public License and is only of importance to you if
36 # you choose to contribute your changes and enhancements to the
37 # community by submitting them to Best Practical Solutions, LLC.)
38 #
39 # By intentionally submitting any modifications, corrections or
40 # derivatives to this work, or any other work intended for use with
41 # Request Tracker, to Best Practical Solutions, LLC, you confirm that
42 # you are the copyright holder for those contributions and you grant
43 # Best Practical Solutions,  LLC a nonexclusive, worldwide, irrevocable,
44 # royalty-free, perpetual, license to use, copy, create derivative
45 # works based on those contributions, and sublicense and distribute
46 # those contributions and any derivatives thereof.
47 #
48 # END BPS TAGGED BLOCK }}}
49 use strict;
50 use warnings;
51
52 use DBI;
53 use DBD::mysql 4.002;
54
55 unless (@ARGV) {
56     print STDERR "usage: $0 db_name[:server_name] db_user db_password\n";
57     exit 1;
58 }
59
60 # pretty correct support of charsets has been introduced in mysql 4.1
61 # as RT doesn't use it may result in issues:
62 # 1) data corruptions when default charset of mysql server has data restrictions like utf8
63 # 2) wrong ordering (collations)
64
65 # we have to define correct types for all columns. RT uses UTF-8, ascii and binary.
66 # * ascii is subset of many mysql's charsets except may be one or two rare where some ascii
67 #   characters replaced with local
68 # * for many charsets mysql allows us to store any octets sequences even when those are
69 #   invalid for this particula set, for example we can store UTF-8 data in latin1
70 #   column and fetch it as UTF-8, however sorting will be wrong
71
72 # here is tricky algorithm to change column to desired charset:
73 # * text to binary convertion is pretty straight forward except that text types
74 #   have length definitions in terms of characters and in some cases we must
75 #   use longer binary types to satisfy space requirements
76 # * binary to text is much easier as we know that there is ascii or UTF-8 then
77 #   we just make convertion, also 32 chars are long enough to store 32 bytes, so
78 #   length changes is not required
79 # * text to text convertion is trickier. no matter what is the current character set
80 #   of the column we know that there is either ascii or UTF-8, so we can not use
81 #   direct convertion, instead we do text to binary plus binary to text convertion
82 #   instead
83 # * as well we add charset definition for all tables and for the DB as well,
84 #   so all new columns by default will be in UTF-8 charset
85
86 my @tables = qw(
87     ACL
88     Attachments
89     Attributes
90     CustomFields
91     CustomFieldValues
92     GroupMembers
93     Groups
94     Links
95     ObjectCustomFields
96     ObjectCustomFieldValues
97     Principals
98     Queues
99     ScripActions
100     ScripConditions
101     Scrips
102     sessions
103     Templates
104     Tickets
105     Transactions
106     Users
107     FM_Articles
108     FM_Classes
109     FM_ObjectTopics
110     FM_Topics
111 );
112
113 my %charset = (
114     ACL                      => {
115         RightName     => 'ascii',
116         ObjectType    => 'ascii',
117         PrincipalType => 'ascii',
118     },
119     Attachments              => {
120         MessageId  => 'ascii',
121         Subject  => 'utf8',
122         Filename  => 'utf8',
123         ContentType  => 'ascii',
124         ContentEncoding  => 'ascii',
125         Content  => 'binary',
126         Headers  => 'utf8',
127     },
128     Attributes               => {
129         Name  => 'utf8',
130         Description  => 'utf8',
131         Content  => 'binary',
132         ContentType  => 'ascii',
133         ObjectType  => 'ascii',
134     },
135     CustomFields             => {
136         Name  => 'utf8',
137         Type  => 'ascii',
138         Pattern  => 'utf8',
139         Description  => 'utf8',
140         LookupType => 'ascii',
141     },
142     CustomFieldValues        => {
143         Name  => 'utf8',
144         Description  => 'utf8',
145     },
146     FM_Articles => {
147         Name => 'utf8',
148         Summary => 'utf8',
149         URI => 'ascii',
150     },
151     FM_Classes => {
152         Name => 'utf8',
153         Description => 'utf8',
154     },
155     FM_ObjectTopics => {
156         ObjectType => 'ascii',
157     },
158     FM_Topics => {
159         Name => 'utf8',
160         Description => 'utf8',
161         ObjectType => 'ascii',
162     },
163     Groups                   => {
164         Name  => 'utf8',
165         Description  => 'utf8',
166         Domain  => 'ascii',
167         Type  => 'ascii',
168     },
169     Links                    => {
170         Base  => 'ascii',
171         Target  => 'ascii',
172         Type  => 'ascii',
173     },
174     ObjectCustomFieldValues  => {
175         ObjectType  => 'ascii',
176         Content  => 'utf8',
177         LargeContent  => 'binary',
178         ContentType  => 'ascii',
179         ContentEncoding  => 'ascii',
180     },
181     Principals               => {
182         PrincipalType  => 'ascii',
183     },
184     Queues                   => {
185         Name  => 'utf8',
186         Description  => 'utf8',
187         CorrespondAddress  => 'ascii',
188         CommentAddress  => 'ascii',
189     },
190     ScripActions             => {
191         Name  => 'utf8',
192         Description  => 'utf8',
193         ExecModule  => 'ascii',
194         Argument  => 'binary',
195     },
196     ScripConditions          => {
197         Name  => 'utf8',
198         Description  => 'utf8',
199         ExecModule  => 'ascii',
200         Argument  => 'binary',
201         ApplicableTransTypes  => 'ascii',
202     },
203     Scrips                   => {
204         Description  => 'utf8',
205         ConditionRules  => 'utf8',
206         ActionRules  => 'utf8',
207         CustomIsApplicableCode  => 'utf8',
208         CustomPrepareCode  => 'utf8',
209         CustomCommitCode  => 'utf8',
210         Stage  => 'ascii',
211     },
212     sessions                 => {
213         id         => 'binary', # ascii?
214         a_session  => 'binary',
215     },
216     Templates                => {
217         Name  => 'utf8',
218         Description  => 'utf8',
219         Type  => 'ascii',
220         Language  => 'ascii',
221         Content  => 'utf8',
222     },
223     Tickets                  => {
224         Type  => 'ascii',
225         Subject  => 'utf8',
226         Status  => 'ascii',
227     },
228     Transactions             => {
229         ObjectType  => 'ascii',
230         Type  => 'ascii',
231         Field  => 'ascii',
232         OldValue  => 'utf8',
233         NewValue  => 'utf8',
234         ReferenceType  => 'ascii',
235         Data  => 'utf8',
236     },
237     Users                    => {
238         Name  => 'utf8',
239         Password  => 'binary',
240         Comments  => 'utf8',
241         Signature  => 'utf8',
242         EmailAddress  => 'ascii',
243         FreeformContactInfo  => 'utf8',
244         Organization  => 'utf8',
245         RealName  => 'utf8',
246         NickName  => 'utf8',
247         Lang  => 'ascii',
248         EmailEncoding  => 'ascii',
249         WebEncoding  => 'ascii',
250         ExternalContactInfoId  => 'utf8',
251         ContactInfoSystem  => 'utf8',
252         ExternalAuthId  => 'utf8',
253         AuthSystem  => 'utf8',
254         Gecos  => 'utf8',
255         HomePhone  => 'utf8',
256         WorkPhone  => 'utf8',
257         MobilePhone  => 'utf8',
258         PagerPhone  => 'utf8',
259         Address1  => 'utf8',
260         Address2  => 'utf8',
261         City  => 'utf8',
262         State  => 'utf8',
263         Zip  => 'utf8',
264         Country  => 'utf8',
265         Timezone  => 'ascii',
266         PGPKey  => 'binary',
267     },
268 );
269
270 my %max_type_length = (
271     char       => int 1<<8,
272     varchar    => int 1<<8,
273     tinytext   => int 1<<8,
274     mediumtext => int 1<<16,
275     text       => int 1<<24,
276     longtext   => int 1<<32,
277 );
278
279 my @sql_commands;
280
281 my ($db_datasource, $db_user, $db_pass) = (shift, shift, shift);
282 my $dbh = DBI->connect("dbi:mysql:$db_datasource", $db_user, $db_pass, { RaiseError => 1 });
283 my $db_name = $db_datasource;
284 $db_name =~ s/:.*$//;
285
286 my $version = ($dbh->selectrow_array("show variables like 'version'"))[1];
287 ($version) = $version =~ /^(\d+\.\d+)/;
288
289 push @sql_commands, qq{ALTER DATABASE `$db_name` DEFAULT CHARACTER SET utf8};
290 convert_table($_) foreach @tables;
291
292 print join "\n", map(/;$/? $_ : "$_;", @sql_commands), "";
293 my $use_p = $db_pass ? " -p" : '';
294 print STDERR <<ENDREMINDER;
295 -- ** NOTICE: No database changes have been made. **
296 -- Please review the generated SQL, ensure you have a full backup of your database 
297 -- and apply it to your database using a command like:
298 -- mysql -u ${db_user}${use_p} $db_name < queries.sql";
299 ENDREMINDER
300 exit 0;
301
302 my %alter_aggregator;
303 sub convert_table {
304     my $table = shift;
305     @alter_aggregator{'char_to_binary','binary_to_char'} = (['DEFAULT CHARACTER SET utf8'],[]);
306
307     my $sth = $dbh->column_info( undef, $db_name, $table, undef );
308     $sth->execute;
309     my $columns = $sth->fetchall_arrayref({});
310     return unless @$columns;
311     foreach my $info (@$columns) {
312         convert_column(%$info);
313     }
314     for my $conversiontype (qw(char_to_binary binary_to_char)) {
315         next unless @{$alter_aggregator{$conversiontype}};
316         push @sql_commands, qq{ALTER TABLE $table\n   }.
317             join(",\n   ",@{$alter_aggregator{$conversiontype}});
318     }
319 }
320
321 sub convert_column {
322     my %info = @_;
323     my $table = $info{'TABLE_NAME'};
324     my $column = $info{'COLUMN_NAME'};
325     my $type = $info{'TYPE_NAME'};
326     return unless $type =~ /(CHAR|TEXT|BLOB|BINARY)$/i;
327
328     my $required_charset = $charset{$table}{$column};
329     unless ( $required_charset ) {
330         print STDERR join(".", @info{'TABLE_SCHEMA', 'TABLE_NAME', 'COLUMN_NAME'})
331             ." has type $type however mapping is missing.\n";
332         return;
333     }
334
335     my $collation = column_info($table, $column)->{'collation'};
336     # mysql 4.1 returns literal NULL instead of undef
337     my $current_charset = $collation && $collation ne 'NULL'? (split /_/, $collation)[0]: 'binary';
338     return if $required_charset eq $current_charset;
339
340     if ( $required_charset eq 'binary' ) {
341         char_to_binary(%info);
342     }
343     elsif ( $current_charset eq 'binary' ) {
344         binary_to_char( $required_charset, %info);
345     } else {
346         char_to_char( $required_charset, %info);
347     }
348 }
349
350 sub char_to_binary {
351     my %info = @_;
352
353     my $table = $info{'TABLE_NAME'};
354     my $column = $info{'COLUMN_NAME'};
355     my $new_type = calc_suitable_binary_type(%info);
356     push @{$alter_aggregator{char_to_binary}},
357         "MODIFY $column $new_type ".build_column_definition(%info);
358
359 }
360
361 sub binary_to_char {
362     my ($charset, %info) = @_;
363
364     my $table = $info{'TABLE_NAME'};
365     my $column = $info{'COLUMN_NAME'};
366     my $new_type = lc $info{'TYPE_NAME'};
367     if ( $new_type =~ /binary/ ) {
368         $new_type =~ s/binary/char/;
369         $new_type .= '('. $info{'COLUMN_SIZE'} .')';
370     } else {
371         $new_type =~ s/blob/text/;
372     }
373
374     push @{$alter_aggregator{binary_to_char}},
375         "MODIFY $column ". uc($new_type) ." CHARACTER SET ". $charset
376         ." ". build_column_definition(%info);
377 }
378
379 sub char_to_char {
380     my ($charset, %info) = @_;
381
382     my $table = $info{'TABLE_NAME'};
383     my $column = $info{'COLUMN_NAME'};
384     my $new_type = $info{'mysql_type_name'};
385
386     char_to_binary(%info);
387     push @{$alter_aggregator{binary_to_char}},
388         "MODIFY $column ". uc($new_type)." CHARACTER SET ". $charset
389         ." ". build_column_definition(%info);
390 }
391
392 sub calc_suitable_binary_type {
393     my %info = @_;
394     my $type = lc $info{'TYPE_NAME'};
395     return 'LONGBLOB' if $type eq 'longtext';
396
397     my $current_max_byte_length = column_byte_length(@info{qw(TABLE_NAME COLUMN_NAME)}) || 0;
398     if ( $max_type_length{ $type } > $current_max_byte_length ) {
399         if ( $type eq 'varchar' || $type eq 'char' ) {
400             my $new_type = $type;
401             $new_type =~ s/char/binary/;
402             $new_type .= $info{'COLUMN_SIZE'} >= $current_max_byte_length
403                 ? '('. $info{'COLUMN_SIZE'} .')'
404                 : '('. $current_max_byte_length .')';
405             return uc $new_type;
406         } else {
407             my $new_type = $type;
408             $new_type =~ s/text/blob/;
409             return uc $new_type;
410         }
411     } else {
412         my $new_type;
413         foreach ( sort { $max_type_length{$a} <=> $max_type_length{$b} } keys %max_type_length ) {
414             next if $max_type_length{ $_ } <= $current_max_byte_length;
415             
416             $new_type = $_; last;
417         }
418         $new_type =~ s/text/blob/;
419         return uc $new_type;
420     }
421 }
422
423 sub build_column_definition {
424     my %info = @_;
425
426     my $res = '';
427     $res .= 'NOT ' unless $info{'NULLABLE'};
428     $res .= 'NULL';
429     my $default = column_info(@info{qw(TABLE_NAME COLUMN_NAME)})->{default};
430     if ( defined $default ) {
431         $res .= ' DEFAULT '. $dbh->quote($default);
432     } elsif ( $info{'NULLABLE'} ) {
433         $res .= ' DEFAULT NULL';
434     }
435     $res .= ' AUTO_INCREMENT' if $info{'mysql_is_auto_increment'};
436     return $res;
437 }
438
439 sub column_byte_length {
440     my ($table, $column) = @_;
441     if ( $version >= 5.0 ) {
442         # information_schema searches can be case sensitive
443         # and users may use lower_case_table_names, use LOWER
444         # for everything just in case
445         # http://dev.mysql.com/doc/refman/5.1/en/charset-collation-information-schema.html
446         my ($char, $octet) = @{ $dbh->selectrow_arrayref(
447             "SELECT CHARACTER_MAXIMUM_LENGTH, CHARACTER_OCTET_LENGTH FROM information_schema.COLUMNS WHERE"
448             ."     LOWER(TABLE_SCHEMA) = ". lc( $dbh->quote($db_name) )
449             ." AND LOWER(TABLE_NAME)   = ". lc( $dbh->quote($table) )
450             ." AND LOWER(COLUMN_NAME)  = ". lc( $dbh->quote($column) )
451         ) };
452         return $octet if $octet == $char;
453     }
454     return $dbh->selectrow_arrayref("SELECT MAX(LENGTH(". $dbh->quote_identifier($column) .")) FROM $table")->[0];
455 }
456
457 sub column_info {
458     my ($table, $column) = @_;
459     # XXX: DBD::mysql doesn't provide this info, may be will do in 4.0007 if I'll write a patch
460     local $dbh->{FetchHashKeyName} = 'NAME_lc';
461     return $dbh->selectrow_hashref("SHOW FULL COLUMNS FROM $table LIKE " . $dbh->quote($column));
462 }
463