Resume
Facebook
LinkedInMySQL/PHP Data Dictionary Script
June 5, 2010Last Update: February 4, 2012
| Tweet |
Download Script Here (20kb)
This free script is designed to help database administrators, system administrators, and web developers generate data dictionaries, and determine a leaner database hierarchy.
The current version, 3.0, adds several enhancements, please see the (changelog).
Several shortcomings have been corrected in this version, including security, aesthetics, and functionality.
Because it interfaces directly with MySQL, you can allow users to use this script and it will respect the permissions from the DBMS.
Like any software that interacts with your back end, you should make sure the software is secure and operate it only over SSL. You should only allow it to be used by trusted users and inside of a secured directory.
Logging into MySQL
Selecting a Database
Selecting a Table
Viewing the Dictionary
Changelog
v3.0 - Major Revision, Bug Fixes and Code Updates February 4, 2012
- Code updates to improve ease of reading, reduce filesize, and remove pointless characters.
- Aesthetic improvements including some shadows and gradients.
- Better Cross-Browser support.
- Removal of built-in print button, using external print stylesheet.
- Migration of built-in stylesheet to external.
- Fixed annoying bug with number_format where null, non-numeric fields would cause a PHP Warning.
- Negation of use of arrays for the SmartType processor to reduce needless memory consumption and element resolution.
- Escaping of database, table, and fields to allow use with poorly named fields in SmartType area.
- Updated header to "noindex, nofollow, nocache, noarchive" so that search engines do not cache the page.
- Changed error handling for queries to follow error handling specified by PHP.INI, instead of dumping to the screen.
- Header and footer functions seperate instead of "hugging" the page.
- Made Robert-Lerner link open in new tab.
- Log Out now takes you directly to the login page.
- Now requires Mcrypt extension to protect server/username/password combination. The less-secure version 2.1 can be downloaded here that doesn't require Mcrypt.
- Updated login form security information, and enhanced use by defaulting to the password box.
- Added "about" page, and moved changelog to there.
- Added license fetching to the about page for systems that support it.
- Added donations button to about page.
- Initialize all variables to prevent register_globals exploits, and to remove some notices.
- Update version from invalid "1.1" to "2.1".
- Fix slight bug in SmartType while determining MEDIUM INT field.
- Fixed Table Column Headers "Field, Type, Null, Key, Null, Extra" to "Field, Type, Null, Key, Default, Extra".
- Tables are sorted into colums
- Addition of:
- Min Field (Shows minimum numeric entry for a numeric field, minimum character length for a string field, nothing for date field).
- Max Field (Shows maximum numeric entry for a numeric field, maximum character length for a string field, nothing for a date field)
- SmartType (Makes presumptions about the current dataset to give advice on new ways to structure columns. While pretty good, some issues exist, notibly:)
- For speed, decimal fields are interpreted as a numeric field with 4-fixed floating digits, and displayed that way in MIN and MAX, however SmartType does not do a full table scan, so it cannot determine if there is a need for a float/decimal field. To be implemented later.
- Does not account for future changes, it shows the minimum required field type for the data present. A good example of when you should ignore the suggestions is in the creation of a user table, or an empty table. If you use a TINYINT UNSIGNED for the user_id column of your database (either because you just made the database, or it hasn't been used enough) then you will be limited to 255 users. Always run this on a well-used database and make your own conclusions about the desired dataset.
- Suggests numeric lengths (tinyint(1), tinyint(2), etc). They're not really needed, just keeping it with what people are used to.
- Time/Date fields have no interpretation, and are simply returned the same as they were provided.
- All string fields under 255 characters that have a dynamic range (min and max are not the same), and also do not use an INDEX will return as a varchar(MAX).
- All string fields under 255 characters that have a static range (min and max are the same) will return as CHAR(MAX).
- All numeric fields are first checked if they can be used as UNSIGNED if their MIN is >= Zero.
- Totally doesn't care what TINYBLOB, BLOB, MEDIUMBLOB, LONGBLOB, ENUM, and SET do.
- Print Mode (Drops some of the styling off so that it may be printed with background colors on and preserve ink.)
Download Script Here (20kb)
| Posted: Friday, September 10, 2010 at 3:52:52 PM |
| By: |
| thks a lot |