Software engineering techniques applied to databases

Problematic

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.

Proposal

Adopting a Software Engineering approach to manage DB evolution.

Context

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...

Issues of tools using a database-approach

error

Dependencies concerning stored procedures are unknown by these tools because their source code are stored as plain text in the RDBMS meta-data

error

These tools warn user that a dependency makes a change impossible but do not propose a solution to allow the change to be integrated

error

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.

Goals

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:

zoom_out_map

Perform Impact Analysis inside DB and on client applications

build

Apply modifications automatically or semi-automatically on DB schemas

thumbs_up_down

Find quality defects in DB schemas

dashboard

Re-architect DB schemas

Open-source tools developed

  • build
    DBConnectionsManager

    A tool to manage connections to databases.

  • thumbs_up_down
    DBCritics

    A tool to check that a database schema respects a list of predefined properties.

  • build
    DBQueryBenchmarker

    A tool to benchmark queries and do statistical analysis of results.

  • build
    PgMetadata

    A tool to build a model of the database under analysis using the meta-data tables of PostgreSQL

  • search
    PostgreSQL parser

    A parser for PostgreSQL allowing to build an AST to perform static analysis.

  • build
    SQL Meta-model

    A meta-model for relational databases.

Contact me

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!