Thread: Versioning Schema SQL ideas needed

Hi,

I've spent too long staring at code today and am facing a bit of a
block when trying to figure out how to best implement the following.

I'm basically looking at tracking versions of some configuration items
saved in a database table.  My thinking of the table is something
along the following :

create table templates(
   template_id int not null primary key,
   template_groupid int not null,
   template_version int not null
   template_text text not null);

My thinking on the above is :
- template_id is a unique ID for that version
- template_groupid identifies the set the template belongs to
- version is the version

Would I need to resort to using a CTE or subquery to make this sort of
thing work ?  I can't seem to make it work with group by since group
by expects aggregation. Surely I don't need to normalise it into a
couple of tables ?

Sorry if its a silly question !

Tim

> DISTINCT ON is my favorite lesser-known Postgres feature.

You said it !  There I was, trying and failing to make "DISTINCT" work
for me, little did I know that the little word ON was missing from my
Postgresql vocabulary !


Thanks !

On 27 January 2015 at 02:24, Maciek Sakrejda <maciek@heroku.com> wrote:
> On Mon, Jan 26, 2015 at 2:38 PM, Tim Smith <randomdev4+postgres@gmail.com>
> wrote:
>>
>> create table templates(
>>    template_id int not null primary key,
>>    template_groupid int not null,
>>    template_version int not null
>>    template_text text not null);
>>
>> Would I need to resort to using a CTE or subquery to make this sort of
>> thing work ?  I can't seem to make it work with group by since group
>> by expects aggregation. Surely I don't need to normalise it into a
>> couple of tables ?
>
>
>
> What sort of thing? Selecting the latest version of each template? Try
>
> SELECT
>   DISTINCT ON (template_group_id) template_id, template_group_id,
> template_version, template_text
> FROM
>   templates
> ORDER BY
>   template_group_id, template_version DESC
>
> You could even create a latest_templates view for this. DISTINCT ON is my
> favorite lesser-known Postgres feature.

On 1/26/15 4:38 PM, Tim Smith wrote:
> create table templates(
>     template_id int not null primary key,
>     template_groupid int not null,
>     template_version int not null
>     template_text text not null);
>
> My thinking on the above is :
> - template_id is a unique ID for that version
> - template_groupid identifies the set the template belongs to
> - version is the version

I suggest something different that (IMHO) is clearer:

CREATE TABLE template_group(
   template_group_id SERIAL PRIMARY KEY
   , template_group_name text NOT NULL UNIQUE
   ...
);

CREATE TABLE template_history(
   template_history_id SERIAL
   , template_group_id int REFERENCES template_group
   , template_previous_hid int REFERENCES template_history
   , ...
);
CREATE UNIQUE INDEX template_history__u_group_null_previous
   ON template_history( template_group_id )
   WHERE template_previous_hid IS NULL
;
CREATE UNIQUE INDEX template_history__u_group__previous
   ON template_history( template_group_id, template_previous_hid )
   WHERE template_previous_hid IS NOT NULL
;
And then a BEFORE INSERT OR UPDATE trigger that correctly sets
template_previous_hid to whatever the previous history id for that group is.

The reason prefer this way of doing history is it's very hard to screw
up. The previous field always points to the prior record and the two
UNIQUE indexes enforce that it has to be unique. You will want a
function that get you the most recent history ID for a specific group_id
by walking down the chain (which you can do with a CTE).

Note that you can switch previous_hid to next_hid if you want. I
personally don't like that because it means you have to UPDATE the
previous record. I would rather make it so you can't actually update a
history record (since you shouldn't be able to rewrite history unless
you live in a George Orwell world...). The one upside to using next
instead of previous is it's trivial to find the most current record. But
if you're worried about the performance of that, I would just have the
trigger that sets previous_hid also update a template_current table that
is just template_group_id, template_current_history_id.

BTW, when I've actually done this for real I just used 'hid' everywhere
instead of 'history_id'.
--
Jim Nasby, Data Architect, Blue Treble Consulting
Data in Trouble? Get it in Treble! http://BlueTreble.com