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. On Jan 2, 3:42 pm, mdipierro <[email protected]> wrote: > I do not know. The same version that comes with python 2.5. > > Massimo > > On Jan 2, 9:35 am, Petros Diveris <[email protected]> wrote: > > > 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 > > > On Jan 2, 1:27 pm, mdipierro <[email protected]> wrote: > > > > 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 > > > > On Jan 2, 5:01 am, Petros Diveris <[email protected]> wrote: > > > > > 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! --~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "web2py Web Framework" group. To post to this group, send email to [email protected] To unsubscribe from this group, send email to [email protected] For more options, visit this group at http://groups.google.com/group/web2py?hl=en -~----------~----~----~----~------~----~------~--~---
