Version 6.0 Database Changes

Version 6.0 introduces a number of database schema changes. These consist of the addition of new tables, fields, and perhaps most importantly, the adjusting of data types on a number of existing fields.

Detailed information regarding all of these changes and adjustments are provided below.

New Tables

Table Name	Purpose
tblknowledgebasetags	Stores associated tags for knowledgebase articles
tblproduct_downloads	Stores relationships between products and their associated downloads
tblproduct_upgrade_products	Stores relationships between products and their possible upgrade or downgrade products
tblserversssoperms	Stores admin role group permissions for Single Sign-On
tbltld_categories	Stores TLD category types
tbltld_category_pivot	Stores the relationships between TLDs and their Categories
tbltlds	Stores TLDs used in TLD category display (not to be confused with TLDs available for registration)
tblupdatehistory	Stores information relating to WHMCS installations and upgrades

New Fields

Table Name	Field Name	Field Type
tblservers	port	Integer (8)

Field Changes

Table Name	Field Name	Previous Field Type	New Field Type
tblaffiliates	id	Integer (3)	Integer (10)
tblclients	pwresetexpiry	Unix Timestamp	Date Timestamp
tblinvoiceitems	type	Text	Varchar (30)
tblproducts	autoterminateemail	Text	Integer (10)
tbproducts	downloads	Text	Removed*
tblproducts	order	Integer (1)	Integer (10)
tblproducts	qty	Integer (1)	Integer (10)
tblproducts	upgradeemail	Text	Integer (10)
tblproducts	upgradepackages	Text	Removed*
tbltickets	status	Text	Varchar (64)
tbltickets	tid	Varchar (15)	Varchar (128)
tblticketstatuses	title	Text	Varchar (64)

* The removed fields are now handled by way of new pivot tables detailed in the New Tables section of this document.

Addition of Timestamp Fields

The following tables have two additional new fields: created_at and updated_at.

These are used by the new database ORM system to track when records are created and updated. They use the MySQL DateTime field type and therefore will be stored in the format YYYY-MM-DD HH:MM:SS

tbladminsecurityquestions
tblaffiliates
tblannouncements
tblcancelrequests
tblclients
tblcontacts
tblconfiguration
tblemailtemplates
tbldomains
tbldomainsadditionalfields
tbldownloads
tbldownloadcats
tblhosting
tblhostingaddons
tblnetworkissues
tblproducts
tblproductgroups
tblquoteitems

Conversion of Boolean Fields

Historically, some boolean fields have used a text field type with an empty value to denote false and any text value to mean true (usually 'on').

Version 6.0 changes a number of these to be integer based fields, with 1 for true and 0 for false. These include:

tblannouncements
- published
tblclients
- disableautocc
- latefeeoverride
- separateinvoices
- taxexempt
tbldomains
- dnsmanagement
- emailforwarding
- idprotection
- synced
tbldownloads
- clientsonly
- hidden
- productdownload
tbldownloadcats
- hidden
tblemailtemplates
- custom
- disabled
- plaintext
tblhosting
- overideautosuspend
tblproducts
- affiliateonetime
- configoptionsupgrade
- hidden
- proratabilling
- showdomainoptions
- stockcontrol
- retired
- tax
tblproductgroups
- hidden

In reality, providing you are only doing loosely typed evaluations of these field values, no changes will be necessary to any custom code. It remains that any value that evaluates as true is considered on, and any empty value will mean off.

New Indexes

The following tables have had additional indexes added for improved performance.

tblactivitylog
tblinvoiceitems
tbltickets

Other Changes

As of 6.0, the value used to denote a disabled domain pricing period in tblpricing has changed from 0.00 to -1.00.
The "downloads" and "upgradepackages" columns in the tblproducts table have been converted to pivot tables as noted in the New Tables and Field Changes sections above.
Any duplicate settings in tblconfiguration will be removed upon upgrading to 6.0, with only the last (valid) setting in the table being retained.

Documentation