• 38
• Managing the Database
• Overview of the Database Designer
• Table Editor
• Modifying a Table Structure
• Entering Data into a Table
• View Editor
• Synonyms
• Sequences
• Indexes
• Summary

38

Managing the Database

A key feature of OPO is its built-in database management. As you would expect from Oracle, OPO provides a graphical environment where database objects can be created, modified, and deleted. The ability to manage objects within a database from the application design environment is one of OPO's major advantages over competitor products. This chapter illustrates how to manage database objects from the OPO Designer. Also discussed in this chapter are several database management limitations imposed by this initial release of OPO.

Overview of the Database Designer

Unlike most other application development tools, OPO allows the developer to manage database objects from the design environment. In OPO, database management is performed through the session object. After a session has been activated or connected to the database, objects within the database such as tables, views, indexes, sequences, and synonyms can be managed through the database designer. The database designer consists of a table editor for creating and modifying tables, a view editor for creating and modifying views, and dialogs for creating indexes, sequences and synonyms.

Table Editor

When you create a new table within a session, by either clicking the New Table toolbar button or by selecting the File | New Table menu command, the table editor is invoked. The table editor is a spreadsheet-type window used to define a table's data structure as illustrated in Figure 38.1.

Figure 38.1. The Table Editor.

The title of the table editor indicates the name of the table and it's associated session that you are currently editing. Each row of this spreadsheet represents a data column of the table. For each of the table's columns, there are fields that define the characteristics of that column. Table 38.1 is a list of a column's characteristics:

Table 38.1. Table column characteristics.

Characteristic

Description

The column definitions are set by entering the column name, selecting a data type from a pop-up list and entering any other desired characteristics. Occasionally, column definition values are too large to fit in the table editor fields. Each of the fields can be resized by grabbing and moving the fields boundaries.

The table editor can also set primary keys for columns in a table (Blaze tables are limited to a single primary key per table). Setting a primary key creates an index for that column. This index is useful for searching the table against the primary key column or for establishing relational links between columns of two tables. To designate a primary key, select the desired column by clicking on the leftmost, gray portion of that column. Then, set this column as a primary key column by clicking on the primary key button in the upper left of the table editor. A primary key indicator will appear in the gray area on the left of the column. To remove a primary key, repeat the same steps used to set the primary key.

The table is given a default name, which is indicated in the property sheet's Name property. The Name will be used to label this table in the session and as an identifier in future table operations. After completing the table information and specifying the table's Name, the table can be saved using the Save toolbar button or selecting the File | Save menu command. Figure 38.2 shows a completed table named EMP which has several columns of various data types and a primary key:

Figure 38.2. A completed table definition.

Modifying a Table Structure

At any time, the structure of a table can be modified. Currently, this does not apply to Blaze tables, which are not editable. Double-clicking on an existing table in a session, opens the table editor with that table's information. Although the column name cannot be altered due to database restrictions, the other column characteristics can be edited. In some cases, such as changing a column's data type from varchar2 to number or setting Not Null to true, it may be necessary to delete the stored values in the database for this column before the modification can be made. After modifying the table structure, the table should be saved.

Entering Data into a Table

An extended feature of the table editor is the ability to populate a table with data from the design environment. Though most application development tools require data to be entered by running the application or using external data editors, OPO provides a built-in data editor. While the table editor window is open, clicking on the Run Table toolbar button opens the data editor, which is shown in Figure 38.3.

Figure 38.3. The Data Editor.

The data editor temporarily replaces the table editor until the data editor is closed. The data editor displays the values of all the columns for each row in a table. New rows can be inserted and other rows can be edited or deleted. When the data editor has the focus, the toolbar displays the runtime-toolbar, which has buttons for inserting and deleting rows, for requerying the database and for committing or rolling back any outstanding changes. If values are too large to fit, the display fields can be stretched by dragging the fields boundaries to the desired size. Any changes made to the data must be committed, which saves the changes, or rolled back, which reverts the changes, before the data editor can be closed. To close the data editor, simply click on the stop toolbar button.

View Editor

Within OPO, views can be created and modified easily through the view editor. The view editor is a graphical tool used to specify which tables and columns are represented by a view, as well as the relationships and conditions among these columns. The view editor is invoked when the New View toolbar button is clicked, when the File | New View menu command is selected, or when an existing view's icon in a session is double-clicked. Figure 38.4 is an illustration of an existing view that was opened:

Figure 38.4. The View Editor.

The view editor consists of two areas, which are the table display area and the column display area. The table display area illustrates the tables whose columns are used to build this view. The column display area represents which columns are represented by this view. In addition, views also have a single property on the property sheet, which is the Name property. Like tables, the Name property can be specified and used to refer to the view.

The view editor is similar to the table editor, except that columns are selected from existing tables instead of defined. The first action of defining a view is to bring in any necessary tables, which are referred to as base tables. From any open session, a table can be dropped onto the view editor. The view's table display area shows all the base tables as list boxes. For each table's list box, the title indicates the table name and the list represents all the columns in that table. To specify any join relationships between tables, select a column, then drag-and-drop that column onto a column in another table. A line between these columns represents the join. To remove a join, select the join line and delete it. Currently, OPO only provides equi-join relationships. In later releases, inner and outer joins will be supported.

Double-clicking any of the columns in the tables in the table display area adds that column to the column display area. The column display area lists all the columns that have been selected and that will be represented by this view. Each column in the view has several characteristics that define how the view is displayed and what data the view contains. Table 38.2 defines the view's column characteristics:

Table 38.2 View column characteristics.

Characteristic

Description

The view definition in Figure 38.4, the DEPT_EMP view, demonstrates how the column characteristics define the view. The DEPT_EMP view includes the EMPNO, ENAME, and SALARY columns from the EMP table and the DNAME column from the DEPT table. Basically, this provides a view that has the key elements of the EMP table, as well as the name of the department of the employee. Without using a view, displaying this relationship would involve a database lookup to get the name of the department based on the employees department number, DEPTNO. Additionally, this view provides column labels that are more recognizable than the column name. The EMPNO column will be given the Heading Emp. # versus the column name, EMPNO when this view is dropped onto a form. Although the developer could manually change a label from EMPNO to Emp. #, it's easier if this is done automatically, especially when multiple instances of this view will be used. The conditions for this view allow the view to contain only specific records from the base table. The DEPT_EMP view will only contain records of employees who work in Operations or Sales, and whose salary is greater than 1250.

After completing the view information and specifying the view's Name, the view can be saved using the Save toolbar button or selecting the File | Save menu command. Once saved, you can examine the view's data by clicking on the run view toolbar button, which opens the view browser window shown in Figure 38.5.

Figure 38.5. The View Browser.

Running a view is similar to running a table. You view the values of the columns for each row in a spreadsheet type window. However, for views with two or more base tables, the data cannot be modified. Although this would be a nice feature, it's not a likely feature, since many databases, including Oracle 7, do not directly permit this action.

Synonyms

Synonyms provide public, yet limited, access to tables, views, or sequences through aliases. Within a session, clicking on the New Synonym toolbar button or selecting the File | New Synonym menu command invokes the Create Synonym Dialog Box, which is shown in Figure 38.6.

Figure 38.6. The Create Synonym dialog box.

The Synonym Name is the identifier used to refer to this synonym. The For Object specifies the name of the table, view, or sequence for which the synonym is to be created. The Public button indicates whether the synonym is public. If you check the Public button, the synonym is public; otherwise, the synonym is private.

Currently, a session in OPO only displays synonyms owned by the user (objects created by the user). Later releases of OPO will provide access to objects with public and granted access, which will permit a session to display public synonyms, as well as other objects, that were not created by the user.

Sequences

Sequences generate sequential integers that are generally used to generate unique values for primary key columns. Within a session, clicking on the New Sequence toolbar button or selecting the File | New Sequence menu command invokes the Create Sequence Dialog Box, which is shown in Figure 38.7.

Figure 38.7. The Create Sequence dialog box.

The Sequence Name is the identifier used to refer to this sequence. The Starting Value indicates the first sequential value. The Increment By specifies the amount to increment between the sequential values. The Minimum Value is the lowest value that can be generated by the sequence. The Maximum Value is the highest value that can be generated by the sequence. The Cycling button indicates whether the sequence should start the sequence over if it reaches the minimum or maximum value.

Indexes

Indexes provide fast access to tables for query and sorting operations. Within a session, clicking on the New Index toolbar button or selecting the File | New Index menu command invokes the Create Index Dialog Box, which is shown in Figure 38.8.

Figure 38.8. The Create Index dialog box.

The Index Name is the identifier used to refer to this index. The Table to Index is the name of the table containing the columns to be indexed. The Columns to Index field is a comma separated list of columns that will be included in the index.

Summary

Power Objects provides all the required tools to create and maintain tables, views, synonyms, sequences, and indexes. Data can be entered, deleted, and viewed through the data editor window. These visual editor windows promote simplicity in creation and maintenance of the database objects essential to the creation of database applications. The user is not limited just to this visual environment because database objects can be created using other tools such as Oracle SQL*Plus or by using EXEC SQL calls from within an OPO application.