[SOLVED fixed in svn] TSqlite3Dataset - Child table view problem

[totya]

Shortly:
This problem solved by rvk, after the component developer solve this problem too, via svn code.


Hi!

I have three tables, and all tables connect with each other (master/slave). But I have a problem, when I delete all records from the second table, I see all records in the third tables...

I created sample for this problem, see attached file.

More level "with" in this code to rvk

Thanks!

[rvk]

First of all... you didn't do an ApplyUpdates after the Delete action in BeforeDelete for the detailset. So actually all the cars are kept in the dataset but aren't visible because the person is deleted (although restarting your app these persons will be visible again because you don't do ApplyUpdates there too). The records are only really deleted when you do the Applyupdates.

Then, when there are no visible records in the person-dataset, the filter for master/detail is de-activated. You can see that in this code in CustomSqliteDS.pas (the
"(FMasterLink.Dataset.RecordCount = 0)" part).

Code: [Select]

begin
  if not FMasterLink.Active or (FMasterLink.Dataset.RecordCount = 0) then //Retrieve all data
    FEffectiveSQL := FSqlFilterTemplate
  else
    AFilter := ' where ';
    for i := 0 to FMasterLink.Fields.Count - 1 do
  //...

It is debatable if this check "(FMasterLink.Dataset.RecordCount = 0)" should be there (I don't think it should). But the fact remains that if the master-dataset doesn't have records the detail-dataset shouldn't have records either (usually). And in your case it does (because of the missing ApplyUpdates).

(B.T.W. All this could have been avoided if you used foreign keys and the constraint ON DELETE CASCADE, but that aside )

[totya]

First of all... you didn't do an ApplyUpdates after the Delete action in BeforeDelete for the detailset. So actually all the cars are kept in the dataset but aren't visible because the person is deleted (although restarting your app these persons will be visible again because you don't do ApplyUpdates there too). The records are only really deleted when you do the Applyupdates.

Hi Master!

Please look the sample source carefully again, and see this (common)event:

Code: [Select]

procedure TForm1.AfterDelete(DataSet: TDataSet);
begin
  (Dataset as TSqlite3Dataset).ApplyUpdates;
end;         

So, not is the problem.

Thanks!

[rvk]

Please look the sample source carefully again, and see this (common)event:
//...
So, not is the problem.

Woops  You're correct. And it seems like the cars are indeed really deleted because they don't show in the total-list of cars. Only the cars of all the other persons (of the other Bosses) are visible. So it is that line  (FMasterLink.Dataset.RecordCount = 0) that really shouldn't be there.

Now we can call this a bug

(not tested yet:)

Code: [Select]

begin
  if not FMasterLink.Active { or (FMasterLink.Dataset.RecordCount = 0) } then //Retrieve all data
    FEffectiveSQL := FSqlFilterTemplate
  else
    AFilter := ' where ';
    for i := 0 to FMasterLink.Fields.Count - 1 do
  //...

Edit: Tested this change in trunk and it does work. Maybe @LuizAmérico can take a look.

[totya]

Now we can call this a bug

I don't like if I found bug always

(not tested yet:)

Code: [Select]

begin
  if not FMasterLink.Active { or (FMasterLink.Dataset.RecordCount = 0) } then //Retrieve all data
    FEffectiveSQL := FSqlFilterTemplate
  else
    AFilter := ' where ';
    for i := 0 to FMasterLink.Fields.Count - 1 do
  //...


Tested or not tested, it seems to me it's working. I think this component developer modify own svn code too, as usually.

Thanks Master!

[LuizAmérico]

I'll take a look.

Is a mater of defining the behavior  when there's no records in master table.

Does someone know the bahavior of other components that implement master detail?

This would save me sometime

[rvk]

With which component would you like it to compare? With a normal dataset, when connecting to another datasource, you would need to set the sql manually to do the "where"-clause. And this won't change if the master dataset is empty (the "where" would still be in effect). So if you want to mimic that behavior, you can't let this be determined by the fact the master-dataset is empty. When it is empty the detailset should also be empty. True... this is a special case because the master dataset is also a detailset itself but in TDataset that wouldn't matter.

[LuizAmérico]

With which component would you like it to compare?

TDBF

[rvk]

With which component would you like it to compare?

TDBF

I can confirm TDbf shows an empty detail-dataset when the master-dataset is empty, in the given example.

(attached a small converted test. only meant to be run once because I've taken no effort to do db-checking on startup)

[miab3]

Worth a look as it does:
Zeos7.2-beta branches_ testing_Revision 3628:
http://svn.code.sf.net/p/zeoslib/code-0/branches/testing-7.2/
http://zeoslib.sourceforge.net/viewtopic.php?f=40&t=3795&start=330#p41664
There are two ways master-detail: on the server side and the client side.

Michal

[LuizAmérico]

I confirmed the behavior of zeos is equal to the TDBF one.
Fixed sqlite3dataset in fpc repository to match this behavior

[totya]

I confirmed the behavior of zeos is equal to the TDBF one.
Fixed sqlite3dataset in fpc repository to match this behavior

Hi!

Thanks, new code works correctly!