home *** CD-ROM | disk | FTP | other *** search
- Newsgroups: comp.databases.ingres
- Path: sparky!uunet!cs.utexas.edu!zaphod.mps.ohio-state.edu!pacific.mps.ohio-state.edu!linac!uchinews!gsbacd.uchicago.edu!cs_mj
- From: cs_mj@gsbacd.uchicago.edu (Mark Jaeger)
- Subject: Re: DB admin tools
- Message-ID: <1993Jan11.135316.1@gsbacd.uchicago.edu>
- Lines: 65
- Sender: news@uchinews.uchicago.edu (News System)
- Organization:
- References: <ROBM.92Dec22120335@ishtar.Berkeley.EDU> <1993Jan5.004315.1@gsbacd.uchicago.edu> <1993Jan5.190338.23433@pony.Ingres.COM>
- 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'
-
-