DBUpgrade

WebERP database upgrade system.

Synopsis

To improve the database upgrade system, as in this Issue (https://github.com/timschofield/webERP-svn/issues/34). The idea is to apply DBMS independent update files to the webERP database, with each new feature having it's own self contained file containing it's necessary updates.

Files

Each DBMS will have it's own file containing functions to do each of the necessary database updates. These files will be named includes/UpgradeDB_XXX.php where XXX is the DBMS, as is currently the case with the ConnectDB_XXX.php scripts. The functions needed in each file are defined as follows:

function CharacterSet($Table) {}

function CreateTrigger($Table, $TriggerName, $Event, $Row, $EventSql) {}

function NewSecurityToken($TokenId, $TokenName) {}

function NewSysType($TypeID, $TypeDescription) {}

function NewScript($ScriptName, $PageSecurity) {}

function RemoveScript($ScriptName) {}

function NewModule($Link, $Report, $Name, $Sequence) {}

function NewMenuItem($Link, $Section, $Caption, $URL, $Sequence) {}

function RemoveMenuItem($Link, $Section, $Caption, $URL) {}

function AddColumn($Column, $Table, $Type, $Null, $Default, $After) {}

function AddIndex($Columns, $Table, $Name) {}

function DropIndex($Table, $Name) {}

function DropColumn($Column, $Table) {}

function ChangeColumnSize($Column, $Table, $Type, $Null, $Default, $Size) {}

function ChangeColumnName($OldName, $Table, $Type, $Null, $Default, $NewName, $AutoIncrement = '') {}

function ChangeColumnType($Column, $Table, $Type, $Null, $Default) {}

function ChangeColumnDefault($Column, $Table, $Type, $Null, $Default) {}

function RemoveAutoIncrement($Column, $Table, $Type, $Null, $Default) {}

function NewConfigValue($ConfName, $ConfValue) {}

function ChangeConfigValue($ConfName, $NewConfigValue) {}

function ChangeConfigName($OldConfName, $NewConfName) {}

function DeleteConfigValue($ConfName) {}

function CreateTable($Table, $SQL) {}

function ConstraintExists($Table, $Constraint) {}

function DropConstraint($Table, $Constraint) {}

function AddConstraint($Table, $Constraint, $Field, $ReferenceTable, $ReferenceField) {}

function UpdateField($Table, $Field, $NewValue, $Criteria) {}

function DeleteRecords($Table, $Criteria) {}

function DropTable($Table, $Field) {}

function InsertRecord($Table, $CheckFields, $CheckValues, $Fields, $Values) {}

function DropPrimaryKey($Table, $OldKey) {}

function AddPrimaryKey($Table, $Fields) {}

function RenameTable($OldName, $NewName) {}

function SetAutoIncStart($Table, $Field, $StartNumber) {}

function OutputResult($Msg, $Status) {}

More functions can be added as they become needed. Each function carries out some tests on the existing database before applying the update to ensure the users database does not get corrupted by the update.

The individual update files will be given sequential numbers, starting at 0.php and incrementing by one for each new file. They will be stored in their own directory - sql/updates/ - and they will be the only type of file allowed in this directory. They will take the form:

<?php

/* Calls to the individual PHP update functions will go here */

UpdateDBNo(basename(__FILE__, '.php'), 'A description of this update');

?>

The latest file number that has been processed is stored in the config table under the name "DBUpdateNumber". This is updated by the UpdateDBNo() function, which must always be the last code in the numbered update files. THe second parameter to the UpdateDBNo() function is a small description of the updates being done.

The update files are processed by the Z_UgradeDatabase.php script. When a user with system administrator permissions next logs in, a check is made as to whether there are any updates in the sql/updates folder that have not been applied. If there are, then the following screen is shown:

This screen lists all the available update files that have not been applied. Clicking on the arrow by the side of the update file name toggles a view of all the updates within that file:

To process the updates, the user has to just click on the button. Once the updates are completed they will be redirected to the following screen

This screen gives a summary of the updates. If there were any failures, then these are listed on that page.

Migration

WebERP will first check whether the database has all the updates from the previous system applied to it (ie is it at least 4.15.2). If not then it uses the old update system to bring it up to a 4.15.2 database. Then it will apply any updates since 4.15.2. This will ensure that no updates are missed in the transition.

Advantages

1. The interface is more polished and professional.
2. By using PHP files the updates become DBMS independent.
3. By using PHP files, strings entered into the database can be translated using gettext
4. Updates for each new feature or bug fix are isolated into one file, and so greater control of updates can be exercised.