Page 1 of 2

SQL dependancies

Posted: Sun May 07, 2006 10:48 pm
by alex.barylski
Anyone know of a PHP tool which looks at your MySQL database tables and determines relationships automagically via some convention like:

users:
pkid, fname, lname

members:
pkid, users_pkid, email, phone1, phone2

Now a system could generate some UML diagram indicating that members had a psuedo foriegn key linking it to users table...

Get what I'm asking? A tool in PHP for MySQL tables that does this?

Cheers :)

Posted: Sun May 07, 2006 10:58 pm
by Todd_Z
I stick to a standard that I've developed for myself and my sql tables.

tables are always named in the singular: user / gallery / building
tables have references by the table name + '_id': user_id, building_id, address_id

I use the following script to check the tables.

Code: Select all

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd">
<html>
	<head>
		<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1" />
		<title>Database Dump</title>
		<style type="text/css">
			div {
				width: 400px;
				margin: 10px auto 0 auto;
			}
			
			a { 
				font-style: italic;
				text-decoration: none;
				color: #000;
			}
			
			table {
				border: 1px solid #999;
				width: 100%;
				font-size: 11px;
				color: #333;
			}
			
			thead {
				background-color: #EEE;
				font-weight: bold;
			}
			
			thead td {
				padding: 2px;
			}
			
			tbody {
				background-color: #FFF;
				font-family: 'courier new';
			}
			
			tfoot {
				background-color: #EEE;
			}
			
			tfoot td {
				padding-right: 5px;
				text-align: right;
				font-size: 10px;
				font-weight: bold;
			}
			
			.header td {
				text-align: center;
			}
		</style>
	</head>
	<body>
<?

	include "/************/MySQL.php";
	$mySQL = new MySQL();
	
	$sql = "SHOW TABLES FROM database";
	$mySQL->execSQL( $sql );
	$tables = $mySQL->getALL( $sql, "Tables_in_database" );
	
	$regexs = array();
	$regexs[ 'green' ] = "^(".implode("|",$tables).")(_\d)?_id$";
	$regexs[ 'blue' ] = "^status$";
	$regexs[ 'red' ] = "^id$";
	$regexs[ 'purple' ] = "^timestamp$";
	
	foreach ( $tables as $table ) {
		
		$sql = "SHOW COLUMNS FROM `$table`";
		$fields = $mySQL->getALL( $sql );
		
		$sql = "SELECT COUNT(*) AS count FROM `$table`";
		$data = $mySQL->getField( $sql, "count" );
?>
<div>
	<table cellpadding="0" cellspacing="0">
		<thead>
			<tr>
				<td colspan="<?= count( $fields ); ?>">
					<a id="<?= $table; ?>"><?= ucfirst( $table ); ?></a>
				</td>
			</tr>
		</thead>
		<tfoot>
			<tr>
				<td colspan="<?= count( $fields ); ?>"><?= $data; ?> Rows of Data</td>
			</tr>
		</tfoot>
		<tbody>
			<?
				foreach ( $fields as $field ) {
					
					$color = "black";
					foreach ( $regexs as $col => $regex )
						if ( preg_match( "#$regex#", $field->Field ) && $col != "green" )
							$color = $col;
							
					preg_match( "#^([a-z]+(?:\([0-9a-zA-Z',]{0,30}\))?)#", $field->Type, $type );
							
			?>
			<tr>
				<td style="padding-left: 3px;">
					<?
					
						if ( preg_match( "#{$regexs['green']}#", $field->Field, $parts ) || ( in_array( $parts[1] = $field->Field, $tables ) && $field->Type == "int(4) unsigned" ) ) {
							
							$sql = "SHOW COLUMNS FROM `{$parts[1]}` LIKE 'id'";
							if ( $field->Type == "int(4) unsigned" || $field->Type == $mySQL->getField( $sql, "Type" ) )
								$val = "<a href=\"#{$parts[1]}\" style=\"color: green;\">{$field->Field}</a>";
							else $val = $field->Field;
							
						} else $val = $field->Field;
						
						echo "<span style=\"color: $color;\">$val</span><span style=\"color: #999;\">";
						for ( $i=56; $i>strlen( strip_tags( $val ) )+strlen($type[1]); $i-- )
							echo ".";
						echo "</span>{$type[1]}";
						
					?>
				</td>
			</tr>
			<? } ?>
		</tbody>
	</table>
</div>
<? } ?>
	</body>
</html>

Re: SQL dependancies

Posted: Mon May 08, 2006 11:48 am
by alvinphp
Sorry, do not know of a PHP tool that lets you reverse engineer MySQL databases.

Posted: Mon May 08, 2006 1:43 pm
by RobertGonzalez
I have never looked into it, but does phpMyAdmin have any functions similar to what you are looking for?

Posted: Mon May 08, 2006 2:26 pm
by timvw
http://www.fabforce.net/dbdesigner4/.


<off-topic->
UML is a set of languages that allow you to make a representation of a 'system'.

Yes, there are people that swear to use it upfront, and there are people that decide to generate models afterwards in order to make documentation easier... It all depends on *your* needs..
</off-topic>

Posted: Mon May 08, 2006 5:02 pm
by alvinphp
timvw wrote: <off-topic->
UML is a set of languages that allow you to make a representation of a 'system'.
</off-topic>
That is a great definition of UML.

Posted: Mon May 08, 2006 5:27 pm
by Christopher
alvinphp wrote: <off-topic->
So, are you saying that sometimes it is better to do all your documentation after coding and no planning should be done? Do you just jump into coding and then fix as problems arise?
</off-topic->
<off-topic->
In some ways you are describing the spirit and essence of Agile Methologies which are the current cutting edge of development practices. So I vote yes.
</off-topic->

Posted: Mon May 08, 2006 5:37 pm
by alvinphp
We need the ability to delete our own posts. :D

Posted: Mon May 08, 2006 5:44 pm
by Hades
[quote="timvw"]http://www.fabforce.net/dbdesigner4/.

That's exactly what I use for all DB stuff... it's a great app for either designing or reverse engineering databases.

It's MySQL driver is a little flaky though.

Posted: Mon May 08, 2006 6:32 pm
by Christopher
alvinphp wrote:Agile is a very good methodology, but it is not zero planning. You just do not need very much as you are building a very small amount at a time. Agile should not be used as an excuse not to do any planning, especially if you are doing a 3 month project (which would then go against the heart of Agile methodology). At the most basic you want to at least know the tables you want along with their relationship and a list of the functions you will need. So when I say documentation I am not talking 160 pages of mostly useless stuff, I'm talking rapid development methodology type documentation.
<off-topic->
I agree, in that I said in the spirit of Agile, not in the practice. In part, Agile is about only planning what you truly know; only coding what you could really plan; and only documenting that you actually coded.
</off-topic->

Posted: Mon May 08, 2006 8:23 pm
by alex.barylski
Todd_Z wrote:I stick to a standard that I've developed for myself and my sql tables.

tables are always named in the singular: user / gallery / building
tables have references by the table name + '_id': user_id, building_id, address_id

I use the following script to check the tables.

Code: Select all

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd">
<html>
	<head>
		<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1" />
		<title>Database Dump</title>
		<style type="text/css">
			div {
				width: 400px;
				margin: 10px auto 0 auto;
			}
			
			a { 
				font-style: italic;
				text-decoration: none;
				color: #000;
			}
			
			table {
				border: 1px solid #999;
				width: 100%;
				font-size: 11px;
				color: #333;
			}
			
			thead {
				background-color: #EEE;
				font-weight: bold;
			}
			
			thead td {
				padding: 2px;
			}
			
			tbody {
				background-color: #FFF;
				font-family: 'courier new';
			}
			
			tfoot {
				background-color: #EEE;
			}
			
			tfoot td {
				padding-right: 5px;
				text-align: right;
				font-size: 10px;
				font-weight: bold;
			}
			
			.header td {
				text-align: center;
			}
		</style>
	</head>
	<body>
<?

	include "/************/MySQL.php";
	$mySQL = new MySQL();
	
	$sql = "SHOW TABLES FROM database";
	$mySQL->execSQL( $sql );
	$tables = $mySQL->getALL( $sql, "Tables_in_database" );
	
	$regexs = array();
	$regexs[ 'green' ] = "^(".implode("|",$tables).")(_\d)?_id$";
	$regexs[ 'blue' ] = "^status$";
	$regexs[ 'red' ] = "^id$";
	$regexs[ 'purple' ] = "^timestamp$";
	
	foreach ( $tables as $table ) {
		
		$sql = "SHOW COLUMNS FROM `$table`";
		$fields = $mySQL->getALL( $sql );
		
		$sql = "SELECT COUNT(*) AS count FROM `$table`";
		$data = $mySQL->getField( $sql, "count" );
?>
<div>
	<table cellpadding="0" cellspacing="0">
		<thead>
			<tr>
				<td colspan="<?= count( $fields ); ?>">
					<a id="<?= $table; ?>"><?= ucfirst( $table ); ?></a>
				</td>
			</tr>
		</thead>
		<tfoot>
			<tr>
				<td colspan="<?= count( $fields ); ?>"><?= $data; ?> Rows of Data</td>
			</tr>
		</tfoot>
		<tbody>
			<?
				foreach ( $fields as $field ) {
					
					$color = "black";
					foreach ( $regexs as $col => $regex )
						if ( preg_match( "#$regex#", $field->Field ) && $col != "green" )
							$color = $col;
							
					preg_match( "#^([a-z]+(?:\([0-9a-zA-Z',]{0,30}\))?)#", $field->Type, $type );
							
			?>
			<tr>
				<td style="padding-left: 3px;">
					<?
					
						if ( preg_match( "#{$regexs['green']}#", $field->Field, $parts ) || ( in_array( $parts[1] = $field->Field, $tables ) && $field->Type == "int(4) unsigned" ) ) {
							
							$sql = "SHOW COLUMNS FROM `{$parts[1]}` LIKE 'id'";
							if ( $field->Type == "int(4) unsigned" || $field->Type == $mySQL->getField( $sql, "Type" ) )
								$val = "<a href="#{$parts[1]}" style="color: green;">{$field->Field}</a>";
							else $val = $field->Field;
							
						} else $val = $field->Field;
						
						echo "<span style="color: $color;">$val</span><span style="color: #999;">";
						for ( $i=56; $i>strlen( strip_tags( $val ) )+strlen($type[1]); $i-- )
							echo ".";
						echo "</span>{$type[1]}";
						
					?>
				</td>
			</tr>
			<? } ?>
		</tbody>
	</table>
</div>
<? } ?>
	</body>
</html>
Interesting...if it does what I'm asking for...it should work...as your conventions seem similiar to mine :)

Re: SQL dependancies

Posted: Mon May 08, 2006 8:24 pm
by alex.barylski
alvinphp wrote:
Hockey wrote:Anyone know of a PHP tool which looks at your MySQL database tables and determines relationships automagically via some convention like:

users:
pkid, fname, lname

members:
pkid, users_pkid, email, phone1, phone2

Now a system could generate some UML diagram indicating that members had a psuedo foriegn key linking it to users table...

Get what I'm asking? A tool in PHP for MySQL tables that does this?

Cheers :)
A little off subject, buuuut the point of UML is not to document everything after the tables are built. You are 'supposed' to build your UML first so you can see how your table model looks before you develop. This way you find problems before you start developing. And it is astronomically easier to fix a problem before coding then it is halfway through the project. So even if a tool did exist to document the entity relationships, it should really only be used if you took over someones project and they had no documentation.
So what then, would you suggest be used to indicate table relationships??? If not UML, what? :?
Everah wrote:I have never looked into it, but does phpMyAdmin have any functions similar to what you are looking for?
I have the latest version, but I can't find anything, mind you I have looked very hard :P
timvw wrote:http://www.fabforce.net/dbdesigner4/.


<off-topic->
UML is a set of languages that allow you to make a representation of a 'system'.

Yes, there are people that swear to use it upfront, and there are people that decide to generate models afterwards in order to make documentation easier... It all depends on *your* needs..
</off-topic>
Thank you 8)
alvinphp wrote:
timvw wrote: <off-topic->
UML is a set of languages that allow you to make a representation of a 'system'.

Yes, there are people that swear to use it upfront, and there are people that decide to generate models afterwards in order to make documentation easier... It all depends on *your* needs..
</off-topic>
<off-topic->
So, are you saying that sometimes it is better to do all your documentation after coding and no planning should be done? Do you just jump into coding and then fix as problems arise?
</off-topic->
Your missing the point...

I needed some tool and representation of how my tables were related, if at all...

So when I change my model for one table I can look at a UML diagram and quickly figure out what other tables might need be updated...

Which, beats the hell out of updating one model...testing the system 2 weeks later only to learn the hard way (something not working because of improper table relations).
alvinphp wrote:
arborint wrote:
alvinphp wrote: <off-topic->
So, are you saying that sometimes it is better to do all your documentation after coding and no planning should be done? Do you just jump into coding and then fix as problems arise?
</off-topic->
<off-topic->
In some ways you are describing the spirit and essence of Agile Methologies which are the current cutting edge of development practices. So I vote yes.
</off-topic->
Agile is a very good methodology, but it is not zero planning. You just do not need very much as you are building a very small amount at a time. Agile should not be used as an excuse not to do any planning, especially if you are doing a 3 month project (which would then go against the heart of Agile methodology). At the most basic you want to at least know the tables you want along with their relationship and a list of the functions you will need. So when I say documentation I am not talking 160 pages of mostly useless stuff, I'm talking rapid development methodology type documentation.
I have all that hammered out already, but like I said before...

Maybe I need it so in the future as my table relationships change I don't have to update my docs, but use a tool which generates reltionships for me...

Kind of like documentation in comments...it just makes sense :P

Jcart | Yes, we DO have an edit button.

Posted: Mon May 08, 2006 9:28 pm
by Todd_Z
Heres my practice:

I want to use this code to make a mySQL field human readable:

Code: Select all

function show_field ( $field ) {
	$field = str_replace( "_", " ", $field );
	$field = ucwords( $field );
	return preg_replace( "#Id#", "ID", $field );
}
If all of your fields work with that logic, then you can dump out the mySQL fields onto a doc page, and have it decently readable. Then, using the code I already posted, you can have links to the other tables.

This kind of documentation would be more technical than for a client. For example, if you are developing on a team, members of the team may benefit immensely if they can see the database structure in a human readable format.

Posted: Mon May 08, 2006 10:04 pm
by alex.barylski
Makes sense to me :)

Posted: Mon May 08, 2006 10:06 pm
by John Cartwright
Hockey, there is absolutely no reason to ever have 6-7 posts in a row.. please use the edit button.