数据库结构
数据库分为单库和分库两种,在配制文件中这两种库都会对应至一个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); } |