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 
each columns. 

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

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 



Reply via email to