Print Page | Close Window

Using PostgreSQL tstzrange datatype

Printed From: TMS Software
Category: Business Tools
Forum Name: Data Modeler
Forum Discription:
URL: http://www.tmssoftware.com/site/forum/forum_posts.asp?TID=7280
Printed Date: 19 Aug 2019 at 8:15am


Topic: Using PostgreSQL tstzrange datatype
Posted By: Ronald Janse
Subject: Using PostgreSQL tstzrange datatype
Date Posted: 09 Dec 2016 at 3:20pm
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?



Replies:
Posted By: Wagner R. Landgraf
Date Posted: 10 Dec 2016 at 1:34pm
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.


Posted By: Ronald Janse
Date Posted: 12 Dec 2016 at 8:57am
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.


Posted By: Wagner R. Landgraf
Date Posted: 12 Dec 2016 at 10:35pm
Not a plan for that is short term, because it was something that was not asked before.


Posted By: Ronald Janse
Date Posted: 14 Dec 2016 at 10:33am
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?


Posted By: Wagner R. Landgraf
Date Posted: 14 Dec 2016 at 11:58am
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.


Posted By: Ronald Janse
Date Posted: 14 Dec 2016 at 12:00pm
I thought so too, but this field does not have a checkbox in front of it like the other fields....


Posted By: Wagner R. Landgraf
Date Posted: 14 Dec 2016 at 12:02pm
Because you created it as not null. Is that correct? Only nullable fields can be absent from the class.


Posted By: Ronald Janse
Date Posted: 14 Dec 2016 at 1:10pm
Yes, that is correct. I think I can leave that out.


Posted By: Ronald Janse
Date Posted: 21 Dec 2016 at 4:51pm
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.


Posted By: Wagner R. Landgraf
Date Posted: 21 Dec 2016 at 5:00pm
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 - http://www.tmssoftware.biz/business/aurelius/doc/web/multi-model_design.html


Posted By: Ronald Janse
Date Posted: 22 Dec 2016 at 8:54am
Ad 1. The tstzrange is a datatype that holds a from-date and to-date in one variable. It's a date/timerange. See https://www.postgresql.org/docs/9.5/static/rangetypes.html - 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.


Posted By: Wagner R. Landgraf
Date Posted: 22 Dec 2016 at 11:42am
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?


Posted By: Ronald Janse
Date Posted: 22 Dec 2016 at 3:19pm
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.



Posted By: Wagner R. Landgraf
Date Posted: 22 Dec 2016 at 7:20pm
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.



Print Page | Close Window