Thread: help with version checking

help with version checking

From
Arnau
Date:
Hi all!,
  I've got the following problem and I don't know how to solve it in 
PostgreSQL.
  I'd like to add a version checking to my db scripts. That is, I have 
the db creation scripts and the changes/upgrade script, and there is a 
table inside each db that holds the version of script executed. So 
before apply the upgrade script I'd like to check if the installed 
version matches the expected if not then show an error and terminate the 
script execution.
  In Oracle I used to do:

DEFINE PREVIOUS_VERSION = '2.3.5.10'

DECLARE        v_version varchar2(100);        v_ok number;
BEGIN        select version into v_version from version where id = 1;        if v_version <> '&PREVIOUS_VERSION' then
      RAISE_application_error(-20000,            'This script needs SMC version [' ||            '&PREVIOUS_VERSION' ||
']detected version is [' ||            v_version || ']' );        end if;
 
END;
/


I tried to do the following in PostgreSQL:

DECLARE  v_version VARCHAR;

BEGIN  SELECT version INTO v_version FROM version WHERE id = 1;
  IF v_version <> ''1.0.0.0'' THEN    RAISE EXCEPTION ''This script needs Agenda version 1.0.0.0, 
detected version %'', v_version;  END IF;

END;

//The upgrade stuff

but when I execute it, gives a lot of errors:

psql -d dermagier -f upgrade_agenda.sql
psql:upgrade_agenda.sql:2: ERROR:  syntax error at or near "VARCHAR" at 
character 21
psql:upgrade_agenda.sql:5: ERROR:  syntax error at or near "SELECT" at 
character 9
psql:upgrade_agenda.sql:8: ERROR:  syntax error at or near "IF" at 
character 3
psql:upgrade_agenda.sql:9: ERROR:  syntax error at or near "IF" at 
character 7
psql:upgrade_agenda.sql:11: WARNING:  there is no transaction in progress
COMMIT


Anybody knows how I can do this or which is the best way to do it?

Thank you very much
-- 
Arnau


Re: help with version checking

From
Arnau
Date:
Hi Daniel,

> You should define a PL/PGSQL function such as:
> 
> CREATE OR REPLACE FUNCTION check_version()
>   RETURNS void
> AS $$
> DECLARE
>   v_version VARCHAR;
> BEGIN
>   SELECT version INTO v_version FROM version WHERE id = 1;
>  
>   IF v_version <> '1.0.0.0' THEN
>     RAISE EXCEPTION 'This script needs Agenda version 1.0.0.0, detected
> version %', v_version;
>   END IF;
> END;
> $$ LANGUAGE PLPGSQL;
  I don't want, if it's possible, to create a function. I just want to 
check a value stored in a table and if doesn't match the expected one 
then abort the script execution.

-- 
Arnau


Re: help with version checking

From
Tom Lane
Date:
Arnau <arnaulist@andromeiberica.com> writes:
>    I don't want, if it's possible, to create a function.

Unlike Oracle, PG makes a strong distinction between SQL and
programmable languages (including plpgsql).  You can't write
plpgsql code without putting it into a function.
        regards, tom lane


Re: help with version checking

From
Arnau
Date:
Tom Lane wrote:
> Arnau <arnaulist@andromeiberica.com> writes:
>>    I don't want, if it's possible, to create a function.
> 
> Unlike Oracle, PG makes a strong distinction between SQL and
> programmable languages (including plpgsql).  You can't write
> plpgsql code without putting it into a function.
> 
>             regards, tom lane
> 

I've tried Daniel's suggestion but the Raise doesn't terminate the 
script execution, so if doesn't do what I need. Notice the Update 1

arebassa@beowulf:~$ psql -d dermagier -f upgrade_agenda.sql
CREATE FUNCTION
psql:upgrade_agenda.sql:16: ERROR:  This script needs Agenda version 
1.0.0.0, detected version 1.0.0.1
UPDATE 1


-- 
Arnau


Re: help with version checking

From
Arnau
Date:
Arnau wrote:
> Tom Lane wrote:
>> Arnau <arnaulist@andromeiberica.com> writes:
>>>    I don't want, if it's possible, to create a function.
>>
>> Unlike Oracle, PG makes a strong distinction between SQL and
>> programmable languages (including plpgsql).  You can't write
>> plpgsql code without putting it into a function.
>>
>>             regards, tom lane
>>
> 
> I've tried Daniel's suggestion but the Raise doesn't terminate the 
> script execution, so if doesn't do what I need. Notice the Update 1
> 
> arebassa@beowulf:~$ psql -d dermagier -f upgrade_agenda.sql
> CREATE FUNCTION
> psql:upgrade_agenda.sql:16: ERROR:  This script needs Agenda version 
> 1.0.0.0, detected version 1.0.0.1
> UPDATE 1
> 
> 

I paste the script I have created:

CREATE OR REPLACE FUNCTION check_version() RETURNS void
AS '
DECLARE  v_version VARCHAR;

BEGIN  SELECT version INTO v_version FROM agenda_version WHERE id = 1;
  IF v_version <> ''1.0.0.0'' THEN    RAISE EXCEPTION ''This script needs Agenda version 1.0.0.0, 
detected version %'', v_version;  END IF;

END;
' LANGUAGE 'plpgsql';

SELECT check_version();

UPDATE agenda_version set version = '1.0.0.1' where id = 1;



-- 
Arnau


Re: help with version checking

From
Chris Dunworth
Date:
Can you do the whole thing inside a transaction context (both the 
version check and the updates)? The exception should cause the 
transaction to bail out, and the updates won't proceed.

Thus:

BEGIN;
SELECT check_version();
UPDATE agenda_version set version = '1.0.0.1' where id = 1;
COMMIT;

I tried it with your script and it seemed to work for me.

Hope this helps...

-chris


Arnau wrote:
> Tom Lane wrote:
>> Arnau <arnaulist@andromeiberica.com> writes:
>>>    I don't want, if it's possible, to create a function.
>>
>> Unlike Oracle, PG makes a strong distinction between SQL and
>> programmable languages (including plpgsql).  You can't write
>> plpgsql code without putting it into a function.
>>
>>             regards, tom lane
>>
>
> I've tried Daniel's suggestion but the Raise doesn't terminate the 
> script execution, so if doesn't do what I need. Notice the Update 1
>
> arebassa@beowulf:~$ psql -d dermagier -f upgrade_agenda.sql
> CREATE FUNCTION
> psql:upgrade_agenda.sql:16: ERROR:  This script needs Agenda version 
> 1.0.0.0, detected version 1.0.0.1
> UPDATE 1
>
>


Re: help with version checking

From
Daniel CAUNE
Date:
> I tried to do the following in PostgreSQL:
> 
> DECLARE
>    v_version VARCHAR;
> 
> BEGIN
>    SELECT version INTO v_version FROM version WHERE id = 1;
> 
>    IF v_version <> ''1.0.0.0'' THEN
>      RAISE EXCEPTION ''This script needs Agenda version 1.0.0.0,
> detected version %'', v_version;
>    END IF;
> 
> END;
> 
> //The upgrade stuff
> 
> but when I execute it, gives a lot of errors:
> 
> psql -d dermagier -f upgrade_agenda.sql
> psql:upgrade_agenda.sql:2: ERROR:  syntax error at or near "VARCHAR" at
> character 21
> psql:upgrade_agenda.sql:5: ERROR:  syntax error at or near "SELECT" at
> character 9
> psql:upgrade_agenda.sql:8: ERROR:  syntax error at or near "IF" at
> character 3
> psql:upgrade_agenda.sql:9: ERROR:  syntax error at or near "IF" at
> character 7
> psql:upgrade_agenda.sql:11: WARNING:  there is no transaction in progress
> COMMIT
> 
> 
> Anybody knows how I can do this or which is the best way to do it?
> 

You should define a PL/PGSQL function such as:

CREATE OR REPLACE FUNCTION check_version() RETURNS void
AS $$
DECLARE v_version VARCHAR;
BEGIN SELECT version INTO v_version FROM version WHERE id = 1; IF v_version <> '1.0.0.0' THEN   RAISE EXCEPTION 'This
scriptneeds Agenda version 1.0.0.0, detected
 
version %', v_version; END IF;
END;
$$ LANGUAGE PLPGSQL;


Regards,

--
Daniel



Re: help with version checking

From
Karsten Hilbert
Date:
In GNUmed we have created a function
gm_concat_table_structure()

in
http://cvs.savannah.gnu.org/viewcvs/gnumed/gnumed/server/sql/gmSchemaRevisionViews.sql?rev=1.6&root=gnumed&view=log

which returns a reproducable, human-readable TEXT
concatenation of all the relevant parts of the schema.

We then do
select md5(gm_concat_table_structure());

and compare the output to known hashes for certain schema
versions. That way we don't simply "believe" what is in a
table "current_version" but rather actually *detect* (within
reasonable limits) the version.

It works well so far, no noticably delay even during client
startup (which does the check and complains on mismatches).
It may not scale particularly well to very large schemata,
possibly.

Karsten
-- 
GPG key ID E4071346 @ wwwkeys.pgp.net
E167 67FD A291 2BEA 73BD  4537 78B9 A9F9 E407 1346


Re: help with version checking

From
Arnau
Date:
Hi all,
  Thanks for all replies, taking into account all your suggestions and 
my google research I arrived to the next script. I'd like to know your 
opinion. Hopefully this will be useful for somebody else.



--------------------------------

--used to stop the script execution on any error
\set ON_ERROR_STOP 1

--disable the autocommit
\set AUTOCOMMIT off

BEGIN;
  /*    Helper function used to check the current version. If it isn't    the expected then raise an error an abort the
installation. */  CREATE OR REPLACE FUNCTION check_version() RETURNS void AS '    DECLARE      current_version VARCHAR;
    needed_version VARCHAR;
 
    BEGIN      --define the expected version      needed_version := ''1.0.0.0'';
      SELECT version INTO current_version FROM agenda_version WHERE id = 1;
      IF current_version <> needed_version THEN        RAISE EXCEPTION ''This script needs Agenda version %, detected 
version %'', needed_version, current_version;        RETURN;      END IF;
      RETURN;
    END;  ' LANGUAGE 'plpgsql';


  /*    Helper function used update the version to the current version.  */  CREATE OR REPLACE FUNCTION
update_version()RETURNS void AS'    DECLARE      current_version VARCHAR;
 
    BEGIN      current_version := ''1.0.0.1'';
      UPDATE agenda_version set version = current_version where id = 1;
      RETURN;    END;  ' LANGUAGE 'plpgsql';



  /*    The first action ALWAYS MUST BE SELECT check_version() to ensure    that the current version is the one needed
forthis changes script.  */  SELECT check_version();
 


  /*    All the actions that must be performed by the changes script  */


  /*    The last actions ALWAYS MUST BE:      SELECT update_version();      DROP FUNCTION check_version();      DROP
FUNCTIONupdate_version();
 
    to update the script version and remove the helper functions  */  SELECT update_version();  DROP FUNCTION
check_version(); DROP FUNCTION update_version();
 



--close the transaction
END;


-- 
Arnau


Re: help with version checking

From
Chris Dunworth
Date:
I'd probably make a small change to make this a little cleaner.

Specifically, change check_version() to take an argument, which is the 
needed version, and check this against the current value in 
agenda_version, throwing the exception if they don't match. Once you've 
written this, you'll never need to touch it again (no more DROP 
FUNCTIONs required).

Then, at the end of your update script, you update the version in the 
table via normal SQL (no need for a single-use function that does this).

With these tweaks, your update scripts could be simpler, like this:

BEGIN;
SELECT check_version('1.0.0.0');
-- Do all your updates etc. here --
UPDATE agenda_version SET version = '1.0.0.1' WHERE id =1;
COMMIT;

HTH. Good luck...

-chris


Arnau wrote:
> Hi all,
>
>   Thanks for all replies, taking into account all your suggestions and 
> my google research I arrived to the next script. I'd like to know your 
> opinion. Hopefully this will be useful for somebody else.
>
>
>
> --------------------------------
>
> --used to stop the script execution on any error
> \set ON_ERROR_STOP 1
>
> --disable the autocommit
> \set AUTOCOMMIT off
>
> BEGIN;
>
>   /*
>     Helper function used to check the current version. If it isn't
>     the expected then raise an error an abort the installation.
>   */
>   CREATE OR REPLACE FUNCTION check_version() RETURNS void AS '
>     DECLARE
>       current_version VARCHAR;
>       needed_version VARCHAR;
>
>     BEGIN
>       --define the expected version
>       needed_version := ''1.0.0.0'';
>
>       SELECT version INTO current_version FROM agenda_version WHERE id 
> = 1;
>
>       IF current_version <> needed_version THEN
>         RAISE EXCEPTION ''This script needs Agenda version %, detected 
> version %'', needed_version, current_version;
>         RETURN;
>       END IF;
>
>       RETURN;
>
>     END;
>   ' LANGUAGE 'plpgsql';
>
>
>
>   /*
>     Helper function used update the version to the current version.
>   */
>   CREATE OR REPLACE FUNCTION update_version() RETURNS void AS'
>     DECLARE
>       current_version VARCHAR;
>
>     BEGIN
>       current_version := ''1.0.0.1'';
>
>       UPDATE agenda_version set version = current_version where id = 1;
>
>       RETURN;
>     END;
>   ' LANGUAGE 'plpgsql';
>
>
>
>
>   /*
>     The first action ALWAYS MUST BE SELECT check_version() to ensure
>     that the current version is the one needed for this changes script.
>   */
>   SELECT check_version();
>
>
>
>   /*
>     All the actions that must be performed by the changes script
>   */
>
>
>
>   /*
>     The last actions ALWAYS MUST BE:
>       SELECT update_version();
>       DROP FUNCTION check_version();
>       DROP FUNCTION update_version();
>
>     to update the script version and remove the helper functions
>   */
>   SELECT update_version();
>   DROP FUNCTION check_version();
>   DROP FUNCTION update_version();
>
>
>
> --close the transaction
> END;
>
>