Version 6.0 Database Changes

From WHMCS Documentation

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 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. The fields are updated automatically when using the new model classes for these tables, and will not update automatically when you run a query directly on the table.

  • 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.