SQL or Oracle Extension

General Discussion
1

Hello!

I’m new to Liquibase so forgive the naive question…

I’m using an Oracle database. That is unlikely to change any time soon. I’m trying to put together some Liquibase changeSets and a number of the tables use things like check constraints, triggers, etc.

Seems there are two ways to manage that using Liquibase. Firstly, I can use the SQL tag and include the Oracle specific SQL. This seems easiest and quickest (the SQL already exists) so that’ll be the course I take I suspect.

However, I appreciate there are benefits from using the Liquibase tags - deploying to a different RDBMS for example - so was wondering if I ought to be considering the Oracle extension instead? Seems to be doing that is pretty much just rewriting the existing SQL - as presumably those extensions do not support alternate RDBMSs so what would I gain by going through that process? Rollback maybe?

Does it matter much if I just wrap a load of existing SQL in Liquibase XML?

Your thoughts would be very welcome!

Thanks in advance.

Pete

2

Thanks Andreas - that is both reassuring and helpful!

3

Hi Pete,


yes, the benefit of using the Oracle extension is that, where possible, rollback actions are generated automatically. However, at our organization, we use a mix of Oracle-Tag and hand-written SQL nevertheless because we like our rollbacks to be more precise. An example is this:


… some changelog …

        <ora:dropCheck tableName=“our_table” constraintName=“some_constraint_name” />


       

            ALTER TABLE  our_table ADD

            CONSTRAINT some_constraint_name

            CHECK ( the_new_version_of_the_check_condition )

            ENABLE VALIDATE

       


       

            <ora:dropCheck tableName=" our_table " constraintName=" some_constraint_name"/>

               

               

                ALTER TABLE  our_table ADD

                CONSTRAINT some_constraint_name

                CHECK ( the_previous_version_of_the condition )

                ENABLE VALIDATE;

           

         

 

This way, in case of a schema rollback, we will get the exact definition of the previous constraint back. So, our strategy is this: 


  • Use the Oracle extensions where practical (faster once you have memorized the syntax, also more intuitive to our java developers)

  • Use hand-written SQL where you see fit.


So, a mix between Oracle Extensions and hand-written SQL works just fine as far as I am concerned.


Best regards,

Andreas

4

You’re welcome, but please keep in mind that I am a Liquibase-beginner, too :slight_smile: But so far, I can say that it works pretty good in our project. The only feature we are really missing is Liquibase being able to use different connections (=accounts) for different schemas.