PDR605 PowerDesigner Tips, Tricks, and Customizations for Data Modelers

1
PDR605 PowerDesigner Tips, Tricks, and
Customizations for Data Modelers
Anthony HillSenior Product Support
Engineerahill_at_sybase.com / 978-287-2564August
6, 2003
2
Topics

• PowerDesigner Options
• DBMS Definition File (XDB)
• Definition Files (XDB and XEM)
• Generation Template Language (GTL)
• VB Script
• PowerDesigner Metamodel
• Miscellaneous Tips

3
PowerDesigner Options

• What
• Method for controlling PowerDesigner behavior.
• Display Preferences controls visual aspects of
  PowerDesigner
• Model Options controls model specific aspects
  of PowerDesigner
• General Options options not specific to any
  particular model
• Why
• Alter PowerDesigner behavior to best suit your
  needs
• Changes can either be model specific or set as
  defaults for new models created
• How
• General Features Guide Chapter 3 - Using the
  PowerDesigner Interface Defining Global Options
• General Features Guide Chapter 7 Managing
  Models sections dealing with Naming Conventions
  and Conversion
• General Features Guide Chapter 14 Model
  Graphics Model Display Preferences

4
PowerDesigner Options - Display Preferences

• General General display preferences
• Windows background, Unit, Grid, Diagram
• Object View
• Top level controls name splitting of text in all
  graphics options for none, truncation or word
  wrapping
• Display options for each type of base object
  options vary by object
• Format
• Control of actual graphic symbol size, line
  styles, fill, shadow, font, and custom shape
• Text cannot be embedded within custom shapes

5
PowerDesigner Options - Model Options

• Model level List of PDM objects that can have
  options set
• Name/Code case sensitivity applies to all
  objects in the model
• TIP Select Name/Code Sensitivity option to
  ignore the case during comparison, generation, or
  merging of models
• Ignore Identifying Owner applies to tables,
  views, and stored procedures
• Column Domain
• Enforce non-divergence this option determines
  if column properties must be the same as those of
  the domain or if they can differ
• Column properties that can be set are Data type,
  Check, Rules, Mandatory, and Profile
• Default data type - the data type that applies to
  columns and domains if no data type is selected

6
PowerDesigner Options - Model Options

• Model level (cont)
• Reference Rules when creating references
• Unique code forces each reference to have a
  unique code
• Auto-reuse columns - attempts to reuse an
  existing column in the child table as the foreign
  key
• Auto-migrate columns - migrates the primary key
  as a foreign key and will optionally migrate the
  domain, checks and rules associated with the PK
• Default Link on creation - determines if a join
  will be created by default for the reference
• Default implementation - indicates how
  referential integrity is implemented in the
  reference, either declarative or triggers

7
PowerDesigner Options - Model Options

• Naming Conventions Set rules to enforce
  predefined name and code formats for all objects
• Display set to display either name or code
  values for the model
• Enable name/code conversion - enables or disables
  the use of conversion scripts and conversion
  tables to generate a code from a name or a name
  from a code
• Name Code specify the format for constraints
  on length, character case and valid characters
  for objects
• Naming Template specify a format template for
  use with several types of objects within a model
• Name to Code Code to Name uses a combination
  of a conversion script and/or a conversion table
  to generate an objects name from its code or an
  objects code from its name

8
PowerDesigner Options - General Options

• General general default settings
• Delete, Browser, Drag Drop, Output log,
  Graphical tool behavior
• Dialogue define Property sheet behavior
• Tip Enable Name to Code mirroring option if using
  conversion scripts
• Editors specify specific editors for different
  file types
• Variables list of environment variables used
  by PowerDesigner
• Named Paths logical paths used when paths are
  saved within files
• Fonts default font settings and naming
  conventions based upon the language version of
  Windows installed on your machine
• User interface (lists), Code editor (SQL
  preview), RTF editor (description, annotation)
  can be modified
• Repository general repository interaction
  default settings

9
PowerDesigner Options Generation Options

• Inter model
• Convert Names into Codes option must be selected
  during inter model generation
• Object codes are generated from names using
  associated conversion script
• DBCreateAfter Stereotype
• Used with stored procedures and views to control
  generation order

10
PowerDesigner Options Reverse Engineering

• Reverse engineer into an existing model or a new
  model
• Selection specify DBMS source to be reverse
  engineered. Either an ODBC data source or SQL
  script file
• reverse using administrators permission select
  in order to be able to select data in the system
  tables that are reserved to a database admin
• Options
• After reverse engineering options that
  potentially can alter the reverse engineered
  results
• Script terminator terminator used to end the
  statement when reverse engineering via script
• Target Model includes or allows you to add
  target model(s) to create external shortcuts

11
Miscellaneous Tips Disable Synonym creation

• Problem
• When the auto-layout feature is executed,
  synonyms are sometimes created for several
  object. This functionality is not always desired
  especially when reverse engineering a database.
• Solution
• Use the following registry key to deactivate the
  synonym creation
• HKEY_CURRENT_USER\Software\Sybase\PowerDesigner
  9\General
• "SynonymsInAutoLayout" "No" ("Yes" by default)

12
DBMS Definition File (XDB)

• What
• Method used by PowerDesigner that contains
  specifications for a particular Database
  Management System (DBMS). It contains the syntax
  templates for generation, reverse engineering,
  data types and constants for a supported DBMS.
• Why
• Used by PowerDesigner engineering to support all
  the DBMS
• Used by users to alter standard DBMS to better
  suit their needs
• Used by users to add new DBMS that are not
  shipped with PowerDesigner
• How
• Advanced User Documentation Chapter 1 DBMS
  Reference Guide
• Advanced User Documentation Chapter 2
  Managing Profiles
• Physical Data Model Users Guide Chapter 13
  Variables in PowerDesigner
• Physical Data Model Users Guide Chapter 14
  DBMS-Specific Features
• Review included XDB files for understanding and
  ideas

13
DBMS Definition File (XDB)

• General DBMS Identification and general flags
  that apply for all objects
• Script - DBMS characteristics, command
  definition, and data type translations for script
  generation and reverse engineering
• SQL contains values that define the general
  syntax for the database
• Syntax - Contains general parameters for SQL
  syntax
• Format - Contains entries that define script
  formatting
• File - Text entries used during the database
  generation
• Keywords - List of reserved words and functions
  available in SQL
• Objects details for each type of object
  available for generation to and reverse
  engineering from the database

14
DBMS Definition File (XDB)

• Script (cont)
• DataType lists of valid data types including
  conversions to PowerDesigner internal data types
• AmcdDataType list of PowerDesigner internal
  data types mapped to DBMS data types
• OdbcPhysDataType - translation table from ODBC
  data types to target DBMS data types
• PhysDataType contains the translation table
  from target database data types to internal data
  types
• PhysDttpSize - table of storage sizes of target
  DBMS data type
• PhysOdbcDataType contains data type
  translations from target DBMS to ODBC
• HostDataType translation from database data
  type to procedure data type
• Abstract translation files several files for
  abstraction data type translation
• Customize retrieved information from
  PowerDesigner 6 DEF files

15
DBMS Definition File (XDB)

• ODBC same structure as Script category. Used
  for ODBC generation when DBMS Script category
  does not suffice
• Profile see next section
• Trigger templates list of pre-defined trigger
  templates indicating referential integrity
• Trigger type insert, update, delete
• Trigger template items list of pre-defined
  trigger template items to support the
  PowerDesigner trigger templates

16
DBMS Definition File (XDB) - How does it all work?

• Generation - object statements are parsed and
  their variables are evaluated and replaced.
  Script and ODBC use the same statements
• Extension mechanism uses extension statements
  to generate additional code before or after the
  Create, Drop, or Modify statements
• Defined using PowerDesigner Generation Template
  Language (GTL)
• Can contain references to extended attributes,
  variables, and macros
• Modify statements - statements used to modify
  existing objects during database modification and
  to generate DDL outside the table create
  statement
• ModifiableAttributes list of extended
  attributes to be evaluated during the Modify
  database process
• Database definition items
• Statements that customize the PowerDesigner
  interface and behavior according the current
  database features

17
DBMS Definition File - How does it all work?
(cont)

• Reverse engineering
• Script - Each statement in the script file maps
  to a create statement in the DBMS file
• ReversedStatements list of Extension statements
  used in database generation
• ODBC two distinct type of queries are used to
  retrieve information from system tables when the
  default ODBC function calls do not retrieve
  enough information
• Extended Queries user defined queries and
  variables used when system table information is
  stored in Long, Blob, or Text columns
• Returned field allowing this is indicated in
  query header with the "EX" indicator
• When the "EX is found, and if the returned value
  contains a with query name and field name,
  then it will execute the appropriate query and
  use the returned value to replace the part.
• ReversedQueries list of user-defined ODBC
  queries to be taken into account via reverse
  engineering
• Queries must be listed in ReveredQueries item to
  be executed
• The result of a query is mapped to PowerDesigner
  internal variables via the query header

18
Miscellaneous Tips Change Modify SQL order

• Problem
• When executing Modify Database, PowerDesigner
  generates the DDL based upon statement type. All
  the drop statements, then the create statements,
  etc. However, some customers do not like this
  format.
• Solution
• Change the modify SQL order. To group all the SQL
  orders relative to a table in Modify script add
  the following registry key (CR 270997)
• HKEY_CURRENT_USER\Software\Sybase\PowerDesigner
  9\FolderOptions\Physical Objects
• "GenTablGroupStmt" "Yes"

19
Extended Model Definitions (XEM)

• What
• Mechanism for extending PowerDesigner object
  definitions
• Contains graphical, functional and generation
  parameters
• Why
• Used by PowerDesigner engineering to support
  various functions
• Can be used to complement object language
  generation Application servers support
• Can be used to generate totally new text files
• Users can customize included behavior to better
  suit your requirements
• Users can add totally new functionality
• How
• General Features Guide Chapter 8 Working with
  Extended Model Definitions
• Advanced User Documentation Chapter 2 Using
  Profiles
• Advanced User Documentation Chapter 4
  Extended Model Definitions Reference Guide
• Review included XEM files for understanding and
  ideas

20
Extended Model Definitions (XEM)

• Extended Model Definition properties
• Name and Code uniquely identify this definition
• File Name where XEM is saved, blank if internal
  to the model
• Family Classify an XEM, usually to associate it
  with an Object Language
• Sub Family further classification of and XEM
  within the selected family
• Auto attach determines whether the default is
  to include this XEM in new models created
  targeting the family specified above
• Category used to control generation options
• Enable Trace Mode debugging/learning tool that
  lets you preview the templates used
• Complement Language generation determines
  whether this XEM definition will be used in the
  language generation (Only available for OOM XEM
  files)

21
Extended Model Definitions (XEM)

• Generation section define optional tasks to be
  processed after generation and options used
  during generation
• Commands actual commands which will be executed
  post generation
• Options options user can specify as part of the
  generation process
• Tasks actual list presented during generation,
  these reference one or more commands defined
  above
• Profile see next section

22
Miscellaneous Tips - HTML Report page break

• Problem
• PowerDesigner implicitly cut pages every 50 rows
  for list which can be a problem for large model
• Solution
• Define when you want PowerDesigner to generate a
  page break in list using the following keys
• NbRowInHtmlList - allows to define the number of
  items you want on each HTML pages generated for
  list items
• NbSubPageInHtmlList - allows to define the number
  of links you want to have below each pages of the
  list.
• HKEY_CURRENT_USER\Software\Sybase\PowerDesigner
  9\General
• "NbRowInHtmlList" "50" (default value)
• "NbSubPageInHtmlList" "20" (default value)

23
Definition Files (XDB and XEM) Profile

• What
• Extension mechanism for customizing the
  PowerDesigner Metamodel.
• Categorizing objects, customizing graphics,
  adding metadata, adding object specific
  generation options and more
• Why
• Used by PowerDesigner engineering in both XDB and
  XEM files
• Allows customizing graphics based on object type
  and category
• Customize context menus based on object type
• Add additional metadata to specific object or
  categories of objects
• How
• Advanced User Documentation Chapter 2
  Managing Profiles
• Review supplied XDB and XEM files for examples of
  usage

24
Definition Files (XDB and XEM) Profile

• Shared contains extended attribute types or
  templates which can be used by any object
• Metaclass level extensions
• Determines the level at which the extension
  applies
• Extensions apply to all instances of the
  metaclass
• The PowerDesigner metamodel is object oriented
  and thus supports inheritance
• Tip when adding metaclasses, change the filter
  to show abstract classes
• Example extended attributes applied to the
  following levels would apply to
• NamedObject - applies to all
  objects
• ConstrainedNamedObject - applies to Columns and
  Domains
• Columns - applies to Columns only

25
Definition Files (XDB and XEM) Profile

• Instance level extensions
• Stereotype extension applies to instances of
  the object which have had the stereotype set to
  match
• In 9.5.x, primarily used in the PowerDesigner
  OOM.
• In PowerDesigner 10 stereotypes can be used in
  the PDM as well
• Example Java Classes EJBEntity, EJBSession,
  WebAppListener
• Criterion extension applies to instances of the
  object which match the Criterions condition
• Conditions are defined using expressions from the
  .if macro syntax of PowerDesigners GTL

26
Definition Files (XDB and XEM) Profile

• For any of the levels listed above the following
  extensions can be added
• Extended Attributes additional metadata which
  can be controlled by each instance and used to
  control generation
• Custom Checks enhance the validation of
  instances when check model is performed
• Includes check and auto-fix logic
• Written in VBScript
• Custom Symbol customize the visual display of
  objects to better identify the instances
• Custom Tool (Stereotypes only) allows the
  creation of a tool palette object to ease the
  creation of objects
• Templates and Generated files used to generate
  text files based on the model
• Tip - F12 to find where a template is defined
• Method and Menu These two are used to add
  entries to context menus. Method hold the
  VBScript to execute. Menu is the text that shows
  in the menu

27
Generation Template Language

• What
• PowerDesigner internal template based language
  used for text generation
• Used in Definition files (XDB or XEM) to generate
  files based on a model
• Why
• Allows for all PowerDesigner generation to be
  flexible
• Used by PowerDesigner engineering to generate
  text files
• Can be used by users to alter files generated by
  PowerDesigner to suit your individual needs
• Can be used to generate entirely new files.
• How
• Review supplied database definitions and extended
  model definitions
• Advanced User Documentation Chapter 5
  Generation Reference Guide

28
VB Script

• What
• Microsoft scripting language
• Why
• Automate repetitive tasks
• Import / Export information
• Extend PowerDesigner via additional model checks
• Access PowerDesigner information from other
  applications
• Used by PowerDesigner engineering to implement
  certain functionality
• How
• Review supplied sample scripts
• PowerDesigner home directory\VBScripts
• PowerDesigner home directory\OLE Automation
• Get familiar with the PowerDesigner Metamodel
• Experiment - Backup your models first

29
PowerDesigner Metamodel

• What
• A description of PowerDesigner objects,
  properties and methods
• Why
• Learn how to access PowerDesigner objects
• Using VBScript
• Using Generation Template Language
• Using other languages that support COM
• Understand PowerDesigner XML formatted models
• How
• PowerDesigner home directory\Examples\Metamodel.oo
  m
• Set of UML class diagrams graphically
  representing the metamodel
• PowerDesigner home directory\pdvbs9.chm
• Hyperlinked reference document of the
  PowerDesigner Metamodel

30
SDN Presents CodeXchange
A New Benefit for Sybase Developers

• Forum for exchanging samples, tools, scripts,
  etc.
• New features enable community collaboration
• Download samples created by Sybase or external
  users
• Leverage contributions of others to extend Sybase
  products
• Contribute code or start your own collaborative /
  open source project with input from other product
  experts
• Any SDN member can participate
• Log in using your MySybase account via SDN
• www.codexchange.sybase.com
• Or via SDN at www.sybase.com/developer
• SDN CodeXchange at TechWave
• Visit SDN in the Technology Boardwalk
• Learn about CodeXchange in the Sybase Booth
  Theater

31
Conclusion
QUESTIONS ???
32
PDR605 PowerDesigner Tips, Tricks, and
Customizations for Data Modelers
Anthony HillSenior Product Support
Engineerahill_at_sybase.com / 978-287-2564August
6, 2003
33
Miscellaneous TipsChanging colors in code editor

• It is possible to customize the colors used for
  keywords, comments, etc. in the code editors (VB
  Script, Preview, SQL Editor, ...).
• The change can be done in registry under the key
  HKEY_CURRENT_USER\Software\Sybase\PowerDesigner
  9\Controls\Editor
• By default, the Controls\Editor is almost empty.
• The keys are dynamically created once you have
  opened a syntax editor in PowerDesigner (preview,
  script, VB Script, ...).
• You can change the syntax coloring for each type
  of file/editor (Java, SQL, C, ...).
• For each file type, there are several category of
  colors
• Comment, Keyword, Number, Operator, String, Text,
  Text Selection
• For each category, you can change the foreground
  color and background color.

34
Miscellaneous TipsChanging colors (cont)

• To change the text colors
• Open the registry
• Open the node Software\Sybase\PowerDesigner
  9\Controls\Editor
• Open the node of the file type, for example, SQL
  Files
• Open the Color Groups node
• Select a category, for example, Comment
• Create a new DWORD entry
• Rename the entry to Foreground
• Change the value of the entry to BBGGRR in hexa
  format.
• Example 0000FF (red), FF0000 (blue)

35
Miscellaneous Tips Test Data Generation

• Change data format in Test data generation
• In order to change the date format in test data
  generation, you must create new entries in XDB
  file
• \Script\Sql\Format\DateFormat
• \Script\Sql\Format\TimeFormat
• \Script\Sql\Format\DateTimeFormat
• \Script\Sql\Format\OdbcDateFormat
• \Script\Sql\Format\OdbcTimeFormat
• \Script\Sql\Format\OdbcDateTimeFormat
• For datetime(DT) or timestamp (TS) data types
• "DateTimeFormat" is used generating by script
• "ODBCDateTimeFormat" is used generating by ODBC.
  
• For date (D) datatypes
• "DateFormat" is used generating by script
• "ODBCDateFormat" is used generating by ODBC

36
Miscellaneous TipsTest Data Generation(cont)

• For time (T) datatypes
• "TimeFormat" is used generating by script
• "ODBCTimeFormat" is used generating by ODBC.
• PowerDesigner looks at the conceptual
  correspondent data type of the physical data type
  of the column to be generated to know which DBMS
  entry to use.
• The content of these entries will be the format
  for the date using the following formatting
  strings
• "yyyy" represents year on 4 digits
• "yy" represents year on 2 digits
• "mm" represents months
• "dd" represents days
• "HH" represents hours
• "MM" represents minutes
• "SS" represents seconds

37
Miscellaneous Tips- Copy Symbol Format

• Copy Symbol Format
• Applies object formatting information from one
  object to another(line styles, color, font, )
• Only accessible via format toolbar, or add to
  some other toolbar
• two toolbar pieces
• Get Format - Select source object and click
• Apply Format - Select Target object and click

38
Miscellaneous Tips Mouseless Prop Nav

• Mouseless Property Sheet Navigation
• Ctrl - Tab - Next tab page
• Ctrl-Shift-Tab - Previous tab page
• Ctrl-PgUp - Next sub tab, Notes Description or
  Annotations, text area must have focus
• Ctrl-PgDn - Previous sub tab

39
Miscellaneous Tips List Tips

• List tips
• Customize Columns and Filters button in toolbar -
  add or remove displayed columns
• Multi-select to make same change to many objects
• Ctrl-Up - First line in list
• Ctrl - Down - Last line in list
• Ctrl - Left - First column on row
• Ctrl - Right - Last column on row

40
Miscellaneous Tips Mouse Roller Support

• Mouse Roller Support
• roll up scroll to top
• roll down scroll to bottom
• shiftroll up scroll left
• shiftroll down scroll right
• ctrlroll up zoom-in and center the view to the
  position of the mouse
• ctrlroll down zoom-out and center the view to
  the position of the mouse