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. 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. 7.

    Relational Databases Databases are everywhere! Database Application Server Mobile Phone

    or Tablet O ce and Productivity Software Government Astrophysics
  3. 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!
  4. 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.
  5. 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.
  6. 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.
  7. 14.

    Testing Database Schemas Manual testing is onerous and error prone

    DBMonster only supports one DMBS Crashes and poor constraint coverage
  8. 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!
  9. 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
  10. 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.
  11. 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.
  12. 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.
  13. 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.
  14. 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.
  15. 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.
  16. 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.
  17. 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.
  18. 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.
  19. 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.
  20. 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.
  21. 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.
  22. 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.
  23. 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.
  24. 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.
  25. 40.

    Real-world Ready Schemas from Firefox and StackOver ow Scales to

    1,000s of tables and constraints Extensive documentation available on GitHub
  26. 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
  27. 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
  28. 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
  29. 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!
  30. 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