We propose a new method for efficiently checking OCL constraints by means of SQL queries. That is, an OCL constraint is satisfied if its corresponding SQL query returns the empty set. Such queries are computed in an incremental way since, whenever a change in the data occurs, only the constraints that may be violated because of such change are checked and only the relevant values given by the change are taken into account. Moreover, the queries we generate do not contain nested subqueries nor procedures. In this way, we take advantage of relational DBMS capabilities and we get an efficient check of OCL constraints.
A conceptual schema is the description of an Information System in terms of
the data it should contain and the operations available to users to modify such
data [
We aim at defining an efficient method to perform integrity checking of OCL
constraints. That is, to efficiently check whether the OCL constraints of the
schema are satisfied by the data contents. Such problem arises in several
situations like conceptual schema execution in animation tools (like USE [
One way to efficiently check OCL constraints is aimed at reducing such
problem to check the emptiness of some SQL query [
To our knowledge, there are two implemented tools that perform such
translation: OCL2SQL [
The main goal of this paper is to overcome the previous drawback by proposing a translation from OCL constraints to SQL queries which allows to compute them incrementally by a relational DBMS. This is achieved by generating SQL queries which are only recomputed when the change applied to the data may violate their associated constraint and such that, whenever the computation is performed, only the relevant values given by the update are taken into account. In addition, our generated queries do not nest subqueries nor procedures.
Our method starts by applying the automatic translation of OCL Constraints
into Event Dependency Constraints (EDCs), as defined in [
From this point, we define an inductive translation from EDCs to SQL. Broadly speaking, l1 gives the initial table to start the FROM clause, then, each li is joined with the other tables by a cross join (i.e. a Cartesian product), inner join or anti join depending on the binding of the variables of li and its positive/negative sign. Any bilj is directly placed in the WHERE clause.
Literals li representing an instance insertion or deletion, i.e. a change taking place over the data, provide the key for incrementality. Each such li is translated as an SQL join from a table containing such insertions/deletions to the rest of tables translated from the other EDC literals. When a user wants to apply an update, this update is first inserted in the auxiliary tables and, once the method has ensured that it does not cause any integrity constraint violation, the update is applied to the real tables and the auxiliary tables are emptied. Such process may be automatized by means of a DB controller.
Since each SQL query contains at least one of such literals, such query computations will always be tied to the data changes. For instance, in the previous EDC, ιuserAge(X, Age) joins the query with the insertion of an age to a user. Thus, if we do not insert any age for any user, the join will return the empty set and no more evaluation will be needed. On the contrary, if there is some insertion of an age to a user, the join will retrieve only the affected user(s) from which to continue the query computation. Note that such evaluation is better than retrieving all the users of the database and then perform the convenient checks for each one for any kind of data update.
As a result, we get some SQL queries that are empty if and only if the OCL constraints are satisfied. Such queries perform incrementally and, in addition, avoid the use of nested queries/procedures. As a trade-off, the expressiveness of OCL is limited to the fragment of OCL translatable to EDCs.
To show the benefits of our method, we have performed some experiments to compare the efficiency of our translation with the translations given by OCL2SQL and MySQL4OCL. In such empirical study, we show that whereas our approach is capable to check the integrity of a set of constraints in at most 3 seconds per constraint regarding scenarios with 5·106 instances and 5·104 updates, OCL2SQL and MySQL4OCL could not check some invariants after one hour of execution. 2
To our knowledge, there are two tools implementing an OCL to SQL
translation: OCL2SQL[
OCL2SQL. OCL2SQL is a component of the OCL Dresden Toolkit for
translating OCL constraints to SQL views [
how to incrementally check OCL constraints [
In the following, we first define a conceptual schema with some OCL constraints that will be used as a running example to illustrate our method. Then, we show the representation of the OCL constraints in the form of EDCs. Such EDCs adds the incrementality capabilities to constraint checking. Finally, we give an inductive translation from such EDCs to SQL queries to perform the incremental checks of the constraints by means of relational DBMS technology. 3.1
Consider the example in Fig. 1 of a message service application. In this class diagram, a user sends messages to some conversation groups. There are two kinds of conversation groups: pairs, that is, two simple users sending messages to each other; and groups, that is, two or more users formally grouped since some creation date. As expected, a group has some users as members and also one user as owner.
ConversationGroup receiver 1
IsSentToP {disjoint,Pcomplete}P
Pair 0..,
IsFormedByP 2 creatioGnrToimupe:PDate o00gw..r..o,,nuepMdOewmnbeerorwun2se.e.1r,r lspathasottCen:eoUP:nPSsnSeteritrcnritgn:PgDate s1enderSends m0s..g, cbroedayMt:i0oPe.nS.,sTtrsiimnmagegs:egPDate
OCL Constraints in Fig. 2 states some constraints that should always be satisfied by the data of such schema. Concretely, MessagesInAPairBelongToPair and MessagesInAGroupBelongToGroup state that the messages received by any pair/group are sent by the members of such pair/group. UserIsMemberOfOwnedGroups states that the owner of a group is also a member of such group and finally, MessagesOfGroupAreSentAfterItsCreation states that any messages sent to a group has to be created after the creation of the group.
Note that we have deliberately omitted the identifier constraints of the classes (e.g. User.allInstances()->isUnique(phone)) because they can be easily well treated by SQL primary keys on tables.
context Pair inv MessagesInAPairBelongToPair: self.user->includesAll(self.msg.sender) context Group inv MessagesInAGroupBelongToGroup: self.user->includesAll(self.msg.sender) context User inv UserIsMemberOfOwnedGroups: self.group->includesAll(self.owned) context Group inv MessagesOfGroupAreSentAfterItsCreation: self.msg->forAll(e|e.creationTime > self.creationTime)
Given a translation of the UML class diagram into SQL tables, our goal is
to translate each OCL constraint into an SQL query in such a way that the
constraint is not violated by an update (i.e. a change over the data) if and only
if its corresponding SQL query is empty. For this purpose, we will first represent
the violations of OCL constraints by means of EDCs and, then, obtain the SQL
queries from this intermediate representation.
The starting point of our work is the Event-Dependency Constraints (EDC)
representation of the OCL constraint violations. The translation from OCL to
EDCs can be fully automatized using the method described in [
An EDC is a logic formula that states when some structural events (i.e. insertions or deletions of data) may cause the violation of a constraint. In terms of logics, an EDC is a conjunctive query with negated base atoms and builtin literals (i.e. arithmetic comparisons). That is, it has the form l1 ∧ ... ∧ ln ∧ bil1 ∧ ... ∧ bilm → ⊥ where each li is a literal representing either an instance, an instance insertion or an instance deletion; and bilj is a built-in literal. Such builtin-literals are optional. In addition, EDCs are safe clauses, that is, any variable appearing in a negated or built-in literal, also appears in a positive literal.
For instance, the EDCs representation of the UserIsMemberOfOwnedGroups OCL constraint is: ¬owner(G, U ), ιowner(G, U ), ¬member(G, U ), ¬ιmember(G, U ) → ⊥ ¬owner(G, U ), ιowner(G, U ), member(G, U ), δmember(G, U ) → ⊥ owner(G, U ), ¬δowner(G, U ), member(G, U ), δmember(G, U ) → ⊥ (1) (2) (3)
Note that an OCL constraint is written into more than one EDC since each EDC defines a different combination of events that may lead to the violation of the constraint. Concretely, Rule 1 above states that UserIsMemberOfOwnedGroups will be violated if we insert a user U as the owner of group G, where U is not a member of G and without inserting U as a member of G. Rule 2 identifies a violation of the same constraint when we insert U as the owner of G while deleting U as a member of G. Finally, Rule 3 indicates a violation when we delete U as a member of G without deleting U as the owner of G.
The length of an EDC is directly proportional to the length of the OCL
constraint, but the number of EDC rules we get for an OCL constraint is
exponential to the number of navigation steps of it since there is an exponential
number of different ways to violate a constraint and each EDC captures one. To
avoid such situation, we could take out the common factor of EDCs (e.g. rules
2 and 3 could be summarized in one rule using disjunctions). In this way, we
would achieve a behavior similar to the TREAT algorithm [
It is worth saying that the current translation from OCL to EDCs only deals
with a fragment of OCL. However, such fragment is expressive enough to deal
with a superset of the constraints that can be specified with the constraint
patterns defined in [
Before translating the EDCs into SQL queries, we need to translate the UML class diagram into SQL tables. In our example, we will suppose that each UML class/association has been translated into a different SQL table. Thus, the signatures of the tables obtained from the class diagram of Fig. 1 are the following:
3.4
We define now an inductive translation from EDCs to SQL queries based on the EDC length. The translation is composed by two functions: one for computing the from clause and another for computing the where clause. Regarding the select clause, we select the column that represents the id of the instances violating the constraint (i.e. the self OCL instances for which the invariant evaluates to false).
Therefore, the translation of an EDC to an SQL query is given by the pattern:
Where sqlColumn returns the SQL column name corresponding to the given EDC variable. The EDC variable in which we are interested is the one corresponding to the OCL self variable, so, we use the function edcVariable with the parameter ”self” to obtain it. In case that there is no ”self” variable in the OCL constraint (e.g. the constraint may be like Class.allInstances()->forAll(e|...)), other options should be considered like selecting the column/s corresponding to the OCL iterator variable/s (e.g. the previous e variable).
Since EDCs use some literals to represent the insertion/deletion of instances, we assume that our database schema contains also some public auxiliary tables in which we temporally store the instances that are being inserted/deleted. Thus, such literals are mapped to those auxiliary tables.
For instance, EDC 2 uses the literal ιowner. Such literal is mapped to a new auxiliary SQL table ins owner where we temporary write the insertions of instances of owner we are applying to the data.
Finally, recall that an EDC has the form l1 ∧ ... ∧ ln ∧ bil1 ∧ ... ∧ bilm → ⊥. Without loss of generality, we assume that all negated literals are placed in the end of the formula (i.e. from some li to ln); and that all terms of a literal lj are variables with different names. Such condition can be ensured by replacing some terms for new fresh variables and binding such variables to its actual terms with new built-in literals (e.g. P(X,1) would be translated to P(X,Y) ∧ Y = 1 ). Translation Base Case In the base case, the EDC is composed by just one literal l1. Such literal is necessarily positive to ensure the safeness of the clause. Then, the translation is as follows: fromTransl(l1) = sqlTable(l1) whereTransl(l1) = ∅ Translation Inductive Case In the inductive case, the EDC has the form L ∧ li or L ∧ bili, where L is a non-empty list of literals, li is a positive or negated literal, and bili a built-in literal.
In the first case, if li is positive, the translation consists in an inner join between the translations of li and L joining the columns corresponding to their bound variables. If no variable is bound, then, a cross join (i.e., a Cartesian product) is performed instead. Thus, the translation is as follows: fromTransl(L ∧ li) = fromTransl(L) JOIN sqlTable(li) ON (binding(L, li)) whereT ransl(L ∧ li) = whereT ransl(L)
Where binding(L, li) is a function that returns the column joins according to the variables of li that are bound to L.
If li is negative, the translation consists in performing an anti join to get those tuples of L that do not join li. For performing the anti join, we use a left join and check the joined columns to be null. Thus, the translation results in: fromTransl(L ∧ li) = fromTransl(L) LEFT JOIN sqlTable(li) ON (binding(L, li)) whereT ransl(L ∧ li) = whereT ransl(L) AND columnN ame(li, 1) IS NULL
Where columnName(li, 1) returns the name of the first SQL column of the SQL table corresponding to li. Such column is used to check whether the joined columns resulted into null.
Also, notice that binding(L, li) will not be empty because all the variables of li are necessarily bound to L since any EDC satisfies the safeness property.
Finally, the translation when we deal with a built-in literal bili is as follows: fromTransl(L ∧ bili) = fromTransl(L) whereT ransl(L ∧ bili) = whereT ransl(L) AND sqlComparison(L, bili) Where sqlComparison is a function that translates the bili into the SQL syntax. I.e. it changes the variables of bili for the corresponding SQL column names of the L variables they are bound to. Note that all variables of bili are bound to L because the safeness property.
Translation example To illustrate our translation, we show how the EDC in rule 2 is specified as an SQL query. First of all, we sort the EDC literals to move the negated ones to the end of the formula, thus obtaining the following EDC’: ιowner(G, U ), member(G, U ), δmember(G, U )¬owner(G, U ) → ⊥
Next, by applying the translation we have just defined, we get: SELECT T0.user FROM ins_owner AS T0 WHERE T3.group IS NULL
JOIN Member AS T1 ON (T1.group = T0.group AND T1.user = T0.user) JOIN del_Member AS T2 ON (T2.group = T0.group AND T2.user = T0.user) LEFT JOIN Group AS T3 ON (T3.group = T0.group AND T3.group = T0.group) Lastly, and since violations of an OCL constraint are specified by means of several EDCs, the final SQL query we obtain to check the OCL constraint is given by the SQL union of all the queries we have obtained from each EDC. 4
We have conducted an experiment to illustrate the scalability improvement
provided by our SQL queries as compared to OCL2SQL[
Given the schema of a message service application in our example, the conducted experiment consisted into adding some new message instances to some conversation groups in several randomly generated database states of increasing size. Then, we checked all the constraints of the example by means of the queries generated by MySQL4OCL, OCL2SQL and our incremental approach.
The randomly generated data consisted in N users who randomly had already sent N ∗ 10 messages distributed in N/10 groups and N/10 pairs. The update consisted in N/10 new messages. The experiments were carried out on MySQL 5.6 running on Windows 7 in a Intel T4500 2.30GHz machine with 4GB of RAM. The database had the MySQL default indexes for primary/foreign keys.
We show our results in table 1. The title of the columns indicates the size of the database giving the number of current preexisting messages. As it can be seen, we are able able to check the integrity of the constraints in at most 3 seconds per constraint with a database state with 5·106 messages and 5·104 new insertions. In contrast, OCL2SQL/MySQL4OCL cannot afford some of the constraints after 1 hour of execution.
The result of the 3rd constraint requires to take special attention. Since adding new messages cannot cause the violation of the 3rd OCL constraint (UserIsMemberOfOwnedGroups ), our incremental approach performs in almost constant time because the query begins from an empty auxiliary table. For the other approaches, we argue that its better performance might be because it contains one level less of subqueries in comparison to the other constraints.
We have proposed a method for efficiently checking OCL constraints by means of SQL queries that perform incrementally since only the relevant constraints and the relevant values are taken into account during the computation. In addition, they are written in such a way not to nest any other query nor procedure inside.
To achieve it, we first specify the OCL constraint violations through an EDC formalism. Such formalism offers the advantage of stating which events may cause the violation of an integrity constraint. Then, each EDC is translated into an SQL query. When doing such translation, we create some new SQL tables for temporary storing the new instances that are going to be inserted/deleted. Such auxiliary tables are used by our SQL queries to perform incrementally.
We made some experiments showing that our approach is able to check in
seconds four OCL constraints over an scenario containing 5·106 instances with
5·104 updates while other approaches like OCL2SQL and MySQL4OCL could
not afford some of these constraints after 1 hour of execution. As future work, we
would like to extend the fragment of OCL we are dealing with and to implement
in SQL the rules proposed in [