CREATE TABLE `billed_servdefs` (
  `id` int(11) NOT NULL auto_increment,
  `pkg_servdef_id` int(11) default NULL,
  `name` varchar(32) default NULL,
  `description` varchar(255) default NULL,
  `frequency` smallint(6) NOT NULL default '1',
  `base_acctcode_id` int(11) NOT NULL default '0',
  `setup_price` double default NULL,
  `base_qty` double default NULL,
  `base_price` double default NULL,
  `overage_price` double default NULL,
  `overage_acctcode_id` int(11) default NULL,
  `active` tinyint(1) NOT NULL default '1',
  `max_price` double default NULL,
  `min_price` double default NULL,
  `min_qty` double default NULL,
  `max_qty` double default NULL,
  PRIMARY KEY  (`id`),
  KEY `pkg_servdef_id` (`pkg_servdef_id`),
  KEY `base_acctcode_id` (`base_acctcode_id`),
  KEY `overage_acctcode_id` (`overage_acctcode_id`),
  KEY `id` (`id`,`base_acctcode_id`,`name`),
  KEY `id_2` (`id`,`base_acctcode_id`),
  CONSTRAINT `billed_servdefs_ibfk_1` FOREIGN KEY (`pkg_servdef_id`) REFERENCES
`billed_servdefs` (`id`),
  CONSTRAINT `billed_servdefs_ibfk_2` FOREIGN KEY (`base_acctcode_id`)
REFERENCES `account_codes` (`id`),
  CONSTRAINT `billed_servdefs_ibfk_3` FOREIGN KEY (`overage_acctcode_id`)
REFERENCES `account_codes` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE `billed_services` (
  `id` int(11) NOT NULL auto_increment,
  `account_id` int(11) NOT NULL default '0',
  `name` varchar(32) default NULL,
  `description` varchar(255) default NULL,
  `date_start` datetime NOT NULL default '0000-00-00 00:00:00',
  `date_end` datetime default NULL,
  `frequency` smallint(6) NOT NULL default '1',
  `setup_price` double default NULL,
  `base_qty` double default NULL,
  `base_price` double default NULL,
  `overage_price` double default NULL,
  `billed_servdef_id` int(11) NOT NULL default '0',
  `rID` int(10) unsigned default NULL,
  `asset_id` int(11) default NULL,
  `parent_id` int(11) default NULL,
  `date_started` datetime default NULL,
  PRIMARY KEY  (`id`),
  KEY `account_id` (`account_id`),
  KEY `billed_servdef_id` (`billed_servdef_id`),
  KEY `asset_id` (`asset_id`),
  KEY `rID` (`rID`),
  KEY `parent_id` (`parent_id`),
  KEY `date_end` (`date_end`),
  KEY `date_start` (`date_start`),
  KEY `billed_servdef_id_2` (`billed_servdef_id`,`asset_id`,`rID`),
  CONSTRAINT `billed_services_ibfk_1` FOREIGN KEY (`account_id`) REFERENCES
`accounts` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `billed_services_ibfk_4` FOREIGN KEY (`billed_servdef_id`)
REFERENCES `billed_servdefs` (`id`) ON UPDATE CASCADE,
  CONSTRAINT `billed_services_ibfk_5` FOREIGN KEY (`asset_id`) REFERENCES
`assets` (`id`),
  CONSTRAINT `billed_services_ibfk_6` FOREIGN KEY (`rID`) REFERENCES
`NMS`.`server` (`rID`),
  CONSTRAINT `billed_services_ibfk_7` FOREIGN KEY (`parent_id`) REFERENCES
`billed_services` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

delimiter |;

CREATE DEFINER=`root`@`localhost` FUNCTION
`server_code`(assetid INT) RETURNS varchar(255) CHARSET latin1
    READS SQL DATA
BEGIN
  return 'foo';
END
|

CREATE DEFINER=`root`@`localhost` FUNCTION
`metered_name`(resource_id INT, asset_id INT) RETURNS varchar(255) CHARSET latin1 READS SQL DATA
BEGIN
  return 'foo';
END
|

delimiter ;|

CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY
DEFINER VIEW `test`.`metered_services_view` AS select `bs`.`id` AS
`id`,`bs`.`account_id` AS `account_id`,`bs`.`name` AS `name`,`bs`.`description`
AS `description`,`bs`.`date_start` AS `date_start`,`bs`.`date_started` AS
`date_started`,`bs`.`date_end` AS `date_end`,`bs`.`frequency` AS
`frequency`,`bs`.`setup_price` AS `setup_price`,`bs`.`base_qty` AS
`base_qty`,`bs`.`base_price` AS `base_price`,`bs`.`overage_price` AS
`overage_price`,`bs`.`rID` AS `rID`,`bs`.`asset_id` AS
`asset_id`,`bs`.`parent_id` AS `parent_id`,`bsd`.`name` AS
`billed_servdef_id`,`test`.`metered_name`(`bs`.`rID`,`bs`.`asset_id`) AS
`metered_name`, `test`.`server_code`(`bs`.`asset_id`) AS
`code`,round((`bs`.`base_qty` * `bs`.`base_price`),2) AS `mrc` from
`test`.`billed_services` `bs` join `test`.`billed_servdefs` `bsd` where
(`bs`.`billed_servdef_id` = `bsd`.`id`);

show create view metered_services_view;