Ogre (Objects Generated from Relations) is a tool that enables users to convert information stored in a relational database to an object-oriented database (ODB) without requiring knowledge of SQL, the standard relational database access language. A graphical user interface enables the user to specify the form of the translation graphically.

The current version of Ogre is intended to target a POET object-oriented database. The relational database can be from any of a set of vendors supported by ODBC (Open Database Connectivity), such as Informix, Sybase, Oracle, etc., or the Java companion, JDBC.

Ogre provides the following functions:

• a mechanism for conversion from a relational database to an object-oriented database,
• a graphical user interface (GUI) which enables a user to prescribe the form of the conversion, and
• browsing capabilities for both the relational and object-oriented components

Object-Oriented Model

The object-oriented model is very basic:

• An object database is a set of objects.
• Each object consists of a set of attribute-value pairs.
• An attribute is a property which takes a value.

• Each row of the table becomes an object.
• The column attributes of the table become the attributes of the objects.
• The entries in the row become the values of those attributes.
• Any NULL (undefined) entries are converted to null strings as values.

Each object also has a schema attribute, which is by default the name of the original table, although this name can be modified by the user if desired.

Mapping Multiple Tables to Objects

A degenerate form of mapping for multiple tables would involve simply repeating the above single-table relation mapping once for each relation. However, this may not capture the intent of the original relational database. Typically relational databases distribute the description of single objects over multiple tables (through a design process known as "normalization"), and the attribute values of one or more tables would then be joined. We would like to recapture those joins in creating objects. As the exact nature of the join cannot be inferred from the tables alone, it is up to the user to specify the join, using Ogre's GUI.

Currently Ogre runs as a Java application under Microsoft Windows NT. In the following, we walk the user through the various windows and functions in Ogre.

Click on two of the tables, say cust_calls and customer, from the tables menu.

The log should inform you that information about those tables is being loaded:

Then move the mouse to the Ogre Canvas window and click somewhere the upper left-hand corner. You should get a message that the database is being accessed and the canvas will change to a display similar to the following.

The oval nodes represent relations, while the rectangular nodes represent attributes of the relations to which they are connected. This display indicates that cust_calls has seven columns or attributes, while customer has ten columns or attributes. The names for the attributes are those given in the relational database itself. The dark box around the cust_calls relation indicates that this relation is selected. The selection is a group of nodes that can be moved as a unit.

Now suppose we wish to get an idea of what is in these tables. Notice that the Schemas menu shows two schemas, having the same names as the two tables. This is because each table forms a component of the graph by itself.

A trivial application of Ogre is to generate objects directly from each relation, one object per relation row. In this type of usage, the attributes of objects are the same as the attributes (column headers) of the relations. There will be one "type" of object per relation.

In order to generate this form of object, select one or more schemas, then press the populate ODB button.

Except for the addition of new objects, the object-oriented database cannot be modified from Ogre. However, a companion application called poetOgreDBaccess can be run from a DOS command line to clear all objects from the database, and run simple queries on it. This program is started by giving the URL of the object-oriented database on the command line. The program then presents its own command line, which has the following options:

To select multiple nodes, hold the shift key while pressing with the mouse. The outline color of a selected node darkens to indicate that the node is selected. A node can be selected or unselected, with other selections remaining invariant, by holding the shift key.

Another way to select multiple nodes is to press the mouse on the background area of the screen, then drag it. The rectangle thus formed will select nodes contained within. The shift-click technique can then be used to unselect nodes as desired.

When an attribute is dragged using the mouse to where it nearly coincides with another attribute of comparable type (numeric, string, etc.), the two attributes become joined as a single attribute, as shown below.

It is beyond the scope of this guide to explain the theory of joining. However, the impact on the object-oriented database that would be created is that the rows of joined relations will represent a single object.

Note that joining relations changes the schema to a name composed of the names of the joined relations. Each connected subgraph determines one schema for the ODB. So when two relations are first joined, there is one fewer schema than before. The new schema will, by default, have a name comprised of the names of the original schemas with two intervening underscores. For example, joining cust_calls and customer will produce cust_calls__customer. This name can be changed by the user.

Attributes can be unjoined by control-clicking the mouse button over the box (i.e. clicking while holding down the control key). One then has a choice, from the popup menu, of the following options:

There are two ways to view the attribute types in a schema:

The following is the list of options that appear when control-clicking on a relation node:

Suppose we press populate ODB having selected the cust_calls__customer. The log will show the action taking place:

We can view the current object-oriented database by pressing show ODB:

When two attributes are joined, if there are other attributes with common names in the same schema, these will be highlighted, since they are regarded as conflicting. In other words, attempting to create objects using this naming scheme would cause ambiguities. These ambiguities must be resolved before Ogre will allow objects to be created.

There are several ways to resolve the conflict. Each will have a different implication about the form of the objects ultimately created. Before pursuing an option, the user must understand the meaning of the relations and the intent of joining these particular attributes, to make sure that the result is meaningful when objects are created from the schema.

Alternative 1: Join the conflicting attributes:

Alternative 2: Set one of the attributes to masked:

Alternative 3: Hide one of the objects:

The conflict is then resolved.

Alternative 4: Rename one of the attributes in the conflict:

In the Rename Attribute window edit the name. In this case, we will just add a character 2:

The conflict is now resolved.

If the user wishes to view the original names of the attributes, this may be done by double-clicking the corresponding arc, as shown below.

Leave Ogre either by selecting Exit from the file pull-down menu of the Controls window, or by clicking the close box in the upper right-hand corner of that window.

Object-Oriented Database Implementation

The object-oriented database is implemented in Java. The database class is called poetOgreDB, which is a wrapper for the POET class Database.

Each object is an element of the class poetOgreObject, which has as its main component an object of type MapOfStringToObject from the POET libraries. The latter is used to achieve a mapping from attribute names, which are strings, to objects of type poetPersistentValue. The latter is specialized to the following sub-classes:

The Java source code files may be consulted for a definition of the API for poetOgreDB and the other related classes.

Provided along with Ogre is a stand-alone program for accessing the POET database created by Ogre. Using this program, known as poetOgreDBaccess, objects can be searched by attribute value and displayed, modified, or deleted. The database can be displayed in its entirety or cleared. The program has a simple command-line interface with no windows. To use it, the user types:

java poetOgreDBaccess ... URL of POET database ...

Acknowledgments

// User-modifiable Initialization file for Ogre
//
// READ CAREFULLY BEFORE CHANGING
//
// Suggestion: Comment-out any lines you change, for possible example or reversion.
//
// Ogre looks for file named ogre.ini in the same directory as the executable.
// The format of this file is one attribute and one value per line.

// Comments begin with // and are on a line by themselves.
// DO NOT put comments at end of line.

// Generally the items are CASE SENSITIVE.
// Blanks count within values (e.g. for file names)!!!
// Only leading and trailing whitespace is trimmed.
// Tabs are allowed as whitespace.

// Most items are optional; if not specified, they default to none specified
// or some pre-defined value.

// addresses of DBAW servers
DBAW    dbaw://localhost:8889

// addresses of POET servers
ODB     poet://LOCAL/D:\\Program Files\\POET50\\bin\\poetOgreDB

// default user and password for RDB
userName    ... not shown ...
password    ... not shown ...

// default prefix for RDB
RDBprefix dba

// position and size of Control window
controlX        0
controlY        0
controlWidth    640
controlHeight   300

// position and size of Log window
logX            0
logY            345
logWidth        640
logHeight       125

// position and size of Canvas window
canvasWindowX       0
canvasWindowY       510
canvasWindowWidth   640
canvasWindowHeight  450

// The canvas is inside the Canvas window and
// is not necessarily entirely visible at one time.

canvasWidth         1500
canvasHeight        600

// amount by which to jog newly-created windows
jogX 20
jogY 20

// amount by which to space nodes when displaying multiple relations
xNodeSpacing 120
yNodeSpacing 0

// whether to allow intra-relation joins
allowIntraRelationJoin false

catalog			log_record
	catalog_num	Long		item_num	Long
	stock_num	Long		ord_num	Long
	manu_code	String		username	String
	cat_descr	other		update_time	Date
	cat_picture	other		old_qty	Long
	cat_advert	String		new_qty	Long
cust_calls			manufact
	customer_num	Long		manu_code	String
	call_dtime	Date		manu_name	String
	user_id	String		lead_time	String
	call_code	String
	call_descr	String	orders
	res_dtime	Date		order_num	Long
	res_descr	String		order_date	Date
				customer_num	Long
customer				ship_instruct	String
	customer_num	Long		backlog	String
	fname	String		po_num	String
	lname	String		ship_date	Date
	company	String		ship_weight	Double
	address1	String		ship_charge	Double
	address2	String		paid_date	Date
	city	String
	state	String	state
	zipcode	String		code	String
	phone	String		sname	String
items			stock
	item_num	Long		stock_num	Long
	order_num	Long		manu_code	String
	stock_num	Long		description	String
	manu_code	String		unit_price	Double
	quantity	Long		unit	String
	total_price	Double		unit_descr	String
call_type
	call_code	String
	code_descr	String

dbAnywhere Server

POET Server

Informix Server

Sybase SQL Anywhere Server

ODBC Logging

ODBC Administration Programs

• DSN Tool, which is found in the Symantec dbAnywhere folder.
• ODBC 32, which is found in the NT Control Panels folder.