SQL3 / SQL:2008 The SQL3 standard is big and is therefore divided - - PowerPoint PPT Presentation

SQL3 / SQL:2008

The SQL3 standard is big and is therefore divided into parts:

• 1. SQL/Framework, contains information common to all parts of the standard

and describes the parts.

• 2. SQL/Foundation, Data definition and data maniputlation syntax and

semantics, including SQL embedded in non-object programming languages.

• 3. SQL/CLI (Call Level Inteface), the API for programming languages,

corresponds to ODBC.

• 4. SQL/PSM (Persistent Stored Modules), stored routines, external routines,

and procedural language extensions to SQL.

• 5. SQL/MED (Management of External Data) provides extensions to SQL that

define foreign-data wrappers and datalink types to allow SQL to manage data that is accessible to, but not managed by, an SQL-based DBMS.

DD2471 (Lecture 08) Modern database systems & their applications Spring 2012 1 / 44

SQL3 / SQL:2008 . . .

• 6. SQL/OLB (Object Language Bindings), defines the syntax and symantics of

SQLJ, which is SQL embedded in Java.

• 7. The SQL/MM (Multimedia), which extends SQL to deal with large, complex

and maybe streaming data, like video, audio and spatial data.

• 8. SQL/Schemata defines the Information Schema and Definition Schema,

providing a common set of tools to make SQL databases and objects self-describing.

• 9. SQL/JRT (Java Routines and Types), specifies the ability to invoke static Java

methods as routines from within SQL applications. It also calls for the ability to use Java classes as SQL structured user-defined types.

• 10. SQL/XML, specifies SQL-based extensions for using XML in conjunction with

SQL.

DD2471 (Lecture 08) Modern database systems & their applications Spring 2012 2 / 44

SQL3:2008 – row types

A row type, ”ROW TYPE”, is a sequence of pairs, name and data type, and corresponds to either a composite attribute in EER modelling or a row in a table. E.g.:

create table person ( ssn char(11), name row (fname varchar(25), lname varchar(25)), address row (streetname varchar(25), streetno smallint, postalcode char(6), city varchar(25))); insert into person values (’451112-0356’, row(’Serafim’,’Dahl’), row(’Blomsterv¨ agen’,12,’131 37’,’Nacka’));

DD2471 (Lecture 08) Modern database systems & their applications Spring 2012 3 / 44

SQL3:2008 – user defined types (UDT)

• DISTINCT TYPE, is the simplest form. Used to semantically distinguish data

with the same underlying structure , e.g.:

CREATE DISTINCT TYPE SocialSecurityNumber AS CHAR(11); CREATE DISTINCT TYPE EmployeeNumber AS CHAR(11);

Note the distinction here between type and domain. A domain is a restriction

• n type to clarify which values that may be stored in a table column. The

domain is in both cases CHAR(11).

• A UDT consists, in the general case, of one or more attribute definitions, a

number (even zero) of routine declarations and a number (even zero) of

• perator declarations.
• A UDT may be specified in so many ways that an example has to be enough.

Suppose that we want to represent persons:

DD2471 (Lecture 08) Modern database systems & their applications Spring 2012 4 / 44

SQL3:2008 – UDT . . .

CREATE TYPE PersonType AS ( ssn SocialSecurityNumber CHECK (alive(ssn)), firstname VARCHAR(15), lastname VARCHAR(15)) INSTANTIABLE NOT FINAL REF IS SYSTEM GENERATED INSTANCE METHOD age() RETURNS INTEGER, INSTANCE METHOD age(ssn SocialSecurityNumber) RETURNS PersonType; CREATE INSTANCE METHOD age() RETURNS INTEGER; FOR PersonType BEGIN RETURN /* code to calculate age */ END; CREATE INSTANCE METHOD age(ssn SocialSecurityNumber) RETURNS PersonType FOR PersonType BEGIN SELF.ssn = ssn RETURN SELF END;

DD2471 (Lecture 08) Modern database systems & their applications Spring 2012 5 / 44

SQL3:2008 – observers and mutators

For each attribute in a UDT an observer function and a mutator function are automatically created. Both may be (should be??) redefined by the user. SQL3 uses total encapsulation (as in Smalltalk), meaning that attributes are only accessible by the observer and manipulable by the mutator. The observer for firstname in the PersonType is:

FUNCTION firstname (p PersonType) RETURNS VARCHAR(15) RETURN p.firstname;

and the corresponding mutator:

FUNCTION firstname (p PersonType RESULT, newname VARCHAR(15)) RETURNS PersonType BEGIN p.firstname = newname; RETURN p; END;

DD2471 (Lecture 08) Modern database systems & their applications Spring 2012 6 / 44

SQL3:2008 – constructors

A constructor is created as well. The default constructor takes no arguments and sets all attributes to their defaults (which almost always is a bad idea). The user may (must??) redefine the default constructor and a reasonable constructor for PersonType might be:

CREATE CONSTRUCTOR METHOD PersonType ( ssn SocialSecurityNumber, firstname VARCHAR(15), lastname VARCHAR(15)) RETURNS PersonType BEGIN SET SELF.ssn = ssn; SET SELF.firstname = firstname; SET SELF.lastname = lastname; RETURN SELF; END;

DD2471 (Lecture 08) Modern database systems & their applications Spring 2012 7 / 44

SQL3:2008 – User Defined Routine (UDR)

The call for generality makes it important to be able to include functions and procedures that are impossible to construct with the built-in facilities E.g. if you have stored images in the database and want to present thumbnail images for an overview it might be better to be able to generate the thumbnail image rather than to store it in the database A UDR can be defined as part of a UDT or as part of a schema. I may be written in almost any language, even directly in SQL(3) Procedures are invoked by the command CALL and can have parameters of type IN, OUT or INOUT as in ADA

DD2471 (Lecture 08) Modern database systems & their applications Spring 2012 8 / 44

SQL3:2008 – UDR . . .

CREATE FUNCTION thumbNail (IN im ImageType) RETURNS BOOLEAN EXTERNAL NAME /usr/local/bin/thumbnail LANGUAGE C PARAMETER STYLE GENERAL DETERMINISTIC NO SQL;

the external executable ’thumbnail’ has to be provided by the user. The ORDBMS will link to it, store it in the database and invoke it when necessary. ’DETERMINISTIC’ means the the function always gives the same result for the same input (= no side effects) ’NO SQL’ means the the function does not contain any SQL statements (= no embedded SQL in this case) Other options: ’CONTAINS SQL’, ’READS SQL DATA’ and ’MODIFIES SQL DATA’

DD2471 (Lecture 08) Modern database systems & their applications Spring 2012 9 / 44

SQL3:2008 – UDR, polymorphism

Polymorphism in the shape of overloading exists but with restrictions:

• methods may redefine the content of superclass methods with the same

name and the same signature,

• two methods in the same schema may carry the same name as long as they

differ in signature,

• Interesting: If no method is found that has the exact signature of the call (that

can be inferred from the call) SQL tries to find a “closest match” and attempts to invoke that method if one is found.

DD2471 (Lecture 08) Modern database systems & their applications Spring 2012 10 / 44

SQL3:2008 – reference types and object identity

Just as in Postgres there is an implicit ’oid’ assigned to every row in every table. The objective is never to reuse an oid the ensure reference integrity. They are stored in the database, may be shared among databases, and constitute a direct reference to a specific row in a specific table in a database (or, if shared, a specific database in the actual server).

DD2471 (Lecture 08) Modern database systems & their applications Spring 2012 11 / 44

SQL3:2008 – sub- and supertypes

CREATE TYPE empType UNDER PersonType AS ( empNo employeeNumber, position VARCHAR(10) DEFAULT ’Assistant’, salary DECIMAL(8,2), department VARCHAR(10), INSTANCE METHOD isBoss () RETURNS BOOLEAN INSTANTIABLE NOT FINAL; CREATE INSTANCE METHOD isBoss () RETURNS BOOLEAN FOR empType BEGIN IF SELF.position=’Boss’ THEN RETURN True; ELSE RETURN False; ENDIF END;

A type has the type of all its supertypes and, thus, the type system corresponds closely to classes in OOP languages.

DD2471 (Lecture 08) Modern database systems & their applications Spring 2012 12 / 44

SQL3:2008 – sub- and supertypes . . .

A special restriction requires all types to have exactly one most specific type which makes it impossible to have multiple inheritance unless the types share a common supertype Sometimes it takes a rather strained approach to organizing types to make it work. Not so OOP You may have to create a number of “glue” types. You may have to create a mix of table inheritance and OO-inheritance. The privileges to create types and subtypes are standard database privileges.

DD2471 (Lecture 08) Modern database systems & their applications Spring 2012 13 / 44

SQL3:2008 – tables

To guarantee backwards compatibility with earlier SQL standards you must still use ’CREATE TABLE’ even if the table consists of just a UDT. Tables are still the only means for persistence Due to a complex syntax and a likewise complex semantics the variations are infinite, e.g

CREATE TABLE employee ( info empType, PRIMARY KEY (empNo));

• r

CREATE TABLE employee OF empType ( REF IS empID SYSTEM GENERATED, PRIMARY KEY (empNo));

DD2471 (Lecture 08) Modern database systems & their applications Spring 2012 14 / 44

SQL3:2008 – tables with references

CREATE TABLE order (

• rderNo orderNumber,

item itemNo NOT NULL, quantity INTEGER NOT NULL, unit VARCHAR(5) NOT NULL, client SocialSecurityNumber NOT NULL, representative REF(empType) SCOPE employee REFERENCES ARE CHECKED ON DELETE CASCADE, PRIMARY KEY (orderNo));

DD2471 (Lecture 08) Modern database systems & their applications Spring 2012 15 / 44

SQL3:2008 – problems with table references

If we create a table ’client’ as

CREATE TABLE client ( info PersonType, deductionrate SMALLINT, latestPurchase DATE, PRIMARY KEY (ssn));

we will spread information about persons over two tables (’empType’ is a subtype to ’PersonType’). If we use the method regularly then we are in troubles. . . It might be better to create the table differently:

CREATE TABLE client UNDER person( deductionrate SMALLINT, latestPurchase DATE);

DD2471 (Lecture 08) Modern database systems & their applications Spring 2012 16 / 44

SQL3:2008 – problems with table references . . .

Then the part of client that belongs to the person type will be stored in the person table with the extra feature that if information about a client is deleted from the client table then the corresponding info will automatically be erased from the person table. Rules are quite intuitive:

• An insert is automatically distributed over tables and “supertables”.
• An update is automatically propagated to all involved rows in all involved

tables.

• A delete is also automatically proagated to all involved tables.

DD2471 (Lecture 08) Modern database systems & their applications Spring 2012 17 / 44

SQL3:2008 – collection types, ’ARRAY’

So far (and it seems to stop at that), two collection types have been implemented, ’ARRAY’ and ’MULTISET’.

ARRAY works approximately as in programming languages:

Suppose that we add

relative PersonType ARRAY

to the ’employee’-table. Then we can find the ssn of “closest” and “most distant” realtive by

SELECT relative[1].ssn as closestRelSsn, relative[CARDINALITY(relative)].ssn as mostDistantRelSsn FROM employee e WHERE e.ssn=’451112-0356’;

All collection types have the ’CARDINALITY’ method that returns the actual number

• f elements in a collection.

OBS that prior to SQL3 you had to indicate the max number of elements that your collection could contain.

DD2471 (Lecture 08) Modern database systems & their applications Spring 2012 18 / 44

SQL3:2008 – collection types, ’MULTISET’

You may use relative MULTISET(PersonType) instead of ’ARRAY’ and ask for information about the relatives by

SELECT n.ssn, n.firstname FROM employee e, UNNEST (e.relative) AS n(ssn,firstname, lastname) WHERE e.ssn = ’451112-0356’;

DD2471 (Lecture 08) Modern database systems & their applications Spring 2012 19 / 44

SQL3:2008 – collection types, ’MULTISET’ . . .

It would be possible to represent the nested relation from lecture 1 Document Title Author Date Search-words Lang code Lang DBTheory Lindqvist 940322 database 46 swedish Dahl relation normalform ODBMS Johnson 940312 persistent english Peterson transient with (according to MS):

CREATE TABLE document ( title VARCHAR(50), author VARCHAR(50) MULTISET, date DATE, search VARCHAR(50) MULTISET, language ROW (code integer, language VARCHAR(20)) );

DD2471 (Lecture 08) Modern database systems & their applications Spring 2012 20 / 44

SQL3:2008 – Persistent Stored Modules (PSM)

PSM is an addition to SQL3 to ensure computational completeness instead of, as in SQL(1) and SQL2, merely relational completeness. Some of these additions:

• Declarations, e.g.:

DECLARE b BOOLEAN; DECLARE a empType; b = a.isBoss();

• IF ... THEN ... ELSE ... END IF
• CASE lowercase(x)

WHEN ’a’ THEN SET a = 1; WHEN ’b’ THEN SET a = 2; SET b = 1; WHEN ’default THEN set b = 2; END CASE;

DD2471 (Lecture 08) Modern database systems & their applications Spring 2012 21 / 44

SQL3:2008 – Persistent Stored Modules (PSM) . . .

• Repetition statements, where blocks of SQL statements can be executed and you

may loop over the result tables

• FOR x,y AS SELECT a,b FROM tab1 WHERE cond DO

... END FOR;

• WHILE NOT b DO

... END WHILE;

• REPEAT

... UNTIL NOT b END REPEAT;

• A ’CALL’ statement to execute procedures and a ’RETURN’ statement which allows

returning the result of executing an SQL statement from a function invocation.

DD2471 (Lecture 08) Modern database systems & their applications Spring 2012 22 / 44

SQL3:2008 – exceptions

SQL3 includes exception management. It is verbose . . . . You declare an exception and describe the action to take if an error occurs. Then you can choose what to do after the action has been executed. You can choose to be satisfied and do nothing more or send the signal on to enclosing environments.

DECLARE { CONTINUE | EXIT | UNDO } HANDLER FOR SQLSTATE { sqlStatus | conditionName | SQLEXCEPTION | SQLWARNING | NOT FOUND } action; DECLARE cond CONDITION [ FOR SQLSTATE sqlStatus ]

An error (exception) signal may be explicitly sent or resent.

SIGNAL sqlStatus; RESIGNAL sqlStatus;

DD2471 (Lecture 08) Modern database systems & their applications Spring 2012 23 / 44

SQL3:2008 – triggers

A trigger is a (compound) SQL statement that the DBMS execute automatically as a side effect to some event. They are set to execute prior to or after letting the event have is effect on the data and are used to

• check input data
• enforce complex integrity requirements
• message-sending
• uphold transaction logs
• replication in distributed DBMS

CREATE TRIGGER triggername BEFORE | AFTER event ON table [ REFERENCING aliaslist ] [ FOR EACH { ROW | STATEMENT } ] [ WHEN triggercondition ] triggercode

DD2471 (Lecture 08) Modern database systems & their applications Spring 2012 24 / 44

SQL3:2008 – triggers, example

CREATE TRIGGER setnull-trigger BEFORE UPDATE ON employee REFERENCING NEW ROW AS newrow FOR EACH ROW WHEN newrow.phone = ’’ SET newrow.phone = NULL CREATE TRIGGER check-department BEFORE INSERT ON employee REFERENCING NEW ROW AS newrow WHEN newrow.dept NOT IN (SELECT name FROM department) SIGNAL avd-not-found(newrow.dept)

DD2471 (Lecture 08) Modern database systems & their applications Spring 2012 25 / 44

SQL3:2008 – triggers, example . . .

CREATE TRIGGER update-supply AFTER INSERT ON order REFERENCING NEW ROW AS newrow FOR EACH ROW UPDATE supply SET volume = volume - newrow.quantity WHERE item = newrow.item

DD2471 (Lecture 08) Modern database systems & their applications Spring 2012 26 / 44

ORDBMS – some systems

Three of the major DBMS providers – IBM, Informix and Oracle – have extended their RDBMS to ORDBMS or universal servers. They have – each with their own technique – extended their DBMS to handle pluggable modules that extends both its data storage possibilities, its type system, its optimizer and its functionality. Let us look att each of these three.

DD2471 (Lecture 08) Modern database systems & their applications Spring 2012 27 / 44

ORDBMS, – DB2 Relational Extenders

The first IBM attempt to implement SQL3 was in DB2 version 5, where UDTs, UDFs, LOBs, triggers and stored procedures were introduced, some of them with IBM specific notation. In version 6 they introduced abstract data types and extendibility as described on the pervious slide. With the system you get four Relational extenders that can be used to create new extenders.

DD2471 (Lecture 08) Modern database systems & their applications Spring 2012 28 / 44

ORDBMS, – DB2 Relational Extenders . . .

Using the attached relstonal extenders, UDTs, UDFs, et.c. the DBA can create new extenders that cover any data storage need. Third party relational extender developers use exactly the same tools. All collected knowledge about RDBMS is still relevant. Nowadays many third party relational extenders are available. They are simple to install and activate. Once plugged in and activated they act as a fully integral part of the DBMS

DD2471 (Lecture 08) Modern database systems & their applications Spring 2012 29 / 44

ORDBMS, – DB2 Relational Extenders . . .

DD2471 (Lecture 08) Modern database systems & their applications Spring 2012 30 / 44

ORDBMS, – Informix DataBlades

Similarily an Informix DataBlade is a standard software module that can be plugged in to their DBMS.A datablade contains

• one ADT,
• any number of ADF

,

• access methodes,
• indexing methods,
• optimizing methods,
• functions to manipulate the type of data that is defined in the datablade,
• rule definitions and
• restrictions that, once defined, allows the DBMS to cheack them the same way as any

built-in restrictions

DD2471 (Lecture 08) Modern database systems & their applications Spring 2012 31 / 44

ORDBMS, – Informix DataBlades . . .

In Informix Dynamic Server (earlier universal server) all values, including those that are generated from an ADT are first class valuesand may be

• stored,
• inspected (you may query all parts),
• indexed,
• sent to application programs,
• sent as function parameters

et.c.

and they may be defined in terms of already existing ADTs and/or predefined types.

DD2471 (Lecture 08) Modern database systems & their applications Spring 2012 32 / 44

ORDBMS, – Informix DataBlades . . .

There are three basic ADTs in datablades:

• row-types
• distinct types
• “opaque types” – that may be implemented in C, C++ or Java. With “opaque”

is meant that you cannot “look into” them. They are not part of the inspectable values.

DD2471 (Lecture 08) Modern database systems & their applications Spring 2012 33 / 44

ORDBMS, – Informix DataBlades . . .

A UDF can be programmed in Informix Stored Procedure Language (SPL), C, C++, or Java. Those that are programemd in C, C++, or Java are compiled and stored in a shared object file or a DDL and when the function is invoked, the object code is linked into the running program and executed in the server runtime memory. You may store your own runtime modules for each language in the DBMS in order to ensure that you use the appropriate runtime for each used language (the runtime against which compilation was performed)

DD2471 (Lecture 08) Modern database systems & their applications Spring 2012 34 / 44

ORDBMS, – Informix DataBlades . . .

You may implement your set of access methods, which is essentially a set of functions to

• start index scanning
• get the next tuple
• insert a tuple
• delete a tuple

This, turn means that you may design and install specialized indexes like R-trees to manage spatial data types.

DD2471 (Lecture 08) Modern database systems & their applications Spring 2012 35 / 44

ORDBMS, – Informix DataBlades . . .

There is even an API that allows databalades to use other datablades. Datablades may be stored in the database (of course) and they may manipulae their own definition (reflection ??). This makes them data driven and simple to change, manage and . . . extend. There is a development kit for datablade developers.

DD2471 (Lecture 08) Modern database systems & their applications Spring 2012 36 / 44

ORDBMS, – Informix DataBlades . . .

DD2471 (Lecture 08) Modern database systems & their applications Spring 2012 37 / 44

ORDBMS, – Oracle Cartridges

Oracle Cartridge is a key component in Oracle Network Computing Architecture (NCA). The other components are

• client applications,
• Oracle Universal Application Server and
• Oracle Universal Server.

All communicate using open standard protocols internally and externally

DD2471 (Lecture 08) Modern database systems & their applications Spring 2012 38 / 44

ORDBMS, – Oracle Cartridges . . .

From a DB view Oracle Universal Server that provides extedible, scalable data storage facilities. A cartridge is a pluggable module that may be manipulated by other software Its internal structure is defined in standard SQL3 and its interface uses the IMG IDL (as CORBA does) You may use C, C++, PL/SQL or Java when developing cartridges. In the NCA architecture there are three kinds of cartridges (the classification shows their expected location in the NCA architecture)

DD2471 (Lecture 08) Modern database systems & their applications Spring 2012 39 / 44

ORDBMS, – Oracle Cartridges . . .

• A data cartridge is managed in the DBMS and can access Oracles extensible

database services.

• An application server cartridge may be managed either by the application

server or in the database server depending on hos tightly application logics are tied to data stored in DB. In the last version of Oracle that I looked into they were stored in the application server and communicated with DBMS via CORBA/IIOP .

• A client cartrig runs on the client side using the usual standard API:s.

DD2471 (Lecture 08) Modern database systems & their applications Spring 2012 40 / 44

ORDBMS, – Oracle Cartridges . . .

Cartridge-objects communicate via a special internal bus, the Inter-Cartridge Exchange (ICX). The ICX is implemented as a set of libraries and services that enable all the internal programs, in a set of programs that together form an application, to quite freely communicate with each others and surrounding servers and services. The ICX uses HTTP and IIOP for communication and performs automatically certain conversions if necessary. The ICX has an API to manage contact with ActiveX, Java (using CORBA/IDL – IIOP – JDBC), and a large number of programming languages and application programs on the market.

DD2471 (Lecture 08) Modern database systems & their applications Spring 2012 41 / 44

ORDBMS, – Oracle Cartridges . . .

DD2471 (Lecture 08) Modern database systems & their applications Spring 2012 42 / 44

Finally

The brains behind most of the ideas in the ORDBMS world is the team, headed by Michael Stonebraker, that created Ingres, Postgres, Illustra and, finally, the Informix Dynamic Server. The three big dragons – IBM, Informix and Oracle – have seen success but in site

• f all the similarities there are differences

DB2 (supposedly) builds on conformance with the SQL3 standard, Oracle on the use of open standards for communication, mainly IDL and CORBA while Informix has focused on the hardships with adding non-relational stuff in a relational system and the difficulty to mold them into the system and therefore delivers powerful tools for developing datablades and to install them into the system (BladeSmith, Bladepack och Blademanager)

DD2471 (Lecture 08) Modern database systems & their applications Spring 2012 43 / 44

Some other systems

AMOS II (Uppsala University Database laboratory) Cach´ e (InterSystems) Data Access FrameWork / Total (CinCom) Enterprise Application Server (Sybase) KE Texpress (Knowledge Engineering) JBMS (Cloudscape) Lincks (Link¨

• ping University)

Matisse (Matisse) Microsoft Repository (Microsoft) ODB-II (Fujitsu) OSMOS (Unisys) Phasme DBMS (Frederic Andres) Polyhedra (Polyhedra) PostgreSQL (UCB) UniSQL (Cincom)

DD2471 (Lecture 08) Modern database systems & their applications Spring 2012 44 / 44