[置顶] 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 CASCADE
		
		foreach ($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;
	}
}
?>

 

测试代码如下:

<?php
require_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  ;