Updating Issues in SBQL

streakconvertingΛογισμικό & κατασκευή λογ/κού

13 Δεκ 2013 (πριν από 3 χρόνια και 10 μήνες)

209 εμφανίσεις

K.Subieta.
Updating Issues in SBQL
, slide
1

June

200
7


Updating Issues in SBQL

Presentation prepared for OMG Object Database Technology Working Group


OMG TECHNICAL MEETING, Brussels, Belgium

June 25th
-
29th,


2007


by


Prof
.
Kazimierz Subieta



Polish
-
Japanese Institute of Information Technology, Warsaw, Poland

subieta@pjwstk.edu.pl

http://www.ipipan.waw.pl/~subieta

SBA/SBQL pages:

http://www.sbql.pl


K.Subieta.
Updating Issues in SBQL
, slide
2

June

200
7

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


K.Subieta.
Updating Issues in SBQL
, slide
3

June

200
7

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.

K.Subieta.
Updating Issues in SBQL
, slide
4

June

200
7

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

-

n
o conceptual difference
:


2+2


(
x
+
y
)*
z


Employee

where

s
alary

= 1000


(
Employee

where

s
alary

= (
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).

K.Subieta.
Updating Issues in SBQL
, slide
5

June

200
7

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
)





S
ome 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
.

K.Subieta.
Updating Issues in SBQL
, slide
6

June

200
7

Total internal identification


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

K.Subieta.
Updating Issues in SBQL
, slide
7

June

200
7

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.

K.Subieta.
Updating Issues in SBQL
, slide
8

June

200
7

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) version
s
.


They store and process type signatures.

K.Subieta.
Updating Issues in SBQL
, slide
9

June

200
7

SBQL schema (ODRA)

K.Subieta.
Updating Issues in SBQL
, slide
10

June

200
7

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”.

K.Subieta.
Updating Issues in SBQL
, slide
11

June

200
7

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

K.Subieta.
Updating Issues in SBQL
, slide
12

June

200
7

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


);

K.Subieta.
Updating Issues in SBQL
, slide
13

June

200
7

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?


K.Subieta.
Updating Issues in SBQL
, slide
14

June

200
7

Assignments in SBQL


l
query

:=
r
query
;


We do not allow macroscopic assignments:


l
query

must return a single reference


r
query

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”;


};

K.Subieta.
Updating Issues in SBQL
, slide
15

June

200
7

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


);

K.Subieta.
Updating Issues in SBQL
, slide
16

June

200
7

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.

K.Subieta.
Updating Issues in SBQL
, slide
17

June

200
7

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”));

K.Subieta.
Updating Issues in SBQL
, slide
18

June

200
7

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
;

K.Subieta.
Updating Issues in SBQL
, slide
19

June

200
7

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
(
i
stmnt
;

cquery
;

inc
stmnt

)
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
}

K.Subieta.
Updating Issues in SBQL
, slide
20

June

200
7

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;

K.Subieta.
Updating Issues in SBQL
, slide
21

June

200
7

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.

K.Subieta.
Updating Issues in SBQL
, slide
22

June

200
7

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.

K.Subieta.
Updating Issues in SBQL
, slide
23

June

200
7

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” ) = 0

then

printString
(”No effect”);

K.Subieta.
Updating Issues in SBQL
, slide
24

June

200
7

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
.

K.Subieta.
Updating Issues in SBQL
, slide
25

June

200
7

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.

K.Subieta.
Updating Issues in SBQL
, slide
26

June

200
7

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.

K.Subieta.
Updating Issues in SBQL
, slide
27

June

200
7

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”;

K.Subieta.
Updating Issues in SBQL
, slide
28

June

200
7

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.

K.Subieta.
Updating Issues in SBQL
, slide
29

June

200
7

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.

K.Subieta.
Updating Issues in SBQL
, slide
30

June

200
7

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)