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:
- Telephone #'s, National Id's (social security, ...)
- Email addresses, URLs
- Credit card #'s
- Street addresses, Postal codes
- Organization specific - employee #'s, product #'s.
More general use domains would include:
- BLOBs (Binary Large Objects)
- CLOBs (Character Large Objects)
- Complex numbers (real and imaginary parts)
- Various coordinate systems.
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:
- Stored Procedures -- callable by clients
- User-Defined Functions (UDFs) -- used in SQL statements
- Triggers -- listeners for table modifications
- Object Wrappers -- O/R mapping inside the database.
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:
- Solid OO capabilities, including inheritance,
encapsulation, polymorphism
- Sufficient programming power to robustly implement
the most complex domain (stored procedure, etc.)
- Extensive portability - distributed form of code is
fully portable
- Popular and widely used
- A managed language.
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:
- Tighter integration of user/server code
- Sharing of libraries, smaller footprint
- Global optimization by JVM.
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