EmptyTable Question

Hi Pascal.

Nothing else to do. The table data is deleted on disk directly after a call to TSqlitePassDataset.EmptyTable but the table is not automatically cleared from the dataset memory.

In the current implementation, you must call TSqlitePassDataset.refresh.

Does it make sense to you or do you think the refresh should be automatically applied ?

Regards.

Luc

Hi Pascal,

Lunchtime so I can answer your post

FRefetchRows  is used as a boolean flag when TSqlitePassetDataset.InternalRefresh needs to refetch records from disk.

but since I already override TDataset.Refresh, setting  FRefetchRows := True, it doesn't need to be set again in TSqlitePassDataset.EmptyTable.

Procedure TSqlitePassDataset.Refresh;
begin
  FRefetchRows := True;
  Inherited;
end;

{procedure InternalRefresh; virtual; abstract;
-------------------------------------------
Refresh the dataset.}
procedure TSqlitePassDataset.InternalRefresh;
begin
...
   {  Queries data using SQL sentence : Refetch the records from Sqlite database engine }
   Try
   if FRefetchRows then
      begin
      FRecordset.Open(FSQLSelectStmt.SQL, FSQLSelectStmt.FPrimaryKeyCount);
      MapFieldsToMemoryFieldBuffers;
      if Not FLookupFieldsDisplayedRecordsOnly
         then FillLookupFields(0, Pred(FRecordset.FRecordsCount));
      if Not FCalcDisplayedRecordsOnly
         then FillCalcFields(0, Pred(FRecordset.FRecordsCount));
      FRefetchRows := False;
      end;
...

Finally, I decided to change the behavior of EmptyTable :

When called directly from the current dataset, the data will be refreshed automatically

procedure TSqlitePassDataset.EmptyTable;
begin
  if DatasetType = dtTable then
     begin
     FDatabase.Engine.ExecSQL('DELETE FROM "' + DatasetName + '";');
     Refresh;
     end;
end;

Suggest : I could extend the syntax to include dtQuery ->  if DatasetType in [dtTable, dtQuery, dtSQLSelect] ?

When NOT called directly from the current dataset but from Database.TableDefs, the data will NOT be refreshed automatically (because you shouldn't be working on or displaying data from the table you want to empty...)

procedure TSqlitePassTableDefs.EmptyTable(Const TableName: String);
begin
 FDatabase.Engine.ExecSQL('DELETE FROM "' + TableName + '";');
end;

Regards

May be for the 0.51 version, but this should be used with care with queries that involve many tables....

Could you tell me if your speed tests were ok (even if the components are not 100% reliable yet...)

Regards

Test 2:

1000 appends (Test Table with 3 Fields: 1xText, 1xINT64, 1XInteger)

Synchronous mode:
- Off: 3 seconds
- Normal: 10 seconds
- FULL: 8 seconds

Test 3:

Just did an extra test comparing the speed of your components with ZeosLib components. +/- the same speed.

Regards,
Pascal

Last edited by pcoenen (2010-04-01 20:01:57)

Hi Luc,

Another thing about the options:

Suppose I set some options when the database isn't connected (synchronous, cache size, ..). If I connect then the SQLitePass database component should use the options I set using this properties. This isn't the case.
I think this should work like this, it would help users setting up the connection option in an easy way. No we need to do this ourselves using the Pragma commands. Not every one knows this.

Regards,
Pascal