public $connected = false;
public $error = false;
private $_server_info = '';
+ private static $in_transaction = false;
private static $dbo;
private static $relation = array();
return $sql;
}
+ /**
+ * @brief beautifies the query - useful for "SHOW PROCESSLIST"
+ *
+ * This is safe when we bind the parameters later.
+ * The parameter values aren't part of the SQL.
+ *
+ * @param string $sql An SQL string without the values
+ * @return string The input SQL string modified if necessary.
+ */
+ public function clean_query($sql) {
+ $search = array("\t", "\n", "\r", " ");
+ $replace = array(' ', ' ', ' ', ' ');
+ do {
+ $oldsql = $sql;
+ $sql = str_replace($search, $replace, $sql);
+ } while ($oldsql != $sql);
+
+ return $sql;
+ }
+
+
/**
* @brief Replaces the ? placeholders with the parameters in the $args array
*
unset($args[0]);
// When the second function parameter is an array then use this as the parameter array
- if ((count($args) == 1) AND (is_array($args[1]))) {
+ if ((count($args) > 0) AND (is_array($args[1]))) {
$params = $args[1];
- $i = 0;
- foreach ($params AS $param) {
- $args[++$i] = $param;
- }
+ } else {
+ $params = $args;
+ }
+
+ // Renumber the array keys to be sure that they fit
+ $i = 0;
+ $args = array();
+ foreach ($params AS $param) {
+ $args[++$i] = $param;
}
if (!self::$dbo OR !self::$dbo->connected) {
return false;
}
+ if (substr_count($sql, '?') != count($args)) {
+ // Question: Should we continue or stop the query here?
+ logger('Parameter mismatch. Query "'.$sql.'" - Parameters '.print_r($args, true), LOGGER_DEBUG);
+ }
+
+ $sql = self::$dbo->clean_query($sql);
$sql = self::$dbo->any_value_fallback($sql);
if (x($a->config,'system') && x($a->config['system'], 'db_callstack')) {
}
if (self::$dbo->errorno != 0) {
- logger('DB Error '.self::$dbo->errorno.': '.self::$dbo->error."\n".self::replace_parameters($sql, $args));
+ logger('DB Error '.self::$dbo->errorno.': '.self::$dbo->error."\n".
+ $a->callstack(8))."\n".self::replace_parameters($sql, $args);
}
$a->save_timestamp($stamp1, 'database');
* @return int Number of rows
*/
static public function num_rows($stmt) {
+ if (!is_object($stmt)) {
+ return 0;
+ }
switch (self::$dbo->driver) {
case 'pdo':
return $stmt->rowCount();
return self::e($sql, $param);
}
+ /**
+ * @brief Locks a table for exclusive write access
+ *
+ * This function can be extended in the future to accept a table array as well.
+ *
+ * @param string $table Table name
+ *
+ * @return boolean was the lock successful?
+ */
+ static public function lock($table) {
+ // See here: https://dev.mysql.com/doc/refman/5.7/en/lock-tables-and-transactions.html
+ self::e("SET autocommit=0");
+ $success = self::e("LOCK TABLES `".self::$dbo->escape($table)."` WRITE");
+ if (!$success) {
+ self::e("SET autocommit=1");
+ } else {
+ self::$in_transaction = true;
+ }
+ return $success;
+ }
+
+ /**
+ * @brief Unlocks all locked tables
+ *
+ * @return boolean was the unlock successful?
+ */
+ static public function unlock() {
+ // See here: https://dev.mysql.com/doc/refman/5.7/en/lock-tables-and-transactions.html
+ self::e("COMMIT");
+ $success = self::e("UNLOCK TABLES");
+ self::e("SET autocommit=1");
+ self::$in_transaction = false;
+ return $success;
+ }
+
+ /**
+ * @brief Starts a transaction
+ *
+ * @return boolean Was the command executed successfully?
+ */
+ static public function transaction() {
+ if (!self::e('COMMIT')) {
+ return false;
+ }
+ if (!self::e('START TRANSACTION')) {
+ return false;
+ }
+ self::$in_transaction = true;
+ return true;
+ }
+
+ /**
+ * @brief Does a commit
+ *
+ * @return boolean Was the command executed successfully?
+ */
+ static public function commit() {
+ if (!self::e('COMMIT')) {
+ return false;
+ }
+ self::$in_transaction = false;
+ return true;
+ }
+
+ /**
+ * @brief Does a rollback
+ *
+ * @return boolean Was the command executed successfully?
+ */
+ static public function rollback() {
+ if (!self::e('ROLLBACK')) {
+ return false;
+ }
+ self::$in_transaction = false;
+ return true;
+ }
+
/**
* @brief Build the array with the table relations
*
}
/**
- * @brief Insert a row into a table
+ * @brief Delete a row from a table
*
* @param string $table Table name
* @param array $param parameter array
- * @param boolean $in_commit Internal use: Only do a commit after the last delete
+ * @param boolean $in_process Internal use: Only do a commit after the last delete
* @param array $callstack Internal use: prevent endless loops
*
- * @return boolean|array was the delete successfull? When $in_commit is set: deletion data
+ * @return boolean|array was the delete successfull? When $in_process is set: deletion data
*/
- static public function delete($table, $param, $in_commit = false, &$callstack = array()) {
+ static public function delete($table, $param, $in_process = false, &$callstack = array()) {
$commands = array();
}
}
- if (!$in_commit) {
+ if (!$in_process) {
// Now we finalize the process
- self::p("COMMIT");
- self::p("START TRANSACTION");
+ $do_transaction = !self::$in_transaction;
+
+ if ($do_transaction) {
+ self::transaction();
+ }
$compacted = array();
$counter = array();
logger(dba::replace_parameters($sql, $command['param']), LOGGER_DATA);
- if (!self::e($sql, $param)) {
- self::p("ROLLBACK");
+ if (!self::e($sql, $command['param'])) {
+ if ($do_transaction) {
+ self::rollback();
+ }
return false;
}
} else {
logger(dba::replace_parameters($sql, $field_values), LOGGER_DATA);
- if (!self::e($sql, $param)) {
- self::p("ROLLBACK");
+ if (!self::e($sql, $field_values)) {
+ if ($do_transaction) {
+ self::rollback();
+ }
return false;
}
}
}
}
- self::p("COMMIT");
+ if ($do_transaction) {
+ self::commit();
+ }
return true;
}
return self::e($sql, $params);
}
+ /**
+ * @brief Select rows from a table
+ *
+ * @param string $table Table name
+ * @param array $fields array of selected fields
+ * @param array $condition array of fields for condition
+ * @param array $params array of several parameters
+ *
+ * @return boolean|object If "limit" is equal "1" only a single row is returned, else a query object is returned
+ *
+ * Example:
+ * $table = "item";
+ * $fields = array("id", "uri", "uid", "network");
+ * $condition = array("uid" => 1, "network" => 'dspr');
+ * $params = array("order" => array("id", "received" => true), "limit" => 1);
+ *
+ * $data = dba::select($table, $fields, $condition, $params);
+ */
+ static public function select($table, $fields = array(), $condition = array(), $params = array()) {
+ if ($table == '') {
+ return false;
+ }
+
+ if (count($fields) > 0) {
+ $select_fields = "`".implode("`, `", array_values($fields))."`";
+ } else {
+ $select_fields = "*";
+ }
+
+ if (count($condition) > 0) {
+ $condition_string = " WHERE `".implode("` = ? AND `", array_keys($condition))."` = ?";
+ } else {
+ $condition_string = "";
+ }
+
+ $param_string = '';
+ $single_row = false;
+
+ if (isset($params['order'])) {
+ $param_string .= " ORDER BY ";
+ foreach ($params['order'] AS $fields => $order) {
+ if (!is_int($fields)) {
+ $param_string .= "`".$fields."` ".($order ? "DESC" : "ASC").", ";
+ } else {
+ $param_string .= "`".$order."`, ";
+ }
+ }
+ $param_string = substr($param_string, 0, -2);
+ }
+
+ if (isset($params['limit'])) {
+ if (is_int($params['limit'])) {
+ $param_string .= " LIMIT ".$params['limit'];
+ $single_row =($params['limit'] == 1);
+ }
+ }
+
+ $sql = "SELECT ".$select_fields." FROM `".$table."`".$condition_string.$param_string;
+
+ $result = self::p($sql, $condition);
+
+ if (is_bool($result) OR !$single_row) {
+ return $result;
+ } else {
+ $row = self::fetch($result);
+ self::close($result);
+ return $row;
+ }
+ }
+
/**
* @brief Closes the current statement
*
unset($args[0]);
if ($db && $db->connected) {
+ $sql = $db->clean_query($sql);
$sql = $db->any_value_fallback($sql);
$stmt = @vsprintf($sql,$args); // Disabled warnings
//logger("dba: q: $stmt", LOGGER_ALL);
unset($args[0]);
if ($db && $db->connected) {
+ $sql = $db->clean_query($sql);
$sql = $db->any_value_fallback($sql);
$stmt = @vsprintf($sql,$args); // Disabled warnings
if ($stmt === false)