--------------------------------------------------
-- PRM tables
-- Created 20110707
--------------------------------------------------

CREATE TABLE IF NOT EXISTS `prd_trace_header` (
  `id` int(11) NOT NULL auto_increment,
  `system` char(8) NOT NULL,
  `client` char(3) NOT NULL,
  `timestamp` datetime NOT NULL,
  `numrecs` int(11) NOT NULL,
  PRIMARY KEY  (`id`),
  UNIQUE KEY `unique` (`system`,`client`,`timestamp`),
  KEY `syscli`(`system`,`client`)
) ENGINE=InnoDB DEFAULT CHARSET=UTF8;

CREATE TABLE IF NOT EXISTS `prd_trace_detail` (
  `id` int(11) NOT NULL auto_increment,
  `hdr_id` int(11) NOT NULL,
  `date` date NOT NULL,
  `user` varchar(12) NOT NULL,
  `tcode` varchar(40) NOT NULL,
  `hits` int(11) NOT NULL,
  PRIMARY KEY  (`id`),
  UNIQUE KEY `hits` (`hdr_id`,`date`,`user`,`tcode`),
  KEY `sums` (`date`,`user`,`tcode`),
  KEY `hdr` (`hdr_id`),
  CONSTRAINT `hdr` FOREIGN KEY (`hdr_id`) REFERENCES `prd_trace_header` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=UTF8;

CREATE VIEW `prd_trace_view` AS 
  select `prd_trace_header`.`system` AS `system`,
    `prd_trace_header`.`client` AS `client`,
    `prd_trace_detail`.`date` AS `date`,
    `prd_trace_detail`.`user` AS `user`,
    `prd_trace_detail`.`tcode` AS `tcode`,
    sum(`prd_trace_detail`.`hits`) AS `hits` 
  from (`prd_trace_detail` join `prd_trace_header` on((`prd_trace_header`.`id` = `prd_trace_detail`.`hdr_id`))) 
  group by `prd_trace_header`.`system`,`prd_trace_header`.`client`,`prd_trace_detail`.`date`,`prd_trace_detail`.`user`,`prd_trace_detail`.`tcode`;
  
