Skip to main content
SpringerLink
Log in

Incremental test data generation for database queries

  • Published:
Automated Software Engineering Aims and scope Submit manuscript

Abstract

Testing database applications is a complex task since it involves designing test databases with meaningful test data in order to reveal faults and, at the same time, with a small size in order to carry out the testing process in an efficient way. This paper presents an automated approach to generating test data (test relational databases and test inputs for query parameters) for a set of SQL queries, with the aim of covering test requirements as obtained from said queries. The test data generation follows an incremental approach where, in each increment, test data are generated to cover a test requirement by re-using test data previously generated for other test requirements. The test data generation for each test requirement is formulated as a constraint satisfaction problem, where constraints are derived from the test requirement, initial database states and previously generated test data. The generation process is fully automated and supports the execution on complex queries and databases. Evaluation is carried out on a real life application, and the results show that small-size generated test relational databases achieve high coverage scores for the queries under test in a short generating time.

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

Similar content being viewed by others

Notes

  1. http://alloy.mit.edu/alloy/

References

  • Binnig, C., Kossmann, D., Lo, E.: Reverse query processing. In: IEEE 23rd International Conference on Data Engineering, 2007. ICDE 2007, pp. 506–515 (2007a)

  • Binnig, C., Kossmann, D., Lo, E., Özsu, M.T.: Qagen: generating query-aware test databases. In: Chan, C.Y., Ooi, B.C., Zhou, A. (eds) Proceedings of the ACM SIGMOD International Conference on Management of Data, Beijing, China, June 12–14, 2007, pp. 341–352. ACM, New York, NY (2007b)

  • Binnig, C., Kossmann, D., Lo, E.: Multi-rqp: generating test databases for the functional testing of oltp applications. In: Proceedings of the 1st International Workshop on Testing Database Systems, DBTest ’08, pp 5:1–5:6. ACM, New York, NY (2008)

  • Blanco, R., Tuya, J., Seco, R.: Test adequacy evaluation for the user-database interaction: A specification-based approach. In: 2012 IEEE Fifth International Conference on Software Testing, Verification and Validation (ICST), pp. 71–80 (2012)

  • Bruno, N., Chaudhuri, S.: Flexible database generators. In: Proceedings of the 31st International Conference on Very Large Data Bases, VLDB Endowment, VLDB ’05, pp. 1097–1107 (2005)

  • Bruno, N., Chaudhuri, S., Thomas, D.: Generating queries with cardinality constraints for dbms testing. IEEE Trans. Knowl. Data Eng. 18(12), 1721–1725 (2006)

    Article  Google Scholar 

  • Caballero, R., García-Ruiz, Y., Sáenz-Pérez, F.: Applying constraint logic programming to sql test case generation. In: Proceedings of the 10th International Conference on Functional and Logic Programming, FLOPS’10, pp. 191–206. Springer, Berlin (2010)

  • Chandra, B., Chawda, B., Kar, B., Reddy, K., Shah, S., Sudarshan, S.: Data generation for testing and grading sql queries. VLDB J. 24(6), 731–755 (2015)

    Article  Google Scholar 

  • Chays, D., Deng, Y., Frankl, P.G., Dan, S., Vokolos, F.I., Weyuker, E.J.: An agenda for testing relational database applications: research articles. Softw. Test. Verif. Reliab. 14(1), 17–44 (2004)

    Article  Google Scholar 

  • Chays, D., Shahid, J., Frankl, P.G.: Query-based test generation for database applications. In: Proceedings of the 1st International Workshop on Testing Database Systems, DBTest ’08, pp. 6:1–6:6. ACM, New York, NY (2008)

  • Chilenski, J.J.: An investigation of three forms of the modifiedcondition decision coverage (mcdc) criterion. Tech. rep., Office of Aviation Research (2001)

  • Codd, E.F.: The Relational Model for Database Management: Version 2. Addison-Wesley Longman Publishing Co. Inc, Boston, MA (1990)

    MATH  Google Scholar 

  • de la Riva, C., Suárez-Cabal, M.J., Tuya, J.: Constraint-based test database generation for sql queries. In: Proceedings of the 5th Workshop on Automation of Software Test, AST ’10, pp. 67–74. ACM, New York, NY (2010)

  • Emmi, M., Majumdar, R., Sen, K.: Dynamic test input generation for database applications. In: Proceedings of the 2007 International Symposium on Software Testing and Analysis, ISSTA ’07, pp. 151–162. ACM, New York, NY (2007)

  • Halfond, W., Orso, A.: Command-form coverage for testing database applications. In: 21st IEEE/ACM International Conference on Automated Software Engineering, 2006. ASE ’06. pp. 69–80 (2006)

  • Houkjær, K., Torp, K., Wind, R.: Simple and realistic data generation. In: Proceedings of the 32Nd International Conference on Very Large Data Bases, VLDB Endowment, VLDB ’06, pp. 1243–1246 (2006)

  • Kapfhammer, G., McMinn, P., Wright, C.: Search-based testing of relational schema integrity constraints across multiple database management systems. In: 2013 IEEE Sixth International Conference on Software Testing, Verification and Validation (ICST), pp. 31–40 (2013)

  • Kapfhammer, G.M., Soffa, M.L.: A family of test adequacy criteria for database-driven applications. In: Proceedings of the 9th European Software Engineering Conference Held Jointly with 11th ACM SIGSOFT International Symposium on Foundations of Software Engineering, ESEC/FSE-11, pp. 98–107. ACM, New York, NY (2003)

  • Khalek, S., Khurshid, S.: Systematic testing of database engines using a relational constraint solver. In: 2011 IEEE Fourth International Conference on Software Testing, Verification and Validation (ICST), pp. 50–59 (2011)

  • Khalek, S.A., Elkarablieh, B., Laleye, Y.O., Khurshid, S.: Query-aware test generation using a relational constraint solver. In: 23rd IEEE/ACM International Conference on Automated Software Engineering (ASE 2008), 15–19 September 2008, pp. 238–247. IEEE Computer Society, L’Aquila (2008)

  • Li, C., Csallner, C.: Dynamic symbolic database application testing. In: Proceedings of the Third International Workshop on Testing Database Systems, DBTest ’10, pp. 7:1–7:6. ACM, New York, NY (2010)

  • Lo, E., Cheng, N., Lin, W.W., Hon, W.K., Choi, B.: Mybenchmark: generating databases for query workloads. VLDB J. 23(6), 895–913 (2014)

    Article  Google Scholar 

  • Marcozzi, M., Vanhoof, W., Hainaut, J.L.: Relational symbolic execution of SQL code for unit testing of database programs. Sci. Comput. Program. 105, 44–72 (2015)

    Article  Google Scholar 

  • Pan, K., Wu, X., Xie, T.: Generating program inputs for database application testing. In: Proceedings of the 2011 26th IEEE/ACM International Conference on Automated Software Engineering, ASE ’11, pp. 73–82. IEEE Computer Society, Washington, DC (2011)

  • Pan, K., Wu, X., Xie, T.: Automatic test generation for mutation testing on database applications. In: 2013 8th International Workshop on Automation of Software Test (AST), pp. 111–117 (2013)

  • Pan, K., Wu, X., Xie, T.: Guided test generation for database applications via synthesized database interactions. ACM Trans. Softw. Eng. Methodol. 23(2), 12:1–12:27 (2014)

    Article  Google Scholar 

  • Pan, K., Wu, X., Xie, T.: Program-input generation for testing database applications using existing database states. Autom. Softw. Eng. 22(4), 439–473 (2015)

    Article  Google Scholar 

  • Prud’homme, C., Fages, J.G., Lorca, X.: Choco documentation. TASC, INRIA Rennes, LINA CNRS UMR 6241, COSLING S.A.S. http://www.choco-solver.org (2015)

  • Sen, K., Marinov, D., Agha, G.: Cute: A concolic unit testing engine for c. In: Proceedings of the 10th European Software Engineering Conference Held Jointly with 13th ACM SIGSOFT International Symposium on Foundations of Software Engineering, ESEC/FSE-13, pp. 263–272. ACM, New York, NY (2005)

  • Shah, S., Sudarshan, S., Kajbaje, S., Patidar, S., Gupta, B., Vira, D.: Generating test data for killing sql mutants: a constraint-based approach. In: 2011 IEEE 27th International Conference on Data Engineering (ICDE), pp. 1175–1186 (2011)

  • Suárez-Cabal, M.J., Tuya, J.: Structural coverage criteria for testing SQL queries. J. UCS 15(3), 584–619 (2009)

    Google Scholar 

  • Tsang, E.P.K.: Foundations of Constraint Satisfaction. Computation in Cognitive Science. Academic Press, Cambridge (1993)

    Google Scholar 

  • Tuya, J., Suárez-Cabal, M.J., de la Riva, C.: Mutating database queries. Inf. Softw. Technol. 49(4), 398–417 (2007)

    Article  Google Scholar 

  • Tuya, J., Suárez-Cabal, M.J., de la Riva, C.: Full predicate coverage for testing sql database queries. Softw. Test Verif. Reliab. 20(3), 237–288 (2010)

    Article  Google Scholar 

  • Tuya J., de la Riva C., Suárez-Cabal M.J., Blanco R.: Coverage-aware test database reduction. IEEE Trans. Softw. Eng. 42(10), 941–959 (2016). doi:10.1109/TSE.2016.2519032

  • Veanes, M., Grigorenko, P., Halleux, P., Tillmann, N.: Symbolic query exploration. In: Proceedings of the 11th International Conference on Formal Engineering Methods: Formal Methods and Software Engineering, ICFEM ’09, pp. 49–68. Springer, Berlin (2009)

  • Vemasani, P., Brodsky, A., Ammann, P.: Generating test data to distinguish conjunctive queries with equalities. In: Proceedings of the 2014 IEEE International Conference on Software Testing, Verification, and Validation Workshops, ICSTW ’14, pp. 216–221. IEEE Computer Society, Washington, DC (2014)

  • Willmor, D., Embury, S.: An intensional approach to the specification of test cases for database applications. In: Proceedings of the 28th International Conference on Software Engineering, ICSE ’06, pp. 102–111. ACM, New York, NY (2006a)

  • Willmor, D., Embury, S.: Testing the implementation of business rules using intensional database tests. In: Testing: Academic and Industrial Conference–Practice and Research Techniques, 2006. TAIC PART 2006. Proceedings, pp. 115–126 (2006b)

  • Zhang, J., Xu, C., Cheung, S.C.: Automatic generation of database instances for white-box testing. In: Proceedings of the 25th International Computer Software and Applications Conference on Invigorating Software Development, COMPSAC ’01, pp. 161–165. IEEE Computer Society, Washington, DC (2001)

  • Zhou, C., Frankl, P.: Inferential checking for mutants modifying database states. In: 2013 IEEE Sixth International Conference on Software Testing, Verification and Validation 0:259–268, (2011) doi:10.1109/ICST.2011.63

Download references

Acknowledgements

This work was supported in part by projects TIN2013-46928-C3-1-R and TIN2016-76956-C3-1-R, funded by the Spanish Ministry of Science and Technology, and GRUPIN14-007, funded by the Principality of Asturias (Spain) and ERDF funds.

Author information

Authors and Affiliations

  1. Campus Universitario de Gijón, Dpto. Informática, 33204, Gijón, Spain

    María José Suárez-Cabal, Claudio de la Riva, Javier Tuya & Raquel Blanco

Authors
  1. María José Suárez-Cabal
    View author publications

    You can also search for this author in PubMed Google Scholar

  2. Claudio de la Riva
    View author publications

    You can also search for this author in PubMed Google Scholar

  3. Javier Tuya
    View author publications

    You can also search for this author in PubMed Google Scholar

  4. Raquel Blanco
    View author publications

    You can also search for this author in PubMed Google Scholar

Corresponding author

Correspondence to María José Suárez-Cabal.

Rights and permissions

Reprints and permissions

About this article

Check for updates. Verify currency and authenticity via CrossMark

Cite this article

Suárez-Cabal, M.J., de la Riva, C., Tuya, J. et al. Incremental test data generation for database queries. Autom Softw Eng 24, 719–755 (2017). https://doi.org/10.1007/s10515-017-0212-7

Download citation

  • Received:

  • Accepted:

  • Published:

  • Issue Date:

  • DOI: https://doi.org/10.1007/s10515-017-0212-7

Keywords

Search

Navigation