OXID eShop CE  4.8.11
 All Classes Files Functions Variables Pages
oxdbmetadatahandler.php
Go to the documentation of this file.
1 <?php
2 
8 {
13  protected $_aDbTablesFields = null;
14 
15 
20  protected $_aTables = null;
21 
27 
32  protected $_aSkipTablesOnReset = array( "oxcountry" );
33 
41  public function getFields( $sTableName )
42  {
43  $aFields = array();
44  $aRawFields = oxDb::getDb()->MetaColumns( $sTableName );
45  if (is_array($aRawFields)) {
46  foreach ( $aRawFields as $oField ) {
47  $aFields[] = $oField->name;
48  }
49  }
50  return $aFields;
51  }
52 
60  public function tableExists( $sTableName )
61  {
62  $oDb = oxDb::getDb();
63  $aTables = $oDb->getAll("show tables like ". $oDb->quote($sTableName));
64  return count($aTables) > 0;
65  }
66 
75  public function fieldExists( $sFieldName, $sTableName )
76  {
77  $aTableFields = $this->getFields( $sTableName );
78 
79  if ( is_array($aTableFields) ) {
80  $sFieldName = strtoupper( $sFieldName );
81  $aTableFields = array_map('strtoupper', $aTableFields);
82  if ( in_array( $sFieldName, $aTableFields ) ) {
83  return true;
84  }
85  }
86 
87  return false;
88  }
89 
90 
97  public function getAllTables()
98  {
99  if ( empty($this->_aTables) ) {
100 
101  $aTables = oxDb::getDb()->getAll("show tables");
102 
103  foreach ( $aTables as $aTableInfo) {
104  $sTableName = $aTableInfo[0];
105 
106  $this->_aTables[] = $aTableInfo[0];
107  }
108  }
109  return $this->_aTables;
110  }
111 
119  public function getAllMultiTables($sTable)
120  {
121  $aMLTables = array();
122  foreach (array_keys(oxRegistry::getLang()->getLanguageIds()) as $iLangId) {
123  $sLangTableName = getLangTableName($sTable, $iLangId );
124  if ($sTable != $sLangTableName && !in_array($sLangTableName, $aMLTables)) {
125  $aMLTables[] = $sLangTableName;
126  }
127  }
128  return $aMLTables;
129  }
130 
140  protected function _getCreateTableSetSql( $sTable, $iLang)
141  {
142  $sTableSet = getLangTableName($sTable, $iLang);
143 
144  $aRes = oxDb::getDb()->getAll( "show create table {$sTable}" );
145  $sSql = "CREATE TABLE `{$sTableSet}` (".
146  "`OXID` char(32) COLLATE latin1_general_ci NOT NULL, ".
147  "PRIMARY KEY (`OXID`)".
148  ") ".strstr($aRes[0][1], 'ENGINE=');
149  return $sSql;
150  }
151 
163  public function getAddFieldSql( $sTable, $sField, $sNewField, $sPrevField, $sTableSet = null )
164  {
165  if (!$sTableSet) {
166  $sTableSet = $sTable;
167  }
168  $aRes = oxDb::getDb()->getAll( "show create table {$sTable}" );
169  $sTableSql = $aRes[0][1];
170 
171  // removing comments;
172  $sTableSql = preg_replace('/COMMENT \\\'.*?\\\'/', '', $sTableSql);
173  preg_match( "/.*,\s+(['`]?".preg_quote($sField, '/')."['`]?\s+[^,]+),.*/", $sTableSql, $aMatch );
174  $sFieldSql = $aMatch[1];
175 
176  $sSql = "";
177  if ( !empty($sFieldSql) ) {
178  $sFieldSql = preg_replace( "/".preg_quote($sField, '/')."/", $sNewField, $sFieldSql );
179  $sSql = "ALTER TABLE `$sTableSet` ADD " . $sFieldSql;
180  if ($this->tableExists($sTableSet) && $this->fieldExists($sPrevField, $sTableSet)) {
181  $sSql .= " AFTER `$sPrevField`";
182  }
183  }
184  return $sSql;
185  }
186 
187 
188 
199  public function getAddFieldIndexSql( $sTable, $sField, $sNewField, $sTableSet = null )
200  {
201  $aRes = oxDb::getDb()->getAll( "show create table {$sTable}" );
202 
203  $sTableSql = $aRes[0][1];
204 
205  preg_match_all("/([\w]+\s+)?\bKEY\s+(`[^`]+`)?\s*\([^)]+\)/iU", $sTableSql, $aMatch);
206  $aIndex = $aMatch[0];
207 
208  $blUsingTableSet = $sTableSet ? true : false;
209 
210  if (!$sTableSet) {
211  $sTableSet = $sTable;
212  }
213 
214  $aIndexSql = array();
215  $aSql = array();
216  if ( count($aIndex) ) {
217  foreach ( $aIndex as $sIndexSql ) {
218  if ( preg_match("/\([^)]*\b" . $sField . "\b[^)]*\)/i", $sIndexSql ) ) {
219 
220  //removing index name - new will be added automaticly
221  $sIndexSql = preg_replace("/(.*\bKEY\s+)`[^`]+`/", "$1", $sIndexSql );
222 
223  if ( $blUsingTableSet ) {
224  // replacing multiple fields to one (#3269)
225  $sIndexSql = preg_replace("/\([^\)]+\)/", "(`$sNewField`)", $sIndexSql );
226  } else {
227  //replacing previous field name with new one
228  $sIndexSql = preg_replace("/\b" . $sField . "\b/", $sNewField, $sIndexSql );
229  }
230 
231  $aIndexSql[] = "ADD ". $sIndexSql;
232  }
233  }
234  if ( count($aIndexSql) ) {
235  $aSql = array("ALTER TABLE `$sTableSet` ".implode(", ", $aIndexSql));
236  }
237  }
238 
239  return $aSql;
240  }
241 
248  public function getCurrentMaxLangId()
249  {
250  if ( isset($this->_iCurrentMaxLangId) ) {
252  }
253 
254  $sTable = $sTableSet = "oxarticles";
255  $sField = $sFieldSet = "oxtitle";
256  $iLang = 0;
257  while ($this->tableExists($sTableSet) && $this->fieldExists($sFieldSet, $sTableSet)) {
258  $iLang ++;
259  $sTableSet = getLangTableName($sTable, $iLang);
260  $sFieldSet = $sField.'_'.$iLang;
261  }
262 
263  $this->_iCurrentMaxLangId = --$iLang;
265  }
266 
272  public function getNextLangId()
273  {
274  return $this->getCurrentMaxLangId() + 1;
275  }
276 
284  public function getMultilangFields( $sTable )
285  {
286  $aFields = $this->getFields( $sTable );
287  $aMultiLangFields = array();
288 
289  foreach ( $aFields as $sField ) {
290  if ( preg_match("/(.+)_1$/", $sField, $aMatches) ) {
291  $aMultiLangFields[] = $aMatches[1];
292  }
293  }
294 
295  return $aMultiLangFields;
296  }
297 
306  public function getSinglelangFields( $sTable, $iLang )
307  {
308  $aFields = array_merge($this->getFields( $sTable ), $this->getFields(getLangTableName($sTable, $iLang) ));
309  $aSingleLangFields = array();
310 
311  foreach ( $aFields as $sField ) {
312  if ( preg_match("/(.+)_([0-9]+)$/", $sField, $aMatches) ) {
313  if ($aMatches[2] == $iLang) {
314  $aSingleLangFields[$aMatches[1]] = $sField;
315  }
316  } else {
317  $aSingleLangFields[$sField] = $sField;
318  }
319  }
320 
321  return $aSingleLangFields;
322  }
323 
332  public function addNewMultilangField( $sTable )
333  {
334  $aSql = array();
335  $aFields = $this->getMultilangFields($sTable);
336  $iMaxLang = $this->getCurrentMaxLangId();
337  $iNewLang = $this->getNextLangId();
338 
339  $sTableSet = getLangTableName($sTable, $iNewLang);
340  if (!$this->tableExists($sTableSet)) {
341  $aSql[] = $this->_getCreateTableSetSql( $sTable, $iNewLang );
342  }
343 
344  if ( is_array($aFields) && count($aFields) > 0 ) {
345  foreach ( $aFields as $sField ) {
346  $sNewFieldName = $sField . "_" . $iNewLang;
347  if ($iNewLang>1) {
348  $iPrevLang = $iNewLang-1;
349  $sPrevField = $sField.'_'.$iPrevLang;
350  } else {
351  $sPrevField = $sField;
352  }
353 
354  if ( !$this->tableExists($sTableSet) || !$this->fieldExists( $sNewFieldName, $sTableSet ) ) {
355 
356  //getting add field sql
357  $aSql[] = $this->getAddFieldSql( $sTable, $sField, $sNewFieldName, $sPrevField, $sTableSet );
358 
359  //getting add index sql on added field
360  $aSql = array_merge($aSql, (array) $this->getAddFieldIndexSql($sTable, $sField, $sNewFieldName, $sTableSet));
361  }
362  }
363  }
364 
365  $this->executeSql($aSql);
366  }
367 
377  public function resetMultilangFields( $iLangId, $sTableName )
378  {
379  $iLangId = (int)$iLangId;
380 
381  if ( $iLangId === 0 ) {
382  return;
383  }
384 
385  $aSql = array();
386 
387  $aFields = $this->getMultilangFields( $sTableName );
388  if ( is_array($aFields) && count($aFields) > 0 ) {
389  foreach ( $aFields as $sFieldName ) {
390  $sFieldName = $sFieldName . "_" . $iLangId;
391 
392  if ( $this->fieldExists( $sFieldName, $sTableName ) ) {
393  //reseting field value to default
394  $aSql[] = "UPDATE {$sTableName} SET {$sFieldName} = DEFAULT;";
395  }
396  }
397  }
398 
399  if ( !empty($aSql) ) {
400  $this->executeSql( $aSql );
401  }
402  }
403 
410  public function addNewLangToDb()
411  {
412  //reset max count
413  $this->_iCurrentMaxLangId = null;
414 
415  $aTable = $this->getAllTables();
416 
417  foreach ( $aTable as $sTableName ) {
418  $this->addNewMultilangField( $sTableName );
419  }
420 
421  //updating views
422  $this->updateViews();
423  }
424 
433  public function resetLanguage( $iLangId )
434  {
435  if ( (int)$iLangId === 0 ) {
436  return;
437  }
438 
439  $aTables = $this->getAllTables();
440 
441  // removing tables which does not requires reset
442  foreach ( $this->_aSkipTablesOnReset as $sSkipTable ) {
443 
444  if ( ($iSkipId = array_search( $sSkipTable, $aTables )) !== false ) {
445  unset( $aTables[$iSkipId] );
446  }
447  }
448 
449  foreach ( $aTables as $sTableName ) {
450  $this->resetMultilangFields( $iLangId, $sTableName );
451  }
452  }
453 
461  public function executeSql( $aSql )
462  {
463  $oDb = oxDb::getDb();
464 
465  if ( is_array($aSql) && !empty($aSql) ) {
466  foreach ( $aSql as $sSql) {
467  $sSql = trim($sSql);
468  if (!empty($sSql)) {
469  $oDb->execute( $sSql );
470  }
471  }
472  }
473  }
474 
482  public function updateViews( $aTables = null )
483  {
484  set_time_limit( 0 );
485 
486  $oDb = oxDb::getDb();
487  $oConfig = oxRegistry::getConfig();
488 
489  $aShops = $oDb->getAll( "select * from oxshops" );
490 
491  $aTables = $aTables ? $aTables : $oConfig->getConfigParam( 'aMultiShopTables' );
492 
493  $bSuccess = true;
494  foreach ( $aShops as $aShop ) {
495  $sShopId = $aShop[0];
496  $oShop = oxNew( 'oxshop' );
497  $oShop->load( $sShopId );
498  $oShop->setMultiShopTables( $aTables );
499  $blMultishopInherit = $oConfig->getShopConfVar( 'blMultishopInherit_oxcategories', $sShopId );
500  $aMallInherit = array();
501  foreach ( $aTables as $sTable ) {
502  $aMallInherit[$sTable] = $oConfig->getShopConfVar( 'blMallInherit_' . $sTable, $sShopId );
503  }
504  if ( !$oShop->generateViews( $blMultishopInherit, $aMallInherit ) && $bSuccess ) {
505  $bSuccess = false;
506  }
507  }
508 
509  return $bSuccess;
510  }
511 }
512