Traditional GUI-based tools are great for looking up information in a database or making a quick change to a single instance, but their utility often diminishes when you’re working on a prototype system.

For example, take the common scenario of a commercial application that requires a dedicated database. A prototype of the database is designed and created using the author’s favorite tool (SQL Server Management Studio, Oracle Designer, TOAD, etc.), and then exported into script form using that tool’s built-in utility. The resulting script is then used to generate copies of the database for the customers.

The install process is rarely a smooth one, however. First of all, all the elements of the database mirror those of the development environment - users, passwords, and so forth. Those need to be changed, probably through some manual changes to the generated script.

What about updates? Any changes to the database in new software versions create a dilemma: should the changes be deployed as a patch or as a whole database drop-in replacement? The previously described process of modeling the system in an editor and then exporting a script file isn’t granular enough to support most patching operations, but replacing an entire database creates its own problems - most notably the scale of the deployment procedure and the customer’s (usually reasonable) expectation that their data will persist through upgrades.

Anyone who’s had to modify automatically generated code before probably sees the problem here - the job just got a lot more complex. Since no humans were involved in the creation of the script files, there’s no institutional knowledge about their structure, so every change must be carefully researched in order to ensure it does what it’s supposed to and doesn’t break anything else in the process.

Then there’s the question of rollbacks. What if something goes wrong? Can the system be restored to its previous state? Not many database tools have an export feature for rollback scripts.

The problem is, these tools simply weren’t designed to handle scenarios like this.

On the other hand, engineering a custom solution can result in more robust systems in less overall time. Here’s a technique I’ve used several times to simplify database development and management.

At a granular level, any change to database structure; creating or dropping objects, changing permissions, modifying data; can be encapsulated in a SQL (or PL/SQL, or Transact SQL, etc) script. Where database visualization software frequently gloms all these SQL statements together into large “install” scripts, I propose treating them discretely. The important distinction is that for each action, there is an associated “undo” action that returns the database to its prior state:

Action: create table
Undo: drop table

Action: grant permission
Undo: revoke permission

Action: drop table
Undo: create table and restore all data

Any install, upgrade, or rollback activity can be defined as a sequence of these types of actions. Scripting these operations as part of any release can go a long way toward streamline testing and deployment of your changes.


Example:

Your incredibly popular software, Turbo Renegade Interface Plus Extreme!, tracks its current activity and parameters in a database table called TaskInformation. The TaskInformation table was designed for one activity at a time, so it’s missing a primary key, timestamp, or any other identifying data.

At least, that’s how things were in ancient Version 3.4, released three whole months ago. The new, improved Turbo Renegade Interface Plus Extreme! 3.5 scheduled for release next week replaces the TaskInformation table in its database with a TaskHistory table - tracking not only the current activity but data from past activities as well. Amazing.

The problem is, you’ve been doing all your database modeling in TOAD, and the exported script just drops and re-creates each table. What you really want to do is capture the data from the TaskInformation table, drop it, then create the TaskHistory table and insert the old data.

There are a variety of queries and stored procedures that convert data from a table into SQL insert statements available on the internet, so I won’t go into them here except to mention their utility as part of an upgrade script. Once you’ve selected one, your upgrade script pseudocode looks like:

Upgrade TRIPE! 3.4 to 3.5
dump_data(TaskInformation);
drop table TaskInformation;
create table TaskHistory(…);
set permissions on TaskHistory;
insert_data(TaskInformation, TaskHistory);

And just in case the upgrade breaks something or one of your customers changes their mind (note: this never happens), you have an undo script too:

Rollback TRIPE! 3.5 to 3.4
drop table TaskHistory;
create table TaskInformation(…);
set permissions on TaskInformation;
insert_data(TaskInformation, TaskInformation);

I like to keep each action in its own .sql file:

create_table_TaskHistory.sql
print ‘******************************’
print ‘* creating table TaskHistory *’
print ‘******************************’
print ”

create table [TaskHistory]
(
[ID] Integer Identity(1,1) NOT NULL
[Title] varchar(40) NOT NULL,
[Setting] varchar(100),
constraint [pk_TaskHistory] primary key ([ID])
)

And then wrapping those .sql files in an overarching script that calls each of them in turn to accomplish a specific goal:

upgrade_tripe_3.4_to_3.5.sql

print ‘**************************************’
print ‘* upgrading database from 3.4 to 3.5 *’
print ‘**************************************’
print ”

:r “script_1.sql”
:r “script_2.sql”
:r “script_3.sql”
:r “script_4.sql”
:r “create_table_TaskHistory.sql”

These examples are for sqlcmd.exe, using Transact SQL. The print keyword outputs its parameters to stdout, and the :r command invokes an external script file. The syntax would be different for Sql*Plus, etc.

You can then wrap that script in a batch file that provides some user feedback and logging capabilities:

:: upgrade tripe - 3.4 to 3.5.bat
:: february 2009
::
:: makes some minor changes to the database to upgrade from TRIPE! 3.4 to 3.5

@echo.
@echo TRIPE! database update utility, 3.4 to 3.5
@echo Initech Inc.
@echo.

@if “%1″ == “” goto usage
@if “%2″ == “” goto usage
@if “%3″ == “” goto usage
@if “%4″ == “” goto usage

@date /t >> upgrade.log
@time /t >> upgrade.log
@echo TRIPE! database update utility, 3.4 to 3.5 >> upgrade.log
@echo. >> upgrade.log

@echo Performing upgrade.
@echo.
@sqlcmd -S %1 -d %2 -U %3 -P %4 -i “upgrade_tripe_3.4_to_3.5.sql” >> upgrade.log
@echo. >> upgrade.log
@echo ………………………..End Of Line……………………….. >> upgrade.log
@echo. >> upgrade.log

@echo Operation completed.
@echo.
@echo [IMPORTANT] Review upgrade.log to determine if the operation was successful!
@echo.
@goto end

:usage
@echo USAGE: “upgrade tripe - 3.4 to 3.5.bat” [SERVER] [DATABASE_NAME] [USER] [PASSWORD]
@echo.

@goto end

:end
@pause

This batch file accomplishes a couple of things. The comments at the top help identify the file, just like any other source code (You do have header comments on all your source files, don’t you?). The @echo commands either output information to the screen or appends it to a log file along with the script output, and the usage message provides clear instructions on how to use the program. Parameterizing the database and user information allows us to wrap this file in yet another:

:: automated upgrade tripe - 3.4 to 3.5.bat
:: february 2009
::
:: executes “upgrade tripe - 3.4 to 3.5.bat” with specified parameters

@echo.
@echo Calling upgrade script with predefined parameters

@”upgrade tripe - 3.4 to 3.5.bat” myServer myDB sa P455w0RD

The automated batch file (one script to rule them all?) is ultimately the only one that needs to store location-specific information. Once this is prepared, it becomes a one-click process with easily editable modular components. I’ve given it to you in 4 distinct layers, but you may prefer to merge some of them together based on your architecture and style. Ultimately, it’s the script-based approach that makes the difference, allowing you to create mass-distribution utilities (multiple calls to a batch file with scripted parameters), simplifying upgrade and rollback processes, and most importantly, removing automated code generation from your critical path, building institutional knowledge of your system and cutting the time cost of future enhancements.