SQL Bible [Electronic resources] نسخه متنی

Objects

The object-oriented approach — an
interesting academic topic in the 1980s — became mainstream in the early 1990s
and the de-facto standard for every new development thereafter (the
object-oriented term is explained later in the chapter).

Nearly any modern programming language is
(or claims to be) object-oriented. However, even though the object-oriented
approach proved to be successful in computer programming (in terms of increased
development speed, increased robustness etc.), it is not a very popular concept
in the database market so far. Although there are some purely object-oriented
databases (which we discuss later in this chapter), their market share is
rather insignificant. The implementations of the different concept — supplying
objects and tools for working with RDBMS — seems to be more practical. Many
major RDBMS vendors — including Oracle and DB2 UDB — provide some kind of using
objects with traditional RDBMS (the Object Oriented RDBMS — OORDBMS —
approach). Therefore, even though the object-oriented paradigm is not directly
related to the contents of this book, we will briefly introduce it in the
database context in this chapter.

In the RDBMS world, OO refers to the
ability of the database to store and retrieve instances (explained in the
following paragraph) of the objects — very much the same way as XML documents:
either by being parsed into text and reconstituted on demand, or by saving the
entire object AS-IS — be it Java, ActiveX or .NET object. In addition, some
databases sponsor object data types, which introduce OOP
principles into their procedural SQL programming (see
Chapter
14 for more information on SQL procedural extensions). You may think of
these as User Defined Data types (see
Chapter
3 for more information on data types) that also contain some methods or
functions.

OOP Paradigm

Object-oriented approach treats
everything as objects — i.e. programming structures that are capable of
containing data and have some relevant methods. Usually, objects are
representations of real-life entities, reduced in their complexity to a few
well defined features and tasks they should be able to perform. A person, a
tree, a car, an organization — all can be represented as objects; the same goes
for some abstract objects like 'bank account', or 'data access object'.
Consider an object that models, say, a bank account: it might to have an
attribute 'balance' and 'account ID', methods 'withdraw' and 'deposit' — all
representing some functionality that is expected of an object of this type. The
main principles of Object Orientation are encapsulation, inheritance,
polymorphism, and identity, all of which will be discussed in the next
paragraphs.

Objects and classes

Each object has
its own attributes and methods. For example, for object CAR you can have such
attributes as engine size, engine type, wheel size, interior color, exterior
color, shift type, and so on. The methods may include 'drive', 'turn(left |
right)' and so on. The objects are defined through programming concept of
classes. An object is an instance of a class. The common
analogy here is the blueprint of a house and the actual house built based on
that blueprint. You can instantiate many objects of the same class in the very
same way as many houses could be built from the same blueprint, being different
only in their attributes — color, location etc.

Encapsulation

The main idea of encapsulation is to
hide details of implementation, and make it accessible only by explicitly
defined methods that reduce the impact of changes made to the internals of the
objects, and enforce security. The non-programmer's world analogy would be any
programmable electronic device you may have at home like a VCR or a microwave
oven: you normally can manipulate those only through buttons devised for this
purpose, though it might be possible to open the cover and use one's best
judgment to control its operations through manipulating electronic
components.

Inheritance

Inheritance
is a mechanism that allows the programmer to create a new class based on (or
inherited from) the old (existing) class. The new class, called
(unsurprisingly) child class, has all the attributes (properties) and methods
of the old class (parent), but some of them could be ignored or modified, while
some new characteristics could be added. For example, subclasses Chevrolet,
Ford, Honda, Toyota, and Nissan could be derived from class CAR. This allows
for reuse of the code this class contains, and makes development process more
rigorous.

Polymorphism

Polymorphism means that a given method
could be applied to different objects. For example, the same method could
perform logically consistent actions when it gets a different number of
arguments or arguments of different data types. For example, a hypothetical
function
ADD could add numbers, concatenate
strings, and increment a date by the given number of days, depending on the
internal implementation. For the programmer that means that as long as s/he
calls the method with correct arguments, s/he does not have to worry about
details of implementation, and expect correct results.

Identity

Since two objects can be inherited
from the same class and all their characteristics can be exactly the same,
their identities must be different in order to tell them apart. Each object has
its own unique identifier, or handle. Usually handles are
assigned to the objects implicitly by the system.

Oracle 9i
support

Oracle allows you to create
object types — special structures with object attributes
and methods. The syntax consists of two parts. First, you declare the object
type itself:

CREATE [OR REPLACE] TYPE
<object_name> AS OBJECT ( <attribute_name>
<attribute_datatype>,... [MEMBER FUNCTION <function_name>
[<function_parameter> <parameter_datatype>,... RETURN
<datatype>]... [MEMBER PROCEDURE <procedure_name>
[<procedure_parameter> <parameter_datatype>,...]...);

Then you define the object type body,
where all the functionality implementation code would be contained. This part
is optional unless you had any member functions/procedures in the object type
declaration. The syntax is as follows (assuming one member function and one
member procedure were declared in the
CREATE
OBJECT
TYPE section):

CREATE OR REPLACE OBJECT TYPE
BODY <object_name> AS MEMBER FUNCTION <function_name>
[<function_parameter> <parameter_datatype>,... RETURN
<datatype>]... IS [<declaration_section>] BEGIN
<function_body> [EXCEPTION <exception_body>] END; MEMBER PROCEDURE
<procedure_name> [<procedure_parameter>
<parameter_datatype>,...]... IS [<declaration_section>] BEGIN
<procedure_body> [EXCEPTION <exception_body>] END;
END;

The instances of this object could be
stored in the Oracle tables, and invoked in Oracle's PL/SQL stored
procedures.

IBM DB2 UDB 8.1 support

DB2 allows you to create objects in a
similar way to that of Oracle, but the syntax is quite different and more
complex. Here is the simplified version adopted from IBM DB2 UDB:

CREATE TYPE <type_name>
[UNDER <supertype_name>] AS ( <attribute_name>
<attribute_datatype>,...) INSTANTIABLE | NOT INSTANTIABLE [WITHOUT
COMPARISSONS] [NOT FINAL] MODE DB2SQL METHOD <method_name>
(<attribute_name> <attribute_datatype>,...) RETURNS
<datatype> <method_specification>

In addition to optional user-defined
types, the execution of the
CREATE
TYPE statement generates methods for
retrieving and updating the values of attributes, as well as for constructing
instances of a structured type and for supporting the comparison operators
(=,
<>,
<,
<=,
>, and
>=).

For each method declared with the
CREATE
TYPE statement, you can use the
CREATE
METHOD statement to associate a method
body with a method specification. Here is the syntax:

CREATE METHOD
<method_name> RETURNS <datatype> FOR"TYPE <type_name> RETURN
{<value> | <sql_statement>}

Microsoft SQL Server 2000

Microsoft SQL Server 2000 does not
provide object-oriented features with its RDBMS product; the next closest thing
would be the
TABLE system data type that allows you to
arrange data returned by the SQL query as a set of rows. It cannot be used as a
column data type, and therefore cannot be used as permanent storage. An
instance of an object created by some external application could be saved in
the binary format in the database, but it is no different from any other binary
data stored in the RDBMS.

The user-defined data type in MS SQL
Server does not allow for creation of a structure data type (like a record
field), and cannot contain methods.

Compensating the lack of OOP features,
SQL Server 2000 provides the ability to invoke and use ActiveX/OLE objects from
within Transact-SQL code through system stored procedures.

SQL Server 2000 also provides Meta Data
Services that allow for storage and management of meta data about information
systems and applications. It serves as a central repository for data and
component definitions, development and deployment models, reusable software
components, and data warehousing descriptions; it facilitates development and
modeling processes through establishing a common framework for developers and
analysts, borrowing heavily from UML (Universal Modeling Language).

Abstract data types

SQL99
introduced the abstract data type (ADT) that is in fact undistinguishable from
the definition of class. ADT specification consists of two parts: attribute
specification and method specification. ADT behavior is completely encapsulated
within the type definition. The ADT enables database programmers to construct
application-specific data types in the database with built-in methods to
manipulate these data types. As usual, implementation of the standard lags
behind — none of the three major vendors have implemented this feature.

Long-time standard tools of every
programming language, collections and
arrays have made inroads into the domain of RDBMS. They
mostly facilitate programming process, eliminating need for additional tables.
Both arrays and collections contain data elements, the main difference between
these data types being that, as a rule, an array contains elements of the same
data type, whereas collections can accommodate different data types.

Imagine that you have decided, in
spite of all the talk about good database design (see
Appendix
C for more information on RDBMS design), to store an employee's
educational credentials inside the same table where you store other employee
information. If your employees generally have three diplomas, you might
consider declaring an
EDUCATION_CREDENTIALS column as a
VARRAY(5)
OF
VARCHAR(25) — using Oracle
9i features. Now for each employee there will be five
potential records to store information.

To accommodate the case, when more
than five educational credentials are acquired, you may want to use a nested
table (i.e., a column is of table type, and could therefore contain
unrestricted amount of data — Oracle's feature). Both Oracle and IBM DB2 UDB
support arrays and collections, while Microsoft does not.

Object-oriented databases

The topic of object-oriented databases
is clearly out of scope for this book; we are going to give you only a
high-level overview of the current state of affairs.

With the advent of the object-oriented
programming came the idea of object storage. Relational
databases, in spite of all modernization, new data types, and functionality,
remain by and large text-based — that is, they parse, store, and search textual
data. While various new media formats — video, sound, PowerPoint presentations,
Microsoft Word structured storage — were accommodated by inclusion of the new
data types, essentially it remains the same text-based approach, somewhat
expanded.

As OO languages (C++, Java, C#, Visual
Basic.NET, Delphi, Smalltalk, Eiffel, etc.) become increasingly popular, it
begins to make sense to store information in objects as they are defined by the
classes implemented in these languages. Imagine a Java class
EMPLOYEE, which has its properties
(attributes) and methods defined; when an application creates an instance of
this class to add a new employee, it populates its properties and then saves
the whole object into a database. For an RDBMS that would mean populating a
number of tables with textual information; for an object-oriented database, it
would mean saving the object as it is — either as a binary, byte-code, or text
description version, and still being able to track this object by, say,
employee ID. This is what OODBMS and OORDBMS are all about.

While pure object-oriented databases try
to implement their own "pure OO" method of storing information, OORDBMS rely on
proven relational technology to establish a hierarchy of the objects. There are
new standards emerging (Table 17-1), and new ideas taking hold.

Because of a tight coupling of the OO
database with the programming language, the application written for one
database might be impossible to use with some other products. There are several
initiatives to establish a standard for OODBMS, which so far have resulted in
the ODMG 3.0 standard, in addition to a number of proprietary ways to do
things.

While still a novelty, OODBMS are making
it into the mainstream of the academic and corporate worlds, fueled mainly by
adoption of object-oriented technologies like Java (and EJB — Enterprise Java
Beans) and Smalltalk.

Some object-oriented databases available
on the market today are shown in
Table
17-2.

The main
advantage of the OODBMS over OORDBMS comes from eliminating the mapping of the
objects from the application (client) to RDBMS structure. In the current
environment, in which data is still coming as text or numbers, RDBMS are much
faster than comparable OODBMS/OORDBMS; at the same time there are situations
when the object approach might prove to be superior to the "old" relational
model. The jury is still out.