Completed
Push — master ( 79c656...2f230c )
by Nazar
05:08
created

_Abstract::tables()

Size

Total Lines 1

Duplication

Lines 0
Ratio 0 %

Importance

Changes 0
Metric Value
nc 1
dl 0
loc 1
ccs 0
cts 0
cp 0
c 0
b 0
f 0
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
use
10
	Exception;
11
12
abstract class _Abstract {
13
	/**
14
	 * Is connection established
15
	 *
16
	 * @var bool
17
	 */
18
	protected $connected = false;
19
	/**
20
	 * DB type, may be used for constructing requests, accounting particular features of current DB (lowercase name)
21
	 *
22
	 * @var string
23
	 */
24
	protected $db_type = '';
25
	/**
26
	 * Current DB
27
	 *
28
	 * @var string
29
	 */
30
	protected $database;
31
	/**
32
	 * Current prefix
33
	 *
34
	 * @var string
35
	 */
36
	protected $prefix;
37
	/**
38
	 * Total time of requests execution
39
	 *
40
	 * @var float
41
	 */
42
	protected $time = 0;
43
	/**
44
	 * Total number of executed requests
45
	 *
46
	 * @var int
47
	 */
48
	protected $queries_count = 0;
49
	/**
50
	 * Connection time
51
	 *
52
	 * @var float
53
	 */
54
	protected $connecting_time = 0;
55
	/**
56
	 * @var bool
57
	 */
58
	protected $in_transaction = false;
59
	/**
60
	 * Connecting to the DB
61
	 *
62
	 * @param string $database
63
	 * @param string $user
64
	 * @param string $password
65
	 * @param string $host
66
	 * @param string $prefix
67
	 */
68
	abstract public function __construct ($database, $user = '', $password = '', $host = 'localhost', $prefix = '');
69
	/**
70
	 * Query
71
	 *
72
	 * SQL request into DB
73
	 *
74
	 * @abstract
75
	 *
76
	 * @param string|string[] $query      SQL query string or array, may be a format string in accordance with the first parameter of sprintf() function or may
77
	 *                                    contain markers for prepared statements (but not both at the same time)
78
	 * @param array           $parameters There might be arbitrary number of parameters for formatting SQL statement or for using in prepared statements.<br>
79
	 *                                    If an array provided as second argument - its items will be used, so that you can either specify parameters as an
80
	 *                                    array, or in line.
81
	 *
82
	 * @return bool|object|resource
83
	 */
84 64
	public function q ($query, ...$parameters) {
85 64
		$normalized = $this->normalize_parameters($query, $parameters);
86 64
		if (!$normalized) {
87 2
			return false;
88
		}
89 64
		list($query, $parameters) = $normalized;
90
		/**
91
		 * Executing multiple queries
92
		 */
93 64
		if (is_array($query)) {
94 16
			return $this->execute_multiple($query, $parameters);
95
		}
96 64
		return $this->execute_single($query, $parameters);
97
	}
98
	/**
99
	 * @param string|string[] $query
100
	 * @param array           $parameters
101
	 *
102
	 * @return array|false
103
	 */
104 64
	protected function normalize_parameters ($query, $parameters) {
105 64
		if (!$query) {
106 2
			return false;
107
		}
108 64
		$query = str_replace('[prefix]', $this->prefix, $query);
109
		/** @noinspection NotOptimalIfConditionsInspection */
110 64
		if (count($parameters) == 1 && is_array($parameters[0])) {
111 54
			$parameters = $parameters[0];
112
		}
113
		return [
114 64
			$query,
115 64
			array_values($parameters)
116
		];
117
	}
118
	/**
119
	 * @param string[] $queries
120
	 * @param string[] $parameters
121
	 *
122
	 * @return bool
123
	 */
124 16
	protected function execute_multiple ($queries, $parameters) {
125 16
		$time_from         = microtime(true);
126 16
		$parameters_server = [];
127 16
		foreach ($queries as &$q) {
128 16
			$q = $this->prepare_query_and_parameters($q, $parameters);
129 16
			if ($q[1]) {
130 2
				$q                 = $q[0];
131 2
				$parameters_server = $parameters;
132 2
				break;
133
			}
134 16
			$q = $q[0];
135
		}
136 16
		unset($q);
137 16
		$this->queries_count += count($queries);
138 16
		$result = $this->q_multi_internal($queries, $parameters_server);
139 16
		$this->time += round(microtime(true) - $time_from, 6);
140 16
		return $result;
141
	}
142
	/**
143
	 * @param string   $query
144
	 * @param string[] $parameters
145
	 *
146
	 * @return array
147
	 */
148 64
	protected function prepare_query_and_parameters ($query, $parameters) {
149 64
		if (!$parameters || strpos($query, '?') !== false) {
150 64
			return [$query, $parameters];
151
		}
152 48
		foreach ($parameters as &$parameter) {
153 48
			$parameter = $this->s($parameter, false);
154
		}
155 48
		return [vsprintf($query, $parameters), []];
156
	}
157
	/**
158
	 * @param string   $query
159
	 * @param string[] $parameters
160
	 *
161
	 * @return bool|object|resource
162
	 */
163 64
	protected function execute_single ($query, $parameters) {
164 64
		$time_from = microtime(true);
165 64
		list($query, $parameters) = $this->prepare_query_and_parameters($query, $parameters);
166 64
		$result = $this->q_internal($query, $parameters);
167 64
		$this->time += round(microtime(true) - $time_from, 6);
168 64
		++$this->queries_count;
169 64
		return $result;
170
	}
171
	/**
172
	 * SQL request to DB
173
	 *
174
	 * @abstract
175
	 *
176
	 * @param string   $query
177
	 * @param string[] $parameters If not empty, than server-side prepared statements should be used
178
	 *
179
	 * @return bool|object|resource
180
	 */
181
	abstract protected function q_internal ($query, $parameters = []);
182
	/**
183
	 * Multiple SQL request to DB
184
	 *
185
	 * @abstract
186
	 *
187
	 * @param string[] $query
188
	 * @param string[] $parameters If not empty, than server-side prepared statements should be used
189
	 *
190
	 * @return bool
191
	 */
192 16
	protected function q_multi_internal ($query, $parameters = []) {
193 16
		$result = true;
194 16
		foreach ($query as $q) {
195 16
			$result = $result && $this->q_internal($q, $parameters);
196
		}
197 16
		return $result;
198
	}
199
	/**
200
	 * Fetch
201
	 *
202
	 * Fetch a result row as an associative array
203
	 *
204
	 * @abstract
205
	 *
206
	 * @param false|object|resource $query_result
207
	 * @param bool                  $single_column If <b>true</b> function will return not array with one element, but directly its value
208
	 * @param bool                  $array         If <b>true</b> returns array of associative arrays of all fetched rows
209
	 * @param bool                  $indexed       If <b>false</b> - associative array will be returned
210
	 *
211
	 * @return array[]|false|int|int[]|string|string[]
212
	 */
213
	abstract public function f ($query_result, $single_column = false, $array = false, $indexed = false);
214
	/**
215
	 * Query, Fetch
216
	 *
217
	 * Short for `::f(::q())`, arguments are exactly the same as in `::q()`
218
	 *
219
	 * @param string[] $query
220
	 *
221
	 * @return array|false
222
	 */
223 54
	public function qf (...$query) {
224 54
		return $this->f($this->q(...$query));
225
	}
226
	/**
227
	 * Query, Fetch, Array
228
	 *
229
	 * Short for `::f(::q(), false, true)`, arguments are exactly the same as in `::q()`
230
	 *
231
	 * @param string[] $query
232
	 *
233
	 * @return array[]|false
234
	 */
235 34
	public function qfa (...$query) {
236 34
		return $this->f($this->q(...$query), false, true);
237
	}
238
	/**
239
	 * Query, Fetch, Single
240
	 *
241
	 * Short for `::f(::q(), true)`, arguments are exactly the same as in `::q()`
242
	 *
243
	 * @param string[] $query
244
	 *
245
	 * @return false|int|string
246
	 */
247 48
	public function qfs (...$query) {
248 48
		return $this->f($this->q(...$query), true);
249
	}
250
	/**
251
	 * Query, Fetch, Array, Single
252
	 *
253
	 * Short for `::f(::q(), true, true)`, arguments are exactly the same as in `::q()`
254
	 *
255
	 * @param string[] $query
256
	 *
257
	 * @return false|int[]|string[]
258
	 */
259 47
	public function qfas (...$query) {
260 47
		return $this->f($this->q(...$query), true, true);
261
	}
262
	/**
263
	 * Method for simplified inserting of several rows
264
	 *
265
	 * @param string        $query
266
	 * @param array|array[] $parameters Array of array of parameters for inserting
267
	 * @param bool          $join       If true - inserting of several rows will be combined in one query. For this, be sure, that your query has keyword
268
	 *                                  <i>VALUES</i> in uppercase. Part of query after this keyword will be multiplied with coma separator.
269
	 *
270
	 * @return bool
271
	 */
272 42
	public function insert ($query, $parameters, $join = true) {
273 42
		if (!$query || !$parameters) {
274 2
			return false;
275
		}
276 42
		if ($join) {
277 42
			$query    = explode('VALUES', $query, 2);
278 42
			$query[1] = explode(')', $query[1], 2);
279
			$query    = [
280 42
				$query[0],
281 42
				$query[1][0].')',
282 42
				$query[1][1]
283
			];
284 42
			$query[1] .= str_repeat(",$query[1]", count($parameters) - 1);
285 42
			$query = $query[0].'VALUES'.$query[1].$query[2];
286 42
			return (bool)$this->q(
287
				$query,
288 42
				array_merge(...array_map('array_values', _array($parameters)))
289
			);
290
		} else {
291 2
			$result = true;
292 2
			foreach ($parameters as $p) {
293 2
				$result = $result && (bool)$this->q($query, $p);
294
			}
295 2
			return $result;
296
		}
297
	}
298
	/**
299
	 * Id
300
	 *
301
	 * Get id of last inserted row
302
	 *
303
	 * @abstract
304
	 *
305
	 * @return int
306
	 */
307
	abstract public function id ();
308
	/**
309
	 * Affected
310
	 *
311
	 * Get number of affected rows during last query
312
	 *
313
	 * @abstract
314
	 *
315
	 * @return int
316
	 */
317
	abstract public function affected ();
318
	/**
319
	 * Execute transaction
320
	 *
321
	 * All queries done inside callback will be within single transaction, throwing any exception or returning boolean `false` from callback will cause
322
	 * rollback. Nested transaction calls will be wrapped into single big outer transaction, so you might call it safely if needed.
323
	 *
324
	 * @param callable $callback This instance will be used as single argument
325
	 *
326
	 * @return bool
327
	 *
328
	 * @throws Exception Re-throws exception thrown inside callback
329
	 */
330 54
	public function transaction ($callback) {
331 54
		$start_transaction = !$this->in_transaction;
332 54
		if ($start_transaction) {
333 54
			$this->in_transaction = true;
334 54
			if (!$this->q_internal('BEGIN')) {
335
				return false;
336
			}
337
		}
338
		try {
339 54
			$result = $callback($this);
340 2
		} catch (Exception $e) {
341 2
			$this->transaction_rollback();
342 2
			throw $e;
343
		}
344 54
		if ($result === false) {
345 2
			$this->transaction_rollback();
346 2
			return false;
347 54
		} elseif ($start_transaction) {
348 54
			$this->in_transaction = false;
349 54
			return (bool)$this->q_internal('COMMIT');
350
		}
351 2
		return true;
352
	}
353 2
	protected function transaction_rollback () {
354 2
		if ($this->in_transaction) {
355 2
			$this->in_transaction = false;
356 2
			$this->q_internal('ROLLBACK');
357
		}
358 2
	}
359
	/**
360
	 * Free result memory
361
	 *
362
	 * @abstract
363
	 *
364
	 * @param false|object|resource $query_result
365
	 */
366
	abstract public function free ($query_result);
367
	/**
368
	 * Get columns list of table
369
	 *
370
	 * @param string       $table
371
	 * @param false|string $like
372
	 *
373
	 * @return string[]
374
	 */
375
	abstract public function columns ($table, $like = false);
376
	/**
377
	 * Get tables list
378
	 *
379
	 * @param false|string $like
380
	 *
381
	 * @return string[]
382
	 */
383
	abstract public function tables ($like = false);
384
	/**
385
	 * Safe
386
	 *
387
	 * Preparing string for using in SQL query
388
	 * SQL Injection Protection
389
	 *
390
	 * @param string|string[] $string
391
	 * @param bool            $single_quotes_around
392
	 *
393
	 * @return string|string[]
394
	 */
395 58
	public function s ($string, $single_quotes_around = true) {
396 58
		if (is_array($string)) {
397 6
			foreach ($string as &$s) {
398 6
				$s = $this->s_internal($s, $single_quotes_around);
399
			}
400 6
			return $string;
401
		}
402 58
		return $this->s_internal($string, $single_quotes_around);
403
	}
404
	/**
405
	 * Preparing string for using in SQL query
406
	 * SQL Injection Protection
407
	 *
408
	 * @param string $string
409
	 * @param bool   $single_quotes_around
410
	 *
411
	 * @return string
412
	 */
413
	abstract protected function s_internal ($string, $single_quotes_around);
414
	/**
415
	 * Get information about server
416
	 *
417
	 * @return string
418
	 */
419
	abstract public function server ();
420
	/**
421
	 * Connection state
422
	 *
423
	 * @return bool
424
	 */
425 66
	public function connected () {
426 66
		return $this->connected;
427
	}
428
	/**
429
	 * Database type (lowercase, for example <i>mysql</i>)
430
	 *
431
	 * @return string
432
	 */
433 2
	public function db_type () {
434 2
		return $this->db_type;
435
	}
436
	/**
437
	 * Database name
438
	 *
439
	 * @return string
440
	 */
441 4
	public function database () {
442 4
		return $this->database;
443
	}
444
	/**
445
	 * Number of made SQL queries
446
	 *
447
	 * @return int
448
	 */
449 4
	public function queries_count () {
450 4
		return $this->queries_count;
451
	}
452
	/**
453
	 * Total working time (including connection, queries execution and other delays)
454
	 *
455
	 * @return float
456
	 */
457 4
	public function time () {
458 4
		return $this->time;
459
	}
460
	/**
461
	 * Connecting time
462
	 *
463
	 * @return float
464
	 */
465 4
	public function connecting_time () {
466 4
		return $this->connecting_time;
467
	}
468
	/**
469
	 * Cloning restriction
470
	 *
471
	 * @final
472
	 */
473
	final protected function __clone () {
474
	}
475
	/**
476
	 * Disconnecting from DB
477
	 */
478
	abstract public function __destruct ();
479
}
480