Database version control plan: hot or not? -
based on reading around web, stack overflow, , these articles db versioning linked coding horror, i've made stab @ writing plan versioning database of 8 year old php mysql website.
database version control plan - create db "master database" - create table db_version (id, script_name, version_number, author, comment, date_ran) - create baseline script schema+core data creates db scratch, run on master db - create "test data" script load db working data - modifications master db made through db versioning process - ensure developing against master db has local db created baseline script - procedures commiting , updating master db - master db commit - perform schema diff between local db , master db - perform data diff on core data between local db , master db - if there changes in either or both cases, combine these changes update script - collect data added new row in db_version table, , add insert script - new version number = latest master db version number +1 - author - comment - script must named changescript_v.sql v latest master db version +1 - run script against master db - if script executed succesfully, add svn repository - add new db_version record local db_version table - update master db - update local svn checkout have latest change scripts available - compares local db_version table master db_version table determine change scripts run - run required change scripts in order against local db, update local db_version table my first question is, sound correct?
second question is, commit process seems bit complicated more once day. there way reliably automate it? or should not commiting database changes enough matter?
looking @ proposals, doesn't seem that's feasible nor practical. working in company used more 1k tables per database (very complex system), , worked fine this:
- have 1 person in charge of db (lets call him dbperson) - every script/db change has pass through him. avoid unnecessary changes, , 'overlooks' of issues (for example, if moves index perform better query, hi might destroy other persons work, maybe create table redundant , unnecessary, etc...). keep db clean , efficient. if seems work 1 guy (or deputy), in fact isn't - db changes.
- each script has pass validation through dbperson
- when script approved, dbperson assigns number , puts in 'update' folder/svn(...), appropriate numbering (as suggested, incremental numbers example).
- next, if have continuous integration in place, script gets picked , updates db (if don't have continuous integration, manually).
- do not store entire database script, data in script. store actual database instead. if have branches of solution - have each branch it's own database, or can have update scripts divided each of branches rollback/forward branch. but, recommend have separate db each branch.
- have 1 database default data (intact) - needs of unit tests, regression tests etc. whenever tests, them on copy of database. put nightly cleanup of test databases main 1 (if appropriate of course).
in environment you'll have multiple versions of database:
- developers database (local) - 1 dev guy using test work. can copy master or test master.
- master database - 1 default values, maybe semi-empty if you're doing redeploys new clients.
- test master database - master database filled test data. scripts have ran on master ran here well.
- test in progress database - copied test master , used testing - gets overwritten prior new test.
- if have branches (similar database slight difference each of clients) you'll have same above each branch...
you have make modifications of match situation, anyway think keeping textual version of create script entire database wrong in terms of maintainability, merging, updating, etc...
Comments
Post a Comment