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 calledroutines. 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 adirect 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-definedabstract 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.

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 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.

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.

  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 andvirtual 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.

13. Background and References

[Gal92] Leonard Gallagher, "Object SQL: Language Extensions forObject Data Management", Proc. Intl. Conf. on Knowledge and InformationMgmt. (CIKM-92), Baltimore, MD, November 1992.
ftp://speckle.ncsl.nist.gov/isowg3/dbl/BASEdocs/descriptions/sql3overview.txt

[ISO96a] ISO/IEC JTC1/SC21 N10489, ISO//IEC 9075, Part 2, CommitteeDraft (CD), Database Language SQL -Part 2: SQL/Foundation, July 1996.
ftp://speckle.ncsl.nist.gov/isowg3/dbl/BASEdocs/cd-found.pdf

[ISO96b] ISO/IEC JTC1/SC21 N10491, ISO//IEC 9075, Part 8, CommitteeDraft (CD), Database Language SQL -Part 8: SQL/Object, July 1996. ftp://speckle.ncsl.nist.gov/isowg3/dbl/BASEdocs/cd-objct.pdf

[KCDM+95] Krishna Kultarni, Mike Carey, Linda DeMichiel, Nelson Mattos,Wei Hong, and Mike Ubell, "Introducing Reference Types and CleaningUp SQL3's Object Model", SQL3 Change Proposal X3H2-95-456, November26, 1995.

[KCS94] William Kelley, Amelia Carlson, Phil Shaw, personal communication,August 1994.

[Kul93] Krishna G. Kulkarni, "Object-Orientation and the SQL Standard",Computer Standards & Interfaces 15 (1993), 287-300.

[Mat96] Nelson Mattos, "An Overview of the SQL3 Standard",presentation foils, Database Technology Institute, IBM Santa Teresa Lab.,San Jose, CA, July 1996, ftp://speckle.ncsl.nist.gov/isowg3/dbl/BASEdocs/descriptions/SQL3_foils.ps.

[MS93] Jim Melton and Alan R. Simon, Understanding the New SQL: AComplete Guide, Morgan Kaufmann, San Francisco, 1993.

featuresmatrix intro page