20 protected $m_oDB = null;
25 protected $m_sDataBaseID = DB_CATALOG;
30 protected $m_sDBAuthType =
"DB";
35 protected $m_sDBUser =
"dbo";
40 protected $m_aDataBases = [];
45 protected $m_sColumnQuoteBegin =
'`';
50 protected $m_sColumnQuoteEnd =
'`';
55 protected $m_sValueQuoteBegin =
"'";
60 protected $m_sValueQuoteEnd =
"'";
65 protected $m_sNullValue =
"NULL";
70 protected $m_sLastQuery =
"";
79 public function __construct($aConnectionDetails, $sDatabaseID)
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);
99 return $this->m_sDataBaseID;
110 return $this->m_aDataBases;
121 return $this->m_sDBUser;
132 return $this->m_sDBAuthType;
144 return SQL_EXPRESSION_PREFIX . $sExpression;
156 protected function Quote($sData, $sQuoteBegin, $sQuoteEnd)
158 return $sQuoteBegin . $sData . $sQuoteEnd;
170 return addslashes($sData);
182 if (starts_with($vData, SQL_COLUMN))
184 $vData = remove_prefix($vData, SQL_COLUMN);
185 return $this->
Quote($vData, $this->m_sColumnQuoteBegin, $this->m_sColumnQuoteEnd);
187 if (starts_with($vData, SQL_TYPE_STRING))
189 $vData = remove_prefix($vData, SQL_TYPE_STRING);
191 return $this->
Quote($vData, $this->m_sValueQuoteBegin, $this->m_sValueQuoteEnd);
193 if (starts_with($vData, SQL_EXPRESSION_PREFIX))
195 return remove_prefix($vData, SQL_EXPRESSION_PREFIX);
201 if (is_numeric($vData))
207 return $this->m_sNullValue;
210 return $this->
Quote($vData, $this->m_sValueQuoteBegin, $this->m_sValueQuoteEnd);
222 if (is_array($vTableName))
224 $sTableName = array_shift($vTableName);
225 $sTableAlias = array_shift($vTableName);
227 $this->
Quote($sTableName, $this->m_sColumnQuoteBegin, $this->m_sColumnQuoteEnd) .
228 (!empty($sTableAlias) ?
" AS " .
229 $this->
Quote($sTableAlias, $this->m_sColumnQuoteBegin, $this->m_sColumnQuoteEnd) :
"");
233 return $this->
Quote($vTableName, $this->m_sColumnQuoteBegin, $this->m_sColumnQuoteEnd);
247 if (is_array($vData))
249 $sTableName = array_shift($vData);
250 $vData = array_shift($vData);
253 $vData = $sTableName;
257 $sTableName = $this->
Quote($sTableName, $this->m_sColumnQuoteBegin, $this->m_sColumnQuoteEnd) .
".";
260 if (starts_with($vData, SQL_EXPRESSION_PREFIX))
262 return remove_prefix($vData, SQL_EXPRESSION_PREFIX);
264 if (starts_with($vData, SQL_DATA_PREFIX))
266 return $this->
QuoteValue(remove_prefix($vData, SQL_DATA_PREFIX));
272 return $sTableName . $this->
Quote($vData, $this->m_sColumnQuoteBegin, $this->m_sColumnQuoteEnd);
284 if (is_array($vUnquotedElement))
287 if (count($vUnquotedElement) > 2)
289 $sAlias =
" AS " . $this->
QuoteColumn(array_pop($vUnquotedElement));
291 return implode(
'.', array_map(array($this,
'QuoteColumn'), $vUnquotedElement)) . $sAlias;
307 protected function QuoteArray($aUnquotedArray, $bValue =
true)
310 foreach ($aUnquotedArray as $sData)
321 return $aQuotedArray;
333 $aQuotedArray = array();
334 foreach ($aUnquotedArray as $sKey => $sData)
340 return implode(
", ", $aQuotedArray);
352 return $this->
QuoteArray($aUnquotedArray,
true);
364 return $this->
QuoteArray($aUnquotedArray,
false);
377 $sList = implode($sSeparator, $this->
QuoteValueArray($aUnquotedArray));
391 $aProcessedUnquotedArray = array();
392 foreach ($aUnquotedArray as $vUnquotedElement)
396 $sList = implode($sSeparator, $aProcessedUnquotedArray);
419 return $this->m_sLastQuery;
430 public function SQL($sQuery, $nReturnType = \PDO::FETCH_ASSOC)
433 return $this->m_oDB->query($sQuery, $nReturnType);
449 $sQuery =
"INSERT INTO $sTableQuoted (" . $sColumnList .
") VALUES (" . $sValueList .
");";
450 $nResult = $this->m_oDB->exec($sQuery);
467 public function Insert($sTable, $aValues, $bAutoID =
true, $sIDColumn = null)
472 if ($sIDColumn == null || $bAutoID)
484 while (!$bInserted && $nCounter < MAX_RETRY_BY_INSERT)
487 $sMaxID =
"MaxIDOf$sTable";
488 $oMaxIDResult = $this->
Select($sTable, [SQL_EXPRESSION_PREFIX .
"MAX($sIDColumnQuoted) AS $sMaxID"]);
492 if ($oMaxIDResult ===
false)
497 $aMaxIDResult = $oMaxIDResult->fetch(\PDO::FETCH_ASSOC);
498 $nMaxID = $aMaxIDResult[$sMaxID];
509 elseif (!is_numeric($nMaxID))
517 $nNewID = $nMaxID + INCREASE_ID_BY_INSERT;
518 $aValues[$sIDColumn] = $nNewID;
521 $sQuery =
"INSERT INTO $sTableQuoted (" . $sColumnList .
") VALUES (" . $sValueList .
");";
522 $nResult = $this->m_oDB->exec($sQuery);
526 if ($nResult ===
false)
533 elseif ($nResult <= 0)
542 $nLastInsertID = $nNewID;
547 $this->m_sLastQuery = $sQuery;
557 return $nLastInsertID;
571 public function Update($sTable, $aValues, $aWhere = [[1,
'',
'']])
575 $sQuery =
"UPDATE $sTable SET " . $sSetList .
" WHERE " . $this->
CompileWhere($aWhere) .
";";
576 $this->m_sLastQuery = $sQuery;
578 return $this->m_oDB->exec($sQuery);
594 public function Select($sTable, $vColumns =
"*", $aWhere = [], $aOthers = [], $nReturnType = \PDO::FETCH_ASSOC)
597 if (is_array($vColumns))
603 $aQuery[
"select"] = $vColumns;
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", []));
618 $this->m_sLastQuery = $sQuery;
620 return $this->m_oDB->query($sQuery, $nReturnType);
633 if (!empty($aQuery[
"select"]))
635 $sSelectSQL .=
"SELECT $aQuery[select] ";
639 $sSelectSQL .=
"SELECT * ";
641 if (!empty($aQuery[
"from"]))
643 $sSelectSQL .=
"FROM $aQuery[from] ";
645 if (!empty($aQuery[
"join"]))
647 $sSelectSQL .=
" $aQuery[join] ";
649 if (!empty($aQuery[
"where"]))
651 $sSelectSQL .=
"WHERE $aQuery[where] ";
653 if (!empty($aQuery[
"group_by"]))
655 $sSelectSQL .=
"GROUP BY $aQuery[group_by] ";
657 if (!empty($aQuery[
"order_by"]))
659 $sSelectSQL .=
"ORDER BY $aQuery[order_by] ";
661 if (!empty($aQuery[
"limit"]))
663 $sSelectSQL .=
"LIMIT $aQuery[limit] ";
677 if (count($aValue) == 3)
683 elseif (count($aValue) == 2)
686 if (starts_with($aValue[1], SQL_EXPRESSION_PREFIX))
690 remove_prefix($aValue[1], SQL_EXPRESSION_PREFIX);
692 elseif (starts_with($aValue[1], SQL_TYPE_STRING))
720 if (is_array($aValue) && isset($aValue[0]))
722 if (is_array($aValue[0]) && isset($aValue[0][0]))
724 return !is_array($aValue[0][0]);
745 if (is_array($aWhere))
747 foreach ($aWhere as $vKey => $aValue)
749 if (BOOP_OP_AND === $vKey || BOOP_OP_OR === $vKey)
761 $aWhereList[] =
"(" . $this->
CompileWhere($aValue, $aBoolOp) .
")";
766 return implode(
" $aBoolOp ", $aWhereList);
781 foreach ($aJoinOn as $sKey => $aValue)
783 if (is_array($aValue))
785 if (count($aValue) == 3)
791 elseif (count($aValue) == 2)
799 return implode(
' AND ', $aJoinOnList);
811 if (is_array($vLimit))
813 return implode(
", ", $vLimit);
830 $aJoinDirections = [];
833 foreach ($aJoins as $aJoin)
836 switch (strtolower(arr_get($aJoin, 0,
"inner")))
839 $aJoinDirections[] =
"RIGHT JOIN";
842 $aJoinDirections[] =
"LEFT JOIN";
845 $aJoinDirections[] =
"INNER JOIN";
848 $aJoinTables[] = $this->
QuoteTable(arr_get($aJoin, 1,
""));
849 $aJoinOns[] = arr_get($aJoin, 2, []);
866 foreach ($aJoinTables as $nKey => $sJoinTable)
868 $sJoins .= arr_get($aJoinDirections, $nKey,
"INNER JOIN") .
" $sJoinTable ON (" . $this->
CompileJoinOn(arr_get($aJoinOns, $nKey,
"")) .
") ";
883 foreach ($aGroupByList as $nKey => $aGroupByEntry)
888 return implode(
", ", $aGroupBy);
901 foreach ($aOrderByList as $nKey => $aOrderByEntry)
905 . strtoupper(arr_get($aOrderByEntry, 1,
"ASC"));
907 return implode(
", ", $aOrderBy);
static get($sChannel="default", $sLogRoot="")
CompileWhereElement($aValue=[])
QuoteArray($aUnquotedArray, $bValue=true)
QuoteColumnArray($aUnquotedArray)
Select($sTable, $vColumns="*", $aWhere=[], $aOthers=[], $nReturnType=\PDO::FETCH_ASSOC)
ConcatenateJoins($aJoinDirections, $aJoinTables, $aJoinOns)
SQL($sQuery, $nReturnType=\PDO::FETCH_ASSOC)
QuoteValueArray($aUnquotedArray)
Update($sTable, $aValues, $aWhere=[[1, '', '']])
CompileOrderBy($aOrderByList)
QuoteCompundColumn($vUnquotedElement)
CompileSelectSql($aQuery=[])
Quote($sData, $sQuoteBegin, $sQuoteEnd)
CompileGroupBy($aGroupByList)
QuoteValueList($aUnquotedArray, $sSeparator=", ")
BasicInsert($sTable, $aValues)
QuoteColumnList($aUnquotedArray, $sSeparator=", ")
__construct($aConnectionDetails, $sDatabaseID)
CompileWhere($aWhere=[], $aBoolOp=BOOP_OP_AND)
QuoteUpdateArray($aUnquotedArray)
CompileJoinOn($aJoinOn=[])
Insert($sTable, $aValues, $bAutoID=true, $sIDColumn=null)