Using Liquibase for Database Migrations in SAP Netweaver Cloud

Having written a web application for SAP Netweaver Cloud, most developers will very soon have the need to update their database schemas. Doing that manually is surely a possible way, but fun is not really the right word for this…

Luckily there are solutions out there, in this case even open source tools that can take that job. This blog shows as an example how to use Liquibase (http://www.liquibase.org/) within SAP Netweaver Cloud to migrate the database schema of an already deployed web application without data loss. The migration will happen automatically on redeployment of the application.

Prerequisites

To keep things simple we take the JDBC persistence tutorial as basis. The example is created here: https://help.netweaver.ondemand.com/default.htm?add_pers_jdbc.html. Create the web application, start it and add a few persons using the web UI to have an existing set of data that can be migrated later.

Download Liquibase from http://www.liquibase.org/download/. You can use the latest stable Liquibase Core version (currently 2.0.5) and unpack the archive to a folder of your local hard disk.

Preparing the Web Application to Use Liquibase

In order to have the migration of the schema happen automatically on deployment or redeployment of the application we use Liquibase’s ability to be started from a servlet listener. To enable that copy the liquibase.jar from the folder where you unpacked Liquibase to the WebContent/WEB-INF/lib folder of your dynamic web project:

To configure Liquibase you need to add the following lines to your application’s web.xml. Place them right behind the resource-ref tag:

  <context-param>
    <param-name>liquibase.changelog</param-name>
    <param-value>hello/db.changelog.xml</param-value>
  </context-param>
  <context-param>
    <param-name>liquibase.datasource</param-name>
    <param-value>java:comp/env/jdbc/DefaultDB</param-value>
  </context-param>
  <listener>
    <listener-class>liquibase.integration.servlet.LiquibaseServletListener</listener-class>
  </listener>

This configures Liquibase to use the given data source (the same as the rest of the application uses on SAP Netweaver Cloud) with the given database changelog file to perform any necessary migration steps on application startup. The last step is now to create the database changelog file in the hello package. Create a new file named db.changelog.xml there:

<?xml version="1.0" encoding="UTF-8"?>
<databaseChangeLog
  xmlns="http://www.liquibase.org/xml/ns/dbchangelog"
  xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
  xsi:schemaLocation="http://www.liquibase.org/xml/ns/dbchangelog
         http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-2.0.xsd">
</databaseChangeLog>

Redeploy the application to your server. Since there are no migration steps defined, Liquibase will simply do nothing for now. When you deploy the application to a local SAP Netweaver Cloud server, you will find some Liquibase log messages in the OSGI console. Browse through them to check that everything is set up correctly, they should look somewhat like this:

Updating file with path [C:\Development\persistence\Workspace\Servers\SAP NetWeaver Cloud at localhost-config\.\pickup\HelloWorld.war]
INFO 04.09.12 16:33:liquibase: Successfully acquired change log lock
INFO 04.09.12 16:33:liquibase: Creating database history table with name: DATABASECHANGELOG
INFO 04.09.12 16:33:liquibase: Reading from DATABASECHANGELOG
INFO 04.09.12 16:33:liquibase: Reading from DATABASECHANGELOG
INFO 04.09.12 16:33:liquibase: Successfully released change log lock
Update of file with path [C:\Development\persistence\Workspace\Servers\SAP NetWeaver Cloud at localhost-config\.\pickup\HelloWorld.war] is successful.
INFO 04.09.12 16:33:liquibase: Successfully acquired change log lock
INFO 04.09.12 16:33:liquibase: Reading from DATABASECHANGELOG
INFO 04.09.12 16:33:liquibase: Reading from DATABASECHANGELOG
INFO 04.09.12 16:33:liquibase: Successfully released change log lock

Doing a Migration

Now we can go forward and define a migration for our database schema. We will add a new column to our PERSON table that stores the nickname for the person. For this we modify the database changelog file like this:

<?xml version="1.0" encoding="UTF-8"?>
<databaseChangeLog
  xmlns="http://www.liquibase.org/xml/ns/dbchangelog"
  xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
  xsi:schemaLocation="http://www.liquibase.org/xml/ns/dbchangelog
         http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-2.0.xsd">
  <changeSet id="1" author="michael">
    <addColumn tableName="person">
      <column name="nickname" type="varchar(255)" />
    </addColumn>
  </changeSet>
</databaseChangeLog>

This defines one change set that will add the additional column to your table and defines the correct type for it. Now simply redeploy the web application to the cloud server and the database schema will be migrated automatically. Here’s the output Liquibase produces on the OSGI console when the application is deployed onto the local SAP Netweaver Cloud server:

osgi> Deploying file with path [C:\Development\persistence\Workspace\Servers\SAP NetWeaver Cloud at localhost-config\.\pickup\HelloWorld.war]
INFO 04.09.12 10:49:liquibase: Successfully acquired change log lock
INFO 04.09.12 10:49:liquibase: Creating database history table with name: DATABASECHANGELOG
INFO 04.09.12 10:49:liquibase: Reading from DATABASECHANGELOG
INFO 04.09.12 10:49:liquibase: Reading from DATABASECHANGELOG
INFO 04.09.12 10:49:liquibase: ChangeSet hello/db.changelog.xml::1::michael ran successfully in 16ms
INFO 04.09.12 10:49:liquibase: Successfully released change log lock
Deployment of file with path [C:\Development\persistence\Workspace\Servers\SAP NetWeaver Cloud at localhost-config\.\pickup\HelloWorld.war] is successful.

Finally, in order to really check that everything has been migrated, we change the tutorial coding to use the additional column as well. Following is the full source code of the classes we need to adapt (sorry for the lengthy listings, the actual modifications are rather minor):

Person.java

package hello.persistence;
public class Person {
       private String id;
       private String firstName;
       private String lastName;
       private String nickName;
       public String getId() {
             return id;
       }
       public void setId(String id) {
             this.id = id;
       }
       public String getFirstName() {
             return this.firstName;
       }
       public void setFirstName(String firstName) {
             this.firstName = firstName;
       }
       public String getLastName() {
             return this.lastName;
       }
       public void setLastName(String lastName) {
             this.lastName = lastName;
       }
       public String getNickName() {
             return this.nickName;
       }
       public void setNickName(String nickName) {
             this.nickName = nickName;
       }
}

PersonDAO.java

package hello.persistence;
import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import javax.sql.DataSource;
public class PersonDAO {
       private static final String TABLE_NAME = "PERSON";
       private static final String STMT_CREATE_TABLE = "CREATE TABLE "
                    + TABLE_NAME + " (ID INTEGER PRIMARY KEY, "
                    + "FIRSTNAME VARCHAR (255), LASTNAME VARCHAR (255))";
       private static final String STMT_SELECT_ALL = "SELECT ID, FIRSTNAME, LASTNAME, NICKNAME FROM "
                    + TABLE_NAME;
       private static final String STMT_INSERT = "INSERT INTO " + TABLE_NAME
                    + " (ID, FIRSTNAME, LASTNAME, NICKNAME) VALUES (?, ?, ?, ?)";
       private DataSource dataSource;
       public PersonDAO(DataSource dataSource) throws SQLException {
             setDataSource(dataSource);
       }
       public DataSource getDataSource() {
             return dataSource;
       }
       public void setDataSource(DataSource dataSource) throws SQLException {
             this.dataSource = dataSource;
             checkTable();
       }
       public void addPerson(Person person) throws SQLException {
             Connection conn = dataSource.getConnection();
             try {
                    PreparedStatement pstmt = conn.prepareStatement(STMT_INSERT);
                    pstmt.setString(1, person.getId());
                    pstmt.setString(2, person.getFirstName());
                    pstmt.setString(3, person.getLastName());
                    pstmt.setString(4, person.getNickName());
                    pstmt.executeUpdate();
             } finally {
                    if (conn != null) {
                           conn.close();
                    }
             }
       }
       public List<Person> selectAllPersons() throws SQLException {
             Connection conn = dataSource.getConnection();
             try {
                    PreparedStatement pstmt = conn.prepareStatement(STMT_SELECT_ALL);
                    ResultSet rs = pstmt.executeQuery();
                    ArrayList<Person> list = new ArrayList<Person>();
                    while (rs.next()) {
                           Person p = new Person();
                           p.setId(rs.getString(1));
                           p.setFirstName(rs.getString(2));
                           p.setLastName(rs.getString(3));
                           p.setNickName(rs.getString(4));
                           list.add(p);
                    }
                    return list;
             } finally {
                    if (conn != null) {
                           conn.close();
                    }
             }
       }
       private void checkTable() throws SQLException {
             Connection conn = null;
             try {
                    conn = dataSource.getConnection();
                    if (!existsTable(conn)) {
                           createTable(conn);
                    }
             } finally {
                    if (conn != null) {
                           conn.close();
                    }
             }
       }
       private boolean existsTable(Connection conn) throws SQLException {
             DatabaseMetaData meta = conn.getMetaData();
             ResultSet rs = meta.getTables(null, null, TABLE_NAME, null);
             while (rs.next()) {
                    String name = rs.getString("TABLE_NAME");
                    if (name.equals(TABLE_NAME)) {
                           return true;
                    }
             }
             return false;
       }
       private void createTable(Connection conn) throws SQLException {
             PreparedStatement pstmt = conn.prepareStatement(STMT_CREATE_TABLE);
             pstmt.executeUpdate();
       }
}

HelloWorldServlet.java

package hello;
import hello.persistence.Person;
import hello.persistence.PersonDAO;
import java.io.IOException;
import java.io.PrintWriter;
import java.sql.SQLException;
import java.util.List;
import javax.naming.InitialContext;
import javax.naming.NamingException;
import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import javax.sql.DataSource;
import com.sap.security.core.server.csi.IXSSEncoder;
import com.sap.security.core.server.csi.XSSEncoder;
public class HelloWorldServlet extends HttpServlet {
       private static final long serialVersionUID = 1L;
       private PersonDAO personDAO;
       protected void doGet(HttpServletRequest request,
                    HttpServletResponse response) throws ServletException, IOException {
             createResponse(response);
       }
       protected void doPost(HttpServletRequest request,
                    HttpServletResponse response) throws ServletException, IOException {
             try {
                    doAdd(request);
             } catch (SQLException e) {
                    throw new ServletException(e);
             }
             createResponse(response);
       }
       private void createResponse(HttpServletResponse response)
                    throws ServletException, IOException {
             response.setContentType("text/html");
             PrintWriter out = response.getWriter();
             out.write("<html><head></head><body>");
             List<Person> resultList;
             try {
                    resultList = personDAO.selectAllPersons();
             } catch (SQLException e) {
                    throw new ServletException(e);
             }
             out.write("<table border=\"1\"><tr><th colspan=\"4\">"
                           + (resultList.isEmpty() ? "" : resultList.size() + " ")
                           + "Entries in the Database</th></tr>");
             if (resultList.isEmpty()) {
                    out.write("<tr><td colspan=\"4\">Database is empty</td></tr>");
             } else {
                    out.write("<tr><th>First name</th><th>Last name</th><th>Nickname</th><th>Id</th></tr>");
             }
             IXSSEncoder xssEncoder = XSSEncoder.getInstance();
             for (Person p : resultList) {
               String nickName = p.getNickName();
               if (nickName == null) {
                nickName = " ";
               } else {
                nickName = xssEncoder.encodeHTML(p.getNickName()).toString();
               }
                    out.write("<tr><td>" + xssEncoder.encodeHTML(p.getFirstName())
                                  + "</td><td>" + xssEncoder.encodeHTML(p.getLastName())
                                  + "</td><td>" + nickName
                                  + "</td><td>" + p.getId() + "</td></tr>");             }
             out.write("</table>");
             out.write("<form action=\"\" method=\"post\">"
                           + " First name:<input type=\"text\" name=\"FirstName\">"
                           + " Last name:<input type=\"text\" name=\"LastName\">"
                           + " Nickname:<input type=\"text\" name=\"NickName\">"
                           + " Id:<input type=\"text\" name=\"Id\">"
                           + " <input type=\"submit\" value=\"Add Person\">"
                           + " </form>");
             out.write("</body></html>");
       }
       private void doAdd(HttpServletRequest request) throws ServletException,
                    IOException, SQLException {
             String firstName = request.getParameter("FirstName");
             String lastName = request.getParameter("LastName");
             String nickName = request.getParameter("NickName");
             String id = request.getParameter("Id");
             if (firstName != null && lastName != null && id != null
                           && !firstName.trim().isEmpty() && !lastName.trim().isEmpty()
                           && !nickName.trim().isEmpty() && !id.trim().isEmpty()) {
                    Person person = new Person();
                    person.setFirstName(firstName.trim());
                    person.setLastName(lastName.trim());
                    person.setNickName(nickName.trim());
                    person.setId(id.trim());
                    personDAO.addPerson(person);
             }
       }
       @Override
       public void init() throws ServletException {
             try {
                    InitialContext ctx = new InitialContext();
                    DataSource ds = (DataSource) ctx
                                  .lookup("java:comp/env/jdbc/DefaultDB");
                    personDAO = new PersonDAO(ds);
             } catch (SQLException e) {
                    throw new ServletException(e);
             } catch (NamingException e) {
                    throw new ServletException(e);
             }
       }
}

Again redeploy the web application and now there’s the additional table column appearing in the web UI. The person data you have added in the beginning has been preserved during the migration and the new column is available. After adding a new entry the UI looks like this.