AllFusion ERwin Data Modeler_ Developing Scripts with the Macro

Document Sample
AllFusion ERwin Data Modeler_ Developing Scripts with the Macro Powered By Docstoc
					 AllFusion™ ERwin Data
Modeler: Macro Language




                          ca.com
Objectives
       Define Macros
       Use simple macros in Domains
       Use simple macros in Model Naming
        Options
       Elements of Macro language
       Using Macros for pre-post scripts
       Understand the influence of Design Layer
        Architecture on macro availability

#-2                                                ca.com
The AllFusion ERwin Data Modeler Macro Language

       AllFusion ERwin Data Modeler Macros:
        – Are designed to accommodate varying syntax of
          different DBMSs
        – Can be used in customizing templates or in
          creating new templates
        – Are accessible through Macro Toolbox
        – Are documented extensively in AllFusion ERwin
          Data Modeler's Help system




#-3                                                       ca.com
Motivating the Macro Language
       NOT just for triggers and stored procedures
       Designed to facilitate reuse of DDL script
        snippets
       Can be used to customize built-in
        templates or to create new templates
       Are designed to accommodate varying
        syntax of different DBMSs



#-4                                               ca.com
Quick-Starting Macros
       Domains
        – Attribute/column naming rule
        – Definition/note boilerplate
       Model Naming Options
        – Logical-to-physical name mapping




#-5                                          ca.com
Use simple macros in Domains

       Domain: A user-defined, named set of
        attribute and/or column properties
       Domains allow you to:
        – Standardize the model
        – Save time
        – Avoid mistakes
       Domains are reusable



#-6                                            ca.com
Understanding Domains
  Can determine attribute/column properties




                                               ca.com
Domain Inheritance
 Parent Domain

                Parent Domain: Standard_String
                      Attribute Name:
                   %OwnerEntity %AttDomain

                                                 CUSTOMER
                                                 CUSTOMER area code


       Domain Logical Name:
            area code                            EMPLOYEE
                                                 EMPLOYEE area code
      User-Defined Domain

#-8                                                                   ca.com
Example of Macros Used in Domains
 List of available macros (most commonly used):
   – %OwnerEntity - provides the owner entity name (PK/FK)
   – %EntityName - provides the current entity name
   – %AttDomain - provides the assigned attribute domain name
   – %AttName - provides the (translated) column name
   – %EntityProp( ) - provides a entity UDP value
   – %OwnerTable - provides the owner table name (PK/FK)
   – %TableName - provides the current table name
   – %ColDomain - provides the assigned column domain name
   – %TableProp( ) - provides a table UDP value
   – %Substr( ) - provides usage of a sub-string of the domain name
   – %Lower - translates the object name into lower case
   – %Upper - translates the object name into upper case


#-9                                                                   ca.com
Example of Macros Used in Domains


            %Upper()         %OwnerEntity
            %Lower()         %AttDomain
            %EntityName      %ColDomain
            %TableName       %AttName




# - 10                                        ca.com
Understanding Name Mapping Options
  Logical Names Map to Physical Names



                                 Customer




                                    Cust

                                            ca.com
Example of Macros in Name Mapping


          %EntityName      %KeyType
          %AttDomain       %TableName
          %AttName         <literal>
                            <truncation>




# - 12                                      ca.com
Eaxmples
          %If(%EntityProp(EntAlias)){%Lookup(\\ourserver\where\NSSL.TXT,%
           EntityProp(EntAlias))}%Else{%Lookup(\\ourserver\where\NSSL.TXT,
           %EntityName)}
          %If(%AttProp(AttAlias)){%Lookup(\\ourserver\where\NSSL.TXT,%Att
           Prop(AttAlias))}%Else{%Lookup(\\ourserver\where\NSSL.TXT,%AttN
           ame)}

          This says to AllFusion ERwin Data Modeler: Go to Entity UDP called
           EntityAlias, see what value it holds. If this Entity does not have a
           specific alias name, then go to my naming txt file and select the
           appropriate name and get me the value on physical side of the model.
           (similar for attributes.) These macro’s can be inserted in Model
           naming options editor.



# - 13                                                                        ca.com
Elements Of the Macro Language…
          Total of 195 commands
          Actions familiar from other languages:
           –   Declaring variables
           –   Performing arithmetic
           –   Branching
           –   Using Boolean comparisons
           –   Looping
           –   Communicating with external files
          Generalized behavior among objects
          Generalized behavior among DBMSs

# - 14                                              ca.com
Examples of Macro Commands…
          %Parent

            For the relationship “Each CUSTOMER <places> 0, 1, or
            more SALES ORDERs”

            %Parent returns
            CUSTOMER




# - 15                                                              ca.com
Examples of Macro Commands…
          %JoinFKPK(%Child,%Parent)

            For the relationship “Each CUSTOMER <places> 0, 1, or
            more SALES ORDERs”

            %JoinFKPK(%Child, %Parent) returns
            SALES_ORDER.customer_id = CUSTOMER.customer_id




# - 16                                                              ca.com
Examples of Macro Commands
          %DBMSDelim

            For a target server selection of DB2 or Oracle,

            %DBMSDelim returns “;” – a semicolon

            For a target server selection of Sybase or SQLServer

            %DBMSDelim returns “GO”




# - 17                                                             ca.com
…Declaring Variables

         %ChildFKDecl      %ParamDecl
         %ChildNKDecl      %ParentNKDecl
         %ChildParamDecl   %ParentParamDecl
         %ChildPKDecl      %ParentPKDecl
         %Decl             %PKDecl
         %NKDecl




# - 18                                        ca.com
…Performing Arithmetic


              %-
              %*
              %/
              %+




# - 19                   ca.com
…Branching



             %If
             %Else
             %Switch




# - 20                 ca.com
…Using Boolean Comparisons


         %!=      %>=
         %<       %And
         %<=      %Not
         %==      %Or
         %>




# - 21                       ca.com
…Looping
         %ForEachAtt           %ForEachIndexMem
         %ForEachAttribute     %ForEachKey
         %ForEachChildRel      %ForEachKeyMem
         %ForEachColumn        %ForEachLogEntity
         %ForEachDefault
                               %ForEachParentRel
         %ForEachDomain
                               %ForEachTable
         %ForEachEntity
         %ForEachFKAtt         %ForEachValidation
         %ForEachFKAttribute   %ForEachValidValue
         %ForEachFKColumn      %ForEachView
         %ForEachIndex         %ForEachViewColumn

# - 22                                              ca.com
…Communicating With External Files


             %File
             %Include
             %Lookup




# - 23                               ca.com
Generalizing Behavior Among Objects

          Making your script work for
           other objects of the same class

           – %OwnerEntity
           – %VerbPhrase
           – %TableProp()




# - 24                                       ca.com
Generalizing Behavior Across DBMSs

          Making your script work for
           other DBMSs you support:

           – %CurrentDatabase
           – %CurrentUser
           – %DBMSDelim




# - 25                                   ca.com
Accessing Online Help
          Basic online help
          The Macro Toolbox




# - 26                         ca.com
“Name” and “Description”
         %ChildAtts
         Lists all the attributes of the child entity in a relationship, and performs the
         specified action on each.




# - 27                                                                                      ca.com
Sampling Macro Online Help
         %ChildAtts
         Lists all the attributes of the child entity in a relationship, and performs the
         specified action on each.
         %ChildAtts(<separator>, <action>, <prefix>)
         Scope
         RI or Rel Override
         Return Value
         Attribute names and actions
         Example
         Template Code                              Expanded Code
         /* If the current relationship is          update (movie_copy_number) or
         <is in stock as>, then */                  update(master_number) or
         %ChildAtts(“ or “, update)                 …




# - 28                                                                                      ca.com
Reading the “Full Syntax”
  Keyword                              Variables

         %ChildAtts(<separator>, <action>, <prefix>)




                 Required punctuation




# - 29                                                 ca.com
Understanding “Scope”



         Scope
         RI or Rel Override



             Three kinds of “scope”
                  – The “%ForEach” commands in which this macro
                    may be inserted
                  – Whether the macro can serve as a predicate in a
                    conditional (%If)
                  – The template types in which it may be used


# - 30                                                                ca.com
“Scoping” Template Types
          RI or Rel Override
            – Global or relationship trigger overrides
            – A stored procedure that is attached to a table
            – A pre- or post-script within a %ForEachEntity loop
          Trigger Override
            – Any table trigger
            – A stored procedure that is attached to a table
            – A pre- or post-script within a %ForEachEntity loop
          Global
            – Any trigger, any stored procedure, any pre- or post-script




# - 31                                                                     ca.com
Returning Values
          The result to expect when the macro
           expands or is generated

          Return Value
          Attribute names and actions




# - 32                                           ca.com
Types of Return Values


          Some examples…
          –   Name of an object
          –   Numeric value
          –   Value of a variable
          –   Value of a UDP for an object




# - 33                                       ca.com
Example
          Sample template syntax and the
           corresponding expanded SQL
          Based on the MOVIES model shown in the
           Macro Toolbox
          Example
          Template Code                       Expanded Code
          /* If the current relationship is   update (movie_copy_number) or
          <is in stock as>, then */           update(master_number) or
          %ChildAtts(“ or “, update)          …




# - 34                                                                        ca.com
The Macro Toolbox




# - 35              ca.com
 Using the Macro Toolbox
                            The Description Box
                             changes to reflect the
 Expand a category          selected macro
  and select a macro




 Click on a button to
  insert the macro into
  the template at the
  cursor location

# - 36                                           ca.com
Reusable Scripts

             Enhance DDL
             – e.g., automatic single-table views for every table
             Replace built-in DDL generation features
             – e.g., FK Constraint names
             – e.g., Oracle index compression
             Controlling the DBMS
             – e.g., granting and revoking privileges
             – e.g., launching DBMS maintenance tools, like
               “Update statistics” in MSSQL Server

# - 37                                                              ca.com
Examples of pre and post scripts
          Pre and post script examples
           –   Create user permissions at the schema or table level
           –   Drop tables based on owner information
           –   Drop table constraints
           –   Create generic database views
          These type of scripts works well in combination
           with UDPs




# - 38                                                                ca.com
Examples of pre and post scripts
          Macro code examples
           – Grant user permissions at the schema level
                %ForEachTable()
                {
                 grant select on %TableName to %TableProp(GrantSelect)
                 %DBMSDelim
                }
           – Grant user permissions at the table level
                grant select on %TableName to %TableProp(GrantSelect)
                %DBMSDelim




# - 39                                                                   ca.com
Examples of pre and post scripts
          Macro code examples
           – Drop tables based on owner information
             (provided in the form of a UDP)
               %ForEachTable()
               {
                if exists (drop table
             %TableProp(TableOwner).%TableName)
                %DBMSDelim
               }




# - 40                                                ca.com
Examples of pre and post scripts
          Macro code examples
           – Drop table constraints
                %ForEachTable()
                {
                  %ForEachIndex()
                  {
                    if exists
                    (drop index %TableName.%IndexName)
                    %DBMSDelim
                    }
                }




# - 41                                                   ca.com
Examples of pre and post scripts
          Macro code examples
           – Create generic database views, simple with *
                %ForEachTable()
                {
                  create view v_%TableName as
                    (select * from %TableName)
                    %DBMSDelim
                }




# - 42                                                      ca.com
Examples of pre and post scripts
          Macro code examples
           – More complicated (with column names)
               %ForEachTable()
               {
                 create view v_%TableName as
                 (
                   select %ForEachColumn(, ", ") {%ColName}
                   from %TableName
                 )
                 %DBMSDelim
               }




# - 43                                                        ca.com
Examples of pre and post scripts
         FK constraint name
         /* Declarative RI according to relationship delete rule */

             %ForEachEntity()
         {
                 %ForEachChildRel() {
                 ALTER TABLE %Child
                 ADD FOREIGNKEY
             %Lower(%Substr(%Parent,1,3)_%Substr(%Child,1,3))
             %Lower((%ParentPK("," ,)))
                         REFERENCES %Parent
             ON DELETE %RelRI(<DELETE>,<RI type>)%DBMSDelim
             }
         }

# - 44                                                                ca.com
Macro use in Triggers
         -- Causes an automatic update of lastuser who modified the record,
             and the datetime.

         create trigger %TriggerName%TableName %Action on %TableName
           %RefClause
           %Fire (
            UPDATE %TableName SET Modified_By = USER,
                          Modified_DtTm = CURRENT YEAR to SECOND
                          where pre.%PK() = %PK()
         );




# - 45                                                                        ca.com
Macro use in Triggers
         ---- Update statistics for performance
         update statistics MEDIUM for table %TableName
            DISTRIBUTIONS ONLY

         -- Unload a table to a file
         unload to "%TableName.unl" select * from %TableName;



         -- Revoke PUBLIC permissions from a table.
         revoke all on %TableName from "public";


# - 46                                                          ca.com
Incremental Script Development Process
         1. Document and understand the challenge
         2. Determine the type of scripts or templates that
            need to be created or modified
         3. Identify alternative approaches
         4. Start with the objective (straight SQL) in target
            DBMS and test
         5. Build and test script incrementally in small
            pieces
         6. Determine implementation level
         7. Generalize result to other problems and other
            DBMS’s
# - 47                                                          ca.com
Determine Script Type
 Database Objects or DBMS Control
          Model Level Pre-Script
          Schema
         – Tables
               Table Pre-Script
               CREATE TABLE
               CREATE INDEX
               Table Post-Script
         – Views
             View Pre-Script          DB
             CREATE VIEW
             View Post-Script       Server
         – Stored Procedures
             Model Level            Catalog
             Table Level
         – Triggers
          Model Level Post-Script
# - 48                                         ca.com
Using Macros In A Design Layer Architecture

   Three kinds of models:
    – Logical only
    – Physical only
    – Logical/Physical combined (“ERwin Classic”)




                                                    ca.com
Design Layers – the Mechanics
        Develop concurrently…




        Resynch as needed
# - 50                           ca.com
Design Layers at Work




# - 51                  ca.com
Module Summary
         You should now be able to:
          Use simple macros in Domains
          Use simple macros in Model Naming
           Options
          Use the online help facilities
          Understand macro syntax




# - 52                                         ca.com

				
DOCUMENT INFO
Shared By:
Categories:
Tags:
Stats:
views:176
posted:7/26/2012
language:English
pages:52