Skip to content

HTTPS clone URL

Subversion checkout URL

You can clone with
or
.
Download ZIP

Fetching latest commit…

Cannot retrieve the latest commit at this time

..
Failed to load latest commit information.
README.table_version
bde_control_functions.sql
bde_control_tables.sql
bde_functions.sql
bde_roles.sql
bde_schema.sql
bde_schema_index.sql
bde_schema_index_lds.sql
lds_layer_functions.sql
lds_layer_tables.sql
patch_management.sql
patches.sql
table_version_functions.sql
table_version_tables.sql
version_bde_tables.sql

README.table_version

= Table Versioning

As part of linz_bde_loader there is a PostgreSQL table versioning system. This
is a basic revision system that records changes at the row level and does
not allow for branching. 

== Table Prerequisites

- The table must have a have a unique non-composite integer column
- The table must not be temporary

== How it works

When a table is versioned the original table data is left untouched and a new
revision table is created with all the same fields plus a "_revsion_created"
and "_revision_expired" field. A row level trigger is then setup on the original
table and whenever an insert, update and delete statement is run the change
is recorded in the table's revision data table. 

== Install

Make sure you are running a PostgreSQL data >= 9.0. Then run the following:

    psql mydb < table_version_tables.sql
    psql mydb < table_version_functions.sql

== How to Use

-- Data update example

Take the following example. We have a table 'bar' in schema 'foo':

    CREATE TABLE foo.bar (
        id INTEGER NOT NULL PRIMARY KEY,
        d1 TEXT
    );

    INSERT INTO foo.bar (id, d1) VALUES
    (1, 'foo bar 1'),
    (2, 'foo bar 2'),
    (3, 'foo bar 3');

To enable versioning on a table we need to run the following command:

    SELECT table_version.ver_enable_versioning('foo', 'bar');

After you have run this command a trigger 'table_version.foo_bar_revision()'
should have been created on the foo.bar table. Also the
"table_version.foo_bar_revision" table is created to store the revision
data. If you execute a select from the table you can see the base revision
data:

    SELECT * FROM table_version.foo_bar_revision;
    
     _revision_created | _revision_expired | id |    d1
    -------------------+-------------------+----+-----------
                  1001 |                   |  1 | foo bar 1
                  1001 |                   |  2 | foo bar 2
                  1001 |                   |  3 | foo bar 3
    (3 rows)
    

After the table has been versioned and you want to edit some data you
must first start a revision, do the edits and then complete the revision. i.e.:

    SELECT table_version.ver_create_revision('My test edit');

    -- now do some edits
    INSERT INTO foo.bar (id, d1) VALUES (4, 'foo bar 4');
    
    UPDATE foo.bar
    SET    d1 = 'foo bar 1 edit'
    WHERE  id = 1;
    
    DELETE FROM foo.bar
    WHERE id = 3;

    SELECT table_version.ver_complete_revision(); 


Now you should have some more edits in table_version.foo_bar_revision table:

    SELECT * FROM table_version.foo_bar_revision;

     _revision_created | _revision_expired | id |       d1
    -------------------+-------------------+----+----------------
                  1001 |                   |  2 | foo bar 2
                  1002 |                   |  4 | foo bar 4
                  1001 |              1002 |  1 | foo bar 1
                  1002 |                   |  1 | foo bar 1 edit
                  1001 |              1002 |  3 | foo bar 3
    (5 rows)

If we want to get the changed data from one revision to another (in this case
from 1001 to 1002) we run:

    SELECT * FROM table_version.ver_get_foo_bar_diff(1001, 1002);

     _diff_action | id |       d1
    --------------+----+----------------
     U            |  1 | foo bar 1 edit
     D            |  3 | foo bar 3
     I            |  4 | foo bar 4
    (3 rows)

As you can see the updates are recorded below. The '_diff_action' column
indicates the type of modification:

- 'U' = Update
- 'D' = Delete
- 'I' = Insert

If you would like to gain access to a snapshot of the data at a given time
then call the following function:

    SELECT * FROM table_version.ver_get_foo_bar_revision(1001);
    
     id |    d1
    ----+-----------
      2 | foo bar 2
      1 | foo bar 1
      3 | foo bar 3
    (3 rows)

Finally if you would like to remove versioning for the table call:

    SELECT table_version.ver_disable_versioning('foo', 'bar');


-- Creating a process for getting table differences

If you would like to maintain a copy of table data on a remote system this is
easily done with this revision system. First you need to determine which tables
are versioned:

    SELECT * FROM table_version.ver_get_versioned_tables();

     schema_name | table_name | key_column
    -------------+------------+------------
     foo         | bar        | id
    (1 row)
    
From here you need to determine which revisions you what to replicate to your
system:

    -- determine base revision for the table
    SELECT table_version.ver_get_table_base_revision('foo', 'bar');

     ver_get_table_base_revision
    -----------------------------
                             1001
    (1 row)

    -- determine all of the revisions have been applied to the table.
    SELECT
        id,
        revision_time
    FROM
        table_version.ver_get_revisions(
            ARRAY(
                SELECT generate_series(
                    table_version.ver_get_table_base_revision('foo', 'bar'),
                    table_version.ver_get_last_revision()
                )
            )
        )
    ORDER BY
       id ASC;

      id  |      revision_time
    ------+-------------------------
     1001 | 2011-03-11 16:14:49.062
     1002 | 2011-03-11 16:15:22.578
    (2 rows)

    -- Then get a snapshot of that base revision
    CREATE TEMP TABLE foo_bar_1001 AS
    SELECT * FROM table_version.ver_get_foo_bar_revision(
        table_version.ver_get_table_base_revision('foo', 'bar')
    );

    -- Select an incremental change set
    SELECT * FROM table_version.ver_get_foo_bar_diff(1001, 1002);
    
    -- Keep doing this processing by recording the last version you have
    -- locally and then call ver_get_table_last_revision and then
    -- ver_get_foo_bar_diff again. i.e
    SELECT * FROM table_version.ver_get_foo_bar_diff(
        my_last_revision,
        table_version.ver_get_table_last_revision('foo', 'bar')
    );
Something went wrong with that request. Please try again.