Using PostgreSQL tstzrange datatype

First question:
Is it possible to declare fields in a table of type tstzrange (Range of timestamp with time zone) in the datamodeller? And if so, how do I do that? I don't see how it can be achieved by using a domain.
It is not a datatype I require for Aurelius, it is used for temporal tables, to record changes in the database.

Second question:
Can I use inheritance in the datamodeller?

  1. That field type is available in the list of field types indeed. Now workaround you can use is set the field type as "computed" and then type "tstzrange" in the field expression. 

    2. There is an option in the Aurelius export where you choose that 1:1 relationships must be exported as inheritance. If you are talking about database modeling itself, then there is no such option.
  1. Great, that works ok!
    2. It was meant for use in the Datamodeller. Are you perhaps planning something like that in the near future?
    Thanks for the updates.

Not a plan for that is short term, because it was something that was not asked before.

I managed to construct the datatype tstzrange in PostgreSQL as you explained. That works well in PostgreSQL.
But I now have a new problem. I use this field only in the database itself (with database triggers, for temporal use), but because it is in the datamodeller the Aurelius export makes a field for it. And when I run my program I get an error: Unexpected field type: System.Variant. I do NOT use the variable in my program, it is nearly in my generated Aurelius datamodel.
This is the generated Aureliuscode:


    [Column('sys_period', [TColumnProp.NoInsert, TColumnProp.NoUpdate])]
    [Description('')]
    Fsys_period: Nullable<Variant>;

    property sys_period: Nullable<Variant> read Fsys_period;


When I leave this code out the program runs correctly.
Can I somehow bypass the construction of the sys_period variable in the Aurelius export, or - better still - do something in my code that avoids the error?

In the Aurelius Export dialog in Data Modeler you have the option to uncheck the classes and properties you don't want to be exported. Just uncheck that property and the generated class won't have it included.

I thought so too, but this field does not have a checkbox in front of it like the other fields....

Because you created it as not null. Is that correct? Only nullable fields can be absent from the class.

Yes, that is correct. I think I can leave that out.

First Question
============
Is it possible at all to use a tstzrange field, as described above, in Aurelius? As I mentioned, this field, when declared as you mentioned, is of type Nullable<Variant>. There is a compilererror when it is used in Aurelius. What can I do to use it?

Second Question
==============
Wagner told me earlier how to use a View in Aurelius, by creating a table that deliveres the data read-only to Aurelius. That works well. But I would very much like an option to leave that table out of the script that is generated. If I don't remove it manually after the script is created, the result is an error, because a view is created with the same name.

  1. What kind of data you want to store there? Variant type is not supported, I'm afraid there isn't a Delphi primitive type that can hold information from such column type?

    2. You can use the multi-model feature to separate logically the classes. You can tag all classes that need to be in the script in a special model and ask Database Manager to update the database according to that model: http://www.tmssoftware.biz/business/aurelius/doc/web/multi-model_design.html

Ad 1. The tstzrange is a datatype that holds a from-date and to-date in one variable. It's a date/timerange. See here.
This datatype is used to implement a temporal database, that saves changed records automatically in a historytable, with a timerange that shows how long that incarnation of the record was active. You told me (second post of this thread) that this datatype can be created from the datamodeller. And how. And that works great. But when it is exported to Aurelius the datamodeller creates this Variant type, which logically can't be handled by Aurelius. Can I somehow pursuade Aurelius to use this datarangetype?

2. This sounds like a good idea, I'll try that, thank you.

  1. As I mentioned earlier, this field is not supported by Aurelius, you should uncheck it when exporting. My question was: how you expect to use this field from Aurelius? Using which Delphi type for the property that is supposed to hold its value?

I imagined Aurelius could give back a class (Ttstzrange?), containing the two TDateTime values if possible, or two DateTime variables. Var_Name_From and Var_Name_To.

That's what I though, but for now Aurelius doesn't support custom types like a hypothetical Tstzrange type. That's something that should be implemented in the core of it.