Skip to main content
SpringerLink
Log in

SQL-PL4OCL: an automatic code generator from OCL to SQL procedural language

  • Regular Paper
  • Published:
Software & Systems Modeling Aims and scope Submit manuscript

Abstract

In this paper, we introduce a SQL-PL code generator for OCL expressions that, in contrast to other proposals, is able to map OCL iterate and iterator expressions thanks to our use of stored procedures. We explain how the mapping presented here introduces key differences to the previous version of our mapping in order to (i) simplify its definition, (ii) improve the evaluation time of the resulting code, and (iii) consider OCL three-valued evaluation semantics. Moreover, we have implemented our mapping to target several relational database management systems, i.e., MySQL, MariaDB, PostgreSQL, and SQL server, which allows us to widen its usability and to benchmark the evaluation time of the SQL-PL code produced.

This is a preview of subscription content, log in via an institution to check access.

Access this article

Log in via an institution

Price excludes VAT (USA)
Tax calculation will be finalised during checkout.

Instant access to the full article PDF.

Institutional subscriptions

Fig. 1
Fig. 2
Fig. 3
Fig. 4
Fig. 5
Fig. 6

Similar content being viewed by others

Notes

  1. Please notice that in this paper SQL-PL stands simply for SQL Procedural Language. It is not bound to any proprietary PL dialect.

  2. We only consider binary associations, and we do not consider attributes of entity or collection types.

  3. \({ nm}()\) generates unique names for classes, attributes, and associations.

  4. Figure 4 shows the resulting table for a simple Car–Company model.

  5. Please note that our delimiter in SQL-PL is set to ‘//.’

  6. For the sake of simplicity, we will consider here that the top operator of src is a simple expression. The case when the iterator expressions are nested deserve, however, a particular attention.

  7. We ran the benchmark in a laptop with an Intel Core m7, 1.3 GHz, 8 GB RAM, and 500 GB Flash Storage. The RDBMS versions used were MySQL 5.7, MariaDB 10.1, SQL Server 2016 Express, and PostgreSQL 9.6.1.

  8. ‘The result of the mapping model presented here may not apply a part of the expression directly into the result of another subexpression. The combination technique is not formally described.’

    [...]

    ‘This work did not succeed to find a concise and complete formal representation for procedural mapping patterns.’

  9. Notice that here we employ the terminology ‘simple expressions’ and ‘complex expressions’ following our definition in Sect. 4.

References

  1. Armonas, A., Nemuraité, L.: Pattern based generation of full-fledged relational schemas from UML/OCL models. Inf. Technol. Control, 35(1), (2006)

  2. Basin, D., Clavel, M., Egea, M., de Dios, M.A.G., Dania, C., Ortiz, G., Valdazo, J.: Model-driven development of security-aware guis for data-centric applications. In: Foundations of Security Analysis and Design VI—FOSAD Tutorial Lectures, volume 6858 of LNCS, pp. 101–124. Springer, (2011)

  3. Basin, D.A., Clavel, M., Doser, J., Egea, M.: Automated analysis of security-design models. Inf. Softw. Technol. 51(5), 815–831 (2009)

    Article  Google Scholar 

  4. Berrabah, D., Boufarès, F.: Constraints checking in UML class diagrams: SQL vs OCL. In: Wagner, R., Revell, N., Pernul, G. (eds.) Database and Expert Systems Applications, 18th International Conference, DEXA 2007, Regensburg, Germany, September 3–7, 2007, Proceedings, volume 4653 of LNCS, pp. 593–602. Springer, (2007)

  5. Carlos, X.D., Sagardui, G., Trujillo, S.: MQT, an approach for run-time query translation: From EOL to SQL. In: Brucker, A.D., Dania, C., Georg, G., Gogolla, M. (eds.) Proceedings of the 14th International Workshop on OCL and Textual Modelling co-located with 17th International Conference on Model Driven Engineering Languages and Systems (MODELS 2014), Valencia, Spain, September 30, 2014, volume 1285 of CEUR Workshop Proceedings, pp. 13–22. CEUR-WS.org, (2014)

  6. Clavel, M., Egea, M., de Dios, M.A.G.: ECEASST building an efficient component for OCL evaluation. ECEASST, 15, 27–33 (2008)

  7. Cortázar, S.C.: Transformación de las restricciones OCL de un esquema UML a consultas de SQL. trabajo de fin de grado. Technical report, Universidad Carlos III de Madrid, (2012). http://e-archivo.uc3m.es/bitstream/handle/10016/16799/TFG_Sergio_Casillas_Cortazar.pdf?sequence=1&isAllowed=y

  8. Cosentino, V.: A model-based approach for extracting business rules out of legacy information systems. PhD thesis, École des mines de Nantes, France (2013)

  9. Dania, C.: MySQL4OCL: Un compilador de OCL a MySQL. Master thesis. Universidad Complutense de Madrid (2011)

  10. Dania, C. Egea, M.: SQLPL4OCL tool (2016). http://software.imdea.org/~dania/tools/sqlpl4ocl

  11. Demuth, B., Hußmann, H.: Using UML/OCL Constraints for Relational Database Design. In: France, R.B., Rumpe, B. (eds.) UML, volume 1723 of LNCS, pp. 598–613. Springer, (1999)

  12. Demuth, B., Hußmann, H., Loecher, S.: OCL as a Specification Language for Business Rules in Database Applications. In: Gogolla, M., Kobryn, C. (eds.) UML, volume 2185 of LNCS, pp. 104–117. Springer, (2001)

  13. Egea, M., Dania, C., Clavel, M.: MySQL4OCL: A stored procedure-based MySQL code generator for OCL. ECEASST, 36, (2010)

  14. I.O. for Standardization. Object Management Group Object Constraint Language (OCL), (2012). https://www.iso.org/obp/ui/#iso:std:iso-iec:19507:ed-1:v1:en

  15. Heidenreich, F., Wende, C., Demuth, B.: A framework for generating query language code from OCL invariants. ECEASST, 9, (2008)

  16. Kleppe, A., Bast, W., Warmer, J.B., Watson, A.: MDA Explained: The Model Driven Architecture–Practice and Promise. Addison-Wesley, Boston (2003)

    Google Scholar 

  17. MariaDB (2016) https://mariadb.org/

  18. Microsoft. SQL Server (2016). https://www.microsoft.com/es-es/server-cloud/products/sql-server/overview.aspx

  19. Monperrus, M., Jézéquel, J., Baudry, B., Champeau, J., Hoeltzener, B.: Model-driven generative development of measurement software. Softw. Syst. Model. 10(4), 537–552 (2011)

  20. MySQL 5.7 Reference Manual. http://dev.mysql.com/doc/refman/5.7/

  21. Object Management Group: Common Warehouse Metamodel specification. (March 2003). OMG document available at http://www.omg.org/technology/documents/formal/cwm.htm

  22. Object Management Group: Object Constraint Language specification. (May 2006). OMG document available at http://www.omg.org/cgi-bin/doc?formal/2006-05-01

  23. Object Management Group: Object constraint language specification version 2.4. Technical report, OMG, (2014). http://www.omg.org/spec/OCL/2.4

  24. Object Management Group: Unified modeling language version 2.5. Technical report, OMG, (2015). http://www.omg.org/spec/UML/2.5/PDF/

  25. Oriol, X., Teniente, E.: Incremental checking of OCL constraints through SQL queries. In: Brucker, A.D., Dania, C., Georg, G., Gogolla, M. (eds.) Proceedings of the 14th International Workshop on OCL and Textual Modelling co-located with 17th International Conference on Model Driven Engineering Languages and Systems (MODELS 2014), Valencia, Spain, September 30, 2014., volume 1285 of CEUR Workshop Proceedings, pp. 23–32. CEUR-WS.org, (2014)

  26. Pinet, F., Kang, M., Vigier, F.: Spatial Constraint Modelling with a GIS Extension of UML and OCL: Application to Agricultural Information Systems. In: Wiil, U.K. (ed), Metainformatics, volume 3511 of LNCS, pp. 160–178. Springer, (2004)

  27. PL/pgSQL - SQL procedural language (2016). https://www.postgresql.org/docs/9.2/static/plpgsql.html

  28. Schmidt, A.: Untersuchungen zur Abbildung von OCL-ausdrücken auf SQL. Master’s thesis, Institut für Softwaretechnik II - Technische Universität Dresden, Germany, (1998)

  29. Siripornpanit, N., Lekcharoen, S.: An adaptive algorithms translating and back-translating of object constraint language into structure query language. In: International Conference on Information and Multimedia Technology, 2009. ICIMT’09, pp. 149–151. IEEE, (2009)

  30. Sobotka, P.: Transformation from OCL into SQL, 2012. Master thesis. Charles University in Prague. https://is.cuni.cz/webapps/zzp/download/120076745

  31. ISO/IEC 9075-(1–10) Information technology—Database languages—SQL. Technical report, International Organization for Standardization, (2011). http://www.iso.org/iso/home/store/catalogue_tc/catalogue_detail.htm?csnumber=63555

  32. SQL Dialects Reference (2016). https://en.wikibooks.org/wiki/SQL_Dialects_Reference/Print_version

Download references

Author information

Authors and Affiliations

  1. Minsait (by Indra), Madrid, Spain

    Marina Egea

  2. IMDEA Software Institute, Madrid, Spain

    Carolina Dania

Authors
  1. Marina Egea
    View author publications

    You can also search for this author in PubMed Google Scholar

  2. Carolina Dania
    View author publications

    You can also search for this author in PubMed Google Scholar

Corresponding authors

Correspondence to Marina Egea or Carolina Dania.

Additional information

Communicated by Prof. Martin Gogolla.

A guideline to implementation

A guideline to implementation

This annex is intended to provide a high-level overview, abstracting away the details, of how OCL operators are translated to SQL and its procedural extension. This overview is presented in Tables  2, 3, and 4.

Table 2 Guideline of the mapping of OCL Model Specific, Literal, Any and Iterators to SQL-PL
Full size table
Table 3 Guideline of the mapping of OCL Real, Integer, and Strings operators to SQL-PL
Full size table
Table 4 Guideline of the mapping of OCL Collection operators to SQL-PL
Full size table

Rights and permissions

Reprints and permissions

About this article

Check for updates. Verify currency and authenticity via CrossMark

Cite this article

Egea, M., Dania, C. SQL-PL4OCL: an automatic code generator from OCL to SQL procedural language. Softw Syst Model 18, 769–791 (2019). https://doi.org/10.1007/s10270-017-0597-6

Download citation

  • Received:

  • Revised:

  • Accepted:

  • Published:

  • Issue Date:

  • DOI: https://doi.org/10.1007/s10270-017-0597-6

Keywords

Search

Navigation