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

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