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 on 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 operator 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)); 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