tl;dr: I think it's time to add proper Unicode support to the DBI. What do you
think it should look like?
I've brought this up a time or two in the past, but a number of things have
happened lately to make me think that it was again time:
First, on the DBD::Pg list, we've been having a discussion about improving the
DBD::Pg encoding interface.
That design discussion followed on the extended discussion in this bug report:
Seems that the pg_enable_utf8 flag that's been in DBD::Pg for a long time is
rather broken in a few ways. Notably, PostgreSQL sends *all* data back to
clients in a single encoding -- even binary data (which is usually
hex-encoded). So it made no sense to only decode certain columns. How to go
about fixing it, though, and adding a useful interface, has proven a bit tricky.
Then there was Tom Christiansen's StackOverflow comment:
This made me realize that Unicode handling is much trickier than I ever
realized. But it also emphasized for me how important it is to do everything on
can to do Unicode right. Tom followed up with a *lot* more detail in three
OSCON presentations this year, all of which you can read here:
(You're likely gonna want to install the fonts linked at the bottom of that
page before you read the presentations in HTML).
And finally, I ran into an issue recently with Oracle, where we have an Oracle
database that should have only UTF-8 data but some row values are actually in
other encodings. This was a problem because I told DBD::Oracle that the
encoding was Unicode, and it just blindly turned on the Perl utf8 flag. So I
got broken data back from the database and then my app crashed when I tried to
act on a string with the utf8 flag on but containing non-unicode bytes. I
reported this issue in a DBD::Oracle bug report:
But all this together leads me to believe that it's time to examine adding
explicit Unicode support to the DBI. But it needs to be designed as carefully
as possible to account for a few key points:
* The API must be as straightforward as possible without sacrificing necessary
flexibility. I think it should mostly stay out of users ways and have
reasonable defaults. But it should be clear what each knob we offer does and
how it affects things. Side-effects should be avoided.
* Ability to enforce the correctness of encoding and decoding must be given
priority. Perl has pretty specific ideas about is and is not Unicode, so we
should respect that as much as possible. If that means encoding and decoding
rather than just flipping the utf8 bit, then fine.
* The performance impact must be kept as minimal as possible. So if we can get
away with just flipping the UTF-8 bit on and off, it should be so. I'm not
entirely clear on that, though, since Perl's internal representation, called
"utf8", is not the same thing as UTF-8. But if there's an efficient way to
convert between the two, then it should be adopted. For other encodings,
obviously a full encode/decode path must be followed.
* Drivers must be able to adopt the API in a straight-forward way. That is to
say, we need to make sure that the interface covers what most (all?) drivers
need. Some, like DBD::Pg, can specify that only one encoding come back from the
database. Maybe others (DBD::mysql) can have individual columns in different
encodings? It needs to cover that case, too.
* It must be able to give the drivers some flexibility. Where we can't account
for everything that all drivers need forever, we should make it possible for
them to add what they need without changing the overall API or the meaning of
the interfaces provided by the DBI.
I'm not at all clear what such an API should look like. Based on my extensive
experience with DBD::Pg, a fair amount of experience with DBD::SQLite, and
limited experience with DBD::Oracle and DBD::mysql, I'd say it'd be useful to
have at least these knobs:
1. An attribute indicating the database encoding. This is the encoding one
expects all data coming from the database to be in. When this is set, the DBI
or the driver would decode incoming data to Perl's internal format and encode
data sent to the database.
2. A fourth param to bind_param() to indicate the encoding in which to send
column data to the database. Defaults to the database encoding.
3. A new parameter to prepare() to indicate the encodings of specific columns
to be selected.
4. An ENCODING attribute on statement handles that indicates the encoding of
This is just a preliminary proposal, but covers most of the basics, I think.
(I'm sure I'm suggesting the wrong places for some things). It does assume that
one wants one's text data to always be decoded into Perl's internal form and
encoded when sent to the database. There ought to be a way for one also to just
continue to get binary data and encode and decode one's self (e.g. for actual
I know that Tim Bunce has thought about this some in the past, and Greg Sabino
Mullane and I have discussed it quite a lot with Dave Rolsky and others. So I
know that folks have some ideas about this stuff. So let's hear them. Let's put
our minds together and try to come up with an interface that we can all work