4.6 Script the upgrade and downgrade of a database

Learning Objectives

By the end of this lesson you will be able to:

Use this for subheaders

  1. Create a new target in database-integration.xml as shown below. Modify the database driver.
    <target name="update-database">
      <taskdef name="updateDatabase" classname="liquibase.ant.DatabaseUpdateTask" 
        classpathref="project.class.path" />
      <updateDatabase changeLogFile="database.changelog.xml"
        driver="org.apache.derby.jdbc.EmbeddedDriver"
        url="jdbc:derby:brewery"
        username=""
        password=""
        dropFirst="true"
        classpathref="project.class.path"/>
    
  2. Create a new file called database.changelog.xml and put it in the software directory.
    <?xml version="1.0" encoding="UTF-8"?>
    
    <databaseChangeLog xmlns="http://www.liquibase.org/xml/ns/dbchangelog/1.7"
      xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
      xsi:schemaLocation="http://www.liquibase.org/xml/ns/dbchangelog/1.7
             http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-1.7.xsd">
      <changeSet id="2" author="paul">
        <createTable tableName="brewer">
          <column name="id" type="int">
            <constraints primaryKey="true" nullable="false"/>
          </column>
          <column name="name" type="varchar(255)">
            <constraints nullable="false"/>
          </column>
          <column name="active" type="boolean" defaultValue="1"/>
        </createTable>
      </changeSet>
    </databaseChangeLog>
    		
  3. Be sure to include the new Ant target as a dependent target. Then, run the Ant build again. Go to MySQL to find the change.

Apply database changes (OPTIONAL)

  1. Change to the sample_app directory.
    cd ~/sample_app
  2. Rollback changes that were previously applied to a database.
    sudo rake db:rollback
  3. The example below is part of the sample application for Ruby on Rails Tutorial: Learn Rails by Example by Michael Hartl. You can also define the database schema in a script.
     
    class CreateUsers < ActiveRecord::Migration
      def self.up
        create_table :users do |t|
        t.string :name
        t.string :email
    
        t.timestamps
      end
    end
    
      def self.down
        drop_table :users
      end
    end
    			
  4. The example below is using a tool called Liquibase for database change management.
    <changeSet id="4" author="paul">
      <addColumn tableName="student">
        <column name="twitter" type="varchar(255)"/>
      </addColumn> 
    </changeSet>