1Transact SQL API

The following operations are available in the 1Transact SQL API:

Administration Operations

Operation SQL Script Description Arguments Assumptions
Activate table

SELECT sms_1transact.activate_table('test_table');

Prepares a table to be used by Version Manager.

IN: table_name

A table can be version-enabled at any time after Version Manager has been initialised in the database.
Deactivate table

SELECT sms_1transact.deactivate_table('test_table');

Removes a table from Version Manager control, so this table cannot take part in long transactions.

IN: table_name

A version can be version-disabled at any time after the database has been initialised.
Create view

SELECT sms_1transact.create_view('public.mytable')

Creates the view of the activated table that is used as a way to access the data.

This is intended to be used should there be a need to modify the table. DDL can be applied to the underlying _vm storage table and this function will recreate the required view on top of this table.

IN: tableName

Note: Must be a schema specified table name.

 

Management Operations

Operation SQL Script Description Arguments Assumptions
create_checkpoint

SELECT sms_1transact.create_checkpoint();

SELECT sms_1transact.create_checkpoint('my_new_checkpoint');

SELECT sms_1transact.create_checkpoint('my_new_checkpoint', 'master');

Creates a new checkpoint in the given version as a child from the terminal checkpoint in that version.

Note: Every version always has an 'open state' represented by a checkpoint in the checkpoints table. Creating a checkpoint actually names the current open checkpoint with the name provided and creates a new open checkpoint to hold any edits to the version after the checkpoint that has just been created.

IN: checkpoint name (defaults to null)

IN: version_name (defaults to current version)

OUT: ckpt_id

version_name exists
create_version

SELECT sms_1transact.create_version('new_version', 'parent_version');

SELECT sms_1transact.create_version('new_version', 'parent_version', 'My description of what this version is for');

Creates version version_name as a child of parent_version.

IN: version_name

IN: parent_version

IN: description (Defaults to null)

OUT: ckpt_id

version_name does not exist

parent_name exists
delete_version

SELECT sms_1transact.delete_version('childVersion');

Deletes a version and loses any data changes made in that version.

IN: version_name

The version has no children

Cannot delete master version
rollback_version

SELECT sms_1transact.rollback_version('version_name');

Remove all changes in this version to leave it in an 'as new' state.

IN: version_name

version_name exists

No child versions

Has a branch point to go back to (that is, not 'master')
rollback_to_checkpoint

SELECT sms_1transact.rollback_to_checkpoint('version_name', '3');

SELECT sms_1transact.rollback_to_checkpoint('version_name', 'myCheckpoint');

Rollback the changes in the version back to the identified checkpoint.

IN:version_name

IN:ckpt_id_or_name

Up to and including branch point

The rollback will not discard branch points of child versions
compress_checkpoints

SELECT sms_1transact.compress_checkpoints("5", "15");

SELECT sms_1transact.compress_checkpoints('theEarlyCheckpointDescription', 'theLaterDescription');

Copies all changes made into the path of checkpoints to compress into the upper_ckpt_id checkpoint. Removes a range of checkpoints.

Note this is a full scan of all activated tables.

If the range of checkpoints cannot be compressed a suitable exception will be raised to explain. This is useful to work out why a checkpoint cannot be compressed.

IN:lower_ckpt_id

IN:upper_ckpt_id

Lower limit inclusive (compressed)

Each checkpoint in the range has a single child (it is not a branch point) with the exception of upper_ckpt, which can have children.

Each checkpoint in the range does not have a version pointing to it with the exception of the upper_ckpt.

Each checkpoint in the range is not locked with the exception of the upper_ckpt.

Compressing 1,2,3,4,5,6 from 2 to 5 results in 1,5,6.
merge_version

SELECT sms_1transact.merge_version('version_name');

Merge version_name into its parent version.

The visible changes made to version_name since it's creation are applied to the end of the parent version. If there are conflicts with changes made to the parent since version_name was created then these are marked as conflicts and the merge is stopped. If there are no conflicts then the parent is updated to the successful merged checkpoint and version_name is removed.

Returns the new checkpoint ID.

The initial version of this requires that the parent is unmodified since the branch was created; this only works for a linear tree. For this release parallel side-branches are un-mergable.

IN: version_name

version_name has no branches
create_linear_difference_in_ checkpoints

SELECT sms_1transact.create_linear_difference_in_checkpoints('1','5', 'mytable', 'mydiffonetofive', 'diff_type');

SELECT sms_1transact.create_linear_difference_in_checkpoints('1', '80', 'mytable', 'mydiffonetoeighty');

SELECT sms_1transact.create_linear_difference_in_checkpoints('1', 'myCheckpoint', 'mytable', 'mydiffonetoeighty');

Creates a view of the differences between two checkpoints.

Created and modified rows will contain the row from the later checkpoint; deleted rows will contain the row from the earlier checkpoint.

IN: early_checkpoint

IN: later_checkpoint

IN: table_name

IN: view_name

IN: difference_column_name (default 'modification_state')

early_checkpoint will be on the path to later_checkpoint

table_name is an activated table

Data Operations

Operation SQL Script Description Arguments Assumptions
goto_version

SELECT sms_1transact.goto_version('other');

Sets the version-enabled tables to be in the state defined by version_name for the current session.

IN: version_name

 version exists
goto_checkpoint

SELECT sms_1transact.goto_checkpoint('3');

SELECT sms_1transact.goto_checkpoint('name');

Sets the version to checkpoint_id_or_name for the current session.

IN: checkpoint_id_or_name

  
get_current_version

SELECT sms_1transact.get_current_version();

Gets the name of the version that the current session is pointing to.

    
get_last_ckpt

SELECT sms_1transact. get_last_ckpt ();

Gets the ID of the last checkpoint in the current version.

    
checkpoint_name_to_id

SELECT sms_1transact. Checkpoint_name_to_id ('My Checkpoint);

Gets the ID of the named checkpoint as a shortcut to looking it up in the sms_1transact.checkpoint table.

IN: checkpoint_id_or_name

Checkpoint with that name exists