Command-form Coverage for Testing DB Applications Alessandro Orso - PowerPoint PPT Presentation

Command-form Coverage for Testing DB Applications Alessandro Orso William G.J. Halfond Georgia Institute of Technology Supported by NSF awards CCR- 0205422 and CCR-0306372 to GA Tech and by DHS and US Air Force under Contract No. FA8750-05-C-0179.

A Database Application User Interface Application Database Alex Orso – ASE 2006 – September 2006

A Database Application ResultSet srchBook (String searchString, int searchType, bool showRating, bool grpByRating, bool grpByISBN) { UI Application DB String[] srchFields = {"tiitle", "author", "isbn"}; String queryStr = "SELECT title, author, description"; if (showRating) 1) SELECT title, author, queryStr += ", avg(rating) "; description, avg(rating) FROM queryStr += "FROM books WHERE "; books WHERE isbn = <*> if (searchType==2) GROUP BY isbn queryStr += srchFields[searchType] + " = " + searchString; 2) SELECT title, author, else description, avg(rating) FROM queryStr += searchFields[searchType] books WHERE author = ‘ <*> ’ + " = ’“ + searchString + "’ "; . . if (grpByRating) . queryStr += "GROUP BY rating "; 18) SELECT title, author, else if (grpByISBN) description, avg(rating) FROM queryStr += " GROUP BY isbn "; books WHERE author = ‘ <*> ’ return db.executeQuery(queryStr); GROUP BY rating } Alex Orso – ASE 2006 – September 2006

Faults in Generated DB Commands ResultSet srchBook (String searchString, int searchType, bool showRating, bool grpByRating, bool grpByISBN) { String[] srchFields = {"tiitle", "author", "isbn"}; 1. Misspelled column String queryStr = name “tiitle,” "SELECT title, author, description"; 2. Missing delimiter for a if (showRating) queryStr += ", avg(rating) "; concatenation queryStr += "FROM books WHERE "; 3. Lack of “GROUP BY” if (searchType==2) queryStr += srchFields[searchType] + clause for grouping " = " + searchString; function else 4. Missing delimiter queryStr += searchFields[searchType] + " = ’“ + searchString + "’ "; unless one specific if (grpByRating) line is executed queryStr += "GROUP BY rating "; else if (grpByISBN) queryStr += " GROUP BY isbn "; return db.executeQuery(queryStr); } Alex Orso – ASE 2006 – September 2006

Faults in Generated DB Commands ResultSet srchBook (String searchString, int searchType, bool showRating, bool grpByRating, bool grpByISBN) { String[] srchFields = {"tiitle", "author", "isbn"}; 1. Misspelled column String queryStr = name “tiitle,” "SELECT title, author, description"; 2. Missing delimiter for a if (showRating) queryStr += ", avg(rating) "; concatenation queryStr += "FROM books WHERE "; 3. Lack of “GROUP BY” if (searchType==2) queryStr += srchFields[searchType] + clause for grouping " = " + searchString; function else 4. Missing delimiter queryStr += searchFields[searchType] + " = ’“ + searchString + "’ "; unless one specific if (grpByRating) line is executed queryStr += "GROUP BY rating "; else if (grpByISBN) queryStr += " GROUP BY isbn "; return db.executeQuery(queryStr); } Alex Orso – ASE 2006 – September 2006

Faults in Generated DB Commands ResultSet srchBook (String searchString, int searchType, bool showRating, bool grpByRating, bool grpByISBN) { String[] srchFields = {"tiitle", "author", "isbn"}; 1. Misspelled column String queryStr = name “tiitle,” "SELECT title, author, description"; 2. Missing delimiter for a if (showRating) queryStr += ", avg(rating) "; concatenation queryStr += "FROM books WHERE "; 3. Lack of “GROUP BY” if (searchType==2) queryStr += srchFields[searchType] + clause for grouping " = " + searchString; function else 4. Missing delimiter queryStr += searchFields[searchType] + " = ’“ + searchString + "’ "; unless one specific if (grpByRating) line is executed queryStr += "GROUP BY rating "; else if (grpByISBN) queryStr += " GROUP BY isbn "; return db.executeQuery(queryStr); } Alex Orso – ASE 2006 – September 2006

Faults in Generated DB Commands ResultSet srchBook (String searchString, int searchType, bool showRating, bool grpByRating, bool grpByISBN) { String[] srchFields = {"tiitle", "author", "isbn"}; 1. Misspelled column String queryStr = name “tiitle,” "SELECT title, author, description"; 2. Missing delimiter for a if (showRating) queryStr += ", avg(rating) "; concatenation queryStr += "FROM books WHERE "; 3. Lack of “GROUP BY” if (searchType==2) queryStr += srchFields[searchType] + clause for grouping " = " + searchString; function else 4. Missing delimiter queryStr += searchFields[searchType] + " = ’“ + searchString + "’ "; unless one specific if (grpByRating) line is executed queryStr += "GROUP BY rating "; else if (grpByISBN) queryStr += " GROUP BY isbn "; return db.executeQuery(queryStr); } Alex Orso – ASE 2006 – September 2006

Traditional Testing Test Cases ResultSet srchBook (String searchString, int searchType, bool showRating, bool grpByRating, bool grpByISBN) { String[] srchFields = Queries Generated {"tiitle", "author", "isbn"}; String queryStr = "SELECT title, author, description"; if (showRating) queryStr += ", avg(rating) "; queryStr += "FROM books WHERE "; if (searchType==2) queryStr += srchFields[searchType] + " = " + searchString; else queryStr += searchFields[searchType] + " = ’“ + searchString + "’ "; if (grpByRating) queryStr += "GROUP BY rating "; else if (grpByISBN) Faults Revealed queryStr += " GROUP BY isbn "; return db.executeQuery(queryStr); } Alex Orso – ASE 2006 – September 2006

Traditional Testing Test Cases ResultSet srchBook(String searchString, 1. ("0123", 2, false, false, true) int searchType, bool showRating, bool grpByRating, bool grpByISBN) { String[] srchFields = Queries Generated {"tiitle", "author", "isbn"}; String queryStr = 1. SELECT title, author, "SELECT title, author, description"; descriptionFROM books if (showRating) WHERE isbn = 0123 GROUP queryStr += ", avg(rating) "; BY isbn queryStr += "FROM books WHERE "; if (searchType==2) queryStr += srchFields[searchType] + " = " + searchString; else queryStr += searchFields[searchType] + " = ’“ + searchString + "’ "; if (grpByRating) queryStr += "GROUP BY rating "; else if (grpByISBN) Faults Revealed queryStr += " GROUP BY isbn "; 1. #4 return db.executeQuery(queryStr); } Alex Orso – ASE 2006 – September 2006

Traditional Testing Test Cases ResultSet srchBook(String searchString, 1. ("0123", 2, false, false, true) int searchType, bool showRating, 2. (“Poe", 1, false, false, false) bool grpByRating, bool grpByISBN) { String[] srchFields = Queries Generated {"tiitle", "author", "isbn"}; String queryStr = 1. SELECT title, author, "SELECT title, author, description"; descriptionFROM books if (showRating) WHERE isbn = 0123 GROUP queryStr += ", avg(rating) "; BY isbn queryStr += "FROM books WHERE "; if (searchType==2) 2. SELECT title, author, queryStr += srchFields[searchType] + descriptionFROM books " = " + searchString; WHERE author = ‘Poe’ else queryStr += searchFields[searchType] + " = ’“ + searchString + "’ "; if (grpByRating) queryStr += "GROUP BY rating "; else if (grpByISBN) Faults Revealed queryStr += " GROUP BY isbn "; 1. #4 return db.executeQuery(queryStr); 2. #4 } Alex Orso – ASE 2006 – September 2006

Traditional Testing Test Cases ResultSet srchBook(String searchString, 1. ("0123", 2, false, false, true) int searchType, bool showRating, 2. (“Poe", 1, false, false, false) bool grpByRating, bool grpByISBN) { 3. (“Poe", 1, true, true, false) String[] srchFields = Queries Generated {"tiitle", "author", "isbn"}; String queryStr = 1. SELECT title, author, "SELECT title, author, description"; descriptionFROM books if (showRating) WHERE isbn = 0123 GROUP queryStr += ", avg(rating) "; BY isbn queryStr += "FROM books WHERE "; if (searchType==2) 2. SELECT title, author, queryStr += srchFields[searchType] + descriptionFROM books " = " + searchString; WHERE author = ‘Poe’ else 3. SELECT title, author, queryStr += searchFields[searchType] description, avg(rating) FROM + " = ’“ + searchString + "’ "; if (grpByRating) books WHERE author = ‘Poe’ queryStr += "GROUP BY rating "; GROUP BY rating else if (grpByISBN) Faults Revealed queryStr += " GROUP BY isbn "; 1. #4 return db.executeQuery(queryStr); 2. #4 } 3. None Alex Orso – ASE 2006 – September 2006