=Paper=
{{Paper
|id=Vol-3089/ttc21_paper_ocl2sql_Nguyen_case
|storemode=property
|title=The TTC 2021 OCL2PSQL case
|pdfUrl=https://ceur-ws.org/Vol-3089/ttc21_paper11_ocl2sql_Nguyen_case.pdf
|volume=Vol-3089
|authors=Hoang Phuoc-Bao Nguyen,Antonio García-Domíguez,Manuel Clavel
|dblpUrl=https://dblp.org/rec/conf/ttc/NguyenGC21
}}
==The TTC 2021 OCL2PSQL case==
https://ceur-ws.org/Vol-3089/ttc21_paper11_ocl2sql_Nguyen_case.pdf
The TTC 2021 OCL2PSQL case
Hoang Phuoc-Bao Nguyen1 , Antonio García Domínguez2 and Manuel Clavel1
1
Vietnamese-German University, Binh Duong, Vietnam
2
Aston University, Birmingham, United Kingdom
Abstract
The Object Constraint Language (OCL) is a textual, declarative language used as part of the UML standard for specifying
constraints and queries on models. As such, generating code from OCL expressions is part of an end-to-end model-driven
development process. Certainly, this is the case for database-centric application development, where integrity constraints
and queries can be naturally specified using OCL. Not surprisingly, there have already been several attempts to map OCL into
SQL. In this case study, we invite participants to implement, using their own model-transformation methods, one of these
mappings, called OCL2PSQL. We propose this case study as a showcase for different methods to prove their readiness for
coping with moderately complex model transformations, by showing the usability, conciseness, and ease of understanding
of their solutions when implementing a non-trivial subset of OCL2PSQL.
Keywords
OCL, SQL, Model-transformation, Transformation tools
1. Introduction translated. In particular, [5] resorts to imperative features
of SQL (e.g. loops and cursors) for translating OCL itera-
The Object Constraint Language (OCL) [1] is a textual lan- tors, while [6] introduces a mapping (OCL2PSQL) which
guage typically used, as part of the UML standard [2], for only uses standard subselects and joins for translating
specifying constraints and queries on models. It is a side- OCL iterators. 1
effect free specification language: expressions evaluate
Example 1.1. As an example of the transformations pro-
to values without changing anything in the underlying
duced by OCL2PSQL, suppose that we want to know
model. OCL is a strongly-typed language: expressions
if, in a given scenario, there is exactly one car. We can
either have a primitive type (such as Boolean, integer), a
formalize this query in OCL as follows:
class type, a tuple type, or a collection type. The language
provides standard operators on primitive data, tuples, and Car.allInstances()→size() = 1
collections. It also provides a dot-operator to access the
properties of the objects, and several iterators to iterate where we compare the number of objects in the class Car
over collections. with an integer 1. OCL2PSQL translates this expression
The Structured Query Language (SQL) [3] is a special- into a SQL-select statement:
purpose programming language designed for manag- SELECT TEMP_left.res = TEMP_right.res AS res,
ing data in relational database management systems 1 AS val
(RDBMS). Its scope includes data insert, query, update FROM (
and delete, schema creation and modification, and data SELECT COUNT(*) AS res, 1 AS val
access control. Although SQL is, to a great extent, a FROM (
declarative language, it also contains stored-procedures. SELECT Car_id AS res, 1 AS val
These are routines stored in the database that may exe- FROM Car
cute loops using the so-called cursors. ) AS TEMP_src
) AS TEMP_left
In the context of model-driven engineering, there exist
JOIN (
several proposals for translating OCL into SQL [4, 5, 6], SELECT 1 AS res, 1 AS val
which mostly differ in the way how OCL iterators are ) AS TEMP_right
TTC’21: Transformation Tool Contest, Part of the Software in which the select-items include the comparison be-
Technologies: Applications and Foundations (STAF) federated
tween the result of two-subqueries (e.g. TEMP_left.res
conferences, Eds. A. Boronat, A. García-Domínguez, and G. Hinkel,
25 June 2021, Bergen, Norway (online). and TEMP_right.res), representing the result when eval-
" ngpbhoang1406@gmail.com (H. P. Nguyen); uating the two sides of the comparison of the given
a.garcia-dominguez@aston.ac.uk (A. G. Domínguez); OCL expression (e.g. Car.allInstances()→size()
manuel.clavel@vgu.edu.vn (M. Clavel) and 1), respectively. Furthermore, the subquery
0000-0003-4217-0983 (H. P. Nguyen); 0000-0002-4744-9150
1
(A. G. Domínguez); 0000-0002-4966-855X (M. Clavel) The letter “P” in OCL2PSQL stands for pure. The idea is that
© 2021 Copyright for this paper by its authors. Use permitted under Creative
Commons License Attribution 4.0 International (CC BY 4.0). OCL2PSQL only uses the declarative features of SQL for mapping
CEUR
Workshop
Proceedings
http://ceur-ws.org
ISSN 1613-0073
CEUR Workshop Proceedings (CEUR-WS.org) OCL expressions.
TEMP_left returns the size of its subquery, aliased 2.1. Input metamodel
TEMP_src, which is the translation of the sub-expression
OCL is a contextual language: its expressions are written
Car.allInstances(). □
in the context provided by a data model. Consequently,
The full recursive definition of OCL2PSQL can be the input metamodel for OCL2PSQL can be seen as the
found in [6], but we have included the subset of union of two, inter-related metamodels: namely, the
OCL2PSQL definition of the expressions involved in this metamodel for data models and the metamodel for OCL
competition in Appendix A. The solution authors can also expressions.
use Appendix A to understand the above transformation.
The correctness of the mapping is formulated as follows. 2.1.1. Input metamodel for data models
Let 𝑒 be an OCL expression (with no free variables) and let
𝒪 be a scenario of its context model. Then, the evaluation For OCL2PSQL, a data model contains classes and associ-
of the expression 𝑒 in the scenario 𝒪 should return the ations. A class may have attributes and associations-ends.
same result that the execution of the query OCL2PSQL(𝑒), The multiplicity of an association-end is either ‘one’ or
i.e., the SQL query generated by OCL2PSQL from 𝑒, in the ‘many’.
database OCL2PSQL(𝒪), i.e., the database corresponding The data model metamodel for OCL2PSQL is shown
to 𝒪 according to OCL2PSQL. 2 in Figure 1. DataModel is the root element and contains a
The TTC 2021 OCL2PSQL case welcomes participants set of Entitys. Each Entity represents a class in the data
to implement the subset of OCL2PSQL mapping provided model: it contains a set of Attributes and a set of Assoc-
in Appendix A using their own model-transformation iationEnds. Each Attribute represents an attribute of
methods. This case study can serve as a showcase for a class: it has a name and a type. Each AssociationEnd
different methods to prove their readiness to cope with represents an association-end: it has a name, an asso-
moderately complex model-transformations, by showing ciation class name association and a Multiplicity
the usability, conciseness, and understandability of their value. Each AssociationEnd is also linked to its opposite
solutions when implementing the subset of OCL2PSQL. AssociationEnd, and with its target Entity.
More information about the main task will be provided
in Section 3.
All resources for this case are available on Github [7].
Please follow the description in the footnote and create
a pull request with your own solution after you have
submitted your description to EasyChair.
The rest of the document is structured as follows: Sec-
tion 2 describes the input and output of OCL2PSQL trans-
formation. Section 3 provides the main task that should
be tackled in a solution. Finally, Section 4 proposes the
case evaluation scheme for the contest.
Figure 1: OCL2PSQL metamodel for data models.
2. Transformation description
OCL2PSQL is a recently proposed mapping from OCL
to SQL [6]. It addresses some of the challenges and limi-
2.1.2. Input metamodel for OCL expressions
tations of previous OCL-to-SQL mappings, particularly
with respect to the execution-time efficiency of the gen- The definition of the OCL mapping presented in Ap-
erated SQL queries [8]. pendix A only covers a subset of the OCL language. For
Next, we give a detailed description of the input and the OCL expressions involved in this competition, we
output metamodels for the TTC 2021 OCL2PSQL case. have simplified the metamodel for OCL expressions to
The input metamodels represent the part of OCL lan- the minimum. For interested readers and solution au-
guage that is covered in this competition. The output thors who would like to extend or implement their own
metamodel represents the part of the SQL language that implementation, the class diagram of the OCL expression
is used by OCL2PSQL to translate the aforementioned can be found in its specification document in [1].
part of OCL language. The OCL2PSQL metamodel for OCL expressions in
this competition is shown in Figure 2. Readers who are
2
not familiar with the OCL can refer to Appendix C for a
The OCL2PSQL mapping rests on an underlying mapping be-
more detail description of our metamodel.
tween data models and SQL database schema. The full definition of
this mapping is also provided in [6] but it is not needed in this case.
Figure 2: OCL2PSQL metamodel for OCL expression.
For the sake of illustration, we show in Figure 3 the
object diagram of OCL expression in Example 1.1. It is
an expression of class OperationCallExp with = as the
referredOperation. In this expression,
• The source is also an expression of class
OperationCallExp with size() as the referred-
Operation, representing the sub-expression
Car.allInstances()→size(). Furthermore,
in the aforementioned sub-expression, the
source is yet another expression of class
OperationCallExp with allInstances() as
the referredOperation, representing the sub-
expression Car.allInstances(). Finally, in the
aforementioned sub-expression, the source is a Figure 3: The object diagram of OCL expression
TypeExp, representing the sub-expression Car, Car.allInstances()→size() = 1.
which refers to the Car entity of the data model.
• The argument is an expression of type Integer-
LiteralExp with 1 as the integerValue. Figure 5 shows the object diagram of the following SQL-
select statement:
SELECT COUNT(*) > 0 AS res
2.2. Output metamodel FROM Car AS c
For OCL2PSQL, a SQL query is a basic SQL-select state- WHERE c.color IS NULL
ment, which may contain subselects, WHERE clauses,
This is a SelectStatement with a PlainSelect as
GROUP BY clauses, and JOINs.
selectBody. The PlainSelect contains:
2.2.1. Output metamodel for SQL-select • A SelectItem element that represents the clause
statements (SELECT) COUNT(*) > 0 AS res. It contains a
GreaterThanExpression expression, in which
Figure 4 shows the overview diagram of a SQL-select the leftExp is a CountAllFunction expression,
statement. Appendix D describes the elements of this and the rightExp is a LongValue expression with
metamodel in more detail. For the sake of illustration, value 0. Furthermore, it has an Alias named res.
Figure 4: OCL2PSQL metamodel for SQL-select statements.
3. Main task
The main task for the participants in the TTC 2021 OCL-
2PSQL case is to implement the subset of OCL2PSQL
mapping defined in Appendix A using their own model-
transformation methods. Participants are free to extend
or modify the OCL2PSQL mapping, or even to propose
their own mapping from OCL to SQL, in which case
they should also provide convincing arguments that their
solution is correct with respect to the semantics of OCL
and SQL. 3
During the contest, the participants will be presented
with different challenges of increasing complexity. Each
challenge will be an OCL2PSQL OCL expression, i.e.,
an instance of the OCL2PSQL metamodel for OCL ex-
Figure 5: The object diagram of SELECT COUNT(*) > 0 as pressions. The context for all the challenges will be an
res FROM Car c WHERE c.color IS NULL. OCL2PSQL data model, i.e., an instance of OCL2PSQL
metamodel for data models. Then, the participants will
be asked to generate the solutions for these challenges, ap-
• A Car Table with an Alias named c, represents plying their own transformation rules. Very importantly:
the clause (FROM) Car AS c. (i) each solution should be a valid SQL-select statement
• A IsNullExpression element that represents the in the database schema corresponding to the given data
clause (WHERE) c.color IS NULL. It contains a model, according to the definition of the OCL2PSQL map-
Column color referred from the Table Car of the ping; moreover, (ii) each solution should be a SQL-select
previous clause (notice that in this case, the alias statement returning a result-table with (at least) a col-
c of the Table Car is used as a name for the table umn res. When executing the solution for a challenge
referred to the color column). 3
For the participants who would like to extend their implemen-
tation beyond the subset of OCL language provided for our competi-
tion, please revise the full version of our OCL2PSQL mapping in [6]
with the “fixes” included in Appendix B.
on a given scenario, this column res will be interpreted • The folder models contains the challenges listed
as holding the result of evaluating the given challenge in in challenges.txt in XMI format. More specif-
the same scenario. Finally, the solutions will be checked ically, each file Stage𝑖Challenge𝑗 .xmi contains
for correctness, using a set of selected scenarios. the representation of the challenge 𝑗 within the
For the participants’ convenience, we have grouped stage 𝑖 in the file challenges.txt in XMI format.
the challenges into different stages. Each stage contains In the same folder, the file CarPerson.xmi con-
challenges that apply similar OCL2PSQL mapping rules, tains the data model CarPerson in XMI-format.
particularly: • In the folder metamodels, the file ocl.ecore con-
• Stage0 only requires the mapping rule for literals. tains the EMF implementation of OCL2PSQL
The OCL expressions in this stage are context- metamodel for OCL expressions. Also in the same
free. folder, the file sql.ecore contains the EMF im-
• Stage1 is similar to Stage1, with additional map- plementation of OCL2PSQL metamodel for SQL-
ping rules for OperationalCallExp (operator: select statements.
equality and conjunction). The OCL expressions
in this stage are also context-free.
• Stage2 requires the mapping rules for
OperationalCallExp (operator allInstances)
and TypeExp. From this stage on, the OCL
expressions are context-dependent, i.e., the
underlying context model will be needed.
• Stage3 is similar to Stage2, with additional map-
ping rules for OperationalCallExp (operator:
Figure 6: The CarPerson data model.
size and =).
• Stage4 is similar to Stage3, with additional map-
ping rules for VariableExp and IteratorExp
(kind: collect).
• Stage5 is similar to Stage4, with additional map- 4. Benchmark framework
ping rules for PropertyCallExp.
• Stage6 is similar to Stage5, with additional map- The case resources on GitHub [7] include an automated
ping rules for AssociationClassCallExp. benchmark framework for systematic measurement of
• Stage7 is similar to Stage5 and Stage6, with ad- the performance and correctness of the various solutions.
ditional mapping rules for IteratorExp (kind: It is based on the framework of the TTC 2017 Smart Grid
exists). case [9], without the visualisation components. Solution
• Stage8 is a more complex version of Stage7, with authors are recommended to adapt their solutions to this
nested IteratorExp of kind exists. framework to allow for easier integration and comparison
of the various solutions.
For the purpose of testing, the participants can find The configuration of the benchmark framework for
the following material in the case materials repository: the TTC 2021 OCL2PSQL case is stored in the file
• In the docs folder, the file challenges.txt con- config.json inside the folder config. This file includes
tains a list of challenges grouped in the afore- the definitions of the various stages and challenges, the
mentioned stages. Each stage has a unique num- name of the tools to be run, the number of repetitions
ber, and each challenge within a stage has also to be applied, the timeout in milliseconds for each ex-
a unique number. The greater the number of ecution and the connection information for the local
a stage, the greater its complexity. The context MySQL database. Currently, the file config.json has
for all challenges in challenges.txt is the data already contained the stages and challenges listed in the
model CarPerson shown in Figure 6. file challenges.txt.
In the same folder, the file scenarios.txt con- In the folder docker, the Dockerfile contains the in-
tains a list of scenarios. Each scenario de- struction to build a MySQL 5.7 Docker image that con-
scribes an instance of the data model CarPerson. tains all the SQL data scenarios of the CarPerson database
Then, for each scenario, and each (relevant) corresponding to the ones listed in scenarios.txt. This
stage/challenge listed in challenges.txt, the image is currently used for building databases to test the
file scenarios.txt contains the correct result: correctness of the reference solution. Solution authors
i.e., the expected SQL result that corresponds to can use either the image we provide or their own local
the evaluation of the given stage/challenge in MySQL database installation, in which they would need
the given scenario. to change the information in the config.
The repetition of the transformation is handled by the
Listing 1: solution.ini file for the ReferenceXMI solu-
framework. Moreover, for every repetition, the frame-
tion
work provides the following information in environment
[ build ] variables: the run index, stage number and challenge
default =mvn compile number, the OCL expression corresponding to the chal-
skipTests =mvn compile lenge in plaintext, as well as the file path of that expres-
sion in XMI-format, and the file path of the context of
[run]
cmd=mvn -f pom.xml -quiet -Pxmi exec:exec
the challenge, also in XMI-format. More specifically, the
available environment variables are:
• MySQLUsername: the username of the local
4.1. Solution requirements MySQL database system on which the statement
will be run.
All solutions must be forks of the main Github project, • MySQLPassword: the password of the given
and should be submitted as pull requests after the de- user.
scriptions have been uploaded to EasyChair. • MySQLPort: the port number of the local
All solutions should be in a subdirectory of the MySQL database system.
solutions folder, and inside this subdirectory they
• StageIndex: the index of the stage whose chal-
should include a solution.ini file describing how the lenge is to be run.
solution should be built and run. As an example, List-
• ChallengeIndex: the index of the challenge
ing 1 shows the file for the reference solution. The build
within the stage which will be run.
section provides the default and skipTests fields for
specifying how to build and test, and how to simply build, • OCLQuery: the OCL expression, in text-format,
respectively. In the run section, the cmd field specifies corresponding to the challenge to be run.
the command to run the solution. • PathToOCLXMI: the absolute path to the file
Solutions should print to their standard output streams containing the OCL expression, in XMI-format,
a sequence of lines with the following fields, separated corresponding to the challenge to be run.
by semicolons: • PathToSchemaXMI: the absolute path to the
file containing the SQL schema, in XMI-format,
• Tool: name of the tool. corresponding to the context (data model) of the
• Stage: integer with the stage within the case challenges to be run.
whose challenge is being solved. • RunIndex: the index of the repetition to be run.
• Challenge: integer with the challenge within • Tool: the name of the tool (the name of the
the stage which is being solved. solutions subfolder).
• RunIndex: integer with the current repetition
of the transformation. Solution authors may wish to consult the reference
• MetricName: may be “TransformTimeNanos”, solution for guidance on how to use the various environ-
“TestTimeNanos”, or “ScenarioID” where ID is ment variables and how to test the correctness of your
the identifier of the scenario under test. transformations. Solution authors are free to reuse the
source code of this reference solution for these aspects
• MetricValue: the value of the metric:
(e.g. the CaseLauncher and Configuration classes), as
– For “TransformTimeNanos”, an integer well as the lib/sql.jar library, in the reference solution
with nanoseconds spent performing the that parses the SQL-select statement from XMI model
transformation. to plaintext. The reference solution uses Maven to re-
– For “TestTimeNanos”, an integer with trieve the appropriate libraries for communicating with
nanoseconds spent testing the correctness our own implementation of OCL2PSQL. In addition, we
of the transformation through executing have also installed additional libraries locally in folder
the transformed SQL-select statement on lib using a shell script. The instruction for running the
different database scenarios. reference solution can be found on the benchmark repos-
– For metrics following the “ScenarioID” pat- itory.
tern, a string of either “passed” or “failed”
indicating whether the transformation in 4.2. Running the benchmark
that scenario succeeded or failed, respec-
tively. The benchmark framework needs Python 3.3 or later to
be installed, and the reference solution requires Maven 3
and Java 8 or later. Solution authors are free to use alter- 11814 of Lecture Notes in Computer Science, Springer,
native frameworks and programming languages, as long 2019, pp. 185–203.
as these dependencies are explicitly documented. For [7] H. P. Nguyen, A. G. Dominguez, M. Clavel,
the final evaluation, it is planned to construct a Docker The case resources and benchmark framework
image with all solutions, and this will require installing associated with this case, https://github.com/
those dependencies into the image. TransformationToolContest/ttc2021-ocl2psql, 2021.
If all dependencies are installed, the benchmark can be [8] M. Clavel, H. P. Nguyen, Mapping OCL into SQL:
run with python scripts/run.py (potentially python3 Challenges and Opportunities Ahead, in: A. D.
if Python 2.x is installed globally in the same system). Brucker, G. Daniel, F. Jouault (Eds.), 19th Interna-
tional Workshop in OCL and Textual Modeling (OCL
2019) co-located with MODELS 2019, volume 2513
5. Evaluation of CEUR Workshop Proceedings, CEUR-WS.org, 2019,
pp. 3–16.
For the submitted solutions that strictly follow the pro-
[9] G. Hinkel, An NMF solution to the Smart Grid
posed mapping, the benchmark framework will provide
Case at the TTC 2017, in: A. García-Domínguez,
independent measurements of the correctness, complete-
G. Hinkel, F. Krikava (Eds.), Proceedings of the
ness, and time usage of these solutions, then based on
10th Transformation Tool Contest (TTC 2017), co-
the evaluation outcome, the 1st/2nd/3rd place award will
located with the 2017 Software Technologies: Appli-
be rewarded.
cations and Foundations (STAF 2017), Marburg, Ger-
For other solutions, that modify or extend the proposed
many, July 21, 2017, volume 2026 of CEUR Workshop
mapping, besides the aforementioned criteria, attendees
Proceedings, CEUR-WS.org, 2017, pp. 13–17. URL:
to the contest will also evaluate the usability, conciseness,
http://ceur-ws.org/Vol-2026/paper5.pdf.
and understandability of the transformation rules that de-
fine the different solutions, as well as the other attributes
of interest that the solution providers may want to focus
on. In this regard, although some solutions may not be A. The mapping OCL2PSQL in a
entirely complete or may be hard to understand, or may nutshell
not share the common interest, they may still serve as
examples of active research areas within model transfor- The mapping OCL2PSQL is defined recursively over the
mations that the community may wish to showcase. To structure of OCL expressions. To describe the key idea
recognize these contributions, an audience-driven “Most underlying its definition, and to illustrate it with the pre-
Promising” award will be given. sentation of some recursive cases, we need to introduce
some notations first.
Notation. Let qry be a SQL query. Let db be a SQL
References database. Then, we denote by Exec(qry, db) the result
[1] Object Management Group, Object Constraint Lan- of executing qry on db. Let 𝑒 be an OCL expression.
guage Specification Version 2.4, Technical Report, Then, we denote by FVars(𝑒) the set of variables that
2014. occur free in 𝑒, i.e., that are not bound by any iterator. Let
[2] Object Management Group, Unified Modeling Lan- 𝑒 be an OCL expression, and let 𝑣 be a variable introduced
guage, Technical Report, 2017. in 𝑒 by an iterator expression 𝑠→iter (𝑣 | 𝑏). Then, we
[3] International Organization for Standardization, denote by src𝑒 (𝑣) the source 𝑠 of 𝑣 in 𝑒. Let 𝑒 be an
ISO/IEC 9075-(1–10) Information technology – OCL expression and let 𝑒 ′
be a subexpression of 𝑒. Then,
Database languages – SQL, Technical Report, 2011. we denote ′by SVars𝑒 (𝑒 ) the set of variables which (the
′
[4] F. Heidenreich, C. Wende, B. Demuth, A Framework value of) 𝑒 depends on, and is defined as follows:
for Generating Query Language Code from OCL In- ⋃︁
SVars𝑒 (𝑒′ ) = {𝑣} ∪ SVars𝑒 (src𝑒 (𝑣)).
variants, ECEASST 9 (2008).
𝑣∈FVars(𝑒′ )
[5] M. Egea, C. Dania, SQL-PL4OCL: an automatic code
generator from OCL to SQL procedural language, Let 𝑒 be an OCL expression, such that FVars(𝑒) = ∅.
Software and Systems Modeling 18 (2019) 769–791. Let 𝒪 be a scenario. Then, we denote by Eval(𝑒, 𝒪) the
[6] H. P. Nguyen, M. Clavel, OCL2PSQL: An OCL-to- result of evaluating 𝑒 in 𝒪.
SQL Code-Generator for Model-Driven Engineering, Finally, let 𝒟 be a data model. Then, we denote by
in: T. K. Dang, J. Küng, M. Takizawa, S. H. Bui (Eds.), map(𝒟) the SQL database schemata corresponding to 𝒟,
Future Data and Security Engineering - 6th Interna- according to OCL2PSQL. Let 𝒟 be a data model, and let 𝒪
tional Conference, FDSE 2019, Proceedings, volume be a scenario of 𝒟. Then, we denote by map(𝒪) the in-
stance of 𝒟 corresponding to 𝒪, according to OCL2PSQL.
Let 𝑒 be an OCL expression, let 𝑒′ be a subexpression of 𝑐.att as res,
𝑒. Then, we denote the SQL query corresponding to 𝑒′ TEMP_obj.val as val,
by map𝑒 (𝑒′ ), according to OCL2PSQL. TEMP_obj.ref_𝑣 ′ as ref_𝑣 ′ , for each 𝑣 ′ ∈ SVars𝑒 (𝑣)
Definition: key idea and some cases. The differ- FROM (map𝑒 (𝑣)) as TEMP_obj
ent recursive cases follow the same design principle: LEFT JOIN 𝑐
namely, let 𝑒 be an OCL2PSQL-expression, let 𝑒′ be ON TEMP_obj.ref_𝑣 = 𝑐.𝑐_id AND TEMP_obj.val = 1
a subexpression of 𝑒, and let 𝒪 be a scenario. Then,
Exec(map𝑒 (𝑒′ ), map(𝒪)) returns a table, with a col- Association-ends expressions
umn res, a column val, and, for each 𝑣 ∈ SVars𝑒 (𝑒′ ), a
column ref_𝑣. Informally, for each row in this table: (i) Let 𝑒 be an OCL expression. Let 𝑒′ be a subexpression of
the columns ref_𝑣 contain a valid “instantiation” for the 𝑒. Let 𝑒′ = 𝑣 .ase, where 𝑣 is a variable of class-type 𝑐,
iterator variables of which the evaluation of 𝑒′ depends and ase is an association-end of the class 𝑐.
on (if any); (ii) the column val contains 0 when evaluat- Let Assoc(ase) be the association to which ase be-
ing the expression 𝑒′ , with the “instantiation” represented longs, and let Oppos(ase) be the association-end at the
by the columns ref_𝑣, evaluates to the empty set; other- opposite end of ase in Assoc(ase). Then,
wise, the column val contains 1; (iii) when the column
map𝑒 (𝑣 .ase) =
val contains 1, the column res contains the result of
SELECT
evaluating the expression 𝑒′ with the “instantiation” rep-
Assoc(𝑎𝑠𝑒).ase as res,
resented by the columns ref_𝑣; when the column val
CASE Assoc(ase).Oppos(ase) IS NULL
contains 0, the value contained in the column res is not
WHEN 1 THEN 0
meaningful.
ELSE 1 END as val,
We define the recursive definition of OCL2PSQL map-
TEMP_src.ref_𝑣 ′ as ref_𝑣 ′ , for each 𝑣 ′ ∈ SVars𝑒 (𝑣)
pings that will be used in our competition. The definition
FROM (map𝑒 (𝑣)) as TEMP_src
here was taken from the original paper and has already
LEFT JOIN Assoc(ase)
included the corrigenda in Appendix B.
ON TEMP_src.ref_𝑣 = Assoc(ase).Oppos(ase)
String (integer, and Boolean) literals
AllInstances-expressions
Let 𝑒 be an OCL expression. Let 𝑒′ be a subexpression of
Let 𝑒 be an OCL expression. Let 𝑒′ be a subexpression
𝑒. Let 𝑒′ = 𝑙, where 𝑙 is a string literal. Then,
of 𝑒. Let 𝑒′ = 𝑐.allInstances(), where 𝑐 is a class type.
map𝑒 (𝑙) = Then,
SELECT 𝑙 as res, 1 as val
map𝑒 (𝑐.allInstances())=
SELECT 𝑐_id as res, 1 as val FROM 𝑐
Variables
Let 𝑒 be an OCL expression. Let 𝑒′ be a subexpression of size-expressions
𝑒. Let 𝑒′ = 𝑣, where 𝑣 is a variable. Then,
Let 𝑒 be an OCL expression. Let 𝑒′ be a subexpression of 𝑒.
map𝑒 (𝑣) = Let 𝑒′ = 𝑠→size(). We need to consider the following
SELECT cases:
TEMP_dmn.res as res,
TEMP_dmn.res as ref_𝑣 , • FVars(𝑒′ ) = ∅. Then,
TEMP_dmn.val as val,
map𝑒 (𝑠→size()) =
TEMP_dmn.ref_𝑣 ′ as ref_𝑣 ′ ,
SELECT
for each 𝑣 ∈ SVars𝑒 (src(𝑣))
′
COUNT(*) as res,
FROM (map𝑒 (src(𝑣))) as TEMP_dmn
1 as val
FROM (map𝑒 (𝑠)) AS TEMP_src.
Attribute expressions
• FVars(𝑒′ ) ̸= ∅, Then,
Let 𝑒 be an OCL expression. Let 𝑒′ be a subexpression of
𝑒. Let 𝑒′ = 𝑣 .att, where 𝑣 is a variable of class-type 𝑐 map𝑒 (𝑠→size()) =
and att is an attribute of the class 𝑐. Then, SELECT
CASE TEMP_src.val = 0
map𝑒 (𝑣 .att) = WHEN 1 THEN 0
SELECT ELSE COUNT(*) END as res,
TEMP_src.ref_𝑣 as ref_𝑣, exists-expressions
for each 𝑣 ∈ SVars𝑒 (𝑠)
Let 𝑒 be an OCL2PSQL-expression. Let 𝑒′ be a subexpres-
1 as val
sion of 𝑒. Let 𝑒′ = 𝑠→exists(𝑣 | 𝑏). For our competi-
FROM (map𝑒 (𝑠)) AS TEMP_src
tion, we only need to consider the following cases:
GROUP BY
TEMP_src.ref_𝑣,
• 𝑣 ∈ FVars(𝑏) and FVars(𝑒′ ) = ∅. Then
for each 𝑣 ∈ SVars𝑒 (𝑠),
TEMP_src.val SELECT
COUNT(*) > 0 as res,
=-expressions (correspondingly, and-expressions) 1 as val
FROM (map𝑒 (𝑏)) as TEMP_body
Let 𝑒 be an OCL expression. Let 𝑒′ be a subexpression of WHERE TEMP_body.res = 1
𝑒. Let 𝑒′ = (𝑙=𝑟). For our competition, we only need to
consider the following cases: • 𝑣 ∈ FVars(𝑏) and FVars(𝑒′ ) ̸= ∅. Then
• FVars(𝑙) = FVars(𝑟) = ∅. Then, SELECT
CASE TEMP_body.ref_𝑣 IS NULL
map𝑒 (𝑙=𝑟) = WHEN 1 THEN 0
SELECT ELSE TEMP_body.res END as res,
TEMP_left.res = TEMP_right.res as res, 1 as val,
1 as val TEMP_src.ref_𝑣 ′ as ref_𝑣 ′ ,
FROM for each 𝑣 ′ ∈ SVars(𝑠),
(map𝑒 (𝑙)) AS TEMP_left, TEMP_body.ref_𝑣 ′ as ref_𝑣 ′ ,
(map𝑒 (𝑟)) AS TEMP_right for each 𝑣 ′ ∈ SVars(𝑏) ∖ SVars(𝑠) ∖ {𝑣}
FROM (map𝑒 (𝑠)) as TEMP_src
• FVars(𝑙) ̸= ∅, SVars(𝑟) ⊆ SVars(𝑙). Then, LEFT JOIN (
map𝑒 (𝑙=𝑟) = SELECT COUNT(*) > 0 as res,
SELECT TEMP_body.ref_𝑣 ′ as ref_𝑣 ′ ,
TEMP_left.res = TEMP_right.res as res, for each 𝑣 ′ ∈ SVars(𝑏)
CASE FROM (map𝑒 (𝑏)) as TEMP_body
TEMP_left.val = 0 OR TEMP_right.val = 0 WHERE TEMP_body.res = 1
WHEN 1 THEN 0 GROUP BY TEMP_body.ref_𝑣 ′ ,
ELSE 1 END as val, for each 𝑣 ′ ∈ SVars(𝑏) ∖ {𝑣}
TEMP_left.ref_𝑣 as ref_𝑣, ) as TEMP_body
for each 𝑣 ∈ SVars𝑒 (𝑙) ON TEMP_src.ref_𝑣 ′ = TEMP_body.ref_𝑣 ′ ,
FROM (map𝑒 (𝑙)) AS TEMP_left for each 𝑣 ′ ∈ SVars(𝑠)
[LEFT] JOIN (map𝑒 (𝑟)) AS TEMP_right • 𝑣 ̸∈ FVars(𝑏). Similarly, but the source and the
[ON TEMP_left.ref_𝑣 = TEMP_right.ref_𝑣, body would need to be joined using a JOIN-clause
for each 𝑣 ∈ SVars𝑒 (𝑙) ∩ SVars𝑒 (𝑟)] without the group-clause (and possibly, changing
left join to simple join, if there are no common
collect-expressions variables between source and body).
Let 𝑒 be an OCL expression. Let 𝑒′ be a subexpression of
𝑒. Let 𝑒′ = 𝑠→collect(𝑣 | 𝑏). For our competition, we B. Corrigendum
only need to consider the following case:
In [6, Section 4.3], in the second case considered in the
• 𝑣 ∈ FVars(𝑏) and FVars(𝑒′ ) = ∅. definition of the mapping for Exists-expressions instead
of:
SELECT TEMP_body.res as res,
TEMP_body.val as val, • 𝑣 ∈ FVars(𝑏) and FVars(𝑒′ ) ̸= ∅. Then
FROM (map𝑒 (𝑏)) as TEMP_body
SELECT
• 𝑣 ̸∈ FVars(𝑏). Similarly, but the source and the CASE TEMP_src.ref_𝑣 IS NULL
body would need to be joined using a JOIN-clause. WHEN 1 THEN 0
ELSE TEMP.res END as res,
...
LEFT JOIN ( expressions whose source is a VariableExp expression.
SELECT COUNT(*) > 0 as res, For example, given 𝑐 is a Variable of type Car, 𝑐.color
TEMP_body.ref_𝑣 ′ as ref_𝑣 ′ , is a PropertyCallExp expression to get the color of the
for each 𝑣 ′ ∈ SVars(𝑏) ∖ {𝑣} Car.
An AssociationClassCallExp represents an expres-
it should read: sion that calls an association-end of a source object.
The former is represented by an AssociationEnd and
• 𝑣 ∈ FVars(𝑏) and FVars(𝑒′ ) ̸= ∅. Then the latter is represented by an Entity; both belong to
SELECT the OCL2PSQL metamodel for data models. OCL2PSQL
CASE TEMP_body.ref_𝑣 IS NULL only supports AssociationClassCallExp expressions
WHEN 1 THEN 0 whose source is a VariableExp expression. For ex-
ELSE TEMP_body.res END as res, ample, given 𝑐 is a Variable of type Car and own-
... ers is the association-end of Car, then 𝑐.owners is a
LEFT JOIN ( AssociationClassCallExp expression to get the owners
SELECT COUNT(*) > 0 as res, of the Car.
TEMP_body.ref_𝑣 as ref_𝑣 ,
′ ′ An IteratorExp represents an expression that calls
for each 𝑣 ′ ∈ SVars(𝑏) an iterator over a source collection. The body of the
iterator is represented by an OclExpression expression.
And similar errors should be corrected in [6, Sec- The iterator-variable is represented by a Variable. In this
tion 4.3], in the second case considered in the definition competition, we support the following kinds of iterators:
of the mapping for forAll-expressions. exists, and collect.
C. The OCL expression D. The SQL-select statement
metamodel metamodel
In a nutshell, OclExpression is the root element. It is The SelectStatement is the root element: it contains
an abstract class. An OclExpression can be either a lit- a PlainSelect, which represents the body of the SQL-
eral expression, a CallExp, a VariableExp, or a TypeExp. select statement.
Next, we describe each of these classes. A PlainSelect may contain the following objects: a
A literal expression represents a literal value. In list of selItems elements, each of type SelectItem; a
our case, it can be either an IntegerLiteralExp, a fromItem element of type FromItem; a whereExp element
StringLiteralExp, or a BooleanLiteralExp. Each of of type Expression; a list of joins elements of type Join;
these classes contains an attribute to represent an integer, and a groupBy element of type GroupByElement. Next,
a string, or a Boolean literal value, respectively. we describe each of these classes:
A TypeExp represents a type expression. It contains a A SelectItem represents a column that the select-state-
reference referredType of type Entity, which belongs ment retrieves. It contains an Expression element and
to the OCL2PSQL metamodel for data models. an Alias element.
A VariableExp represents a variable expression. A FromItem element represents the table or subselect
A CallExp represents an expression that consists of from which the SQL-select statement retrieves informa-
calling a feature over a source, which is represented by tion. It is an interface. A FromItem element can be either
an OclExpression. CallExp is an abstract class: it can a Table or a SubSelect. The former represents a table.
be either an OperationCallExp, a PropertyCallExp, an The latter represents a subselect. This element will be cre-
AssociationClassCallExp, or an IteratorExp. ated on the fly, i.e., when the FROM-clause is encountered.
An OperationCallExp represents an expression that A whereExp reference of type Expression represents
calls an operation over its source, possibly with argu- a where-clause.
ments. For our competition, we only consider the equality A Join element represents a join with a rightItem of
comparison, i.e., =; conjunctive operation, i.e., AND; and type FromItem, possibly according to its element onExp
two operations on collections, i.e., allInstances() and of type Expression.
size(). A GroupByElement element represents a groupby-
A PropertyCallExp represents an expression that calls clause. It contains groupByExps, a list of objects of type
an attribute of a source object. The former is repre- Expression that defines how the rows are to be grouped.
sented by an Attribute and the latter is represented by Expression is an interface element which plays many
an Entity; both belong to the OCL2PSQL metamodel for roles in a SQL-select statement. For the sake of simplicity,
data models. OCL2PSQL only supports PropertyCallExp the realizations of Expression are hidden from Figure 4.
Next, we describe these realizations which our cases will
need.
A LongValue and a StringValue represent an integer
literal and a string literal in SQL, respectively.
A Column represents a column of a table in SQL.
A BinaryExpression represents a binary expression
in SQL. It contains a leftExp element and a rightExp
element, both of type Expression. BinaryExpression
is an abstract class. It can be either a logical expres-
sion, (OrExpression or AndExpression) , or a compar-
ison expression (EqualsToExpression or GreaterThan-
Expression).
An IsNullExpression represents an IS NULL ex-
pression in SQL. It contains an Exp element of type
Expression.
A CountAllFunction represents a COUNT(*) expres-
sion in SQL.
A CaseExpression represents a CASE-expression in
SQL. It contains whenClauses, a list of objects of type
WhenClause, representing WHEN clauses in SQL.
A SubSelect represents a subselect-expression in SQL.
It contains a selectBody of type PlainSelect .