Code generator for model, store and grid from MySQL tables and views

[Frith]

Here is some quick and dirty code to generate a model, store and grid from a MySQL table or view.

Basically means you can concentrate on database design.

May even extend it to create the PHP page to retrieve the data.

Let me know if this is of use.

<?php    /*
     * Author: Paul Andrews
     * 
     */
    // Save to your web server, update the FIX parts below, and pass the table or view name in the url
    
    if(!isset($_REQUEST['tbl']))
    {
        echo "No table or view defined";
        exit();
    }
    
    $app = "<MYAPP>"; // <-- FIX
    
    $tbl = $_REQUEST['tbl'];
    
    $stub = strtolower($tbl);
    
    $stub = str_replace('tbl', '', $stub); // I prexix tables and views with tbl & vw respectively
    $stub = str_replace('vw', '', $stub);  // Ensure these strings only appear at the start of your table or view names
    
    $mdl = 'mdl'.ucfirst($stub);
    $store = 'jsStore'.ucfirst($stub);
    
    
    
    $query = "SELECT * FROM ".$tbl." LIMIT 1";
    
    $c = db_connect();
        
    $fields = mysql_fetch_fields($tbl, $c);
    
    
    //echo print_r($fields, true);
    
    
    db_disconnect($c);
    
    $columns = "";
    
    $out = "Ext.define('".$mdl."', {
      extend: 'Ext.data.Model',
    fields: [
";
    
    for($f=0;$f<count($fields);$f++)
    {
        $out .= "        { name:'".$fields[$f]->name."' ".$fields[$f]->jstype." },".PHP_EOL;
        $columns .= "        { text:'".$fields[$f]->name."', dataIndex: '".$fields[$f]->name."', ".$fields[$f]->xtype." },".PHP_EOL;
    }
    
    $out = substr($out,0,strlen($out) - 3);
    $columns = substr($columns,0,strlen($columns) - 3);
    
    $out .= PHP_EOL."    ]
});


Ext.define('".$app.".store.".$store."', {
    extend: 'Ext.data.Store',


    constructor: function(cfg) {
        var me = this;
        cfg = cfg || {};
        me.callParent([Ext.apply({
            model: '".$mdl."',
            storeId: ".$store."',
            proxy: {
                type: 'ajax',
                url: '/".$app."/php/get/get".ucfirst($stub).".php',
                extraParams: {
                    'node': 0,
                       'type': 'fldr'
                },
                reader: {
                    type: 'json',
                    root: 'rows',
                    totalProperty: 'total',
                    idProperty: ".$stub."'Id'
                }
                
            }
            
        }, cfg)]);
    }
});
";
    
    $grid = "
    
    
    Ext.define('".$app.".view.ui.grid".ucfirst($stub)."', {
    extend: 'Ext.grid.Panel',
    alias: 'widget.grid".$stub."',


    height: 250,
    width: 400,
    title: '".ucfirst($stub)." Grid',


    initComponent: function() {
        var me = this;


        Ext.applyIf(me, {
            store: ".$store.",
            viewConfig: {
                    stripeRows: true,
                    forceFit: true
                },
            columns: [
".$columns."
            ]
        });


        me.callParent(arguments);
    }
});




";
    
    
    echo $out;
    
    echo $grid;
    
    
    // Modified from original by [mewsterus at yahoo dot com] in user comments for mysql_fetch_field in PHP manual   
    function mysql_fetch_fields($table, $conn) {
        // LIMIT 1 means to only read rows before row 1 (0-indexed)
        $result = mysql_query("SELECT * FROM $table LIMIT 1", $conn);
        $describe = mysql_query("SHOW COLUMNS FROM $table", $conn);
        $num = mysql_num_fields($result);
        $output = array();
        for ($i = 0; $i < $num; ++$i) {
                $field = mysql_fetch_field($result, $i);
                // Analyze 'extra' field
                $field->auto_increment = (strpos(mysql_result($describe, $i, 'Extra'), 'auto_increment') === FALSE ? 0 : 1);
                // Create the column_definition
                $field->definition = mysql_result($describe, $i, 'Type');
                if ($field->not_null && !$field->primary_key) $field->definition .= ' NOT NULL';
                if ($field->def) $field->definition .= " DEFAULT '" . mysql_real_escape_string($field->def) . "'";
                if ($field->auto_increment) $field->definition .= ' AUTO_INCREMENT';
                if ($key = mysql_result($describe, $i, 'Key')) {
                        if ($field->primary_key) $field->definition .= ' PRIMARY KEY';
                        else $field->definition .= ' UNIQUE KEY';
                }
                // Create the field length
                $field->len = mysql_field_len($result, $i);
                
                $field->jstype = '';
                
                if(stripos($field->type,'text') > -1)
                {
                    $field->jstype = ", type:'string'";
                    $field->xtype = "xtype:'gridcolumn'";
                }
                
                if(stripos($field->type,'date') > -1)
                {
                    $field->jstype = ", type:'date', dateFormat: 'Y-m-d H:i:s'";
                    $field->xtype = "xtype:'datecolumn', format: 'D, d M y'";
                }
                
                if(stripos($field->type,'int') > -1)
                {
                    $field->jstype = ", type:'int'";
                    $field->xtype = "xtype:'numbercolumn'";
                }
                
                if(stripos($field->type,'char') > -1)
                {
                    $field->jstype = ", type:'string'";
                    $field->xtype = "xtype:'gridcolumn'";
                }
                
                if(stripos($field->type,'string') > -1)
                {
                    $field->jstype = ", type:'string'";
                    $field->xtype = "xtype:'gridcolumn'";
                }
                
                if(stripos($field->type,'float') > -1)
                {
                    $field->jstype = ", type:'float'";
                    $field->xtype = "xtype:'numbercolumn', format:'0'";
                }
                    
                if(stripos($field->type,'double') > -1)
                {
                    $field->jstype = "', type:'float'";
                    $field->xtype = "xtype:'numbercolumn', format:'0.00'";
                }    
                
                if(stripos($field->type,'decimal') > -1)
                {
                    $field->jstype = "', type:'float'";
                    $field->xtype = "xtype:'numbercolumn', format:'0.00'";
                }
                
                // Store the field into the output
                $output[] = $field;
        }
        return $output;
}


function db_connect()
        {    
            $svr = "localhost"; // <-- FIX
            
            $usr = "<user>"; // <-- FIX
            $pwd = "<pwd>"; // <-- FIX
            $db = "<db>"; // <-- FIX
            
            $conn = mysql_connect($svr,$usr,$pwd) or die("Could not connect");
                    
            mysql_select_db($db);
            
            $result = mysql_query("set names 'utf8'"); 
            
            return $conn;
        }
        
        function db_disconnect($conn)
        {
            mysql_close($conn);
        }    
    
?>

[predator]

I am using this little one for generating model fields definiton, it saves time when the tables are big... It is good that you have taken this to the next level... however I had always concernes about exposing actual column names to the front end...

<?php

class Utils extends CI_Controller {

    function index() {
        echo 'I love you too...';
    }

    public function buildExtModel() {
        /**
         * Here I am using a db class, but all you need is to execute statement like:
         * "DESCRIBE `TABLE`"
         */
        $fields = $this->db->field_data('DB TABLE');
        echo '<pre>';
        $mf = 'fields: [';
        foreach ($fields as $field) {
            $mf .= "{
                    name: '$field->name',
                    type: '" . $this->getExtModelType($field->type) . "'    
                    },";
        }
        $mf = mb_substr($mf, 0, -1);
        $mf .= ']';
        echo $mf;
    }

    private function getExtModelType($sqltype) {
        switch ($sqltype) {
            case 'int':
            case 'bigint':
            case 'smallint':
            case 'tinyint':
                $type = 'int';
                break;
            case 'decimal':
                $type = 'float';
                break;
            case 'varchar':
            case 'char':
                $type = 'string';
                break;
            case 'date':
            case 'datetime':
            case 'timestamp':
                $type = 'date';
                break;
            default:
                $type = 'auto';
        }
        return $type;
    }

}

[Frith]

I agree with you about the column names...that's why I tend to use views and obfuscate the column names. In fact, if I'm feeling really lazy, I make the column names in the view match the column headers on the grid.