Chat now with support
Chat with Support

Toad Data Modeler 8.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 11 Greenplum 4.1 Greenplum 4.2 Ingres 9.3 Ingres 10.0 EDB Postgres Advanced Server 10 Microsoft Access 2010 Microsoft Azure SQL Database V12 Microsoft SQL Server 2012 Microsoft SQL Server 2014 Microsoft SQL Server 2016 Microsoft SQL Server 2017 Microsoft SQL Server 2019 Microsoft SQL Server 2022 MySQL 5.6 MySQL 5.7 MySQL 8.0 Oracle 11g Release 1 Oracle 11g Release 2 Oracle 12c Release 1 Oracle 12c Release 2 Oracle 18c Specifics - Oracle 19c Specifics - Oracle 21c PostgreSQL 9.5 Specifics - PostgreSQL 10 Specifics - PostgreSQL 11 Specifics - 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

Reverse Engineering

Available Data Providers are:

  • Connection via ADO
  • Native Connection
  • Connection via TCP/IP

Connection via ADO:

Native Connection:

Connection via TCP/IP

Note:

  1. If the following error message occurs in the Log area: "Unable to reverse users, roles and permissions. You haven't assigned the SELECT_CATALOG_ROLE role!", it means you have not all the necessary rights to load users, roles and permissions.
  2. To load these items successfully, you need to have the  SELECT_CATALOG_ROLE role assigned or need to set a right for user to SELECT tables DBA_USERS, DBA_ROLES, DBA_ROLE_PRIVS, DBA_TAB_PRIVS.
  • Missing access to system table ALL_TABLES.
  • Missing privilege SELECT on system table ALL_TABLES.

Note: Connection via TCP/IP does not support Oracle native encryption.

Script Generating

Changes since Toad Data Modeler v. 3.5

Automatically generated trigger (trigger for an attribute sequence – see the Attribute Properties dialog | General tab | Used Sequence/Trigger box) is generated as a standard trigger (e.g. entity trigger).

This trigger is generated provided that the Triggers item is selected in the DDL Script Generation dialog | What to Generate tab. It is NOT necessary to select also the Entity item.

In the past, this trigger was generated only provided that the Entities item was selected (the Triggers item was not taken into consideration).

PostgreSQL 9.5

Entity

Table Type checkbox

New combo boxTable Type available in Entity Properties dialog, tab General. When the Typed Tableoption is selected, the new combo boxSelect Composite Type appears below the Table Type combo box.Select Composite Type combo box lists all existing User Data Types.

Note: Make sure you select Composite type table from the combo box or run verification, which will return an Error message in case a wrong type is used.

Unlogged checkbox

New checkbox Unlogged added (verification changed accordingly).

Table, Attribute - Check Constraint

No Inherit - Allows not inherited constraints for tables and attributes.

Generated SQL:

create table distributors2

(

did integer,

name varchar (40),

CONSTRAINT con1_noinh_table CHECK (did > 100 AND name <> '') NO INHERIT

)

User Data Type

General tab, Type box - New type Range Type.

See other objects in Model Explorer:

  • Aggregates
  • Collations
  • Foreign Servers
  • Tablespaces
  • Sequences
  • Rewrite Rules

Foreign Table

When Foreign Table is selected in the Table Type combobox, a new Foreign Table tab displays.

List of Options box should contain the part of CREATE FOREIGN TABLE command written in brackets in OPTIONS—e.g. filename 'c:\\file.txt'.

Although common entity object is used for Foreign Table entity in Toad Data Modeler, only the following features can be used:

  • Name

  • Schema

  • Attributes list
  • Comment
  • Permission
  • Foreign Server combobox
  • Foreign Table Options tab

For Attributes, only the following features can be used:

  • Name

  • Data Type
  • Null

Materialized Views

Views

RECURSIVE parameter

  • CREATE RECURSIVE VIEW name (columns) AS SELECT ...;

WITH, CASCADED, LOCAL, CHECK OPTION parameters

Tables

USER_CATALOG_TABLE parameter for Table Storage Parameters

Aggregates

SSPACE, FINALFUNC_EXTRA, INITCOND, MSFUNC, MINVFUNC, MSTYPE, MSSAPCE, MFINALFUNC, MFINALFUNC_EXTRA, MINITCOND, ORDER BY, HYPOTHETICAL parameters

Schemas

IF NOT EXISTS parameter

COLLATE, CONSTRAINT parameters

MULTIXACT FREEZE MIN/MAX/TABLE AGE for Autovacuum and TOAST Autovacuum Table Storage Parameters

Foreign Server

  • CREATE/DROP/ALTER are not supported
  • only a listing function

Attribute

Collation combobox

The Collation combobox is available for:

  • Data Type— Text, Character varying(x), Character(x), Varchar

  • User Data Type— Base type with selected Collatable checkbox

  • Dictionary Type—if correct Data Type or User Data Type is selected (see above)

  • Domain—combobox is disabled (changes not allowed)

Attributes - Foreign Table

Resulting in:

CREATE FOREIGN TABLE "postgres"."T_CUSTOMER"(

"customer_id" Integer NOT NULL,

"name" Character varying(24) OPTIONS (columndelimiter ':'),

"address" Text

)

Data Type box - Smallserial and Json new data types.

Collation

  • CREATE/DROP/ALTER are not supported
  • only a listing function

User Data Type—Composite Type

COLLATE collation definition can be used for every attribute to set its Collation. To set Collation, manually write COLLATE collation expression in Composite Type tab right after "attributename datatypename" definition (see the screenshot above). Toad Data Modeler will load it during RE.

No attribute is necessary for Composite Type.

Index

Set Collation

It is possible to set Collation of every column/attribute of the table.

To set Collation

  1. Select the Generate Expression checkbox.
  2. In the Expression box, write e.g "atr1 COLLATE collname DESC, atr2".

Toad Data Modeler loads this index as an expression during RE.

 

Index Access Method

Index Access Method- new option spgist.

Buffering parameter option for gist index.

Relationship

Not Valid checkbox—option when you add a constraint of a foreign key.

Key Properties

Options Deferrable and Deferred available on Key Properties dialog, tab General.

Trigger

New Column List box available for trigger event Update (Update checkbox must be selected).

Note: Use comma (,) as a separator.

New When Condition box available on Trigger Properties dialog, tab General.

View - Trigger

  • INSTEAD OF UPDATE does not support columns list

  • AFTER/BEFORE UPDATE support columns list

  • if INSTEAD OF is used, the FOR EACH ROW checkbox must be applied

  • if AFTER/BEFORE UPDATE is used, the FOR EACH STATEMENT checkbox must be applied
  • INSTEAD OF does not support WHEN

Other objects in Model Explorer:

  • Tablespaces
  • Aggregates
  • Sequences
  • Rewrite Rules
  • Collations
  • Foreign Tables
  • Foreign Servers

User Data Type

Enumerated type does not require any Label.

Other objects in Model Explorer:

  • Aggregates
  • Rewrite Rules
  • Sequences
  • Tablespaces

Tables

Storage Parameters - Log Min Duration for Autovacuum and TOAST Autovacuum

Check constraint generation and inheritance is now allowed for foreign tables.

CREATE TABLE users (id SERIAL PRIMARY KEY, username TEXT NOT NULL);

CREATE FOREIGN TABLE users_shard_5 (CONSTRAINT us5 CHECK (id <> 0 AND username <> ''))
INHERITS (users) server myserver options ( table_name 'users' );

CREATE FOREIGN TABLE users_shard_5a (CHECK (id <> 0 AND username <> ''))
INHERITS (users) server myserver options ( table_name 'users' );

Indexes

Index Access Method - brin

Index properties - Pages Per Range, Gin Pending List Limit

Materialized Views

Storage Parameters - Log Min Duration for Autovacuum and TOAST Autovacuum

User Groups

Role Options - BYPASSRLS, NOBYPASSRLS

CREATE ROLE tangerine3 BYPASSRLS;

CREATE ROLE tangerine5 WITH LOGIN PASSWORD 'jw8s0F4' VALID UNTIL '2005-01-01' BYPASSRLS;

Change Script Generation

  • Support of the following PostgreSQL 9.5 features:
DDL New Syntax
CREATE SEQUENCE IF NOT EXISTS
CREATE MATERIALIZED VIEW

IF NOT EXISTS

Support for SET and RESET of storage parameters log_autovacuum_min_duration, toast.log_autovacuum_min_duration (integer)

CREATE TABLE

Support for SET and RESET of storage parameters log_autovacuum_min_duration, toast.log_autovacuum_min_duration (integer)

CREATE INDEX

IF NOT EXISTS

BRIN method

Support for SET and RESET of pages_per_range parameter in WITH section

Support for SET and RESET of new GIN method parameter gin_pending_list_limit in WITH section

CREATE FOREIGN TABLE INHERITS

Functions

Leakproof checkbox on tab General.

Generated SQL:

create function add4 (integer, integer) RETURNS integer
AS 'select $1 + $2;'
LANGUAGE SQL
LEAKPROOF
RETURNS NULL ON NULL INPUT;

Reverse Engineering - PostgreSQL 9.5

See Reverse Engineering - PostgreSQL 12 for more information.

Related Documents
Toad Data Modeler - 8.0
Installation Guide
Release Notes
User Guide
Showing 1 to 3 of 3 rows

The document was helpful.

Select Rating

I easily found the information I needed.

Select Rating