Re: Creating/Altering Database Schemas via Code
- From: Phil Cairns <oti169@xxxxxxxxxxxxxxxx>
- Date: Wed, 31 May 2006 15:59:40 +1000
bill wrote:
I have a conumdrum. Where I am employed, the decision has been made,
against my protests, to create/alter the database for a suite of
applications in the VB code! (VS 2005/SQL Server 2005)
That is,
1) when the "main" application starts it checks if the DB exists (on
the server in an INI file)
2)If it does not exist, it creates it, not using SQL Scripts, but by
running the methods explained in step 3
3)Each table that is needed is associated with a VB class. Everytime an
instance of that class is created it checks to see if the table exists,
if not it creates it.
4) Tables are "versioned". The "version" of each table is hardcoded in
the VB code, and stored in a table in the DB.
5)Thus the same Major.Minor release of an application can be using
different table "versions"
6) If "versions" in the DB and the App do not match, then "ALTER Table"
sql statements are supposedly created automagically and can then be run
by an "administrator" -- Please note that an "administrator" is not
necessarily a programmer or DBA, it can Customer BettySue who has admin
privaleges for the application.
Is it just me or is this crazy?
I have never heard of major applications (a suite of at least 9 client
server and windows services -- used in the Public Safety Sector of all
things) managing their databases in this way. If this is common
practice, please let me know.
Any suggestions/links/horror stories I can use to help stop this
madness?
Sorry, but I use a similar mechanism. I have a table called sysparams that contains name/value pairs, and one of the names is 'dbversion'. The value is the current database version, and if the DB version is lagging behind the application, the application sends a bunch of "ALTER TABLE" or "DROP VIEW/CREATE VIEW" or whatever commands to the database before continuing with the execution.
Mind you, all of these queries are hand-coded by me and embedded into the application, so the idea of BettySue seeing the queries doesn't come into it. Also, if the program is built for an earlier version, it may well crash. However, I tend to be pretty cautious and not remove fields if I can avoid it, and if I can't, I give the users a more vigorous recommendation to upgrade all programs or none all.
The class I use is called DataManager, and it is common to all applications in the suite, so the first one to be started after the system upgrade will bring the database up to date.
Phil
.
- References:
- Creating/Altering Database Schemas via Code
- From: bill
- Creating/Altering Database Schemas via Code
- Prev by Date: Re: Can you search a Visual Source Safe project by file name?
- Next by Date: Re: Lost Examples and Samples
- Previous by thread: Creating/Altering Database Schemas via Code
- Index(es):
Relevant Pages
|
