SQLite DLL with ICU - support multilingual LIKE statements and ORDER BY
Petros Diveris
Following my previous posts with regards to upper and lower case
problems I had with the sqlite3 version included in the python 2.5
(and therefore web2py) distribution, and my promise to resove this,
I'd like to announce that I have finally managed to compile
sqlite3.dll and the necessary ICU support files and make them
available. I have used MSVC2008 express and to my knowledge, no
support DLLs are required - it's all self contained.
The versions compiled are sqlite3.dll 3.8.6 and ICU 3.8.1. Please read
the instructions and get the files here http://www.urban-eye.com/pagesqliteicu.html.
You will need to overwrite the sqlite3.dll included in the web2py
folder and also include the the icu*.dll files. Please note that I
don't know how well or even if this works with Chinese or Russian -
have only tested German, Greek and English. Please also note that I
cannot guarantee the integrity of your data - please make backups and
test thoroughly!
mdipierro
Massimo
On Jan 13, 1:31 pm, Petros Diveris <pdive...@gmail.com> wrote:
> Hi,
>
> Following my previous posts with regards to upper and lower case
> problems I had with the sqlite3 version included in the python 2.5
> (and therefore web2py) distribution, and my promise to resove this,
> I'd like to announce that I have finally managed to compile
> sqlite3.dll and the necessary ICU support files and make them
> available. I have used MSVC2008 express and to my knowledge, no
> support DLLs are required - it's all self contained.
>
> The versions compiled are sqlite3.dll 3.8.6 and ICU 3.8.1. Please read
Timothy Farrell
much everything these days.
ICU is under an MIT-like license.
http://source.icu-project.org/repos/icu/icu/trunk/license.html
Timothy Farrell <tfar...@swgen.com>
Computer Guy
Statewide General Insurance Agency (www.swgen.com)
mdipierro
please let me know and I will upgrade the "official" windows binary
distribution.
massimo
Petros Diveris
"Permission is hereby granted, free of charge, to any person obtaining
a copy of this software and associated documentation files (the
"Software"), to deal in the Software without restriction, including
without limitation the rights to use, copy, modify, merge, publish,
distribute, and/or sell copies of the Software, and to permit persons
to whom the Software is furnished to do so, provided that the above
copyright notice(s) and this permission notice appear in all copies of
the Software and that both the above copyright notice(s) and this
permission notice appear in supporting documentation. "
The sqlite license states that
"All of the deliverable code in SQLite has been dedicated to the
public domain by the authors. All code authors, and representatives of
the companies they work for, have signed affidavits dedicating their
contributions to the public domain and originals of those signed
affidavits are stored in a firesafe at the main offices of Hwaci.
Anyone is free to copy, modify, publish, use, compile, sell, or
distribute the original SQLite code, either in source code form or as
a compiled binary, for any purpose, commercial or non-commercial, and
by any means. "
Based on the above I presume that there aren't any licensing issues.
My compiling and linking the code shouldn't really have an effect on
the license or the distribution of the binaries.
Petros
Markus Gritsch
>
> The versions compiled are sqlite3.dll 3.8.6 [...]
You probably mean 3.6.8. Can you please rebuild using version 3.6.9
since 3.6.8 contains an obscure bug:
On Wed, Jan 14, 2009 at 5:12 AM, D. Richard Hipp <d...@hwaci.com> wrote:
> Internal stress testing revealed an obscure bug in the SQLite query
> optimizer of version 3.6.8. Since that release has only been out for
> two days, and since the fix is simple, we thought it best to go ahead
> and do another release that patches the problem. SQLite version 3.6.9
> is now available on the SQLite website:
>
> http://www.sqlite.org/
>
> D. Richard Hipp
Kind regards,
Markus
Markus Gritsch
> support DLLs are required - it's all self contained.
Python 2.5 is compiled using MS Visual Studio.NET 2003. (The compiler
has the version 13.10 and the runtime libraries for C and C++ are
msvcr71.dll and msvcp71.dll, respectively). Since Python is only C is
only needs msvcr71.dll.
Your builts of sqlite and ICU depend on MSVCR90.DLL (I checked using
http://www.dependencywalker.com/ which shows, beside other things, the
DLLs an EXE or DLL depends on).
IMO there should only be used one runtime library. Problems may arise
having two stacks for example. Either you should recompile using VC
2003, or the Windows binary of web2py should switch to Python 2.6
which is also compiled with MSVC2008.
Kind regards,
Markus
Markus Gritsch
also note, that the unicode-aware LIKE operator does not come for
free. A simple
SELECT * FROM entry WHERE note LIKE '%test%'
on a moderately large DB of about 100 MB of data is more than three
times slower using the ICU extension than without it.
1.14 sec with sqlite3.dll which ships with Python 2.5
3.52 sec with your compiled sqlite3.dll and the icu*.dlls
One should take this (and the currently different MSVCR DLLs) into
account before including it into the official Windows binary
distribution of web2py. I think it depends on the situation if it is
worth the benefit of an unicode aware LIKE operator.
Kind regards,
Markus
Petros Diveris
While I am open to looking at ways of removing the MSVCR90.DLL (and
also recompiling using sqlite 3.6.9), I **suspect** at the same time
that more people are interested in multi-lingual support than
performance? What I mean is, if people really want performance then, I
would imagine, they would be looking at a custom build of sqlite? Then
again, perhaps it's the other way round - there is also the question
of how do we go about keeping this up to date...
In the process of doing all this work, I also compiled, separately, an
sqlite extension called sqliteicu.dll which one should be able to load
as follows:
.load './sqliteicu.dll'
select upper('Τρία πουλάκια κάθονταν και πλέκανε πουλόβερ..');
I haven't had the chance to test this. In fact, I don't know how to
load an sqlite extension from Python and web2py. However, an optional
extension, compiled without the MSVCR90.DLL dependency might be the
best way to deal with this since its the best of both worlds, an
officially maintained sqlite3.dll and an optional extension - if it
works that is!
Markus Gritsch
Miloš Šabík
Dňa utorok, 13. januára 2009 20:31:32 UTC+1 Petros Diveris napísal(-a):