Version 6.0 Database Changes - Revision history

Matt: /* Addition of Timestamp Fields */

2015-11-09T13:45:14Z

‎Addition of Timestamp Fields

Andrew: /* Addition of Timestamp Fields */

2015-07-21T14:44:04Z

‎Addition of Timestamp Fields

Matt at 13:57, 15 April 2015

2015-04-15T13:57:57Z

Matt: Created page with "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..."

2015-04-15T13:57:27Z

Created page with "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..."

New page

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)
|}

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

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

@@ Line 121: / Line 121: @@
 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. 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.
+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

@@ Line 5: / Line 5: @@
 == New Tables ==
-{|
+{| class="wikitable"
 ! Table Name
 ! Purpose
@@ Line 36: / Line 36: @@
 == New Fields ==
-{|
+{| class="wikitable"
 ! Table Name
 ! Field Name
@@ Line 48: / Line 48: @@
 == Field Changes ==
-{|
+{| class="wikitable"
 ! Table Name
 ! Field Name