oxdbmetadatahandler.php

Go to the documentation of this file.

<?php

class oxDbMetaDataHandler extends oxSuperCfg
{
    protected $_aDbTablesFields = null;


    protected $_aTables = null;

    protected $_iCurrentMaxLangId;

    protected $_aSkipTablesOnReset = array( "oxcountry" );

    public function getFields( $sTableName )
    {
        $aFields    = array();
        $aRawFields = oxDb::getDb()->MetaColumns( $sTableName );
        if (is_array($aRawFields)) {
            foreach ( $aRawFields as $oField ) {
                $aFields[] = $oField->name;
            }
        }
        return $aFields;
    }

    public function tableExists( $sTableName )
    {
        $aTables = oxDb::getDb()->getAll("show tables like ".oxDb::getDb()->quote($sTableName));
        return count($aTables) > 0;
    }

    public function fieldExists( $sFieldName, $sTableName )
    {
        $aTableFields = $this->getFields( $sTableName );

        if ( is_array($aTableFields) ) {
            $sFieldName = strtoupper( $sFieldName );
            $aTableFields = array_map('strtoupper', $aTableFields);
            if ( in_array( $sFieldName, $aTableFields ) ) {
                return true;
            }
        }

        return false;
    }


    public function getAllTables()
    {
        if ( empty($this->_aTables) ) {

            $aTables = oxDb::getDb()->getAll("show tables");

            foreach ( $aTables as $aTableInfo) {
                $sTableName = $aTableInfo[0];

                $this->_aTables[] = $aTableInfo[0];
            }
        }
        return $this->_aTables;
    }

    public function getAllMultiTables($sTable)
    {
        $aMLTables = array();
        foreach (array_keys(oxLang::getInstance()->getLanguageIds()) as $iLangId) {
            $sLangTableName = getLangTableName($sTable, $iLangId );
            if ($sTable != $sLangTableName && !in_array($sLangTableName, $aMLTables)) {
                $aMLTables[] = $sLangTableName;
            }
        }
        return $aMLTables;
    }

    protected function _getCreateTableSetSql( $sTable, $iLang)
    {
        $sTableSet = getLangTableName($sTable, $iLang);

        $aRes = oxDb::getDb()->getAll("show create table {$sTable}");
        $sSql = "CREATE TABLE `{$sTableSet}` (".
                "`OXID` char(32) COLLATE latin1_general_ci NOT NULL, ".
                "PRIMARY KEY (`OXID`)".
                ") ".strstr($aRes[0][1], 'ENGINE=');
        return $sSql;
    }

    protected function _getAddFieldSql( $sTable, $sField, $iLang )
    {
        $sTableSet = getLangTableName($sTable, $iLang);
        $sNewField = $sField.'_'.$iLang;

        if ($iLang>1) {
            $iPrevLang = $iLang-1;
            $sPrevField = $sField.'_'.$iPrevLang;
        } else {
            $sPrevField = $sField;
        }

        $aRes = oxDb::getDb()->getAll("show create table {$sTable}");
        $sTableSql = $aRes[0][1];

        preg_match( "/.*,\s+(['`]?".preg_quote($sField, '/')."['`]?\s+[^,]+),.*/", $sTableSql, $aMatch );
        $sFieldSql = $aMatch[1];

        $sSql = "";
        if ( !empty($sFieldSql) ) {
            $sFieldSql = preg_replace( "/".preg_quote($sField, '/')."/", $sNewField, $sFieldSql );
            $sSql = "ALTER TABLE `$sTableSet` ADD " . $sFieldSql;
            if ($this->tableExists($sTableSet) && $this->fieldExists($sPrevField, $sTableSet)) {
                $sSql .= " AFTER `$sPrevField`";
            }
        }
        return $sSql;
    }



    protected function _getAddFieldIndexSql( $sTable, $sField, $iLang )
    {
        $sTableSet = getLangTableName($sTable, $iLang);
        $sNewField = $sField.'_'.$iLang;

        $aRes = oxDb::getDb()->getAll("show create table {$sTable}");
        $sTableSql = $aRes[0][1];

        preg_match_all("/([\w]+\s+)?\bKEY\s+(`[^`]+`)?\s*\([^)]+\)/iU", $sTableSql, $aMatch);
        $aIndex = $aMatch[0];

        $aIndexSql = array();
        if ( count($aIndex) ) {
            foreach ( $aIndex as $sIndexSql ) {
                if ( preg_match("/\([^)]*\b" . $sField . "\b[^)]*\)/i", $sIndexSql )  ) {

                    //removing index name - new will be added automaticly
                    $sIndexSql = preg_replace("/(.*\bKEY\s+)`[^`]+`/", "$1", $sIndexSql );

                    //replacing previous field name with new one
                    $sIndexSql = preg_replace("/\b" . $sField . "\b/", $sNewField, $sIndexSql );

                    $aIndexSql[] =  "ALTER TABLE `$sTableSet` ADD ". $sIndexSql;
                }
            }
        }

        return $aIndexSql;
    }

    public function getCurrentMaxLangId()
    {
        if ( isset($this->_iCurrentMaxLangId) ) {
            return $this->_iCurrentMaxLangId;
        }

        $sTable = $sTableSet = "oxarticles";
        $sField = $sFieldSet = "oxtitle";
        $iLang  = 0;
        while ($this->tableExists($sTableSet) && $this->fieldExists($sFieldSet, $sTableSet)) {
            $iLang ++;
            $sTableSet = getLangTableName($sTable, $iLang);
            $sFieldSet = $sField.'_'.$iLang;
        }

        $this->_iCurrentMaxLangId = --$iLang;
        return $this->_iCurrentMaxLangId;
    }

    public function getNextLangId()
    {
        return $this->getCurrentMaxLangId() + 1;
    }

    public function getMultilangFields( $sTable )
    {
        $aFields = $this->getFields( $sTable );
        $aMultiLangFields = array();

        foreach ( $aFields as $sField ) {
            if ( preg_match("/(.+)_1$/", $sField, $aMatches) ) {
                $aMultiLangFields[] = $aMatches[1];
            }
        }

        return $aMultiLangFields;
    }

    public function getSinglelangFields( $sTable, $iLang )
    {
        $aFields = array_merge($this->getFields( $sTable ), $this->getFields(getLangTableName($sTable, $iLang) ));
        $aSingleLangFields = array();

        foreach ( $aFields as $sField ) {
            if ( preg_match("/(.+)_([0-9]+)$/", $sField, $aMatches) ) {
                if ($aMatches[2] == $iLang) {
                    $aSingleLangFields[$aMatches[1]] = $sField;
                }
            } else {
                $aSingleLangFields[$sField] = $sField;
            }
        }

        return $aSingleLangFields;
    }

    public function addNewMultilangField( $sTable )
    {
        $aSql = array();
        $aFields  = $this->getMultilangFields($sTable);
        $iMaxLang = $this->getCurrentMaxLangId();
        $iNewLang = $this->getNextLangId();

        $sTableSet = getLangTableName($sTable, $iNewLang);
        if (!$this->tableExists($sTableSet)) {
            $aSql[] = $this->_getCreateTableSetSql( $sTable, $iNewLang );
        }

        if ( is_array($aFields) && count($aFields) > 0 ) {
            foreach ( $aFields as $sField ) {
                $sNewFieldName = $sField . "_" . $iNewLang;
                if ( !$this->tableExists($sTableSet) || !$this->fieldExists( $sNewFieldName, $sTableSet ) ) {

                    //getting add field sql
                    $aSql[] = $this->_getAddFieldSql($sTable, $sField, $iNewLang);

                    //getting add index sql on added field
                    $aSql = array_merge($aSql, (array) $this->_getAddFieldIndexSql($sTable, $sField, $iNewLang));
                }
            }
        }
        $this->_executeSql($aSql);
    }

    public function resetMultilangFields( $iLangId, $sTableName )
    {
        $iLangId = (int)$iLangId;

        if ( $iLangId === 0 ) {
            return;
        }

        $aSql = array();

        $aFields = $this->getMultilangFields( $sTableName );
        if ( is_array($aFields) && count($aFields) > 0 ) {
            foreach ( $aFields as $sFieldName ) {
                $sFieldName = $sFieldName . "_" . $iLangId;

                if ( $this->fieldExists( $sFieldName, $sTableName ) ) {
                    //reseting field value to default
                    $aSql[] = "UPDATE {$sTableName} SET {$sFieldName} = DEFAULT;";
                }
            }
        }

        if ( !empty($aSql) ) {
            $this->_executeSql( $aSql );
        }
    }

    public function addNewLangToDb()
    {
        //reset max count
        $this->_iCurrentMaxLangId = null;

        $aTable = $this->getAllTables();

        foreach ( $aTable as $sTableName ) {
            $this->addNewMultilangField( $sTableName );
        }

        //updating views
        $this->updateViews();
    }

    public function resetLanguage( $iLangId )
    {
        if ( (int)$iLangId === 0 ) {
            return;
        }

        $aTables = $this->getAllTables();

        // removing tables which does not requires reset
        foreach ( $this->_aSkipTablesOnReset as $sSkipTable ) {

            if ( ($iSkipId = array_search( $sSkipTable, $aTables )) !== false ) {
                unset( $aTables[$iSkipId] );
            }
        }

        foreach ( $aTables as $sTableName ) {
            $this->resetMultilangFields( $iLangId, $sTableName );
        }
    }

    protected function _executeSql( $aSql )
    {
        $oDb = oxDb::getDb();

        if ( is_array($aSql) && !empty($aSql) ) {
            foreach ( $aSql as $sSql) {
                $sSql = trim($sSql);
                if (!empty($sSql)) {
                    $oDb->execute( $sSql );
                }
            }
        }
    }

    public function updateViews()
    {
        oxDb::getInstance()->updateViews();
    }

}