This extension for StarUML support to generate DDL (Data Definition Language) from PostgreSQL. Install this extension from Extension Manager of StarUML.
- Click the menu (
Tools > Postgresql Model Generation > Generate DDL...
) - Select a directory where the generated code will be deployed
- Save the ERD model if changes were perfomed during code generation.
If you want to change the generation options please follow the steps below.
- Click the menu (
Tools > Postgresql Model Generation > Configure...
) - From
Preferences/Postgresql DDL
tab set your preferences and close - Follow the spteps above to re-generate with the new preferences.
Belows are the rules to convert from ERD elements to DDL.
All entities and columns are converted to create table statements as follow:
Project
: provides the database name, either derived from its name or through thedatabase
tag. If the tag is not present one will be created with the actual name.Data Model
: part of aProject
, it gives the schema where all member tables will be created. The schema name is provided by theschema
tag, default value beingpublic
if none is defined.ER Diagram
: groups all the Entities. Each entity will be transformed into a table and at this level the user can provide a table name prefix for them using the tagprefix
.Entity
: provides table description. The table name is composed of theER Diagram
prefix (optional) and the entity name. Thetable
tag can be used to override theEntity
name.Column
: generates a table column. The column name is derived from the name but thecolumn
tag can be used to override it. The default value can be stored in thedefault
tag.
- primary key: each column marked as primary key will be part of the primary key constraint.
- foreign key: each column marked as foreign key will have its own index
- unique constraint: the columns marked as unique will be part of the table's unique contraint. With this version only one unique contraint is generated per table.
- reference constraints: for a foreign key column with the reference field populated, a reference contraint will be created.
The integer columns with length -1
will be mapped to the equivalent serial type. For instance BIGINT
with length -1
will be mapped to BIGSERIAL
.
StarUML Type | PostgreSQL type | Note |
---|---|---|
VARCHAR | varchar | length can be used for maximum size |
BOOLEAN | boolean | length is not used |
INTEGER | integer | serial if length is set to -1 |
CHAR | char | length can be used for size |
BINARY | bytea | length is ignored |
VARBINARY | bytea | length is ignored |
BLOB | bytea | length is ignored |
TEXT | text | length is not used |
SMALLINT | smallint | smallserial with length -1 |
BIGINT | bigint | bigserial if length is -1 |
DECIMAL | numeric | length is used |
NUMERIC | numeric | length is used |
FLOAT | real | length is ignored |
DOUBLE | double precision | length is ignored |
BIT | bit | length is used |
DATE | date | length is not used |
TIME | time without time zone | length is not used |
DATETIME | timestamp with time zone | length is not used |
TIMESTAMPTZ | timestamp with time zone | length is not used |
TIMESTAMP | timestamp without time zone | length is not used |
POINT | point | |
POLYGON | polygon | |
enum | [table]_[column] type | enumeration type named with the provided pattern |
For ease of use the files are generated following specific patterns. In general the files are in pairs, one ending in _create
for the object creation and one ending in _drop
that will clear all objects from the database.
Type | Creation filename pattern | Removal filename pattern | Deployment notes |
---|---|---|---|
Database | db_create.sql |
db_drop.sql |
Unless the database name is changed these files needs to be run once |
Schema | schema_create.sql |
schema_drop.sql |
If schema name changes these files need to be run followed by the table ones |
Diagram Tables | <model name>_<diagram name>_create.sql |
<model name>_<diagram name>_drop.sql |
Code for all the children diagram entities |
Schema tables | <model name>_table_create.sql |
<model name>_table_drop.sql |
Code for all the children data model entities |
- Note: the difference between diagram and schema tables is given by the model owner: the diagram entities that are moved under a diagram are diagram tables, the others (default) are schema's.
Once files are generated or changed, you have to deploy or re-deploy them into your database server. Connect to the server using psql
or your favorite client and deploy the files based on the scenarios below.
- Note: if you choose to have reference constraints please deploy the file accordingly.
It is assumed that the DDL is generated for the first time. The files should be deployed in the following oder:
- deploy the database creation file, for instance from the
psql
prompt:\i db_create.sql
- deploy the schema creation files,
\i schema_create.sql
- for each data model deploy the table file or files.
Tables should be removed if model changes are performed to them. Identify the specific data model or diagram where the table are and run the specific script ending with _drop.sql
.
Run db_drop.sql
.
It is possible to assist with the code generation through custom tags with specific names. The code generation will normally use the model names to generate the SQL ones but that can be overriden.
Tag | Level | Description |
---|---|---|
database |
Project |
Overrides the database name given by the normalized project name |
prefix |
ERDDiagram |
Gives the prefix of all the table names of the diagram |
schema |
ERDDataModel |
Indicates the schema where the tables will be created |
table |
Entity |
Override the table name otherwise given by the normalized Entity name |
column |
Column |
Override the column name, otherwise given by the Column name |
default |
Column |
Gives the default column value. The string value is taken as is so user needs to be aware of specific database syntax |
enum |
Column |
Gives the column comma separated enumeration elements |
PROCEDURE |
Project |
Name for the reference tag that would assign a tag for a schema to be generated as Procedure |
FUNCTION |
Project |
Name for the reference tag that would assign a tag for a schema to be generated as Function |
TRIGGER |
Project |
Name for the reference tag that would assign a tag for a schema to be generated as Trigger |
- Note: name normalization consists in replacing the space characters with underscores and converting to lower case.
The override tags are used to separate the high level model from the physical model. The model table name may be user/business friendly but the generated one may follow specific constraints that can be hard to follow at the high level.
For example, the User
table can be problematic in a real schema as User
is a keyword but it makes perfect sense while building the model; the database name for this table may end up being cex_people_usr
.
Postgresql supports enumeration for column value and the plugin allows the user to define them using the enum
custom tag. The user can declare the column type as enum
and then add the custom enum
tag on the column.
The value
text field should have the list of possible values. The plugin will subsequently create a dedicated enumration type with the name pattern table_column
.
In order to facilitate the usage of enumeration values, an implicit character varying conversion cast is created; as such the user can use the enumeration values as strings without an explicit casting.
In the included example, the Employee status field is a (permanent, temporary) enumeration.
Use the description / documentation for Entity
as a storage for the data that you would like to have as inserts.
Each insert will contain all the columns into each INSERT
statement.
Use | as separator for each column, if no value is between NULL
will be used as default value.
If number of separators (|
) is bellow the number of columns, they will be automatically replaced by NULL
value.
The following preferences are currently available:
Preference | Description |
---|---|
Owner |
Postgresql object owner, only one for entire model |
Storage tablespace |
Database storage tablespace name |
Storage character set |
Database storage character set |
Database collation |
Collation list |
Generate Foreign Key Constraints |
Generate foreign key constraints when column reference field is populated |
Use tab |
use TAB for indentation |
Indent spaces |
Number of spaces to be used for indentation if tab use is off |
Procedure tag |
Name for the reference tag that would assign a tag for a schema to be generated as Procedure |
Function tag |
Name for the reference tag that would assign a tag for a schema to be generated as Function |
Trigger tag |
Name for the reference tag that would assign a tag for a schema to be generated as Trigger |
Single file |
Generate single file script, rather than multiple |
Drop statements |
Skip or not, drop statements creation |
Table inserts |
Uses entity description/documentation as source to generate insert data |
Any contributions are welcome. If you find a bug or have a suggestion, please post as an issue.
By default the entities when dropped into the diagram view are added in the model as diagram siblings. They can be manually moved into the diagram manually to achieve a similar structure with the sample included. However the generator accommodates for both cases.