Automatic Recommendations for Evolving Relational Databases Schema

TL;DR

DBEvolution tackles the challenge of evolving relational database schemas without introducing intermediate inconsistencies. It introduces a dependency-aware schema framework and a rich set of operators, including behavior-focused changes to stored procedures and views, to automatically compute impacts and generate a valid SQL patch. The approach is validated on a real PostgreSQL database, showing substantial time savings (roughly 75%) in reproducing complex migrations and supporting modularization tasks. The work advances database engineering by extending schema evolution tooling to cover behavioral entities and by providing a structured patch generation workflow that preserves integrity throughout the evolution process.

Abstract

Relational databases play a central role in many information systems. Their schema contains structural (e.g. tables and columns) and behavioral (e.g. stored procedures or views) entity descriptions. Then, just like for ``normal'' software, changes in legislation, offered functionalities, or functional contexts, impose to evolve databases and their schemas. But in some scenarios, it is not so easy to deconstruct a wished evolution of the schema into a precise sequence of operations. Changing a database schema may impose manually dropping and recreating dependent entities, or manually searching for dependencies in stored procedures. This is important because getting even the order of application of the operators can be difficult and have profound consequences. This meta-model allows us to compute the impact of planned changes and recommend additional changes that will ensure that the RDBMS constraints are always verified. The recommendations can then be compiled into a valid SQL patch actually updating the database schema in an orderly way. We replicated a past evolution showing that, without detailed knowledge of the database, we could perform the same change in 75\% less time than the expert database architect. We also exemplify the use of our approach on other planned changes.

Figures (7)

• Figure 1: Coarse-grain illustration of the approach
• Figure 2: Structural entities of the meta-model.
• Figure 3: Behavioral entities of the meta-model
• Figure 4: A small example of dependencies between a table, two views, and a stored procedure
• Figure 5: Recommendations selection for renaming person.uid on the example of Figure \ref{['fig:exampledb']}