aboutsummaryrefslogtreecommitdiffstats
path: root/Bugzilla/DB
diff options
context:
space:
mode:
authormkanat%bugzilla.org <>2006-11-18 23:10:11 +0000
committermkanat%bugzilla.org <>2006-11-18 23:10:11 +0000
commit496a78f616b10c1c5c4bd0398021232e9bffa77e (patch)
treefd30d0e1fc1c7789111d43ef5d7ce7dd31baa7de /Bugzilla/DB
parentb8012210e03800d6473d2b19d7cf1beae6d12dc3 (diff)
downloadbugs-496a78f616b10c1c5c4bd0398021232e9bffa77e.tar
bugs-496a78f616b10c1c5c4bd0398021232e9bffa77e.tar.gz
bugs-496a78f616b10c1c5c4bd0398021232e9bffa77e.tar.bz2
bugs-496a78f616b10c1c5c4bd0398021232e9bffa77e.tar.xz
bugs-496a78f616b10c1c5c4bd0398021232e9bffa77e.zip
Bug 304550: Bugzilla should always store data in MySQL as UTF-8
Patch By Max Kanat-Alexander <mkanat@bugzilla.org> r=LpSolit, a=justdave
Diffstat (limited to 'Bugzilla/DB')
-rw-r--r--Bugzilla/DB/Mysql.pm86
-rw-r--r--Bugzilla/DB/Schema.pm23
-rw-r--r--Bugzilla/DB/Schema/Mysql.pm14
3 files changed, 119 insertions, 4 deletions
diff --git a/Bugzilla/DB/Mysql.pm b/Bugzilla/DB/Mysql.pm
index 3bcd74389..5dd598dff 100644
--- a/Bugzilla/DB/Mysql.pm
+++ b/Bugzilla/DB/Mysql.pm
@@ -59,6 +59,10 @@ sub new {
my $self = $class->db_new($dsn, $user, $pass);
+ # This makes sure that if the tables are encoded as UTF-8, we
+ # return their data correctly.
+ $self->do("SET NAMES utf8") if Bugzilla->params->{'utf8'};
+
# all class local variables stored in DBI derived class needs to have
# a prefix 'private_'. See DBI documentation.
$self->{private_bz_tables_locked} = "";
@@ -545,6 +549,88 @@ sub bz_setup_database {
MAX_ROWS=100000");
}
+ # Convert the database to UTF-8 if the utf8 parameter is on.
+ if (Bugzilla->params->{'utf8'} && !$self->bz_db_is_utf8) {
+ print <<EOT;
+
+WARNING: We are about to convert your table storage format to UTF8. This
+ allows Bugzilla to correctly store and sort international characters.
+ However, if you have any non-UTF-8 data in your database,
+ it ***WILL BE DELETED*** by this process. So, before
+ you continue with checksetup.pl, if you have any non-UTF-8
+ data (or even if you're not sure) you should press Ctrl-C now
+ to interrupt checksetup.pl, and run contrib/recode.pl to make all
+ the data in your database into UTF-8. You should also back up your
+ database before continuing.
+
+ If you ever used a version of Bugzilla before 2.22, we STRONGLY
+ recommend that you stop checksetup.pl NOW and run contrib/recode.pl.
+
+ Continuing in 60 seconds...
+EOT
+ sleep 60;
+
+ print "Converting table storage format to UTF-8. This may take a",
+ " while.\n";
+ foreach my $table ($self->bz_table_list_real) {
+ my $info_sth = $self->prepare("SHOW FULL COLUMNS FROM $table");
+ $info_sth->execute();
+ while (my $column = $info_sth->fetchrow_hashref) {
+ # If this particular column isn't stored in utf-8
+ if ($column->{Collation} ne 'NULL'
+ && $column->{Collation} !~ /utf8/)
+ {
+ my $name = $column->{Field};
+
+ # The code below doesn't work on a field with a FULLTEXT
+ # index. So we drop it. The upgrade code will re-create
+ # it later.
+ if ($table eq 'longdescs' && $name eq 'thetext') {
+ $self->bz_drop_index('longdescs',
+ 'longdescs_thetext_idx');
+ }
+ if ($table eq 'bugs' && $name eq 'short_desc') {
+ $self->bz_drop_index('bugs', 'bugs_short_desc_idx');
+ }
+
+ print "Converting $table.$name to be stored as UTF-8...\n";
+ my $col_info =
+ $self->bz_column_info_real($table, $name);
+
+ # CHANGE COLUMN doesn't take PRIMARY KEY
+ delete $col_info->{PRIMARYKEY};
+
+ my $sql_def = $self->_bz_schema->get_type_ddl($col_info);
+ # We don't want MySQL to actually try to *convert*
+ # from our current charset to UTF-8, we just want to
+ # transfer the bytes directly. This is how we do that.
+
+ # The CHARACTER SET part of the definition has to come
+ # right after the type, which will always come first.
+ my ($binary, $utf8) = ($sql_def, $sql_def);
+ my $type = $self->_bz_schema->convert_type($col_info->{TYPE});
+ $binary =~ s/(\Q$type\E)/$1 CHARACTER SET binary/;
+ $utf8 =~ s/(\Q$type\E)/$1 CHARACTER SET utf8/;
+ $self->do("ALTER TABLE $table CHANGE COLUMN $name $name
+ $binary");
+ $self->do("ALTER TABLE $table CHANGE COLUMN $name $name
+ $utf8");
+ }
+ $self->do("ALTER TABLE $table DEFAULT CHARACTER SET utf8");
+ }
+ } # foreach my $table (@tables)
+
+ my $db_name = Bugzilla->localconfig->{db_name};
+ $self->do("ALTER DATABASE $db_name CHARACTER SET utf8");
+ }
+}
+
+sub bz_db_is_utf8 {
+ my $self = shift;
+ my $db_collation = $self->selectrow_arrayref(
+ "SHOW VARIABLES LIKE 'character_set_database'");
+ # First column holds the variable name, second column holds the value.
+ return $db_collation->[1] =~ /utf8/ ? 1 : 0;
}
diff --git a/Bugzilla/DB/Schema.pm b/Bugzilla/DB/Schema.pm
index 6846691e2..c2c884e6d 100644
--- a/Bugzilla/DB/Schema.pm
+++ b/Bugzilla/DB/Schema.pm
@@ -1302,7 +1302,7 @@ sub get_type_ddl {
}
my $fkref = $self->{enable_references} ? $finfo->{REFERENCES} : undef;
- my $type_ddl = $self->{db_specific}{$type} || $type;
+ my $type_ddl = $self->convert_type($type);
# DEFAULT attribute must appear before any column constraints
# (e.g., NOT NULL), for Oracle
$type_ddl .= " DEFAULT $default" if (defined($default));
@@ -1313,7 +1313,19 @@ sub get_type_ddl {
return($type_ddl);
} #eosub--get_type_ddl
-#--------------------------------------------------------------------------
+
+sub convert_type {
+
+=item C<convert_type>
+
+Converts a TYPE from the L</ABSTRACT_SCHEMA> format into the real SQL type.
+
+=cut
+
+ my ($self, $type) = @_;
+ return $self->{db_specific}->{$type} || $type;
+}
+
sub get_column {
=item C<get_column($table, $column)>
@@ -1383,7 +1395,12 @@ sub get_table_columns {
return @columns;
} #eosub--get_table_columns
-#--------------------------------------------------------------------------
+
+sub get_create_database_sql {
+ my ($self, $name) = @_;
+ return ("CREATE DATABASE $name");
+}
+
sub get_table_ddl {
=item C<get_table_ddl>
diff --git a/Bugzilla/DB/Schema/Mysql.pm b/Bugzilla/DB/Schema/Mysql.pm
index 0069dcc7f..d7cd708a2 100644
--- a/Bugzilla/DB/Schema/Mysql.pm
+++ b/Bugzilla/DB/Schema/Mysql.pm
@@ -129,7 +129,9 @@ sub _get_create_table_ddl {
my($self, $table) = @_;
- return($self->SUPER::_get_create_table_ddl($table) . ' TYPE = MYISAM');
+ my $charset = Bugzilla->dbh->bz_db_is_utf8 ? "CHARACTER SET utf8" : '';
+ return($self->SUPER::_get_create_table_ddl($table)
+ . " ENGINE = MYISAM $charset");
} #eosub--_get_create_table_ddl
#------------------------------------------------------------------------------
@@ -150,6 +152,16 @@ sub _get_create_index_ddl {
} #eosub--_get_create_index_ddl
#--------------------------------------------------------------------
+sub get_create_database_sql {
+ my ($self, $name) = @_;
+ # We only create as utf8 if we have no params (meaning we're doing
+ # a new installation) or if the utf8 param is on.
+ my $create_utf8 = Bugzilla->params->{'utf8'}
+ || !defined Bugzilla->params->{'utf8'};
+ my $charset = $create_utf8 ? "CHARACTER SET utf8" : '';
+ return ("CREATE DATABASE $name $charset");
+}
+
# MySQL has a simpler ALTER TABLE syntax than ANSI.
sub get_alter_column_ddl {
my ($self, $table, $column, $new_def, $set_nulls_to) = @_;