An ORDBMS that is Truly Relational
An ORDBMS that is Truly Relational

Object/Relational Database Management Systems (ORDBMSs) are becoming common. They are the result of adding Object-Oriented (OO) capabilities to a Relational DBMS or, more accurately, to a SQL DBMS. However, many have it upside down, they overlay OO on top of relational. The problem is that Object-Orientation has no coherent data model, resulting in a weakening of the relational data model with such a scheme.

The FirstSQL/J ORDBMS takes a different approach; it uses the power of OO to extend the relational capabilities of SQL. The primary use is for implementing relational domains, though FirstSQL/J also uses OO for implementing Stored Procedures, User-Defined Functions (UDFs) and more. In addition to integrated OO capabilities, FirstSQL/J includes other relational enhancements to SQL, making it an ORDBMS that is truly relational.

Integrating Objects with Relational

Often, Object/Relational systems overlay Object-Orientation (OO) on top of relational. They move towards the following mapping of OO entities to Relational entities:

OO Entity Relational Entity
class table
object instance table row
object field table column

While the above appears logical, it is actually an incorrect mapping. It overlays the OO data model on a relational structure, even though OO has no formalized data model or design principles like Relational and Normalization. OO experts will disagree whether a given OO design is better than another. On the other hand, disputes about a properly normalized relational design are rare. Imposing OO principles on the structure of relational tables weakens the design, reduces the power of the relational structures and impacts the integrity of the data.

The driving force behind the mapping shown above is the desire to use the same structure for data persistence as for objects inside application programs. This is rarely a valid design choice. The lack of a direct mapping between relational data structures and application program structures is often labeled - An Impedance Mismatch. Actually, this conflict is the natural tension between an individual application's implementation and the database requirements of the entire system. Other applications in the system or even a different implementation of the same application may have contrasting views of the data, expressed as a working-set of objects within the application. The database is a shared resource; it services a range of clients: applications, ad-hoc queries, reporting, warehousing, ... A normalized relational database is best suited to handle this load as well as future needs. See the forthcoming article - What is the Impedance Mismatch?, for a more detailed discussion of the notorious mismatch between OO applications and relational structure.

Note: Some have suggested that the solution to the concerns discussed above is to normalize OO designs. However, this is an impractical solution. Normalization is predicated on the Relational Model and is only effective in such an environment. Using Normalization for object designs within an application will result in clumsy implementations with little practical advantage.

Object Wrappers solve the Impedance Mismatch by performing the mapping efficiently on the Server. The Other Uses for OO section below details FirstSQL/J Object Wrappers.

A Better Alternative

Rather than use an arbitrary mapping that reduces power and threatens data integrity, FirstSQL/J pursues a better alternative. FirstSQL/J utilizes OO functionality to improve the relational fidelity of SQL. It uses OO to implement relational domains, an important relational feature missing in SQL. FirstSQL/J also improves other SQL capabilities -- Stored Procedures, User-Defined Functions (UDFs) and triggers, with full OO functionality.

Relational domains are similar to User-Defined Types (UDTs) in programming languages. They allow application specific extensions to the basic datatypes. This leads to the following mapping:

OO Entity Relational Entity
class datatype/domain
object instance table column
object field value sub-field (e.g., month sub-field of a Date value)

FirstSQL/J provides this mapping to preserve and enhance the relational capabilities of SQL. It supports an OO language for implementing relational domains as well as other extensions to SQL. Syntax enhancements allow seamless access and manipulation of objects in SQL.

A Closer Look

Relational Domains

Relational domains are user-defined datatypes that model application specific information as well as more general use items. The types (domains) are usually more complicated than the built-in SQL types and may contain sub-fields that are built-in types themselves. Note: some built-in types are also complex, composed of sub-fields: DATE, TIME, TIMESTAMP, INTERVAL.

Many application specific domains are elaborations of built-in types with more involved validation and formatting, for example:

More general use domains would include: While relational domains may be complex objects, their purpose is to represent scalar values. They are not appropriate for larger structures, lattices of objects such as one might find inside application code. Larger structures or lattices are sub-optimal and clumsy to access in SQL, which is declarative in nature. Such objects require navigational code to access properly thus negating the power of relational operators.

It is best that database columns not become simply repositories for complicated application object structures. The structural capabilities of the relational model are more suited to representing this level of complexity. Relational uses tables and relationships between tables to represent structures, making the full power of SQL available. The relational model and normalization form a true, formal data and design model, unlike OO. FirstSQL/J's combination of the two provides the most effective melding of OO and Relational.

Relational domains implemented as objects have comparable functionality to built-in SQL datatypes:

Action SQL Datatypes Object Datatypes
Type Definition Built-in Add class to database catalog
Column Definition Specify SQL datatype Specify cataloged class as datatype
Value Construction Basic Literal values:
1.23, 'text', TIME '23.59.59'		 Instance constructor for class
Complex Result of expression Result of object method call
Operations on Values Built-in expression operators and functions Calls to object methods plus CAST and INHERITS operators
Modify column Set column to new value Set column to new instance value
Evaluate column method to modify object
Client Interface with Values Retrieve as primitive values (int, float, ...) and complex objects (date/time, interval) Retrieve as local objects (instance of class)
Send primitive values and complex objects Send local objects

To illustrate the actual syntax supported, the following examples utilize a relational domain based on a Point class. The Point class represents the coordinates of a position on a computer screen. It contains 2 sub-fields - x and y.

Action Domain Example
Type Declaration CREATE CLASS Point FROM 'graphics.Point'
Column Definition location Point NOT NULL
Value Construction new Point(25, 95)
Operations on Values location.getX()
Modify Column location.setY(location.getY() + 1)
Client Interface with Values (using JDBC) Point loc = (Point) rs.getObject("location");
stmt.setObject(2, new Point(35, 15));

Using an OO language to implement relational domains allows arbitrarily complex domains and direct use of OO features like inheritance, polymorphism and encapsulation. For example, a table column may declare its domain as a base class. Instances of classes deriving (inheriting) from the base can be used as values to set individual columns. SQL statements can then manipulate the values with operations defined on the base class. Through polymorphism, the operations actually use the derived implementation of each operation, giving specialized behavior. Encapsulation hides the internal details of the domain implementation from the SQL user.

Another Domain Example
As a concrete example, we will use a base class called - Shape, that represents generic shapes on a computer screen. Derived classes might include - Circle, Square, Triangle. The instance constructor for the derived classes would receive parameters germane to building the specific shape, for instance, diameter for a circle or length of a side for a square. A polymorphic method implemented for each class might be - draw(). The draw() method in the Circle class would draw a circle through a graphics interface. It would hide the details of that operation through encapsulation.

Other Uses for OO

Besides relational domains, OO is useful for implementing:

Each of these facilities use the OO language. They are implemented as class level methods. The containing class must be cataloged in the database.

Stored Procedures and UDFs are quite similar. The main difference is that methods accessed as UDFs must return a value. Methods used for Stored Procedures need not return a value. In addition, a UDF must return scalar value - a SQL datatype or a relational domain. Stored Procedures can return structured objects like result-sets from a database query. A method that returns a scalar value can function both as a Stored Procedure and as a UDF (User-Defined Function).

Triggers are in a special category called plugin methods. Plugin methods have a first argument that references a database system class. This first argument identifies it as a plugin method. The specific system class referenced by the first argument differentiates types of plugins. The first argument is an object containing information about the internal state of processing for a given SQL statement (or clause within the statement), relating to the context of the plugin.

The context of a trigger method is the execution of a modification of a table row. The context information includes the before and after column values for the row as well as metadata about the table and columns. The CREATE TRIGGER statement attaches a trigger method to a table and type of modification. DROP TRIGGER removes the trigger.

Other types of plugin methods are special User-Defined Functions (UDFs), called in SQL statements. When a SQL statement uses a plugin UDF, it omits the first argument in the call syntax. The system silently supplies the first argument. A plugin function for a recursive query, for example, must be called in a recursive context (START WITH, CONNECT BY). Its context information provides access to data for each level of the current recursion.

Object Wrappers are classes and methods that provide Object/Relational mapping between objects in client applications and data in database tables. What is unique about them is that the mapping is performed in the database server. They are native wrappers. The client interfaces with fully structured objects that are application specific. See our PDF - Using Native Wrappers in FirstSQL.

The methods described above -- Stored Procedures, UDFs, Triggers, Object Wrappers, can all manipulate relational domains natively. UDFs often extend the semantics (operations) for domains. This provides a convenient and powerful integration of these facilities under the OO implementation language.

Stored Procedures, UDFs, Triggers, Object Wrappers can also execute SQL statements using the current connection. This is important for Object Wrappers and commonly used in Stored Procedures.

Choosing an OO Implementation Language

The requirements for an OO language to be integrated into the ORDBMS for implementing domains, stored procedures, UDFs and plugins are:

The natural choice for the OO language is Java. It fulfills all the requirements above and runs in the native mode of the ORDBMS (FirstSQL/J is implemented in Java). User code and server code run in the same JVM giving improved performance, because of: In FirstSQL/J, relational domains can be built on general Java objects, known as Plain Old Java Objects (POJO). The ORDBMS stores Java code and object instances in the database for security and efficiency.

An additional advantage of choosing Java is the ability to create a sandbox for running user code. A sandbox allows deeper management of code by the server. The FirstSQL/J sandbox eliminates interference between user code running in different server sessions and interference between user code and the server itself. It also limits resource misuse by user written code. On the other hand, the sandbox for user code does not sacrifice the performance advantages listed above.

Java does provide a Security Manager with some of the features of the sandbox. However, the JVM only allows installation of a single Security Manager. By providing its own sandbox implementation, the ORDBMS can run embedded in a larger application or container that already has a Security Manager. For example, Java Applets run under a Security Manager provided by the browser. FirstSQL/J's approach allows the ORDBMS to be embedded in any Applet, even an unsigned one.

The Database in Java
Several of the major SQL DBMSs are now running user code written in Java inside the DBMS. They use their own JVM, supporting "Java in the Database". FirstSQL/J reverses this, supporting "The Database in Java". See our article, Java and Database Synergy, for a discussion contrasting these approaches.

Copyright © 2009 FFE Software, Inc. All Rights Reserved Worldwide