CodeCritics applied to database schema: Challenges and first results

International audience—Relational databases (DB) play a critical role in many information systems. For different reasons, their schemas gather not only tables and columns but also views, triggers or stored functions (i.e., fragments of code describing treatments). As for any other code-related artefact, software quality in a DB schema helps avoiding future bugs. However, few tools exist to analyse DB quality and prevent the introduction of technical debt. Moreover, these tools suffer from limitations like the difficulty to deal with some entities (e.g., functions) or dependencies between entities. This paper presents research issues related to assessing the software quality of a DB schema by adapting existing source code analysis research to database schemas. We present preliminary results that have been validated through the implementation of DBCritics, a prototype tool to perform static analysis on the SQL source code of a database schema. DBCritics addresses the limitations of existing DB quality tools based on an internal representation considering all entities of the database and their relationships

Modeling and Analysis of SQL Queries in PHP Systems

PHP is a common language used for creating dynamic websites. These websites often include the use of databases to store data, with embedded SQL queries constructed within the PHP code and executed through the use of database access libraries. One of these libraries is the original MySQL library that, despite not being supported in current versions of PHP, is still widely used in existing PHP code. As a first step towards developing program comprehension and transformation tools for PHP systems that use this library, this research presents a query modeling tool that models embedded SQL queries in PHP systems and an empirical study conducted through analysis of these models. A main focus of this study was to establish common patterns developers use to construct SQL queries and to extract information about their occurrences in actual PHP systems. Using these patterns, the parts of queries that are generally static, and the parts that are often computed at runtime were extracted. For dynamically computed query parts, we also extracted data about which PHP language features are used to construct them. Finally, information about which clauses most often differ based on control flow was extracted as well as counts for how often each SQL query type and SQL clause is used in practice. We believe this information is useful for future work on building program understanding and transformation tools to renovate PHP code using database libraries

Automatically Documenting Software Artifacts

Software artifacts, such as database schema and unit test cases, constantly change during evolution and maintenance of software systems. Co-evolution of code and DB schemas in Database-Centric Applications (DCAs) often leads to two types of challenging scenarios for developers, where (i) changes to the DB schema need to be incorporated in the source code, and (ii) maintenance of a DCAs code requires understanding of how the features are implemented by relying on DB operations and corresponding schema constraints. On the other hand, the number of unit test cases often grows as new functionality is introduced into the system, and maintaining these unit tests is important to reduce the introduction of regression bugs due to outdated unit tests. Therefore, one critical artifact that developers need to be able to maintain during evolution and maintenance of software systems is up-to-date and complete documentation. In order to understand developer practices regarding documenting and maintaining these software artifacts, we designed two empirical studies both composed of (i) an online survey of contributors of open source projects and (ii) a mining-based analysis of method comments in these projects. We observed that documenting methods with database accesses and unit test cases is not a common practice. Further, motivated by the findings of the studies, we proposed three novel approaches: (i) DBScribe is an approach for automatically documenting database usages and schema constraints, (ii) UnitTestScribe is an approach for automatically documenting test cases, and (iii) TeStereo tags stereotypes for unit tests and generates html reports to improve the comprehension and browsing of unit tests in a large test suite. We evaluated our tools in the case studies with industrial developers and graduate students. In general, developers indicated that descriptions generated by the tools are complete, concise, and easy to read. The reports are useful for source code comprehension tasks as well as other tasks, such as code smell detection and source code navigation