Re: Generate a CRUD matrix

From: Terry Dykstra <tddykstra_at_forestoil.ca>
Date: Fri, 15 Jan 2010 19:38:55 GMT
Message-ID: <jv34n.61692$PH1.19337_at_edtnps82>

"Malcolm Dew-Jones" <yf110_at_vtn1.victoria.tc.ca> wrote in message news:4b50aff2$1_at_news.victoria.tc.ca...
> joel garry (joel-garry_at_home.com) wrote:
> : On Jan 15, 6:38=A0am, Mark D Powell <Mark.Powe..._at_hp.com> wrote:
> : > On Jan 14, 6:40=A0pm, joel garry <joel-ga..._at_home.com> wrote:
> : >
> : >
> : >
> : > > On Jan 14, 12:36=A0pm, vsevolod afanassiev
> : >
> : > > <vsevolod.afanass..._at_gmail.com> wrote:
> : > > > I don't think it is possible: a stored procedure may
> select/insert/
> : > > > update/delete many tables, something like that
> : >
> : > > > SELECT
> : > > > FROM TABLE_A
> : >
> : > > > UPDATE
> : > > > TABLE_B
> : >
> : > > > DELETE TABLE_C
> : >
> : > > > Searching USER_SOURCE won't be enough
> : >
> : > > =A0What wouldn't be in USER_SOURCE (oh, well, ok, DBA_SOURCE)?
> =A0Once =
> : you
> : > > get the candidate list, you parse the procedures for their tables.
> : > > Select is easy, just whatever follows FROM until no more commas,
> : > > update and delete will always be one or two words after? =A0Did I
> : > > misread the OP or miss something obvious? =A0I was thinking of
> getting
> : > > the various elements separately (these procedures update, containing
> : > > tables x, y z..., etc), but maybe easier just to get
> procedure/table/
> : > > elements parsing directly, put in table, then do what you want.
> : >
> : > > jg
> : > > --
> : > > _at_home.com is
> bogus.http://thedailywtf.com/Articles/The-Little-Red-Switc=
> : h.aspx
> : >
> : > I do not think parsing the from clause would be all that easy since
> : > you have to allow for encountering terms like inner join, right outer
> : > join, etc... then what about dynamic SQL where portions of the SQL
> : > statement are going to be held in variables? =A0You may also have to
> : > deal with commented out sections of source.

>


> : Very good points, obviously my head is stuck in O7.  Still, you can

> : perhaps get everything in a new join syntax up until the ON keyword.

> : This makes a lot more sense if you are limited to a certain subset of

> : language usage on an older system, rewriting the latest oracle SQL

> : would be huge - I can imagine one impenetrable model clause could stop

> : me cold. I totally missed the dynamic SQL issue, though it would
> : still have to have some kind of FROM, I think?

>


> : >

> : > I would be interested in getting a copy of a routine that can

> : > automatically extract SQL from stored code even if it had no ability

> : > to deal with dynamic SQL (since no well written system should use more

> : > than a few dynamic SQL statements).

> : >
> : > HTH -- Mark D Powell --

>


> : OK, I'm convinced, this would bite off more than I could chew.  Easier

> : to just extract all the procedures and eyeball them for crud. Maybe
> : comments in procedures will answer the OP :-)

>

> No, use the dependencies table (USER_DEPENDENCIES (?)) to find what table
> names are accessed. Then search for those names. Or grep those names to
> find the line numbers in the code and go straight to those line numbers.
> Or use (e.g.) perl to add a tag to each keyword, and search for the tag
> (which is easier than searching for each keyword).

>

> untested:

>

> :: DOS BAT FILE, invoke perl for each keyword (line wrapped for msg)
> for %i in (list the table names here) do
> perl -i.%i.bak -pe "s/(%i)/ /*!*/ $1/gi" source-file.pls

>

> In this example I would search for /*!*/.

>

> $0.10

>

I'm not following. What good does going to a line in the source table going to do for me?
select col1,
col2,
.,
..,
from mytable;

-- 
Terry Dykstra

Received on Fri Jan 15 2010 - 13:38:55 CST