浏览 3062 次
|
精华帖 (0) :: 良好帖 (0) :: 新手帖 (0) :: 隐藏帖 (0)
|
|
|---|---|
| 作者 | 正文 |
|
发表时间：2010-01-08
最后修改：2010-01-08
简化 这个步骤: 在程序运行时动态增加表 以及建立表的关系 结构 <?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 ;
声明：ITeye文章版权属于作者，受法律保护。没有作者书面许可不得转载。
推荐链接
|
| 返回顶楼 | |
|
发表时间：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 ; |
| 返回顶楼 | |
|
发表时间：2010-01-08
已经添加 外键功能 呵呵...
|
| 返回顶楼 | |
|
发表时间：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();
|
| 返回顶楼 | |
|
发表时间：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'] = 'sese.timeinchina@gmail.com' ;
$author->save();
|
| 返回顶楼 | |
|
发表时间：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_'
)
));
}
}
|
| 返回顶楼 | |
|
发表时间：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));
}
}
|
| 返回顶楼 | |
|
发表时间：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);}
}
?>
|
| 返回顶楼 | |
