The Design of Software (CLOSED)

A public forum for discussing the design of software, from the user interface to the code architecture. Now closed.

The "Design of Software" discussion group has been merged with the main Joel on Software discussion group.

The archives will remain online indefinitely.

Mysql help needed - find dependencies

Hi Everybody,
I am refering to the information_schema views. Is there any way I can find whether a particular table is used either within a View, Procedure or function?

Similarly I need to find dependency information on procedures and functions, if this function is called from some other procedure or is dependent on a table etc

I come from Oracle background so this kind of information is available using the meta-data tables.

Is it possible to extract similar dependencies from information_schema or the show commands.

Thanks
Nilesh
Nilesh Jethwa Send private email
Sunday, May 06, 2007
 
 
This may get you started - I am no guru, I simply tried poking around in my MySQL after reading this... I am sure there is someone far more skilled than I who can give a better answer, I was just a bit bored and enjoy this sort of thing :)

use test

DROP TABLE IF EXISTS `test`.`t1`;
CREATE TABLE  `test`.`t1` (
  `myid` int(10) unsigned NOT NULL auto_increment,
  `mytext` varchar(45) NOT NULL,
  PRIMARY KEY  (`myid`);

DROP VIEW IF EXISTS `test`.`v_t1`;
CREATE VIEW  `test`.`v_t1`
AS select `t1`.`myid` AS `myid`,`t1`.`mytext` AS `mytext` from `t1`;

DELIMITER $$

DROP PROCEDURE IF EXISTS `test`.`p1_v1` $$
CREATE PROCEDURE `test`.`p1_v1` ()
BEGIN
  insert into t1(mytext) values(user());

  select last_insert_id() as newid;
END $$

DELIMITER ;


DELIMITER $$

DROP PROCEDURE IF EXISTS `test`.`p2_v1` $$
CREATE PROCEDURE `test`.`p2_v1` ()
BEGIN
  select myid, mytext from t1;
END $$

DELIMITER ;

-- end

Running:

select c.table_name, c.column_name
from information_schema.columns c
where c.column_name = 'myid';

Returns:

table_name | column_name
t1 | myid
v_t1 | myid

So view metadata is visible there, but stored procedure references do not appear in columns in that way.

I did a simmple query on information_schema.routines

select r.routine_name from information_schema.routines r
where routine_definition like ('%myid%');

this bagged me:

routine
p2_v1

If you search around on mysql.com, you may find exactly what you need, but if not, this may help you get started.

I sometimes miss Oracle, it is a truly great database to work with, especially the simplicity of date handling (compared to MS SQL Swerver). A few less frills with MySQL, but hey, you get what you pay for ;)
I still code in Delphi
Tuesday, May 08, 2007
 
 
I nipped too much text from my sql for the table create, so as not to force you to create InnoDb table if you are using some other storage - but now there is a syntax error in that script!! OMG!

DROP TABLE IF EXISTS `test`.`t1`;
CREATE TABLE  `test`.`t1` (
  `myid` int(10) unsigned NOT NULL auto_increment,
  `mytest` varchar(45) NOT NULL,
  PRIMARY KEY  (`myid`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

;)
I still code in Delphi
Tuesday, May 08, 2007
 
 
Hi Delphi guy,
 thanks for your input. I was also playing around with the REGEX function.

What I need is

1.Which views reference table 'A'
2.Which procedures or functions refer to table 'A'
3.What are the tables (or views) being used by view 'X'
4.What are the tables (or views) being used by procedure 'Y'

I have got the answer for 1 and 2 using REGEX function

select table_name 'View Name' from information_schema.views
where view_definition REGEXP  concat('from.*','Table Name','.*where')

similarly for routines I get the routine references

Now I need some REGEX logic to derive 3 and 4. But I am not sure if this is the optimal way.

In Oracle, there is an all_dependencies table which maintains all the needed information.

Isn't there anything similar in MySQL?

I posted the same question on Mysql forum and googled a lot but I didn't find any solution

Thanks
Nilesh
Nilesh Jethwa Send private email
Tuesday, May 08, 2007
 
 
Off the top of my head, and this is probably not the best method, but if I was in a pinch I would start with what information is to hand...

Create table T containing all the table names in the schema.

Create child table TREF containing all the refrences found in a regexp search of the routines table, matched against the table_names in T. You could likely grab a substring of the procedure text and store that in a varchar column in TREF, that would be useful if you have some long procedures to look at.

Wrap it all in a stored procedure, change it to temporary tables and allow for a parameter of a single table name, and there you go... a nice little utility. Heck, can I have a copy when you are done? :)

There *should* be something, I'm just not aware of it, and haven't needed one for MySQL as yet.
I still code in Delphi
Tuesday, May 15, 2007
 
 
Hi there,
 I finally figured out to do item 3 and 4 and it is now packaged in this MySQL documentor tool. I am doing it using REGEX. You can actually check the SQL code that I am using in the below dashboard.

Check it out and would like to see feedback

http://www.infocaptor.com/mysql-documentor.php

Thanks for your responses
Nilesh
Nilesh Jethwa Send private email
Tuesday, May 15, 2007
 
 
The sakila example looks pretty good, thanks for posting that.
I still code in Delphi
Tuesday, May 22, 2007
 
 

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics
 
Powered by FogBugz