"has many" and "has many through" relationships

ab · 2010-10-29 05:56:28

http://en.wikipedia.org/wiki/Many-to-many_(data_model) wrote:

In systems analysis, a many-to-many relationship is a type of cardinality that refers to the relationship between two entities (see also Entity-Relationship Model) A and B in which A may contain a parent row for which there are many children in B and vice versa. For instance, think of A as Authors, and B as Books. An Author can write several Books, and a Book can be written by several Authors. Because most database management systems only support one-to-many relationships, it is necessary to implement such relationships physically via a third and fourth junction table, say, AB with two one-to-many relationships A -> AB and B -> AB. In this case the logical primary key for AB is formed from the two foreign keys (i.e. copies of the primary keys of A and B).

In our SQLite3 framework, we just created a dedicated class for handling such "many to many" relationships.
From the record point of view, and to follow the ORM vocabulary (in Ruby on Rails, python, or other ActiveRecord clones), we could speak of "has many" relationships.
In fact, a pivot table is created, containing two references to both related records.
Additional information can be stored in this pivot table. It could be used, for instance, to store association time or corresponding permissions of the relationship. This is called a "has many through" relationship.

Martin Fowler defines the Active Record pattern as:

An object that wraps a row in a database table or view, encapsulates the database access, and adds domain logic on that data.

In short, this is the ORM approach.

In our framework, we started from the record point of view. Since the "has many through" relationships will need one record with optional additional fields, we'll start from this pivot table.
A dedicated class, inheriting from the standard TSQLRecord class (which is the base of all objects stored in our ORM), has been created, named TSQLRecordMany.

Here is the declaration of this class:
Warning: this has been modified/updated - see below.

type
  {{ handle "has many" and "has many through" relationships
   - many-to-many relationship is tracked using a table specifically for that
     relationship, turning the relationship into two one-to-many relationships
     pointing in opposite directions
   - by default, only two TRecordReference (i.e. INTEGER) fields are created,
     the first pointing to the source record (the one with a TSQLRecordMany
     published property) and the second to the destination record
   - you should first create a type inheriting from TSQLRecordMany, providing
     additional "through" parameters if necessary, named after the destination
     record class name (ending with a "s" by convention, any last char is trimmed):
     ! TSQLDest = class(TSQLRecord);
     ! TSQLDests = class(TSQLRecordMany)
     ! published
     !   property Source: TRecordReference read fSource; // map Source column
     !   property Dest: TRecordReference read fDest; // map Dest column
     !   property AssociationTime: TDateTime read fTime write fTime;
     ! end;
     ! TSQLSource = class(TSQLRecord)
     ! published
     !   DestList: TSQLDests read fDestList;
     ! end;
   - in all cases, at leat two Source and Dest published properties must always
     be declared as TRecordReference in any TSQLRecordMany descendant
     because they will always be needed for the 'many to many' relationship
   - when a TSQLRecordMany published property exists in a TSQLRecord, it is
     initialized automaticaly by TSQLRecord.Create
   - to read all Dest records IDs, use the DestGet() method
   - to read the Dest records and the associated "through" fields content, use
     the FillMany, FillRow, FillOne and FillRewind methods
   - to add some Dest record to the records list, use the DestAdd() method
   - one TSQLRecordMany can be used in diverse TSQLRecord classes and properties,
     because the Source (=TSQLRecord) and Dest classes are stored inside the
     TRecordReference }
  TSQLRecordMany = class(TSQLRecord)
  protected
    fSource, fDest: TRecordReference;
    // internal field initialized during TSQLRecord.Create, via CreateForRecord
    fSourceClass: TSQLRecordClass;
    // internal field initialized on the fly, stored here for caching purpose
    fDestClass: TSQLRecordClass;
    // 'TSQLDests' -> TSQLDest
    procedure SetDestClassFromClassName(aModel: TSQLModel);
    // get the SQL WHERE statement for a specified Source + fSourceClass/fDestClass,
    // or Dest + fSourceClass/fDestClass if isDest is TRUE
    function IDWhereSQL(aClient: TSQLRest; aID: integer; isDest: boolean): RawUTF8;
  public
    /// release all used memory of this instance
    //- will release the internal fTable, created with any FillMany method call
    destructor Destroy; override;
    /// retrieve all records associated to a particular source record, which
    // has a TSQLRecordMany property
    // - returns the Count of records corresponding to this aSource record
    // - the records are stored in an internal TSQLTable, refered in the private
    // fTable field, and initialized via a FillPrepare call: all Dest items
    // are therefore accessible with standard FillRow, FillOne and FillRewind methods
    // - use a "for .." loop or a "while FillOne do ..." loop to iterate
    // through all Dest items, getting also any additional 'through' columns
    function FillMany(aClient: TSQLRest; aSourceID: integer): integer;
    /// retrieve all Dest items IDs associated to the specified Source
    function DestGet(aClient: TSQLRest; aSourceID: integer; out DestIDs: TIntegerDynArray): boolean;
    /// retrieve all Source items IDs associated to the specified Dest ID
    function SourceGet(aClient: TSQLRest; aDestID: integer; out SourceIDs: TIntegerDynArray): boolean;
    /// add a Dest record to the Source record list
    // - returns TRUE on success, FALSE on error
    // - if NoDupplicates is TRUE, the existence of this Source/Dest ID pair
    // is first checked
    // - current Source and Dest properties are filled with the corresponding
    // TRecordReference values corresponding to the supplied IDs
    // - any current value of the additional fields are used to populate the
    // newly created content (i.e. all published properties of this record)
    function DestAdd(aClient: TSQLRest; aSourceID, aDestID: Integer;
      NoDupplicates: boolean=false): boolean;
  end;

The implementation is therefore the following:
- this TSQLRecordMany class must be named after an existing TSQLRecord class, for instance TSQLDests for storing TSQLDest - this is called the "Dest" record, in our terminology (this naming convention is mandatory, because the ORM engine uses this to guess the "Dest" record type);
- the TSQLRecord which will contain this TSQLRecordMany as published property will be called the "Source" record, in our terminology;
- this TSQLRecordMany published property will be initialized by TSQLRecord.Create, and will make available direct access to the pivot table, without any need to additional code, via the DestAdd/FillMany/SourceGet/DestGet methods;
- both "Source" and "Dest" records are refered by a TRecordReference field in the TSQLRecordMany table columns: therefore a single TSQLRecordMany class/Table can be used by several "Source" classes/tables, that is the same TSQLRecordMany type can be used in several classes as published properties.

First of all, the DestAdd method is used to create a relationship, that is to add a row to the pivot table.
Source and Dest IDs are to be provided to this method, and additional "through" fields are taken from the current values of the TSQLRecordMany instance.

Most of the time, in case of a pure "has many" relationship, only the Dest IDs are searched, according to a specified Source ID.
This is the purpose of the DestGet method, which will return a TIntegerDynArray containing all Dest IDs.

You can also search for the opposite, that is all Source IDs related to a specified Dest ID.
This is the purpose of the SourceGet method, which will return a TIntegerDynArray containing all Source IDs.

In order to handle easily, and in a pure ORM approach a "has many through" relationship, the FillMany method is to be called, according to a specified Source ID.
It will fill the internal result table of the TSQLRecordMany instance with all "through" pivot table records corresponding to this Source ID.
Therefore, the FillOne method can be used to browse all the matching record of the pivot table, and retrieve all associated column values (association time and such). This implements an easy iterator-based mechanism, without the overhead of creating TCollection instance or such.

This new feature has been tested in our automated regression tests.
Here is the corresponding code of the unitary testing. It will help you understand how easy it is to manage these "has many" and "has many through" relationships with our ORM framework:
Warning: this has been modified/updated - see below - in particular, we don't use TRecordReference any more, but plain TSQLRecord child class.

type
  TSQLDest = class;
  TSQLDests = class(TSQLRecordMany)
  private
    fTime: TDateTime;
  published
    property Source: TRecordReference read fSource;
    property Dest: TRecordReference read fDest;
    property AssociationTime: TDateTime read fTime write fTime;
  end;
  TSQLSource = class(TSQLRecordSigned)
  private
    fDestList: TSQLDests;
  published
    property SignatureTime: TTimeLog read fSignatureTime;
    property Signature: RawUTF8 read fSignature;
    property DestList: TSQLDests read fDestList;
  end;
  TSQLDest = class(TSQLRecordSigned)
  published
    property SignatureTime: TTimeLog read fSignatureTime;
    property Signature: RawUTF8 read fSignature;
  end;


procedure TestMany(aClient: TSQLRestClient);
var MS: TSQLSource;
    MD: TSQLDest;
    i: integer;
    sID, dID: array[1..100] of Integer;
    res: TIntegerDynArray;
begin
  MS := TSQLSource.Create;
  MD := TSQLDest.Create;
  try
    MD.fSignatureTime := Iso8601Now;
    MS.fSignatureTime := MD.fSignatureTime;
    Check(MS.DestList<>nil);
    Check(MS.DestList.InheritsFrom(TSQLRecordMany));
    aClient.TransactionBegin(TSQLSource); // faster process
    for i := 1 to high(sID) do begin
      MD.fSignature := FormatUTF8('% %',[aClient.ClassName,i]);
      sID[i] := aClient.Add(MD,true);
      Check(sID[i]>0);
    end;
    for i := 1 to high(dID) do begin
      MS.fSignature := FormatUTF8('% %',[aClient.ClassName,i]);
      dID[i] := aClient.Add(MS,True);
      Check(dID[i]>0);
      MS.DestList.AssociationTime := i;
      Check(MS.DestList.DestAdd(aClient,sID[i],dID[i])); // associate both lists
      Check(not MS.DestList.DestAdd(aClient,sID[i],dID[i],true)); // no dup
    end;
    aClient.Commit;
    for i := 1 to high(dID) do begin
      Check(MS.DestList.SourceGet(aClient,dID[i],res));
      if not Check(length(res)=1) then
        Check(res[0]=sID[i]);
    end;
    for i := 1 to high(sID) do begin
      Check(MS.DestList.DestGet(aClient,sID[i],res));
      if Check(length(res)=1) then
        continue; // avoid GPF
      Check(res[0]=dID[i]);
      Check(MS.DestList.FillMany(aClient,sID[i])=1);
      Check(MS.DestList.FillOne);
      Check(RecordRef(MS.DestList.Source).ID=sID[i]);
      Check(RecordRef(MS.DestList.Dest).ID=dID[i]);
      Check(MS.DestList.AssociationTime=i);
      Check(not MS.DestList.FillOne);
    end;
  finally
    MD.Free;
    MS.Free;
  end;
end;

In order to retrieve the records, we had to check the Table type from the TRecordReference column value. Therefore, the "modulo" operator had to be used in the corresponding SQL statement.
Unfortunately, all DBMS engines don't implement the "%" operator. For instance, Oracle doesn't. Since we want our ORM to be (in the near future) DBMS-agnostic, we used the more common "MOD()" function.
Note: this is not used any more (TSQLRecord is used instead of TRecordReference).

Since this "MOD()" function is not existing in the default SQLite3 engine, we had to define it in our code. The SQLite3 unit now handles User Defined Functions, via sqlite3_create_function_v2 and corresponding sqlite3_result_* functions. Here is the resulting code... just a few lines to add a function:

procedure InternalMod(Context: pointer;
argc: integer; var argv: TSQLHandleArray); {$ifdef USEC}cdecl;{$endif}
var A1, A2: Int64;
begin // implements the MOD() function, just like Oracle and others
if argc<>2 then
exit; // two parameters expected
A1 := sqlite3_value_int64(argv[0]);
A2 := sqlite3_value_int64(argv[1]);
if A2=0 then // avoid computation exception, returns NULL
sqlite3_result_null(Context) else
sqlite3_result_int64(Context, A1 mod A2);
end;
function TSQLDataBase.DBOpen: integer;
(...)
// register the MOD() user function, similar to the standard % operator
sqlite3_create_function_v2(DB,'MOD',2,SQLITE_UTF8,nil,InternalMod,nil,nil,nil);
end;

Last edited by ab (2010-12-07 07:49:23)

ab · 2010-10-29 07:20:40

The TSQLRestServer.AfterDeleteForceCoherency method now handles specifically TRecordReference, TSQLRecord (fixed a bug) and new 'has many' Source/Dest fields.

This is our internal "FOREIGN KEY" implementation - we choose not to rely on the database engine for that, in order to be independent from the database engine used for the storage...
In fact, SQLite3 introduced FOREIGN KEY in 3.6.19 version only.

When 'has many' Source/Dest fields record is deleted, the whole row of the pivot table is deleted automatically.
This is a feature. If you need another approach (for instance, to maintain "through" data), we could mitigate this by some parameters.

Need your feedback and expectations, here.

WladiD · 2010-10-29 10:01:07

I'm currently study your implementation of "foreign keys" and I think the opposite of TSQLRecordMany.DestAdd is absent.

I mean the possibility to release a connection between A and B without delete it.

Or have I something overlooked/misunderstood?

ab · 2010-10-29 11:35:16

It's feasible, but not directly via a dedicated method.
It's a very good remark!

So I add the ManyDelete method, which will be the exact reverse of the ManyAdd (formerly DestAdd) method.

And a ManySelect method, which is similar to ManyDelete, which will fill the current TSQLRecordMany instance with all corresponding "through" fields.

So I renamed DestAdd into ManyAdd, because Dest didn't have any meaning here.

ab · 2010-10-29 12:09:29

Here is the new unitary testing code, for all these methods (after renaming):

procedure TestMany(aClient: TSQLRestClient);
var MS: TSQLSource;
    MD: TSQLDest;
    i: integer;
    sID, dID: array[1..100] of Integer;
    res: TIntegerDynArray;
begin
  MS := TSQLSource.Create;
  MD := TSQLDest.Create;
  try
    MD.fSignatureTime := Iso8601Now;
    MS.fSignatureTime := MD.fSignatureTime;
    Check(MS.DestList<>nil);
    Check(MS.DestList.InheritsFrom(TSQLRecordMany));
    aClient.TransactionBegin(TSQLSource); // faster process
    for i := 1 to high(sID) do begin
      MD.fSignature := FormatUTF8('% %',[aClient.ClassName,i]);
      sID[i] := aClient.Add(MD,true);
      Check(sID[i]>0);
    end;
    for i := 1 to high(dID) do begin
      MS.fSignature := FormatUTF8('% %',[aClient.ClassName,i]);
      dID[i] := aClient.Add(MS,True);
      Check(dID[i]>0);
      MS.DestList.AssociationTime := i;
      Check(MS.DestList.ManyAdd(aClient,sID[i],dID[i])); // associate both lists
      Check(not MS.DestList.ManyAdd(aClient,sID[i],dID[i],true)); // no dup
    end;
    aClient.Commit;
    for i := 1 to high(dID) do begin
      Check(MS.DestList.SourceGet(aClient,dID[i],res));
      if not Check(length(res)=1) then
        Check(res[0]=sID[i]);
      Check(MS.DestList.ManySelect(aClient,sID[i],dID[i]));
      Check(MS.DestList.AssociationTime=i);
    end;
    for i := 1 to high(sID) do begin
      Check(MS.DestList.DestGet(aClient,sID[i],res));
      if Check(length(res)=1) then
        continue; // avoid GPF
      Check(res[0]=dID[i]);
      Check(MS.DestList.FillMany(aClient,sID[i])=1);
      Check(MS.DestList.FillOne);
      Check(RecordRef(MS.DestList.Source).ID=sID[i]);
      Check(RecordRef(MS.DestList.Dest).ID=dID[i]);
      Check(MS.DestList.AssociationTime=i);
      Check(not MS.DestList.FillOne);
    end;
    aClient.TransactionBegin(TSQLDests); // faster process
    for i := 1 to high(sID) shr 2 do
      Check(MS.DestList.ManyDelete(aClient,sID[i*4],dID[i*4]));
    aClient.Commit;
    for i := 1 to high(sID) do
      if i and 3<>0 then begin
        Check(MS.DestList.ManySelect(aClient,sID[i],dID[i]));
        Check(MS.DestList.AssociationTime=i);
      end else
        Check(not MS.DestList.ManySelect(aClient,sID[i],dID[i]));
  finally
    MD.Free;
    MS.Free;
  end;
end;

WladiD · 2010-10-29 12:30:50

Can you release the v. 1.11 with these changes? The current contents from fossil are somehow not working for me. Thanks in advance.

ab · 2010-10-29 13:29:39

I've updated the fossil repository some minutes ago.
Check http://synopse.info/fossil/info/7f7ca82dbf

This should work as expected.

But I can't release an official 1.11 version just now, because I always want to compile it under Delphi 7, Delphi 2007 and Delphi 2010 IDE before releasing a new version. All unitary tests must pass with all these compilers. And no warning must be displayed under Delphi 2010 (in order to be sure than no data will be corrupted/lost during any string conversion).
And I won't have these compiler at hand before.... next week!!!

ab · 2010-10-29 13:56:53

As far as I can tell, our new implementation via TSQLRecordMany covers all the cases listed in http://guides.rubyonrails.org/association_basics.html

So we could say that our ORM begins to be complete.

Next main step is the database independence.
See http://synopse.info/forum/viewtopic.php?pid=665

WladiD · 2010-11-01 15:49:23

Hi Androu,

I try to implement your new "foreign keys" semantic and so far I was partially successful with following code:

TSQLRecordImage = class;

TSQLRecordImages = class(TSQLRecordMany)
private
    FCreated:TDateTime;
published
    property Source:TRecordReference read FSource;
    property Dest:TRecordReference read FDest;
    property Created:TDateTime read FCreated write FCreated;
end;

TSQLRecordImage = class(TSQLRecord)
private
    FURL:RawUTF8;
    FCreated:TDateTime;
    FImage:TSQLRawBlob;
published
    property URL:RawUTF8 read FURL write FURL stored FALSE;
    property Created:TDateTime read FCreated write FCreated;
    property Image:TSQLRawBlob read FImage write FImage;
end;

TSQLRecordItem = class(TSQLRecord, IInterface, IEBayItemBase, IEBayItemPictures) // I need interfaces at this record
private
    FItemID:RawUTF8;
    ...
    FImages:TSQLRecordImages;
    FPictureURLs:TStringList;
protected
    FRefCount:Integer;
    function QueryInterface(const IID:TGUID; out Obj):HResult; stdcall;
    function _AddRef:Integer; stdcall;
    function _Release:Integer; stdcall;

    function GetItemID:String;
    ...
public
    destructor Destroy; override;

    procedure Assign(const Source:IEBayItemBase); overload;
    procedure Assign(const Source:IEBayItemPictures); overload;

    procedure AfterConstruction; override;
    procedure BeforeDestruction; override;
    class function NewInstance:TObject; override;
published
    property ItemID:RawUTF8 read FItemID write FItemID stored FALSE;
    ...
    property Images:TSQLRecordImages read FImages write FImages;
end;

If the database isn't exist, the tables will be created like expected, but if it already exists an AV occurs at the following point:

procedure TSQLRestServerDB.CreateMissingTables(user_version: cardinal);
...
      end else begin
        // this table is existing: check that all fields do exist -> create if necessary
        DB.GetFieldNames(Fields,Model.Tables[t].SQLTableName);
        with InternalClassProp(Model.Tables[t])^ do begin
          Field := @PropList;
          for f := 0 to PropCount-1 do begin
            if FindRawUTF8(Fields,Field^.Name,false)<0 then begin 
              if not DB.TransactionActive then
                DB.TransactionBegin; 
              DB.Execute(Model.GetSQLAddField(t,f)); // <---HERE an empty query will be passed to DB.Execute
              Model.Tables[t].InitializeTable(self,Field^.Name); 
            end;
            Field := Field^.Next;
          end;
        end;

It want to add a field for TSQLRecordItem.Images:TSQLRecordImages, but that is not a native table field ...

I hope this hint is helpful to find the bug.

ab · 2010-11-01 19:12:45

I'll take a look at that tomorrow.

Just missing a check of TSQLRecordMany class....

Thanks for the feedback.

ab · 2010-11-02 08:12:05

I've corrected the source code trunk in the repository.

It was a bug
Thanks for the detailed report!

See http://synopse.info/fossil/info/e5aad1e03c

WladiD · 2010-11-02 08:36:01

Super. No AV at start of Lauge anymore. Thank you!

ab · 2010-11-02 18:48:31

I'll release the 1.11 version of the framework, when the SQlite3 engine will be released as 3.7.4.

The version number has been upgraded in the SQlite3 official source code repository to 3.7.4, so it could be a matter of days.

Stay tuned!

ab · 2010-12-06 09:20:52

The TSQLRecordMany implementation has been upgraded.

TSQLRecordMany now uses TSQLRecord properties, and not TRecordReference any more.

Here is the declaration of this class:

{{ handle "has many" and "has many through" relationships
- many-to-many relationship is tracked using a table specifically for that
relationship, turning the relationship into two one-to-many relationships
pointing in opposite directions
- by default, only two TSQLRecord (i.e. INTEGER) fields must be created,
named "Source" and "Dest", the first pointing to the source record (the one
with a TSQLRecordMany published property) and the second to the destination record
- you should first create a type inheriting from TSQLRecordMany, which
will define the pivot table, providing optional "through" parameters if needed
! TSQLDest = class(TSQLRecord);
! TSQLSource = class;
! TSQLDestPivot = class(TSQLRecordMany)
! private
! fSource: TSQLSource;
! fDest: TSQLDest;
! fTime: TDateTime;
! published
! property Source: TSQLSource read fSource; // map Source column
! property Dest: TSQLDest read fDest; // map Dest column
! property AssociationTime: TDateTime read fTime write fTime;
! end;
! TSQLSource = class(TSQLRecord)
! private
! fDestList: TSQLDestPivot;
! published
! DestList: TSQLDestPivot read fDestList;
! end;
- in all cases, at leat two 'Source' and 'Dest' published properties must
be declared as TSQLRecord children in any TSQLRecordMany descendant
because they will always be needed for the 'many to many' relationship
- when a TSQLRecordMany published property exists in a TSQLRecord, it is
initialized automaticaly by TSQLRecord.Create
- to add some associations to the pivot table, use the ManyAdd() method
- to retrieve an association, use the ManySelect() method
- to delete an association, use the ManyDelete() method
- to read all Dest records IDs, use the DestGet() method
- to read the Dest records and the associated "through" fields content, use
FillMany then FillRow, FillOne and FillRewind methods to loop through records
- to read all Source records and the associaed "through" fields content,
FillManyFromDest then FillRow, FillOne and FillRewind methods
- to read all Dest IDs after a join to the pivot table, use DestGetJoined }
TSQLRecordMany = class(TSQLRecord)
protected
// internal fields initialized during TSQLRecord.Create
// - map to the Source and Dest properties field values in TSQLRecord values
fSourceID: PPtrInt;
fDestID: PPtrInt;
// - the Source and Dest classes, as defined in so named properties
fSourceClass: TSQLRecordClass;
fDestClass: TSQLRecordClass;
/// retrieve the TSQLRecordMany ID from a given source+dest IDs pair
function InternalIDFromSourceDest(aClient: TSQLRest; aSourceID, aDestID: integer): integer;
function InternalFillMany(aClient: TSQLRest; aID: integer;
const aAndWhereSQL: RawUTF8; isDest: boolean): integer;
public
/// initialize this instance, and needed internal fields
// - will set protected fSourceID/fSourceClass and fDestID/fDestClass fields
constructor Create; overload;
/// retrieve all records associated to a particular source record, which
// has a TSQLRecordMany property
// - returns the Count of records corresponding to this aSource record
// - the records are stored in an internal TSQLTable, refered in the private
// fTable field, and initialized via a FillPrepare call: all Dest items
// are therefore accessible with standard FillRow, FillOne and FillRewind methods
// - use a "for .." loop or a "while FillOne do ..." loop to iterate
// through all Dest items, getting also any additional 'through' columns
// - if source ID parameter is 0, the ID is taken from the fSourceID field
// (set by TSQLRecord.Create)
// - the optional aAndWhereSQL parameter can be used to add any additional
// condition to the WHERE statement (e.g. 'Salary>1000 AND Salary<2000')
// according to TSQLRecordMany properties
// - note that if the Source record has just been added, fSourceID is not
// set, so this method will fail: please specify aSourceID parameter with
// the one just added/created
function FillMany(aClient: TSQLRest; aSourceID: integer=0;
const aAndWhereSQL: RawUTF8=''): integer;
/// retrieve all records associated to a particular Dest record, which
// has a TSQLRecordMany property
// - returns the Count of records corresponding to this aSource record
// - use a "for .." loop or a "while FillOne do ..." loop to iterate
// through all Dest items, getting also any additional 'through' columns
// - the optional aAndWhereSQL parameter can be used to add any additional
// condition to the WHERE statement (e.g. 'Salary>1000 AND Salary<2000')
// according to TSQLRecordMany properties
function FillManyFromDest(aClient: TSQLRest; aDestID: integer;
const aAndWhereSQL: RawUTF8=''): integer;
/// retrieve all Dest items IDs associated to the specified Source
function DestGet(aClient: TSQLRest; aSourceID: integer; out DestIDs: TIntegerDynArray): boolean; overload;
/// retrieve all Dest items IDs associated to the current Source ID
// - source ID is taken from the fSourceID field (set by TSQLRecord.Create)
// - note that if the Source record has just been added, fSourceID is not
// set, so this method will fail: please call the other overloaded method
function DestGet(aClient: TSQLRest; out DestIDs: TIntegerDynArray): boolean; overload;
/// retrieve all Source items IDs associated to the specified Dest ID
function SourceGet(aClient: TSQLRest; aDestID: integer; out SourceIDs: TIntegerDynArray): boolean;
/// retrieve all Dest items IDs associated to the current or
// specified Source ID, adding a WHERE condition against the Dest rows
// - if aSourceID is 0, the value is taken from current fSourceID field
// (set by TSQLRecord.Create)
// - aDestWhereSQL can specify the Dest table name in the statement, e.g.
// 'Employee.Salary>1000'
// - this is faster than a manual FillMany() then loading each Dest,
// because the condition is executed in the SQL statement by the server
function DestGetJoined(aClient: TSQLRestClient; const aDestWhereSQL: RawUTF8;
aSourceID: Integer; out DestIDs: TIntegerDynArray): boolean; overload;
/// create a Dest record, then FillPrepare() it to retrieve all Dest items
// associated to the current or specified Source ID, adding a WHERE condition
// against the Dest rows
// - if aSourceID is 0, the value is taken from current fSourceID field
// (set by TSQLRecord.Create)
// - aDestWhereSQL can specify the Dest table name in the statement, e.g.
// 'Employee.Salary>1000'
function DestGetJoined(aClient: TSQLRestClient; const aDestWhereSQL: RawUTF8;
aSourceID: Integer): TSQLRecord; overload;
/// create a TSQLTable, containing all specified Fields, after a JOIN
// associated to the current or specified Source ID
// - the Table will have the fields specified by the JoinKind parameter
// - if aSourceID is 0, the value is taken from current fSourceID field
// (set by TSQLRecord.Create)
// - aDestWhereSQL can specify the Dest table name in the statement, e.g.
// 'Employee.Salary>1000'
function DestGetJoinedTable(aClient: TSQLRestClient; aDestWhereSQL: RawUTF8;
aSourceID: Integer; JoinKind: TSQLRecordManyJoinKind): TSQLTable;
/// add a Dest record to the Source record list
// - returns TRUE on success, FALSE on error
// - if NoDupplicates is TRUE, the existence of this Source/Dest ID pair
// is first checked
// - current Source and Dest properties are filled with the corresponding
// TRecordReference values corresponding to the supplied IDs
// - any current value of the additional fields are used to populate the
// newly created content (i.e. all published properties of this record)
function ManyAdd(aClient: TSQLRest; aSourceID, aDestID: Integer;
NoDupplicates: boolean=false): boolean; overload;
/// add a Dest record to the current Source record list
// - source ID is taken from the fSourceID field (set by TSQLRecord.Create)
// - note that if the Source record has just been added, fSourceID is not
// set, so this method will fail: please call the other overloaded method
function ManyAdd(aClient: TSQLRest; aDestID: Integer;
NoDupplicates: boolean=false): boolean; overload;
/// will delete the record associated with a particular Source/Dest pair
// - will return TRUE if the pair was found and successfully deleted
function ManyDelete(aClient: TSQLRest; aSourceID, aDestID: Integer): boolean; overload;
/// will delete the record associated with the current source and a specified Dest
// - source ID is taken from the fSourceID field (set by TSQLRecord.Create)
// - note that if the Source record has just been added, fSourceID is not
// set, so this method will fail: please call the other overloaded method
function ManyDelete(aClient: TSQLRest; aDestID: Integer): boolean; overload;
/// will retrieve the record associated with a particular Source/Dest pair
// - will return TRUE if the pair was found
// - in this case, all "through" columns are available in the TSQLRecordMany
// field instance
function ManySelect(aClient: TSQLRest; aSourceID, aDestID: Integer): boolean; overload;
/// will retrieve the record associated with the current source and a specified Dest
// - source ID is taken from the fSourceID field (set by TSQLRecord.Create)
// - note that if the Source record has just been added, fSourceID is not
// set, so this method will fail: please call the other overloaded method
function ManySelect(aClient: TSQLRest; aDestID: Integer): boolean; overload;
// get the SQL WHERE statement to be used to retrieve the associated
// records according to a specified ID
// - search for aID as Source ID if isDest is FALSE
// - search for aID as Dest ID if isDest is TRUE
// - the optional aAndWhereSQL parameter can be used to add any additional
// condition to the WHERE statement (e.g. 'Salary>1000 AND Salary<2000')
// according to TSQLRecordMany properties
function IDWhereSQL(aClient: TSQLRest; aID: integer; isDest: boolean;
const aAndWhereSQL: RawUTF8=''): RawUTF8;
end;

You've some new methods, perhaps the most powerful could be DestGetJoinTable(), which creates a TSQLTable, containing all specified Fields, after a JOIN associated to the current or specified Source ID. The JOIN extend is specified by an enumeration:

  /// the kind of fields to be available in a Table resulting of
  // a TSQLRecordMany.DestGetJoinedTable() method call
  // - Source fields are not available, because they will be always the same for
  // a same SourceID, and they should be available from the TSQLRecord which
  // hold the TSQLRecordMany instance
  TSQLRecordManyJoinKind = (
    jkDestID, jkPivotID, jkDestFields, jkPivotFields, jkPivotAndDestFields);

Implementation sounds almost complete now.

Thanks to migajek for its feedback and ideas - see http://synopse.info/forum/viewtopic.php?id=164

coblongpamor · 2011-03-23 05:53:22

ab wrote:

You've some new methods, perhaps the most powerful could be DestGetJoinTable(), which creates a TSQLTable, containing all specified Fields, after a JOIN associated to the current or specified Source ID. The JOIN extend is specified by an enumeration:
  /// the kind of fields to be available in a Table resulting of
  // a TSQLRecordMany.DestGetJoinedTable() method call
  // - Source fields are not available, because they will be always the same for
  // a same SourceID, and they should be available from the TSQLRecord which
  // hold the TSQLRecordMany instance
  TSQLRecordManyJoinKind = (
    jkDestID, jkPivotID, jkDestFields, jkPivotFields, jkPivotAndDestFields);

If i use this function bellow, i'll get all Dest Field.

DestGetJoinedTable(Client,'',AID,jkDestFields);

for example, i'll get ID,Field1,Field2,Field3,Field4,Field5,...

but how i can get only part of Dest Field (not all Dest field)
maybe if possible, i can write the code like

DestGetJoinedTable(Client,'',AID,jkDestFields,[Field1,Field2,Field3]);

or more simple parameter than [Field1,Field2,Field3]

Thanks AB.

ab · 2011-03-23 07:59:59

Good idea.

I've added an optional parameter to the method definition:

function TSQLRecordMany.DestGetJoinedTable(aClient: TSQLRestClient;
  const aDestWhereSQL: RawUTF8; aSourceID: Integer; JoinKind: TSQLRecordManyJoinKind;
  const aCustomFieldsCSV: RawUTF8): TSQLTable;

Then renamed/enhanced the JoinKind values:

type
  /// the kind of fields to be available in a Table resulting of
  // a TSQLRecordMany.DestGetJoinedTable() method call
  // - Source fields are not available, because they will be always the same for
  // a same SourceID, and they should be available from the TSQLRecord which
  // hold the TSQLRecordMany instance
  // - jkDestID and jkPivotID will retrieve only DestTable.ID and PivotTable.ID
  // - jkDestFields will retrieve DestTable.* simple fields, or the fields
  // specified by aCustomFieldsCSV (the Dest table name will be added: e.g.
  // for aCustomFieldsCSV='One,Two', will retrieve DestTable.One, DestTable.Two)
  // - jkPivotFields will retrieve PivotTable.* simple fields, or the fields
  // specified by aCustomFieldsCSV (the Pivot table name will be added: e.g.
  // for aCustomFieldsCSV='One,Two', will retrieve PivotTable.One, PivotTable.Two)
  // - jkPivotAndDestAllFields for PivotTable.* and DestTable.* simple fields,
  // or will retrieve the specified aCustomFieldsCSV fields (with
  // the table name associated: e.g. 'PivotTable.One, DestTable.Two')
  TSQLRecordManyJoinKind = (
    jkDestID, jkPivotID, jkDestFields, jkPivotFields, jkPivotAndDestFields);

I think it will fulfill your needs.

See http://synopse.info/fossil/info/c322380e76

coblongpamor · 2011-03-23 08:34:57

This is exactly what i need.
thanks..

esmondb · 2011-03-25 11:35:11

Could an overloaded version of DestGetJoinedTable be added which can be passed a TSQLRestServer instead of client? I've put a version below which seems to work for me.

function TSQLRecordMany.DestGetJoinedTable(aServer: TSQLRestServer;
  const aDestWhereSQL: RawUTF8; aSourceID: Integer; JoinKind: TSQLRecordManyJoinKind;
  const aCustomFieldsCSV: RawUTF8): TSQLTable;
var Select, SQL, Resp, resphead: RawUTF8;
    SelfProps, DestProps: TSQLRecordProperties;
procedure SelectFields(const Classes: array of TSQLRecordProperties);
var i: integer;
begin
  for i := 0 to high(Classes) do begin
    Select := Select+Classes[i].SQLTableSimpleFields[True,True];
    if i<high(Classes) then
      Select := Select+',';
  end;
end;
begin
  result := nil;
  if (Self=nil) or (fSourceClass=nil) or (fDestClass=nil) or (aServer=nil) then
    exit;
  if aSourceID=0 then
    if fSourceID<>nil then
      aSourceID := fSourceID^;
  if aSourceID=0 then
    exit;
  DestProps := fDestClass.RecordProps;
  SelfProps := self.RecordProps;
  case JoinKind of
    jkDestID:
      Select := DestProps.SQLTableName+'.ID';
    jkPivotID:
      Select := SelfProps.SQLTableName+'.ID';
    jkDestFields:
      if aCustomFieldsCSV='' then
        SelectFields([DestProps]) else
        Select := AddPrefixToCSV(pointer(aCustomFieldsCSV),DestProps.SQLTableName+'.');
    jkPivotFields:
      if aCustomFieldsCSV='' then
        SelectFields([SelfProps]) else
        Select := AddPrefixToCSV(pointer(aCustomFieldsCSV),SelfProps.SQLTableName+'.');
    jkPivotAndDestFields:
      if aCustomFieldsCSV='' then
        SelectFields([SelfProps,DestProps]) else
        Select := aCustomFieldsCSV;
  end;
  if aDestWhereSQL='' then
    // fast inlined prepared statement
    SQL := 'SELECT % FROM %,% WHERE %.Source=:(%): AND %.Dest=%.ID' else
    if PosEx(':(',aDestWhereSQL,1)>0 then
      // statement is globaly inlined -> cache prepared statement
      SQL := 'SELECT % FROM %,% WHERE %.Source=:(%): AND %.Dest=%.ID AND %' else
      // statement is not globaly inlined -> no caching of prepared statement
      SQL := 'SELECT % FROM %,% WHERE %.Source=% AND %.Dest=%.ID AND %';

  SQL := FormatUTF8(pointer(SQL),
    [Select, DestProps.SQLTableName, SelfProps.SQLTableName,
     SelfProps.SQLTableName, aSourceID, SelfProps.SQLTableName, DestProps.SQLTableName, aDestWhereSQL]);
  with aServer.URI(aServer.Model.Root,'GET',SQL,Resp,resphead,SUPERVISOR_ACCESS_RIGHTS) do
    if Lo=200 then begin // GET with SQL sent
      result := TSQLTableJSON.Create([PPointer(Self)^,fDestClass],SQL,Resp);
      result.fInternalState := Hi;
    end else // get data
      result := nil;
end;

ab · 2011-03-25 12:37:02

I've made two modifications:
- TSQLRecordMany.DestGetJoined* methods now accept generic TSQLRest class
- ExecuteList defined in TSQLRest, so now available also in TSQLRestServer

See http://synopse.info/fossil/info/f2f0a44d64

So it's not an overloaded method with a lot of duplicate code, but a more generic behavior of the TSQLRest class.

esmondb · 2011-03-25 15:08:11

Thanks, that seems a much better way.

ab · 2011-12-07 08:59:30

Automatic JOIN query has been implemented.

It is a very nice enhancement to handle many-to-many relationship.

See http://blog.synopse.info/post/2011/12/0 … JOIN-query

coblongpamor · 2012-04-28 07:44:27

could you add GetSourceClass and GetDestClass to the TSQLRecordMany?

thank you.

ab · 2012-04-28 07:54:17

I've published RecordManySourceProp / RecordManyDestProp / RecordManySourceClass / RecordManyDestClass to the TSQLRecordProperties.

See http://synopse.info/fossil/info/f7a31b4586

coblongpamor · 2012-04-28 16:49:07

thank you. so, now i can write something like this:

DestClass:= PM.fMany.RecordProps.RecordManyDestClass;
PM.Dest:= DestClass.Create;

ab · 2012-04-28 17:48:37

In your normal code, you should better know the exact type of PM.
But I think you are using a more generic functionality here, for all TSQLRecordMany.

Be aware that:
- You'll have to destroy the Dest instance created as such manually;
- If you use a JOINed query, the Dest instance may be created by the framework.

coblongpamor · 2012-04-28 19:14:18

In your normal code, you should better know the exact type of PM.

Yes, i understand.

But I think you are using a more generic functionality here, for all TSQLRecordMany.

You are right. i like control generation from the RecordEditForm.
i try to inheriting RecordEditForm, then modify it to agree with my requirement.

But, sometimes i have AV error that i can not fix.
many times debuger break at the line 15057 of the SQLite3Commons

if MainField[ReturnFirstIfNoUnique]<0 then

also at the line 13952

mov edx,[eax+vmtAutoTable]

i just doubleclick the row in the MainForm then press the Save button.

You'll have to destroy the Dest instance created as such manually;

Yes, i understand.

If you use a JOINed query, the Dest instance may be created by the framework.

i don't understand with this one. did you mean that it also need destroy by manualy?

ab · 2012-04-29 17:43:02

About the AV, this is weird...
Sounds like the TSQLRecordProperties were not properly initialized.
RecordEditForm will work only with TSQLRecord.
Do you have any third-party unit which may use vmtAutoTable? Like a memory manager or such?
If you have the source code of all the units of your project, could you search for vmtAutoTable entry?

During a JOINed query - see http://blog.synopse.info/post/2011/12/0 … JOIN-query - the Dest property is populated with a true class instance.

This instance is cleared when the record is freed, but will need a call to FillClose otherwise.
See the documentation:

Explicit call to FillClose has been added in order to release all Dest instances created in FillPrepareMany. This call is not mandatory if you call MS.Free directly, but it is required if the same MS instance is about to use some regular many-to-many methods, like MS.DestList.ManySelect() - it will prevent any GPF exception to occur with code expecting the Dest property not to be an instance, but a pointer(DestID) value.

So if you create your Dest instance, just ensure that you won't call a FillPrepareMany loop, without previously freeing it.

coblongpamor · 2012-04-30 00:20:45

about vmtAutoTable, i think, i'm not using it for now.
i only use third-party grid component, but with conditional defines {$IFDEF USENEXTPACK}
i suspect the problem is that the Tab.CurrentRecord sometimes not get the right record.

...
faEdit: begin
      if Tab.Retrieve(Client,Tab.List.Row,False) then
      try
          isOK:= CRUD(Tab.CurrentRecord,'',False);
          if isOK then
            if Client.Update(Tab.CurrentRecord) then
              Ribbon.GotoRecord(Tab.CurrentRecord);
      finally
        Client.UnLock(Tab.CurrentRecord);
      end;
    end;
....

//CRUD
function TfrmMain.CRUD(aRec: TSQLRecord; const aTitle: string; aReadOnly: boolean): boolean;
var
  BPBaseEditForm: TBPBaseEditForm;
begin
  BPBaseEditForm:= TBPBaseEditForm.Create(Self);
  try
    BPBaseEditForm.SetRecord(Client,aRec,nil,Ribbon,'',0,'');
    Result := BPBaseEditForm.ShowModal= mrOk;
  finally
    BPBaseEditForm.Free;
  end;
end;

i'll try to step by step debuging more carefully.
ok i'll take a look the documentation again about TSQLRecordMany.

Thank you

coblongpamor · 2012-05-01 03:55:17

I think I found the cause that triggered the emergence of AV.
AV would happen if I do double click on "another row" in the grid with a "very fast", but it never happened if I did it with normal speed.
But if we choose the line first, then the AV will not happen even if we do a very quick double click.

There may be issues on TSQLLister.OnSelectCell ()?

ab · 2012-05-01 05:45:22

What do you mean "another row"?

coblongpamor · 2012-05-01 07:45:28

sorry for my english, that made you confused.
i mean, from current selected row to another row.
for example:
current selected row is row 1, then immediately i double click another row quickly.

note: this AV will not occure in synfile main demo, only on my project with using RecordEditForm.

ab · 2012-05-01 10:29:57

OK.
This makes the issue difficult to track, if not reproducible with standard code.

Perhaps the selected ID is to be used instead of Tab.CurrentRecord.
You may have the selection double-click triggered during the RecordEditForm display.

mORMot Open Source

#1 2010-10-29 05:56:28

"has many" and "has many through" relationships

#2 2010-10-29 07:20:40

Re: "has many" and "has many through" relationships

#3 2010-10-29 10:01:07

Re: "has many" and "has many through" relationships

#4 2010-10-29 11:35:16

Re: "has many" and "has many through" relationships

#5 2010-10-29 12:09:29

Re: "has many" and "has many through" relationships

#6 2010-10-29 12:30:50

Re: "has many" and "has many through" relationships

#7 2010-10-29 13:29:39

Re: "has many" and "has many through" relationships

#8 2010-10-29 13:56:53

Re: "has many" and "has many through" relationships

#9 2010-11-01 15:49:23

Re: "has many" and "has many through" relationships

#10 2010-11-01 19:12:45

Re: "has many" and "has many through" relationships

#11 2010-11-02 08:12:05

Re: "has many" and "has many through" relationships

#12 2010-11-02 08:36:01

Re: "has many" and "has many through" relationships

#13 2010-11-02 18:48:31

Re: "has many" and "has many through" relationships

#14 2010-12-06 09:20:52

Re: "has many" and "has many through" relationships

#15 2011-03-23 05:53:22

Re: "has many" and "has many through" relationships

#16 2011-03-23 07:59:59

Re: "has many" and "has many through" relationships

#17 2011-03-23 08:34:57

Re: "has many" and "has many through" relationships

#18 2011-03-25 11:35:11

Re: "has many" and "has many through" relationships

#19 2011-03-25 12:37:02

Re: "has many" and "has many through" relationships

#20 2011-03-25 15:08:11

Re: "has many" and "has many through" relationships

#21 2011-12-07 08:59:30

Re: "has many" and "has many through" relationships

#22 2012-04-28 07:44:27

Re: "has many" and "has many through" relationships

#23 2012-04-28 07:54:17

Re: "has many" and "has many through" relationships

#24 2012-04-28 16:49:07

Re: "has many" and "has many through" relationships

#25 2012-04-28 17:48:37

Re: "has many" and "has many through" relationships

#26 2012-04-28 19:14:18

Re: "has many" and "has many through" relationships

#27 2012-04-29 17:43:02

Re: "has many" and "has many through" relationships

#28 2012-04-30 00:20:45

Re: "has many" and "has many through" relationships

#29 2012-05-01 03:55:17

Re: "has many" and "has many through" relationships

#30 2012-05-01 05:45:22

Re: "has many" and "has many through" relationships

#31 2012-05-01 07:45:28

Re: "has many" and "has many through" relationships

#32 2012-05-01 10:29:57

Re: "has many" and "has many through" relationships

Board footer