Tuesday, March 1, 2016

Sql 2 Json Schema

Voici un petit script qui fait une introspection de la base de donnee , ici wordpress , et qui genere des fichiers json schema.


class SchemaProvider {
        var $_tables = array();
  var $_fields = array();
  
  function __construct($database)
  {
   mysql_connect("localhost", "root", "");
   
   $this->fetchTables($database);
   foreach( $this->_tables as $t )
   {
    $this->_fields[$t] = $this->fetchFields($t);
   }
  }
  
  function fetchTables($database)
  {
   $result = mysql_list_tables($database);
   $num_rows = mysql_num_rows($result);
   $this->_tables = array();
   for ($i = 0; $i < $num_rows; $i++) {
      $this->_tables []= mysql_tablename($result, $i);
   }
   mysql_free_result($result);
  }
  
  function fetchFields( $table)
  {
   $result = array();
  
  
   $sql = mysql_query("SELECT * FROM $table LIMIT 1");
   for ($i = 0; $i < mysql_num_fields($sql); $i++) {
    $meta = mysql_fetch_field($sql, $i);
    $result[] = $meta;
   }
   /*
   $resultdb = mysql_query ("SHOW COLUMNS FROM ". $table);
   while ($row = mysql_fetch_array($resultdb, MYSQL_NUM)) {
    
    $result[] = new FieldStructure( $row );
   }
   mysql_free_result($resultdb);
   */
   return $result;
  }
};


class SchemaToJsonSchema {

 var $jsonschemaarray = array();
 function __construct( $schema )
 {
   foreach( $schema as $key=>$s )
   {
     $this->jsonschemaarray[$key] = $this->convertFromTable( $s );
   }
 }
 
 function get_type_json( $type ) {
  switch( $type ) 
  {
  case "int":
   return "integer";
  case "blog":
   return "string";
  case "datetime":
   return "string";
  default:
   return $type;
  }
 }
 
 function convertFromTable( $tableschema )
 {
    $result = array();
    foreach( $tableschema as $schema_item )
    {
      $type = $this->get_type_json( $schema_item->type );
  $result[ $schema_item->name ] = array( "type"=> $this->get_type_json( $schema_item->type ) ); 
  
         if ( $schema_item->type  == "string" ) {
    $result[ $schema_item->name ]["maxLength"] = $schema_item->max_length; 
    }
   
   if ( $schema_item->type  == "datetime" ) {
   $result[ $schema_item->name ]["format"] = "date-time"; 
   }
   
   if ( $schema_item->type == "int" && $schema_item->unsigned  == 1 ) {
   $result[ $schema_item->name ]["minimum"] = 0; 
   }
   
   
   
   if ( empty( $schema_item->desc ) == false ) {
   $result[ $schema_item->name ]["description"] = $schema_item->desc; 
   }
   }
 return $result;
 }
 
 function get_jsonschema($table)
 {
   $result = array();
   $result["\$schema"] = "http://json-schema.org/draft-04/schema#";
      $result["title"] = $table;
      $result["description"] = $table;
      $result["type"] = "object";
      $result["properties"] = $this->jsonschemaarray[$table];
   return $result;
   }
};


$database = "wordpress";
$schema = new SchemaProvider($database);
$convert = new SchemaToJsonSchema( $schema->_fields );

//print_r($convert->jsonschemaarray);
if ( is_dir ("schema\\$database") == false ) {
 mkdir("schema\\$database");
}
foreach( $convert->jsonschemaarray as $key=>$t){
  file_put_contents("schema\\$database\\$key.json" ,json_encode($convert->get_jsonschema($key),JSON_PRETTY_PRINT));  
}
//print_r($schema);

No comments: