00001 <?php
00002
00007 class oxDbMetaDataHandler extends oxSuperCfg
00008 {
00013 protected $_aDbTablesFields = null;
00014
00019 protected $_aTables = null;
00020
00025 protected $_iCurrentMaxLangId;
00026
00031 protected $_aSkipTablesOnReset = array( "oxcountry" );
00032
00040 public function getFields( $sTableName )
00041 {
00042 $aFields = array();
00043 $aRawFields = oxDb::getDb()->MetaColumns( $sTableName );
00044 if (is_array($aRawFields)) {
00045 foreach ( $aRawFields as $oField ) {
00046 $aFields[] = $oField->name;
00047 }
00048 }
00049 return $aFields;
00050 }
00051
00059 public function tableExists( $sTableName )
00060 {
00061 $aTables = oxDb::getDb()->getAll("show tables like ".oxDb::getDb()->quote($sTableName));
00062 return count($aTables) > 0;
00063 }
00064
00073 public function fieldExists( $sFieldName, $sTableName )
00074 {
00075 $aTableFields = $this->getFields( $sTableName );
00076
00077 if ( is_array($aTableFields) ) {
00078 $sFieldName = strtoupper( $sFieldName );
00079 if ( in_array( $sFieldName, $aTableFields ) ) {
00080 return true;
00081 }
00082 }
00083
00084 return false;
00085 }
00086
00087
00094 public function getAllTables()
00095 {
00096 if ( empty($this->_aTables) ) {
00097
00098 $aTables = oxDb::getDb()->getAll("show tables");
00099
00100 foreach ( $aTables as $aTableInfo) {
00101 $sTableName = $aTableInfo[0];
00102
00103 $this->_aTables[] = $aTableInfo[0];
00104 }
00105 }
00106 return $this->_aTables;
00107 }
00108
00116 public function getAllMultiTables($sTable)
00117 {
00118 $aMLTables = array();
00119 foreach (array_keys(oxLang::getInstance()->getLanguageIds()) as $iLangId) {
00120 $sLangTableName = getLangTableName($sTable, $iLangId );
00121 if ($sTable != $sLangTableName && !in_array($sLangTableName, $aMLTables)) {
00122 $aMLTables[] = $sLangTableName;
00123 }
00124 }
00125 return $aMLTables;
00126 }
00127
00137 protected function _getCreateTableSetSql( $sTable, $iLang)
00138 {
00139 $sTableSet = getLangTableName($sTable, $iLang);
00140
00141 $aRes = oxDb::getDb()->getAll("show create table {$sTable}");
00142 $sSql = "CREATE TABLE `{$sTableSet}` (".
00143 "`OXID` char(32) COLLATE latin1_general_ci NOT NULL, ".
00144 "PRIMARY KEY (`OXID`)".
00145 ") ".strstr($aRes[0][1], 'ENGINE=');
00146 return $sSql;
00147 }
00148
00158 protected function _getAddFieldSql( $sTable, $sField, $iLang )
00159 {
00160 $sTableSet = getLangTableName($sTable, $iLang);
00161 $sNewField = $sField.'_'.$iLang;
00162
00163 if ($iLang>1) {
00164 $iPrevLang = $iLang-1;
00165 $sPrevField = $sField.'_'.$iPrevLang;
00166 } else {
00167 $sPrevField = $sField;
00168 }
00169
00170 $aRes = oxDb::getDb()->getAll("show create table {$sTable}");
00171 $sTableSql = $aRes[0][1];
00172
00173 preg_match( "/.*,\s+(['`]?".preg_quote($sField, '/')."['`]?\s+[^,]+),.*/", $sTableSql, $aMatch );
00174 $sFieldSql = $aMatch[1];
00175
00176 $sSql = "";
00177 if ( !empty($sFieldSql) ) {
00178 $sFieldSql = preg_replace( "/".preg_quote($sField, '/')."/", $sNewField, $sFieldSql );
00179 $sSql = "ALTER TABLE `$sTableSet` ADD " . $sFieldSql;
00180 if ($this->tableExists($sTableSet) && $this->fieldExists($sPrevField, $sTableSet)) {
00181 $sSql .= " AFTER `$sPrevField`";
00182 }
00183 }
00184 return $sSql;
00185 }
00186
00187
00188
00198 protected function _getAddFieldIndexSql( $sTable, $sField, $iLang )
00199 {
00200 $sTableSet = getLangTableName($sTable, $iLang);
00201 $sNewField = $sField.'_'.$iLang;
00202
00203 if ($iLang>1) {
00204 $iPrevLang = $iLang-1;
00205 $sPrevField = $sField.'_'.$iPrevLang;
00206 } else {
00207 $sPrevField = $sField;
00208 }
00209
00210 $aRes = oxDb::getDb()->getAll("show create table {$sTable}");
00211 $sTableSql = $aRes[0][1];
00212
00213 preg_match_all("/([\w]+\s+)?\bKEY\s+(`[^`]+`)?\s*\([^)]+\)/iU", $sTableSql, $aMatch);
00214 $aIndex = $aMatch[0];
00215
00216 $aIndexSql = array();
00217 if ( count($aIndex) ) {
00218 foreach ( $aIndex as $sIndexSql ) {
00219 if ( preg_match("/\([^)]*\b" . $sPrevField . "\b[^)]*\)/i", $sIndexSql ) ) {
00220
00221
00222 $sIndexSql = preg_replace("/(.*\bKEY\s+)`[^`]+`/", "$1", $sIndexSql );
00223
00224
00225 $sIndexSql = preg_replace("/\b" . $sPrevField . "\b/", $sNewField, $sIndexSql );
00226
00227 $aIndexSql[] = "ALTER TABLE `$sTableSet` ADD ". $sIndexSql;
00228 }
00229 }
00230 }
00231
00232 return $aIndexSql;
00233 }
00234
00241 public function getCurrentMaxLangId()
00242 {
00243 if ( isset($this->_iCurrentMaxLangId) ) {
00244 return $this->_iCurrentMaxLangId;
00245 }
00246
00247 $sTable = $sTableSet = "oxarticles";
00248 $sField = $sFieldSet = "oxtitle";
00249 $iLang = 0;
00250 while ($this->tableExists($sTableSet) && $this->fieldExists($sFieldSet, $sTableSet)) {
00251 $iLang ++;
00252 $sTableSet = getLangTableName($sTable, $iLang);
00253 $sFieldSet = $sField.'_'.$iLang;
00254 }
00255
00256 $this->_iCurrentMaxLangId = --$iLang;
00257 return $this->_iCurrentMaxLangId;
00258 }
00259
00265 public function getNextLangId()
00266 {
00267 return $this->getCurrentMaxLangId() + 1;
00268 }
00269
00277 public function getMultilangFields( $sTable )
00278 {
00279 $aFields = $this->getFields( $sTable );
00280 $aMultiLangFields = array();
00281
00282 foreach ( $aFields as $sField ) {
00283 if ( preg_match("/(.+)_1$/", $sField, $aMatches) ) {
00284 $aMultiLangFields[] = $aMatches[1];
00285 }
00286 }
00287
00288 return $aMultiLangFields;
00289 }
00290
00299 public function getSinglelangFields( $sTable, $iLang )
00300 {
00301 $aFields = array_merge($this->getFields( $sTable ), $this->getFields(getLangTableName($sTable, $iLang) ));
00302 $aSingleLangFields = array();
00303
00304 foreach ( $aFields as $sField ) {
00305 if ( preg_match("/(.+)_([0-9]+)$/", $sField, $aMatches) ) {
00306 if ($aMatches[2] == $iLang) {
00307 $aSingleLangFields[$aMatches[1]] = $sField;
00308 }
00309 } else {
00310 $aSingleLangFields[$sField] = $sField;
00311 }
00312 }
00313
00314 return $aSingleLangFields;
00315 }
00316
00325 public function addNewMultilangField( $sTable )
00326 {
00327 $aSql = array();
00328 $aFields = $this->getMultilangFields($sTable);
00329 $iMaxLang = $this->getCurrentMaxLangId();
00330 $iNewLang = $this->getNextLangId();
00331
00332 $sTableSet = getLangTableName($sTable, $iNewLang);
00333 if (!$this->tableExists($sTableSet)) {
00334 $aSql[] = $this->_getCreateTableSetSql( $sTable, $iNewLang );
00335 }
00336
00337 if ( is_array($aFields) && count($aFields) > 0 ) {
00338 foreach ( $aFields as $sField ) {
00339 $sNewFieldName = $sField . "_" . $iNewLang;
00340 if ( !$this->fieldExists( $sNewFieldName, $sTable ) ) {
00341
00342
00343 $aSql[] = $this->_getAddFieldSql($sTable, $sField, $iNewLang);
00344
00345
00346 $aSql = array_merge($aSql, (array) $this->_getAddFieldIndexSql($sTable, $sField, $iNewLang));
00347 }
00348 }
00349 }
00350 $this->_executeSql($aSql);
00351 }
00352
00362 public function resetMultilangFields( $iLangId, $sTableName )
00363 {
00364 $iLangId = (int)$iLangId;
00365
00366 if ( $iLangId === 0 ) {
00367 return;
00368 }
00369
00370 $aSql = array();
00371
00372 $aFields = $this->getMultilangFields( $sTableName );
00373 if ( is_array($aFields) && count($aFields) > 0 ) {
00374 foreach ( $aFields as $sFieldName ) {
00375 $sFieldName = $sFieldName . "_" . $iLangId;
00376
00377 if ( $this->fieldExists( $sFieldName, $sTableName ) ) {
00378
00379 $aSql[] = "UPDATE {$sTableName} SET {$sFieldName} = DEFAULT;";
00380 }
00381 }
00382 }
00383
00384 if ( !empty($aSql) ) {
00385 $this->_executeSql( $aSql );
00386 }
00387 }
00388
00395 public function addNewLangToDb()
00396 {
00397
00398 $this->_iCurrentMaxLangId = null;
00399
00400 $aTable = $this->getAllTables();
00401
00402 foreach ( $aTable as $sTableName ) {
00403 $this->addNewMultilangField( $sTableName );
00404 }
00405
00406
00407 $this->updateViews();
00408 }
00409
00418 public function resetLanguage( $iLangId )
00419 {
00420 if ( (int)$iLangId === 0 ) {
00421 return;
00422 }
00423
00424 $aTables = $this->getAllTables();
00425
00426
00427 foreach ( $this->_aSkipTablesOnReset as $sSkipTable ) {
00428
00429 if ( ($iSkipId = array_search( $sSkipTable, $aTables )) !== false ) {
00430 unset( $aTables[$iSkipId] );
00431 }
00432 }
00433
00434 foreach ( $aTables as $sTableName ) {
00435 $this->resetMultilangFields( $iLangId, $sTableName );
00436 }
00437 }
00438
00446 protected function _executeSql( $aSql )
00447 {
00448 $oDb = oxDb::getDb();
00449
00450 if ( is_array($aSql) && !empty($aSql) ) {
00451 foreach ( $aSql as $sSql) {
00452 $sSql = trim($sSql);
00453 if (!empty($sSql)) {
00454 $oDb->execute( $sSql );
00455 }
00456 }
00457 }
00458 }
00459
00465 public function updateViews()
00466 {
00467
00468 if ( oxSession::getVar("malladmin") ) {
00469 oxDb::getInstance()->updateViews();
00470 }
00471 }
00472
00473 }
00474