cs\DB\PostgreSQL

Complexity

Total Complexity

48

Size/Duplication

Total Lines	262
Duplicated Lines	0 %

Coupling/Cohesion

Components	2
Dependencies	1

Test Coverage

Coverage

0%

Importance

Changes

0

15 Methods

Rating	Name	Duplication	Size	Complexity
A	__construct()	0	17	3
B	get_host_port_and_persistent()	0	22	5
A	q()	0	6	1
B	convert_sql()	0	39	4
A	q_internal()	0	12	3
A	convert_prepared_statements_syntax()	0	11	3
B	f()	0	16	9
A	id()	0	3	1
A	affected()	0	3	2
A	free()	0	6	2
B	columns()	0	22	5
A	tables()	0	20	4
A	s_internal()	0	3	2
A	server()	0	3	1
A	__destruct()	0	6	3

<?php
/**
 * @package   CleverStyle Framework
 * @author    Nazar Mokrynskyi <nazar@mokrynskyi.com>
 * @copyright Copyright (c) 2011-2016, Nazar Mokrynskyi
 * @license   MIT License, see license.txt
 */
namespace cs\DB;
class PostgreSQL extends _Abstract {
	/**
	 * @var resource DB connection handler
	 */
	protected $handler;
	/**
	 * @var resource
	 */
	protected $query_result;
	/**
	 * @inheritdoc
	 */
	public function __construct ($database, $user = '', $password = '', $host = 'localhost', $prefix = '') {
		$start = microtime(true);
		/**
		 * Parsing of $host variable, detecting port and persistent connection
		 */
		list($host, $port, $persistent) = $this->get_host_port_and_persistent($host);
		$connection_string = "host=$host port=$port dbname=$database user=$user password=$password options='--client_encoding=UTF8'";
		$this->handler     = $persistent ? pg_connect($connection_string) : pg_pconnect($connection_string);
		if (!is_resource($this->handler)) {
			return;
		}
		$this->database        = $database;
		$this->connected       = true;
		$this->connecting_time = microtime(true) - $start;
		$this->db_type         = 'postgresql';
		$this->prefix          = $prefix;
	}
	/**
	 * Parse host string into host, port and persistent separately
	 *
	 * Understands `p:` prefix for persistent connections
	 *
	 * @param string $host_string
	 *
	 * @return array
	 */
	protected function get_host_port_and_persistent ($host_string) {
		$host       = explode(':', $host_string);
		$port       = 5432;
		$persistent = false;
		switch (count($host)) {
			case 1:
				$host = $host[0];
				break;
			case 2:
				if ($host[0] == 'p') {
					$persistent = true;
					$host       = $host[1];
				} else {
					list($host, $port) = $host;
				}
				break;
			case 3:
				$persistent = true;
				list(, $host, $port) = $host;
		}
		return [$host, $port, $persistent];
	}
	/**
	 * @inheritdoc
	 */
	public function q ($query, ...$params) {
		return parent::q(
			$this->convert_sql($query),
			...$params
		);
	}
	/**
	 * Convert small subset of MySQL queries into PostgreSQL-compatible syntax
	 *
	 * @param string $query
	 *
	 * @return string
	 */
	protected function convert_sql ($query) {
		$query = str_replace('`', '"', $query);
		return preg_replace_callback(
			'/(INSERT IGNORE INTO|REPLACE INTO)(.+)(;|$)/Uis',
			function ($matches) {
				// Only support simplest cases
				if (stripos($matches[2], 'on duplicate')) {
					return $matches[0];
				}
				switch (strtoupper($matches[1])) {
					case 'INSERT IGNORE INTO':
						return "INSERT INTO $matches[2] ON CONFLICT DO NOTHING$matches[3]";
					case 'REPLACE INTO':
						$table_name = substr(
							$matches[2],
							strpos($matches[2], '"') + 1
						);
						$table_name = substr(
							$table_name,
							0,
							strpos($table_name, '"')
						);
						$update     = preg_replace_callback(
							'/"([^"]+)"/',
							function ($matches) {
								return "\"$matches[1]\" = EXCLUDED.\"$matches[1]\"";
							},
							substr(
								strstr($matches[2], ')', true),
								strpos($matches[2], '(') + 1
							)
						);
						// Only support constraint named as table with `_primary` prefix
						return "INSERT INTO $matches[2] ON CONFLICT ON CONSTRAINT \"{$table_name}_primary\" DO UPDATE SET $update$matches[3]";
				}
			},
			$query
		);
	}
	/**
	 * @inheritdoc
	 *
	 * @return false|resource
	 */
	protected function q_internal ($query, $parameters = []) {
		if (!$query) {
			return false;
		}
		if ($parameters) {
			// Allows to provide more parameters for prepared statements than needed
			$local_parameters = array_slice($parameters, 0, substr_count($query, '?'));
			$query            = $this->convert_prepared_statements_syntax($query);
			return $this->query_result = pg_query_params($this->handler, $query, $local_parameters);
		}
		return $this->query_result = pg_query($this->handler, $query);
	}
	/**
	 * @param string|string[] $query
	 *
	 * @return string|string[]
	 */
	protected function convert_prepared_statements_syntax ($query) {
		if (is_array($query)) {
			return array_map([$this, 'convert_prepared_statements_syntax'], $query);
		}
		$i = 1;
		while ($q_pos = strpos($query, '?')) {
			$query = substr($query, 0, $q_pos)."$$i".substr($query, $q_pos + 1);
			++$i;
		}
		return $query;
	}
	/**
	 * @inheritdoc
	 *
	 * @param false|resource $query_result
	 */
	public function f ($query_result, $single_column = false, $array = false, $indexed = false) {
		if (!is_resource($query_result)) {
			return false;
		}
		$result_type = $single_column || $indexed ? PGSQL_NUM : PGSQL_ASSOC;
		if ($array) {
			$result = [];
			while ($current = pg_fetch_array($query_result, null, $result_type)) {
				$result[] = $single_column ? $current[0] : $current;
			}
			$this->free($query_result);
			return $result;
		}
		$result = pg_fetch_array($query_result, null, $result_type);
		return $single_column && $result ? $result[0] : $result;
	}
	/**
	 * @inheritdoc
	 */
	public function id () {
		return (int)$this->qfs('SELECT lastval()');
	}
	/**
	 * @inheritdoc
	 */
	public function affected () {
		return is_resource($this->query_result) ? pg_affected_rows($this->query_result) : 0;
	}
	/**
	 * @inheritdoc
	 *
	 * @param false|resource $query_result
	 */
	public function free ($query_result) {
		if (is_resource($query_result)) {
			return pg_free_result($query_result);
		}
		return true;
	}
	/**
	 * @inheritdoc
	 */
	public function columns ($table, $like = false) {
		if (!$table) {
			return false;
		}
		if ($like) {
			$like    = $this->s($like);
			$columns = $this->qfas(
				"SELECT `column_name` 
				FROM `information_schema`.`columns`
				WHERE
					`table_name` = '$table' AND
					`column_name` LIKE $like"
			) ?: [];
		} else {
			$columns = $this->qfas(
				"SELECT `column_name`
				FROM `information_schema`.`columns`
				WHERE `table_name` = '$table'"
			) ?: [];
		}
		return $columns;
	}
	/**
	 * @inheritdoc
	 */
	public function tables ($like = false) {
		if ($like) {
			$like = $this->s($like);
			return $this->qfas(
				"SELECT `table_name`
				FROM `information_schema`.`tables`
				WHERE
					`table_schema` = 'public' AND
					`table_name` LIKE $like
				ORDER BY `table_name` ASC"
			) ?: [];
		} else {
			return $this->qfas(
				"SELECT `table_name`
				FROM `information_schema`.`tables`
				WHERE `table_schema` = 'public'
				ORDER BY `table_name` ASC"
			) ?: [];
		}
	}
	/**
	 * @inheritdoc
	 */
	protected function s_internal ($string, $single_quotes_around) {
		return $single_quotes_around ? pg_escape_literal($this->handler, $string) : pg_escape_string($this->handler, $string);
	}
	/**
	 * @inheritdoc
	 */
	public function server () {
		return pg_version($this->handler)['server'];
	}
	/**
	 * @inheritdoc
	 */
	public function __destruct () {
		if ($this->connected && is_resource($this->handler)) {
			pg_close($this->handler);
			$this->connected = false;
		}
	}
}