Difference between revisions of "Version 6.0 Database Changes"
(→Addition of Timestamp Fields) |
|||
| (One intermediate revision by one other user not shown) | |||
| Line 121: | Line 121: | ||
The following tables have two additional new fields: '''created_at''' and '''updated_at'''. | The following tables have two additional new fields: '''created_at''' and '''updated_at'''. | ||
| − | These are used by the | + | 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 | * tbladminsecurityquestions | ||
Latest revision as of 13:45, 9 November 2015
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.
Contents
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.