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

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/

4ae30d49c8cc07e42d5a871efb9bcfba?s=128

Gregory Kapfhammer

October 02, 2016
Tweet

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