k.subieta. updating issues in sbql, slide 1 june 2007 updating issues in sbql presentation prepared...

K.Subieta. Updating Issues in SBQL, slide 1 June 2007

Updating Issues in SBQL

Presentation prepared for OMG Object Database Technology Working Group

OMG TECHNICAL MEETING, Brussels, BelgiumJune 25th-29th, 2007

by

Prof. Kazimierz Subieta

Polish-Japanese Institute of Information Technology, Warsaw, Poland [email protected]://www.ipipan.waw.pl/~subietaSBA/SBQL pages: http://www.sbql.pl


Topics

• Approaches to updates via a query language

• SBQL as integrated QL/PL

• Updating in integrated QL/PL – issues

• SBQL imperative statements

• SBQL program control statements

• SBQL procedures and methods

• SBQL virtual updateable views

• SBQL transactions


Approaches to updates via a query language

• No approach: updates via methods in some host programming language - ODMG OQL

• Bottom-up: a side-effect-free query language is extended with updating capabilities:

– SQL, PL/SQL, T-SQL, XQuery, Hibernate,…

– To some extent: SQL-99, SQL-2003 (extensions of SQL-92)

– In some cases (PL/SQL, T-SQL, SQL-99, SQL-2003) the approach leads to a new programming language.

• Top-down: Integrated query and programming language.

– Several DataBase Programming Languages (DBPL), e.g. SBQL;

– No definite border line between querying and programming

– A unified and universal conceptual and semantic frame for queries and programs involving queries, including procedures, functions, classes, types, methods, views, etc.


Integrated QL/PL – decisions (1)

• The SBA/SBQL solution relies on adopting a run-time mechanism of PLs and introducing necessary improvements.

• The main syntactic decision is the unification of PL expressions and queries - no conceptual difference:– 2+2

– (x+y)*z

– Employee where salary = 1000

– (Employee where salary = (x+y)*z).surname

• All such expressions/queries can be used as:– Arguments of imperative statements (update, insert, delete, …)

– Actual parameters of procedures, functions or methods

– Return from a functional procedure (method).


Integrated QL/PL – decisions (2)

• Queries should be prepared to return references to objects– No more value-oriented approach – no algebras, calculi, formal logic,…

• References returned by queries can be used as:– Left sides of assignments– Arguments of delete statements– Out and inout parameters (call-by-reference, strict-call-by-value)– …

• Some imperative statements are to be designed as macroscopic.• Orthogonal persistence: a unified typing system for queries addressing

persistent (shared) and volatile (non-shared) entities– No difference in access to persistent and volatile data

• Total internal identification: to return references each database or program entity, which could be separately retrieved, updated, inserted, deleted, etc., should possess a unique internal identifier.


Total internal identification

• SBQL object (ODRA): each object, attribute, sub-attribute, pointer, etc. has a unique internal identifier.


Updating in integrated QL/PL – issues

• Imperative constructs based on queries (macroscopic): variable declarations, assignments, create, insert, delete, etc.

• Program control statements based on queries: if, loops, for each, etc.• Procedures and functions based on query constructs

– New issues for strong typing– Parameter passing methods based on queries (in and out parameters) – Local procedure/function objects (based on a unified typing system)– Functions with macroscopic output (SQL-like views)– Classes and methods

• Transactions and transaction processing – for updating shared resources• Virtual updateable O-O views (consistent updating of virtual objects)• Events and triggers • Interoperability issues, in particular:

– Updating relational databases via object-oriented queries– Gateways to/from OO PLs, e.g. Java.


Integrated QL/PL - naming, scoping, binding

• Integrated QL/PL requires careful designing of naming, scoping and binding mechanisms

• The common PLs’ approach is that scopes are organized in an environment stack with the “search from the top” rule.

– Some extensions to the structure of stacks used in PLs are necessary.

• Query operators, imperative programming constructs and procedures (functions, methods, views, etc.) are defined in terms of the three internal data structures:

– Environment stack (for scoping and binding names)

– Query result stack (for storing temporary and final query results)

– Object store (for storing all persistent and volatile data entities)

• For strong typing and query optimizations the stacks must also exist in static (compile time) versions.

– They store and process type signatures.


SBQL schema (ODRA)


Variable (object) declaration

• In ODRA any variable (object) must be declared. – The declaration must be visible to the environment against which a

given query is executed.

• The variable declaration has the following syntax:

name: type [cardinality]– For instance:

x: integer;

Emp: EmpType [0..*];

type PersonType is record { name: string; age: integer; };

georg: PersonType;

Variables can be declared as persistent (shared, on a server), temporal (session’s), local (to a procedure, function or method).

• The concept of cardinality (as in UML) instead of „collections”.


Object creation• Objects are created by the create operator.

– It is checked according to types and cardinality. • Syntax: create [where] name(query);• Semantics:

– The operator is macroscopic (one statement can create many objects)– Parameterized by a place indicator (where) - permanent, temporal, local– Can be used to create each kind of objects (simple, complex, pointer).– Requires appropriate variable declaration.

• Simple object creation create amount(2500);– The persistency status depends on the context; create possibleMeetingDate(2007-06-04 union 2007-09-12); create local fullName( (Emp where worksIn.Dept.name = “adv”).(fName + “ “ + lName));

• Pointer creation: create permanent highPayed( ref (Emp where sal > 3000) );


Complex object creation

• To create a complex object the query must return structures with named fields or a reference to a complex object. – The reference will be automatically dereferenced;– ref for creating pointers;– If the argument query returns a bag, many objects are created.

• Create a shared complex Emp object

create permanent Emp( “Tom” as fName, “Jones” as lName, 2500 as sal, ref (Dept where dName = “adv”) as worksIn, ( ref (Dept where dName = “pr”) union ref (Dept where dName = “retail”) ) groupas prevJobPlace );


Assignments and queries

• In PLs, the assignment operator has the (possible) syntax :

lvalue := rvalue;– lvalue and rvalue are expressions

– lvalue must return a reference to an entity (e.g. to a variable)

– rvalue returns a new value assigned to the entity (deref is enforced)

– Types of lvalue and rvalue must coincide.

• Can both lvalue and rvalue be queries (returning collections)?

• Can lvalue return a reference to a complex object and what in such a case rvalue shoud return (the substitutability problem)? – How to define the concept of „complex value”?

• Can lvalue be a reference to a pointer what in such a case rvalue should return?


Assignments in SBQL

lquery := rquery;

• We do not allow macroscopic assignments:

– lquery must return a single reference

– rquery must return a single value (with automatic deref enforced).

– Other solutions are inconsistent

• Instead, we allow to nest assignments into control statements:

foreach Emp where job = ”programmer” do { sal := sal +100; job := ”engineer”; };

– The solution is like SQL update, but SBQL is more orthogonal:

foreach (avg(Emp.sal) as a join (Emp where sal < a) as e) do { e.sal := a + 100; e.job := ”programmer”; };


Assignments to complex objects• SBQL supports this feature.

• It requires the definition of „complex value” that can be calculated at the right side of the assignment.

• We have defined it through the concept of binder, i.e. an entity n(x), where n is a name, x is any (perhaps complex) value.

– Operators as and groupas – creating binders

– Operator ref – prevents dereferencing

(Emp where lName = „Jnes”) := ( “Tom” as fName, “Jones” as lName, 2500 as sal, ref (Dept where dName = “adv”) as worksIn, ( ref (Dept where dName = “pr”) union ref (Dept where dName = “retail”) ) groupas prevJobPlaces );


Assignments to pointers (links)

• Requires a reference to an object as the right hand operand.

(Emp where eNbr = 4419).worksIn := ref (Dept where dName = “adv”);

– ref can be omitted due to type inference.

• Assignments to binary links:

– Any binary (two-way) link is considered as twin pointers semantically constrained; e.g. worksIn and employs.

– Assignment to one of them triggers a corresponding operation on its twin.

– See the ODMG standard, C++ binding.


Insertion

lQuery :< rQuery; lQuery :<< rQuery;

• Inserts an object into another object.

– The result of lQuery is a reference to a complex object.

– The result of rQuery is a bag of references to objects being inserted.

– Insertion is type and cardinality checked.

• Insert new prevJobPlace pointer object into Doe’s object:

(Emp where lName=“Doe”):< create prevJobPlace( ref(Dept where dName=“pr”));

• A variant of insertion – create and insert operator (Emp where lName=“Doe”):<< prevJobPlace( ref(Dept where dName=“pr”));


Deletion

delete query;

• Removes objects from the store.

– The operator is macroscopic.

– Concerns all kinds of run-time program or database entities.

– The result of operand query have to be a reference or a bag of references.

– Can be used to delete each kind of objects (simple, complex, pointer).

– Type checking concerns the cardinality after deletion.

• Delete location London from the Marketing department.

delete (Dept where dName = ”Marketing”). (loc as x where x = ”London”).x;


Program control statements

• We implemented typical statements known from many PLs:

if query then statement1 else statement2

if query then statement

while query do statement

do statement while (query)

for( istmnt; cquery; incstmnt ) do statement

– query and cquery must return a boolean value.

• Example:

if count( Emp where hireyear = 2006) - count( Emp where hireyear = 2005) > 100 then report :<< note(“employment increase achieved”);else report :<< note(“employment increase not achieved”);

• statement ::= {statement_list}


For Each statement

foreach query do statement• Iterates through elements of a collection determined by a query.

– query is evaluated first, it should return a bag. – For each bag element r its internal environment nested(r) is

calculated and pushed at the top of the environment stack. – After statement execution the environment nested(r) is destroyed.

• Example: Increase by 100 the salary of employees having salary below the average.– Without “iteration variable”:

foreach Emp where sal < avg(Emp.sal) do sal := sal + 100;– With “iteration variable”:

foreach (Emp where sal < avg(Emp.sal)) as e do e.sal := e.sal + 100;


SBQL procedures and methods

• Procedures are special complex objects.– Inside modules - treated as global procedures.– Inside classes – treated as methods and called in the instance context.– Inside views – treated as local to views.

• Encapsulate arbitrary complex computation.– Local objects and actual parameters are invisible from outside.

• Can be parameterized by parameters and/or by the state. • Little distinction between procedures and functional procedures.

– A functional procedure call is a query, but with possible side effects. • The result of a functional procedure is typed, similarly to other PLs. • Syntax of procedure declaration:

name([parameter_list]):[returntype] {statement_list}– Return can be determined by any query, according to returntype.

• Typical stack-based semantics– Any recursive calls are supported, with no special declaration.


Parameters of procedures and methods

• The parameter passing technique implemented in ODRA is known as strict-call-by-value:– Actual parameter determined by a query is evaluated before the

procedure execution.– The result is stored at the procedure activation record as a binder

(named value). – The method combines call-by-value and call-by-reference in a very

general fashion.– It allows the programmer to pass as a parameter the result of any

complex query that combines atomic values, references, auxiliary names, structures, bags, sequences, etc.

• Parameter declaration syntax: name: type [ cardinality ]– If the cardinality is not specified the default [1..1] is assumed.


SBQL: example of a procedure

• Procedure ChangeDept moves the specified employees to the specified department; returns the number of the moved employees.

• Let Kim become the boss of all designers working so far for Lee:

procedure ChangeDept( E: EmpType[0..*]; D: DeptType ): integer { delete ( Dept . employs ) where Emp in E; for each E as e do { D :<< employs( ref e ); e . worksIn := ref D }; return count(E);};

if ChangeDept( Emp where job = “designer” and (worksIn.Dept.boss.Emp.lName) = “Lee”; Dept where (boss.Emp.lName) = “Kim” ) = 0then printString(”No effect”);


Updating via procedure return

• SBQL functions may return references, which can be then used in imperative statements:

– Procedure EmpSalBoss returns references to names of employees earning less than 2000, to their salaries and to their boss names:

procedure EmpSalBoss(): record{e: ref string; s: ref integer; b: ref string}[0..*] { return (Emp where sal < 2000). (lName as e, sal as s, (worksIn.Dept.boss.Emp.lName) as b); };

• Updating through the return is possible: for each (EmpSalBoss where e = ”Doe” and b = ”Lee”) do s := s + 100;

• Hence SBQL functions may work as updateable views.

– In general, such updates lead to inconsistency (view updating problem).

– We have developed and implemented special updateable views.


SBQL virtual updateable views

• SQL views have limitations that restrict their applications: – Limited power of a view definition facilities (far below the full

algorithmic power);– Limited data model (only relational tables);– Limited view updating (updating of virtual tables is prohibited or

severely restricted);– Performance can be compromised by the use of views.

• instead of trigger views of Oracle, SQL Server and DB2– Relax the third limitation.

• SBQL views:– No limit concerning the algorithmic power;– No limit concerning the datamodel;– No limit concerning the semantics of view updating;– Powerful optimization methods of queries involving views.


SBQL views - generalities

• Some applications may require updating of virtual data:

– Updates of virtual data are to be mapped into updates of stored data.

– Typically (SQL) these updates are made by side effects of view invocations.

• In SBQL we take another point of view.

– Our method is based on overloading generic updating operations (create, delete, update, insert, …) acting on virtual objects by invocation of procedures that are written by the view definer.

– The procedures have full algorithmic power.

– Full transparency of virtual objects: they cannot be distinguished from stored objects by any programming option.

– High-level view definition, full algorithmic power, any datamodel, view updating anomalies controlled by the programmer, the optimization potential.


View example

– Delivers virtual objects named EmpBoss, with attribute name (of an employee) and bossName (of his/her boss).

– Updating may concern bossName => a corresponding employee is moved to the department managed by the new boss.

– Move Doe to the department managed by Lee:

view EmpBossDef{ virtual objects EmpBoss: record{e:ref Emp;}[0..*]{ return Emp as e; }; view nameDef{ virtual objects name: record{en: string;}{ return e.lName as en;}}; on_retrieve: string { return en; } }; view bossNameDef{ virtual objects bossName: record{bn: string;}{ return e.worksIn.Dept.boss.Emp.lName as bn; };

on_retrieve: string { return bn; }; on_update(newBoss: string){ e.worksIn := ref (Dept where (boss.Emp.lName) = newBoss); }}}

(EmpBoss where name = ”Doe”).bossName := ”Lee”;


Transactions in SBQL

• Shared objects must obey the transactional semantics.• In SBQL we have introduced transactions in a different form in

comparison to ODMG (and other proposals).• The basic assumption is that each transaction must possess an identity

in a source code and during runtime.– Transactions are similar to procedures – they have a name, parameters

and local objects.– Syntactic difference concerns some keywords.– Semantic difference concerns ACID properties.– Nested transactions are possible.

• In runtime transactions are represented by objects of a special class – Managed by SBQL.– DBA has rights to get some privileges to access to these objects.– This is especially important in distributed database environments and

protocols such as 2PC.


Conclusions

• Any programming environment must support updates

– This concerns a new object-oriented database standard

• In SBA/SBQL we follow seamless integration of querying and programming capabilities

– Delegating updates to a host PL → impedance mismatch

– Ad hoc extending queries with updates → limitations and inconsistencies

• Unification of PL expressions and queries

– For updating, queries must return references

– Classical stack-based semantics w.r.t. queries

– Queries can be used as components of imperative statements, as parameters of procedures and as a return from a functional procedure

• Procedures, functions, types, classes, methods, updateable views, transactions, etc. are abstractions that can be based on queries

– They should be components of a new OO database standard.


Acknowledgement

• This work is supported by the European Commission 6-th Framework Programme, Project VIDE - VIsualize all moDel drivEn programming, IST 033606 STP

• VIDE Participant List (in random order)

– SAP AG (Germany)

– SOFTEAM (France)

– Institute for Information Systems at the German Research Center for Artificial Intelligence (Germany)

– IESE Fraunhofer (Germany)

– Polish-Japanese Institute for Information Technology (Poland, coordinator)

– FIRST Fraunhofer (Germany)

– TNM Software GmbH (Germany)

– Bournemouth University (United Kingdom)

– Rodan Systems S.A. (Poland)

– ALTEC (Greece)

k.subieta. updating issues in sbql, slide 1 june 2007 updating issues in sbql presentation prepared...

Documents