CODEWITHSUNDEEP
spring-bootliquibase
abj1305
abj1305

Reputation: 665

Approach to have updating change sets in liquibase

I have a situation wherein a previous release I had 0 seeded values in a table1 using a liquibase script. My primary key was a sequence in java and the values started from 100000.

This went on for a couple of releases.

Now, in the subsequent release, there is a sudden need of seeding some (for an instance say 10) values into the table table1. As a date migration process, there might be some manually (using the application) inserted values in the table table1. For the primary keys not to conflict with the already present keys, I plan to start the IDs from 1 to 10 as the sequence starts from 10000 and will never have a value less than 100000.

I have an option of writing an alternate script using two approaches:

Approach 1 code is as follows:

- changeSet:
    id: ID-1
    author: demo
    preConditions:
      onFail: MARK_RAN
      sqlCheck:
        expectedResult: 0
        sql: select count(*) from ${schema}.table1 where column_1 >= 1 and column_1 <= 10;
    changes:
    - sql:
      dbms: PostgreSQL
      splitStatements: true
      stripComments: true
      sql: INSERT INTO ${schema}.table1 (column_1, column_2) VALUES(1, 'text1');
           INSERT INTO ${schema}.table1 (column_1, column_2) VALUES(2, 'text2');
        .....
           INSERT INTO ${schema}.table1 (column_1, column_2) VALUES(10, 'text10');

Approach 2 code is as follows:

- changeSet:
    id: ID-1
    author: demo
    preConditions:
      onFail: MARK_RAN
      sqlCheck:
        expectedResult: 0
        sql: select count(*) from ${schema}.table1 where column_1 = 1 
    changes:
    - sql:
      dbms: PostgreSQL
      splitStatements: true
      stripComments: true
      sql: INSERT INTO ${schema}.table1 (column_1, column_2) VALUES(1, 'text1');

- changeSet:
    id: ID-2
    author: demo
    preConditions:
      onFail: MARK_RAN
      sqlCheck:
        expectedResult: 0
        sql: select count(*) from ${schema}.table1 where column_1 = 2
    changes:
    - sql:
      dbms: PostgreSQL
      splitStatements: true
      stripComments: true
      sql: INSERT INTO ${schema}.table1 (column_1, column_2) VALUES(2, 'text2');

---- 

- changeSet:
    id: ID-10
    author: demo
    preConditions:
      onFail: MARK_RAN
      sqlCheck:
        expectedResult: 0
        sql: select count(*) from ${schema}.table1 where column_1 = 10
    changes:
    - sql:
      dbms: PostgreSQL
      splitStatements: true
      stripComments: true
      sql: INSERT INTO ${schema}.table1 (column_1, column_2) VALUES(10, 'text10');

I am of an opinion to use approach number 1 as there are lesser checks and but wanted to confirm first as the code change will directly go into the customer environment and future changes the above-mentioned logic will bring checksum errors.

Upvotes: 0

Views: 308

Answers (1)

htshame
htshame

Reputation: 7330

If you need all 10 rows of data or nothing, and if you are sure that select count(*) from ${schema}.table1 where column_1 >= 1 and column_1 <= 10; will indeed return 0, then go with the first approach.

Otherwise, I'd go with the second approach.

I wouldn't worry about checksum errors since you're supposed to make changeSet's ids unique.

Also, in my opinion, the approach, when normally the ID sequence starts from 10000 but in some edge cases (like this one) it starts from 1, is not a very good and reliable one.

Upvotes: 1

Related Questions