Subject Re: Management Tool specific
Author Adam
--- In, "bmckenna6" <bmckenna@...> wrote:
> My app allows the user to create add'l tables as needed, so when it
> comes upgrade time, my users may all have somewhat different sets of
> tables.
> Tools like IBPump are very useful, but I don't think that it can
> duplicate a table scheme.

gbak -m does a metadata only backup, useful for creating a target for
a data pump.

> Are there any management tools that will create source based upon a
> db's specific tables and tablenames and then allow for additions and
> changes before generating a new db?
> Or does this need to be handled by reading system tables and writing
> new source based upon that?
> Any keywords and direction/resources in helping to expand my vision on
> this issue will be greatly appreciated.

I don't like the idea of customers adding arbitrary objects to the
database. Then again, your customers may have had database design
education to at least write normalised data structures.

At the very least, your UI should prefix their table names with some
code so that you can identify it.

I don't see how you can write DDL that affects tables you don't know
about. What about dependency checks etc? Do you allow them to define
foreign keys to your tables? If so you may have trouble refactoring
those tables later on. If you wanted to normalise the data in one of
your own tables, and create a view with the old table name to maintain
compatibility etc, the dependency checks will still make this difficult.

So you may need to store somewhere the DDL they ran to add these
tables, or reverse engineer the DDL from the system tables, then you
can pump the base data into your base structure, update it to the
latest without their additional dependencies, then run the DDL the
customer defined, then pump the data from the customer defined tables
into the new database.