Please note : This help page is for an older version of Enteprise Archtiect. The latest help can be found here.

Topic
Prev Next

Working in the Database Builder

When a data model is loaded, the Database Builder creates a set of logical folders, one for each object type supported by the current DBMS. Each logical folder is populated with all objects of that type found in the data model's hierarchy of Packages (as shown in the Project Browser).

 

In this image the data model 'Orders (postgres)' shows logical folders for Tables, Views, Functions, Sequences, Queries and Connections. It is worth noting there is no folder for 'Procedures' since PostgreSQL does not support database procedures.

Available Actions in the Database Builder Tree

The majority of the Database Builder functions are accessible via context menus. Each object in the Tree has its own set of unique menu items based on its type and status. This table describes the available context menu items and identifies which objects they apply to.

Menu Option

Applies to / Description

New data model

Applies To: Blank Space

Description: Opens the Model Wizard screen.

Refresh All

Applies to: Blank Space

Description: Reloads the complete list of data models.

Load

Applies to: Root Node

Description: Loads the full details of the data model.

Unload

Applies to: Root Node

Description: Unloads the full details of the data model.

Import DB Schema from ODBC

Applies to: Loaded Root Node

Description: Opens the 'Import DB schema from ODBC' dialog using the current active connection as the ODBC source.

Generate DDL

Applies to: Loaded Root Node, Folder, Table, View, Procedure, Function, Sequence, Package

Description: Opens the 'Generate DDL' dialog with the current object(s) selected.

Show Differences

Applies to: Loaded Root Node, Folder, Table, View, Procedure, Function, Sequence

Description: Compares the selected objects to the current active connection.

Show Differences with Options

Applies to: Loaded Root Node, Folder, Table, View, Procedure, Function, Sequence, Package

Description: Compares the selected objects to the current active connection and optionally ignore some of the differences based on the specified compare options.

Manage DBMS Options

Applies to: Loaded Root Node

Description: Opens the 'Manage DBMS Options' dialog, which can be used to change the allocated DBMS and Owner of multiple objects.

View Record Count

Applies to:  Table, View

Description: Builds and runs a SELECT query (formatted to suit the element's DBMS) to show the number of records in the selected Table or View.

If there is no active connection, you are prompted to select one.

View Top 100 Rows

Applies to: Table, View

Description: Builds and runs a SELECT query (formatted to suit the element's DBMS) to show the top 100 rows of the selected Table or View.

If there is no active connection, you are prompted to select one.

View Top 1000 Rows

Applies to: Table, View

Description: Builds and runs a SELECT query (formatted to suit the element's DBMS) to show the top 1000 rows of the selected Table or View.

If there is no active connection, you are prompted to select one.

View All Rows

Applies to: Table, View

Description: Builds and runs a SELECT query (formatted to suit the element's DBMS) to show all rows of the selected Table or View.

If there is no active connection, you are prompted to select one.

Properties

Applies to: Loaded Root Node, Folder, Table, View, Procedure, Function, Sequence, Package, Connection

Description: Opens the standard 'Properties' dialog for the selected object.

Find in Project Browser

Applies to: Loaded Root Node, Folder, Table, View, Procedure, Function, Sequence, Package, SQL Query, Connection

Description: Finds the selected object in the Project Browser.

Refresh

Applies to: Loaded Root Node

Description: Reloads the details of the current loaded data model.  This is necessary when objects are added, changed or deleted by other users or when the changes are performed outside of the Database Builder.

Add new <type>

Applies to: Folder, Table, View, Procedure, Function, Sequence, Package, SQL Query, Connection

Description: Creates a new object of the specified type.

Clone <name>

Applies to: Folder, Table, View, Procedure, Function, Sequence, Package, SQL Query, Connection

Description: Makes a new copy of the selected object. When you select this option, a prompt displays on which you set the name and owner of the new object. For Table objects, you can choose which existing constraints should be copied (and set a name for each one) along with which Foreign Keys should be copied. For SQL-based objects, you can make any necessary changes to the SQL for the new element.

Delete <name>

Applies To: Table, View, Procedure, Function, Sequence, Package, SQL Query, Connection

Description: Permanently deletes the selected object from the repository.

Add new Foreign Key on <name>

Applies to: Table

Description: Creates a new relationship between the selected Table and another one, then shows the 'Foreign key Constraint' screen for the new relationship.

SQL Object Properties

Applies to: View, Procedure, Function, Sequence

Description: Opens the 'SQL Object Editor' screen.

Edit

Applies to: SQL Query

Description: Loads the SQL (as defined in the selected element) into the SQL Scratch Pad.

Run

Applies to: SQL Query

Description: Loads the SQL in the SQL Scratch Pad and runs it.

If there is no active connection, you are prompted to select one.

Set as active DB Connection

Applies to: Connection

Description: Flags the selected Database Connection as the active one for the current session.

Set as Default DB Connection

Applies to: Connection

Description: Flags the selected Database Connection as the active one each time the data model is loaded.

DB Connection Properties

Applies to: Connection

Description: Opens the 'Database Connection Properties' screen, to manage the connection settings.

Create/Edit/Delete Database Objects

The pages listed here describe in detail how to use the Database Builder's interface to create and manipulate database tables; however, the process of creating and manipulating SQL-based database objects is documented in other areas. See these topics for details:

Database Connections in the Database Builder

When performing certain tasks such as 'Compare' or 'Execute DDL', the Database Builder requires an active database connection. Only one database connection can be made active (indicated by a colored 'Database Connection' icon, while the others are gray) at a given time. If a database connection is not currently active and you try to perform a task that requires one, the Database Builder performs one of these actions based on how many connections are defined:

  • 0 Connections – prompts you to create a connection and, if successful, continues
  • 1 Connection – sets it as active and continues
  • 2 (or more) Connections – prompts you to select one and, if successful, continues