1

I'd like to store a number in my Firebird database that indicates the revision of its structure. The number is supposed to be updated only when the structure has successfully been altered by some migration script. Therefore the storage mechanism needs to be transaction sensitive, which is why I cannot use a generator. The alternative I considered was to define a stored procedure which returns the number, and alter its definition in a transaction, but that as well seems to survive a rollback. What options do I have, not using a table?

  • DDL in Firebird is transactional, so you could just make the update of the structure version number part of your update script and COMMIT at the end. – Mark Rotteveel Mar 18 '13 at 8:30
4

I think you can use Database trigger which fires on TRANSACTION COMMIT. However, you need to react only to those commits caused by the "upgrade script" so you need to set up an flag to indicate it. For that you could set an context variable in USER_TRANSACTION namespace. Ie your script would look like this

RDB$SET_CONTEXT('USER_TRANSACTION', 'IncrementVersion', 1);
-- other statements
...
-- in the end of the script commit
COMMIT WORK;

Now the database trigger can check for the IncrementVersion context variable and act accordingly

CREATE TRIGGER IncrementDBVersion
ACTIVE ON TRANSACTION COMMIT
AS
BEGIN
  IF(RDB$GET_CONTEXT('USER_TRANSACTION', 'IncrementVersion') IS NOT NULL)THEN BEGIN
     -- increment the version number
     GEN_ID(GEN_DB_Version, 1);
     -- just in case clear the context variable
     RDB$SET_CONTEXT('USER_TRANSACTION', 'IncrementVersion', NULL);
  END
END

Here trigger uses generator (aka sequence) named "GEN_DB_Version" to "store" the version number.

If you use COMMIT WORK statements in your script you have to make the system more complicated, either set up additional flags to ignore those commits in the middle of the script or set the flag up right before final commit or perhaps some third way - it all depends on what "side effects" you want to use.

|improve this answer|||||
1

Try user context variables. You can access them through RDB$SET_CONTEXT, RDB$GET_CONTEXT functions. For example:

RDB$SET_CONTEXT('USER_TRANSACTION', 'SomeVar', 0)

will set SomeVar to 0.

There are two namespaces available: USER_TRANSACTION and USER_SESSION. I think their names say for itself.

|improve this answer|||||
  • I don't understand how you'd like me to apply this. The number I'm altering has to remain in the database. I just want to make sure that its value is only altered globally if the transaction in which I do that is committed. – Thijs van Dien Mar 17 '13 at 21:48

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy