James Linden

~# linux ninja / web dev geek / robotics nerd / idea machine / N6NRD

Code / Schema Generator for MySQL

Schema Generator for MySQL

Overview

The Schema Generator for MySQL is a PHP script for generating and maintaining schemas when building applications utilizing MySQL.

There are two generator scripts:

Environment

  • GNU/Linux
  • PHP 5.3 +
  • MySQL 5.x

Notes

  • This script is designed to run on the command line.

Basic Usage

The schema configuration input is a simple PHP array system, saved as a normal PHP script.

A database column is defined by an associative array:

$column = array( 'name' => 'email', 'type' => 'VARCHAR(128)', 'default' => 'NULL' );

A table is simply an array of column items:

$table = array(
array( 'name' => 'name', 'type' => 'VARCHAR(64)', 'default' => 'NULL' ),
array( 'name' => 'email', 'type' => 'VARCHAR(128)', 'default' => 'NULL' )
);

There are a few special notations available:

  • To enforce a unique requirement on the column simply set that key to true:

    $column = array( 'name' => 'email', 'type' => 'VARCHAR(128)', 'default' => 'NULL', 'unique' => true );
  • For a foreign key column which points to the primary key column of another table, it is sufficient to define the name of the column and the table it points to:

    $column = array( 'name' => 'category', 'reference' => 'category' );
  • For an ENUM or SET column type, you must define the value list as an array:

    $column = array( 'name' => 'language', 'type' => 'ENUM', 'default' => 'NULL', 'values' => array( 'eng', 'fre', 'ger', 'spa' ) );

Primary key fields are automatically created, so don't include them in your table definitions.

Howto

For the purposes of this document, we'll create a schema with a single user table with the following structure:

name type features description
id integer auto increment, primary key The unique record ID
parent integer foreign key The primary account this account is under
user varchar unique Username
pass varchar Password
name varchar Friendly name
email varchar Email address
access enum admin, user, or none Access level

We create a clean PHP script called schema_def.php. This is the configuration input for the generator scripts.

First, we set some basic feature options:

# Set base database name
$DATABASE = 'sample';

# Automatically create primary key column for tables?
$AUTOSERIAL = true;

# Create log of changes (for advanced schema only)
$CHANGELOG = true;

Then, we define our user table:

# Create database table 'user'
$TABLES['user'] = array(
array( 'name' => 'parent', 'reference' => 'user' ),
array( 'name' => 'user', 'type' => 'VARCHAR(64)', 'default' => 'NULL', 'unique' => true ),
array( 'name' => 'pass', 'type' => 'VARCHAR(64)', 'default' => 'NULL' ),
array( 'name' => 'name', 'type' => 'VARCHAR(128)', 'default' => 'NULL' ),
array( 'name' => 'email', 'type' => 'VARCHAR(128)', 'default' => 'NULL' ),
array( 'name' => 'access', 'type' => 'ENUM', 'default' => 'NULL', 'values' => array( 'admin', 'user', 'none' ) )
);

After saving the file, we can run the script and pass it the filename:

./mysql-simpleschemagen.php schema_def.php

mysql-simpleschemagen will generate the following schema:

DROP DATABASE IF EXISTS sample;

CREATE DATABASE sample DEFAULT CHARACTER SET utf8 DEFAULT COLLATE utf8_bin;

USE sample;

CREATE TABLE IF NOT EXISTS t_user (
c_id SERIAL PRIMARY KEY,
c_parent BIGINT UNSIGNED DEFAULT NULL REFERENCES t_user (c_id),
c_user VARCHAR(64) DEFAULT NULL UNIQUE,
c_pass VARCHAR(64) DEFAULT NULL,
c_name VARCHAR(128) DEFAULT NULL,
c_email VARCHAR(128) DEFAULT NULL,
c_access ENUM('admin','user','none') DEFAULT NULL
) ENGINE=InnoDB;

mysql-advschemagen will generate the following schema:

DROP DATABASE IF EXISTS sample_storage;

CREATE DATABASE sample_storage DEFAULT CHARACTER SET utf8 DEFAULT COLLATE utf8_bin;

DROP DATABASE IF EXISTS sample_archive;

CREATE DATABASE sample_archive DEFAULT CHARACTER SET utf8 DEFAULT COLLATE utf8_bin;

DROP DATABASE IF EXISTS sample_log;

CREATE DATABASE sample_log DEFAULT CHARACTER SET utf8 DEFAULT COLLATE utf8_bin;

USE sample_log;

CREATE TABLE IF NOT EXISTS t_change (
c_id SERIAL PRIMARY KEY,
c_user BIGINT UNSIGNED DEFAULT NULL,
c_datetime DATETIME DEFAULT NULL,
c_action ENUM('i','u','d') DEFAULT NULL,
c_table VARCHAR(32) DEFAULT NULL,
c_record BIGINT UNSIGNED DEFAULT NULL,
c_version INT UNSIGNED DEFAULT NULL
) ENGINE=InnoDB;

USE sample_archive;

CREATE TABLE IF NOT EXISTS t_user (
c_id SERIAL PRIMARY KEY,
c__version INT UNSIGNED DEFAULT NULL,
c__record BIGINT UNSIGNED DEFAULT NULL,
c_parent BIGINT UNSIGNED DEFAULT NULL,
c_user VARCHAR(64) DEFAULT NULL,
c_pass VARCHAR(64) DEFAULT NULL,
c_name VARCHAR(128) DEFAULT NULL,
c_email VARCHAR(128) DEFAULT NULL,
c_access VARCHAR(5)
) ENGINE=InnoDB;

USE sample_storage;

CREATE TABLE IF NOT EXISTS t_user (
c_id SERIAL PRIMARY KEY,
c__version INT UNSIGNED DEFAULT NULL,
c_parent BIGINT UNSIGNED DEFAULT NULL REFERENCES t_user (c_id),
c_user VARCHAR(64) DEFAULT NULL UNIQUE,
c_pass VARCHAR(64) DEFAULT NULL,
c_name VARCHAR(128) DEFAULT NULL,
c_email VARCHAR(128) DEFAULT NULL,
c_access ENUM('admin','user','none') DEFAULT NULL
) ENGINE=InnoDB;

DELIMITER $$

CREATE TRIGGER bi_user BEFORE INSERT ON t_user
FOR EACH ROW BEGIN
SET NEW.c__version = 1;
END $$
$$

CREATE TRIGGER ai_user AFTER INSERT ON t_user
FOR EACH ROW BEGIN
INSERT INTO sample_log.t_change (c_user,c_datetime,c_action,c_table,c_record,c_version) VALUES (@user_id,NOW(),'i','user',NEW.c_id,NEW.c__version);
END $$
$$

CREATE TRIGGER bu_user BEFORE UPDATE ON t_user
FOR EACH ROW BEGIN
INSERT INTO sample_archive.t_user (c__record,c__version,c_parent,c_user,c_pass,c_name,c_email,c_access) VALUES (OLD.c_id,OLD.c__version,OLD.c_parent,OLD.c_user,OLD.c_pass,OLD.c_name,OLD.c_email,OLD.c_access);
SET NEW.c__version = (OLD.c__version + 1);
END $$
$$

CREATE TRIGGER au_user AFTER UPDATE ON t_user
FOR EACH ROW BEGIN
INSERT INTO sample_log.t_change (c_user,c_datetime,c_action,c_table,c_record,c_version) VALUES (@user_id,NOW(),'u','user',OLD.c_id,NEW.c__version);
END $$
$$

CREATE TRIGGER bd_user BEFORE DELETE ON t_user
FOR EACH ROW BEGIN
INSERT INTO sample_archive.t_user (c__record,c__version,c_parent,c_user,c_pass,c_name,c_email,c_access) VALUES (OLD.c_id,OLD.c__version,OLD.c_parent,OLD.c_user,OLD.c_pass,OLD.c_name,OLD.c_email,OLD.c_access);
END $$
$$

CREATE TRIGGER ad_user AFTER DELETE ON t_user
FOR EACH ROW BEGIN
INSERT INTO sample_log.t_change (c_user,c_datetime,c_action,c_table,c_record,c_version) VALUES (@user_id,NOW(),'d','user',OLD.c_id,OLD.c__version);
END $$
$$

DELIMITER ;

Use

When using databases made using mysql-simpleschemagen.php, there is no special functionality required to use the database.

When using databases made using mysql-advschemagen.php, you must set the current user before any SQL statement, or at the beginning of a transaction:

SET @user_id = 1;

This is will attribute all record events to that user in the change log.

License

This project is BSD (2 clause) licensed.

photo of James Linden
Founder / Head Geek
Digital Dock, LLC
aka kodekrash & N6NRD
Perkasie, PA USA

What I Do

Linux administration/virtualization
Datacenter management
Web development

Full CV

What I've Done

Drowned a motorcycle
Rescued a skunk
Built Prime GNU/Linux
Contributed to Spidering Hacks