Automating Script Generation with Version Control

for RuBoard

Automating Script Generation with Version Control

Another nice feature of version control systems is their ability to automate file-based tasks using console applications and APIs. In conjunction with command-line compilers and scripting tools, these facilities allow you to easily do things like extract the latest version of a project from your code database and compile or run it from an automated process. Because the code is stored in a central database, and because the source code management system knows which version is most current, a simple API can provide all you need to construct automated processes to handle things like nightly test runs and weekly builds.

In VSS, this API is actually a COM interface that you can access from any Automation-capable programming environment such as Visual Basic or Delphi. Using the VSS Automation interface, you can do basically anything the VSS Explorer can do because it uses this interface itself. Via fairly trivial program code, you can navigate VSS project versions programmatically, check files in or out, extract specific project versions, and so forth.

GGSQLBuilder

As an example of how powerful and easy this is, I've included a Delphi-based utility that can scan a VSS project tree for SQL scripts and extract them to a pair of T-SQL script files that you can then run. GGSQLBuilder finds each SQL script in a VSS project hierarchy, then scans backward through the various versions of the file and finds the last one to have a label assigned to it (this assumes that you label software versions before shipping thema common practice) or the first version of the file if no version labels are found. Once it finds the correct version of each file, it appends that script to the script file it is building for the entire database.

GGSQLBuilder can be used interactively as well as from the command line. When run interactively, it takes the form of a wizard: It prompts you for the data it needs to locate your scripts, you choose some output filenames, and it finds and extracts your scripts. Using GGSQLBuilder's command-line interface, you can completely automate regular script generations.

How GGSQLBuilder Works

I mentioned that GGSQLBuilder will extract your SQL scripts to a pair of T-SQL script files. Why two? Because GGSQLBuilder is designed to generate scripts for an end- user application that's built around SQL Server. It's specifically designed to help with generating the scripts necessary to publish an update to such an application.

Typically, end-user applications based on SQL Server make use of two types of databases: one or more user databases and the master database. As you might expect, user databases usually store end-user data and application-specific T-SQL code objects such as stored procedures and views. The master database, on the other hand, is typically used to store custom system procedures and functions that are either system-oriented routines or that contain code that needs to be shared by all databases. As a rule, you shouldn't store anything in the master database that doesn't meet these criteria. GGSQLBuilder is designed to make extracting these two types of script files painless. Once it has generated the two files, you can run the end-user script against as many user databases as necessary. For example, if you planned to use the end-user script as part of a software update, your update program could call OSQL and use its -d parameter to specify the database to run the script against. You could easily automate applying the script to multiple end-user databases by calling OSQL repetitively from a command file.

As for the master script file, you'd typically run it just once during the installation or update of your software. It would install or update new system objects in the master database that would then be usable across the system.

The Advantages of Script Generation Tools

Why would you want to use a tool like GGSQLBuilder in the first place? Why not just ship the individual scripts themselves or use something like Enterprise Manager to create single script files? First, deploying hundreds or even thousands of individual script files to end users can be problematic . Every file you deploy increases the likelihood that an installation or update will fail. Second, unless you rebuild your customers' data every time you ship a software update, the scripts generated by Enterprise Manager are not suitable for updates. You probably wouldn't want DROP/CREATE TABLE statements in scripts meant to update an existing database.

How GGSQLBuilder Selects and Orders SQL Scripts

How does GGSQLBuilder know what to consider a SQL script and to which file to extract it? It makes some basic assumptions about the organization of your VSS projects:

It assumes that the scripts for a given project reside in subproject folders with the names listed in Table 4-4.
It assumes that any scripts that need to be run against the master database will be located under a subproject named MasterDB.
It identifies SQL scripts in your VSS project tree by examining their extensions. By default, files with extensions matching those in Table 4-5 are considered T-SQL scripts.

Notice that the folders in Table 4-4 are listed in order of object dependence. Objects in the master database may need to exist before the creation of user objects, default objects will need to be created before tables that reference them can be created, tables need to be created before scripts that alter them can be executed, and so forth. GGSQLBuilder will write the scripts it finds in your VSS database to the output scripts using the folder order from Table 4-4.

Table 4-4. VSS Project Folders that GGSQLBuilder Recognizes

Folder name	Type
MasterDB	Scripts to be applied against the master database
Defaults	Default objects (e.g., CREATE DEFAULT)
Rules	Rule objects (e.g., CREATE RULE)
Tables	Tables (e.g., CREATE TABLE)
TableAlters	Table alterations (e.g., ALTER TABLE)
Triggers	Triggers (e.g., CREATE TRIGGER)
UDFs	UDFs (e.g., CREATE FUNCTION)
Views	View objects (e.g., CREATE VIEW)
StoredProcs	Stored procedures (e.g., CREATE PROC or ALTER PROC)

Table 4-5. File Extensions that GGSQLBuilder Recognizes by Default

Extension	File type
SQL	General SQL scripts
PRC	Stored procedures
TRG	Triggers
UDF	UDFs
TAB	Tables
VIW	Views
DEF	Defaults
RUL	Rules
UDT	User-defined data types
FTX	Full-text index

This helps ensure that your scripts are executed in the right order and should preserve object dependencies.

The best way to learn about GGSQLBuilder is to run it interactively. If you have a VSS database with some SQL scripts checked into it, feel free to run GGSQLBuilder and allow it to attempt to locate those SQL scripts. If the project hierarchy is deep, you'll notice a delay while GGSQLBuilder examines every version of every file in the project tree. If you've grouped your script project folders (from Table 4-4) together under a single parent project folder (a good practice), you can instruct GGSQLBuilder to start its search with this project. Once GGSQLBuilder has found your scripts, allow it to build the two output script files to see how it works.

CAUTION

Because GGSQLBuilder identifies SQL scripts based on file extension alone, it's possible that the output scripts it generates will contain object creation/destruction that you may not want. In other words, if you have a create table script checked into VSS and GGSQLBuilder finds it, you may end up with not only an unwanted CREATE TABLE statement, but also a DROP TABLE statement if the original script happened to contain one. The moral of the story is this: Use GGSQLBuilder's project tree to examine closely the SQL scripts that it identifies, and uncheck those that you do not want to appear in the output scripts.

Experiment with GGSQLBuilder and see whether it might help in developing updates to SQL Server-based applications and in automating script generation and testing. Keep one thing in mind, though: Tools like GGSQLBuilder won't be of any use to you unless your T-SQL code is stored in a source code management system.

for RuBoard