Software Hero Worship

Checking Foreign Key attribute consistency

2011-12-27T22:45:00.000-08:00

While evolving a schema during development, it's sometimes hard to make sure that column attributes remain consistent across different tables.

Some databases provide domain types. So for example, with Firebird/InterBase you can define a domain type or alias for your invoice_number column as, say, VARCHAR(10). Then you can use the domain type in defining any tables containing invoice_number, and the column attributes will always be consistent.

Oracle doesn't have a natural way to do this. One trick I've used is preprocessing SQL DDL files with Ant and using "macros" for column types. So I might have a types.properties file like this:

...
INVOICE_NUMBER_TYPE = VARCHAR2(10)
...

Then in create_table_invoice.sql I might have:

CREATE TABLE invoice (
  invoice_number @INVOICE_NUMBER_TYPE@ NOT NULL,
  ...
);

And also use the macro elsewhere for any foreign key column.

But, this turned out to be a bit of a pain. For one thing, it makes the build more convoluted because of the preprocessing required. More importantly, it made the SQL DDL files "invalid". We couldn't just run one in sqlplus, without the preprocessing step first. We couldn't send one to a DBA. We didn't get good IDE support, because the IDE doesn't understand the type macros.

As a result, I've reverted to putting hardcoded column attributes in the DDL files. But this takes me back to the problem of keeping the foreign keys parent/child attributes consistent.

Another approach to that problem is to use a view like this:

CREATE OR REPLACE VIEW chk_foreign_key_type AS
SELECT
  ac.table_name child_table,
  acc.column_name child_column,
  atc.data_type child_data_type,
  atc.data_length child_data_length,
  atc.data_scale child_data_scale,
  ac2.table_name parent_table,
  acc2.column_name parent_column,
  atc2.data_type parent_data_type,
  atc2.data_length parent_data_length,
  atc2.data_scale parent_data_scale
FROM   all_constraints ac
       JOIN all_cons_columns acc ON acc.owner = ac.owner 
            AND acc.constraint_name = ac.constraint_name
       JOIN all_tab_columns atc ON atc.owner = ac.owner 
            AND atc.table_name = acc.table_name 
            AND atc.column_name = acc.column_name
       JOIN all_constraints ac2 ON ac2.owner = ac.owner 
            AND ac2.constraint_name = ac.r_constraint_name
       JOIN all_cons_columns acc2 ON acc2.owner = ac2.owner 
            AND acc2.constraint_name = ac2.constraint_name 
            AND acc2.position = acc.position
       JOIN all_tab_columns atc2 ON atc2.owner = acc2.owner 
            AND atc2.table_name = acc2.table_name 
            AND atc2.column_name = acc2.column_name
WHERE  ac.owner = 'your_schema'
  AND  ac.constraint_type = 'R'
  AND  (atc2.data_type <> atc.data_type
        OR atc2.data_length <> atc.data_length
        OR NVL(atc2.data_scale, -1) <> NVL(atc2.data_scale, -1))
ORDER BY 1, 2
/

COMMENT ON TABLE chk_foreign_key_type IS
  'Foreign key column(s) different from parent type/length/scale'
/

This view will return any foreign key where a data type, length or scale of a column in the child table does not match the corresponding column in the parent table.

Ant Target Dependency Graph

2011-02-08T22:58:00.000-08:00

We can get a pretty good diagram of our Ant target dependencies very easily using an embedded Groovy script and GraphViz.

Add this to build.xml:


    
    
      def u(x) {x.toString().replace("-", "_").replace(".", "_")}
      new File("build.dot").text = """
digraph ant {
  ${project.targets.values().collect {target ->
    target.dependencies.collect {dep ->
      u(dep) + " -> " + u(target)
    }.join("\n")
  }.join("\n")}
}
"""

You will need to have the embeddable groovy-all.jar in Ant's classpath, e.g. in ~/.ant/lib/.

Then run "ant target-graph". It writes a build.dot file in the current directory.

Convert this into a picture using the GraphViz dot command:

dot -Tsvg -O build.dot

The results are surprisingly good.

Here's an example from the Apache commons-dbcp project:

I'm not going to show you the diagram I got for our system at work, the reason I wrote this script! The diagram is so big and complex, I was shocked. After being happy with Ant all these years, I think it's time to be getting serious about Gradle.

SlickEdit 2011 Wish List

2011-01-22T12:35:00.000-08:00

Well, we're nearly through January 2011, and the SlickEdit 2011 beta is due any day now.

I've been using SlickEdit since 1996, and I still get kind of excited around this time of year when a new version comes out. Sometimes the team surprises me.

Here is my wish list for features in SlickEdit 2011:

Groovy Language Support

Scala Language Support

Clojure Language Support

Git DVCS Support

Mercurial DVCS Support

Bazaar DVCS Support

Useful Subversion VCS Support

Useful "subword" features

The first three are hot JVM languages that I'd love to see support for. I don't really expect to see them, but you never know. Last year, SlickEdit added support for Erlang, Haskell and F#, so they aren't completely in the dark about hot languages.

The next three items are popular distributed version control systems. Again, I don't really expect much from SlickEdit on that, yet. Forum posts on the topic have met with disappointing reponses from SlickEdit staff -- doesn't look like they "clicked" on DVCS yet. It took them an awful long time to move on from CVS to Subversion themselves, and even now the Subversion support is miserable compared to any other tool I've used. And so, my wish, modernise the Subversion support.

SlickEdit has a lot of advanced features for C/C++ programmers. C/C++ programmers probably make up a very large chunk, if not the majority, of SlickEdit users. And as far as I can tell, SlickEdit is actually one of the best "IDE"s available for C/C++. I don't do C/C++ any more myself though, I do JVM-based languages mostly. And with Java, SlickEdit also tries to be an uber-IDE, with Project Types, JUnit, Ant support and more. But here it falls far, far short of industry standards. Java programmers are really spoiled by the superb IDEs aavailable for them, and two of the best ones are even free. Anyway, I don't wish for SlickEdit to improve its Java IDE features. I'm happy to use a Java IDE for that kind of work. The point I'd like to make is that supporting current VCS systems, and supporting them really well, would benefit all SlickEdit users. I can't imagine many SlickEdit users are not using VCS, and many of them probably use a modern VCS, such as Git. It's really about time SlickEdit caught up with the VCS game.

For a couple of examples of excellent VCS integration, look at:

IntelliJ IDEA and related products from JetBrains

SmartSVN, SmartGit and even SmartCVS from Syntevo

SlickEdit 2011 included some rather dubious new features. My favorite "non useful feature" was Subword Navigation. You can move the cursor through camel-cased words such as AbstractBeanFactory. But when would anyone want to do that? Far more useful would be file or class completion/loading using smart "camel typing", as introduced by IntelliJ IDEA and copied by other tools. With IDEA, I can press Ctrl+N to open a class, then type "ABF" or "AbBeFa" to open the AbstractBeanFactory class. This is really useful, and would be something SlickEdit could really benefit from.

Anyway, I'm sure SlickEdit 2011 will contain a few pleasant surprises, as well as a few new annoying bugs. As always, it will be interesting to figure whether the feature-to-bug ratio improves, or not. I'm looking forward to the beta.

Groovy DSL/Builders: ZIP Output Streams

2011-01-06T18:04:00.000-08:00

Let's follow up last week's post with another example of a very similar, very simple builder.

This one is for outputting ZIPped data to a stream. Let's take the standard example of using Java's ZIP support to zip up a folder of files.

Because the JDK does not include methods to traverse the filesystem, we need to define a method to be called recursively for subdirectories:


  private void zipDirectory(File dir, ZipOutputStream zos) throws IOException {
    for (File file : dir.listFiles()) {
      if (file.isDirectory()) {
        zipDirectory(file, zos);
      }
      else {
        ZipEntry entry = new ZipEntry(file.getPath());
        entry.setSize(file.length());
        entry.setTime(file.lastModified());
        zos.putNextEntry(entry);
        IOUtils.copy(new FileInputStream(file), zos);
      }
    }
  }

We cheated a little here by using the Apache Commons IO IOUtils class to actually copy the file bytes to the ZIP file. Also, we don't do anything here with IOExceptions.

With this method in place, we can create a ZIP file from a folder using:


  ZipOutputStream zos = new ZipOutputStream(new FileOutputStream(zipFile));
  zipDirectory(new File(dir), zos);
  zos.close();

Groovy's JDK IO extensions, and filesystem traversal methods, make this job quite a bit easier. Here's the Groovy code to do the same thing:


  new ZipOutputStream(new FileOutputStream(zipFile)).withStream {zos ->
    new File(dir).traverse(type: FileType.FILES) {File file ->
      def entry = new ZipEntry(file.path)
      entry.size = file.length()
      entry.time = file.lastModified()
      zos.putNextEntry(entry)
      zos << file.bytes
    }

This code is still a bit awkward in how it interacts with Java's ZIP API, in particular the creation of the ZipEntry object.

Using a simple builder, we can rewrite this as follows:


  new ZipBuilder(new FileOutputStream(zipFile)).zip {
    new File(dir).traverse(type: FileType.FILES) {File file ->
      entry(file.path, size: file.length(), time: file.lastModified()) {it << file.bytes}
    }
  }

The ZipBuilder provides two methods:

zip(): creates and manages the ZipOutputStream

entry() (nested): creates and adds a ZipEntry to the enclosing zip stream

As with other builders, this builder promotes readable code that reflects the structure of the object to be created.

Here's the code for the builder itself:


class ZipBuilder {

  @InheritConstructors
  static class NonClosingOutputStream extends FilterOutputStream {
    void close() {
      // do nothing
    }
  }

  ZipOutputStream zos

  ZipBuilder(OutputStream os) {
    zos = new ZipOutputStream(os)
  }

  void zip(Closure closure) {
    closure.delegate = this
    closure.call()
    zos.close()
  }

  void entry(Map props, String name, Closure closure) {
    def entry = new ZipEntry(name)
    props.each {k, v -> entry[k] = v}
    zos.putNextEntry(entry)
    NonClosingOutputStream ncos = new NonClosingOutputStream(zos)
    closure.call(ncos)
  }

  void entry(String name, Closure closure) {
    entry([:], name, closure)
  }
}

This builder uses the same style with Closures as the HSSFWorkbookBuilder described earlier.

There are a few other Groovy (and Java) features to note:

Java's ZIP library requires clients to write to the ZipOutputStream for each entry created. We need to make sure that no entry closes the ZipOutputStream -- it must be closed only when the zip stream is finished. (Many of Groovy's output methods close streams automatically.) For this reason, we wrap the output stream in a NonClosingOutputStream before passing it to an entry. This class is simply defined as a FilterOutputStream (OutputStream decorator) with a no-op close() method.

We use Groovy's @InheritConstructors to save repeating the trivial constructor.

The entry() method creates a new ZipEntry with its mandatory name property. It then populates additional optional properties from a Map, using Groovy's support for setting Java Beans properties as Map keys. These properties are intended to be provided as named arguments to the method, as shown in the example earlier. This makes for a very concise and intuitive way to set the properties.

The main overload of entry() is declared to take its arguments in this order: Map props, String name, Closure closure. When called, entry() is (typically) given arguments in a different order: String name, Map props, Closure closure. This is due to Groovy's convention for passing named arguments to a method, described here, in the section "Named Arguments".

One final note about this builder -- it doesn't just work with files. Because the constructor takes an OutputStream, it can write to any stream. So it could be used to write directly to a servlet response, for example. Similarly, the entries are populated as streams, so they can be filled by anything that can write to a stream.

Groovy DSL/Builders: POI Spreadsheets

2010-12-28T21:26:00.000-08:00

It's well-known that Groovy is very rich for creating DSLs and fluent builder APIs.

I work a lot with the Apache POI library to generate Excel workbooks from data. We can use Groovy very easily to support a fluent and readable API for creating workbooks.

Here's a very simple example. Suppose we want to populate a workbook with two sheets with some data. Using the raw POI API, we could code something like this:


  def workbook = new HSSFWorkbook()
  def sheet1 = workbook.createSheet("Data")
  def row10 = sheet1.createRow(0)
  row10.createCell(0).setCellValue(new HSSFRichTextString("Invoice Number"))
  row10.createCell(1).setCellValue(new HSSFRichTextString("Invoice Date"))
  row10.createCell(2).setCellValue(new HSSFRichTextString("Amount"))
  def row11 = sheet1.createRow(1)
  row11.createCell(0).setCellValue(new HSSFRichTextString("100"))
  row11.createCell(1).setCellValue(Date.parse("yyyy-MM-dd", "2010-10-18"))
  row11.createCell(2).setCellValue(123.45)
  def row12 = sheet1.createRow(2)
  row12.createCell(0).setCellValue(new HSSFRichTextString("600"))
  row12.createCell(1).setCellValue(Date.parse("yyyy-MM-dd", "2010-11-17"))
  row12.createCell(2).setCellValue(132.54)
  def sheet2 = workbook.createSheet("Summary")
  def row20 = sheet2.createRow(0)
  row20.createCell(0).setCellValue(new HSSFRichTextString("Sheet: Summary"))
  def row21 = sheet2.createRow(1)
  row21.createCell(0).setCellValue(new HSSFRichTextString("Total"))
  row21.createCell(1).setCellValue(123.45 + 132.54)

This is not very readable. Even if we extract routines such as a common method to generate the cells in a row, the structure of our code does not follow closely the structure of what we want to create.
One of the big advantages of builders is that the structure of the code can match closely the structure of the generated result.

Here's the same workbook, created with a simple builder API:


  def workbook = new HSSFWorkbookBuilder().workbook {
    sheet("Data") { // sheet1
      row(["Invoice Number", "Invoice Date", "Amount"])
      row(["100", Date.parse("yyyy-MM-dd", "2010-10-18"), 123.45])
      row(["600", Date.parse("yyyy-MM-dd", "2010-11-17"), 132.54])
    }
    sheet("Summary") { // sheet2
      row(["Sheet: Summary"])
      row(["Total", 123.45 + 132.54])
    }
  }

The HSSFWorkbookBuilder class required to do this is very straightforward:


import org.apache.poi.hssf.usermodel.HSSFRichTextString
import org.apache.poi.hssf.usermodel.HSSFWorkbook
import org.apache.poi.ss.usermodel.Cell
import org.apache.poi.ss.usermodel.Row
import org.apache.poi.ss.usermodel.Sheet
import org.apache.poi.ss.usermodel.Workbook

class HSSFWorkbookBuilder {

  private Workbook workbook = new HSSFWorkbook()
  private Sheet sheet
  private int rows

  Workbook workbook(Closure closure) {
    closure.delegate = this
    closure.call()
    workbook
  }

  void sheet(String name, Closure closure) {
    sheet = workbook.createSheet(name)
    rows = 0
    closure.delegate = this
    closure.call()
  }

  void row(values) {
    Row row = sheet.createRow(rows++ as int)
    values.eachWithIndex {value, col ->
      Cell cell = row.createCell(col)
      switch (value) {
        case Date: cell.setCellValue((Date) value); break
        case Double: cell.setCellValue((Double) value); break
        case BigDecimal: cell.setCellValue(((BigDecimal) value).doubleValue()); break
        default: cell.setCellValue(new HSSFRichTextString("" + value)); break
      }
    }
  }

}

The magic is in the handling of the nested closures, and setting the delegate for each to the builder so that methods are resolved against the builder.

Here's another example of using such a builder. This one takes an SQL query and creates a workbook with two sheets. The first sheet contains the result of running the query, and the second sheet contains the query text.


  def workbook = new HSSFWorkbookBuilder().workbook {
    sheet("Data") {
      db.eachRow(
        sql,
        {meta -> row(meta*.columnName)},           // header row with columns names from ResultSetMetaData
        {rs -> row(rs.toRowResult().values())}     // data row for each ResultSet row
      )
    }
    sheet("SQL") {
      sql.eachLine {line ->
        row([line])
      }
    }
  }

Release builds with TeamCity: Selecting the branch

2010-02-04T13:51:00.000-08:00

We've long had TeamCity doing regular "CI style" checkin builds for our Java/Ant projects. We recently added nightly builds for extra reports, and for longer-running performance tests. This was straightforward.

We finally got TeamCity doing our release builds. There were a couple of tricky points, which I thought would be worth writing up:

Selecting the branch
Manipulating the repository
Ensuring correct (release) versions of dependencies

Selecting the branch

This was the trickiest thing. Checkin and nightly builds always run against trunk. Well, you could set up a checkin build for a long-running development branch too, but that's not difficult. The release build should be done from the release branch, and that can be different for each release. Or it can be the same, if you have to do a fix release on an existing one!

We found a pretty good way to do this with TeamCity, using Build Configuration Templates and Configuration Parameters.

The idea is that you set up a template that contains all of the settings for the release build, except for the branch name. The branch name is specified by a configuration parameter. Then, the template is instantiated for each branch as desired. Each time the template is instantiated, the branch name configuration parameter is given for that instance.

Here are some details, using Subversion VCS and a hypothetical project named "xxx":

Create the Release Build Template

Edit project's existing checkin build config.
Click "Extract Template" to create a new template.
Enter "release-build-template" for Name.
Back in the checkin build config, click "Detach from Template".
Click OK.

We've created a template, with no configurations attached.

Set up the Release Build Template

Edit the template. Change settings as given in the following sections.

Version Control Settings

Create a new VCS root named xxx-branches.
Specify Subversion as the Type of VCS.
Enter Svn repo URL + "/xxx/branches" as the URL.
Test the Connection.
Save the VCS root.
Attach the template to the xxx-branches VCS root.
Detach the template from the xxx-trunk VCS root.
Add checkout rule for VCS root: "%release.branch%=>.". This tells TeamCity to checkout the specific release branch into the working directory.
Save Version Control Settings.

Runner Settings

Change Target to "release-build", or whatever you want to call your release build target.
Save Runner Settings.

You must have a target in your build script called "release-build", or whatever you want to call your release build target. This target must build the release and publish it somewhere. For example, it could copy it to a staging area on a server. Or, it could publish it to your enterprise repository.

The Ant target might look something like this:

Build Triggering Settings

Delete/disable all triggering (VCS and Dependencies).
Save Build Triggering Settings.

We've modified the template so that it will checkout the source from a release branch, with the specific branch given by a configuration parameter ("release.branch"). It will then build and publish the release.

Create a Release Branch Build Config for Release xx.yy

This procedure creates a release build config for a particular branch.

Edit the release-build template's build configuration.
Click Create Build Configuration From Template.
Enter "release-xx.yy" for Name, where "xx.yy" is the name of your release branch.
Enter the name of the branch for the release.branch parameter. For example, "RB-01.05".

We've created a configuration for running a release build on the branch.

To create a release:

Ensure all changes for the release are checked into trunk.

Create the branch. For example:

svn copy $SVN/xxx/trunk $SVN/xxx/branches/RB-xx.yy

Click Run on the release build configuration in TeamCity.

You can keep the release build configuration around for a particular branch as long as you like. If you are finished with a branch, you can delete the build configuration. If you need it again, it's easy to recreate it from the template.

That's it. I hope to write up some notes on the other points (manipulating the repository and ensuring the correct release versions of dependencies) soon.

Oracle SQL-Developer 2.1

2009-10-29T00:02:00.000-07:00

I've been trying out Oracle SQL-Developer 2.1 early adopter for the last couple of days, because SQL-Developer is the standard query tool at the company where I'm working.

Personally, I mostly use Aqua Data Studio. Apart from its odious activation scheme, ADS is excellent in every respect. However, ADS is kind of expensive, while SQL-Developer is free.

Previous versions of SQL-Developer were promising, but not very polished. They would paint strange colors on the screen in my installation, and behave poorly with regard to threading and large queries. The new version seems like a big improvement so far. Although it doesn't have the same brilliant keyboard support as ADS, it does seem to work pretty solidly, and appears reasonably attractive. (Much prettier than TOAD at any rate.)

I did notice one amusing glitch so far:

In fact the query only returned about 20 rows.

Table size frequencies

2009-09-10T13:32:00.001-07:00

Here's an interesting query to run on your Oracle database:

Here's what I get on the main application I've been working on the last several years:


ROW_COUNT               NUM_TABLES
----------------------  ----------
1-10                            36
10-100                          22
100-1000                        22
1000-10000                      22
10000-100000                    22
100000-1000000                  33
1000000-10000000                20
10000000-100000000               2
100000000-1000000000             3
1000000000-10000000000           1
                               183

Inline data for DbUnit tests in Grails

2009-07-23T16:59:00.000-07:00

There is a DbUnit plugin for Grails: http://docs.codehaus.org/display/GRAILS/DBUnit+Plugin.

However, like almost all tutorials for DbUnit, this plugin assumes that the best way to organize your test data is using DbUnit's FlatXmlDataSet. The example given on the Wiki link above has this FlatXmlDataSet:

During my 5 years of working with DbUnit, I have come to the conclusion that FlatXmlDataSet is not the best way to organize test data. Here are my reasons:

XML is not a good representation for tabular data. CSV is more readable.

For most of my tests, I would rather see the test data together with the test, rather than separate.

DbUnit tests often have 3 "phases":

Prime the database with setup data.

Run some code of the System Under Test.

Verify changed data in the database.

For most of my DbUnit tests, I would like to see the data used for the first and last parts together with the test itself.

For this reason, we've gravitated towards inline CSV datasets in most of our DbUnit test cases. You can achieve this using Groovy in Grails very simply.

The Wiki example, used in a test with inline datasets, might look like this:

(I'm using pipe characters instead of commas in this case!)
If the same Person data were used in different test methods, we might factor it out into a common method or field.

What I tend to find is that I use a lot of common reference data in my tests, but test-specific detail data. I handle the common reference data in a setUp() method, or even in the base DbUnitTestCase class. I put the test-specific detail data in each test. This prevents duplication of the common stuff, and keeps each test clean and focused. By reading the test method, you can see everything involved in the test.

We define cleanInsert() and a few other conveniences in DbUnitTestCase:

Tomcat Expert Seminar

2009-04-03T00:13:00.000-07:00

Yesterday I attended SpringSource's Tomcat Expert Seminar.

This session rocked! I have learned a lot of neat ways to enhance our usage and troubleshooting techniques. The presenter, Filip Hanik, was not only extremely knowledgeable, but he was also entertaining and engaging. He handled questions from the audience extremely well, being able to give a direct and informative answer to nearly every question.

The most interesting topics for me were:

Large scale deployments -- showed flexible ways to organise deployment to simplify instance configuration and management.

Troubleshooting -- great tips on Tomcat but even more on Java and server-based systems in general.

I look forward to attending similar sessions in the future.

Linux on new Laptop

2008-09-15T11:59:00.000-07:00

I bought a new laptop a couple of weeks ago, because Zena's old hand-me-down IBM R50p's screen bit the dust. So she gets my IBM/Lenovo Z61p, and I bought a Sony VGN-Z17GN. Wow, Sony have long model numbers.

The thing that sold me on the Sony was the high spec in the small (and elegant) package. It has 4GB RAM, 320 SATA hard drive, dual core processor and a 1600x900 screen. But the machine is tiny, and weighs only 1.5kg. In a small laptop bag and with the power supply, it's lighter than my previous laptop's backpack, empty.

Like most people, I don't care for Vista. So I actually applied the XP Pro upgrade option before I even got the machine home. But the OEM XP Pro disk did not include the machine's specific drivers. So I had to download a ZIP bundle from Sony which was supposed to include all of the drivers. It did include 20 of them, which meant about 20 times of clicking through the installer, accepting whatever inane licence agreement, and rebooting. Sony didn't include the ethernet driver in their bundle, so I hunted that down on the Intel web site. It in turned required MSXML, so I had to find that and install it too. In the end, a fairly typical experience of installing Windows, mind-numbingly tedious.

After I finally got XP working, more or less, I popped in a freshly-burned Ubuntu disk and began the Linux install. Wow, what a difference. I had actually been a little worried about how hard it would be to get the video annd ethernet working with Linux, since the machine is quite new on the market. (I have the first one sold in New Zealand.) Well, in a few minutes, with perhaps one or maybe two reboots, I had Ubuntu installed, and everything just works. Everything I care about, anyway. I have no idea whether the fingerprint reader is supported in Linux, but I don't care. To be honest, the wireless network doesn't work yet, but apparently it is supported directly in the next Ubuntu version, due out next month, so I'll just wait for that.

Once I restored my home directory from a backup, all my desktop and configuration settings were ready to go on the new machine. No registry hacking, no special software for migrating settings. It's funny how with Windows, a lot of the "features" are workarounds for problems that don't exist in other operating systems.

Tests For Your Data 2: When to Use

2008-07-28T02:06:00.000-07:00

My friend Nigel Charman commented on Tests For Your Data with some good questions.

First the short answers.

Where possible the constraints are matched in the application. So the "double check" idea holds true. Occasionally this turns up bugs in the application. But more often it turns up bugs in manual edits of data.

With referential constraints, despite what some people seem to think, you need to define them in the database, regardless of whether they are also enforced at the application level. Exactly the same here. Double checks are useful, and applications are not infallible.

In my experience I'm running these only in production, and yes I regularly get production failures for them. (That's why I do them. ;-)

I probably shouldn't have hijacked the Continuous Integration metaphor for this idea. Basically this is a data management practice, and doesn't have much to do with the development cycle. However, it is a practice I am very passionate about. Just as a good test suite keeps my code healthy and vigorous, I feel that these data checks help me keep my production data healthy and clean.

Now for the meaty question: For what types of data is this approach suited?

Data from external sources. For data entered interactively, it's usually best to reject invalid data immediately at point of entry. For external-sourced data that are loaded in batch, this is not always the best way. Sometimes data are correlated with data loaded via another batch stream, in a separate transaction. There isn't always a good place to validate and reject bad data. In such cases, check views help catch the bad data.

Data in 3rd-party systems. We have applications for which we are not the developer. So we have little or no control over the application logic or database constraints. But with check views, at least we can identify data problems and work to fix them.

Multi-row conditions. The classic case here is checking for gaps and overlaps between multiple rows containing date ranges. In my work at Red Energy, we have many tables with bitemporal data (two time dimenions). It's quite hard to visualize these data simply by looking at the tabular form, so it's useful to have check queries to verify that the "shape" of the data is valid.

Aggregations. We have cases where different tables aggregate the same basic information by completely different keys. After having the IT manager complain to me twice about two reports (driven from two tables) not balancing, I added a check query to verify that the aggregations balance. The next time the problem happened, I was the first to know.

Parent-to-child relations. Foreign keys can enforce that every child has a parent. Sometimes you want to enforce that every parent has at least one child.

"Future" conditions. Sometimes you have "static" data that cover a range of time, such as calendar data or pricing. You enter data for the next three years, and then start running your system. A carefully-written check view can remind you when it's time to update for the next three years.

Believe it or not, we also have some check views on the check views. There is one that warns if any view (or PL/SQL package) in the database contains errors. There is another that verifies that every check view has a comment.

Tests For Your Data

2008-07-25T17:28:00.000-07:00

These days automated tests for your code are standard practice in any professional IT shop. There are a variety of automated testing tools in use, from JUnit and TestNG that can run unit tests and integration tests, through Behavior Driven Design, FITness, and others.

I propose we should have tests for our data too.

Code makes a lot of assumptions about the data it works on. Many of these assumptions can be enforced using constraints in the database itself:

A PRIMARY KEY constraint defines a unique key for the table.
A UNIQUE constraint identifies an alternative candidate key, which also must be unique.
A FOREIGN KEY constraint defines a relationship to a parent table, and is used to enforce referential integrity.
A CHECK constraint can be used to check arbitrary conditions on the values in a row.

In addition to these constraints, you can also use triggers to check more complex conditions, perhaps involving multiple rows.

Despite all of these, there are many cases where constraints are too awkward or inefficient. Particularly when conditions span multiple rows, database constraints and triggers are not very good for enforcing them.

Here's an example. Suppose we have a table billing_period:

BILLING_PERIOD ACTUAL_START ACTUAL_END
200825 2008-06-04 2008-06-10
200826 2008-06-11 2008-06-17
200827 2008-06-18 2008-06-24
etc

The billing_period table is supposed to contain weekly billing periods, along with the dates belonging to them. Each billing period is supposed to be exactly seven days long. There should be no overlaps or gaps, either. How would we enforce these conditions using constraints or triggers?

You have probably written hundreds of queries to test conditions like this about the database. How about making those queries into a test suite for your production data?

Start with a view like this:

CREATE VIEW chk_billing_period_7_days_long AS
SELECT *
FROM billing_period
WHERE actual_start - actual_end <> 6

This view returns a row for any billing period which is not seven days (actually six days) from its start to its end.

Here's another one, to check for overlaps:

CREATE VIEW chk_billing_period_overlaps AS
SELECT *
FROM billing_period bp1
WHERE EXISTS (
SELECT *
FROM billing_period bp2
WHERE bp2.actual_start BETWEEN bp1.actual_start AND bp1.actual_end
OR bp2.actual_end BETWEEN bp1.actual_start AND bp1.actual_end
)

Finally, to check for gaps:

CREATE VIEW chk_billing_period_gaps AS
SELECT *
FROM billing_period bp1
WHERE EXISTS (
SELECT *
FROM billing_period bp2
WHERE bp2.actual_start > bp2.actual_end
)
AND NOT EXISTS (
SELECT *
FROM billing_period bp3
WHERE bp3.actual_start = bp2.actual_end + 1
)

None of these views should ever return any results. If any of them does, we have a data integrity problem. The problem may cause our application's views or code to fail, because of violated assumptions.

Because we named all the views according to a convention (they all start with chk_) we can easily write a program that iterates over these views and tests them all. This program could be scheduled to run every day. It could email us results from any check view that returns data.

If our database supports it, we can add descriptive comments to the views, such as:

COMMENT ON TABLE chk_billing_period_overlaps IS
'Overlap between two or more billing periods'

This comment would make a nice subject line for an email message.

It's easy to add more check views: just define a view beginning with
chk_.

I've gotten into the habit, when I'm designing application code or view logic, to think about the assumptions. If an assumption can be reasonably enforced with a database constraint, I will add a constraint. Otherwise, I write a check view for the assumption and add the check view to the database. Also, just as when I find a bug in my application code, I write a unit test to expose it, so I also write check views to expose data bugs I find in the database.

A scheduled job runs every check view every day, and emails data problems to the team. The views comprise a test suite for our data. The scheduled job gives us continuous integration of sorts. We are alerted to problems virtually as soon as they happen. (Well, the next day.)

We've been running this system at Red Energy for a couple of years now. On one application, featuring about 150 tables, we have a little over 100 check views. I think we should have a lot more. Even so, this system has allowed us to maintain a very high level of data integrity.

JAOO Sydney 2008

2008-06-03T03:34:00.000-07:00

The message from JAOO 2008 is: Software development is in crisis. To get over it, we're all going to be programming in functional languages and deploying in the Cloud. Maybe fundamentalist functional languages.

Dave Thomas is thought-provoking. But he can't make up his mind whether shiny things are the new one-true-path or the spawn of the devil. He's a great devil's advocate. He'll tell you that he'd rather maintain a legacy COBOL application than a legacy Java app. Then he'll tell you that the "new SQL" is to be found in the query features in LINQ. Then he'll say OO is the best technology for product development, but a terrible thing for enterprise software. He thinks the computer scientists have been running the asylum.

Martin Fowler is an opinionated grump but I like him more and more. It's probably just because he agrees with many of the things I believe. XSLT was a bad technology for writing user interfaces (or whole applications). OO is still a good way to write complex domain logic. You cannot write serious applications with Doodleware. It's better to evolve a framework out of a working application
than to start with the framework before writing the application.

There was a video clip of him claiming that it isn't "significantly" harder to design a DSL than to design an API. I have tended to disagree with this, but I am probably confusing DSLs with full-blown languages. If he's talking about the special-purpose "DSL"s such as Grails' GORM DSL, or Ruby-on-Rails, then of course he's right. These are DSLs. They're also glorified APIs in a way. Don't get me wrong: they're great. I'm looking forward to his new book on the subject, though I'm sad to hear that PEAA2 is on the back-burner.

Fowler presented Kent Beck's four features of well-designed software:

Passes the tests
Code shows the intent
No duplication
"Less stuff"

Fair enough. I am interested in whether people feel that tests should be factored the same as production code. These days I am inclined to "show everything" in the test, to make each test easy to understand in isolation. Erik Dörnenburg seems to agree with this. Robert Martin says to factor the tests just the same as production code.

Gregor Hohpe is always fun to watch. He really ought to have a TV show or something. He told us about the "new" ACID properties, for distributed systems:

Asynchronous
Concurrent
Internet-Scale
Distributed

And he hoped he got them 50% right...

SOA seems to be "out", according to many on the Enterprise Systems Panel. I guess that means it isn't shiny any more and the only people pushing it now are the out-of-date vendors who haven't caught on yet. I hope they catch on soon. Like most shiny IT technologies, SOA has probably been very useful to some organisations, been a complete failure for others, and is not particularly significant for the rest of us.

I am very skeptical about the "new SQL" of LINQ. It is very cool and neato and so forth, and a small part of me is kind of envious of the .NET camp for this nice stuff coming out of Microsoft. But application data access technologies come and go nearly every year, and SQL is still with us. None of these conference speakers or other "leading edge" developers at the conference seem to think that ad-hoc queries are important, both for users and developers. I make dozens of them every day. I wonder where these people work.

I talked to a rather drunk ThoughWorker (at the party) who suggested my enterprise app of 100-odd relational tables would be better factored as two or three smaller apps using hash maps for persistence. I think he was serious. He also claimed that the performance of the Cloud was so great that it could substitute one kind of index for another without you knowing or caring. Maybe so. But why bother with an index at all then? You only use indexes when you care about performance, and even then you need to understand the performance characteristics of the index you are using, if you want to get any
benefit from it.

The second day we had Robert Martin. He's also very entertaining. I asked him if his (new) book was as much fun as his talk and he said yes, so I think I will buy it. I didn't expect a talk to change my thinking about functions and clean code, but he may have. For example, he thinks functions should be between one and five lines long. I have a lot that are a bit longer than that, and now I think maybe they are not as good as I thought. He showed that by having really small functions, named very carefully for what they do, the code is really self-documenting. Much more so than if you have, say, 20-30 line functions.

He has these rules for Good Functions:

Small
Do one thing
Use descriptive names
No more than three arguments
No side-effects

I met Rod Johnson (again), and expressed commiseration that he has to explain the licensing for SpringSource Application Platform so much, over and over again. Even many of the Spring people don't understand it. I have been told by two different Spring guys that companies won't be able to run SSAP without paying for it. I guess I'd better go figure out whom I'm supposed to be paying for this copy of Linux on my laptop then, since it's the same license (GPL). (Of course I understand Spring has a commercial license as well as GPL. It's much the same as MySQL in that respect.)