I have started on a new project and am looking forward to implementing continuous integration (CI).  There are quite a few decisions that need to be made as far as how we want to handle the build and deployment process.  I am working with a small group of people who have worked together in the past.  They have some practices that have worked well for them.  I don't want to rock the boat too much, so I am evaluating their process with what I know and am familiar with myself.  The biggest difference between us... Java for them and .Net for me.

The first significant divergence is related to database change management.  They have used a process maintaining change scripts for their clients while updating the creation scripts for source control.  This allows them to easily query their source control system for a specific revision and build both the application and the database.  Personally, it feels like there is extra work involved in updating the creation scripts.  I like the idea of storing the change scripts (including data manipulation to accommodate updating schemas) in source control and letting the build tool of choice execute the scripts in the proper order.

I will be working with the team to determine what makes the most sense for us.  If there are no compelling reasons to change this aspect of the build process, then I will leave it alone.  Another consideration is making it easy for developers to submit change scripts in reference to user story or defect ids.  The team already has something similar for their previous projects but I think that it is specific to a client.

If anyone has experience with either of these approaches (or with other approaches altogether) feel free to provide feedback.  Expect an update in a couple of days.

posted on Thursday, January 17, 2008 4:18 PM
Filed Under [ Builds & CI Database ]

Comments

Gravatar
# re: Snapshot or Change Scripts?
posted by Michael Cline
on 1/17/2008 9:45 PM
In a previous life, I also used a system of the 'continuous change script' methodology. When dealing with databases there are a few different areas:
Schema
Stored Procedures
Data
In the environment that I am referring to we were building an application that was installed at the client site. As we were a start-up as far as development went we had many releases without necessarily a definate schedule. Customer's wouldn't all go to the latest version and customer's may skip versions between upgrades. Because of this we needed to be able to go from any version to any later version.
For stored procedures every installation/upgrade dropped and recreated all of the stored procs and granted security to them. This guaranteed that at the time of the upgrade the procs were in sync with the code that was deployed.
For schema and data we internally used a database where every change was assigned an incremental number. A developer would play around with the changes that needed to be made locally and then they would create a change script. Our requirements were that each change script had a 'precondition' which could to the best of our ability prove the change was necessary, and then the actual script that changed the database. So for example:
Precondition --
IF NOT EXISTS (SELECT 1 FROM SYSOBJECTS SO JOIN SYSCOLUMNS SC ON SO.id = SC.id WHERE SO.name = 'table' and SC.name = 'columntoadd')
RETURN 1

Change Script --
ALTER TABLE table ADD columntoadd INT NOT NULL

We then also had a table in the database where we would set the database version number to the number that corresponded with the change. So:

UPDATE databaseversion SET version = 120

This meant that at any time you could take the tool to a database and tell it to run. It would see the version that the database appeared to be on (120) and start with change 121... to the end. The precondition just allowed another safety net for us.

Over time there were obviously some changes that could have been removed from the script. Change 101 adds a column, change 205 moves the column to a different table so it is dropped from the original table. But the only time that these 'unnecessary' changes run is on a new install and at that point, who cares if it runs 100 steps or 500 steps. All that you care is that the end result is the result you needed. If you wanted you could always 'recreate the baseline' at a point that you knew was the lowest common denominator of all of your customers. That takes time that we didn't see the value in spending.

Constant change scripts added a development overhead during the process, but made upgrading any customer the same process. Run the tool that ran all of the scripts. If someone tried to run the upgrade twice there was no harm because it was based on the version it thought the database was so if all went well it would be at 300 when the max change version it had was 300.

So, for what it's worth. We maintained a database of changes internally and then whenever a version was tagged we extracted the changes for that point in time into a file that could be put in source control.

Post A Comment
Title:
Name:
Email:
Comment:
Verification: