09
Feb
12

ALTERing Database Schema Using DDLUtils


This page describes how to use DDLUtils to only prints out alter statements instead of making the changes automatically.

Requirements

Background

My previous article described how to use DDLUtils to automatically make changes to a database. Since we were using an in-memory/temporary database, it wouldn’t be a big deal if DDLUtils made a mistake.

Shortcut: If you have not implemented the “In memory database” in the requirements section and you have your own database. Then you can skip to the short cut section below. the return here.

DDLUtils has functionality to fetch the current DDL and dump it into an XML file. Once we modify the xml we can have DDLUtils compare the xml against the current database and only print “ALTER” statements to the screen. We can take those alter statements and manually run them against the database.

Create the Java Code

The following class is designed to be run from the command line. You can wrap it into a shell script. Take some time to review the java source below. It is pretty much self explanatory. Please be sure to name your xml file different from the prior project. In the example below its called “ddl-draft.xml”.

vi src/main/java/com/test/DDLUtils.java

package com.test;

import java.io.InputStreamReader;
import java.io.StringWriter;

import javax.sql.DataSource;

import org.apache.ddlutils.Platform;
import org.apache.ddlutils.PlatformFactory;
import org.apache.ddlutils.io.DatabaseIO;
import org.apache.ddlutils.model.Database;
import org.springframework.context.ApplicationContext;
import org.springframework.context.support.ClassPathXmlApplicationContext;

/**
 * <p>
 * Dumps or generates schema information for the batch project. This class is
 * only designed to output information. Although the framework is capabile, It
 * should not make any alterations to the database since automated alterations
 * could be risky.
 * </p>
 * 
 * <p>
 * The Dump command takes the existing database schema and creates an xml file.
 * You can choose to redirect this output to a file so you can modify the
 * schema.
 * </p>
 * 
 * <p>
 * The alter command takes the xml located in the /ddl.xml file and generates
 * alter statements to standard output. You can redirect this to a file and use
 * it to alter the database manually.
 * </p>
 * 
 * <p>
 * mvn -q exec:java -Dexec.mainClass=com.test.DDLUtils -Dexec.args="dump"
 * </p> language
 * <p>
 * mvn -q exec:java -Dexec.mainClass=com.test.DDLUtils -Dexec.args="alter"
 * </p>
 * 
 */
public class DDLUtils {
	private DataSource dataSource;

	public DDLUtils() {
		super();
		ApplicationContext context = new 
		ClassPathXmlApplicationContext("com/test/app-config.xml");
		dataSource = (DataSource) context.getBean("dataSource");		
	}
	
	public static void main(String args[]) {
		(new DDLUtils()).contextInitialized(args);
	}
	
    public void contextInitialized(String args[]) {
        Platform platform = PlatformFactory
                .createNewPlatformInstance(dataSource);
 
        Database database = new DatabaseIO().read(new InputStreamReader(
                getClass().getResourceAsStream("/ddl-draft.xml")));
        if(args.length < 1) {
        	usage();
        	return;
        }
        if("dump".equals(args[0])) {
    		StringWriter writer = new StringWriter();
    		new DatabaseIO().write(platform.readModelFromDatabase("batch"), writer);
    		System.out.println(writer.toString());        	
        } else if("alter".equals(args[0])) {
        	System.out.println(platform.getAlterTablesSql(database));
        } else {
        	usage();
        	return;
        }
    }

	private void usage() {
    	System.out.println("usage: ");
    	System.out.println("\t\t"+getClass().getName()+" (dump|alter)\n\n");    
	}
    
}

Testing the Application

The following command takes the current schema and creates an xml file to standard output.

mvn -q exec:java -Dexec.mainClass=com.test.DDLUtils -Dexec.args="dump"

The following prints to standard out the DDL that will get the current database to resemble what is documented in the xml file.

mvn -q exec:java -Dexec.mainClass=com.test.DDLUtils -Dexec.args="alter"

Shortcut

Read this section if you already have a database and simply want to use DDL utils to query the schema or help modify it.

Step 1: Create a Maven Project with all the necessary dependencies listed below + the JDBC driver specific to your database.
mkdir ddlutil
cd ddlutil
vi pom.xml

<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
  xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/maven-v4_0_0.xsd">
  <modelVersion>4.0.0</modelVersion>
  <groupId>com.test</groupId>
  <artifactId>ddlutil</artifactId>
  <packaging>jar</packaging>
  <version>1.0-SNAPSHOT</version>
  <name>ddlutil Maven Webapp</name>
  <url>http://maven.apache.org</url>
  <dependencies>
    <dependency>
      <groupId>junit</groupId>
      <artifactId>junit</artifactId>
      <version>3.8.1</version>
      <scope>test</scope>
    </dependency>
    <dependency>
		<groupId>org.apache.ddlutils</groupId>
		<artifactId>ddlutils</artifactId>
		<version>1.0</version>    
    </dependency>
		<dependency>
			<groupId>org.springframework</groupId>
			<artifactId>spring</artifactId>
			<version>2.5.6</version>
		</dependency>                
  </dependencies>
  <build>
    <finalName>ddlutil</finalName>
    
		<plugins>
			<plugin>
				<groupId>org.apache.maven.plugins</groupId>
				<artifactId>maven-compiler-plugin</artifactId>
				<version>2.0.2</version>
				<configuration>
					<source>1.5</source>
					<target>1.5</target>
				</configuration>
			</plugin>
		</plugins>    
  </build>
</project>

Step 2: Create the spring configuration file with the information necessary to connect to your datasource.

src/main/resources/applicationContext.xml

<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans"
        xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
        xmlns:context="http://www.springframework.org/schema/context"
        xsi:schemaLocation="http://www.springframework.org/schema/beans
  
http://www.springframework.org/schema/beans/spring-beans-2.5.xsd
  
http://www.springframework.org/schema/context
  
http://www.springframework.org/schema/context/spring-context-2.5.xsd">
  
<context:annotation-config/>
<context:component-scan base-package="com.test"/>
<bean name="dataSource" class="org.springframework.jdbc.datasource.DriverManagerDataSource">
        <property name="driverClassName" value="xxxreplace with your database driverxxx"/>
        <property name="url" value="xxxreplace with your database urlxxx"/>
        <property name="username" value="xxx_username_xxx"/>
        <property name="password" value="xxx"/>
</bean>
</beans>

See the following URL to setup data connectivity using spring and help fill in the values above.

Step 3: Create sample DDL and return the the Background section on this page.

src/main/resources/ddl.xml

<?xml version="1.0"?>
<!DOCTYPE database SYSTEM "http://db.apache.org/torque/dtd/database.dtd">
<database name="testdb">
  <table name="author">
    <column name="author_id"
            type="INTEGER"
            primaryKey="true"
            required="true"/>
    <column name="name"
            type="VARCHAR"
            size="50"
            required="true"/>
    <column name="organization"
            type="VARCHAR"
            size="50"
            required="false"/>
  </table>

  <table name="book">
    <column name="book_id"
            type="INTEGER"
            required="true"
            primaryKey="true"
            autoIncrement="true"/>
    <column name="isbn"
            type="VARCHAR"
            size="15"
            required="true"/>
    <column name="author_id"
            type="INTEGER"
            required="true"/>
    <column name="title"
            type="VARCHAR"
            size="255"
            required="true"/>

    <foreign-key foreignTable="author">
      <reference local="author_id" foreign="author_id"/>
    </foreign-key>  

    <index name="book_isbn">
      <index-column name="isbn"/>
    </index>
  </table>
</database>

References

Advertisements

1 Response to “ALTERing Database Schema Using DDLUtils”



Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s


Enter your email address to subscribe to this blog and receive notifications of new posts by email.

Join 77 other followers

February 2012
S M T W T F S
« Jan   Apr »
 1234
567891011
12131415161718
19202122232425
26272829  

Blog Stats

  • 846,580 hits

%d bloggers like this: