If you are not doing continuous integration, you should; and if you are – then you ought to consider database install as integral a part of your build process.
Most CI servers out there would allow you to execute batch or shell commands, and virtually every RDBMS provides a command line utility (and creating one on your own – if needed – is rather trivial).
Installing a database as part of your build process, and populating it with data could play role in your unit testing strategy, and should definitely be considered integral part of functional and regression testing procedures.
The following gives but an example of how to make MS SQL Server database install a part of your build process utilizing Microsoft command line utility SQLCMD and open source continuous integration server Hudson. This could be applied to any other RDBMS package – MySQL, PostgreSQL, Oracle, DB2 or Sybase – with minor adjustments.
The command line utility can be downloaded separately, or installed as part of SQL Server 200X installation. If your unit tests require database support, it might be a good idea to install free SQL Server Express Edition which could be started as part of the build process and shut down afterwards.
“The sqlcmd utilitylets you enter Transact-SQL statements, system procedures, and script files at the command prompt, in Query Editor in SQLCMD mode, in a Windows script file or in an operating system (Cmd.exe) job step of a SQL Server Agent job. This utility uses OLE DB to execute Transact-SQL batches.”
This provides an opportunity to make creation of a database and all dependent database objects a part in your continuous integration build process with Hudson – an open source continuous integration serverthrough executing scripts – either integrated with your build management utility such as Maven, Ant or MSBuild – depending on your platform, or just plain batch or shell commands.
A very basic Windows batch command in Hudson installing database through SQLCMD might look like this:
sqlcmd –S<IP address>,[port] -U<user> -P<password> -dmaster -i%WORKSPACE% \exec.sql
- -S indicates IP of the SQL Server instance to connect to
- - U and –P - user ID and password, respectively (this example uses SQL server Authentication)
- -d specifies the default database to connect to, and [master] database is the one you would want if creating a database is part of your build process.
NB: for complete commands list see documentation. Keep in mind that UserID/Password are in clear text, and will be sent over the network as such (unless you are using DAC). To minimize amount of hard-coded use include files in your script.
Here is an example as SQL code could be organized, in order of execution (I will link script files soon):
| 1 |
exec.sql |
main controller of the database installation process |
| 2 |
constants.config |
contains declaration of all variables to be used in the script; note that file extension is irrelevant for execution |
| 3 |
backupDB.sql |
backup existing database (if present); note that backup directory must exist on remote computer |
| 4 |
createDB.sql |
create new database; note that all the paths must exist on the remote computer |
| 5 |
createTables.sql |
creates all tables in the database; it might include creation of indices and constraints as part of the script but I would advise against it because of the potential dependencies conflicts |
| 6 |
createFunctions.sql |
creates all the user-defined functions for the database; the order in which objects are created in the database is important, placing functions before [views] and [stored procedures] reflects common dependency pattern as both could use the functions. |
| 7 |
createViews.sql |
creates all views |
| 8 |
createProcedures.sql |
creates all procedures |
| 9 |
createConstraints.sql |
adds constraints to the objects: primary keys, foreign keys, indices etc. |
| 10 |
importData.sql |
if your database has static data this could be used to add it at creation time; you may want to switch 9 and 10 as your data might potentially violate constraints (e.g. orphaned records); this also could be used in unit testing strategies |
| 11 |
createUsers.sql |
add all users; this script assumes that logins are already created (if not, add script to create logins first) |
| 12 |
grantPrivileges.sql |
grant privileges to the objects (e.g. EXECUTE) |
Gotchas:
It is important to understand that GO command completes the batch execution and flushes the buffer; it makes SQLCMD “forget” everything you might have declared prior to executing the command. In the above example, all variables declared in [constants.config ] are no longer part of the script once the GO command was issued.
When creating scripts, keep in mind differences between local (Hudson) directories and remote (SQL Server) ones. The former refer to location of the SQL script files checked out by Hudson from your source control, understood by SQLCMD and Hudson only; the latter specifies directories that SQL Server understands – backup and database locations.
SQLCMD takes in arguments in clear text which constitutes potential security breach; use it in fully trusted environment. Alternative would be implement workaround such as local batch files in secure directories with hard-coded userID/Passwords, and rely on Hudson security matrix; only users with access to the server would be able to see it. This does increase maintenance butb is relatively easy to implement.
If you want SQLCMD generated messages to be displayed in Hudson console output do not specify output file. Alternatively, I could envision a plugin that would parse the output file, and present it nicely in Hudson environment; I might take a stab at it, time permitting.
The successful execution of the scripts relies on correct order of creation – you must figure out object dependencies, and factor it in your scripts. Unfortunately, this is classical Catch 22 – the reliable way to determine dependencies is to query SQL Server after the objects has been created… Which means that you ‘d have to run all the script manually first, and adjust your scripts accordingly.
Here are some clever scripts that allow for discovery of the correct sequence for stored procedures and functions.
