Tweet
Tweet

Slide 1

Slide 1 text

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

Slide 2

Slide 2 text

Relational Databases Databases are everywhere!

Slide 3

Slide 3 text

Relational Databases Databases are everywhere! Database Application Server

Slide 4

Slide 4 text

Relational Databases Databases are everywhere! Database Application Server Mobile Phone or Tablet

Slide 5

Slide 5 text

Relational Databases Databases are everywhere! Database Application Server Mobile Phone or Tablet O ce and Productivity Software

Slide 6

Slide 6 text

Relational Databases Databases are everywhere! Database Application Server Mobile Phone or Tablet O ce and Productivity Software Government

Slide 7

Slide 7 text

Relational Databases Databases are everywhere! Database Application Server Mobile Phone or Tablet O ce and Productivity Software Government Astrophysics

Slide 8

Slide 8 text

Relational Databases Databases are everywhere! Database Application Server Mobile Phone or Tablet O ce and Productivity Software Government Astrophysics Over 1,000,000 posts!

Slide 9

Slide 9 text

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.

Slide 10

Slide 10 text

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.

Slide 11

Slide 11 text

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.

Slide 12

Slide 12 text

Testing Database Schemas Manual testing is onerous and error prone

Slide 13

Slide 13 text

Testing Database Schemas Manual testing is onerous and error prone DBMonster only supports one DMBS

Slide 14

Slide 14 text

Testing Database Schemas Manual testing is onerous and error prone DBMonster only supports one DMBS Crashes and poor constraint coverage

Slide 15

Slide 15 text

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!

Slide 16

Slide 16 text

SchemaAnalyst

Slide 17

Slide 17 text

SchemaAnalyst Coverage Criterion

Slide 18

Slide 18 text

SchemaAnalyst Coverage Criterion Data Generator

Slide 19

Slide 19 text

SchemaAnalyst Coverage Criterion Data Generator Database Schema

Slide 20

Slide 20 text

SchemaAnalyst Coverage Criterion Data Generator Database Schema JUnit Test Suite

Slide 21

Slide 21 text

SchemaAnalyst Coverage Criterion Data Generator Database Schema JUnit Test Suite Extensible tool for test data generation

Slide 22

Slide 22 text

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

Slide 23

Slide 23 text

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.

Slide 24

Slide 24 text

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.

Slide 25

Slide 25 text

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.

Slide 26

Slide 26 text

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.

Slide 27

Slide 27 text

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.

Slide 28

Slide 28 text

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.

Slide 29

Slide 29 text

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.

Slide 30

Slide 30 text

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.

Slide 31

Slide 31 text

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.

Slide 32

Slide 32 text

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.

Slide 33

Slide 33 text

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.

Slide 34

Slide 34 text

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.

Slide 35

Slide 35 text

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.

Slide 36

Slide 36 text

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.

Slide 37

Slide 37 text

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.

Slide 38

Slide 38 text

Real-world Ready Schemas from Firefox and StackOver ow

Slide 39

Slide 39 text

Real-world Ready Schemas from Firefox and StackOver ow Scales to 1,000s of tables and constraints

Slide 40

Slide 40 text

Real-world Ready Schemas from Firefox and StackOver ow Scales to 1,000s of tables and constraints Extensive documentation available on GitHub

Slide 41

Slide 41 text

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

Slide 42

Slide 42 text

Usage Tool Demo

Slide 43

Slide 43 text

Key Contributions SchemaAnalyst: an open-source test data generator for relational database schemas

Slide 44

Slide 44 text

Key Contributions SchemaAnalyst: an open-source test data generator for relational database schemas Extensible to new data generators, coverage criteria, and database management systems

Slide 45

Slide 45 text

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

Slide 46

Slide 46 text

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!

Slide 47

Slide 47 text

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