Database Updates

There are the following 2 types of database updates:

Extension database updates
Extensions are special features that a user may or may not want to use. If the extension requires database changes, they must be of the sort that does not impact SI if the extension is subsequently disabled. Extension database changes can be applied by using an init.php file in the extension include directory. Have this file call a function that checks if the database change is present. If not, apply the change. If it is present, just continue on with normal processing. Be sure to verify that the database changes do not affect SI when the extension is disabled.
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 data, an 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. ‘source’ – Denotes patches made for the fearless359 SI version as opposed to the standard version.
  4. self::makePatch(‘nnn’, $patch) – This fucntion performs the operations necessary to applying the patch.

Patch entry example

$patch = [

‘name’ => “Add display department option.”,

‘patch’ => “INSERT INTO `” . TB_PREFIX . “system_defaults` (name ,value ,domain_id ,extension_id ) VALUES (‘display_department’, 1, $domainId, 1);”,

‘date’ => “20210930”,

‘source’ => ‘fearless359’

];

self::makePatch(‘326’, $patch); 

Note that there can be multiple SQL commands included in a patch.