Upper and lower case problems with sqllite, Greek / German and itemize / select

340 views
Skip to first unread message

Petros Diveris

unread,
Jan 2, 2009, 6:01:40 AM1/2/09
to web2py Web Framework
Hi everyone and happy neww year!

I have come across a very strange problem where I am trying to select
from an sqlite table with

db.babel.definition.upper().like("'%"+me.word.upper()+"%'") ) which
yields nothing.

The same happens if I try using sqlite's upper function

db.babel.definition.upper().like("upper('%"+me.word+"%')") )

which also yields nothing. The text I am trying to select is Greek -
it all works fine with English

The same sql statements work fine (they return rows) from native
sqlite clients (I haven't tried as yet from command line python.) Not
only it works fine with the native client, it even handles accented
characters etc. Same with German and umlauts...

Has anybody seen anything like this with international text?

I have even tried this so to avoid handling the keyword in Python

rows=db("upper(definition) like '%' || upper((select word from
babel where id="+str(me.id)+")) || '%'").select(db.babel.ALL)

assuming that something happens on the python side. One would expect
this to work since the whole string comparison should happen on the
sqlite side. It still doesn't handle uupper / lower case. Help!


mdipierro

unread,
Jan 2, 2009, 6:27:19 AM1/2/09
to web2py Web Framework
This

http://www.sqlite.org/lang_expr.html

says:

"A bug: SQLite only understands upper/lower case for 7-bit Latin
characters. Hence the LIKE operator is case sensitive for 8-bit
iso8859 characters or UTF-8 characters. For example, the expression
'a' LIKE 'A' is TRUE but 'æ' LIKE 'Æ' is FALSE."

perhaps it is possible to redefine upper using stored procedures...
but Python itself does not seem to support this either:

>>> a="\303\206"
>>> print a
Æ
>>> print a.lower()
Æ

If anybody has any suggestion I would like to know. If there is a
workaround in Python, we can use sqlite3_create_function.

Massimo

Petros Diveris

unread,
Jan 2, 2009, 10:35:20 AM1/2/09
to web2py Web Framework
Hi

Thanks for the quick reply. I was rather surprised to read on the
supplied link, I always assumed that sqlite would handle utf-8 upper /
lower case. What version is the sqlite bundled with web2py 1.54
windows?

Cheers

Petros Diveris

mdipierro

unread,
Jan 2, 2009, 10:42:05 AM1/2/09
to web2py Web Framework
I do not know. The same version that comes with python 2.5.

Massimo

Petros Diveris

unread,
Jan 9, 2009, 8:36:31 AM1/9/09
to web2py Web Framework
Ok,

It seems that the version of sqlite shipped with Python versions 2.5
to 3.0 for Windows has been compiled in a way as to not include what
the sqlite guys call the ICU extension. The ICU is a little extension
that links sqlite with the ICU library (installed separately), thus
allowing it to provide lower and upper case functions for the full
range of unicode characters, a unicode aware LIKE operator and ICU
collation sequences.

The readme file for ICU is here http://www.sqlite.org/cvstrac/fileview?f=sqlite/ext/icu/README.txt

I have spent many days now trying to get a windows binary
(sqlite3.dll) with the icu extensions. There are instructions on how
to do this here http://devmentor.org/articles/sqlite/sqlite.html but
they require ms visual studio. The open source alternative, using
mingw or cygwin, crashes somewhere half way through the compilation of
the ICU library itself (not the extension). If anybody wants to take
on the task of compiling ICU and sqlite with inrenational support and
make the dll available I would be grateful (and many other people
too.) and I would be willing to help maintain the distro and perhaps
host it.

If on the other hand all you want is international support in a web2py
project that runs off a usb stick (as I do) then you can install
SQLite Developer from http://www.sqlitedeveloper.com/. The installer
will also add a series of sqlite* and icu* related dlls. Simply
copying those over your web2py ones will do the trick (you get full
uncode sorting and like support etc.)

Warning! Should you wish to do so please a) make sure that you make a
backup of your web2py installation and b) you also copy the
Microsoft.VC80.CRT.manifest and msvcr80.dll files.

I haven't tried to get this done on linux or the mac, since I need to
ship a windows usb stick for this particular app. I have reasons to
believe though that building sqlite with ICU is much easier on a
decent linux distro and should be relatively easy on the mac with fink
and gcc.

mdipierro

unread,
Jan 9, 2009, 9:10:42 AM1/9/09
to web2py Web Framework
Interesting. I could use some help with this. Will the license allow
redistribution?

On Jan 9, 7:36 am, Petros Diveris <pdive...@gmail.com> wrote:
> Ok,
>
> It seems that the version of sqlite shipped with Python versions 2.5
> to 3.0 for Windows has been compiled in a way as to not include what
> the sqlite guys call the ICU extension. The ICU is a little extension
> that links sqlite with the ICU library (installed separately), thus
> allowing it to provide lower and upper case functions for the full
> range of unicode characters, a unicode aware LIKE operator and ICU
> collation sequences.
>
> The readme file for ICU is herehttp://www.sqlite.org/cvstrac/fileview?f=sqlite/ext/icu/README.txt
>
> I have spent many days now trying to get a windows binary
> (sqlite3.dll) with the icu extensions. There are instructions on how
> to do this herehttp://devmentor.org/articles/sqlite/sqlite.htmlbut
> they require ms visual studio. The open source alternative, using
> mingw or cygwin, crashes somewhere half way through the compilation of
> the ICU library itself (not the extension). If anybody wants to take
> on the task of compiling ICU and sqlite with inrenational support and
> make the dll available I would be grateful (and many other people
> too.) and I would be willing to help maintain the distro and perhaps
> host it.
>
> If on the other hand all you want is international support in a web2py
> project that runs off a usb stick (as I do) then you can install
> SQLite Developer fromhttp://www.sqlitedeveloper.com/. The installer

Petros Diveris

unread,
Jan 9, 2009, 9:24:17 AM1/9/09
to web2py Web Framework
Hi Massimo,

I don't think that the sharplus guys would like to distribute their
DLLs with an opensource package but I will ask them. On the other
hand, I am determined to get an ICU enabled version of sqlite compiled
with open source tools or at least linked with libraries that would
allow us to distribute the dll with we2py. I am going to work on it
this weekend. It is absolutely crucial for me since most projects I
work on are multilingual.

Petros
Reply all
Reply to author
Forward
0 new messages