04/11/2023 BX2014 : Bx in Database Evolution 1

Nicolas Hames (UNamur), Mathieu Beine (UNamur), Anthony Cleve (UNamur), Jens Weber (UVic)

Bidirectional Transformations in Database Evolution:

A case study « at scale »

04/11/2023 BX2014 : Bx in Database Evolution 2

1. Introduction

04/11/2023 BX2014 : Bx in Database Evolution 3

1. Introduction

• Case study: OSCAR CMR System– Schema evolution / cleaning

• 467 tables (>1000 columns / table)

– Technology dependence • MySQL

– Need of formal transformations• Migration of critical data

– Need of reversible transformations• Backward compatibiliy with the application

– Implementation « At Scale »• Design decisions and trade-offs

Provability, scalability, reversibility and MySQL compatibility

04/11/2023 BX2014 : Bx in Database Evolution 4

1. Introduction

04/11/2023 BX2014 : Bx in Database Evolution 5

2. Method

• Based on the « Channels » concept (J. Terwilliger).– Partial implementation for specific needs

• Only a subset of operators• Only a subset of properties

04/11/2023 BX2014 : Bx in Database Evolution 6

2. Method

04/11/2023 BX2014 : Bx in Database Evolution 7

2. Method

• UNPIVOT– No UNPIVOT operator in MySQL

• Procedural approach : stored procedure• Declarative approach: single select statement

04/11/2023 BX2014 : Bx in Database Evolution 8

2. MethodProcedural approach Declarative approach

04/11/2023 BX2014 : Bx in Database Evolution 9

2. Method

• PIVOT– No PIVOT operator in MySQL

• Joins• Joins of joins• « The coalescing approach »

04/11/2023 BX2014 : Bx in Database Evolution 10

2. Method

• PIVOT: « The coalescing approach »

04/11/2023 BX2014 : Bx in Database Evolution 11

2. MethodJoins The coalescing approach

04/11/2023 BX2014 : Bx in Database Evolution 12

3. Results

• Based on the « Channels » concept (J. Terwilliger).– Partial implementation for specific needs

• Only DML (CRUD)

– Tool support• Plugin for DBMain Case tool

– Code generation• > 300 pages for the insert trigger only !

04/11/2023 BX2014 : Bx in Database Evolution 13

4. Conclusion

• Implementation « at scale »– Design decision and trade-offs– Technology dependent

• Theoretical concepts not always directly applicable– Max join per query limit– No PIVOT / UNPIVOT operators in function set

– Provable transformations– Performance comparison– Further performance improvement:

• Idea : ORM based channels implementation

04/11/2023 BX2014 : Bx in Database Evolution 14

5. Questions ?