Recent

Author Topic: [SOLVED] DisableControls breaks master/detail working  (Read 10281 times)

totya

  • Hero Member
  • *****
  • Posts: 720
[SOLVED] DisableControls breaks master/detail working
« on: May 03, 2015, 09:30:17 pm »
Short summary for this topic question after answers:
This is the normal works of DisableControls, this isn't error. If I use DisableControls, then I break connection between master and child table, then when I add record to the child table, I need to add parentID manually to the child table too, then I use EnableControls (in finally block!!!) the result is: everything is okay, and the speed is much faster than before. But see rvk second idea too, in this topic.
I'm sorry for my bad English.


Hi!

Sqlite3DS again, but I think this is TDataset problem.

It seems to me, if I use child-table, then data adding speed is very slow. Very slow. With DisableControls/EnableControls the speed is okay, but the result is wrong. I found a similar topic:

http://talk.remobjects.com/t/disablecontrols-breaks-master-detail-linkage/5822

But

Code: [Select]
xx.MasterSource:=nil;
xx.MasterSource:=yymaster;

doesn't help for me. What is the real soolution, if exists with Lazarus?

I tired DBgrid.DataSources to nil and back, but I don't see big differents in time.

Thanks!
« Last Edit: May 05, 2015, 10:12:21 pm by totya »

rvk

  • Hero Member
  • *****
  • Posts: 6111
Re: DisableControls breaks master/detail working
« Reply #1 on: May 03, 2015, 10:13:06 pm »
Beside the possible problem of this being a bug... why do you need the master/detail relationship during insert?

Couldn't you just insert into the master and save the id and subsequently add the detail records?

And for the delete mentioned in that topic... I don't think you should ever delete details manually if you delete the master. That's what FOREIGN KEYS are for and the ON DELETE CASCADE. In that case deleting the master-records the detail records will be deleted automatically by the database.

rvk

  • Hero Member
  • *****
  • Posts: 6111
Re: DisableControls breaks master/detail working
« Reply #2 on: May 03, 2015, 10:22:09 pm »
BTW. This behavior might be according to design.

This is also the case in Delphi:
Quote
If the dataset is the master of a master/detail relationship, calling DisableControls also disables the master/detail relationship. Setting BlockReadSize instead of calling DisableControls updates the detail datasets as you scroll through the dataset, but does not update data-aware controls.
(source: http://docwiki.embarcadero.com/Libraries/XE8/en/Data.DB.TDataSet.DisableControls)

and http://stackoverflow.com/questions/12228420/how-to-use-tdataset-disablecontrols-with-master-detail-datasets

totya

  • Hero Member
  • *****
  • Posts: 720
Re: DisableControls breaks master/detail working
« Reply #3 on: May 03, 2015, 10:37:08 pm »
Hi Master!

This is not bug I think, because I found many topics with this problem.

If Master-detail connection is live, DisableControls break this. Under Delphi too... :)

Beside the possible problem of this being a bug... why do you need the master/detail relationship during insert?

Because I want insert data to the child table.

Couldn't you just insert into the master and save the id and subsequently add the detail records?

Do you mean, I can handle id system maually? I will try it... I try break connection between the master and child, and then I add data to the child table, I insert to the correct parent id field, did you mean?

And for the delete mentioned in that topic... I don't think you should ever delete details manually if you delete the master. That's what FOREIGN KEYS are for and the ON DELETE CASCADE. In that case deleting the master-records the detail records will be deleted automatically by the database.

I didn't ask the delete, but thanks for the information. But these linked text hard to read for me. Now I delete manually, see:
http://sqlite4fpc.yolasite.com/dataset-tutorial-2.php
Quote
It's also a good practice delete all phones together when a contact is deleted. To accomplish this create a BeforeDelete event handler in ContactsDataset and put this code:

  while not PhonesDataset.IsEmpty do
    PhonesDataset.Delete;

Thank you!

rvk

  • Hero Member
  • *****
  • Posts: 6111
Re: DisableControls breaks master/detail working
« Reply #4 on: May 03, 2015, 11:10:31 pm »
Because I want insert data to the child table.

Do you mean, I can handle id system maually? I will try it... I try break connection between the master and child, and then I add data to the child table, I insert to the correct parent id field, did you mean?
You wouldn't even need to break the connection between master and child. You could just use another SQLQuery or even use Sqlite3Dataset1.ExecuteDirect with an insert statement. If you insert the master record you should be able to use Sqlite3Dataset1.LastInsertRowId to get your last inserted contactID (if contactID is an autoincrement column in your table). After that you could add the phone-records with the just saved ContactID.

Quote
It's also a good practice delete all phones together when a contact is deleted. To accomplish this create a BeforeDelete event handler in ContactsDataset and put this code:

  while not PhonesDataset.IsEmpty do
    PhonesDataset.Delete;
Ieks  %) Yes. Deleting all Phonenumbers is good practice. But what if there is a problem. You might end up with a deleted master and partially deleted phonenumer. But some phonenumber may still be there if your program crashes or something.

Good database-design has build in FOREIGN KEYS. Do you have a FOREIGN KEY in your phonenumber table to your contacts table? If not, that could lead to problems. You could delete the contact without deleting the phonenumbers, leaving the phonenumbers without a contact. Ieks :)

You have two options with foreign keys (but for a good database you need at least one of them):
Just declare a foreign key in phones like this:

Code: [Select]
CREATE TABLE phones(
  phoneid     INTEGER,
  contactid     INTEGER,
  phonenumber TEXT,
  FOREIGN KEY(contactid) REFERENCES contacts(contactid)
);
In that case when you try to delete a contact which still had phonenumbers, it fails (as it should). You would need to delete all phonenumers first (manually via code) before deleting the contact.

The second option is easier. The database can delete the phonenumbers for you when you delete the contact. In that case you would add ON DELETE CASCADE to the FOREIGN KEY-line.
Code: [Select]
  FOREIGN KEY(contactid) REFERENCES contacts(contactid) ON DELETE CASCADE

So this is just a way to keep your database consistent.

For now you could do it manually in code but when you get the chance you should look into adding foreign key constraints to your database.

A quick sidenote (which would make all the above meaningless):
I noticed that the documentation of SQLite says you need to enable foreign keys in your application:
Quote
Assuming the library is compiled with foreign key constraints enabled, it must still be enabled by the application at runtime, using the PRAGMA foreign_keys command.
Does anybody know if this is still the case? Does it require extra commands to enable the foreign key support in sqlite? (I haven't tested this myself because my main database-engine is Firebird)

totya

  • Hero Member
  • *****
  • Posts: 720
Re: DisableControls breaks master/detail working
« Reply #5 on: May 03, 2015, 11:23:17 pm »
A quick sidenote (which would make all the above meaningless):
I noticed that the documentation of SQLite says you need to enable foreign keys in your application:
Quote
Assuming the library is compiled with foreign key constraints enabled, it must still be enabled by the application at runtime, using the PRAGMA foreign_keys command.
Does anybody know if this is still the case? Does it require extra commands to enable the foreign key support in sqlite? (I haven't tested this myself because my main database-engine is Firebird)

Thanks for detailed answers, I read it after sleep :)

Well, if I create database with TSqlite3Dataset, it has foreign keys support, I see this in DB browser, for example, see attached picture:


rvk

  • Hero Member
  • *****
  • Posts: 6111
Re: DisableControls breaks master/detail working
« Reply #6 on: May 03, 2015, 11:34:56 pm »
Yes, if you made the database outside of your own code it probably was made with a program which set foreign keys to on.

You still might need to do this in your own application.
I found this line in the source of TSQLite3Connection:
Code: [Select]
  if Params.IndexOfName('foreign_keys') <> -1 then
    execsql('PRAGMA foreign_keys =  '+Params.Values['foreign_keys']);
So that means you could activate it with:
Code: [Select]
SQLite3Connection1.Params.Add('foreign_keys=ON');

But because you're not using the TSQLite3Connection I'm not sure it is needed in Sqlite3Dataset.
If it is you should probably do something like this at the beginning of your program:
Code: [Select]
Sqlite3Dataset1.ExecuteDirect('PRAGMA foreign_keys = ON');

LuizAmérico

  • Sr. Member
  • ****
  • Posts: 457
Re: DisableControls breaks master/detail working
« Reply #7 on: May 04, 2015, 02:08:14 am »
To activate foreign keys is necessary to call in the start of app, once for the datafile:

Sqlite3Dataset1.ExecSQL('PRAGMA foreign_keys = ON');

Use ExecSQL (is safer)

totya

  • Hero Member
  • *****
  • Posts: 720
Re: DisableControls breaks master/detail working
« Reply #8 on: May 04, 2015, 06:22:31 am »
To activate foreign keys is necessary to call in the start of app, once for the datafile:

Sqlite3Dataset1.ExecSQL('PRAGMA foreign_keys = ON');

Use ExecSQL (is safer)

Hi!

Thanks, but as I wrote (for rvk) if I create database with your component, foreign key settings default value is ON, I see this in DBBrowser app. But the best if I use this code, thanks.

The queston is, hogy can I use foreign key without sql command? because your component usage is simple like this (from your tutorial):
Code: [Select]
with PhonesDataset do
  begin
    FileName := IncludeTrailingPathDelimiter(GetAppConfigDirUTF8(True)) + 'data.db';
    if not TableExists then
    begin
      FieldDefs.Clear;
      FieldDefs.Add('Id', ftAutoInc);
      FieldDefs.Add('Phone', ftString);
      FieldDefs.Add('ParentId', ftInteger);
      CreateTable;
    end;
    Open;
  end;

If I want to use foreign key, I need SQL tommand to use... see from rvk:

Quote
FOREIGN KEY(contactid) REFERENCES contacts(contactid) ON DELETE CASCADE

But how can I do this? Before CreateTable command, or how? (I think need new command of your component...)

The second, see the topic subject, adding data is very slow with active master/data connection, what is your idea of this?
« Last Edit: May 04, 2015, 12:19:38 pm by totya »

rvk

  • Hero Member
  • *****
  • Posts: 6111
Re: DisableControls breaks master/detail working
« Reply #9 on: May 04, 2015, 11:58:37 am »
Wow, this isn't easy to do with SQLite (and SQLite3ds).

Okay. I've tried it with SQLite3ds and with SQLite3Connection (see attached).
With SQLite3Connection there used to be a problem that 'PRAGMA foreign_keys = ON' needs to be set before there is a transaction started. That's why there was the option for the Params.Add('foreign_keys=ON'); created. See the topic here: http://forum.lazarus.freepascal.org/index.php?topic=15477.0

I've managed to create a working example where you can delete one contact and the phonenumbers are deleted automatically (cascade). It's in foreign_key_with_sqlite3connection.zip. You can press create table and see a working master/detail construction. You can delete contacts. If you click "All phones" the connection is severed so you can see all phonenumbers. That's a good way to see the phonenumbers are really deleted for the deleted contacts (automatically).

Second, I tried to do this with the SQLite3ds. The problem is I can't set 'PRAGMA foreign_keys = ON'. Or at least it doesn't seem to work for me. See the attached foreign_key_with_sqlite3ds.zip. When clicking "All phones" and deleting a contact the corresponding phonenumbers should be deleted too, but they aren't. Are transactions handled internally in TSqlite3Dataset? I'm guessing that SQLite3ds can't set the 'PRAGMA foreign_keys = ON' correctly. If it needs to be done during connection build and before any transaction I think it is done too late in sqlite3ds. I also think I read somewhere it needs to be set for every connection to SQLite.dll and before any transaction is started. I'm not sure about the internals of SQLite3ds how this is exactly done but it seems to me it has no options or possibility to set the 'foreign key'-construct and work with consistent databases. Where as SQLite3Connection does.

@totya: You can look at the foreign_key_with_sqlite3connection.zip how it is supposed to be with a database with foreign keys constraint to make sure the database is always consistent and with a minimum of coding.

@LuizAmérico: Can you look at foreign_key_with_sqlite3ds.zip and see if i've done anything wrong and maybe you can get the foreign-key constraints going. Or can you make the same construction with Params.Add('foreign_keys=ON'); (as discussed here) so it doesn't have to be executed in ExecuteDirect or ExecSQL. Because I think there it is too late.

rvk

  • Hero Member
  • *****
  • Posts: 6111
Re: DisableControls breaks master/detail working
« Reply #10 on: May 04, 2015, 03:58:43 pm »
Okay, here I am again. I've got SQLite3ds working with FOREIGN KEY constraints.
One of the problems was that I didn't have PrimaryKey set for the datasets.

Code: [Select]
  tbContacts.PrimaryKey := 'ID';
  tbPhones.PrimaryKey := 'ID';

After that, and an ApplyUpdates and Open/Close for tbPhones, it all works.

Furthermore, I found out SQLite3ds doesn't work with normal transaction control. It saves DeletedItems and InsertedItems until Applyupdates and does a COMMIT directly afterwards. So the .ExecSQL('PRAGMA foreign_keys = ON'); works correctly before really using the tables (no transaction is started at that moment). As far as I can tell SQLite3ds doesn't have direct FOREIGN KEY support for creating a table but you can do that with the ExecSQL-command (like I did in this demo).

totya

  • Hero Member
  • *****
  • Posts: 720
Re: DisableControls breaks master/detail working
« Reply #11 on: May 04, 2015, 06:07:59 pm »
Okay, here I am again. I've got SQLite3ds working with FOREIGN KEY constraints.
One of the problems was that I didn't have PrimaryKey set for the datasets.

Code: [Select]
  tbContacts.PrimaryKey := 'ID';
  tbPhones.PrimaryKey := 'ID';

After that, and an ApplyUpdates and Open/Close for tbPhones, it all works.

Doesn't work :) Hi master!

But in your new code I see, "ApplyUpdates and Open/Close" needed for every DBNavigator1Click. Okay, it's working, thank you!
This is mean, this code  needed always, before I want to delete. But with Tdataset bug, this is before "cancel; delete". :)

rvk

  • Hero Member
  • *****
  • Posts: 6111
Re: DisableControls breaks master/detail working
« Reply #12 on: May 04, 2015, 06:34:39 pm »
But in your new code I see, "ApplyUpdates and Open/Close" needed for every DBNavigator1Click. Okay, it's working, thank you!
This is mean, this code  needed always, before I want to delete. But with Tdataset bug, this is before "cancel; delete". :)
The DBNavigator1Click is executed AFTER the real .Delete so you would still need the .Cancel in BeforeAction in the dbnavigator to cancel the edit-mode.
So the order for dbnavigator is... OnBeforeAction (with Button=nbDelete) --> The real .Delete by the TDataset --> OnClick-event (with Button=nbDelete)
And you need to do the Cancel in OnBeforeAction and the ApplyUpdates/Open/Close in the OnClick (actually OnAfter) event.

And it's not really necessary for every dbnavigatorclick to do the . You could do something like this:
Code: [Select]
procedure TForm1.DBNavigator1BeforeAction(Sender: TObject; Button: TDBNavButtonType);
begin
  if Button = nbDelete then
  begin
    if tbContacts.State in [dsInsert, dsEdit] then
    begin
      tbContacts.Post;  // <---- Add this to cancel the EDIT-mode !!
      //tbContacts.Delete; // NOT HERE. it is done by dbnavigator
    end;
  end;
end;

procedure TForm1.DBNavigator1Click(Sender: TObject; Button: TDBNavButtonType);
begin
  if Button = nbEdit then
  begin
    // set the dbgrid in edit mode
    DBGrid1.Options := DBGrid1.Options + [dgEditing];
  end;
  if Button = nbPost then
  begin
    // set the dbgrid in row-select mode
    DBGrid1.Options := DBGrid1.Options + [dgRowSelect];
  end;
  if Button = nbDelete then
  begin
    // record is already deleted here, only need to apply
    // otherwise the cascade won't happen
    tbContacts.ApplyUpdates;
    tbPhones.Close;
    tbPhones.Open;
  end;
end;

One note... If you use the + in the dbnavigator and you click the - directly after, the Cancel makes the inserted record go away and the delete is executed on the current record. You probably don't want that. So for Insert you would need to do .Post (so the subsequent .Delete is correct) and for Edit you want .Cancel.

But are you going to work with the dbnavigator or menu-options ?
If you are going to use the dbnavigator it would be better to limit the buttons so you can't click delete when you are in editmode.
(You can use the OnStateChange of dsContacts to see and set the dbnavigator buttons you want to enable/disable.)
An example of this is in this post of mine (at the bottom): http://forum.lazarus.freepascal.org/index.php/topic,28046.msg174617.html#msg174617

But it kind of depends on your form-designs.

(Are you still following everything  %)  :D)
« Last Edit: May 04, 2015, 06:38:35 pm by rvk »

totya

  • Hero Member
  • *****
  • Posts: 720
Re: DisableControls breaks master/detail working
« Reply #13 on: May 05, 2015, 09:59:15 pm »
Hi!

Your informations doesn't lost, I read carefully, if I have time :)

Okay, I use Disable/EnableControls, but I break master/child connections with all TDataset in form at once, and I add parent id value manually (that's easy), and this is much fater way. Thanks! Topic is about solved.

Your second idea:
Because I want insert data to the child table.

Do you mean, I can handle id system maually? I will try it... I try break connection between the master and child, and then I add data to the child table, I insert to the correct parent id field, did you mean?
You wouldn't even need to break the connection between master and child. You could just use another SQLQuery or even use Sqlite3Dataset1.ExecuteDirect with an insert statement. If you insert the master record you should be able to use Sqlite3Dataset1.LastInsertRowId to get your last inserted contactID (if contactID is an autoincrement column in your table). After that you could add the phone-records with the just saved ContactID.

I have little problem with it.  First, I always use Sqlite3Dataset for own table. Second, ExecuteDirect/ExecSQL is not really Sqlite3Dataset/TDataset command (see Append; Edit; Insert; Pos; FieldByname().AsXxxx:= etc.), well, if I do not use this component commands, then why I use this component? :) But thanks for the tip! Otherwise, I read SQL book, if I have time. Very long book...

...and a little question, as you wrote, you use firebird, why chose it? (Firebird can use local files like as SQLite).

 

TinyPortal © 2005-2018