LIVE SCHEMA UPDATES
The goal of this open-source project is to support the schema transformation/change in databases while concurrently serving the clients. The source code has been developed under the supervision of Dr Rida Bazzi (Associate Professor, Arizona State University, USA) and the work is supported by NSF grant CNS 08334757. Loland [1], in his PhD work, developed a database system to support live schema transformation without blocking user transactions. His prototype requires the modification of the database engine to support online schema update. We target the existing database systems and working towards to support the schema transformation process at client level without affecting internals of a DBMS software.
DETAILS OF THE UPDATE PROCESS
We follow Loland's approach. The whole process is divided in three phases. In first phase, the available current state of data is copied from old database to new database. All the changes occurring at old database due to active user transactions are captured in a log table through triggers. In second phase, data of the log table is propogated to the new database in multiple runs while concurrently serving the clients. While propagating the changes in the log table, a running estimate of the remaining time needed to process the log table is maintained. When this estimate is below a certain threshold, the original database is made unavailable and the last batch of changes is fully propagated at which time the new database is made available and client code starts accessing the new database.
This version of source code supports the following schema transformations: Horizontal Merge with Duplicates (HMND), Vertical Merge, Horizontal Split, Vertical Split with Primary Key (VSWP), Vertical Split with No Primary key (VSNP), Difference and Intersection with Duplicates (DIWD), Difference and Intersection with No Duplicates (DIND).
Live Schema Transformation requires queries that are specific to the schema being updated. In this release, we show how schema update can be done for a specific schema consisting of eleven tables that are subject to above-listed schema transformations. The same approach can be applied to any schema.
For any questions/clarification, please contact: ptyagi3@asu.edu or bazzi@asu.edu
Below are the assumptions valid for the current released version.
We have taken an example schema (SQLPart\schemapagila.sql) to perform dynamic database updates.
Steps to be followed:
1. Create the database consisting of the schema (schemapagila.sql)
2. Provide the appropriate configuration details in config.properties.
3. On live database server, execute the following files in order: Prerequisite.sql --> GrantPermission.sql --> NewSchema.sql --> LiveSchemaTransformation.sql
4. Run the program (Class Name: IntegrateTransformations).
References:
[1] Jorgen Loland, Svein ? Olaf Hvasshovd: Online, Non-blocking Relational Schema Changes (2006)
