Vertabelo XML version 2.1

Vertabelo internally stores your model in an XML format. You can download the XML file and use it to extract some information by using a script. Let’s take a look at the structure of this XML file.

Here is the XSD file and an example of the XML file for the above sample database model created in Vertabelo.

The root element of the document is the DatabaseModel element with the VersionId attribute. As of today, the current version is 2.1.

<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<DatabaseModel VersionId="2.1">
...
</DatabaseModel>

The DatabaseModel has the following elements:

• ModelGid – for backward compatibility
• ModelVersionGid – for backward compatibility
• Counters – for autonumbering objects in the XML file. See [Counter and ids] section.
• AdditionalSQLBeforeCreate – SQL instructions to put at the beginning of the create script
• AdditionalSQLAfterCreate – SQL instructions to put at the end of the create script
• AdditionalSQLBeforeDrop – SQL instructions to put at the beginning of the drop script
• AdditionalSQLAfterDrop – SQL instructions to put at the end of the drop script
• Tables – table definitions
• Views – view definitions
• References – reference definitions
• Sequences – sequence definitions
• Notes – notes
• Areas – subject areas
• Texts – reserved for future use
• TableDisplays – graphical table representations
• ViewDisplays – graphical view representations
• ReferenceDisplays – graphical reference representations
• Properties – model properties

Counters and IDs

Each counter has a name, value, and prefix.

<Counter>
   <Name>table</Name>
   <Value>78</Value>
   <Prefix>t</Prefix>
</Counter>

Each model has the following counters:

1. table, prefix t
2. column, prefix c
3. index, prefix i
4. reference, prefix r
5. table_check, prefix tc
6. alternate_key, prefix a
7. sequence, prefix s
8. note, prefix n
9. view, prefix v
10. area, prefix ar
11. text, prefix tx
12. view_column, prefix vc
13. tableDisplay, prefix td
14. viewDisplay, prefix vd
15. referenceDisplays, prefix rd

Counters are used for generating IDs of different kinds of objects (tables, columns, indexes and so on). The ID of an object is the prefix followed by a number. For example, IDs for tables are “t35,” “t145.” The value of the counter has to be higher than any of the numbers already used for a class of objects. If the highest table ID is t140 then the table counter has to be 141 or more.

Tables

Each table has the following format.

<Table Id="t1">
    <Name>book</Name>
    <Description></Description>
    <Columns>
        <Column Id="c1">
            <Name>id</Name>
            <Type>int4</Type>
            <Description></Description>
            <Nullable>false</Nullable>
            <PK>true</PK>
            <DefaultValue></DefaultValue>
            <CheckExpression></CheckExpression>
            <Properties/>
        </Column>
        <Column Id="c2">
            <Name>isbn</Name>
            <Type>varchar(15)</Type>
            <Description></Description>
            <Nullable>false</Nullable>
            <PK>false</PK>
            <DefaultValue></DefaultValue>
            <CheckExpression></CheckExpression>
            <Properties/>
        </Column>
        ...
    </Columns>
    <AlternateKeys>
        <AlternateKey Id="a1">
            <Name>AK_KEY_ISBN</Name>
            <Columns>
                <Column>c2</Column>
            </Columns>
            <Properties/>
    </AlternateKey>
    <Indexes>
        <Index Id="i1">
            <Name>some_index</Name>
            <Columns>
                <Column>c3</Column>
                <Ascending>true</Ascending>
            </Columns>
            <Properties/>
        </Index>/>
    <Indexes/>
    <TableChecks>
        <TableCheck Id="tc2">
            <Name>silly_check</Name>
            <Description></Description>
            <CheckExpression>id > 1500</CheckExpression>
            <Properties/>
        </TableCheck>
    <TableChecks/>
    <AdditionalSQLBefore></AdditionalSQLBefore>
    <AdditionalSQLAfter></AdditionalSQLAfter>
    <Properties/>
</Table>

Hopefully, the fields are self-explanatory. Of course, a table may have no alternative keys, table checks, or indexes. In this case the corresponding XML element is empty.

The Table element describes the database table, not the graphical representation of the table. The graphical representations are defined in the TableDisplays section. Each table has to have at least one TableDisplay representation. Otherwise, your model is invalid and Vertabelo will refuse to import the XML file.

Views

An example view definition:

<View Id="v1">
    <Name>all_books_view</Name>
    <Description></Description>
    <SQLQuery>select * from book</SQLQuery>
    <ViewColumns>
       <ViewColumn Id="vc78">
           <Name>id</Name>
           <Type>int4</Type>
           <Description></Description>
           <Properties/>
       </ViewColumn>
       ...
    </ViewColumns>
    <AdditionalSQLBefore></AdditionalSQLBefore>
    <AdditionalSQLAfter></AdditionalSQLAfter>
    <Properties/>
</View>

As with tables, the graphical representations of views are defined in the ViewDisplays section. Every view has to have at least one ViewDisplay representation. The definitions for tables and view displays are similar.

<TableDisplay Id="td1">
    <X>645.5</X>
    <Y>3126.5</Y>
    <Width>261.0</Width>
    <Height>102.0</Height>
    <LineColor>#000000</LineColor>
    <FillColor>#B6D7A8</FillColor>
    <Table>t1</Table>
    <FixedSize>false</FixedSize>
</TableDisplay>

<ViewDisplay Id="vd1">
    <X>802.5</X>
    <Y>1568.5</Y>
    <Width>340.0</Width>
    <Height>366.0</Height>
    <LineColor>#000000</LineColor>
    <FillColor>#C9DAF8</FillColor>
    <FixedSize>false</FixedSize>
    <View>v1</View>
</ViewDisplay>

The X and Y elements define the upper left content of the rectangle, the Width and Height are the dimensions of the rectangle, and LineColor and FillColor define the HTML coloring of the element. Table and View elements are the IDs of the table or view represented by the display.

References

Here is an example reference definition. Again, this is just the definition of the database reference, not the lines representing it.

<Reference Id="r1">
    <Name>FK_ACCOUNT_REFERENCE_USER_ACC</Name>
    <Description></Description>
    <PKTable>t40</PKTable>
    <FKTable>t2</FKTable>
    <PKRole></PKRole>
    <FKRole></FKRole>
    <ReferenceColumns>
        <ReferenceColumn>
            <PKColumn>c216</PKColumn>
            <FKColumn>c7</FKColumn>
        </ReferenceColumn>
        ...
    </ReferenceColumns>
    <Cardinality>0..*</Cardinality>
    <Mandatory>true</Mandatory>
    <OnUpdateAction>Restrict</OnUpdateAction>
    <OnDeleteAction>Restrict</OnDeleteAction>
    <Properties/>
</Reference>

The PKTable is the ID of the primary key table; the FKTable is the ID of the foreign key table. PKColumn is the ID of the referenced primary key column and FKColumn is the ID of the referenced foreign key column. Cardinality can be one of 0..*, 1..*, 0..1, 1..1. The OnUpdateAction and the OnDeleteAction can be Restrict, Cascade, SetNull, or Default.

Reference displays

A reference display is the graphical representation of a reference. A reference display always connects two table displays representing appropriate tables.

<ReferenceDisplay Id="rd1">
    <Color>#000000</Color>
    <ControlPointsType>Cross_3CP</ControlPointsType>
    <ControlPoint>
        <X>2299.5</X>
        <Y>2201.5</Y>
    </ControlPoint>
    <ControlPoint>
        <X>2299.5</X>
        <Y>1877.5</Y>
    </ControlPoint>
    <ControlPoint>
        <X>2061.5</X>
        <Y>1877.5</Y>
    </ControlPoint>
    <PkTableDisplay>td40</PkTableDisplay>
    <FkTableDisplay>td2</FkTableDisplay>
    <Reference>r1</Reference>
</ReferenceDisplay>

The Reference attribute is the ID of the represented reference. The PkTableDisplay and FkTableDisplay contain IDs of appropriate table displays. The color is the color of the line representing a reference. Control points tell where the line starts, where it ends and where the bends are. There are 7 possible reference line shapes in Vertabelo. ControlPointsType define the shape of the line connecting tables. Possible values are:

• Vertical_2CP – vertical line with two control points
• Horizontal_2CP - horizontal line with two control points
• Vertical_4CP - vertical U-shaped line with four control points
• Horizontal_4CP – horizontal U-shaped line with four control points
• Cross_3CP – an L-shaped line with three control points
• Self_4CP - self -reference with four-control points
• Self_5CP – self-reference with five control points

Sequences

The format of the sequence definition follows:

<Sequence Id="s1">
    <Name>user_seq</Name>
    <Description></Description>
    <StartWith>10000</StartWith>
    <IncrementBy>1</IncrementBy>
    <MinValue></MinValue>
    <HasMinValue>false</HasMinValue>
    <MaxValue></MaxValue>
    <HasMaxValue>false</HasMaxValue>
    <Cycle>false</Cycle>
    <Cache></Cache>
    <HasCache>false</HasCache>
    <AdditionalSQLBefore></AdditionalSQLBefore>
    <AdditionalSQLAfter></AdditionalSQLAfter>
    <Properties/>
</Sequence>

Subject areas

Areas are named rectangles. You can find out more about subject areas in the video. The definition of the area is as follows:

<Area Id="ar1">
    <Name>Company account</Name>
    <DisplayInfo>
        <X>1323.5</X>
        <Y>630.5</Y>
        <Width>1790.0</Width>
        <Height>895.0</Height>
        <LineColor>#000000</LineColor>
        <FillColor>#FFFFFF</FillColor>
        <DashArray>[5,5]</DashArray>
        <NameX>1646.0</NameX>
        <NameY>864.0</NameY>
        <NameColor>#000000</NameColor>
        <ZIndex>1</ZIndex>
    </DisplayInfo>
</Area>

Elements X, Y, Width, and Height define the location of an area. LineColor, FillColor, and DashArray define the colors of the area. NameX, NameY, and NameColor define the location and color of an area label. Finally, Zindex defines the zindex of the rectangle (areas with higher zindex overlap those with lower zindex).