Database Updates

There are the following 2 types of database updates:

Extension database updates
Extension database updates are made when the associated extension is enabled and should not impact processing if the extension is disabled. The should not impact processing means that if the extension is disabled and the changes are left in place, they do not affect the remaining Simple Invoices functionality. If they will affect the performance, then they must be removed
Simple Invoices Database Changes

These are permanent changes to the Simple Invoices database. They are implemented via the SQL Patch Process.

SQL Patch Process

This process is invoked every time a Simple Invoices user request is processed. It checks to see if the database patch level is current (caught up on all changes) and if not, applies changes to bring the database structure current prior to executing the current user request.

There are 5 files that need to be updated for each patch. They are:

  1. ./include/sql_patches.php – Instructions for this file are given below.
  2. ./databases/json/sample_data.json – Updated if seed data needs to be stored in the test database.
  3. ./databases/json/essential_data.json – Updated if required database information is required when a new install implements the database.
  4. ./databases/mysql/Full_Simple_Invoices.sql – Updated with database structure changes and test data specified for it.
  5. ./databases/mysql/structure.sql – Updated with the changed database structure without any data.
    • Once this patch has been applied to the database, you can use phpMyAdmin to generate without any dataan updated version of this file.

The procedures to implement an SQL Patch file are:

  1. Changes are appended to the ./sys/include/sql_patches.php file.
  2. The new patch is assigned the next number following the number last assigned in the file.
  3. Each patch consists of an associative array with three key/value pairs and submission of that array to the list of patches. The entries for each patch specify the:
    1. ‘name’ – A user description of the patch.
    2. ‘patch’ – One or more SQL commands that are to be  performed..
    3. ‘date’ – The date the patch was added to the sql_patches.php file. The date must be in the yyyymmdd format.
    4. patchmaker() – This function call and specifies the number assigned to the patch.

Patch entry example

$patchlines = array(
'name' => "Clipped size of zip_code and credit_card_number fields to realistic values",
'patch' => "ALTER TABLE `".TB_PREFIX."customers`
CHANGE
`zip_code` `zip_code` VARCHAR(20) NULL,
CHANGE `credit_card_number` `credit_card_number` VARCHAR(20) NULL;
ALTER TABLE `".TB_PREFIX."biller` CHANGE `zip_code` `zip_code` VARCHAR(20) NULL;",
'date' => "20131111"
);
patchmaker('291', $patchlines, $si_patches);
Note that there are multiple SQL commands included in this patch.