Database Changes Done Right

I love reading The Daily WTF dealing with programmers’ stories, experiences, and the occasional code nightmare. While it’s fun to read, it also makes you aware of how easily you can mistakes yourself, especially in an unfamiliar programming language, even after years of programming experience.

A recent article was titled Database Changes Done Right, and I was surprised that it was not fun or horror story, but rather a serious article on database change management, along with a couple of rules to watch when implementing database schema changes (yes, those ugly dev – test – production stages).

I also felt connected with the topic of the article, because for a couple of years now, I develop software that should help other developers ease this burden of managing database changes:

dbscript (blog, website, download) is a web application that stores database versions in repository (i.e. MSSQL database), calculates schema change scripts, stores definitions of table value initialization scripts and C# constant declarations, and keeps track of product version at customer installations. dbscript focuses on MS SQL Server databases in these areas, but also supports Oracle and PostgreSql databases for documentation purposes: document your database schema in a wiki or in a Word HTML file, or generate a data diagram with just a couple of mouse clicks.

SMOscript (blog, website, download) is a command-line tool that generates SQL scripts for all objects in a MSSQL database (tables, views, stored procedures, etc) to a single file, or to one file per object. These generates scripts can be kept in directories covered by source control systems (SVN, TFS, etc), and thus SMOscript simplifies storing the scripts for each database version in a source code repository.

oraddlscript (blog, website, download) is a command-line tool similar to SMOscript, but covering Oracle databases.

checktsql (blog, website, download) is a Windows tool that verifies all objects in a database (views, functions, stored procedures) by executing them or selecting from them (each in a transaction that will be rolled back), and reporting any errors that occurred during their invocation.

This product summary does not cover the complete functionality of each program. As programs evolve, they gain a lot of functionality, as users (including me!) add requirements and contribute ideas. Feel free to add yours!

oraddlscript 0.17

oraddlscript is a command-line utilty which generates CREATE scripts of database objects in an Oracle database using  the DBMS_METADATA.GET_DDL function.

The most recent update includes a separate executable using the DataDirect ADO.Net Data Provider to connect to an Oracle database (named oraddlscriptdd).

Command-line parameters and functionality are the same as in the original version.

The latest version of oraddlscript is available for download.

Source Control Management and Databases

Source controlling or versioning database schemas is a recurring topic on StackOverflow and on database-related blogs and web sites.

There does not seem to be a single correct solution, rather everybody tries to find their own way to deal with this topic.

The core of the problems seems to be the different types of data between file-based source code and database schemas. Whereas source control systems typically have to deal with source code in files, and every check-in relies on the ability to compile every revision in source control (well, in an ideal world), versioning databases is different in the aspects:

• store a version of the full database schema (for installation)
• store a change script / migration script to upgrade from one version to the next (for upgrades)
• store the values of certain tables (lookup tables, system and application constants)

Command-line tools

In recent years I developed a set of tools that I think may be helpful to the task of versioning databases:

SMOscript is a tool which scripts all database objects in an MS SQL Server database to file using the MS SMO library. Using the -f switch, one file per object is created in a directory. If that directory is under source control, the developer has all CREATE statements in the same revision tool as their application source code.

Oraddlscript is the equivalent of SMOscript for Oracle databases. It makes use of the built-in function DBMS_METADATA.GET_DDL, which provides the CREATE statements for any object inside an Oracle database.

Integrated solution

dbscript is a web application which tries to integrate the 3 aspects mention above:

• dbscript keeps in its database every schema ever uploaded or imported.
• dbscript compares any two database schema versions displaying the differences
• dbscript generates a migration script of selected objects as a result of this comparison
• dbscript compares a set of schema versions to display a timeline of version changes
• dbscript generates T-SQL value scripts and C# constant declarations based on database data

The generated files can be stored inside the dbscript database (always related to a schema version or a comparison between versions), and / or downloaded to a directory covered by source control.

Note: I am biased, since I use dbscript for the projects I develop since its first stable and reliable version. Actually, dbscript development itself uses dbscript, as can be seen in the SQL files shipped for installation and upgrades.

For more information on the products, see the company website. Free downloads are availabe here.

oraddlscript 0.16

Version 0.16 of oraddlscript includes two fixes:

• Database Links are now scripted
• Reduced memory consumption

Scripting database links required translating the OBJECT_TYPE “DATABASE LINK” into the GET_DDL() parameter value “DB_LINK”.

Increased memory consumption was caused by a DataReader that was not closed.

Thanks to Thomas for pointing out the problems.

The latest version of oraddlscript is available for download.

Update of oraddlscript 0.15

Version 0.15 of oraddlscript includes two fixes requested by a user of this utility:

To correctly separate the CREATE PACKAGE and CREATE PACKAGE BODY statements of a package, it is necessary to execute the command

DBMS_METADATA.SET_TRANSFORM_PARAM(
    DBMS_METADATA.SESSION_TRANSFORM, 'SQLTERMINATOR', true);

before calling GET_DDL().

(I am really considering passing SESSION_TRANSFORM parameters from the command line if it should be necessary)

If DDL generation fails, oraddlscript will now additionally output the Oracle error message. This will allow for further debugging if you have database objects that won’t script.

The latest version of oraddlscript is available for download.

oraddlscript: Solving ODP.Net version conflicts

I got feedback on oraddlscript that exceptions are raised due to incompatible versions of Oracle.DataAccess.dll. I could reproduce these exceptions on a non-development machine.

After installing Oracle Data Access Components (ODAC) 11.1.0.6.21 using the install command

install.bat all c:\oracle\odp odac

the problem was solved, and I could connect to the database server.

Oracle Data Access Components (ODAC) are available for download from Oracle here.

Introducing oraddlscript

A recent question on StackOverflow inspired me to write a utility called oraddlscript:

How to generate scripts for Oracle schema objects, such as tables, procedures, etc.

The answers directed me to the DBMS_METADATA package (9i documentation, 10g documentation). The function DBMS_METADATA.GET_DDL is the functional equivalent of the MSSQL SMO library, which prompted me to adapt my command-line utility SMOscript to Oracle databases. Voilà, oraddlscript.

oraddlscript 0.14.3584.16268 (c) by devio.at 2009

    list and script databases and database objects.

    usage: oraddlscript [options] [command]

    options: (leading '-' or '/')

    -s server       TNS name or host name or host:port
    -svc service    service name (if host name is provided)
    -o owner        owner name

    -u username     username (default: integrated authentication)
    -p password     password (if -u is missing, password for sa)

    -f filename     output to file
    -F directory    output to directory

    -A              current ANSI codepage
    -O              ASCII
    -T              Unicode
    -U              UTF8

    commands:

    l               list objects
    s               script object/s (using dbms_meta.get*ddl)
    -xml            use dbms_meta.get*xml

    list object owners on server (implied by -s)
    list objects in database (implied by -s -o)
    script all objects (implied by -s -o -F/-f)

The command-line arguments are consistent with SMOscript, except for -d (database) which has been replaced by -o (owner name).

The list of objects is retrieved by querying DBA_OBJECTS, ALL_OBJECTS and USER_OBJECTS depending on which of the catalog views is accessible by the user specified by -u.

The package also contains a function GET_XML which is used to retrieve the XML representation of a database object.

The functions of oraddlscript are:

• list usernames of object owners
• list objects of specific owner
• generate CREATE scripts of objects owned by specific user
• generate XML files representing objects owned by specific user
• generate one file per object or single file for all objects

Of course, logging and batch operations work just as previously described for SMOscript.

oraddlscript is available for download here.