Has anyone attempted to create an xml build file to use ANT to make a
master SQL script that can build an entire database from a repository?
I am thinking VSS specifically. We would like to use ANT to define
the dependencies between database objects and pull the source code
from VSS. Then we could build a master script capable of rebuilding
the entire database.

We already run JBuilder 8 for our Java devlopment, and it would be
convenient for the developers to just run another target from within
this IDE to build the database schema (for use in deploying and
building a database from scratch on a local Sybase ASE server).

Just wondering if anyone has attempted this yet?

Thanks for any replies,

Kevin

"K Burke" <kdburke (AT) sympatico (DOT) ca> wrote

Initially I am not sure what the repository has to do with anything? You
wish to create the db schema from the project not the repository, correct?

Have a look at the apache project for OJB .... I am not sure where it is at
but one of the things it was aiming to do was to generate a db schema from
the XML file that is used to define the class->table dependencies. OJB is
an O/R Mapping tool. It is open source so you may get ideas from it or may
decide that it is just the solution you need. http://db.apache.org/ojb/

Otherwise I think what you are wishing to do would greatly be influenced by
how you are accessing the the database from your application. E.g. are you
using a Data Mapper pattern as described in Pattern of Enterprise
Application Architecture (Martin Fowler)?

HTH
Shane


--
"Yes you could implement an OO model in C or assembler, even in pure binary
if you wanted to. And you could also paint your house with a toothbrush. I
just hope that you have better and more enjoyable things to do with your
time." - Rod Davison

"Shane Mingins" <shanemingins (AT) yahoo (DOT) com.clothes> wrote

Thanks for the reply Shane. Ideally, creating a db schema from a
project would be cool. But, we already have a db schema and the DDL
scripts for creating the database are stored in SourceSafe. Keeping
SourceSafe up-to-date with the changes is too time consuming for our
DBAs, so we would like something to automate the process.

At this point in time, maintaining the schema is a separate beast from
the project code. I merely wanted to add a target for the developers
in their project build script (XML) to create a master SQL file for
the database. Some of our developers have their own database for
working on that need to be resynced once a week (on a laptop) and it
very easy if they just create a clean database and run the DDL script
in to the empty database. Then they know they got the latest schema
because it came from SourceSafe.

So if I understand correctly, you do not wish to generate the SQL file based
on Classes .. right?

What you want to do is to take the existing SQL files and execute them
against a target DB, correct?

If so, then do have a look at the OJB source files that I mentioned as it
has an ANT build script that creates tables based on SQL files to your
target DB. This may use a user-defined task??

I guess at the end of the day I would probably write a utility that parses
the SQL files executing them against a target DB or writing a master script
(depending which way u want to go) ... or depending on your environment this
may be a command line to the OS ..... UNIX has amazing commands and you may
be able to pipe all the SQL files into one SQL file .... then you have your
master SQL file to execute?

How does any of that sound?

Shane