Loading ...
Sorry, an error occurred while loading the content.

Schema Versioning

Expand Messages
  • Alexander Karmanov
    Hi, I ended up with my own tool too :-) There were no tools that would suit my needs. My tool is based on a single change log and a set of configuration files
    Message 1 of 41 , Aug 25, 2010
    • 0 Attachment
      Hi,



      I ended up with my own tool too :-)

      There were no tools that would suit my needs.



      My tool is based on a single change log and a set of configuration files
      which combine various scripts.

      Structure changes are coded as separate files, programmables are created as
      regular routine re-creation (drop+create) scripts.

      Both are registered in the change log in which set of changes is labeled
      with version.

      Database version is preserved in itself. Deployment tool locates current
      version in the change log and applies all the scripts from the subsequent
      entries.

      Fresh db creation is possible too - database schema creation script is
      created from the incrementally built one, routines are taken from the same
      files as they were used in the incremental build(s).



      I described it once here:
      http://www.simple-talk.com/sql/database-administration/deploying-database-de
      velopments/

      Please mind the scripts attached to the article were not quite finished.
      I've polished them since then.



      As I can tell from discussion with other people who more-or-less
      successfully resolved this issue - all their solutions were built on some
      kind of the change log.

      That is - they are pretty close to mine :).

      Unfortunately they all were proprietary solutions, so would not be
      distributed.



      HTH,

      Alex



      [Non-text portions of this message have been removed]
    • timander37
      I created a simple project that demonstrates how we track database changes. The source code can be found here:
      Message 41 of 41 , Sep 5, 2010
      • 0 Attachment
        I created a simple project that demonstrates how we track database changes.

        The source code can be found here:
        http://github.com/timander/schema-versioning-example

        Please try it out and share your opinions/suggestions.

        Tim Andersen



        --- In agileDatabases@yahoogroups.com, "timander37" <timander@...> wrote:
        >
        > We have a solution for database schema versioning, though I'm not sure how well it supports branching. Is the branching for separate production databases or for different versions of the database in different environments along the development lifecycle (Dev, QA, Prod)?
        >
        > I refer to this as "poor man's migrations" because it is like how Rails does it, though we were using this solution before Rails Migrations. The idea of versioning the schema was inspired by both Martin Fowler's article http://martinfowler.com/articles/evodb.html and Scott Ambler's book "Agile Database Techniques."
        >
        > 1. Our build always executes a "nuke & pave" of the database.
        > 2. We have a tool to extract all of the data from a database to SQL insert scripts.
        > 3. We have a table called "schema_version" that keeps a list of all of the scripts that have been applied.
        > 4. Each script has an "insert into schema_version values ('name_of_script')"
        > 5. We have a JUnit test that verifies all of the SQL scripts in our "changes" directory have been accounted for in our schema_version table.
        >
        > The DBA's then have a tool-independent way to identify which changes have been applied to each database.
        >
        > We have also experimented with a database metadata test that creates a Hypersonic in-memory database from the SQL scripts and compares the metadata of the in-memory database with the real database. Keeping the SQL Hypersonic-compatible was a lot of overhead and we ended up taking this out because our schema version tracking worked well.
        >
        > I'd like to hear how other people are keeping track of their database version.
        > Tim Andersen
        >
        >
        >
        >
        > --- In agileDatabases@yahoogroups.com, "emanuelbulic" <emanuelbulic@> wrote:
        > >
        > > Hello all,
        > >
        > > I have been looking for a solid approach to database schema versioning for some time, and, ended up writing my own versioning tool. I wanted something similar to a source code repository. It must be version agnostic, and must have support for branching (multiple lineages). It should also treat table alterations and programmables appropriately.
        > >
        > > Any advice?
        > >
        >
      Your message has been successfully submitted and would be delivered to recipients shortly.