#php #mysql modified the command line schema generator to be web based

mysql-schema-gen.php

<?php 

//example config as ref
$conf = '
// Set base database name
$DATABASE = "sample";

// Automatically create primary key column for tables?
$AUTOSERIAL = 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" ) )
);
';
$conf = isset( $_POST['conf'] )? $_POST['conf'] : $conf;
?>

<?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;
        $dump_file_ptrn = 'dump.db.%s.t-'.time().'.sql';
        
        
        /* disabling command line input
	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($_POST['conf'])) {
            file_put_contents('tmp.php', '<?php'.PHP_EOL . $conf . PHP_EOL.'?>');
            require 'tmp.php';
            
            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 ) );
                    $query = implode( chr(10) . chr(10), $sql ) . chr(10);
                    file_put_contents(sprintf($dump_file_ptrn, $DATABASE), $query);
            } else {
                    die( 'Table definitions missing' . chr(10) );
            }
        } //!empty($conf)


?>

<!DOCTYPE html>
<html>
    <head>
        <meta charset="utf-8">
        <title>MySQL Schema Generator :: Create</title>
        <style>
            html,body,form { height: 100%; }
            form { 
                width: 50%; 
                float: left; 
            }
            .output { 
                width: 48%;
                float: right;
            }
            textarea { 
                width: 100%;
                height: 80%;
                overflow: auto;
            }
            button {
                width: 100%;
                height: 40px;
                cursor: pointer;
            }
        </style>
    </head>
    <body>
        <form method="post">
            <textarea name="conf"><?php echo $conf; ?></textarea>
            <button type="submit">Show SQL and generate dump file</button>
        </form>
        
        <div class="output">
            <?php 
                echo '<pre>' . $query . '</pre>';
            ?>
        </div>
        <br clear="all">
    </body>
</html>