allfusion erwin data modeler macro language n.
Download
Skip this Video
Loading SlideShow in 5 Seconds..
AllFusion™ ERwin Data Modeler: Macro Language PowerPoint Presentation
Download Presentation
AllFusion™ ERwin Data Modeler: Macro Language

Loading in 2 Seconds...

play fullscreen
1 / 52

AllFusion™ ERwin Data Modeler: Macro Language - PowerPoint PPT Presentation


  • 585 Views
  • Uploaded on

AllFusion™ ERwin Data Modeler: Macro Language. 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.

loader
I am the owner, or an agent authorized to act on behalf of the owner, of the copyrighted work described.
capcha
Download Presentation

AllFusion™ ERwin Data Modeler: Macro Language


An Image/Link below is provided (as is) to download presentation

Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.


- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -
    Presentation Transcript
    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