Chapter 3 - User-defined Routines and Object Behavior

Question 1

What is the general idea and the different kinds of User-defined Routines?

Answer 1

• Named persisted code to be invoked in SQL (PSM/external)
• Integrated in schema, CREATE/ALTER/DROP, privileges
• Procedure (CALL statement), function (expression), method (type function)

Question 2

What is the general structure of a routine definition in SQL?

Answer 2

• > Head: Name + list of parameters (IN/OUT/INOUT) + returns* type
• • procedures
• **functions/methods
• > Body: SQL (PSM); or host programming language

Question 3

What are the alternatives for providing implementation for routines?

Answer 3

SQL(PSM)**, external

** Procedural language extensions (statements)

Question 4

What are the differences between procedures and functions in SQL?

Answer 4

• > Procedure -> Statement context
  • OUT/INOUT parameters: modified by procedure and mode available to caller afterwords
  • Dynamic Result Sets: Declare and return cursors
• > Function -> Expression context
  • returns (mandatory) (no result sets)

Question 5

Which additional functionality is provided by table functions?

Answer 5

Transform non-relational data on relational table dynamically
Returns a table (multiset of rows)
-> RETURNS TABLE (name type, name type, …)
-> RETURNS TABLE (SELECT… FROM…)
Used in FROM clause
-> SELECT … FROM TABLE (f(a,b) AS

Question 6

Compare table functions and views as mechanisms for virtual tables?

Answer 6

TFs support dynamic parameters

Question 7

How are privileges applied to UDRs?

Answer 7

Execute privilege
Roles: (DEFINER**, INVOKER) when executing queries inside routine def
** With GRANT option
** Routine droped if lost

Question 8

What are implications of cross-schema overloading?

Answer 8

• Overloading routines with the same signature

* Need to define schema path for overload resolution

Question 9

What is the general idea and the algorithm for subject routine determination in SQL?

Answer 9

• > Decide which routine to invoke based on: Static types of args, type precedence list (subtype relation for STs) schema path, EXECUTE privilege
• > Algorithm: 1. set of candidates (name+ num of args) from all PATH if not fully qualified. 2. Eliminate unprivileged and incompatible arg types (not in prec. list). 3. Select best match from left to right. 4. Select first on PATH.

Question 10

How is the overloading of procedures supported in SQL?

Answer 10

Only based on param numbers.

Question 11

What are methods in SQL and how do they differ from functions?

Answer 11

-> SQL functions belonging to a structured type.
diffs:
-> Implicit SELF parameter (subject parameter)
-> Separate body and signature def (same schema as ST)
-> Invoked with dot notation: UDT-value.method()
CREATE TYPE T AS (…)
METHOD in RETURNS type;
CREATE METHOD m FOR T
BEGIN … END;

Question 12

What are the different types of methods supported in SQL?

Answer 12

-> Instance (original vs overriding), constructor, static

Question 13

How does SQL support overriding methods?

Answer 13

Signature must match, except for implict SELF, which is of subtype

Question 14

How does subject routine determination work for instance methods?

Answer 14

• > Just like for functions, but setting PATH to list of supertype schemas
• > Dynamic dispatch: decide between overriding methods based on dynamic type of SELF.
• Schema evolution affects actual invoked method

Question 15

How does method invocation work for typed tables and object references?

Answer 15

Typed tables: DEREF(oid).method( ) -> Transform ROW into value of ST
references: “->” notation -> Requires SELECT privilege on scoped table: table-specific INVOKE privileges

Question 16

What is the goal of observer and mutator methods?

Answer 16

Access and modification of attributes is made through them
-> Implicity associated to attributes
Mutators return modified copy of object

Question 17

Give an overview of how the modification of object state works in SQL.

Answer 17

• > Values of STs can’t be modified, just copied through mutators (no side-effects)
• > Rows of typed tables are modified with UPDATE statement

Question 18

How does attributes access work for SQL objects and assignments?

Answer 18

DOT notation: Att access = invoke observer
Systantic sugar for assignments.
SET obj.att = value -> obj.att(value)

Question 19

How does SQL support object encapsulation?

Answer 19

• > State is only accessible through methods (separate iface/impl.)
• > Public interface definition not supported
• > Implemented through privileges

Question 20

Give an overview over static methods in SQL?

Answer 20

No subject parameter (SELF), no overriding, static binding

Invocation ‘::’ “STATIC METHOD…”

Question 21

What are the different alternatives for constructing objects in SQL?

Answer 21

System defined (default values) + Mutator invocations
User-defined: "CONSTRUCTOR METHOD type RETURNS type"+ "CREATE ... FOR type" + NEW

Question 22

How does SQL deal with type substitutability of method return values?

Answer 22

• Return type must be the ST, method returns copy of self
• inherited methods return sypertype: static type error (for columns types e.g.)
• Type-preserving functions/methods: SELF AS RESULT: type of self argument is type of return value

Question 23

Give an overview over the additional routine characteristics.

Answer 23

• DETERMINISTIC vs NOT DETERMINISTIC
• RETURNS NULL ON NULL INPUT vs CALLED ON NULL INPUT
• CONTAINS SQL, READS SQL DATA, MODIFIES SQL DATA

Question 24

What is the goal of external routines and how are they supported in SQL?

Answer 24

Better performance, existing libraries

No body on CREATE: LANGUAGE + NAME (path)

Question 25

How does SQL deal with parameters of external routines?

Answer 25

• no names, type depends on host language, CAST FROM to convert from host types
• > parameter style
  • > SQL: additional pointer parameters: name, return value, error msg, SQLSTATE, null idicators…
  • > GENERAL: no additional params -> same signature, but with pointers (null not supp)

Question 26

Give an overview of the functionality provided by SQLJ part. 1

Answer 26

Implement SQL routines using java (LANGUAGE JAVA PARAMETER STYLE JAVA)

• > JAR file as a database object
• > SQL/JRT(2003)
• > arguments must be SQL data type

Question 27

What is the functionality provided by SQLJ part 2?

Answer 27

• > Define SQL types using java (Java class -> SQL UDT)
• > based on install_jar
• > mapping state + behavior
• > Automatic mapping to java object on fetch/method invocation (Java serializ., JDBS SQL data interface)

Question 28

How does SQLJ handle instance update methods?

Answer 28

• Identify method with SELF AS RESULT

* SQL system invokes java method on a copy, returning it with the modifications

Question 29

What are the different serialization mechanism of SQLJ?

Answer 29

(CREATE TYPE… LANGUAGE JAVA USING x)

• Serializable: persistent obj state defined by java serialization
  
  • > SQL attributes must correspond to java public fields
• SQLDATA: persistent state defined by SQL attributes
  
  • > readsql + write SQL methods (fields != attrs possible)
• Implement both interfaces and dont specify on CREATE type -> portability
  
  • > complete state in public fields