| 1 |
rodolico |
1 |
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
|
|
|
2 |
<html>
|
|
|
3 |
|
|
|
4 |
<head>
|
|
|
5 |
<title>sql2admin_hash.pl Manual</title>
|
|
|
6 |
<meta name="GENERATOR" content="Quanta Plus">
|
|
|
7 |
<meta http-equiv="Content-Type" content="text/html; charset=utf-8">
|
|
|
8 |
</head>
|
|
|
9 |
<body>
|
|
|
10 |
|
|
|
11 |
<h1>sql2admin_hash.pl</h1>
|
|
|
12 |
|
|
|
13 |
<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>
|
|
|
14 |
<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>
|
| 5 |
rodolico |
15 |
<p>If no filename is passed on the command line, reads stdin, so can be used as a filter.</p>
|
| 1 |
rodolico |
16 |
<h3>Limitations</h3>
|
|
|
17 |
|
|
|
18 |
<ul>
|
|
|
19 |
<li>You must manually remove all insert statements from the dump file prior to running sql2admin_hash.pl</li>
|
|
|
20 |
<LI>No knowledge of foreign keys; you must manually enter this information</LI>
|
|
|
21 |
<li>All char and varchar fields labeled string; boolean fields must be found and manually corrected</li>
|
|
|
22 |
<li>all other field types passed through to the resulting hash; you must manually determine the correct field type</li>
|
|
|
23 |
<li>Integers will acquire the size of whatever mySQL reports on a dump</li>
|
|
|
24 |
</ul>
|
|
|
25 |
|
|
|
26 |
<h3>Suggested Use</h3>
|
|
|
27 |
<p>It has been found useful to follow these steps to create the correct hash</p>
|
|
|
28 |
<ol>
|
| 5 |
rodolico |
29 |
<LI><em>mysqldump --no-defaults --allow-keywords --no-data --skip-add-drop-table <strong>tablename</strong> | ./sql2admin_hash.pl > database.php</em>
|
|
|
30 |
</LI>
|
|
|
31 |
<li>Include <em>database.php</em> in your primary php script, before library.php is loaded</li>
|
|
|
32 |
<li>Edit <em>database.php</em> to clean up any problems</li>
|
| 1 |
rodolico |
33 |
</ol>
|
|
|
34 |
|
| 5 |
rodolico |
35 |
|
| 1 |
rodolico |
36 |
<h3>How it works</h3>
|
|
|
37 |
|
|
|
38 |
<p>Knowing how the program works is useful for knowing why things happen the way they do, and troubleshootin any problems</p>
|
|
|
39 |
<ol>
|
|
|
40 |
<li>Read entire input file into an array</li>
|
| 5 |
rodolico |
41 |
<li>Filter out comments</li>
|
| 1 |
rodolico |
42 |
<li>Remove all newlines from array</li>
|
|
|
43 |
<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>
|
|
|
44 |
<li>for each row in array
|
|
|
45 |
<ol>
|
| 5 |
rodolico |
46 |
<li>remove all back ticks</li>
|
| 1 |
rodolico |
47 |
<li>remove all duplicate spaces and all spaces before commas</li>
|
|
|
48 |
<li>find the table name (m/create table ([a-z0-9_]+)) and remember it</li>
|
|
|
49 |
<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>
|
|
|
50 |
<LI>Retrieve all Primary Key and Unique Key entries, save the Primary Key. Throw the matched lines away</LI>
|
|
|
51 |
<li>Split the remainder of the previous steps into an array on comma's. We should now only have column information</li>
|
|
|
52 |
<li>For each column definition
|
|
|
53 |
<ol>
|
|
|
54 |
<LI>determine if it is a primary key</LI>
|
|
|
55 |
<li>determine if it is nullable</li>
|
|
|
56 |
<li>determine if it has a default</li>
|
|
|
57 |
<li>if it is auto-increment, set it as read only</li>
|
|
|
58 |
<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>
|
|
|
59 |
</ol>
|
|
|
60 |
</li>
|
|
|
61 |
<li>Merge all of the information from the previous steps into one string</li>
|
|
|
62 |
</ol>
|
|
|
63 |
</li>
|
|
|
64 |
<li>Merge all of the row information (ie, the tables) into one string</li>
|
|
|
65 |
<li>put the variable definition hash delimters around the string. prepend some constants for placeholders</li>
|
|
|
66 |
<li>Print the whole thing out with the php wrappers around it</li>
|
|
|
67 |
</ol>
|
|
|
68 |
|
|
|
69 |
</body>
|
|
|
70 |
</html>
|
|
|
71 |
|
|
|
72 |
|