/**
* 测试
*/
private function test()
{
//delete from tableTest where (id='2') limit 10 ;
$delete = \HuiLib\Db\Query::delete()->table('test')->where(Where::createPair('id', '2'))->limit(10);
//echo $delete->query();
echo $delete->toString();
}
/**
* 生成查询条件
*
* @return string
*/
protected function renderWhere()
{
if ($this->where === NULL) {
return '';
}
return 'where ' . $this->where->toString();
}
/**
* Select 普通测试
*/
private function testAdapterSelect()
{
$select = \HuiLib\Db\Query::select()->table('test.test')->where(Where::createPair('id', 2))->limit(10)->offset(0)->enableForUpdate();
$re = $select->query();
\HuiLib\Helper\Debug::out($re->fetchAll());
echo $select->toString();
}
/**
* 测试
*/
private function test()
{
//update tableTest set field1='fvalue1', num=num+1 where (id='16') ;
$update = \HuiLib\Db\Query::update()->table('tableTest')->where(Where::createPair('id', '16'));
$update->sets(array('field1' => 'fvalue1', 'field2' => 'fvalue2', 'num' => array('plain' => 'num=num+1')));
//$update->query();
echo $update->toString();
}
/**
* 生成文字表达的SQL语句
*
* orObject、andObject有可能共存，or优先，最好不要共存
*/
public function toString()
{
//获取自身对象代表的语句
$method = 'render' . ucfirst($this->type);
$whereString = '(' . $this->{$method}() . ')';
if ($this->andObject) {
if ($this->andHand == self::HAND_RIGHT) {
$whereString = '(' . $whereString . ' ' . self::WHERE_AND . ' ' . $this->andObject->toString() . ')';
} else {
$whereString = '(' . $this->andObject->toString() . ' ' . self::WHERE_AND . ' ' . $whereString . ')';
}
}
if ($this->orObject) {
if ($this->orHand == self::HAND_RIGHT) {
$whereString = '(' . $whereString . ' ' . self::WHERE_OR . ' ' . $this->orObject->toString() . ')';
} else {
$whereString = '(' . $this->orObject->toString() . ' ' . self::WHERE_OR . ' ' . $whereString . ')';
}
}
return $whereString;
}
private function testWhereBenchMark()
{
Debug::mark('startSelect');
$select = Query::select('test');
Debug::mark('endSelect');
Debug::mark('startWhere');
$where1 = Where::createPair('test', 'zzzzzzzzzzzzzzzzzzzzzzz')->orCase(Where::createPlain('test is null'));
$where = Where::createQuote('num in (?)', array(3, 5, 16))->andCase($where1, Where::HAND_LEFT);
Debug::mark('endWhere');
//初始化adapter后才能escape
$select->where($where);
//echo $select->toString();
Debug::mark('startQuery');
$re = $select->query();
Debug::mark('endQuery');
\HuiLib\Helper\Debug::out($re->fetchAll());
Debug::elapsed('startSelect', 'endSelect');
Debug::elapsed('startWhere', 'endWhere');
Debug::elapsed('startQuery', 'endQuery');
Debug::elapsed('startSelect', 'endQuery');
Debug::elapsed('startSelect', 'endALL');
}
/**
* 通过单个Field获取单条记录的某个字段值
*
* @param string $field
* @param string $value
* @param string $column 要获取的字段，是字段名，不是column序号
*/
public function getColumnByField($field, $value, $column)
{
$select = Query::select(static::TABLE);
if ($this->dbAdapter !== NULL) {
$select->setAdapter($this->dbAdapter);
}
$unit = $select->where(Where::createPair($field, $value))->limit(1)->query()->fetch();
if (isset($unit[$column])) {
return $unit[$column];
} else {
return false;
}
}
/**
* 从数据库获取数据，重建列表
*/
protected function importFromDb()
{
//通过主键尝试数据表获取数据
$tableClass = static::TABLE_CLASS;
$primaryIdKey = $this->getRowPrimaryIdKey();
$primaryId = 0;
$fields = explode(',', $this->hashValueField);
array_push($fields, $this->hashKeyField, $primaryIdKey);
$fields = array_unique($fields);
//删除旧数据
$this->getAdapter()->delete($this->getRedisKey());
do {
$select = $tableClass::create()->select()->columns($fields);
$select->where(Where::createQuote($primaryIdKey . ' >?', $primaryId))->limit(self::FETCH_PER_ACTION)->order($primaryIdKey . ' asc');
//echo $select->toString()."\n";
$dataList = $select->query()->fetchAll();
if ($dataList) {
$result = array();
foreach ($dataList as $iter => $valueUnit) {
//包含无限循环的要尽量限制严格些
if (!isset($valueUnit[$this->hashKeyField])) {
throw new \Exception('Field fetch error.');
}
$valueString = $this->getValueString($valueUnit);
$keyString = $this->formatHashKey($valueUnit[$this->hashKeyField]);
if (empty($valueString) || empty($keyString)) {
continue;
}
$result[$keyString] = $valueString;
}
//包含无限循环的要尽量限制严格些
if (!isset($valueUnit[$primaryIdKey])) {
throw new \Exception('Value of primary key fetch error.');
}
$primaryId = $valueUnit[$primaryIdKey];
$this->getAdapter()->hMset($this->getRedisKey(), $result);
}
} while (!empty($dataList));
//埋Redis更新时间戳
$this->getAdapter()->hSet($this->getRedisKey(), self::REDIS_UPDATE_KEY, time());
return TRUE;
}
/**
* 删除一个值
*
* @return int
*/
public function delete()
{
$tableInstance = $this->tableInstance;
if ($tableInstance === NULL || $tableInstance::TABLE === NULL) {
throw new Exception('Table class constant TABLE has not been set.');
}
$delete = Query::delete($tableInstance::TABLE);
if ($this->dbAdapter !== NULL) {
$delete->setAdapter($this->dbAdapter);
}
$delete->where(Where::createPair(static::PRIMAY_IDKEY, $this->data[static::PRIMAY_IDKEY]));
$this->onBeforeDelete();
$result = $delete->query();
$this->onAfterDelete();
return $result;
}