PHP Classes

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

Recommend this page to a friend!
  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: 17 years ago
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") == 0 && (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") == 0 ||
                   
strcmp($type, "date") == 0 ||
                   
strcmp($type, "datetime") == 0 ||
                   
strcmp($type, "tinyint") == 0 ||
                   
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"])) != 0 ||
                    (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") == 0 ||
           
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();

?>