[置顶] Mysql DBMigrate Class -- 增加外键支持功能

 简化 这个步骤: 在程序运行时动态增加表 以及建立表的关系 结构

<?php// author: sese vb2005xu.iteye.com // Mysql DBMigrate Class class XDBTable {public $name = null ,$params = array(), $columns = array() ,$pri_keys = array() ,$feign_keys = array();public function __construct($name = null,$primary_key = null){$this->name = $name ;$this->primary_key = $primary_key ;}public function column($column_obj){if (!empty($column_obj) && is_array($column_obj) && $column_obj['name']){$this->columns[$column_obj['name']] = $column_obj ;}}public function add_column($name,$type,$limit=null,$null=true,$default=null,$unique=false){if (empty($name) || empty($type)) return ;$this->column(array('name' => $name ,'type' => $type ,'limit' => $limit ,'null' => $null ,'default' => $default ,'unique' => $unique ,));}public function timestamps(){//添加时间戳字段$this->add_column('created_at','datetime');$this->add_column('updated_at','datetime');}public function remove_column($name){if (empty($name)) return ;if ($this->feign_keys[$name])unset($this->feign_keys[$name]);unset($this->columns[$name]);}public function primary_keys($pri_keys){ $this->pri_keys = $pri_keys ;}public function foreign_keys($feign_keys){ $this->feign_keys = $feign_keys ;}public function foreign_key($field,$refer_tb,$refer_field,$fk_id=null,$on_action=null){if(!empty($field) && !empty($refer_tb) && !empty($refer_field)){if (empty($fk_id)) $fk_id = "fk_tb__{$this->name}\${$field}__{$refer_tb}\${$refer_field}" ;if (empty($on_action))$on_action = XMysqlConst::$FK_ON_NO_TODO ;  $this->feign_keys[$field] = array($refer_tb,$refer_field,$fk_id,$on_action) ;}}public function extra_params($params){if (is_array($params)) {$this->params = array_merge($this->params, $params);$this->params = array_unique($this->params);}}public function extra_params_mysql($engine='MyISAM',$charset='utf8',$collate='utf8_unicode_ci'){$this->extra_params(array('engine' => $engine ,'default charset' => $charset ,'collate' => $collate));}}/** * Mysql 常量类 * */class XMysqlConst {public static $Engine_InnoDB = 'InnoDB';public static $Engine_MyISAM = 'MyISAM';public static $FK_ON_NO_TODO = '' ;//拒绝对父表的删除或更新操作public static $FK_ON_DELETE_NO_ACTION = 'ON DELETE NO ACTION' ;public static $FK_ON_UPDATE_NO_ACTION = 'ON UPDATE NO ACTION' ;//拒绝对父表的删除或更新操作public static $FK_ON_DELETE_RESTRICT = 'ON DELETE RESTRICT' ;public static $FK_ON_UPDATE_RESTRICT = 'ON UPDATE RESTRICT' ;//从父表删除或更新且自动删除或更新子表中匹配的行public static $FK_ON_DELETE_CASCADE = 'ON DELETE CASCADE' ;public static $FK_ON_UPDATE_CASCADE = 'ON UPDATE CASCADE' ;//从父表删除或更新行，并设置子表中的外键列为NULL ,外键列没有指定NOT NULL限定词public static $FK_ON_DELETE_SET_NULL = 'ON DELETE SET NULL' ;public static $FK_ON_UPDATE_SET_NULL = 'ON UPDATE SET NULL' ;}class DBMigrate {private $tb_name = null ,$tb_columns = array() ,$params = array() ,$tb_sql = array();private $tb_primary_keys = array() ,$str_primary_keys = '' ;private $tb_foreign_keys = array() ,$str_foreign_keys = '';private function __construct(){}/** * Helper_DBMigrate 单态方法 * * @param XDBTable $x_table * @return Helper_DBMigrate */public static function instance($x_table=null){static $instance;        if (is_null($instance))        {            $instance = new DBMigrate();        }        if ($x_table)        $instance->init($x_table);        return $instance;}/** * Helper_DBMigrate 单态方法 * * @param XDBTable $x_table */private function init($x_table){if (!$x_table || !($x_table instanceof XDBTable)) return ;if (empty($x_table->name) || trim($x_table->name) == '')throw new Exception("XDBTable::table_name cannot set null!");$this->tb_columns = array();$this->x_table = $x_table ;$this->tb_name = $x_table->name ;$this->array_remove_empty($x_table->pri_keys);$this->tb_primary_keys = $x_table->pri_keys ;$this->array_remove_empty($x_table->feign_keys);$this->tb_foreign_keys = $x_table->feign_keys ;$this->array_remove_empty($x_table->params);if (!empty($x_table->params)){$this->params = array('engine' => null ,'default charset' => null ,'collate' => null);foreach ($this->params as $k=>$v){$this->params[$k] = $x_table->params[$k];}}if (!empty($this->tb_primary_keys)){$this->parse_primary_keys($this->tb_primary_keys);}//外键解析必须放置在 参数解析的后面,对mysql,仅仅innodb引擎才支持外键if (!empty($this->tb_foreign_keys)){dump($this->tb_foreign_keys,"表 [$this->tb_name] 外键: ");$this->parse_foreign_keys($this->tb_foreign_keys);}if (!empty($x_table->columns)){foreach ($x_table->columns as $column_obj){$this->parse_column_obj($column_obj);}}}private function array_remove_empty(& $arr, $trim = true){    foreach ($arr as $key => $value) {        if (is_array($value)) {            $this->array_remove_empty($arr[$key]);        } else {            $value = trim($value);            if ($value == '') {                unset($arr[$key]);            } elseif ($trim) {                $arr[$key] = $value;            }        }    }}public function to_sql($key=null,$tb_name=null){$tb_name = $tb_name ? $tb_name : $this->tb_name ;if ($this->tb_sql[$tb_name]){if ($this->tb_sql[$tb_name][$key]){return $this->tb_sql[$tb_name][$key];}return $this->tb_sql[$tb_name];}return null ;}private function parse_column_obj($column_obj){if (empty($column_obj) || !is_array($column_obj))return null ;$column_sql = "`{$column_obj['name']}` " ;$column_sql .= (isset($column_obj['type'])&&$column_obj['type'])?$column_obj['type']:'';$column_sql .= (isset($column_obj['limit'])&&$column_obj['limit'])?"({$column_obj['limit']}) ":'';if (in_array($column_obj['name'],$this->tb_primary_keys) ){$column_obj['priv'] = true ;$column_obj['null'] = false ;}switch (trim(strtolower($column_obj['type']))){case 'int':$column_sql .= ( isset($column_obj['priv'])&&$column_obj['priv'] ? ' auto_increment ':'') ; break ; case 'varchar':case 'text':$column_sql .= ( isset($column_obj['collate'])&&$column_obj['collate'] ? " collate {$column_obj['collate']} ":'');break ;}$column_sql .= (isset($column_obj['null'])&&$column_obj['null'])?'':' NOT NULL ';$column_sql .= (isset($column_obj['default'])&&$column_obj['default'])?" default '{$column_obj['default']}' ":'';$column_sql .= (isset($column_obj['unique'])&&$column_obj['unique'])?' unique ':'';array_push($this->tb_columns,$column_sql);}private function parse_primary_keys($primary_keys=null){if (empty($primary_keys)) return ;$str_primary_keys = implode(',',$primary_keys);$this->str_primary_keys = " PRIMARY KEY ($str_primary_keys),\n";}private function parse_foreign_keys($foreign_keys=null){if ($this->params['engine'] != XMysqlConst::$Engine_InnoDB){return ;}if (empty($foreign_keys)) return ;$str_foreign_keys = '' ;$tb_fk_format1 = "KEY `%s` (`%s`),\n" ;$tb_fk_format2 = "CONSTRAINT `%s` FOREIGN KEY (`%s`) REFERENCES `%s` (`%s`) %s,\n" ;//KEY `fk_tb_ac_articles` (`article_id`),//CONSTRAINT `fk_tb_articlemetas` FOREIGN KEY (`articlemeta_id`) REFERENCES `articlemetas` (`id`) ON DELETE CASCADEforeach ($foreign_keys as $k=>$v){//row[$field] = [$refer_tb,$refer_field,$fk_id,$on_action]$str_foreign_keys .= sprintf($tb_fk_format1,$v[2],$k);$str_foreign_keys .= sprintf($tb_fk_format2,$v[2],$k,$v[0],$v[1],$v[3]);}$this->str_foreign_keys = $str_foreign_keys;}public function create_table(){if (empty($this->tb_name)) return null ;$tb_sql_format = "CREATE TABLE `{$this->tb_name}` ( \n %s \n) %s ;" ; $body_sql = '' ;foreach ($this->tb_columns as $column_sql){$body_sql .= "{$column_sql},\n";}if ($this->str_primary_keys)$body_sql .= $this->str_primary_keys ;if ($this->str_foreign_keys)$body_sql .= $this->str_foreign_keys ;$body_sql = preg_replace('/,$/','',$body_sql);$body_sql = trim($body_sql);$extra_params = $this->parse_extra_params() ;$this->tb_sql[$this->tb_name]['create'] = sprintf($tb_sql_format,$body_sql,$extra_params);}private function parse_extra_params(){$extra_params = '' ;if (!empty($this->params)){foreach ($this->params as $k=>$v){$extra_params .= empty($v)?'':"{$k}={$v} " ;}}return $extra_params ;}public function drop_table(){if (empty($this->tb_name)) return null ;$this->tb_sql[$this->tb_name]['drop'] = "DROP TABLE IF EXISTS `{$this->tb_name}` ;" ; }protected function say_with_time($note,$exec_sql=null){}}if (!function_exists('dump')){function dump($vars, $label = '', $return = false){    if (ini_get('html_errors')) {        $content = "<pre>\n";        if ($label != '') {            $content .= "<strong>{$label} :</strong>\n";        }        $content .= htmlspecialchars(print_r($vars, true));        $content .= "\n</pre>\n";    } else {        $content = $label . " :\n" . print_r($vars, true);    }    if ($return) { return $content; }    echo $content;    return null;}}?>

 

测试代码如下:

<?phprequire_once('dbmigrate.php');DBMigrate::instance();// authors table$tb_authors = new XDBTable('authors');$tb_authors->add_column('id','int',11,false);$tb_authors->add_column('name','varchar',40,false,null,true);$tb_authors->column(array('name' => 'description' ,'type' => 'text' ,'collate' => 'utf8_unicode_ci' ,    ));$tb_authors->primary_keys(array('id'));$tb_authors->extra_params_mysql(XMysqlConst::$Engine_InnoDB);$dbmigrate = DBMigrate::instance($tb_authors);$dbmigrate->create_table();$tb_authors_sql_create = ($dbmigrate->to_sql('create'));$dbmigrate->drop_table();$tb_authors_sql_drop = ($dbmigrate->to_sql('drop'));dump($tb_authors_sql_create,$tb_authors_sql_drop);// books table$tb_books = new XDBTable('books');$tb_books->add_column('id','int',11,false);$tb_books->add_column('author_id','int',11,false);$tb_books->add_column('name','varchar',80,false,null,true);$tb_books->column(array('name' => 'description' ,'type' => 'text' ,'collate' => 'utf8_unicode_ci' ,    ));$tb_books->timestamps();$tb_books->primary_keys(array('id'));$tb_books->extra_params_mysql(XMysqlConst::$Engine_InnoDB);$tb_books->foreign_key('author_id','authors','id',null,XMysqlConst::$FK_ON_DELETE_CASCADE);$dbmigrate = DBMigrate::instance($tb_books);$dbmigrate->create_table();$tb_books_sql_create = ($dbmigrate->to_sql('create'));$dbmigrate->drop_table();$tb_books_sql_drop = ($dbmigrate->to_sql('drop'));dump($tb_books_sql_create,$tb_books_sql_drop);?>

 

运行结果如下:

DROP TABLE IF EXISTS `authors` ; :CREATE TABLE `authors` (  `id` int(11)  auto_increment  NOT NULL ,`name` varchar(40)  NOT NULL  unique ,`description` text collate utf8_unicode_ci  NOT NULL , PRIMARY KEY (id) ) engine=InnoDB default charset=utf8 collate=utf8_unicode_ci  ;表 [books] 外键:  :Array(    [author_id] => Array        (            [0] => authors            [1] => id            [2] => fk_tb__books$author_id__authors$id            [3] => ON DELETE CASCADE        ))DROP TABLE IF EXISTS `books` ; :CREATE TABLE `books` (  `id` int(11)  auto_increment  NOT NULL ,`author_id` int(11)  NOT NULL ,`name` varchar(80)  NOT NULL  unique ,`description` text collate utf8_unicode_ci  NOT NULL ,`created_at` datetime,`updated_at` datetime, PRIMARY KEY (id),KEY `fk_tb__books$author_id__authors$id` (`author_id`),CONSTRAINT `fk_tb__books$author_id__authors$id` FOREIGN KEY (`author_id`) REFERENCES `authors` (`id`) ON DELETE CASCADE ) engine=InnoDB default charset=utf8 collate=utf8_unicode_ci  ;