1

Is there a portable way or best practice to access a version number for a schema at runtime?

Rationale: you have multiple systems (development, QA, etc.) where you push schema changes from one system to the next. Say you're not sure if you already have the latest schema on the QA system, how can you find out the version of the current schema (preferably automated with a script)?

I could imagine having

  • a convention on using a table comment (COMMENT ON TABLE with Oracle, is that a standard?) to version every table
  • a special table in the schema storing version information

How do you do this? Is there a best practice?

pesche
  • 111
  • 2
  • We use Liquibase for this. –  Oct 31 '14 at 10:19
  • @a_horse_with_no_name How does Liquibase do it, has it a table of its own per schema? Do you then query this table directly or has Liquibase an API? – pesche Oct 31 '14 at 15:02
  • Liquibase manages that in its own table. You just run the liquibase script and it manages everything. Do read the information on their website. It's all explained there –  Oct 31 '14 at 15:19
  • @a_horse_with_no_name The Liquibase website is big... How would you check if a schema is up to date with respect to an other system? Use liquibase diffand expect it to be empty? Or use liquibase status and expect no unrun change sets? – pesche Oct 31 '14 at 15:46
  • 1
    You don't do "diffs" with such a tool. You just run the script and it will update the schema as necessary. Doing schema management through "diffs" is the wrong approach. –  Oct 31 '14 at 16:24
  • @a_horse_with_no_name I don't want to run the script, I'd just like to know if the script has been run already. I don't want to risk to modify the current schema, I'd like just to know which state/version the schema is at. – pesche Nov 03 '14 at 13:31
  • You can ask Liquibase to show the statements that would be executed if you did run the script. You can also just look at the meta table from Liquibase to determine what the last changeset was that got applied. –  Nov 03 '14 at 13:38

0 Answers0