Dia2Postgres takes its input from Dia diagrams. You may now use compressed as well as uncompressed diagrams. Anyway, those diagrams must follow a very strict structure so that the script may parse them. This document explains how to create a diagram that may be fed into Dia2Postgres.
D2P diagrams may contain 3 types of top-level elements : the cardridge (an unique text describing the contents of the diagram), modules (groups that contain table definitions for a part of the generated database) and external modules (links to modules defined in another diagram).
The cartridge is a group containing a rectangle and a (possibly multiline) text. It must be unique inside of a diagram (an error will occur if multiple cartridges have been defined). The text from the cartridge will be read by the parser, thus allowing code generation modules to use it as a title comment. Note that it is an optionnal part, and may be omitted without causing any problem.
Module definitions are groups containing the following elements :
Any D2P diagram must contain at least one module, but more modules may be defined. Note that module groups may contain subgroups too ; those will be ignored by the parser. Multiple rectangles will be ignored too.
Complex databases may be (and should be) described by multiple diagrams. In order to create relations from a diagram's table to another's, you need to use external modules. External modules are groups that contain the following elements :
An external module must contain at least one table.
We are going to examine which diagram elements must be used to define the database structure inside modules.
Tables are described using UML classes (Dia object UML - Class). Tables may contain fields and indices. The 'Class name' field from the 'Class' tab of the class' properties defines the name of the table. Make sure that the table name is unique for a single database.
The class' attributes will be interpreted as the table's fields. Each field may be defined as shown below :
The 'Operations' tab can be used to create indices on a table. Each defined operation will be interpreted as shown below :
Tables declared inside external modules need to define the fields that are part of the primary key, and no other.
Table inheritance may be specified using Dia standard lines, arcs or zig-zag lines, as well as UML Generalization or Realization objects.
In order to implement table inheritance with those objects, one needs to link the parent and child tables with any of those elements, starting from the child table and going to its parent. The end arrow type must be set to 'Hollow triangle'. If one wants to use native Postgres inheritance, he will have to set the line type to 'Dotted' (in any other case, D2P library inheritance will be used).
Table inheritance can be implemented through both of those UML elements (they are considered as equivalent by the parser). To use them, one must link the parent and child tables with any of those elements, starting from the parent table and going to the child table. Native Postgres inheritance can be forced by setting the object's stereotype to "pg".
Foreign keys can be generated using Dia standard lines, arcs or zig-zag lines, as well as UML Dependency objects. Dia standard objects are better, since they may specify the foreign key's behavior (the equivalent of SQL clause "ON [DELETE|UPDATE] [CASCADE|NO ACTION]").
Please note that only simple foreign keys are currently supported. Support for composite foreign keys will be implemented in a near future.
In order to create a foreign key with those objects, the following steps must be followed :
Arrow type | ON DELETE ... | ON UPDATE ... |
---|---|---|
Lines | NO ACTION | NO ACTION |
Half Head | CASCADE | NO ACTION |
Slashed Cross | NO ACTION | CASCADE |
Unfilled Triangle | CASCADE | CASCADE |
D2P library references will be generated, unless you set the line type to 'Dotted' (in which case native Postgres references - FOREIGN KEY constraints - will be created)
UML Dependency objects can be created in order to generate non-cascading foreign keys. One only needs to create an UML Dependency starting from the referencing field and going to the referenced field to have D2P generate a D2P library reference. Setting the Stereotype attribute to "pg" will force a native Postgres reference to be generated.
D2P allows you to create comments for a module, a table, a field or an index. Basically comments are specified with the UML Note object ; they must belong to a module group (not an external module!).
Comments are illustrated by the Example4.dia diagram.
Module comments are the simplest form of comments. Simply create your UML note inside the module, and fill it. The text will be appended at the top of the module's SQL code, just after the module name.
Comments may be associated with either tables, fields or indices : you need to create an UML note inside the table's module, then link the note to the table with the UML Implements object, starting from the table and going to the comment. The entity documented by the comment depends on where the link starts (i.e. attribute anchor for fields, operation anchor for indices or "neutral" anchor for table comments).
NOTE : from now on, D2P also supports the use of Dia's "Comment" field for tables and fields.