<div dir="ltr"><div class="gmail_default" style="font-family:arial,helvetica,sans-serif;font-size:small"><font><span style="font-family:courier new,monospace">package MyApp::Schema::Loader::DBI::mysql::ExtraColumnInfo;<br>
use strict;<br>use warnings;<br>use base 'DBIx::Class::Schema::Loader::DBI::mysql';<br>use mro 'c3';<br><br>my $mysql_text_types = qr/^(?: var(?:char)? | char(?:acter)? | (?:tiny|medium|long)?text )$/msxi;<br>
<br>sub new {<br> return shift->next::method( @_ );<br>}<br><br>sub _extra_column_info {<br> my ( $self, $table, $col, $info, $dbi_info ) = @_;<br><br> my $extra_info = $self->next::method( $table, $col, $info, $dbi_info );<br>
<br> if ( $info->{data_type} =~ /$mysql_text_types/msxi ) {<br> my ( $character_set_name, $collation_name ) = eval {<br> $self->dbh->selectrow_array( qq{<br> SELECT character_set_name, collation_name<br>
FROM information_schema.columns<br> WHERE table_name = ? AND lower(column_name) = ?<br> }, {}, $table->name, lc( $col ) );<br> };<br><br> if ( my $has_information_schema = not $@ ) {<br>
$extra_info->{ extra }{ mysql_collate } = $collation_name;<br> $extra_info->{ extra }{ mysql_charset } = $character_set_name;<br> }<br> }<br><br> return $extra_info;<br>}<br><br>1;</span></font><br>
<br><br></div><div class="gmail_default" style="font-family:arial,helvetica,sans-serif;font-size:small">And include it in the schema loader options too:<br></div><div class="gmail_default" style="font-family:arial,helvetica,sans-serif;font-size:small">
<br><span style="font-family:courier new,monospace">loader_class => 'MyApp::Schema::Loader::DBI::mysql::ExtraColumnInfo'</span><br><br></div><div class="gmail_default" style="font-family:arial,helvetica,sans-serif;font-size:small">
It's actually reading the database where this information gets lost, it's never stored in the YAML generated by DBIx::Schema::Loader, so it won't be output properly either until it is added.<br><br></div><div class="gmail_default" style="font-family:arial,helvetica,sans-serif;font-size:small">
-- Lianna<br></div><div class="gmail_default" style="font-family:arial,helvetica,sans-serif;font-size:small"><br></div></div><div class="gmail_extra"><br><br><div class="gmail_quote">On 10 August 2014 15:32, Matija Grabnar <span dir="ltr"><<a href="mailto:matija@serverflow.com" target="_blank">matija@serverflow.com</a>></span> wrote:<br>
<blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex"><div class="im HOEnZb">I am developing a Catalyst application that uses DBIx::Class. I thought I would do things right and use DBIx::Class::DeploymentHandler right from the start.<br>
I was able to get it to deploy and upgrade my tables very nicely.<br>
However, I have run into a show stopper now:<br>
<br>
The character set settings of tables and strings are not preserved. That means that if I declare a table like this:<br>
<br>
create table graph_category (<br>
id serial primary key,<br>
category varchar(190) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,<br>
unique index i_graph_category_category(<u></u>category)<br>
) engine=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;<br>
<br></div><div class="im HOEnZb">
and then do<br>
dbicdump -o dump_directory=./lib \<br>
-o debug=1 \<br>
MyDB \<br>
'dbi:mysql:dbname=mydb' \<br>
someusername \<br>
notthepassword<br>
<br>
<br>
And then a script containing<br>
my $dh = DBIx::Class::<u></u>DeploymentHandler->new(<br>
{<br>
schema => $schema,<br>
script_directory => "$FindBin::Bin/../dbicdh",<br>
databases => 'MySQL',<br>
sql_translator_args => { add_drop_table => 1 },<br>
force_overwrite => $force_overwrite,<br>
}<br>
);<br>
<br>
$dh->prepare_install;<br>
$dh->install;<br>
<br>
The table in the database will become:<br>
<br></div><div class="HOEnZb"><div class="h5">
CREATE TABLE `graph_category` (<br>
`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,<br>
`category` varchar(190) DEFAULT NULL,<br>
PRIMARY KEY (`id`),<br>
UNIQUE KEY `i_graph_category_category` (`category`)<br>
) ENGINE=InnoDB DEFAULT CHARSET=latin1<br>
<br>
In other words, all the character set info has disappeared. Since the requirements specify a lot of UTF8 work, I find that quite distressing. And in production, I'm afraid it could lead to data corruption.<br>
<br>
Does anybody know of a way to avoid this problem (short of discarding DeploymentHandler altogether)?<br>
<br>
______________________________<u></u>_________________<br>
List: <a href="http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/dbix-class" target="_blank">http://lists.scsys.co.uk/cgi-<u></u>bin/mailman/listinfo/dbix-<u></u>class</a><br>
IRC: <a href="http://irc.perl.org#dbix-class" target="_blank">irc.perl.org#dbix-class</a><br>
SVN: <a href="http://dev.catalyst.perl.org/repos/bast/DBIx-Class/" target="_blank">http://dev.catalyst.perl.org/<u></u>repos/bast/DBIx-Class/</a><br>
Searchable Archive: <a href="http://www.grokbase.com/group/dbix-class@lists.scsys.co.uk" target="_blank">http://www.grokbase.com/group/<u></u>dbix-class@lists.scsys.co.uk</a><br>
</div></div></blockquote></div><br></div>