David, I forgot to answer your post first and ended up putting most of my
comments in a reply to Greg's posting - sorry, it was a long night last night.
Some further comments below:
On 21/09/11 19:44, David E. Wheeler wrote:
On Sep 10, 2011, at 3:08 AM, Martin J. Evans wrote:
I'm not sure any change is required to DBI to support unicode. As
far as I'm aware unicode already works with DBI if the DBDs do the
right thing.
Right, but the problem is that, IME, none of them do "the right
thing." As I said, I've submitted encoding-related bug reports for
every DBD I've used in production code. And they all have different
interfaces for tweaking things.
So it is these "different interfaces for tweaking things" you'd like to
standardise I presume.
If you stick to the rule that all data Perl receives must be
decoded and all data Perl exports must be encoded it works
(ignoring any issues in Perl itself).
Er, was there supposed to be a ", then …" statement there?
I bow to Tom's experience but I'm still not sure how that applies
to DBI so long as the interface between the database and Perl
always encodes and decodes then the issues Tom describes are all
Perl ones - no?
The trouble is that:
1. They don't always encode or decode 2. When they do, the tend to
get subtle bits wrong 3. And they all have different interfaces and
philosophies for doing so
Surely Oracle should return the data encoded as you asked for it
and if it did not Oracle is broken. I'd still like to see this case
and then we can see if Oracle is broken and if there is a fix for
it.
Oh I don't doubt that Oracle is broken.
In some places DBD::Oracle does sv_utf8_decode(scalar) or
SvUTF8_on(scalar) (depending on your Perl) and in some places it
just does SvUTF8_on(scalar). I believe the latter is much quicker
as the data is not checked. Many people (myself included) are
particularly interested in DBD::Oracle being fast and if all the
occurrences were changed to decode I'd patch that out in my copy as
I know the data I receive is UTF-8 encoded.
IME It needs an "assume Oracle is broken" knob. That is, I should
have the option to enface encoding and decoding, rather than just
flipping SvUTF8. And I think that such an interface should be
standardized in the DBI along with detailed information for driver
authors how how to "get it right."
ok, I get that.
See above. I'd like the chance to go with speed and take the
consequences rather than go with slower but know incorrect UTF-8 is
spotted.
And maybe that's the default. But I should be able to tell it to be
pedantic when the data is known to be bad (see, for example data from
an SQL_ASCII-encoded PostgreSQL database).
I thought UTF-8 when used in Perl used the strict definition and
utf-8 used Perl's looser definition - see
http://search.cpan.org/~dankogai/Encode-2.44/Encode.pm#UTF-8_vs._utf8_vs._UTF8
That's right. So if I want to ensure that I'm getting strict encoding
in my database, It needs to encode and decode, not simply flip
SvUTF8.
Don't DBDs do this now? I know the encoding of the data I receive
in DBD::ODBC and decode it when I get it and encode it when I send
it and I believe that is what DBD::Oracle does as well. There is
one exception in ODBC for drivers which don't truly abide by ODBC
spec and send 8 bit data back UTF-8 encoded (see later).
There is no single API for configuring this in the DBI, and I argue
there should be.
I've spent a lot of effort getting unicode working in DBD::ODBC
(for UNIX and with patches from Alexander Foken for Windows) which
is implemented in an awkward fashion in ODBC. I'd like to hear from
DBD authors what support they already have and how it is
implemented so we can see what ground is already covered and where
the problems were.
DBD::Pg's approach is currently broken. Greg is working on fixing it,
but for compatibility reasons the fix is non-trivial (an the API
might be, too). In a perfect world DBD::Pg would just always do the
right thing, as the database tells it what encodings to use when you
connect (and *all* data is encoded as such, not just certain data
types). But the world is not perfect, there's a lot of legacy stuff.
Greg, care to add any other details?
as I remain unconvinced a problem exists other than incorrectly
coded DBDs. I'm happy to collate that information. As a start I'll
describe the DBD::ODBC:
1. ODBC has 2 sets of APIs, SQLxxxA (each chr is 8 bits) and
SQLxxxW (each chr is 16 bits and UCS-2). This is how Microsoft did
it and yes I know that does not support all of unicode but code
pages get involved too.
2. You select which API you are using with a macro when you compile
your application so you cannot change your mind. You can in theory
call SQLxxxA or SQLxxxW functions directly but if you use SQLxxx
you get the A or W depending on what the macro is set to. Problem:
DBD::ODBC has to built one way or the other.
3. When using the SQLxxxA functions you can still bind
columns/parameters as wide characters but the ODBC driver needs to
support this.
4. When using SQLxxxW functions all strings are expected in UCS-2.
You can bind columns and parameters as whatever type you like but
obviously if you bind a unicode column as SQLCHAR instead of
SQLWCHAR you probably get the wrong data back.
5. If you write a unicode ODBC application using SQLxxxW functions
and you connect to an ODBC driver which does not support SQLxxxW
functions the ODBC driver manager drops the high byte and passes
the data to the drivers SQLxxxA function and similarly the other
way around if the app uses SQLxxxA fns but the driver supports
SQLxxxW fns the ODBC driver manager converts the 8 bit data to
UCS-2 and calls the SQLxxxW fns in the driver. The driver manager
does not get involved in conversions on bound column or parameter
data.
Wow. I'd want an exception in such a situation, personally.
In reality, no one with any sense runs a non-unicode enabled application
against a unicode database or one that has unicode in it so I doubt it matters.
Problem: the non-windows driver managers have implemented unicode
support in this respect differently and I do not support them all
when built for unicode. unixODBC does what Microsoft does -
supported.
http://search.cpan.org/~mjevans/DBD-ODBC-1.31/ODBC.pm#Unicode
describes what unixODBC does and how you can avoid some
conversions. iODBC uses wchar_t types (not supported). DataDirect
ODBC driver manager uses UTF-8 (which has other problems in ODBC -
see http://www.martin-evans.me.uk/node/20#unicode) and is
unsupported when built for unicode but because this driver uses
UTF-8 you can use unicode when DBD::ODBC is not built for unicode.
6. usernames, passwords, column names, table names, SQL, DSNs etc
can all be unicode. Problem: DBI did not used to support supplying
a scalar to the connect method - it was a char *. I changed DBI
ages ago (1.607) to add another connect method to pass scalar
username, password, DSN string to support unicode in connect
parameters. Fortunately the prepare method already passed a
scalar. I don't think there are problems with the other DBI methods
as I think they all use scalars and not char *.
Excellent. Musta been a bitch to track that down.
7. When DBD::ODBC is built for unicode (the default on Windows and
an option on other platforms but not the default) DBD::ODBC has to
convert all that UCS-2 data to UTF-8 and vice versa. Problem: this
is time consuming so the code to do this is included in DBD::ODBC
and written in C using either Windows API calls for Windows and
code included with DBD::ODBC for other platforms.
Yeah, that's reasonable.
8. DBD::ODBC does not support unicode parameter names. You cannot
do:
select * from table where column = :unicode_param_name
9. you need at least Perl 5.8.1 as it is fundamentally broken
before then.
10. When using the wide APIs in ODBC some drivers will report
different column types for columns. For instances when listing
tables, columns etc some drivers (e.g., Microsoft SQL Server) will
report the column types as wide types even if the strings actually
fit in 7-bit ASCII.
11. Some ODBC drivers do not support the SQLxxxW APIs but can
return data UTF-8 encoded (e.g., PostgreSQL). DBD::ODBC has no idea
the data is UTF-8 encoded so someone sent me a patch to add the
odbc_utf8_on parameter which tells DBD::ODBC all data is UTF-8
encoded.
Yeah, that's the kind of interface I'm talking about. DBD::Pg calls a
similar (but flawed) parameter pg_enable_utf8. DBD::SQLite calls it
sqlite_unicode.
right.
12. To signal to Perl data is UTF-8 DBD::ODBC does:
#ifdef sv_utf8_decode sv_utf8_decode(sv); #else SvUTF8_on(sv);
#endif
I believe the former does check the encoding, the latter does not
but not all Perls have the former.
Makes sense. Would be nice to have the option to turn off the first
item for performance reasons (see note from Lyle in this thread).
I use DBD::Oracle and DBD::ODBC a lot and I know you can send and
receive unicode data reliably because both those DBDs decode/encode
data from/to the database. In fact, for Oracle we have masses of
unicode data here in all sorts of languages including Chinese and
it is working fine.
Yeah, it works fine, I think we have some decent 80-90% solutions.
It's the lack of consistency in the interface and rules between DBDs
that's unfortunate.
I guess I just don't see what is broken in DBI as it stands
although I'm not ruling out it might be able to do a little more to
help a DBD.
It's not broken. The problem is every driver does it differently and
none of them does it quite right. There need to be some standards on
this, IMHO. That's why I was suggesting interface additions to the
DBI in my OP. If you don't accept that such things should be
standardized, that's an discussion we can have. The rest is
implementation details.
Best,
David
So we need to define what the options are? Perhaps some of the ones I put in
answer to Greg's email.
Martin
--
Martin J. Evans
Easysoft Limited
http://www.easysoft.com
