Transcript A Conceptual Approach to Database Applications Evolution Anthony Cleve, Anne-France Brogneaux and Jean-Luc Hainaut PReCISE research center University of Namur, Belgium SATToSE 2011, Koblenz, Germany.

A Conceptual Approach to
Database Applications Evolution
Anthony Cleve, Anne-France Brogneaux and Jean-Luc Hainaut
PReCISE research center
University of Namur, Belgium
SATToSE 2011, Koblenz, Germany
Introduction

Database applications


set of programs manipulating a database
subject to continuous evolution to fit ever changing business needs and
technical requirements

Database applications evolution


complex, time-consuming, risky and costly process
co-evolution of several interdepedent artefacts:


conceptual schema, logical schema, physical schema, DDL code, data,
mappings, programs
inter-artefact consistency must be preserved over time
1
Database schemas
2
Schema-programs co-evolution scenarios

CS: conceptual schema; LS: logical schema; PS: physical schema;
XS: external schema; and P a program using XS

Scenario 1: Change in CS without impact on P


Scenario 2: Change in CS, with impact on P


requires manual adaptation of P. impact analysis may be difficult in case of
dynamically generated queries
Scenario 3: Change in LS, with CS unchanged


derivation of new LS and CS/LS mapping, but XS/LS mapping unchanged
frequent scenario, tool-supported program adaptation is possible but very
difficult in case of dynamically generated queries
Scenario 4: change in PS, with CS and LS unchanged

No impact on P
3
In this paper…

Focus


Goals



co-evolution of conceptual schema, logical schema and programs
mitigate the impact of database schema changes on programs
provide automated support for schemas-programs co-evolution, to
reduce cost, effort and risks
Approach



transformation-based derivation of relational logical schema (LS) from
conceptual schema (CS)
automatic derivation of the mapping between CS and LS
automatic generation of a data access API that provides the programs
with a conceptual view to the relational database
4
Our approach
5
Transformation-based logical schema
derivation

Logical design = deriving LS from CS


can be modelled as a chain of semantics-preserving schema
transformations
Transformation plan to obtain a relational LS from a CS
is-a relationships into one-to-one rel-types
 complex rel-types into entity types and one-to-many rel-types
 complex attributes into entity types
 one-to-many and one-to-one rel-types into foreign keys
+ renaming some tables and columns, if needed
+ adding some technical identifiers, if needed

6
Conceptual & logical (relational) schemas
7
Schema mapping derivation

Idea:


propagate mapping « stamps » through successive schema
transformations applied to CS to obtain LS
derive mapping M(CS,LS) between CS and LS
8
Conceptual API generation

Idea: generate a conceptual data manipulation API from M(CS,LS)




providing programs with a conceptual view of the relational database
isolating programs from logical schema changes
facilitating impact analysis of conceptual schema changes
Conceptual API



CS-based class hierarchy
CS-based data navigation
CS-based data modification
9
CS-based class hierarchy
BOOK
Book-id
Title
Publisher
Date-Published
Abstract[0-1]
id: Book-id
0-N
0-N
of
1-1
written
0-N
COPY
Copy-No
Date-Acquired
Location
Nbr-of-Volumes
State
Comment[0-1]
id: of.BOOK
Copy-No
AUTHOR
Origin[0-1]
PERSON
Pid
Name
First-Name
id: Pid
P
0-1
borrowing
0-N
BORROWER
Address
Phone
public class Book
public class Copy
public class Person
public class Author extends Person
public class Borrower extends Person

working hypothesis: each is-a relationship represents a partition
10
CS-based data navigation (by entity type)
BOOK
Book-id
Title
Publisher
Date-Published
Abstract[0-1]
id: Book-id
0-N
0-N
of
1-1
written
0-N
COPY
Copy-No
Date-Acquired
Location
Nbr-of-Volumes
State
Comment[0-1]
id: of.BOOK
Copy-No
AUTHOR
Origin[0-1]
PERSON
Pid
Name
First-Name
id: Pid
P
0-1
borrowing
0-N
BORROWER
Address
Phone
public Vector<Book> getAllBook() {…}
public Vector<Copy> getAllPerson() {…} // returns all authors and borrowers
…
public Book getBookById(String BookId) {…} // standard id
public Person getPersonById(Integer Pid) {…} // standard id
public Copy getCopyById(Book book, Integer copyNo) {…} // hybrid id
public Author getAuthorById(Integer Pid) {…} // inherited id
public Borrower getBorrowerById(Integer Pid) {…} // inherited id
11
CS-based data navigation (by rel. type)
BOOK
Book-id
Title
Publisher
Date-Published
Abstract[0-1]
id: Book-id
0-N
0-N
of
1-1
written
0-N
COPY
Copy-No
Date-Acquired
Location
Nbr-of-Volumes
State
Comment[0-1]
id: of.BOOK
Copy-No
AUTHOR
Origin[0-1]
PERSON
Pid
Name
First-Name
id: Pid
P
0-1
borrowing
0-N
BORROWER
Address
Phone
// in class Copy:
public Book getBookViaOf() {…}
public Borrower getBorrowerViaBorrowing() {…}
// in class Book:
public Vector<Copy> getAllCopyViaOf() {…}
public Vector<Author> getAllAuthorViaWritten() {…}
// in class Author:
public Vector<Book> getAllBookViaWritten() {…}
// in class Borrower:
public Vector<Copy> getAllCopyViaBorrowing() {…}
12
CS-based data modification (create)
BOOK
Book-id
Title
Publisher
Date-Published
Abstract[0-1]
id: Book-id
0-N
0-N
of

1-1
written
0-N
COPY
Copy-No
Date-Acquired
Location
Nbr-of-Volumes
State
Comment[0-1]
id: of.BOOK
Copy-No
AUTHOR
Origin[0-1]
PERSON
Pid
Name
First-Name
id: Pid
P
0-1
borrowing
0-N
BORROWER
Address
Phone
creation arguments: all (inherited) conceptual attributes and roles
Copy c = db.insertCopy(copyNo,…, comment, book);

creation available for leaf entity types only (recursive call to super class)
Author a = db.insertAuthor(pid, name, firstName, origin);
Borrower b = db.insertBorrower(pid, name, firstName, address, phone);
13
CS-based data modification (delete)
BOOK
Book-id
Title
Publisher
Date-Published
Abstract[0-1]
id: Book-id
0-N
0-N
of

0-N
COPY
Copy-No
Date-Acquired
Location
Nbr-of-Volumes
State
Comment[0-1]
id: of.BOOK
Copy-No
AUTHOR
Origin[0-1]
P
0-1
borrowing
0-N
BORROWER
Address
Phone
visible delete() method



1-1
written
PERSON
Pid
Name
First-Name
id: Pid
deletes or disconnects all the entity type instances that reference the entity
type instance to be deleted (similar to delete cascade mode)
starting from the root type
internal deleteFromDB() method


actually removes the entity type instance from the database
starting from the leaf type
14
CS-based data modification (update)
BOOK
Book-id
Title
Publisher
Date-Published
Abstract[0-1]
id: Book-id
0-N
0-N
of

1-1
written
0-N
COPY
Copy-No
Date-Acquired
Location
Nbr-of-Volumes
State
Comment[0-1]
id: of.BOOK
Copy-No
AUTHOR
Origin[0-1]
PERSON
Pid
Name
First-Name
id: Pid
P
0-1
borrowing
0-N
BORROWER
Address
Phone
Update value of (inherited) conceptual attributes and roles
borrower.updateAddress(address);

Connect/disconnect entity type instances
author.connectToBookViaWritten(book);
copy.disconnectFromBorrowerViaBorrowing(borrower);
15
Schema-programs co-evolution scenarios
revisited

CS: conceptual schema; LS: logical schema; PS: physical schema;
XS: external schema; and P a program using XS

Scenario 1: Change in CS without impact on P
automated derivation on new LS and CS/LS mapping (the API), but XS/LS
mapping unchanged
+ easier to start manipulating instances of new CS concepts


Scenario 2: Change in CS, with impact on P


still requires manual adaptation of P, but impact analysis can be done
statically (by the Java compiler)
Scenario 3: Change in LS, with CS unchanged

frequent scenario, tool-supported program adaptation is not necessary, just
regenerate the API from the new CS/LS mapping and you’re done 
16
Tool support

DB-MAIN, version 9 (http://www.db-main.be)






database schemas design and manipulation (GER model)
manual conceptual schema design
automatic logical (and physical) schema derivation
automatic DDL code generation (MySQL, PostGreSQL, DB2, Oracle, etc)
automatic schema mapping propagation and derivation
DB-MAIN Java plugins




schema mapping manipulation and visualization
customized DDL code generation
conceptual API generation (java+jdbc/odbc, tested with MySQL, SQLLite
Interbase, DB2, Oracle)
client programs generation (for systematic testing purposes)
17
Application

Gisele project

eHealth domain, IT support for clinical pathway management



Clinical pathways represented as process models

to be stored in a database and accessed by dedicated programs

Ex: creation, evolution, deletion, analysis of clinical pathways
Conceptual schema of process models


ex. treatment of specific kinds of cancer
inspired by several process modelling languages like BPMN, Yawl, etc.
Continuous schema evolution
stable kernel, but frequent changes for adding various dimensions
 special attention paid to reduce the impact of evolutions on already
developed programs

18
Application: some figures

Representative schema size and mapping complexity

Generated API



50 java classes
> 20.000 lines of code
extended kernel systematically tested using generated client programs
allowing to create, delete, and update clinical pathways in the database
19
Conclusions and perspectives

Conceptual approach to database applications design and evolution



Combining generative and transformational techniques
Co-evolution between database schemas and programs better mastered
Future work






Relaxing some working hypotheses
Better integration of the supporting tools
Application to other data-intensive applications in other domains
Precisely measure the gain od our approach in terms of co-evolution
effort, based on realistic scenarios
Extension of the approach to other evolution scenarios (e.g., migration,
integration)
Possible integration with an ORM technology? (no, I’m joking)
20
ORM technologies are getting very popular
(just a few examples)
C++:
LiteSQL, Debea, dtemplatelib, hiberlite, romic, SOCI, etc.
Delphi: Bold for Delphi, Macrobject DObject, InstantObjects, tiOPF, etc.
Java:
Carbonado, Cayenne, CocoBase, Ebean, EJB 3.0, Enterprise Objects Framework,
Hibernate, iBATIS, JPM2Java, JPOX, Kodo, Object Relational Bridge OpenJPA,
SimpleORM, Spring, TopLink, Torque, GenORMous, etc.
.NET:
ADO.NET Entity Framework, Atlas, Base One Foundation Component Library, BCSEi
ORM Code Generator, Business Logic Toolkit for .NET, Castle ActiveRecord,
DataObjects.Net, CocoBase, Devart LINQ to SQL, DevForce, Developer Express, ECO,
EntityORM, EntitySpaces, Euss, Habanero, iBATIS, Invist, LLBLGen Pro, LightSpeed,
Altova Mapforce, Neo, .netTiers, NConstruct, NHibernate, Opf3, ObjectMapper .NET,
Picasso, OpenAccess, TierDeveloper, Persistor.NET, Quick Objects, Sooda, Subsonic,
Wilson ORMapper, etc.
PHP:
CakePHP, Coughphp, DABL, Data Shuffler, dbphp, Doctrine, dORM, EZPDO, Hormon,
LightOrm, Outle, pdoMap, PersistentObject, PHPSimpleDB, Propel, Rocks, Qcodo,
Redbean, Xyster, etc.
Python: Django, SQLAlchemy, SQLObject, Storm, etc.
Ruby:
Active Record, Ruby on Rails, Datamapper, iBATIS, Sequel, etc.
Pearl:
DBIx::Class, Rose::DB::Object, Fey::ORM, Jifty::DBI, DBIx::DataModel,
Data::ObjectDriver, Class::DBI, etc.
ORM-based program development 
… and evolution 
Application
programs
Relational
DB schema
Application
programs
Class
schema
O/R
Mapping
RDB data
standard architecture
Relational
DB schema
RDB data
ORM architecture
Looks like our architecture
… but the mapping is not automatically maintained
ORM-based program evolution…
"Object/relational mapping is the Vietnam of Computer Science"
Ted Neward
"It represents a quagmire which starts well, gets more complicated as time
passes, and before long entraps its users in a commitment that has no clear
demarcation point, no clear win conditions, and no clear exit strategy.“
Ted Neward's Technical Blog - June 2006
http://blogs.tedneward.com/2006/06/26/The+Vietnam+Of+Computer+Science.aspx
Questions, comments, suggestions,
complaints?
[email protected]
24
Backups slides…
25
Generic schema representation

Generic Entity-Relationship Model (GER)

encompasses the three main abstraction levels




conceptual
logical
physical
serves as a pivot model for most data modelling paradigms





ER
relational
object-oriented
object-relational
file structures




network
hierarchical
XML
…
26