class.ilDBGenerator.php

Go to the documentation of this file.

<?php

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

class ilDBGenerator
{

    protected $target_encoding = 'UTF-8';
    protected $whitelist = array();
    protected $blacklist = array();
    protected $tables = array();
    protected $filter = array();


    public function __construct()
    {
        global $DIC;
        $ilDB = $DIC->database();

        $this->manager = $ilDB->loadModule(ilDBConstants::MODULE_MANAGER);
        $this->reverse = $ilDB->loadModule(ilDBConstants::MODULE_REVERSE);
        $this->il_db = $ilDB;
        $this->analyzer = new ilDBAnalyzer();

        $this->allowed_attributes = $ilDB->getAllowedAttributes();
    }

    public static function lookupAbstractedTables()
    {
        global $DIC;
        $ilDB = $DIC->database();

        $query = "SELECT DISTINCT(table_name) FROM abstraction_progress ";
        $res = $ilDB->query($query);
        $names = array();
        while ($row = $res->fetchRow(ilDBConstants::FETCHMODE_OBJECT)) {
            $names[] = $row->table_name;
        }

        // tables that have been already created in an abstracted
        // way or tables that have been renamed after being abstracted
        // (see db_update script)
        $abs_tables = array_merge($names, array(
            'acc_access_key',
            'acc_user_access_key',
            'ldap_rg_mapping',
            'page_anchor',
            'qpl_question_orderinghorizontal',
            'qpl_question_fileupload',
            'chat_smilies',
            'style_color',
            'style_template_class',
            'style_template',
            'page_style_usage',
            'style_setting',
            'page_editor_settings',
            'mep_data',
            'license_data',
            'loginname_history',
            'mep_item',
            'qpl_a_cloze',
            'qpl_a_imagemap',
            'qpl_a_matching',
            'qpl_num_range',
            'qpl_qst_cloze',
            'qpl_qst_essay',
            'qpl_qst_fileupload',
            'qpl_qst_flash',
            'qpl_qst_horder',
            'qpl_qst_imagemap',
            'qpl_qst_javaapplet',
            'qpl_qst_matching',
            'qpl_qst_mc',
            'qpl_qst_numeric',
            'qpl_qst_ordering',
            'qpl_qst_sc',
            'qpl_qst_textsubset',
            'qpl_qst_type',
            'qpl_sol_sug',
            'udf_text',
            'udf_clob',
            'xmlnestedsettmp',
            'cache_text',
            'cache_clob',
            'qpl_a_errortext',
            'qpl_qst_errortext',
            'tst_rnd_cpy',
            'tst_rnd_qpl_title',
            'qpl_a_mdef',
        ));

        return $abs_tables;
    }


    public function setTargetEncoding($a_encoding)
    {
        $this->target_encoding = $a_encoding;
    }


    public function getTargetEncoding()
    {
        return $this->target_encoding;
    }


    public function setBlackList($a_blacklist)
    {
        $this->blacklist = $a_blacklist;
    }


    public function getBlackList()
    {
        return $this->blacklist;
    }


    public function setWhiteList($a_whitelist)
    {
        $this->whitelist = $a_whitelist;
    }


    public function getWhiteList()
    {
        return $this->whitelist;
    }


    public function setFilter($a_filter, $a_value)
    {
        $this->filter[$a_filter] = $a_value;
    }


    public function getTables()
    {
        $r = $this->manager->listTables();
        $this->tables = $r;

        return $this->tables;
    }


    public function checkProcessing($a_table)
    {
        // check black list
        if (in_array($a_table, $this->blacklist)) {
            return false;
        }

        // check white list
        if (count($this->whitelist) > 0 && !in_array($a_table, $this->whitelist)) {
            return false;
        }

        return true;
    }


    protected function openFile($a_path)
    {
        if (1) {
            $file = fopen($a_path, "w");
            $start .= "\t" . 'global $ilDB;' . "\n\n";
            fwrite($file, $start);

            return $file;
        }

        $file = fopen($a_path, "w");
        $start = '<?php' . "\n" . 'function setupILIASDatabase()' . "\n{\n";
        $start .= "\t" . 'global $ilDB;' . "\n\n";
        fwrite($file, $start);

        return $file;
    }


    protected function closeFile($fp)
    {
        if (1) {
            #fwrite ($fp, $end);
            fclose($fp);

            return;
        }

        $end = "\n}\n?>\n";
        fwrite($fp, $end);
        fclose($fp);
    }


    public function buildDBGenerationScript($a_filename = "")
    {
        if (@is_dir($a_filename)) {
            $isDirectory = true;
            $path = $a_filename;
        } else {
            $isDirectory = false;
            $path = '';
        }

        $file = "";
        if ($a_filename != "" and !$isDirectory) {
            $file = fopen($a_filename, "w");

            $start = '<?php' . "\n" . 'function setupILIASDatabase()' . "\n{\n";
            $start .= "\t" . 'global $ilDB;' . "\n\n";
            fwrite($file, $start);
        } elseif ($isDirectory) {
            ;
        } else {
            echo "<pre>";
        }

        $this->getTables();

        foreach ($this->tables as $table) {
            if ($this->checkProcessing($table)) {
                if ($a_filename != "") {
                    flush();
                }

                if ($isDirectory) {
                    $file = $this->openFile($path . '/' . $table);
                }

                // create table statement
                $this->buildCreateTableStatement($table, $file);

                // primary key
                $this->buildAddPrimaryKeyStatement($table, $file);

                // indices
                $this->buildAddIndexStatements($table, $file);

                // constraints (currently unique keys)
                $this->buildAddUniqueConstraintStatements($table, $file);

                // auto increment sequence
                $this->buildCreateSequenceStatement($table, $file);

                if (in_array($table, array('usr_session_stats', 'usr_session_raw'))) {
                    continue;
                }

                // inserts
                if ($isDirectory) {
                    $this->buildInsertStatement($table, $path);
                #$this->buildInsertStatementsXML($table,$path);
                } else {
                    $this->buildInsertStatements($table, $file);
                }

                if ($isDirectory) {
                    $this->closeFile($file);
                }
            } else {
                if ($a_filename != "") {
                    echo "<br><b>missing: " . $table . "</b>";
                    flush();
                }
            }
        }

        // sequence(s) without table (of same name)
        $this->buildSingularSequenceStatement($file);

        if ($a_filename == "") {
            echo "</pre>";
        } elseif (!$isDirectory) {
            $end = "\n}\n?>\n";
            $ok = fwrite($file, $end);
            var_dump($ok);
            fclose($file);
        }
    }


    public function buildCreateTableStatement($a_table, $a_file = "")
    {
        $fields = $this->analyzer->getFieldInformation($a_table, true);
        $this->fields = $fields;
        $create_st = "\n\n//\n// " . $a_table . "\n//\n";
        $create_st .= '$fields = array (' . "\n";
        $f_sep = "";
        foreach ($fields as $f => $def) {
            $create_st .= "\t" . $f_sep . '"' . $f . '" => array (' . "\n";
            $f_sep = ",";
            $a_sep = "";
            foreach ($def as $k => $v) {
                if ($k != "nativetype" && $k != "alt_types" && $k != "autoincrement" && !is_null($v)) {
                    switch ($k) {
                        case "notnull":
                        case "unsigned":
                        case "fixed":
                            $v = $v ? "true" : "false";
                            break;

                        case "default":
                        case "type":
                            $v = '"' . $v . '"';
                            break;

                        default:
                            break;
                    }
                    $create_st .= "\t\t" . $a_sep . '"' . $k . '" => ' . $v . "\n";
                    $a_sep = ",";
                }
            }
            $create_st .= "\t" . ')' . "\n";
        }
        $create_st .= ');' . "\n";
        $create_st .= '$ilDB->createTable("' . $a_table . '", $fields);' . "\n";

        if ($a_file == "") {
            echo $create_st;
        } else {
            fwrite($a_file, $create_st);
        }
    }


    public function buildAddPrimaryKeyStatement($a_table, $a_file = "")
    {
        $pk = $this->analyzer->getPrimaryKeyInformation($a_table);

        if (is_array($pk["fields"]) && count($pk["fields"]) > 0) {
            $pk_st = "\n" . '$pk_fields = array(';
            $sep = "";
            foreach ($pk["fields"] as $f => $pos) {
                $pk_st .= $sep . '"' . $f . '"';
                $sep = ",";
            }
            $pk_st .= ");\n";
            $pk_st .= '$ilDB->addPrimaryKey("' . $a_table . '", $pk_fields);' . "\n";

            if ($a_file == "") {
                echo $pk_st;
            } else {
                fwrite($a_file, $pk_st);
            }
        }
    }


    public function buildAddIndexStatements($a_table, $a_file = "")
    {
        $ind = $this->analyzer->getIndicesInformation($a_table, true);

        if (is_array($ind)) {
            foreach ($ind as $i) {
                if ($i["fulltext"]) {
                    $ft = ", true";
                } else {
                    $ft = ", false";
                }
                $in_st = "\n" . '$in_fields = array(';
                $sep = "";
                foreach ($i["fields"] as $f => $pos) {
                    $in_st .= $sep . '"' . $f . '"';
                    $sep = ",";
                }
                $in_st .= ");\n";
                $in_st .= '$ilDB->addIndex("' . $a_table . '", $in_fields, "' . $i["name"] . '"' . $ft . ');' . "\n";

                if ($a_file == "") {
                    echo $in_st;
                } else {
                    fwrite($a_file, $in_st);
                }
            }
        }
    }


    public function buildAddUniqueConstraintStatements($a_table, $a_file = "")
    {
        $con = $this->analyzer->getConstraintsInformation($a_table, true);

        if (is_array($con)) {
            foreach ($con as $i) {
                $in_st = "\n" . '$in_fields = array(';
                $sep = "";
                foreach ($i["fields"] as $f => $pos) {
                    $in_st .= $sep . '"' . $f . '"';
                    $sep = ",";
                }
                $in_st .= ");\n";
                $in_st .= '$ilDB->addUniqueConstraint("' . $a_table . '", $in_fields, "' . $i["name"] . '");' . "\n";

                if ($a_file == "") {
                    echo $in_st;
                } else {
                    fwrite($a_file, $in_st);
                }
            }
        }
    }


    public function buildCreateSequenceStatement($a_table, $a_file = "")
    {
        $seq = $this->analyzer->hasSequence($a_table);
        if ($seq !== false) {
            $seq_st = "\n" . '$ilDB->createSequence("' . $a_table . '", ' . (int) $seq . ');' . "\n";

            if ($a_file == "") {
                echo $seq_st;
            } else {
                fwrite($a_file, $seq_st);
            }
        }
    }


    public function buildSingularSequenceStatement($a_file = "")
    {
        $r = $this->manager->listSequences();

        foreach ($r as $seq) {
            if (!in_array($seq, $this->tables)) {
                // 12570
                if ($seq == "sahs_sc13_seq") {
                    continue;
                }

                $create_st = "\n" . '$ilDB->createSequence("' . $seq . '");' . "\n";

                if ($a_file == "") {
                    echo $create_st;
                } else {
                    fwrite($a_file, $create_st);
                }
            }
        }
    }


    public function buildInsertStatement($a_table, $a_basedir)
    {
        global $DIC;
        $ilLogger = $DIC->logger()->root();

        $ilLogger->log('Starting export of:' . $a_table);

        $set = $this->il_db->query("SELECT * FROM " . $this->il_db->quoteIdentifier($a_table));
        $row = 0;

        umask(0000);
        mkdir($a_basedir . '/' . $a_table . '_inserts', fileperms($a_basedir));

        $filenum = 1;
        while ($rec = $this->il_db->fetchAssoc($set)) {
            $values = array();
            foreach ($rec as $f => $v) {
                if ($this->fields[$f]['type'] == 'text' and $this->fields[$f]['length'] >= 1000) {
                    $v = $this->shortenText($a_table, $f, $v, $this->fields[$f]['length']);
                }

                $values[$f] = array(
                    $this->fields[$f]['type'],
                    $v,
                );
            }

            $rows[$a_table][$row++] = $values;

            if ($row >= 1000) {
                $ilLogger->log('Writing insert statements after 1000 lines...');
                $fp = fopen($a_basedir . '/' . $a_table . '_inserts/' . $filenum++ . '.data', 'w');
                fwrite($fp, serialize((array) $rows));
                fclose($fp);

                $row = 0;
                unset($rows);
            }
        }
        if ($rows) {
            $fp = fopen($a_basedir . '/' . $a_table . '_inserts/' . $filenum++ . '.data', 'w');
            fwrite($fp, serialize((array) $rows) . "\n");
            fclose($fp);
        }

        $ilLogger->log('Finished export of: ' . $a_table);
        if (function_exists('memory_get_usage')) {
            $ilLogger->log('Memory usage: ' . memory_get_usage(true));
        }

        return true;
    }


    public function buildInsertStatementsXML($a_table, $a_basedir)
    {
        $w = new ilXmlWriter();
        $w->xmlStartTag('Table', array( 'name' => $a_table ));

        $set = $this->il_db->query("SELECT * FROM " . $this->il_db->quoteIdentifier($a_table));
        $ins_st = "";
        $first = true;
        while ($rec = $this->il_db->fetchAssoc($set)) {
            #$ilLog->write('Num: '.$num++);
            $w->xmlStartTag('Row');

            $fields = array();
            $types = array();
            $values = array();
            foreach ($rec as $f => $v) {
                if ($this->fields[$f]['type'] == 'text' and $this->fields[$f]['length'] >= 1000) {
                    $v = $this->shortenText($a_table, $f, $v, $this->fields[$f]['length']);
                }

                $w->xmlElement('Value', array(
                        'name' => $f,
                        'type' => $this->fields[$f]['type'],
                    ), $v);
            }

            $w->xmlEndTag('Row');
        }
        $w->xmlEndTag('Table');

        $w->xmlDumpFile($a_basedir . '/' . $a_table . '.xml', false);
    }


    public function buildInsertStatements($a_table, $a_file = "")
    {
        if ($a_table == "lng_data") {
            return;
        }

        $set = $this->il_db->query("SELECT * FROM " . $this->il_db->quoteIdentifier($a_table));
        $ins_st = "";
        $first = true;
        while ($rec = $this->il_db->fetchAssoc($set)) {
            $fields = array();
            $types = array();
            $values = array();
            $i_str = array();
            foreach ($rec as $f => $v) {
                $fields[] = $f;
                $types[] = '"' . $this->fields[$f]["type"] . '"';
                $v = str_replace('\\', '\\\\', $v);
                $values[] = "'" . str_replace("'", "\'", $v) . "'";
                $i_str[] = "'" . $f . "' => array('" . $this->fields[$f]["type"] . "', '" . str_replace("'", "\'", $v) . "')";
            }
            $fields_str = "(" . implode(",", $fields) . ")";
            $types_str = "array(" . implode(",", $types) . ")";
            $values_str = "array(" . implode(",", $values) . ")";
            $ins_st = "\n" . '$ilDB->insert("' . $a_table . '", array(' . "\n";
            $ins_st .= implode(", ", $i_str) . "));\n";
            //$ins_st.= "\t".$fields_str."\n";
            //$ins_st.= "\t".'VALUES '."(%s".str_repeat(",%s", count($fields) - 1).')"'.",\n";
            //$ins_st.= "\t".$types_str.','.$values_str.');'."\n";

            if ($a_file == "") {
                echo $ins_st;
            } else {
                fwrite($a_file, $ins_st);
            }
            $ins_st = "";
        }
    }


    public function getHTMLOverview($a_filename = "")
    {
        $tpl = new ilGlobalTemplate("tpl.db_overview.html", true, true, "Services/Database");

        $this->getTables();
        $cnt = 1;
        foreach ($this->tables as $table) {
            if ($this->checkProcessing($table)) {
                // create table statement
                if ($this->addTableToOverview($table, $tpl, $cnt)) {
                    $cnt++;
                }
            }
        }

        $tpl->setVariable("TXT_TITLE", "ILIAS Abstract DB Tables (" . ILIAS_VERSION . ")");

        if ($a_filename == "") {
            echo $tpl->get();
        }
    }


    public function addTableToOverview($a_table, $a_tpl, $a_cnt)
    {
        $fields = $this->analyzer->getFieldInformation($a_table);
        $indices = $this->analyzer->getIndicesInformation($a_table);
        $constraints = $this->analyzer->getConstraintsInformation($a_table);
        $pk = $this->analyzer->getPrimaryKeyInformation($a_table);
        $auto = $this->analyzer->getAutoIncrementField($a_table);
        $has_sequence = $this->analyzer->hasSequence($a_table);

        // table filter
        if (isset($this->filter["has_sequence"])) {
            if ((!$has_sequence && $auto == "" && $this->filter["has_sequence"])
                || (($has_sequence || $auto != "") && !$this->filter["has_sequence"])
            ) {
                return false;
            }
        }

        // indices
        $indices_output = false;
        if (is_array($indices) && count($indices) > 0 && !$this->filter["skip_indices"]) {
            foreach ($indices as $index => $def) {
                $f2 = array();
                foreach ($def["fields"] as $f => $pos) {
                    $f2[] = $f;
                }
                $a_tpl->setCurrentBlock("index");
                $a_tpl->setVariable("VAL_INDEX", $def["name"]);
                $a_tpl->setVariable("VAL_FIELDS", implode(", ", $f2));
                $a_tpl->parseCurrentBlock();
                $indices_output = true;
            }
            $a_tpl->setCurrentBlock("index_table");
            $a_tpl->parseCurrentBlock();
        }

        // constraints
        $constraints_output = false;
        if (is_array($constraints) && count($constraints) > 0 && !$this->filter["skip_constraints"]) {
            foreach ($constraints as $index => $def) {
                $f2 = array();
                foreach ($def["fields"] as $f => $pos) {
                    $f2[] = $f;
                }
                $a_tpl->setCurrentBlock("constraint");
                $a_tpl->setVariable("VAL_CONSTRAINT", $def["name"]);
                $a_tpl->setVariable("VAL_CTYPE", $def["type"]);
                $a_tpl->setVariable("VAL_CFIELDS", implode(", ", $f2));
                $a_tpl->parseCurrentBlock();
                $constraints_output = true;
            }
            $a_tpl->setCurrentBlock("constraint_table");
            $a_tpl->parseCurrentBlock();
        }

        // fields
        $fields_output = false;
        foreach ($fields as $field => $def) {
            // field filter
            if (isset($this->filter["alt_types"])) {
                if (($def["alt_types"] == "" && $this->filter["alt_types"])
                    || ($def["alt_types"] != "" && !$this->filter["alt_types"])
                ) {
                    continue;
                }
            }
            if (isset($this->filter["type"])) {
                if ($def["type"] != $this->filter["type"]) {
                    continue;
                }
            }
            if (isset($this->filter["nativetype"])) {
                if ($def["nativetype"] != $this->filter["nativetype"]) {
                    continue;
                }
            }
            if (isset($this->filter["unsigned"])) {
                if ($def["unsigned"] != $this->filter["unsigned"]) {
                    continue;
                }
            }

            $a_tpl->setCurrentBlock("field");
            if (empty($pk["fields"][$field])) {
                $a_tpl->setVariable("VAL_FIELD", strtolower($field));
            } else {
                $a_tpl->setVariable("VAL_FIELD", "<u>" . strtolower($field) . "</u>");
            }
            $a_tpl->setVariable("VAL_TYPE", $def["type"]);
            $a_tpl->setVariable("VAL_LENGTH", (!is_null($def["length"])) ? $def["length"] : "&nbsp;");

            if (strtolower($def["default"]) == "current_timestamp") {
                //$def["default"] = "0000-00-00 00:00:00";
                unset($def["default"]);
            }

            $a_tpl->setVariable("VAL_DEFAULT", (!is_null($def["default"])) ? $def["default"] : "&nbsp;");
            $a_tpl->setVariable("VAL_NOT_NULL", (!is_null($def["notnull"])) ? (($def["notnull"]) ? "true" : "false") : "&nbsp;");
            $a_tpl->setVariable("VAL_FIXED", (!is_null($def["fixed"])) ? (($def["fixed"]) ? "true" : "false") : "&nbsp;");
            $a_tpl->setVariable("VAL_UNSIGNED", (!is_null($def["unsigned"])) ? (($def["unsigned"]) ? "true" : "false") : "&nbsp;");
            $a_tpl->setVariable("VAL_ALTERNATIVE_TYPES", ($def["alt_types"] != "") ? $def["alt_types"] : "&nbsp;");
            $a_tpl->setVariable("VAL_NATIVETYPE", ($def["nativetype"] != "") ? $def["nativetype"] : "&nbsp;");
            $a_tpl->parseCurrentBlock();
            $fields_output = true;
        }

        if ($fields_output) {
            $a_tpl->setCurrentBlock("field_table");
            $a_tpl->parseCurrentBlock();
        }

        // table information
        if ($indices_output || $fields_output || $constraints_output) {
            $a_tpl->setCurrentBlock("table");
            $a_tpl->setVariable("TXT_TABLE_NAME", strtolower($a_table));
            if ($has_sequence || $auto != "") {
                $a_tpl->setVariable("TXT_SEQUENCE", "Has Sequence");
            } else {
                $a_tpl->setVariable("TXT_SEQUENCE", "No Sequence");
            }
            $a_tpl->setVariable("VAL_CNT", (int) $a_cnt);
            $a_tpl->parseCurrentBlock();

            return true;
        }

        return false;
    }


    protected function shortenText($table, $field, $a_value, $a_size)
    {
        global $DIC;
        $ilLogger = $DIC->logger()->root();

        if ($this->getTargetEncoding() == 'UTF-8') {
            return $a_value;
        }
        // Convert to target encoding
        $shortened = mb_convert_encoding($a_value, $this->getTargetEncoding(), 'UTF-8');
        // Shorten
        $shortened = ilStr::shortenText($shortened, 0, $a_size, $this->getTargetEncoding());
        // Convert back to UTF-8
        $shortened = mb_convert_encoding($shortened, 'UTF-8', $this->getTargetEncoding());

        if (strlen($a_value) != strlen($shortened)) {
            $ilLogger->log('Table        : ' . $table);
            $ilLogger->log('Field        : ' . $field);
            $ilLogger->log('Type         : ' . $this->fields[$field]['type']);
            $ilLogger->log('Length       : ' . $this->fields[$field]['length']);
            $ilLogger->log('Before       : ' . $a_value);
            $ilLogger->log('Shortened    : ' . $shortened);
            $ilLogger->log('Strlen Before: ' . strlen($a_value));
            $ilLogger->log('Strlen After : ' . strlen($shortened));
        }

        return $shortened;
    }
}