| Line 10... |
Line 10... |
| 10 |
|
10 |
|
| 11 |
<h1>sql2admin_hash.pl</h1>
|
11 |
<h1>sql2admin_hash.pl</h1>
|
| 12 |
|
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>
|
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>
|
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>
|
| - |
|
15 |
<p>If no filename is passed on the command line, reads stdin, so can be used as a filter.</p>
|
| 15 |
<h3>Limitations</h3>
|
16 |
<h3>Limitations</h3>
|
| 16 |
|
17 |
|
| 17 |
<ul>
|
18 |
<ul>
|
| 18 |
<li>You must manually remove all comments and table definition information from the dump file prior to running sql2admin_hash.pl</li>
|
- |
|
| 19 |
<li>You must manually remove all insert statements from the dump file prior to running sql2admin_hash.pl</li>
|
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>
|
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>
|
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>
|
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>
|
23 |
<li>Integers will acquire the size of whatever mySQL reports on a dump</li>
|
| 24 |
</ul>
|
24 |
</ul>
|
| 25 |
|
25 |
|
| 26 |
<h3>Suggested Use</h3>
|
26 |
<h3>Suggested Use</h3>
|
| 27 |
<p>It has been found useful to follow these steps to create the correct hash</p>
|
27 |
<p>It has been found useful to follow these steps to create the correct hash</p>
|
| 28 |
<ol>
|
28 |
<ol>
|
| 29 |
<LI>Create a dump file with mysqldump, sending the output to a temporary file</LI>
|
- |
|
| 30 |
<li>Manually edit the temporary file, removing all comments, structure information and insert statements. Note, the resulting file should contain only create table tablename ( column def, column def, primaary key (pk def));</li>
|
29 |
<LI><em>mysqldump --no-defaults --allow-keywords --no-data --skip-add-drop-table <strong>tablename</strong> | ./sql2admin_hash.pl > database.php</em>
|
| 31 |
<li>Execute sql2admin_hash.pl <em>tempfile</em> > <em>resultfile</em></li>
|
- |
|
| - |
|
30 |
</LI>
|
| 32 |
<li>Include <em>resultfile</em> in your primary php script, before library.php is loaded</li>
|
31 |
<li>Include <em>database.php</em> in your primary php script, before library.php is loaded</li>
|
| 33 |
<li>Edit <em>resultfile</em> to clean up any problems</li>
|
32 |
<li>Edit <em>database.php</em> to clean up any problems</li>
|
| 34 |
</ol>
|
33 |
</ol>
|
| 35 |
|
34 |
|
| - |
|
35 |
|
| 36 |
<h3>How it works</h3>
|
36 |
<h3>How it works</h3>
|
| 37 |
|
37 |
|
| 38 |
<p>Knowing how the program works is useful for knowing why things happen the way they do, and troubleshootin any problems</p>
|
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>
|
39 |
<ol>
|
| 40 |
<li>Read entire input file into an array</li>
|
40 |
<li>Read entire input file into an array</li>
|
| - |
|
41 |
<li>Filter out comments</li>
|
| 41 |
<li>Remove all newlines from array</li>
|
42 |
<li>Remove all newlines from array</li>
|
| 42 |
<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>
|
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>
|
| 43 |
<li>for each row in array
|
44 |
<li>for each row in array
|
| 44 |
<ol>
|
45 |
<ol>
|
| - |
|
46 |
<li>remove all back ticks</li>
|
| 45 |
<li>remove all duplicate spaces and all spaces before commas</li>
|
47 |
<li>remove all duplicate spaces and all spaces before commas</li>
|
| 46 |
<li>find the table name (m/create table ([a-z0-9_]+)) and remember it</li>
|
48 |
<li>find the table name (m/create table ([a-z0-9_]+)) and remember it</li>
|
| 47 |
<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>
|
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>
|
| 48 |
<LI>Retrieve all Primary Key and Unique Key entries, save the Primary Key. Throw the matched lines away</LI>
|
50 |
<LI>Retrieve all Primary Key and Unique Key entries, save the Primary Key. Throw the matched lines away</LI>
|
| 49 |
<li>Split the remainder of the previous steps into an array on comma's. We should now only have column information</li>
|
51 |
<li>Split the remainder of the previous steps into an array on comma's. We should now only have column information</li>
|