Re: Generate a CRUD matrix
From: Terry Dykstra <tddykstra_at_forestoil.ca>
Date: Mon, 18 Jan 2010 18:22:07 GMT
Message-ID: <jF15n.62113$PH1.60871_at_edtnps82>
"Malcolm Dew-Jones" <yf110_at_vtn1.victoria.tc.ca> wrote in message news:4b50d4ca$1_at_news.victoria.tc.ca...
> Terry Dykstra (tddykstra_at_forestoil.ca) wrote:
> : "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?
> You said you were going to "eyeball them for crud". I'm suggesting you
> can do that more quickly and reliably by first finding and marking the
> text that contains the references to the objects, that way in your editor
> you can jump straight to all the parts of the code that need to be
> examined, and also know you missed nothing (except execute immediate
> stuff). You can use user_dependencies to find the names of the objects.
> Whether this is useful depends on the number of objects and the size of
> the files and how you like to work.
Date: Mon, 18 Jan 2010 18:22:07 GMT
Message-ID: <jF15n.62113$PH1.60871_at_edtnps82>
"Malcolm Dew-Jones" <yf110_at_vtn1.victoria.tc.ca> wrote in message news:4b50d4ca$1_at_news.victoria.tc.ca...
> Terry Dykstra (tddykstra_at_forestoil.ca) wrote:
> : "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?
>
> You said you were going to "eyeball them for crud". I'm suggesting you
> can do that more quickly and reliably by first finding and marking the
> text that contains the references to the objects, that way in your editor
> you can jump straight to all the parts of the code that need to be
> examined, and also know you missed nothing (except execute immediate
> stuff). You can use user_dependencies to find the names of the objects.
> Whether this is useful depends on the number of objects and the size of
> the files and how you like to work.
I think it was Joel who suggested the "eye-balling". Not me.
Terry Received on Mon Jan 18 2010 - 12:22:07 CST