How to use Liquibase to alter views on both Oracle and HSQL DBs, (replaceIfExists not supported on HSQLDB)?

General Discussion
1

We use Liquibase to manage our Oracle DB schema. We also use HSQL DB for integration tests. I’d like to use Liquibase to build the HSQL schema as well (it’s currently built programatically from java - so I’m using the Liquibase java API to trigger it in this instance).

We have several view changeSets, which are run towards the end of any liquibase migration whenever their contents are changed:

  2.      
  3.             SELECT …

Liquibase for HSQL DB doesn’t support replaceIfExists, so this approach fails.

I can see a few options, but they’re all ugly. Has anyone found a better approach?
  1. Drop each view if it exists, then create, on every migration. This works, but creates a huge amount of redundant SQL for even small changes - it breaks runOnChange.
  2. Use duplicate changeSets for HSQL DB and for Oracle - Oracle will use replaceIfExists and runOnChange, and HSQL DB will follow the first pattern - drop if exists, then create. This means duplicating our view logic.
  3. Go through the liquibase code and try to support replaceIfExists on HSQL DB - Liquibase could issue a drop view command, rather than relying on the underlying DB to handle it. Good, but I don’t have time!

[ note - also on Stack Overflow]

2

Found it! Use the modifySql element (whose documentation page isn't linked to in the manual!):


  1.    
            SELECT ...