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';
        
        var $whitelist = array();
        var $blacklist = array();
        var $tables = array();

        function __construct()
        {
                global $ilDB;
                
                $this->manager = $ilDB->db->loadModule('Manager');
                $this->reverse = $ilDB->db->loadModule('Reverse');
                $this->il_db = $ilDB;
                include_once("./Services/Database/classes/class.ilDBAnalyzer.php");
                $this->analyzer = new ilDBAnalyzer();
                
                $this->allowed_attributes = $ilDB->getAllowedAttributes();
        }
        
        public static function lookupAbstractedTables()
        {
                global $ilDB;
                
                $query = "SELECT DISTINCT(table_name) FROM abstraction_progress ";
                $res = $ilDB->query($query);
                $names = array();
                while($row = $res->fetchRow(DB_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',
                        'payment_vats',
                        '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;
        }
        
        function setBlackList($a_blacklist)
        {
                $this->blacklist = $a_blacklist;
        }

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

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

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

        function setFilter($a_filter, $a_value)
        {
                $this->filter[$a_filter] = $a_value;
        }
        
        function getTables()
        {
                $r = $this->manager->listTables();
                if (!MDB2::isError($r))
                {
                        $this->tables = $r;
                }

                // fix for 12570
                if (!in_array("sahs_sc13_seq_seq", $r))
                {
                        $this->tables[] = "sahs_sc13_seq_seq";
                }

                return $this->tables;
        }
        
        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);
        }
        
        
        function buildDBGenerationScript($a_filename = "")
        {
//echo "<br>3"; flush();
                $isDirectory = false;
                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>";
                }
//echo "<br>4"; flush();
                $this->getTables();
                foreach ($this->tables as $table)
                {
                        if ($this->checkProcessing($table))
                        {
                                if ($a_filename != "")
                                {
                                        echo "<br>$table"; 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);
                                
                                // 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";
                        fwrite ($file, $end);
                        fclose ($file);
                }
        }
        
        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.'"';
                                                        brak;
                                                        
                                                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);
                }
        }
        
        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);
                        }
                }
        }

        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);
                                }
                        }
                }
        }

        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);
                                }
                        }
                }
        }

        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);
                        }
                }
        }
                
        function buildSingularSequenceStatement($a_file = "")
        {               
                $r = $this->manager->listSequences();
                if (!MDB2::isError($r))
                {
                        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 $ilLog;
                
                $ilLog->write('Starting export of:'.$a_table);

                $set = $this->il_db->query("SELECT * FROM `".$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)
                        {
                                $ilLog->write('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);
                }
                
                $ilLog->write('Finished export of: '.$a_table);
                if(function_exists('memory_get_usage'))
                {
                        $ilLog->write('Memory usage: '.memory_get_usage(true));
                }
                return true;
        }
        
        function buildInsertStatementsXML($a_table,$a_basedir)
        {
                global $ilLog;
                
                include_once './Services/Xml/classes/class.ilXmlWriter.php';
                $w = new ilXmlWriter();
                $w->xmlStartTag('Table',array('name' => $a_table));
                
                $set = $this->il_db->query("SELECT * FROM `".$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);
        }

        function buildInsertStatements($a_table, $a_file = "")
        {
                if ($a_table == "lng_data")
                {
                        return;
                }
                
                $set = $this->il_db->query("SELECT * FROM `".$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 = "";
                }
        }

        function getHTMLOverview($a_filename = "")
        {
                $tpl = new ilTemplate("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();
                }
        }
        
        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 $ilLog;

                if($this->getTargetEncoding() == 'UTF-8')
                {
                        return $a_value;
                }
                // Convert to target encoding
                $shortened = mb_convert_encoding($a_value, $this->getTargetEncoding(), 'UTF-8');
                // Shorten
                include_once './Services/Utilities/classes/class.ilStr.php';
                $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))
                {
                        $ilLog->write('Table        : '.$table);
                        $ilLog->write('Field        : '.$field );
                        $ilLog->write('Type         : '.$this->fields[$field]['type']);
                        $ilLog->write('Length       : '.$this->fields[$field]['length']);
                        $ilLog->write('Before       : '.$a_value);
                        $ilLog->write('Shortened    : '.$shortened);
                        $ilLog->write('Strlen Before: '.strlen($a_value));
                        $ilLog->write('Strlen After : '.strlen($shortened));
                }
                return $shortened;
        }

}
?>