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.
1. How DB entities such as tables, views or stored procedures are used in the wild?
2. What are common issues encountered by DBAs?
3. What is the extent of the common issues encountered by DBAs?
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
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.
Provide a data-less PostgreSQL dump of your database(s)
Report an issue you have while developing your database
Answer this survey about your usage of DB schema's entities
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 firstname.lastname@example.org. If you live in Belgium or in the north of France, we can even meet for real if needed!
I will participate to PgDay Paris 2018 Meeting @ to meet the PostgreSQL community.
I was at BENEVOL 2017 Workshop @ Antwerp to give a presentation named: "Software Engineering Issues in RDBMS, a Preliminary Survey".