sql2admin_hash.pl

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

sql2admin_hash.pl is run as sql2admin_hash.pl sqlFileName 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

If no filename is passed on the command line, reads stdin, so can be used as a filter.

Syntax

Will read filename, an SQL create file, and process it. STDIN is used if filename is not defined. Will send processed file to STDOUT.

If -s 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 -s, sql2admin_has.pl will create a full definition, defining various constants, defining $DATABASE_DEFINITION as a global, then populating it with constants

If -s is defined, it will define each table as an entry to be concated to the already defined variable.

Limitations

• All char and varchar fields labeled string; boolean fields must be found and manually corrected
• all other field types passed through to the resulting hash; you must manually determine the correct field type
• Integers will acquire the size of whatever mySQL reports on a dump
• Do not use commas or parentheses in comments

Information Created

• comment is inserted as a PHP comment for both fields and tables
• Table Information
• Table Names are plugged into display name
• display query and display fields contain all fields in table
• key field populated if only one primary key defined in table
• Column Information
• Column Names are plugged into display name
• If column defined with references, will build a lookup definitoin
• Will honor default
• primary key and auto_increment set read only to true
• not null causes read only flag to be set
• column width definition used to populate width parameter

Suggested Use

It has been found useful to follow these steps to create the correct hash

1. mysqldump --no-defaults --allow-keywords --no-data --skip-add-drop-table tablename | ./sql2admin_hash.pl > database.php
2. Include database.php in your primary php script, before library.php is loaded
3. Edit database.php to clean up any problems

How it works

Knowing how the program works is useful for knowing why things happen the way they do, and troubleshootin any problems

1. Read entire input file into an array
2. Filter out comments
3. Remove all newlines from array
4. 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
5. remove all entries that do not start with create table
6. for each row in array
   1. remove all back ticks
   2. remove all duplicate spaces and all spaces before commas
   3. find the table name (m/create table ([a-z0-9_]+)) and remember it
   4. 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
   5. Retrieve all Primary Key and Unique Key entries, save the Primary Key. Throw the matched lines away
   6. Split the remainder of the previous steps into an array on comma's. We should now only have column information
   7. For each column definition
      1. If references defined, set as lookup, othewise, do the following
      2. determine if it is a primary key
      3. determine if it is nullable
      4. determine if it has a default
      5. if it is auto-increment, set it as read only
      6. 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
   8. Merge all of the information from the previous steps into one string
7. Merge all of the row information (ie, the tables) into one string
8. If not run with the -s parameter, put the variable definition hash delimters around the string. prepend some constants for placeholders
9. Print the whole thing out with the php wrappers around it