Re: [pysqlite] Encoding for connect to SqLite3

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Dinesh B Vadhia wrote:
> The questions around Unicode, utf-8 etc. appear on a regular basis on
> the Python lists and on this list too. 

Indeed they do.  I wish every person asking a question had to read the
Joel Unicode article first.

> The Python language developers
> could have made this whole area much simpler by making Unicode/utf-8 the
> default and everything else exceptions. 

Unicode and UTF8 are not the same thing.  Unicode is about representing
characters (using that term very loosely as human languages are complex)
and does so using numeric "codepoints".  UTF8 is about representing
those codepoints as bytes.  Historically codepoints and bytes have been
the same thing especially when dealing with ASCII and especially in
other programming languages.

Most developers don't want to deal with having to distinguish between
strings and bytes and so see this unicode stuff as a burden, doing the
minimum necessary in order to get the code to appear to work.

In Python (<3) the default string type (str) is really just a sequence
of bytes and Unicode was grafted on as an additional type.  (To be fair
Python existed before Unicode did.)

In Python 3 the only string type available is Unicode and there is a
separate bytes type.  There are no implicit conversions between them.
You have to do conversions explicitly and state what the encodings are.
 The Python developers have effectively fixed the issue by doing this,
but had to break backwards compatibility which is why Python 3 was the
earliest it could be done.

> The default Python encoding is
> 'ascii' which is really where (imho) the problems begin because the
> developer is lulled into a false sense of wellness until you hit the
> various encode/decode errors. 

Using 'ascii' as the default encoding in Python 2 is the right thing in
my opinion as there is a fine line between doing the right thing as
Python 3 does and not breaking existing code.  It is the least worst
solution.

> It is relatively easy to grasp what Unicode and utf-8 are and how they
> work BUT it is not easy to grasp how to make Python programs
> Unicode/utf-8 compatible as there are multiple holes that have to be
> covered to ensure that what you are processing inside a Python program
> are Unicode/utf-8 strings.

Encodings are the least of your issues.  Even if you get the whole
Unicode side of things right, other ugly issues rear their head.  For
example to do sorting requires knowing the locale.  Some even have
different sorting methods.  For example Germany has a different way of
sorting if you are doing it for a phonebook versus for a dictionary.
And when you have text from multiple locales together, then what do you
do?  For example your German phone could have people with Swedish names
in it as well as German ones.  Do you sort those Swedish names using
German or Swedish rules?  There is a UCA - Unicode Collation Algorithm -
that tries to do a good job but again it forms the least worst solution.
 The page includes lots of good examples of just how complex this stuff is:

  http://unicode.org/reports/tr10/

Python itself is actually rather basic and messy as Chris Lenz writes:

http://www.cmlenz.net/archives/2008/07/the-truth-about-unicode-in-python

My pet annoyance is the compile time option of the internal
representation using 2 or 4 bytes for unicode codepoints.  It doubles
the amount of testing I have to do for APSW as well as makes the code
more complex.  I have to use SQLite UTF8 apis for 4 byte Python strings
but the SQLite UTF16 apis for 2 byte Python strings.  (Ok I don't have
to but that is most effective.)

There are two further issues with relation to SQLite.  Firstly pysqlite
lets you fight the system and put garbage into/out of the database.
This made sense for SQLite 2 which strictly speaking was using bytes not
strings.  This gave developers enough rope to hang themselves.  In APSW
you cannot do this.

A secondary issue that SQLite does not verify that what is provided to
its APIs is actually valid UTF8, so users of the API can provide random
sequences of bytes instead.  It is debateable if the API should incur
extra overhead to defend against bad programming practise.  (Again in
APSW you cannot do this.)

Consider yourself lucky that you don't see what happens on the SQLite
mailing list.  Every month or so someone discovers that SQLite only pays
attention to ASCII characters when sorting (others are just sorted as
bytes) and this breaks on pretty much any string that contains non-ASCII
characters.  This behaviour is clearly documented and not a secret.
SQLite does let you register collations.  It also has an extension for
ICU which will give (more) correct results.  Then they'll propose some
simple allegedly space efficient mechanism that is faster and lighter
than ICU claiming ICU too big and slow.  Of course anything less than
ICU will be better than just ASCII but give pretty poor results except
maybe in a few trivial cases.  Debate proceeds around this eventually
fizzling out until the next month.

> I think that the core Python documentation set should include one on
> characters, character sets, Unicode and utf-8 and provide (Python) usage
> examples of the most common cases.

File a bug report against Python.  Documentation is one area that I
believe Python does badly because of how it can be updated.
Historically it required using Tex based tools (I believe) and was done
"cathedral" style.  Sphinx and RST is a lot nicer but there are still
barriers.  In my opinion PHP got it right - on any page on the doc you
can add a comment which others can see and can be absorbed into future
copies of the doc.  There have been various attempts to help making
updates to Python's doc as easy as PHP but none seem to have succeeded.
 The lower the barriers, the better the doc will be.

Roger
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.9 (GNU/Linux)

iEUEARECAAYFAkpJO1AACgkQmOOfHg372QQAnACYii+EfyHCz/X85IsH1h/tOLYl
JgCeP6nCJcIO+zrEHn6J/l0Kn1TAGnQ=
=EVLn
-----END PGP SIGNATURE-----