ORM TNullable* fields for NULL storage
In Delphi code, NULLable types do not exist as such. There is no
int? type, as in C#.
But at SQL and JSON levels, the NULL value does exist and should be converted as expected by the ORM.
In SQLite3 itself, NULL is handled as stated in http://www.sqlite.org/lang_expr.html
IS NOT operators).
It is worth noting that NULL handling is not consistent among all existing database engines, e.g. when you are comparing NULL with non NULL values... so we recommend using it with care in any database statements, or only with proper (unit) testing, when you switch from one database engine to another.
By default, in the mORMot ORM/SQL code, NULL will appear only in
case of a BLOB storage with a size of
Otherwise, you should not see it as a value, in most kinds of ORM properties.
Null-oriented value types have been implemented in our framework, since the object pascal language does not allow defining a nullable type (yet).
We choose to store those values as
variant, with a set of
TNullable dedicated types, as defined in
type TNullableInteger = type variant; TNullableBoolean = type variant; TNullableFloat = type variant; TNullableCurrency = type variant; TNullableDateTime = type variant; TNullableTimeLog = type variant; TNullableUTF8Text = type variant;
In order to define a
NULLable column of such types, you could
use them as types for your
TSQLRecord class definition:
type TSQLNullableRecord = class(TSQLRecord) protected fInt: TNullableInteger; fBool: TNullableBoolean; fFlt: TNullableFloat; fCurr: TNullableCurrency; fDate: TNullableDateTime; fTimeStamp: TNullableTimeLog; fCLOB: TNullableUTF8Text; fText: TNullableUTF8Text; published property Int: TNullableInteger read fInt write fInt; property Bool: TNullableBoolean read fBool write fBool; property Flt: TNullableFloat read fFlt write fFlt; property Curr: TNullableCurrency read fCurr write fCurr; property Date: TNullableDateTime read fDate write fDate; property TimeStamp: TNullableTimeLog read fTimeStamp write fTimeStamp; property CLOB: TNullableUTF8Text read fCLOB write fCLOB; property Text: TNullableUTF8Text index 32 read fText write fText; end;
Such a class would let the ORM handle SQL NULL values as expected, i.e.
null variant value, or an integer/number/text value if
there is something stored.
Of course, the corresponding column in the database would have the expected data type, e.g. a
NULLABLE INTEGER for
TNullableUTF8Text is defined as a
RawUTF8 usual field.
That is, without any size limitation by default (as for the
property), or with an explicit size limitation using the
attribute (as for
Text property, which would be converted as a
VARCHAR(32) SQL column).
You could use the following wrapper functions to create a
TNullable* value from any non-nullable standard Delphi value:
function NullableInteger(const Value: Int64): TNullableInteger; function NullableBoolean(Value: boolean): TNullableBoolean; function NullableFloat(const Value: double): TNullableFloat; function NullableCurrency(const Value: currency): TNullableCurrency; function NullableDateTime(const Value: TDateTime): TNullableDateTime; function NullableTimeLog(const Value: TTimeLog): TNullableTimeLog; function NullableUTF8Text(const Value: RawUTF8): TNullableUTF8Text;
Some corresponding functions are able to return the expected
null value for each kind, with strong typing (to be used for FPC
compatibility, which does not allow direct assignment to a
type variant property):
function NullableIntegerNull: TNullableInteger; function NullableIntegerBoolean: TNullableBoolean; ...
You could check for a
TNullable* value to contain null, using
the following functions:
function NullableIntegerIsEmptyOrNull(const V: TNullableInteger): Boolean; function NullableBooleanIsEmptyOrNull(const V: TNullableBoolean): Boolean; ...
Or retrieve a Delphi non-nullable value in one step, using the corresponding wrappers:
function NullableIntegerToValue(const V: TNullableInteger; out Value: Int64): Boolean; function NullableBooleanToValue(const V: TNullableBoolean; out Value: Boolean): Boolean; ... function NullableIntegerToValue(const V: TNullableInteger): Int64; function NullableBooleanToValue(const V: TNullableBoolean; out Value: Boolean): Boolean; ...
Thanks to those types, and their corresponding wrapper functions, you have at hand everything needed to safely store some nullable values into your application database, with proper handling on Delphi side.
Thanks hnb for the idea, feedback, and support!
The documentation has been updated, and should be browsed as reference.
Feedback is welcome on our forum, as usual!