Reg. Precondition to validate multiple DDL statements

Liquibase Development
1

Hi, I have a single sql script which has multiple DDL statement like,

  1. create sequence
  2. create table
  3. alter table

For the very first time when I execute the sql script through springboot (dbchangelog.yaml), it works well, when i run again with application restart, it fails as the objects are already exists.

I can understand “object exists” failure can be handled with precondition, But, how could i handle multiple objects exists situation with precondition (“or” clause)?

It would be great that if anyone can help to clarify this please?

2

I recommend putting each DDL statement in a separate changeset (which is best practice if you are using Oracle) within the changelog, where each DDL statement can have it’s own unique precondition.

1 Like
3

Thanks for the update, Do you like to keep like this?

databaseChangeLog:

  • changeSet:
    id: Create test Table
    author: mkp
    comment: Create New Table test
    preConditions:
    - onFail: MARK_RAN
    not:
    tableExists:
    tableName: test
    runAlways: true
    changes:
    • sqlFile:
      dbms: postgresql
      encoding: utf8
      path: changelog/ddl/test-create-table.sql
1 Like
4

And, I have 100 plus mix of DDL statements, Is there a way that we can group new tables creation in single sql file with precondition to validate the table exists or not with any OR condition ?
Since i am using YAML format, Is there any sample precondition to review?

I am new to liquibase, it would be great if you could help.

Thanks again for replying,

5

when i tried like this, it fails, with 2nd run (when the tables are already exists)

databaseChangeLog:

  • changeSet:
    id: newtables_1.0
    author: postgres
    comment: newtables_1.0
    preConditions:
    - or:
    - onFail: MARK_RAN
    not:
    tableExists:
    tableName: table1
    - onFail: MARK_RAN
    not:
    tableExists:
    tableName: table2
    runAlways: true
    changes:
    • sqlFile:
      dbms: postgresql
      encoding: utf8
      path: changelog/ddl/newtables.sql
      relativeToChangelogFile: true

Error:

Reason: 
      /db/changelog/db.changelog-master.yaml : Not precondition failed
      /db/changelog/db.changelog-master.yaml : Not precondition failed

Please help !

6

I think you need to determine why Liquibase is running again and not finding that the changesets were already executed and recorded in the databasechangelog table.

7

Thanks for the reply, Daryl.
We wanted to validate/ verify few mandatory database objects (tables, sequences, constraints) which are required to make sure the application functioning with no issue. If any of those objects are not available or missing while bringing up , then want to recreate them on the fly through liquibase.

8

Why don’t you test the table/sequence existence inside the sql, before creating them?

For instance, you could do this:

declare
c int;
begin
select count(*) into c from user_tables where table_name = upper(‘<my_table_name>’);
if c = 0 then
execute immediate ‘create table <my_table_name> (…)’;
end if;
end;

9

That works also, but it’s cumbersome to wrap every DDL statement in an PL/SQL block.

10

Thank you, Mony, Daryldoak.
Just wondering, if validating the db objects with precondition set is not appropriate !?