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 2.1.0, at vendor/wpfluent/framework/src/WPFluent/Database/Query/WPDBConnection.php

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