Schema Evolution and Version Management

1. Schema Evolution and Version Management Presented by Patrick Spinler Nadia Ghazanfar

2. Table of Contents • What is schema evolution and versioning? • Methods of accomplishing • Manual means of accomplishing • Temporal Database Systems • Other Considerations • Drawbacks to doing schema versioning • Practical considerations to limit impact of schema changes • Example

3. Definitions • Process of managing changes to an existing database • For large or existing applications • Schema Modification • Changing schema without preserving data • Schema Evolution • Changing the schema, one schema available • Schema Versioning • Two or more schemas available simultaneously

4. DBMS DBMS replace by replace Table foo.1 Table foo.2 by Application Figure: Schema Modification Schema Modification • Changes to database schema requiring application recoding where existing data is lost.

5. DBMS replace Table foo.1 Table foo.2 by Application Figure: Schema Evolution Schema Evolution • Changes to database schema requiring application recoding but preserving any existing data

6. DBMS Table foo.1 Table foo.2 Application 1 Application 2 Application 3 Figure: Schema Versioning Schema Versioning • Existing applications continue to work • Preserve existing data • Data is visible through both old and new schema at the same time • Partial Schema Versioning • Old schemas are view only • Data can only be added in latest schema • Full Schema Versioning • Old schemas are updateable

7. Methods of accomplishing • Manual Process Simulated using standard SQL • DBMS package support Temporal Database Systems

8. Methods of accomplishing • Manual Process • Schema Modification • Schema Evolution • Schema Versioning • By Views • By database procedures • Tradeoffs

9. Schema Modification • Example drop table test_table; create table test_table (a int, b varchar(24));

10. Schema Evolution • Example alter table test_table add column (a int); alter domain foo_domain …; create table _temp_save_data as select a, b, from test_table; drop table test_table; create table test_table (a decimal(10,2), b foo_domain); insert into test_table (a, b) as select cast_decimal (a), cast_domain (b) from _temp_save_data;

11. Manual Schema Versioning • Using SQL Views create table table1 (col_a int); create view table1_v1 as select col_a from table1; alter table table1 add column col_b varchar(24) with default “”; create view table1_v2 as select col_a, col_b from table1; • Old applications use table1_v1 • New applications use table1_v2

12. Manual Schema Versioning • Using Database Procedures • Example create table table1 (col_a int); create procedure table1_insert_v1 (…) as …; create procedure table1_select_v1 (…) as …; alter table table1 add column col_b varchar(24) with default “”; create procedure table1_insert_v2 (…) as …; create procedure table1_select_v3 (…) as …;

13. Manual Schema Versioning • Views vs. Procedures – Tradeoffs • Requires a procedure for every select used, applications can’t do a join on procedures • DBA is a bottleneck to application development • Procedures may fit well with “all access by procedures” strategy • Procedures make it easier to deal with radical schema changes

14. DBMS support • Temporal database systems • Primary key includes a valid time reference • Table is defined with a valid start time and valid end time • Start time and end time cannot overlap • Timestamp types • Transaction time - time was defined in DBMS • Valid time - ‘real world’ time • Bi-temporal - both

15. Temporal databases - TSQL2 • Temporal SQL – TSQL2 • set schema set schema date ‘2003-01-31’;select last_name from people; • In a where clause select last_name from peoplewhere schema(people) preceeds ‘2003-01-31’; • Only transaction time • valid time uses other extensions • Several competing standards

16. Temporal databases - ATSQL • A newer standard than TSQL2 • ATSQL addresses advanced SQL features such as views, integrity constraints, assertions, the data definition language, aggregation and duplicates. • http://www.cs.auc.dk/~tigeradm/atsql.ps.gz • Many constructs from ATSQL proposed for SQL3 standard • Examples SET VALID PERIOD '1996/6/20 - NOW' INSERT INTO prescription VALUES (7565836, 69111, 'Dr Hook', '1996/6/12'); COMMIT; SEQUENCED VALID AND SEQUENCED TRANSACTION PERIOD '1994/1/8' SELECT * FROM prescription;

17. Temporal databases • Some available packages • BtPgsql - Bi-Temporal PostgreSQL • Neither TSQL nor ATSQL standard • http://raa.ruby-lang.org/list.rhtml?name=btpgsql • Tiger - Advanced Temporal Database System • Frontend to Oracle – Provides ATSQL • http://www.cs.auc.dk/~tigeradm/ • JTemporal • Temporal framework for Java – Provides TSQL2 • http://jtemporal.sourceforge.net/ • TAU Project - Temporal Access for Users • Umbrella project, datatypes, XML utilities, and databases • http://www.cs.arizona.edu/tau/

18. Other Considerations • How to covert data when schema is modified • At time of schema modification • ‘lazy’ conversion, when data is used or modified • ‘logical’ conversion only, actual base data is not modified, only modified during retrieval • Use of traditional version control systems/repositories in schema evolution • Schema evolution, with old schemas stored in version control system • No access to old views of data, but could manually reproduce schemas

19. Drawbacks • Complexity • Manual process entails very high maintenance overhead • Package support is not common and requires special programming from project start • Types of modifications are restricted • For full schema versioning, and datum must be valid in any version of the schema

20. Practical considerations • Never leave column list in SQL unspecified • No select *, instead select col_a, col_b • No insert into foo values (a, b), instead insert into foo (col_a, col_b) values (a, b) • Isolate data schema from applications • Transaction monitor • Application Data Access Layer • DB Procedures & views

21. Example • Clinical Trials Lab systems • Uses schema evolution, not schema versioning • Uses a version control repository to store old schemas

22. Conclusions • This is: the process of managing modifications to an existing database’s schema • Several methodologies • Schema modification • Schema evolution • Schema versioning • Schema versioning can by done • Manually, using standard SQL • Using Temporal Databases

23. Questions ?