AllFusion™ ERwin Data Modeler: Macro Language

1. AllFusion™ ERwin Data Modeler: Macro Language

2. 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

3. 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

4. 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

5. Quick-Starting Macros • Domains • Attribute/column naming rule • Definition/note boilerplate • Model Naming Options • Logical-to-physical name mapping

6. 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

7. Understanding Domains • Can determine attribute/column properties

8. 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

9. 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 Example of Macros Used in Domains

10. %Upper() %Lower() %EntityName %TableName %OwnerEntity %AttDomain %ColDomain %AttName Example of Macros Used in Domains

11. Understanding Name Mapping Options • Logical Names Map to Physical Names Customer Cust

12. %EntityName %AttDomain %AttName %KeyType %TableName <literal> <truncation> Example of Macros in Name Mapping

13. 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,%AttProp(AttAlias))}%Else{%Lookup(\\ourserver\where\NSSL.TXT,%AttName)} • 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.

14. 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

15. Examples of Macro Commands… • %Parent For the relationship “Each CUSTOMER <places> 0, 1, or more SALES ORDERs” %Parent returns CUSTOMER

16. 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

17. 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”

18. %ChildFKDecl %ChildNKDecl %ChildParamDecl %ChildPKDecl %Decl %NKDecl %ParamDecl %ParentNKDecl %ParentParamDecl %ParentPKDecl %PKDecl …Declaring Variables

19. …Performing Arithmetic %- %* %/ %+

20. …Branching %If %Else %Switch

21. %!= %< %<= %== %> %>= %And %Not %Or …Using Boolean Comparisons

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

23. …Communicating With External Files %File %Include %Lookup

24. Generalizing Behavior Among Objects • Making your script work for other objects of the same class • %OwnerEntity • %VerbPhrase • %TableProp()

25. Generalizing Behavior Across DBMSs • Making your script work for other DBMSs you support: • %CurrentDatabase • %CurrentUser • %DBMSDelim

26. Accessing Online Help • Basic online help • The Macro Toolbox

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

28. 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 /* If the current relationship is <is in stock as>, then */ %ChildAtts(“ or “, update) Expanded Code update (movie_copy_number) or update(master_number) or …

29. Reading the “Full Syntax” • Variables • Keyword %ChildAtts(<separator>, <action>, <prefix>) • Required punctuation

30. Understanding “Scope” 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 Scope RI or Rel Override

31. “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

32. Returning Values • The result to expect when the macro expands or is generated Return Value Attribute names and actions

33. Types of Return Values • Some examples… • Name of an object • Numeric value • Value of a variable • Value of a UDP for an object

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

35. The Macro Toolbox

36. Using the Macro Toolbox • The Description Box changes to reflect the selected macro • Expand a category and select a macro • Click on a button to insert the macro into the template at the cursor location

37. 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

38. 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

39. 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

40. 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 }

41. Examples of pre and post scripts • Macro code examples • Drop table constraints %ForEachTable() { %ForEachIndex() { if exists (drop index %TableName.%IndexName) %DBMSDelim } }

42. 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 }

43. 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 }

44. 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 } }

45. -- Causes anautomatic updateof lastuser who modified therecord, 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() ); 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() );

46. -- Causes anautomatic updateof lastuser who modified therecord, 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() ); 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";

47. Incremental Script Development Process • Document and understand the challenge • Determine the type of scripts or templates that need to be created or modified • Identify alternative approaches • Start with the objective (straight SQL) in target DBMS and test • Build and test script incrementally in small pieces • Determine implementation level • Generalize result to other problems and other DBMS’s

48. DB Server Catalog } 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 • CREATE VIEW • View Post-Script • Stored Procedures • Model Level • Table Level • Triggers • Model Level Post-Script

49. Using Macros In A Design Layer Architecture • Three kinds of models: • Logical only • Physical only • Logical/Physical combined (“ERwin Classic”)

50. Design Layers – the Mechanics • Develop concurrently… • Resynch as needed