vikbooking / libraries / adapter / database / query.php

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

914 lines 19.5 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	JLoader::import('adapter.database.query.element');
15
16	/**
17	* Query Building Class.
18	*
19	* @since 10.0
20	*/
21	class JDatabaseQuery
22	{
23	/**
24	* The database driver.
25	*
26	* @var JDatabase
27	*/
28	protected $dbo = null;
29
30	/**
31	* The query type.
32	*
33	* @var string
34	*/
35	protected $type = '';
36
37	/**
38	* The select element.
39	*
40	* @var JDatabaseQueryElement
41	*/
42	protected $select = null;
43
44	/**
45	* The delete element.
46	*
47	* @var JDatabaseQueryElement
48	*/
49	protected $delete = null;
50
51	/**
52	* The update element.
53	*
54	* @var JDatabaseQueryElement
55	*/
56	protected $update = null;
57
58	/**
59	* The insert element.
60	*
61	* @var JDatabaseQueryElement
62	*/
63	protected $insert = null;
64
65	/**
66	* The from element.
67	*
68	* @var JDatabaseQueryElement
69	*/
70	protected $from = null;
71
72	/**
73	* The join element.
74	*
75	* @var JDatabaseQueryElement
76	*/
77	protected $join = null;
78
79	/**
80	* The set element.
81	*
82	* @var JDatabaseQueryElement
83	*/
84	protected $set = null;
85
86	/**
87	* The where element.
88	*
89	* @var JDatabaseQueryElement
90	*/
91	protected $where = null;
92
93	/**
94	* The group by element.
95	*
96	* @var JDatabaseQueryElement
97	*/
98	protected $group = null;
99
100	/**
101	* The having element.
102	*
103	* @var JDatabaseQueryElement
104	*/
105	protected $having = null;
106
107	/**
108	* The column list for an INSERT statement.
109	*
110	* @var JDatabaseQueryElement
111	*/
112	protected $columns = null;
113
114	/**
115	* The values list for an INSERT statement.
116	*
117	* @var JDatabaseQueryElement
118	*/
119	protected $values = null;
120
121	/**
122	* The order element.
123	*
124	* @var JDatabaseQueryElement
125	*/
126	protected $order = null;
127
128	/**
129	* Details of window function.
130	*
131	* @var array
132	* @since 10.1.30
133	*/
134	protected $selectRowNumber = null;
135
136	/**
137	* Class constructor.
138	*
139	* @param JDatabase $db The database driver.
140	*/
141	public function __construct(?JDatabase $dbo = null)
142	{
143	if (is_null($dbo))
144	{
145	$dbo = JFactory::getDbo();
146	}
147
148	$this->dbo = $dbo;
149	}
150
151	/**
152	* Magic function to convert the query to a string.
153	*
154	* @return string The completed query.
155	*/
156	public function __toString()
157	{
158	$query = '';
159
160	switch ($this->type)
161	{
162	case 'select':
163	$query .= (string) $this->select;
164	$query .= (string) $this->from;
165
166	if ($this->join)
167	{
168	// special case for joins
169	foreach ($this->join as $join)
170	{
171	$query .= (string) $join;
172	}
173	}
174
175	if ($this->where)
176	{
177	$query .= (string) $this->where;
178	}
179
180	if ($this->selectRowNumber === null)
181	{
182	if ($this->group)
183	{
184	$query .= (string) $this->group;
185	}
186
187	if ($this->having)
188	{
189	$query .= (string) $this->having;
190	}
191	}
192
193	if ($this->order)
194	{
195	$query .= (string) $this->order;
196	}
197
198	break;
199
200	case 'delete':
201	$query .= (string) $this->delete;
202	$query .= (string) $this->from;
203
204	if ($this->join)
205	{
206	// special case for joins
207	foreach ($this->join as $join)
208	{
209	$query .= (string) $join;
210	}
211	}
212
213	if ($this->where)
214	{
215	$query .= (string) $this->where;
216	}
217
218	if ($this->order)
219	{
220	$query .= (string) $this->order;
221	}
222
223	break;
224
225	case 'update':
226	$query .= (string) $this->update;
227
228	if ($this->join)
229	{
230	// special case for joins
231	foreach ($this->join as $join)
232	{
233	$query .= (string) $join;
234	}
235	}
236
237	$query .= (string) $this->set;
238
239	if ($this->where)
240	{
241	$query .= (string) $this->where;
242	}
243
244	if ($this->order)
245	{
246	$query .= (string) $this->order;
247	}
248
249	break;
250
251	case 'insert':
252	$query .= (string) $this->insert;
253
254	if ($this->values)
255	{
256	if ($this->columns)
257	{
258	$query .= (string) $this->columns;
259	}
260
261	$elements = $this->values->getElements();
262
263	if (!($elements[0] instanceof $this))
264	{
265	$query .= ' VALUES ';
266	}
267
268	$query .= (string) $this->values;
269	}
270
271	break;
272	}
273
274	/**
275	* If we are accessing #__users table, we need to route all
276	* the specified columns that belong to Joomla framework.
277	*
278	* By adjusting the query here we can support inner queries
279	* that have been invoked after FROM statment.
280	*
281	* @since 10.1.16
282	*/
283	$query = JDatabase::adjustJoomlaQuery2WP($query);
284
285	return $query;
286	}
287
288	/**
289	* Magic function to get protected variable value.
290	*
291	* @param string $name The name of the variable.
292	*
293	* @return mixed The property value if set, otherwise null.
294	*/
295	public function __get($name)
296	{
297	return isset($this->$name) ? $this->$name : null;
298	}
299
300	/**
301	* Clear data from the query or a specific clause of the query.
302	*
303	* @param string $clause Optionally, the name of the clause to clear, or nothing to clear the whole query.
304	*
305	* @return self This object to support chaining.
306	*/
307	public function clear($clause = null)
308	{
309	if (is_null($clause))
310	{
311	/**
312	* Clear all only in case the clause is not specified,
313	* because the query would be cleared by passing an
314	* unsupported clause (e.g. 'limit').
315	*
316	* @since 10.1.23
317	*/
318	$this->type = null;
319	$this->select = null;
320	$this->delete = null;
321	$this->update = null;
322	$this->insert = null;
323	$this->from = null;
324	$this->join = null;
325	$this->set = null;
326	$this->where = null;
327	$this->group = null;
328	$this->having = null;
329	$this->order = null;
330	$this->columns = null;
331	$this->values = null;
332
333	$this->selectRowNumber = null;
334	}
335	else
336	{
337	switch ($clause)
338	{
339	case 'select':
340	$this->select = null;
341	$this->type = null;
342
343	$this->selectRowNumber = null;
344	break;
345
346	case 'delete':
347	$this->delete = null;
348	$this->type = null;
349	break;
350
351	case 'update':
352	$this->update = null;
353	$this->type = null;
354	break;
355
356	case 'insert':
357	$this->insert = null;
358	$this->type = null;
359	break;
360
361	case 'from':
362	$this->from = null;
363	break;
364
365	case 'join':
366	$this->join = null;
367	break;
368
369	case 'set':
370	$this->set = null;
371	break;
372
373	case 'where':
374	$this->where = null;
375	break;
376
377	case 'group':
378	$this->group = null;
379	break;
380
381	case 'having':
382	$this->having = null;
383	break;
384
385	case 'order':
386	$this->order = null;
387	break;
388
389	case 'columns':
390	$this->columns = null;
391	break;
392
393	case 'values':
394	$this->values = null;
395	break;
396	}
397	}
398
399	return $this;
400	}
401
402	/**
403	* Adds a column, or array of column names that would be used for an INSERT INTO statement.
404	*
405	* @param mixed $columns A column name, or array of column names.
406	*
407	* @return self This object to support chaining.
408	*/
409	public function columns($columns)
410	{
411	if (is_null($this->columns))
412	{
413	// don't name the element to wrap the columns between the parentheses
414	$this->columns = new JDatabaseQueryElement('()', $columns);
415	}
416	else
417	{
418	$this->columns->append($columns);
419	}
420
421	return $this;
422	}
423
424	/**
425	* Add a table name to the DELETE clause of the query.
426	*
427	* Note that you must not mix insert, update, delete and select method calls when building a query.
428	*
429	* Usage:
430	* $query->delete('#__a')->where('id = 1');
431	*
432	* @param string $table The name of the table to delete from.
433	*
434	* @return self This object to support chaining.
435	*/
436	public function delete($table = null)
437	{
438	$this->type = 'delete';
439	$this->delete = new JDatabaseQueryElement('DELETE', null);
440
441	if (!empty($table))
442	{
443	$this->from($table);
444	}
445
446	return $this;
447	}
448
449	/**
450	* Add a table to the FROM clause of the query.
451	*
452	* Note that while an array of tables can be provided, it is recommended you use explicit joins.
453	*
454	* Usage:
455	* $query->select('*')->from('#__a');
456	*
457	* @param mixed $tables A string or array of table names.
458	* This can be a JDatabaseQuery object (or a child of it) when used
459	* as a subquery in FROM clause along with a value for $subQueryAlias.
460	* @param string $subQueryAlias Alias used when $tables is a JDatabaseQuery.
461	*
462	* @return self This object to support chaining.
463	*/
464	public function from($tables, $subQueryAlias = null)
465	{
466	if ($tables instanceof $this)
467	{
468	if (is_null($subQueryAlias))
469	{
470	throw new RuntimeException('Missing alias for sub-query.', 500);
471	}
472
473	$tables = '( ' . (string) $tables . ' ) AS ' . $this->dbo->qn($subQueryAlias);
474	}
475
476	if (is_null($this->from))
477	{
478	$this->from = new JDatabaseQueryElement('FROM', $tables);
479	}
480	else
481	{
482	$this->from->append($tables);
483	}
484
485	return $this;
486	}
487
488	/**
489	* Add a grouping column to the GROUP clause of the query.
490	*
491	* Usage:
492	* $query->group('id');
493	*
494	* @param mixed $columns A string or array of ordering columns.
495	*
496	* @return self This object to support chaining.
497	*/
498	public function group($columns)
499	{
500	if (is_null($this->group))
501	{
502	$this->group = new JDatabaseQueryElement('GROUP BY', $columns);
503	}
504	else
505	{
506	$this->group->append($columns);
507	}
508
509	return $this;
510	}
511
512	/**
513	* A conditions to the HAVING clause of the query.
514	*
515	* Usage:
516	* $query->group('id')->having('COUNT(id) > 5');
517	*
518	* @param mixed $conditions A string or array of columns.
519	* @param string $glue The glue by which to join the conditions. Defaults to AND.
520	*
521	* @return self This object to support chaining.
522	*/
523	public function having($conditions, $glue = 'AND')
524	{
525	if (is_null($this->having))
526	{
527	$glue = strtoupper($glue);
528	$this->having = new JDatabaseQueryElement('HAVING', $conditions, " $glue ");
529	}
530	else
531	{
532	$this->having->append($conditions);
533	}
534
535	return $this;
536	}
537
538	/**
539	* Add an INNER JOIN clause to the query.
540	*
541	* Usage:
542	* $query->innerJoin('b ON b.id = a.id')->innerJoin('c ON c.id = b.id');
543	*
544	* @param string $condition The join condition.
545	*
546	* @return self This object to support chaining.
547	*
548	* @uses join()
549	*/
550	public function innerJoin($condition)
551	{
552	$this->join('INNER', $condition);
553
554	return $this;
555	}
556
557	/**
558	* Add a table name to the INSERT clause of the query.
559	*
560	* Note that you must not mix insert, update, delete and select method calls when building a query.
561	*
562	* Usage:
563	* $query->insert('#__a')->columns('id, title')->values('1,2')->values('3,4');
564	* $query->insert('#__a')->columns('id, title')->values(array('1,2', '3,4'));
565	*
566	* @param mixed $table The name of the table to insert data into.
567	*
568	* @return self This object to support chaining.
569	*/
570	public function insert($table)
571	{
572	$this->type = 'insert';
573	$this->insert = new JDatabaseQueryElement('INSERT INTO', $table);
574
575	return $this;
576	}
577
578	/**
579	* Add a JOIN clause to the query.
580	*
581	* Usage:
582	* $query->join('INNER', 'b ON b.id = a.id);
583	*
584	* @param string $type The type of join. This string is prepended to the JOIN keyword.
585	* @param string $conditions A string or array of conditions.
586	*
587	* @return self This object to support chaining.
588	*/
589	public function join($type, $conditions)
590	{
591	if (is_null($this->join))
592	{
593	$this->join = array();
594	}
595
596	$this->join[] = new JDatabaseQueryElement(strtoupper($type) . ' JOIN', $conditions);
597
598	return $this;
599	}
600
601	/**
602	* Add a LEFT JOIN clause to the query.
603	*
604	* Usage:
605	* $query->leftJoin('b ON b.id = a.id')->leftJoin('c ON c.id = b.id');
606	*
607	* @param string $condition The join condition.
608	*
609	* @return self This object to support chaining.
610	*
611	* @uses join()
612	*/
613	public function leftJoin($condition)
614	{
615	$this->join('LEFT', $condition);
616
617	return $this;
618	}
619
620	/**
621	* Add an ordering column to the ORDER clause of the query.
622	*
623	* Usage:
624	* $query->order('foo')->order('bar');
625	* $query->order(array('foo','bar'));
626	*
627	* @param mixed $columns A string or array of ordering columns.
628	*
629	* @return self This object to support chaining.
630	*/
631	public function order($columns)
632	{
633	if (is_null($this->order))
634	{
635	$this->order = new JDatabaseQueryElement('ORDER BY', $columns);
636	}
637	else
638	{
639	$this->order->append($columns);
640	}
641
642	return $this;
643	}
644
645	/**
646	* Add an OUTER JOIN clause to the query.
647	*
648	* Usage:
649	* $query->outerJoin('b ON b.id = a.id')->outerJoin('c ON c.id = b.id');
650	*
651	* @param string $condition The join condition.
652	*
653	* @return self This object to support chaining.
654	*
655	* @uses join()
656	*/
657	public function outerJoin($condition)
658	{
659	$this->join('OUTER', $condition);
660
661	return $this;
662	}
663
664	/**
665	* Add a RIGHT JOIN clause to the query.
666	*
667	* Usage:
668	* $query->rightJoin('b ON b.id = a.id')->rightJoin('c ON c.id = b.id');
669	*
670	* @param string $condition The join condition.
671	*
672	* @return self This object to support chaining.
673	*
674	* @uses join()
675	*/
676	public function rightJoin($condition)
677	{
678	$this->join('RIGHT', $condition);
679
680	return $this;
681	}
682
683	/**
684	* Add a single column, or array of columns to the SELECT clause of the query.
685	*
686	* Note that you must not mix insert, update, delete and select method calls when building a query.
687	* The select method can, however, be called multiple times in the same query.
688	*
689	* Usage:
690	* $query->select('a.*')->select('b.id');
691	* $query->select(array('a.*', 'b.id'));
692	*
693	* @param mixed $columns A string or an array of field names.
694	*
695	* @return self This object to support chaining.
696	*/
697	public function select($columns)
698	{
699	$this->type = 'select';
700
701	if (is_null($this->select))
702	{
703	$this->select = new JDatabaseQueryElement('SELECT', $columns);
704	}
705	else
706	{
707	$this->select->append($columns);
708	}
709
710	return $this;
711	}
712
713	/**
714	* Add a single condition string, or an array of strings to the SET clause of the query.
715	*
716	* Usage:
717	* $query->set('a = 1')->set('b = 2');
718	* $query->set(array('a = 1', 'b = 2');
719	*
720	* @param mixed $conditions A string or array of string conditions.
721	* @param string $glue The glue by which to join the condition strings. Defaults to ,.
722	* Note that the glue is set on first use and cannot be changed.
723	*
724	* @return self This object to support chaining.
725	*/
726	public function set($conditions, $glue = ',')
727	{
728	if (is_null($this->set))
729	{
730	$glue = strtoupper($glue);
731	$this->set = new JDatabaseQueryElement('SET', $conditions, "\n\t$glue ");
732	}
733	else
734	{
735	$this->set->append($conditions);
736	}
737
738	return $this;
739	}
740
741	/**
742	* Add a table name to the UPDATE clause of the query.
743	*
744	* Note that you must not mix insert, update, delete and select method calls when building a query.
745	*
746	* Usage:
747	* $query->update('#__foo')->set(...);
748	*
749	* @param string $table A table to update.
750	*
751	* @return self This object to support chaining.
752	*/
753	public function update($table)
754	{
755	$this->type = 'update';
756	$this->update = new JDatabaseQueryElement('UPDATE', $table);
757
758	return $this;
759	}
760
761	/**
762	* Adds a tuple, or array of tuples that would be used as values for an INSERT INTO statement.
763	*
764	* Usage:
765	* $query->values('1,2,3')->values('4,5,6');
766	* $query->values(array('1,2,3', '4,5,6'));
767	*
768	* @param string $values A single tuple, or array of tuples.
769	*
770	* @return self This object to support chaining.
771	*/
772	public function values($values)
773	{
774	if (is_null($this->values))
775	{
776	// don't name the element to wrap the values between the parentheses
777	$this->values = new JDatabaseQueryElement('()', $values, '),(');
778	}
779	else
780	{
781	$this->values->append($values);
782	}
783
784	return $this;
785	}
786
787	/**
788	* Add a single condition, or an array of conditions to the WHERE clause of the query.
789	*
790	* Usage:
791	* $query->where('a = 1')->where('b = 2');
792	* $query->where(array('a = 1', 'b = 2'));
793	*
794	* @param mixed $conditions A string or array of where conditions.
795	* @param string $glue The glue by which to join the conditions. Defaults to AND.
796	* Note that the glue is set on first use and cannot be changed.
797	*
798	* @return self This object to support chaining.
799	*/
800	public function where($conditions, $glue = 'AND')
801	{
802	if (is_null($this->where))
803	{
804	$glue = strtoupper($glue);
805	$this->where = new JDatabaseQueryElement('WHERE', $conditions, " $glue ");
806	}
807	else
808	{
809	$this->where->append($conditions);
810	}
811
812	return $this;
813	}
814
815	/**
816	* Extend the WHERE clause with a single condition or an array of conditions, with a potentially
817	* different logical operator from the one in the current WHERE clause.
818	*
819	* Usage:
820	* $query->where(array('a = 1', 'b = 2'))->extendWhere('XOR', array('c = 3', 'd = 4'));
821	* will produce: WHERE ((a = 1 AND b = 2) XOR (c = 3 AND d = 4)
822	*
823	* @param string $outerGlue The glue by which to join the conditions to the current WHERE conditions.
824	* @param mixed $conditions A string or array of WHERE conditions.
825	* @param string $innerGlue The glue by which to join the conditions. Defaults to AND.
826	*
827	* @return self This object to support chaining.
828	*/
829	public function extendWhere($outerGlue, $conditions, $innerGlue = 'AND')
830	{
831	// Replace the current WHERE with a new one which has the old one as an unnamed child.
832	// Use "()" as name to wrap the existing WHERE between the parentheses.
833	$this->where = new JDatabaseQueryElement('WHERE', $this->where->setName('()'), " $outerGlue ");
834
835	// append the new conditions as a new unnamed child
836	$this->where->append(new JDatabaseQueryElement('()', $conditions, " $innerGlue "));
837
838	return $this;
839	}
840
841	/**
842	* Extend the WHERE clause with an OR and a single condition or an array of conditions.
843	*
844	* Usage:
845	* $query->where(array('a = 1', 'b = 2'))->orWhere(array('c = 3', 'd = 4'));
846	* will produce: WHERE ((a = 1 AND b = 2) OR (c = 3 AND d = 4)
847	*
848	* @param mixed $conditions A string or array of WHERE conditions.
849	* @param string $glue The glue by which to join the conditions. Defaults to AND.
850	*
851	* @return self This object to support chaining.
852	*
853	* @uses extendWhere()
854	*/
855	public function orWhere($conditions, $glue = 'AND')
856	{
857	return $this->extendWhere('OR', $conditions, $glue);
858	}
859
860	/**
861	* Extend the WHERE clause with an AND and a single condition or an array of conditions.
862	*
863	* Usage:
864	* $query->where(array('a = 1', 'b = 2'))->andWhere(array('c = 3', 'd = 4'));
865	* will produce: WHERE ((a = 1 AND b = 2) AND (c = 3 OR d = 4)
866	*
867	* @param mixed $conditions A string or array of WHERE conditions.
868	* @param string $glue The glue by which to join the conditions. Defaults to OR.
869	*
870	* @return self This object to support chaining.
871	*
872	* @uses extendWhere()
873	*/
874	public function andWhere($conditions, $glue = 'OR')
875	{
876	return $this->extendWhere('AND', $conditions, $glue);
877	}
878
879	/**
880	* Return the number of the current row.
881	*
882	* Usage:
883	* $query->select('id');
884	* $query->selectRowNumber('ordering,publish_up DESC', 'new_ordering');
885	* $query->from('#__content');
886	*
887	* @param string $orderBy An expression of ordering for window function.
888	* @param string $orderColumnAlias An alias for new ordering column.
889	*
890	* @return JDatabaseQuery Returns this object to allow chaining.
891	*
892	* @since 10.1.30
893	* @throws RuntimeException
894	*/
895	public function selectRowNumber($orderBy, $orderColumnAlias)
896	{
897	if ($this->selectRowNumber)
898	{
899	throw new RuntimeException("Method 'selectRowNumber' can be called only once per instance.");
900	}
901
902	$this->type = 'select';
903
904	$this->selectRowNumber = array(
905	'orderBy' => $orderBy,
906	'orderColumnAlias' => $orderColumnAlias,
907	);
908
909	$this->select("ROW_NUMBER() OVER (ORDER BY $orderBy) AS $orderColumnAlias");
910
911	return $this;
912	}
913	}
914