Marzhill Musings

This article was my life saver

Have you ever wanted to test a long sql DDL script for syntax errors but didn’t want to actually create your db structure yet? I’ve found the easiest way to do this is through the use of transactions. simply begin a transaction at the start of the script and roll it back at the end of the script. For example:

-- PostgreSQL DDL script
BEGIN; -- begins our transaction block

CREATE TABLE test_tbl
(
 pk numeric NOT NULL,
 data varchar(128),
);

ROLLBACK; -- roll back everything this script just did
COMMIT; -- use this instead of ROLLBACK to commit the changes

This has the benefit of allowing us to test the script for errors and yet not actually run it on the DB. The EXPLAIN command can do this also on some DB’s but you would need it for every statement you wrote in the script and some statements will error out if you use EXPLAIN on them. I’ve found the Transaction method to work best for what I want to do.

I am working on a legacy web application right now that is giving me fits. I’d say about 90% or so of the application is done in SQL. Yes you got that right. The business logic is almost completely written in a huge number of stored procedures, sql functions, and scheduled database tasks. This makes tracking down the parts of your app you are trying to work on very difficult. Every time I turn around there is another stored procedure, function, or scheduled database task that needs tweaking. I’m starting to go a little crazy.

The problem is it obfuscates what your application is really doing. You think a perl obfuscation contest produces difficult to follow code? They got nothing on this. I realize stored procedures were the cat’s meow at the time but this is beyond all human decency. I have got to start refactoring this thing before it gets out of control.