Nullable types for mORMot

hnb · 2015-06-24 11:27:24

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)

hnb · 2015-06-24 20:13:37

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.

ab · 2015-06-24 21:08:49

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.

ab · 2015-06-25 06:46:13

I'm working on it!

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

hnb · 2015-06-25 06:48:21

IMO idea to use a "Nullable* = type variant" definition is more "Convention over configuration" . 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.

hnb · 2015-06-25 07:44:16

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)

ab · 2015-06-25 10:03:56

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!

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

hnb · 2015-06-25 11:47:51

ab wrote:

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

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

ab · 2015-06-25 19:09:47

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?

hnb · 2015-06-25 20:24:13

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 :
http://synopse.info/files/html/Synopse% … #TITLE_138

ab · 2015-06-26 17:36:49

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!

hnb · 2015-07-20 12:08:49

ab wrote:

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

So first proposition was the best .

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)

hnb · 2015-07-24 09:03:45

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

ab · 2015-07-24 12:50:20

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.

hnb · 2015-07-24 13:08:05

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 I will try to fix FPC compiler issues related to this, but in later time.

hnb · 2015-07-29 07:08:19

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

Patch is attached:

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

ab · 2015-07-29 09:05:03

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

Thanks for the patch!

ab · 2015-09-26 13:33:31

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!

hnb · 2015-09-28 06:28:09

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

ab · 2015-09-28 07:19:13

You are the man, here, not me!

cypriotcalm · 2015-12-11 09:44:53

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!

cypriotcalm · 2016-01-15 12:41:18

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)

ab · 2016-01-15 15:28:57

Thanks for the tip!

mORMot Open Source

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

Nullable types for mORMot

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

Re: Nullable types for mORMot

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

Re: Nullable types for mORMot

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

Re: Nullable types for mORMot

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

Re: Nullable types for mORMot

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

Re: Nullable types for mORMot

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

Re: Nullable types for mORMot

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

Re: Nullable types for mORMot

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

Re: Nullable types for mORMot

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

Re: Nullable types for mORMot

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

Re: Nullable types for mORMot

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

Re: Nullable types for mORMot

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

Re: Nullable types for mORMot

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

Re: Nullable types for mORMot

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

Re: Nullable types for mORMot

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

Re: Nullable types for mORMot

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

Re: Nullable types for mORMot

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

Re: Nullable types for mORMot

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

Re: Nullable types for mORMot

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

Re: Nullable types for mORMot

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

Re: Nullable types for mORMot

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

Re: Nullable types for mORMot

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

Re: Nullable types for mORMot

Board footer