Why would you want to place a database under source control?
Reproducible databases for testing.
Easier to compare environments
Create a Continuous Integration environment, triggered by changes in SCM
Central location where developers make their changes.
Automated updates to databases
Extract DDL from database
Populate SCM from existing production DB
Merge manual DB changes DB into SCM (emergency fixes or hacks)
Extracting into a branch can support audit process
Build Deliverable
Creates package that applies changes to a DB
Targets that can be applied:
Clean
Removes all traces of a Project from DB.
Primarily for build and development.
Deploy
Apply Bootstrap patches
Apply baseline objects that arent re-created by an update.
Only relevant to new DB or after a Clean
Update
Apply Bootstrap patches
Apply SYS and SYSTEM Patches
Apply Patches
Apply Code
Apply GRANTS, PRIVS and SYNONYMS
Compile invalid Objects
Compile and Validation
Apply GRANTS, PRIVS and SYNONYMS
Compile invalid Objects
Validate
No invalid objects
No failed grants, privs or synonyms
A database project implies the generic layout in SCM used to manage a logical database. A common seto build scripts are used to populate the project and subsequently create a build artefact. The DDL object in a database are treated as either invariable or replacable. Invariable objects, such as tables, must be modified by patches, where as replacable objects, such as view and triggers, can be changed directly in SCM.
More often than not, there is a production database that needs to be managed, this is refered to as the GOLD database as it is always taken as "correct".
EXPORT/IMPORT: There needs to be a way or extracting the database contents and importing it into SCM. This includes DDL and DATA (segregated into reference and business data)
TOKENISE: Many of the exported objects can have database instance specific content e.g. tablespace names, directory paths etc. These need to be tokenised so that they can be replaced by instance specific content (A production environment is likely to be set up quite differently to a test environment).
MD5: For automated comparison of databases, an MD5 of the exported object can be useful (The tokenisation mentioned above helps to make the files consistent). To ensure that code formatting doesnt skew the result, all white space is removed and the characters transformed to lower case before the MD5 is calculated.
Building the Database Project results in a Build Artefact. This contains a complete instance of the the database at the point of the build and can be used to create a new database or update and existing one.
The build artefact should be uploaded to a central location (e.g. Nexus or a fileserver), so that it can automatically be applied to environments by a build system such as Jenkins.
A build artefact has a set of embedded targets that can be used to modify a database. Some can be used to tear down an existing database and re-build it, others are to update an existing database.
clean: Delete the working files from previous target invocations
nuke: Delete the contents of the database that this build artefact creates
deploy: Apply invariable objects from gold database e.g. tables and constraints
update: Apply replaceable objects e.g. procedures and triggers