2 # BEGIN BPS TAGGED BLOCK {{{
6 # This software is Copyright (c) 1996-2011 Best Practical Solutions, LLC
7 # <sales@bestpractical.com>
9 # (Except where explicitly superseded by other copyright notices)
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
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.
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.
31 # CONTRIBUTION SUBMISSION POLICY:
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.)
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.
48 # END BPS TAGGED BLOCK }}}
56 print STDERR "usage: $0 db_name[:server_name] db_user db_password\n";
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)
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
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
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
96 ObjectCustomFieldValues
111 RightName => 'ascii',
112 ObjectType => 'ascii',
113 PrincipalType => 'ascii',
116 MessageId => 'ascii',
119 ContentType => 'ascii',
120 ContentEncoding => 'ascii',
126 Description => 'utf8',
128 ContentType => 'ascii',
129 ObjectType => 'ascii',
135 Description => 'utf8',
136 LookupType => 'ascii',
138 CustomFieldValues => {
140 Description => 'utf8',
144 Description => 'utf8',
153 ObjectCustomFieldValues => {
154 ObjectType => 'ascii',
156 LargeContent => 'binary',
157 ContentType => 'ascii',
158 ContentEncoding => 'ascii',
161 PrincipalType => 'ascii',
165 Description => 'utf8',
166 CorrespondAddress => 'ascii',
167 CommentAddress => 'ascii',
171 Description => 'utf8',
172 ExecModule => 'ascii',
173 Argument => 'binary',
177 Description => 'utf8',
178 ExecModule => 'ascii',
179 Argument => 'binary',
180 ApplicableTransTypes => 'ascii',
183 Description => 'utf8',
184 ConditionRules => 'utf8',
185 ActionRules => 'utf8',
186 CustomIsApplicableCode => 'utf8',
187 CustomPrepareCode => 'utf8',
188 CustomCommitCode => 'utf8',
192 id => 'binary', # ascii?
193 a_session => 'binary',
197 Description => 'utf8',
208 ObjectType => 'ascii',
213 ReferenceType => 'ascii',
218 Password => 'binary',
221 EmailAddress => 'ascii',
222 FreeformContactInfo => 'utf8',
223 Organization => 'utf8',
227 EmailEncoding => 'ascii',
228 WebEncoding => 'ascii',
229 ExternalContactInfoId => 'utf8',
230 ContactInfoSystem => 'utf8',
231 ExternalAuthId => 'utf8',
232 AuthSystem => 'utf8',
236 MobilePhone => 'utf8',
237 PagerPhone => 'utf8',
249 my %max_type_length = (
252 tinytext => int 1<<8,
253 mediumtext => int 1<<16,
255 longtext => int 1<<32,
260 my ($db_datasource, $db_user, $db_pass) = (shift, shift, shift);
261 my $dbh = DBI->connect("dbi:mysql:$db_datasource", $db_user, $db_pass, { RaiseError => 1 });
262 my $db_name = $db_datasource;
263 $db_name =~ s/:.*$//;
265 my $version = ($dbh->selectrow_array("show variables like 'version'"))[1];
266 ($version) = $version =~ /^(\d+\.\d+)/;
268 push @sql_commands, qq{ALTER DATABASE $db_name DEFAULT CHARACTER SET utf8};
269 convert_table($_) foreach @tables;
271 print join "\n", map(/;$/? $_ : "$_;", @sql_commands), "";
272 my $use_p = $db_pass ? " -p" : '';
273 print STDERR <<ENDREMINDER;
274 -- ** NOTICE: No database changes have been made. **
275 -- Please review the generated SQL, ensure you have a full backup of your database
276 -- and apply it to your database using a command like:
277 -- mysql -u ${db_user}${use_p} $db_name < queries.sql";
281 my %alter_aggregator;
284 @alter_aggregator{'char_to_binary','binary_to_char'} = (['DEFAULT CHARACTER SET utf8'],[]);
286 my $sth = $dbh->column_info( undef, $db_name, $table, undef );
288 while ( my $info = $sth->fetchrow_hashref ) {
289 convert_column(%$info);
291 for my $conversiontype (qw(char_to_binary binary_to_char)) {
292 next unless @{$alter_aggregator{$conversiontype}};
293 push @sql_commands, qq{ALTER TABLE $table\n }.
294 join(",\n ",@{$alter_aggregator{$conversiontype}});
300 my $table = $info{'TABLE_NAME'};
301 my $column = $info{'COLUMN_NAME'};
302 my $type = $info{'TYPE_NAME'};
303 return unless $type =~ /(CHAR|TEXT|BLOB|BINARY)$/i;
305 my $required_charset = $charset{$table}{$column};
306 unless ( $required_charset ) {
307 print STDERR join(".", @info{'TABLE_SCHEMA', 'TABLE_NAME', 'COLUMN_NAME'})
308 ." has type $type however mapping is missing.\n";
312 my $collation = column_info($table, $column)->{'collation'};
313 # mysql 4.1 returns literal NULL instead of undef
314 my $current_charset = $collation && $collation ne 'NULL'? (split /_/, $collation)[0]: 'binary';
315 return if $required_charset eq $current_charset;
317 if ( $required_charset eq 'binary' ) {
318 char_to_binary(%info);
320 elsif ( $current_charset eq 'binary' ) {
321 binary_to_char( $required_charset, %info);
323 char_to_char( $required_charset, %info);
330 my $table = $info{'TABLE_NAME'};
331 my $column = $info{'COLUMN_NAME'};
332 my $new_type = calc_suitable_binary_type(%info);
333 push @{$alter_aggregator{char_to_binary}},
334 "MODIFY $column $new_type ".build_column_definition(%info);
339 my ($charset, %info) = @_;
341 my $table = $info{'TABLE_NAME'};
342 my $column = $info{'COLUMN_NAME'};
343 my $new_type = lc $info{'TYPE_NAME'};
344 if ( $new_type =~ /binary/ ) {
345 $new_type =~ s/binary/char/;
346 $new_type .= '('. $info{'COLUMN_SIZE'} .')';
348 $new_type =~ s/blob/text/;
351 push @{$alter_aggregator{binary_to_char}},
352 "MODIFY $column ". uc($new_type) ." CHARACTER SET ". $charset
353 ." ". build_column_definition(%info);
357 my ($charset, %info) = @_;
359 my $table = $info{'TABLE_NAME'};
360 my $column = $info{'COLUMN_NAME'};
361 my $new_type = $info{'mysql_type_name'};
363 char_to_binary(%info);
364 push @{$alter_aggregator{binary_to_char}},
365 "MODIFY $column ". uc($new_type)." CHARACTER SET ". $charset
366 ." ". build_column_definition(%info);
369 sub calc_suitable_binary_type {
371 my $type = lc $info{'TYPE_NAME'};
372 return 'LONGBLOB' if $type eq 'longtext';
374 my $current_max_byte_length = column_byte_length(@info{qw(TABLE_NAME COLUMN_NAME)}) || 0;
375 if ( $max_type_length{ $type } > $current_max_byte_length ) {
376 if ( $type eq 'varchar' || $type eq 'char' ) {
377 my $new_type = $type;
378 $new_type =~ s/char/binary/;
379 $new_type .= $info{'COLUMN_SIZE'} >= $current_max_byte_length
380 ? '('. $info{'COLUMN_SIZE'} .')'
381 : '('. $current_max_byte_length .')';
384 my $new_type = $type;
385 $new_type =~ s/text/blob/;
390 foreach ( sort { $max_type_length{$a} <=> $max_type_length{$b} } keys %max_type_length ) {
391 next if $max_type_length{ $_ } <= $current_max_byte_length;
393 $new_type = $_; last;
395 $new_type =~ s/text/blob/;
400 sub build_column_definition {
404 $res .= 'NOT ' unless $info{'NULLABLE'};
406 my $default = column_info(@info{qw(TABLE_NAME COLUMN_NAME)})->{default};
407 if ( defined $default ) {
408 $res .= ' DEFAULT '. $dbh->quote($default);
409 } elsif ( $info{'NULLABLE'} ) {
410 $res .= ' DEFAULT NULL';
412 $res .= ' AUTO_INCREMENT' if $info{'mysql_is_auto_increment'};
416 sub column_byte_length {
417 my ($table, $column) = @_;
418 if ( $version >= 5.0 ) {
419 my ($char, $octet) = @{ $dbh->selectrow_arrayref(
420 "SELECT CHARACTER_MAXIMUM_LENGTH, CHARACTER_OCTET_LENGTH FROM information_schema.COLUMNS WHERE"
421 ." TABLE_SCHEMA = ". $dbh->quote($db_name)
422 ." AND TABLE_NAME = ". $dbh->quote($table)
423 ." AND COLUMN_NAME = ". $dbh->quote($column)
425 return $octet if $octet == $char;
427 return $dbh->selectrow_arrayref("SELECT MAX(LENGTH(". $dbh->quote_identifier($column) .")) FROM $table")->[0];
431 my ($table, $column) = @_;
432 # XXX: DBD::mysql doesn't provide this info, may be will do in 4.0007 if I'll write a patch
433 local $dbh->{FetchHashKeyName} = 'NAME_lc';
434 return $dbh->selectrow_hashref("SHOW FULL COLUMNS FROM $table LIKE " . $dbh->quote($column));