MySQL Database Schema XML Export

MySQL Database Schema Export

The below PHP function used to export the Mysql database schema in XML format. This exported XML schema contains all the details and the data set such as DB name, table and the attributes bundled in to a single unit. Here the chmod is used to set the file permission to modify the exported MySQL Schema

PHP Code to MySQL Schema XML Export


private function execute( $sql )
{
return $this->mysqli->query ( $sql );
}
/**
* I save the entire database schema to a local xml file.
*
* @param [string] $database the name of your database
* @return [result] true or false
*/
public function saveSchemaXML( $database )
{
$dom = new DOMDocument ( '1.0' );

/************************************
* Builds the root
************************************/
//create a element
$schema = $dom->createElement ( 'schema' );
//set the element on itself
$schema = $dom->appendChild ( $schema );
//set a attribute for the schema node
$schema->setAttribute ( 'name', $database );

/***********************************
* Builds the table inside the root
**********************************/
$tableQuery = $this->execute ( "SHOW TABLES FROM $database" );

while ( $tableRow = mysqli_fetch_row ( $tableQuery ) )
{
//create a element
$table = $dom->createElement ( 'table' );
//set the element on itself
$table = $dom->appendChild ( $table );
//set a attribute
$table->setAttribute ( 'name', $tableRow [ 0 ] );

$fieldQuery = $this->execute ( "DESCRIBE $database.$tableRow[0]" );

while ( $fieldRow = mysqli_fetch_assoc ( $fieldQuery ) )
{
/***********************************
* Builds the attributes inside the table
**********************************/
//create a element
$field = $dom->createElement ( 'field' );
//set the element on itself
$field = $dom->appendChild ( $field );
//set the name attribute
$field->setAttribute ( 'name', $fieldRow [ 'Field' ] );
//set the type attribute
$field->setAttribute ( 'type', $this->replaceNumbers ( $fieldRow [ 'Type' ] ) );
//set the null attribute
$field->setAttribute ( 'null', strtolower ( $fieldRow [ 'Null' ] ) );

if ( $fieldRow [ 'Default' ] != '' )
{
//set the default
$field->setAttribute ( 'default', strtolower ( $fieldRow [ 'Default' ] ) );
}
if ( $fieldRow [ 'Key' ] != '' )
{
//set the key
$field->setAttribute ( 'key', strtolower ( $fieldRow [ 'Key' ] ) );
}
if ( $fieldRow [ 'Extra' ] != '' )
{
//set the value/length attribute
$field->setAttribute ( 'extra', strtolower ( $fieldRow [ 'Extra' ] ) );
}

//put the field inside of the table
$table->appendChild ( $field );
}

//put the table inside of the schema
$schema->appendChild ( $table );
}

$dom->formatOutput = true;
$dom->saveXML ();
$filename = 'output/' . ucfirst ( $database ) . 'Schema.xml';

//save the file
$xml = $dom->save ( $filename );

//change the permissions
chmod ( "$filename", 0777 );

return $xml;
}


Related Topic CRON MySQL Database Backup Script
Mysql Import Dump file in Command line
Increase MySQL connection Limit
Setting permission for stored procedure mysql
Maximum Table Size in mysql
SQL  mysql  database  PHP  XML  

nScraps.com 2011   Privacy Policy  Terms of Service  Feedback