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 provide DBMS indepenxcent update files, 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 if 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 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 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: