Chat now with support
Chat with Support

Toad Data Modeler 7.2 - User Guide

Introduction User Interface Models and Model Objects
Physical Data Model
Entity Relationship Diagram Objects Basic Database Design Advanced Database Design
Universal Data Model Logical Data Model Working with Model Objects
Features and Tools
Application Variables Export/Import DDL Script Generation Graphics Model Actions Print Create New Project Reports Reverse Engineering Scripting and Customization About Templates Tips and Tricks Toad for Oracle Integration Toad Intelligence Central (TIC) Integration Tools Version Control
Options and Configuration Databases
Amazon Redshift 1.0 IBM DB2 LUW 9.5 IBM DB2 LUW 9.7 IBM DB2 LUW 10.1 IBM DB2 LUW 10.5 IBM DB2 LUW 11.1 IBM DB2 z/OS 10 IBM DB2 z/OS 11 Greenplum 4.1 Greenplum 4.2 Ingres 9.3 Ingres 10.0 EDB Postgres Advanced Server 10 Microsoft Access 2007/2010 Microsoft Azure SQL Database V12 Microsoft SQL Server 2005 Microsoft SQL Server 2008 Microsoft SQL Server 2012 Microsoft SQL Server 2014 Microsoft SQL Server 2016 Microsoft SQL Server 2017 Microsoft SQL Server 2019 MySQL 5.0 MySQL 5.1 MySQL 5.5 MySQL 5.6 MySQL 5.7 MySQL 8.0 Oracle 10g Oracle 11g Release 1 Oracle 11g Release 2 Oracle 12c Release 1 Oracle 12c Release 2 Oracle 18c Oracle 19c PostgreSQL 9.0 PostgreSQL 9.1 PostgreSQL 9.2 PostgreSQL 9.3 PostgreSQL 9.4 PostgreSQL 9.5 PostgreSQL 10 PostgreSQL 11 PostgreSQL 12 SQLite 3.7 Sybase ASE 15.5 Sybase ASE 15.7 SAP ASE 16.0 Sybase IQ 15.2 Sybase SQL Anywhere 11 SAP SQL Anywhere 17 Teradata 13 Vertica Database 8.0
Copyright Legal Notices

Custom Property Example

Scenario

You would like to add an additional property to all entities which would indicate that modeling of a particular entity is not done yet and there are more things to be done before the entity is considered complete. The property could be named InProgress.

Solution:

Create a simple custom property and add a checkbox to Entity Properties linked to the property. Use the following steps as a guide:

  1. Right-click any entity in Model Explorer and select Add Custom Property.

  2. In the New Custom Property dialog, enter the following:
    Caption/Name - enter InProgress as Name and anything you want as Caption
    Level - we assume the property should be available in all entities in all models, select PEREntity
    Type - the boolean type is the most suitable for our type of property (true/false)
    Readonly - the property value should be editable, do not check this checkbox
    Script Implementation - the property value is not dependent on other property values and it doesn't change any other property value either, do not check this checkbox
    Package - feel free to choose any Package

  3. Confirm the changes, you will need to restart the application in order to access the newly created property.

  4. The custom property is now available for use. You can verify that by accessing it via scripting.

  5. Now it's time to add a checkbox to the Entity Properties form. Right-click anywhere in the form and select Customize Form.

    Note:If you have chosen Package other than My Package, you need to select Customize Form as option and choose the Package you have selected previously (step 2). This option is disabled by default. To enable it, go to Settings Menu | Options | Expert Mode and uncheck the Save the definitions the the 'My Package' option.


  6. The Entity Properties form is now editable and several other forms appear. The checkbox for our property should be placed somewhere where we can see it no matter what tab is currently selected. Let's place it next to the Generate option. First, click the bottom section of the form, where the option is placed along with several buttons.

    Note: If you accidentally close some of the customization forms, go to Settings Menu | Options | Expert Mode | Editable Forms and check the appropriate option to show them again.


  7. Now find the Component Palette, go to Data section and double-click DataCheckBox to add it to the form.


  8. Move the newly added checkbox next to the Generate option (you can use Alignment form). Make sure the checkbox is selected, then locate the Component Inspector.

  9. In Component Inspector, change the following properties:
    Caption - enter a descriptive caption, e.g. In Progress
    DataSource - choose Entity
    DataField - find your custom property in the list and select it (InProgress in our case)

  10. Close the editable Entity Properties via cross button in the upper-right corner. This will cause you to exit the editable mode of the form.
  11. Right-click anywhere in the form and select Save Form. The changed form definition will be saved to My Package.

  12. Your custom property is now available in all entities across all databases. The property and the modified form definition are stored in My Package (or in other package, depending on what you chose in step 2).

TIP: If you want to transfer your customizations to another installation of Toad Data Modeler, simply move the package file from the Packages folder to the same folder of the target installation (e.g. "C:\Program Files (x86)\Quest Software\Toad Data Modeler 5.5\Packages\User\My Package.txg")

Scripting and Customization Samples

In this topic, you can find sample scripts that can be executed via Scripting Window or used in user macros.

Reorder attributes/columns

Option 1:

Attributes.InitSort();

//set ordinal values as you need

var temp = Attr1.Ordinal;

Attr1.Ordinal = Attr2.Ordinal;

Attr2.Ordinal = temp;

Attributes.Sort(); //it will get ordered according to the ordinal values set for individual items

Option 2:

Attributes.Move(2,0) // Moves the third attribute to the place of the first attribute (parameters are current_index,

The first parameter is the Current index in the list and the second is the index where an item should move.

Refresh of Model Explorer and Workspace

RefreshAllWorkspaces

- Refreshes all Workspaces of currently active model.

RefreshWorkspace

– Refreshes the selected Workspaces by ID.

RefreshModel

– Refreshes all Workspaces and also Model Explorer.

Add a Relationship and Key

//Create Relation with Shortcut on WS workspace

var Rel = Model.AddLink(2004,ParentEntShape.ID, ChildEntShape.ID, WS.ID);

OR

//Create Relation without Shortcut

Model.AddLinkObject(2004, ParentEnt, ChildEnt);

Add Columns to Keys

Key.AddAttribute(Attr)

– Adds an attribute to key.

Key.CommitChanges()

– Propagates the changes.

Possibility to Set User Rights

//SelectedObject - Every object that can have permission (Entity, Function, Procedure etc.)

//User - User or User Group

Model.AddUserRight(SelectedObject, User, 'SELECT', 'Grant');

Accessing First Entity in First Model

function main()

{

   var app = System.GetInterface('Application');

   var Model = app.Models.GetObject(0); // first model in Application View

   var Entity = Model.Entities.GetObject(0); // first entity in a model

   ...

   ...

}

Using Lock and Unlock Methods

It is good to use the Lock and Unlock methods, otherwise you will not see changes on your Workspace directly. Use both methods to make safe modifications and refresh your WS automatically.

function main()

{

   ...

   Entity.Lock();

   Entity.Name = "new_name";

   Entity.UnLock();

   ...

 

Accessing Log (Message Explorer)

Use the following to make Log accessible.

function main()

{

   ...

   Log = System.CreateObject('Log');

   ...

}

Iterating Entities and Modifying Second Parameter of Attribute Data Type

This sample is for Oracle models only and changes the second parameter of Char(x) and Varchar2(x) data types to BYTE.

 

function main()

{

  var app = System.GetInterface('Application');

  var Model = app.Models.GetObject(0);

  var e, a, Entity, Attribute;

  Model.Lock();

  for (e=0; e<Model.Entities.Count; e++) // iterate entities

  {

    Entity = Model.Entities.GetObject(e);

    Entity.Lock();

    for (a=0; a<Entity.Attributes.Count; a++) // iterate attributes

    {

      Attribute = Entity.Attributes.GetObject(a);

      if ((Attribute.DataType.Caption == "Char(x)") || (Attribute.DataType.Caption == "Varchar2(x)"))

      {

        Attribute.DataTypeParam2 = "BYTE";

      }

    }

    Entity.UnLock();

  }

  Model.UnLock();

}

Creating New Entities

function main()

{

  var app = System.GetInterface('Application');

  var Model = app.Models.GetObject(0);

  Model.Lock();

  var Entity = Model.CreateNewObject( 2002 );  

  Entity.Name = 'MyNewEntity';

  Model.UnLock(); // Refresh all windows associated with your model  

}

 

Object type of PEREntity is 2002. More information can be found in the Reference document. Click Help | Reference to open the Reference.

Converting Entity and Attribute Names to Lower Case

function main()

{

  var app = System.GetInterface('Application');

  var Model = app.Models.GetObject(0);

 

  for (e=0; e<Model.Entities.Count; e++)

  {

    Entity = Model.Entities.GetObject(e);

    Entity.Lock();

    Entity.Name = Entity.Name.toLowerCase();

    Entity.UnLock();

    Log.Information("Name of entity "+Entity.Name+" was changed.");

    for (a=0; a<Entity.Attributes.Count; a++)

    {

      Attribute = Entity.Attributes.GetObject(a);

      Attribute.Lock();

      Attribute.Name = Attribute.Name.toLowerCase();

      Attribute.UnLock();

      Log.Information("Name of attribute "+Attribute.Name+" in entity "+Attribute.Owner.Name+" was changed.");

    }

  }

}

 

Note: The toLowerCase function is a JavaScript function.

Adding a Prefix to Entity, Index and Trigger Names

function main()

{

  var prefix = "abc"; // defined prefix

  var regular_expression_prefix = new RegExp(prefix+"_");

  var app = System.GetInterface('Application');

  var Model = app.Models.GetObject(0);

 

  // Entities

  for (e=0; e<Model.Entities.Count; e++)

  {

    Entity = Model.Entities.GetObject(e);

    if (Entity.Name.search(regular_expression_prefix) == -1) // if prefix is not used in name

    {

      Entity.Lock();

      Entity.Name = prefix+"_"+Entity.Name;

      Entity.UnLock();

      Log.Information("Name of entity "+Entity.Name+" was changed.");

    }

   

    // Indexes  

    for (i=0; i<Entity.Indexes.Count; i++)

    {

      Index = Entity.Indexes.GetObject(i);

      if (Index.Name.search(regular_expression_prefix) == -1) //if prefix is not used in name

      {

        Index.Lock();

        Index.Name = prefix+"_"+Index.Name;

        Index.UnLock();

        Log.Information("Name of index "+Index.Name+" in entity "+Index.Owner.Name+" was changed.");

      }

    }

 

    // Triggers

    for (t=0; t<Entity.Triggers.Count; t++)

    {

      Trigger = Entity.Triggers.GetObject(t);

      if (Trigger.Name.search(regular_expression_prefix) == -1) //if prefix is not used in name

      {

        Trigger.Lock();

        Trigger.Name = prefix+"_"+Trigger.Name;

        Trigger.UnLock();

        Log.Information("Name of trigger "+Trigger.Name+" in entity "+Index.Owner.Name+" was changed.");

      }

    }

  }

}

Renaming NotNull Constraints

The script is for Oracle models only. It goes through all NotNull attributes and sets their notnull constraint name in format NN_nameoftable_number. For names exceeding 30 characters, it will truncate the NN_nameoftable part.  

function main()

{

  var app = System.GetInterface('Application');

  var Model = app.Models.GetObject(0);

  

  for (e=0; e<Model.Entities.Count; e++)

  {

    Entity = Model.Entities.GetObject(e);

    count = 0;

    for (a=0; a<Entity.Attributes.Count; a++)

    {

      Attribute = Entity.Attributes.GetObject(a);

      count++;

      if (Attribute.NotNull)

      {

        ConstraintNotNullName = "NN_" + Entity.Name;

        SumLength = ConstraintNotNullName.length + 1 + count.toString().length;

        if (SumLength > 30)

          ConstraintNotNullName = ConstraintNotNullName.substr(0,30-(count+1));

        ConstraintNotNullName = ConstraintNotNullName + "_" + count.toString();

        Attribute.ConstraintNotNullName = ConstraintNotNullName; // change name of index

        Log.Information("NotNull Constraint Name of attribute "+Attribute.Name+" in entity "+Attribute.Owner.Name+" was changed.");

      }

    }

  }

}

Selecting Override Identity Checkboxes at Once

This script is valid for Microsoft SQL Server 2000 and Microsoft SQL Server 2005 models only.

You can use the script provided that you set autoincrement in a domain, use the domain in PK attribute and create relationship to another entity. In this case, FK attribute with the domain (and identity) will be created in child entity. However, you need to override the identity. Not to do it for each FK attribute individually, you can run this script that goes through all FK attributes and automatically selects the checkbox Override Identity. After you execute the script, take a look at the Log window where all entities and attributes where the checkbox was selected is written out.

function main()

{

  var app = System.GetInterface('Application');

  var Model = app.Models.GetObject(0);

  for (e=0; e<Model.Entities.count; e++)

  {

    Entity = Model.Entities.GetObject(e);

    for (a=0; a<Entity.Attributes.count; a++)

    {

      Attr = Entity.Attributes.GetObject(a);

      for (i=0; i<Attr.PKForeignKeys.count; i++)

      {

        PKAttr = Attr.PKForeignKeys.GetObject(i).AttrParent;

        FKAttr = Attr.PKForeignKeys.GetObject(i).AttrChild;

        if (PKAttr.Domain != null)

        {

          if (PKAttr.Domain.Identity)

          {

            Log.Information(FKAttr.Owner.Name+"."+FKAttr.Name);

            FKAttr.Identity_override = true;

          }

        }

      }

    }

  }

}

Create New Package

Why Do We Need a New Package?

Packages are containers for groups of scripts, customized Form definitions, metamodels etc. In Toad Data Modeler, the following three types of packages may exist.

  • System packages - have the lowest priority (distributed with Toad Data Modeler application).
  • Add-on packages - have higher priority than system packages (can be downloaded from web site, shared among users etc. No add-on package exists after installation.).
  • My Package - has the highest priority (created automatically upon installation of Toad Data Modeler).

User packages exist as separate XML files with extension .TXG. System packages are in binary format with extension .TBG.

You can make your modification without the necessity to create a new package, but all scripts and modifications you will ever make will be stored in the My Package.txg file. If you plan to share your modifications with others, it's a good idea to create a new package for this purpose. In this example, we will create a new package CustomerFeedback, and store all scripts used in this tutorial, metamodel and form modifications into this package. It will give us the possibility to share the CustomerFeedback.txg file with others.

Create a New Package

Click Expert Mode| Customization | New Package or activate Package Explorer and click the New Custom Package icon.

Define Name of the package.

 

Select packages on which the new package depends. In this example, the Customer Feedback will not be dependent package. It will just extend existing packages.

Example of dependent package: Package RE Microsoft SQL Server 2005 depends on RE Microsoft SQL Server package and extends Microsoft SQL Server 2005 package. (RE is abbreviation for Reverse Engineering.)

Select packages you want to extend. In our example, we will be extending Database Oracle 10g package and HTML Reports for Oracle 10g.

You can write description to the Description tab.

Newly created package will appear in the Package Explorer. You can also see package extensions there. Custom packages have blue icon.

Just to compare, see My Package (where all modifications are stored if you don't use add-on packages) - it has a green icon and is listed at the top.

Well, a new package exists, let's continue adding new properties.

For more information, see Add New Properties in Metamodel.

Add New Properties in Metamodel

Properties and methods can be added visually, via Metamodel.

For our purpose, we will need two new properties.

  • ConfirmedByCustomer (boolean)
  • NotesFromCustomer (string)

This is where our values will be stored. The properties will be assigned to items that will appear in Entity Properties form. The ConfirmedByCustomer property value will be assigned to a checkbox, and the NotesFromCustomer value will be assigned to a text box.

How To Add New Properties

Right-click the CustomerFeedback package and select Open Metamodel.

Empty metamodel digram opens.

Right-click the workspace and select Add Class...

Select class you want to extend. For our purpose, we need to extend PEREntityOR10 class.

PER - Physical Entity Relationship model.

Entity - Items must be accessible in the Entity Properties form.

OR10 - Modification will be made for Oracle 10g only.

Select the newly added class and right-click it. Select Edit.

Add two new properties to the class.

ConfirmedByCustomer, data type Boolean.

On tab Attributes of the Property Properties dialog, you can define property Attributes. Select Editable (we need to be able to edit the values) and Store Property (we want to store the values with model).

Now define the second Property NotesFromCustomer, on tab Attributes, select the same attributes - Editable and Store Property.

Properties have been added to the metamodel. Now it's necessary to save the metamodel and restart the application. Then we can continue modifying Entity Properties form.

Note: Metamodels are XML documents stored as .TXM files.

For more information, see Modify Form.

Related Documents

The document was helpful.

Select Rating

I easily found the information I needed.

Select Rating