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

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>

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).

If you feed UTF8-encoded text into SQLite, it should give it right back to you in the same form. For example, given an SQLite database named test.db containing this schema:

CREATE TABLE things
(
  id   INTEGER PRIMARY KEY AUTOINCREMENT,
  name VARCHAR(64) NOT NULL
);

Run this code in a script in the same directory as the test.db database:

package My::DB;

use base qw(Rose::DB);

__PACKAGE__->use_private_registry;

__PACKAGE__->register_db
(
  driver   => 'sqlite',
  database => 'test.db',
);

package My::Thing;

use base qw(Rose::DB::Object); 

__PACKAGE__->meta->setup
(
  table   => 'things',
  columns =>
  [
    id   => { type => 'serial', primary_key => 1, not_null => 1 },
    name => { type => 'text', length => 64, not_null => 1 },
  ],
);

sub init_db { My::DB->new }

package main;

# Set the name to a UTF8-encoded smiley: Unicode 0x263A
my $thing = My::Thing->new(name => "\x{e2}\x{98}\x{ba}")->save; 

$thing = My::Thing->new(id => $thing->id)->load;

# This will print the UTF8-encoded smiley; make sure your
# terminal can handle UTF8 output.
print $thing->name, "\n";

If this is not working for you, then perhaps your calls to get the form parameters (e.g., $self->param('type')) are returning character strings instead of UTF8-encoded strings. That is, in the case of the smiley face string, perhaps $self->param('foo') is returning "\x{263a}" and not "\x{e2}\x{98}\x{ba}". In that case, the solution would be to encode the strings as UTF8 before setting the object attributes:

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