X-Git-Url: https://git.mxchange.org/?a=blobdiff_plain;f=include%2Fdba.php;h=1f428bf4651b2b43ed0c47dc785761b3b0d1d443;hb=bee6ad5916159f5218903bbd656bdae6a93089ca;hp=8cbad876f50d41f419f8cb9de966f437cf572fb0;hpb=6882b5cda9e3f31e438cd905e963064257c45130;p=friendica.git

diff --git a/include/dba.php b/include/dba.php
index 8cbad876f5..1f428bf465 100644
--- a/include/dba.php
+++ b/include/dba.php
@@ -22,14 +22,24 @@ class dba {
 	public  $connected = false;
 	public  $error = false;
 	private $_server_info = '';
+	private static $in_transaction = false;
 	private static $dbo;
 	private static $relation = array();
 
-	function __construct($server, $user, $pass, $db, $install = false) {
+	function __construct($serveraddr, $user, $pass, $db, $install = false) {
 		$a = get_app();
 
 		$stamp1 = microtime(true);
 
+		$serveraddr = trim($serveraddr);
+
+		$serverdata = explode(':', $serveraddr);
+		$server = $serverdata[0];
+
+		if (count($serverdata) > 1) {
+			$port = trim($serverdata[1]);
+		}
+
 		$server = trim($server);
 		$user = trim($user);
 		$pass = trim($pass);
@@ -55,6 +65,11 @@ class dba {
 		if (class_exists('\PDO') && in_array('mysql', PDO::getAvailableDrivers())) {
 			$this->driver = 'pdo';
 			$connect = "mysql:host=".$server.";dbname=".$db;
+
+			if (isset($port)) {
+				$connect .= ";port=".$port;
+			}
+
 			if (isset($a->config["system"]["db_charset"])) {
 				$connect .= ";charset=".$a->config["system"]["db_charset"];
 			}
@@ -64,7 +79,7 @@ class dba {
 			}
 		} elseif (class_exists('mysqli')) {
 			$this->driver = 'mysqli';
-			$this->db = @new mysqli($server,$user,$pass,$db);
+			$this->db = @new mysqli($server, $user, $pass, $db, $port);
 			if (!mysqli_connect_errno()) {
 				$this->connected = true;
 
@@ -74,8 +89,8 @@ class dba {
 			}
 		} elseif (function_exists('mysql_connect')) {
 			$this->driver = 'mysql';
-			$this->db = mysql_connect($server,$user,$pass);
-			if ($this->db && mysql_select_db($db,$this->db)) {
+			$this->db = mysql_connect($serveraddr, $user, $pass);
+			if ($this->db && mysql_select_db($db, $this->db)) {
 				$this->connected = true;
 
 				if (isset($a->config["system"]["db_charset"])) {
@@ -444,6 +459,27 @@ class dba {
 		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
 	 *
@@ -484,18 +520,29 @@ class dba {
 		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')) {
@@ -553,9 +600,10 @@ class dba {
 					$values[] = &$args[$param];
 				}
 
-				array_unshift($values, $params);
-
-				call_user_func_array(array($stmt, 'bind_param'), $values);
+				if (count($values) > 0) {
+					array_unshift($values, $params);
+					call_user_func_array(array($stmt, 'bind_param'), $values);
+				}
 
 				if (!$stmt->execute()) {
 					self::$dbo->error = self::$dbo->db->error;
@@ -577,7 +625,8 @@ class dba {
 		}
 
 		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');
@@ -681,6 +730,9 @@ class dba {
 	 * @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();
@@ -754,6 +806,83 @@ class dba {
 		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
 	 *
@@ -776,16 +905,16 @@ class dba {
 	}
 
 	/**
-	 * @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();
 
@@ -847,10 +976,13 @@ class dba {
 			}
 		}
 
-		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();
@@ -861,8 +993,10 @@ class dba {
 
 					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 {
@@ -889,14 +1023,18 @@ class dba {
 
 						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;
 		}
 
@@ -991,6 +1129,76 @@ class dba {
 		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
 	 *
@@ -1052,6 +1260,7 @@ function q($sql) {
 	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);
@@ -1089,6 +1298,7 @@ function qu($sql) {
 	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)