Login   Register  
PHP Classes
elePHPant
Icontem

File: class.mysql.db.maint.inc.php

Recommend this page to a friend!
Stumble It! Stumble It! Bookmark in del.icio.us Bookmark in del.icio.us
  Classes of Nico den Boer  >  Database metadata  >  class.mysql.db.maint.inc.php  >  Download  
File: class.mysql.db.maint.inc.php
Role: Class source
Content type: text/plain
Description: Arranges update of database structure, based on metadata
Class: Database metadata
Update a MySQL database schema using on metadata
Author: By
Last change: Depedencies removed
Date: 2006-07-23 05:13
Size: 10,024 bytes
 

Contents

Class file image Download
<?php
/**
 * See class DocBlock
 *
 * @author H.F.N. den Boer <nico@denboer-ims.nl>
 * @version 1.1.0
 * @package nl.denboer-ims.imslib.admin
 */

global $imslib_Backbone;

//    First check if access of this page is allowed
if (!isset($imslib_Backbone))
    die(
"In order to call this file, the backbone must be included first...");

/**
 * Class to automatically update the database structure based on available data.
 *
 * First written 25-05-2006
 *
 * Changelog:
 * <ol>
 *    <li></li>
 * </ol>
 *
 * @package nl.denboer-ims.imslib.admin
 * @author H.F.N. den Boer <nico@denboer-ims.nl>
 */
class imslib_mysql_db_maint
{

    
/**
     * Update database
     *
     * @param int $task
     * @return unknown
     */
    
public function update()
    {
        global 
$imslib_Backbone;
        
$dname $imslib_Backbone->database["Catalog"];
        
$mayDrop true;
        
$scriptOnly = isset($imslib_Backbone->database["scriptOnly"]) && $imslib_Backbone->database["scriptOnly"];

        
/**
         * First check what is now in the database,
         * drop tables, indexes and fields which are obsolete,
         * alter fields which are changed
         */
        
$dataReader null;
        
$tables = array();
           
/* @var $dataReader imslib_dataReader */
        
$sql "SHOW TABLES";
        
$dataReader null;
        
$imslib_Backbone->getDataSet($sql$dataReader);
        while (
$dataReader->hasNext())
        {
            
$row $dataReader->getNext();
            
reset($row);
            
$table current($row);
            
$id $imslib_Backbone->getOneRow("SELECT id FROM db_tables WHERE db_table LIKE '$table'""id");
            if (!isset(
$id) || (int)$id <= 0)
            {
                
//    Table does not exist in metadata
                
$sql "DROP TABLE $table;";
                if (!
$mayDrop)
                    
$sql "/* $sql */";
                if (
$scriptOnly)
                    echo 
"<br />$sql";
                elseif (
$mayDrop)
                    
$imslib_Backbone->execSql($sql);
            }
            else
            {
                
$tables[$table] = array();
                
$tables[$table]["indexes"] = array();
            }
        }
        
reset($tables);
        
$value current($tables);
        
$table key($tables);
        while (
$table)
        {
            
$dataReader->dispose();
            
$dataReader null;
            
//    Read indexes
            
$sql "SHOW INDEXES FROM $table";
            
$dataReader null;
            
$imslib_Backbone->getDataSet($sql$dataReader);
            while (
$dataReader->hasNext())
            {
                
$row $dataReader->getNext();
                
reset($row);
                
$key trim($row["Key_name"]);
                
$field trim($row["Column_name"]);
                
$info = (int)$imslib_Backbone->getOneRow("
                    SELECT id FROM db_indexes
                        WHERE db_table LIKE '
$table'
                        AND idx_expr LIKE '
$field'""id");
                if (!isset(
$info) || $info <= 0)
                {
                    
//    Field does not exist in metadata
                    
$sql "DROP INDEX $key ON $table;";
                    if (!
$mayDrop)
                        
$sql "/* $sql */";
                    if (
$scriptOnly)
                        echo 
"<br />$sql";
                    elseif (
$mayDrop)
                        
$imslib_Backbone->execSql($sql);
                }
                else
                    
$tables[$table]["indexes"][$key]["done"] = true;
            }

            
$tables[$table]["finished"] = true;
            
$value mysql_list_fields($dname$table);    //    get resource
            
for ($i 0$i mysql_num_fields($value); $i++)
            {
                
$decimals 0;
                
$field mysql_field_name($value$i);
                
$info $imslib_Backbone->getOneRow("
                    SELECT * FROM db_fields
                        WHERE db_table LIKE '
$table'
                        AND db_field LIKE '
$field'");
                if (!isset(
$info))
                {
                    
//    Field does not exist in metadata
                    
$sql "ALTER TABLE $table DROP $field;";
                    if (!
$mayDrop)
                        
$sql "/* $sql */";
                    if (
$scriptOnly)
                        echo 
"<br />$sql";
                    elseif (
$mayDrop)
                        
$imslib_Backbone->execSql($sql);
                    continue;
                }
                
$type mysql_field_type($value$i);
                
$len mysql_field_len($value$i);
                if (
strcmp($type"int") == && (int)$len 10)
                    
$type "tinyint";
                elseif (
strcmp($type"string") == 0)
                    
$type "varchar";
                elseif (
strcmp($type"real") == 0)
                {
                    
//    Amount here
                    
$len -= 2;
                    
$decimals += 2;
                    
$type "numeric";
                }
                elseif (
strcmp($type"blob") == 0)
                    
$type "text";
                if (
strcmp($type"int") == ||
                    
strcmp($type"date") == ||
                    
strcmp($type"datetime") == ||
                    
strcmp($type"tinyint") == ||
                    
strcmp($type"text") == 0)
                {
                    
$len 0;
                }

                
$tables[$table][$field] = array();
                
$tables[$table][$field]["finished"] = true;
                
//    Alter table if ness.
                
if (strcmp($type$dataReader->getCleanDbString($info["ftype"])) != ||
                    (int)
$len != (int)$info["width"] ||
                    (int)
$decimals != (int)$info["decimals"])
                {
                    
$create $this->_getFieldCreate($info);
                    
$sql "ALTER TABLE $table CHANGE COLUMN $field $create;";
                    if (
$scriptOnly)
                        echo 
"<br />$sql";
                    else
                        
$imslib_Backbone->execSql($sql);

                }
            }
            
$value next($tables);
            
$table key($tables);
        }
        
$dataReader->dispose();
        
$dataReader null;
        require_once(
"class.metadata.inc.php");
        
/**
         * Now go trough metadata,
         * add new tables
         */
        
$metadata = new imslib_metadata();
        
/* @var $metadata imslib_metadata */
        
$meta $metadata->getTables();
        
reset($meta);
        
$value current($meta);
        
$table key($meta);
        while (
$table)
        {
            if (isset(
$tables[$table]) &&
                isset(
$tables[$table]["finished"]) &&
                
$tables[$table]["finished"])
            {
                
$value next($meta);
                
$table key($meta);
                continue;
            }
            
$sql $this->_getTableCreate($table);
            if (
$scriptOnly)
                echo 
"<br /><pre>$sql</pre>";
            else
                
$imslib_Backbone->execSql($sql);
            
$tables[$table] = array();
            
$tables[$table]["new"] = true;
            
$value next($meta);
            
$table key($meta);
        }
        unset(
$meta);

        
/**
         * Now go trough metadata,
         * add new fields
         */
        
$dataReader null;
        
$imslib_Backbone->getDataSet("SELECT * FROM db_fields"$dataReader);
        while (
$dataReader->hasNext())
        {
            
$row $dataReader->getNext();
            
$table $row["db_table"];
            if (isset(
$tables[$table]["new"]) &&
                
$tables[$table]["new"])
            {
                
//    Full table just created
                
continue;
            }
            
$field $row["db_field"];
            if (isset(
$tables[$table][$field]) &&
                isset(
$tables[$table][$field]["finished"]) &&
                
$tables[$table][$field]["finished"]
                )
            {
                
//    Field just altered
                
continue;
            }
            
//    Add field
            
$create $this->_getFieldCreate($row);
            
$sql "ALTER TABLE $table ADD COLUMN $create;";
            if (
$scriptOnly)
                echo 
"<br />$sql";
            else
                
$imslib_Backbone->execSql($sql);
        }
        
$dataReader->dispose();
        
$dataReader null;
        
/**
         * Now go trough metadata,
         * add new indexes
         */
        
reset($tables);
        
$value current($tables);
        
$table key($tables);
        while (
$table)
        {
            if (isset(
$tables[$table]["new"]) &&
                
$tables[$table]["new"])
            {
                
//    Full table just created
                
$value next($tables);
                
$table key($tables);
                continue;
            }
            
$imslib_Backbone->getDataSet("SELECT * FROM db_indexes WHERE db_table LIKE '$table'"$dataReader);
            while (
$dataReader->hasNext())
            {
                
$row $dataReader->getNext();
                
$key $row["idx_name"];
                
$expr $row["idx_expr"];
//                print_r($tables[$table]["indexes"][$key]["done"]);
                
if (strcmp(strtolower($expr), "id") == 0)
                    continue;
//                print_r($tables[$table]["indexes"]);
//                exit();
                
if (isset($tables[$table]["indexes"][$key]["done"]) &&
                    
$tables[$table]["indexes"][$key]["done"])
                {
                    
//    Index already checked
                    
continue;
                }
                
$sql "CREATE INDEX $key on $table ($expr);";
                if (
$scriptOnly)
                    echo 
"<br />$sql";
                else
                    
$imslib_Backbone->execSql($sql);
            }
            
$dataReader->dispose();
            
$dataReader null;
            
$value next($tables);
            
$table key($tables);
        }
    }

    private function 
_getFieldCreate($row)
    {
        
$nullValues true;
        
$remarks "";
        
$isPrimary false;
        
$isForeignKey false;
        
$decimals $row["decimals"];
        
$name $row["db_field"];
        if (
strcmp($name"id") == 0)
        {
            
$isPrimary true;
            
$nullValues false;
            
$remarks "keyfield";
        }
        elseif (
strpos($name"_id") > 0)
        {
            
$isForeignKey true;
            
$nullValues false;
            
$remarks "foreign key";
        }
        if (
strlen($remarks) > 0$remarks "($remarks)";
        
$type $row["ftype"];
        
$len $row["width"];
        
$create "\n\t$name $type";
        if (
strcmp($type"int") == ||
            
strcmp($type"tinyint") == 0)
        {
            
$create .= " unsigned";
            if (
$isPrimary)
                
$create .= " auto_increment";
        }
        elseif (
strcmp($type"varchar") == 0)
            
$create .= "($len)";
        if (
strcmp($type"numeric") == 0)
        {
//            $len += $decimals;
            
$create .= "($len$decimals)";
        }
        if (
$nullValues)
            
$create .= " NULL";
        else
            
$create .= " NOT NULL";
        if (
$isForeignKey)
            
$create .= " DEFAULT '0'";
        elseif (
strcmp($type"varchar") == 0)
            
$create .= " DEFAULT NULL";
        return 
$create;
    }
    private function 
_getTableCreate($table)
    {
        global 
$imslib_Backbone;
        
$dataReader null;
        
$retVal "\nCREATE TABLE $table (";
        
$imslib_Backbone->getDataSet("SELECT * FROM db_fields WHERE db_table LIKE '$table'"$dataReader);
        while (
$dataReader->hasNext())
        {
            
$row $dataReader->getNext();
            
$retVal .= $this->_getFieldCreate($row);
            if (
$dataReader->hasNext())
                
$retVal .= ",";
        }
        
$retVal .= ",\n\tPRIMARY KEY (id)";
        
$dataReader->dispose();
        
$dataReader null;
        
$imslib_Backbone->getDataSet("SELECT * FROM db_indexes WHERE db_table LIKE '$table'"$dataReader);
        while (
$dataReader->hasNext())
        {
            
$row $dataReader->getNext();
            
$key $row["idx_name"];
            
$expr $row["idx_expr"];
            if (
strcmp(strtolower($expr), "id") == 0)
            {
                if (
$dataReader->hasNext())
                    
$retVal .= ",";
                continue;
            }
            
$retVal .= "\n\tKEY $key ($expr)";
            if (
$dataReader->hasNext())
                
$retVal .= ",";
        }
        
$retVal .= "\n) ENGINE=InnoDB DEFAULT CHARSET=utf8;";
        return 
$retVal;
    }
}
$imslib_DataMaintenance = new imslib_mysql_db_maint();

?>