[SOLVED] DisableControls breaks master/detail working lazarus / 16-Databases ---------------------------------------------------------------------- totya · 2015-05-03 19:30 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! ---------------------------------------------------------------------- rvk · 2015-05-03 20:13 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 · 2015-05-03 20:22 BTW. This behavior might be according to design. This is also the case in Delphi: QuoteIf 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.TData Set.DisableControls) and http://stackoverflow.com/questions/12228420/how-to-use-tdataset- disablecontrols-with-master-detail-datasets ---------------------------------------------------------------------- totya · 2015-05-03 20:37 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... Quote from: rvk on May 03, 2015, 10:13:06 pmBeside 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. Quote from: rvk on May 03, 2015, 10:13:06 pmCouldn'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? Quote from: rvk on May 03, 2015, 10:13:06 pmAnd 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 QuoteIt'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 · 2015-05-03 21:10 Quote from: totya on May 03, 2015, 10:37:08 pmBecause 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. QuoteIt'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: QuoteAssuming 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 · 2015-05-03 21:23 Quote from: rvk on May 03, 2015, 11:10:31 pmA 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: QuoteAssuming 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 · 2015-05-03 21:34 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 · 2015-05-04 00:08 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 · 2015-05-04 04:22 Quote from: LuizAmérico on May 04, 2015, 02:08:14 amTo 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: QuoteFOREIGN 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? ---------------------------------------------------------------------- rvk · 2015-05-04 09:58 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 · 2015-05-04 13:58 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 · 2015-05-04 16:07 Quote from: rvk on May 04, 2015, 03:58:43 pmOkay, 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 · 2015-05-04 16:34 Quote from: totya on May 04, 2015, 06:07:59 pmBut 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://for um.lazarus.freepascal.org/index.php/topic,28046.msg174617.html#msg1746 17 But it kind of depends on your form-designs. (Are you still following everything    ) ---------------------------------------------------------------------- totya · 2015-05-05 19:59 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: Quote from: rvk on May 03, 2015, 11:10:31 pmQuote from: totya on May 03, 2015, 10:37:08 pmBecause 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).