Views, stored procedures, triggers are mostly considered as text by DB tools. Consequently, change impact analysis, refactoring and re-architecting implying such entities is badly or not managed at all.
Adopting a Software Engineering approach to manage DB evolution.
Relational databases have existed for several decades, but the management of their evolution seems to remain complex. The ideas behind this PhD subject come from issues encountered by a database architect (DBA).
The problems encountered by this DBA were initially related to the implementation of views. When a view has to be modified in PostgreSQL, it has to be deleted and then recreated. The problem is that if this view is used by another view, the dependant view has to be deleted and recreated as well. When you have a lot of views depending on each others, it becomes hard to modify them. By further questioning the DBA, we realised that he was facing other issues related to stored procedures or triggers.
A study of the literature and commercial solutions for RDBMS management suggested us that a “database approach” is adopted by those solutions. That is to say, they provide utilities to create/modify/destroy entities in a database and to do a dependency analysis based on the meta-data provided by the RDBMS. Thee three main problems with this approach are developed below...
Dependencies concerning stored procedures are unknown by these tools because their source code are stored as plain text in the RDBMS meta-data
These tools warn user that a dependency makes a change impossible but do not propose a solution to allow the change to be integrated
No visualisation of dependencies is provided to DBAs which leads them to build the dependency graph manually in order to evaluate the impact of the change to apply
These issues in RDBMS tools make the maintenance of databases containing a lot of entities difficult for DBAs. The lack of support for automated or semi-automated change integration in the database makes the maintenance even more difficult.
My PhD aims to help Database Architects during the implementation and evolution of their databases. In this context, tools and techniques will be developed to:
Perform Impact Analysis inside DB and on client applications
Apply modifications automatically or semi-automatically on DB schemas
Find quality defects in DB schemas
Re-architect DB schemas
A tool to manage connections to databases.
A tool to check that a database schema respects a list of predefined properties.
A tool to benchmark queries and do statistical analysis of results.
A tool to build a model of the database under analysis using the meta-data tables of PostgreSQL
A parser for PostgreSQL allowing to build an AST to perform static analysis.
A meta-model for relational databases.
You can contact me by mail julien[dot]delplanque[at]inria[dot]fr. If you live in Belgium or in the north of France, we can even meet for real if needed!