Web forum is in read-only mode. Login as active registered customer for write access
  Forum Search   New Posts New Posts

Using PostgreSQL tstzrange datatype

 Post Reply Post Reply
Author
Ronald Janse View Drop Down
Senior Member
Senior Member
Avatar

Joined: 20 Aug 2015
Posts: 265
Post Options Post Options   Quote Ronald Janse Quote  Post ReplyReply Direct Link To This Post Topic: Using PostgreSQL tstzrange datatype
    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?
Back to Top
Wagner R. Landgraf View Drop Down
TMS Support
TMS Support
Avatar

Joined: 18 May 2010
Posts: 2299
Post Options Post Options   Quote Wagner R. Landgraf Quote  Post ReplyReply Direct Link To This Post 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.
Back to Top
Ronald Janse View Drop Down
Senior Member
Senior Member
Avatar

Joined: 20 Aug 2015
Posts: 265
Post Options Post Options   Quote Ronald Janse Quote  Post ReplyReply Direct Link To This Post 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.
Back to Top
Wagner R. Landgraf View Drop Down
TMS Support
TMS Support
Avatar

Joined: 18 May 2010
Posts: 2299
Post Options Post Options   Quote Wagner R. Landgraf Quote  Post ReplyReply Direct Link To This Post Posted: 12 Dec 2016 at 10:35pm
Not a plan for that is short term, because it was something that was not asked before.
Back to Top
Ronald Janse View Drop Down
Senior Member
Senior Member
Avatar

Joined: 20 Aug 2015
Posts: 265
Post Options Post Options   Quote Ronald Janse Quote  Post ReplyReply Direct Link To This Post 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?
Back to Top
Wagner R. Landgraf View Drop Down
TMS Support
TMS Support
Avatar

Joined: 18 May 2010
Posts: 2299
Post Options Post Options   Quote Wagner R. Landgraf Quote  Post ReplyReply Direct Link To This Post 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.
Back to Top
Ronald Janse View Drop Down
Senior Member
Senior Member
Avatar

Joined: 20 Aug 2015
Posts: 265
Post Options Post Options   Quote Ronald Janse Quote  Post ReplyReply Direct Link To This Post 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....
Back to Top
Wagner R. Landgraf View Drop Down
TMS Support
TMS Support
Avatar

Joined: 18 May 2010
Posts: 2299
Post Options Post Options   Quote Wagner R. Landgraf Quote  Post ReplyReply Direct Link To This Post 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.
Back to Top
Ronald Janse View Drop Down
Senior Member
Senior Member
Avatar

Joined: 20 Aug 2015
Posts: 265
Post Options Post Options   Quote Ronald Janse Quote  Post ReplyReply Direct Link To This Post Posted: 14 Dec 2016 at 1:10pm
Yes, that is correct. I think I can leave that out.
Back to Top
Ronald Janse View Drop Down
Senior Member
Senior Member
Avatar

Joined: 20 Aug 2015
Posts: 265
Post Options Post Options   Quote Ronald Janse Quote  Post ReplyReply Direct Link To This Post 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.
Back to Top
Wagner R. Landgraf View Drop Down
TMS Support
TMS Support
Avatar

Joined: 18 May 2010
Posts: 2299
Post Options Post Options   Quote Wagner R. Landgraf Quote  Post ReplyReply Direct Link To This Post 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
Back to Top
Ronald Janse View Drop Down
Senior Member
Senior Member
Avatar

Joined: 20 Aug 2015
Posts: 265
Post Options Post Options   Quote Ronald Janse Quote  Post ReplyReply Direct Link To This Post 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 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.
Back to Top
Wagner R. Landgraf View Drop Down
TMS Support
TMS Support
Avatar

Joined: 18 May 2010
Posts: 2299
Post Options Post Options   Quote Wagner R. Landgraf Quote  Post ReplyReply Direct Link To This Post 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?
Back to Top
Ronald Janse View Drop Down
Senior Member
Senior Member
Avatar

Joined: 20 Aug 2015
Posts: 265
Post Options Post Options   Quote Ronald Janse Quote  Post ReplyReply Direct Link To This Post 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.

Back to Top
Wagner R. Landgraf View Drop Down
TMS Support
TMS Support
Avatar

Joined: 18 May 2010
Posts: 2299
Post Options Post Options   Quote Wagner R. Landgraf Quote  Post ReplyReply Direct Link To This Post 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.
Back to Top
 Post Reply Post Reply

Forum Jump Forum Permissions View Drop Down