PostgreSQL::convert_sql() - Code Metrics - Inspection of "Small refactoring" - nazar-pc/CleverStyle-Framework - Measure and Improve Code Quality continuously with Scrutinizer

Completed

Push — master ( 238e1f...47c7ce )

by Nazar

created 2016-08-17 20:01 UTC

PostgreSQL::convert_sql() B

↳ Parent: PostgreSQL

Complexity

Conditions	4
Paths	1

Size

Total Lines	39
Code Lines	27

Duplication

Lines	0
Ratio	0 %

Code Coverage

Tests	0
CRAP Score	20

Importance

Changes

Metric	Value
cc	4
eloc	27
nc	1
nop	1
dl	0
loc	39
ccs	0
cts	36
cp	0
crap	20
rs	8.5806
c	0
b	0
f	0

<?php
/**
 * @package   CleverStyle Framework
 * @author    Nazar Mokrynskyi <[email protected]>
 * @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;
		}
	}
}


1			<?php
2			/**
3			* @package CleverStyle Framework
4			* @author Nazar Mokrynskyi <[email protected]>
5			* @copyright Copyright (c) 2011-2016, Nazar Mokrynskyi
6			* @license MIT License, see license.txt
7			*/
8			namespace cs\DB;
9			class PostgreSQL extends _Abstract {
10			/**
11			* @var resource DB connection handler
12			*/
13			protected $handler;
14			/**
15			* @var resource
16			*/
17			protected $query_result;
18			/**
19			* @inheritdoc
20			*/
21			public function __construct ($database, $user = '', $password = '', $host = 'localhost', $prefix = '') {
22			$start = microtime(true);
23			/**
24			* Parsing of $host variable, detecting port and persistent connection
25			*/
26			list($host, $port, $persistent) = $this->get_host_port_and_persistent($host);
27			$connection_string = "host=$host port=$port dbname=$database user=$user password=$password options='--client_encoding=UTF8'";
28			$this->handler = $persistent ? pg_connect($connection_string) : pg_pconnect($connection_string);
29			if (!is_resource($this->handler)) {
30			return;
31			}
32			$this->database = $database;
33			$this->connected = true;
34			$this->connecting_time = microtime(true) - $start;
35			$this->db_type = 'postgresql';
36			$this->prefix = $prefix;
37			}
38			/**
39			* Parse host string into host, port and persistent separately
40			*
41			* Understands `p:` prefix for persistent connections
42			*
43			* @param string $host_string
44			*
45			* @return array
46			*/
47			protected function get_host_port_and_persistent ($host_string) {
48			$host = explode(':', $host_string);
49			$port = 5432;
50			$persistent = false;
51			switch (count($host)) {
52			case 1:
53			$host = $host[0];
54			break;
55			case 2:
56			if ($host[0] == 'p') {
57			$persistent = true;
58			$host = $host[1];
59			} else {
60			list($host, $port) = $host;
61			}
62			break;
63			case 3:
64			$persistent = true;
65			list(, $host, $port) = $host;
66			}
67			return [$host, $port, $persistent];
68			}
69			/**
70			* @inheritdoc
71			*/
72			public function q ($query, ...$params) {
73			return parent::q(
74			$this->convert_sql($query),
75			...$params
76			);
77			}
78			/**
79			* Convert small subset of MySQL queries into PostgreSQL-compatible syntax
80			*
81			* @param string $query
82			*
83			* @return string
84			*/
85			protected function convert_sql ($query) {
86			$query = str_replace('`', '"', $query);
87			return preg_replace_callback(
88			'/(INSERT IGNORE INTO\|REPLACE INTO)(.+)(;\|$)/Uis',
89			function ($matches) {
90			// Only support simplest cases
91			if (stripos($matches[2], 'on duplicate')) {
92			return $matches[0];
93			}
94			switch (strtoupper($matches[1])) {
95			case 'INSERT IGNORE INTO':
96			return "INSERT INTO $matches[2] ON CONFLICT DO NOTHING$matches[3]";
97			case 'REPLACE INTO':
98			$table_name = substr(
99			$matches[2],
100			strpos($matches[2], '"') + 1
101			);
102			$table_name = substr(
103			$table_name,
104			0,
105			strpos($table_name, '"')
106			);
107			$update = preg_replace_callback(
108			'/"([^"]+)"/',
109			function ($matches) {
110			return "\"$matches[1]\" = EXCLUDED.\"$matches[1]\"";
111			},
112			substr(
113			strstr($matches[2], ')', true),
114			strpos($matches[2], '(') + 1
115			)
116			);
117			// Only support constraint named as table with `_primary` prefix
118			return "INSERT INTO $matches[2] ON CONFLICT ON CONSTRAINT \"{$table_name}_primary\" DO UPDATE SET $update$matches[3]";
119			}
120			},
121			$query
122			);
123			}
124			/**
125			* @inheritdoc
126			*
127			* @return false\|resource
128			*/
129			protected function q_internal ($query, $parameters = []) {
130			if (!$query) {
131			return false;
132			}
133			if ($parameters) {
134			// Allows to provide more parameters for prepared statements than needed
135			$local_parameters = array_slice($parameters, 0, substr_count($query, '?'));
136			$query = $this->convert_prepared_statements_syntax($query);
137			return $this->query_result = pg_query_params($this->handler, $query, $local_parameters);
138			}
139			return $this->query_result = pg_query($this->handler, $query);
140			}
141			/**
142			* @param string\|string[] $query
143			*
144			* @return string\|string[]
145			*/
146			protected function convert_prepared_statements_syntax ($query) {
147			if (is_array($query)) {
148			return array_map([$this, 'convert_prepared_statements_syntax'], $query);
149			}
150			$i = 1;
151			while ($q_pos = strpos($query, '?')) {
152			$query = substr($query, 0, $q_pos)."$$i".substr($query, $q_pos + 1);
153			++$i;
154			}
155			return $query;
156			}
157			/**
158			* @inheritdoc
159			*
160			* @param false\|resource $query_result
161			*/
162			public function f ($query_result, $single_column = false, $array = false, $indexed = false) {
163			if (!is_resource($query_result)) {
164			return false;
165			}
166			$result_type = $single_column \|\| $indexed ? PGSQL_NUM : PGSQL_ASSOC;
167			if ($array) {
168			$result = [];
169			while ($current = pg_fetch_array($query_result, null, $result_type)) {
170			$result[] = $single_column ? $current[0] : $current;
171			}
172			$this->free($query_result);
173			return $result;
174			}
175			$result = pg_fetch_array($query_result, null, $result_type);
176			return $single_column && $result ? $result[0] : $result;
177			}
178			/**
179			* @inheritdoc
180			*/
181			public function id () {
182			return (int)$this->qfs('SELECT lastval()');
183			}
184			/**
185			* @inheritdoc
186			*/
187			public function affected () {
188			return is_resource($this->query_result) ? pg_affected_rows($this->query_result) : 0;
189			}
190			/**
191			* @inheritdoc
192			*
193			* @param false\|resource $query_result
194			*/
195			public function free ($query_result) {
196			if (is_resource($query_result)) {
197			return pg_free_result($query_result);
198			}
199			return true;
200			}
201			/**
202			* @inheritdoc
203			*/
204			public function columns ($table, $like = false) {
205			if (!$table) {
206			return false;
207			}
208			if ($like) {
209			$like = $this->s($like);
210			$columns = $this->qfas(
211			"SELECT `column_name`
212			FROM `information_schema`.`columns`
213			WHERE
214			`table_name` = '$table' AND
215			`column_name` LIKE $like"
216			) ?: [];
217			} else {
218			$columns = $this->qfas(
219			"SELECT `column_name`
220			FROM `information_schema`.`columns`
221			WHERE `table_name` = '$table'"
222			) ?: [];
223			}
224			return $columns;
225			}
226			/**
227			* @inheritdoc
228			*/
229			public function tables ($like = false) {
230			if ($like) {
231			$like = $this->s($like);
232			return $this->qfas(
233			"SELECT `table_name`
234			FROM `information_schema`.`tables`
235			WHERE
236			`table_schema` = 'public' AND
237			`table_name` LIKE $like
238			ORDER BY `table_name` ASC"
239			) ?: [];
240			} else {
241			return $this->qfas(
242			"SELECT `table_name`
243			FROM `information_schema`.`tables`
244			WHERE `table_schema` = 'public'
245			ORDER BY `table_name` ASC"
246			) ?: [];
247			}
248			}
249			/**
250			* @inheritdoc
251			*/
252			protected function s_internal ($string, $single_quotes_around) {
253			return $single_quotes_around ? pg_escape_literal($this->handler, $string) : pg_escape_string($this->handler, $string);
254			}
255			/**
256			* @inheritdoc
257			*/
258			public function server () {
259			return pg_version($this->handler)['server'];
260			}
261			/**
262			* @inheritdoc
263			*/
264			public function __destruct () {
265			if ($this->connected && is_resource($this->handler)) {
266			pg_close($this->handler);
267			$this->connected = false;
268			}
269			}
270			}
271

nazar-pc / CleverStyle-Framework

Push — master ( 238e1f...47c7ce )

PostgreSQL::convert_sql() B

Complexity

Size

Duplication

Code Coverage

Importance

Duplication Side-by-Side

Filter issues like