[yunqa.de] Re: Integrity check bug?

  • From: "Clyde" <clyde@xxxxxxxx>
  • To: yunqa@xxxxxxxxxxxxx
  • Date: Mon, 17 Aug 2009 09:25:35 +0800

>This looks like a collating sequence problem. Does your database use
>custom collation sequences which use the Windows API for string

>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.

>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.

>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]


Delphi Inspiration mailing list

Other related posts: