Skip to content

Understanding the Workbench Xml database scheme

Jump to bottom Edit New page
johmue edited this page May 30, 2012 · 3 revisions

The Workbench XML database scheme

If you want to take a look at the raw data of the database scheme you have to open document.mwb.xml with a normal text/xml editor. The model can easily consume 6.000+ lines of XML.

The raw data can be used to identify all options and values concerning a special database object e.g. scale, precision, defaultValue etc. for a column.

Here I will show you just the basic concept without the included informations.

The basic structure

<?xml version="1.0"?>
<data document_type="MySQL Workbench Model">
    <value struct-name="workbench.Document">
        ...
        <value key="physicalModels">
            <value struct-name="workbench.physical.Model">
                <value key="catalog">
                    <!-- ## IMPORTANT PART FOLLOWS HERE ## -->
                    ...
                </value>
            </value>
        </value>
        ...
    </value>
</data>

The catalog structure

...
<value key="catalog">
    <value key="schemata">
        <!-- schema -->
        <value struct-name="db.mysql.Schema" id="{...}">
            <value key="tables>
                <!-- ## LIST OF TABLES FOLLOWS HERE ## -->
                ...
            </value>
            <value key="name">schema1</value>
            <value key="comment"></value>
            <value key="defaultCharacterSetName"></value>
            <value key="defaultCollationName"></value>
            ...
        </value>

        <!-- next schema -->
        <value struct-name="db.mysql.Schema" id="{...}">
            <value key="name">schema2</value>
            ...
        </value>
        ...
    </value>
</value>
...

The tables structure

...
<value key="tables>
    ...
    <value struct-name="db.mysql.Table">
        <value key="columns">
            <value struct-name="column">
                <value key="name">column1</value>
                ...
            </value>
            <value struct-name="column">
                <value key="name">column2</value>
                ...
            </value>
            ...
        </value>
        <value key="name">table1</value>
        ...
    </value>
    ...
</value>
...

The PHP object tree of the database structure

The mysql-workbench-schema-exporter tool parses the XML and builds an object tree using the same structure so that the tool can walk through the object graph and generate the requested output.

Here you can see the PHP object tree:

|
|-- Document
|   |-- PhysicalModel
|       |-- Catalog
|           |-- Schemas
|               |-- Schema
|               |   |-- Tables
|               |   |   |-- Table
|               |   |   |   |-- Columns
|               |   |   |   |   |-- Column
|               |   |   |   |   |-- Column
|               |   |   |   |   |-- ...
|               |   |   |   |
|               |   |   |   |-- Indices
|               |   |   |   |   |-- Index
|               |   |   |   |   |-- Index
|               |   |   |   |   |-- ...
|               |   |   |   |
|               |   |   |   |-- ForeignKeys
|               |   |   |       |-- ForeignKey
|               |   |   |       |-- ForeignKey
|               |   |   |       |-- ...
|               |   |   |
|               |   |   |-- Table
|               |   |   |   |-- ...
|               |   |   |
|               |   |   |-- ...
|               |   |
|               |   |-- Views
|               |       |-- View
|               |       |   |-- Columns
|               |       |       |-- Column
|               |       |       |-- Column
|               |       |       |...
|               |       |
|               |       |-- View
|               |       |   |-- ...
|               |       |
|               |       |-- ...
|               |
|               |-- Schema
|               |   |-- ...
|               |
|               |-- ...
|

For each type of object you will find a corresponding php file in the lib/MwbExporter/Model directory. The instantiated objects will include all the informations from the XML structure so that you can access easily all the informations you need.

Each of these objects extends the lib/MwbExporter/Model/Base class which provides some basic methods that are equal to all objects like parseComment(), getParent() or debug().

Real world XML example structures

Column

...
<value type="object" struct-name="db.mysql.Column" id="{8C1CFD3F-A65F-4CC3-9791-150781C7C4C1}" struct-checksum="0x783b5183">
    <value type="int" key="autoIncrement">0</value>
    <value type="string" key="characterSetName"></value>
    <value _ptr_="0A345320" type="list" content-type="object" content-struct-name="db.CheckConstraint" key="checks"/>
    <value type="string" key="collationName"></value>
    <value type="string" key="datatypeExplicitParams"></value>
    <value type="string" key="defaultValue">CURRENT_TIMESTAMP</value>
    <value type="int" key="defaultValueIsNull">0</value>
    <value _ptr_="0A345378" type="list" content-type="string" key="flags">
        <value type="string">UNSIGNED</value>
    </value>
    <value type="int" key="isNotNull">1</value>
    <value type="int" key="length">-1</value>
    <value type="int" key="precision">-1</value>
    <value type="int" key="scale">-1</value>
    <link type="object" struct-name="db.SimpleDatatype" key="simpleType">com.mysql.rdbms.mysql.datatype.timestamp</link>
    <value type="string" key="comment"></value>
    <value type="string" key="name">last_update</value>
    <value type="string" key="oldName">last_update</value>
    <link type="object" struct-name="GrtObject" key="owner">{E1526C7B-1737-41A7-B4C2-8AEA23E4C62E}</link>
</value>
...

ForeignKey

...
<value type="object" struct-name="db.mysql.ForeignKey" id="{763F42B3-0F6D-4E76-827D-D182084D56AB}" struct-checksum="0x70a8fc40">
    <link type="object" struct-name="db.mysql.Table" key="referencedTable">{9A9B78B3-FE80-4FC3-AAE0-A6C97802FBEC}</link>
    <value _ptr_="0A344DA0" type="list" content-type="object" content-struct-name="db.Column" key="columns">
        <link type="object">{295FF693-FDE3-4E51-BC6B-7B2862EFC13D}</link>
    </value>
    <value type="int" key="deferability">0</value>
    <value type="string" key="deleteRule">RESTRICT</value>
    <link type="object" struct-name="db.Index" key="index">{C6C744B3-C82B-42B1-93E1-9C423BCCDFBB}</link>
    <value type="int" key="mandatory">1</value>
    <value type="int" key="many">1</value>
    <value type="int" key="modelOnly">0</value>
    <link type="object" struct-name="db.Table" key="owner">{98BA5D3B-DCF0-47DE-BCFF-EC5C0D35C2A6}</link>
    <value _ptr_="0A344DF8" type="list" content-type="object" content-struct-name="db.Column" key="referencedColumns">
        <link type="object">{CBA1CA45-0B69-4F69-9DAD-70DB0686E9C1}</link>
    </value>
    <value type="int" key="referencedMandatory">1</value>
    <value type="string" key="updateRule">CASCADE</value>
    <value type="string" key="comment"></value>
    <value type="string" key="name">fk_users_address</value>
    <value type="string" key="oldName">fk_users_address</value>
</value>
...

If you follow the foreign key structure ForeignKey->columns you can see that the <link> references an object with id {...-7B2862EFC13D} which is the internal MySQL Workbench id of the address_id column in the users table example below.

*ForeignKey->referencedTable links to the table id {...-A6C97802FBEC} which is the internal id of the addresses table.

*ForeignKey->owner links to the table id {...-EC5C0D35C2A6} which is the internal id of the users table (see below).

*ForeignKey->referencedColumns links to the column id {...-70DB0686E9C1} which is the internal id of the id column in the addresses table.

Index

...
<value type="object" struct-name="db.mysql.Index" id="{60651453-7FD8-456E-BFB7-19555A00B4F0}" struct-checksum="0x309b847a">
    <value _ptr_="0A344E50" type="list" content-type="object" content-struct-name="db.mysql.IndexColumn" key="columns">
        <value type="object" struct-name="db.mysql.IndexColumn" id="{002905A8-A71D-429A-B65F-E1269981942D}" struct-checksum="0x62630b3c">
            <value type="int" key="columnLength">0</value>
            <value type="string" key="comment"></value>
            <value type="int" key="descend">0</value>
            <link type="object" struct-name="db.Column" key="referencedColumn">{53006D22-2B79-4306-ABA4-0BB4131D44E6}</link>
            <value type="string" key="name">id</value>
            <link type="object" struct-name="GrtObject" key="owner">{60651453-7FD8-456E-BFB7-19555A00B4F0}</link>
        </value>
    </value>
    <value type="string" key="indexKind"></value>
    <value type="int" key="keyBlockSize">0</value>
    <value type="string" key="withParser"></value>
    <value type="string" key="comment"></value>
    <value type="int" key="deferability">0</value>
    <value type="string" key="indexType">PRIMARY</value>
    <value type="int" key="isPrimary">1</value>
    <value type="string" key="name">PRIMARY</value>
    <value type="int" key="unique">0</value>
    <value type="string" key="oldName">PRIMARY</value>
    <link type="object" struct-name="GrtObject" key="owner">{98BA5D3B-DCF0-47DE-BCFF-EC5C0D35C2A6}</link>
</value>
...

If you follow the index structure Index -> columns -> IndexColumn -> referencedColumn, you can see that the id {...-0BB4131D44E6} of the referenced column references the first column id of the users table in the example below. It's the XML definition of the primary index of the id column.

Table

...
<value type="object" struct-name="db.mysql.Table" id="{98BA5D3B-DCF0-47DE-BCFF-EC5C0D35C2A6}" struct-checksum="0x1a403946">
    <value type="string" key="avgRowLength"></value>
    <value type="int" key="checksum">0</value>
    <value _ptr_="0A3443A8" type="list" content-type="object" content-struct-name="db.mysql.Column" key="columns">
        <value type="object" struct-name="db.mysql.Column" id="{53006D22-2B79-4306-ABA4-0BB4131D44E6}" struct-checksum="0x783b5183">
            <value type="int" key="autoIncrement">1</value>
            <link type="object" struct-name="db.SimpleDatatype" key="simpleType">com.mysql.rdbms.mysql.datatype.tinyint</link>
            <value type="int" key="isNotNull">1</value>
            <value type="string" key="comment"></value>
            <value type="string" key="name">id</value>
            ...
        </value>
        <value type="object" struct-name="db.mysql.Column" id="{8BA74697-BFE0-40A1-B192-34490AC424DA}" struct-checksum="0x783b5183">
            <value type="string" key="name">first_name</value>
            ...
        </value>
        <value type="object" struct-name="db.mysql.Column" id="{719902CC-B9A3-4157-9569-00EAAD6B6D8A}" struct-checksum="0x783b5183">
            <value type="string" key="name">last_name</value>
            ...
        </value>
        <value type="object" struct-name="db.mysql.Column" id="{295FF693-FDE3-4E51-BC6B-7B2862EFC13D}" struct-checksum="0x783b5183">
            <value type="string" key="name">address_id</value>
        </value>
        <value type="object" struct-name="db.mysql.Column" id="{11B6C729-7327-40B0-B732-0DE9FF355F09}" struct-checksum="0x783b5183">
            <value type="string" key="name">email</value>
        </value>
        <value type="object" struct-name="db.mysql.Column" id="{C6A9A8EF-E4AC-497D-A3A9-B9031239AB23}" struct-checksum="0x783b5183">
            <value type="string" key="name">active</value>
        </value>
        <value type="object" struct-name="db.mysql.Column" id="{C5D84186-7325-4617-99C9-8DC4C8554874}" struct-checksum="0x783b5183">
            <value type="string" key="name">username</value>
        </value>
        <value type="object" struct-name="db.mysql.Column" id="{3A1D7A9F-1BD2-423C-8F90-435C076154C6}" struct-checksum="0x783b5183">
            <value type="string" key="name">password</value>
        </value>
        <value type="object" struct-name="db.mysql.Column" id="{EF743A00-2965-4583-B083-6209A19E1D0E}" struct-checksum="0x783b5183">
            <value type="string" key="name">last_update</value>
        </value>
    </value>
    <value type="string" key="connectionString"></value>
    <value type="string" key="defaultCharacterSetName">utf8</value>
    <value type="string" key="defaultCollationName"></value>
    <value type="int" key="delayKeyWrite">0</value>
    <value _ptr_="0A344400" type="list" content-type="object" content-struct-name="db.mysql.ForeignKey" key="foreignKeys">
        <!-- ## see ForeignKey ## -->
        ...
    </value>
    <value _ptr_="0A344458" type="list" content-type="object" content-struct-name="db.mysql.Index" key="indices">
        <!-- ## see Index ## -->
        ...
    </value>
    <value type="string" key="maxRows"></value>
    <value type="string" key="mergeInsert"></value>
    <value type="string" key="mergeUnion"></value>
    <value type="string" key="minRows"></value>
    <value type="string" key="nextAutoInc"></value>
    <value type="string" key="packKeys"></value>
    <value type="int" key="partitionCount">0</value>
    <value _ptr_="0A344508" type="list" content-type="object" content-struct-name="db.mysql.PartitionDefinition" key="partitionDefinitions"/>
    <value type="string" key="partitionExpression"></value>
    <value type="string" key="partitionType"></value>
    <value type="string" key="password"></value>
    <link type="object" struct-name="db.mysql.Index" key="primaryKey">{60651453-7FD8-456E-BFB7-19555A00B4F0}</link>
    <value type="string" key="raidChunkSize"></value>
    <value type="string" key="raidChunks"></value>
    <value type="string" key="raidType"></value>
    <value type="string" key="rowFormat"></value>
    <value type="int" key="subpartitionCount">0</value>
    <value type="string" key="subpartitionExpression"></value>
    <value type="string" key="subpartitionType"></value>
    <value type="string" key="tableDataDir"></value>
    <value type="string" key="tableEngine">InnoDB</value>
    <value type="string" key="tableIndexDir"></value>
    <value _ptr_="0A3444B0" type="list" content-type="object" content-struct-name="db.mysql.Trigger" key="triggers"/>
    <value type="int" key="isStub">0</value>
    <value type="int" key="isSystem">0</value>
    <value type="int" key="isTemporary">0</value>
    <value type="string" key="temporaryScope"></value>
    <value type="int" key="commentedOut">0</value>
    <value type="string" key="createDate"></value>
    <value _ptr_="0A1C7588" type="dict" key="customData"/>
    <value type="string" key="lastChangeDate">2010-09-14 11:00</value>
    <value type="int" key="modelOnly">0</value>
    <value type="string" key="name">users</value>
    <value type="string" key="temp_sql"></value>
    <value type="string" key="comment"></value>
    <value type="string" key="oldName">users</value>
    <link type="object" struct-name="GrtObject" key="owner">{C15C8D5E-989D-48B7-AA03-48CB5CCF5A01}</link>
</value>
...
Add a custom footer
Add a custom sidebar

Clone this wiki locally