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 .