Posts by jlc

jlc · mORMot 1

Found out that I don't need any extra reference because TSQLRecord also resets the ID to 0 and as I only have one table it's enough. :-)

jlc · mORMot 1

@Vitaly
Thanks for your help.

I'm still unsure if the code

  TSQLClientsRecord = class(TSQLRecord)
  private
    FName: TSQLNamesRecord;
    FNameReference: TRecordReferenceToBeDeleted;
    FAddress: TSQLAddressRecord;
    FAddressReference: TRecordReferenceToBeDeleted;
  published
    property ClientName: TSQLNamesRecord read FName write FName;
    property ClientAddress: TSQLAddressRecord read FAddress write FAddress;
    property ClientNameReference: TRecordReferenceToBeDeleted read FNameReference write FNameReference;
    property ClientAddressReference: TRecordReferenceToBeDeleted read FAddressReference write FAddressReference;
  end;

is as it should be done with mORMot framework.
Both, the normal property and the reference, contain the same ID - it only added additional foreign keys for the references. So couldn't I remove the extra reference in the ClientsRecord?

The help also mentions TSQLRecordClassName[ToBeDeleted]ID but I got no idea how I can use TSQLNamesRecordToBeDeletedID / TSQLAddressRecordToBeDeletedID because both are not defined. The help says it can delete in one table, which is what I need as shown above.
Does it also need an extra reference?

jlc · mORMot 1

I've read the documentation several times already and I also figured out that I need:

  TSQLClientsRecord = class(TSQLRecord)
  private
    FName: TSQLNamesRecord;
    FNameReference: TRecordReferenceToBeDeleted;
    FAddress: TSQLAddressRecord;
    FAddressReference: TRecordReferenceToBeDeleted;
  published
    property ClientName: TSQLNamesRecord read FName write FName;
    property ClientAddress: TSQLAddressRecord read FAddress write FAddress;
    property ClientNameReference: TRecordReferenceToBeDeleted read FNameReference write FNameReference;
    property ClientAddressReference: TRecordReferenceToBeDeleted read FAddressReference write FAddressReference;
  end;

which ends in laborious code as I now also need

ClientNameReference := ClientName.ID;
ClientAddressReference := ClientAddress.ID;

when inserting something.
But it does not delete the row in TSQLClientsRecord, instead it sets the reference value to 0 for the Name where it deleted to row from the Names table. Latter is what I want but it should also delete TSQLAddressRecord row and TSQLCountryRecord row if it's not used for any other TSQLAddressRecord entry.

jlc · mORMot 1

Vitaly wrote:

Can you explain, please, why do you need to check and clean all dictionaries after every single Client removing? Some kind of storage space economy?

Not really space, just to have a 'clean' dataset without any not used (bloat) values.
Depending on what to save it might be also a thing of privacy reasons if someone requests to delete all his data...but my app runs local, therefore no shared server/data.

Vitaly wrote:

Maybe, in this case, it is more reasonable to make a separate server-side procedure for cleaning dictionaries, which will be scheduled once a week/month/year, for example.

What do you mean by this? And how would it be done? By writing own SQL code? But as I use TSQLRestClientDB I don't have any running server, its just a local database for the program which should not contain any garbage/old/unused data.

Vitaly wrote:

I think you have mistyped here a bit. Published Country property should be also TSQLCountryRecord type, I guess

Yes, right. Must be

    FCountry: TSQLCountryRecord;
  published
    property Country: TSQLCountryRecord read FCountry write FCountry;
  end;

jlc · mORMot 1

No one has an idea?

jlc · mORMot 1

Hi,
I have following example classes:

  TSQLNamesRecord = class(TSQLRecord)
  private
    FName: RawUTF8;
  published
    property Name: RawUTF8 read FName write FName stored AS_UNIQUE;
  end;

  TSQLCountryRecord = class(TSQLRecordNoCase)
  private
    FCoutryName: RawUTF8;
  published
    property CountryName: RawUTF8 read FCoutryName write FCoutryName stored AS_UNIQUE;
  end;

  TSQLAddressRecord = class(TSQLRecordNoCase)
  private
    FAddress: RawUTF8;
    FCountry: TSQLCountryRecord;
  published
    property Address: RawUTF8 read FAddress write FAddress;
    property Country: RawUTF8 read FCountry write FCountry;
  end;

  TSQLClientsRecord = class(TSQLRecord)
  private
    FName: TSQLNamesRecord;
    FAddress: TSQLAddressRecord;
  published
    property ClientName: TSQLNamesRecord read FName write FName;
    property ClientAddress: TSQLAddressRecord read FAddress write FAddress;
  end;

Now I want to delete the TSQLClientsRecord for a given Name and it should also delete the relevant data including TSQLAddressRecord and TSQLCountryRecord if no other client has the same address.
Is there a way to realize that with the framework by ORM? I think that's what TRecordReferenceToBeDeleted does but I don't find any example. In which of the above classes do I need to put it? And do I need to always set/update it when a new client uses an existing unique value?
Or is the only way be writing an own SQL query for it and put it into ExecuteFmt?

Thanks in advance!

jlc · mORMot 1

It's strange, even github doesn't find anything: https://github.com/synopse/mORMot/searc … pareJoined

But I don't think you can define it, all code examples in docu shows that it will get *

One example I used:

fRecord := TSQLMyRecord.CreateAndFillPrepareJoined(DBCon, '(InfoList.Name = ? OR Task.Name = ?) AND timestamp > date(?, ?)', [], [InfoName, TaskName, 'now', Period]);
    try
      while fRecord.FillOne do
      begin
        ...
      end;
    finally
      fRecord.Free;
    end;

EDIT: the forum search doesn't find this thread maybe related to the word 'join'?

jlc · mORMot 1

Vitaly wrote:

jlc wrote:
NameRec := TSQLNamesRecord.CreateAndFillPrepare(DBCon, 'Name = ?', [NewName], 'Name'); // <-- is it good to use 'Name' here or is
it not needed as the record has only one entry anyway?
I don't think, that you need to point custom fields here:
- default aCustomFieldsCSV='' will retrieve all simple table fields

I've read that but as I may extend my record and just need to know if the entry already exists, it's enough to get 'Name' field only. That's why I think leaving it with 'Name' is fine.

Vitaly wrote:

jlc wrote:
if NameRec.Name = '' then // <- which one is the correct way to use it?
//if not NameRec.FillOne then // <- this is already done by CreateAndFillPrepare or?
If you want to fill your TSQLRecord object published fields, you have to call FillOne first after CreateAndFillPrepare. And FillOne will tell you, if there is any record retrieved resulting boolean value.
So, I would quickly correct a part of your code like this (if I understood your desires right):
  NameRec := TSQLNamesRecord.CreateAndFillPrepare(DBCon, 'Name = ?', [NewName]);
   if not NameRec.FillOne then
    begin
      NameRec.Name := StringToUTF8(NewName);
      DBcon.Add(NameRec, True);
    end;

Yes, your absolutely right. I noticed it after having the TSQLModel alive. Ended in the code you proposed :-)

Is there any other way than always doing a TSQL*Record.CreateAndFillPrepare() with maybe 'NewName' item and FillOne to check if it already exist?
I have another class now which has a reference via AsTSQLRecord to the Name record. When extending it with another few record classes, I'll end up in many calls to TSQL*Record.CreateAndFillPrepare() and FillOne to get the ID if existing or add the item to get in the end the proper reference via AsTSQLRecord. Is there any smarter way?

jlc · mORMot 1

Vitaly wrote:

You can just add
 YourTSQLModel.Owner := YourTSQLRest; 
So YourTSQLModel will be freed automatically with YourTSQLRest destroying. See: https://synopse.info/files/html/api-1.1 … ODEL_OWNER

Missed that but seems to be not that clean...

Vitaly wrote:

Or you can do the same through the constructor: https://synopse.info/files/html/api-1.1 … THOWNMODEL

This constructor is not available for TSQLRestClientDB

jlc · mORMot 1

Looks like the issue occurred because I haven't had my TSQLModel as global variable; instead I created it before TSQLRestClientDB and freed it after calling TSQLRestClientDB.Create.
Haven't read in documentation that it must exist for the whole lifetime of the application, maybe that could be improved? Or it could do a copy of the object into its own TSQLRestClientDB object?

jlc · mORMot 1

Hello,
I have following code

  TSQLNamesRecord = class(TSQLRecord)
  private
    FName: RawUTF8;
  published
    property Name: RawUTF8 read FName write FName stored AS_UNIQUE;
  end;

At the beginning, the NewName does not exist and it gives '' as Name property

  // DBcon is TSQLRestClientDB
  NameRec := TSQLNamesRecord.CreateAndFillPrepare(DBCon, 'Name = ?', [NewName], 'Name');  // <-- is it good to use 'Name' here or is
it not needed as the record has only one entry anyway?

then I try to add it with

    if NameRec.Name = '' then // <- which one is the correct way to use it?
    //if not NameRec.FillOne then // <- this is already done by CreateAndFillPrepare or?
    begin
      NameRec.Name := StringToUTF8(NewName);
      DBcon.Add(NameRec, True);
    end;

but it always ends in an access violation in TSQLModel.GetTableIndex function:

  if c^=aTable then

Thanks in advance!

mORMot Open Source

#1 Re: mORMot 1 » Delete all references by given data of main class » 2019-08-31 11:03:12

#2 Re: mORMot 1 » Delete all references by given data of main class » 2019-08-28 16:35:25

#3 Re: mORMot 1 » Delete all references by given data of main class » 2019-08-21 21:16:54

#4 Re: mORMot 1 » Delete all references by given data of main class » 2019-08-21 17:46:04

#5 Re: mORMot 1 » Delete all references by given data of main class » 2019-08-21 16:13:21

#6 mORMot 1 » Delete all references by given data of main class » 2019-08-19 19:51:07

#7 Re: mORMot 1 » CreateAndFillPrepareJoined custom fields » 2019-08-16 21:50:03

#8 Re: mORMot 1 » AV in GetTableIndex when trying to save unique TSQLRecord » 2019-08-16 15:31:20

#9 Re: mORMot 1 » AV in GetTableIndex when trying to save unique TSQLRecord » 2019-08-16 11:18:23

#10 Re: mORMot 1 » AV in GetTableIndex when trying to save unique TSQLRecord » 2019-08-16 08:30:53

#11 mORMot 1 » AV in GetTableIndex when trying to save unique TSQLRecord » 2019-08-15 22:14:16

Board footer