How to use predefined SQLite indexes
lazarus / 16-Databases

----------------------------------------------------------------------
JanRoza · 2016-01-07 23:44

In SQLite I have defined some indexes but how do I use these in my
application?
Is there something like 'SET INDEX TO <indexname>' that I can use in
the SQL text?
I want to trigger these indexes via the titleclick of a dbgrid but
have no idea what action to use for activating the predefined index.

----------------------------------------------------------------------
mangakissa · 2016-01-08 08:16

Indexes in databases are serversite. If you looking a record with an
id, the index file of this table is called instead of sequential
looking into the table (if field id is primary). If you create an
index for example nicknames of your table and try to find all
nicknames requiring 'A' it looks like
Code: [Select]SELECT * FROM table WHERE nicknames like 'A%';
The engine of the database knows there's a index of the table and can
search quickly to index file, which is sorted.

http://www.greatis.com/delphicb/tips/lib/databases-sortgrid.html

Most T()Query components has the property indexfieldnames. This
property creates internally an index on the filled in fieldname(s). So
changing the index on klicking the title, you have to change the
indexfieldname.

----------------------------------------------------------------------
JanRoza · 2016-01-08 12:31

Thanks mangakissa,

I've sold it slightly different by doing the following:

Code: Pascal  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i   if
Column.FieldName = 'Datum'  then DataModule1.qryOnderhoud.SQL.Text :=
'SELECT * FROM Onderhoud ORDER BY Datum,SortRegistratie ASC'  else if
Column.FieldName = 'Registratie'       then
DataModule1.qryOnderhoud.SQL.Text := 'SELECT * FROM Onderhoud ORDER BY
SortRegistratie,Datum ASC'       else if Column.FieldName = 'Naam'   
       then DataModule1.qryOnderhoud.SQL.Text := 'SELECT * FROM
Onderhoud ORDER BY Naam,Datum ASC'            else if Column.FieldName
= 'DossierNr'                 then DataModule1.qryOnderhoud.SQL.Text
:= 'SELECT * FROM Onderhoud ORDER BY DossierNr ASC';  
DataModule1.qryOnderhoud.ExecSQL;  DataModule1.qryOnderhoud.Active :=
True; 
Only problem is that the grid looses the focus on the record that was
selected.
I tried to solve it by using bookmarks but that only resulted in
keeping the selector on the same line in the grid but in that line is
now shown a different record.
So I'm still trying to perfect the bookmark solution.
I will also experiment with the indexfieldnames property to see if
that will do the trick.

----------------------------------------------------------------------
teos · 2016-01-09 22:25

Jan,

I would store the ID field in a variable and use locate to find the ID
after opening the dataset. That points the dataset and the grid back
on the right record.

----------------------------------------------------------------------
JanRoza · 2016-01-10 11:25

I already use the ID field in my locates, that's not the problem. My
problem is how to use predefined SQLite indexes in my application for
sorting purposes not for locate purposes.

----------------------------------------------------------------------
teos · 2016-01-10 12:22

You say your grid loses focus on the record and bookmarks don't work
(offcourse: a bookmark is lost when you re-fetch data). So my
suggestion is to search for the record in the dataset after loading
it.

I would suggest an in-memory dataset for this where sorting is done
locally. That prevents re-loading data multiple times and it keeps
your bookmarks valid.

----------------------------------------------------------------------
mangakissa · 2016-01-11 08:03

That's why indexfieldnames is yeasier to use.