Generate custom SQL code with Template Language in Erwin

Secenario: I want to generate view for every table in data warehouse automately, except some views create manually. Fortunately, Erwin privode a back door in the function of Forward Engineer, so it could be implemented simply.

Reference Doc:

Template Language and Macro Reference.pdf

Editing Forward Engineering Templates.pdf

 

Ok, let's take a look at how to do that. Below is the steps.

 

1. open Erwin, click menu item Tool->Forward Engineer->Schema Generation.

2. Edit database template, the template editor will pop up.

3. create 3 template using the following code.

SPItemBegin [keep format] = Is View Existing
[
ForEachOfType("View")
{
    @if(Equal(Property("Name"),"%1"))
    {
        "Success"
    }
}
]
SPItemEnd

SPItemBegin [keep format] = Create View Of Entity
[
    "CREATE VIEW views."< QuotedName >
    "/nAS"
    "/nSELECT/n/t "
    ForEachOwnee("Attribute")
    {
        ListSeparator("/n/t,")
        Property("Physical_Name")
    }
    "/nFROM "
    [ FE::OwnerOverride( "true" ) "." ] < QuotedName >
    FE::EndOfStatement
]
SPItemEnd

SPItemBegin [keep format] = Drop View Of Entity

FE::Bucket( "35" )
[
    @if(ExecuteTest("Is View Existing","",Property("Physical_Name")))
    {
               
    }

    @elseif(Equal(Property("Name_Qualifier"),"adw"))
    {
        ["DROP VIEW views."Property("Physical_Name")]
        "/n/n"
        FE::EndOfStatement
    }
]
SPItemEnd

 

4. Modify template  "Create Entity", "Drop Entity", they are the entry point of Forward engineer.

SPItemBegin [keep format] = Drop Entity
    [
    ...
        [
        FE::Bucket( "35" )
        Execute( "Drop View Of Entity")
        ]
    ...
    ]
SPItemEnd
       
SPItemBegin [keep format] = Create Entity
    [
    ...
        [
            FE::Bucket( "140" )

            Execute( "Create View Of Entity" )
        ]
    ...
    ]
SPItemEnd

5. Save what you made to another fet file. At this point, you can try this new function in "Schema generation" dialogue by clicking Preview buttion. If drop veiw and create view statement is shown, that's correct.