Chat now with support
Chat with Support

Toad Data Modeler 6.3 - User Guide

Introduction User Interface Models and Model Objects
Physical Data Model
About Physical Data Modeling Benefits of Physical Data Model Entity Relationship Diagram Objects Basic Database Design Advanced Database Design
Universal Data Model Logical Data Model Working with Model Objects Model Properties Model Statistics
Features and Tools
Application Variables Export/Import DDL Script Generation Graphics Model Actions Print Projects Reports Reverse Engineering Scripting and Customization SQL Templates Tips and Tricks Toad for Oracle Integration Toad Intelligence Central (TIC) Integration Tools Version Control
Options and Configuration Databases
Supported Databases Connection Types by Database DB2 UDB v. 8 (LUW) DB2 v. 9 (LUW) DB2 v. 9.5 (LUW) DB2 v. 9.7 (LUW) DB2 v. 10.1 (LUW) DB2 v. 10.5 (LUW) DB2 z/OS v. 9 DB2 z/OS v.10 DB2 z/OS v. 11 Greenplum 4.1 Greenplum 4.2 Ingres 9.3 Ingres 10.0 Microsoft Access 2000/2002/2003 Microsoft Access 2007/2010 Microsoft Azure SQL Database Microsoft Azure SQL Database V12 Microsoft SQL Server 2000 Microsoft SQL Server 2005 Microsoft SQL Server 2008 Microsoft SQL Server 2012 Microsoft SQL Server 2014 Microsoft SQL Server 2016 MySQL 5.0 MySQL 5.1 MySQL 5.5 MySQL 5.6 MySQL 5.7 Oracle 9i Oracle 10g Oracle 11g Release 1 Oracle 11g Release 2 Oracle 12c Release 1 Oracle 12c Release 2 PostgreSQL 8.1 PostgreSQL 8.2 PostgreSQL 8.3 PostgreSQL 8.4 PostgreSQL 9.0 PostgreSQL 9.1 PostgreSQL 9.2 PostgreSQL 9.3 PostgreSQL 9.4 PostgreSQL 9.5 SQLite 3.7 Sybase ASE 12.5 Sybase ASE 15 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
Legal Information

Scripting Window

Features and Tools > Scripting and Customization > Scripting Window

Scripting Window allows you to run simple scripts only. In the Scripting Window, you cannot save scripts as they do not relate to any package.

To open Scripting Window

  1. Enable Expert mode: select Settings | Options | General | select the Expert Mode checkbox.
  2. Select Expert Mode | Scripting Window.

In the Application Window, see the new tab Scripting with the Scripting Window open.

To display/hide toolbar for Scripting Window

Select View | Toolbars | Scripting Window.

Icon Command

Show windows automatically

Show/hide Log

Show/hide Code Explorer

Execute script

Stop script

Script type selection box

Load script from file
Save script to a file
Save script as

Minimize all undocked forms

To display the Available Objects and the Selected Objects windows in Scripting Window

Select View | Show Registered Objects.

Sample Scripts and Scripting Tips

Features and Tools > Scripting and Customization > Sample Scripts and Scripting Tips

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;

          }

        }

      }

    }

  }

}

Custom Properties

How to Create Custom Properties

Features and Tools > Scripting and Customization > Custom Properties > How to Create Custom Properties

To further extend the functionality of Toad Data Modeler, you can create your own custom properties for most objects. To create a new custom property:

  1. Right-click an object in Model Explorer and select Add Custom Property.

  2. New Custom Property dialog appears. Here you can configure your property. Once, you're done, restart Toad Data Modeler and your property will be created and accessible via scripting.

Option Description
Caption/Name Logical and physical name of the property. Name is used to refer to the property in scripting.
Level

Determines on which level the property should be created. For example, if you create a new custom property for an entity (in Oracle 10g model), you can choose from the following options:

  • PEREntityOR10 - The property can be used only in Entities in Oracle 10g models.
  • PEREntityOR - The property can be used only in Entities in any Oracle models.
  • PEREntity - The property can be used in all Entities.
Type

Sets the custom property data type. The data type should be chosen depending on what would you like to store in the property:

  • Widestring - Text strings in general, supports Unicode characters.
  • String - Text strings in general, does not support Unicode characters.
  • Integer - Positive/negative whole numbers.
  • Boolean - True/False values.
  • Real - Floating points numbers.
  • Dispatch - Any Toad Data Modeler object (Entities, Attributes...)
Readonly Flags the property as Read Only, meaning its value cannot be changed.
Script Implementation

This option allows you to customize the way your property will behave using scripting. You should check it in two cases:

  • Your custom property value will be set according to other property values - In this case, use the automatically generated Get method to get other properties values and set your custom property value according to them.
  • Other properties values will be changed depending on your custom property value - This can be done using the automatically generated Set method. From there, you can change the value of any other property.
Package Determines in which Package the custom property will be stored. Loading/Disallowing the selected Package will cause the property to be usable/unavailable.
Related Documents

The document was helpful.

Select Rating

I easily found the information I needed.

Select Rating