OXID eShop CE  4.10.0
 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 
107  public function getAllTables()
108  {
109  if (empty($this->_aTables)) {
110 
111  $aTables = oxDb::getDb()->getAll("show tables");
112 
113  foreach ($aTables as $aTableInfo) {
114  $sTableName = $aTableInfo[0];
115 
116  $this->_aTables[] = $aTableInfo[0];
117  }
118  }
119 
120  return $this->_aTables;
121  }
122 
130  public function getAllMultiTables($sTable)
131  {
132  $aMLTables = array();
133  foreach (array_keys(oxRegistry::getLang()->getLanguageIds()) as $iLangId) {
134  $sLangTableName = getLangTableName($sTable, $iLangId);
135  if ($sTable != $sLangTableName && !in_array($sLangTableName, $aMLTables)) {
136  $aMLTables[] = $sLangTableName;
137  }
138  }
139 
140  return $aMLTables;
141  }
142 
152  protected function _getCreateTableSetSql($sTable, $iLang)
153  {
154  $sTableSet = getLangTableName($sTable, $iLang);
155 
156  $aRes = oxDb::getDb()->getAll("show create table {$sTable}");
157  $sSql = "CREATE TABLE `{$sTableSet}` (" .
158  "`OXID` char(32) COLLATE latin1_general_ci NOT NULL, " .
159  "PRIMARY KEY (`OXID`)" .
160  ") " . strstr($aRes[0][1], 'ENGINE=');
161 
162  return $sSql;
163  }
164 
176  public function getAddFieldSql($sTable, $sField, $sNewField, $sPrevField, $sTableSet = null)
177  {
178  if (!$sTableSet) {
179  $sTableSet = $sTable;
180  }
181  $aRes = oxDb::getDb()->getAll("show create table {$sTable}");
182  $sTableSql = $aRes[0][1];
183 
184  // removing comments;
185  $sTableSql = preg_replace('/COMMENT \\\'.*?\\\'/', '', $sTableSql);
186  preg_match("/.*,\s+(['`]?" . preg_quote($sField, '/') . "['`]?\s+[^,]+),.*/", $sTableSql, $aMatch);
187  $sFieldSql = $aMatch[1];
188 
189  $sSql = "";
190  if (!empty($sFieldSql)) {
191  $sFieldSql = preg_replace("/" . preg_quote($sField, '/') . "/", $sNewField, $sFieldSql);
192  $sSql = "ALTER TABLE `$sTableSet` ADD " . $sFieldSql;
193  if ($this->tableExists($sTableSet) && $this->fieldExists($sPrevField, $sTableSet)) {
194  $sSql .= " AFTER `$sPrevField`";
195  }
196  }
197 
198  return $sSql;
199  }
200 
201 
212  public function getAddFieldIndexSql($sTable, $sField, $sNewField, $sTableSet = null)
213  {
214  $aRes = oxDb::getDb()->getAll("show create table {$sTable}");
215 
216  $sTableSql = $aRes[0][1];
217 
218  preg_match_all("/([\w]+\s+)?\bKEY\s+(`[^`]+`)?\s*\([^)]+\)/iU", $sTableSql, $aMatch);
219  $aIndex = $aMatch[0];
220 
221  $blUsingTableSet = $sTableSet ? true : false;
222 
223  if (!$sTableSet) {
224  $sTableSet = $sTable;
225  }
226 
227  $aIndexSql = array();
228  $aSql = array();
229  if (count($aIndex)) {
230  foreach ($aIndex as $sIndexSql) {
231  if (preg_match("/\([^)]*\b" . $sField . "\b[^)]*\)/i", $sIndexSql)) {
232 
233  //removing index name - new will be added automaticly
234  $sIndexSql = preg_replace("/(.*\bKEY\s+)`[^`]+`/", "$1", $sIndexSql);
235 
236  if ($blUsingTableSet) {
237  // replacing multiple fields to one (#3269)
238  $sIndexSql = preg_replace("/\([^\)]+\)/", "(`$sNewField`)", $sIndexSql);
239  } else {
240  //replacing previous field name with new one
241  $sIndexSql = preg_replace("/\b" . $sField . "\b/", $sNewField, $sIndexSql);
242  }
243 
244  $aIndexSql[] = "ADD " . $sIndexSql;
245  }
246  }
247  if (count($aIndexSql)) {
248  $aSql = array("ALTER TABLE `$sTableSet` " . implode(", ", $aIndexSql));
249  }
250  }
251 
252  return $aSql;
253  }
254 
261  public function getCurrentMaxLangId()
262  {
263  if (isset($this->_iCurrentMaxLangId)) {
265  }
266 
267  $sTable = $sTableSet = "oxarticles";
268  $sField = $sFieldSet = "oxtitle";
269  $iLang = 0;
270  while ($this->tableExists($sTableSet) && $this->fieldExists($sFieldSet, $sTableSet)) {
271  $iLang++;
272  $sTableSet = getLangTableName($sTable, $iLang);
273  $sFieldSet = $sField . '_' . $iLang;
274  }
275 
276  $this->_iCurrentMaxLangId = --$iLang;
277 
279  }
280 
286  public function getNextLangId()
287  {
288  return $this->getCurrentMaxLangId() + 1;
289  }
290 
298  public function getMultilangFields($sTable)
299  {
300  $aFields = $this->getFields($sTable);
301  $aMultiLangFields = array();
302 
303  foreach ($aFields as $sField) {
304  if (preg_match("/({$sTable}\.)?(?<field>.+)_1$/", $sField, $aMatches)) {
305  $aMultiLangFields[] = $aMatches['field'];
306  }
307  }
308 
309  return $aMultiLangFields;
310  }
311 
320  public function getSinglelangFields($sTable, $iLang)
321  {
322  $sLangTable = getLangTableName($sTable, $iLang);
323 
324  $aBaseFields = $this->getFields($sTable);
325  $aLangFields = $this->getFields($sLangTable);
326 
327  //Some fields (for example OXID) must be taken from core table.
328  $aLangFields = $this->filterCoreFields($aLangFields);
329 
330  $aFields = array_merge($aBaseFields, $aLangFields);
331  $aSingleLangFields = array();
332 
333  foreach ($aFields as $sFieldName => $sField) {
334  if (preg_match("/(({$sTable}|{$sLangTable})\.)?(?<field>.+)_(?<lang>[0-9]+)$/", $sField, $aMatches)) {
335  if ($aMatches['lang'] == $iLang) {
336  $aSingleLangFields[$aMatches['field']] = $sField;
337  }
338  } else {
339  $aSingleLangFields[$sFieldName] = $sField;
340  }
341  }
342 
343  return $aSingleLangFields;
344  }
345 
352  public function addNewMultilangField($sTable)
353  {
354  $iNewLang = $this->getNextLangId();
355  $this->ensureMultiLanguageFields($sTable, $iNewLang);
356  }
357 
367  public function resetMultilangFields($iLangId, $sTableName)
368  {
369  $iLangId = (int) $iLangId;
370 
371  if ($iLangId === 0) {
372  return;
373  }
374 
375  $aSql = array();
376 
377  $aFields = $this->getMultilangFields($sTableName);
378  if (is_array($aFields) && count($aFields) > 0) {
379  foreach ($aFields as $sFieldName) {
380  $sFieldName = $sFieldName . "_" . $iLangId;
381 
382  if ($this->fieldExists($sFieldName, $sTableName)) {
383  //resetting field value to default
384  $aSql[] = "UPDATE {$sTableName} SET {$sFieldName} = DEFAULT;";
385  }
386  }
387  }
388 
389  if (!empty($aSql)) {
390  $this->executeSql($aSql);
391  }
392  }
393 
398  public function addNewLangToDb()
399  {
400  //reset max count
401  $this->_iCurrentMaxLangId = null;
402 
403  $aTable = $this->getAllTables();
404 
405  foreach ($aTable as $sTableName) {
406  $this->addNewMultilangField($sTableName);
407  }
408 
409  //updating views
410  $this->updateViews();
411  }
412 
421  public function resetLanguage($iLangId)
422  {
423  if ((int) $iLangId === 0) {
424  return;
425  }
426 
427  $aTables = $this->getAllTables();
428 
429  // removing tables which does not requires reset
430  foreach ($this->_aSkipTablesOnReset as $sSkipTable) {
431 
432  if (($iSkipId = array_search($sSkipTable, $aTables)) !== false) {
433  unset($aTables[$iSkipId]);
434  }
435  }
436 
437  foreach ($aTables as $sTableName) {
438  $this->resetMultilangFields($iLangId, $sTableName);
439  }
440  }
441 
447  public function executeSql($aSql)
448  {
449  $oDb = oxDb::getDb();
450 
451  if (is_array($aSql) && !empty($aSql)) {
452  foreach ($aSql as $sSql) {
453  $sSql = trim($sSql);
454  if (!empty($sSql)) {
455  $oDb->execute($sSql);
456  }
457  }
458  }
459  }
460 
468  public function updateViews($aTables = null)
469  {
470  set_time_limit(0);
471 
472  $oDb = oxDb::getDb();
473  $oConfig = oxRegistry::getConfig();
474 
476 
477  $aShops = $oDb->getAll("select * from oxshops");
478 
479  $aTables = $aTables ? $aTables : $oConfig->getConfigParam('aMultiShopTables');
480 
481  $bSuccess = true;
482  foreach ($aShops as $aShop) {
483  $sShopId = $aShop[0];
484  $oShop = oxNew('oxshop');
485  $oShop->load($sShopId);
486  $oShop->setMultiShopTables($aTables);
487  $aMallInherit = array();
488  foreach ($aTables as $sTable) {
489  $aMallInherit[$sTable] = $oConfig->getShopConfVar('blMallInherit_' . $sTable, $sShopId);
490  }
491  if (!$oShop->generateViews(false, $aMallInherit) && $bSuccess) {
492  $bSuccess = false;
493  }
494  }
495 
496  return $bSuccess;
497  }
498 
507  protected function filterCoreFields($aFields)
508  {
509  foreach ($this->aForceOriginalFields as $aFieldname) {
510  if (array_key_exists($aFieldname, $aFields)) {
511  unset($aFields[$aFieldname]);
512  }
513  }
514  return $aFields;
515  }
516 
524  {
525  $iMaxLang = $this->getCurrentMaxLangId();
526  $aMultiLanguageTables = $this->getConfig()->getConfigParam('aMultiLangTables');
527 
528  if (!is_array($aMultiLanguageTables) || empty($aMultiLanguageTables)) {
529  return; //nothing to do
530  }
531 
532  foreach ($aMultiLanguageTables as $sTable) {
533  if ($this->tableExists($sTable)) {
534  //We start with language id 1 and rely on that all fields for language 0 exists.
535  //For language id 0 we have e.g. OXTITLE and logic here would expect it to
536  //be OXTITLE_0, add that as new field, leading to incorrect data in views later on.
537  for ($i=1;$i<=$iMaxLang;$i++) {
538  $this->ensureMultiLanguageFields($sTable, $i);
539  }
540  }
541  }
542  }
543 
552  protected function ensureMultiLanguageFields($sTable, $iLanguageId)
553  {
554  $aFields = $this->getMultilangFields($sTable);
555 
556  $sTableSet = getLangTableName($sTable, $iLanguageId);
557  if (!$this->tableExists($sTableSet)) {
558  $aSql[] = $this->_getCreateTableSetSql($sTable, $iLanguageId);
559  }
560 
561  if (is_array($aFields) && count($aFields) > 0) {
562  foreach ($aFields as $sField) {
563  $sNewFieldName = $sField . "_" . $iLanguageId;
564  if ($iLanguageId > 1) {
565  $iPrevLang = $iLanguageId - 1;
566  $sPrevField = $sField . '_' . $iPrevLang;
567  } else {
568  $sPrevField = $sField;
569  }
570 
571  if (!$this->tableExists($sTableSet) || !$this->fieldExists($sNewFieldName, $sTableSet)) {
572 
573  //getting add field sql
574  $aSql[] = $this->getAddFieldSql($sTable, $sField, $sNewFieldName, $sPrevField, $sTableSet);
575 
576  //getting add index sql on added field
577  $aSql = array_merge($aSql, (array) $this->getAddFieldIndexSql($sTable, $sField, $sNewFieldName, $sTableSet));
578  }
579  }
580  }
581 
582  $this->executeSql($aSql);
583  }
584 }