| Subject | Re: [firebird-support] Upgrading a database |
|---|---|
| Author | Carl Peto |
| Post date | 2004-03-30T15:55:13Z |
I've had similar problems to this one before.
If you can use it then IBConsole is excellent for displaying dependencies,
plus it has the wonderful feature of showing the metadata that would be
needed to create the table/view/SP/UDF, etc. that you are looking at, so you
can copy/paste this metadata (CREATE TABLE statements, etc.) and use either
your favourite editor (or just Edit|Replace if you're limited to a primitive
one like Notepad) to turn these into DROP TABLE statements, etc.
As far as I know this would be the dependency chain...
Triggers
|
v
Views/Stored Procedures *
|
v
Tables (including referential integrity constraints)
|
v
User Defined Functions and Exceptions
That is to say that you will not be able to drop UDF definitions (possibly)
until all tables referring to them (in calculated fields) are dropped first
and you will not be able to drop a table until the views and stored
procedures depending on it are dropped.
Stored Procedures are at a middle level with views because (unfortunately)
you could have views that depend on SPs and you could have SPs that depend
on views, as well as (of course) views that depend on other views and SPs
that depend on other SPs.
And because of referential integrity constraints even tables that depend on
tables.
All of this are standard database problems that I've experienced just as
often with Oracle too.
If you don't have IBConsole then another tool like ibWebAdmin *may* help,
although I find that not as powerful.
Finally the raw data is available in the RDB$DEPENDENCIES system table but
it is very cryptic.
So I think you'll need to create an enormous, carefully constructed drop
script to do this. Good luck!
At this point I will state my opinion as well. I would strongly recommend
that you move to using a tool for designing and building databases. For
instance we use a tool called Dezign for Databases that not only allows you
to model the database structure but also to create the DDL scripts to create
the database and/or drop it.
In my 8 years as a senior database developer, team leader and database
architect with Oracle and Interbase and Firebird I have learnt the hard way.
For teams of one person or more :) you will need it at some point.
Otherwise you WILL lose some of your precious life finding out how the
database is put together, where that mystery constraint is and why it is
there. If you aren't in charge of the project then just volunteer to draw
up a database design for documentation purposes, managers will rarely refuse
that offer, in my experience as they all know that they should have one.
If this doesn't apply to you because you have already got your database
documented then *good for you* but it applies to everyone reading this post
that hasn't!
Carl
If you can use it then IBConsole is excellent for displaying dependencies,
plus it has the wonderful feature of showing the metadata that would be
needed to create the table/view/SP/UDF, etc. that you are looking at, so you
can copy/paste this metadata (CREATE TABLE statements, etc.) and use either
your favourite editor (or just Edit|Replace if you're limited to a primitive
one like Notepad) to turn these into DROP TABLE statements, etc.
As far as I know this would be the dependency chain...
Triggers
|
v
Views/Stored Procedures *
|
v
Tables (including referential integrity constraints)
|
v
User Defined Functions and Exceptions
That is to say that you will not be able to drop UDF definitions (possibly)
until all tables referring to them (in calculated fields) are dropped first
and you will not be able to drop a table until the views and stored
procedures depending on it are dropped.
Stored Procedures are at a middle level with views because (unfortunately)
you could have views that depend on SPs and you could have SPs that depend
on views, as well as (of course) views that depend on other views and SPs
that depend on other SPs.
And because of referential integrity constraints even tables that depend on
tables.
All of this are standard database problems that I've experienced just as
often with Oracle too.
If you don't have IBConsole then another tool like ibWebAdmin *may* help,
although I find that not as powerful.
Finally the raw data is available in the RDB$DEPENDENCIES system table but
it is very cryptic.
So I think you'll need to create an enormous, carefully constructed drop
script to do this. Good luck!
At this point I will state my opinion as well. I would strongly recommend
that you move to using a tool for designing and building databases. For
instance we use a tool called Dezign for Databases that not only allows you
to model the database structure but also to create the DDL scripts to create
the database and/or drop it.
In my 8 years as a senior database developer, team leader and database
architect with Oracle and Interbase and Firebird I have learnt the hard way.
For teams of one person or more :) you will need it at some point.
Otherwise you WILL lose some of your precious life finding out how the
database is put together, where that mystery constraint is and why it is
there. If you aren't in charge of the project then just volunteer to draw
up a database design for documentation purposes, managers will rarely refuse
that offer, in my experience as they all know that they should have one.
If this doesn't apply to you because you have already got your database
documented then *good for you* but it applies to everyone reading this post
that hasn't!
Carl
----- Original Message -----
From: "Anand" <akashelkar@...>
To: <firebird-support@yahoogroups.com>
Sent: Tuesday, March 30, 2004 3:58 PM
Subject: [firebird-support] Upgrading a database
> I have a peculiar problem. We have revamped the database
> structure being used in our application, and the new design has
> to be created in the GDB file used by our app. The code for
> creating tables and views and SPs is all ready to be executed,
> in fact it does work alright. My problem is I don't want the
> old tables to continue in the new database. The code should
> create the new structure, run a few SPs which will copy data
> from all the old tables into the new tables, and the old tables
> should be dropped.
>
> However when doing that, because of the various dependencies the
> tables may not be dropped. What's the way out? Is there any
> order to be followed while dropping the database objects, say
> start with the SPs first, then go for views and then for tables?
>
> Is there any other way of doing this, maybe create a separate
> database with the new structure and import data from the old
> database into the new one, so I can simply delete the old GDB
> file? How would that be possible in an SP?
>
>
> -
> Anand
>
>
> __________________________________
> Do you Yahoo!?
> Yahoo! Finance Tax Center - File online. File on time.
> http://taxes.yahoo.com/filing.html
>
>
> --------------------------------------------------------------------------
------
> Yahoo! Groups Links
>
> a.. To visit your group on the web, go to:
> http://groups.yahoo.com/group/firebird-support/
>
> b.. To unsubscribe from this group, send an email to:
> firebird-support-unsubscribe@yahoogroups.com
>
> c.. Your use of Yahoo! Groups is subject to the Yahoo! Terms of Service.
>
>