vikbooking / libraries / adapter / database / database.php

database.php in VikBooking Hotel Booking Engine & PMS trunk, at libraries/adapter/database/database.php

937 lines 21.2 KB

Raw Download Zip

1	<?php
2	/**
3	* @package VikWP - Libraries
4	* @subpackage adapter.database
5	* @author E4J s.r.l.
6	* @copyright Copyright (C) 2023 E4J s.r.l. All Rights Reserved.
7	* @license http://www.gnu.org/licenses/gpl-2.0.html GNU/GPL
8	* @link https://vikwp.com
9	*/
10
11	// No direct access
12	defined('ABSPATH') or die('No script kiddies please!');
13
14	/**
15	* This adapter is required to wrap the Wordpress DB
16	* functions using the Joomla DB interface.
17	* This is helpful to improve the portability between Joomla and Wordpress.
18	*
19	* @since 10.0
20	*/
21	class JDatabase
22	{
23	/**
24	* The singleton instance of the database.
25	*
26	* @var JDatabase
27	*/
28	protected static $instance = null;
29
30	/**
31	* The global $wpdb instance.
32	*
33	* @var $wpdb
34	*/
35	protected $db;
36
37	/**
38	* The query set for the execution.
39	*
40	* @var string
41	*/
42	protected $q;
43
44	/**
45	* The last result fetched.
46	*
47	* @var mixed
48	*/
49	protected $result;
50
51	/**
52	* The query offset (start).
53	*
54	* @var integer
55	* @since 10.1.15
56	*/
57	protected $offset;
58
59	/**
60	* The query limit (max number of records).
61	*
62	* @var integer
63	* @since 10.1.15
64	*/
65	protected $limit;
66
67	/**
68	* The common database table prefix.
69	*
70	* @var string
71	* @since 10.1.37
72	*/
73	protected $tablePrefix;
74
75	/**
76	* Returns the global database adapter object, only creating it if it
77	* doesn't already exist.
78	*
79	* @param $wpdb $db The wordpress database handler.
80	*
81	* @return self A new instance of this class.
82	*/
83	public static function getInstance($db)
84	{
85	if (static::$instance === null)
86	{
87	static::$instance = new static($db);
88	}
89
90	return static::$instance;
91	}
92
93	/**
94	* Class constructor.
95	*
96	* @param $wpdb $db The wordpress db handler.
97	*/
98	protected function __construct($db)
99	{
100	$this->db = $db;
101
102	/**
103	* Hook used to suppress/enable database errors.
104	*
105	* @param boolean True to suppress the errors, false otherwise (false by default).
106	*
107	* @since 10.1.13
108	*/
109	$this->db->suppress_errors(apply_filters('vik_db_suppress_errors', false));
110
111	/**
112	* Hook used to show/hide database errors.
113	* In case errors are suppressed, this hook would result useless.
114	*
115	* @param boolean True to show the errors, false otherwise (true by default).
116	*
117	* @since 10.1.13
118	*/
119	$this->db->show_errors(apply_filters('vik_db_show_errors', true));
120	}
121
122	/**
123	* Magic method to proxy the functions in the $wpdb wrapped instance.
124	*
125	* @param string $method The called method.
126	* @param array $args The array of arguments passed to the method.
127	*
128	* @return mixed The value returned by the dispatched method.
129	* Null if the method doesn't exist.
130	*/
131	public function __call($name, $args)
132	{
133	if (method_exists($this->db, $name))
134	{
135	return call_user_func_array(array($this->db, $name), $args);
136	}
137
138	throw new RuntimeException('Call to undefined method ' . __CLASS__ . '::' . $name . '()', 500);
139	}
140
141	/**
142	* This function replaces a string placeholder with the real database prefix.
143	*
144	* @param string $sql The SQL statement to prepare.
145	* @param string $prefix The common table prefix.
146	*
147	* @return string The processed SQL statement.
148	*/
149	public function replacePrefix($sql, $prefix = '#__')
150	{
151	// generate a random placeholder
152	$placeholder = md5($prefix . uniqid());
153
154	// Replace all prefixes between the single/double quotes
155	// with the placeholder generated previously.
156	// This avoids to affect also strings that contains the actual prefix.
157	$sql = preg_replace_callback(
158	// "/('.($prefix).')\|(\".($prefix).\")/",
159	// get all the strings contained between single and double quotes,
160	// even if they don't contain the prefix
161	"/('.?')\|(\".?\")/",
162	function($match) use ($prefix, $placeholder)
163	{
164	// if contained, replace the prefix with the placeholder
165	return str_replace($prefix, $placeholder, $match[0]);
166	},
167	$sql
168	);
169
170	// get the prefix to use
171	$wp_prefix = $this->getPrefix();
172
173	// replace remaining prefixes (e.g. within backticks) with the real db prefix
174	$sql = str_replace($prefix, $wp_prefix, $sql);
175
176	// replace random placeholders with the original escaped prefix
177	$sql = str_replace($placeholder, $prefix, $sql);
178
179	return $sql;
180	}
181
182	/**
183	* Sets the SQL statement string for later execution.
184	*
185	* @param mixed $q The SQL statement to set as string.
186	* @param integer $offset The affected row offset to set.
187	* @param integer $limit The maximum affected rows to set.
188	*
189	* @return self This object to support chaining.
190	*/
191	public function setQuery($q, $offset = 0, $limit = 0)
192	{
193	$this->result = null;
194
195	/**
196	* If we are accessing #__users table, we need to route all
197	* the specified columns that belong to Joomla framework.
198	*
199	* @since 10.1.16
200	*/
201	$q = static::adjustJoomlaQuery2WP($q);
202
203	// save the query with the real db prefix
204	$this->q = $this->replacePrefix($q);
205
206	// register offset and limit (always override previous values)
207	$this->offset = abs((int) $offset);
208	$this->limit = abs((int) $limit);
209
210	return $this;
211	}
212
213	/**
214	* Gets the current query object or a new JDatabaseQuery object.
215	*
216	* @param boolean $new False to return the current query object, True to return a new JDatabaseQuery object.
217	*
218	* @return mixed The JDatabaseQuery object or a SQL plain string.
219	*/
220	public function getQuery($new = false)
221	{
222	if ($new)
223	{
224	JLoader::import('adapter.database.query');
225
226	return new JDatabaseQuery($this);
227	}
228
229	return $this->q;
230	}
231
232	/**
233	* Execute the SQL statement.
234	*
235	* @return boolean True on success, otherwise false.
236	*/
237	public function execute()
238	{
239	$sql = trim((string) $this->q);
240
241	// try to limit the query
242	if ($this->limit > 0 && $this->offset > 0)
243	{
244	$sql .= ' LIMIT ' . $this->offset . ', ' . $this->limit;
245	}
246	elseif ($this->limit > 0)
247	{
248	$sql .= ' LIMIT ' . $this->limit;
249	}
250
251	// if we are executing a SELECT query we need to
252	// load directly all the results fetched
253	if (preg_match("/^(SELECT\|SHOW)/i", $sql))
254	{
255	// result should contain an array
256	$this->result = $this->db->get_results($sql);
257
258	/**
259	* Flush result after executing the query to free disk space.
260	*
261	* @since 10.1.73
262	*/
263	$this->db->flush();
264	}
265	// otherwise we can launch a generic query
266	else
267	{
268	// result should contain an integer
269	$this->result = $this->db->query($sql);
270	}
271
272	return (bool) $this->result;
273	}
274
275	/**
276	* Get the number of returned rows for the previous executed SQL statement.
277	* This command is only valid for statements like SELECT or SHOW that return an actual result set.
278	*
279	* @return integer The number of returned rows.
280	*/
281	public function getNumRows()
282	{
283	if (is_array($this->result))
284	{
285	return count($this->result);
286	}
287
288	return 0;
289	}
290
291	/**
292	* Get the number of affected rows by the last INSERT, UPDATE, REPLACE or DELETE
293	* for the previous executed SQL statement.
294	*
295	* @return integer The number of affected rows.
296	*/
297	public function getAffectedRows()
298	{
299	if (is_numeric($this->result))
300	{
301	return $this->result;
302	}
303
304	return 0;
305	}
306
307	/**
308	* Method to get the auto-incremented value from the last INSERT statement.
309	*
310	* @return mixed The value of the auto-increment field from the last inserted row.
311	*/
312	public function insertid()
313	{
314	return $this->db->insert_id;
315	}
316
317	/**
318	* Method to get an array of the result set rows from the database query
319	* where each row is an object.
320	*
321	* @return array The object list.
322	*
323	* @uses execute()
324	*/
325	public function loadObjectList()
326	{
327	if (is_null($this->result))
328	{
329	$this->execute();
330	}
331
332	if (is_array($this->result))
333	{
334	/**
335	* Copy result on a local variable and flush the cached value.
336	*
337	* @since 10.1.73
338	*/
339	$result = $this->result;
340	$this->result = null;
341
342	return $result;
343	}
344
345	return array();
346	}
347
348	/**
349	* Method to get an array of the result set rows from the database query
350	* where each row is an associative array of ['field_name' => 'row_value'].
351	*
352	* @return array The associative arrays list.
353	*
354	* @uses loadObjectList()
355	*/
356	public function loadAssocList()
357	{
358	$app = array();
359
360	foreach ($this->loadObjectList() as $obj)
361	{
362	$app[] = (array) $obj;
363	}
364
365	return $app;
366	}
367
368	/**
369	* Method to get the first row of the result set from the database query as an object.
370	*
371	* @return mixed The return value or null if the query failed.
372	*
373	* @uses loadObjectList()
374	*/
375	public function loadObject()
376	{
377	$list = $this->loadObjectList();
378
379	if (count($list))
380	{
381	return $list[0];
382	}
383
384	return null;
385	}
386
387	/**
388	* Method to get the first row of the result set from the database query
389	* as an associative array of ['field_name' => 'row_value'].
390	*
391	* @return mixed The return value or null if the query failed.
392	*
393	* @uses loadObject()
394	*/
395	public function loadAssoc()
396	{
397	$obj = $this->loadObject();
398
399	if ($obj !== null)
400	{
401	return (array) $obj;
402	}
403
404	return null;
405	}
406
407	/**
408	* Method to get the first field of the first row of the result set from the database query.
409	*
410	* @return mixed The return value or null if the query failed.
411	*
412	* @uses loadAssoc()
413	*/
414	public function loadResult()
415	{
416	$arr = $this->loadAssoc();
417
418	if (is_array($arr))
419	{
420	$keys = array_keys($arr);
421
422	return $arr[$keys[0]];
423	}
424
425	return null;
426	}
427
428	/**
429	* Method to get the first row of the result set from the database query as an array.
430	*
431	* Columns are indexed numerically so the first column in the result set would be accessible via <var>$row[0]</var>, etc.
432	*
433	* @return mixed The return value or null if the query failed.
434	*
435	* @since 10.1.37
436	*/
437	public function loadRow()
438	{
439	$arr = $this->loadAssoc();
440
441	if (is_array($arr))
442	{
443	return array_values($arr);
444	}
445
446	return null;
447	}
448
449	/**
450	* Method to get an array of values from the <var>$offset</var> field in each row
451	* of the result set from the database query.
452	*
453	* @param integer $offset The row offset to use to build the result array.
454	*
455	* @return array A list containing the columns.
456	*
457	* @uses loadAssocList()
458	*/
459	public function loadColumn($offset = 0)
460	{
461	$column = array();
462
463	foreach ($this->loadAssocList() as $arr)
464	{
465	$keys = array_keys($arr);
466
467	$column[] = $arr[$keys[$offset]];
468	}
469
470	return $column;
471	}
472
473	/**
474	* Quotes and optionally escapes a string to database requirements for use in database queries.
475	*
476	* @param mixed $text A string or an array of strings to quote.
477	* @param boolean $escape True (default) to escape the string, false to leave it unchanged.
478	*
479	* @return mixed The quoted input.
480	*/
481	public function quote($text, $escape = true)
482	{
483	if (is_array($text))
484	{
485	return esc_sql($text);
486	}
487
488	return '\'' . ($escape ? esc_sql((string) $text) : $text) . '\'';
489	}
490
491	/**
492	* Shorten alias for quote() method.
493	*
494	* @see quote()
495	*/
496	public function q($text, $escape = true)
497	{
498	return $this->quote($text, $escape);
499	}
500
501	/**
502	* Wraps an SQL statement identifier name such as column, table or database names
503	* in quotes to prevent injection risks and reserved word conflicts.
504	*
505	* @param mixed $name The identifier name to wrap in quotes, or an array of identifier
506	* names to wrap in quotes. Each type supports dot-notation name.
507	* @param mixed $as The AS query part associated to $name. It can be string or array.
508	*
509	* @return string The quote wrapped name.
510	*
511	* @uses _quoteName()
512	*/
513	public function quoteName($name, $as = null)
514	{
515	// define an empty array
516	$arr = array();
517
518	// fill $arr recursively with quoted names
519	$this->_quoteName($arr, $name, $as);
520
521	// concat the list using a comma separator
522	return implode(', ', $arr);
523	}
524
525	/**
526	* Shorten alias for quoteName() method.
527	*
528	* @see quoteName()
529	*/
530	public function qn($str, $as = null)
531	{
532	return $this->quoteName($str, $as);
533	}
534
535	/**
536	* Recursive method to quote a list of names.
537	*
538	* @param array &$arr A list containing all the quotes names.
539	* @param mixed $name The identifier name to wrap in quotes, or an array of identifier
540	* names to wrap in quotes. Each type supports dot-notation name.
541	* @param mixed $as The AS query part associated to $name. It can be string or array.
542	*
543	* @return void
544	*/
545	protected function _quoteName(array &$arr, $name, $as = null)
546	{
547	// if the name is (still) an array, quote it recursively
548	// until we have a scalar value
549	if (is_array($name))
550	{
551	// iterate the names contained in the list
552	foreach ($name as $i => $inner)
553	{
554	// obtain the AS only if it exists
555	$_as = !is_null($as) && is_array($as) && isset($as[$i]) ? $as[$i] : null;
556
557	$this->_quoteName($arr, $inner, $_as);
558	}
559	}
560	// quote the scalar value
561	else
562	{
563	// explode the name for dot-notation
564	$exp = explode('.', $name);
565
566	$name = "`{$exp[0]}`";
567	if (count($exp) > 1)
568	{
569	$name .= ".`{$exp[1]}`";
570	}
571
572	if (!is_null($as))
573	{
574	$name .= " AS `$as`";
575	}
576
577	$arr[] = $name;
578	}
579	}
580
581	/**
582	* Inserts a row into a table based on an object's properties.
583	*
584	* @param string $table The name of the database table to insert into.
585	* @param object &$object A reference to an object whose public properties match the table fields.
586	* @param string $key The name of the primary key. If provided the object property is updated.
587	*
588	* @return boolean True on success.
589	*/
590	public function insertObject($table, &$object, $key = null)
591	{
592	$data = array();
593
594	foreach (get_object_vars($object) as $k => $v)
595	{
596	// exclude primary key, not null values, arrays, objects and
597	// internal properties (prefixed with an underscore)
598	if ($k != $key && $v !== null && is_scalar($v) && $k[0] != '_')
599	{
600	$data[$k] = $v;
601	}
602	}
603
604	// insert the new record
605	if (!$this->db->insert($this->replacePrefix($table), $data))
606	{
607	return false;
608	}
609
610	// update the primary key if it exists
611	$id = $this->db->insert_id;
612
613	// store affected row
614	$this->result = $id;
615
616	if ($key && $id && is_string($key))
617	{
618	$object->{$key} = $id;
619	}
620
621	return true;
622	}
623
624	/**
625	* Updates a row in a table based on an object's properties.
626	*
627	* @param string $table The name of the database table to update.
628	* @param object &$object A reference to an object whose public properties match the table fields.
629	* @param mixed $key The name (or a list of names) of the primary key.
630	* @param boolean $nulls True to update null fields or false to ignore them.
631	*
632	* @return boolean True on success.
633	*/
634	public function updateObject($table, &$object, $key, $nulls = false)
635	{
636	$set = array();
637	$where = array();
638
639	if (is_string($key))
640	{
641	$key = array($key);
642	}
643
644	if (is_object($key))
645	{
646	$key = (array) $key;
647	}
648
649	foreach (get_object_vars($object) as $k => $v)
650	{
651	// exclude arrays, objects and internal properties (prefixed with an underscore)
652	if (is_array($v) \|\| is_object($v) \|\| $k[0] == '_')
653	{
654	continue;
655	}
656
657	// set the primary key to the WHERE clause instead of a field to update
658	if (in_array($k, $key))
659	{
660	$where[$k] = $v;
661	continue;
662	}
663
664	// update field only if not null or if nulls values are allowed
665	if ($v !== null \|\| $nulls)
666	{
667	$set[$k] = $v;
668	}
669	}
670
671	// we don't have any fields to update
672	if (empty($set))
673	{
674	return true;
675	}
676
677	// update the specified record
678	$affected = $this->db->update($this->replacePrefix($table), $set, $where);
679
680	// store affected rows
681	$this->result = (int) $affected;
682
683	return $affected !== false;
684	}
685
686	/**
687	* Returns the error faced (if any) during the last query execution.
688	*
689	* @return string The error message.
690	*
691	* @since 10.1.58
692	*/
693	public function getLastError()
694	{
695	return $this->db->last_error;
696	}
697
698	/**
699	* Returns a PHP date() function compliant date format for the database driver.
700	*
701	* @return string The format string.
702	*/
703	public function getDateFormat()
704	{
705	return 'Y-m-d H:i:s';
706	}
707
708	/**
709	* Returns the null date in the format of the database driver.
710	*
711	* @return string The null date string.
712	*
713	* @since 10.1.5
714	*/
715	public function getNullDate()
716	{
717	return '0000-00-00 00:00:00';
718	}
719
720	/**
721	* Get the common table prefix for the database driver.
722	*
723	* @return string The common database table prefix.
724	*
725	* @since 10.1.37
726	*/
727	public function getPrefix()
728	{
729	if (is_null($this->tablePrefix))
730	{
731	/**
732	* Hook used to filter the default WP database prefix before it is used.
733	*
734	* @param string The database prefix to use for queries.
735	*
736	* @since 10.1.1
737	*/
738	$this->tablePrefix = apply_filters('vik_get_db_prefix', $this->db->prefix);
739	}
740
741	return $this->tablePrefix;
742	}
743
744	/**
745	* Retrieves field information about a given table.
746	*
747	* @param string $table The name of the database table.
748	* @param boolean $typeOnly True to only return field types.
749	*
750	* @return array An array of fields for the database table.
751	*
752	* @since 10.1.19
753	*/
754	public function getTableColumns($table, $typeOnly = true)
755	{
756	/**
757	* Do not escape the table name to support SQLite too.
758	*
759	* @since 10.1.53
760	*/
761	$q = "SHOW FULL COLUMNS FROM " . $table;
762
763	// set the query to get the table fields statement
764	$this->setQuery($q);
765	$this->execute();
766
767	$fields = $this->loadObjectList();
768
769	$result = [];
770
771	// if we only want the type as the value add just that to the list.
772	if ($typeOnly)
773	{
774	foreach ($fields as $field)
775	{
776	$result[$field->Field] = preg_replace('/[(0-9)]/', '', $field->Type);
777	}
778	}
779	// if we want the whole field data object add that to the list.
780	else
781	{
782	foreach ($fields as $field)
783	{
784	/**
785	* With SQLite the Extra column might not be included.
786	* Simulate the same result by checking whether the column
787	* is equal to `id` and force the "auto_increment" rule.
788	*
789	* @since 10.1.53
790	*/
791	if (!isset($field->Extra))
792	{
793	$field->Extra = $field->Field === 'id' ? 'auto_increment' : '';
794	}
795
796	$result[$field->Field] = $field;
797	}
798	}
799
800	return $result;
801	}
802
803	/**
804	* Method to get an array containing all the database tables.
805	*
806	* @return array An array of all the tables in the database.
807	*
808	* @since 10.1.37
809	*/
810	public function getTableList()
811	{
812	// set the query to get the tables statement
813	$this->setQuery('SHOW TABLES');
814	$this->execute();
815
816	return $this->loadColumn();
817	}
818
819	/**
820	* Shows the table CREATE statement that creates the given tables.
821	*
822	* @param mixed $tables A table name or a list of table names.
823	*
824	* @return array A list of the create SQL for the tables.
825	*
826	* @since 10.1.37
827	*/
828	public function getTableCreate($tables)
829	{
830	$result = [];
831
832	// sanitize input to an array and iterate over the list
833	$tables = (array) $tables;
834
835	foreach ($tables as $table)
836	{
837	// set the query to get the table CREATE statement
838	$this->setQuery('SHOW CREATE TABLE ' . $this->qn($table));
839	$this->execute();
840
841	$row = $this->loadRow();
842
843	// populate the result array based on the create statements
844	$result[$table] = $row[1];
845	}
846
847	return $result;
848	}
849
850	/**
851	* Adjusts a query built for Joomla to WordPress needs.
852	*
853	* @param mixed $query The SQL query string or a query builder.
854	*
855	* @return void
856	*
857	* @since 10.1.16
858	*/
859	public static function adjustJoomlaQuery2WP($query)
860	{
861	// always cast to string
862	$query = (string) $query;
863
864	// check if the query contains `#__users` and an optional alias
865	if (preg_match("/`#__users`(?:\s+AS\s+`([a-z0-9_]+)`)?/i", $query, $match))
866	{
867	$userTable = !empty($match[1]) ? $match[1] : null;
868
869	// check whether an alias should be used
870	$tableAlias = $userTable ? "`{$userTable}`\." : "";
871	$lookup = array();
872
873	// replace all the columns that match the regex
874	$query = preg_replace_callback("/{$tableAlias}`([a-z0-9_]+)`(?:\sAS\s`([a-z0-9_]+)`)?/i", function($match) use ($userTable, $tableAlias, $query, &$lookup)
875	{
876	// get current column and alias
877	$col = $match[1];
878	$alias = isset($match[2]) ? $match[2] : $match[1];
879
880	switch (strtolower($col))
881	{
882	case 'name':
883	$col = 'display_name';
884	break;
885
886	case 'username':
887	$col = 'user_login';
888	break;
889
890	case 'email':
891	$col = 'user_email';
892	break;
893	}
894
895	// rebuild column without using ALIAS
896	$str = ($userTable ? "`{$userTable}`." : "") . "`{$col}`";
897
898	$sign = ($tableAlias ? $tableAlias . '.' : '') . $col;
899
900	// check if lookup doesn't contain this column and the query is a select
901	if (!isset($lookup[$sign]) && preg_match("/^\s*SELECT/i", $query))
902	{
903	// obtain position of current column and position of FROM statement and
904	// make sure the chunk position is displayed before FROM
905	if (preg_match("/{$match[0]}/i", $query, $token, PREG_OFFSET_CAPTURE)
906	&& preg_match("/\sFROM\s/i", $query, $from, PREG_OFFSET_CAPTURE)
907	&& $token[0][1] < $from[0][1])
908	{
909	// add alias for column within SELECT
910	$str .= " AS `{$alias}`";
911	}
912	}
913
914	// mark column as registered within the lookup in order
915	// to avoid adding alias again outside the SELECT
916	$lookup[$sign] = 1;
917
918	return $str;
919	}, $query);
920
921	/**
922	* In case of multi-site, always use the base prefix when
923	* querying the users database table.
924	*
925	* @since 10.1.31
926	*/
927	if (is_multisite())
928	{
929	global $wpdb;
930	$query = preg_replace("/`#__users`/", "`{$wpdb->base_prefix}users`", $query);
931	}
932	}
933
934	return $query;
935	}
936	}
937