Constraints through SQL queries Xavier Oriol and Ernest Teniente xorioltenienteessiupcedu Motivation 1 creationT Date Group phone String state String lastConnect Date ID: 931442
Download Presentation The PPT/PDF document "Incremental Checking of OCL" is the property of its rightful owner. Permission is granted to download and print the materials on this web site for personal, non-commercial use only, and to display it on your personal computer provided you do not modify the materials and that you retain all copyright notices contained in the materials. By downloading content from our website, you accept the terms of this agreement.
Slide1
Incremental Checking of
OCL Constraints through SQL queries
Xavier Oriol and Ernest Teniente{xoriol,teniente}@essi.upc.edu
Slide2Motivation
1
creationT: DateGroup
phone: String
state: String
lastConnect
: Date
User
body: String
creationT
: Date
Message
Member
Owner
Sends
group
user
owned
owner
*
*
2..*
1
1
*
sender
msg
IsSentTo
1
*
msg
receiver
ConversationGroup
Pair
{disjoint, complete}
2
*
IsFormedBy
Slide3Motivation
2
ConversationGroup
Pair
creationT
: Date
Group
phone: String
state: String
lastConnect
: Date
User
body: String
creationT
: Date
Message
{disjoint, complete}
Member
Owner
Sends
IsSentTo
group
user
owned
owner
*
*
2..*
1
1
*
1
*
sender
msg
msg
receiver
2
*
IsFormedBy
c
ontext
User
inv
MemberOfOwned
:
self.
group
->
includesAll
(
self
.
owned
)
Slide4Motivation
3
ConversationGroup
Pair
creationT
: Date
Group
phone: String
state: String
lastConnect
: Date
User
body: String
creationT
: Date
Message
{disjoint, complete}
Member
Owner
Sends
IsSentTo
group
user
owned
owner
*
*
2..*
1
1
*
1
*
sender
msg
msg
receiver
2
*
IsFormedBy
c
ontext
User
inv
MemberOfOwned
:
self.
group
->
includesAll
(
self
.
owned
)
c
ontext
Group
inv
MessagesAfterCreation
:
self.
msg
->
forAll
(
e
|
e
.
creationT
>
self
.
creationT
)
Slide5Motivation
3
context User inv MemberOfOwned:self.
group->includesAll(
self
.
owned
)
c
ontext
Group
inv
MessagesAfterCreation
:
self.msg->forAll(e
| e.creationT >
self.creationT)
Slide6Motivation
4How
can we check OCL constraints?c
ontext User
inv
MemberOfOwned
:
self.
group
->
includesAll
(
self.
owned)
context Group
inv MessagesAfterCreation:self.msg
->forAll(e
| e.creationT
> self.creationT
)
Translating OCL constraints
to SQL queries
Idea: Query
for the
instances violating
the constraint
SELECT
*FROM Group
JOIN IsSentTo
JOIN Message
WHERE Group.createT
>= Message.creationT
Slide7Motivation
5There are some automatic OCL to SQL
translators:OCL2SQL from OCLDresden
MySQL4OCL
None of them is incremental
Slide8Motivation
6
ConversationGroup
Pair
creationT
: Date
Group
phone: String
state: String
lastConn
: Date
User
body: String
creationT
: Date
Message
{disjoint, complete}
Member
Owner
Sends
IsSentTo
group
user
owned
owner
*
*
2..*
1
1
*
1
*
sender
msg
msg
receiver
2
*
IsFormedBy
SELECT
*
FROM
Group
JOIN
IsSentTo
JOIN
Message
WHERE
Group.createT
<=
Message.creationT
SELECT
*
FROM
Owner
EXCEPT
Member
Hello
!
We
n
eed
to
compute
the
whole
queries
again
DATA
INSERT INTO
Messages
VALUES(
‘
Hello
!’
,
...
)
Slide9Our approach
Translate
OCL constraints into incremental SQL queries
Goal
7
Slide10Our approach
8Incremental
Only check the potentially
violated constraints
Only
check
for
the
new
updated
values
context
User inv MemberOfOwned:
self.group->includesAll
(self.owned
)
context Group
inv MessagesAfterCreation:
self.msg->
forAll(e|
e.creationT >
self.creationT
)
Hello
!
New
Message
Only
check
whether
the new
message “Hello
!” has been
created after
the receiver’s
group
creation
Slide11Our approach
Translate
OCL constraints into EDCs
and EDCs to incremental SQL
queries
Our
approach
7
Slide12Our approach
9Translate
OCL into EDCsAn EDC is a logic
rule that tells
when
a data
update
m
ay
cause
the
violation of
some integrity constraint
context User inv MemberOfOwned:
self.group->includesAll(self
.owned)
¬Owner(u, g)
∧ ins_Owner(u, g)
∧ ¬Member(u
, g) ∧ ¬
ins_Member(u,g)
→ ⏊¬
Owner(u, g) ∧ ins_Owner
(u, g) ∧ Member
(u, g) ∧
del_Member(u,g) →
⏊
Owner(u, g) ∧
¬del_Owner(u, g) ∧
Member(u, g) ∧
del_Member(u,g
) → ⏊
Slide13Our approach
10
Translate EDCs into SQLIdea: Join
the columns
of
the
bound
variables
ins_Owner
(u, g
) ∧ ¬
Owner
(u, g)
∧
Member(u, g) ∧ del_Member
(u,g) → ⏊
SELECT
*
FROM ins_Owner
ANTI JOIN Owner
ON (user
, group)
JOIN Member
ON (user,
group)
JOIN del_Member
ON (user,
group)
Slide14Our approach
10
SELECT *FROM ins_Owner ANTI JOIN Owner ON (
user, group
)
JOIN
Member
ON (
user
,
group
)
JOIN del_Member ON (user
, group)
Translate EDCs
into SQL
Slide15Our approach
11
SELECT *FROM ins_Owner ANTI JOIN Owner ON (
user, group
)
JOIN
Member
ON (
user
,
group
)
JOIN del_Member ON (user,
group)
The translation
never uses subqueries
DBMS query planners
can fully optimize
the query
Hello
!
New
Message
||
ins_Owner
|| = 0
Query
returns
empty
set
practically
in constant
time
c
ontext User
inv MemberOfOwned:
self.group->includesAll
(self
.owned)
Translate
EDCs into SQL
Slide16Our approach
12
SELECT *FROM Group ANTI JOIN del_Group ON (group
) JOIN ins_SendsTo
ON (
group
)
ANTI JOIN
SendsTo
ON (
group)
JOIN
ins_Message ON (message) ANTI JOIN Message ON (
message)WHERE Group.creationT
>= ins_Message.creationT
Hello
!
New
Message
||
ins_Message
|| = 1
Query
only
looks
for data
joining
the unique new
message
Translate EDCs
into SQL
c
ontext Group inv
MssgsAfterCreation:self.msg
->forAll(e
| e.cT
> self
.cT)
Slide17Our approach
13
Some experiments
N = 5.000
N = 50.000
N = 500.000
N = 5.000.000
MySQL4OCL
0,60 s
2,06 s
17,0 s
223 s
OCL2SQL
0,11 s
0,17 s
0,51 s
42,15 s
Our
Approach
0,09 s
0,09 s
0,10 s
0,10 s
MySQL4OCL
1,94 s
15,0 s
126 s
> 1 h
OCL2SQL
0,11 s
0,25 s
1,72 s
>
1h
Our
Approach
0,09 s
0,09 s
0,24 s
1,63 s
N = #
Messages
N/10 = #
Users
N/100 = #
ins_Messages
MemberOfOwned
MsgsAfterCreation
Slide18Our approach
13
Some experiments
N = 5.000
N = 50.000
N = 500.000
N = 5.000.000
MySQL4OCL
0,60 s
2,06 s
17,0 s
223 s
OCL2SQL
0,11 s
0,17 s
0,51 s
42,15 s
Our
Approach
0,09 s
0,09 s
0,10 s
0,10 s
MySQL4OCL
1,94 s
15,0 s
126 s
> 1 h
OCL2SQL
0,11 s
0,25 s
1,72 s
>
1h
Our
Approach
0,09 s
0,09 s
0,24 s
1,63 s
MemberOfOwned
MsgsAfterCreation
N = #
Messages
N/10 = #
Users
N/100 = #
ins_Messages
Slide19Our approach
13
Some experiments
N = 5.000
N = 50.000
N = 500.000
N = 5.000.000
MySQL4OCL
0,60 s
2,06 s
17,0 s
223 s
OCL2SQL
0,11 s
0,17 s
0,51 s
42,15 s
Our
Approach
0,09 s
0,09 s
0,10 s
0,10 s
MySQL4OCL
1,94 s
15,0 s
126 s
> 1 h
OCL2SQL
0,11 s
0,25 s
1,72 s
>
1h
Our
Approach
0,09 s
0,09 s
0,24 s
1,63 s
MemberOfOwned
MsgsAfterCreation
N = #
Messages
N/10 = #
Users
N/100 = #
ins_Messages
Slide20Our approach
13
Some experiments
N = 5.000
N = 50.000
N = 500.000
N = 5.000.000
MySQL4OCL
0,60 s
2,06 s
17,0 s
223 s
OCL2SQL
0,11 s
0,17 s
0,51 s
42,15 s
Our
Approach
0,09 s
0,09 s
0,10 s
0,10 s
MySQL4OCL
1,94 s
15,0 s
126 s
> 1 h
OCL2SQL
0,11 s
0,25 s
1,72 s
>
1h
Our
Approach
0,09 s
0,09 s
0,24 s
1,63 s
MemberOfOwned
MsgsAfterCreation
N = #
Messages
N/10 = #
Users
N/100 = #
ins_Messages
Slide21Our approach
13
Some experiments
N = 5.000
N = 50.000
N = 500.000
N = 5.000.000
MySQL4OCL
0,60 s
2,06 s
17,0 s
223 s
OCL2SQL
0,11 s
0,17 s
0,51 s
42,15 s
Our
Approach
0,09 s
0,09 s
0,10 s
0,10 s
MySQL4OCL
1,94 s
15,0 s
126 s
> 1 h
OCL2SQL
0,11 s
0,25 s
1,72 s
>
1h
Our
Approach
0,09 s
0,09 s
0,24 s
1,63 s
MemberOfOwned
MsgsAfterCreation
N = #
Messages
N/10 = #
Users
N/100 = #
ins_Messages
Slide22Our approach
13
Some experiments
N = 5.000
N = 50.000
N = 500.000
N = 5.000.000
MySQL4OCL
0,60 s
2,06 s
17,0 s
223 s
OCL2SQL
0,11 s
0,17 s
0,51 s
42,15 s
Our
Approach
0,09 s
0,09 s
0,10 s
0,10 s
MySQL4OCL
1,94 s
15,0 s
126 s
> 1 h
OCL2SQL
0,11 s
0,25 s
1,72 s
>
1h
Our
Approach
0,09 s
0,09 s
0,24 s
1,63 s
MemberOfOwned
MsgsAfterCreation
N = #
Messages
N/10 = #
Users
N/100 = #
ins_Messages
Slide23Our approach
13
Some experiments
N = 5.000
N = 50.000
N = 500.000
N = 5.000.000
MySQL4OCL
0,60 s
2,06 s
17,0 s
223 s
OCL2SQL
0,11 s
0,17 s
0,51 s
42,15 s
Our
Approach
0,09 s
0,09 s
0,10 s
0,10 s
MySQL4OCL
1,94 s
15,0 s
126 s
> 1 h
OCL2SQL
0,11 s
0,25 s
1,72 s
>
1h
Our
Approach
0,09 s
0,09 s
0,24 s
1,63 s
MemberOfOwned
MsgsAfterCreation
N = #
Messages
N/10 = #
Users
N/100 = #
ins_Messages
Slide24Our approach
13
Some experiments
N = 5.000
N = 50.000
N = 500.000
N = 5.000.000
MySQL4OCL
0,60 s
2,06 s
17,0 s
223 s
OCL2SQL
0,11 s
0,17 s
0,51 s
42,15 s
Our
Approach
0,09 s
0,09 s
0,10 s
0,10 s
MySQL4OCL
1,94 s
15,0 s
126 s
> 1 h
OCL2SQL
0,11 s
0,25 s
1,72 s
>
1h
Our
Approach
0,09 s
0,09 s
0,24 s
1,63 s
MemberOfOwned
MsgsAfterCreation
N = #
Messages
N/10 = #
Users
N/100 = #
ins_Messages
Slide2514
ConclusionsWe translate
OCL into incremental SQL Translate from OCL to EDCs
Translate from EDCs
to SQL
Our
translation
is incremental
When
an
update is
performed: Only constraints
that might be violated
are checked
Only for the rellevant
valuesFuture
Work
Enhance the
subset of OCL translatable
to EDCs
Use triggers to
avoid auxiliar tables
Slide26Thank you
Questions?
Slide27Incremental Checking of
OCL Constraints through SQL queries
Xavier Oriol
and
Ernest Teniente
{xoriol,teniente}@essi.upc.edu