Skip to content

Customizing ErWin templates for PostgreSQL

ErWin 7.2 does not provide support for PostgreSQL, indeed with few tricks it still can be used to model database and generate valid SQL code.

(Note: In ErWin 7.3 templates look very different from 7.2, but nevertheless, I believe, similar approach still can be applied).

Trick 1. Use Oracle 10.x as the target DBMS

PostgreSQL has common ideology with Oracle and, in my opinion, is the best choice for PostgreSQL needs.

Trick 2. Fix syntax differences
I have encoundered two syntax differences – in the order designator in the column specifier of CREATE INDEX statement and extra parenthesis in ALTER TABLE ADD CONSTRAINT

Following samples illustrate what causes PostgreSQL to fail with generated code:

CREATE INDEX Publisher_xIF1 ON Publisher (PublisherID ASC);

ALTER TABLE Publisher
ADD (CONSTRAINT Publisher_fObject FOREIGN KEY (ObjectID) REFERENCES Object(ObjectID) ON DELETE CASCADE);

This can be fixed by altering ErWin FE template as the following:

2.1. Copy and rename Oracle.erwin_fe_template to Postgre.erwin_fe_template and then open it in a text editor.

2.2. Find
SPItemBegin = KeyGroupMembers

2.3. Below that find and remove line containing [" " PropertyValueX("Key Group Sort Order")]

Key Group Sort Order

2.4. Find"ADD " "(" IsNotNullX(ExecuteX("FKConstraint"),"") ")"

2.5. Remove "(" ")", so it looks like the following:"ADD " IsNotNullX(ExecuteX("FKConstraint"),"")

2.6. Switch you project to use this template
(Tools>Forward Engineer>Scema Generation; Database template – Browse; select Postgre.erwin_fe_template)

These two tricks are essential to produce valid SQL code from an ErWin model that contain no Oracle specific features, like table partitions, logging, validation, etc. If you already have an Oracle model which you prefer to keep intact and still be able to generate SQL for Postgress, you will need to void all parts of FE template responsible for Oracle specific features.

Add constraint

Trick 3. Implement use of INHERITS for subtype relationships
This part is optional and necessary if you really need to use table inheritance.

3.1. Find first occurrence of [ExecuteX("EndOfStatementX")] after SPItemBegin = Create Entity

3.2. Add [ExecuteX("Extra Properties")] before [ExecuteX("EndOfStatementX")]

[ExecuteX("Table Properties")]
[ExecuteX("Extra Properties")]
[ExecuteX("EndOfStatementX")]

Extra Properties

3.3. At the end of the file add

SPItemBegin = Extra Properties
  10000:
  {#
  ForEachVectorReferenceX("Child Relations Ref")
  {
    [ IsPropertyEqual("Type","9")
        " INHERITS("

          [
              PushReferenceX("Parent Entity Ref")
              [ [OwnerX"."]PhysicalNameX ]
              PopX
          ]
       ")"
     ]
   }
   #}
   SPItemEnd
Extra Properties Implemetation

Post a Comment

Your email is never published nor shared. Required fields are marked *
*
*

*

This blog is protected by dr Dave\'s Spam Karma 2: 197 Spams eaten and counting...