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?

asked 24 Feb '10, 00:59

Nick%20Brooks's gravatar image

Nick Brooks
513171931
accept rate: 50%

edited 28 Feb '10, 13:32

Breck%20Carter's gravatar image

Breck Carter
27.4k424585836


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"

permanent link

answered 24 Feb '10, 07:23

Thomas%20Duemesnil's gravatar image

Thomas Dueme...
2.5k213460
accept rate: 15%

edited 25 Feb '10, 11:42

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?

(25 Feb '10, 03:33) Nick Brooks

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.

(25 Feb '10, 07:22) Thomas Dueme...

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

(25 Feb '10, 07:26) Thomas Dueme...

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?

(25 Feb '10, 07:59) Nick Brooks

We use PowerDesigner too (PDM). For every build of our software there is a corresponding physical model, checked in using SourceSafe. We let PowerDesigner create differential DDL scripts to modify databases according to the changed model.

For updates we bundle the necessary scripts with a master script, that READs them im chronological order and checks against a column in a version table if it's ok to apply the changes.

For our new product line though we took another approach: all schema information is defined by meta data within the framework. Version informations including the data model are stored for each client version. For updates a wizard automatically creates all SQL statements for the target DBMS to update the client's db to the current model and generates an installer.

permanent link

answered 24 Feb '10, 10:33

Reimer%20Pods's gravatar image

Reimer Pods
4.2k334481
accept rate: 12%

Thanks for your answer Reimer. We've never used PowerDesigner and Sybase's website is, well, less than clear. It appears to be targeted more towards your PHB. Can you script PowerDesigner to create those DDL change scripts? Part of this exercise is to also automate as much as possible.

(25 Feb '10, 03:29) Nick Brooks
Comment Text Removed
Comment Text Removed
Comment Text Removed
Comment Text Removed

Nick, I'm not really a PD expert and can't answer that question. You might want to post your question in the newsgroup sybase.public.powerdesigner.general.

(25 Feb '10, 08:37) Reimer Pods

Generating an update script from the differences of two models is easily done with a few clicks. As we don't do that more than once or twice a month we never considered automation.

(25 Feb '10, 08:37) Reimer Pods

Since we develop 'package' software to be deployed to multiple sites, we implement schema changes via a script file to insure it is easily repeatable; the last line in that script updates a schema version setting (a particular row in a "settings" table).

We maintain an "expected schema version number" in our app and check that against the "schema version" in the db at app startup.

permanent link

answered 24 Feb '10, 02:58

Bill%20Aumen's gravatar image

Bill Aumen
2.0k294473
accept rate: 16%

What program are you using to make the schema changes? DO you have a source control provider you are using with Delphi code?

permanent link

answered 24 Feb '10, 14:54

Roland%20Smith's gravatar image

Roland Smith
91448
accept rate: 0%

Hi Roland. We generally just use Sybase Central to make the changes, however a lot of the developers then cut'n'paste the changes to a db update script. We use subversion as a VCS.

(25 Feb '10, 03:26) Nick Brooks

Wooops - that should read "copy'n'paste".

(25 Feb '10, 03:31) Nick Brooks

Congratulations to Roland: The first SQLA user to use an "Answer" to ask a question. I don't think that's going to become common practice BUT I SEE NOTHING WRONG WITH IT. In this case Roland's question is larger in scope than the usual question-about-a-question that's posted as a comment to the original question. Anyway, SQLA ain't StackOverflow... over there, you get tarred and feathered for deviating from The Rules :)

(25 Feb '10, 08:26) Breck Carter

@Nick: You can edit a comment by deleting-and-re-adding it. And you delete a comment by clicking on the X which appears when you hover over the comment text.

(25 Feb '10, 08:28) Breck Carter

I'll try not to Answer by posting a Question.

(25 Feb '10, 17:09) Roland Smith

We use PowerDesigner first off. Unfortunately, we don't have the Repository feature, so we handle the 'versioning' with good'ole Copy+Paste of the PDM file. We then have our application version stored in the database, and then when the application connects, it compares.

It has its faults, but it works out pretty well in the end.

permanent link

answered 24 Feb '10, 01:55

Calvin%20Allen's gravatar image

Calvin Allen
1.5k232638
accept rate: 25%

Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×22
×13
×3

question asked: 24 Feb '10, 00:59

question was seen: 3,560 times

last updated: 28 Feb '10, 13:32