Drop and recreate table if changeset of create table changes

General Discussion
1

I am facing scenario, I have executed a changeset to create table and now did a change on create table. I want my changeset to drop table if exists and re create it. Is this possible with XML format in liquibase.


       

       

       

       

            create table1 as select col1,col2,col3 from table2 where id in (1,2,3,4,5);

     

2

The easiest would use the ā€œcreate or replace tableā€ syntax if your database supports it, like:


Nathan

3

Hi! Is there a modern equivalent of this please? Iā€™m guessing this old answer from 2013 was using raw SQL, itā€™s hard to tell as the forum seems to have swallowed the XML markup.

Iā€™m quite new to the runOnChange attribute of changeSet, and the replaceIfExists attribute on createProcedure, so I may veer off track here :slight_smile:

Iā€™ve got a changeset XML file that wants to create a table, create a stored procedure that will populate that table, then run the stored procedure. Unfortunately the createTable step fails the second time around because the table already exists.

I can mark the changeset as runOnChange="true", and the createProcedure as replaceIfExists="true", but I cannot use replaceIfExists with createTable. I also donā€™t see a way to use dropTable only if the table already exists. It seems these features ought to be on the core createTable command, similar to SQLā€™s CREATE OR REPLACE TABLE foo....

Am I missing something obvious? Iā€™ve Googled around but there doesnā€™t seem to be a good answer here. I imagine the best solution may be to split the changeset into two, one which creates the table, and one which runs the createProcedure replaceIfExists command, but if the table definition needs to change too then the procedure would fail when it is re-run.

4

I donā€™t know what DBMS platform you are using, but here are a couple of tips:

  1. runOnChange should only be used for ā€œrepeatableā€ statements, like CREATE OR REPLACE, never for a ā€œregularā€ CREATE.

  2. Each Changeset should only contain 1 DDL change. This prevents the issue you are having with with runOnChange.

Here is an example:

<changeSet id="create_test_employee_table" author="bob">
  <sql>
    CREATE TABLE test_employee (
    employeeNumber  VARCHAR2(15) NOT NULL,
    username        VARCHAR2(30));
  </sql>
  <rollback>
    DROP TABLE test_employee;
  </rollback>  
</changeSet>

<changeSet id="create_procedure" author="bob" runOnChange="true">
  <comment>Create the test_proc stored procedure</comment> 
  <sql endDelimiter="/">
    create or replace procedure test_proc is
      v_dummy1 date;
      v_dummy2 date;
    begin
      select sysdate into v_dummy1 from test_employee;
      select sysdate-1 into v_dummy2 from test_employee;
    end test_proc;
    /  
  </sql>
  <rollback>
    DROP PROCEDURE test_proc;
  </rollback>
</changeSet>

<changeSet id="execute_procedure" author="bob" runAlways="true">
  <sql endDelimiter="/">
    BEGIN
      test_proc();
    END;
    /  
  </sql>
  <rollback/>
</changeSet>

If you need to recreate the table with a different structure, you have 2 choices:

  1. Add new changeset with the required changes.
  2. Rollback the create table changeset, modify the changeset, run the changeset again.

Hopefully that helps.

5

Thank you Daryl,

Thank you very much for the detailed response, I really appreciate your
time.

Ah yes, splitting one XML changeLog file into multiple changeSets has
always seemed weird to me, because Iā€™m logically rolling out ā€œoneā€
feature to our site, I add the single XML file to the changelog.xml file
and issue a single ā€˜applyā€™ command to run all the changesets that are
outstanding, then if I want to rollback Iā€™d like to logically rollback
just ā€œoneā€ feature. If I split a single XML file into three changeSets
as you (probably rightly) suggest, then if I need to rollback the entire
feature I need to dig into it and be damn careful how many changesets I
give to the rollback command, or I might rollback a changeSet from a
dusty old feature I forgot about.

I suppose this is par for the course, and of course Iā€™m just one
developer using one of the many different use cases for Liquidbase.

From what you say, there is no ā€˜CREATE OR REPLACEā€™ operation for tables
(Iā€™m on MariaDB by the way) so Iā€™ll stick with a single DDL changeSet
with a create and rollback-drop workflow, as you suggest.

Thanks!
Nick

1 Like