[SOLVED] SQLite3 REINDEX

[1HuntnMan]

Anyone know the correct syntax for REINDEX a SQLite3 table. Check out the attached diagram...
I have a maintenance form for reindexing tables in the DataBase and VACUUM the database.
I'm using SQLite3DataSet with TDataSource. It doesn't like this:

Code: Pascal  [Select][+][-]

1.     SQLite3DSCntks.Open;
2.     REINDEX.DSCntks.CONTACTS; // Reindex Contacts table
3.  

[JanRoza]

Why not do it via sql like:

<query>.active := false;
<query>.sql.text := 'REINDEX CONTACTS';
<query>.execsql;
<query>.sql.text := 'SELECT * FROM CONTACTS';
<query>.active := true;

[1HuntnMan]

Ok JanRoza, the form I designed for reindexing and vacuuming, I had just SQLite3DataSet's.
Would this work?
SQLite3DSCntks.ExecSQL('REINDEX CONTACTS');

Or, would I need to remove the SQLite3DataSets and replace with a TSQLQuery i.e. QryCntks.Open, QryCntks.SQL.Text:='REINDEX CONTACTS';, QryCntks.ExecSQL;

[JanRoza]

Just drop a query on your form and name it qryTemp or whatever you like and perform the sql action mentioned on that query. No need to change anything on your datasource.

[1HuntnMan]

Okay, that's cool!

[CharlyTango]

hm....

That's kind of DBF thinking.

One of the big advantages (and sometimes obstacles) is that i do not have to care about RE-indexing a sql server (and SQLite behaves as such one)

In contrast to DBF files, an SQLite has a lot of functions and intelligence to offer.

What you are dealing with is a DBMS (database management system), which requires a different way of thinking and handling data.
You pass a data request (SELECT ... FROM) to the DBMS and get a response. The DBMS itself decides on the best access path, you don't have to worry about that. Accordingly, you cannot usually decide which index the DBMS uses or not. The DBMS also takes care of updating the indices.

A clean table design with cleverly selected indices helps the DBMS to respond with high performance. But this is a matter for the DBMS and the topic of table design and has nothing to do with the database frontend.

This different way of thinking also has consequences, because when you fetch data from a DBMS, you practically never do it by loading an entire table into a grid. This would be the absolute exception, whereas it is more common to access a table by index when displaying DBF files.
It is therefore also good programming practice to offer the user a good search option with the aim of actually only having a handful of data in the grid from which the user can then select.

Only if errors actually occur would it be necessary to rebuild indices. In 35 years of programming, I have hardly done this more than 5 times, but then the problem was so big anyway that backups came into play

[1HuntnMan]

CharlyTango,
     That's exactly what I'm doing is converting a working app from DBF to SQLite3!  In this app when it was a good working app for a lot of folks written with DBF tables, it's a Contacts Mgt. app that friends and friends intall on their Winder's computers because Winders doesn't come with a Contacts app like the iPhone, iPad and Mac. Not sure about the Mac but assume.  Anyway, I have a lot of issues reteaching my brain for SQL. Another issue I have and guess I'm going to just delete this one unit and move on...
In dBase I always have a unit in my DBF apps dedicated to maintenance of the dBase tables, reindex and pack all tables.  Accordingly, from what you just taught me, not needed! A headache I've been dealing with trying to change code.  For example, Lazarus/SQLite3 doesn't like this in my MaintTables.pas unit:

Code: Pascal  [Select][+][-]

1. procedure TFrmRebldCntksDB.SpdBtnRebuildClick(Sender: TObject);
2. { Pack the Contacts, Categores, Countries and States tables.
3.   Recreate all table indexes for all tables in the database. }
4. begin
5.   SpdBtnRebuild.Enabled:= False;
6.   ForceDirectories('Data');
7.   PrgresBarRebld.Step:= 5;
8.   PrgresBarRebld.Position:= 5;
9.   try // Contacts table maintenance start...
10.     DbfCntks.Exclusive:= True;
11.     DbfCntks.Open;
12.     DbfCntks.PackTable; // Pack Contacts.dbf
13.     PrgresBarRebld.Position:= 10;
14.     DbfCntks.RegenerateIndexes; // Reindex Contacts.dbf
15.     DbfCntks.Close;
16.   finally
17.     DbfCntks.Free;
18.     PrgresBarRebld.Position:= 20;
19.   end;
20.   try // Categories table maintenance start...
21.     DbfCategories.Exclusive:= True;
22.     PrgresBarRebld.Position:= 40;
23.     DbfCategories.Open;
24.     DbfCategories.PackTable; // Pack Categories.dbf
25.     DbfCategories.RegenerateIndexes; // Reindex Categories.dbf
26.     DbfCategories.Close;
27.   finally
28.     DbfCategories.Free;
29.     PrgresBarRebld.Position:= 50;
30.   end;
31. ...
32. My changes... for SQLite3
33. procedure TFrmRebldCntksDB.SpdBtnRebuildClick(Sender: TObject);
34. { Pack the Contacts, Categores, Countries and States tables. Delete
35.   and recreate all table indexes for all tables in the database. }
36. begin
37.   SpdBtnRebuild.Enabled:= False;
38.   PrgresBarRebld.Step:= 5;
39.   PrgresBarRebld.Position:= 5;
40.   try // Contacts table maintenance start...
41.     QryCntks.Open;
42.     //SQLite3DSCntks.Open;
43.     //Sqlite3DSCntks.SQL:= 'REINDEX CONTACTS';
44.     //Sqlite3DSCntks.ExecSQL('REINDEX CONTACTS');
45.     QryCntks.SQL.Text:= 'REINDEX CONTACTS';
46.     QryCntks.ExecSQL;  // Reindex Contacts table
47.     PrgresBarRebld.Position:= 10;
48.     QryCntks.Close;
49.   finally
50.     PrgresBarRebld.Position:= 20;
51.   end;
52. Then later, and this code errors:
53.   //SQLite3DSCntks.ExecSQL('VACUUM');
54.  

Also, having issues reprogramming to change or set a table to open with an index active so the DBGrid shows in a specific order. This compiles but errors when I run:

Code: Pascal  [Select][+][-]

1. procedure TFrmCntksMain.FormShow(Sender: TObject);
2. begin
3.   {SQLite3DSCntks.IndexFieldNames:= 'CONTACTS';
4.   SQLite3DSCntks.Open;
5.   SQLite3DSStates.IndexFieldNames:= 'STPROVCODES';
6.   SQLite3DSStates.Open;
7.   SQLite3DSCntries.IndexFieldNames:= 'COUNTRIES';
8.   SQLite3DSCntries.Open;
9.   SQLite3DSCategories.IndexFieldNames:= 'CATEGORIES';
10.   SQLite3DSCategories.Open;}
11.   DSStates.DataSet.FieldByName('STPROVCODE').DisplayWidth:= 2;
12.   DSStates.DataSet.FieldByName('STPROVNAME').DisplayWidth:= 12;
13.   DSCntries.DataSet.FieldByName('CODE').DisplayWidth:= 2;
14.   DSCntries.DataSet.FieldByName('COUNTRY').DisplayWidth:= 10;
15.   DSCategories.DataSet.FieldByName('CATID').DisplayWidth:= 2;
16.   DSCategories.DataSet.FieldByName('CATEGORY').DisplayWidth:= 25;
17.  

Contacts table, index CONTACT is indexed by LASTNAME, FIRSTNAME, MI.
I also have a ComboBxSelIndex so the user can change the sort order of the table, i.e.

Code: Pascal  [Select][+][-]

1. procedure TFrmCntksMain.FormCreate(Sender: TObject);
2. begin
3.   //-> Add items to the CmboBxSelIndex component
4.   CmboBxSelIndex.Items.Clear;
5.   CmboBxSelIndex.Items.Add('Name (L,F,M)');
6.   CmboBxSelIndex.Items.Add('Company');
7.   CmboBxSelIndex.Items.Add('Category');
8.   CmboBxSelIndex.Items.Add('State/Province');
9.   CmboBxSelIndex.Items.Add('Country');
10.   CmboBxSelIndex.Items.Add('City, State, Country');
11. end;
12.  
13. procedure TFrmCntksMain.CmboBxSelIndexSelect(Sender: TObject);
14. begin
15.   Case CmboBxSelIndex.Text of
16.     'Name (L,F,M)':SQLite3DSCntks.IndexFieldNames:= 'CNTKLNAME';
17.     'Company':SQLite3DSCntks.IndexFieldNames:= 'CLIENT';
18.     'Category':SQLite3DSCntks.IndexFieldNames:= 'CATEGORY';
19.     'State/Province':SQLite3DSCntks.IndexFieldNames:= 'STPROV';
20.     'Country':SQLite3DSCntks.IndexFieldNames:= 'CNTRYCODE';
21.     'City/Town':SQLite3DSCntks.IndexFieldNames:= 'CITYSTCNTRY'
22.   else
23.     WriteLn ('Index Unchanged!');
24.   end;
25.   JvDBSrchEditByIndex.DataField:= '';
26.   SQLite3DSCntks.Refresh;
27. end;
28.  

I don't think this is going to work, not sure until I am able to figure out how to set the tables' active index.

Tks... working on it!

[1HuntnMan]

Rewrote everything and no compile errors but when I attempt to run, Lazarus runs into an error at the 2nd line of the FormShow in the Main unit at the second line:

Code: Pascal  [Select][+][-]

1.   SQLite3DSCntks.FileName:= SysUtils.ExtractFilePath(ParamStr(0))+'ContactsDB.SQLite3'
2.   SQLite3DSCntks.Active:= True; // Error here: cntksmain.pas(227,3) Error: Syntax error, ";" expected but "identifier SQLITE3DSCNTKS" found
3.  

[cdbc]

Missing ; (semicolon) in line 1 at the end.
Regards

[1HuntnMan]

OMG     
Tks, CDBC

[CharlyTango]

Even if the analogy doesn't quite apply, a simplified model might help.
If you use a DBMS such as SQLite, you can see the database as a black box from the application's point of view.
The application is basically not responsible for data storage, optimisation, indexing or any other administrative tasks relating to the data.

You can retrieve data from this black box using the SELECT commands. The DBMS is usually also responsible for sorting the required data. You get the data the way you want it. That's it.

However, you can not only query a single table but also design the query in such a way that several tables are combined. For example, you can get the name, address and telephone number of several people in one query.
For example, the user enters a location in the search window and receives a list of people with address and telephone number.

You can query many things at the same time, but you can only write one table at a time.

So there is no active index that can be set, only a desired sorting that you want to have in the query result. The DBMS decides which index (or several or even a full table scan) to use with the help of its query optimiser. The application has no influence on this.
You can create intelligent indices in the DBMS, but the optimiser decides on their use.

You no longer have anything to do with all the indices and switching between them. You only request the desired sorting.

Code: Pascal  [Select][+][-]

1. SELECT <fieldlist>
2. FROM <tablename>
3. ORDER BY <fieldname1,fieldname2,fieldnameN>

If you want a different sorting, request a new result set from the DBMS with a new SELECT.
This is normally so fast that the new query is not a problem. I practically always do it this way.

[CharlyTango]

The part of the code that takes care of maintaining the tables and indices is unnecessary when using SQLite.
Especially if you do this kind of data maintenance at every start.
You can remove this from the start procedure and place it in a separate menu item if you wish.
That takes care of this problem.

[CharlyTango]

Set-based vs. row-based: An SQL database works at the level of sets, while a DBF database works at the level of rows. You need to get used to the new way of processing data.

Join: In an SQL database, you can combine data from different tables by using join commands.