Updating database structure

Problem:

How to patch the database structure when upgrading the program?

Solution:

1. Create a class using the 'plug and play' concept (please refers to this topic). This class contains all the SQL scripts to be executed.
2. Upon the application startup, check the database version and run all the necessary SQL script to update the database structure.

Class design:

1. DBUpdate class:

• This class stores the necessary patch for the given version.
• Attributes: db_version, release_datetime & sql_script.

2. DBUpdateList class:

• This class contains the DBUpdate object instance and it is responsible for executing all the patches.
• Attributes: release_datetime & DBUpdate class type (note: it's not required to instantiate this object or otherwise will take up some memory and slowing down the application startup).

Flow:

1) Get the last patch date/time that stores in the database.
2) Get the list of the patches that is newer than the value get from step 1.
3) Execute all the necessary patches against the database.
4) Get the newest date/time among the patches that has been executed.
5) Update the last patch date/time into the database.
The purpose of storing the last patch date/time is to avoid all the patches being run each time the application starts. So, the application startup performance will not be affected much.