fluent-boards / vendor / wpfluent / framework / src / WPFluent / Database / Query / WPDBConnection.php

WPDBConnection.php in FluentBoards – Project Management, Task Management, Goal Tracking, Kanban Board, and, Team Collaboration 1.40, at vendor/wpfluent/framework/src/WPFluent/Database/Query/WPDBConnection.php

852 lines 20.5 KB

Raw Download Zip

1	<?php
2
3	/*
4	* WPDB Connection
5	*/
6
7	namespace FluentBoards\Framework\Database\Query;
8
9	use Closure;
10	use Exception;
11	use DateTimeInterface;
12	use FluentBoards\Framework\Foundation\App;
13	use FluentBoards\Framework\Database\Schema;
14	use FluentBoards\Framework\Database\QueryException;
15	use FluentBoards\Framework\Database\ConnectionInterface;
16	use FluentBoards\Framework\Database\Events\QueryExecuted;
17	use FluentBoards\Framework\Database\Query\Expression;
18	use FluentBoards\Framework\Database\Query\Processors\MySqlProcessor;
19	use FluentBoards\Framework\Database\Query\Processors\SQLiteProcessor;
20	use FluentBoards\Framework\Database\Query\Builder as QueryBuilder;
21	use FluentBoards\Framework\Database\Query\Grammars\MySqlGrammar;
22	use FluentBoards\Framework\Database\Query\Grammars\SQLiteGrammar;
23
24	class WPDBConnection implements ConnectionInterface
25	{
26	/**
27	* $wpdb Global $wpdb instance
28	* @var Object
29	*/
30	protected $wpdb;
31
32	/**
33	* The name of the connected database.
34	*
35	* @var string
36	*/
37	protected $database;
38
39	/**
40	* The table prefix for the connection.
41	*
42	* @var string
43	*/
44	protected $tablePrefix = '';
45
46	/**
47	* The database connection configuration options.
48	*
49	* @var array
50	*/
51	protected $config = [];
52
53	/**
54	* The query grammar implementation.
55	*
56	* @var \FluentBoards\Framework\Database\Query\Grammars\Grammar
57	*/
58	protected $queryGrammar;
59
60	/**
61	* The query post processor implementation.
62	*
63	* @var \FluentBoards\Framework\Database\Query\Processors\Processor
64	*/
65	protected $postProcessor;
66
67	/**
68	* The number of total transactions.
69	*
70	* @var int
71	*/
72	protected $transactionCount = 0;
73
74	/**
75	* The event dispatcher.
76	*
77	* @var FluentBoards\Framework\Events
78	*/
79	protected $event = null;
80
81	/**
82	* Create a new database connection instance.
83	*
84	* @param $wpdb $pdo
85	* @param string $database
86	* @param string $tablePrefix
87	* @param array $config
88	* @return void
89	*/
90	public function __construct(
91	$pdo, $database = '', $tablePrefix = '', array $config = []
92	)
93	{
94	$this->setupWpdbInstance($pdo);
95
96	// First we will setup the default properties. We keep track of the DB
97	// name we are connected to since it is needed when some reflective
98	// type commands are run such as checking whether a table exists.
99	$this->database = $database;
100
101	$this->tablePrefix = $tablePrefix;
102
103	$this->config = $config;
104
105	// We need to initialize a query grammar and the query post processors
106	// which are both very important parts of the database abstractions
107	// so we initialize these to their default values while starting.
108	$this->useDefaultQueryGrammar();
109
110	$this->useDefaultPostProcessor();
111
112	$this->event = App::make('events');
113	}
114
115	/**
116	* Populate $wpdb instance & turn off db errors
117	*
118	* @param $wpdb Global $wpdb instance
119	* @return Null
120	*/
121	protected function setupWpdbInstance($wpdb)
122	{
123	$this->wpdb = $wpdb;
124
125	if (!str_starts_with(App::env(), 'prod')) {
126	$this->wpdb->show_errors(false);
127	}
128	}
129
130	/**
131	* Set the query grammar to the default implementation.
132	*
133	* @return void
134	*/
135	public function useDefaultQueryGrammar()
136	{
137	$this->queryGrammar = $this->getDefaultQueryGrammar();
138	}
139
140	/**
141	* Get the default query grammar instance.
142	*
143	* @return \FluentBoards\Framework\Database\Query\Grammars\Grammar
144	*/
145	protected function getDefaultQueryGrammar()
146	{
147	return $this->isSqlite() ? new SQLiteGrammar : new MySqlGrammar;
148	}
149
150	/**
151	* Set the query post processor to the default implementation.
152	*
153	* @return void
154	*/
155	public function useDefaultPostProcessor()
156	{
157	$this->postProcessor = $this->getDefaultPostProcessor();
158	}
159
160	/**
161	* Get the default post processor instance.
162	*
163	* @return \FluentBoards\Framework\Database\Query\Processors\Processor
164	*/
165	protected function getDefaultPostProcessor()
166	{
167	return $this->isSqlite() ? new SQLiteProcessor : new MySqlProcessor;
168	}
169
170	/**
171	* Begin a fluent query against a database table.
172	*
173	* @param \Closure\|\FluentBoards\Framework\Database\Query\Builder\|string $table
174	* @param string\|null $as
175	* @return \FluentBoards\Framework\Database\Query\Builder
176	*/
177	public function table($table, $as = null)
178	{
179	return $this->query()->from($table, $as);
180	}
181
182	/**
183	* Get a new query builder instance.
184	*
185	* @return \FluentBoards\Framework\Database\Query\Builder
186	*/
187	public function query()
188	{
189	return new QueryBuilder(
190	$this, $this->getQueryGrammar(), $this->getPostProcessor()
191	);
192	}
193
194	/**
195	* Run a select statement and return a single result.
196	*
197	* @param string $query
198	* @param array $bindings
199	* @param bool $useReadPdo
200	* @return mixed
201	*/
202	public function selectOne($query, $bindings = [], $useReadPdo = true)
203	{
204	return $this->run($query, $bindings, function ($query, $bindings) {
205	$query = $this->bindParams($query, $bindings);
206
207	$result = $this->wpdb->get_row($query);
208
209	if ($result === false \|\| $this->wpdb->last_error) {
210	throw new QueryException(
211	$query, $bindings, new Exception($this->wpdb->last_error)
212	);
213	}
214
215	return $result;
216	});
217	}
218
219	/**
220	* Run a select statement and return the first column of the first row.
221	*
222	* @param string $query
223	* @param array $bindings
224	* @param bool $useReadPdo
225	* @return mixed
226	*
227	* @throws \FluentBoards\Framework\Database\MultipleColumnsSelectedException
228	*/
229	public function scalar($query, $bindings = [], $useReadPdo = true)
230	{
231	$record = $this->selectOne($query, $bindings, $useReadPdo);
232
233	if (is_null($record)) {
234	return null;
235	}
236
237	$record = (array)$record;
238
239	if (count($record) > 1) {
240	throw new MultipleColumnsSelectedException(
241	'The query returned more than one column.'
242	);
243	}
244
245	return reset($record);
246	}
247
248	/**
249	* Run a select statement against the database.
250	*
251	* @param string $query
252	* @param array $bindings
253	* @param bool $useReadPdo
254	* @return array
255	*/
256	public function select($query, $bindings = [], $useReadPdo = true)
257	{
258	return $this->run($query, $bindings, function ($query, $bindings) {
259	$query = $this->bindParams($query, $bindings);
260
261	$result = $this->wpdb->get_results($query);
262
263	if ($result === false \|\| $this->wpdb->last_error) {
264	throw new QueryException(
265	$query, $bindings, new Exception($this->wpdb->last_error)
266	);
267	}
268
269	return $result;
270	});
271	}
272
273	/**
274	* A hacky way to emulate bind parameters into SQL query
275	*
276	* @param $query
277	* @param $bindings
278	*
279	* @return mixed
280	*/
281	protected function bindParams($query, $bindings, $update = false)
282	{
283	$query = str_replace('"', '`', $query);
284
285	$bindings = $this->prepareBindings($bindings);
286
287	if (!$bindings) {
288	return $query;
289	}
290
291	$bindings = array_map(function ($replace) {
292
293	if (is_string($replace)) {
294	$replace = "'" . esc_sql($replace) . "'";
295	} elseif ($replace === null) {
296	$replace = "null";
297	}
298
299	return $replace;
300
301	}, $bindings);
302
303	$query = str_replace(array('%', '?'), array('%%', '%s'), $query);
304
305	$query = vsprintf($query, $bindings);
306
307	return $query;
308	}
309
310	/**
311	* A hacky way to emulate bind parameters into SQL query for mysqli
312	* Only used to run a cursor query using the underlying mysqli instance.
313	*
314	* @param $query
315	* @param $bindings
316	*
317	* @return mixed
318	*/
319	protected function bindParamsForSqli($query, $bindings, $update = false)
320	{
321	$query = str_replace('"', '`', $query);
322
323	$bindings = $this->prepareBindings($bindings);
324
325	if (!$bindings) {
326	return $query;
327	}
328
329	$bindings = array_map(function ($replace) {
330
331	if (is_string($replace)) {
332	$replace = "'" . esc_sql($replace) . "'";
333	} elseif ($replace === null) {
334	$replace = "null";
335	}
336
337	return $replace;
338
339	}, $bindings);
340
341	$query = vsprintf($query, $bindings);
342
343	return $query;
344	}
345
346	/**
347	* Run a select statement against the database and returns a generator.
348	*
349	* @param string $query
350	* @param array $bindings
351	* @param bool $useReadPdo
352	* @return \Generator
353	*/
354	public function cursor($query, $bindings = [], $useReadPdo = true)
355	{
356	// When the underlying driver is not the mysqli.
357	// it's not a pure cursor just mimicked like one.
358	if (!$this->wpdb->dbh instanceof \mysqli) {
359	foreach ($this->select($query, $bindings) as $row) {
360	yield $row;
361	}
362	return;
363	}
364
365	// The underlying driver is the mysqli
366	$this->wpdb->flush();
367	$this->wpdb->insert_id = 0;
368	$this->wpdb->check_current_query = true;
369
370	if (!$this->wpdb->check_connection()) {
371	throw new QueryException(
372	$query, $bindings, new Exception(
373	$this->wpdb->last_error \|\| 'Error reconnecting to the database.'
374	)
375	);
376	}
377
378	if (defined('SAVEQUERIES') && SAVEQUERIES) {
379	$this->wpdb->timer_start();
380	}
381
382	$statement = $this->wpdb->dbh->prepare(
383	$this->bindParamsForSqli($query, $bindings)
384	);
385
386	$bindings && $statement->bind_param(
387	str_repeat('s', count($bindings)),
388	...$bindings
389	);
390
391	$start = microtime(true);
392
393	if ($statement->execute()) {
394
395	$result = $statement->get_result();
396
397	$this->wpdb->num_queries++;
398	$this->wpdb->last_query = $query;
399	$this->wpdb->num_rows = $result->num_rows;
400
401	if (defined('SAVEQUERIES') && SAVEQUERIES) {
402	$this->wpdb->log_query(
403	$query,
404	$this->wpdb->timer_stop(),
405	$this->wpdb->get_caller(),
406	$this->wpdb->time_start,
407	[]
408	);
409	}
410
411	$time = $this->getElapsedTime($this->wpdb->time_start);
412
413	$this->event->dispatch(
414	new QueryExecuted($query, $bindings, $time, $this)
415	);
416
417	$i = 0;
418	while ($row = $result->fetch_assoc()) {
419	$this->wpdb->last_result[$i] = $row;
420	$i++;
421	yield $row;
422	}
423
424	return;
425	}
426
427	if ($statement->error \|\| $statement->errno) {
428
429	$this->wpdb->last_error = $statement->error \|\| 'Mysqli Error No: ' . $statement->errno;
430
431	throw new QueryException(
432	$query, $bindings, new Exception(
433	$statement->error \|\| 'Mysqli Error No: ' . $statement->errno
434	)
435	);
436	}
437	}
438
439	/**
440	* Run an insert statement against the database.
441	*
442	* @param string $query
443	* @param array $bindings
444	* @return bool
445	*/
446	public function insert($query, $bindings = [])
447	{
448	return $this->statement($query, $bindings);
449	}
450
451	/**
452	* Run an update statement against the database.
453	*
454	* @param string $query
455	* @param array $bindings
456	* @return int
457	*/
458	public function update($query, $bindings = [])
459	{
460	return $this->affectingStatement($query, $bindings);
461	}
462
463	/**
464	* Run a delete statement against the database.
465	*
466	* @param string $query
467	* @param array $bindings
468	* @return int
469	*/
470	public function delete($query, $bindings = [])
471	{
472	return $this->affectingStatement($query, $bindings);
473	}
474
475	/**
476	* Execute an SQL statement and return the boolean result.
477	*
478	* @param string $query
479	* @param array $bindings
480	* @return bool
481	*/
482	public function statement($query, $bindings = [])
483	{
484	return $this->run($query, $bindings, function ($query, $bindings) {
485	$query = $this->bindParams($query, $bindings, true);
486
487	$result = $this->unprepared($query);
488
489	if ($result === false \|\| $this->wpdb->last_error) {
490	throw new QueryException(
491	$query, $bindings, new Exception($this->wpdb->last_error)
492	);
493	}
494
495	return $result;
496	});
497	}
498
499	/**
500	* Run an SQL statement and get the number of rows affected.
501	*
502	* @param string $query
503	* @param array $bindings
504	* @return int
505	*/
506	public function affectingStatement($query, $bindings = [])
507	{
508	return $this->run($query, $bindings, function ($query, $bindings) {
509	$query = $this->bindParams($query, $bindings, true);
510
511	$result = $this->wpdb->query($query);
512
513	if ($result === false \|\| $this->wpdb->last_error) {
514	throw new QueryException(
515	$query, $bindings, new Exception($this->wpdb->last_error)
516	);
517	}
518
519	return intval($result);
520	});
521	}
522
523	/**
524	* Run a raw, unprepared query against the PDO connection.
525	*
526	* @param string $query
527	* @return bool
528	*/
529	public function unprepared($query)
530	{
531	return $this->wpdb->query($query);
532	}
533
534	/**
535	* Execute the given callback in "dry run" mode.
536	*
537	* @param \Closure $callback
538	* @return array
539	*/
540	public function pretend(Closure $callback)
541	{
542	// ...
543	}
544
545	/**
546	* Prepare the query bindings for execution.
547	*
548	* @param array $bindings
549	* @return array
550	*/
551	public function prepareBindings(array $bindings)
552	{
553	$grammar = $this->getQueryGrammar();
554
555	foreach ($bindings as $key => $value) {
556	// We need to transform all instances of DateTimeInterface into
557	// the actual date string. Each query grammar maintains its
558	// own date string format so we'll just ask the grammar
559	// for the format to get from the date.
560	if ($value instanceof DateTimeInterface) {
561	$bindings[$key] = $value->format($grammar->getDateFormat());
562	} elseif (is_bool($value)) {
563	$bindings[$key] = (int)$value;
564	}
565	}
566
567	return $bindings;
568	}
569
570	public function run($query, $bindings, $callback)
571	{
572	$start = microtime(true);
573
574	try {
575	return $callback($query, $bindings);
576	} finally {
577	$time = $this->getElapsedTime($start);
578	$this->event->dispatch(
579	new QueryExecuted($query, $bindings, $time, $this)
580	);
581	}
582	}
583
584	/**
585	* Get a new raw query expression.
586	*
587	* @param mixed $value
588	* @return \FluentBoards\Framework\Database\Query\Expression
589	*/
590	public function raw($value)
591	{
592	return new Expression($value);
593	}
594
595	/**
596	* Get the query grammar used by the connection.
597	*
598	* @return \FluentBoards\Framework\Database\Query\Grammars\Grammar
599	*/
600	public function getQueryGrammar()
601	{
602	$this->queryGrammar->setTablePrefix($this->wpdb);
603
604	return $this->queryGrammar;
605	}
606
607	/**
608	* Set the query grammar used by the connection.
609	*
610	* @param \FluentBoards\Framework\Database\Query\Grammars\Grammar $grammar
611	* @return $this
612	*/
613	public function setQueryGrammar(Grammar $grammar)
614	{
615	$this->queryGrammar = $grammar;
616
617	return $this;
618	}
619
620	/**
621	* Get the query post processor used by the connection.
622	*
623	* @return \FluentBoards\Framework\Database\Query\Processors\Processor
624	*/
625	public function getPostProcessor()
626	{
627	return $this->postProcessor;
628	}
629
630	/**
631	* Set the query post processor used by the connection.
632	*
633	* @param \FluentBoards\Framework\Database\Query\Processors\Processor $processor
634	* @return $this
635	*/
636	public function setPostProcessor(Processor $processor)
637	{
638	$this->postProcessor = $processor;
639
640	return $this;
641	}
642
643	/**
644	* Return the last insert id
645	*
646	* @param string $args
647	*
648	* @return int
649	*/
650	public function lastInsertId($args)
651	{
652	return $this->wpdb->insert_id;
653	}
654
655	/**
656	* Return self as PDO, the Processor instance uses it.
657	*
658	* @return \FluentBoards\Framework\Database\Query\WPDBConnection
659	*/
660	public function getPdo()
661	{
662	return $this;
663	}
664
665	/**
666	* Returns the $wpdb object.
667	*
668	* @return Object $wpdb
669	*/
670	public function getWPDB()
671	{
672	return $this->wpdb;
673	}
674
675	/**
676	* Get the database connection name.
677	*
678	* @return string\|null
679	*/
680	public function getName()
681	{
682	return $this->isSqlite() ? 'sqlite' : 'mysql';
683	}
684
685	/**
686	* Get the name of the connected database.
687	*
688	* @return string
689	*/
690	public function getDatabaseName()
691	{
692	return $this->wpdb->dbname;
693	}
694
695	/**
696	* Get the server version for the connection.
697	*
698	* @return string
699	*/
700	public function getServerVersion(): string
701	{
702	return $this->getWPDB()->db_version();
703	}
704
705	/**
706	* Execute a Closure within a transaction.
707	*
708	* @param Closure $callback
709	* @param int $attempts
710	*
711	* @return mixed
712	*
713	* @throws Exception
714	*/
715	public function transaction(Closure $callback, $attempts = 1)
716	{
717	$this->beginTransaction();
718	try {
719	$data = $callback();
720	$this->commit();
721	return $data;
722	} catch (Exception $e) {
723	$this->rollBack();
724	throw $e;
725	}
726	}
727
728	/**
729	* Start a new database transaction.
730	*
731	* @return void
732	*/
733	public function beginTransaction()
734	{
735	$transaction = $this->unprepared("START TRANSACTION;");
736
737	if (false !== $transaction) {
738	$this->transactionCount++;
739	}
740	}
741
742	/**
743	* Commit the active database transaction.
744	*
745	* @return void
746	*/
747	public function commit()
748	{
749	if ($this->transactionCount < 1) {
750	return;
751	}
752
753	$transaction = $this->unprepared("COMMIT;");
754
755	if (false !== $transaction) {
756	$this->transactionCount--;
757	}
758	}
759
760	/**
761	* Rollback the active database transaction.
762	*
763	* @return void
764	*/
765	public function rollBack()
766	{
767	if ($this->transactionCount < 1) {
768	return;
769	}
770
771	$transaction = $this->unprepared("ROLLBACK;");
772
773	if ($transaction !== false) {
774	$this->transactionCount--;
775	}
776	}
777
778	/**
779	* Get the number of active transactions.
780	*
781	* @return int
782	*/
783	public function transactionLevel()
784	{
785	return $this->transactionCount;
786	}
787
788	/**
789	* Get the column listing for a given table.
790	*
791	* @param string $table
792	* @return array
793	*/
794	public function getColumnListing($table)
795	{
796	return Schema::getColumns($table);
797	}
798
799	/**
800	* Alias for getColumnListing.
801	*
802	* @param @param string $t
803	* @return array
804	*/
805	public function getColumns($t)
806	{
807	return $this->getColumnListing($t);
808	}
809
810	/**
811	* Determine if the connected database is a sqlite database.
812	*
813	* @return bool
814	*/
815	public function isSqlite()
816	{
817	return Schema::isSqlite();
818	}
819
820	/**
821	* Determine if the connected database is a mariadb database.
822	*
823	* @return bool
824	*/
825	public function isMaria()
826	{
827	return Schema::isMaria();
828	}
829
830	/**
831	* Register a database query listener with the connection.
832	*
833	* @param \Closure $callback
834	* @return void
835	*/
836	public function listen(Closure $callback)
837	{
838	$this->event->listen(QueryExecuted::class, $callback);
839	}
840
841	/**
842	* Get the elapsed time since a given starting point.
843	*
844	* @param int $start
845	* @return float
846	*/
847	protected function getElapsedTime($start)
848	{
849	return round((microtime(true) - $start) * 1000, 2);
850	}
851	}
852