Rev 1 | Blame | Last modification | View Log | RSS feed
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<title>sql2admin_hash.pl Manual</title>
<meta name="GENERATOR" content="Quanta Plus">
<meta http-equiv="Content-Type" content="text/html; charset=utf-8">
</head>
<body>
<h1>sql2admin_hash.pl</h1>
<p>sql2admin_hash.pl is a utility designed to take the modified output of a mysqldump file and create a hash suitable for inclusion in the administrative functions of library.php</p>
<p>sql2admin_hash.pl is run as <strong>sql2admin_hash.pl <em>sqlFileName</em></strong> with output going to stdout. The resulting output can be considered a starting point for creating a database definition hash for the admin functions of library.php</p>
<p>If no filename is passed on the command line, reads stdin, so can be used as a filter.</p>
<h3>Limitations</h3>
<ul>
<li>You must manually remove all insert statements from the dump file prior to running sql2admin_hash.pl</li>
<LI>No knowledge of foreign keys; you must manually enter this information</LI>
<li>All char and varchar fields labeled string; boolean fields must be found and manually corrected</li>
<li>all other field types passed through to the resulting hash; you must manually determine the correct field type</li>
<li>Integers will acquire the size of whatever mySQL reports on a dump</li>
</ul>
<h3>Suggested Use</h3>
<p>It has been found useful to follow these steps to create the correct hash</p>
<ol>
<LI><em>mysqldump --no-defaults --allow-keywords --no-data --skip-add-drop-table <strong>tablename</strong> | ./sql2admin_hash.pl > database.php</em>
</LI>
<li>Include <em>database.php</em> in your primary php script, before library.php is loaded</li>
<li>Edit <em>database.php</em> to clean up any problems</li>
</ol>
<h3>How it works</h3>
<p>Knowing how the program works is useful for knowing why things happen the way they do, and troubleshootin any problems</p>
<ol>
<li>Read entire input file into an array</li>
<li>Filter out comments</li>
<li>Remove all newlines from array</li>
<li>Join Array with null string, then break it apart on semi-colons. Note, the resulting array contains one database definition per row at this time (you did remove all inserts and comments, right???)</li>
<li>for each row in array
<ol>
<li>remove all back ticks</li>
<li>remove all duplicate spaces and all spaces before commas</li>
<li>find the table name (m/create table ([a-z0-9_]+)) and remember it</li>
<li>Remove everything outside the major parentheses (ie, have only the column definitions left). This is actually done in one step with retrieving the table name</li>
<LI>Retrieve all Primary Key and Unique Key entries, save the Primary Key. Throw the matched lines away</LI>
<li>Split the remainder of the previous steps into an array on comma's. We should now only have column information</li>
<li>For each column definition
<ol>
<LI>determine if it is a primary key</LI>
<li>determine if it is nullable</li>
<li>determine if it has a default</li>
<li>if it is auto-increment, set it as read only</li>
<li>make a guess as to its type (ie, char and varchar go into string). If we don't understand the type, just set the type to the database column type</li>
</ol>
</li>
<li>Merge all of the information from the previous steps into one string</li>
</ol>
</li>
<li>Merge all of the row information (ie, the tables) into one string</li>
<li>put the variable definition hash delimters around the string. prepend some constants for placeholders</li>
<li>Print the whole thing out with the php wrappers around it</li>
</ol>
</body>
</html>