X-Git-Url: http://git.freeside.biz/gitweb/?a=blobdiff_plain;f=rt%2Fetc%2Fupgrade%2Fupgrade-mysql-schema.pl;h=8923fc6c40ee1fd2ba2445025d07fdf31f00edba;hb=HEAD;hp=4a104b60f21b430fd767cdd3208ac9fe5f8b5c73;hpb=0fb307c305e4bc2c9c27dc25a3308beae3a4d33c;p=freeside.git

diff --git a/rt/etc/upgrade/upgrade-mysql-schema.pl b/rt/etc/upgrade/upgrade-mysql-schema.pl
index 4a104b60f..8923fc6c4 100755
--- a/rt/etc/upgrade/upgrade-mysql-schema.pl
+++ b/rt/etc/upgrade/upgrade-mysql-schema.pl
@@ -1,9 +1,9 @@
-#!/usr/bin/perl
+#!/usr/bin/env perl
 # BEGIN BPS TAGGED BLOCK {{{
 #
 # COPYRIGHT:
 #
-# This software is Copyright (c) 1996-2011 Best Practical Solutions, LLC
+# This software is Copyright (c) 1996-2019 Best Practical Solutions, LLC
 #                                          <sales@bestpractical.com>
 #
 # (Except where explicitly superseded by other copyright notices)
@@ -104,6 +104,10 @@ my @tables = qw(
     Tickets
     Transactions
     Users
+    FM_Articles
+    FM_Classes
+    FM_ObjectTopics
+    FM_Topics
 );
 
 my %charset = (
@@ -139,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',
@@ -163,8 +184,8 @@ my %charset = (
     Queues                   => {
         Name  => 'utf8',
         Description  => 'utf8',
-        CorrespondAddress  => 'ascii',
-        CommentAddress  => 'ascii',
+        CorrespondAddress  => 'utf8',
+        CommentAddress  => 'utf8',
     },
     ScripActions             => {
         Name  => 'utf8',
@@ -218,7 +239,7 @@ my %charset = (
         Password  => 'binary',
         Comments  => 'utf8',
         Signature  => 'utf8',
-        EmailAddress  => 'ascii',
+        EmailAddress  => 'utf8',
         FreeformContactInfo  => 'utf8',
         Organization  => 'utf8',
         RealName  => 'utf8',
@@ -265,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), "";
@@ -285,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)) {
@@ -416,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;
     }