Tutorial Extension  1.0.0
SellerDeck Extensions - Tutorial Extension
CDataBase.php
1 <?php
2 
3 /**
4  * CDataBase.php - Implementation file for DataBase class.
5  *
6  * @package SellerDeck Extensions
7  *
8  * @author Péter Erd?di
9  * @copyright © SellerDeck Ltd 2015. All rights reserved.
10  */
11 
12 namespace SDExtension\DB;
13 
14 class CDataBase
15  {
16 
17  /**
18  * @var object DataBase Object
19  */
20  protected $m_oDB = null;
21 
22  /**
23  * @var string DataBase ID
24  */
25  protected $m_sDataBaseID = DB_CATALOG;
26 
27  /**
28  * @var string DataBase Authentication Type
29  */
30  protected $m_sDBAuthType = "DB";
31 
32  /**
33  * @var string DataBase User
34  */
35  protected $m_sDBUser = "dbo";
36 
37  /**
38  * @var object DataBases
39  */
40  protected $m_aDataBases = [];
41 
42  /**
43  * @var string Begin quote sign for DB Columns
44  */
45  protected $m_sColumnQuoteBegin = '`';
46 
47  /**
48  * @var string End quote sign for DB Columns
49  */
50  protected $m_sColumnQuoteEnd = '`';
51 
52  /**
53  * @var string Begin quote sign for Values
54  */
55  protected $m_sValueQuoteBegin = "'";
56 
57  /**
58  * @var string End quote sign for Values
59  */
60  protected $m_sValueQuoteEnd = "'";
61 
62  /**
63  * @var string Null Value
64  */
65  protected $m_sNullValue = "NULL";
66 
67  /**
68  * @var string Last Query
69  */
70  protected $m_sLastQuery = "";
71 
72  /**
73  * __construct - Object constructor method
74  *
75  * @access public
76  * @param array $aConnectionDetails
77  * @return void
78  */
79  public function __construct($aConnectionDetails, $sDatabaseID)
80  {
81  $sDsn = arr_get($aConnectionDetails, 'sDsn', '');
82  $this->m_sDBUser = arr_get($aConnectionDetails, 'sUser', '');
83  $sPassword = arr_get($aConnectionDetails, 'sPassword', '');
84  $this->m_sDataBaseID = arr_get($aConnectionDetails, 'sDatabaseID', '');
85  $this->m_sDBAuthType = arr_get($aConnectionDetails, 'sDBAuthType', '');
86  $this->m_aDataBases = arr_get($aConnectionDetails, 'aDataBases', []);
87  $this->m_oDB = new \PDO($sDsn, $this->m_sDBUser, $sPassword);
88  \SDExtension\Helper\CLogger::get(LOG_CHANNEL_NAME)->addInfo("Connected to Database: $sDatabaseID");
89  }
90 
91  /**
92  * GetDatabaseID - Database ID
93  *
94  * @access public
95  * @return string Database ID
96  */
97  public function GetDatabaseID()
98  {
99  return $this->m_sDataBaseID;
100  }
101 
102  /**
103  * GetDataBases - DataBases array
104  *
105  * @access public
106  * @return array DataBase
107  */
108  public function GetDataBases()
109  {
110  return $this->m_aDataBases;
111  }
112 
113  /**
114  * GetDBUser - DataBase User
115  *
116  * @access public
117  * @return string DataBase User
118  */
119  public function GetDBUser()
120  {
121  return $this->m_sDBUser;
122  }
123 
124  /**
125  * GetDBAuthType - DataBase Authentication Type
126  *
127  * @access public
128  * @return string DataBase Authentication Type
129  */
130  public function GetDBAuthType()
131  {
132  return $this->m_sDBAuthType;
133  }
134 
135  /**
136  * Expression - Mark SQL Expression to act as CDataBase Expression (ie. not to be surounded by quotes)
137  *
138  * @access public
139  * @param string $sExpression SQL Expression to be marked as CDataBase expression
140  * @return string Marked as expression
141  */
142  public function Expression($sExpression)
143  {
144  return SQL_EXPRESSION_PREFIX . $sExpression;
145  }
146 
147  /**
148  * Quote - Surrounds string with given prefix and suffix (quote signs)
149  *
150  * @access protected
151  * @param string $sData Data to quote
152  * @param string $sQuoteBegin Begin quote sign
153  * @param string $sQuoteBegin End quote sign
154  * @return string Quoted string
155  */
156  protected function Quote($sData, $sQuoteBegin, $sQuoteEnd)
157  {
158  return $sQuoteBegin . $sData . $sQuoteEnd;
159  }
160 
161  /**
162  * Sanitize - Sanitizes string
163  *
164  * @access protected
165  * @param string $sData Data to sanitize
166  * @return string Sanitized string
167  */
168  protected function Sanitize($sData)
169  {
170  return addslashes($sData);
171  }
172 
173  /**
174  * QuoteValue - Quotes Value string
175  *
176  * @access public
177  * @param mixed $vData Data to quote
178  * @return string Quoted string
179  */
180  public function QuoteValue($vData)
181  {
182  if (starts_with($vData, SQL_COLUMN))
183  {
184  $vData = remove_prefix($vData, SQL_COLUMN);
185  return $this->Quote($vData, $this->m_sColumnQuoteBegin, $this->m_sColumnQuoteEnd);
186  }
187  if (starts_with($vData, SQL_TYPE_STRING))
188  {
189  $vData = remove_prefix($vData, SQL_TYPE_STRING);
190  $vData = $this->Sanitize($vData);
191  return $this->Quote($vData, $this->m_sValueQuoteBegin, $this->m_sValueQuoteEnd);
192  }
193  if (starts_with($vData, SQL_EXPRESSION_PREFIX))
194  {
195  return remove_prefix($vData, SQL_EXPRESSION_PREFIX);
196  }
197  if (is_bool($vData))
198  {
199  return $this->BoolValue($vData);
200  }
201  if (is_numeric($vData))
202  {
203  return $vData;
204  }
205  if ($vData == null)
206  {
207  return $this->m_sNullValue;
208  }
209  $vData = $this->Sanitize($vData);
210  return $this->Quote($vData, $this->m_sValueQuoteBegin, $this->m_sValueQuoteEnd);
211  }
212 
213  /**
214  * QuoteTable - Quotes Table name
215  *
216  * @access public
217  * @param string|array $vTableName Table name
218  * @return string Quoted Table name
219  */
220  public function QuoteTable($vTableName)
221  {
222  if (is_array($vTableName))
223  {
224  $sTableName = array_shift($vTableName);
225  $sTableAlias = array_shift($vTableName);
226  return
227  $this->Quote($sTableName, $this->m_sColumnQuoteBegin, $this->m_sColumnQuoteEnd) .
228  (!empty($sTableAlias) ? " AS " .
229  $this->Quote($sTableAlias, $this->m_sColumnQuoteBegin, $this->m_sColumnQuoteEnd) : "");
230  }
231  else
232  {
233  return $this->Quote($vTableName, $this->m_sColumnQuoteBegin, $this->m_sColumnQuoteEnd);
234  }
235  }
236 
237  /**
238  * QuoteColumn - Quotes DB Column string
239  *
240  * @access public
241  * @param string|array $vData Data to quote
242  * @return string Quoted string
243  */
244  public function QuoteColumn($vData)
245  {
246  $sTableName = "";
247  if (is_array($vData))
248  {
249  $sTableName = array_shift($vData);
250  $vData = array_shift($vData);
251  if (empty($vData))
252  {
253  $vData = $sTableName;
254  }
255  else
256  {
257  $sTableName = $this->Quote($sTableName, $this->m_sColumnQuoteBegin, $this->m_sColumnQuoteEnd) . ".";
258  }
259  }
260  if (starts_with($vData, SQL_EXPRESSION_PREFIX))
261  {
262  return remove_prefix($vData, SQL_EXPRESSION_PREFIX);
263  }
264  if (starts_with($vData, SQL_DATA_PREFIX))
265  {
266  return $this->QuoteValue(remove_prefix($vData, SQL_DATA_PREFIX));
267  }
268  if ($vData == "*")
269  {
270  return $vData;
271  }
272  return $sTableName . $this->Quote($vData, $this->m_sColumnQuoteBegin, $this->m_sColumnQuoteEnd);
273  }
274 
275  /**
276  * QuoteCompundColumn - Quotes DB Column string or array
277  *
278  * @access public
279  * @param string|array $vUnquotedElement Data to quote
280  * @return string Quoted string
281  */
282  public function QuoteCompundColumn($vUnquotedElement)
283  {
284  if (is_array($vUnquotedElement))
285  {
286  $sAlias = "";
287  if (count($vUnquotedElement) > 2)
288  {
289  $sAlias = " AS " . $this->QuoteColumn(array_pop($vUnquotedElement));
290  }
291  return implode('.', array_map(array($this, 'QuoteColumn'), $vUnquotedElement)) . $sAlias;
292  }
293  else
294  {
295  return $this->QuoteColumn($vUnquotedElement);
296  }
297  }
298 
299  /**
300  * QuoteArray - Quotes array elements
301  *
302  * @access protected
303  * @param array $aUnquotedArray Array to quote
304  * @param bool $bValue
305  * @return array Quoted array
306  */
307  protected function QuoteArray($aUnquotedArray, $bValue = true)
308  {
309  $aQuotedArray = [];
310  foreach ($aUnquotedArray as $sData)
311  {
312  if ($bValue)
313  {
314  $aQuotedArray[] = $this->QuoteValue($sData);
315  }
316  else
317  {
318  $aQuotedArray[] = $this->QuoteColumn($sData);
319  }
320  }
321  return $aQuotedArray;
322  }
323 
324  /**
325  * QuoteUpdateArray - Generates array of quoted parameters used in UPDATE expressions
326  *
327  * @access protected
328  * @param array $aUnquotedArray Array to quote
329  * @return string Quoted string
330  */
331  protected function QuoteUpdateArray($aUnquotedArray)
332  {
333  $aQuotedArray = array();
334  foreach ($aUnquotedArray as $sKey => $sData)
335  {
336  $aQuotedArray[] = $this->QuoteColumn($sKey) .
337  " = " .
338  $this->QuoteValue($sData);
339  }
340  return implode(", ", $aQuotedArray);
341  }
342 
343  /**
344  * QuoteValueArray - Quotes array elements, with Value quotes
345  *
346  * @access protected
347  * @param array $aUnquotedArray Array to quote
348  * @return array Quoted array
349  */
350  protected function QuoteValueArray($aUnquotedArray)
351  {
352  return $this->QuoteArray($aUnquotedArray, true);
353  }
354 
355  /**
356  * QuoteColumnArray - Quotes array elements, with DB Column quotes
357  *
358  * @access protected
359  * @param array $aUnquotedArray Array to quote
360  * @return array Quoted array
361  */
362  protected function QuoteColumnArray($aUnquotedArray)
363  {
364  return $this->QuoteArray($aUnquotedArray, false);
365  }
366 
367  /**
368  * QuoteValueList - Generates string of quoted Values used in INSERT expressions
369  *
370  * @access protected
371  * @param array $aUnquotedArray Array to quote
372  * @param string $sSeparator Separator
373  * @return array Quoted array
374  */
375  protected function QuoteValueList($aUnquotedArray, $sSeparator = ", ")
376  {
377  $sList = implode($sSeparator, $this->QuoteValueArray($aUnquotedArray));
378  return $sList;
379  }
380 
381  /**
382  * QuoteColumnList - Generates string of quoted DB Columns used in INSERT expressions
383  *
384  * @access protected
385  * @param array $aUnquotedArray Array to quote
386  * @param string $sSeparator Separator
387  * @return array Quoted array
388  */
389  protected function QuoteColumnList($aUnquotedArray, $sSeparator = ", ")
390  {
391  $aProcessedUnquotedArray = array();
392  foreach ($aUnquotedArray as $vUnquotedElement)
393  {
394  $aProcessedUnquotedArray[] = $this->QuoteCompundColumn($vUnquotedElement);
395  }
396  $sList = implode($sSeparator, $aProcessedUnquotedArray);
397  return $sList;
398  }
399 
400  /**
401  * GetDB - DB
402  *
403  * @access public
404  * @return object DB
405  */
406  public function GetDB()
407  {
408  return $this->m_oDB;
409  }
410 
411  /**
412  * GetLastQuery - Returns last query SQL
413  *
414  * @access public
415  * @return string Quoted array
416  */
417  public function GetLastQuery()
418  {
419  return $this->m_sLastQuery;
420  }
421 
422  /**
423  * SQL query
424  *
425  * @access public
426  * @param string $sQuery Table name
427  * @param int $nReturnType Return Type (associative array by default)
428  * @return array|object Query result
429  */
430  public function SQL($sQuery, $nReturnType = \PDO::FETCH_ASSOC)
431  {
432  \SDExtension\Helper\CLogger::get(LOG_CHANNEL_NAME)->addDebug(__FUNCTION__ . " : " . $sQuery);
433  return $this->m_oDB->query($sQuery, $nReturnType);
434  }
435 
436  /**
437  * BasicInsert - Generates and runs Basic INSERT expression
438  *
439  * @access protected
440  * @param string $sTable Table name
441  * @param array $aValues Array of (DB Column => Value) pairs
442  * @return null
443  */
444  protected function BasicInsert($sTable, $aValues)
445  {
446  $sTableQuoted = $this->QuoteTable($sTable);
447  $sColumnList = $this->QuoteColumnList(array_keys($aValues));
448  $sValueList = $this->QuoteValueList(array_values($aValues));
449  $sQuery = "INSERT INTO $sTableQuoted (" . $sColumnList . ") VALUES (" . $sValueList . ");";
450  $nResult = $this->m_oDB->exec($sQuery);
451  \SDExtension\Helper\CLogger::get(LOG_CHANNEL_NAME)->addDebug(__FUNCTION__ . " : $sQuery");
452  return null;
453  }
454 
455  /**
456  * Insert - Generates and runs INSERT expression
457  *
458  * @todo "OR" is missing from WHERE
459  *
460  * @access public
461  * @param string $sTable Table name
462  * @param array $aValues Array of (DB Column => Value) pairs
463  * @param bool $bAutoID Has Auto ID?
464  * @param string $sIDColumn ID Column
465  * @return null | mixed Last inserted ID
466  */
467  public function Insert($sTable, $aValues, $bAutoID = true, $sIDColumn = null)
468  {
469  //
470  // Default INSERT, without ID column
471  //
472  if ($sIDColumn == null || $bAutoID)
473  {
474  return $this->BasicInsert($sTable, $aValues);
475  }
476  $sQuery = "";
477  $sTableQuoted = $this->QuoteTable($sTable);
478  $bInserted = false;
479  $nCounter = 0;
480  $nLastInsertID = 0;
481  //
482  // Repeated INSERT
483  //
484  while (!$bInserted && $nCounter < MAX_RETRY_BY_INSERT)
485  {
486  $sIDColumnQuoted = $this->QuoteColumn($sIDColumn);
487  $sMaxID = "MaxIDOf$sTable";
488  $oMaxIDResult = $this->Select($sTable, [SQL_EXPRESSION_PREFIX . "MAX($sIDColumnQuoted) AS $sMaxID"]);
489  //
490  // Can't read Table
491  //
492  if ($oMaxIDResult === false)
493  {
494  \SDExtension\Helper\CLogger::get(LOG_CHANNEL_NAME)->addWarning("Can't read $sTable when getting Last ID!");
495  return null;
496  }
497  $aMaxIDResult = $oMaxIDResult->fetch(\PDO::FETCH_ASSOC);
498  $nMaxID = $aMaxIDResult[$sMaxID];
499  //
500  // First record
501  //
502  if (empty($nMaxID))
503  {
504  $nMaxID = 1;
505  }
506  //
507  // Not numeric value: can't be increased!
508  //
509  elseif (!is_numeric($nMaxID))
510  {
511  \SDExtension\Helper\CLogger::get(LOG_CHANNEL_NAME)->addWarning("Table ID [$sTable.$sIDColumn] is not numeric [$nMaxID]!");
512  return null;
513  }
514  //
515  // Attepmting INSERT
516  //
517  $nNewID = $nMaxID + INCREASE_ID_BY_INSERT;
518  $aValues[$sIDColumn] = $nNewID;
519  $sColumnList = $this->QuoteColumnList(array_keys($aValues));
520  $sValueList = $this->QuoteValueList(array_values($aValues));
521  $sQuery = "INSERT INTO $sTableQuoted (" . $sColumnList . ") VALUES (" . $sValueList . ");";
522  $nResult = $this->m_oDB->exec($sQuery);
523  //
524  // Unsuccessful #1 invalid query?
525  //
526  if ($nResult === false)
527  {
528  \SDExtension\Helper\CLogger::get(LOG_CHANNEL_NAME)->addWarning("Can't INSERT into table [$sTable] with ID [$sIDColumn] value $nNewID!");
529  }
530  //
531  // Unsuccessful #2 zero records inserted.
532  //
533  elseif ($nResult <= 0)
534  {
535  \SDExtension\Helper\CLogger::get(LOG_CHANNEL_NAME)->addWarning("0 records inserted.");
536  }
537  //
538  // Insertion successful
539  //
540  else
541  {
542  $nLastInsertID = $nNewID;
543  $bInserted = true;
544  }
545  $nCounter++;
546  }
547  $this->m_sLastQuery = $sQuery;
548  \SDExtension\Helper\CLogger::get(LOG_CHANNEL_NAME)->addDebug(__FUNCTION__ . " : " . $sQuery);
549  //
550  // Final insertion unsuccessful
551  //
552  if (!$bInserted)
553  {
554  \SDExtension\Helper\CLogger::get(LOG_CHANNEL_NAME)->addWarning("Can't INSERT into table [$sTable] with ID [$sIDColumn] value $nNewID!");
555  return null;
556  }
557  return $nLastInsertID;
558  }
559 
560  /**
561  * Update - Generates and runs UPDATE expression
562  *
563  * @todo "OR" is missing from WHERE
564  *
565  * @access public
566  * @param string $sTable Table name
567  * @param array $aValues Array of (DB Column => Value) pairs
568  * @param array $aWhere Array of conditions
569  * @return int Number of rows that were modified or deleted
570  */
571  public function Update($sTable, $aValues, $aWhere = [[1, '', '']])
572  {
573  $sSetList = $this->QuoteUpdateArray($aValues);
574  $sTable = $this->QuoteTable($sTable);
575  $sQuery = "UPDATE $sTable SET " . $sSetList . " WHERE " . $this->CompileWhere($aWhere) . ";";
576  $this->m_sLastQuery = $sQuery;
577  \SDExtension\Helper\CLogger::get(LOG_CHANNEL_NAME)->addDebug(__FUNCTION__ . " : " . $sQuery);
578  return $this->m_oDB->exec($sQuery);
579  }
580 
581  /**
582  * Select - Generates and runs SELECT expression
583  *
584  * @todo "OR" is missing from WHERE
585  *
586  * @access public
587  * @param string $sTable Table name
588  * @param string|array $vColumns Columns to select
589  * @param array $aWhere Array of conditions
590  * @param array $aOthers Other parameters
591  * @param int $nReturnType Return Type (associative array by default)
592  * @return array|object Query result
593  */
594  public function Select($sTable, $vColumns = "*", $aWhere = [], $aOthers = [], $nReturnType = \PDO::FETCH_ASSOC)
595  {
596  $aQuery = [];
597  if (is_array($vColumns))
598  {
599  $aQuery["select"] = $this->QuoteColumnList($vColumns);
600  }
601  else
602  {
603  $aQuery["select"] = $vColumns;
604  }
605  $aQuery["from"] = $this->QuoteTable($sTable);
606  if (!empty($aWhere))
607  {
608  //
609  // TODO: "OR" is missing...
610  //
611  $aQuery["where"] = $this->CompileWhere($aWhere);
612  }
613  $aQuery["join"] = $this->CompileJoin(arr_get($aOthers, "join", []));
614  $aQuery["group_by"] = $this->CompileGroupBy(arr_get($aOthers, "group_by", []));
615  $aQuery["order_by"] = $this->CompileOrderBy(arr_get($aOthers, "order_by", []));
616  $aQuery["limit"] = $this->CompileLimit(arr_get($aOthers, "limit", []));
617  $sQuery = $this->CompileSelectSql($aQuery);
618  $this->m_sLastQuery = $sQuery;
619  \SDExtension\Helper\CLogger::get(LOG_CHANNEL_NAME)->addDebug(__FUNCTION__ . " : " . $sQuery);
620  return $this->m_oDB->query($sQuery, $nReturnType);
621  }
622 
623  /**
624  * CompileSelectSql - Compiles SQL expression
625  *
626  * @access protected
627  * @param array $aWhere Array of conditions
628  * @return string WHERE statement
629  */
630  protected function CompileSelectSql($aQuery = [])
631  {
632  $sSelectSQL = "";
633  if (!empty($aQuery["select"]))
634  {
635  $sSelectSQL .= "SELECT $aQuery[select] ";
636  }
637  else
638  {
639  $sSelectSQL .= "SELECT * ";
640  }
641  if (!empty($aQuery["from"]))
642  {
643  $sSelectSQL .= "FROM $aQuery[from] ";
644  }
645  if (!empty($aQuery["join"]))
646  {
647  $sSelectSQL .= " $aQuery[join] ";
648  }
649  if (!empty($aQuery["where"]))
650  {
651  $sSelectSQL .= "WHERE $aQuery[where] ";
652  }
653  if (!empty($aQuery["group_by"]))
654  {
655  $sSelectSQL .= "GROUP BY $aQuery[group_by] ";
656  }
657  if (!empty($aQuery["order_by"]))
658  {
659  $sSelectSQL .= "ORDER BY $aQuery[order_by] ";
660  }
661  if (!empty($aQuery["limit"]))
662  {
663  $sSelectSQL .= "LIMIT $aQuery[limit] ";
664  }
665  return $sSelectSQL;
666  }
667 
668  /**
669  * CompileWhereElement - Compiles a single WHERE element
670  *
671  * @access protected
672  * @param array $aValue Array of a single condition
673  * @return string WHERE statement
674  */
675  protected function CompileWhereElement($aValue = [])
676  {
677  if (count($aValue) == 3)
678  {
679  return $this->QuoteColumn($aValue[0]) .
680  " $aValue[1] " .
681  $this->QuoteValue($aValue[2]);
682  }
683  elseif (count($aValue) == 2)
684  {
685  $sWhere = $this->QuoteColumn($aValue[0]);
686  if (starts_with($aValue[1], SQL_EXPRESSION_PREFIX))
687  {
688  return $sWhere .
689  " " .
690  remove_prefix($aValue[1], SQL_EXPRESSION_PREFIX);
691  }
692  elseif (starts_with($aValue[1], SQL_TYPE_STRING))
693  {
694  return $sWhere .
695  " LIKE " .
696  $this->QuoteValue($aValue[1]);
697  }
698  else
699  {
700  return $sWhere .
701  " = " .
702  $this->QuoteValue($aValue[1]);
703  }
704  }
705  else
706  {
707  return $aValue[0];
708  }
709  }
710 
711  /**
712  * IsElementFormat - Checks if value is a well formatted WHERE element
713  *
714  * @access protected
715  * @param array $aValue Value
716  * @return bool
717  */
718  protected function IsElementFormat($aValue)
719  {
720  if (is_array($aValue) && isset($aValue[0]))
721  {
722  if (is_array($aValue[0]) && isset($aValue[0][0]))
723  {
724  return !is_array($aValue[0][0]);
725  }
726  return true;
727  }
728  return false;
729  }
730 
731  /**
732  * CompileWhere - Generates WHERE expression
733  *
734  * Used in SELECT and UPDATE
735  *
736  * @todo "OR" is missing from WHERE
737  *
738  * @access protected
739  * @param array $aWhere Array of conditions
740  * @return string WHERE statement
741  */
742  public function CompileWhere($aWhere = [], $aBoolOp = BOOP_OP_AND)
743  {
744  $aWhereList = [];
745  if (is_array($aWhere))
746  {
747  foreach ($aWhere as $vKey => $aValue)
748  {
749  if (BOOP_OP_AND === $vKey || BOOP_OP_OR === $vKey)
750  {
751  $aWhereList[] = $this->CompileWhere($aValue, $vKey);
752  }
753  else
754  {
755  if ($this->IsElementFormat($aValue))
756  {
757  $aWhereList[] = $this->CompileWhereElement($aValue);
758  }
759  else
760  {
761  $aWhereList[] = "(" . $this->CompileWhere($aValue, $aBoolOp) . ")";
762  }
763  }
764  }
765  }
766  return implode(" $aBoolOp ", $aWhereList);
767  }
768 
769  /**
770  * CompileJoinOn - Generates JOIN ... ON expression
771  *
772  * @todo "OR" is missing from WHERE
773  *
774  * @access protected
775  * @param array $aJoinOn Array of conditions
776  * @return string WHERE statement
777  */
778  public function CompileJoinOn($aJoinOn = [])
779  {
780  $aJoinOnList = [];
781  foreach ($aJoinOn as $sKey => $aValue)
782  {
783  if (is_array($aValue))
784  {
785  if (count($aValue) == 3)
786  {
787  $aJoinOnList[] = $this->QuoteCompundColumn($aValue[0]) .
788  " $aValue[1] " .
789  $this->QuoteCompundColumn($aValue[2]);
790  }
791  elseif (count($aValue) == 2)
792  {
793  $aJoinOnList[] = $this->QuoteCompundColumn($aValue[0]) .
794  " = " .
795  $this->QuoteCompundColumn($aValue[1]);
796  }
797  }
798  }
799  return implode(' AND ', $aJoinOnList);
800  }
801 
802  /**
803  * CompileLimit - Compiles LIMIT expression
804  *
805  * @access protected
806  * @param array|string $vLimit Array or String of LIMIT(s)
807  * @return string LIMIT expression
808  */
809  protected function CompileLimit($vLimit)
810  {
811  if (is_array($vLimit))
812  {
813  return implode(", ", $vLimit);
814  }
815  else
816  {
817  return $vLimit;
818  }
819  }
820 
821  /**
822  * CompileJoin - Compiles JOIN expression
823  *
824  * @access protected
825  * @param array $aJoins Array of JOINs
826  * @return string JOIN expression
827  */
828  protected function CompileJoin($aJoins)
829  {
830  $aJoinDirections = [];
831  $aJoinTables = [];
832  $aJoinOns = [];
833  foreach ($aJoins as $aJoin)
834  {
835  $sJoinTable = "";
836  switch (strtolower(arr_get($aJoin, 0, "inner")))
837  {
838  case "right":
839  $aJoinDirections[] = "RIGHT JOIN";
840  break;
841  case "left":
842  $aJoinDirections[] = "LEFT JOIN";
843  break;
844  default:
845  $aJoinDirections[] = "INNER JOIN";
846  break;
847  }
848  $aJoinTables[] = $this->QuoteTable(arr_get($aJoin, 1, ""));
849  $aJoinOns[] = arr_get($aJoin, 2, []);
850  }
851  return $this->ConcatenateJoins($aJoinDirections, $aJoinTables, $aJoinOns);
852  }
853 
854  /**
855  * ConcatenateJoins - Concatenates JOIN expression
856  *
857  * @access protected
858  * @param array $aJoinDirections Array of JOIN Directions
859  * @param array $aJoinTables Array of JOIN Tables
860  * @param array $aJoinOns Array of JOIN ONs
861  * @return string JOIN expression
862  */
863  protected function ConcatenateJoins($aJoinDirections, $aJoinTables, $aJoinOns)
864  {
865  $sJoins = "";
866  foreach ($aJoinTables as $nKey => $sJoinTable)
867  {
868  $sJoins .= arr_get($aJoinDirections, $nKey, "INNER JOIN") . " $sJoinTable ON (" . $this->CompileJoinOn(arr_get($aJoinOns, $nKey, "")) . ") ";
869  }
870  return $sJoins;
871  }
872 
873  /**
874  * CompileGroupBy - Compiles GROUP BY expression
875  *
876  * @access protected
877  * @param array $aGroupByList Array of GROUP BYs
878  * @return string GROUP BY expression
879  */
880  protected function CompileGroupBy($aGroupByList)
881  {
882  $aGroupBy = [];
883  foreach ($aGroupByList as $nKey => $aGroupByEntry)
884  {
885  $aGroupBy[] = $this->QuoteCompundColumn(arr_get($aGroupByEntry, 0, "")) . "." .
886  $this->QuoteCompundColumn(arr_get($aGroupByEntry, 1, ""));
887  }
888  return implode(", ", $aGroupBy);
889  }
890 
891  /**
892  * CompileOrderBy - Compiles ORDER BY expression
893  *
894  * @access protected
895  * @param array $aOrderByList Array of ORDER BYs
896  * @return string ORDER BY expression
897  */
898  protected function CompileOrderBy($aOrderByList)
899  {
900  $aOrderBy = [];
901  foreach ($aOrderByList as $nKey => $aOrderByEntry)
902  {
903  $aOrderBy[] = $this->QuoteCompundColumn(arr_get($aOrderByEntry, 0, ""))
904  . " "
905  . strtoupper(arr_get($aOrderByEntry, 1, "ASC"));
906  }
907  return implode(", ", $aOrderBy);
908  }
909 
910  /**
911  * BoolValue - Converts Boolean to appropriate value depending on driver
912  *
913  * @access protected
914  * @param bool $bData Boolean value
915  * @return bool
916  */
917  protected function BoolValue($bData)
918  {
919  return $bData;
920  }
921 
922  }
static get($sChannel="default", $sLogRoot="")
Definition: CLogger.php:90
CompileWhereElement($aValue=[])
Definition: CDataBase.php:675
Expression($sExpression)
Definition: CDataBase.php:142
QuoteArray($aUnquotedArray, $bValue=true)
Definition: CDataBase.php:307
QuoteColumnArray($aUnquotedArray)
Definition: CDataBase.php:362
Select($sTable, $vColumns="*", $aWhere=[], $aOthers=[], $nReturnType=\PDO::FETCH_ASSOC)
Definition: CDataBase.php:594
ConcatenateJoins($aJoinDirections, $aJoinTables, $aJoinOns)
Definition: CDataBase.php:863
SQL($sQuery, $nReturnType=\PDO::FETCH_ASSOC)
Definition: CDataBase.php:430
QuoteValueArray($aUnquotedArray)
Definition: CDataBase.php:350
Update($sTable, $aValues, $aWhere=[[1, '', '']])
Definition: CDataBase.php:571
CompileOrderBy($aOrderByList)
Definition: CDataBase.php:898
QuoteCompundColumn($vUnquotedElement)
Definition: CDataBase.php:282
CompileSelectSql($aQuery=[])
Definition: CDataBase.php:630
Quote($sData, $sQuoteBegin, $sQuoteEnd)
Definition: CDataBase.php:156
CompileGroupBy($aGroupByList)
Definition: CDataBase.php:880
QuoteValueList($aUnquotedArray, $sSeparator=", ")
Definition: CDataBase.php:375
BasicInsert($sTable, $aValues)
Definition: CDataBase.php:444
QuoteColumnList($aUnquotedArray, $sSeparator=", ")
Definition: CDataBase.php:389
__construct($aConnectionDetails, $sDatabaseID)
Definition: CDataBase.php:79
CompileWhere($aWhere=[], $aBoolOp=BOOP_OP_AND)
Definition: CDataBase.php:742
QuoteUpdateArray($aUnquotedArray)
Definition: CDataBase.php:331
QuoteTable($vTableName)
Definition: CDataBase.php:220
CompileJoinOn($aJoinOn=[])
Definition: CDataBase.php:778
Insert($sTable, $aValues, $bAutoID=true, $sIDColumn=null)
Definition: CDataBase.php:467