John's MySQL Tools

Background

MySQL is a wonderful database for small database applications and web based databases.  It is small, fast, reliable, predictable and above all, well documented.

Working with databases is always shitty work.  No matter how well you design the database in the first place, you always end up changing the structure later.  I learned a long time ago that if you keep a Data Dictionary then it makes changing your database just a little bit easier.  By Data Dictionary, I don't mean documentation I mean SQL CREATE TABLE statements.  This is a small text file which is easily placed into CVS with the rest of your project.  You can add extra comments in the file if you want.

One of the projects I am working on has a PHP back end which uses a MySQL database.  When I need to make changes to the database I edit my data dictionary and then, depending on what changes I have made and what data is already in the database either manually change the rows with SQL statements or drop all the tables and recreate them.  This is made easy by mysql by piping your data dictionary into mysql.

The problem I came across was that there comes a time when my changes have to be migrated over to the live system.  There is live data in the live system so dropping the database is not a solution.  Changing each column by hand is the only way to do it but I no longer know what has changed and what has not.  This is where the idea for this tool comes in.

Using the output of mysqldump and your data dictionary, this tool will produce the SQL statements required to make the database reflect the data dictionary.

Overview

I decided that because I was writing a PHP application, PHP would be a good language to write this tool in.  In the end it works fine but is not lightning fast.  I might port it to a real language one day.

Download

Download version 0.22 here.

Instructions

Being written in PHP there are a few requirements to run the program.  First you need a cgi version of PHP.  If you do not have the cgi version then you could adapt the script to run through your web server.

Second, the cgi version of PHP does not retain your current directory, instead it changes it to the directory of the script you are running.  For this reason you need to give the full path to the files you pass as parameters. (This has changed in new versions of PHP so you will have to check).

Assuming both the script and data dictionary are in your current directory

$ ./mysql-dict datadict.sql datadump.sql

datadump.sql is the output of mysqldump, generated like this

$ mysqldump -d database -u user --password=mypassword > datadump.sql

You can of course use whatever parameters are necessary such as -p --host etc.

mysql-dict can do the data dump for you if you add the following lines to your data dictionary

#database: database
#user: user
#password: mypassword
#debug: true

This means that you only have to supply one parameter to the mysql-dict script.

Sample data dictionary

This is an example data dictionary.  It is a fictitious database but should illustrate what the data dictionary will look like.  I also suggest you look at my SQL style.  Keeping you SQL readable will make life easier when you come back to change the database later.

 
--
-- datadict.sql
-- You can use this file with mysql-dict or you can pipe it through mysql directly
--

#database: sampledb
#user: username
#password: password
#host: localhost
#debug: true

-- A table to store user accounts
-- mysql-dict will ignore the DROP TABLE command but if you leave it there then you
-- can pipe this file through mysql and it will recreate the database.
-- I always name tables as tbl_xxxx.  This prevents name space conflicts
DROP TABLE IF EXISTS tbl_user;
CREATE TABLE tbl_user (
 username           VARCHAR(64)     NOT NULL,
 password           VARCHAR(64)     NOT NULL,
 first_name         VARCHAR(64)     NOT NULL,
 last_name          VARCHAR(64)     NOT NULL,
 last_update        TIMESTAMP,
 password_changed   ENUM('T','F')   NOT NULL DEFAULT 'F',
 email              VARCHAR(64)     NOT NULL,
 PRIMARY KEY (username)
);

-- Here is some initial data to populate the table with.  mysql-dict will ignore the INSERT statements
-- but mysql will process them if you pipe the file.
-- If you change the initial data, you can screen scrape from the DELETE FROM and then that data and
-- paste that into mysql which will save you 5 seconds of typing, or if you accidentally DELETE FROM the wrong table
-- could save you hours of restoring from backups.

-- DELETE FROM tbl_user;
INSERT INTO tbl_user VALUES ('demo', MD5('demo'), 'Demo', 'Account', NULL, 'Y','demo@localhost');
-- etc...


CREATE TABLE .... etc.

Feedback

This tool was developed for in house use but I am making it available under the GPL.  If you want to make changes I am happy to incorporate them into my published version but I don't have the time to add features that I don't need to use.

You can send your feedback to me John Newbigin at jn@it.swin.edu.au

Last modified 20050915
Maintained by John Newbigin