#php #mysql - mysql schema generator

example-schema-def.php

<?php

// 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 example-schema-def.php

mysql-simpleschemagen.php

<?php

	/**
	 * @copyright 2012 James Linden <[email protected]>
	 * @author James Linden <[email protected]>
	 * @url http://jameslinden.com/code/mysql/schema-generator
	 * @license BSD (2 clause) <http://www.opensource.org/licenses/BSD-2-Clause>
	 */

	error_reporting( E_ALL ^E_NOTICE );
	ini_set( 'display_errors', true );
	ini_set( 'html_errors', false );

	$DATABASE = null;
	$TABLES = array();
	$CHANGELOG = false;
	$UTILITY = false;
	$AUTOSERIAL = true;
	$TABLE_PREFIX = '';
	$COLUMN_PREFIX = '';
	$HAS_ID_COLUMN_TABLE_NAME_PREFIX = true;
	$HAS_ID_COLUMN_TABLE_NAME_PREFIX = false;

	if( count( $_SERVER['argv'] ) != 2 ) {
		die( 'Usage: ' . $_SERVER['argv'][0] . ' schema_definitions_file' . chr(10) );
	}

	$f = $_SERVER['argv'][1];
	if( is_readable( $f ) ) {
		include $f;
	} else {
		die( 'Unable to load specified schema definition file' . chr(10) );
	}

        if( empty( $DATABASE ) ) {
                die( 'Database name must be specified: $DATABASE' . chr(10) );
        }

        if( !is_array( $TABLES ) || count( $TABLES ) == 0 ) {
                die( 'Table data must be specified: $TABLES' . chr(10) );
        }

        /**************************************************************************************************************************/

        function defval( $s ) {
                if( !in_array( $s, array('FALSE','TRUE','NULL') ) ) {
                        $s = is_numeric( $s ) ? $s : "'" . $s . "'";
                }
                return $s;
        }

        function array_value_maxlength( $s ) {
                $i = 0;
                if( is_array( $s ) ) {
                        foreach( $s as $k ) {
                                $i = strlen( $k ) > $i ? strlen( $k ) : $i;
                        }
                }
                return $i;
        }

        if( is_array( $TABLES ) && count( $TABLES ) > 0 ) {
                $main = array();
                foreach( $TABLES as $tn => $cds ) {
                        $id_col_name = ($HAS_ID_COLUMN_TABLE_NAME_PREFIX)? $tn.'_id' : 'id';
                        $tmain = $cols = array();
                        if( $AUTOSERIAL ) {
                                $tmain[] = $id_col_name . ' SERIAL PRIMARY KEY';
                        }
                        foreach( $cds as $cd ) {
                                $cols[] = $cd['name'];
                                $s1 = $s2 = $COLUMN_PREFIX . $cd['name'];
                                if( in_array( $cd['type'], array( 'TEXT', 'MEDIUMTEXT', 'LONGTEXT' ) ) ) {
                                        $s1 .= ' ' . $cd['type'];
                                        $s2 .= ' ' . $cd['type'];
                                } else if( in_array( $cd['type'], array( 'SET', 'ENUM' ) ) ) {
                                        $s1 .= ' ' . $cd['type'] . "('" . implode( "','", $cd['values'] ) . "') DEFAULT NULL";
                                        $s2 .= ' VARCHAR(' . array_value_maxlength( $cd['values'] ) . ')';
                                } else if( array_key_exists( 'reference', $cd ) && !empty( $cd['reference'] ) ) {
                                        $s1 .= ' BIGINT UNSIGNED DEFAULT NULL REFERENCES ' . $TABLE_PREFIX . $cd['reference'] . ' ('. $id_col_name .')';
                                        $s2 .= ' BIGINT UNSIGNED DEFAULT NULL';
                                } else {
                                        $s1 .= ' ' . $cd['type'];
                                        if( array_key_exists( 'default', $cd ) && !empty( $cd['default'] ) ) {
                                                $s1 .= ' DEFAULT ' . defval( $cd['default'] );
                                        } else {
                                                $s1 .= ' DEFAULT NULL';
                                        }
                                        $s2 .= ' ' . $cd['type'] . ' DEFAULT NULL';
                                }
                                if( array_key_exists( 'unique', $cd ) ) {
                                        $s1 .= ' UNIQUE';
                                }
                                $tmain[] = $s1;
                                $tarchive[] = $s2;
                        }
                        $s1 = 'CREATE TABLE IF NOT EXISTS ' . $TABLE_PREFIX . $tn . ' (';
                        $s2 = ') ENGINE=InnoDB;';
                        $main[] = $s1 . chr(10) . chr(9) . implode( ',' . chr(10) . chr(9), $tmain ) . chr(10) . $s2 . chr(10);
                }
                $sql = array();
                $sql[] = 'DROP DATABASE IF EXISTS ' . $DATABASE . ';';
                $sql[] = 'CREATE DATABASE ' . $DATABASE . ' DEFAULT CHARACTER SET utf8 DEFAULT COLLATE utf8_bin;';
                $sql[] = 'USE ' . $DATABASE . ';';
                $sql[] = trim( implode( chr(10), $main ) );
                print implode( chr(10) . chr(10), $sql ) . chr(10);
        } else {
                die( 'Table definitions missing' . chr(10) );
        }


?>