Wednesday, December 28, 2011

Checking Foreign Key attribute consistency

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 file like this:


Then in create_table_invoice.sql I might have:

CREATE TABLE invoice (

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
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))

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.

Wednesday, February 9, 2011

Ant Target Dependency Graph

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("").text = """
digraph ant {
${project.targets.values().collect {target ->
target.dependencies.collect {dep ->
u(dep) + " -> " + u(target)

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 file in the current directory.

Convert this into a picture using the GraphViz dot command:
dot -Tsvg -O

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.

Sunday, January 23, 2011

SlickEdit 2011 Wish List

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:

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:

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.

Friday, January 7, 2011

Groovy DSL/Builders: ZIP Output Streams

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());
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);

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 << 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 {

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

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

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.