Handling UTF8-encoded text in SQLite using Rose::DB::Object

Question

I am using Rose::DB::Object, SQLite, and Chinese text. My classes look like this:

package My::DB;

use base qw(Rose::DB);

__PACKAGE__->use_private_registry;

__PACKAGE__->register_db(
  driver   => 'sqlite',
  database => 'data/sqmple.db',
);

package Motorcycle;

use My::DB;

use base qw(Rose::DB::Object); 
...
sub init_db { My::DB->new() };

The code used to store a record:

Motorcycle->new(
  type  => $self->param('type'),
  brand => $self->param('brand'),
  color => $self->param('color'),
)->save;

The code used to display the data (from within a Mojolicious app):

<td><%= Mojo::ByteStream->new($cycle->type)->decode("utf-8") %></td>
<td><%= Mojo::ByteStream->new($cycle->brand)->decode("utf-8") %></td>
<td><%= Mojo::ByteStream->new($cycle->color)->decode("utf-8") %></td>

How can I eliminate the decoding step? I'd like the display code to look like this instead:

<td><%= $cycle->type %></td>
<td><%= $cycle->brand %></td>
<td><%= $cycle->color %></td>

Answer 1

I think you need to get the sqlite_unicode => 1 configuration value down to SQLite, there was a similar question about UTF-8 and SQLite, setting sqlite_unicode did the trick there.

I don't think Rose::DB::SQLite supports this configuration parameter though. Based on this possibly similar issue with MySQL you might be able to patch Rose::DB::SQLite to add support for sqlite_unicode by adding this to the driver:

sub sqlite_unicode {
{
  shift->dbh_attribute_boolean('sqlite_unicode', @_)
}

I'll leave a comment on John's answer so he can sanity check this.

If that works then you might want to send a patch to John Siracusa (who is not only already on this question but also the Rose::DB maintainer).