Feature Requests
Share ideas, discuss and vote on requests from other users in community
 

WHMCS DataBase with CONSTRAINT

Host Md shared this idea 5 years ago
Declined

WHMCS tables with FOREIGN KEY restriction

something like this table:

CREATE TABLE IF NOT EXISTS `DB`.`hosting` (

`id` int(10) unsigned NOT NULL AUTO_INCREMENT,

`customer_id` int(10) unsigned NOT NULL,

`package_id` smallint(4) unsigned NOT NULL,

`webserver_id` smallint(4) unsigned NOT NULL,

`mailserver_id` smallint(4) unsigned NOT NULL,

`mysqlserver_id` smallint(4) unsigned NOT NULL,

`backupserver_id` smallint(4) unsigned NOT NULL,

`status` enum('notValidated','Validated','Active','Blocked','Reserved','Suspended','pendingCreate','pendingDelete','pendingUpdate') NOT NULL DEFAULT 'notValidated',

`billing_cycle` enum('1','3','6','12','24') NOT NULL DEFAULT '3',

`domains` smallint(3) unsigned NOT NULL DEFAULT '0',

`subdomains` smallint(3) unsigned NOT NULL DEFAULT '0',

`website_aliases` smallint(3) unsigned NOT NULL DEFAULT '0',

`disk_space_allocated` int(10) unsigned NOT NULL DEFAULT '1',

`website_disk_space_used` bigint(20) unsigned NOT NULL DEFAULT '0',

`mail_disk_space_used` bigint(20) unsigned NOT NULL DEFAULT '0',

`mysql_disk_space_used` bigint(20) unsigned NOT NULL DEFAULT '0',

`daily_data_transfer_allocated` smallint(4) unsigned NOT NULL DEFAULT '1',

`daily_data_transfer_used` bigint(20) unsigned NOT NULL DEFAULT '0',

`monthly_data_transfer_allocated` smallint(5) unsigned NOT NULL DEFAULT '1',

`monthly_data_transfer_used` bigint(20) unsigned NOT NULL DEFAULT '0',

`email_accounts` smallint(3) unsigned NOT NULL DEFAULT '0',

`ftp_accounts` smallint(3) unsigned NOT NULL DEFAULT '0',

`mysql_databases` smallint(3) unsigned NOT NULL DEFAULT '0',

`mailbox_quota` smallint(5) unsigned NOT NULL DEFAULT '1',

`php_as` enum('apache','fast_cgi') NOT NULL DEFAULT 'apache',

`cgi_support` enum('0','1') NOT NULL DEFAULT '0',

`notes` text,

`comments` text,

`creation_date` datetime NOT NULL,

`updated_date` TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

`expiration_date` datetime,

`grace_period` smallint(3) unsigned NOT NULL DEFAULT '0',

`cron_output_mailto` varchar(255),

`auto_delete` enum('0','1') NOT NULL DEFAULT '0',

`auto_renew` enum('0','1') NOT NULL DEFAULT '0',

PRIMARY KEY (`id`),

CONSTRAINT `hosting_ibfk_1` FOREIGN KEY (`customer_id`) REFERENCES `customer` (`id`) ON DELETE RESTRICT,

CONSTRAINT `hosting_ibfk_2` FOREIGN KEY (`package_id`) REFERENCES `package` (`id`) ON DELETE RESTRICT,

CONSTRAINT `hosting_ibfk_3` FOREIGN KEY (`webserver_id`) REFERENCES `webserver` (`id`) ON DELETE RESTRICT,

CONSTRAINT `hosting_ibfk_4` FOREIGN KEY (`mailserver_id`) REFERENCES `mailserver` (`id`) ON DELETE RESTRICT,

CONSTRAINT `hosting_ibfk_5` FOREIGN KEY (`mysqlserver_id`) REFERENCES `mysqlserver` (`id`) ON DELETE RESTRICT,

CONSTRAINT `hosting_ibfk_6` FOREIGN KEY (`backupserver_id`) REFERENCES `backupserver` (`id`) ON DELETE RESTRICT

) ENGINE=InnoDB AUTO_INCREMENT=1001 DEFAULT CHARSET=latin1 COMMENT='Hosting Account';

Best Answer
photo

Introducing an InnoDB would present a number of challenges for many of our users and so for that reason, we are unable to do this.

Comments (3)

photo
1

I think this is a great idea..but then, i was stumbling with my provider's server that only allow myisam database engine that cant support relational database..

If this feature is implemented, sure it will help developers to read the database relation..but then WHMCS must add system requirements that state: a relational database engine support is required.

photo
1

Introducing an InnoDB would present a number of challenges for many of our users and so for that reason, we are unable to do this.

photo
1

you said you unable to do that. but when i see your last sql data on upgrade700alpha5.sql and upgrade700beta3.sql it did contain ENGINE=InnoDB

taken from /whmcs/resources/sql on whmcs_v701_full at 2016-11-4(at this reply date)

i did change the engine to myisam to be working on my installation and still cant found any trouble