SQL Zone is brought to you in partnership with:

Rob Gordon is a seasoned Java developer and a big fan of open source. Oddjob is his own open source project to make job scheduling and task automation in Java much much easier. Rob is based in London. Rob is a DZone MVB and is not an employee of DZone and has posted 17 posts at DZone. You can read more from them at their website. View Full User Profile

SQL Scripts With Green Ticks

05.28.2012
| 2062 views |
  • submit to reddit

Running SQL install scripts can be tedious. They get so far, and then fail. Finding out where they failed isn't always easy and when the problem is fixed, there is a choice - drop the schema and re-run, or comment out what has been applied already, and re-run. I once spent an entire morning in this fix/re-run cycle for a particularly large and badly maintained script. It was very ungratifying work. What I need, I thought, are some friendly green ticks, some positive visual feedback to aid job satisfaction.

How do you get green ticks with a SQL script? Oddjob of course. Here's some green ticks to make you feel good about reading this blog:

Some Gree Ticks

Let me break it.

SQL Install Script Failed

We can see which table failed and we can see just the log messages for that job. Can you spot the typo? Once fixed we don't need to re-run the entire script - just reset the job that failed and run the root job again. Only jobs that haven't completed will run this time.

How can you do this at home? The script must be split into tables (if it isn't already) and then, either create a SQL Job for each table, or less obtrusively, use a For Each Job with files named so that they are in the correct order.

SQL Job Per Table

Creating an Oddjob SQL job for each table generates a lot of configuration:

<oddjob id="this">
  <job>
    <sequential>
      <jobs>
        <properties name="Load JDBC Properties From File">
          <input>
            <file file="${this.dir}/jdbc.properties" />
          </input>
        </properties>
        <variables id="dataSource">
          <connection>
            <connection driver="${jdbc.driver}" password="${jdbc.password}"
              url="${jdbc.url}" username="${jdbc.user}">
              <classLoader>
                <url-class-loader>
                  <files>
                    <files files="${this.dir}/*.jar" />
                  </files>
                </url-class-loader>
              </classLoader>
            </connection>
          </connection>
        </variables>
        <sql expandProperties="true" name="Create Schema">
          <connection>
            <value value="${dataSource.connection}" />
          </connection>
          <input>
            <file file="${this.dir}/schema.ddl" />
          </input>
        </sql>
        <sequential name="Create Tables">
          <jobs>
            <sql expandProperties="true" name="Create BUSINESS_DATE Table">
              <connection>
                <value value="${dataSource.connection}" />
              </connection>
              <input>
                <file file="${this.dir}/tables/BUSINESS_DATE.ddl" />
              </input>
            </sql>
            <sql expandProperties="true" name="Create HOLIDAY Table">
              <connection>
                <value value="${dataSource.connection}" />
              </connection>
              <input>
                <file file="${this.dir}/tables/HOLIDAY.ddl" />
              </input>
            </sql>
            <sql expandProperties="true" name="Create CURRENCY Table">
              <connection>
                <value value="${dataSource.connection}" />
              </connection>
              <input>
                <file file="${this.dir}/tables/CURRENCY.ddl" />
              </input>
            </sql>
            <sql expandProperties="true" name="Create ACCOUNT Table">
              <connection>
                <value value="${dataSource.connection}" />
              </connection>
              <input>
                <file file="${this.dir}/tables/ACCOUNT.ddl" />
              </input>
            </sql>
            <sql expandProperties="true" name="Create PRODUCT Table">
              <connection>
                <value value="${dataSource.connection}" />
              </connection>
              <input>
                <file file="${this.dir}/tables/PRODUCT.ddl" />
              </input>
            </sql>
            <sql expandProperties="true" name="Create TRADE Table">
              <connection>
                <value value="${dataSource.connection}" />
              </connection>
              <input>
                <file file="${this.dir}/tables/TRADE.ddl" />
              </input>
            </sql>
            <sql expandProperties="true" name="Create FEED_TRADE Table">
              <connection>
                <value value="${dataSource.connection}" />
              </connection>
              <input>
                <file file="${this.dir}/tables/FEED_TRADE.ddl" />
              </input>
            </sql>
          </jobs>
        </sequential>
        <folder name="Utils">
          <jobs>
            <sql expandProperties="true" name="Drop Schema">
              <connection>
                <value value="${dataSource.connection}" />
              </connection>
              <input>
                <file file="${this.dir}/drop.ddl" />
              </input>
            </sql>
          </jobs>
        </folder>
      </jobs>
    </sequential>
  </job>
</oddjob>

Which looks scary, but Oddjob's GUI designer makes managing it very easy. Adding a table is a simple copy and paste in the designer and then editing two fields.

Oddjob Designer

Let's go through the configuration job by job.

First we load the properties file:

<properties name="Load JDBC Properties From File">
  <input>
    <file file="${this.dir}/jdbc.properties" />
  </input>
</properties>

This loads a standard Java properties file:

myapp.db.schema=myapp

jdbc.driver=com.mysql.jdbc.Driver
jdbc.url=jdbc:mysql://rob-7
jdbc.user=rob
jdbc.password=secret

Then we use a Variable Job to define a connection:

<variables id="dataSource">
  <connection>
    <connection driver="${jdbc.driver}" password="${jdbc.password}"
      url="${jdbc.url}" username="${jdbc.user}">
      <classLoader>
        <url-class-loader>
          <files>
            <files files="${this.dir}/*.jar" />
          </files>
        </url-class-loader>
      </classLoader>
    </connection>
  </connection>
</variables>

This only defines the connection, the actual connection isn't created until each SQL job is configured, which is when they are run.

The class loader for the database driver is optional. An alternative would be to provide the class path on the command line with the -cp argument.

Creating a connection like this creates a connection per job. There isn't a data source job that shares a connection but creating one would be quite simple (In fact so simple that I'll include one in Oddjob 1.2).

Finally there is the SQL jobs. Here's just one.

<sql expandProperties="true" name="Create BUSINESS_DATE Table">
  <connection>
    <value value="${dataSource.connection}" />
  </connection>
  <input>
    <file file="${this.dir}/tables/BUSINESS_DATE.ddl" />
  </input>
</sql>

The expandProperties="true" causes Oddjob to replace properties in the BUSINESS_DATE.ddl file.

create table ${myapp.db.schema}.BUSINESS_DATE
(
	ID		bigint AUTO_INCREMENT,
	PREVIOUS	date,
	CURRENT		date,
	NEXT		date,
	primary key (ID)
);

So the ${myapp.db.schema} gets replaced with myapp as defined in the jdbc.properties file.

The final folder called 'Utils' keeps its contents separate from Oddjob's normal execution flow so that dropping the schema can only be done manually from the GUI.

The For Each Method

As mentioned before, This method relies on the scripts being named in the order they are to be applied. Like this:

Scripts Named For Order

The configuration becomes much simpler:

<oddjob id="this">
  <job>
    <sequential>
      <jobs>
        <properties name="Load JDBC Properties From File">
          <input>
            <file file="${this.dir}/jdbc.properties" />
          </input>
        </properties>
        <variables id="dataSource">
          <connection>
            <connection driver="${jdbc.driver}" password="${jdbc.password}"
              url="${jdbc.url}" username="${jdbc.user}">
              <classLoader>
                <url-class-loader>
                  <files>
                    <files files="${this.dir}/*.jar" />
                  </files>
                </url-class-loader>
              </classLoader>
            </connection>
          </connection>
        </variables>
        <sql expandProperties="true" name="Create Schema">
          <connection>
            <value value="${dataSource.connection}" />
          </connection>
          <input>
            <file file="${this.dir}/schema.ddl" />
          </input>
        </sql>
        <foreach file="${this.dir}/foreach-file.xml">
          <values>
            <files files="${this.dir}/tables/*" />
          </values>
        </foreach>
        <folder name="Utils">
          <jobs>
            <sql expandProperties="true" name="Drop Schema">
              <connection>
                <value value="${dataSource.connection}" />
              </connection>
              <input>
                <file file="${this.dir}/drop.ddl" />
              </input>
            </sql>
          </jobs>
        </folder>
      </jobs>
    </sequential>
  </job>
</oddjob>

The foreach job is on lines 32-36, it will run its configuration for each of the values it is given. In this case, the values are a list of the files matching the pattern ${this.dir}/tables/*. The configuration for the foreach is in the file foreach-file.xml which is:

<foreach id="files">
  <job>
    <sql expandProperties="true" name="Run ${files.current}">
      <connection>
        <value value="${dataSource.connection}" />
      </connection>
      <input>
        <file file="${files.current}" />
      </input>
    </sql>
  </job>
</foreach>

Here it is running:

SQL Install For Each File Method

No GUIs Allowed

If you need to run headless on a server then Oddjob can also run the script directly like this:

SQL Install From the Command Line

Note that I added an echo job with the text 'Install Complete' to the end of the Oddjob configuration. By default Oddjob only logs errors to the console, and I wanted some feel good feedback in the absence of my green ticks.

Source: http://rgordon.co.uk/blog/2012/04/18/sql-scripts-with-green-ticks/

Published at DZone with permission of Rob Gordon, author and DZone MVB.

(Note: Opinions expressed in this article and its replies are the opinions of their respective authors and not those of DZone, Inc.)

Tags:

Comments

Daniel Slazer replied on Tue, 2012/06/12 - 12:35pm

The key points are that a) A single object is created, b) It IS-A Parent and it IS-A Child, c) It has all the fields from both objects, and d) There is no waste unless the programmer introduced it by a flaw in his class hierarchy design.

Comment viewing options

Select your preferred way to display the comments and click "Save settings" to activate your changes.