Re: Generate a CRUD matrix
Date: Fri, 15 Jan 2010 09:33:15 -0800 (PST)
Message-ID: <069ca1a1-07fd-4441-b19d-cf275ac0d940_at_j14g2000yqm.googlegroups.com>
On Jan 15, 6:38 am, Mark D Powell <Mark.Powe..._at_hp.com> wrote:
> On Jan 14, 6:40 pm, joel garry <joel-ga..._at_home.com> wrote:
>
>
>
> > On Jan 14, 12:36 pm, 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
>
> > What wouldn't be in USER_SOURCE (oh, well, ok, DBA_SOURCE)? Once 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? Did I
> > misread the OP or miss something obvious? I 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-Switch.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? You 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 :-)
jg
-- _at_home.com is bogus. http://www.newspeakdictionary.com/wastetime.gifReceived on Fri Jan 15 2010 - 11:33:15 CST
