Delete all references by given data of main class

jlc · 2019-08-19 19:51:07

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 · 2019-08-21 16:13:21

No one has an idea?

Vitaly · 2019-08-21 17:05:36

I'd like to help, but I haven't faced such tasks earlier. 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?
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.
Tell me, please, if I understood you wrong.

btw,

jlc wrote:

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

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

jlc · 2019-08-21 17:46:04

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;

Vitaly · 2019-08-21 20:24:00

jlc wrote:

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.

I meant making some procedure to run in a separate thread, which will run periodically and service DB.
Btw, TSQLRestClientDB creates TSQLRestServerDB, so technically you have running server But it doesn't matter.

From my point of view, dictionaries shouldn't contain any personal information... And everything really personal, concerning a client (including address, but you will need to change your model slightly), can be deleted cascaded by reference to Client table, using T{ClassName}ToBeDeletedID or TRecordReferenceToBeDeleted. See 5.1.8 - 5.1.10 of docs: https://synopse.info/files/html/Synopse … l#TITLE_71 . It seems rather clear to me, although I do not use these options - customers usually don't like the ability to purge any information irretrievably.

Just try to use it following docs, and if you'll have troubles, tell me - I'll try to use it by myself and provide you the code. Or maybe somebody will provide you a sample

jlc · 2019-08-21 21:16:54

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.

Vitaly · 2019-08-21 21:48:26

jlc wrote:

it should also delete TSQLAddressRecord row and TSQLCountryRecord row if it's not used for any other TSQLAddressRecord entry.

It is not a cascade deletion (some kind of opposite deletion), so, unfortunately, you will have to make it by your hands, I guess...

jlc · 2019-08-28 16:35:25

@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?

Vitaly · 2019-08-28 19:03:11

As far as I understand, you could use smth like this (following your naming):

  TSQLNamesRecord = class(TSQLRecord)
  ...
  end;
  TSQLNamesRecordToBeDeletedID = type TID;

  TSQLAddressRecord= class(TSQLRecord)
  ...
  end;
  TSQLAddressRecordToBeDeletedID = type TID;  

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

So, if any Name (or Address) record is deleted, all Clients records, which use this particular Name (or Address) ID, also will be removed.

But again, I afraid, that it is not what were you looking for in previous messages. Hope, I'm wrong

jlc · 2019-08-31 11:03:12

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. :-)

macfly · 2019-09-03 13:28:52

I Agree with @Vitaly,

The TSQLXYZRecordToBeDeletedID does the opposite of what you are looking for.
Deleting an Address deletes clients that use it.

mORMot Open Source

#1 2019-08-19 19:51:07

Delete all references by given data of main class

#2 2019-08-21 16:13:21

Re: Delete all references by given data of main class

#3 2019-08-21 17:05:36

Re: Delete all references by given data of main class

#4 2019-08-21 17:46:04

Re: Delete all references by given data of main class

#5 2019-08-21 20:24:00

Re: Delete all references by given data of main class

#6 2019-08-21 21:16:54

Re: Delete all references by given data of main class

#7 2019-08-21 21:48:26

Re: Delete all references by given data of main class

#8 2019-08-28 16:35:25

Re: Delete all references by given data of main class

#9 2019-08-28 19:03:11

Re: Delete all references by given data of main class

#10 2019-08-31 11:03:12

Re: Delete all references by given data of main class

#11 2019-09-03 13:28:52

Re: Delete all references by given data of main class

Board footer