SQL3 Object Model
0. Intended Use
1. Basic Concepts
ANSI
(X3H2) and ISO (ISO/IEC JTC1/SC21/WG3) SQL standardization
committeeshave for some time been adding features to the SQL
specification to supportobject-oriented data management. The current
version of SQL in progressincluding these extensions is often referred
to as "SQL3" [ISO96a,b].SQL3 object facilities primarily involve
extensions to SQL's
typefacilities; however, extensions to SQL
table
facilities can alsobe considered relevant. Additional facilities
include control structuresto make SQL a computationally complete
language for creating, managing,and querying persistent object-like data
structures. The added facilitiesare intended to be upward compatible
with the current SQL92 standard (SQL92).This and other sections of the
Features Matrix describing SQL3 concentrateprimarily on the SQL3
extensions relevant to object modeling. However,numerous other
enhancements have been made in SQL as well [Mat96]. In addition,it
should be noted that SQL3 continues to undergo development, and thusthe
description of SQL3 in this Features Matrix does not necessarily
representthe final, approved language specifications.
The parts of SQL3 that provide the primary basis for supporting object-orientedstructures are:
- user-defined types (ADTs, named row types, anddistinct types)
- type constructors for row types and reference types
- type constructors for collection types (sets, lists, and multisets)
- user-defined functions and procedures
- support for large objects (BLOBs and CLOBs)
One
of the basic ideas behind the object facilities is that, in additionto
the normal built-in types defined by SQL, user-defined types may alsobe
defined. These types may be used in the same way as built-in types.For
example, columns in relational tables may be defined as taking valuesof
user-defined types, as well as built-in types. A user-defined
abstractdata type
(ADT) definition encapsulates attributes and operations ina single
entity. In SQL3, an abstract data type (ADT) is defined by specifyinga
set of declarations of the stored attributes that represent the valueof
the ADT, the operations that define the equality and ordering
relationshipsof the ADT, and the operations that define the behavior
(and any virtualattributes) of the ADT. Operations are implemented by
procedures called
routines. ADTs can also be defined as subtypes
of other ADTs. Asubtype inherits the structure and behavior of its
supertypes (multipleinheritance is supported). Instances of ADTs can be
persistently storedin the database only by storing them in columns of
tables.
A
row type is a sequence of field name/data type
pairs resemblinga table definition. Two rows are type-equivalent if both
have the samenumber of fields and every pair of fields in the same
position have compatibletypes. The row type provides a data type that
can represent the types ofrows in tables, so that complete rows can be
stored in variables, passedas arguments to routines, and returned as
return values from function invocations.This facility also allows
columns in tables to contain row values. A
namedrow type is a row
type with a name assigned to it. A named row typeis effectively a
user-defined data type with a non-encapsulated internalstructure
(consisting of its fields). A named row type can be used to specifythe
types of rows in table definitions. A named row type can also be usedto
define a
reference type. A value of the reference type definedfor
a specific row type is a unique value which identifies a specific
instanceof the row type within some (top level) database table. A
reference typevalue can be stored in one table and used as a direct
reference ("pointer")to a specific row in another table, just as an
object identifier in otherobject models allows one object to directly
reference another object. Thesame reference type value can be stored in
multiple rows, thus allowingthe referenced row to be "shared" by those
rows.
Collection types for sets, lists, and multisets have
also beendefined. Using these types, columns of tables can contain
sets, lists,or multisets, in addition to individual values.
Tables have also been enhanced with a
subtable facility. A tablecan be declared as a subtable of one or more supertables (it is then a
direct subtable
of these supertables), using an UNDER clause associatedwith the table
definition. When a subtable is defined, the subtable inheritsevery
column from its supertables, and may also define columns of its own.The
subtable facility is completely independent from the ADT subtype
facility.
See also
2. Objects and
7. Types and Classes.
2. Objects
One
of the basic ideas behind the object extensions in SQL3 is that,in
addition to the normal built-in types defined by SQL, user-defined
typesmay also be defined. These types may be used in the same way as
built-intypes. For example, columns in relational tables may be defined
as takingvalues of user-defined types, as well as built-in types. A
user-defined
abstract data type (ADT) definition encapsulates
attributes andoperations in a single entity. In SQL3, an abstract data
type (ADT) isdefined by specifying a set of declarations of the stored
attributes thatrepresent the value of the ADT, the operations that
define the equalityand ordering relationships of the ADT, and the
operations that define thebehavior (and any virtual attributes) of the
ADT. Operations are implementedby procedures called
routines.
ADTs can also be defined as subtypesof other ADTs. A subtype inherits
the structure and behavior of its supertypes(multiple inheritance is
supported). Instances of ADTs can be persistentlystored in the database
only by storing them in columns of tables.
A
row type is a
sequence of field name/data type pairs resemblinga table definition.
Two rows are type-equivalent if both have the samenumber of fields and
every pair of fields in the same position have compatibletypes. The row
type provides a data type that can represent the types ofrows in tables,
so that complete rows can be stored in variables, passedas arguments to
routines, and returned as return values from function invocations.This
facility also allows columns in tables to contain row values. A
namedrow type
is a row type with a name assigned to it. A named row typeis
effectively a user-defined data type with a non-encapsulated
internalstructure (consisting of its fields). A named row type can be
used to specifythe types of rows in table definitions. A named row type
can also be usedto define a
reference type. A value of the
reference type definedfor a specific row type is a unique value which
identifies a specific instanceof the row type within some (top level)
database table. A reference typevalue can be stored in one table and
used as a direct reference ("pointer")to a specific row in another
table, just as an object identifier in otherobject models allows one
object to directly reference another object. Thesame reference type
value can be stored in multiple rows, thus allowingthe referenced row to
be "shared" by those rows.
Tables have also been enhanced with a
subtable facility. A tablecan be declared as a subtable of one or more supertables (it is then a
direct subtable
of these supertables), using an UNDER clause associatedwith the table
definition. When a subtable is defined, the subtable inheritsevery
column from its supertables, and may also define columns of its own.The
subtable facility is completely independent from the ADT subtype
facility.
See also
7. Types and Classes, and
8. Inheritance and Delegation.
2.1 operations
Operations
that may be invoked in SQL include defined operations ontables (SELECT,
INSERT, UPDATE, DELETE), the implicitly defined functionsdefined for
ADT attributes, and routines either explicitly associated withADTs or
defined separately.
Routines associated with ADTs are FUNCTION
definitions for type-specificuser-defined behavior. The FUNCTION
definitions specify the operationson the ADT and return a single value
of a defined data type. Functionsmay either be SQL functions, completely
defined in an SQL schema definition,or external functions, defined in
standard programming languages.
See also
2.4 specification of behavioral semantics, and
2.5methods.
2.2 requests
SQL
functions associated with ADTs are invoked using either a
functionalnotation or a dot notation (the dot notation is syntactic
sugar for thefunctional notation). For example:
BEGIN DECLARE r real_estate ... SET r..area = 2540; /* same as area(r,2540) SET ... = r..area; /* same as area(r) ... SET ... = r..location..state; /* same as state(location(r)) SET r..location..city = 'LA'; /* same as city(location(r),'LA') ...
See also
2.4 specification of behavioral semantics.
2.3 messages
See
2.2 requests and
2.4 specification of behavioral semantics
2.4 specification of behavioral semantics
Routines
(procedures and functions) that define aspects of the behaviorof the
ADT may be encapsulated within the ADT definition (these routineshave
access to the ADT's PRIVATE attributes; routines may also be
definedoutside an ADT definition). A number of these routines have
predefinednames. For example, when an ADT is defined, a constructor
function is automaticallydefined to create new instances of the type.
The constructor function hasthe same name as the type and takes zero
arguments. It returns a new instanceof the type whose attributes are set
to their default values. The constructorfunction is PUBLIC. For every
attribute, observer and mutator functionsare also automatically defined
(these functions may also be explicitlydefined by the user). These
functions are used to read or modify the ADTattribute values. EQUAL and
LESS THAN functions may be defined to specifytype-specific functions for
comparing ADT instances. RELATIVE and HASHfunctions can be specified to
control ordering of ADT instances. CAST functionscan also be specified
to provide user-specified conversion functions betweendifferent ADTs.
Other
routines associated with ADTs include function definitions
fortype-specific user-defined behavior. ADT function definitions return
eitherBOOLEAN, if the result is to be used as a truth value in a Boolean
predicate,or a single value of a defined data type, if the result is to
be used asa value specification. Functions may either be SQL functions,
completelydefined in an SQL schema definition, or external function
calls to functionsdefined in standard programming languages.
See also
2. Objects and
2.5 methods.
2.5 methods (including multimethods and method combinations)
An
SQL routine is basically a subprogram. A routine may be either
aFUNCTION or a PROCEDURE. A routine reads or updates components of an
ADTinstance or accesses any other parameter declared in its parameter
list.A routine is specified by giving its name, its parameters, a
RETURNS clauseif it is a function, and a body. A parameter in the
parameter list consistsof a parameter name, its data type, and whether
it is IN, OUT, or INOUT(for functions, the parameters are always IN; the
RETURNS clause specifiesthe data type of the result returned).
A
routine may be either an SQL routine or an external routine. An
SQLroutine has a body that is written completely in SQL. An external
routinehas an externally-provided body written in some standard
programming language.If the function is an SQL routine, its body is any
SQL statement, includingcompound statements and control statements (see
11. Object Languages).A
number of new statement types have been added in SQL3 in order to
makeSQL computationally-complete enough so that ADT behavior can be
completelyspecified in SQL.
2.6 state
SQL3
supports state in the form of the values of the various SQL3 datatypes.
For example, the state of an ADT instance is the ordered sequenceof
stored components of an ADT instance; the state of a row is the
orderedset of values of its columns; and so on. Values can only be
stored persistentlyby storing them in the columns of database tables.
2.7 object lifetime
An
ADT instance can exist in any location that an ADT name can be
referenced.However, the only way that any ADT instance can be stored
persistentlyin the database is to be stored as the column value of a
table. For example,in order to store instances of an
employee_tADT (see
7. Types and Classes) persistently in a database, a tablewould have to be created with a column having the ADT as its data type,such as the
emp_data columnin:
CREATE TABLE employees ( emp_data employee_t );
There
is no facility in SQL3 to name individual instances of an ADT,and to
store them persistently in the database using only that name.
Similarly,there is no central place that all instances of a given ADT
will exist(a built-in type extent), unless the user explicitly creates
such a place,i.e., by defining a table in which all instances are
stored. Thus, in SQL3it is not necessarily possible to apply SQL query
operations to all instancesof a given ADT. The instances must first be
stored in one or more tables(as column values).
A row in a table exists until it is deleted. Deletion of an ADT instanceis done by deleting the row in which it is stored.
See also
7. Types and Classes.
2.8 behavior/state grouping
SQL3
routines may be defined within ADT definitions, or independentlyof
them. SQL3 supports a generalized object model in terms of
dispatching(see also
4. Polymorphism). However, there is no
concept of a genericfunction which groups routines with a common
signature. A routine definedwithin an ADT has access to that ADT's
PRIVATE members.
See also
2. Objects.
2.10 events
In SQL, a
trigger
is a named database construct that is implicitlyactivated whenever a
triggering event occurs. When a trigger is activated,the specified
action is executed if the specified condition is satisfied.An example
is:
CREATE TRIGGER update_balance BEFORE INSERT ON account_history /* event */ REFERENCING NEW AS ta FOR EACH ROW WHEN (ta.TA_type = 'W") /* condition */ UPDATE accounts /* action */ SET balance = balance - ta.amount WHERE account_# = ta.account_#;
Triggers
can be used for a number of purposes, such as validating inputdata,
reading from other tables for cross-referencing purposes, or
supportingalerts (e.g., through electronic mail messages). Triggering
events includeinsertion, deletion, and update of tables and columns. A
condition canbe any SQL condition (including those that involve complex
queries), andan action can be any SQL statement (including compound
statements, andthose that invoke SQL routines). The trigger can also
specify whether thetrigger should be activated BEFORE the triggering SQL
operation is performed,or AFTER. The condition and action can refer to
both old and new valuesof rows affected by the SQL statement. The
trigger condition and actioncan be executed FOR EACH ROW affected by the
triggering statement, or onlyonce for the whole triggering statement
(FOR EACH STATEMENT).
3. Binding
See
4. Polymorphism.
4. Polymorphism
Different routines may have the same name. This is referred to as
overloading,and
may be required, for example, to allow an ADT subtype to redefine
anoperation inherited from a supertype. SQL3 implements what is
sometimesknown as a
generalized object model, meaning that the types of
allarguments
of a routine are taken into consideration when determining whatroutine
to invoke, rather than using only a single type specified in
theinvocation as, for example, in C++ or Smalltalk. As a result, the
rulesfor determining which routine to invoke for a given invocation can
be fairlycomplex. The instance of the routine that is chosen for
execution is thebest match given the types of the actual arguments of
the invocation atrun time.
5. Encapsulation
Each
component (attribute or function) of an ADT has an encapsulationlevel
of either PUBLIC, PRIVATE, or PROTECTED. PUBLIC components form
theinterface of the ADT and are visible to all authorized users of the
ADT.PRIVATE components are totally encapsulated, and are visible only
withinthe definition of the ADT that contains them. PROTECTED components
arepartially encapsulated; they are visible both within their own ADT
andwithin the definition of all subtypes of the ADT. SQL3 also supports
encapsulationfor tables to the extent that views (derived tables) are
considered asproviding encapsulation.
6. Identity, Equality, Copy
By
default, testing corresponding attribute values for equality servesto
test for the equality of two ADT instances. Alternatively, the
specificationof an ADT supports declaration of a function to be used to
determine equalityof two ADT instances.
Two values are said to be
not distinct if either: both are the nullvalue, or they compare equal
according to [the SQL3] "< comparisonpredicate >". Otherwise they
are distinct. Two rows (or partialrows) are distinct if at least one of
their pairs of respective valuesis distinct. Otherwise they are not
distinct. The result of evaluatingwhether or not two values or two rows
are distinct is never unknown.
7. Types and Classes
The
parts of SQL3 that provide the primary basis for supporting
object-orientedstructures are extensions to its type facilities,
specifically:
- user-defined types (ADTs, named row types, anddistinct types)
- type constructors for row types and reference types
- type constructors for collection types (sets, lists, and multisets)
- user-defined functions and procedures
- support for large objects (BLOBs and CLOBs)
- SQL3 also supports a number of built-in scalar types.
One
of the basic ideas behind the object facilities is that, in additionto
the normal built-in types defined by SQL, user-defined types may alsobe
defined. These types may be used in the same way as built-in types.For
example, columns in relational tables may be defined as taking valuesof
user-defined types, as well as built-in types.
The simplest form of user-defined type in SQL3 is the
distincttype,
which provides a facility for the user to declare that two
otherwiseequivalent type declarations are to be treated as separate data
types.The keyword
DISTINCT used in an
declarationindicates that the resulting type is to be treated as
"distinct"from any other declaration of the same type. For example, if
two new typesare declared as:
CREATE DISTINCT TYPE us_dollar AS DECIMAL(9,2)
CREATE DISTINCT TYPE canadian_dollar AS DECIMAL(9,2)
any
attempt to treat an instance of one type as an instance of the
otherwould result in an error, even though each type has the same
representation.
A user-defined
abstract data type (ADT)
definition encapsulatesattributes and operations in a single entity. In
SQL3, an abstract datatype (ADT) is defined by specifying a set of
declarations of the storedattributes that represent the value of the
ADT, the operations that definethe equality and ordering relationships
of the ADT, and the operationsthat define the behavior (and any virtual
attributes) of the ADT. Operationsare implemented by procedures called
routines.
ADTs can also bedefined as subtypes of other ADTs. A subtype inherits
the structure andbehavior of its supertypes (multiple inheritance is
supported). Instancesof ADTs can be persistently stored in the database
only by storing themin columns of tables. An example ADT declaration
from [Mat95] is:
CREATE TYPE employee_t (PUBLIC name CHAR(20), b_address address_t, manager employee_t, hiredate DATE, PRIVATE base_salary DECIMAL(7,2), commission DECIMAL(7,2), PUBLIC FUNCTION working_years (p employee_t) RETURNS INTEGER <code to calculate number of working years>, PUBLIC FUNCTION working_years (p employee_t, y years) RETURNS employee_t <code to update number of working years>, PUBLIC FUNCTION salary (p, employee_t) RETURNS DECIMAL <code to calculate salary>);
ADTs
are completely encapsulated; only attributes and functions definedas
PUBLIC are accessible from outside the ADT definition. For each
attribute(such as
name), an observerand mutator function is automatically defined. Virtual attributes (suchas
working_years)
canalso be defined. These do not have stored values; their behavior is
providedby user-defined observer and mutator functions that read and
define theirvalues (
salary
is a read-onlyvirtual attribute). ADT instances are created by
system-defined constructorfunctions. The instances created in this way
have their attributes initializedwith their default values, and can be
further initialized by the user byinvoking mutator functions, as in:
BEGIN DECLARE e employee_t; SET e..working_years = 10; SET y = e..working_years; SET z = e..salary; END;
The expression
e..working_yearsillustrates the dot notation used to invoke the
working_yearsfunction of the ADT instance denoted by
e.Users can also define specialized constructor functions which take parametersto initialize attributes.
A
row type
is a sequence of field name/data type pairs resemblinga table
definition. Two rows are type-equivalent if both have the samenumber of
fields and every pair of fields in the same position have
compatibletypes. The row type provides a data type that can represent
the types ofrows in tables, so that complete rows can be stored in
variables, passedas arguments to routines, and returned as return values
from function invocations.This facility also allows columns in tables
to contain row values. An exampleis:
CREATE TABLE employees (name CHAR(40), address ROW(street CHAR(30), city CHAR(20), zip ROW(original CHAR(5), plus4 CHAR(4)))); INSERT INTO employees VALUES('John Doe', ('2225 Coral Drive', 'San Jose', ('95124', '2347'))));
A
named row type
is a row type with a name assigned to it. Anamed row type is
effectively a user-defined data type with a non-encapsulatedinternal
structure (consisting of its fields). A named row type can beused to
specify the types of rows in table definitions. For example:
CREATE ROW TYPE account_t (acctno INT, cust REF(customer_t), type CHAR(1), opened DATE, rate DOUBLE PRECISION, balance DOUBLE PRECISION, );
CREATE TABLE account OF account_t (PRIMARY KEY acctno );
A named row type can also be used to define a
reference type.A
value of the reference type defined for a specific row type is a
uniquevalue which identifies a specific instance of the row type within
somebase (top level) database table. A reference type value can be
stored inone table and used as a direct reference ("pointer") to a
specificrow in another table, just as an object identifier in other
object modelsallows one object to directly reference another object. The
same referencetype value can be stored in multiple rows, thus allowing
the referencedrow to be "shared" by those rows. For example, the
account_trow type defined above contains a
custcolumn with the reference type
REF(customer_t).A value of this column identifies a specific row of type
customer_t.The
value of a reference type is unique within the database, never
changesas long as the corresponding row exists in the database, and is
never reused.
In general, the value of a reference type such as
REF(customer_t)can refer to a row in any table having rows of type
customer_t.If a SCOPE clause is specified in the definition of a table, such referencesare restricted to rows in a single table, as in:
CREATE TABLE account OF account_t (PRIMARY KEY acctno, SCOPE FOR cust IS customer );
In this case
customer_trows referenced in the
custcolumn must be stored in the
customertable. Use of SCOPE does not imply any referential integrity constraint.
References can be used in
path expressions
(similar to thoseused in some other object query languages), that
permit traversal of objectreferences to "navigate" from one row to
another. Such expressionscan also include the invocation of functions on
ADT instances. An exampleis:
SELECT a.cust->name FROM account a WHERE a.cust->address..city = "Hollywood" AND a.balance > 1000000;In the SELECT statement, a.cust->name represents:
1. the selection of the
custcolumn's value (an instance of type
REF(customer_t)) from the row denoted by
a(a row of type
account_t)
2. the traversal (dereference) of that instance of type
REF(customer_t)to the row of type
customer_tit refers to (
-> isa dereferencing operator)
3. the selection of the name column from the referenced
customer_trow.
In the
WHERE clause,
a.cust->address..cityrepresents a similar process, identifying the
addresscolumn of the referenced
customer_trow, and then applying the
cityobserver function to the ADT instance found in the
addresscolumn.
Collection types
for sets, lists, and multisets have also beendefined. Using these
types, columns of tables can contain sets, lists,or multisets, in
addition to individual values. For example:
CREATE TABLE employees (id INTEGER PRIMARY KEY, name VARCHAR(30), address ROW(street VARCHAR(40), city CHAR(20), start CHAR(2), zip INTEGER), projects SET (INTEGER), children LIST(person), hobbies SET (VARCHAR(20)) );
The
BLOB (Binary Large Object) and CLOB (Character Large Object) typeshave
been defined to support very large objects. Instances of these typesare
stored directly in the database (rather than being maintained in
externalfiles). For example:
CREATE TABLE employees (id INTEGER, name VARCHAR(30), salary us_dollar, ... resume CLOB(75K), signature BLOB(1M), picture BLOB(12M));
LOB
types are excluded from some operations, such as greater and lessthan
operators, but are supported by other operations, such as value
retrieval,and the LIKE predicate.
8. Inheritance and Delegation
An
ADT can be defined as a subtype of one or more ADTs by defining itas
UNDER those ADTs (multiple inheritance is supported). In this case,the
ADT is referred to as a
direct subtype of the ADTs specifiedin the UNDER clause, and these ADTs are
direct supertypes.
A typecan have more than one subtype and more than one supertype. A
subtype inheritsall the attributes and behavior of its supertypes;
additional attributesand behavior can also be defined. An instance of a
subtype is consideredan instance of all of its supertypes. An instance
of a subtype can be usedwherever an instance of any of its supertypes is
expected.
Every instance is associated with a "most specific
type" thatcorresponds to the lowest subtype assigned to the instance. At
any giventime, an instance must have exactly one most specific type (in
some cases,multiple inheritance must be used to ensure this is true).
The most specifictype of an instance need not be a leaf type in the type
hierarchy. Forexample, a type hierarchy might consist of a maximal
supertype
person,with
student and
employeeas subtypes.
student might have two directsubtypes
undergrad and
grad.An instance may be created with a most specific type of
student,even
though it is not a leaf type in the hierarchy. A TYPE predicate
allowsfor the type of an ADT instance to be tested at run time.
A
subtype definition has access to the representation of all of itsdirect
supertypes (but only within the ADT definition that defines thesubtype
of that supertype), but it has no access to the representationof its
sibling types. Effectively, components of all direct supertype
representationsare copied to the subtype's representation with the same
name and datatype. To avoid name clashes, a subtype can rename selected
components ofthe representation inherited from its direct supertypes.
A
subtype can define operations like any other ADT. A subtype can
alsodefine operations which have the same name as operations defined for
othertypes, including its supertypes (overriding).
A table can be declared as a subtable of one or more supertables (itis then a
direct subtable of these supertables), using an UNDERclause associated with the table definition. An example is:
CREATE TABLE person (name CHAR(20), sex CHAR(1), age INTEGER);
CREATE TABLE employee UNDER person (salary FLOAT);
CREATE TABLE customer UNDER person (account INTEGER);
The
subtable facility is completely independent from the ADT
subtypefacility. When a subtable is defined, the subtable inherits every
columnfrom its supertables, and may also define columns of its own. A
maximalsupertable (a supertable that is not a subtable of any other
table) togetherwith all its subtables (direct and indirect) makes up a
subtable family.A
subtable family must always have exactly one maximal supertable. Anyrow
of a subtable must correspond to exactly one row of each direct
supertable.Any row of a supertable corresponds to at most one row of a
direct subtable.
The rules for the SQL INSERT, DELETE, and UPDATE
DML statements aredefined in such a way as to keep the rows in the
tables of a subtable familyconsistent with each other, in accordance
with the rules described above.Specifically:
- If a row is
inserted into a subtable T, then a corresponding row (withthe same row
identifier, and the same values as any values provided forinherited
columns of T) is inserted into each supertable of T, cascadingupward in
the table hierarchy. If T is a maximal supertable, a row is insertedonly
into T.
- If a row is updated in a supertable, then all inherited columnsin all corresponding rows of the direct and indirect subtables are correspondinglychanged.
- If a row is updated in a subtable, then every correspondingrow is changed so that their column values match the newly updated values.
- If a row in a table that belongs to a subtable family is deleted, thenevery corresponding row is also deleted.
The
semantics maintained are those of "containment"; a rowin a subtable is
effectively "contained" in its supertables.This means that, for example,
a row could exist for a person in the
persontable without a corresponding row in the
employeetable
(if the person is not also an employee). A row for a new employee,not
corresponding to any existing person, could be inserted into the
employeetable, and this would automatically create a corresponding row in the
persontable.
9. Noteworthy Objects
9.1 relationships
Relations
(tables) can be used to define generalized n-ary relationships,as in
SQL92; referential and other integrity constraints can be definedon
these tables. Columns whose types are reference types also allow
modelingof relationships in SQL3. References to groups of objects can be
specifiedusing rows containing (directly or indirectly) instances of
the SQL3 MULTISET(..),LIST(..), and SET(..) collection types (see
9.5 aggregates).
9.2 attributes
There are two types of ADT attributes,
stored attributes and
virtual attributes. A
stored
attribute is specified by givingan attribute name and a data type. The
data type of a stored attributecan be any known data type, including
another ADT. Each stored attributeimplicitly declares a pair of
functions to get (observer function) andset (mutator function) the
attribute value. A
virtual attributehas a value that is derived
or computed by a user-defined observer function.Because ADTs are
encapsulated, and because the syntax for function invocationis the same
for any attribute, only the type owner and subtype definerswould ever be
aware of this distinction.
Columns of tables can also be used to represent attributes, as in SQL92.
9.3 literals
In
SQL3, literals are used to specify non-null values. The rules
forforming literals for the various built-in types are contained in the
draftstandard [ISO96a]. ADTs do not have literal values. Row type
literals areformed by concatenating values for the individual columns,
as in:
CREATE TABLE employees (name CHAR(40), address ROW(street CHAR(30), city CHAR(20), zip ROW(original CHAR(5), plus4 CHAR(4)))); INSERT INTO employeesVALUES('John Doe', ('2225 Coral Drive', 'San Jose', ('95124', '2347'))));
9.4 containment
SQL3
supports the concept of values being contained within values
(e.g.,instances of row types, or collections of such instances, can be
containedin a column of a row) or within ADTs. A form of containment
semantics canalso be implemented by specifying triggers to enforce
cascaded manipulationsof a collection of data structures when one of
them is manipulated. Thiskind of containment must be specified by the
user.
9.5 aggregates
SQL3 provides row types as
literal structures. Instances of row typescan be used as values in
tables; row types can also be nested. A numberof predefined
parameterized collection types are also defined. A collectionmay be
specified as
SET(<type>),
MULTISET(<type>),or
LIST(<type>). In each case, the
<type>parameter (called the
element type) can be a predefined type, anADT, a row type, or another collection type. For example
SET(INTEGER)and
SET(LIST(INTEGER)) would both be validdeclarations, as would
SET(movie) and
SET(LIST(movie)),where
movie
is some previously defined ADT.At present, the element type cannot be a
reference type, nor can it bea named row type containing a field whose
type is a reference type.
A collection can be used as a simple
table in queries. In this case,each element of the collection
corresponds to a row in the table. The tableis treated as having a
single column whose type is defined by the typeof the instances of the
collection. Since collection types are data types,they must be declared
as the types of table columns in order to store instancesof collections
persistently in the database.
10. Extensibility
New tables and types (ADTs, row types, collection types, etc.) can bedefined based on existing types. See also
2. Objects and
7. Typesand Classes.
Existing types may be modified to add new operations, attributes, orconstraints.
Existing instances may not acquire or lose type without creating a newinstance and destroying the old one.
10.1 Dynamic
Limited
schema evolution is possible by applying the ALTER statementto a base
table. Actions that can be taken using the ALTER statement
includeadding, altering, and dropping columns, and adding and dropping
supertables,and table constraints. Data types can also be added and
dropped.
10.2 Metaclasses/Metaobject Protocol
SQL3 has no notion of metaclass and its semantics are not extensible.
10.3 Introspection
definitional aspects of instances; access to definitions (e.g., type/classobjects) at run time)
Some metadata is maintained in SQL3 tables (this is a requirement ofSQL92) and can be read by the user.
11. Object Languages
A
number of new statement types have been added in SQL3 in order tomake
SQL computationally-complete enough so that object behavior can
becompletely specified in SQL. Some of the additional statements
providedfor writing SQL functions include:
- An assignment
statement that allows the result of an SQL value expressionto be
assigned to a free standing local variable, a column, or an attributeof
an ADT.
- A CALL statement that allows invocation of an SQL procedure.
- A RETURN statement that allows the result of an SQL value expressionto be returned as the RETURNS value of the SQL function.
- A CASE statement to allow selection of an execution path based on alternativechoices.
- An
IF statement with THEN, ELSE, and ELSEIF alternatives to allow
selectionof an execution path based on the truth value of one or more
conditions.
- Statements for LOOP, WHILE, and REPEAT to allow
repeated executionof a block of SQL statements. WHILE checks a
<search condition> beforeexecution of the block, and REPEAT checks
it afterwards. All three statementsare allowed to have a statement
label.
Additional control facilities available include compound
statementsand exception
handling.
A compound statement is a statement thatallows a collection of SQL
statements to be grouped together into a "block".A compound statement
may declare its own local variables and specify exceptionhandling for an
exception that occurs during execution of any statementin the group.
For exception handling, a CONDITION declaration establishesa one-to-one
correspondence between an SQLSTATE error condition and a
user-definedexception name. HANDLER declarations associate user-defined
exception handlerswith specific exceptions.
The SQL92 standard
defines language bindings for a number of standardlanguages. A key
aspect of the individual language bindings is the definitionsof
correspondences between SQL data types and host language data types.In
some cases, these are relatively straightforward; e.g., the SQL
CHARACTERdata type maps to a C char. In other cases, the mapping is not
so straightforward.For example, SQL92 has a TIMESTAMP data type, but
standard programminglanguages do not contain a corresponding built-in
type. In these cases,SQL requires the use of a CAST function to convert
database TIMESTAMP datato character data in the program, and vice-versa
[MS93]. In SQL92, thesetype correspondences are defined only at the
level of elementary scalardata types. There are no type correspondences
defined for structured types,e.g., between a row of an SQL table and a
flat record or structure in aprogramming language (although some such
correspondences would be relativelystraightforward to define).
There
are currently no bindings defined between the SQL3 ADT extensions(or
rows containing them) and object classes or types in
object-orientedprogramming languages such as C++ or Smalltalk, although
these are underinvestigation.
12. Semantics of Base Classes (+ type constructors)
See
7. Types and Classes.
