Automation Interface Code Samples
The code samples here are working scripts and also located in the ERStudiox.x\Macros\SampleMacros directory. Descriptions and comments appear in the code.
Basic Modeling Demonstrations: Add/Remove, Item, String and Attribute Editing
| Topic | Description |
|---|---|
| Add Base Attributes to Person Entity | Adds base attributes to selected entities, which represent people. It will also introduce a primary key based upon the entity's name. |
| Add Definition to Type Entities | Add a definition to selected entities. The definition applied to the selected entity will also include the object's name automatically, as in "My definition text + entity name +(s)" |
| Add Parent Name Prefix to Propagating Key | Add the entity name as a prefix to all attribute role names. It demonstrates how to use the FKColumnPair object |
| Add Table Name Prefix Globally | Add a prefix string to the names of all entities in the model. The macro opens a dialog box prompting the user for the prefix. If the current active model is logical, it will assign the prefix to all entity names. If the current model is physical, it will assign the prefix to all table names. |
| Add Table Name Prefix Selectively | Add a prefix string to the names of selected entities in the model. The macro opens a dialog to prompting the user for the prefix. If the current model is logical, it assigns the prefix to selected entity names. If the current model is physical, it will assign the prefix to selected table names. |
| Add Table Owner Globally | For physical models, this will update the owner field in the table editor for all tables in the active model. The active model must be a physical model. |
| Add View Owner Selectively | For physical models, this will update the owner field in the view editor for all selected entities. The active model must be a physical model. |
| Attachment Example | Exports attachment data for each bound attachment for every selected table in the active model. The file name and path is "C:\Attachment Data.txt. |
| Attribute Binding Macro | Lists all the unbound attributes/columns of all the models (logical and all physicals) and domains. The attributes can then be bounded to any of listed domains by pressing the "Bind" button. |
| Auto-Align Selected Entities Left | Performs the align left' operation (similar to the align buttons on the Alignment Toolbar) for all selected entities. This macro demonstrates more on how to use entity display objects. |
| Auto-Color All Entities with FKs | Make all entities with foreign keys purple. It will first select (highlight) all entities in the current, submodel. Then it will make all selected entities with foreign keys (which, in this case, are all the entities in the submodel) purple. Finally, it will deselect (unhighlight) all entities in the current, active submodel. This macro illustrates how to use entity objects, entity display objects, and selected objects - and how to distinguish among 'them. |
| Auto-Combine Selected Entities | Takes all the attributes from all the selected entities and add them into one newly created entity. Includes a separate function GetAveragePoint(), this function will get the average x or y coordinate point among all selected entities. The second parameter determines whether to calculate the average x coordinate or the average y coordinate. |
| Auto-Convert Datatype | Iterate through all the selected entities and change the datatypes for all attributes which use VARCHAR to TEXT. |
| Auto-Create Data Dictionary and Bound Domain | Creates a new diagram, then create a rule, default and domain in the data dictionary. Then it will bind the rule and default to the domain. Next, it will then create an entity and an attribute. Finally, it will bind the domain to the attribute. |
| Auto-Create Data Dictionary | Creates a user-defined data dictionary quickly. The macro can be used as a template to create user-defined or business-specific data dictionaries. This macro can be inserted into ER/Studio's ERSBasicHandlers system (specifically in the '"CreateDiagramHandler(CurDiagram As Object)" section of ERSBasicHandlers) and if the Create Handlers option is checked on in ER/Studio's Automation Interface Options, this Data Dictionary will be created and populated any time a user creates a new diagram. |
| Auto-Create New Diagram | Creates a new diagram. The new diagram will contain an IBM DB/2 physical model and six entities in the physical model. |
| Constraint Generator | Generates constraints from the defined reference values. The reference values must be defined in the data dictionary of ER/Studio. The macro can select from bound attributes, all attributes, bound domains or all domains. This is chosen with the radio buttons at the bottom of the dialog. the constraint will be inserted in the check constraint/rule tab of the domain or attribute. |
| Convert Name Case | Converts the case of Attributes, Columns and Entity/Table names for the highlighted tables/Entities in the active SubModel to all upper or lower case. Works in Logical or Physical models |
| Definition Editor | Lists all the tables and allows the user to update the definition field for the table, by pressing the "update" button. There will also be a list of columns for the respective table, that the user can use to update the definitions for each column. This macro will run on a logical or a physical model and populate the lists accordingly. |
| Domain Bindings Export to Excel | Lists all the tables and allow the user to update the definition field for the table, by pressing the "update" button. There will also be a list of columns for the respective table, that the user can use to update the definitions for each column. This macro will run on a logical or a physical model and populate the lists accordingly. |
| Domain Bindings Import from Excel | Exports domain bindings for all the attributes in the current model. The existing columns in the spread sheet must be left in the same format if the bindings are going to be used to import back into a model. Note that the attribute data type is only in the spread sheet for informational purposes when refactoring attributes and columns and assigning them to domains. |
| Domain Bindings | Imports domain bindings from Excel. I will map domains to attributes or columns depending on the data in the spread sheet. The macro provides an interface to select the dictionary where the domains are located and the model to search for the attributes or columns. When importing into a physical model the spread sheet must have the physical table and column names that match the selected physical model. When importing into the logical model, the names in the spread sheet must match the entity and attribute names. |
| Example Macro: Loop through Objects of Active Submodel | Brings up a dialog box that shows all attributes bounded to a selected domain. It also allows the user to unbind specific attributes from the selected domain. The dialog box contains two list boxes and two buttons. The first list box, "Domains", shows all the domains in the diagram. The second list box, "Bounded Attributes", shows all the attributes bounded to the selected domain in the "Domains" list box. There is also an "OK" button and an "UnBind" button. The user can press the "UnBind" button to unbind selected attributes in the "Bounded Attributes" list box from the selected domain in the "Domains" list box. |
| Export Domain Info to Excel | Exports the domains and their properties to excel. Each record in the Excel spread sheet represents on domains from the data dictionary. The spread sheet can be used to store domains outside of ER/Studio. The domain properties can be updated in the spread sheet and imported back into an existing model or a new model using the "Import Domains From Excel" macro. The columns in the spread sheet must remain in the same order to import them properly. REQUIREMENT: You must have MS Excel 97 or later installed. |
| Export Index Column Info to Excel | Generates an index column report for the active model in ER/Studio. Each record in the Excel spread sheet represents an indexed column. The spread sheet can be used to apply naming conventions to indexes. The new index name can be read in with the "Import Index Names from Excel" macro from the "New Index Name" column in the spread sheet. REQUIREMENT: You must have MS Excel 97 or later installed |
Metadata Import/Export and Formatting Examples
The following examples demonstrate various methods of importing and exporting model meta data with MSOffice applications such as Excel and Word. Importing has such uses as transferring spreadsheets or lists of names and attributes into your model. Exporting might be used for a presentation or report.
- Export Metadata to Excel Version 2
- Export Metadata to Excel Version 2.1
- Export Metadata to Excel Version 3
- Export Metadata to Excel Version 3.1
- Export Model Metadata to Excel
- Export Model Metadata to Word
- Export Object Definitions and Notes to Excel
- Export Reference Value Info to Excel
- Export Relationship Info to Excel
- Import Domains from Excel
- Import Index Names from Excel
- Import Object Definitions and Notes from Excel
- Import Reference Values from Excel
- Import Relationship Names from Excel
- Submodel Report
Pre/PostSQL Demonstrations for Selected Target Databases
Selectively Add DB2 Permissions to PostSQL
Selectively Add MS SQL Permissions to PostSQL
Selectively Add Oracle Permissions to PostSQL
Selectively Add Oracle Sequence
Selectively Add Sybase Permissions to PostSQL
Metadata Operations and Target Database Operations
Generate Partitions For OS390 Clustered Indexes - Provides and interface to add multiple partitions to clustered indexes. A list of tables is provided. Selecting a table will load the clustered indexes to be partitioned. The storage parameters are uniform across partitions.
Get Related Tables - Selects the related parents and/or child of the selected tables. To use the macro lasso a group of entities on the diagram or select them in the diagram tree, then right click on the macro to execute. Parents and children will be selected depending if the option is checked. This macro can be used to assist in submodel creation.
ImportDataDictionary - Import Data Dictionary objects from a flat file. The imported file has to follow a specific format guidelines listed in the comments with examples.
Index Naming - Applies naming conventions to all types of indexes. It provides an option to use the selected tables or all tables. The prefix and suffix variables can be used to add company specific conventions to the beginning and/or end of the target name. The target name can be either the existing name, table name, tablespace name or database name. The tablespace and database can only be used for applicable platforms such as Oracle or DB2. Truncate parameters can also be specified to remove the first N characters or the last N characters of the target name. Another parameter can also be used to limit the total number of characters in the target name. These are applied before the prefix and suffix are added.
Name Foreign Constraints - Prompts the user with a dialog to specify the naming convention for Foreign Constraints. It will then name all the constraints using the parent and child table names. It will also make sure the name is unique by adding an index for the last characters of duplicate names.
Name Primary Constraints - Names all primary key constraints with the given naming conventions. The table name with either a prefix or suffix.
ProcedureEdit - Adds a Synonym to a specified table/view. The macro will prompt the user with a dialog to choose the table/view. The synonym code will be inserted into the PostSQL of the specified table/view.
Selectively Output PK and FK DDL - Outputs DDL for primary and foreign constraints for all selected tables. To operate, selected the desired tables, then right-click on the macro to execute. The DDL can be previewed or written to a file.
Selectively Update Oracle Index Storage - Provides a list of tables from the active physical model. The tables in the right box will be updated with the specified storage parameters. Tables can be added and removed from the updated list.
Selectively Update OS390 Table Storage - Provides a list of tables from the active physical model. The tables in the right box will be updated with the specified storage parameters. Tables can be added and removed from the updated list.
SQL Server Storage Update - Updates SQL Server storage parameters for tables and indexes.
Switch Domain Bindings - Scans all the columns and attributes in the active model or all models and switches the domain bindings from the source domain to the target domain. The information for each bound column will be updated with the target domain. Any domain overrides will be preserved.
Update DB2 OS390 Index Storage Parameters - Provides a list of tables from the active physical model. The tables in the right box will be updated with the specified storage parameters. Tables can be added and removed from the updated list.
Update DB2 OS390 Table Storage Parameters - Provides a list of tables from the active physical model. The tables in the right box will be updated with the specified storage parameters. Tables can be added and removed from the updated list.