SchemaAnalyst: Search-based test data generation for relational database schemas

Interested in learning more about this topic? Visit this web site to read the paper: https://www.gregorykapfhammer.com/research/papers/McMinn2016c/

Transcript

1. SchemaAnalyst Search-based Testing for Relational Database Schemas Cody Kinneer Institute for Software Research Carnegie Mellon University Additional Co-Authors: Phil McMinn, Chris J. Wright, Cody Kinneer, Colton McCurdy, Michael Camara, and Gregory M. Kapfhammer
2. Relational Databases Databases are everywhere!
3. Relational Databases Databases are everywhere! Database Application Server
4. Relational Databases Databases are everywhere! Database Application Server Mobile Phone or Tablet
5. Relational Databases Databases are everywhere! Database Application Server Mobile Phone or Tablet O ce and Productivity Software
6. Relational Databases Databases are everywhere! Database Application Server Mobile Phone or Tablet O ce and Productivity Software Government
7. Relational Databases Databases are everywhere! Database Application Server Mobile Phone or Tablet O ce and Productivity Software Government Astrophysics
8. Relational Databases Databases are everywhere! Database Application Server Mobile Phone or Tablet O ce and Productivity Software Government Astrophysics Over 1,000,000 posts!
9. Database Schemas 1 CREATE TABLE DEPT_INFO ( 2 DEPT_ID INTEGER NOT NULL, 3 DEPT_NAME VARCHAR(50), 4 PRIMARY KEY (DEPT_ID) 5 ); 6 7 CREATE TABLE OFFICE_INFO ( 8 OFFICE_ID INTEGER NOT NULL, 9 OFFICE_NAME VARCHAR(50), 10 HAS_PRINTER SMALLINT, 11 PRIMARY KEY (OFFICE_ID) 12 ); Figure: A sample of the UnixUsage schema.
10. Database Schemas 1 CREATE TABLE DEPT_INFO ( 2 DEPT_ID INTEGER NOT NULL, 3 DEPT_NAME VARCHAR(50), 4 PRIMARY KEY (DEPT_ID) 5 ); 6 7 CREATE TABLE OFFICE_INFO ( 8 OFFICE_ID INTEGER NOT NULL, 9 OFFICE_NAME VARCHAR(50), 10 HAS_PRINTER SMALLINT, 11 PRIMARY KEY (OFFICE_ID) 12 ); Figure: A sample of the UnixUsage schema.
11. Database Schemas 1 CREATE TABLE DEPT_INFO ( 2 DEPT_ID INTEGER NOT NULL, 3 DEPT_NAME VARCHAR(50), 4 PRIMARY KEY (DEPT_ID) 5 ); 6 7 CREATE TABLE OFFICE_INFO ( 8 OFFICE_ID INTEGER NOT NULL, 9 OFFICE_NAME VARCHAR(50), 10 HAS_PRINTER SMALLINT, 11 PRIMARY KEY (OFFICE_ID) 12 ); Figure: A sample of the UnixUsage schema.
12. Testing Database Schemas Manual testing is onerous and error prone
13. Testing Database Schemas Manual testing is onerous and error prone DBMonster only supports one DMBS
14. Testing Database Schemas Manual testing is onerous and error prone DBMonster only supports one DMBS Crashes and poor constraint coverage
15. Testing Database Schemas Manual testing is onerous and error prone DBMonster only supports one DMBS Crashes and poor constraint coverage Schemas often not tested at all!
16. SchemaAnalyst
17. SchemaAnalyst Coverage Criterion
18. SchemaAnalyst Coverage Criterion Data Generator
19. SchemaAnalyst Coverage Criterion Data Generator Database Schema
20. SchemaAnalyst Coverage Criterion Data Generator Database Schema JUnit Test Suite
21. SchemaAnalyst Coverage Criterion Data Generator Database Schema JUnit Test Suite Extensible tool for test data generation
22. Search-Based Testing 0 0.2 0.4 0.6 0.8 1 0 0.5 1 0 0.5 1 X: Parameter 1 Value Y: Parameter 2 Value Z: Fitness
23. Automatically Testing Schemas 1 CREATE TABLE DEPT_INFO ( 2 DEPT_ID INTEGER NOT NULL, 3 DEPT_NAME VARCHAR(50), 4 PRIMARY KEY (DEPT_ID) 5 ); Figure: A sample of the UnixUsage schema.
24. Automatically Testing Schemas 1 CREATE TABLE DEPT_INFO ( 2 DEPT_ID INTEGER NOT NULL, 3 DEPT_NAME VARCHAR(50), 4 PRIMARY KEY (DEPT_ID) 5 ); Figure: A sample of the UnixUsage schema. 1 INSERT INTO DEPT_INFO VALUES (0, ''); 2 INSERT INTO DEPT_INFO VALUES (NULL, ''); Figure: Data generated by SchemaAnalyst.
25. Automatically Testing Schemas 1 CREATE TABLE DEPT_INFO ( 2 DEPT_ID INTEGER NOT NULL, 3 DEPT_NAME VARCHAR(50), 4 PRIMARY KEY (DEPT_ID) 5 ); Figure: A sample of the UnixUsage schema. 1 INSERT INTO DEPT_INFO VALUES (0, ''); 2 INSERT INTO DEPT_INFO VALUES (NULL, ''); Figure: Data generated by SchemaAnalyst.
26. Automatically Testing Schemas 1 CREATE TABLE DEPT_INFO ( 2 DEPT_ID INTEGER NOT NULL, 3 DEPT_NAME VARCHAR(50), 4 PRIMARY KEY (DEPT_ID) 5 ); Figure: A sample of the UnixUsage schema. 1 INSERT INTO DEPT_INFO VALUES (0, ''); 2 INSERT INTO DEPT_INFO VALUES (NULL, ''); Figure: Data generated by SchemaAnalyst.
27. Automatically Testing Schemas 1 CREATE TABLE DEPT_INFO ( 2 DEPT_ID INTEGER NOT NULL, 3 DEPT_NAME VARCHAR(50), 4 PRIMARY KEY (DEPT_ID) 5 ); Figure: A sample of the UnixUsage schema. 1 INSERT INTO DEPT_INFO VALUES (0, ''); 2 INSERT INTO DEPT_INFO VALUES (NULL, ''); Figure: Data generated by SchemaAnalyst.
28. Automatically Testing Schemas 1 CREATE TABLE DEPT_INFO ( 2 DEPT_ID INTEGER NOT NULL, 3 DEPT_NAME VARCHAR(50), 4 PRIMARY KEY (DEPT_ID) 5 ); Figure: A sample of the UnixUsage schema. 1 INSERT INTO DEPT_INFO VALUES (0, ''); 2 INSERT INTO DEPT_INFO VALUES (NULL, ''); Figure: Data generated by SchemaAnalyst.
29. Automatically Testing Schemas 1 CREATE TABLE DEPT_INFO ( 2 DEPT_ID INTEGER NOT NULL, 3 DEPT_NAME VARCHAR(50), 4 PRIMARY KEY (DEPT_ID) 5 ); Figure: A sample of the UnixUsage schema. 1 INSERT INTO DEPT_INFO VALUES (0, ''); 2 INSERT INTO DEPT_INFO VALUES (NULL, ''); Figure: Data generated by SchemaAnalyst.
30. Automatically Testing Schemas 1 CREATE TABLE DEPT_INFO ( 2 DEPT_ID INTEGER NOT NULL, 3 DEPT_NAME VARCHAR(50), 4 PRIMARY KEY (DEPT_ID) 5 ); Figure: A sample of the UnixUsage schema. 1 INSERT INTO DEPT_INFO VALUES (0, ''); 2 INSERT INTO DEPT_INFO VALUES (NULL, ''); Figure: Data generated by SchemaAnalyst.
31. Automatically Testing Schemas 1 CREATE TABLE DEPT_INFO ( 2 DEPT_ID INTEGER NOT NULL, 3 DEPT_NAME VARCHAR(50), 4 PRIMARY KEY (DEPT_ID) 5 ); Figure: A sample of the UnixUsage schema. 1 INSERT INTO DEPT_INFO VALUES (0, ''); 2 INSERT INTO DEPT_INFO VALUES (NULL, ''); Figure: Data generated by SchemaAnalyst.
32. Automatically Testing Schemas 1 CREATE TABLE DEPT_INFO ( 2 DEPT_ID INTEGER NOT NULL, 3 DEPT_NAME VARCHAR(50), 4 PRIMARY KEY (DEPT_ID) 5 ); Figure: A sample of the UnixUsage schema. 1 INSERT INTO DEPT_INFO VALUES (0, ''); 2 INSERT INTO DEPT_INFO VALUES (NULL, ''); Figure: Data generated by SchemaAnalyst.
33. Automatically Testing Schemas 1 CREATE TABLE DEPT_INFO ( 2 DEPT_ID INTEGER NOT NULL, 3 DEPT_NAME VARCHAR(50), 4 PRIMARY KEY (DEPT_ID) 5 ); Figure: A sample of the UnixUsage schema. 1 INSERT INTO DEPT_INFO VALUES (0, ''); 2 INSERT INTO DEPT_INFO VALUES (NULL, ''); Figure: Data generated by SchemaAnalyst.
34. Automatically Testing Schemas 1 CREATE TABLE DEPT_INFO ( 2 DEPT_ID INTEGER NOT NULL, 3 DEPT_NAME VARCHAR(50), 4 PRIMARY KEY (DEPT_ID) 5 ); Figure: A sample of the UnixUsage schema. 1 INSERT INTO DEPT_INFO VALUES (0, ''); 2 INSERT INTO DEPT_INFO VALUES (NULL, ''); Figure: Data generated by SchemaAnalyst.
35. Automatically Testing Schemas 1 CREATE TABLE DEPT_INFO ( 2 DEPT_ID INTEGER NOT NULL, 3 DEPT_NAME VARCHAR(50), 4 PRIMARY KEY (DEPT_ID) 5 ); Figure: A sample of the UnixUsage schema. 1 INSERT INTO DEPT_INFO VALUES (0, ''); 2 INSERT INTO DEPT_INFO VALUES (NULL, ''); Figure: Data generated by SchemaAnalyst.
36. Automatically Testing Schemas 1 CREATE TABLE DEPT_INFO ( 2 DEPT_ID INTEGER NOT NULL, 3 DEPT_NAME VARCHAR(50), 4 PRIMARY KEY (DEPT_ID) 5 ); Figure: A sample of the UnixUsage schema. 1 INSERT INTO DEPT_INFO VALUES (0, ''); 2 INSERT INTO DEPT_INFO VALUES (NULL, ''); Figure: Data generated by SchemaAnalyst.
37. Automatically Testing Schemas 1 CREATE TABLE DEPT_INFO ( 2 DEPT_ID INTEGER NOT NULL, 3 DEPT_NAME VARCHAR(50), 4 PRIMARY KEY (DEPT_ID) 5 ); Figure: A sample of the UnixUsage schema. 1 INSERT INTO DEPT_INFO VALUES (0, ''); 2 INSERT INTO DEPT_INFO VALUES (NULL, ''); Figure: Data generated by SchemaAnalyst.
38. Real-world Ready Schemas from Firefox and StackOver ow
39. Real-world Ready Schemas from Firefox and StackOver ow Scales to 1,000s of tables and constraints
40. Real-world Ready Schemas from Firefox and StackOver ow Scales to 1,000s of tables and constraints Extensive documentation available on GitHub
41. Real-world Ready Schemas from Firefox and StackOver ow Scales to 1,000s of tables and constraints Extensive documentation available on GitHub SchemaAnalyst provides an e cient means of gener- ating test data for real-world database applications
42. Usage Tool Demo
43. Key Contributions SchemaAnalyst: an open-source test data generator for relational database schemas
44. Key Contributions SchemaAnalyst: an open-source test data generator for relational database schemas Extensible to new data generators, coverage criteria, and database management systems
45. Key Contributions SchemaAnalyst: an open-source test data generator for relational database schemas Extensible to new data generators, coverage criteria, and database management systems Extensive documentation support- ing the use and modi cation of the tool
46. Key Contributions SchemaAnalyst: an open-source test data generator for relational database schemas Extensible to new data generators, coverage criteria, and database management systems Extensive documentation support- ing the use and modi cation of the tool Enhance the testing of database systems in industry and enable future research!
47. Key Contributions SchemaAnalyst: an open-source test data generator for relational database schemas Extensible to new data generators, coverage criteria, and database management systems Extensive documentation support- ing the use and modi cation of the tool Enhance the testing of database systems in industry and enable future research! https://github.com/schemaanalyst-team/schemaanalyst