Take the 2-minute tour ×
Programmers Stack Exchange is a question and answer site for professional programmers interested in conceptual questions about software development. It's 100% free, no registration required.

We run an internal web server with in-house software to run a manufacturing line.

When new product features are to be added, either or both of the following occur:

  1. changes to the in-house server software may be required to support these - these are for significant changes in functionality, being code drive.
  2. changes to the MySQL database for new entries for the part numbers, these are for smaller changes, configurations, changes to already existing values and parameters -- such changes don't require code changes. Ideally we'd want our changes to be here rather than in item 1.

Item 1 is version controlled in Subversion, so previous revisions can be referred to for rolling back to in the event of problems introduced in the latest revision.

But what about changes to the MySQL database?

We have quality processes to ensure that such changes are error-free but there is always a chance that errors can pass through, e.g. mistake in data entry or faults with the code that uses the MySQL corrupting the database etc.

We have a automated backup every 6 hours but what if we want more manual defined checkpoints in between these intervals, we could use the same backup system but I wondered if folks here used other methods to store previous states of databases, e.g. exporting the database as a plain text SQL dump -- at least with this method it would be possible to see diffs e.g. in Beyond Compare for trouble shooting.

Thoughts?

share|improve this question
add comment

3 Answers

up vote 2 down vote accepted

Each of our branches has a private folder, inside there we store the patch files for any SQL changes, both the SQL script and a back out script in case the issue needs to be rolled back.

Upon release the patches for all the development branches that are part of a release are tested together to ensure there are no conflicts.

share|improve this answer
    
That's what I would be looking for - something independent of the database. Contrast with an in-situ version control that is not immune to a faulty piece of code corrupting the whole database including the version history. –  therobyouknow Feb 1 '11 at 19:27
    
+1 This approach gives fine granularity but still the same safety of controlling the whole database stage outside of the database itself. Safety because the database itself isnt used to version control itself - it could get corrupted, rather this is done externally. Fine granularity because each change can be recognised rather than a complete snapshot containing everything. Also because this is a working solution - it must be some feet to get the "undo" side of a change working. –  therobyouknow Feb 1 '11 at 19:33
add comment

For the particular project (a LAMP application) that I am currently working on, I have 2 Subversion repositories. I have a Project repository, which contains the source code, and another "support" SVN repository. The Support SVN repository contains files that help me with the implementation of the application, but aren't used by the application. I keep Database schema diagrams, documents listing my global variables, and every now and then database backups to the support SVN repository. This way, I can overwrite the last backup but still get that back if I need to. This also keeps things less clutered - I just need to have one file that keeps changing for DB backups.

share|improve this answer
add comment

We created our own history/version tracking system for the data in the database, analogous to the answers here:

http://stackoverflow.com/questions/323065/how-to-version-control-a-record-in-a-database

share|improve this answer
    
+1 I like this solution because it has merit in situations where the previous versions of database items need to be directly accessible by the application that uses it, perhaps even making these available to the end user. Not quite what I am looking for as I'm looking for something outside of the database to manage its history as this has the advantage that if the whole database was trashed or corrupted by faulty code using it, then the "offline" external version control system can be used to recover previous editions of the whole dataset. –  therobyouknow Feb 1 '11 at 19:30
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.