DEPICT: DiscovEring Patterns and InteraCTions in databases

1. DEPICT: DiscovEring Patterns and InteraCTions in databases A tool for testing data-intensive systems

2. Problem context (1) • Modern enterprise class systems are heavily based on RDBMS (Relational DataBase Management System). • Each one of those systems can have its own subsystem for enforcing the consistency and the integrity of the data. Mainly they are in the form of: • Business Logic Layer (often built on top of a middleware) • Business Rules (declarative approach) • Constraints and procedures at the RDBMS level • Any combination of the previous ones • No matter how those subsystems are implemented, their role is vital to the whole system. DEPICT - DiscovEring Patterns and InteraCTions in databases

3. Problem context (2) • So a crucial question, in a testing environment, is: «How can we reliably verify the coverage of a test suite?» • The wide variety and complexity of those subsystems makes really hard to devise a common test strategy • Since we are mainly interested in the integrity of the data why not directly involving it in the test process? DEPICT - DiscovEring Patterns and InteraCTions in databases

4. The idea behind • We propose the use of classification tree models: they are simple and intuitive. • They are effective because they: • Present a high-level view • Are Visual • In DEPICT a classification tree models a «Data Interaction». • A «Data Interaction» is a set of values coming from different fields in different tables of a database schema. DEPICT - DiscovEring Patterns and InteraCTions in databases

5. The overall process Specify a data interaction Create or import test cases from an external repository Import data interaction model as well as test cases into DEPICT 1 2 3 4 Run checks and analyze the results DEPICT - DiscovEring Patterns and InteraCTions in databases

6. An overview of the tool (1) DEPICT - DiscovEring Patterns and InteraCTions in databases DEPICT is realized as an Eclipse RCP (Rich Client Platform) Application. It makes use of CTE-XL (Berner & MattnerSystemtechnikGmbH) for classification tree modelling and test cases management. Developed in collaboration with the Directorate of Norwegian Customs and Excise, Oslo, Norway

7. DEPICT features Different DBs Located anywhere DEPICT - DiscovEring Patterns and InteraCTions in databases

8. An overview of the tool (2) Interaction Coverage table Interaction Coverage graphs Navigator view for browsing the workspace across projects and imported model E-R Model Graph of the imported data interaction Dynamic filtering over data interactions DEPICT - DiscovEring Patterns and InteraCTions in databases

9. An overview of the tool (3) Let’s examine the tool through an industrial case study from Norwegian Customs and Excise (NCE) with this concrete use case: «Do our test cases cover alcohol importation test requirement?» DEPICT - DiscovEring Patterns and InteraCTions in databases

10. Step 1: Data Interaction model By the means of CTE XL we create the classification tree. The first two levels of the tree represent the catalog and the schema (optional) names (useful for accessing the database metadata). Third level: DB table names. Fourth level: Table column names Last level: actual values of the columns relevant to the data interaction. The model defines data interactions relative to the importation of four specific alcohols and two specific categories of declaration. Test focuses on: Whisky, Vodka, Rum, Beer Selected categories: MA: Manual inspection FU: Processed declarations DEPICT - DiscovEring Patterns and InteraCTions in databases

11. Step 2: Test cases definition Below the model the test engineer can create the set of test cases related to the data interaction by simply clicking on the values intersection. In the picture we see an example of 4 test cases related to alcohols whose importation has been manually inspected. DEPICT - DiscovEring Patterns and InteraCTions in databases

12. Step 3: Import model and test cases (1) • Importing a CTE-XL model by following the instructions of a wizard procedure. • DEPICT will guide the user in the creation of the needed information for establishing a connection toward the DB server. • DEPICT can manage practically any DB server provided that a JDBC driver for that server exists. Current version has been tested with: Sybase, MySQL and PostgreSQL. • You can have multiple DB connections within the same workspace and/or project. DEPICT - DiscovEring Patterns and InteraCTions in databases

13. Step 3: Import model and test cases (2) DEPICT - DiscovEring Patterns and InteraCTions in databases • During the import procedure DEPICT will: • Validate the CTE-XL model against the DB metadata. • Translate the classification tree in a graph according to the E-R (Entity-Relationship) model in order to obtain a set of feasible and effective SQL statements. • Make a copy of the model within its workspace.

14. Step 4: Run tests and analyze results (1) This is the navigator view in which the user can browse the workspace across multiple projects and models. A click on a model will trigger an update of all the views DEPICT - DiscovEring Patterns and InteraCTions in databases

15. Step 4: Run tests and analyze results (2) Interaction coverage view Filter to highlight holes in data Expression used for probing the data interaction coverage. DEPICT - DiscovEring Patterns and InteraCTions in databases

16. Step 4: Run tests and analyze results (3) Here we see, for example, that the test cases: «Manual inspection» of «Rum» and «Processed inspection» of Vodka haven’t been covered. DEPICT - DiscovEring Patterns and InteraCTions in databases Selecting the filter «Holes in data» we obtain the list of test cases not covered by the current data set which answers to our initial question.

17. Step 4: Run tests and analyze results (5) DEPICT - DiscovEring Patterns and InteraCTions in databases

18. Further use case scenarios DEPICT - DiscovEring Patterns and InteraCTions in databases • By the means of DEPICT they: • Can create a DEPICT model, for each «Mask Control», verifying if related test cases have been covered. • Easily organize DEPICT models as a library of models ready to be used • Mantain a traceability from rules to tests • Document tests outcome • Check test cases coverage as new live data arrives

19. Main features DEPICT - DiscovEring Patterns and InteraCTions in databases Full Eclipse integration Graphical editor for classification tree and test cases Management of multiple data sources Display test case coverage in tabular and graphical way Automatically creates complex SQL queries out of simple classification trees

20. Future works DEPICT - DiscovEring Patterns and InteraCTions in databases Model comparison: look for differences between DBs (useful for example for regression testing) Managing multiple relationships between a pair of tables Improving the expressiveness of the model through the use of a Domain Specific Language (DSL): numerical values ranges, enumerated values, ... Scheduled execution for time consuming tests that could run overnight.