File: SchemeCreator.class.php

<?php

/**
 * SchemeCreator
 * @author Thomas Schaefer
 * @email scaphare@gmail.com
 */
class SchemeCreator {

    private $connectionData;
    private $connection;
    private $resource;
    private $results;
    private $outputDirectory;
	private $header = array(
		"FieldNames" => false,
		"TableNames" => false,
		"Databases" => false,
	);


    public function __construct($outputDirectory=""){
        $this->outputDirectory = $outputDirectory;
    }

    public function doCreate(){
        $this->fetchAll();
        $this->build();
    }

    public function fetchAll(){
        
        $context = $this->connectionData->database;

        $result = $this->showTablesIn($context)->getObjects();
        foreach($result as $key => $value) {
            $relationName = $context .".". $value->{"Tables_in_".$context};
            
            $this->results[$value->{"Tables_in_".$context}] = $this->describe($relationName);
            $this->results[$value->{"Tables_in_".$context}]["FullQualifiedName"] = $relationName;


            $constraints = $this->showJoinedConstraintsWithColumnsOnRelation($context, $value->{"Tables_in_".$context})->getObjects();            
            if(count($constraints)) {
                $this->results[$value->{"Tables_in_".$context}]["Constraints"] = $constraints;
            }

            $references = $this->showJoinedReference($context, $value->{"Tables_in_".$context})->getObjects();
            if(count($references)) {
                $this->results[$value->{"Tables_in_".$context}]["References"] = $references;
            }

            if(isset( $value->{"Tables_in_".$context})) {
                $tables = $this->getTableInfo($context, $value->{"Tables_in_".$context})->getObject();
                $this->results[$value->{"Tables_in_".$context}]["Info"] = $tables;
            }
        }
        return $this;
    }

    /**
     * build
     * @return void
     */
	public function build() {
        
        $xml = new SimpleXMLElement('<database></database>');
		$xml->addAttribute("name",$this->connectionData->database);		

        foreach($this->results as $name => $result)
        {
            $table = $xml->addChild("table");
            $table->addAttribute("name", $result["TableNames"][0]);
            $table->addAttribute("phpName", self::ucfirstAndCamelcased($result["TableNames"][0] ) );

            if($tableResults = $result["Info"]) {
                $table->addAttribute("engine", $tableResults->ENGINE);
                $table->addAttribute("type", self::ucfirstAndCamelcased( str_replace("" ,"_",strtolower( $tableResults->TABLE_TYPE ) ) ) );
                $table->addAttribute("collation", $tableResults->TABLE_COLLATION );
                $table->addAttribute("comment", $tableResults->TABLE_COMMENT );
            }
            
            foreach($result["Columns"] as $fullPhpPerspectiveName => $columnName) {
                $column = $table->addChild("column");
                if(isset($result["Attributes"][$columnName])) {
                    foreach($result["Attributes"][$columnName] as $attributeName => $attributeValue){
                        if(strlen($attributeValue)) {
                            switch($attributeName){
                                case "required":
                                case "multiple_key":
                                case "primary_key":
                                case "auto_increment":
                                case "unique":
                                    if($val=self::intBool($attributeValue)) {
                                        $column->addAttribute($attributeName, $val);
                                    }
                                    break;
                                default:
                                    $column->addAttribute($attributeName, $attributeValue);
                                    break;

                            }
                        }
                    }
                }
                $column->addAttribute("phpName",$fullPhpPerspectiveName);
            }

            
            if(array_key_exists("Constraints", $result) and count($result["Constraints"])) {
                $constraintsResults = $result["Constraints"];                
                foreach($constraintsResults as $index => $constraints) {
                    $foreignKey = $table->addChild("foreignkey");
                    $foreignKey->addAttribute("type", "constraint");
                    $foreignKey->addAttribute("localKey", $constraints->TABLE_SCHEMA.".".$constraints->TABLE_NAME.".".$constraints->COLUMN_NAME);
                    $foreignKey->addAttribute("referenceKey", $constraints->CONSTRAINT_SCHEMA .".".$constraints->REFERENCED_TABLE_NAME.".".$constraints->COLUMN_NAME);
                    $foreignKey->addAttribute("constraintName", $constraints->CONSTRAINT_NAME);
                    $foreignKey->addAttribute("onUpdate", $constraints->UPDATE_RULE);
                    $foreignKey->addAttribute("onDelete", $constraints->DELETE_RULE);
                    $foreignKey->addAttribute("matchOption", $constraints->MATCH_OPTION);
                }
            } else {
                // using internal referencee from table phpmyadmin
                // myisam engine                
                if(array_key_exists("References", $result) and count($result["References"])) {
                    $constraintsResults = $result["References"];
                    foreach($constraintsResults as $index => $constraints) {
                        $foreignKey = $table->addChild("foreignkey");
                        $foreignKey->addAttribute("type", "relationship");
                        $foreignKey->addAttribute("localKey", $constraints->master_db.".".$constraints->master_table.".".$constraints->master_field);
                        $foreignKey->addAttribute("referenceKey", $constraints->foreign_db.".".$constraints->foreign_table.".".$constraints->foreign_field);
                        $foreignKey->addAttribute("constraintName", $constraints->master_db."_".$constraints->master_table."_".$constraints->master_field);
                        $foreignKey->addAttribute("onUpdate", "NULL");
                        $foreignKey->addAttribute("onDelete", "NULL");
                        $foreignKey->addAttribute("matchOption", "NULL");
                    }
                }

            }
        }

        // serialize
        $xml->asXML($this->outputDirectory . $this->connectionData->database.".xml");

        return $this;

	}


    /**
     * mysql methods
     */

    /**
     *
     * @param string $host
     * @param string $db
     * @param string $user
     * @param string $password
     */
    public function setConnection($host,$db,$user,$password){
        $this->connectionData = new stdClass;
        $this->connectionData->host = $host;
        $this->connectionData->database = $db;
        $this->connectionData->user = $user;
        $this->connectionData->password = $password;
        return $this;
    }

    /**
     * connext
     */
    private function connect() {
        if(empty($this->connection)){
            $this->connection = mysql_connect($this->connectionData->host, $this->connectionData->user, $this->connectionData->password);
            if(empty($this->connection)){
                throw new Exception("Could not establish database connection. Error: ". mysql_error());
            }
        }
    }

    /**
     * query
     * @param string $sql
     * @return void
     */
    private function query($sql) {
        $this->resource = mysql_query($sql);
        return $this;
    }

    /**
     * getObject
     * @return stdClass
     */
    private function getObject() {
        if(is_resource($this->resource)){
            return mysql_fetch_object($this->resource);
        } else {
            throw new Exception("No resource available. Error: ". $this->error());
        }
    }

    /**
     * getPbjects
     * @return array
     */
    private function getObjects() {
        $array = array();
        if($this->getNumRows() and !$this->hasError()){
            while($obj=$this->getObject()){
                $array[] = $obj;
            }
        }
        return $array;
    }

    /**
     * getNumRows
     * @return int
     */
    private function getNumRows(){
        return mysql_num_rows($this->resource);
    }

    /**
     * error
     * @return string
     */
    private function error(){
        return mysql_error($this->connection);
    }

    /**
     * hasError
     * @return mixed
     */
    private function hasError(){
        if (mysql_errno()>0) {
			return mysql_errno();
		}
		return false;
    }

    /**
     * close
     */
    private function close(){
        mysql_close($this->connection);
    }

    /**
     * statements
     */

    /**
     * showTablesIn
     * @param string $contextName database
     * @return resource
     */
	public function showTablesIn($contextName) {
		return $this->query("SHOW TABLES IN ".$contextName.";");
	}

    /**
     * showJoinedReference
     * @param string $contextName database
     * @param string $relationName table
     * @return resource
     */
	public function showJoinedReference($contextName, $relationName) {
		return $this->query("SELECT * FROM phpmyadmin.pma_relation WHERE master_db='".$contextName."' AND master_table='".$relationName."';");
	}

    /**
     * showJoinedConstraintsWithColumnsOnRelation
     * @param string $contextName database
     * @param string $relationName table
     * @return resource
     */
	public function showJoinedConstraintsWithColumnsOnRelation($contextName, $relationName) {
		return $this->query("SELECT distinct col.*,rs.* FROM information_schema.TABLE_CONSTRAINTS ts
LEFT JOIN information_schema.REFERENTIAL_CONSTRAINTS rs ON rs.CONSTRAINT_SCHEMA=ts.CONSTRAINT_SCHEMA AND rs.TABLE_NAME=ts.TABLE_NAME
LEFT JOIN information_schema.COLUMNS col ON col.TABLE_SCHEMA = rs.CONSTRAINT_SCHEMA AND col.TABLE_NAME = rs.TABLE_NAME
WHERE ts.CONSTRAINT_TYPE = 'FOREIGN KEY' AND col.COLUMN_KEY='PRI'  AND col.DATA_TYPE='int'
AND rs.CONSTRAINT_SCHEMA='".$contextName."' AND rs.TABLE_NAME='".$relationName."';");
	}

    /**
     * getTableInfo
     * @param string $contextName database
     * @param string $relationName table
     * @return resource
     */
	public function getTableInfo($contextName, $relationName) {
        return $this->query("SELECT *  FROM information_schema.TABLES WHERE TABLE_SCHEMA='".$contextName."' AND TABLE_NAME='".$relationName."';");
	}

    /**
     * describe
     * @param string $relationName
     * @return array
     */
	public function describe($relationName){

        $header = $this->header;
		if($relationName) {
			$rel = explode(".", $relationName);

			$database = $rel[0];
			$table = $rel[1];

			$header["Databases"]  = array( $database );
			$header["TableNames"] = array( $table );

			$resultObject = $this->query(" SHOW FULL COLUMNS FROM ".$relationName)->getObjects();
            
			foreach($resultObject as $key => $row) {
                $object = SchemeCreator::fetchField($row);                
                $perspectiveName = self::ucfirstAndCamelcased($object->name . 'Of' . ucfirst($table). 'In' . ucfirst($database));
                $header["ColumnNames"][$table.".".$object->name] = $perspectiveName;
                $header["Columns"][$perspectiveName] = $object->name;

                $object->privileges = $row->Privileges;
                $object->comment = $row->Comment;
                
                $header["Attributes"][$object->name] = $object;
			}
		}
		return $header;
	}

    /**
     * convention helpers
     */

    /**
     * fetchField
     * @param stdClass $row
     * @return stdClass
     */
	private static function fetchField($row) {
		$object = new stdClass();
		$object->name = $row->Field;
		$split = preg_split("/[\(\),]/", $row->Type);
		$mode = is_array($split)?$split[0]:$split[0];
		switch($mode) {
			case "varchar":
			case "char":
			case "int":
			case "tinyint":
			case "mediumint":
			case "smallint":
			case "bigint":
			case "bit":
				$object->type = $split[0];
				$object->length = $split[1];
				break;
			case "decimals":
			case "float":
				$object->type = $split[0];
				$object->length = $split[1];
				$object->decimals = $split[2];
				break;
			case "longtext":
			case "longblob":
				$object->max_length = 4294967295;
				$object->type = $mode;
				break;
			case "mediumblob":
			case "mediumtext":
				$object->max_length = 16777215 ;
				$object->type = $mode;
				break;
			case "blob":
			case "text":
				$object->max_length = 65535;
				$object->type = $mode;
				break;
			case "enum":
				$object->type = $mode;
				unset($split[0]);
				unset($split[count($split)]);
				$object->default = implode(",",$split);
				break;
			default:
				$object->type = $mode;
				break;
		}

		$object->required = ($row->Null == "NO") ? true : false;
		$object->multiple_key = (stristr($row->Key,"MUL")) ? true : false;
		$object->primary_key = (stristr($row->Key,"PRI")) ? true : false;
		$object->unique = (stristr($row->Key,"UNI")) ? true : false;
		$object->auto_increment = (stristr($row->Extra,"auto_increment")) ? true : false;
		if(empty($object->default)) {
			$object->default = $row->Default!=""? true : false;
		}
		return $object;

	}

    /**
     * ucfirstAndCamelcased
     * @param string $word
     * @return string
     */
	public static function ucfirstAndCamelcased($word = null) {
		$return = str_replace(" ", "", ucwords(str_replace("_", " ", $word)));
        $string = strtoupper(substr($return,0,1)).substr($return,1);
        return $string;
	}

    /**
     * intBool
     * @param string $value
     * @return mixed
     */
    private static function intBool($value) {
       return ($value==1?"true":false);
    }

}