Subversion Repositories php_library

Rev

Rev 31 | Details | Compare with Previous | Last modification | View Log | RSS feed

Rev Author Line No. Line
31 rodolico 1 
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
1 rodolico 2 
<html>
31 rodolico 3 
  <head>
4 
    <title>sql2admin_hash.pl Manual</title>
5 
    <meta name="GENERATOR" content="Quanta Plus">
6 
    <meta http-equiv="Content-Type" content="text/html; charset=utf-8">
7 
  </head>
8 
  <body>
9 
    <h1>
10 
       sql2admin_hash.pl
11 
    </h1>
12 
    <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>
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>
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
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>
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
30 
    </p>
31 
    <p>
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>
35 
       Limitations
36 
    </h3>
37 
    <ul>
38 
      <li>
39 
         All char and varchar fields labeled string; boolean fields must be found and manually corrected
40 
      </li>
41 
      <li>
42 
         all other field types passed through to the resulting hash; you must manually determine the correct field type
43 
      </li>
44 
      <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>
95 
    </ul>
96 
    <h3>
97 
       Suggested Use
98 
    </h3>
99 
    <p>
100 
       It has been found useful to follow these steps to create the correct hash
101 
    </p>
102 
    <ol>
103 
      <LI>
104 
        <em>mysqldump --no-defaults --allow-keywords --no-data --skip-add-drop-table <strong>tablename</strong> | ./sql2admin_hash.pl > database.php</em>
105 
      </LI>
106 
      <li>
107 
         Include <em>database.php</em> in your primary php script, before library.php is loaded
108 
      </li>
109 
      <li>
110 
         Edit <em>database.php</em> to clean up any problems
111 
      </li>
112 
    </ol>
113 
    <h3>
114 
       How it works
115 
    </h3>
116 
    <p>
117 
       Knowing how the program works is useful for knowing why things happen the way they do, and troubleshootin any problems
118 
    </p>
119 
    <ol>
120 
      <li>
121 
         Read entire input file into an array
122 
      </li>
123 
      <li>
124 
         Filter out comments
125 
      </li>
126 
      <li>
127 
         Remove all newlines from array
128 
      </li>
129 
      <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
131 
      </li>
132 
      <li>
133 
         remove all entries that do not start with <em>create table</em>
134 
      </li>
135 
      <li>
136 
         for each row in array
1 rodolico 137 
      <ol>
31 rodolico 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>
161 
             determine if it is a primary key
162 
          </LI>
163 
          <li>
164 
             determine if it is nullable
165 
          </li>
166 
          <li>
167 
             determine if it has a default
168 
          </li>
169 
          <li>
170 
             if it is auto-increment, set it as read only
171 
          </li>
172 
          <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>
1 rodolico 180 
      </ol>
181 
      </li>
31 rodolico 182 
      <li>
183 
         Merge all of the row information (ie, the tables) into one string
184 
      </li>
185 
      <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>
189 
         Print the whole thing out with the php wrappers around it
190 
      </li>
191 
    </ol>
192 
  </body>
1 rodolico 193 
</html>