TSQLRecord in TCollectionItem

lele9 · 2012-05-18 10:07:15

hi there,
i have a TSQLRecord class with a TCollection property.
This property store a collection of TCollectionItem descendant thant contain a TSQLRecord property.
a little example:

type
   TCondomino = class (TCollectionItem)
   private
    FPersona: TSQLPersona;
    FMillesimi: Integer;
    procedure setPersona(const Value: TSQLPersona);
   published
    property Persona: TSQLPersona read FPersona write setPersona;
    //property Persona: TSQLPersona read FPersona write FPersona;
    property Millesimi: Integer read FMillesimi write FMillesimi;
   end;

   TCondomini = class (TCollection)

   end;

   TSQLCondominio = class (TSQLRecord)
   private
    FNome: RawUTF8;
    FIndirizzo: RawUTF8;
    FComune: TSQLComune;
    FCondomini: TCondomini;
    FDataCreazione: TCreateTime;
    FUltimaModifica: TModTime;
   public
    constructor Create; Override;
    destructor Destroy; Override;
   published
    property Nome: RawUTF8 read FNome write FNome;
    property Indirizzo: RawUTF8 read FIndirizzo write FIndirizzo;
    property Comune: TSQLComune read FComune write FComune;
    property Condomini: TCondomini read FCondomini write FCondomini;
    property DataCreazione: TCreateTime read FDataCreazione write FDataCreazione;
    property UltimaModifica: TModTime read FUltimaModifica write FUltimaModifica;
   end;

implementation

{ TSQLCondominio }

constructor TSQLCondominio.Create;
begin
   inherited;
   FCondomini := TCondomini.Create(TCondomino);
end;

destructor TSQLCondominio.Destroy;
begin
   FreeAndNil(FCondomini);
   inherited;
end;

{ TCondomino }

procedure TCondomino.setPersona(const Value: TSQLPersona);
begin
   Self.FPersona := TSQLPersona(Value.ID);
end;

when i save TSQLCondominio Object with empty collection its all ok but if there is a collectionitem in collection i have an access violation
in TJSONSerializer.WriteObject (SQLLite3Commons).
Where i mistake?
thanks,
Emanuele

ab · 2012-05-18 12:45:14

Do not use a TSQLRecord in a TCollection item!

The TSQLRecord type is to be used only for CRUD operations of the ORM part of the framework.

You have to use plain TPersistent children to for a TCollection.

Take also a look at the dynamic array handling, which now includes JSON custom serialization.
It is a good way of storing collections of "value objects" (in the Domain Driven vocabulary).

For a native way of implementing lists of TSQLRecord, you'll have to use TSQLRecordMany, and the associated methods.
It will use a "classic" pivot table, like any RDBMS, and creates JOINed queries for you.
See the documentation.

In fact, there are several families of ORM design (perhaps more):
- Map collections into JOINed query from the ORM (i.e. pivot tables are abstracted by the framework, to implement "has many" relationship, but you will have to define lazy loading and such);
- Explicitly handle pivot tables as ORM classes, and provide methods to access to them (it will allow both "has many" and "has many through" relationship).
- Store collections within the ORM classes property (data sharding).

In mORMot, we do not handle the 1st kind, but the 2nd and 3rd families.
In mORMot, there is no native handling of TSQLRecord collections (as they do appear in the first family of ORMs): you are mapping the DB with dedicated TSQLRecordMany classes, which allows some true pivot table to be available (that is the 2nd family).
It has some powerful methods - like a JOINed query - but it sounds definitively more easy to use TCollections (of TPersistent) or dynamic arrays, and data sharding (i.e. the 3rd family), for most applications.

lele9 · 2012-05-18 14:30:56

hi ab,
thanks for the answer. so the correct way is something like this?

type
  
   TSQLCondominio = class;

   TSQLCondomini = class (TSQLRecordMany)
   private
    FCondominio: TSQLCondominio;
    FPersona: TSQLPersona;
    FMillesimi: Integer;
   published
    property Source: TSQLCondominio read FCondominio;
    property Persona: TSQLPersona read FPersona;
    property Millesimi: Integer read FMillesimi write FMillesimi;
   end;

   TSQLCondominio = class (TSQLRecord)
   private
    FNome: RawUTF8;
    FIndirizzo: RawUTF8;
    FComune: TSQLComune;
    FCondomini: TSQLCondomini;
    FDataCreazione: TCreateTime;
    FUltimaModifica: TModTime;
   published
    property Nome: RawUTF8 read FNome write FNome;
    property Indirizzo: RawUTF8 read FIndirizzo write FIndirizzo;
    property Comune: TSQLComune read FComune write FComune;
    property Condomini: TSQLCondomini read FCondomini write FCondomini;
    property DataCreazione: TCreateTime read FDataCreazione write FDataCreazione;
    property UltimaModifica: TModTime read FUltimaModifica write FUltimaModifica;
   end;

its right?
thanks,
Emanuele.

lele9 · 2012-05-18 14:32:26

ooops...

published
    property Source: TSQLCondominio read FCondominio;
    property Persona: TSQLPersona read FPersona;              -->      property Dest: TSQLPersona read FPersona;
    property Millesimi: Integer read FMillesimi write FMillesimi;
   end;

ab · 2012-05-18 15:37:51

Yes, you need Source and Dest properties.
And then you can use the TSQLCondominio.Condomini auto-created instance to handle the pivot table.

I've refactored/updated ORM documentation about Objects relationship (i.e. cardinality).
See http://synopse.info/fossil/info/064dd5562c

Hope it helps.

lele9 · 2012-05-18 18:07:41

hi ab,
your help its perfect always.
another, i think stupid question but i try...
i can save to orm TSQLCondomini in the same moment of TSQLCondominio?
more generic can i save to orm TSQLRecord Dest in the same moment of TSQLRecord Source or is necessary save the source, get the id and then save the dest?

ab · 2012-05-18 19:40:59

You'll need to save the pivot table content separately from the main table, using the dedicated methods of the TSQLRecordMany instance of the main TSQLRecord.

lele9 · 2012-05-19 08:20:24

And what's happen if i use manyadd method when source record is new record (id =0)?

ab · 2012-05-21 05:09:25

There is a clear split between the main TSQLRecord and its TSQLRecordMany content.

You can have a JOINed read of both classes content, using the dedicate FillPrepareMany method.
See http://blog.synopse.info/post/2011/12/0 … JOIN-query

But you can't have a direct write of both classes content.
In fact, you'll have to follow the classic RDBMS many-to-many pattern:
- Create the Source TSQLRecord content;
- Create the Dest TSQLRecord content, if it is not yet existing;
- Create the pivot table rows one by one.

If you want a direct read/write of your main TSQLRecord content, you should better use a dynamic array or a TCollection of TPersistent classes.
It will implement data sharding at the record level. See http://blog.synopse.info/post/2011/07/0 … chitecture

lele9 · 2012-05-21 10:22:30

thanks i understand! now its ok!

lele9 · 2012-05-21 16:59:40

hi ab,
i know...from yesterday you hate me i have another question about sqlrecordmany.
in a class i can declare a property like this
property XXX: Integer read getXXX;
where getXXX is a function that find XXX value.
so, can i have, for example, a property like this in my TSQLCondominio class:
property MillesimiTot: Integer read getMillesimiTot;
where getMillesimiTot loop in TSQLCondomini (TSQLRecordMany property) to find the sum of Millesimi of each Condomino in the Pivot Table.
it's possible?or the only way is declare a public function getMillesimiTot(aClient: tsqlRest): Integer and loop with fillmany/fillone?
thanks,
Emanuele.

ab · 2012-05-21 17:36:09

The ORM only handle published properties.

So you can define any public property, with a getter, within the record definition.
But I suspect you'll need a reference to the underlying TSQLRest instance... So perhaps a method is better.

In all cases, a property with a getter will execute the getter method.
This is just a syntaxic sugar of Delphi.

And no, I do not hate you!

mORMot Open Source

#1 2012-05-18 10:07:15

TSQLRecord in TCollectionItem

#2 2012-05-18 12:45:14

Re: TSQLRecord in TCollectionItem

#3 2012-05-18 14:30:56

Re: TSQLRecord in TCollectionItem

#4 2012-05-18 14:32:26

Re: TSQLRecord in TCollectionItem

#5 2012-05-18 15:37:51

Re: TSQLRecord in TCollectionItem

#6 2012-05-18 18:07:41

Re: TSQLRecord in TCollectionItem

#7 2012-05-18 19:40:59

Re: TSQLRecord in TCollectionItem

#8 2012-05-19 08:20:24

Re: TSQLRecord in TCollectionItem

#9 2012-05-21 05:09:25

Re: TSQLRecord in TCollectionItem

#10 2012-05-21 10:22:30

Re: TSQLRecord in TCollectionItem

#11 2012-05-21 16:59:40

Re: TSQLRecord in TCollectionItem

#12 2012-05-21 17:36:09

Re: TSQLRecord in TCollectionItem

Board footer