Support for a TGUID field

h.hasenack · 2012-10-17 15:02:15

Hi Ab

I want to add a TGUID type field to my TSQLModel class, but unfortunately it is mapped to sftUnknown, and not even a binary or string representyation is created. When debugging TSQLRecordClassToExternalFields it reveals the TGUID property getting mapped to sftUnknown, which causes no type to be generated for the field.

Would you be so kind to
1) Add TGUID support for the native field types
2) Generate some identifier for the unknown type eg "TypeUnknown" or maybe raise an exception stating which field(=property) causes the error.

Thx - Hans

ab · 2012-10-17 15:55:39

1) I'll add TGUID support, if I can - I'm not sure there is the needed RTTI.

2) This was by design.
I've modified the unit that that now TSQLRestServerStaticExternal won't create any columns for external tables with unsupported published property types (sftUnknown or sftMany).
See http://synopse.info/fossil/info/89ab0e40ef

ab · 2012-10-17 16:10:22

About 1) this is what I was afraid of...
I've added a published property GUID: TGUID read fGUID write fGUID but the property is not added to the RTTI information.
At least with Delphi 7. I'll try later with newer Delphi versions.

h.hasenack · 2012-10-18 06:43:26

I guess using RTTI you might check on the type name, and simply use 'TGUID' as identifier. Or maybe compare pointers for the PTypeInfo of TGUID?

I have been thinking about it a little, and for tackling similar future issues maybe you could "Open up" the type-to-field mapping using eg virtual functions or by providing some means to "Register" custom type mappings:

RegisterTypeToFieldType(aTypeInfo:PTypeinfo;aFieldType:TFieldType);

This of course also implies that there needs to be some data handling added. So why not also add pointers to data handling routines:

  TFieldToPropProc:procedure(aField:TField;aInstance:TSQLObject;aProperty:PPropInfo) of Object;
  TPropToFieldProc:procedure(aField:TField;aInstance:TSQLObject;aProperty:PPropInfo) of Object;

procedure RegisterTypeToFieldTypeMapping(aType:TTypeinfo;aFieldType:TFieldType;aReader:TFieldToPropProc;aWriter:TPropToFieldProc);

BTW it's just a suggestion, I am quite sure you can think of better prototypes then the ones i scribbled here

ab · 2012-10-18 07:05:44

The RTTI returns NO property: if you add a TGUID property, the PropCount does not change.
It is just like if the property was not defined!
I suspect this is because TGUID is a fixed-size array of bytes, so has no reference counted variable within, so do not have RTTI in Delphi.
So in this case, custom registration won't make the difference.

The only possibility may be to define a RawUTF8 and use the string representation of the TGUID.

But it is sure that the RTTI part of mORMot needs some refactoring, e.g. to add new custom types.
I was thinking about a class hierarchy, instead of such events, which are more difficult to customize for all cases (e.g. how do you store information if needed, e.g.).

h.hasenack · 2012-10-18 07:56:37

How about a 'DefineProperties' kind of solution like the one that is used in TComponent streaming? This kind of solution also provides a means to add your own custom datatypes with their custom readers and writers, and testing for "empty/Default" values.

ab · 2012-10-18 08:02:27

I've added the item in the RoadMap.

h.hasenack · 2012-10-18 12:25:37

Ab, I have found a clean way for checking if a property is of type GUID. I have tested this with XE3, but I am pretty sure this also works in the other versions... It's based on comparing the TypeData pointer rather than the typekind.

type
  TMyCustomObject=class(TPersistent)
  private
    FMyGUID: TGUID;
  published
    property MyGUID:TGUID read FMyGUID write FMyGUID;
  end;

  TSomeCustomRecord=record
    MyInteger:integer;
  end;

procedure TForm1.Button1Click(Sender: TObject);
VAR Obj:TMyCustomObject;
    ObjTypeInfo,
    GUIDTypeInfo,
    CustomRecordTypeInfo:PTypeInfo;
    PropList:PPropList;
    PropCnt:integer;
begin
  GUIDTypeInfo:=TypeInfo(TGUID);
  CustomRecordTypeInfo:=TypeInfo(TSomeCustomRecord);
  Obj:=TMyCustomObject.Create;
  try
    ObjTypeInfo:=Obj.ClassInfo;
    PropCnt:=GetPropList(ObjTypeInfo,PropList);
    if PropList[0].PropType^.TypeData=GUIDTypeInfo^.TypeData then
      MessageDlg('It''s a match for TGUID!',mtInformation,[mbOK],0)
    else MessageDlg('It''s not a match TGUID!',mtError,[mbOK],0); // ERROR if here

    if PropList[0].PropType^.TypeData=CustomRecordTypeInfo^.TypeData then
      MessageDlg('It''s a match for TSomeCustomRecord!',mtError,[mbOK],0) // ERROR if here
    else MessageDlg('It''s not a match TSomeCustomRecord!',mtInformation,[mbOK],0);

  finally
    Obj.Free;
  end;
  // these assignments to keep the optimizer from dropping the variables prematurely
  GUIDTypeInfo:=nil;
  PropCnt:=0;
  PropList:=nil;
  ObjTypeInfo:=nil;
end;

ab · 2012-10-18 13:30:18

As I stated above, in Delphi 7 there is no entry in the PPropList for such properties.

It may work with Delphi 2010+, but not with older versions of Delphi.

h.hasenack · 2012-10-18 13:38:21

Pity :s , but I am still not convinced. I mean, D7 also needs to perform type checking when loading dfm files etcetera isn't it? This seems quite hard to me if it cannot access the 'actual' type data from within the TPropInfo structure.

Looking at this (from 2002!) http://www.nldelphi.com/forum/showthread.php?t=2900

It seemed to me you could get to the TypeInfo of a property way back then.

I cannot imagine the TypeInfo being present in D7... Maybe it is missing in lazarus? - One could in that case alwasys fall back to TypeInfo.Name comparison.

Anyway, I adjusted the code to avoid the TypeData function call present in XE3, and it still works like a charm.

type
  TMyCustomObject=class(TPersistent)
  private
    FMyGUID: TGUID;
  published
    property MyGUID:TGUID read FMyGUID write FMyGUID;
  end;

  TSomeCustomRecord=record
    MyInteger:integer;
  end;

procedure TForm1.Button1Click(Sender: TObject);
VAR Obj:TMyCustomObject;
    ObjTypeInfo,
    GUIDTypeInfo,
    CustomRecordTypeInfo:PTypeInfo;
    PropList:PPropList;
    PropCnt:integer;
    PTypeData:pointer;
begin
  GUIDTypeInfo:=TypeInfo(TGUID);
  CustomRecordTypeInfo:=TypeInfo(TSomeCustomRecord);
  Obj:=TMyCustomObject.Create;
  try
    ObjTypeInfo:=Obj.ClassInfo;
    PropCnt:=GetPropList(ObjTypeInfo,PropList);

    if PropList[0].PropType^=GUIDTypeInfo then // CHANGED FOR D7 COmpatability
      MessageDlg('It''s a match for TGUID!',mtInformation,[mbOK],0)
    else MessageDlg('It''s not a match TGUID!',mtError,[mbOK],0); // ERROR if here

    if PropList[0].PropType^=CustomRecordTypeInfo then // CHANGED FOR D7 COmpatability
      MessageDlg('It''s a match for TSomeCustomRecord!',mtError,[mbOK],0) // ERROR if here
    else MessageDlg('It''s not a match TSomeCustomRecord!',mtInformation,[mbOK],0);

  finally
    Obj.Free;
  end;
  // these assignments to keep the optimizer from dropping the variables prematurely
  GUIDTypeInfo:=nil;
  PropCnt:=0;
  PropList:=nil;
  ObjTypeInfo:=nil;
end;

Last edited by h.hasenack (2012-10-18 14:15:09)

ab · 2012-10-18 19:35:11

No, older versions of Delphi do not store RTTI for records without internal reference-counted variables.
In fact, record RTTI was only generated for those kind of records, in order to initialize and finalize them.

Plain bunch of bytes do not have RTTI in older version of Delphi.
And such properties are not listed.

I'm currently making a huge code refactoring of the property handling, which will allow mapping of any kind of property.

h.hasenack · 2012-10-19 07:01:24

I see. Thx for bearing wit me :>

ab · 2012-10-26 10:10:59

I've finished some deep code refactoring, introducing TSQLPropInfo* classes in order to decouple the ORM definitions from the RTTI.
See http://synopse.info/fossil/info/79546deff4

Thanks to the integrated regression tests, code modifications are huge, but should be safe!
Any feedback is welcome.

It will allow definition of any class members, even if there is no RTTI generated or via custom properties attributes or a fluent interface.

First direct use could be to add a TGUID kind of property, even for older Delphi versions.

h.hasenack · 2012-10-26 13:38:16

I didnt get the time to look at it yet, but my hands are itching to get started with it

Thanks

itSDS · 2014-05-14 12:26:02

There is a Problem in XE6 with registering the TGUID as described in the Handbook 1.18.

It throws Exception unhandled type for ... in TSQLPropInfoRTTI.CreateFrom

To test add
TSQLRecordCustomProps in SynSelfTests.pas ~ Line 8571 TSQLModel.Create(...., TSQLRecordCustomProps);

Last edited by itSDS (2014-05-14 12:41:41)

ab · 2014-05-14 14:08:19

Sounds like if XE6 finally implemented the tkRecord RTTI !!!
I do not have XE6 at hand here, but I'll try to fix it.

BTW, custom record types are not fully tested yet (since I do not use them yet on production).
Any feedback is welcome!

itSDS · 2014-05-14 15:24:45

Yes you are right. In TSQLPropInfoRTTI.CreateFrom
the aType.Kind = tkRecord and aType.Name = 'TGUID'
aSQLFieldType = sftUnknown

ab · 2014-05-15 15:44:20

I'm currently trying Delphi XE6.
As a result, I've added support of enhanced RTTI for JSON serialization of records and dynamic arrays.

After some hours fighting against the low-level RTTI info, sounds pretty much stable and efficient now.
See http://synopse.info/fossil/info/976a824618
I've also updated the documentation (new SAD 1.18 pdf).

itSDS · 2014-05-16 07:42:14

I just tried the new Version - but get Exception:

Unhandled sftUnknown/tkRecord for property xyzID

the class function InternalRegisterCustomProperties was not called before the Error !

Also i get memory fault after in TSQLModel.Destroy i just added 1 line of code

destructor TSQLModel.Destroy;
var
  i,j: integer;
begin
  for i := 0 to fTablesMax do begin
// >>> ADDED
    if assigned(TableProps[i]) then
// <<< ADDED
      with TableProps[i].Props do begin
        EnterCriticalSection(fLock); // may be called from several threads at once

Last edited by itSDS (2014-05-16 07:45:01)

itSDS · 2014-05-21 11:36:45

I tested TGUID - Custom Property again today. The Exception still exists.
I think there must something be changed in TSQLRecordProperties.Create
The function AddParentsFirst throws an Exception cause of the GUID - Field before the Function InternalRegisterCustomProperties is called.

In my opinion custom properties should be made available and then you should create the field - List.

Hopefully you understand what i mean

ab · 2014-05-21 11:50:01

You are right current implementation is broken.

Could you please create a ticket?

Feng · 2014-06-04 15:36:39

Started trying to develop a small project with mormot, but the same problems encountered GUID attribute, very much hope that this issue can be resolved.

ab · 2014-06-04 22:52:04

This issue is http://synopse.info/fossil/tktview?name=b653e5f4ca

It will be resolved soon.

Thanks for the feedback.

Feng · 2014-06-05 03:09:15

Thanks

ab · 2014-06-07 20:24:55

Now you can create TGUID fields, as such:

  TSQLRecordCustomProps = class(TSQLRecordPeople)
  protected
    fGUID: TGUID;
    class procedure InternalRegisterCustomProperties(Props: TSQLRecordProperties); override;
  public
    property GUID: TGUID read fGUID write fGUID;
  end;

class procedure TSQLRecordCustomProps.InternalRegisterCustomProperties(Props: TSQLRecordProperties);
begin
  Props.RegisterCustomPropertyFromTypeName(self,'TGUID','GUID',
    @TSQLRecordCustomProps(nil).fGUID,[aIsUnique],38);
end;

See http://synopse.info/fossil/info/d02722ec7c

ab · 2014-06-08 13:49:48

... and, since Delphi XE6, you can define and work directly with published record properties of TSQLRecord:

TSQLMyRecord = class(TSQLRecordPeople)
protected
fGUID: TGUID;
published
property GUID: TGUID read fGUID write fGUID index 38;
end;

The record will be serialized as JSON - here TGUID will be serialized as a JSON string - then will be stored as TEXT column in the database.

Published properties of records are handled by our code, but Delphi doesn't create the corresponding RTTI for such properties before Delphi XE6. So record published properties, as defined in the above class definition, won't work directly for older versions of Delphi, or FreePascal.

You could use a dynamic array with only one element, in order to handle records within your TSQLRecord class definition - see Dynamic arrays from SQL code. But it may be confusing.

If you want to work with such properties before Delphi XE6, you can override the TSQLRecord.InternalRegisterCustomProperties() virtual method of a given table, to explicitly define a record property, as stated above.

See http://synopse.info/fossil/info/4d59d398d4

itSDS · 2014-06-09 15:48:48

Thank you Arnaud it works

Feng · 2014-06-10 04:08:18

Great job, thank you very much.

Feng · 2014-06-27 08:32:09

In the case of SQL Server, can be stored as a real GUID type?

ab · 2014-06-27 11:53:19

At SynDB level, there is no support of the TGUID type itself.
So it is not easily feasible, unless MS SQL Server itself is able to directly accept string values into TGUID.
Isn't it the case? See http://msdn.microsoft.com/en-us/library/ms187942

In this case, you would have to create the tabls in MS SQL by hand, not via CreateMissingTables method...

Feng · 2014-06-30 02:19:08

Thank you very much for your reply, then I will try to manually create data tables under the program.

itSDS · 2014-07-03 13:27:25

Hi AB testet the feature today with XE5 but got same error as on 24.4.2014 (XE6 is working perfect)

ab · 2014-07-03 14:19:25

Does it mean that published properties defined with record types emits RTTI since XE5 and not only since XE6?

itSDS · 2014-07-04 09:03:10

No, atm only xe6 supports RTTI for TGUID.

I tried the code unter XE5 with InternalRegisterCustomProperties but InternalRegisterCustomProperties is not called before the RTTI is needed as i wrote in this thread on 21.5.2014

itSDS wrote:

I tested TGUID - Custom Property again today. The Exception still exists.
I think there must something be changed in TSQLRecordProperties.Create
The function AddParentsFirst throws an Exception cause of the GUID - Field before the Function InternalRegisterCustomProperties is called.
In my opinion custom properties should be made available and then you should create the field - List.
Hopefully you understand what i mean smile

Last edited by itSDS (2014-07-04 09:09:36)

ab · 2014-07-04 10:32:48

The function AddParentsFirst throws an Exception cause of the GUID - Field before the Function InternalRegisterCustomProperties is called.

Sounds exactly to me like if TGUID RTTI was available for XE5, so InternalRegisterCustomProperties() is not reached....

Try to change in Synopse.inc:

    {$if CompilerVersion >= 26.0}
      {$define ISDELPHIXE5}
      {$define PUBLISHRECORD}
    {$ifend}

itSDS · 2014-07-04 11:36:32

That was the clue, I set the PUBLISHRECORD OPtion an removed the InternalRegister... functions. Now it works.

ab · 2014-07-04 11:44:47

OK so I may officially upgrade Synopse.inc to reflect this.

Could you please run the TestSQL3.dpr regression tests on XE5 to ensure everything is working as expected with PUBLISHRECORD set on XE5?

itSDS · 2014-07-06 10:28:48

Test 1.4 Synopse PDF failed

Exception at

      Check(Hash32(MS.Memory,MS.Position)=Hash[embed]);

 1.4. Synopse PDF:
!  - TPdfDocument: 1 / 4 FAILED  9.47s
  - TPdfDocumentGDI: 8 assertions passed  1.57s
  Total failed: 1 / 12  - Synopse PDF FAILED  11.05s

Last edited by itSDS (2014-07-06 10:32:19)

ab · 2014-07-06 11:30:53

I suppose this is due to some system code page issue.
This test works as expected only with a system code page = 1252 (Western Europe / English).

So if there is only this test failing, I suppose it is safe to enable PUBLISHRECORD on XE5.
See http://synopse.info/fossil/info/11f6d1c4e962
Could you please try to run the regression tests with XE5 and this latest commit?

Thanks for the feedback!

Feng · 2014-07-07 08:22:15

ab wrote:

At SynDB level, there is no support of the TGUID type itself.
So it is not easily feasible, unless MS SQL Server itself is able to directly accept string values into TGUID.
Isn't it the case? See http://msdn.microsoft.com/en-us/library/ms187942
In this case, you would have to create the tabls in MS SQL by hand, not via CreateMissingTables method...

In the case of manually creating tables, found the following exceptions:
20140707 10311161 SQL TOleDBStatement(02B687E0) insert into dbo.CollectPointRecord (ID,CollectPointID,CollectPointName) VALUES (?OOR?,?OOR?,?OOR?)
20140707 10311161 EXC ESQLDBException ("Invalid TOleDBStatement.Bind(2,TSQLDBFieldType(0),24A7499C-358E-43AC-B740-F37A5A631BF5)") at 001A99DA SynDB.TSQLDBStatement.Bind (5079)
stack trace API
001238A8 SynCommons.SynRtlUnwind (39860)
000090F0 System.@HandleAnyException (18659)
001A99DA SynDB.TSQLDBStatement.Bind (5079)
001B34E8 mORMotDB.TSQLRestStorageExternal.ExecuteFromJSON (1556)
001B15EF mORMotDB.TSQLRestStorageExternal.EngineAdd (1057)
0017E501 mORMot.TSQLRestServer.EngineAdd (26741)
00171407 mORMot.TSQLRest.Add (22991)

Correlation table definition：
CREATE TABLE [dbo].[CollectPointRecord](
[ID] [int] NOT NULL,
[CollectPointID] [uniqueidentifier] NULL,
[CollectPointName] [nvarchar](30) NULL
) ON [PRIMARY]

Am I overlooking what is content?

ab · 2014-07-07 09:10:05

Is it allowed to bind a string value to a UniqueIdentifier column, for MS SQL?

Feng · 2014-07-07 09:21:46

String that can be plugged directly into MS SQL, by direct database connection. If modify the TSQLRecord field is String type UniqueIdentifier columns, still exist.
20140707 17142543 EXC ESQLDBException ("Invalid TOleDBStatement.Bind(2,TSQLDBFieldType(0),{3AF6AA5B-D6D3-460C-9737-6630BB6B8EAE})") at 001A9A16 SynDB.TSQLDBStatement.Bind (5079)
stack trace API
001238E4 SynCommons.SynRtlUnwind (39860)
000090F0 System.@HandleAnyException (18659)
001A9A16 SynDB.TSQLDBStatement.Bind (5079)
001B3524 mORMotDB.TSQLRestStorageExternal.ExecuteFromJSON (1556)
001B162B mORMotDB.TSQLRestStorageExternal.EngineAdd (1057)
0017E53D mORMot.TSQLRestServer.EngineAdd (26741)
00171443 mORMot.TSQLRest.Add (22991)

The only difference is the GUID of a brace.

Feng · 2014-07-07 10:21:05

Tracking source code, should feel no recognizes MS SQL uniqueidentifier type TSQLDBConnectionProperties.ColumnTypeNativeToDB method.

ab · 2014-07-07 12:45:32

Indeed.
TSQLDBFieldType(0) in the log means ftUnknown.

Method TSQLDBStatement.ColumnTypeNativeToDB() will now recognize 'uniqueidentifier' data type as ftUTF8.
See http://synopse.info/fossil/info/1d27aac8155a7dc4f88dd8

Feng · 2014-07-08 02:12:34

Great, String bound to MS SQL uniqueidentifier data types.

mORMot Open Source

#1 2012-10-17 15:02:15

Support for a TGUID field

#2 2012-10-17 15:55:39

Re: Support for a TGUID field

#3 2012-10-17 16:10:22

Re: Support for a TGUID field

#4 2012-10-18 06:43:26

Re: Support for a TGUID field

#5 2012-10-18 07:05:44

Re: Support for a TGUID field

#6 2012-10-18 07:56:37

Re: Support for a TGUID field

#7 2012-10-18 08:02:27

Re: Support for a TGUID field

#8 2012-10-18 12:25:37

Re: Support for a TGUID field

#9 2012-10-18 13:30:18

Re: Support for a TGUID field

#10 2012-10-18 13:38:21

Re: Support for a TGUID field

#11 2012-10-18 19:35:11

Re: Support for a TGUID field

#12 2012-10-19 07:01:24

Re: Support for a TGUID field

#13 2012-10-26 10:10:59

Re: Support for a TGUID field

#14 2012-10-26 13:38:16

Re: Support for a TGUID field

#15 2014-05-14 12:26:02

Re: Support for a TGUID field

#16 2014-05-14 14:08:19

Re: Support for a TGUID field

#17 2014-05-14 15:24:45

Re: Support for a TGUID field

#18 2014-05-15 15:44:20

Re: Support for a TGUID field

#19 2014-05-16 07:42:14

Re: Support for a TGUID field

#20 2014-05-21 11:36:45

Re: Support for a TGUID field

#21 2014-05-21 11:50:01

Re: Support for a TGUID field

#22 2014-06-04 15:36:39

Re: Support for a TGUID field

#23 2014-06-04 22:52:04

Re: Support for a TGUID field

#24 2014-06-05 03:09:15

Re: Support for a TGUID field

#25 2014-06-07 20:24:55

Re: Support for a TGUID field

#26 2014-06-08 13:49:48

Re: Support for a TGUID field

#27 2014-06-09 15:48:48

Re: Support for a TGUID field

#28 2014-06-10 04:08:18

Re: Support for a TGUID field

#29 2014-06-27 08:32:09

Re: Support for a TGUID field

#30 2014-06-27 11:53:19

Re: Support for a TGUID field

#31 2014-06-30 02:19:08

Re: Support for a TGUID field

#32 2014-07-03 13:27:25

Re: Support for a TGUID field

#33 2014-07-03 14:19:25

Re: Support for a TGUID field

#34 2014-07-04 09:03:10

Re: Support for a TGUID field

#35 2014-07-04 10:32:48

Re: Support for a TGUID field

#36 2014-07-04 11:36:32

Re: Support for a TGUID field

#37 2014-07-04 11:44:47

Re: Support for a TGUID field

#38 2014-07-06 10:28:48

Re: Support for a TGUID field

#39 2014-07-06 11:30:53

Re: Support for a TGUID field

#40 2014-07-07 08:22:15