I'm looking for recommendations/guidance on how best to store DDL/DML and PL/SQL script in Source Control (we're using Microsoft Visual Studio TFS) for an in-house developed SaaS application.
We have a team of up to 7 developers working on a project that is based on a fairly straight-forward Dev/Main branch model. There are dependencies (primarily execution order) between scripts.
What has worked well under similar circumstances for you?
See my answer to How should you build your database from source control?. It is mainly based on the excellent K. Scott Allen series on that topic.
We kept the DDL scripts under source control and for DML, we've used something similar with this: http://dbdeploy.com/, but ours was written in Perl so it was kinda awkward.
A simplified look at a leg in our source control would be:
\DatabasePatches
\Core
\Data
\DatabaseSource
\Core
\SchemaA
\SchemaB
\SchemaC
DDL for a particular chunk of work is written and checked in under core patches with DML/migration checked in under data patches. Part of the patch label is a sequence number (manually add 10 each time allowing future insertion of patches in between) so a patch might be called "DATAPATCH01530 - migrate of xyz.sql".
When deploying to a new environment all the core patches are run and then data patches are run. If there is DML important for the next part of a core patch then it may be included in that core patch.
Once a patch has been run for the first time on a new location the file is marked FINAL in the source control (we use PVCS and lock the file with a user called FINAL) to make sure it can't be changed and cause inconsistency. Any additional changes should be included in a seperate patch.
Stored procedures, functions, packages etc. are saved and checked in under the DatabaseSource leg. You are unable to guarantee if these objects are promoted before scripts are run so we accommodate for this by creating stubs in our scripts (e.g views would be created as SELECT '1' FROM dual, packages would contain a dummy procedure) that guarantee the object exists and allows you to grant privileges etc. When the actual object gets promoted it replaces the stub and retains privileges.
You could sniff through LiquiBase (http://en.wikipedia.org/wiki/LiquiBase) LiquiBase is an open source database-independent library for tracking, managing and applying database changes. (XML-based)
It has build system, where you are able to order your scripts in your own way. All you need - is just put includes in correct order. I think It should help.
I'm going to use this tool to track database DML, PLSQL changes with GIT version control.
Why do you want to keep the DDL scripts? You can never use them again. You can't trust them with out comparing them to production. The only reason I would keep DDL (other than it sounds like a good idea) is to be able to compare full base lines by putting each scheme in a single file. This would allow you to do a diff before deployment to see the changes.
I have checked in DDL on many projects and have found that by building changes on top of the source code checkout instead of what is in prod. we lost changes.
DB is different than front-end source code. You have people wit the ability to connect to the database and make changes through sql-plus or toad to fix an emergency problem and you will not get your change into source code control. DBAs could make changes ....
In my opinion it sound good but does not work well in practice.