Rev 31 | Blame | Compare with Previous | Last modification | View Log | Download | RSS feed
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.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>
Syntax
</h3>
sql2admin_hash.pl [-s] [-f<em>filename</em>]
<p>
Will read <em>filename</em>, an SQL create file, and process it. STDIN is used if <em>filename</em> is not defined. Will send processed file to STDOUT.
</p>
<p>
If <em>-s</em> flag is used, will process as a secondary file, ie one that will be included in a primary file that defines the database structure statically. Bascially, without the <em>-s</em>, sql2admin_has.pl will create a full definition, defining various constants, defining $DATABASE_DEFINITION as a global, then populating it with constants
</p>
<p>
If <em>-s</em> is defined, it will define each table as an entry to be concated to the already defined variable.
</p>
<h3>
Limitations
</h3>
<ul>
<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>
<li>
<em>Do not use commas or parentheses in comments</em>
</li>
</ul>
<h3>
Information Created
</h3>
<ul>
<li>
<em>comment</em> is inserted as a PHP comment for both fields and tables
</li>
<li>
Table Information
</li>
<ul>
<li>
Table Names are plugged into <em>display name</em>
</li>
<li>
<em>display query</em> and <em>display fields</em> contain all fields in table
</li>
<li>
<em>key field</em> populated if only one primary key defined in table
</li>
</ul>
<li>
Column Information
</li>
<ul>
<li>
Column Names are plugged into <em>display name</em>
</li>
<li>
If column defined with <em>references</em>, will build a <em>lookup</em> definitoin
</li>
<li>
Will honor <em>default</em>
</li>
<li>
<em>primary key</em> and <em>auto_increment</em> set <em>read only</em> to true
</li>
<li>
<em>not null</em> causes <em>read only</em> flag to be set
</li>
<li>
column width definition used to populate <em>width</em> parameter
</li>
</ul>
</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
</li>
<li>
remove all entries that do not start with <em>create table</em>
</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>If <em>references</em> defined, set as lookup, othewise, do the following</li>
<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>
<strong>If not run with the <em>-s</em> parameter</strong>, 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>