Chat now with support
Chat with Support

Toad Data Modeler 7.0 - 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 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 Specifics - Oracle 12c Release 2 PostgreSQL 9.0 PostgreSQL 9.1 PostgreSQL 9.2 PostgreSQL 9.3 PostgreSQL 9.4 PostgreSQL 9.5 PostgreSQL 10 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

Script Explorer

In Script Explorer, you can edit existing scripts, write your own scripts/macros and more.

To open Script Explorer

  1. Enable Expert mode: select Settings Menu | Options | General | check the Expert Mode checkbox.
  2. Click on the toolbar or select Expert Mode Menu | Customization | Script Explorer.

Script Explorer does not contain all data that you can find in Package Explorer, but only scripts stored in folders.

Via the Script Explorer, you can:

  • Make fine modifications in user scripts - see option Edit Source Code (in New Window)
  • View source code of read-only scripts (via the Edit Source Code option as well), and copy their parts to use them in new scripts
  • Extend functionality of existing scripts and modify them significantly - see option Add New Script
  • Delete user scripts

See Scripting in Script Editor for more information.

The Script Explorer Tree

From the combo-box at the top, you can select a script for:

  • All Models
  • Physical Model
  • Specific database
  • Metamodel
  • Logical Model

Example: See the screenshot above - Microsoft SQL Server 2012 has been selected. The scripts with visibility enabled in Microsoft SQL Server 2012 are displayed.

Options for Folders

Folders (Script Categories):

  • Constants - scripts with constants
  • CSAO Class Definitions - scripts with class definitions, mostly organized to folders with specific database names
  • Dictionaries - contains dictionary scripts
  • Form Events - contains event scripts
  • Reports - contains scripts for HTML and RTF report generation
  • Reverse engineering - contains scripts for creating a model via reverse engineering
  • Verification - contains scripts for verification

Right-click a folder to see the following options:

Option Description

Add New Script

Adds a new script under the selected folder.

Delete Folder

Deletes the selected folder, including its contents.

Folders which contain system scripts cannot be deleted - they are locked (see the padlock icon).

Add New Folder

Creates a new folder in selected folder.

Properties

Opens the Properties dialog of the selected folder  From here, you can also manage items of the folder - add, delete and edit them, provided the folder is not read-only.

General Tab

This tab contains some basic information on the folder.

Items Tab

All subfolders and scripts of a particular folder are listed on this tab. Here, you can also manage them - add, edit, delete. (Provided that they are not read-only.)

Options for Scripts

Right-click a script to see the following options:

Option Description
Create New Script Creates a new script under the selected script.

Delete Script

Deletes the selected script. If this script contains other script items, they will be deleted as well.

System scripts cannot be deleted. They are locked (see the padlock icon). It's not possible to delete other read-only scripts either.

Edit Source Code

Opens Script Editor in the Application Window.

This option is available for all scripts - user, system and read-only scripts. Note that there is a significant difference though:

  • User scripts that are not locked: You can edit the source code entirely.
  • System scripts and read-only scripts: You can only view the script and copy its parts. You cannot edit source code of such scripts directly.

Edit Source Code in New Window

Same as above, however the source code of the selected script will open in a new instance of Script Editor.

Note: You can open source code of the same script multiple times, for example when viewing different parts of a script in two windows.

If any of the Edit options is selected, the script will be locked automatically, and other modifications of this script won't be allowed. (In Script Explorer, such scripts are disabled.)

Properties

Opens the Script Properties dialog. Properties of system and read-only scripts cannot be edited.

Script Properties

General Tab Description

Name

Physical Script name

Caption

Logical Script name

Category

Selects the script category (folder) in which it can be found.

Script Type

Select Script Type:  JScript, VBScript and Internal script are available.

Package

Name of the package where the script is stored. User scripts are stored in "My Package" by default.

Script Folder

Name of the folder in Script Explorer in which the script was created. Cannot be changed.

Visibility Tab

On this tab, you can find information about the script visibility. It defines when the script is visible when you filter scripts in Script Explorer.

Let's say your script visibility is set to "Physical Model". When you filter scripts in Script Explorer by category, your script will be shown each time the filtered category falls under Physical Model. But if you filter script by the Logical Model category, your script won't be shown.

Others Tab

Contains mostly identifying info such as Author, Company, Version etc.

Notes Tab

A tab for writing notes about the script.

Script Editor

Script Editor is accessible from Script Explorer or Package Explorer.

To open Script Editor

  • Right-click a script and choose Edit Source Code or Edit Source Code in New Window.

or

  • Simply double click a script/macro in Script Explorer

When you open an instance of Script Editor, the Scripts tab is displayed at the top of the Application Window. When this tab is focused, a new tab row appears. All opened scripts are shown as tabs there.

Note: Read-only scripts (system scripts and locked scripts) can be viewed only. You can still copy their code and use it in your own scripts.

Left section of Script Editor

List of functions in currently opened script.

Save

Saves changes you made in the script. (Shortcut: CTRL+S)

Rollback

Discards all changes made since the last save.

Note: Double-click a function in the left section of Script Editor to move to its source code in the editor itself.

TIP:

  • You can insert bookmarks in your code:
  1. Press CTRL+SHIFT+number 0-9 to insert a bookmark on selected row.
  2. To move to a created bookmark, press CTRL + bookmark number.
  • You can also quickly search through all of existing scripts by going to Expert Mode Menu | Customization | Find in Scripts or pressing CTRL+ALT+F.

Script Editor Right-Click Options

These are standard functions that can be used while working with scripts in Script Editor.

Convert Internal Script

Option for internal scripts that allows you to see your internal script in JScript.

Convert Internal Script 2

Option for internal scripts that allows you to see your internal script in JScript. Moreover, via this option, you can see numbers of lines that you can map in case of errors in the script.

Note: If an error occurs and it is an error in script, the script will open in Script Editor and the particular problematic line in the script will be highlighted. (Expert mode must be enabled.)

Automation - Calling TDM From Other Applications

Toad Data Modeler can be called from other applications, for example by running JavaScript code. This section showcases several possible scripts that may be used to perform various Toad Data Modeler tasks.

Note: By default, scripts from outside Toad Data Modeler will use the latest installed version of the application. If you want to use some other version, you will need to locate its executable file and execute the following command in its folder:

TDM.exe /regserver

Available tasks

By calling Toad Data Modeler from other applications, you can perform the following tasks silently:

  • DDL script generation
  • Report and change report generation
  • Reverse engineering
  • Model conversion

DDL script generation - Generate.js

function Generate(Model, Output)
{
	var Generator = Model.DefaultCodeGenerator;
	Generator.GenerateToFile(Output);
}
//*****************************************************
var App = new ActiveXObject("TDM.App");

//Wait until all TDM packages are loaded
while(!App.Application.IsPackagesLoaded)
{
	WScript.Sleep(1000);
}

//Model for which DDL script will be generated
var Model = App.OpenModelFromFile("C:\\My\ Models\\Videorental.txp");
//DDL script destination folder and name
Generate(Model, 'C:\\Scripts\\VideorentalGeneratedScript.sql');
// Model is deleted only from memory 
Model.Delete(); 

HTML report generation - HtmlReport.js

function HTMLReport(Model, System, OutputPath)
{
	var ReportRegistrar = System.CreateObject('ReportRegistrar');
	ReportRegistrar.DataSource = Model;
	var Report = ReportRegistrar.CreateReport('BasicHTMLPERReport'+Model.ModelDef.Abbrev, 1 , Model); //1 - HTML report
	ReportRegistrar.RegisterLayoutClasses(1);
	Report.Path = OutputPath;
	Report.FileName = 'Report';	//Name of the HTML report file
	Report.Language = 'ENU'; //Abbreviation of language of dictionary used to translate terms in report (default is english - ENU)
	Report.Kind = 'HTML';
	Report.Layout = ReportRegistrar.GetLayoutClass(0); //Report Layout (0 - Frameless, 1 - Top Menu, 2 - Left Menu)
	Report.CSS = Report.Layout.CSSList.GetObject(0); //CSS style (Frameless 0-10, Top Menu 0-2, Left Menu 0-1)
	Report.GenerateInfo = false; //If true, adds information about model to the report
	Report.Generate();
}
//*****************************************************
var App = new ActiveXObject("TDM.App");

//Wait until all TDM packages are loaded
while(!App.Application.IsPackagesLoaded)
{
	WScript.Sleep(1000);
}

//Model for which the report will be generated
var Model = App.OpenModelFromFile("C:\\Models\\Videorental.txp");
//Report destination folder
HTMLReport(Model, App.System, 'C:\\Reports\\' );

Change script generation - GenerateChangeScript.js

function ChangeFiles(App, FileName1, FileName2, Output)
{
	var Model1 = App.OpenModelFromFile(FileName1);
	var Model2 = App.OpenModelFromFile(FileName2);
	var Convertor = Model1.CreateNewObjectInternal(25000);

	Convertor.Model2ToAlter = true;
	Convertor.Converting = true; // Do not modify
	Convertor.Altering = false; // Do not modify
	Convertor.Model1Model = Model1;
	Convertor.Model2Model = Model2;
	Convertor.CreateDefaultAlterScriptSetting();
	Convertor.InitSelectedOTPs();
	Convertor.InitAvailableOTPs();
	Convertor.SelectPhysicalPropertiesOnlyInSelectedOTPs();
	Convertor.AlterFileName = Output;
	var Stream = App.System.CreateObject('TextStream');
	Convertor.SynchronizeModels();
	Convertor.LoadAllDifferences();
	Convertor.SelectAllModel1ToModel2(true, false);
	Convertor.RunAlter();
	Stream.Text = Convertor.AlterScript;
	Stream.FileName = Convertor.AlterFileName;
	Stream.SaveToFile();
	
	Convertor.Delete();
	Model1.Delete();
	Model2.Delete();
}
//*****************************************************
var App = new ActiveXObject("TDM.App");

//Wait until all TDM packages are loaded
while(!App.Application.IsPackagesLoaded)
{
	WScript.Sleep(1000);
}
//First and second model to be compared, change script destination folder and name
ChangeFiles(App, "C:\\Models\\Videorental.txp", "C:\\Models\\Videorental2.txp", "C:\\Scripts\\VideorentalChangeScript.sql" );

Change report generation - GenerateChangeReport.js

function ChangeReport(App, FileName1, FileName2, OutputPath)
{
	var System = App.System;
	var Model1 = App.OpenModelFromFile(FileName1);
	var Model2 = App.OpenModelFromFile(FileName2);
	var Convertor = Model1.CreateNewObjectInternal(25000);

	Convertor.Model2ToAlter = false;
	Convertor.Converting = true; //Do not modify
	Convertor.Altering = false;	//Do not modify
	Convertor.Model1Model = Model1;
	Convertor.Model2Model = Model2;
	Convertor.CreateDefaultAlterScriptSetting();
	Convertor.InitSelectedOTPs();
	Convertor.InitAvailableOTPs();

	Convertor.SynchronizeModels();
	Convertor.LoadAllDifferences();
	Convertor.SelectAllModel1ToModel2(true, false);

	var ReportRegistrar = System.CreateObject('ReportRegistrar');
	ReportRegistrar.DataSource = Convertor;
	var Report = ReportRegistrar.CreateReport('UniversalHTMLAlterReport', 6, Convertor); // 6 - diff HTML
	ReportRegistrar.RegisterLayoutClasses(6);
	Report.Path = OutputPath;
	Report.FileName = 'Report'; //Name of the HTML report file
	Report.Language = 'ENU'; //Abbreviation of language of dictionary used to translate terms in report (default is english - ENU)
	Report.Kind = 'HTML'; 
	Report.Layout = ReportRegistrar.GetLayoutClass(0); //Report Layout (0 - Frameless, 1 - Top Menu, 2 - Left Menu)
	Report.CSS = Report.Layout.CSSList.GetObject(0); //CSS style (Frameless 0-10, Top Menu 0-2, Left Menu 0-1)
	Report.Generate();

	Convertor.Delete();
	Model1.Delete();
	Model2.Delete();
}
//*****************************************************
var App = new ActiveXObject("TDM.App");

//Wait until all TDM packages are loaded
while(!App.Application.IsPackagesLoaded)
{
	WScript.Sleep(1000);
}

ChangeReport(App,"C:\\Models\\Videorental.txp","C:\\Models\\Videorental2.txp","C:\\Reports\\")

Converting model to PostgreSQL - ConvertToPg.js

function Convert(Model, App)
{
	var Convertor = Model.CreateNewObjectInternal(25000);
	Convertor.Model2ToAlter = false;
	Convertor.Model2ToConvert = true;
	Convertor.Model2ModelTemp = true;

	Convertor.Converting = true; //Do not modify
	Convertor.Altering = false; //Do not modify
	Convertor.Model1Model = Model;

	var PM = App.System.GetInterface('PackageManager');
	var DestinationMDef = PM.ModelDefs.GetObjectByName('PostgreSQL 9.0'); //Target database platform and version
	DestinationMDef.LoadPackages();
 
	Convertor.Model2ModelDef = DestinationMDef;
	Convertor.Model2Model = App.NewModel(2001, DestinationMDef, true, true); //2001 = Physical ER Model (PERModel)
 
	Convertor.CreateDefaultAlterScriptSetting();
	Convertor.InitSelectedOTPs();
	Convertor.InitAvailableOTPs();

	Convertor.SynchronizeModels();
	Convertor.LoadAllDifferences();
	Convertor.SelectAllModel1ToModel2(true, false);
	Convertor.RunConvert();

	var Result = Convertor.Model2Model;
	Convertor.Delete();
	return Result;
}

function Generate(Model, Output)
{
	var Generator = Model.DefaultCodeGenerator;
	// Sample generator settings changes. For more available settigs, see class PERCodeGenerator in Reference Guide
	Generator.UseQuotations = false;
	Generator.GenerateToFile(Output);
}
//*****************************************************
var App = new ActiveXObject("TDM.App");

//Wait until all TDM packages are loaded
while(!App.Application.IsPackagesLoaded)
{
	WScript.Sleep(1000);
}

//Source Model Path
var Model = App.OpenModelFromFile("C:\\Models\\Videorental.txp");
var ModelPG = Convert(Model, App);
// Save model
App.SaveModelToFile(Model, 'C:\\Models\\VideorentalPG.txp');
// Generate SQL
Generate(ModelPG, 'C:\\Models\\script.sql');
// Delete only from memory.
ModelPG.Delete();
Model.Delete();

Reverse engineering using existing Alias - REExistingAlias.js

function LoadPackagesByModelDefName(AName, System)
{
	var PM = System.GetInterface('PackageManager');
	var MDef = PM.ModelDefs.GetObjectByName(AName);
	MDef.LoadPackages();
}
//*****************************************************
function REByAlias(Alias)
{
	var REDataMigrator = Alias.REDataMigrator;
	REDataMigrator.InitiateREDataMigrator();
	REDataMigrator.InitObjects();
	var List = Alias.REStruct.Tables;
	var i;
	
	for (i=0;i<List.Count;i++)
	{
		List.GetObject(I).Selected = true;
	}

	Alias.REDataMigrator.REDataProvider.OpenConnection();//NEW
	Alias.REDataMigrator.LoadObjects();
	Alias.REDataMigrator.REDataProvider.CloseConnection();//NEW
	if (Alias.REStruct.Model!=null)
	{
		return Alias.REStruct.Model;
	}
	else
	{
		return null;
	}
}
//*****************************************************
function RE(AliasName, App, AModelDefName)
{
	var REManager = App.REManager;
	//Load Packages
	LoadPackagesByModelDefName(AModelDefName, App.System);
	var Alias;
	Alias = REManager.REAliases.GetObjectByName(AliasName);
	if (Alias==null)
	{
		var AliasPath = App.ApplicationConfig.AliasesPath;
		Alias = REManager.LoadREAliasFromFile(AliasPath+AliasName+'.txa');
	}
	return REByAlias(Alias);
}
//*********************************************************************
var App = new ActiveXObject("TDM.App");
 
//Wait until the application loads all packages
while(!App.Application.IsPackagesLoaded)
{
	WScript.Sleep(1000);
}
 
var Model = RE('PG9 unidac', App, 'PostgreSQL 9.0');
if (App.Application.SaveModelToFile(Model, 'C:\\test.txp'))
{
	WScript.Echo("OK");
}
else
{
	WScript.Echo("Error");
}

Reverse engineering and new alias - RENewAlias.js

function REByAlias(Alias)
{
	var REDataMigrator = Alias.REDataMigrator;
	REDataMigrator.InitiateREDataMigrator();
	REDataMigrator.InitObjects();

	var List = Alias.REStruct.Tables;
	var i;
	//Select some objects to RE, only example
	for (i=0;i<List.Count;i++)
	{
		List.GetObject(i).Selected = true;
	}

	Alias.REDataMigrator.LoadObjects();
	if (Alias.REStruct.Model!=null)
	{
		return Alias.REStruct.Model;
	}
}
//*********************************************************************
function LoadPackagesByModelDefName(AName, System)
{
	var PM = System.GetInterface('PackageManager');
	var MDef = PM.ModelDefs.GetObjectByName(AName);
	MDef.LoadPackages();
}
//*********************************************************************
function SetAliasParameters(REAlias)
{
	var REDataProvider = REAlias.REDataProvider;
	REDataProvider.SetConnectionParam('HostName', 'Localhost');
	REDataProvider.SetConnectionParam('DatabaseName', 'Videorental');
	REDataProvider.SetConnectionParamAsInt('Port', 0);
	REDataProvider.SetConnectionParam('UserName', 'Scott');
	REDataProvider.Password = 'Lion';
	return REAlias;
}
//*******************************************************************************************
var App = new ActiveXObject("TDM.App");
 
//Wait until the application loads all packages
while(!App.Application.IsPackagesLoaded)
{
	WScript.Sleep(1000);
}
 
LoadPackagesByModelDefName('PostgreSQL 9.0', App.System);
var REManager = App.System.GetInterface('REManager');
var REAlias = REManager.CreateAlias('REDataSourceDBPG90', 'REDataProviderUniDACClientPG');
REAlias = SetAliasParameters(REAlias);
 
var Model = REByAlias(REAlias);
REAlias.Delete();
 
if (App.SaveModelToFile(Model, 'C:\\Models\\Videorental.txp'))
{
	WScript.Echo("OK");
}
else
{
	WScript.Echo("Error");
}

Package Explorer

Package Explorer displays package structure in Toad Data Modeler and allows you to:

  • Manage objects saved in the packages (rename, move, copy objects, open Object Properties dialog etc.)
  • Access all package Metamodels

To open Package Explorer

  1. Enable Expert mode: select Settings Menu | Options | General | select the Expert Mode checkbox.
  2. Click on the toolbar or select Expert Mode Menu | Customization | Package Explorer.

Toad Data Modeler contains packages for:

  • Specific supported databases (e.g. Oracle, Microsoft SQL Server 2005, MySQL 5.0 etc.)
  • Physical ER models
  • Logical models
  • HTML and RTF reports
  • and other functions, such as SQL/DDL generation, reverse engineering etc.

The Package Explorer Tree

Via the box at the top, you can filter the list of packages.

Read-only packages/scripts cannot be edited.

Root folders contain packages that have various lists of objects, e.g.:

  • Forms Definitions - contain partial definitions with a list of changed visual components of forms modified by user  
  • External Class Definitions- contain classes and their members designed by user (see the "Metamodel" topic)
  • Package Dependencies - show dependencies between the selected package and other packages

Right-click Package options

Option Description

Load Package

Loads the package from disk to Toad Data Modeler. For faster work with packages and for faster loading of application, some information is loaded only on demand.

Delete Package

Deletes the selected Package and all items it contains.

Disallow Package

Selected package won't be loaded during next Toad Data Modeler launch.

Export Package

Exports the selected Package in .tbg format to the chosen folder

Open Metamodel

Opens the selected Package Metamodel. See Metamodels for more information.

Extend with New Package

Creates a new package that will extend the currently selected package.

Create Dependent Package

Creates a new package that will be dependent on the currently selected package (will inherit from it).

Properties

Contains information about the selected package and its items (General information, Visibility, Script folders, Scripts, Description).  From here, you can also manage the scripts in the package - add, delete or edit them. (See the following example.)

Package File box in the Package Properties dialog - Path to the location where the package is stored. Click the button on the right to open the location in file explorer.

Save BIN The selected XML package (*.txg) will be saved in binary format (*.tbg).
Save All as BIN All XML packages (*.txg) will be saved in binary format (*.tbg).

Package Explorer Toolbar

Icon Command

Save Actual - saves changes to actually active Package

Save All - saves all changes made in all modified Packages

Creates a new user Package.
Adds an existing Package to the Package Explorer.
Hides all system Packages, leaving only the user and add-on ones visible.

Icons of Packages in Package Explorer

Package Unlocked Locked

Type of Package/State

Loaded

Loaded-Modified

Unloaded

Disallowed

Loaded

Unloaded

Disallowed

Error during Loading

System

Add-On

My Package

-

-

State Description

Locked

A package is locked when:

a) it is marked as read-only on the disk

b) it is a system package and user does not have Expert Mode enabled

Note: All system packages are read-only by default. However, in Expert mode it is possible to change the lock/unlock property of the package (right-click the package in Package Explorer | Properties.) Generally, in Expert mode it is possible to lock/unlock system packages and add-on packages.

Loaded

Complete package has been loaded to memory.

Loaded - Modified

Package has been modified by user.

Unloaded

Package has not been loaded to memory.

Disallowed

Package has been disallowed by user (right-click the package in Package Explorer | Disallow Package).

Error during Loading

Loading of the package failed. It is an error state. This situation can happen e.g. when a dependent package of this package is missing (for example it was not selected during installation of the application).

Note: It is not possible to edit a script when package is locked. Unlock a package to edit its scripts.

Related Documents