Ignoring columns for DB-First

hnb · 2016-04-04 09:44:48

Hi!

I need to implement addition (new parameter for TSQLRest.Add method) to ignore selected columns during adding/inserting new record (I have DB-First approach for existing project where mORMot is introduced). I need to let DB to fill few of my columns (and that can't be changed).

Is any sense to preparing patch for this (because it will be ignored)? I'd also like to fix "Insert new record and Bad Request (400)" ( http://synopse.info/forum/viewtopic.php?id=3245 ).

ab · 2016-04-04 11:39:04

Why not just don't put the fields in the TSQLRecord definition?

hnb · 2016-04-04 12:00:37

that was fine for beginning of including mORMot in my big project (~600 tables and 6 MLOC) for some tables but the fields are used in many tables for update, select etc. So I need to put these fields in the TSQLRecord definition. The insert is the only case in DB-First approach when I need to omit some fields. I'd like to omit declaring two models for each table...

The new parameter for "Add" seems most logically. I don't see any alternative.

ab · 2016-04-04 13:12:38

What you can try is change TYourTable.RecordProps.SimpleFieldsBits[soInsert] to exclude the bits of the non needed fields.

Please also try http://synopse.info/fossil/info/6fffa2ab16
which renamed TSQLRestStorageExternal.EngineAddIgnoreID boolean as a "EngineAddForcedID: TID" property.
Put here a "fake" ID which would be returned when TSQLRest.Add() is called, as a non 0 "fake" ID - it should avoid the 404 error.

hnb · 2016-04-05 08:33:42

Thanks ab, nice smart solution, but it is missing additional check for decode fields in TSQLRestStorageExternal.ExecuteFromJSON:

should be

    // decode fields
    if fEngineAddForcedID = 0 then
      Decoder.Decode(SentData,nil,pNonQuoted,InsertedID,true) else
      Decoder.Decode(SentData,nil,pNonQuoted,0,true);

instead of

    // decode fields
    Decoder.Decode(SentData,nil,pNonQuoted,InsertedID,true);

without modification is raised exception: TSQLRestStorageExternal.JSONDecodedPrepareToSQL(TSQLxxx): No column for "ID" field in table public.xxx

btw. I am working on ignoring columns

ab · 2016-04-05 12:18:17

Please try http://synopse.info/fossil/info/3fd36c702d

hnb · 2016-04-05 13:21:29

thanks, works as expected.

Patch is ready. To be consistent with standard SQL and with other mORMot parts, I must to say that "excluding" is bad idea. Instead of excluding is used standard approach with CustomFields. All old code will work as before (with small exception for one virtual Add, but IMO this is the must to keep clear code base):

https://drive.google.com/file/d/0B4PZhd … sp=sharing

example usage:

function NewMaleBaby(Client: TSQLRest; const Name,Address: RawUTF8): TID;
var Baby: TSQLBaby;   // store a record
begin
  Baby := TSQLBaby.Create;
  try
    Baby.Name := Name;
    Baby.Address := Address;
    Baby.BirthDate := Date;
    Baby.Sex := sMale;
    result := Client.Add(Baby,true,'Name,Address,Sex'); // (!!!) BirthDate is filled by DB 
    // also is possible to call:
    // result := Client.Add(Baby,true,'*');
    // which is equal to:
    // result := Client.Add(Baby,true);
  finally
    Baby.Free;
  end;
end;

hnb · 2016-04-05 17:24:50

I need to know whether the patch will be included. This is the key feature for my team, so eventually I need keep this somehow outside the core of mORMot library.

ab · 2016-04-05 20:35:07

Please check http://synopse.info/fossil/info/ef66456cb4

hnb · 2016-04-06 07:24:43

Horray! mORMot is now very tasty in including to existing project!

As far as I understand "SendData" parameter is useless for our case?

ab · 2016-04-06 08:03:53

It was my guess...
Since we provide field names (or bits), we would provide associated data to be sent.
So SendData is always true for the field-oriented overloaded methods.

mORMot Open Source

#1 2016-04-04 09:44:48

Ignoring columns for DB-First

#2 2016-04-04 11:39:04

Re: Ignoring columns for DB-First

#3 2016-04-04 12:00:37

Re: Ignoring columns for DB-First

#4 2016-04-04 13:12:38

Re: Ignoring columns for DB-First

#5 2016-04-05 08:33:42

Re: Ignoring columns for DB-First

#6 2016-04-05 12:18:17

Re: Ignoring columns for DB-First

#7 2016-04-05 13:21:29

Re: Ignoring columns for DB-First

#8 2016-04-05 17:24:50

Re: Ignoring columns for DB-First

#9 2016-04-05 20:35:07

Re: Ignoring columns for DB-First

#10 2016-04-06 07:24:43

Re: Ignoring columns for DB-First

#11 2016-04-06 08:03:53

Re: Ignoring columns for DB-First

Board footer