rt 4.0.23

[freeside.git] / rt / etc / upgrade / upgrade-mysql-schema.pl

diff --git a/rt/etc/upgrade/upgrade-mysql-schema.pl b/rt/etc/upgrade/upgrade-mysql-schema.pl
index bc59c97..92d18e3 100755 (executable)
--- a/rt/etc/upgrade/upgrade-mysql-schema.pl
+++ b/rt/etc/upgrade/upgrade-mysql-schema.pl
@@ -1,5 +1,51 @@
-#!/usr/bin/perl
-
+#!/usr/bin/env perl
+# BEGIN BPS TAGGED BLOCK {{{
+#
+# COPYRIGHT:
+#
+# This software is Copyright (c) 1996-2015 Best Practical Solutions, LLC
+#                                          <sales@bestpractical.com>
+#
+# (Except where explicitly superseded by other copyright notices)
+#
+#
+# LICENSE:
+#
+# This work is made available to you under the terms of Version 2 of
+# the GNU General Public License. A copy of that license should have
+# been provided with this software, but in any event can be snarfed
+# from www.gnu.org.
+#
+# This work is distributed in the hope that it will be useful, but
+# WITHOUT ANY WARRANTY; without even the implied warranty of
+# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the GNU
+# General Public License for more details.
+#
+# You should have received a copy of the GNU General Public License
+# along with this program; if not, write to the Free Software
+# Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA
+# 02110-1301 or visit their web page on the internet at
+# http://www.gnu.org/licenses/old-licenses/gpl-2.0.html.
+#
+#
+# CONTRIBUTION SUBMISSION POLICY:
+#
+# (The following paragraph is not intended to limit the rights granted
+# to you to modify and distribute this software under the terms of
+# the GNU General Public License and is only of importance to you if
+# you choose to contribute your changes and enhancements to the
+# community by submitting them to Best Practical Solutions, LLC.)
+#
+# By intentionally submitting any modifications, corrections or
+# derivatives to this work, or any other work intended for use with
+# Request Tracker, to Best Practical Solutions, LLC, you confirm that
+# you are the copyright holder for those contributions and you grant
+# Best Practical Solutions,  LLC a nonexclusive, worldwide, irrevocable,
+# royalty-free, perpetual, license to use, copy, create derivative
+# works based on those contributions, and sublicense and distribute
+# those contributions and any derivatives thereof.
+#
+# END BPS TAGGED BLOCK }}}
 use strict;
 use warnings;
 
@@ -58,6 +104,10 @@ my @tables = qw(
     Tickets
     Transactions
     Users
+    FM_Articles
+    FM_Classes
+    FM_ObjectTopics
+    FM_Topics
 );
 
 my %charset = (
@@ -93,6 +143,23 @@ my %charset = (
         Name  => 'utf8',
         Description  => 'utf8',
     },
+    FM_Articles => {
+        Name => 'utf8',
+        Summary => 'utf8',
+        URI => 'ascii',
+    },
+    FM_Classes => {
+        Name => 'utf8',
+        Description => 'utf8',
+    },
+    FM_ObjectTopics => {
+        ObjectType => 'ascii',
+    },
+    FM_Topics => {
+        Name => 'utf8',
+        Description => 'utf8',
+        ObjectType => 'ascii',
+    },
     Groups                   => {
         Name  => 'utf8',
         Description  => 'utf8',
@@ -117,8 +184,8 @@ my %charset = (
     Queues                   => {
         Name  => 'utf8',
         Description  => 'utf8',
-        CorrespondAddress  => 'ascii',
-        CommentAddress  => 'ascii',
+        CorrespondAddress  => 'utf8',
+        CommentAddress  => 'utf8',
     },
     ScripActions             => {
         Name  => 'utf8',
@@ -172,7 +239,7 @@ my %charset = (
         Password  => 'binary',
         Comments  => 'utf8',
         Signature  => 'utf8',
-        EmailAddress  => 'ascii',
+        EmailAddress  => 'utf8',
         FreeformContactInfo  => 'utf8',
         Organization  => 'utf8',
         RealName  => 'utf8',
@@ -219,7 +286,7 @@ $db_name =~ s/:.*$//;
 my $version = ($dbh->selectrow_array("show variables like 'version'"))[1];
 ($version) = $version =~ /^(\d+\.\d+)/;
 
-push @sql_commands, qq{ALTER DATABASE $db_name DEFAULT CHARACTER SET utf8};
+push @sql_commands, qq{ALTER DATABASE `$db_name` DEFAULT CHARACTER SET utf8};
 convert_table($_) foreach @tables;
 
 print join "\n", map(/;$/? $_ : "$_;", @sql_commands), "";
@@ -239,7 +306,9 @@ sub convert_table {
 
     my $sth = $dbh->column_info( undef, $db_name, $table, undef );
     $sth->execute;
-    while ( my $info = $sth->fetchrow_hashref ) {
+    my $columns = $sth->fetchall_arrayref({});
+    return unless @$columns;
+    foreach my $info (@$columns) {
         convert_column(%$info);
     }
     for my $conversiontype (qw(char_to_binary binary_to_char)) {
@@ -370,11 +439,15 @@ sub build_column_definition {
 sub column_byte_length {
     my ($table, $column) = @_;
     if ( $version >= 5.0 ) {
+        # information_schema searches can be case sensitive
+        # and users may use lower_case_table_names, use LOWER
+        # for everything just in case
+        # http://dev.mysql.com/doc/refman/5.1/en/charset-collation-information-schema.html
         my ($char, $octet) = @{ $dbh->selectrow_arrayref(
             "SELECT CHARACTER_MAXIMUM_LENGTH, CHARACTER_OCTET_LENGTH FROM information_schema.COLUMNS WHERE"
-            ."     TABLE_SCHEMA = ". $dbh->quote($db_name)
-            ." AND TABLE_NAME   = ". $dbh->quote($table)
-            ." AND COLUMN_NAME  = ". $dbh->quote($column)
+            ."     LOWER(TABLE_SCHEMA) = ". lc( $dbh->quote($db_name) )
+            ." AND LOWER(TABLE_NAME)   = ". lc( $dbh->quote($table) )
+            ." AND LOWER(COLUMN_NAME)  = ". lc( $dbh->quote($column) )
         ) };
         return $octet if $octet == $char;
     }