Virtual Mutation Analysis of Relational Database Schemas University - - PowerPoint PPT Presentation
Virtual Mutation Analysis of Relational Database Schemas University of Sheffield Phil McMinn Allegheny College Gregory M. Kapfhammer University of Sheffield Chris J. Wright Relational Databases Why Should We (Still) Care?
Phil McMinn Gregory M. Kapfhammer Chris J. Wright University of Sheffield Allegheny College University of Sheffield
A vital component of many software systems Despite the wave of interest in “NoSQL” technologies, Relational Databases are still popular (and faster) For developers: schemas provide self-documentation
A vital component of many software systems Despite the wave of interest in “NoSQL” technologies, Relational Databases are still popular (and faster) For developers: schemas provide self-documentation
Table
Table Column and data type
Prevent invalid data being entered into the database Encode domain logic
Prevent invalid data being entered into the database Encode domain logic
Correctly accepted by the schema Correctly rejected by the schema Correctly rejected by the schema
Correctly accepted by the schema Correctly rejected by the schema Correctly rejected by the schema
Correctly accepted by the schema Correctly rejected by the schema Correctly rejected by the schema
Correctly accepted by the schema Correctly rejected by the schema Correctly rejected by the schema
To trap common errors when designing a schema
For example: lack of uniqueness property on usernames, out of range values
To trap common errors when designing a schema
For example: lack of uniqueness property on usernames, out of range values
To test development behaviour vs deployment
DBMSs have subtly different behaviors
To trap common errors when designing a schema
For example: lack of uniqueness property on usernames, out of range values
To test development behaviour vs deployment
DBMSs have subtly different behaviors
Nobody throws away a database of data
To test the success of database migrations
To trap common errors when designing a schema
For example: lack of uniqueness property on usernames, out of range values
To test development behaviour vs deployment
DBMSs have subtly different behaviors
Nobody throws away a database of data
To test the success of database migrations
Industry advice
Destroying database consistency can have huge cost implications
Once a test suite has been created, its fault finding capability can be estimated with mutation analysis.
For relational database schema testing, mutants are created by making small changes to the schema
Once a test suite has been created, its fault finding capability can be estimated with mutation analysis.
For relational database schema testing, mutants are created by making small changes to the schema
Once a test suite has been created, its fault finding capability can be estimated with mutation analysis.
For relational database schema testing, mutants are created by making small changes to the schema
Once a test suite has been created, its fault finding capability can be estimated with mutation analysis.
For relational database schema testing, mutants are created by making small changes to the schema
schema mutants
schema mutants mutant test suite database
Mutant killed / alive
+
schema mutants mutant test suite database
Mutant killed / alive
+
SchemaAnalyst
schema mutants mutant test suite database
Mutant killed / alive
+
SchemaAnalyst
schema mutants mutant test suite database
Mutant killed / alive
+
SchemaAnalyst
schema mutants mutant test suite database
Mutant killed / alive
+
SchemaAnalyst
H i g h c
t
c
m u n i c a t i n g w i t h t h e D B M S a n d e x e c u t i n g S Q L q u e r i e s
i t
schema mutants mutant test suite database
Mutant killed / alive
+
SchemaAnalyst
schema mutants mutant test suite database
Mutant killed / alive
+
SchemaAnalyst
L
a l , n
m u n i c a t i
e r h e a d
schema mutants mutant test suite database
Mutant killed / alive
+
SchemaAnalyst
model of
schema mutants mutant test suite database
Mutant killed / alive
+
SchemaAnalyst
model of
schema mutants mutant test suite database
Mutant killed / alive
+
SchemaAnalyst
model of
L
e r e x e c u t i
e r h e a d
Integrity constraint predicate icp1
icp2 icp3
Integrity constraint predicate icp1
icp2 icp3 icp4 icp5
Integrity constraint predicate icp1
Form an acceptance predicate for the table:
icp2 icp3 icp4 icp5
ap = icp1 /\ icp2 /\ icp3 /\ icp4 /\ icp5 Integrity constraint predicate icp1
Form an acceptance predicate for the table:
icp2 icp3 icp4 icp5
ap = icp1 /\ icp2 /\ icp3 /\ icp4 /\ icp5 Integrity constraint predicate icp1
True when DBMS would accept the data False otherwise
approach is run for as long as the virtual one?
Standard Virtual 1 1 1 1 1 1 HyperSQL PostgreSQL SQLite C
f e e O r d e r s E m p l
e e I n v e n t
y I s
1 6 6 J W h
s S e r v e r M
i l l a P e r m i s s i
s N i s t W e a t h e r P e r s
P r
u c t s C
f e e O r d e r s E m p l
e e I n v e n t
y I s
1 6 6 J W h
s S e r v e r M
i l l a P e r m i s s i
s N i s t W e a t h e r P e r s
P r
u c t s Database Schema Mutation Analysis Time (Log Transformed)
Standard Virtual 1 1 1 1 1 1 HyperSQL PostgreSQL SQLite C
f e e O r d e r s E m p l
e e I n v e n t
y I s
1 6 6 J W h
s S e r v e r M
i l l a P e r m i s s i
s N i s t W e a t h e r P e r s
P r
u c t s C
f e e O r d e r s E m p l
e e I n v e n t
y I s
1 6 6 J W h
s S e r v e r M
i l l a P e r m i s s i
s N i s t W e a t h e r P e r s
P r
u c t s Database Schema Mutation Analysis Time (Log Transformed)
Virtual Mutation Analysis is significantly more efficient for Postgres and HyperSQL, but not SQLite
−100 −50 50 100 50 100 150 Number of Mutants Percentage of Mean Time Saved HyperSQL PostgreSQL SQLite
−100 −50 50 100 50 100 150 Number of Mutants Percentage of Mean Time Saved HyperSQL PostgreSQL SQLite
Virtual Mutation Analysis yields large time savings for Postgres and HyperSQL but not always with SQLite, leading to an average time saving of 51% overall
HyperSQL PostgreSQL SQLite 5 1 1 5 C
f e e O r d e r s E m p l
e e I n v e n t
y I s
1 6 6 J W h
s S e r v e r M
i l l a P e r m i s s i
s N i s t W e a t h e r P e r s
P r
u c t s C
f e e O r d e r s E m p l
e e I n v e n t
y I s
1 6 6 J W h
s S e r v e r M
i l l a P e r m i s s i
s N i s t W e a t h e r P e r s
P r
u c t s C
f e e O r d e r s E m p l
e e I n v e n t
y I s
1 6 6 J W h
s S e r v e r M
i l l a P e r m i s s i
s N i s t W e a t h e r P e r s
P r
u c t s Database Schema Total Number of Mutants Selective Virtual
HyperSQL PostgreSQL SQLite 5 1 1 5 C
f e e O r d e r s E m p l
e e I n v e n t
y I s
1 6 6 J W h
s S e r v e r M
i l l a P e r m i s s i
s N i s t W e a t h e r P e r s
P r
u c t s C
f e e O r d e r s E m p l
e e I n v e n t
y I s
1 6 6 J W h
s S e r v e r M
i l l a P e r m i s s i
s N i s t W e a t h e r P e r s
P r
u c t s C
f e e O r d e r s E m p l
e e I n v e n t
y I s
1 6 6 J W h
s S e r v e r M
i l l a P e r m i s s i
s N i s t W e a t h e r P e r s
P r
u c t s Database Schema Total Number of Mutants Selective Virtual
Virtual Mutation Analysis evaluates more mutants
Selective Virtual 0.00 0.25 0.50 0.75 1.00 0.00 0.25 0.50 0.75 1.00 0.00 0.25 0.50 0.75 1.00 HyperSQL PostgreSQL SQLite CoffeeOrders Employee Inventory Iso3166 JWhoisServer MozillaPermissions NistWeather Person Products CoffeeOrders Employee Inventory Iso3166 JWhoisServer MozillaPermissions NistWeather Person Products Database Schema Mutation Score
Selective Virtual 0.00 0.25 0.50 0.75 1.00 0.00 0.25 0.50 0.75 1.00 0.00 0.25 0.50 0.75 1.00 HyperSQL PostgreSQL SQLite CoffeeOrders Employee Inventory Iso3166 JWhoisServer MozillaPermissions NistWeather Person Products CoffeeOrders Employee Inventory Iso3166 JWhoisServer MozillaPermissions NistWeather Person Products Database Schema Mutation Score
Virtual Mutation Analysis is the best option when highly accurate scores are needed under a time constraint
Virtual Mutation Analysis Technique:
Removes the need to use a real DBMS for relational database schema mutation testing More cost-effective while still being accurate: