> > Hello. I'm sure I'm not the first to have the need for it-- I need a
> > way to detect the current database setup(tables, fields, indexes) and
> > alter the MSSQL database appropriately based on the current state.
> In past development, QA and beta environments, I've used xCase to
> synchronize changes between the xCase model and the target database.
> xCase also helpfully provides SQL change scripts, which I've captured
> and used to incrementally modify production databases.
>
> With Fox as the front end, I have used a "master" table on the target
> database that held the database version, say '42.' When an
> administrative front end connected, it queried the version number,
> then sent modification scripts to the back-end to update it to the
> current version, say, 42to43.sql, 43to44.sql and 44to45.sql. The
Thanks. I considered the versioning mechanism, but decided it could
become a behemoth to maintain every individual update from each
version to the next-- not to mention, if a single alter in a specific
version fails for some reason, it causes more problems-- plus,
creating a new database instead of just altering existing ones
requires separate 'create table' scripts, or for it to run through the
original create table and every alter table for the history of the
entire application....
My VBS script(which I just converted very simply to .NET) is actually
very simple. The XML spec is something like this:
<db>
<table name="Table1">
<field name="iID" type="int identity(1,1)" pk="true"/>
<field name="cField1" type="varchar" length="50" default="''"/>
<field name="cField2" type="text" default="''"/>
</table>
</db>
With numerous table entries.
The script loops the tables, issuing "create table..." if they don't
exist, and otherwise looping fields and adding, removing, and altering
columns if necessary(I have a simple 'remove=true' attribute in the
field spec to prevent having to compare all the fields). I just have
to add index support to this architecture...
Simple, extensible, re-usable-- I just create a different xml file for
a different project.
I actually just created a simple .NET application that will go out and
update over a dozen such MSSQL(MSDE, really) databases using this
method.
--
Derek
_______________________________________________
Post Messages to: ProFox@leafe.com
Subscription Maintenance: http://leafe.com/mailman/listinfo/profox
OT-free version of this list: http://leafe.com/mailman/listinfo/profoxtech
Searchable Archive: http://leafe.com/archives/search/profox
This message: http://leafe.com/archives/byMID/profox/[EMAIL PROTECTED]
** All postings, unless explicitly stated otherwise, are the opinions of the
author, and do not constitute legal or medical advice. This statement is added
to the messages for those lawyers who are too stupid to see the obvious.
