Making Document~ Make a Database Schema SQL from StarUML's Class Diagrams

<?php
// input data.

if (PHP_SAPI == 'cli') {
    if ($argc == 2 and $argv[1] == 'help') {
        echo "$argv[0]  input_start_uml_file  db_class_diagram_name\n";
        exit(1);
    }
    $starUmlFile = isset($argv[1]) ? $argv[1] : 'project.uml'; 
    $dbSchemaDiagramName = isset($argv[2]) ? $argv[2] : 'DB';
}
else {
    $argv = explode('/', $_SERVER['PATH_INFO']);
    $starUmlFile = '../' 
        . preg_replace('/\\\\/', '', $argv[1])
        . '/doc/'
        . preg_replace('/\\\\/', '', $argv[2]);
    $dbSchemaDiagramName= $argv[3];
}

/**
 * CODE START
 */

if ( !is_readable($starUmlFile) ) {
    echo $starUmlFile, " is not readable!\n";
    exit(1);
}
  
// Get the class diagram of database schema

foreach (simplexml_load_file($starUmlFile)->xpath('//XPD:OBJ[@type="UMLModel"]/XPD:OBJ[@type="UMLClassDiagram"]') 
    as $classDiagram) 
{
    $name = (string) current($classDiagram->xpath('XPD:ATTR[@name="Name"]'));
    if ($name == $dbSchemaDiagramName) {
        $dbSchemaXml = $classDiagram; 
        break;
    }
}

if ( !isset($dbSchemaXml) ) {
    echo $dbSchemaDiagramName, " is not found!\n";
    exit(1);
}

$viewTypeSet = array(
    'UMLClassView',
    'UMLNoteView',
    'UMLNoteLinkView'
); 

foreach ($viewTypeSet as $viewName) {
    $viewSet = $viewName . 'Set'; // like 'UMLClassViewSet'

    $$viewSet = array();  // $UMLClassViewSet = array();


    foreach ($dbSchemaXml->xpath("XPD:OBJ[@type='UMLClassDiagramView']/XPD:OBJ[@type='{$viewName}']") 
        as $viewXmlNode) 
    {
        ${$viewSet}[(string)$viewXmlNode['guid']] = $viewXmlNode;
    }
}

/**
 * Fetch each note text associatied to class.
 */ 
$classNoteSet = array();
foreach ($UMLNoteLinkViewSet as $guid => $noteLinkView) {
    if ($refSet = $noteLinkView->xpath('XPD:REF')) { // if not false

        $headGuid = (string)$refSet[0];
        $tailGuid = (string)$refSet[1];

        if (isset($UMLClassViewSet[$headGuid])) {
            $classGuid = (string)current($UMLClassViewSet[$headGuid]->xpath('XPD:REF'));
            // xpath('XPD:REF[1]')

           
            $classNote = $UMLNoteViewSet[$tailGuid];
        }
        else if (isset($UMLClassViewSet[$tailGuid])) {
            $classGuid = (string)current($UMLClassViewSet[$tailGuid]->xpath('XPD:REF'));
            // xpath('XPD:REF[1]')

           
            $classNote = $UMLNoteViewSet[$headGuid];
        }
        
        $classNoteSet[$classGuid] = (string)current($classNote->xpath('XPD:ATTR[@name="Text"]'));
    }
} // endforeach ($UMLNoteLinkViewSet as $guid => $noteLinkView)



/**
 * Fetch table's information 
 */  
$tableSet = array();
foreach ($UMLClassViewSet as $guid => $node) {
    $tableGuid = (string)current($node->xpath('XPD:REF[@name="Model"]'));
    $tableNode = current($dbSchemaXml->xpath('../XPD:OBJ[@guid="' . $tableGuid . '"]'));
    $tableName = (string)current($tableNode->xpath('XPD:ATTR[@name="Name"]'));

    $tableSet[$tableGuid] = array();
    $tableSet[$tableGuid]['name'] = $tableName;

    $currentTable =& $tableSet[$tableGuid];
    if (isset($classNoteSet[$tableGuid]))
        $tableNotes = explode("\n", $classNoteSet[$tableGuid]);
    else
        $tableNotes = array(); //There is no note that link to this class.


    $tableFields = $tableNode->xpath('XPD:OBJ[@type="UMLAttribute"]');
    foreach ($tableFields as $field) {
        $fieldName = (string)current($field->xpath('XPD:ATTR[@name="Name"]'));
        $fieldDataType = current($field->xpath('XPD:ATTR[@name="TypeExpression"]'));
        if ($fieldDataType != false) {
            $fieldDataType = (string)$fieldDataType;
        }
        else {
            $fieldDataType = 'int'; //default type

        }

        $currentTable['fields'][$fieldName] = array();
        $currentTable['fields'][$fieldName]['dataType'] = $fieldDataType;

        $currentTable['fields'][$fieldName]['note'] = fetchNoteOfField($fieldName, &$tableNotes);
        
    } // endforeach ($tableFields as $field)

} // endforeach ($dbSchemaXml->xpath('../XPD:OBJ[@type="UMLClass"]') as $tableNode)


/**
 * Fetch note of field from tableNotes.
 *
 * 以 "空行" 或 "非空格開頭" 為分段。
 * 找尋開頭為 欄位名稱 的註解段落，該段落皆為此欄位的註解內容。
 */  
function fetchNoteOfField($fieldName, $tableNotes) {
    $fetching = false;
    $noteText = '';

    foreach ($tableNotes as $noteLine) {
        //找尋開頭為 欄位名稱 的註解段落

        if ((stripos($noteLine, $fieldName) === 0) and !$fetching) {
            $fetching = true;
            $noteText = trim(substr($noteLine, strlen($fieldName)), ' :');
        }
        else if ($fetching) {
            //"空行" 或 "非空格開頭" 分段。

            if (empty($noteLine) or (strpos($noteLine, ' ') !== 0))
                break;

            //空格開頭，視為同一段落，接在先前的內容之後。

            $noteText .= trim($noteLine);
        }
    }
    return $noteText;
}

function makeSQLCreateTableString($table) {
    $fields = array();
    $n = count($table['fields']);

    //foreach ($table['fields'] as $name => $field) {

    for ($i = 1; $i <= $n; ++$i) {
        list($name, $field) = each($table['fields']);
        $fields[] = "    "{$name}"    {$field['dataType']}" 
            . ($i < $n ? ', ' : '')
            . (empty($field['note']) ? '' : " -- {$field['note']}")
            . "\n";
    }

    $SQLString = "CREATE TABLE "{$table['name']}" (\n"
        . implode('', $fields)
        . ");\n"; 

    return $SQLString;
}

/**
 * Output 
 */ 
if (PHP_SAPI != 'cli')
    header('Content-type: text/plain; charset="utf-8"');

foreach ($tableSet as $table) {
    echo makeSQLCreateTableString(&$table);
}

?>

# php makeDbSchemaFromUml example.uml DB


CREATE TABLE "Product" (
    "id"    int primary key, 
    "name"    varchar(255),  -- 商品名稱
    "cost"    numeric(10,2) not null -- 成本
);
CREATE TABLE "Customer" (
    "id"    int primary key, 
    "name"    varchar(255)
);
CREATE TABLE "Order" (
    "id"    int primary key, 
    "customerId"    int references "Customer"(id),  -- 顧客編號
    "orderDate"    timestamp,  -- 訂購時間
    "deliveryAddress"    varchar(255),  -- 送貨地址
    "deliveryDate"    timestamp,  -- 送貨時間
    "type"    char -- 訂單類型
);
CREATE TABLE "OrderItem" (
    "id"    int primary key, 
    "orderId"    int references "Order"(id), 
    "productId"    int references "Product"(id), 
    "price"    numeric(10,2) not null,  -- 售價
    "qty"    int not null -- 購買數量
);