From: cs_mj@gsbacd.uchicago.edu (Mark Jaeger)
Subject: Re: DB admin tools
Organization: 
Date: Mon, 11 Jan 1993 19:53:16 GMT

In article <1993Jan5.190338.23433@pony.Ingres.COM>, billc@Ingres.COM 
(Bill Coffin) writes:

> iidbdepends isn't really a System Catalog, and so it is not supported
> on all gateways or in STAR.  Also, we don't guarantee that it won't
> change, the way we guarantee catalogs like iitables.  So you can use
> iidbdepends, but without connectivity and without guaranteed backward
> compatibility.

Maybe we're just quibbling about apples and oranges, but I thought that
"system catalog" meant anything that is in an empty, newly-created
database.  At least, that's what it meant in version 5.  I think it
might be more correct to say that "iidbdepends" is not part of the
Standard Catalog Interface, but rather it is an internal dbms system
catalog, for INGRES internal use only.  

The tables in the Standard Catalog Interface are mostly views on the
internal system catalogs.  Bill is correct in his warning that one
should not write tools that _depend_ on the internal catalogs--use the
SCI instead.  However, we, as users, still need to get to them
sometimes.  This happened to me recently when I had to delete rows from
iirelation, iiattribute, and iiindex due to a bug in the JSP server.

> My reccomendation to Rob McNicholas, the original poster, would be to
> use views liberally while developing the applications.  You can use
> many tricks to avoid the necessity of redefining tables.

Bill's suggestion doesn't speak to most of the reasons that I need to
drop and recreate a table: adding a column; making a column longer; and
renaming a column.  That we do this so frequently is probably due to a
flawed development process, with an inadequate analysis and design
phase, but the fact remains that we users _do_ have to change our tables
from time to time.  The copydb tool has long been inadequate to the
task, really since version 6, when INGRES first started dropping views
when you drop a table.

I have attached a query that I wrote to find the dependencies on a given
table, although I won't vouch for its accuracy.  It only goes one level
deep, also, so it won't catch a view that is defined on a view.  But
it's a start.

--Mark Jaeger				internet: cs_mj@gsbvax.uchicago.edu
Graduate School of Business		yellnet:  (312) 702-0328
University of Chicago			faxnet:   (312) 702-0233
Disclaimer: My opinions are my own and not those of my employer.
Je suis un virus.  Prends ton pied et copie moi a ta .signature.

================================================================================
select distinct obj_type = 'View'
	, obj_owner = rv.relowner , obj_name = rv.relid
	, base_name = rt.relid
	from iirelation rv , iidbdepends d , iirelation rt
	where 1=1
	and rt.relowner = '$t_owner' and rt.relid = '$t_name'
	and rv.reltid = d.deid1 and rv.reltidx = d.deid2
	and rt.reltid = d.inid1 and rt.reltidx = d.inid2
	and d.itype = 0 and d.dtype = 17 and qid = 0
union all select distinct obj_type = 'Rule'
	, obj_owner = r.rule_owner , obj_name = r.rule_name
	, base_name = r.table_name
	from iirules r
	where 1=1
	and r.rule_owner = '$t_owner'
	and r.table_name = '$t_name'