#1 2015-06-24 11:27:24

hnb
Member
Registered: 2015-06-15
Posts: 234

Nullable types for mORMot

Hi,

Next episode for "NULL" story.

I really need use more precise ORM system for master/slave replication. For master is used "database-first" approach (PostgreSQL), but for slave client on android is used "code-first". The problem exist for "SlaveServer.CreateMissingTables". Slave table is very different from master table. All columns created for table from published variant property they are "TEXT", but I need them as TDateTime (TEXT COLLATE ISO8601) or as Integer, and I need to be able set theirs values to NULL from ORM.

My initial solution:

  TSQLexample = class(TSQLRecord)
  protected
    ftest: NullableInteger;
  published
    property test: NullableInteger read ftest write ftest;
  end;

"draft patch" with NullableInteger and NullableDateTime attached:

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

Patch can be extended for new nullable types and type checking in new classes like TSQLPropInfoRTTINullableInteger, TSQLPropInfoRTTINullableDateTime etc.

regards,
Maciej Izak

Last edited by hnb (2015-06-24 11:30:04)


best regards,
Maciej Izak

Offline

#2 2015-06-24 20:13:37

hnb
Member
Registered: 2015-06-15
Posts: 234

Re: Nullable types for mORMot

I do not know whether the technique is correct (just a concept). Patch is ofc not finished. I ask before I start further work on this.


best regards,
Maciej Izak

Offline

#3 2015-06-24 21:08:49

ab
Administrator
From: France
Registered: 2010-06-21
Posts: 9,070
Website

Re: Nullable types for mORMot

It is a good idea to use a "Nullable* = type variant" definition, for nullable types.
Another possibility may be to use a generic record (TNullable<Integer>) but it may not be so simple.

I guess we may just use a single sftVariant kind of field, but with some options at TSQLPropInfoRTTIVariant level, and dedicated SQL process at SynDB/mORMotDB levels.
It would make the code easier to maintain.

Offline

#4 2015-06-25 06:46:13

ab
Administrator
From: France
Registered: 2010-06-21
Posts: 9,070
Website

Re: Nullable types for mORMot

I'm working on it!

I suspect I can do it easily, with minimal code change.

Offline

#5 2015-06-25 06:48:21

hnb
Member
Registered: 2015-06-15
Posts: 234

Re: Nullable types for mORMot

IMO idea to use a "Nullable* = type variant" definition is more "Convention over configuration" smile. Especially for mixing "database-first"/"code-first" ORM coding, where I have a tons of Nullable* columns.

Sadly we can't use TNullable<Integer>... FPC don't allow this kind of type in published section...

Solution with single sftVariant kind of field needs more (much more...) configuration from code level, for example:

// nt* = Nullable Type Enum
fModel.Props[TSQLRecordWithVariants].ExternalDB.
  MapVariantFieldType('foo', ntInteger).
  MapVariantFieldType('YearOfDeath', ntDateTime);

Using few more types like sftVariant don't bring much more code.

The decision is yours.


best regards,
Maciej Izak

Offline

#6 2015-06-25 07:44:16

hnb
Member
Registered: 2015-06-15
Posts: 234

Re: Nullable types for mORMot

Another solution (IMO the best):

  TSQLexample = class(TSQLRecord)
  protected
    ftest: Variant;
    fstrtest: Variant;
  published
    property test: Variant index NULLABLE_INTEGER read ftest write ftest; // NULLABLE_INTEGER = -1, NULLABLE_DATETIME = -2 etc.
    property strtest: Variant index 30 read fstrtest write fstrtest; // similar to property strtest: RawUTF8 index 30 (...)
  end;

Last edited by hnb (2015-06-25 09:54:24)


best regards,
Maciej Izak

Offline

#7 2015-06-25 10:03:56

ab
Administrator
From: France
Registered: 2010-06-21
Posts: 9,070
Website

Re: Nullable types for mORMot

hnb wrote:

Another solution (IMO the best):

     property test: Variant index NULLABLE_INTEGER read ftest write ftest; // NULLABLE_INTEGER = -1, NULLABLE_DATETIME = -2 etc.

This is very clever.
And very... mORMotish...
I wish I had this idea!
wink

I have created a feature request ticket.
See http://synopse.info/fossil/tktview/a3bce7fdd19e

Offline

#8 2015-06-25 11:47:51

hnb
Member
Registered: 2015-06-15
Posts: 234

Re: Nullable types for mORMot

ab wrote:

I have created a feature request ticket.
See http://synopse.info/fossil/tktview/a3bce7fdd19e

Yay big_smile when it will be finished? Lack of this feature is blocking my work...


best regards,
Maciej Izak

Offline

#9 2015-06-25 19:09:47

ab
Administrator
From: France
Registered: 2010-06-21
Posts: 9,070
Website

Re: Nullable types for mORMot

What do you think of those type definitions:
http://synopse.info/fossil/info/0c16fc685c

Do you think other types are needed?
Is the documentation clear enough?

Offline

#10 2015-06-25 20:24:13

hnb
Member
Registered: 2015-06-15
Posts: 234

Re: Nullable types for mORMot

IMO for now it is enough. Looks great, it came out perfect. It came out consistent with the whole TSQLFieldType enum. This will be great improvements for mORMot (missing element).

soon you have to make adjustments the documentation wink :
http://synopse.info/files/html/Synopse% … #TITLE_138


best regards,
Maciej Izak

Offline

#11 2015-06-26 17:36:49

ab
Administrator
From: France
Registered: 2010-06-21
Posts: 9,070
Website

Re: Nullable types for mORMot

I've finally defined some TNullable* types.
We did not use the "... variant index sftNullable* read ..." trick, which appeared to be overcomplicated to define - just my opinion, but "index" is one of misunderstood and not natural feature of the framework, as has been reported to me.

See http://synopse.info/fossil/info/7d5e0d4ba4
It was in fact some huge work, since we had to let a property behave like several kind of properties... All other properties have a fixed storage type, not our TNullable* fields.
Not so easy...

Once it is stabilized and proven, I would update the documentation.

Warning: it is not tested yet: feedback is welcome!

Offline

#12 2015-07-20 12:08:49

hnb
Member
Registered: 2015-06-15
Posts: 234

Re: Nullable types for mORMot

ab wrote:

I've finally defined some TNullable* types.
...
Warning: it is not tested yet: feedback is welcome!

So first proposition was the best smile.

All works fine, but with small problems in FPC. Using "T* = type Variant" will raise "Fatal: Compilation aborted" for any usage:

VarIsNull(somerec.nullableColumn); // Fatal: Compilation aborted

workaround:

VarIsNull(Variant((@somerec.nullableColumn)^));

Last edited by hnb (2015-07-20 12:09:37)


best regards,
Maciej Izak

Offline

#13 2015-07-24 09:03:45

hnb
Member
Registered: 2015-06-15
Posts: 234

Re: Nullable types for mORMot

Other problems in FPC. To assign any Value to nullable property, I need to use another workaround:

var
  LVariantValue: Variant;
begin
  LVariantValue := someInt64var;
  if LVariantValue <> 0 then
    someRecord.fooNullableInt := TNullableInteger(LVariantValue)
  else
    someRecord.fooNullableInt := TNullableInteger(NULL);  

Otherwise I got error: Incompatible types: got "X" expected "TNullableY".

this construction is unavailable in FPC:

  if someInt64var <> 0 then
    someRecord.fooNullableInt := someInt64var
  else
    someRecord.fooNullableInt := NULL;  

Btw.

approach with sftNullable* is a solution to the problem:

http://synopse.info/fossil/info/0c16fc6 … 4626d096fb

best regards,
Maciej Izak


best regards,
Maciej Izak

Offline

#14 2015-07-24 12:50:20

ab
Administrator
From: France
Registered: 2010-06-21
Posts: 9,070
Website

Re: Nullable types for mORMot

I've added Nullable*() Nullable*Null Nullable*IsEmptyOrNull() wrapper functions.
See http://synopse.info/fossil/info/7c28f2cbe862

Could be used to circumvent those FPC compilation issues, and also makes cleaner code.

Offline

#15 2015-07-24 13:08:05

hnb
Member
Registered: 2015-06-15
Posts: 234

Re: Nullable types for mORMot

ab wrote:

I've added Nullable*() Nullable*Null Nullable*IsEmptyOrNull() wrapper functions.
See http://synopse.info/fossil/info/7c28f2cbe862

Could be used to circumvent those FPC compilation issues, and also makes cleaner code.

Thanks for that smile I will try to fix FPC compiler issues related to this, but in later time.


best regards,
Maciej Izak

Offline

#16 2015-07-29 07:08:19

hnb
Member
Registered: 2015-06-15
Posts: 234

Re: Nullable types for mORMot

I've added Nullable*ToValue() wrapper functions. With this, "nullable runtime" is complete smile

Patch is attached:

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


best regards,
Maciej Izak

Offline

#17 2015-07-29 09:05:03

ab
Administrator
From: France
Registered: 2010-06-21
Posts: 9,070
Website

Re: Nullable types for mORMot

Included by http://synopse.info/fossil/info/b8275632e2

Thanks for the patch!

Offline

#18 2015-09-26 13:33:31

ab
Administrator
From: France
Registered: 2010-06-21
Posts: 9,070
Website

Re: Nullable types for mORMot

I've redirected the http://blog.synopse.info/post/2015/09/2 … LL-storage blog article comments to this page.

Thanks again hnb for sharing!

Offline

#19 2015-09-28 06:28:09

hnb
Member
Registered: 2015-06-15
Posts: 234

Re: Nullable types for mORMot

I really like your credits to contributors! It is really motivating smile


best regards,
Maciej Izak

Offline

#20 2015-09-28 07:19:13

ab
Administrator
From: France
Registered: 2010-06-21
Posts: 9,070
Website

Re: Nullable types for mORMot

You are the man, here, not me!
cool

Offline

#21 2015-12-11 09:44:53

cypriotcalm
Member
Registered: 2015-02-18
Posts: 117

Re: Nullable types for mORMot

Hello,

this is a nice feature! But a question, how can I save a NULL value. I am using MySQL+FireDAC.

TSQLCar = class(TSQLRecord)
public
  Color: TNullableUTF8Text;
end;

Reading from DB is good if the filed is NULL. As a result I get in the object an empty string.

But writing! I am doing the following:

Car.Color := ''; // -> it saves the empty string, not NULL

If I execute the following code, I get from FireDAC an exception something like "[FireDAC][Phys][MySQL]Datatype of the parameter is unknown...Please provide the TFDParam.DataType..."

Car.Color := NULL; // -> Exception
or 
Car.Color := NullableUTF8TextNull; // -> Exception

What am I doing wrong? Thanky you for the help!

Offline

#22 2016-01-15 12:41:18

cypriotcalm
Member
Registered: 2015-02-18
Posts: 117

Re: Nullable types for mORMot

Hi there!

in order to get rid of the error above you have to change the default parameter data type in FireDAC to Variant

  FDManager.FormatOptions.DefaultParamDataType := Data.DB.TFieldType.ftVariant;

Have a good day! :-)

Last edited by cypriotcalm (2016-01-15 12:47:28)

Offline

#23 2016-01-15 15:28:57

ab
Administrator
From: France
Registered: 2010-06-21
Posts: 9,070
Website

Re: Nullable types for mORMot

Thanks for the tip!

Offline

Board footer

Powered by FluxBB