Recent

Author Topic: Master-Detail in SQLite  (Read 946 times)

folkeu08

  • New Member
  • *
  • Posts: 47
Master-Detail in SQLite
« on: April 08, 2021, 03:37:05 pm »
Hi,
I am creating an application with SQLite tables. I am using components from Lazarus 'SQLdb' library
I have a master table (Cell) and a detail table (Contract) whose common link field is "cell_id".
This is declared ainbsi in the base.

I am not using a TDBNavigator component to manage tables. I write scripts in SQL for a tSQLQuery component associated with a TDataSpource component for each function (Insert, edit, delete) on tables.
This procedure allows the insertion of fields in the detail table (Contract) without declaring in the SQLQuery the DataSource of the SQLQuery of the "Cell" table of the DataSource property.

Code: Pascal  [Select][+][-]
  1. Procedure TNewContrat_form.BtnEnregistrerclick (Sender : TObject);
  2.   begin
  3.     Cellule_DataModule.Contrat_SQLQuery.SQL.Clear;
  4.    Cellule_Datamodule.Contrat_SQLQuery.SQL.Text := 'insert into Cellule_Contrat (id_cellule, statut, grade, categorie, date_debut, date_fin, duree, fin_contrat, date_avantfin) values (:id_cellule, :statut, :grade, :categorie, :date_debut, :date_fin, :duree, :fin_contrat, :date_avantfin);';
  5.     Cellule_DataModule.Contrat_SQLQuery.Prepare;
  6.       Try
  7.         Cellule_Datamodule.Contrat_SQLQuery.Params.ParamByName('id_cellule').AsInteger := Var_Agent;
  8.         Cellule_Datamodule.Contrat_SQLQuery.Params.ParamByName('statut').AsString := Statut_ComboBox.Caption;
  9.         Cellule_Datamodule.Contrat_SQLQuery.Params.ParamByName('grade').AsString := Grade_ComboBox.Caption;
  10.         Cellule_Datamodule.Contrat_SQLQuery.Params.ParamByName('categorie').AsString := Categorie_ComboBox.Caption;
  11.         Cellule_Datamodule.Contrat_SQLQuery.Params.ParamByName('date_debut').AsDate := DateDebut_DateEdit.Date;
  12.         Cellule_Datamodule.Contrat_SQLQuery.Params.ParamByName('date_fin').AsDate := DateFin_DateEdit.Date;
  13.         Cellule_Datamodule.Contrat_SQLQuery.Params.ParamByName('duree').AsInteger := Duree_SpinEdit.Value;
  14.         Cellule_Datamodule.Contrat_SQLQuery.Params.ParamByName('fin_contrat').AsBoolean := AvantTerme_CheckBox.Checked;
  15.         Cellule_Datamodule.Contrat_SQLQuery.Params.ParamByName('date_avantfin').AsDate := DateAvantFin_DateEdit.Date;
  16.          Cellule_Datamodule.Contrat_SQLQuery.ExecSQL;
  17.         Cellule_Datamodule.Contrat_SQLQuery.SQLTransaction.Commit;
  18.    except
  19.         on e: Exception do
  20.           begin
  21.             e.Message  := e.Message + 'Insertion annulée.';
  22.             Cellule_Datamodule.Contrat_SQLQuery.SQLTRansaction.Rollback;
  23.           raise e;
  24.           end;
  25.       end;
  26.  
  27.   //Refesh de la grille
  28.   Cellule_Datamodule.Contrat_SQLQuery.SQL.Clear;
  29.   Cellule_Datamodule.Contrat_SQLQuery.SQL.Text := 'SELECT * from cellule_contrat WHERE id_cellule=:id_cellule;';
  30.   Cellule_Datamodule.Contrat_SQLQuery.Prepare;
  31.   Close;
  32.   end;                                          
  33.  

If I use this same procedure by associating the Datasource of the SQLQuery of the table Cell (Master) in the Datasource property of the SQLQuery of the table "contract" (detail), an error occurs.
Do you have any idea of ​​the cause?
Thanks

egsuh

  • Hero Member
  • *****
  • Posts: 783
Re: Master-Detail in SQLite
« Reply #1 on: April 09, 2021, 07:58:15 am »
In normal cases SQLQuery does not need Datasource.  Datasources are for data controls' datasource property, and you assign datasets (e.g. TSQLQuery) to Datasource.dataset property.

If you are using remote access you may need datasource for datasets. 

devEric69

  • Hero Member
  • *****
  • Posts: 515
Re: Master-Detail in SQLite
« Reply #2 on: April 09, 2021, 09:39:00 am »
If you want to use the TDatalink design pattern, and the automatic " master-details " articulation pattern (and thus, effectively use the TDatasource component, whether or not you need visual TControls to visually browse and CRUD the data-records), then the connection between the properties of the components must be set like this:

                           ┌-------------------------┐
                            | TDataset master (1) |
                           └-------------------------┘
                                         ↑
                           ┌-------------------------┐
                            | TDatasource master |                 ( ⇦ needed, in order to bridge the 2 datasets)
                           └-------------------------┘
                                 ↗
                              ↗
                           ↗ 
    ┌-------------------------┐
     | TDataset details (+) |
    └-------------------------┘



Note @egsuh: here, there is no TControl - for the relation's master side - connected to the needed TDatasource master (since, there is no need to browse and CRUD the master table). But, there is effectively no TDatasource details (since here, having no need to browse and CRUD the details table, no TControl - for the relation's details side - will be dropped on a TForm).
« Last Edit: April 09, 2021, 10:53:54 am by devEric69 »
use: Linux 64 bits (Ubuntu 20.04 LTS).
Lazarus version: 2.0.4 (svn revision: 62502M) compiled with fpc 3.0.4 - fpDebug \ Dwarf3.

egsuh

  • Hero Member
  • *****
  • Posts: 783
Re: Master-Detail in SQLite
« Reply #3 on: April 09, 2021, 10:56:46 am »
@devEric69

Does TSQLQuery provide Master-detail relationship? I remember old Delphi TTables, which defined master-detail relationship. I think that TSQLite3DataSet may provide such functionality, but not sure about TSQLQuery.

Sorry, I have to check it myself, but right now I'm too tired because I'm tied up with other things^^  I might need to implement such relationship later.

devEric69

  • Hero Member
  • *****
  • Posts: 515
Re: Master-Detail in SQLite
« Reply #4 on: April 09, 2021, 11:52:36 am »
Hello @egsuh,

Does TSQLQuery provide Master-detail relationship? I remember old Delphi TTables, which defined master-detail relationship. I think that TSQLite3DataSet may provide such functionality, but not sure about TSQLQuery.

Yes, it can, it's exactly the same thing O:-) .

For your information, this is what the documentation for tsqlquery.datasource  says: "When Open or ExecSQL is called, and the Datasource property is not Nil then for each parameter for which no value was explicitly set (its Bound property is False), the value will be retrieved from the dataset connected to the datasource."
Said differently, the values of the parameter(s) in the SQL clause "WHERE details.master_record_no = :master_record_no" in the query's details are retrieved automatically from the current value of the pointed TDataset's "master.master_record_no" field, through this TDataset.Datasource property.

In addition, TSQLQuery allows a deeper filtering with its SQL "WHERE" SQL clause, which is finer than a simple TTable that does a SQL "fetch all" (not necessarily ad'hoc for the network, when there are large amounts of data).

I might need to implement such relationship later.

I've tested SQLite with Zeos components: it works very well (having said that, I have only queried and used small tables).
« Last Edit: April 09, 2021, 12:08:21 pm by devEric69 »
use: Linux 64 bits (Ubuntu 20.04 LTS).
Lazarus version: 2.0.4 (svn revision: 62502M) compiled with fpc 3.0.4 - fpDebug \ Dwarf3.

 

TinyPortal © 2005-2018