mORMot Open Source

imperyal

Member

Registered: 2018-10-11

Posts: 54

Unexpect query results after REINDEX

Hello!

I'm experiencing a very strange problem..
First I started noticing that I was getting some extra results that shouldn't be on a particular table, they where deleted some time ago.
I did a Vacuum on SQLiteStudio, but the problem persisted. Then I did a Reindex (also on SQLiteStudio), the particular query started returning the correct results on SQLiteStudio but now, that same query returns no results using the ExecuteList function.

Then, when I add new records to that table I will get some results via mORMot (ExecuteList) and others on SQLiteStudio, both start missing some records... Very strange!

Is there any incompatibility when Reindex is ran?
Is there a way to do a reindex directly on the server using mORMot? (Ideally without stopping the server...)

Thank you!

ab

Administrator

From: France

Registered: 2010-06-21

Posts: 14,655

Website

Re: Unexpect query results after REINDEX

Isn't it a collation problem?

edwinsn

Member

Registered: 2010-07-02

Posts: 1,218

Re: Unexpect query results after REINDEX

I guess SQLiteStudio  cannot recognize some of the mORMot collations? I use SQLite Studio for querying only, never dare to perform actions  like that with it.

Last edited by edwinsn (2019-04-30 08:33:18)

---

Delphi XE4 Pro on Windows 7 64bit.
Lazarus trunk built with fpcupdelux on Windows with cross-compile for Linux 64bit.

oz

Member

Registered: 2015-09-02

Posts: 98

Re: Unexpect query results after REINDEX

Hi,

try to execute following 2 statements using your Rest-Connection or via SynDBExplorer:

pragma integrity_check;
reindex;

Do not use any other tools for write operations to your DB.

ab

Administrator

From: France

Registered: 2010-06-21

Posts: 14,655

Website

Re: Unexpect query results after REINDEX

The default text collation generated by the ORM is not a standard collation, but a full Unicode collation which is not supported by SQLite studio, unless you inherit e.g. from TSQLRecordNoCase or TSQLRecordCaseSensitive.
See the documentation of those classes.

Use our SynDBExplorer tool if you want to have consistent collation support with the ORM.

imperyal

Member

Registered: 2018-10-11

Posts: 54

Re: Unexpect query results after REINDEX

Hello! I did a reindex directly on the server and that solved all the issues I was having.

In Portugal we have all kinds of special characters and accents, and it seems TSQLRecordCaseSensitive does not support these.
I would prefer to have full compatibility with SQLite Studio, but I'm guessing it will not be possible. I was able to use SynDBExplorer and it works OK. It is not as polished as SQLite Studio of course.

Thank you very much for the replies, people in this forum are awesome.