/** * 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; }