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.

Research questions I address at the moment

help

1. How DB entities such as tables, views or stored procedures are used in the wild?

help

2. What are common issues encountered by DBAs?

help

3. What is the extent of the common issues encountered by DBAs?

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:

thumbs_up_down

Find quality defects in DB schemas

zoom_out_map

Perform Impact Analysis inside DB and on client applications

build

Apply modifications automatically or semi-automatically on DB schemas

dashboard

Re-architect DB schemas

How can you help me?

To achieve such an ambitious project, I need your help. By helping me, you are not only helping Software Engineering researches to make progress (which aims to make your life easier by creating tools and technique to help programmers in their daily tasks) but you will also get new open-source tools to help you during database development and evolution.

insert_drive_file

Provide a data-less PostgreSQL dump of your database(s)

report_problem

Report an issue you have while developing your database

check_box

Answer this survey about your usage of DB schema's entities

Open-source tools developed

  • thumbs_up_down
    DBCritics

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

  • search
    PostgreSQL parser

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

Contact me

You can contact me by mail julien.delplanque@inria.fr. If you live in Belgium or in the north of France, we can even meet for real if needed!

News feed

  • 17/11/2017
    Participating to BENEVOL 2017

    I was at BENEVOL 2017 Workshop @ Antwerp to give a presentation named: "Software Engineering Issues in RDBMS, a Preliminary Survey"