=Paper=
{{Paper
|id=Vol-3089/ttc21_paper_ocl2sql_Hinkel_solution
|storemode=property
|title=An NMF solution to the TTC 2021 OCL to SQL case
|pdfUrl=https://ceur-ws.org/Vol-3089/ttc21_paper13_ocl2sql_Hinkel_solution.pdf
|volume=Vol-3089
|authors=Georg Hinkel
|dblpUrl=https://dblp.org/rec/conf/ttc/Hinkel21c
}}
==An NMF solution to the TTC 2021 OCL to SQL case==
https://ceur-ws.org/Vol-3089/ttc21_paper13_ocl2sql_Hinkel_solution.pdf
An NMF solution to the TTC 2021 OCL to SQL case
Georg Hinkel1
1
Am Rathaus 4b, 65207 Wiesbaden, Germany
Abstract
Recent advancements in modern general-purpose programming languages challenge the often stated assumption that dedi-
cated model transformation languages are required to express model transformations, especially when not only few of the
typical properties of model transformations are required. In particular, the OCL to SQL case at the Transformation Tool
Contest (TTC) 2021 asks for solutions to a transformation from OCL, a typical tree model. This paper presents a solution to
this case using dynamic C# code, but without any dedicated model transformation language, only using NMF for the model
representation. The transformation tools of NMF are not used because the case does not fall under NMFs definition of a
model transformation problem and we discuss the reasons for that.
Keywords
Model Queries, OCL, SQL
1. Introduction statements. For this purpose, metamodels are provided
to understand OCL and SQL as models.
In a frequently cited paper [1], Sendall and Kozaczyn- The .NET Modeling Framework [3] is a framework for
ski state that dedicated model transformation languages model-driven engineering on the .NET platform and pa-
should be used for most model transformation activities per presents a solution of the OCL to SQL case using NMF.
because general-purpose languages are less suited for this NMF even has multiple model transformation languages,
kind of tasks. Particularly in recent years, this assump- but these do not fit to the problem at hand. Instead, this
tion has become very popular [2]. However, modern paper presents a solution using plain C# with massive
general-purpose programming languages have signifi- usage of the Dynamic Language Runtime.
cantly evolved since the paper from Sendall and Koza- In the remainder of the paper, I first briefly introduce
czynski was published and it is fair to doubt whether that the Dynamic Language Runtime that is heavily used for
statement is still correct, in particular since surprisingly the solution in this paper in Section 2. Section 3 presents
few empirical evidence is given to support the assump- the solution. Finally, Section 4 discusses the solution.
tion [2].
One of the arguments in favor of model transformation
languages is the simple and usually type-safe access to a 2. Dynamic C#
trace model. However, the trace is only important when
model elements are referenced more than once and there The solution makes use of the dynamic language runtime
is an important category of models where model elements (DLR) that is part of the .NET Framework but perhaps
are typically only referenced only once, namely trees, for not so widely known. The idea of the DLR is to allow el-
instance expression trees. ements of dynamic programming languages in the scope
A particularly important expression tree model is the of the .NET runtime. These features are also available in
Object Constraint Language (OCL). OCL is an important C#, in particular the ability for late binding. That is, by
language to denote expressions based on models in order converting variables to dynamics, the compiler sees that
to formulate constraints, but it is also used to specify method calls are only resolved at runtime, based on the
queries. If the models are stored in a database, it is de- usual C# overload selection principles which the compiler
sirable to translate these queries to SQL statements such attaches to make them available at runtime. However,
that they can be processed directly by the database. especially when passing dynamic objects only as param-
Using models for OCL and SQL, the OCL to SQL case at eters, the compiler is able to calculate the set of methods
the Transformation Tool Contest (TTC) asks tool authors that are candidates for a certain call already, which makes
to transform models of OCL queries into models of SQL the actual call very efficient. Further, integrated editors
such as Visual Studio even show errors, if no suitable
TTC’21: Transformation Tool Contest, Part of the Software candidates could be found, the reference count counts all
Technologies: Applications and Foundations (STAF) federated possible methods that the call could be resolved to and
conferences, Eds. A. Boronat, A. García-Domínguez, and G. Hinkel, the "‘Go To Definition"’ feature lists all of them.
25 June 2021, Bergen, Norway (online).
" georg.hinkel@gmail.com (G. Hinkel)
© 2021 Copyright for this paper by its authors. Use permitted under Creative
Commons License Attribution 4.0 International (CC BY 4.0).
CEUR
Workshop
Proceedings
http://ceur-ws.org
ISSN 1613-0073
CEUR Workshop Proceedings (CEUR-WS.org)
3. Solution other OCL expressions require to modify the context in
which they are called.
To discuss the solution, I first give an overview in Section Listing 1 shows how this applies to boolean expres-
3.1 before Sections 3.2, 3.3 and 3.4 go into details for the sions where the literal is simply converted to an Equal-
actual translation process, pruning and printing the SQL sToExpression, either that 1 = 1 for true or 1 = 0 for
statement models to strings. false.
1 private IExpression GetExpression(SelectContext context,
3.1. Overview BooleanLiteralExp booleanLiteral) {
2 return new EqualsToExpression {
3 LeftExp = new LongValue { Value = 1 },
NMF does have a model transformation language (NTL, 4 RightExp = new LongValue {
[4, 5])1 but I decided not to use it for this case. Why? Ac- 5 Value = booleanLiteral.BooleanValue.GetValueOrDefault()
? 1 : 0
cording to the philosophy of NTL, the biggest challenge 6 }
of a model transformation is to establish an isomorphism 7 };
}
between source and target models that provides a trac- 8
ing functionality and that is used to ensure that certain Listing 1: Translating simple boolean expressions
input model elements are only transformed once and not
once for every reference. This is because maintaining Calls to GetExpression can be nested as denoted in
such a trace is difficult in general-purpose programming Listing 2 that depicts how to translate And call expres-
languages because it requires a lot of bookkeeping – one sions.
essentially requires a dedicated hashtable for each type
1 return new AndExpression {
and as soon as inheritance is in place, things start to 2 LeftExp = GetExpression(context, (dynamic)callExpression.
become messy. Source),
3 RightExp = GetExpression(context, (dynamic)callExpression.
However, both the OCL and the SQL metamodels are Argument[0])
essentially expression models that have a tree structure 4 };
with very few cross-references, even none in the case of
Listing 2: Nesting translation calls to translate an And
SQL. Because NMF takes containments very seriously
call expression
and model elements must always have exactly one parent,
trying to add an existing model element to a containment More interesting is the handling of the AllInstances
reference of another model element removes it from its method as depicted in Listing 3. Because it does not
old container. Therefore, not only that a trace is not directly have an impact on the result, we return a null
needed, it is even counter-productive. reference, but this time change the context and set it to
Since the availability of a trace is not an argument the table with the name of the referred type.
in favor of NTL, the question is whether NTL still adds
value against a pure general-purpose code solution and I 1 privateIEntity IExpression GetAllInstances(SelectContext context,
referredType) {
believe the answer is plainly no. Especially using features 2 var table = new Table { Name = referredType.Name };
if (context.Body.FromItem == null) {
like the DLR, the late binding can be implemented directly 34 context.Body.FromItem = table;
in C# with concepts known by a lot more developers and 5 } else {
6 context.Body.Joins.Add(new Join { RightItem = table });
therefore easier to understand and better supported by 7 }
tools. 8 return null;
Therefore, I decided to create a solution to the case 9 }
using plain C# code making use of DLR features. Listing 3: Handling the AllInstances method
3.2. Translator To handle iterators, we need to determine how to bind
the variable. For this, the considered subset of the OCL
The general idea of the solution is to translate the OCL language knows to collections that can be iterated: A
expressions in a (mutable) context to SQL expressions. collection returned by the AllInstances method or an as-
This context includes a notion of open variables and their sociation of a different variable. In both cases, we add an
types as well as the body of the enclosing SQL statement open variable to the select context while calculating the
and a counter of temporary tables created for a statement expression for the iterator body and remove it afterwards.
in order that they do not get confused. While simple With the iterators in place, we can implement the Prop-
expressions can be mapped to simple SQL expressions, ertyCallExp expressions as depicted in Listing 4.
The (syntactically allowed) case that a property of a
1
In fact, NMF even has two model transformation languages property is queried would require adding more joins,
where NTL is the rather imperative approach. NMF Synchroniza-
which is ignored in the current solution, particularly
tions [6] is more declarative and targets incremental and/or bidirec-
tional model transformations. given that this was not required for the reference inputs.
1 private IExpression GetExpression(SelectContext context, this, we create a temporary sub-select model with the
PropertyCallExp propertyCall) {
2 switch (propertyCall.Source) {
current context query inside, group that query by all
3 case VariableExp variableRef: context variables and return a column of the temporary
4 var table = context.Variables[variableRef.
ReferredVariable.Name];
table. However, because this eliminates the open vari-
5 return new Column { ables that might be needed elsewhere, we group the result
6 Table = new Table {
7 Name = table,
by all open variables and add these variables to the result.
8 Alias = new Alias { To make them available in the sub-select, which is the
9 Name = variableRef.ReferredVariable.Name
10 }
new context select statement, we add joins for each open
11 }, variable from their original table.
12 Name = propertyCall.ReferredProperty.Name
13 };
To see this, consider an extension of stage 8 where we
14 default: reuse the open variable c as depicted in Listing 6. We
15 throw new NotSupportedException();
16 }
refer to this query later on as stage 9.
17 }
1 Car.allInstances()->exists(c|c.owners->exists(p|p.name = ’
Peter’) and c.color=’black’)
Listing 4: Transformation of a PropertyCallExp
Listing 6: Slight extension of the stage 8 query that reuses
the open variable c
1 private IExpression GetExpression(SelectContext context,
AssociationClassCallExp association) { Note, the exists method is treated as a filter condi-
2 switch (association.Source) {
3 case VariableExp variableRef:
tion and an additional size aggregate. We need to keep
4 var variable = variableRef.ReferredVariable.Name; the variable c in order to be able to check whether the
5 var associationEnd = association.
ReferredAssociationEnds;
color is black.
6 var alias = variable + "_" + associationEnd.Association
;
7 context.Body.Joins.Add(new Join { 3.3. Pruning
8 Left = false,
9 RightItem = new Table { The resulting SQL statement may join tables that are not
10 Name = associationEnd.Association,
11 Alias = new Alias { Name = alias } actually needed, e.g. when joined tables are not actually
12 }, needed. This gets apparent in challenge 8, where the
13 OnExp = new EqualsToExpression {
14 LeftExp = new Column { open variable c is only used to calculate the size, but
15 Table = new Table { given that we are not interested in any of its properties,
16 Name = context.Variables[variable],
17 Alias = new Alias { Name = variable } we do not actually need to join the Car table once again
18 }, after the initial context is gone.
19 Name = associationEnd.Name,
20 },
1 if (selectBody.SelItems.Select(s => s.Exp).OfType<
21 RightExp = new Column {
CountAllFunction>().Any()) {
22 Table = new Table {
2 return;
23 Name = associationEnd.Association,
3 }
24 Alias = new Alias { Name = alias }
4 var expressionsToCheck = selectBody.SelItems.Select(s => s.
25 },
Exp).ToList();
26 Name = context.Variables[variable] + "_id",
5 if (selectBody.WhereExp != null) {
27 }
6 expressionsToCheck.Add(selectBody.WhereExp);
28 }
7 }
29 });
8 var usedAliases = (from selectExp in expressionsToCheck
30 context.LastJoin = Tuple.Create(variable,
9 from column in selectExp.Descendants().OfType<
associationEnd);
Column>()
31 return null;
10 select column.Table.Alias.Name).Distinct();
32 default:
11 for (int i = selectBody.Joins.Count - 1; i >= 0; i--) {
33 throw new NotSupportedException();
12 var join = selectBody.Joins[i];
34 }
13 if (join.RightItem is Table table && !usedAliases.Contains
35 }
(table.Alias.Name)) {
14 selectBody.Joins.RemoveAt(i);
Listing 5: Transformation of an AssociationCallExp 15 }
16 }
17 if (selectBody.FromItem is SubSelect subSelect) {
18 Prune(subSelect.SelectBody);
19 }
In case of an AssociationCallExp, we register the
Listing 7: Pruning the joins of the resulting SQL
join as last join in the context and add the join to the
statement
current select context as depicted in Listing 5.
Perhaps the most interesting expression is the method The implementation of the pruning is depicted in List-
to return the sizes. This is because the aggregate dras- ing 7. Aggregate (sub-)queries are not pruned because
tically changes the execution of the query and we need removing joins changes the number of result elements
to return rows for actually empty combinations. To do and thus the result get incorrect. Otherwise, we select all
table aliases that appear either in the selection or in the is tested with larger databases. First, the transformation
where clause and remove all joins that join tables that scheme used inside this paper differs from the original
are not actually needed. Lastly, we recurse in case the OCL2PSQL transformation scheme [7] and a correctness
source is a sub-query. proof for the transformation scheme presented used in
this paper is correct is out of scope for this paper. The
3.4. Printer reason that I did not use the OCL2PSQL mapping is that
I am generally not satisfied with the verbosity of the SQL
The solution to print the SQL statement models to strings statements generated by it, whereas the SQL statements
works similar by using the DLR to dispatch the different generated by the transformation scheme presented here
object types and then print them to strings. are much easier to comprehend in my opinion. The val-
1 public static string Print(IPlainSelect selectBody) {
idation of the mapping scheme presented here will be
2 var resultBuilder = new StringBuilder(); subject of future work. However, this different mapping
3 resultBuilder.Append($"SELECT {string.Join(", ",
selectBody.SelItems.Select(Print))}");
scheme also makes a comparison with alternative im-
4 if (selectBody.FromItem != null) { plementations that stick more closely to the OCL2PSQL
5 resultBuilder.Append($" FROM {PrintFrom((dynamic)
selectBody.FromItem)}");
mapping more difficult.
6 } In my opinion, the solution shows well how to use the
7 foreach (var join in selectBody.Joins) {
8 resultBuilder.Append($" {(join.Left.GetValueOrDefault() ?
Dynamic Language Runtime available in C# to perform
"LEFT" : "INNER")} JOIN {PrintFrom((dynamic)join. dispatch on parameters, a frequent selling argument of
RightItem)} ON {PrintExpression((dynamic)join.
OnExp)}");
model transformation languages apart from access to
9 } trace, incrementality and bidirectionality. The latter two
10 if (selectBody.WhereExp != null) {
11 resultBuilder.Append($" WHERE {PrintExpression((dynamic)
properties require a very declarative way of specifying
selectBody.WhereExp)}"); model transformations such as exemplified e.g. by NMF
12 }
13 if (selectBody.GroupBy != null) {
Synchronizations [6], but the transformation at hand is
14 resultBuilder.Append($" GROUP BY {string.Join(", ", written in a very imperative style. Anyways, incremental
selectBody.GroupBy.GroupByExps.Select(exp =>
PrintExpression((dynamic)exp)))}");
change propagation is not relevant for the case at hand,
15 } since changes the main purpose of the transformation is
16 return resultBuilder.ToString();
17 }
to execute the resulting SQL statement and analyze the
result data. Bidirectionality would be very interesting to
Listing 8: Printing the resulting SQL statement using the reverse-engineer SQL statements in order to make them
DLR more understandable, but it is unclear to what extend this
is possible at all. Given that the trace is not important in
As an example, the method to print the actual SQL this case, there is just no reason not to use the Dynamic
statement is depicted in Listing 8. The query printer Language Runtime, especially taking into account the
makes intensive use of the string interpolation available very good performance results.
in C#. A further advantage of a solution in plain C# is that
it can be easily integrated into model transformations
4. Evaluation and discussion written in internal DSLs using C# as a host language, in
particular NTL, since usually, not the entire model forms
The solution has been integrated into the benchmark a tree structure and hence, access to the trace is required.
framework. In order to get an insight on the generated This integration, however, would be much more dif-
SQL queries, the resulting queries are depicted in Listing ficult when incrementality was important despite incre-
9. mentalization systems like NMF Expressions [8] that op-
Notably, to reduce the influence of just-in-time com- erate on C# code (or models thereof). It will be subject of
pilation, I actually run the solution 100 times on a Intel future work how transformations like the mapping from
Core i7-8550U CPU clocked at 1.99 Ghz in a system with OCL to SQL can be supported when incremental change
8GB RAM running Windows 10 and divide the result by propagation is required.
1002 . The resulting transformation times then are in the
range of up to 1.4ms for the stage 8 query and in the
sub-millisecond area for most of the other queries and
References
thus is negligible. The time for the test lies around 20ms [1] S. Sendall, W. Kozaczynski, Model transformation
but that certainly gets more interesting once the solution the heart and soul of model-driven software devel-
opment, Technical Report, 2003.
2
Actually, I do not because the smallest time unit in .NET hap- [2] S. Götz, M. Tichy, R. Groner, Claimed advantages and
pens to be 100ns, so we merge the division by 100 with the multipli- disadvantages of (dedicated) model transformation
cation by 100.
1 ***** Stage#0 ***
2 +++ challenge#0: SQL: SELECT 2 res
3 +++ challenge#1: SQL: SELECT ’Peter’ res
4 +++ challenge#2: SQL: SELECT 1 = 1 res
5 ***** Stage#1 ***
6 +++ challenge#0: SQL: SELECT 2 = 3 res
7 +++ challenge#1: SQL: SELECT ’Peter’ = ’Peter’ res
8 +++ challenge#2: SQL: SELECT 1 = 1 and 1 = 1 res
9 ***** Stage#2 ***
10 +++ challenge#0: SQL: SELECT Car_id res FROM Car
11 ***** Stage#3 ***
12 +++ challenge#0: SQL: SELECT tmp1.res res FROM (SELECT COUNT(*) res FROM Car) AS tmp1
13 +++ challenge#1: SQL: SELECT tmp1.res = 1 res FROM (SELECT COUNT(*) res FROM Car) AS tmp1
14 ***** Stage#4 ***
15 +++ challenge#0: SQL: SELECT 5 res FROM Car AS c
16 +++ challenge#1: SQL: SELECT c.Car_id res FROM Car AS c
17 +++ challenge#2: SQL: SELECT 1 = 0 res FROM Car AS c
18 ***** Stage#5 ***
19 +++ challenge#0: SQL: SELECT c.color res FROM Car AS c
20 +++ challenge#1: SQL: SELECT c.color = ’black’ res FROM Car AS c
21 ***** Stage#6 ***
22 +++ challenge#0: SQL: SELECT tmp1.res res FROM (SELECT c.Car_id, COUNT(c_Ownership.ownedCars) res FROM Car AS c LEFT JOIN
Ownership AS c_Ownership ON c.Car_id = c_Ownership.ownedCars GROUP BY c.Car_id) AS tmp1
23 +++ challenge#1: SQL: SELECT tmp1.res = 0 res FROM (SELECT c.Car_id, COUNT(c_Ownership.ownedCars) res FROM Car AS c LEFT JOIN
Ownership AS c_Ownership ON c.Car_id = c_Ownership.ownedCars GROUP BY c.Car_id) AS tmp1
24 ***** Stage#7 ***
25 +++ challenge#0: SQL: SELECT tmp1.res > 0 res FROM (SELECT COUNT(*) res FROM Car AS c WHERE 1 = 1) AS tmp1
26 +++ challenge#1: SELECT tmp1.res > 0 res FROM (SELECT COUNT(*) res FROM Car AS c WHERE 1 = 0) AS tmp1
27 +++ challenge#2: SQL: SELECT tmp1.res > 0 res FROM (SELECT COUNT(*) res FROM Car AS c WHERE c.color = ’black’) AS tmp1
28 +++ challenge#3: SQL: SELECT tmp2.res > 0 res FROM (SELECT COUNT(*) res FROM (SELECT c.Car_id, COUNT(c_Ownership.ownedCars)
res FROM Car AS c LEFT JOIN Ownership AS c_Ownership ON c.Car_id = c_Ownership.ownedCars GROUP BY c.Car_id) AS tmp1
WHERE tmp1.res = 1) AS tmp2
29 ***** Stage#8 ***
30 +++ challenge#0: SQL: SELECT tmp2.res > 0 res FROM (SELECT COUNT(*) res FROM (SELECT c.Car_id, COUNT(p.Person_id) res FROM
Car AS c LEFT JOIN Ownership AS c_Ownership ON c.Car_id = c_Ownership.ownedCars LEFT JOIN Person AS p ON c_Ownership.
ownedCars = p.Person_id WHERE p.name = ’Peter’ GROUP BY c.Car_id) AS tmp1 WHERE tmp1.res > 0) AS tmp2
Listing 9: Resulting SQL Statements
languages: a systematic literature review, Software [8] G. Hinkel, R. Heinrich, R. Reussner, An extensible
and Systems Modeling 20 (2021) 469–503. approach to implicit incremental model analyses,
[3] G. Hinkel, NMF: A multi-platform Modeling Frame- Software & Systems Modeling 18 (2019) 3151–3187.
work, in: A. Rensink, J. S. Cuadrado (Eds.), Theory
and Practice of Model Transformations: 11th Interna-
tional Conference, ICMT 2018, Held as Part of STAF
2018, Toulouse, France, June 25-29, 2018. Proceed-
ings, Springer International Publishing, Cham, 2018,
pp. 184–194.
[4] G. Hinkel, An approach to maintainable model trans-
formations using an internal DSL, Master’s thesis,
Karlsruhe Institute of Technology, 2013.
[5] G. Hinkel, T. Goldschmidt, E. Burger, R. Reuss-
ner, Using Internal Domain-Specific Languages
to Inherit Tool Support and Modularity for Model
Transformations, Software & Systems Model-
ing (2017) 1–27. URL: http://rdcu.be/oTED. doi:10.
1007/s10270-017-0578-9.
[6] G. Hinkel, E. Burger, Change Propagation and Bidi-
rectionality in Internal Transformation DSLs, Soft-
ware & Systems Modeling (2017). URL: http://rdcu.
be/u9PT. doi:10.1007/s10270-017-0617-6.
[7] H. N. P. Bao, M. Clavel, Ocl2psql: An ocl-to-sql
code-generator for model-driven engineering, in: In-
ternational Conference on Future Data and Security
Engineering, Springer, 2019, pp. 185–203.