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. DD2471 (Lecture 08) Modern database systems & their applications Spring 2012 6 / 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. DD2471 (Lecture 08) Modern database systems & their applications Spring 2012 6 / 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; DD2471 (Lecture 08) Modern database systems & their applications Spring 2012 6 / 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). DD2471 (Lecture 08) Modern database systems & their applications Spring 2012 7 / 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 DD2471 (Lecture 08) Modern database systems & their applications Spring 2012 8 / 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 DD2471 (Lecture 08) Modern database systems & their applications Spring 2012 8 / 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) DD2471 (Lecture 08) Modern database systems & their applications Spring 2012 8 / 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; DD2471 (Lecture 08) Modern database systems & their applications Spring 2012 9 / 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. DD2471 (Lecture 08) Modern database systems & their applications Spring 2012 9 / 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) DD2471 (Lecture 08) Modern database systems & their applications Spring 2012 9 / 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) DD2471 (Lecture 08) Modern database systems & their applications Spring 2012 9 / 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 DD2471 (Lecture 08) Modern database systems & their applications Spring 2012 10 / 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, DD2471 (Lecture 08) Modern database systems & their applications Spring 2012 10 / 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, DD2471 (Lecture 08) Modern database systems & their applications Spring 2012 10 / 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; DD2471 (Lecture 08) Modern database systems & their applications Spring 2012 12 / 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 DD2471 (Lecture 08) Modern database systems & their applications Spring 2012 13 / 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 DD2471 (Lecture 08) Modern database systems & their applications Spring 2012 13 / 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. DD2471 (Lecture 08) Modern database systems & their applications Spring 2012 13 / 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. DD2471 (Lecture 08) Modern database systems & their applications Spring 2012 13 / 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. DD2471 (Lecture 08) Modern database systems & their applications Spring 2012 14 / 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 DD2471 (Lecture 08) Modern database systems & their applications Spring 2012 14 / 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 DD2471 (Lecture 08) Modern database systems & their applications Spring 2012 14 / 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)); DD2471 (Lecture 08) Modern database systems & their applications Spring 2012 14 / 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)); or 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 ( orderNo 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)); DD2471 (Lecture 08) Modern database systems & their applications Spring 2012 16 / 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. . . DD2471 (Lecture 08) Modern database systems & their applications Spring 2012 16 / 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. DD2471 (Lecture 08) Modern database systems & their applications Spring 2012 17 / 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”. DD2471 (Lecture 08) Modern database systems & their applications Spring 2012 17 / 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. DD2471 (Lecture 08) Modern database systems & their applications Spring 2012 17 / 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 ’. DD2471 (Lecture 08) Modern database systems & their applications Spring 2012 18 / 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. DD2471 (Lecture 08) Modern database systems & their applications Spring 2012 18 / 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’; DD2471 (Lecture 08) Modern database systems & their applications Spring 2012 18 / 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 of 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 0 english Peterson transient DD2471 (Lecture 08) Modern database systems & their applications Spring 2012 20 / 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 0 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. DD2471 (Lecture 08) Modern database systems & their applications Spring 2012 21 / 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(); DD2471 (Lecture 08) Modern database systems & their applications Spring 2012 21 / 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 DD2471 (Lecture 08) Modern database systems & their applications Spring 2012 21 / 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 DD2471 (Lecture 08) Modern database systems & their applications Spring 2012 22 / 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; DD2471 (Lecture 08) Modern database systems & their applications Spring 2012 22 / 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; DD2471 (Lecture 08) Modern database systems & their applications Spring 2012 22 / 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; DD2471 (Lecture 08) Modern database systems & their applications Spring 2012 22 / 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. DD2471 (Lecture 08) Modern database systems & their applications Spring 2012 23 / 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; DD2471 (Lecture 08) Modern database systems & their applications Spring 2012 23 / 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 ] DD2471 (Lecture 08) Modern database systems & their applications Spring 2012 23 / 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. DD2471 (Lecture 08) Modern database systems & their applications Spring 2012 24 / 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 DD2471 (Lecture 08) Modern database systems & their applications Spring 2012 24 / 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 DD2471 (Lecture 08) Modern database systems & their applications Spring 2012 24 / 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 DD2471 (Lecture 08) Modern database systems & their applications Spring 2012 24 / 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 DD2471 (Lecture 08) Modern database systems & their applications Spring 2012 24 / 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 DD2471 (Lecture 08) Modern database systems & their applications Spring 2012 24 / 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 DD2471 (Lecture 08) Modern database systems & their applications Spring 2012 25 / 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. DD2471 (Lecture 08) Modern database systems & their applications Spring 2012 27 / 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. DD2471 (Lecture 08) Modern database systems & their applications Spring 2012 28 / 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. DD2471 (Lecture 08) Modern database systems & their applications Spring 2012 29 / 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. DD2471 (Lecture 08) Modern database systems & their applications Spring 2012 29 / 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. DD2471 (Lecture 08) Modern database systems & their applications Spring 2012 29 / 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. DD2471 (Lecture 08) Modern database systems & their applications Spring 2012 29 / 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
Recommend
More recommend
