OXID eShop CE  4.10.2
 All Classes Namespaces Files Functions Variables Pages
oxdbmetadatahandler.php
Go to the documentation of this file.
1 <?php
2 
8 {
9 
14  protected $_aDbTablesFields = null;
15 
16 
21  protected $_aTables = null;
22 
28 
33  protected $_aSkipTablesOnReset = array("oxcountry");
34 
40  protected $aForceOriginalFields = array('OXID');
41 
49  public function getFields($sTableName)
50  {
51  $aFields = array();
52  $aRawFields = oxDb::getDb()->MetaColumns($sTableName);
53  if (is_array($aRawFields)) {
54  foreach ($aRawFields as $oField) {
55  $aFields[$oField->name] = "{$sTableName}.{$oField->name}";
56  }
57  }
58 
59  return $aFields;
60  }
61 
69  public function tableExists($sTableName)
70  {
71  $oDb = oxDb::getDb();
72  $aTables = $oDb->getAll("show tables like " . $oDb->quote($sTableName));
73 
74  return count($aTables) > 0;
75  }
76 
85  public function fieldExists($sFieldName, $sTableName)
86  {
87  $aTableFields = $this->getFields($sTableName);
88  $sTableName = strtoupper($sTableName);
89  if (is_array($aTableFields)) {
90  $sFieldName = strtoupper($sFieldName);
91  $aTableFields = array_map('strtoupper', $aTableFields);
92  if (in_array("{$sTableName}.{$sFieldName}", $aTableFields)) {
93  return true;
94  }
95  }
96 
97  return false;
98  }
99 
100 
108  public function getIndices($tableName)
109  {
110  $result = array();
111 
112  if ($this->tableExists($tableName)) {
113  $result = oxDb::getDb(oxDb::FETCH_MODE_ASSOC)->getAll("SHOW INDEX FROM $tableName");
114  }
115 
116  return $result;
117  }
118 
127  public function hasIndex($indexName, $tableName)
128  {
129  $result = false;
130 
131  foreach ($this->getIndices($tableName) as $index) {
132  if ($indexName === $index['Column_name']) {
133  $result = true;
134  }
135  }
136 
137  return $result;
138  }
139 
140 
147  public function getAllTables()
148  {
149  if (empty($this->_aTables)) {
150 
151  $aTables = oxDb::getDb()->getAll("show tables");
152 
153  foreach ($aTables as $aTableInfo) {
154  $sTableName = $aTableInfo[0];
155 
156  $this->_aTables[] = $aTableInfo[0];
157  }
158  }
159 
160  return $this->_aTables;
161  }
162 
170  public function getAllMultiTables($sTable)
171  {
172  $aMLTables = array();
173  foreach (array_keys(oxRegistry::getLang()->getLanguageIds()) as $iLangId) {
174  $sLangTableName = getLangTableName($sTable, $iLangId);
175  if ($sTable != $sLangTableName && !in_array($sLangTableName, $aMLTables)) {
176  $aMLTables[] = $sLangTableName;
177  }
178  }
179 
180  return $aMLTables;
181  }
182 
192  protected function _getCreateTableSetSql($sTable, $iLang)
193  {
194  $sTableSet = getLangTableName($sTable, $iLang);
195 
196  $aRes = oxDb::getDb()->getAll("show create table {$sTable}");
197  $sSql = "CREATE TABLE `{$sTableSet}` (" .
198  "`OXID` char(32) COLLATE latin1_general_ci NOT NULL, " .
199  "PRIMARY KEY (`OXID`)" .
200  ") " . strstr($aRes[0][1], 'ENGINE=');
201 
202  return $sSql;
203  }
204 
216  public function getAddFieldSql($sTable, $sField, $sNewField, $sPrevField, $sTableSet = null)
217  {
218  if (!$sTableSet) {
219  $sTableSet = $sTable;
220  }
221  $aRes = oxDb::getDb()->getAll("show create table {$sTable}");
222  $sTableSql = $aRes[0][1];
223 
224  // removing comments;
225  $sTableSql = preg_replace('/COMMENT \\\'.*?\\\'/', '', $sTableSql);
226  preg_match("/.*,\s+(['`]?" . preg_quote($sField, '/') . "['`]?\s+[^,]+),.*/", $sTableSql, $aMatch);
227  $sFieldSql = $aMatch[1];
228 
229  $sSql = "";
230  if (!empty($sFieldSql)) {
231  $sFieldSql = preg_replace("/" . preg_quote($sField, '/') . "/", $sNewField, $sFieldSql);
232  $sSql = "ALTER TABLE `$sTableSet` ADD " . $sFieldSql;
233  if ($this->tableExists($sTableSet) && $this->fieldExists($sPrevField, $sTableSet)) {
234  $sSql .= " AFTER `$sPrevField`";
235  }
236  }
237 
238  return $sSql;
239  }
240 
241 
252  public function getAddFieldIndexSql($sTable, $sField, $sNewField, $sTableSet = null)
253  {
254  $aRes = oxDb::getDb()->getAll("show create table {$sTable}");
255 
256  $sTableSql = $aRes[0][1];
257 
258  preg_match_all("/([\w]+\s+)?\bKEY\s+(`[^`]+`)?\s*\([^)]+\)/iU", $sTableSql, $aMatch);
259  $aIndex = $aMatch[0];
260 
261  $blUsingTableSet = $sTableSet ? true : false;
262 
263  if (!$sTableSet) {
264  $sTableSet = $sTable;
265  }
266 
267  $aIndexSql = array();
268  $aSql = array();
269  if (count($aIndex)) {
270  foreach ($aIndex as $sIndexSql) {
271  if (preg_match("/\([^)]*\b" . $sField . "\b[^)]*\)/i", $sIndexSql)) {
272 
273  //removing index name - new will be added automaticly
274  $sIndexSql = preg_replace("/(.*\bKEY\s+)`[^`]+`/", "$1", $sIndexSql);
275 
276  if ($blUsingTableSet) {
277  // replacing multiple fields to one (#3269)
278  $sIndexSql = preg_replace("/\([^\)]+\)/", "(`$sNewField`)", $sIndexSql);
279  } else {
280  //replacing previous field name with new one
281  $sIndexSql = preg_replace("/\b" . $sField . "\b/", $sNewField, $sIndexSql);
282  }
283 
284  $aIndexSql[] = "ADD " . $sIndexSql;
285  }
286  }
287  if (count($aIndexSql)) {
288  $aSql = array("ALTER TABLE `$sTableSet` " . implode(", ", $aIndexSql));
289  }
290  }
291 
292  return $aSql;
293  }
294 
301  public function getCurrentMaxLangId()
302  {
303  if (isset($this->_iCurrentMaxLangId)) {
305  }
306 
307  $sTable = $sTableSet = "oxarticles";
308  $sField = $sFieldSet = "oxtitle";
309  $iLang = 0;
310  while ($this->tableExists($sTableSet) && $this->fieldExists($sFieldSet, $sTableSet)) {
311  $iLang++;
312  $sTableSet = getLangTableName($sTable, $iLang);
313  $sFieldSet = $sField . '_' . $iLang;
314  }
315 
316  $this->_iCurrentMaxLangId = --$iLang;
317 
319  }
320 
326  public function getNextLangId()
327  {
328  return $this->getCurrentMaxLangId() + 1;
329  }
330 
338  public function getMultilangFields($sTable)
339  {
340  $aFields = $this->getFields($sTable);
341  $aMultiLangFields = array();
342 
343  foreach ($aFields as $sField) {
344  if (preg_match("/({$sTable}\.)?(?<field>.+)_1$/", $sField, $aMatches)) {
345  $aMultiLangFields[] = $aMatches['field'];
346  }
347  }
348 
349  return $aMultiLangFields;
350  }
351 
360  public function getSinglelangFields($sTable, $iLang)
361  {
362  $sLangTable = getLangTableName($sTable, $iLang);
363 
364  $aBaseFields = $this->getFields($sTable);
365  $aLangFields = $this->getFields($sLangTable);
366 
367  //Some fields (for example OXID) must be taken from core table.
368  $aLangFields = $this->filterCoreFields($aLangFields);
369 
370  $aFields = array_merge($aBaseFields, $aLangFields);
371  $aSingleLangFields = array();
372 
373  foreach ($aFields as $sFieldName => $sField) {
374  if (preg_match("/(({$sTable}|{$sLangTable})\.)?(?<field>.+)_(?<lang>[0-9]+)$/", $sField, $aMatches)) {
375  if ($aMatches['lang'] == $iLang) {
376  $aSingleLangFields[$aMatches['field']] = $sField;
377  }
378  } else {
379  $aSingleLangFields[$sFieldName] = $sField;
380  }
381  }
382 
383  return $aSingleLangFields;
384  }
385 
392  public function addNewMultilangField($sTable)
393  {
394  $iNewLang = $this->getNextLangId();
395  $this->ensureMultiLanguageFields($sTable, $iNewLang);
396  }
397 
407  public function resetMultilangFields($iLangId, $sTableName)
408  {
409  $iLangId = (int) $iLangId;
410 
411  if ($iLangId === 0) {
412  return;
413  }
414 
415  $aSql = array();
416 
417  $aFields = $this->getMultilangFields($sTableName);
418  if (is_array($aFields) && count($aFields) > 0) {
419  foreach ($aFields as $sFieldName) {
420  $sFieldName = $sFieldName . "_" . $iLangId;
421 
422  if ($this->fieldExists($sFieldName, $sTableName)) {
423  //resetting field value to default
424  $aSql[] = "UPDATE {$sTableName} SET {$sFieldName} = DEFAULT;";
425  }
426  }
427  }
428 
429  if (!empty($aSql)) {
430  $this->executeSql($aSql);
431  }
432  }
433 
438  public function addNewLangToDb()
439  {
440  //reset max count
441  $this->_iCurrentMaxLangId = null;
442 
443  $aTable = $this->getAllTables();
444 
445  foreach ($aTable as $sTableName) {
446  $this->addNewMultilangField($sTableName);
447  }
448 
449  //updating views
450  $this->updateViews();
451  }
452 
461  public function resetLanguage($iLangId)
462  {
463  if ((int) $iLangId === 0) {
464  return;
465  }
466 
467  $aTables = $this->getAllTables();
468 
469  // removing tables which does not requires reset
470  foreach ($this->_aSkipTablesOnReset as $sSkipTable) {
471 
472  if (($iSkipId = array_search($sSkipTable, $aTables)) !== false) {
473  unset($aTables[$iSkipId]);
474  }
475  }
476 
477  foreach ($aTables as $sTableName) {
478  $this->resetMultilangFields($iLangId, $sTableName);
479  }
480  }
481 
487  public function executeSql($aSql)
488  {
489  $oDb = oxDb::getDb();
490 
491  if (is_array($aSql) && !empty($aSql)) {
492  foreach ($aSql as $sSql) {
493  $sSql = trim($sSql);
494  if (!empty($sSql)) {
495  $oDb->execute($sSql);
496  }
497  }
498  }
499  }
500 
508  public function updateViews($aTables = null)
509  {
510  set_time_limit(0);
511 
512  $oDb = oxDb::getDb();
513  $oConfig = oxRegistry::getConfig();
514 
516 
517  $aShops = $oDb->getAll("select * from oxshops");
518 
519  $aTables = $aTables ? $aTables : $oConfig->getConfigParam('aMultiShopTables');
520 
521  $bSuccess = true;
522  foreach ($aShops as $aShop) {
523  $sShopId = $aShop[0];
524  $oShop = oxNew('oxshop');
525  $oShop->load($sShopId);
526  $oShop->setMultiShopTables($aTables);
527  $aMallInherit = array();
528  foreach ($aTables as $sTable) {
529  $aMallInherit[$sTable] = $oConfig->getShopConfVar('blMallInherit_' . $sTable, $sShopId);
530  }
531  if (!$oShop->generateViews(false, $aMallInherit) && $bSuccess) {
532  $bSuccess = false;
533  }
534  }
535 
536  return $bSuccess;
537  }
538 
547  protected function filterCoreFields($aFields)
548  {
549  foreach ($this->aForceOriginalFields as $aFieldname) {
550  if (array_key_exists($aFieldname, $aFields)) {
551  unset($aFields[$aFieldname]);
552  }
553  }
554  return $aFields;
555  }
556 
564  {
565  $iMaxLang = $this->getCurrentMaxLangId();
566  $aMultiLanguageTables = $this->getConfig()->getConfigParam('aMultiLangTables');
567 
568  if (!is_array($aMultiLanguageTables) || empty($aMultiLanguageTables)) {
569  return; //nothing to do
570  }
571 
572  foreach ($aMultiLanguageTables as $sTable) {
573  if ($this->tableExists($sTable)) {
574  //We start with language id 1 and rely on that all fields for language 0 exists.
575  //For language id 0 we have e.g. OXTITLE and logic here would expect it to
576  //be OXTITLE_0, add that as new field, leading to incorrect data in views later on.
577  for ($i=1;$i<=$iMaxLang;$i++) {
578  $this->ensureMultiLanguageFields($sTable, $i);
579  }
580  }
581  }
582  }
583 
592  protected function ensureMultiLanguageFields($sTable, $iLanguageId)
593  {
594  $aFields = $this->getMultilangFields($sTable);
595 
596  $sTableSet = getLangTableName($sTable, $iLanguageId);
597  if (!$this->tableExists($sTableSet)) {
598  $aSql[] = $this->_getCreateTableSetSql($sTable, $iLanguageId);
599  }
600 
601  if (is_array($aFields) && count($aFields) > 0) {
602  foreach ($aFields as $sField) {
603  $sNewFieldName = $sField . "_" . $iLanguageId;
604  if ($iLanguageId > 1) {
605  $iPrevLang = $iLanguageId - 1;
606  $sPrevField = $sField . '_' . $iPrevLang;
607  } else {
608  $sPrevField = $sField;
609  }
610 
611  if (!$this->tableExists($sTableSet) || !$this->fieldExists($sNewFieldName, $sTableSet)) {
612 
613  //getting add field sql
614  $aSql[] = $this->getAddFieldSql($sTable, $sField, $sNewFieldName, $sPrevField, $sTableSet);
615 
616  //getting add index sql on added field
617  $aSql = array_merge($aSql, (array) $this->getAddFieldIndexSql($sTable, $sField, $sNewFieldName, $sTableSet));
618  }
619  }
620  }
621 
622  $this->executeSql($aSql);
623  }
624 }