/**
* Creates a new save point inside the transaction so that it could be rolled back to this
* step
* @param string $savepointthe name of the savepoint
* @return Transaction itself
*/
function save($savepointId)
{
$this->savepoints[] = $savepointId;
$query = 'SAVEPOINT ' . $this->db->quoteIdentifier($savepointId);
$this->db->sendQuery(new RawSqlQuery($query));
return $this;
}
/**
* Conducts an insert without the need to pass SQL code
*
* This function will insert a row in the the specified table ($tableName) with values specified by
* the array $arUpdates. $arUpdates is associative, where the keys are interpreted as field names.
* Field names that appear in the table but not in $arValues will not be specified in the SQL query.
* This may cause problems for fields specified as NOT_NULL or similar. If a value of '#id#' is
* specified then this will be assumed to be an auto increment primary key field, causing the new
* id to be returned.
*
* @param string $tableName The name of the table to update
* @param array $arValues Associative array of fields/values to be inserted
* @param bool $prepare Set to false if you do not wish the query to use prepared statements (not recommended)
* @return int The next ID if #id# is specified as a value, otherwise the number of affected rows.
*
*/
public function insert($tableName, $arValues, $prepare = true)
{
$id = null;
//echo "<br> called insert: " . $tableName . print_r($arValues);
self::logQuery();
$arValueList = array();
$count = 0;
foreach ($arValues as $key => &$value) {
if (strtolower($value) == '#id#') {
//we need to get the next value from this table's sequence
$value = $id = $this->conn->nextID($tableName . "_" . $key);
//Fix as values are not updating correctly
//$id = $this->conn->nextID($tableName . "_id");
//echo "<br>" . $tableName . "_id : " . $value;
if (DB::isError($id)) {
throw new LoggedException($id->getMessage(), $id->getCode(), self::module);
}
//array_splice($arValues,$count,1);
}
// else { //else added as part of fix for value insert
$bin = false;
if (strtolower(substr($value, 0, 1)) == "b" && strtolower(substr($value, strlen($value) - 1, 1)) == "b") {
if (Database::binaryCheck(substr($value, 1, strlen($value) - 2))) {
$bin = true;
} else {
$bin = false;
}
}
if ($bin) {
$arValueList[] = "B'" . substr($value, 1, strlen($value) - 2) . "'";
} else {
$arValueList[] = $this->conn->quoteSmart($value);
}
//}
$count++;
}
$sFieldList = join(', ', array_keys($arValues));
$sValueList = implode(', ', $arValueList);
//make sure the table name is properly escaped
$tableName = $this->conn->quoteIdentifier($tableName);
if ($prepare) {
$questionMarks = array_pad(array(), count($arValues), '?');
$qmString = join(', ', $questionMarks);
$sql = "INSERT INTO {$tableName} ( {$sFieldList}) VALUES ( {$qmString} )";
$resource = $this->prepare($sql);
$result = $this->execute($resource, array_values($arValues));
} else {
$sql = "INSERT INTO {$tableName} ( {$sFieldList}) VALUES ( {$sValueList} )";
$result = $this->conn->query($sql);
}
if (DB::isError($result)) {
throw new LoggedException($result->getMessage(), $result->getCode(), self::module);
}
//return the ID, if there was one, or the number of rows affected
return $id ? $id : $this->conn->affectedRows();
}
/**
* Add a having condition
*
* @param string $key Column to filter on
* @param mixed $value [optional] Value to search, set it to null if values are directly setted in $key. Default is null
* @param string $type [optional] Type of request AND or OR. Default is 'AND'
* @return f_db_Where Current instance of f_db_Where
*/
protected function having($key, $value = null, $type = 'AND')
{
$cond = array();
if (!is_array($key)) {
$key = array($key => $value);
}
foreach ($key as $k => $v) {
$varName = 'hvar' . count($this->_having);
// case of no escape
if (preg_match('/\\((.*)\\)/i', $k)) {
if ($value !== null) {
$k = str_replace('?', ':' . $varName, $k);
$this->_vars[$varName] = $v;
}
$this->_having[] = array('noescape' => $k, 'type' => $type);
return $this;
}
if ($v === null) {
$cond['cond'] = DB::quoteIdentifier($k) . ' IS NULL ';
$cond['var'] = '';
} else {
if (Db::hasOperator($k)) {
list($n, $o) = preg_split('/\\s/i', trim($k), 2);
$cond['cond'] = Db::quoteIdentifier($n) . ' ' . $o . ' ';
} else {
$cond['cond'] = DB::quoteIdentifier($k) . '= ';
}
$cond['var'] = ':' . $varName;
$this->_vars[$varName] = $v;
}
$cond['type'] = $type;
$this->_having[] = $cond;
}
return $this;
}
/**
* Build the select request to execute
*
* @return string SQL request
*/
public function build($config = 'db1')
{
$sql = $this->_distinct ? 'SELECT DISTINCT ' : 'SELECT ';
$sql .= !empty($this->_cols) ? join(', ', array_unique(array_map(array('DB', 'quoteIdentifier'), $this->_cols))) . ' ' : '* ';
$sql .= 'FROM ';
$sql .= !empty($this->_from) ? join(', ', array_unique(array_map(array('DB', 'quoteTable'), $this->_from, array($config)))) . ' ' : '* ';
if (!empty($this->_join)) {
foreach ($this->_join as $join) {
if ($join['type'] != '') {
$sql .= $join['type'] . ' ';
}
$sql .= 'JOIN ' . Db::quoteTable($join['table'], $config) . ' ' . Db::quoteIdentifier($join['condition']) . ' ';
}
}
$sql .= $this->_buildWhere();
if (!empty($this->_group)) {
$sql .= 'GROUP BY ';
$sql .= join(', ', array_map(array('DB', 'quoteIdentifier'), $this->_group)) . ' ';
}
$sql .= $this->_buildHaving();
$c = count($this->_order);
if ($c > 0) {
$sql .= 'ORDER BY ';
foreach ($this->_order as $k => $v) {
$sql .= DB::quoteIdentifier($v['field']) . ' ' . $v['type'] . ' ';
if ($c > 1 && $k + 1 < $c) {
$sql .= ', ';
}
}
}
if (isset($this->_limit)) {
$sql .= ' LIMIT ' . $this->_limit;
}
return trim($sql);
}
/**
* Execute the current update builded query
*
* @param string $config [optional] Config to use from the current config file. Default is "db1"
* @return integer Return the number of affected rows
*/
public function execute($config = 'db1')
{
$sql = 'UPDATE ' . DB::quoteTable($this->_table, $config) . ' SET ';
$i = 0;
$fields = array();
foreach ($this->_values as $k => $v) {
$i++;
$fields[] = DB::quoteIdentifier($k) . '=:val' . $i;
$this->_vars['val' . $i] = $v;
}
$sql .= join(',', $fields) . ' ';
$sql .= $this->_buildWhere();
$q = Db::query($sql, $config)->execute($this->_vars);
return is_object($q) ? $q->count() : 0;
}
