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.
The root element of the document is the DatabaseModel element with the VersionId attribute. As of today, the current version is 2.1.
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.
table 78 t
Each model has the following counters:
- table, prefix t
- column, prefix c
- index, prefix i
- reference, prefix r
- table_check, prefix tc
- alternate_key, prefix a
- sequence, prefix s
- note, prefix n
- view, prefix v
- area, prefix ar
- text, prefix tx
- view_column, prefix vc
- tableDisplay, prefix td
- viewDisplay, prefix vd
- 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.
Each table has the following format.
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.
An example view definition:
all_books_view select * from book ... id int4
As with tables, the graphical representations of views are defined in the ViewDisplays section. Every view has to have at least one ViewDisplay representation. Table and view displays
The definitions for tables and view displays are similar.
645.5 3126.5 261.0 102.0 #000000 #B6D7A8
802.5 1568.5 340.0 366.0 #000000 #C9DAF8 false v1
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.
Here is an example reference definition. Again, this is just the definition of the database reference, not the lines representing it.
FK_ACCOUNT_REFERENCE_USER_ACC t40 t2 ... c216 c7 0..* true Restrict Restrict
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.
A reference display is the graphical representation of a reference. A reference display always connects two table displays representing appropriate tables.
#000000 Cross_3CP 2299.5 2201.5 2299.5 1877.5 2061.5 1877.5 td40 td2 r1
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
The format of the sequence definition follows:
user_seq 10000 1 false false false false
Areas are named rectangles. You can find out more about subject areas in the video. The definition of the area is as follows:
Company account 1323.5 630.5 1790.0 895.0 #000000 #FFFFFF [5,5] 1646.0 864.0 #000000 1
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).