Subversion Repositories php_library

Rev

Rev 5 | Show entire file | Ignore whitespace | Details | Blame | Last modification | View Log | RSS feed

Rev 5 Rev 31
Line 1... Line 1...
1 
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
 1 
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
2 
<html>
 2 
<html>
3 
 
 - 
 
4 
<head>
 3 
  <head>
5 
  <title>sql2admin_hash.pl Manual</title>
 4 
    <title>sql2admin_hash.pl Manual</title>
6 
  <meta name="GENERATOR" content="Quanta Plus">
 5 
    <meta name="GENERATOR" content="Quanta Plus">
7 
  <meta http-equiv="Content-Type" content="text/html; charset=utf-8">
 6 
    <meta http-equiv="Content-Type" content="text/html; charset=utf-8">
8 
</head>
 7 
  </head>
9 
<body>
 8 
  <body>
10 
 
 9 
    <h1>
11 
<h1>sql2admin_hash.pl</h1>
 10 
       sql2admin_hash.pl
- 
 
 11 
    </h1>
12 
 
 12 
    <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>
 13 
       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
- 
 
 14 
    </p>
- 
 
 15 
    <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>
 16 
       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
- 
 
 17 
    </p>
- 
 
 18 
    <p>
15 
<p>If no filename is passed on the command line, reads stdin, so can be used as a filter.</p>
 19 
       If no filename is passed on the command line, reads stdin, so can be used as a filter.
- 
 
 20 
    </p>
- 
 
 21 
    <h3>
- 
 
 22 
       Syntax
16 
<h3>Limitations</h3>
 23 
    </h3>
- 
 
 24 
     sql2admin_hash.pl [-s] [-f<em>filename</em>]
- 
 
 25 
    <p>
- 
 
 26 
       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.
- 
 
 27 
    </p>
- 
 
 28 
    <p>
17 
 
 29 
       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
18 
<ul>
 30 
    </p>
- 
 
 31 
    <p>
19 
   <li>You must manually remove all insert statements from the dump file prior to running sql2admin_hash.pl</li>
 32 
       If <em>-s</em> is defined, it will define each table as an entry to be concated to the already defined variable.
- 
 
 33 
    </p>
- 
 
 34 
    <h3>
20 
   <LI>No knowledge of foreign keys; you must manually enter this information</LI>
 35 
       Limitations
- 
 
 36 
    </h3>
- 
 
 37 
    <ul>
- 
 
 38 
      <li>
21 
   <li>All char and varchar fields labeled string; boolean fields must be found and manually corrected</li>
 39 
         All char and varchar fields labeled string; boolean fields must be found and manually corrected
- 
 
 40 
      </li>
- 
 
 41 
      <li>
22 
   <li>all other field types passed through to the resulting hash; you must manually determine the correct field type</li>
 42 
         all other field types passed through to the resulting hash; you must manually determine the correct field type
- 
 
 43 
      </li>
- 
 
 44 
      <li>
23 
   <li>Integers will acquire the size of whatever mySQL reports on a dump</li>
 45 
         Integers will acquire the size of whatever mySQL reports on a dump
- 
 
 46 
      </li>
- 
 
 47 
      <li>
- 
 
 48 
        <em>Do not use commas or parentheses in comments</em>
- 
 
 49 
      </li>
- 
 
 50 
    </ul>
- 
 
 51 
    <h3>
- 
 
 52 
       Information Created
- 
 
 53 
    </h3>
- 
 
 54 
    <ul>
- 
 
 55 
      <li>
- 
 
 56 
        <em>comment</em> is inserted as a PHP comment for both fields and tables
- 
 
 57 
      </li>
- 
 
 58 
      <li>
- 
 
 59 
         Table Information
- 
 
 60 
      </li>
- 
 
 61 
      <ul>
- 
 
 62 
        <li>
- 
 
 63 
           Table Names are plugged into <em>display name</em>
- 
 
 64 
        </li>
- 
 
 65 
        <li>
- 
 
 66 
          <em>display query</em> and <em>display fields</em> contain all fields in table
- 
 
 67 
        </li>
- 
 
 68 
        <li>
- 
 
 69 
          <em>key field</em> populated if only one primary key defined in table
- 
 
 70 
        </li>
- 
 
 71 
      </ul>
- 
 
 72 
      <li>
- 
 
 73 
         Column Information
- 
 
 74 
      </li>
- 
 
 75 
      <ul>
- 
 
 76 
        <li>
- 
 
 77 
           Column Names are plugged into <em>display name</em>
- 
 
 78 
        </li>
- 
 
 79 
        <li>
- 
 
 80 
           If column defined with <em>references</em>, will build a <em>lookup</em> definitoin
- 
 
 81 
        </li>
- 
 
 82 
        <li>
- 
 
 83 
           Will honor <em>default</em>
- 
 
 84 
        </li>
- 
 
 85 
        <li>
- 
 
 86 
          <em>primary key</em> and <em>auto_increment</em> set <em>read only</em> to true
- 
 
 87 
        </li>
- 
 
 88 
        <li>
- 
 
 89 
          <em>not null</em> causes <em>read only</em> flag to be set
- 
 
 90 
        </li>
- 
 
 91 
        <li>
- 
 
 92 
           column width definition used to populate <em>width</em> parameter
- 
 
 93 
        </li>
- 
 
 94 
      </ul>
24 
</ul>
 95 
    </ul>
25 
 
 96 
    <h3>
26 
<h3>Suggested Use</h3>
 97 
       Suggested Use
- 
 
 98 
    </h3>
- 
 
 99 
    <p>
27 
<p>It has been found useful to follow these steps to create the correct hash</p>
 100 
       It has been found useful to follow these steps to create the correct hash
- 
 
 101 
    </p>
28 
<ol>
 102 
    <ol>
- 
 
 103 
      <LI>
29 
  <LI><em>mysqldump --no-defaults --allow-keywords --no-data --skip-add-drop-table <strong>tablename</strong>  | ./sql2admin_hash.pl > database.php</em>
 104 
        <em>mysqldump --no-defaults --allow-keywords --no-data --skip-add-drop-table <strong>tablename</strong> | ./sql2admin_hash.pl > database.php</em>
30 
  </LI>
 105 
      </LI>
- 
 
 106 
      <li>
31 
  <li>Include <em>database.php</em> in your primary php script, before library.php is loaded</li>
 107 
         Include <em>database.php</em> in your primary php script, before library.php is loaded
- 
 
 108 
      </li>
- 
 
 109 
      <li>
32 
  <li>Edit <em>database.php</em> to clean up any problems</li>
 110 
         Edit <em>database.php</em> to clean up any problems
- 
 
 111 
      </li>
33 
</ol>
 112 
    </ol>
34 
 
 - 
 
35 
 
 113 
    <h3>
36 
<h3>How it works</h3>
 114 
       How it works
- 
 
 115 
    </h3>
37 
 
 116 
    <p>
38 
<p>Knowing how the program works is useful for knowing why things happen the way they do, and troubleshootin any problems</p>
 117 
       Knowing how the program works is useful for knowing why things happen the way they do, and troubleshootin any problems
- 
 
 118 
    </p>
39 
<ol>
 119 
    <ol>
- 
 
 120 
      <li>
40 
   <li>Read entire input file into an array</li>
 121 
         Read entire input file into an array
- 
 
 122 
      </li>
- 
 
 123 
      <li>
41 
   <li>Filter out comments</li>
 124 
         Filter out comments
- 
 
 125 
      </li>
- 
 
 126 
      <li>
42 
   <li>Remove all newlines from array</li>
 127 
         Remove all newlines from array
- 
 
 128 
      </li>
- 
 
 129 
      <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>
 130 
         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
44 
   <li>for each row in array
 - 
 
45 
   <ol>
 131 
      </li>
46 
      <li>remove all back ticks</li>
 132 
      <li>
47 
      <li>remove all duplicate spaces and all spaces before commas</li>
 133 
         remove all entries that do not start with <em>create table</em>
48 
      <li>find the table name (m/create table ([a-z0-9_]+)) and remember it</li>
 134 
      </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>
 - 
 
- 
 
 135 
      <li>
52 
      <li>For each column definition
 136 
         for each row in array
53 
      <ol>
 137 
      <ol>
- 
 
 138 
        <li>
- 
 
 139 
           remove all back ticks
- 
 
 140 
        </li>
- 
 
 141 
        <li>
- 
 
 142 
           remove all duplicate spaces and all spaces before commas
- 
 
 143 
        </li>
- 
 
 144 
        <li>
- 
 
 145 
           find the table name (m/create table ([a-z0-9_]+)) and remember it
- 
 
 146 
        </li>
- 
 
 147 
        <li>
- 
 
 148 
           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
- 
 
 149 
        </li>
- 
 
 150 
        <LI>
- 
 
 151 
           Retrieve all Primary Key and Unique Key entries, save the Primary Key. Throw the matched lines away
- 
 
 152 
        </LI>
- 
 
 153 
        <li>
- 
 
 154 
           Split the remainder of the previous steps into an array on comma's. We should now only have column information
- 
 
 155 
        </li>
- 
 
 156 
        <li>
- 
 
 157 
           For each column definition
- 
 
 158 
        <ol>
- 
 
 159 
          <li>If <em>references</em> defined, set as lookup, othewise, do the following</li>
- 
 
 160 
          <LI>
54 
         <LI>determine if it is a primary key</LI>
 161 
             determine if it is a primary key
- 
 
 162 
          </LI>
- 
 
 163 
          <li>
55 
         <li>determine if it is nullable</li>
 164 
             determine if it is nullable
- 
 
 165 
          </li>
- 
 
 166 
          <li>
56 
         <li>determine if it has a default</li>
 167 
             determine if it has a default
- 
 
 168 
          </li>
- 
 
 169 
          <li>
57 
         <li>if it is auto-increment, set it as read only</li>
 170 
             if it is auto-increment, set it as read only
- 
 
 171 
          </li>
- 
 
 172 
          <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>
 173 
             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
- 
 
 174 
          </li>
- 
 
 175 
        </ol>
- 
 
 176 
        </li>
- 
 
 177 
        <li>
- 
 
 178 
           Merge all of the information from the previous steps into one string
- 
 
 179 
        </li>
59 
      </ol>
 180 
      </ol>
60 
      </li>
 181 
      </li>
- 
 
 182 
      <li>
61 
      <li>Merge all of the information from the previous steps into one string</li>
 183 
         Merge all of the row information (ie, the tables) into one string
62 
   </ol>
 184 
      </li>
63 
   </li>
 185 
      <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>
 186 
         <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
- 
 
 187 
      </li>
- 
 
 188 
      <li>
66 
   <li>Print the whole thing out with the php wrappers around it</li>
 189 
         Print the whole thing out with the php wrappers around it
- 
 
 190 
      </li>
67 
</ol>
 191 
    </ol>
68 
 
 - 
 
69 
</body>
 192 
  </body>
70 
</html>
 193 
</html>
71 
 
 - 
 
72 
 
 -