Recent advancements in modern general-purpose programming languages challenge the often stated assumption that dedicated 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.
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 pashould 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 is an important category of models where model elements are typically only referenced only once, namely trees, for instance expression trees.
A particularly important expression tree model is the Object Constraint Language (OCL). OCL is an important language to denote expressions based on models in order to formulate constraints, but it is also used to specify queries. If the models are stored in a database, it is desirable to translate these queries to SQL statements such that they can be processed directly by the database.
Using models for OCL and SQL, the OCL to SQL case at the Transformation Tool Contest (TTC) asks tool authors to transform models of OCL queries into models of SQL The solution makes use of the dynamic language runtime (DLR) that is part of the .NET Framework but perhaps not so widely known. The idea of the DLR is to allow elements of dynamic programming languages in the scope of the .NET runtime. These features are also available in C#, in particular the ability for late binding. That is, by converting variables to dynamics, the compiler sees that method calls are only resolved at runtime, based on the usual C# overload selection principles which the compiler attaches to make them available at runtime. However, especially when passing dynamic objects only as parameters, the compiler is able to calculate the set of methods that are candidates for a certain call already, which makes the actual call very eficient. Further, integrated editors such as Visual Studio even show errors, if no suitable candidates could be found, the reference count counts all possible methods that the call could be resolved to and the "‘Go To Definition"’ feature lists all of them.
To discuss the solution, I first give an overview in Section 3.1 before Sections 3.2, 3.3 and 3.4 go into details for the actual translation process, pruning and printing the SQL statement models to strings. other OCL expressions require to modify the context in which they are called.
Listing 1 shows how this applies to boolean expressions where the literal is simply converted to an EqualsToExpression, either that 1 = 1 for true or 1 = 0 for false.
Calls to GetExpression can be nested as denoted in Listing 2 that depicts how to translate And call expressions. 1 private IExpression GetExpression(SelectContext context,
BooleanLiteralExp booleanLiteral) { return new EqualsToExpression { LeftExp = new LongValue { Value = 1 }, RightExp = new LongValue { Value = booleanLiteral.BooleanValue.GetValueOrDefault() ? 1 : 0 become messy.
However, both the OCL and the SQL metamodels are 3 essentially expression models that have a tree structure 4 }; with very few cross-references, even none in the case of SQL. Because NMF takes containments very seriously and model elements must always have exactly one parent, trying to add an existing model element to a containment reference of another model element removes it from its old container. Therefore, not only that a trace is not needed, it is even counter-productive.
Since the availability of a trace is not an argument in favor of NTL, the question is whether NTL still adds value against a pure general-purpose code solution and I 1 private IExpression GetAllInstances(SelectContext context, believe the answer is plainly no. Especially using features 2 var tIaEbnlteit=y nreewfeTrarbeldeTy{peN)am{e = referredType.Name }; like the DLR, the late binding can be implemented directly 3 if (context.Body.FromItem == null) { in C# with concepts known by a lot more developers and 45 } ceolnsteex{t.Body.FromItem = table; therefore easier to understand and better supported by 6 context.Body.Joins.Add(new Join { RightItem = table }); tools. 87 }return null;
Therefore, I decided to create a solution to the case 9 } using plain C# code making use of DLR features.
More interesting is the handling of the AllInstances method as depicted in Listing 3. Because it does not directly have an impact on the result, we return a null reference, but this time change the context and set it to the table with the name of the referred type.
Listing 2: Nesting translation calls to translate an And
call expression Listing 3: Handling the AllInstances method 1In fact, NMF even has two model transformation languages where NTL is the rather imperative approach. NMF Synchronizations [6] is more declarative and targets incremental and/or bidirectional model transformations. 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 asThis context includes a notion of open variables and their sociation of a diferent 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 Propexpressions can be mapped to simple SQL expressions, ertyCallExp expressions as depicted in Listing 4.
The (syntactically allowed) case that a property of a property is queried would require adding more joins, which is ignored in the current solution, particularly given that this was not required for the reference inputs. private IExpression GetExpression(SelectContext context,
PropertyCallExp propertyCall) { switch (propertyCall.Source) { case VariableExp variableRef: var table = context.Variables[variableRef.
ReferredVariable.Name]; return new Column { Table = new Table { Name = table,
Alias = new Alias { Name = variableRef.ReferredVariable.Name } }, Name = propertyCall.ReferredProperty.Name }; default: throw new NotSupportedException(); Listing 4: Transformation of a PropertyCallExp private IExpression GetExpression(SelectContext context,
AssociationClassCallExp association) { switch (association.Source) { case VariableExp variableRef: var variable = variableRef.ReferredVariable.Name; var associationEnd = association.
ReferredAssociationEnds; var alias = variable + "_" + associationEnd.Association
; context.Body.Joins.Add(new Join { Left = false, RightItem = new Table { Name = associationEnd.Association, Alias = new Alias { Name = alias } }, OnExp = new EqualsToExpression { LeftExp = new Column { Table = new Table { Name = context.Variables[variable], Alias = new Alias { Name = variable } }, Name = associationEnd.Name, }, RightExp = new Column { Table = new Table { Name = associationEnd.Association, Alias = new Alias { Name = alias } }, Name = context.Variables[variable] + "_id", } } }); context.LastJoin = Tuple.Create(variable,
associationEnd); return null; default: throw new NotSupportedException();
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 Listto 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 this, we create a temporary sub-select model with the current context query inside, group that query by all context variables and return a column of the temporary table. However, because this eliminates the open variables that might be needed elsewhere, we group the result by all open variables and add these variables to the result.
To make them available in the sub-select, which is the new context select statement, we add joins for each open variable from their original table.
To see this, consider an extension of stage 8 where we reuse the open variable c as depicted in Listing 6. We refer to this query later on as stage 9. 1
Car.allInstances()->exists(c|c.owners->exists(p|p.name = ’
Peter’) and c.color=’black’) Listing 6: Slight extension of the stage 8 query that reuses
the open variable c
Note, the exists method is treated as a filter condition and an additional size aggregate. We need to keep the variable c in order to be able to check whether the color is black.
The resulting SQL statement may join tables that are not actually needed, e.g. when joined tables are not actually needed. This gets apparent in challenge 8, where the open variable c is only used to calculate the size, but given that we are not interested in any of its properties, we do not actually need to join the Car table once again after the initial context is gone.
if (selectBody.SelItems.Select(s => s.Exp).OfType<
CountAllFunction>().Any()) { return; } var expressionsToCheck = selectBody.SelItems.Select(s => s.
Exp).ToList(); if (selectBody.WhereExp != null) { expressionsToCheck.Add(selectBody.WhereExp); } var usedAliases = (from selectExp in expressionsToCheck from column in selectExp.Descendants().OfType<
Column>() select column.Table.Alias.Name).Distinct(); for (int i = selectBody.Joins.Count - 1; i >= 0; i--) { var join = selectBody.Joins[i]; if (join.RightItem is Table table && !usedAliases.Contains (table.Alias.Name)) { selectBody.Joins.RemoveAt(i); } } } if (selectBody.FromItem is SubSelect subSelect) {
Prune(subSelect.SelectBody); 1
The solution has been integrated into the benchmark framework. In order to get an insight on the generated SQL queries, the resulting queries are depicted in Listing 9.
Notably, to reduce the influence of just-in-time compilation, I actually run the solution 100 times on a Intel Core i7-8550U CPU clocked at 1.99 Ghz in a system with 8GB RAM running Windows 10 and divide the result by 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 thus is negligible. The time for the test lies around 20ms but that certainly gets more interesting once the solution is tested with larger databases. First, the transformation scheme used inside this paper difers from the original OCL2PSQL transformation scheme [7] and a correctness proof for the transformation scheme presented used in this paper is correct is out of scope for this paper. The reason that I did not use the OCL2PSQL mapping is that I am generally not satisfied with the verbosity of the SQL statements generated by it, whereas the SQL statements generated by the transformation scheme presented here are much easier to comprehend in my opinion. The validation of the mapping scheme presented here will be subject of future work. However, this diferent mapping scheme also makes a comparison with alternative implementations that stick more closely to the OCL2PSQL mapping more dificult.
In my opinion, the solution shows well how to use the Dynamic Language Runtime available in C# to perform dispatch on parameters, a frequent selling argument of model transformation languages apart from access to trace, incrementality and bidirectionality. The latter two properties require a very declarative way of specifying model transformations such as exemplified e.g. by NMF Synchronizations [6], but the transformation at hand is written in a very imperative style. Anyways, incremental change propagation is not relevant for the case at hand, since changes the main purpose of the transformation is to execute the resulting SQL statement and analyze the result data. Bidirectionality would be very interesting to reverse-engineer SQL statements in order to make them more understandable, but it is unclear to what extend this is possible at all. Given that the trace is not important in this case, there is just no reason not to use the Dynamic Language Runtime, especially taking into account the very good performance results.
A further advantage of a solution in plain C# is that it can be easily integrated into model transformations written in internal DSLs using C# as a host language, in particular NTL, since usually, not the entire model forms a tree structure and hence, access to the trace is required.
This integration, however, would be much more dififcult when incrementality was important despite incrementalization systems like NMF Expressions [8] that operate on C# code (or models thereof). It will be subject of future work how transformations like the mapping from OCL to SQL can be supported when incremental change propagation is required. [1] S. Sendall, W. Kozaczynski, Model transformation the heart and soul of model-driven software development, Technical Report, 2003.
2Actually, 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. ***** Stage#0 *** +++ challenge#0: SQL: SELECT 2 res +++ challenge#1: SQL: SELECT ’Peter’ res +++ challenge#2: SQL: SELECT 1 = 1 res ***** Stage#1 *** +++ challenge#0: SQL: SELECT 2 = 3 res +++ challenge#1: SQL: SELECT ’Peter’ = ’Peter’ res +++ challenge#2: SQL: SELECT 1 = 1 and 1 = 1 res ***** Stage#2 *** +++ challenge#0: SQL: SELECT Car_id res FROM Car ***** Stage#3 *** +++ challenge#0: SQL: SELECT tmp1.res res FROM (SELECT COUNT(*) res FROM Car) AS tmp1 +++ challenge#1: SQL: SELECT tmp1.res = 1 res FROM (SELECT COUNT(*) res FROM Car) AS tmp1 ***** Stage#4 *** +++ challenge#0: SQL: SELECT 5 res FROM Car AS c +++ challenge#1: SQL: SELECT c.Car_id res FROM Car AS c +++ challenge#2: SQL: SELECT 1 = 0 res FROM Car AS c ***** Stage#5 *** +++ challenge#0: SQL: SELECT c.color res FROM Car AS c +++ challenge#1: SQL: SELECT c.color = ’black’ res FROM Car AS c ***** Stage#6 *** +++ 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 +++ 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 ***** Stage#7 *** +++ challenge#0: SQL: SELECT tmp1.res > 0 res FROM (SELECT COUNT(*) res FROM Car AS c WHERE 1 = 1) AS tmp1 +++ challenge#1: SELECT tmp1.res > 0 res FROM (SELECT COUNT(*) res FROM Car AS c WHERE 1 = 0) AS tmp1 +++ challenge#2: SQL: SELECT tmp1.res > 0 res FROM (SELECT COUNT(*) res FROM Car AS c WHERE c.color = ’black’) AS tmp1 +++ 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 ***** Stage#8 *** +++ 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