Poll: How do you version control your schema?

[#1]

We're a long time SQL Anywhere+Delphi development shop. A lot of our developers have come up with their own simple implementations of how to manage the version of a SQL Anywhere database schema with its related delphi application(s). However we're wanting to standardise how we do this, so thought we'd use this fantasic resource.

By manage, I mean:

• For this particular version of our application in our version control system this is the version of the database schema it is designed to work with.
• Using version control to track the change of a table, view, sproc, etc. over time.
• Match changes to the schema to the related changes to the application. E.g. we added purchase ordering to our application, what specific changes to the database were required.

So, what do other SQL Anywhere developers use to version control their schema?

[thomas_duemesnil]

We use PowerDesigner to manage the schema. The CDM and PDM Files are in a classical CVS Repository.

Each Procedures, Function and View is stored a SQL Script.

Changes to the Database are always done by a update script. This scipt uses the read statement to load the scripts and updates the schema. Complex DDL (aka add fields, new tables ) is also scripted in the update file. Each update script is intended to update a database from version x to y. If you have a Database Version v you have to run update w, x and y to get to the current version.

All sql scripts are hold in a CVS Repository.

When we release a new version of the client Application we tag the Scripts and the PD Files in the CVS with the client version they are intended for.

Update_x_zzz.sql

-- --------------------------------------------------------------
-- Update.sql
-- Updates the database from x.yyy to x.zzz
-- $Id$
-- --------------------------------------------------------------

SETTING_CheckSchemaVersion('x.yyy');

-- ==============================================================
message 'DDL Changes' type info to client;
-- ==============================================================

-- ==============================================================
message 'TRIGGER' type info to client;
-- ==============================================================

-- ==============================================================
message 'STORED PROCEDURES' type info to client;
-- ==============================================================

-- ==============================================================
message 'FUNCTIONS' type info to client;
-- ==============================================================

-- ==============================================================
message 'User-defined error messages' type info to client;
-- ==============================================================

read "..\\reload\\UserDefinedError.sql";

-- ==============================================================
message 'User-defined views' type info to client;
-- ==============================================================

-- ==============================================================
-- Update schema version in the SETTING table
-- ==============================================================
insert into SETTING (SET_VARNAME, SET_VALUE, SET_TYPE)
on existing update
values ('SCHEMA_VERSION', 'x.zzz', 'S');

commit;

-- ==============================================================
message 'Data Changes' type info to client;
-- ==============================================================

commit;

The function SETTING_CheckSchemaVersion raises an error when the version in the Database is not equal to what we expect.

We write the DDL manually based on the PDM but you can also use the DDL Script Power Designer can generate and embed them by read "pdchanges.sql"

[former_SQLA_member1694882]

Thanks for the answer TDuemesnil. How do you get the changes into the sql script? Does each developer do it manually? And what if there are versioning issues with a PowerDesigner schema when it comes time to commit it to your CVS repo?

[thomas_duemesnil]

Each Developer works on his own update main script. When he has finished his work he has to checkout all changes done by others. We use http://winmerge.org/ and http://www.tortoisecvs.org to check for conflicts in changes. Then he can finalize his update script and check in all changes. PowerDesigner has its own Compare and Merge Model possibilities. You have to have the old and new version available to use them.

[thomas_duemesnil]

As you use subversion you know http://tortoisesvn.net ?

[former_SQLA_member1694882]

Sure and we use that. You indicated you have multiple update scripts which are applied to the db. How do you know in which order to run the script as there could be dependencies among the different scripts?