views:

88

answers:

3

I develop an app with both thick-client and thin-client components. We also version our database such that schema changes result in their own version number and change scripts can be applied. Database changes, however, don't always occur in step with thick-client changes. Yes, today's database change might add a column and necessitate a in the thick-client, but tomorrow's database change might fix an error in a stored procedure that does not require any external changes. How can I code the thick-client to test whether it is compatible with a specific database version when some are backwards compatible and some are not?

In the even that anyone cares, ours is a .NET app integrating with SQL Server, but this seems like more of a versioning question than a platform question. Unless there is a platform specific solution ...

A: 

Could you adopt a major/minor version number scheme?

A change in major number implies that clients need updating, and change in minor number does not.

djna
A "version number" table which records the major and minor version are essential to every database design. It should have one row. Applications should simply query it before doing anything.
S.Lott
@ S.Lott: Can you share a little more details about this "version number" table approach?
Tahir Akhtar
@Tahir Akhtar: `CREATE TABLE MY_APPLICATION_VERSION( COMPONENT VARCHAR, MAJOR NUMBER, MINOR NUMBER );` Every change updates this table the current version of the named component. Every application queries this to get the current version of the named component.
S.Lott
What is the significance of MINOR version?
Tahir Akhtar
I guess answer to my question is already given by Silk
Tahir Akhtar
A: 

With any of these, version numbers are always increasing.

If the database knows the minimum client version it requires, and the client knows the minimum database version it requires, it's a simple check to determine what needs upgrading (if anything) -- now whether you encapsulate the logic in a stored procedure, or in code, that's your decision...

Rowland Shaw
+2  A: 

You can create a table, ex. metadata with two string columns, and put there an entry (or more entries) with the current version of the schema. I suppose that you do something similar now.

And split the version to two numbers (like major/minor scheme). When you change the schema in non-backwards compatibile way then you increse the major version. After backward compatible change you just update minor version.

Major is used by app to check if it is compatible with the current schema, and major+minor is used to check if you can/need to update the schema.

I think this is the solution that is used by most applications.

silk