Allow last hid in temporal table and public table view

[Crystark]

Hi,

We're using Chronomodel for two purpose:

• versioning records to track user changes
• track the record version when it's referenced outside our application (through URLs)

We can currently achieve the latest by doing an INNER JOIN with the historical table but as the amount of versions will grow this will prove less and less performant.

What we'd like its to be able to access the latest HID using the temporal table and if possible the view in the public schema.
Is it possible to add the HID to the temporal table ? Or at least give an option to do so ?

Thanks
Regards

[vjt]

Hi!

To access the latest hid you could use something like SELECT MAX(hid) FROM history.foos WHERE id = $id - that is indexed and should be fast enough for your needs. Or, alternatively, you can ORDER BY hid DESC in your INNER JOIN and then use LIMIT...

Caching it in the temporal table is possible, by amending the INSERT and UPDATE triggers - but I think it should be opt-in.

What do you think?

[Crystark]

The request you mention is fine on it's own but we're actually fetching the hid inside a much more complex one that has many joins and sub-queries which is why we'd like to cache it in the temporal table (and the view).

I totally agree it should be opt-in. Do you have a way to easily add this so that the triggers are built depending on an option ?

[vjt]

Yes! Chronomodel saves the options you pass to create_table in table metadata (the COMMENT field: [link]

Then, one of these options, selective journaling is used to build a custom UPDATE trigger.

You may follow the same approach.

<loudthinking>
Of course, this needs refactoring, as that method has become too big, and string interpolation is not enough anymore so we may want to use a template engine such as ERb to build these triggers, it'll be done in the future :-)
<loudthinking>