Back in the early days of my career, I worked on quite a few projects that had a relatively efficient process for developing software. However, when it came to the database, we seemed to throw out all good judgment and retreat to the days without configuration management by using a "throw it over the wall" approach to managing the changes in the database. There was an understanding that the DBAs were doing “their thing” and we (the developers) were doing “our thing”. In those days, as developers, it was seldom that we could recreate the database on our machine using Data Definition Language (DDL) and Data Manipulation Language (DML) scripts because they either
- Jayne (Developer): “I'm experiencing a lot of problems testing on v1.3.1.b1 of the database using build 418”
- Mike (DBA): “When using build 418, you should use v1.3.1.b2, but I also need to make a few changes to it first”
- Jayne: “I just spent three hours for no reason”
- Mike: “You should have asked me first”
- Did not create the DDL/DML scripts or
- Did not check them into the SCM repository. Instead, we would be at the whim of the DBA if we needed any kind of change to the database.
Fortunately, some of us have awoken to what we could have been doing all along: integrating our DDL and DML into our build process to ensure we are working off the same schema and test data as our fellow developers and DBAs.
To effectively integrate your database definition and manipulation into the build process, you must establish the following: DDL scripts, DML scripts, an SCM tool, a local database, and build script(s). Each is described in more detail below.
DDL
DDL is just a fancy acronym for SQL that creates your database schema. Typically, you will see code like this in a DDL script:
CREATE TABLE beer(name VARCHAR(50), brewer VARCHAR(50), date_received DATE);
DML
DML is another one of them highfalutin acronyms for SQL that means inserting, updating, and removing (etc.) data in your database. You'll find this type of code in your DML:
INSERT INTO beer(name, brewer, date_received) VALUES ('Sam Adams Lager','Boston Beer Company','2005-12-09');
Just remember, we're talking about test data here; you're not going to be inserting millions of records into your local database. This is for another time and place.
SCM Tool You need to manage your DDL and DML just like you would your source code, so you'll need a source code management tool such as Subversion, ClearCase, Perforce or whatever you're using at your shop. You will check in the DDL and DML scripts that will recreate your database schema and insert all of the test data.
Local Database You need a local version of your database running so that your can run the DDL and DML scripts. If you are running this database on your developer workstation, you will probably want a lightweight version of your database so that you don't degrade your machine's performance. If you're using Oracle, look into Oracle Express or McKoi as lightweight options. If you're using SQL Server, then SQL Server Express is a viable option. If you're using MySQL, you're in luck, as you can use MySQL locally.
Build Scripts Now that you've got your DDL/DML scripts, a local database, and you are managing your database scripts in your SCM tool, you're ready for the final step: making your database definition/manipulation as a part of your build process. In makes no difference whether you are using Ant, NAnt, make or other build tool just as long as the script is run as part of a standard process of developing and building your software. An example in Ant and MySQL is listed below.
<target name="db:create-database" description="Create DB and Insert data">
<echo>Creating database...</echo>
<sql driver="com.mysql.jdbc.Driver" url="jdbc:mysql://localhost:3306/brewery" userid="sam" password="@d@ms" classpathref="db.lib.path" delimiter=";">
<fileset file="${db.dir}/create-database.sql">
</fileset>
</fileset>
Comments