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  ;

 

评论

7 楼 vb2005xu 2010-01-14  

<?php

/**
 * Validation 拦截器, 用于验证
 * 
 * 规则数组的格式如下:
 * $rules = array(
 * 		'key_name' => array( // 每条规则包含若干项
 * 			array('func_name',[func_params,...],'error_tip')
 * 			.... n
 * 		)
 * )
 * 
 * 例如:
 * 'validation'=>array(
 *		'name' => array(
 *			array('not_empty', '用户名不能为空'),
 *	    	array('min_length', 5, '用户名不能少于 5 个字符'),
 *			array('max_length', 15, '用户名不能超过 15 个字符'),
 *			array('is_alnum', '用户名只能由字母和数字组成'),
 *		)
 *	)
 * 
 */
class Validation_XInterceptor implements IXInterceptor {
		
	public function interceptor(&$data,$rules){
		$info_array = array() ;
		Qse::dump($rules,get_class($this));
		Qse::dump(get_class($this));
		foreach ($rules as $key=>$rule) {
			Qse::array_remove_empty($rule);
			if (empty($rule)) continue ;
			
			$value = $data[$key];
			foreach ($rule as $item){
				$func = array_shift($item);
				if (method_exists($this,$func)){
					$error_tip = array_pop($item);
					//被T掉了头/尾
					$func_params = $item ;					
					if (!$this->$func($value,$func_params)){
						$info_array[$key] = $error_tip ;
						break ;
					}						
				}else 
					Qse::dump($func,'不存在');
			}
		}
		
//		Qse::dump($data);
		
		Qse::array_remove_empty($info_array);
		return $info_array ;
	}
	
	function is_null($value){echo "is_null{$value}" ; return empty($value);}
}
?>

6 楼 vb2005xu 2010-01-14  

重写了下 代码,解决了相同拦截器的行为不能合并的问题

protected function on($evt_types,$interceptor_list){
		$evt_types = trim($evt_types);
		if (empty($evt_types)) return ;		
		$evt_types = explode(',',$evt_types);
		foreach ($evt_types as $evt_type){
			
			if (!is_array($this->interceptor[$evt_type])){
				$this->interceptor[$evt_type] = array();				
			}
			
			if (is_array($interceptor_list)){
				$this->interceptor[$evt_type] = array_merge_recursive($this->interceptor[$evt_type],$interceptor_list);
			}	
		}
		
	}
	
	protected function trigger($evt_types,&$data,$func_callback=null){
		
		$evt_types = trim($evt_types);
		if (empty($evt_types)) return ;
		$evt_types = explode(',',$evt_types);
		
		foreach ($evt_types as $evt_type){
			if (!is_array($this->interceptor[$evt_type]))
				continue ;
//			Qse::dump($this->interceptor[$evt_type],get_class($this));	
			foreach($this->interceptor[$evt_type] as $i_key=>$rules){
				if (!is_array($rules))
					continue ;
				$interceptor = Qse::singleton("{$i_key}_XInterceptor");	
					if ($interceptor instanceof IXInterceptor){
						//TODO: 调用 拦截器方法,在这里应该捕获跑出来的异常或者信息
						
						$interceptor->interceptor($data,$rules);
						
					}	
					else {
						//清除错误的拦截器设置
						unset($interceptor);
						unset($this->interceptor[$evt_type][$i_key]);
					}
			}
			
//			Qse::dump($this->interceptor[$evt_type],get_class($this));
		}
	}

5 楼 vb2005xu 2010-01-14  

新增 拦截器功能,方便做数据处理以及扩展

//事件绑定机制,绑定 数据操作 - 处理逻辑 , 比如 绑定值验证
	
	/**
	 * 为特定事件绑定 处理模块 
	 * 可为多个事件绑定相同的拦截器模块组 
	 * 
	 * $interceptor_list = array('IXInterceptor'=>$arr_rules)
	 * 
	 * @param string $evt_types 以,号分隔事件名称
	 * @param array $interceptor_list  拦截器模块组
	 */
	protected function on($evt_types,$interceptor_list){
		$evt_types = trim($evt_types);
		if (empty($evt_types)) return ;		
		$evt_types = explode(',',$evt_types);
		foreach ($evt_types as $evt_type){
			if (!is_array($this->interceptor[$evt_type])){
				$this->interceptor[$evt_type] = array();				
			}
			if (is_array($interceptor_list))
				array_push($this->interceptor[$evt_type],$interceptor_list) ;
		}
		
	}
	
	protected function trigger($evt_types,&$data,$func_callback=null){
		
		$evt_types = trim($evt_types);
		if (empty($evt_types)) return ;
		$evt_types = explode(',',$evt_types);
		
		foreach ($evt_types as $evt_type){
			if (!is_array($this->interceptor[$evt_type]))
				continue ;
			foreach($this->interceptor[$evt_type] as $index=>$interceptor_list){
				if (!is_array($interceptor_list))
					continue ;
				foreach($interceptor_list as $i_name => $rules){
					$interceptor = Qse::singleton("{$i_name}_XInterceptor");					
					if ($interceptor instanceof IXInterceptor){
						//TODO: 调用 拦截器方法,在这里应该捕获跑出来的异常或者信息
						
						$interceptor->interceptor($data,$rules);
						
					}	
					else {
						unset($interceptor);
						unset($this->interceptor[$evt_type][$index][$i_name]);
					}
				}
				dump($this->interceptor[$evt_type]);
			}
		}
	}

class Author_XRecord extends XRecord {
	 
	protected function __define(){
		// 定义记录元数据
		$this->xtable = Qse::singleton('Authors_XTable') ;		
		$this->on('create,update',array(			
			'validation'=>array(
				'name' => array(
					array('not_empty', '用户名不能为空'),
			        array('min_length', 5, '用户名不能少于 5 个字符'),
			        array('max_length', 15, '用户名不能超过 15 个字符'),
			        array('is_alnum', '用户名只能由字母和数字组成'),
				)
			) ,
			'prefix' => array(
				'name' => 'iam_'
			)
		));
	}
}

4 楼 vb2005xu 2010-01-13  

重新封装并重写了 主解析模式 ,降低了 代码的易用性

/**
 * 定义 作者表[authors]
 */
class Author_XTable extends XTable {
	
	public function __define(){		
		$this->table_name('authors');
		
		$this->add_column('id','int',11,false);
		$this->add_column('name','varchar',40,false,null,true);
		$this->column(array(
			'name' => 'description' ,
			'type' => 'text' ,
			'collate' => 'utf8_unicode_ci' ,    		
		));
		$this->primary_keys(array('id'));
		$this->extra_params_mysql(XMysqlConst::Engine_InnoDB);
		
	}
}

class Author_XRecord extends XRecord {
	 
	protected function __define(){
		// 定义记录元数据
		$this->xtable = new Author_XTable() ;
		Qse::dump($this->xtable,$this->xtable['table_name']);
	}
}

$author = XRecord::instance('Author_XRecord')->__new();
$author['name'] = 'qse' ;
$author['description'] = '[e-mail]' ;
$author->save();

3 楼 vb2005xu 2010-01-11  

下面会增加一点新功能,实现简易的记录模型:

if (!class_exists('XArrayAccessAdapter')){
	class XArrayAccessAdapter implements ArrayAccess{
		protected $record = array() ;
		public function offsetExists($offset) {
	        return isset($this->record[$offset]);
	    }
	    public function offsetUnset($offset) {
	        unset($this->record[$offset]);
	    }
		public function offsetSet($offset, $value) {
	        $this->record[$offset] = $value;
	    }
	    public function offsetGet($offset) {
	        return isset($this->record[$offset]) ? $this->record[$offset] : null;
	    }
	}
}

/**
 * XRecord 封装对记录模型的操作,是给开发者使用的模型基类
 * 如: 验证操作,事件绑定等等
 */
abstract class XRecord extends XArrayAccessAdapter{
	public function __construct(){}
	
	
	//事件绑定机制,绑定 数据操作 - 处理逻辑 , 比如 绑定值验证
	protected function on($evt_types,$evt_handel){
		
	}	
	protected function trigger($evt_types,$func_callback){
		
	}
}

class XDBRecord {
    
	/**
	 * XRecord 对象
	 *
	 * @var XRecord
	 */
	private $xrecord = null ;
	
	protected function __construct($xrecord){
		$this->xrecord = $xrecord ;
	} 
	
	/**
	 * 通过传入的XRecord 记录模型对象,构建相应的XDBRecord对象
	 *
	 * @param XRecord $xrecord
	 * @return XDBRecord
	 */
	public function __new($xrecord){
		return new XDBRecord($xrecord);
	}
	
	public function insert(){
		dump($this->xrecord,get_class($this->xrecord));
	}
	public function update(){}
	public function delete(){}
	public function select(){}
}

使用代码如下:

class Author_XRecord extends XRecord {
	 
}

$author = XDBRecord::__new(new Author_XRecord());
$author->insert();

2 楼 vb2005xu 2010-01-08  

已经添加 外键功能 呵呵...

1 楼 vb2005xu 2010-01-08  

引用

Array
(
)


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  ;

Array
(
    [0] => Array
        (
            [0] => author_id
            [1] => authors
            [2] => id
            [3] => fk_tb_book_author
        )

)


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)
) engine=InnoDB default charset=utf8 collate=utf8_unicode_ci  ;