rt/etc/upgrade/upgrade-mysql-schema.pl

   1 #!/usr/bin/perl
   2 # BEGIN BPS TAGGED BLOCK {{{
   3 #
   4 # COPYRIGHT:
   5 #
   6 # This software is Copyright (c) 1996-2011 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 );
 108
 109 my %charset = (
 110     ACL                      => {
 111         RightName     => 'ascii',
 112         ObjectType    => 'ascii',
 113         PrincipalType => 'ascii',
 114     },
 115     Attachments              => {
 116         MessageId  => 'ascii',
 117         Subject  => 'utf8',
 118         Filename  => 'utf8',
 119         ContentType  => 'ascii',
 120         ContentEncoding  => 'ascii',
 121         Content  => 'binary',
 122         Headers  => 'utf8',
 123     },
 124     Attributes               => {
 125         Name  => 'utf8',
 126         Description  => 'utf8',
 127         Content  => 'binary',
 128         ContentType  => 'ascii',
 129         ObjectType  => 'ascii',
 130     },
 131     CustomFields             => {
 132         Name  => 'utf8',
 133         Type  => 'ascii',
 134         Pattern  => 'utf8',
 135         Description  => 'utf8',
 136         LookupType => 'ascii',
 137     },
 138     CustomFieldValues        => {
 139         Name  => 'utf8',
 140         Description  => 'utf8',
 141     },
 142     Groups                   => {
 143         Name  => 'utf8',
 144         Description  => 'utf8',
 145         Domain  => 'ascii',
 146         Type  => 'ascii',
 147     },
 148     Links                    => {
 149         Base  => 'ascii',
 150         Target  => 'ascii',
 151         Type  => 'ascii',
 152     },
 153     ObjectCustomFieldValues  => {
 154         ObjectType  => 'ascii',
 155         Content  => 'utf8',
 156         LargeContent  => 'binary',
 157         ContentType  => 'ascii',
 158         ContentEncoding  => 'ascii',
 159     },
 160     Principals               => {
 161         PrincipalType  => 'ascii',
 162     },
 163     Queues                   => {
 164         Name  => 'utf8',
 165         Description  => 'utf8',
 166         CorrespondAddress  => 'ascii',
 167         CommentAddress  => 'ascii',
 168     },
 169     ScripActions             => {
 170         Name  => 'utf8',
 171         Description  => 'utf8',
 172         ExecModule  => 'ascii',
 173         Argument  => 'binary',
 174     },
 175     ScripConditions          => {
 176         Name  => 'utf8',
 177         Description  => 'utf8',
 178         ExecModule  => 'ascii',
 179         Argument  => 'binary',
 180         ApplicableTransTypes  => 'ascii',
 181     },
 182     Scrips                   => {
 183         Description  => 'utf8',
 184         ConditionRules  => 'utf8',
 185         ActionRules  => 'utf8',
 186         CustomIsApplicableCode  => 'utf8',
 187         CustomPrepareCode  => 'utf8',
 188         CustomCommitCode  => 'utf8',
 189         Stage  => 'ascii',
 190     },
 191     sessions                 => {
 192         id         => 'binary', # ascii?
 193         a_session  => 'binary',
 194     },
 195     Templates                => {
 196         Name  => 'utf8',
 197         Description  => 'utf8',
 198         Type  => 'ascii',
 199         Language  => 'ascii',
 200         Content  => 'utf8',
 201     },
 202     Tickets                  => {
 203         Type  => 'ascii',
 204         Subject  => 'utf8',
 205         Status  => 'ascii',
 206     },
 207     Transactions             => {
 208         ObjectType  => 'ascii',
 209         Type  => 'ascii',
 210         Field  => 'ascii',
 211         OldValue  => 'utf8',
 212         NewValue  => 'utf8',
 213         ReferenceType  => 'ascii',
 214         Data  => 'utf8',
 215     },
 216     Users                    => {
 217         Name  => 'utf8',
 218         Password  => 'binary',
 219         Comments  => 'utf8',
 220         Signature  => 'utf8',
 221         EmailAddress  => 'ascii',
 222         FreeformContactInfo  => 'utf8',
 223         Organization  => 'utf8',
 224         RealName  => 'utf8',
 225         NickName  => 'utf8',
 226         Lang  => 'ascii',
 227         EmailEncoding  => 'ascii',
 228         WebEncoding  => 'ascii',
 229         ExternalContactInfoId  => 'utf8',
 230         ContactInfoSystem  => 'utf8',
 231         ExternalAuthId  => 'utf8',
 232         AuthSystem  => 'utf8',
 233         Gecos  => 'utf8',
 234         HomePhone  => 'utf8',
 235         WorkPhone  => 'utf8',
 236         MobilePhone  => 'utf8',
 237         PagerPhone  => 'utf8',
 238         Address1  => 'utf8',
 239         Address2  => 'utf8',
 240         City  => 'utf8',
 241         State  => 'utf8',
 242         Zip  => 'utf8',
 243         Country  => 'utf8',
 244         Timezone  => 'ascii',
 245         PGPKey  => 'binary',
 246     },
 247 );
 248
 249 my %max_type_length = (
 250     char       => int 1<<8,
 251     varchar    => int 1<<8,
 252     tinytext   => int 1<<8,
 253     mediumtext => int 1<<16,
 254     text       => int 1<<24,
 255     longtext   => int 1<<32,
 256 );
 257
 258 my @sql_commands;
 259
 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/:.*$//;
 264
 265 my $version = ($dbh->selectrow_array("show variables like 'version'"))[1];
 266 ($version) = $version =~ /^(\d+\.\d+)/;
 267
 268 push @sql_commands, qq{ALTER DATABASE $db_name DEFAULT CHARACTER SET utf8};
 269 convert_table($_) foreach @tables;
 270
 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";
 278 ENDREMINDER
 279 exit 0;
 280
 281 my %alter_aggregator;
 282 sub convert_table {
 283     my $table = shift;
 284     @alter_aggregator{'char_to_binary','binary_to_char'} = (['DEFAULT CHARACTER SET utf8'],[]);
 285
 286     my $sth = $dbh->column_info( undef, $db_name, $table, undef );
 287     $sth->execute;
 288     while ( my $info = $sth->fetchrow_hashref ) {
 289         convert_column(%$info);
 290     }
 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}});
 295     }
 296 }
 297
 298 sub convert_column {
 299     my %info = @_;
 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;
 304
 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";
 309         return;
 310     }
 311
 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;
 316
 317     if ( $required_charset eq 'binary' ) {
 318         char_to_binary(%info);
 319     }
 320     elsif ( $current_charset eq 'binary' ) {
 321         binary_to_char( $required_charset, %info);
 322     } else {
 323         char_to_char( $required_charset, %info);
 324     }
 325 }
 326
 327 sub char_to_binary {
 328     my %info = @_;
 329
 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);
 335
 336 }
 337
 338 sub binary_to_char {
 339     my ($charset, %info) = @_;
 340
 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'} .')';
 347     } else {
 348         $new_type =~ s/blob/text/;
 349     }
 350
 351     push @{$alter_aggregator{binary_to_char}},
 352         "MODIFY $column ". uc($new_type) ." CHARACTER SET ". $charset
 353         ." ". build_column_definition(%info);
 354 }
 355
 356 sub char_to_char {
 357     my ($charset, %info) = @_;
 358
 359     my $table = $info{'TABLE_NAME'};
 360     my $column = $info{'COLUMN_NAME'};
 361     my $new_type = $info{'mysql_type_name'};
 362
 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);
 367 }
 368
 369 sub calc_suitable_binary_type {
 370     my %info = @_;
 371     my $type = lc $info{'TYPE_NAME'};
 372     return 'LONGBLOB' if $type eq 'longtext';
 373
 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 .')';
 382             return uc $new_type;
 383         } else {
 384             my $new_type = $type;
 385             $new_type =~ s/text/blob/;
 386             return uc $new_type;
 387         }
 388     } else {
 389         my $new_type;
 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;
 392
 393             $new_type = $_; last;
 394         }
 395         $new_type =~ s/text/blob/;
 396         return uc $new_type;
 397     }
 398 }
 399
 400 sub build_column_definition {
 401     my %info = @_;
 402
 403     my $res = '';
 404     $res .= 'NOT ' unless $info{'NULLABLE'};
 405     $res .= 'NULL';
 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';
 411     }
 412     $res .= ' AUTO_INCREMENT' if $info{'mysql_is_auto_increment'};
 413     return $res;
 414 }
 415
 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)
 424         ) };
 425         return $octet if $octet == $char;
 426     }
 427     return $dbh->selectrow_arrayref("SELECT MAX(LENGTH(". $dbh->quote_identifier($column) .")) FROM $table")->[0];
 428 }
 429
 430 sub column_info {
 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));
 435 }
 436