Location: PHPKode > scripts > MySQL DB Schema to XML and XML to Schema > mysql-db-schema-to-xml-and-xml-to-schema/mysql_db_xml_2_schema.php
<?php
/*
	 @author: Taufeeq - Thu Sep 18 13:51:27 PKT 2008::13:51:27
	 @package: mysql
	 @class: mysql_db_xml_2_schema
	 @desc: class to update the mysql database schema from XML file using DOM
 */

class mysql_db_xml_2_schema{

	private $db_handle;
	private $db_name;
	private $tables;
	private $xml_file;
	private $queries;
	public  $error_msg;

	public function mysql_db_xml_2_schema($xml_file,$db_name,$db_handle=null,$db_host=null,$db_user=null,$db_pass=null){
		$this->db_name = $db_name;
		$this->tables = array();
		$this->queries = array();

		$this->xml_file = $xml_file;

		if($db_handle){
			$this->db_handle = $db_handle;
		}else{
			$this->db_handle = mysql_connect($db_host,$db_user,$db_pass)
			or die("Could not connect to database");
			mysql_select_db($this->db_name,$this->db_handle)
			or die("Could not select the database or database doesn't exist");
		}
	}

	private function _mysql_db_xml_2_schema(){
		mysql_close($this->db_handle);
		unset($this->db_handle,$this->db_name,$this->tables,$this->queries);
	}

	private function getTables(){
		$res = mysql_query("SHOW TABLES FROM ".$this->db_name ,$this->db_handle);
		if($res && (mysql_num_rows($res)>0)){
			while ($tbl_row = mysql_fetch_row($res)) {
				$this->tables[] = $tbl_row[0];
				$this->getTableFields($tbl_row[0]);
			}
		}
	}

	private function getTableFields($table){
		$res = mysql_query("SHOW FULL COLUMNS FROM `$table` ", $this->db_handle);
		if($res && (mysql_num_rows($res)>0)){
			while ($fld_row = mysql_fetch_assoc($res)) {
				$this->tables[$table][] = $fld_row;
			}
		}
	}

	public function XML2Schema(){
		$log = "";
		$tbl_fld_temp_data = array();

		if(!is_file($this->xml_file)){
			$this->error_msg = "File not found, may be removed";
			return false;
		}
		$this->getTables();
		//echo '<pre>';print_r($this->tables);
		//echo in_array("columns_privs",$this->tables);
		$doc = new DOMDocument();
		$doc->load($this->xml_file);
		//$xml = file_get_contents($query);
		//$doc->loadXML($xmlstr);
		$root = $doc->firstChild;
		$charset = $root->getAttribute("charset");
		$engine = $root->getAttribute("engine");

	    $xml_tables = $root->getElementsByTagName( "table" );

	  	foreach( $xml_tables as $xml_tbl){

			$xml_tbl_comments = "";

			$xml_tbl_name = $xml_tbl->getAttribute("name");
	  		$xml_flds = $xml_tbl->getElementsByTagName( "field" );
	  		$xml_tbl_struct = $xml_tbl->getElementsByTagName("structure")->item(0)->nodeValue;
			//echo "<br><br>".$xml_tbl_name."<br><br>";
	  		if(in_array($xml_tbl_name, $this->tables)){
	  			//echo "no need to insert";
                //echo "'".$xml_tbl_name."'<br />";
	  			$tbl_fields = $this->tables["$xml_tbl_name"];
				//print_r($tbl_fields);

				//Array to hold all xml fields names to compare with the existing db at the end
				//$xml_fld_names = array();
	  			foreach ($xml_flds as $xml_fld){
	  				$tbl_fld_type_changed = array();
					$xml_fld_key = null;
					$xml_fld_extra = null;
					$xml_fld_nul = null;
					$xml_fld_default = null;
					$xml_fld_collation = null;
					$xml_fld_comments = "";

					//$xml_fld_names[] =
					$xml_fld_name = $xml_fld->getAttribute("name");
					//echo "<br>".($xml_fld_name);
					$xml_fld_type = $xml_fld->getElementsByTagName("type")->item(0)->nodeValue;

					if(is_object($xml_fld->getElementsByTagName("collation")) && is_object( $xml_fld->getElementsByTagName("collation")->item(0))){
						$xml_fld_collation = $xml_fld->getElementsByTagName("collation")->item(0)->nodeValue;
					}
					if(is_object($xml_fld->getElementsByTagName("null")) && is_object( $xml_fld->getElementsByTagName("null")->item(0))){
						$xml_fld_nul = $xml_fld->getElementsByTagName("null")->item(0)->nodeValue;
					}
					if(is_object($xml_fld->getElementsByTagName("key")) && is_object( $xml_fld->getElementsByTagName("key")->item(0))){
						$xml_fld_key = $xml_fld->getElementsByTagName("key")->item(0)->nodeValue;
					}
					if(is_object($xml_fld->getElementsByTagName("default")) && is_object( $xml_fld->getElementsByTagName("default")->item(0))){
						$xml_fld_default=$xml_fld->getElementsByTagName("default")->item(0)->nodeValue;
					}
					if(is_object($xml_fld->getElementsByTagName("extra")) && is_object( $xml_fld->getElementsByTagName("extra")->item(0))){
						$xml_fld_extra = $xml_fld->getElementsByTagName("extra")->item(0)->nodeValue;
					}
					if(is_object($xml_fld->getElementsByTagName("comment")) && is_object( $xml_fld->getElementsByTagName("comment")->item(0))){
						$xml_fld_comments = $xml_fld->getElementsByTagName("comment")->item(0)->nodeValue;
					}
					$found = false;

					//finding if the field already exists
					foreach ($tbl_fields as $tbl_field){
						if($tbl_field['Field'] == $xml_fld_name){
							//echo "<br><< found : ".$xml_fld_name;
							$found = true;
							break;
						}
					}


					// if exists modify it else add it to the table
					if($found){
						/*
							checking if the type of the existing field conflicts with the
							new coming from xml
						*/
						if(!isset($tbl_field['Collation']))$tbl_field['Collation']=null;
						if(!isset($tbl_field['Null']))$tbl_field['Null']=null;
						if(!isset($tbl_field['Key']))$tbl_field['Key']=null;
						if(!isset($tbl_field['Default']))$tbl_field['Default']=null;
						if(!isset($tbl_field['Extra']))$tbl_field['Extra']=null;
						if(!isset($tbl_field['Comment']))$tbl_field['Comment']="";


						if(($tbl_field['Type'] != $xml_fld_type) or
							($tbl_field['Collation'] != $xml_fld_collation) or
							($tbl_field['Null'] != $xml_fld_nul) or
							($tbl_field['Key'] != $xml_fld_key) or
							($tbl_field['Default'] != $xml_fld_default) or
							($tbl_field['Extra'] != $xml_fld_extra) or
							($tbl_field['Comment'] != $xml_fld_comments)
						 ){
						 	/* //for testing only
							 	echo ("<br>".$tbl_field['Type']." >> ". $xml_fld_type)."<br>".
								($tbl_field['Collation'] ." >> ". $xml_fld_collation) ."<br>".
								($tbl_field['Null']." >> ". $xml_fld_nul) ."<br>".
								($tbl_field['Key'] ." >> ". $xml_fld_key) ."<br>".
								($tbl_field['Default'] ." >> ". $xml_fld_default) ."<br>".
								($tbl_field['Extra'] ." >> ". $xml_fld_extra) ."<br>".
								($tbl_field['Comment'] ." >> ". $xml_fld_comments);
							*/

							//echo '<br>'.$tbl_field['Field'].": ".$tbl_field['Type'] ." >> ". $xml_fld_type;

							//if conflict found then add it to the array to process it after loop to avoid redundency

							/*
								first fetch the data from the existing table and put into the temp
								array then update the filed structure and refill the data again
							*/
							//echo $xml_fld_name." changed <br>______";

							/*
							//Hence mysql changes datatype it self so there is no need to do it ur self
							$res = mysql_query("select $xml_fld_name from $xml_tbl_name ", $this->db_handle);
							if($res && (mysql_num_rows($res) > 0)){
								$temp = array();
								while ($data_row = mysql_fetch_row($res)){
									$temp[] = $data_row[0];
								}
								//echo '<pre>'; var_export($temp); echo '</pre>';
								$tbl_fld_temp_data[$xml_tbl_name][$xml_fld_name.":".$xml_fld_type] = $temp;
								//echo '<pre>'; var_export($tbl_fld_temp_data); echo '</pre>';
							}
							*/

							$query = "ALTER TABLE `$xml_tbl_name` CHANGE `$xml_fld_name` `$xml_fld_name` $xml_fld_type ".
							($xml_fld_collation?" COLLATE $xml_fld_collation ":"").
							 ($xml_fld_nul=='NO'?($xml_fld_default?" default $xml_fld_default":'NOT NULL'):($xml_fld_default?" default $xml_fld_default":'NULL'))
							."  $xml_fld_extra  ;";

							$log .= "\n changing type of field $xml_fld_name of table $xml_tbl_name,  \n\n";
							$this->queries[] = $query;
						}



						//echo "<BR>".	$query;

					}else{
						//echo "insert as new field in same table ".$xml_fld_name;
						 $query = "ALTER TABLE `$xml_tbl_name`
						 ADD `$xml_fld_name` $xml_fld_type
						 ".
						 ($xml_fld_nul=='NO'?($xml_fld_default?" default $xml_fld_default":'NOT NULL'):($xml_fld_default?" default $xml_fld_default":'NULL'))
						."  $xml_fld_extra  ;
						";

						$log .= "\n Adding new field in table $xml_tbl_name as $xml_fld_name \n";
						$this->queries[] = $query;
					}

	  			}


	  		}else{
				//echo $xml_tbl_struct;
				$log.= " \n Creating table \n ".$xml_tbl_name." \n \n";
				$this->queries[] = $xml_tbl_struct;
	  		}
	  		//echo "<br>".$xml_tbl_name;

	  	}
	  	//executing queries
	 // 	echo '<pre>';var_export($this->queries);

	 	/*

	 	if(count($tbl_fld_temp_data) > 0){
	 		foreach ($tbl_fld_temp_data as $table=>$table_data){
	 			foreach ($table_data as $field=>$field_data){
					list($field,$type) = split(":",$field);
					$query = "update $table set $field = ".eval("($type)")
	 			}
	 		}
	 	}

	 	*/
	  	if(count($this->queries)>0){

	  		foreach ($this->queries as $query){
				//echo "<br> Executing: ".$query;
	  			mysql_query($query,$this->db_handle);
	  		}

	  	}

	  	/*
	  		After updating the schema now insert the data for that fields
	  		which type has been changed
	  	*/
	  	$this->_mysql_db_xml_2_schema();
	  	return $log;
	}
}
?>
Return current item: MySQL DB Schema to XML and XML to Schema