Thursday, September 16, 2010

More work on WshBuild?

A while ago I posted about a little project I was working on for work called WshBuild. I’ve slowly been making tweaks to it as I need them at work. I have a couple of addins, one especially for MS Access.  This was the primary reason why I wrote it in the first place, to automate my build process for one of the databases I am in charge of.  I use the Windows Script Host because it makes COM automation a little easier for me. That allows me to control an instances of Access to perform things like mde/ade file generation, compacting databases, and other things.

What it does now

At the moment it is very much geared towards only my current project. It can handle task dependencies (only calling a dependent task once even if several tasks require it to run), mapping paths, playing with the file system (via the Scripting.FileSystemObject), and working with Access.
For the Access part, it can compact/repair a database, get/set project properties, compile an mde/ade from a mdb/adp file, and export all of the objects in the database to text files*.  This last bit is the interesting point of today. I effectively have all of the source of the database exported to text files so that I can get diff support from git.  It makes it really easy for me to review changes and allows me to not have to include the actual database files into source control.

What I am thinking of adding

It is all well and good to have these text files in source control. But if I ever need to revert to a previous version, it is going to be a painstaking process. I would have to first revert the source, then manually change the database to reflect the state of the text files.
So my idea is this: what if the build system could generate the database for me? I could add functionality to WshBuild to create a new database file and import all of the objects one at a time until the database was completely reconstructed. I would be able to revert to a specific commit and then just regenerate the database file.
In order to get this to work, I would run in to a few obvious issues. First, I would need some additional metadata about the database that I don’t currently track  (is it an mdb or adp file?). Second, my databases tend to come with a number of linked tables. I need to have a way to handle this as well. How would the build system know the dependency chain for the files? It is only one deep, but it would still need to know which files to generate first.

Should I bother?

The final question on my mind is: should I even bother with this? Windows Script Host is not exactly cutting edge technology. And there are much better build systems out there. I am only refusing to use things like NAnt or MSBuild because I would have to do a lot of work to get Access automation to work. Also, I can at least guarantee that WSH is installed on all of our workstations.
Trouble is, when/if we upgrade our systems in the future, Vista and Win7 both come with powershell. Once I have that in place, I can easily just use psake and use the .NET COM wrappers for Office. This would give me a tried and true, supported build system to work with, and the ease of automation that I desire. I could then work on my export/regenerate methods as an extension to psake.  Suggestions?
* This isn't 100% accurate. While it can export nearly all objects, it does not currently handle relationships, and it cannot handle tables and queries in an adp file. It also doesn't do project or database level properties. Properties and relationships aren't handled because I haven't needed them yet. Project (adp) file tables and queries are stored in SQL Server, so I haven't decided on a good way to handle this. So for now I use SSMSE to manually export the DDL for these.

No comments: