[yunqa.de] Re: Integrity check bug?
- From: Delphi Inspiration <delphi@xxxxxxxx>
- To: yunqa@xxxxxxxxxxxxx
- Date: Mon, 17 Aug 2009 12:12:57 +0200
At 03:25 17.08.2009, Clyde wrote:
>>Please know that if the definition of a collation sequence that you have
>>used to create an index varies even slightly from locale to locale
>>(English to Swedish) or machine to machine (win98 to windows vista), you
>>are headed for database corruption.
>
>Yes, I am using one of the collations supported in SQLiteSpy - "SystemNocase".
>Funny enough, I came to this conclusion just before you wrote your reply. This
>seems to be a big "gotcha" that doesn't have any reference in the help file
>when collations are mentioned.
Nice to read that were able to identify the problem!
>>If this applies to your application and database, please exchange the
>>Windows-based collation function with an application based one.
>
>Yea, I already do have such a collation. Unfortunately by using a custom
>collation in the table definition virtually makes my tables "proprietary" and
>can therefore only be used in my application. I noticed you support the
>"SystemNocase" collation in SQLiteSpy so I used that in my application (it
>behaved similar to my custom one I wanted for my application) and this meant
>my users could then also open the tables for testing in SQLiteSpy. I wasn't
>aware of the ramifications of different locals causing index corruption of the
>database with that collation, so thanks for setting me right on that score.
>
>The fact that using custom collations in table definitions makes the tables
>some what "proprietary" is why I had asked you previously if there was some
>mechanism for SQLiteSpy to support user built custom collations, but sadly
>your answer was no. I guess I just have to live with that.
Along the lines of cross platform portability, the built-in vs. proprietary
collation function was discussed on the SQLite mailing list some time ago
(http://www.mail-archive.com/sqlite-users@xxxxxxxxxx/msg25597.html). The SQLite
developers and users basically agreed that full Unicode collation tables are
too huge to fit into the "light" SQLite3 library. Suggestions for standard
Unicode collation definitions within SQLite did not bear fruit so far.
>>Alternatively, you might want to ask your customers to reindex the
>>database and run PRAGMA integrity_check again.
>
>Thanks. That is another possibility I will look into. Just seems a shame that
>further processing has to be done on a database that for all intent and
>purposes is "static" or read only. It also means a fundamental change to my
>application as this file is installed with the application (default being
>"c:\program files). Windows Vista/7 doesn't like you changing files in this
>folder so I would then need to place in something like [application data]
I plan to appended the documentation of DISQLite3Collations.pas as below. It
will be part of the next version, due later today or tomorrow. Feel free to
comment.
Ralf
------------------
Unit DISQLite3Collations
Description
"System" and "User" custom collation callback compare functions.
Caution:
All collation callbacks in this unit use the Windows API for their actual
string comparisons (CompareStringA and CompareStringW). As it turned out, these
functions may yield different results accross different Windows versions (say,
Win98 and Vista) or user locales (say, English and Swedish).
On the other hand, DISQLite3 requires that collation callbacks always return
the exact same results. This means that if the collation sequence that you have
used to create an index varies even slightly from machine to machine, you are
headed for database corruption.
To be clear: The collation callbacks in this unit may not be portable accross
different machines if they host different versions of Windows and / or use
different system or user locales. In such cases, PRAGMA integrity_check; will
fail and indicate that the database will not function properly!
Workarounds:
1. REINDEX the the relevant indexes on the target machine prior to using the
database. This will re-create indexes using the target machines's collation. It
may, however, result in a different sorting orders and / or violate uniqueness
constraints.
2. Use SQLite3_Compare_Custom_UTF16LE() and specify the locale. This should at
least protect against different user or system locales, but not against
different Windows versions.
3. Use collation sequence independend of the operating system and locale. All
built-in collation sequences (BINARY, NOCASE, RTRIM) are guaranteed to be cross
machine as well as platform (Windows, Linux, etc).
_______________________________________________
Delphi Inspiration mailing list
yunqa@xxxxxxxxxxxxx
//www.freelists.org/list/yunqa
Other related posts:
