class.ilMySQLAbstraction.php

Go to the documentation of this file.

<?php
/* Copyright (c) 1998-2009 ILIAS open source, Extended GPL, see docs/LICENSE */

class ilMySQLAbstraction {

        public $analyzer;
        protected $ilDBInterface;
        protected $manager;
        protected $reverse;
        protected $testmode;


        public function __construct() {
                global $ilDB;

                $this->ilDBInterface = $ilDB;
                $this->manager = $ilDB->loadModule(ilDBConstants::MODULE_MANAGER);
                $this->reverse = $ilDB->loadModule(ilDBConstants::MODULE_REVERSE);
                if (@is_file('../Services/Database/classes/class.ilDBAnalyzer.php')) {
                        include_once '../Services/Database/classes/class.ilDBAnalyzer.php';
                } else {
                        include_once './Services/Database/classes/class.ilDBAnalyzer.php';
                }
                $this->analyzer = new ilDBAnalyzer();
                $this->setTestMode(false);
        }


        public function setTestMode($a_testmode) {
                $this->testmode = $a_testmode;
        }


        public function getTestMode() {
                return $this->testmode;
        }


        public function performAbstraction($a_table_name, $a_set_text_ts_fields_notnull_false = true) {
                // to do: log this procedure

                // count number of records at the beginning
                $nr_rec = $this->countRecords($a_table_name);

                // convert table name to lowercase
                if (!$this->getTestMode()) {
                        $this->lowerCaseTableName($a_table_name);
                        $a_table_name = strtolower($a_table_name);
                        $this->storeStep($a_table_name, 10);
                }

                // get auto increment information
                $auto_inc_field = $this->analyzer->getAutoIncrementField($a_table_name);

                // get primary key information
                $pk = $this->analyzer->getPrimaryKeyInformation($a_table_name);

                // get indices information
                $indices = $this->analyzer->getIndicesInformation($a_table_name);

                // get constraints information
                $constraints = $this->analyzer->getConstraintsInformation($a_table_name);

                // get field information
                $fields = $this->analyzer->getFieldInformation($a_table_name);

                if (!$this->getTestMode()) {
                        // remove auto increment
                        $this->removeAutoIncrement($a_table_name, $auto_inc_field, $fields);
                        $this->storeStep($a_table_name, 20);

                        // remove primary key
                        $this->removePrimaryKey($a_table_name, $pk);
                        $this->storeStep($a_table_name, 30);

                        // remove indices
                        $this->removeIndices($a_table_name, $indices);
                        $this->storeStep($a_table_name, 40);

                        // remove constraints
                        $this->removeConstraints($a_table_name, $constraints);
                        $this->storeStep($a_table_name, 45);
                }

                // alter table using mdb2 field types
                $this->alterTable($a_table_name, $fields, $a_set_text_ts_fields_notnull_false, $pk);
                if ($this->getTestMode()) {
                        $a_table_name = strtolower($a_table_name) . "_copy";
                } else {
                        $this->storeStep($a_table_name, 50);
                }

                // lower case field names
                $this->lowerCaseColumnNames($a_table_name);
                if (!$this->getTestMode()) {
                        $this->storeStep($a_table_name, 60);
                }

                // add primary key
                $this->addPrimaryKey($a_table_name, $pk);
                if (!$this->getTestMode()) {
                        $this->storeStep($a_table_name, 70);
                }

                // add indices
                $this->addIndices($a_table_name, $indices);
                if (!$this->getTestMode()) {
                        $this->storeStep($a_table_name, 80);
                }

                // add constraints
                $this->addConstraints($a_table_name, $constraints);
                if (!$this->getTestMode()) {
                        $this->storeStep($a_table_name, 85);
                }

                // add "auto increment" sequence
                if ($auto_inc_field != "") {
                        $this->addAutoIncrementSequence($a_table_name, $auto_inc_field);
                }
                if (!$this->getTestMode()) {
                        $this->storeStep($a_table_name, 90);
                }

                // replace empty strings with null values in text fields
                $this->replaceEmptyStringsWithNull($a_table_name);
                if (!$this->getTestMode()) {
                        $this->storeStep($a_table_name, 100);
                }

                // replace empty "0000-00-00..." dates with null
                $this->replaceEmptyDatesWithNull($a_table_name);
                if (!$this->getTestMode()) {
                        $this->storeStep($a_table_name, 110);
                }

                $nr_rec2 = $this->countRecords($a_table_name);

                if (!$this->getTestMode()) {
                        if ($nr_rec != $nr_rec2) {
                                throw new ilDatabaseException("ilMySQLAbstraction: Unexpected difference in table record number, table '" . $a_table_name . "'."
                                                              . " Before: " . ((int)$nr_rec) . ", After: " . ((int)$nr_rec2) . ".");
                        }
                }
        }


        public function countRecords($a_table_name) {
                global $ilDB;

                $st = $ilDB->prepare("SELECT count(*) AS cnt FROM `" . $a_table_name . "`");
                $res = $ilDB->execute($st);
                $rec = $ilDB->fetchAssoc($res);

                return $rec["cnt"];
        }


        public function storeStep($a_table, $a_step) {
                $st = $this->ilDBInterface->prepareManip("REPLACE INTO abstraction_progress (table_name, step)" . " VALUES (?,?)", array(
                        "text",
                        "integer",
                ));
                $this->ilDBInterface->execute($st, array( $a_table, $a_step ));
        }


        public function replaceEmptyStringsWithNull($a_table) {
                global $ilDB;

                $fields = $this->analyzer->getFieldInformation($a_table);
                $upfields = array();
                foreach ($fields as $field => $def) {
                        if ($def["type"] == "text"
                            && ($def["length"] >= 1 && $def["length"] <= 4000)
                        ) {
                                $upfields[] = $field;
                        }
                }
                foreach ($upfields as $uf) {
                        $ilDB->query("UPDATE `" . $a_table . "` SET `" . $uf . "` = null WHERE `" . $uf . "` = ''");
                }
        }


        public function replaceEmptyDatesWithNull($a_table) {
                global $ilDB;

                if (!$this->ilDBInterface->tableExists($a_table)) {
                        return;
                }

                $fields = $this->analyzer->getFieldInformation($a_table);
                $upfields = array();
                foreach ($fields as $field => $def) {
                        if ($def["type"] == "timestamp") {
                                $upfields[] = $field;
                        }
                }
                foreach ($upfields as $uf) {
                        $ilDB->query("UPDATE `" . $a_table . "` SET `" . $uf . "` = null WHERE `" . $uf . "` = '0000-00-00 00:00:00'");
                }

                $upfields = array();
                reset($fields);
                foreach ($fields as $field => $def) {
                        if ($def["type"] == "date") {
                                $upfields[] = $field;
                        }
                }
                foreach ($upfields as $uf) {
                        $ilDB->query("UPDATE `" . $a_table . "` SET `" . $uf . "` = null WHERE `" . $uf . "` = '0000-00-00'");
                }
        }


        public function lowerCaseTableName($a_table_name) {
                global $ilDB;

                if ($a_table_name != strtolower($a_table_name)) {
                        // this may look strange, but it does not work directly
                        // (seems that mysql does not see no difference whether upper or lowercase characters are used
                        mysql_query("ALTER TABLE `" . $a_table_name . "` RENAME `" . strtolower($a_table_name) . "xxx" . "`");
                        mysql_query("ALTER TABLE `" . strtolower($a_table_name) . "xxx" . "` RENAME `" . strtolower($a_table_name) . "`");
                }
        }


        public function lowerCaseColumnNames($a_table_name) {
                global $ilDB;

                $result = mysql_query("SHOW COLUMNS FROM `" . $a_table_name . "`");
                while ($row = mysql_fetch_assoc($result)) {
                        if ($row["Field"] != strtolower($row["Field"])) {
                                $ilDB->renameTableColumn($a_table_name, $row["Field"], strtolower($row["Field"]));
                        }
                }
        }


        public function removeAutoIncrement($a_table_name, $a_auto_inc_field) {
                if ($a_auto_inc_field != "") {
                        $this->ilDBInterface->modifyTableColumn($a_table_name, $a_auto_inc_field, array());
                }
        }


        public function removePrimaryKey($a_table, $a_pk) {
                if ($a_pk["name"] != "") {
                        $this->ilDBInterface->dropPrimaryKey($a_table, $a_pk["name"]);
                }
        }


        public function removeIndices($a_table, $a_indices) {
                if (is_array($a_indices)) {
                        foreach ($a_indices as $index) {
                                $this->ilDBInterface->query("ALTER TABLE `" . $a_table . "` DROP INDEX `" . $index["name"] . "`");
                        }
                }
        }


        public function removeConstraints($a_table, $a_constraints) {
                if (is_array($a_constraints)) {
                        foreach ($a_constraints as $c) {
                                if ($c["type"] == "unique") {
                                        $this->ilDBInterface->query("ALTER TABLE `" . $a_table . "` DROP INDEX `" . $c["name"] . "`");
                                }
                        }
                }
        }


        public function alterTable($a_table, $a_fields, $a_set_text_ts_fields_notnull_false = true, $pk = "") {
                $n_fields = array();
                foreach ($a_fields as $field => $d) {
                        $def = $this->reverse->getTableFieldDefinition($a_table, $field);
                        $this->ilDBInterface->handleError($def);
                        $best_alt = $this->analyzer->getBestDefinitionAlternative($def);
                        $def = $def[$best_alt];

                        // remove "current_timestamp" default for timestamps (not supported)
                        if (strtolower($def["nativetype"]) == "timestamp"
                            && strtolower($def["default"]) == "current_timestamp"
                        ) {
                                unset($def["default"]);
                        }

                        if (strtolower($def["type"]) == "float") {
                                unset($def["length"]);
                        }

                        // remove all invalid attributes
                        foreach ($def as $k => $v) {
                                if (!in_array($k, array( "type", "default", "notnull", "length", "unsigned", "fixed" ))) {
                                        unset($def[$k]);
                                }
                        }

                        // determine length for decimal type
                        if ($def["type"] == "decimal") {
                                $l_arr = explode(",", $def["length"]);
                                $def["length"] = $l_arr[0];
                        }

                        // remove length values for float
                        if ($def["type"] == "float") {
                                unset($def["length"]);
                        }

                        // set notnull to false for text/timestamp/date fields
                        if ($a_set_text_ts_fields_notnull_false
                            && ($def["type"] == "text"
                                || $def["type"] == "timestamp"
                                || $def["type"] == "date")
                            && (!is_array($pk) || !isset($field, $pk["fields"][$field]))
                        ) {
                                $def["notnull"] = false;
                        }

                        // set unsigned to false for integers
                        if ($def["type"] == "integer") {
                                $def["unsigned"] = false;
                        }

                        // set notnull to false for blob and clob
                        if ($def["type"] == "blob" || $def["type"] == "clob") {
                                $def["notnull"] = false;
                        }

                        // remove "0000-00-00..." default values
                        if (($def["type"] == "timestamp" && $def["default"] == "0000-00-00 00:00:00")
                            || ($def["type"] == "date" && $def["default"] == "0000-00-00")
                        ) {
                                unset($def["default"]);
                        }

                        $a = array();
                        foreach ($def as $k => $v) {
                                $a[$k] = $v;
                        }
                        $def["definition"] = $a;

                        $n_fields[$field] = $def;
                }

                $changes = array(
                        "change" => $n_fields,
                );

                if (!$this->getTestMode()) {
                        $r = $this->manager->alterTable($a_table, $changes, false);
                } else {
                        $r = $this->manager->createTable(strtolower($a_table) . "_copy", $n_fields);
                }

                return true;
        }


        public function addPrimaryKey($a_table, $a_pk) {
                if (is_array($a_pk["fields"])) {
                        $fields = array();
                        foreach ($a_pk["fields"] as $f => $pos) {
                                $fields[] = strtolower($f);
                        }
                        $this->ilDBInterface->addPrimaryKey($a_table, $fields);
                }
        }


        public function addIndices($a_table, $a_indices) {
                if (is_array($a_indices)) {
                        $all_valid = true;

                        foreach ($a_indices as $index) {
                                if (strlen($index["name"]) > 3) {
                                        $all_valid = false;
                                }
                        }

                        $cnt = 1;
                        foreach ($a_indices as $index) {
                                if (is_array($index["fields"])) {
                                        if (!$all_valid) {
                                                $index["name"] = "i" . $cnt;
                                        }
                                        $fields = array();
                                        foreach ($index["fields"] as $f => $pos) {
                                                $fields[] = strtolower($f);
                                        }
                                        $this->ilDBInterface->addIndex($a_table, $fields, strtolower($index["name"]), $index["fulltext"]);
                                        $cnt ++;
                                }
                        }
                }
        }


        public function addConstraints($a_table, $a_constraints) {
                if (is_array($a_constraints)) {
                        $all_valid = true;

                        foreach ($a_constraints as $c) {
                                if (strlen($c["name"]) > 3) {
                                        $all_valid = false;
                                }
                        }

                        $cnt = 1;
                        foreach ($a_constraints as $c) {
                                if (is_array($c["fields"])) {
                                        if (!$all_valid) {
                                                $c["name"] = "c" . $cnt;
                                        }
                                        $fields = array();
                                        foreach ($c["fields"] as $f => $pos) {
                                                $fields[] = strtolower($f);
                                        }
                                        $this->ilDBInterface->addUniqueConstraint($a_table, $fields, strtolower($c["name"]));
                                        $cnt ++;
                                }
                        }
                }
        }


        public function fixIndexNames($a_table) {
                if (!$this->ilDBInterface->tableExists($a_table)) {
                        return;
                }
                $all_valid = true;
                $indices = $this->analyzer->getIndicesInformation($a_table);
                foreach ($indices as $index) {
                        if (strlen($index["name"]) > 3) {
                                $all_valid = false;
                        }
                }

                if (!$all_valid) {
                        foreach ($indices as $index) {
                                $this->ilDBInterface->dropIndex($a_table, $index["name"]);
                        }
                        $this->addIndices($a_table, $indices);
                }
        }


        public function addAutoIncrementSequence($a_table, $a_auto_inc_field) {
                if ($a_auto_inc_field != "") {
                        $set = $this->ilDBInterface->query("SELECT MAX(`" . strtolower($a_auto_inc_field) . "`) ma FROM `" . $a_table . "`");
                        $rec = $this->ilDBInterface->fetchAssoc($set);
                        $next = $rec["ma"] + 1;
                        $this->ilDBInterface->createSequence($a_table, $next);
                }
        }


        public function fixClobNotNull($a_table) {
                if (!$this->ilDBInterface->tableExists($a_table)) {
                        return;
                }
                $all_valid = true;
                $fields = $this->analyzer->getFieldInformation($a_table);
                foreach ($fields as $name => $def) {
                        if ($def["type"] == "clob" && $def["notnull"] == true) {
                                $this->ilDBInterface->modifyTableColumn($a_table, $name, array( "type" => "clob", "notnull" => false ));
                        }
                }
        }


        public function fixDatetimeValues($a_table) {
                if (!$this->ilDBInterface->tableExists($a_table)) {
                        return;
                }
                $all_valid = true;
                $fields = $this->analyzer->getFieldInformation($a_table);
                foreach ($fields as $name => $def) {
                        if ($def["type"] == "timestamp"
                            && ($def["notnull"] == true || $def["default"] == "0000-00-00 00:00:00")
                        ) {
                                $nd = array( "type" => "timestamp", "notnull" => false );
                                if ($def["default"] == "0000-00-00 00:00:00") {
                                        $nd["default"] = null;
                                }
                                $this->ilDBInterface->modifyTableColumn($a_table, $name, $nd);
                        }
                        if ($def["type"] == "date"
                            && ($def["notnull"] == true || $def["default"] == "0000-00-00")
                        ) {
                                $nd = array( "type" => "date", "notnull" => false );
                                if ($def["default"] == "0000-00-00") {
                                        $nd["default"] = null;
                                }
                                $this->ilDBInterface->modifyTableColumn($a_table, $name, $nd);
                        }
                }
        }
}