protected function _query($sql)
{
if ($sql == null) {
if (empty($this->_lastCmd)) {
throw new \Sooh\Base\ErrException('empty sql given');
}
$this->_lastCmd->dowhat = strtolower($this->_lastCmd->dowhat);
switch ($this->_lastCmd->dowhat) {
case 'insert':
$sql = 'insert into ' . $this->_lastCmd->tablenamefull . ' set ' . $this->_fmtField($this->_lastCmd->field);
break;
case 'addlog':
$sql = 'insert delayed into ' . $this->_lastCmd->tablenamefull . ' set ' . $this->_fmtField($this->_lastCmd->field);
break;
case 'insert':
$sql = 'insert into ' . $this->_lastCmd->tablenamefull . ' set ' . $this->_fmtField($this->_lastCmd->field);
break;
case 'update':
$sql = 'update ' . $this->_lastCmd->tablenamefull . ' set ' . $this->_fmtField($this->_lastCmd->field);
break;
case 'delete':
$sql = 'delete from ' . $this->_lastCmd->tablenamefull;
break;
case 'select':
$sql = 'select ' . $this->_fmtField($this->_lastCmd->field) . ' from ' . $this->_lastCmd->tablenamefull;
break;
default:
throw new \Sooh\Base\ErrException("unsupport sql cmd:" . $this->_lastCmd->dowhat);
}
if (!empty($this->_lastCmd->where)) {
$sql .= ' ' . $this->_lastCmd->where;
}
if (!empty($this->_lastCmd->orderby)) {
$arr = explode(' ', trim($this->_lastCmd->orderby));
$mx = count($arr);
$orderby = array();
$groupby = array();
for ($i = 0; $i < $mx; $i += 2) {
$k = $arr[$i];
$v = $arr[$i + 1];
switch ($k) {
case 'rsort':
$orderby[] = $v . ' desc';
break;
case 'sort':
$orderby[] = $v;
break;
case 'groupby':
case 'group':
$groupby[] = $v;
break;
default:
$err = new \Sooh\Base\ErrException('unsupport:' . $orderby);
throw $err;
}
}
if (!empty($groupby)) {
$sort_group .= ' group by ' . implode(',', $groupby);
}
if (!empty($orderby)) {
$sort_group .= ' order by ' . implode(',', $orderby);
}
$sql .= ' ' . $sort_group;
}
if ($this->_lastCmd->dowhat == 'select' && is_array($this->_lastCmd->fromAndSize) && $this->_lastCmd->fromAndSize[1] > 0) {
$sql .= ' limit ' . $this->_lastCmd->fromAndSize[0] . ',' . $this->_lastCmd->fromAndSize[1];
}
$this->_lastCmd->resetForNext();
$this->_lastCmd->strTrace = '[' . $this->_lastCmd->server . ']' . $sql;
} else {
if (is_string($sql)) {
$this->_lastCmd->resetForNext();
$this->_lastCmd->strTrace = '[' . $this->_lastCmd->server . ']' . $sql;
} else {
$err = new \Sooh\Base\ErrException('sql gived is not a string');
error_log($err->getMessage() . "\n" . $err->getTraceAsString());
throw $err;
}
}
sooh_broker::pushCmd($this->_lastCmd);
$skip = sooh_dbErr::$maskSkipTheseError;
sooh_dbErr::$maskSkipTheseError = array();
$rs = mysqli_query($this->_connection, $sql);
$this->_chkErr($skip);
return $rs;
}
protected function _query($sql)
{
if ($sql == null) {
if (empty($this->_lastCmd)) {
throw new \ErrorException('empty sql given');
}
$orderby = array();
$groupby = array();
if (!empty($this->_lastCmd->orderby)) {
$arr = explode(' ', trim($this->_lastCmd->orderby));
$mx = count($arr);
for ($i = 0; $i < $mx; $i += 2) {
$k = $arr[$i];
$v = $arr[$i + 1];
switch ($k) {
case 'rsort':
$orderby[] = $v . ' desc';
break;
case 'sort':
$orderby[] = $v;
break;
case 'groupby':
case 'group':
$groupby[] = $v;
break;
default:
$err = new \ErrorException('unsupport:' . $orderby);
sooh_trace::exception($err);
throw $err;
}
}
}
$this->_lastCmd->dowhat = strtolower($this->_lastCmd->dowhat);
switch ($this->_lastCmd->dowhat) {
case 'select':
$sql = 'select ';
if (is_array($this->_lastCmd->fromAndSize) && $this->_lastCmd->fromAndSize[1] > 0) {
$sql .= ' top ' . $this->_lastCmd->fromAndSize[1] . ' ';
if ($this->_lastCmd->fromAndSize[0] !== 0) {
if (is_array($this->_lastCmd->pkey) && sizeof($this->_lastCmd->pkey) > 1) {
throw new \ErrorException("multi-pkey not support in mssql for limit");
}
if (is_array($this->_lastCmd->pkey)) {
$pkey = key($this->_lastCmd->pkey);
if (is_int($pkey)) {
$pkey = current($this->_lastCmd->pkey);
}
} else {
if (empty($this->_lastCmd->pkey)) {
$pkey = 'Id';
} else {
if (is_string($this->_lastCmd->pkey)) {
$pkey = $this->_lastCmd->pkey;
} else {
throw new \ErrorException("invalid pkey in mssql found for limit");
}
}
}
$limit = "{$pkey} NOT IN (SELECT TOP {$this->_lastCmd->fromAndSize[0]} {$pkey} FROM {$this->_lastCmd->tablenamefull} __WHERE__";
if (!empty($orderby)) {
$limit .= ' order by ' . implode(',', $orderby);
}
$limit .= ")";
//throw new \ErrorException('todo: 获取并缓存主键');//SELECT TOP 10 * FROM sql WHERE ( code NOT IN (SELECT TOP 20 code FROM TestTable ORDER BY id))
}
}
$sql .= $this->_fmtField($this->_lastCmd->field) . ' from ' . $this->_lastCmd->tablenamefull;
break;
case 'addlog':
case 'insert':
$sql = 'insert into ' . $this->_lastCmd->tablenamefull;
$sql .= " (" . implode(',', array_keys($this->_lastCmd->field)) . ") ";
$sql .= "values (";
foreach ($this->_lastCmd->field as $k => $v) {
$sql .= $this->_safe($k, $v) . ',';
}
$sql = substr($sql, 0, -1) . ")";
break;
case 'update':
//update FE_temp.dbo.tb_user set tb_user.timeLastBought = tb_bought.lastBought
//// from FE_temp.dbo.tb_user left join FE_temp.dbo.tb_bought on tb_bought.userIdentifier=tb_user.userIdentifier
$sql = 'update ' . $this->_lastCmd->tablenamefull . ' set ' . $this->_fmtField($this->_lastCmd->field);
break;
case 'delete':
$sql = 'delete from ' . $this->_lastCmd->tablenamefull;
break;
default:
throw new \ErrorException('unsupport sql cmd:' . $this->_lastCmd->dowhat);
}
if (!empty($limit)) {
if (!empty($this->_lastCmd->where)) {
$where = substr(trim($this->_lastCmd->where), 5);
$limit = str_replace('__WHERE__', ' where ' . $where, $limit);
$sql .= ' where (' . $where . ') and (' . $limit . ')';
} else {
$sql .= ' where ' . ($limit = str_replace('__WHERE__', '', $limit));
}
} elseif (!empty($this->_lastCmd->where)) {
$sql .= ' ' . $this->_lastCmd->where;
}
if (!empty($this->_lastCmd->orderby)) {
if (!empty($groupby)) {
$sort_group .= ' group by ' . implode(',', $groupby);
}
if (!empty($orderby)) {
$sort_group .= ' order by ' . implode(',', $orderby);
}
$sql .= ' ' . $sort_group;
}
$this->_lastCmd->resetForNext();
$this->_lastCmd->strTrace = '[' . $this->_lastCmd->server . ']' . $sql;
} else {
if (is_string($sql)) {
$this->_lastCmd->resetForNext();
$this->_lastCmd->strTrace = '[' . $this->_lastCmd->server . ']' . $sql;
} else {
$err = new \ErrorException('sql gived is not a string');
error_log($err->getMessage() . "\n" . $err->getTraceAsString());
throw $err;
}
}
sooh_broker::pushCmd($this->_lastCmd);
$skip = sooh_dbErr::$maskSkipTheseError;
sooh_dbErr::$maskSkipTheseError = array();
//throw new \ErrorException($sql);
$rs = sqlsrv_query($this->_connection, $sql);
$this->_chkErr($skip);
return $rs;
}