[ACCEPTED]-Is there a version control system for database structure changes?-version-control

In Ruby on Rails, there's a concept of a 15 migration -- a quick script to change the database.

You 14 generate a migration file, which has rules 13 to increase the db version (such as adding 12 a column) and rules to downgrade the version 11 (such as removing a column). Each migration 10 is numbered, and a table keeps track of 9 your current db version.

To migrate up, you run a command 8 called "db:migrate" which looks 7 at your version and applies the needed scripts. You 6 can migrate down in a similar way.

The migration 5 scripts themselves are kept in a version 4 control system -- whenever you change the 3 database you check in a new script, and 2 any developer can apply it to bring their 1 local db to the latest version.

I'm a bit old-school, in that I use source 23 files for creating the database. There 22 are actually 2 files - project-database.sql 21 and project-updates.sql - the first for 20 the schema and persistant data, and the 19 second for modifications. Of course, both 18 are under source control.

When the database 17 changes, I first update the main schema 16 in project-database.sql, then copy the relevant 15 info to the project-updates.sql, for instance 14 ALTER TABLE statements. I can then apply 13 the updates to the development database, test, iterate 12 until done well. Then, check in files, test 11 again, and apply to production.

Also, I usually 10 have a table in the db - Config - such as:

SQL

CREATE TABLE Config
(
    cfg_tag VARCHAR(50),
    cfg_value VARCHAR(100)
);

INSERT INTO Config(cfg_tag, cfg_value) VALUES
( 'db_version', '$Revision: $'),
( 'db_revision', '$Revision: $');

Then, I 9 add the following to the update section:

UPDATE Config SET cfg_value='$Revision: $' WHERE cfg_tag='db_revision';

The 8 db_version only gets changed when the database is 7 recreated, and the db_revision gives me an indication 6 how far the db is off the baseline.

I could 5 keep the updates in their own separate files, but 4 I chose to mash them all together and use 3 cut&paste to extract relevant sections. A 2 bit more housekeeping is in order, i.e., remove 1 ':' from $Revision 1.1 $ to freeze them.

MyBatis (formerly iBatis) has a schema migration, tool for use 6 on the command line. It is written in java 5 though can be used with any project.

To achieve 4 a good database change management practice, we 3 need to identify a few key goals. Thus, the 2 MyBatis Schema Migration System (or MyBatis 1 Migrations for short) seeks to:

• Work with any database, new or existing
• Leverage the source control system (e.g. Subversion)
• Enable concurrent developers or teams to work independently
• Allow conflicts very visible and easily manageable
• Allow for forward and backward migration (evolve, devolve respectively)
• Make the current status of the database easily accessible and comprehensible
• Enable migrations despite access privileges or bureaucracy
• Work with any methodology
• Encourages good, consistent practices

Redgate has a product called SQL Source Control. It integrates 2 with TFS, SVN, SourceGear Vault, Vault Pro, Mercurial, Perforce, and 1 Git.

I highly recommend SQL delta. I just use it to generate 4 the diff scripts when i'm done coding my 3 feature and check those scripts into my 2 source control tool (Mercurial :))

They have 1 both an SQL server & Oracle version.

I wonder that no one mentioned the open 10 source tool liquibase which is Java based and should 9 work for nearly every database which supports 8 jdbc. Compared to rails it uses xml instead 7 ruby to perform the schema changes. Although 6 I dislike xml for domain specific languages 5 the very cool advantage of xml is that liquibase 4 knows how to roll back certain operations 3 like

<createTable tableName="USER"> 
   <column name="firstname" type="varchar(255)"/>
</createTable>

So you don't need to handle this of 2 your own

Pure sql statements or data imports 1 are also supported.

Most database engines should support dumping 5 your database into a file. I know MySQL 4 does, anyway. This will just be a text file, so 3 you could submit that to Subversion, or 2 whatever you use. It'd be easy to run a 1 diff on the files too.

If you're using SQL Server it would be hard 11 to beat Data Dude (aka the Database Edition 10 of Visual Studio). Once you get the hang 9 of it, doing a schema compare between your 8 source controlled version of the database 7 and the version in production is a breeze. And 6 with a click you can generate your diff 5 DDL.

There's an instructional video on MSDN that's 4 very helpful.

I know about DBMS_METADATA 3 and Toad, but if someone could come up with 2 a Data Dude for Oracle then life would be 1 really sweet.

For Oracle, I use Toad, which can dump a schema 5 to a number of discrete files (e.g., one 4 file per table). I have some scripts that 3 manage this collection in Perforce, but 2 I think it should be easily doable in just 1 about any revision control system.

Have your initial create table statements 9 in version controller, then add alter table 8 statements, but never edit files, just more 7 alter files ideally named sequentially, or 6 even as a "change set", so you can find 5 all the changes for a particular deployment.

The 4 hardiest part that I can see, is tracking 3 dependencies, eg, for a particular deployment 2 table B might need to be updated before 1 table A.

Take a look at the oracle package DBMS_METADATA.

In 7 particular, the following methods are particularly 6 useful:

• DBMS_METADATA.GET_DDL
• DBMS_METADATA.SET_TRANSFORM_PARAM
• DBMS_METADATA.GET_GRANTED_DDL

Once you are familiar with how they 5 work (pretty self explanatory) you can write 4 a simple script to dump the results of those 3 methods into text files that can be put 2 under source control. Good luck!

Not sure 1 if there is something this simple for MSSQL.

I write my db release scripts in parallel 8 with coding, and keep the release scripts 7 in a project specific section in SS. If 6 I make a change to the code that requires 5 a db change, then I update the release script 4 at the same time. Prior to release, I run 3 the release script on a clean dev db (copied 2 structure wise from production) and do my 1 final testing on it.

I've done this off and on for years -- managing 16 (or trying to manage) schema versions. The 15 best approaches depend on the tools you 14 have. If you can get the Quest Software 13 tool "Schema Manager" you'll be in good 12 shape. Oracle has its own, inferior tool 11 that is also called "Schema Manager" (confusing 10 much?) that I don't recommend.

Without an 9 automated tool (see other comments here 8 about Data Dude) then you'll be using scripts 7 and DDL files directly. Pick an approach, document 6 it, and follow it rigorously. I like having 5 the ability to re-create the database at 4 any given moment, so I prefer to have a 3 full DDL export of the entire database (if 2 I'm the DBA), or of the developer schema 1 (if I'm in product-development mode).

PLSQL Developer, a tool from All Arround 10 Automations, has a plugin for repositories 9 that works OK ( but not great) with Visual 8 Source Safe.

From the web:

The Version Control 7 Plug-In provides a tight integration between 6 the PL/SQL Developer IDE >>and any 5 Version Control System that supports the 4 Microsoft SCC Interface Specification. >>This 3 includes most popular Version Control Systems 2 such as Microsoft Visual SourceSafe, >>Merant 1 PVCS and MKS Source Integrity.

http://www.allroundautomations.com/plsvcs.html

ER Studio allows you to reverse your database schema 12 into the tool and you can then compare it 11 to live databases.

Example: Reverse your 10 development schema into ER Studio -- compare 9 it to production and it will list all of 8 the differences. It can script the changes 7 or just push them through automatically.

Once 6 you have a schema in ER Studio, you can 5 either save the creation script or save 4 it as a proprietary binary and save it in 3 version control. If you ever want to go 2 back to a past version of the scheme, just 1 check it out and push it to your db platform.

There's a PHP5 "database migration 5 framework" called Ruckusing. I haven't 4 used it, but the examples show the idea, if you 3 use the language to create the database 2 as and when needed, you only have to track 1 source files.

We've used MS Team System Database Edition with pretty good success. It 8 integrates with TFS version control and 7 Visual Studio more-or-less seamlessly and 6 allows us to manages stored procs, views, etc., easily. Conflict 5 resolution can be a pain, but version history 4 is complete once it's done. Thereafter, migrations 3 to QA and production are extremely simple.

It's 2 fair to say that it's a version 1.0 product, though, and 1 is not without a few issues.

You can use Microsoft SQL Server Data Tools in visual studio to generate 9 scripts for database objects as part of 8 a SQL Server Project. You can then add the 7 scripts to source control using the source 6 control integration that is built into visual 5 studio. Also, SQL Server Projects allow 4 you verify the database objects using a 3 compiler and generate deployment scripts 2 to update an existing database or create 1 a new one.

In the absence of a VCS for table changes 5 I've been logging them in a wiki. At least 4 then I can see when and why it was changed. It's 3 far from perfect as not everyone is doing 2 it and we have multiple product versions 1 in use, but better than nothing.

I'd recommend one of two approaches. First, invest 24 in PowerDesigner from Sybase. Enterprise Edition. It 23 allows you to design Physical datamodels, and 22 a whole lot more. But it comes with a repository 21 that allows you to check in your models. Each 20 new check in can be a new version, it can 19 compare any version to any other version 18 and even to what is in your database at 17 that time. It will then present a list of 16 every difference and ask which should be 15 migrated… and then it builds the script 14 to do it. It’s not cheap but it’s a bargain 13 at twice the price and it’s ROI is about 12 6 months.

The other idea is to turn on DDL 11 auditing (works in Oracle). This will create 10 a table with every change you make. If you 9 query the changes from the timestamp you 8 last moved your database changes to prod 7 to right now, you’ll have an ordered list 6 of everything you’ve done. A few where clauses 5 to eliminate zero-sum changes like create 4 table foo; followed by drop table foo; and 3 you can EASILY build a mod script. Why keep 2 the changes in a wiki, that’s double the 1 work. Let the database track them for you.

Schema Compare for Oracle is a tool specifically 5 designed to migrate changes from our Oracle 4 database to another. Please visit the URL 3 below for the download link, where you will 2 be able to use the software for a fully 1 functional trial.

http://www.red-gate.com/Products/schema_compare_for_oracle/index.htm

Two book recommendations: "Refactoring 9 Databases" by Ambler and Sadalage and 8 "Agile Database Techniques" by 7 Ambler.

Someone mentioned Rails Migrations. I 6 think they work great, even outside of Rails 5 applications. I used them on an ASP application 4 with SQL Server which we were in the process 3 of moving to Rails. You check the migration 2 scripts themselves into the VCS. Here's 1 a post by Pragmatic Dave Thomas on the subject.