Posted By

jonniespratley on 03/09/09


Tagged

mysql php xml


Versions (?)

Who likes this?

3 people have marked this snippet as a favorite

rezzz
thastyle
umang_nine


MySQL Schema XML Export


 / Published in: PHP
 

Export entire database schema to local xml file.

  1. private function execute( $sql )
  2. {
  3. return $this->mysqli->query ( $sql );
  4. }
  5. /**
  6.  * I save the entire database schema to a local xml file.
  7.  *
  8.  * @param [string] $database the name of your database
  9.  * @return [result] true or false
  10.  */
  11. public function saveSchemaXML( $database )
  12. {
  13. $dom = new DOMDocument ( '1.0' );
  14.  
  15. /************************************
  16. * Builds the root
  17. ************************************/
  18. //create a element
  19. $schema = $dom->createElement ( 'schema' );
  20. //set the element on itself
  21. $schema = $dom->appendChild ( $schema );
  22. //set a attribute for the schema node
  23. $schema->setAttribute ( 'name', $database );
  24.  
  25. /***********************************
  26. * Builds the table inside the root
  27. **********************************/
  28. $tableQuery = $this->execute ( "SHOW TABLES FROM $database" );
  29.  
  30. while ( $tableRow = mysqli_fetch_row ( $tableQuery ) )
  31. {
  32. //create a element
  33. $table = $dom->createElement ( 'table' );
  34. //set the element on itself
  35. $table = $dom->appendChild ( $table );
  36. //set a attribute
  37. $table->setAttribute ( 'name', $tableRow [ 0 ] );
  38.  
  39. $fieldQuery = $this->execute ( "DESCRIBE $database.$tableRow[0]" );
  40.  
  41. while ( $fieldRow = mysqli_fetch_assoc ( $fieldQuery ) )
  42. {
  43. /***********************************
  44. * Builds the attributes inside the table
  45. **********************************/
  46. //create a element
  47. $field = $dom->createElement ( 'field' );
  48. //set the element on itself
  49. $field = $dom->appendChild ( $field );
  50. //set the name attribute
  51. $field->setAttribute ( 'name', $fieldRow [ 'Field' ] );
  52. //set the type attribute
  53. $field->setAttribute ( 'type', $this->replaceNumbers ( $fieldRow [ 'Type' ] ) );
  54. //set the null attribute
  55. $field->setAttribute ( 'null', strtolower ( $fieldRow [ 'Null' ] ) );
  56.  
  57. if ( $fieldRow [ 'Default' ] != '' )
  58. {
  59. //set the default
  60. $field->setAttribute ( 'default', strtolower ( $fieldRow [ 'Default' ] ) );
  61. }
  62. if ( $fieldRow [ 'Key' ] != '' )
  63. {
  64. //set the key
  65. $field->setAttribute ( 'key', strtolower ( $fieldRow [ 'Key' ] ) );
  66. }
  67. if ( $fieldRow [ 'Extra' ] != '' )
  68. {
  69. //set the value/length attribute
  70. $field->setAttribute ( 'extra', strtolower ( $fieldRow [ 'Extra' ] ) );
  71. }
  72.  
  73. //put the field inside of the table
  74. $table->appendChild ( $field );
  75. }
  76.  
  77. //put the table inside of the schema
  78. $schema->appendChild ( $table );
  79. }
  80.  
  81. $dom->formatOutput = true;
  82. $dom->saveXML ();
  83. $filename = 'output/' . ucfirst ( $database ) . 'Schema.xml';
  84.  
  85. //save the file
  86. $xml = $dom->save ( $filename );
  87.  
  88. //change the permissions
  89. chmod ( "$filename", 0777 );
  90.  
  91. return $xml;
  92. }

Report this snippet  

You need to login to post a comment.