Skip to main content
SpringerLink
Log in

Automatic query rewriting schemes for multitenant SaaS applications

  • Published:
Automated Software Engineering Aims and scope Submit manuscript

Abstract

In Software as a Service (SaaS) environments, designing and realizing multitenant schema-mapping that supports a shared database with custom extensions is a non-trivial task. Universal Table is one promising schema-mapping technique that is commonly used. However, there has been little research devoted to the design and realization of a query rewriting scheme for Universal Table. In this paper, we present a collection of general query rewriting schemes for Universal Table that can transparently transform tenant-specific logical queries into corresponding physical queries. Based on the design, we have developed a Java-based schema-mapping and query rewriting middleware for Universal Table and a sample online shopping SaaS application to verify its feasibility. Additionally, analytical results that can be used to predict the overhead of our schemes are also reported. Finally, we conduct a series of experiments and find that the results not only agree well with our analytical predictions but also show that our schemes are scalable to the number of tenants and the number of concurrent database connections.

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
Fig. 7
Fig. 8
Fig. 9
Fig. 10
Fig. 11

Similar content being viewed by others

References

  • Aulbach, S., Grust, T., Jacobs, D., Kemper, A., Rittinger, J.: Multi-Tenant databases for software as a service: schema-mapping techniques. In: Proceedings of the ACM International Conference on Management of Data. ACM, New York (2008)

  • Chong, F., Carraro, G.: Architecture strategies for catching the long tail. http://msdn.microsoft.com/en-us/library/aa479069.aspx (2006). Accessed 26 May, July 2013

  • Chong, F., Carraro, G., Wolter, R.: Multi-tenant data architecture. http://msdn.microsoft.com/en-us/library/aa479086.aspx (2006). Accessed 26 May 2013

  • Clinton, B.: iBATIS JPetStore 4.0.5 (2004). http://sourceforge.net/projects/ibatisjpetstore/. Accessed 26 May 2013

  • Codd, E. F.: Relational completeness of database sublanguages. In: Database Systems. Prentice-Hall, Upper Saddle River (1972)

  • Copeland, G. P., Khoshafian, S. N.: A decomposition storage model. In: Proceedings of the ACM International Conference on Management of data (SIGMOD), pp. 268–279. ACM, New York (1985)

  • Du, J., Wen, H. Y., Yang, Z. J.: Research on data layer sturcture of multi-tenant E-commerce system. In: Proceedings of the IEEE 17th International Conference on Industrial Engineering and Engineering Management (IE&EM), pp. 362–365. IEEE (2010)

  • Elmasri, R., Navathe, S.B.: Database Systems: Models, Languages, Design, and Application Programming. Pearson Education, Upper Saddle River (2011)

    MATH  Google Scholar 

  • Halili, E.H.: Apache Jmeter: A Practical Beginner’s Guide to Automated Testing and Performance Measurement for Your Websites. Packt Publishing, Birmingham (2008)

    Google Scholar 

  • Jarke, M., Koch, J.: Query optimization in database systems. ACM Comput. Surv. 16(4), 111–152 (1984)

    Article  MathSciNet  MATH  Google Scholar 

  • Koziolek, H.: The SPOSAD architectural style for multi-tenant software applications. In: Proceedings of the 9th Working IEEE/IFIP Conferences on Software Architecture, pp. 320–327. IEEE (2011)

  • Krebs, R., Momm, C., Konev, S.: Architectural concerns in multi-tenant SaaS applications. In: Proceedings of the International Conference on Cloud Computing and Service Science (CLOSER12) (2012)

  • Li, C.: Transforming relational database into HBase: a case study. In: Proceedings of the IEEE International Conference on Software Engineering and Service Sciences (ICSESS). IEEE (2010)

  • Li, H., Yang, D., Zhang, X.H.: Survey on multi-tenant data architecture for SaaS. Int. J. Comput. Sci. Issues 9(6–3), 198–204 (2012)

    Google Scholar 

  • Liao, C. F., Chen, K., Chen, J. J.: Toward a tenant-aware query rewriting engine for universal table schema-mapping. In: Proceedings of the IEEE International Conference on Cloud Computing Technology and Science (IEEE CloudCom’12), Presented in 2012 International Workshop on SaaS (Software-as-a-Service) Architecture and Engineering, pp. 833–838. IEEE (2012)

  • Lynch, N.A.: Distributed Algorithms. Morgan Kaufmann, Burlington (1996)

  • Maier, D., Ullman, J.D.: Maximal objects and the semantics of universal relation databases. ACM Trans. Database Syst. 8(11), 1–14 (1983)

    Article  MATH  Google Scholar 

  • OSGi Core Release 5, The OSGi Alliance (2012)

  • Pereira, J., Chiueh, T.C.: SQL Rewriting Engine and its Applications. Technical Report. Stony Brook University (2007)

  • Russell, C.: Java Data Objects 2.0. JSR 243 Specification (2010)

  • Weissman, C. D., Bobrowski, S.: The design of the Force.com multitenant internet application development platform. In: Proceedings of the ACM SIGMOD International Conference on Management of Data. ACM, New York (2009)

  • Yaish, H., Goyal, M., Feuerlicht, G.: An elastic multi-tenant database schema for software as a service. In: Proceedings of the IEEE International Conference on Dependable, Autonomic and Secure Computing, pp. 737–743. IEEE (2011)

Download references

Acknowledgments

This work is partially sponsored by Ministry of Science and Technology, Taiwan, under Grant 103-2221-E-004-005, 103-2221-E-004-018, 103-2218-E-004-003 and 103-2218-E-004-001.

Author information

Authors and Affiliations

  1. CS Department and Program in Digital Content and Technologies, National Chengchi University, No. 64, Sec. 2, Zhi-Nan Rd., Wenshan District, Taipei, 116, Taiwan

    Chun-Feng Liao

  2. CS Department, National Chengchi University, No. 64, Sec. 2, Zhi-Nan Rd., Wenshan District, Taipei, 116, Taiwan

    Kung Chen & Jiu-Jye Chen

  3. IECS Department, Feng Chia University, No. 100, Wenhwa Rd., Seatwen District, Taichung, 407, Taiwan

    Deik Hoong Tan

Authors
  1. Chun-Feng Liao
    View author publications

    You can also search for this author in PubMed Google Scholar

  2. Kung Chen
    View author publications

    You can also search for this author in PubMed Google Scholar

  3. Deik Hoong Tan
    View author publications

    You can also search for this author in PubMed Google Scholar

  4. Jiu-Jye Chen
    View author publications

    You can also search for this author in PubMed Google Scholar

Corresponding author

Correspondence to Chun-Feng Liao.

Rights and permissions

Reprints and permissions

About this article

Check for updates. Verify currency and authenticity via CrossMark

Cite this article

Liao, CF., Chen, K., Tan, D.H. et al. Automatic query rewriting schemes for multitenant SaaS applications. Autom Softw Eng 23, 535–568 (2016). https://doi.org/10.1007/s10515-015-0178-2

Download citation

  • Received:

  • Accepted:

  • Published:

  • Issue Date:

  • DOI: https://doi.org/10.1007/s10515-015-0178-2

Keywords

Search

Navigation