php框架中的数据操作接口

数据库结构

数据库分为单库和分库两种，在配制文件中这两种库都会对应至一个cluster_id。

每个分库的表名、结构完全一致，分库规则是按一个给定的值（通常是对象id）进行指定的哈希算法运算得到。

分库及单库的操作上基本一致。只是创建Model库时分库需要传入一个额外的值去哈希计算所在的数据库。

数据表Model

所有的 Model 都继承至基类 Db_Model, Db_Model提供了所有数据表的操作。

一般你有下面两种方式去操作一个数据表：

//假定有一个数据库，在配制文件中指定的cluster_id是ad_info，数据库中有一个slot表。 
//方法1 
class Model_Slot extends Model_Db 
{ 
public function __construct() 
{ 
parent::__construct('slot', 'ad_info'); 
} 
} 

$slotModel = new Model_Slot(); 
$slot = $slotModel->getById($slotId); 
$slot = $slotModel->get_by_id($slotId); 
$slot = $slotModel->selectOne(array('id' => $slotId)); 

//方法2 
$slotModel = new Db_Model('slot', 'ad_info'); 
$slot = $slotModel->getById($slotId); 
$slot = $slotModel->get_by_id($slotId); 
$slot = $slotModel->selectOne(array('id' => $slotId));

查询条件

Db_Model支持复杂查询，查询条件的构造主要通过两个数组参数。

(构造SQL的类为：Db_Sql)

$where : 条件

$attr : 属性

$where 语法：

WHERE :

array(

FIELD => VALUE | VALUE_LIST | FIELD_COND,

[FIELD => VALUE | VALUE_LIST | FIELD_COND,]

...

[LOGIC => 'AND' | 'OR'] //Default LOGIC 'AND'

)

|

array(

WHERE,

[WHERE,]

...

[LOGIC => 'AND' | 'OR'] //Default LOGIC 'OR'

)

LOGIC : Db_Sql::LOGIC

FIELD : STRING

RAW_VALUE1 : Db_Sql::rawValue(STRING) //Value not need quoted

RAW_VALUE2 : Db_Sql::rawValue(STRING, FALSE) //Value not need quoted and excaped

SIMPLE_VALUE : STRING | NUMBER | RAW_VALUE1 | RAW_VALUE2

VALUE : SIMPLE_VALUE | NULL

VALUE_LIST : array(SIMPLE_VALUE, [SIMPLE_VALUE,] ...)

OPERATION : STRING //'=', 'like', '!=', '>=', 'in' ...

COND : OPERATION => VALUE

FIELD_COND : array(

COND, //field operation value [AND | OR] field operatoin value ...

[COND,]

...

[LOGIC => 'AND' | 'OR'] //Default logic OR

) | array(

VALUE_LIST | array(COND),

[VALUE_LIST | array(COND),]

...

[LOGIC => 'AND' | 'OR'] //Default logic OR

)

$attrs 语法：

ATTRS : array(

order_by => STRING, // Like 'create_time DESC, id ASC'

group_by => STRING,

select => STRING, //Deault '*'

limit => NUMBER,

offset => NUMBER,

)

示例：

$where = array( 
'a' => '1', 
'b' => array('!=' => 3), 
); 
//Output : a = '1' AND b != '3' 

$where = array( 
'a' => array(1, 2, 3), 
'b' => array('<=' => Db_Sql::rawValue('CURRENT_TIMESTAMP')),

'c' =>  array('not in' => Db_Sql::rawValue('('.implode(',',$item_ids).')')); 

'd' =>  array('not in' => Db_Sql::rawValue('("a","b")',false)); 
); 
//Output (a = '1' OR a = '2' OR a = '3') AND b <= CURRENT_TIMESTAMP 

$where = array( 
'create_time' => array( 
'>=' => '2011-06-01', 
'<=' => '2011-06-07', 
Db_Sql::LOGIC => 'AND' 
) 
); 
//Output create_time >= '2011-06-01' AND create_time <= '2011-06-07'

                 selct * from tb where (a=1 or b=2 ) and c=3

                 $this->select(

array(Db_Sql::RAW_STR_NO_ESCAPE_PREFIX => array(
'a' => 1
,'b =' =>2
, Db_Sql::LOGIC => 'OR' 
)
,'c' => 3
)

)

                 

 $conds = array(

           array('create_at' => array('>=' => $time, '<' => $time+24*60*60, Db_Sql::LOGIC => 'AND')), array('create_at' => array('>=' => $time+24*60*60, '<' => $time+4*24*60*60, Db_Sql::LOGIC => 'AND'), 'client_time' =>array('>=' => $time, '<' => $time+24*60*60, Db_Sql::LOGIC => 'AND'))

, Db_Sql::LOGIC => 'OR');

$m = new Model_Slot(); 

$where = array( 
'id' => array('1', '2', '3'), 
'status' => array('!=' => 0), 
'name' => array('like' => '%kw%'), 
); 
$attrs = array( 
'select' => 'name', 
limit => 10, 
order_by => 'create_time DESC', 
); 

$total = $m->selectCount($where); 
$slots = $m->select($where, $attrs);

 $where[Db_Sql::rawValue('FIND_IN_SET')]=array("('1'" => Db_Sql::rawValue(",tags)")); 

---

$where[] = array(
array(
array(
'status' => self::STATUS_UNUSE
,'end_time' => array('>' => $curr_time)
)
,array(
'status' => self::STATUS_UNSEND
,'end_time' => array('>' => $curr_time)
,'begin_time' => array('<' => $curr_time)
) 
,Db_Sql::LOGIC => 'or'
)
,array('uid' => $uid )
,Db_Sql::LOGIC => 'AND'
);

---

(((`status`='1' AND `end_time` > '1478777316') or (`status`='2' AND `end_time` > '1478777316' AND `begin_time` < '1478777316')) AND (`uid`='110'))

利用游标遍历海量数据 Db_Base

获取SQL查询结果 select($sql, &$res, $fetchStyle = PDO::FETCH_NAMED, $fetchMode = self::FETCH_ONE)

获取查询结果下一行 

 fetchNext(&$res, $fetchStyle = PDO::FETCH_NAMED)

EXP:

$isCloseCursor = false; // 是否关键游标
$record = $myObj->select ($where , $attrs , PDO::FETCH_NAMED, Db_Base::FETCH_ONE,$isCloseCursor);
do{
    if(empty($record)){
        continue;
   }
//TODO

}while(true == $myObj->fetchNext($record));

数据插入/替换/更新/删除

Db_Model提供了对应的方法

insert($data)

insertReplace($data, $replaceData),

update($where, $data)

delete($where)

$data的格式

DATA : array(

FIELD => VALUE,

[FIELD => VALUE,]

...

)

VALUE : NULL | STRING | RAW_VALUE

RAW_VALUE : Db_Sql::rawValue(STRING) //Same of '&/STRING'

sql:  

update .... set  total_income=total_income+1

php:

$attr = array('total_income' => Db_Sql::RAW_STR_PREFIX.'total_income + ' . (int)$coin);
return $this->update($where, $attr);

事件

Db_Model提供了许多事件监控函数，只要在子类中重载这些方法，就能很方便的监控到Model数据的所有操作。

下面是事件函数列表

protected function beforeInsert() {} 
protected function afterInsert() {} 
protected function beforeUpdate() {} 
protected function afterUpdate() {} 
protected function beforeInsertReplace() {} 
protected function afterInsertReplace() {} 
protected function beforeDelete() {} 
protected function afterDelete() {}

通过这些事件可以很方便的实现如清缓存等的功能

可缓存对象Model

Model_Cacheable继承自Db_Model，支持单个对象的缓存，适用场景，如果对象被频率的使用且是以主键方式查询。

相关配制的修改可以通过重载实例变量的方法或是在构造器中设置，不过通过不用做额外设置。

例：

Model_User extends Model_Cacheable
{
protected $CACHE_TIME = 14400; //Default 7200
protected $COLUMN_ID = 'myid'; //Default 'id'
protected $MEMCACHE_CLUSTER_ID = 'my_cache_cluster'; //Default 'default'

public function __construct()
{
parent::__construct('user', 'dfp_main');
}

public static function get($uids, $useCache = TRUE)
{
return self::_get(__CLASS__, $uids, $useCache);
}
}

Model_User::get($uid);

//获取一批对象，返回对象的关系数组，以对象主键为key
Model_User::get($uids);

拆库配置

    'db_cluster' => array(        'd_comm_user' => array(            'farm_policy' => 'partition_by_char', # 该回调函数 conf\config.php  里有定义            'db_name_prefix' => 'd_comm_user_',            'farm_id_converter' => 'partition_by_char',            'map' => array(                '01' => 1,# 库名后辍 => 对应db机器                '04' => 2,            ),        ),    ),

Model 使用 

   public function __construct($uid, $projectId,$hash='') {$projectId = sprintf("%02x",$projectId);//十六进制不足2位补零$hash = (ENV =='LOCAL' || ENV =='DEVELOPMENT' ? '00' : (empty($hash) ? substr(md5($uid),1,2) : $hash));parent::__construct('t_user_account_' . $hash, 'd_comm_user',$projectId);}