#1 2017-03-18 20:43:00

George
Member
Registered: 2016-04-05
Posts: 140

ORM and Dynamic models

ORM can help if application has good knowledge about data structure and relations.
I mean, properly designed classes with required fields, predefined models and so on.

Other interesting and not trivial situation - when server have no complete information about data model until start.
Such behavior often seen in business software that allow add metadata on the fly (by metadata, i mean information, that describe data model).
On server side, in best way, server knows only about base classes, while client side may take serialized base class as template, add own fields and tell server - hello, i have new data structure "TCustomObj(TBaseClass)", please make all necessary changes in database.
But, new data structure may include not only simple fields, but also relations to another custom data structures like TSecondCustomObj(TBaseClass).

I know that other available ORM's not fits this requirement, which is predictable due rareness.
I'm almost sure that mORMot can't help here too, but if i'm wrong, possibilities can blow mind smile

Last edited by George (2017-03-18 20:43:14)

Offline

#2 2017-03-18 21:13:45

ab
Administrator
From: France
Registered: 2010-06-21
Posts: 14,207
Website

Re: ORM and Dynamic models

Forget about the RDBMS - "relational" model.
Thanks to its TDocVariant support, mORMot's ORM can do exactly what you describe: schema-less information.
Then you will be able to store it in your local SQLite3 databases, or, very efficiently, in NoSQL/MongoDB engines.
This is the pattern we use in production, with great benefit.

Offline

#3 2017-03-18 22:50:56

George
Member
Registered: 2016-04-05
Posts: 140

Re: ORM and Dynamic models

Thanks to its TDocVariant support, mORMot's ORM can do exactly what you describe: schema-less information.

Thanks, that's sounds hopeful!

Actually MongoDB 3.4 is most interesting option, it supports document validation, it use new storage engine (WiredTiger) that provide locks per document which was improved performance.
But i'm afraid about data consistency and integrity.
Even "isolated write" operation does not provide “all-or-nothing” atomicity (also it locks entire collection which break down performance).

But, MongoDB provide big freedom for architectural design.
Assume that is possible to use single collection for all custom objects (even came from different user classes. Validator will check data depending on "field" with type name).
Fields with "document references" (in cooperation with validation rules) will work as primary keys.
Main problem is how to change few documents in single transaction.
Two Phase Commits can help here. Probably hmm

This is the pattern we use in production, with great benefit.

And you not experience any data inconsistencies?

Last edited by George (2017-03-18 23:07:11)

Offline

#4 2017-03-18 23:09:48

George
Member
Registered: 2016-04-05
Posts: 140

Re: ORM and Dynamic models

Also, interesting thing, for storing files they implemented GridFS, does mORMot support it?
-- regarding change log and current source file, answer is - no.

Do you suggest direct access to mongoDB via SynMongoDB (is it enough updated to work with mongoDB 3.4?)
or maybe use somehow with mORMot's ORM firedac connector (which not support GridFS too)?
or would be better for now to use node.js library with all new features like GridFS (from SpiderMonkey)?

Last edited by George (2017-03-18 23:40:36)

Offline

#5 2017-03-19 08:49:04

ab
Administrator
From: France
Registered: 2010-06-21
Posts: 14,207
Website

Re: ORM and Dynamic models

You are still reasoning in terms of relational database design.
There is no such thing as a transaction in MongoDB and you don't need it.
We are supposed to switch from the relational model to the aggregate model - see our doc.
A single collection storing everything is not a good idea: you need some kind  of schema at least to index the main fields for efficient loookup.
SynMongoDB is integrated in the framework and is faster and has no dependency.
If you really need truly acid storage, use a SQL engine for it.

GridFS is something else and we don't support it yet.
We didn't need it - our blobs are smaller than 16mb.
And it sounded like an half backed features to me: you have a much better solution with distributed file systems in Linux or BSD, for storage of bigger content.

Offline

#6 2017-03-20 18:44:30

George
Member
Registered: 2016-04-05
Posts: 140

Re: ORM and Dynamic models

Thanks! I appreciate your opinion.

Sometimes it is impossible to completely avoid logical references between entities, that mean, some kind of transactions are required (at least on application level).
Pretty hard to provide freedom for users to customize data structures, without native transactions support from DBMS...

SQLite3 support only single writer and as stated on their site, SQLite cant replace big RDBMS like MSSQL or PostgreSQL.
I expect heavy load to database system from different threads, each with own connection (probably from pool).

PostgresSQL:
Since PostgreSQL manual promise advanced JSON support, it may be a solution that gives freedom and data consistency in same time.
https://www.postgresql.org/docs/9.6/sta … -json.html
https://www.postgresql.org/docs/current … -json.html

https://blog.2ndquadrant.com/jsonb-type … resql-9-4/
Conclusions

With the introduction of the JSONB type and the GIN indexes
using jsonb_path_ops operator class, PostgreSQL combines the elasticity of the JSON format at an amazing data access speed.

Today it is thus possible to store and process data in JSON format with high performance while enjoying the robustness and flexibility that PostgreSQL has habitually provided us with over the years.

http://blog.endpoint.com/2013/06/postgr … ation.html
On the other hand you could of course add a trigger checking the JSON, before saving it to database, to check the list of available fields. This way you could prevent adding new fields by the application.

MongoDB
I like how looks Mongo site, installer, folder structure, admin UI pretty stylish and handy, CLI access present too, (single process, while PostgreSQL use 5-6 processes to operate), many other advantages.
Only one limitation - no atomic batches, that feature was requested in 2010, hope, someday they add any kind of internal transactions that will work when requested (while in most cases it should not be used).
They really have such plans in JIRA tracker, but without dates.
I still suppose that for now, transactions may be implemented on application layer (when impossible to aggregate some data).

Do you have any negative experience with PostgreSQL?

Last edited by George (2017-03-21 11:26:16)

Offline

#7 2017-03-21 16:58:34

ab
Administrator
From: France
Registered: 2010-06-21
Posts: 14,207
Website

Re: ORM and Dynamic models

In practice, SQlite3 is faster than most databases.

The idea to maximize performance are:
- to maintain several SQLite3 databases, depending on the data stored (perhaps using remote ORM tables)
- use TSQLRestBactch to write in them
- enable the mORMot ORM cache.

On production, our SQlite3 databases have response time in a dozen microseconds, or less than a microsecond from cache.
So, if properly done, sequential access is not noticeable.
Under heavy load, with batches running in separated threads writing on separated Sqlite3 instances, performance is awesome, on really heavy load (dozens of thousands of IoT devices sending events in real time).
It is much faster that we could have achieved with a classical RDBMS.

PostgreSQL is just great, and JSONB is really a killing feature.
We use PostgreSQL with some older projects, and it is clearly a killer DB - a real alternative to MSSQL or Oracle, for most usecases.

Offline

#8 2017-03-21 17:26:37

George
Member
Registered: 2016-04-05
Posts: 140

Re: ORM and Dynamic models

PostgreSQL is just great, and JSONB is really a killing feature.
We use PostgreSQL with some older projects, and it is clearly a killer DB - a real alternative to MSSQL or Oracle, for most usecases.

Awesome! That's just what i wanted to read)

Found awesome tool for PostgreSQL - http://pgmodeler.com.br/ (not just modeler).
Open source or 10$ for pre-compiled version.
Similar software for other DBMS is about 1000$ and higher with less functionality.

I like SQLite3 too, (i used it for small data with low IO), work like a charm.

Offline

#9 2024-03-12 10:48:04

Vit
Member
Registered: 2024-03-12
Posts: 8

Re: ORM and Dynamic models

Hello there
This topic very interesting for me, as looking for any ORM with possibility to add fields on the fly (for example customized by user, but not available in "base" class).
What I understood I have to:
1) use not relational model, but aggregate model instead
2) use TDocVariant 

This is not clear for me, but will my possible solution "map" on DB field to one vaiants property?

Do Mormot fw have such a example? ORM implementation widely found in examples, but this - not.

Thank in advance.

Offline

#10 2024-03-12 16:50:03

tbo
Member
Registered: 2015-04-20
Posts: 335

Re: ORM and Dynamic models

Vit wrote:

This is not clear for me, but will my possible solution "map" on DB field to one vaiants property?

Do Mormot fw have such a example? ORM implementation widely found in examples, but this - not.

You can read this article in Delphi-Praxis forum. It can also be found in the examples. To query this field directly via SQL, it uses the SQLite syntax.

With best regards
Thomas

Offline

#11 2024-03-12 17:07:55

Vit
Member
Registered: 2024-03-12
Posts: 8

Re: ORM and Dynamic models

Thank you.
I did it already :-)

And still have those quastions.

But suppose I have this shortened class / table - for example any register, like customers:
ID integer
Name varchar
This example (from link above) can be easily implemented for that case.

But what if client1 (let assume this possibility already exists) has add field:
X integer
But client2 has added another / different Y boolen field.

I want to have "core" / pure structure and plus all what client added.

As I realized, 1) ORM can not be omitted entirely?
2) Not going so deep, but seems like all TDocvariant (entire JSON / object) - image with all properties in example mentioned - will be put in ONE separate field. Am I right?

Thanks a lot.

Offline

#12 2024-03-13 03:42:35

Chaa
Member
Registered: 2011-03-26
Posts: 245

Re: ORM and Dynamic models

Vit wrote:

2) Not going so deep, but seems like all TDocvariant (entire JSON / object) - image with all properties in example mentioned - will be put in ONE separate field. Am I right?

Yes. Then, in SQL queries, you can use database functions to work with JSON, like json_extract and etc.

But there is more complex way, if you need separate fields in database.

Create TOrmPropInfoCustom descendant that can store your fields in any way. And override InternalRegisterCustomProperties to add your props.

class procedure TOrmMyEntity.InternalRegisterCustomProperties(Props: TOrmProperties);
var
    i: Integer;
begin
    inherited InternalRegisterCustomProperties(Props);
    for i := 0 to Length(FMyProps) - 1 do
        Props.Fields.Add(TOrmPropInfoMyField.Create(..., {aProperty=}@TOrmMyEntity(nil).FMyValues[i]));
end;

Offline

#13 2024-03-14 16:45:07

Vit
Member
Registered: 2024-03-12
Posts: 8

Re: ORM and Dynamic models

Probably its quite a simple for you, but I did not get it working.
I have created decsendent this way:

type
  TDummyPropInfo = class(TOrmPropInfoCustom)
    //
  end;
  TOrmFile = class(TOrm)
  private
    FMyProps: TDummyPropInfo;
    FTitle: RawUtf8;
    FComment: RawUtf8;

But can't implement RegisterCustomProperties  - error "Instanse member FMyProps inaccessible here":

class procedure TOrmFile.InternalRegisterCustomProperties(Props: TOrmProperties);
begin
    inherited InternalRegisterCustomProperties(Props);
    for i := 0 to Length(FMyProps) - 1 do

Anyway I did other test (copy / paste), with way working (I see new dedicated field):

 
 Props.RegisterCustomPropertyFromTypeName(
    self,
    'TGUID',
    'GUID',
    @TOrmFile(nil).fGUID,
    [aIsUnique],
    38
  );

How I can see initialize empty TDocvariant (and query the structure)? The only way I now so far is:

var
  doc: TDocVariantData;
begin
  doc.InitFast(dvObject);
  doc.AddValue('Creator', pmcCreator);
  doc.AddValue('Location', pmcLocation);
  doc.AddValue('Latitude', pmLatitude);
  doc.AddValue('Longitude', pmLongitude);
  doc.AddValue('Date', DateToIso8601(pmDate, True));
  doc.AddValue('Time', TimeToIso8601(pmTime, True));
  Result := Variant(doc);

But I want somwthing like:

 
  TOrmFile = class(TOrm)
  private
    FMyDummy: TDocVariantData; <- and some existing structure already here

You can see, I'm pretty beginner, but it is quite interesting framework in general and this topic as well.

Thank you in advance.

Offline

#14 2024-03-14 17:35:22

tbo
Member
Registered: 2015-04-20
Posts: 335

Re: ORM and Dynamic models

Vit wrote:

You can see, I'm pretty beginner, but it is quite interesting framework in general and this topic as well.

Read this article to find out more about DocVariant. The help describes how the properties are mapped.

With best regards
Thomas

Offline

#15 2024-03-15 04:27:51

Chaa
Member
Registered: 2011-03-26
Posts: 245

Re: ORM and Dynamic models

There is two way:

1) One field in database. Use Variant property and DocVariant to store additional properties in one JSON field in database.
See:
https://www.delphipraxis.net/210843-mor … tellt.html

2) Multiple fields in database.
Describe you properties like that:

TPropInfo = packed record
    Name: RawUtf8;
    FieldType: TOrmFieldType;
    FieldWidth: Integer;
    // may be any additional property info, like Caption
end;
TPropInfoDynArray = array of TPropInfo;
TPropValueArray = array [0..MAX_SQLFIELDS - 1] of Variant;

Then ORM class, with properties descriptions for entity of that class and property values:

class TOrmMyEntity = class(TOrm)
        FMyValues: TPropValueArray;
    class var
        FMyProps: TPropInfoDynArray;
    class procedure Init;
end;

Then initialize properties descriptions at your application start:

class procedure TOrmMyEntity.Init;
begin
  // Load FMyProps array from client options
  // May be DynArrayLoadJson or something else
end;

Create TOrmPropInfoCustom descendant that can store your fields:

TOrmPropInfoMyField = class(TOrmPropInfoCustom)
...
end;
// Code to access property value:
// var
//     Value: PVariant;
// begin
//     Value := GetFieldAddr(Instance);
// end

And register your properties in InternalRegisterCustomProperties, as above.

Note, this is complicated solution, and you need to go deep in mormot.orm.base.pas and mormot.orm.core.pas source code.

Offline

#16 2024-03-15 12:50:50

Vit
Member
Registered: 2024-03-12
Posts: 8

Re: ORM and Dynamic models

Thank you so much for examples.

I see that solution one working aout of the box (no need to extra code), but second one is tricky and still have questions.
Here is my example:

const
  HttpPort = '11111';

type
  TPropInfo = packed record
      Name: RawUtf8;
      FieldType: TOrmFieldType;
      FieldWidth: Integer;
  end;

  TPropInfoDynArray = array of TPropInfo;

  TPropValueArray = array [0..MAX_SQLFIELDS - 1] of Variant;

  TOrmPropInfoMyField = class(TOrmPropInfoCustom)
  public
    //
  end;

  TOrmSample = class(TOrm)
  private
    FName: RawUTF8;
    FQuestion: RawUTF8;
    FTime: TModTime;
    FDummyData: Variant;

    class constructor Create;
    class destructor Destroy;
    constructor Create;
  protected
    class procedure Init;
    class procedure InternalRegisterCustomProperties(Props: TOrmProperties); override;
  published
    property Name: RawUTF8 read FName write FName;
    property Question: RawUTF8 read FQuestion write FQuestion;
    property Time: TModTime read FTime write FTime;
    property DummyData: Variant read FDummyData write FDummyData;
  public
    class var
        FMyProps: TPropInfoDynArray;
    FMyValues: TPropValueArray;
  end;

  function CreateSampleModel: TOrmModel;

implementation

function CreateSampleModel: TOrmModel;
begin
  result := TOrmModel.Create([TOrmSample]);
end;

{ TOrmSample }

class constructor TOrmSample.Create;
begin
  inherited;

  Init;
end;

constructor TOrmSample.Create;
begin
  inherited Create;
end;

class destructor TOrmSample.Destroy;
begin

  inherited;
end;

class procedure TOrmSample.Init;
begin
  SetLength(self.FMyProps, 1);
  self.FMyProps[0].Name := 'DummyColumn';
  self.FMyProps[0].FieldType := oftUtf8Text;
  self.FMyProps[0].FieldWidth := 255;
end;

class procedure TOrmSample.InternalRegisterCustomProperties(
  Props: TOrmProperties);
var
    i: Integer;
begin
    inherited InternalRegisterCustomProperties(Props);

    for i := 0 to Length(FMyProps) - 1 do
        Props.Fields.Add(
          TOrmPropInfoMyField.Create(
            {aName=}          FMyProps[i].Name,
            {aOrmFieldType=}  FMyProps[i].FieldType,
            {aAttributes=}    [],
            {aFiledWidth=}    FMyProps[i].FieldWidth,
            {aPropIndex=}     i,
            {aProperty=}      @TOrmSample(nil).FMyValues[i],
            {aData2Text=}     nil,
            {aTexttoData=}    nil
          )
        );
end;

DummyData as entire JSON working as expected, but DummyColumn - not.

I'm stuck on last step - getting value - GetFieldAddr(Instance);
I Have now this class, I have field description added, but it i snot enough

  TOrmPropInfoMyField = class(TOrmPropInfoCustom)
  public
    //
  end;

In client as long as I call Add, there is error "abstract error"

procedure TMainForm.ButtonAddClick(Sender: TObject);
var
  Rec: TOrmSample;
  Value: PVariant;

  function MyDummy: Variant;
  var
    doc: TDocVariantData;
  begin
    doc.InitFast(dvObject);
    doc.AddValue('Dummy', 'Here is dummy text');
    Result := Variant(doc);
  end;

begin
  Rec := TOrmSample.Create;
  try
    Rec.Name := StringToUTF8(NameEdit.Text);
    Rec.Question := StringToUTF8(QuestionMemo.Text);
    Rec.DummyData := MyDummy;

    Rec.FMyValues[0] := '123';

    //Rec.OrmProps.Fields

    if HttpClient.Orm.Add(Rec, True) = 0 then
      ShowMessage('Error adding the data') else begin
      NameEdit.Text := '';
      QuestionMemo.Text := '';
      NameEdit.SetFocus;
    end;
  finally
    Rec.Free;
  end;
end;

Have no idea wath is wrong. But (let say if second version is working), can be initial field (DummyData) be omitted entirelly?

THank you so much. Almost there, but can't fix it itself.

Offline

#17 2024-03-16 05:48:02

Chaa
Member
Registered: 2011-03-26
Posts: 245

Re: ORM and Dynamic models

You need to override abstract methods of TOrmPropInfo. I did not provide my solution because it contains some specific application logic.

For example see TOrmPropInfoRttiVariant.

Main methods is SetValue and GetValue.

For example:

procedure TOrmPropInfoVariantArray.SetValue(Instance: TObject; Value: PUtf8Char;
    ValueLen: PtrInt; wasString: boolean);
var
    V: PVariant;
    tmp: TSynTempBuffer;
begin
    V := GetFieldAddr(Instance);
    if ValueLen > 0 then
    begin
        tmp.Init(Value, ValueLen);
        try
            GetVariantFromJsonField(tmp.buf, wasString, V^, nil);
        finally
            tmp.Done;
        end;
    end
    else
        VarClear(V^);
end;

procedure TOrmPropInfoVariantArray.GetValueVar(Instance: TObject; ToSql:
    boolean; var result: RawUtf8; wasSqlString: PBoolean);
var
    wasString: Boolean;
    V: PVariant;
begin
    V:= GetFieldAddr(Instance);
    VariantToUTF8(V^, result, wasString);
    if wasSQLString <> nil then
        wasSQLString^ := not VarIsEmptyOrNull(V^);
end;

Also, you need override NormalizeValue (do nothing), GetBinary and SetBinary (see TOrmPropInfoRttiVariant).

Offline

#18 2024-03-16 14:56:17

Vit
Member
Registered: 2024-03-12
Posts: 8

Re: ORM and Dynamic models

Thank you so much for help
A bit closer
This code working at least when row with ORM added , but field is empty. For some reason no one method (GetValue, SetValue etc) is fired.

const
  HttpPort = '11111';

type
  TPropInfo = packed record
      Name: RawUtf8;
      FieldType: TOrmFieldType;
      FieldWidth: Integer;
  end;

  TPropInfoDynArray = array of TPropInfo;

  TPropValueArray = array [0..MAX_SQLFIELDS - 1] of Variant;

  TOrmPropInfoMyField = class(TOrmPropInfoCustom)
  public
    procedure SetValue(Instance: TObject; Value: PUtf8Char; ValueLen: PtrInt; wasString: boolean);
    procedure GetValueVar(Instance: TObject; ToSQL: boolean; var result: RawUTF8; wasSQLString: PBoolean); override;
    procedure SetBinary(Instance: TObject; var Read: TFastReader); override;
    procedure GetBinary(Instance: TObject; W: TFileBufferWriter); override;
    procedure NormalizeValue(var Value: RawUTF8); override;
  end;

  TOrmSample = class(TOrm)
  private
    FName: RawUTF8;
    FQuestion: RawUTF8;
    FTime: TModTime;
    FDummyData: Variant;

    class constructor Create;
    class destructor Destroy;
    constructor Create;
  protected
    class procedure Init;
    class procedure InternalRegisterCustomProperties(Props: TOrmProperties); override;
  published
    property Name: RawUTF8 read FName write FName;
    property Question: RawUTF8 read FQuestion write FQuestion;
    property Time: TModTime read FTime write FTime;
    property DummyData: Variant read FDummyData write FDummyData;
  public
    class var
      FMyProps: TPropInfoDynArray;
    FMyValues: TPropValueArray;
  end;

  function CreateSampleModel: TOrmModel;

implementation

function CreateSampleModel: TOrmModel;
begin
  Result := TOrmModel.Create([TOrmSample]);
end;

{ TOrmSample }

class constructor TOrmSample.Create;
begin
  inherited;

  Init;
end;

constructor TOrmSample.Create;
begin
  inherited Create;
end;

class destructor TOrmSample.Destroy;
begin

  inherited;
end;

class procedure TOrmSample.Init;
begin
  SetLength(self.FMyProps, 1);
  self.FMyProps[0].Name := 'DummyColumn';
  self.FMyProps[0].FieldType := oftUtf8Text;
  self.FMyProps[0].FieldWidth := 255;
end;

class procedure TOrmSample.InternalRegisterCustomProperties(
  Props: TOrmProperties);
var
    i: Integer;
begin
    inherited InternalRegisterCustomProperties(Props);

    for i := 0 to Length(FMyProps) - 1 do
        Props.Fields.Add(
          TOrmPropInfoMyField.Create(
            {aName=}          FMyProps[i].Name,
            {aOrmFieldType=}  FMyProps[i].FieldType,
            {aAttributes=}    [],
            {aFiledWidth=}    FMyProps[i].FieldWidth,
            {aPropIndex=}     i,
            {aProperty=}      @TOrmSample(nil).FMyValues[i],
            {aData2Text=}     nil,
            {aTexttoData=}    nil
          )
        );
end;

{ TOrmPropInfoMyField }

procedure TOrmPropInfoMyField.NormalizeValue(var Value: RawUTF8);
begin // do nothing
end;

procedure TOrmPropInfoMyField.GetBinary(Instance: TObject; W: TFileBufferWriter);
var JSON: RawUTF8;
begin
  //
end;

procedure TOrmPropInfoMyField.SetBinary(Instance: TObject; var Read: TFastReader);
begin
  //
end;

procedure TOrmPropInfoMyField.GetValueVar(Instance: TObject; ToSQL: boolean; var result: RawUTF8; wasSQLString: PBoolean);
var
    wasString: Boolean;
    V: PVariant;
begin
    V:= GetFieldAddr(Instance);
    VariantToUTF8(V^, result, wasString);
    if wasSQLString <> nil then
        wasSQLString^ := not VarIsEmptyOrNull(V^);
end;

procedure TOrmPropInfoMyField.SetValue(Instance: TObject; Value: PUtf8Char; ValueLen: PtrInt; wasString: boolean);
var
    V: PVariant;
    tmp: TSynTempBuffer;
begin
    V := GetFieldAddr(Instance);
    if ValueLen > 0 then
    begin
        tmp.Init(Value, ValueLen);
        try
            GetVariantFromJsonField(tmp.buf, wasString, V^, nil);
        finally
            tmp.Done;
        end;
    end
    else
        VarClear(V^);
end;

end.

Here is "adding row" code:

var
  Rec: TOrmSample;
  Value: PVariant;

  function MyDummy: Variant;
  var
    doc: TDocVariantData;
  begin
    doc.InitFast(dvObject);
    doc.AddValue('Dummy', 'Here is dummy text');
    Result := Variant(doc);
  end;

begin
  Rec := TOrmSample.Create;
  try
    Rec.Name := StringToUTF8(NameEdit.Text);
    Rec.Question := StringToUTF8(QuestionMemo.Text);
    Rec.DummyData := MyDummy;

    Rec.FMyValues[0] := '123';

    //Rec.OrmProps.Fields

    if HttpClient.Orm.Add(Rec, True) = 0 then
      ShowMessage('Error adding the data') else begin
      NameEdit.Text := '';
      QuestionMemo.Text := '';
      NameEdit.SetFocus;
    end;
  finally
    Rec.Free;
  end;
end;

But again I see this "abstract error" when try to read already added data:

var
  Rec: TOrmSample;
begin
  Rec := TOrmSample.Create(HttpClient.Orm,'Name=?',[StringToUTF8(NameEdit.Text)]);
  try
    if Rec.ID=0 then
      QuestionMemo.Text := 'Not found' else
      QuestionMemo.Text := UTF8ToString(Rec.Question);
  finally
    Rec.Free;
  end;
end;

Probably this feature (dedicated field) requires more  / deep knowledge.
I know, result is pretty close, but I can not it implement.

Thank you for help so much.

P.S. I have those "guid" example, it works, but can not do something similar.

Offline

#19 2024-03-17 16:56:16

Vit
Member
Registered: 2024-03-12
Posts: 8

Re: ORM and Dynamic models

Hopefully with such a excellent assistent I've finally got it!
Thank you so much.
Only the question, how to work with data?
I have now harcoded dummy string, but do I have to fill this TDocVariant and extract data from there or even it does not needed at all?

procedure TMainForm.ButtonAddClick(Sender: TObject);
var
  Rec: TOrmSample;
  Value: PVariant;

  function MyDummy: Variant;
  var
    doc: TDocVariantData;
  begin
    doc.InitFast(dvObject);
    doc.AddValue('Dummy', 'Here is dummy text');
    Result := Variant(doc);
  end;

begin
  Rec := TOrmSample.Create;
  try
    Rec.Name := StringToUTF8(NameEdit.Text);
    Rec.Question := StringToUTF8(QuestionMemo.Text);
    Rec.DummyData := MyDummy;                               <- this is all-in-one JSON field
    Rec.FMyValues[0] := 'Here is dummy text';               <- my dedicated field

Thank you so much again! thats amazing result for me.

Offline

#20 2024-04-01 12:46:54

Vit
Member
Registered: 2024-03-12
Posts: 8

Re: ORM and Dynamic models

Hello everyone.
I decided to post my next question here, which is only indirectly related to this discussion.
1) separate column for each field works as expected (made according this topic)
2) in order to somehow show it in theG UI, I decided to transfer the entire returned set (array) to the clientdataset. It give me yje same power as dataset.FieldByName etc... Wouldn't this be a bottleneck?

uses
  SysUtils,
  SynDB,
  SynCommons,
  DB;

procedure VariantArrayToDataSet(const Values: TVariantDynArray; DataSet: TDataSet);
var
  i, j: Integer;
begin
  // Clear the dataset before adding new records
  DataSet.Close;
  DataSet.Fields.Clear;
  for i := Low(Values) to High(Values) do
    DataSet.Fields.Add(TStringField.Create(DataSet)).FieldName := 'Field' + IntToStr(i);

  DataSet.Open;
  for j := 0 to High(Values[0]) do // Assuming all rows have the same length
  begin
    DataSet.Append;
    for i := Low(Values) to High(Values) do
      DataSet.Fields[i].Value := Values[i][j];
    DataSet.Post;
  end;
end;

procedure TestVariantArrayToDataSet;
var
  VariantArray: TVariantDynArray;
  DataSet: TClientDataSet;
begin
  // Sample array of variants
  SetLength(VariantArray, 3);
  VariantArray[0] := VarArrayOf(['Value 1', 'Value 2', 'Value 3']);
  VariantArray[1] := VarArrayOf([123, 456, 789]);
  VariantArray[2] := VarArrayOf([True, False, True]);

  // Create a TClientDataSet
  DataSet := TClientDataSet.Create(nil);
  try
    // Convert array of variants to TDataSet
    VariantArrayToDataSet(VariantArray, DataSet);

    // Output the dataset
    DataSet.First;
    while not DataSet.Eof do
    begin
      Writeln(DataSet.Fields[0].AsString, ', ', DataSet.Fields[1].AsInteger, ', ', DataSet.Fields[2].AsBoolean);
      DataSet.Next;
    end;
  finally
    DataSet.Free;
  end;
end;

begin
  TestVariantArrayToDataSet;
end.

Thanks a lot!

Offline

#21 2024-04-01 15:53:02

ab
Administrator
From: France
Registered: 2010-06-21
Posts: 14,207
Website

Re: ORM and Dynamic models

You have already a TClientDataSet-derivated class in mORMot 1.

Offline

#22 2024-04-01 17:01:52

Vit
Member
Registered: 2024-03-12
Posts: 8

Re: ORM and Dynamic models

do you mean that one in unit mORMotMidasVCL?
What about Mormot2 then?,Thank you

Offline

#23 2024-04-01 20:34:36

ab
Administrator
From: France
Registered: 2010-06-21
Posts: 14,207
Website

Re: ORM and Dynamic models

You are in the mORMot 1 topic here, so I suspected it was for mORMot 1.

For mORMot 2, you have https://github.com/synopse/mORMot2/blob … ui.cds.pas
You could have searched for TClientDataSet in the units descriptions (top of each unit or the README per-folder files) to find it in 15 seconds.

Offline

Board footer

Powered by FluxBB