All,
What I have done in the past is done this in two separate ways.
I’m not sure which I prefer at this stage but I think it is the second as
you get a nice history of the changes that have occurred against the a database
and it supports multiple teams developing against the same database. That
being said the first is easiest to implement.
The
first current process uses numbered change scripts which at release time are
compared against the databases. For example say that
the release directory contains 4 scripts and the table in the database contains
the version number 2. The directory would look like this:
o Script1.sql
o Script2.sql
o Script3.sql
o Script4.sql
At the time release
is performed scripts 3 and 4 will be executed against the database. It is
pretty simple to run write an app to do that.
The second way is a
little bit more complicated. If you think about the changes that
can occur to a database then they come in three broad categories:
o Schema Changes; Changes where
tables,views,udt's are added, modified or deleted.
o Data Changes; Changes where
reference data is added, modified or deleted
o Code Changes; Changes where
functions, stored procedures and triggers are added, modified or deleted
Of the changes they
pretty much need to be applied in that order, Schema changes need to be applied
before data and code changes. What I have then done is in the
source code control system have one file per database object. For
example if you have table called Account then I would have an corresponding
file that contains the statements required to create the account table.
Similarly for stored procs etc I would also have one file per stored proc.
To make the process simpler I separated out the creation of foreign keys for a
table also out into their file. This then allowed me to do the following
1.
Run
the table changes against the database
2.
Apply
the foreign keys changes against the database
3.
Apply
the data changes
4.
Apply
the stored proc changes.
Finally, in order to
know what files to apply I had a simple text file that listed the names of the
files that need to be applied against the database for a given release.
(We were looking at automating this based on what files have changed from a
given label).
One last thing I
should mention is that on schema, foreign key and data changes we always did
and if exists test to see if the object existed or not.
Hope that helps some
thinking on the subject.
Regards,
Chris Burrows
Readify - Senior Consultant
M: +61 404 254 654 | C: [EMAIL PROTECTED]
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] On Behalf Of Jeremy Thake
Sent: Tuesday, 19 September 2006 11:05 AM
To: [email protected]
Subject: Re: [OzTFS] Database Integration in Builds
I've done a few projects now
with Continuous Integration including rebuilding the database from scripts each
time.
It is really great in a big team where you do this because it enforces that
people always put their changes in the stored procudure scripts, table creation
scripts, data generation scripts etc. rather than making changes directly to
their own developer instance of the database and then wondering why no one else
can get an environment up and running.
It also means that it is really easy to get someone up and running in a
development environment because you just run the scripts locally. We all know
how much of a pain it is hunting things down to build a project don't we!
I use CruiseControl.Net at the moment with NANT calling the MS SQL command
line. What way were you looking to execute the scripts in TFS? We found the
tricky thing was firing the scripts in the correct order due to dependencies on
other stored procedures etc. and obviously running TABLE creates before VIEWS
etc.
Be great to see what others do,
Jeremy Thake
On 9/19/06, Mitch Denny <[EMAIL PROTECTED] > wrote:
Hi Christian,
Cool. I like the idea of having
a suite of ordered tests which build up the database incrementally, including
adding schema, sample data, schema, sample data. So each unit test would
represent a new version of the database.
Hi Mitch
We have just started this project. The idea is create builds where code and
database are created from source control and the database is deployed as part
of the out put.
We want to be able to create
releases where the database schema and code are a part of the release and
enable us to always get a working copy of the database. We have used Speed SQL
pro for database source control, but are looking at the TFS Team Database
edition as replacement.
While we are waiting to see what
TFS Database can do for us :), we are creating a custom build task, which
collects SQL statement form Source control and creates the database.
Hi Christian,
I'm interested to hear your experiences around integrating database
operations into your build. I kind of like the idea of the build
bringing everything it needs with it and only giving it things like a
connection string to get started. Whereby it builds the database up from
scratch.
-----Original Message-----
From: [EMAIL PROTECTED]
[
mailto:[EMAIL PROTECTED]] On Behalf Of
Christian Gram
Sent: Monday, 18 September 2006 9:39 PM
To: [email protected]
Subject: RE: [OzTFS] Introductions
Name: Christian Gram
Location: Copenhagen, Denmark.
Employer: SoftwarePeople
TFS experience (pick some): Installation, maintenance, proxy server,
Extending, Process templates, Builds, Project server integration
Current projects: Database integration in builds
Favorite TFS tools (with links):
-----Original Message-----
From: [EMAIL PROTECTED]
[
mailto:[EMAIL PROTECTED]] On Behalf Of
Grant Holliday
Sent: Monday, 18 September 2006 5:44 PM
To: [email protected]
Subject: [OzTFS] Introductions
Hi again,
What I thought would be a good way to kick of the list is to start
with a round of introductions. So if you just want to reply to the
list with a bit about yourself, that would be great!
(I'll even give you a template to start with, just copy and paste)
------
Name:
Location:
Blog:
Employer:
TFS experience (pick some): Installation, Process Guidance, Extending
TFS, Team Build, Building Teams, Reporting, Anything else..
Current projects:
Current goals:
Favorite TFS tools (with links):
------
Name: Grant Holliday
Location: Canberra, Australia
Blog: http://www.holliday.com.au/
Employer: Readify (www.readify.net)
TFS experience:
* Installation
* Extending TFS
* Migrating from SourceSafe
* TFS MSSCCI Provider
* Reporting
Current projects:
I'm currently working for a large australian government department and
migrating their 3Gb SourceSafe database into TFS. They are still on
1.1, so I've been using the MSSCCI Provider in anger and I've been
building a web-wrapper around the TFS API to facility user permissions
for the 300+ developers they have.
Current goals:
* I'd like to learn more about reporting using the TFS cube
* I'm looking to become MCT certified so that I can deliver some TFS
training
* And of course, building a community :)
Favorite TFS tools:
* The new TFS power tools are my favourite. tfpt.exe history
/followbranches is neat
http://blogs.msdn.com/bharry/archive/2006/09/07/744993.aspx
* I have found the TFS Sidekicks from Attrice is handy for
viewing/deleting workspaces in a large developer environment.
http://www.attrice.info/cm/tfs/
* and of course, my own little tools (TFS Bug Snapper,
TfsDownloadFile, TfsGroupMemberImport, TfsListProjectAreas) - all of
which are invaluable while migrating this mammoth VSS database.
http://www.projectdistributor.net/Groups/Group.aspx?groupId=80
So that's me, how about you?
Grant
OzTFS.com - to unsubscribe from this list, send a message back to the
list with 'unsubscribe' as the subject.
Powered by mailenable.com -
List managed by www.readify.net
OzTFS.com - to unsubscribe from this list, send a message back to the
list with 'unsubscribe' as the subject.
Powered by mailenable.com -
List managed by www.readify.net
OzTFS.com - to unsubscribe from this list, send a message back to the
list with 'unsubscribe' as the subject.
Powered by mailenable.com -
List managed by www.readify.net
OzTFS.com - to unsubscribe from this list, send a message back to the list with
'unsubscribe' as the subject.
Powered by mailenable.com -
List managed by www.readify.net
OzTFS.com - to unsubscribe from this list, send a message back to the list
with 'unsubscribe' as the subject.
Powered by mailenable.com -
List managed by www.readify.net
OzTFS.com - to unsubscribe from this list,
send a message back to the list with 'unsubscribe' as the subject.
Powered by mailenable.com
- List managed by www.readify.net
OzTFS.com - to unsubscribe from this list, send a message back to the list with
'unsubscribe' as the subject.
Powered by mailenable.com - List managed by www.readify.net
OzTFS.com - to unsubscribe from this list, send a message back to the list with 'unsubscribe' as the subject.