There have been several attempts to map Object Constraint Language queries to SQL: one of these is the OCL2PSQL mapping proposed by Nguyen and Clavel. In this paper, I describe an implementation of OCL2PSQL using two languages from the Eclipse Epsilon project: 744 lines of ETL code for the model-to-model transformation itself, and 97 lines of EGL code for a model-to-text transformation that produces more readable SQL than the reference version. The solution passes all correctness tests set out in the original framework: the transformation has a median time of 0.93s across all scenarios.
• OCL2SQL, which encapsulates a model-to-model transformation written in the Epsilon Transformation Language into an easy-to-use Java class. import ’s0_literals.etl’; More information on the ETL transformation is import ’s1_equals_and.etl’; available in Section 3. import ’s2_allInstances.etl’; • SQL2Text, which replaces the model-to-text import ’s3_size.etl’; transformation in the reference solution with one import ’s4_collect_variable.etl’; based on the Epsilon Generation Language. More import ’s5_attributes.etl’; details on the EGL transformation are given in import ’s6_associationEnds.etl’; Section 4. import ’s7_exists.etl’; import ’s8_existsWithFree.etl’;
The Epsilon project includes several languages for performing model transformations: post { var firstRootSelect = SQL!PlainSelect.all.selectOne(
ps|ps.eContainer.isUndefined()); } var stmt = new SQL!SelectStatement; stmt.selectBody = firstRootSelect; • The Epsilon Object Language (EOL) is an OCL- subgraph produced from the OCL e subtree, allowing inspired imperative language which is well suited the diferent SQL subtrees to be linked together. The for small in-place (endogenous) model transfor- OCL2PSQL case did not require any manual rule schedulmations, though it can be used for purely imper- ing or the use of greedy/lazy rules: the automated rule ative exogenous transformations as well. scheduling done by ETL based on source element types • The Epsilon Transformation Language (ETL) and guards was suficient.
builds on top of EOL by adding the concept of a ETL allows for breaking up the transformation across rule, which transforms a certain type of source several modules. This made it relatively easy to iteramodel element (possibly with some guards lim- tively implement the various stages in OCL2PSQL and iting its applicability) to a target model element. test out how it behaved for the various challenges. ListThe rule scheduling can be kept entirely declara- ing 1 shows the main script of the ETL transformation: tive, or can be controlled to some extent through it is composed of a number of import statements that the use of “greedy” or “lazy” rules. ETL is well- bring in the rules needed for each stage, and a post rule suited for exogenous transformations, where a which places the one PlainSelect without a container new model is produced from the source model. into a SelectStatement element as mandated by the • Flock can be thought of as ETL with an automated SQL metamodel.
“copy-unless-otherwise-stated” default strategy. Some of the OCL2PSQL mappings had to produce sigIt is well suited for model migration tasks, where nificantly large SQL subtrees: to keep the code concise, a model has to undergo small changes from one a library of EOL operations (utilities.eol) was creversion of a metamodel to the next. ated and reused from the ETL rules. This library largely • The Epsilon Wizard Language (EWL) is a variant contained a set of functions for simple creation of SQL of EOL which allows users to define “wizards” model elements, an implementation of the OCL2PSQL that users can manually trigger on specific model functions for listing the free variables in an OCL expreselements, performing small in-place transforma- sion (FVars()) and for listing the source variables that tions (perhaps with some simple user interaction). the value of a subexpression ′ of the OCL expression depends upon (SVars(′)), and several other miscella
Out of these languages, ETL was chosen since the neous functions. As a simple example, Listing 2 shows
original formulation of OCL2PSQL as a collection of re- the code needed to transform OCL integer literals to the
cursive mappings by source type was a close match to target SQL metamodel.
the rules used by ETL. For the most part, each of those The ETL rules are for the most part a direct
one-tomappings was translated into an ETL rule, and calls to one translation from the descriptions at the end of the
map() were translated into e.equivalent() calls in ETL. OCL2PSQL case, except for two changes.
The e.equivalent() built-in operation retrieves the SQL The first change was considering one special case listed
rule IntLiteral
transform e:OCL!IntegerLiteralExp
to ps:SQL!PlainSelect {
ps.selItems.add(longSelectItem(’res’, e.integerValue.asLong()));
ps.selItems.add(longSelectItem(’val’, 1l));
in the original OCL2PSQL paper [
The second change was due to an unexpected inter- } action between the recursive approach used to define return value; OCL2PSQL, the definition of SubSelect.selectBody as a } containment reference in the SQL metamodel, and how the ETL e.equivalent() operation works. ETL will only apply a certain rule once to each matching source model element, and from them on e.equivalent() will always and if so it performed a deep cloning of the SQL subtree. return the same counterpart in the target model (e.g. the A better fix (which unfortunately would have required exact same object). This resulted in some SQL queries a rewriting of the input files for this case) would be to “losing” the body of their SubSelect objects to other sub- change the SQL metamodel so that SubSelect.selectBody is trees of the SQL model, as they also needed the mapping no longer a containment reference, and the same Plainfor that part of the OCL expression. Select can be reused from multiple SubSelect model For instance, consider the final challenge: elements.
Even further, this suggests that the OCL2PSQL mapCar.allInstances()−>exists(c| ping really produces a SQL expression graph (where some c.owners−>exists(p| subexpressions are reused) rather than a SQL abstract p.name = ’Peter’)) syntax tree. Instead of running the same subquery from several places, it may be advisable to redefine OCL2PSQL so it produces a sequence of SQL queries rather than a single large SQL query: it would run these reused subqueries first, and then provide their results to the higherlevel queries. Otherwise, there may be a risk that the SQL query could grow exponentially if suficiently large subqueries have to be duplicated across several locations.
Overall, the transformation required writing 14 rules across 531 lines of ETL code, with a support library of EOL operations that was 213 lines long. These line counts included whitespace and comments: if these are excluded, the line counts are reduced to 305 lines of ETL code and
and the SQL mapping of p.name both require using the mapping of c.owners as a subquery. ETL successfully maps c.owners to SQL, but EMF will not allow a single model element to be contained from more than one place. Since the mapping of c.owners−>exists(...) will complete last, it will efectively “steal” the subquery representing c.owners from the mapping of p.name.
The fix for this issue turned out to be simple, as shown in Listing 3. All uses of the e.equivalent() operation were wrapped into a new EOL operation: the operation tested if this “stealing” was about to take place (i.e. if the PlainSelect was already contained in another SubSelect),
count-etl-lines.sh script included in the solution folder.
the The reference solution included a model-to-text transfor- operations, which allow EGL templates in their body and mation that produced the SQL query to be run in MySQL return strings which can be used within expressions (e.g. from the SQL model. During the development of this so- for concatenating separators, as in line 12). lution, it was found that the generated SQL was dificult The script also uses some of the built-in Epsilon varito read in the presence of multiple levels of subqueries, ables: hasMore is a built-in Epsilon variable available in as it was entirely on one line. loops which is true if and only if there are more values
In order to improve the readability of the SQL queries after the current one. and help with the debugging, an alternative implementa- One useful feature in EGL is its ability to integrate fortion was written in 96 lines of EGL. The EGL template tra- matters that postprocess the generated text. In particular, verses the SQL model recursively from the root Select- the EGL LanguageFormatter was customised for SQL Statement, breaking up SELECT statements, CASE ex- (as shown in Listing 5) to automatically indent the lines pressions, joins, and subqueries across multiple lines. of the SQL script to improve readability, while keeping
Listing 4 shows an excerpt of the EGL script: the first the EGL script as simple as possible. This class only reline is the entry point of the entire script, kicking of quires the regular expressions that should increase and the recursive descent of the SQL model from the Select- decrease the indentation level after a match. Using this Statement. The EGL script makes heavy use of template script, queries are generated in the more readable form shown in Listing 6. 1.2 1.1 1.0 0.9 0.8 sd0.7 on0.6 c eS0.5 0.4 0.3 0.2 0.1 0.0
S0C0S0C1S0C2S1C0S1C1S1C2S2C0S3C0S3C1S4C0S4C1S4C2S5C0S5C1S6C0S6C1S7C0S7C1S7C2S7C3S8C0
the transformation was then run 10 more times within the same JVM, and the average execution time was recorded.
After implementing the ETL model-to-model transfor- This was followed by a single execution of the generated mation and the EGL model-to-text transformation, Java SQL query, to test if the expected results were produced. code to encapsulate these transformations and integrate Whereas the execution times for the “warmup” runs them with the TTC benchmark framework was added. are between 0.9s and 1.2s, the average execution times are The transformations passed all correctness cases for all much smaller and comparable to other solutions of the scenarios across all stages and challenges. contest, ranging between 0.1s and 0.25s. Test execution
In terms of execution time, the transformations were times ranged between 0.3s and 0.4s.
run on a Lenovo X1 laptop with an i7-6600U CPU
running at 2.60GHz with 16GiB of physical RAM, running
Ubuntu Linux 20.04.2 LTS with Linux 5.4.0-74-generic References
and the Oracle JDK 11.0.8. The default Java memory
allocation settings were used (no -Xmx or other JVM op- [
Bui (Eds.), Future Data and Security Engineering, Springer International Publishing, Cham, 2019, pp. 185–203. doi:10.1007/978-3-030-35653-8_13. tions were given). The Docker image provided by the OCL2PSQL case authors was used to run MySQL, using Docker Engine 20.10.7.
The transformation and test times are shown in Figure 1: the transformation times include both the ETL model-to-model transformation and the EGL model-to- [2] D. S. Kolovos, R. F. Paige, F. Polack, The Epsilon Object Language (EOL), in: Model Driven Architecture - Foundations and Applications, Second European Conference, ECMDA-FA 2006, Bilbao, Spain, July 10-13, 2006, Proceedings, 2006, pp. 128–142. doi:10.1007/11787044_11. text transformation, in order to mimic the two transformations done by the reference implementation (the proper OCL2PSQL transformation, and a model-to-model transformation between a 3rd-party JSqlParser metamodel and the EMF-based metamodel). It was noted during the open peer review stage of the contest that the ifrst execution of the transformation was considerably slower than later executions, due to Java class-loading and just-in-time recompiler warmup times. In order to obtain more representative results from a typical user (who would have a long-running Java process running the transformation repeatedly for diferent OCL queries),