Skip to main content
SpringerLink
Log in

Unrolling SQL: 1999 Recursive Queries

  • Conference paper
Computer Applications for Database, Education, and Ubiquitous Computing (EL 2012, DTA 2012)

Part of the book series: Communications in Computer and Information Science ((CCIS,volume 352))

Abstract

Hierarchical and graph data structures are common in practical application development. In order to query such data, one can use SQL:1999 recursive queries based on Common Table Expressions. Nowadays, numerous relational database management systems implement them. However, some popular systems, e.g. MySQL, still lack this useful feature. In this paper we show three methods to circumvent this situation: (1) the direct loop, (2) horizontal unrolling and (3) vertical unrolling. We analyze them and present a report on tests of their efficiency. We also describe our implementation of these methods as a prototype extension to Hibernate, a major object-relational mapping system. This extension allows running SQL:1999 recursive queries directly from an application code, even when the underlying DBMS does not support them.

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

Access this chapter

Log in via an institution
Chapter
USD 29.95
Price excludes VAT (USA)
  • Available as PDF
  • Read on any device
  • Instant download
  • Own it forever
eBook
USD 39.99
Price excludes VAT (USA)
  • Available as PDF
  • Read on any device
  • Instant download
  • Own it forever
Softcover Book
USD 54.99
Price excludes VAT (USA)
  • Compact, lightweight edition
  • Dispatched in 3 to 5 business days
  • Free shipping worldwide - see info

Tax calculation will be finalised at checkout

Purchases are for personal use only

Institutional subscriptions

Preview

Unable to display preview. Download preview PDF.

Unable to display preview. Download preview PDF.

References

  1. Brandon, D.: Recursive database structures. J. Comput. Small Coll. 21(2), 295–304 (2005)

    Google Scholar 

  2. Przymus, P., Boniewicz, A., Burzańska, M., Stencel, K.: Recursive Query Facilities in Relational Databases: A Survey. In: Zhang, Y., Cuzzocrea, A., Ma, J., Chung, K.-I., Arslan, T., Song, X. (eds.) DTA/BSBT 2010. CCIS, vol. 118, pp. 89–99. Springer, Heidelberg (2010)

    Chapter  Google Scholar 

  3. Ghazal, A., Crolotte, A., Seid, D.Y.: Recursive SQL Query Optimization with k-Iteration Lookahead. In: Bressan, S., Küng, J., Wagner, R. (eds.) DEXA 2006. LNCS, vol. 4080, pp. 348–357. Springer, Heidelberg (2006)

    Chapter  Google Scholar 

  4. Ordonez, C.: Optimization of Linear Recursive Queries in SQL. IEEE Trans. Knowl. Data Eng., 264–277 (2010)

    Google Scholar 

  5. Burzańska, M., Stencel, K., Wiśniewski, P.: Pushing Predicates into Recursive SQL Common Table Expressions. In: Grundspenkis, J., Morzy, T., Vossen, G. (eds.) ADBIS 2009. LNCS, vol. 5739, pp. 194–205. Springer, Heidelberg (2009)

    Chapter  Google Scholar 

  6. Melnik, S., Adya, A., Bernstein, P.A.: Compiling mappings to bridge applications and databases. ACM Transactions on Database Systems (TODS), 33(4), 1–50 (2008)

    Article  Google Scholar 

  7. Keller, W.: Mapping objects to tables: A pattern language. In: EuroPLoP (2007)

    Google Scholar 

  8. O’Neil, E.J.: Object/relational mapping 2008: Hibernate and the entity data model (EDM). In: Proc. ACM SIGMOD, pp. 1351–1356 (2008)

    Google Scholar 

  9. Bauer, C., King, G.: Java Persistence with Hibernate. Manning Publications Co., Greenwich (2006)

    Google Scholar 

  10. Burzańska, M., Stencel, K., Suchomska, P., Szumowska, A., Wiśniewski, P.: Recursive Queries Using Object Relational Mapping. In: Kim, T.-H., Lee, Y.-H., Kang, B.-H., Ślęzak, D. (eds.) FGIT 2010. LNCS, vol. 6485, pp. 42–50. Springer, Heidelberg (2010)

    Chapter  Google Scholar 

  11. Burzańska, M., Boniewicz, A., Szumowska, A., Winiewski, P.: Hibernate the Recursive Queries - Defining the Recursive Queries Using Hibernate ORM. In: ADBIS 2011, pp.190–199 (2011)

    Google Scholar 

  12. Szumowska, A., Burzańska, M., Wiśniewski, P., Stencel, K.: Efficient Implementation of Recursive Queries in Major Object Relational Mapping Systems. In: Kim, T.-H., Adeli, H., Slezak, D., Sandnes, F.E., Song, X., Chung, K.-I., Arnett, K.P. (eds.) FGIT 2011. LNCS, vol. 7105, pp. 78–89. Springer, Heidelberg (2011)

    Chapter  Google Scholar 

  13. Gawarkiewicz, M., Wiśniewski, P.: Partial Aggregation Using Hibernate. In: Kim, T.-H., Adeli, H., Slezak, D., Sandnes, F.E., Song, X., Chung, K.-I., Arnett, K.P. (eds.) FGIT 2011. LNCS, vol. 7105, pp. 90–99. Springer, Heidelberg (2011)

    Chapter  Google Scholar 

  14. Boniewicz, A., Gawarkiewicz, M., Wiśniewski, P.: Automatic Selection of Functional Indexes for Object Relational Mappings System. In: FGIT 2012 (2012)

    Google Scholar 

Download references

Author information

Authors and Affiliations

  1. Faculty of Mathematics and Computer Science, Nicolaus Copernicus University, Toruń, Poland

    Aleksandra Boniewicz, Krzystof Stencel & Piotr Wiśniewski

Authors
  1. Aleksandra Boniewicz
    View author publications

    You can also search for this author in PubMed Google Scholar

  2. Krzystof Stencel
    View author publications

    You can also search for this author in PubMed Google Scholar

  3. Piotr Wiśniewski
    View author publications

    You can also search for this author in PubMed Google Scholar

Editor information

Editors and Affiliations

  1. GVSA and University of Tasmania, Hobart, TAS, Australia

    Tai-hoon Kim

  2. Hosei University, 3-7-2, Kajino-cho, 184-8584, Koganei-shi, Tokyo, Japan

    Jianhua Ma

  3. National Chiao Tung University, Hsinchu, Taiwan, R.O.C.

    Wai-chi Fang

  4. Victoria University, 8001, Melbourne, VIC, Australia

    Yanchun Zhang

  5. University of Calabria, Via P. Bucci, 41C, I-87036, Rende, Cosenza, Italy

    Alfredo Cuzzocrea

Rights and permissions

Reprints and permissions

Copyright information

© 2012 Springer-Verlag Berlin Heidelberg

About this paper

Cite this paper

Boniewicz, A., Stencel, K., Wiśniewski, P. (2012). Unrolling SQL: 1999 Recursive Queries. In: Kim, Th., Ma, J., Fang, Wc., Zhang, Y., Cuzzocrea, A. (eds) Computer Applications for Database, Education, and Ubiquitous Computing. EL DTA 2012 2012. Communications in Computer and Information Science, vol 352. Springer, Berlin, Heidelberg. https://doi.org/10.1007/978-3-642-35603-2_50

Download citation

  • DOI: https://doi.org/10.1007/978-3-642-35603-2_50

  • Publisher Name: Springer, Berlin, Heidelberg

  • Print ISBN: 978-3-642-35602-5

  • Online ISBN: 978-3-642-35603-2

  • eBook Packages: Computer ScienceComputer Science (R0)

Publish with us

Policies and ethics

Search

Navigation