liquibase « Intelligrape Groovy & Grails Blogs

Posts Tagged ‘ liquibase ’

How to apply liquibase change set conditionally

Posted by on May 18th, 2011

In our recent grails project, we were about to move to production so we decided to manage the database changes with liquibase. So we generated the changelog from development env by executing the command “grails generate-changelog /home/uday/projects/projectName/grails-app/migrations/changelog.xml” which contained all the table creation changesets. We updated the dbCreate=’none’ in all env. When we ran the application in test env, having the database in create-drop mode, it worked, but in other envs, which had database in “update” mode, it started failing. Now we wanted some hook to execute the creation changeset only if the tables did not exist.
We just went through the liquibase site and we got the answer to our problem. The solution was in liquibase PRECONDITIONS. We added the preconditions to the changesets to check whether the table existed or not.

<changeSet author="uday (generated)" id="1305002274717-1">
    <preConditions onFail="MARK_RAN">
      <not>
        <tableExists tableName="account"/>
      </not>
    </preConditions>
    <createTable tableName="account">
      <column autoIncrement="true" name="id" type="BIGINT">
        <constraints nullable="false" primaryKey="true"/>
      </column>
      <column name="version" type="BIGINT">
        <constraints nullable="false"/>
      </column>
      <column name="balance" type="DECIMAL(19,2)">
        <constraints nullable="false"/>
      </column>
      <column name="date_created" type="DATETIME">
        <constraints nullable="false"/>
      </column>
      <column name="last_updated" type="DATETIME">
        <constraints nullable="false"/>
      </column>
      <column name="firstname" type="VARCHAR(255)"/>
    </createTable>
  </changeSet>

In the above example the changeset will execute only if the precondition passes, if the condition fails you can specify what needs to be done. In our case we mark it as run so that liquibase marks it as being executed. You can do other things on fail event like HALT, CONTINUE, WARN,MARK_RAN.
The documentation is quite good, you can find the examples of other changesets like constraints and indexes as well.

Hope it helps
Uday Pratap Singh
uday@intelligrape.com
https://twitter.com/meudaypratap
http://in.linkedin.com/in/meudaypratap

Tags: , , , ,
Posted in Database, Grails, Plugin
No Comments »

Grails Liquibase plugin: dbDiff tool workaround

Posted by on October 12th, 2010

The current project I am working on, is going through QA. At the same time development of the new features and bug fixing is on and we couldn’t afford to loose the test data. So synchronizing the state of the QA database with the development was becoming a pain. So we decided to use grails liquibase plugin.

I found one great article by Jackob Kulzer. As explained by Jackob Kulzer, dbDiff tool in the plugin is hard coded to compare development and test environment databases. The other option available was to write every changeset manually. But I was afraid, what if I forget to update even a single changeset.

I decided to write the script, which would overwrite my test database with the schema of the QA database. QA database was on another server. So I had to write multiple scripts to make it work. But once done it made my job really easy.

Following are the scripts to be created:

1. qaSchemaDump.sh (To be created on the development machine):


#SSH to the server and execute generateSchema shell script.
#Note: I had created password less login to the server.
ssh amit@qa.myserver.net /home/amit/Scripts/generateSchema.sh

#Copy QA Database schema to the development machine
scp apg@qa.myserver.net:/home/amit/Scripts/qaDBSchema.sql /home/amit/Scripts/liquibase/qaDBSchema.sql

#Import QA Database schema to test database using createTestDBFromQASchema.sql
mysql -u root --password=myPassword < /home/amit/Scripts/liquibase/createTestDBFromQASchema.sql
exit

2. generateSchema.sh (To be created on the Server):


/* Creates QA database schema */
mysqldump --no-data --tables -u root --password=myPassword myQADatabase > /home/amit/Scripts/qaDBSchema.sql

3. createTestDBFromQASchema.sql (To be created on the development machine):

/* drop, create and then use the test database.*/
drop database myTestDB; create database myTestDB;
use myTestDB;

/*Import the QA database schema to the newly created test database*/
source /home/amit/Scripts/liquibase/qaDBSchema.sql
exit

Once all the above mentioned scripts are created, we just need to run ‘qaSchemaDump.sh’ and followed by ‘grails dbDiff’ and the changeset would be available to us on the console. Then copy this changeset, append it to changeLog.xml and commit it to the repository.

With all this done, login to your QA/production server, take database backup and now we can run grails migrate-sql to confirm the sql that would be generated and then finally run ‘grails migrate’ to update QA/Production database.

Thanks to Jackob Kulzer and Nathan Voxland (author of the plugin)

Hope this helped!

~~Amit Jain~~
amit@intelligrape.com

http://www.IntelliGrape.com/

Tags: , , ,
Posted in Database, Grails
1 Comment »