/**
* 测试
*/
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();
}
/**
* 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();
}
public function flush()
{
if (!$this->buffer) {
return FALSE;
}
$this->lastFlush = time();
$rows = Query::insert($this->table)->batchSaveRows($this->buffer)->exec();
$this->buffer = array();
return $rows;
}
/**
* 测试多行行对象插入
*/
private function testRowInstanceInsert()
{
//insert into user_salt (`Uid`, `Salt`) values ('31', 'fdadsf'), ('32', 'aaadsf'), ('35', 'ddddd') ;
$batch = array();
$row = \Model\Table\UserSalt::create()->createRow();
$row->Uid = 31;
$row->Salt = "fdadsf";
$batch[] = $row;
$row = \Model\Table\UserSalt::create()->createRow();
$row->Uid = 32;
$row->Salt = "aaadsf";
$batch[] = $row;
$row = \Model\Table\UserSalt::create()->createRow();
$row->Uid = 35;
$row->Salt = "ddddd";
$batch[] = $row;
$insert = \HuiLib\Db\Query::Insert()->batchSaveRows($batch);
echo $insert->toString();
//$insert->query();
}
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');
}
/**
* 编译成SQL语句
*/
protected function compile()
{
$parts = array();
$parts['start'] = 'select';
$parts[self::COLUMNS] = $this->renderColumns();
$parts['from'] = 'from';
$parts[self::TABLE] = $this->renderTable();
$parts[self::JOINS] = $this->renderJoin();
$parts[self::INDEX] = $this->renderIndex();
$parts[self::WHERE] = $this->renderWhere();
$parts[self::UNION] = $this->renderUnion();
//一下不能在union子句中出现
$parts[self::ORDER] = $this->renderOrder();
$parts[self::GROUP] = $this->renderGroup();
$parts[self::LIMIT] = $this->renderLimit();
$parts[self::OFFSET] = $this->renderOffset();
$parts[self::FOR_UPDATE] = $this->renderForUpdate();
$parts[self::ENDS] = $this->ends;
$this->parts =& $parts;
return parent::compile();
}
/**
* 通过关联数据插入某表一行数据
*
* @param array $dataArray 插入数组
*/
public function delete(\HuiLib\Db\Query\Where $where)
{
$delete = Query::delete(static::TABLE);
if ($this->dbAdapter !== NULL) {
$delete->setAdapter($this->dbAdapter);
}
return $delete->where($where)->query();
}
/**
* 渲染Quote语句
*
* TODO 引号中的问号问题，目前尽量传递进的占位符短小
*/
private function renderQuote()
{
return str_ireplace('?', self::$query->escape($this->placeBind), $this->placeHolder);
}
$insert->fields(array('field1', 'field2'))->values(array('fvalue1', 'fvalue2'));
//insert into test (field1, field2) values ('fvalue1', 'fvalue2'), ('fvalue11', 'fvalue22') ;
$insert->values(array('fvalue11', 'fvalue22'));
//附加 前面的
//2.2.2 关联数组插入模式
//insert into test (field1, field2) values ('fvalue1', 'fvalue2'), ('fvalue11', 'fvalue22') ;
$insert->kvInsert(array('field1' => 'fvalue1', 'field2' => 'fvalue2'))->values(array('fvalue11', 'fvalue22'));
//2.2.3 Duplicate Key Update模式
//需要注意dupFields和dupValues的关联性，弱耦合
$insert->enableDuplicate(true);
//开启自动Dup更新模式
//insert into test set field1='fvalue1', field2='fvalue2' on duplicate key update field1='newfvalue1', num=num+1 ;
$insert->fields(array('field1', 'field2'))->dupFields(array('field1', 'num'))->values(array('fvalue1', 'fvalue2'), array('field2' => 'newfvalue1', array('plain' => 'num=num+1')));
//2.2.4 通过row对象数组或rowset批量插入
$insert->batchSaveRows();
//2.3 更新部分
//获取更新对象
//update tableTest set field1='fvalue1', num=num+1 where (id='16') ;
$update = \HuiLib\Db\Query::update('tableTest');
//支持两种更新Set模式，KV模式和Plain模式
$update->sets(array('field1' => 'fvalue1', 'num' => array('plain' => 'num=num+1')));
//绑定条件
$update->where(Where::createPair('id', '16'));
//2.4 删除部分
//获取删除对象
//delete from tableTest where (id='2') limit 10 ;
$delete = \HuiLib\Db\Query::delete('tableTest');
//设置删除条件
$delete->where(Where::createPair('id', '2'));
//设置删除行数
$delete->limit(10);
/**
* 直接发起默认数据库请求
*
* @return int 删除操作影响行数
*/
public function query()
{
$stmt = parent::query();
return $stmt->rowCount();
}
public function table($table)
{
parent::table($table);
return $this;
}
/**
* 编译成SQL语句
*/
protected function compile()
{
$parts = array();
$parts['start'] = 'update';
$parts[self::TABLE] = $this->renderTable();
$parts['setSep'] = 'set';
$parts[self::SETS] = $this->renderSets();
$parts[self::WHERE] = $this->renderWhere();
$parts[self::LIMIT] = $this->renderLimit();
$parts[self::ENDS] = $this->ends;
$this->parts =& $parts;
return parent::compile();
}
/**
* 删除一个值
*
* @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;
}
