CREATE DATABASE `lightpath` /*!40100 DEFAULT CHARACTER SET latin1 */;



CREATE TABLE  `lightpath`.`angles` (
  `angle` int(10) unsigned NOT NULL default '0',
  `az_angle` double NOT NULL,
  `view_sun_angle` double NOT NULL,
  PRIMARY KEY  (`angle`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 ROW_FORMAT=FIXED;


CREATE TABLE  `lightpath`.`c_lum_tot` (
  `sim_id` int(10) unsigned NOT NULL default '0',
  `angle` int(10) unsigned NOT NULL default '0',
  `lum_tot` double default NULL,
  PRIMARY KEY  (`sim_id`,`angle`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;


CREATE TABLE  `lightpath`.`c_temp_results` (
  `sim_id` int(10) unsigned NOT NULL default '0',
  `temp_results` tinyint(1) default NULL,
  PRIMARY KEY  (`sim_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;


CREATE TABLE  `lightpath`.`collectors` (
  `min_scat_order` int(10) unsigned NOT NULL default '0',
  `max_scat_order` int(10) unsigned NOT NULL default '0',
  `angle` int(10) unsigned default NULL,
  `x_pos` double default NULL,
  `x_pos_stddev` double default NULL,
  `z_pos` double default NULL,
  `z_pos_stddev` double default NULL,
  `photons_shot` bigint(20) unsigned default NULL,
  `sim_id` int(10) unsigned default NULL,
  `luminance` double default NULL,
  `luminance_stddev` double default NULL,
  `total_shots` bigint(20) default NULL,
  `temp_results` tinyint(1) NOT NULL default '1',
  `total_luminance` double NOT NULL,
  UNIQUE KEY `key` USING BTREE (`sim_id`,`angle`,`min_scat_order`,`max_scat_order`),
  KEY `Index_2` (`sim_id`,`temp_results`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 ROW_FORMAT=FIXED COMMENT='InnoDB free: 1150976 kB; InnoDB free: 6144 kB';



CREATE TABLE  `lightpath`.`lum_tot` (
  `sim_id` int(10) unsigned NOT NULL default '0',
  `angle` int(10) unsigned NOT NULL default '0',
  `lum_tot` double default NULL,
  PRIMARY KEY  (`sim_id`,`angle`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;



CREATE TABLE  `lightpath`.`luminances` (
  `min_scat_order` int(10) unsigned NOT NULL default '0',
  `max_scat_order` int(10) unsigned NOT NULL default '0',
  `angle` int(10) unsigned default NULL,
  `luminance` double default NULL,
  `luminance_stddev` double default NULL,
  `photons_shot` bigint(20) default NULL,
  `sim_id` int(10) unsigned default NULL,
  `total_shots` bigint(20) default NULL,
  `temp_results` tinyint(1) NOT NULL default '1',
  `total_luminance` double NOT NULL,
  UNIQUE KEY `key` USING BTREE (`sim_id`,`angle`,`min_scat_order`,`max_scat_order`),
  KEY `Index_2` (`sim_id`,`temp_results`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COMMENT='InnoDB free: 11264 kB';


CREATE TABLE  `lightpath`.`min_lum` (
  `sim_id` int(10) unsigned NOT NULL default '0',
  `min_lum` double default NULL,
  PRIMARY KEY  (`sim_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;



CREATE TABLE  `lightpath`.`nb_collectors` (
  `sim_id` int(10) unsigned NOT NULL default '0',
  `nb_coll` bigint(21) default NULL,
  PRIMARY KEY  (`sim_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;



CREATE TABLE  `lightpath`.`nb_luminances` (
  `sim_id` int(10) unsigned NOT NULL default '0',
  `nb_lum` bigint(21) default NULL,
  PRIMARY KEY  (`sim_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;



CREATE TABLE  `lightpath`.`simulations` (
  `id` bigint(20) unsigned NOT NULL auto_increment,
  `modified_mie` tinyint(1) NOT NULL default '1',
  `radius` double NOT NULL default '6',
  `density` double NOT NULL default '400',
  `scat_order` int(10) unsigned NOT NULL default '0',
  `thickness` double NOT NULL default '0',
  `depth` double NOT NULL default '0',
  `view_angle` double NOT NULL default '0',
  `confidence_level` double unsigned NOT NULL default '0.05',
  `lum_running` int(1) NOT NULL default '0',
  `coll_running` int(1) NOT NULL default '0',
  `confidence_interval` double NOT NULL default '0.05',
  `isotropic` tinyint(1) NOT NULL default '0',
  `disabled` tinyint(1) NOT NULL default '0',
  PRIMARY KEY  (`id`),
  UNIQUE KEY `key` USING BTREE (`modified_mie`,`radius`,`density`,`scat_order`,`thickness`,`depth`,`view_angle`,`confidence_level`,`confidence_interval`),
  KEY `Index_3` (`scat_order`)
) ENGINE=InnoDB AUTO_INCREMENT=13418 DEFAULT CHARSET=latin1 ROW_FORMAT=FIXED;



CREATE TABLE  `lightpath`.`temp_results` (
  `sim_id` int(10) unsigned NOT NULL default '0',
  `temp_results` tinyint(1) default NULL,
  PRIMARY KEY  (`sim_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;


DROP VIEW IF EXISTS `lightpath`.`coll_lefttodo`;
CREATE ALGORITHM=UNDEFINED DEFINER=`lightpath_user`@`poudreuse.grenoble.inria.fr` SQL SECURITY DEFINER VIEW  `lightpath`.`coll_lefttodo` AS select `ctr`.`temp_results` AS `temp_results`,(`nl`.`nb_lum` = (`s`.`scat_order` * 166)) AS `LuminanceDone`,(`ctr`.`temp_results` = 0) AS `CollDone`,`ml`.`min_lum` AS `min_lum`,`s`.`id` AS `id`,`s`.`modified_mie` AS `modified_mie`,`s`.`radius` AS `radius`,`s`.`density` AS `density`,`s`.`scat_order` AS `scat_order`,`s`.`thickness` AS `thickness`,`s`.`depth` AS `depth`,`s`.`view_angle` AS `view_angle`,`s`.`confidence_level` AS `confidence_level`,`s`.`lum_running` AS `lum_running`,`s`.`coll_running` AS `coll_running`,`s`.`confidence_interval` AS `confidence_interval` from (((`simulations` `s` left join `c_temp_results` `ctr` on((`s`.`id` = `ctr`.`sim_id`))) left join `nb_luminances` `nl` on((`s`.`id` = `nl`.`sim_id`))) left join `min_lum` `ml` on((`s`.`id` = `ml`.`sim_id`))) order by (`ctr`.`temp_results` = 0),(`nl`.`nb_lum` = (`s`.`scat_order` * 166)) desc,`ctr`.`temp_results` desc,`ml`.`min_lum` desc;

DROP VIEW IF EXISTS `lightpath`.`duplicate_sims`;
CREATE ALGORITHM=UNDEFINED DEFINER=`lightpath_user`@`poudreuse.grenoble.inria.fr` SQL SECURITY DEFINER VIEW  `lightpath`.`duplicate_sims` AS select `s2`.`id` AS `othersim`,`s2`.`scat_order` AS `othersim_scat_order`,`s`.`id` AS `id`,`s`.`modified_mie` AS `modified_mie`,`s`.`radius` AS `radius`,`s`.`density` AS `density`,`s`.`scat_order` AS `scat_order`,`s`.`thickness` AS `thickness`,`s`.`depth` AS `depth`,`s`.`view_angle` AS `view_angle`,`s`.`confidence_level` AS `confidence_level`,`s`.`lum_running` AS `lum_running`,`s`.`coll_running` AS `coll_running`,`s`.`confidence_interval` AS `confidence_interval` from (`simulations` `s` left join `simulations` `s2` on(((`s`.`thickness` = `s2`.`thickness`) and (`s`.`depth` = `s2`.`depth`) and (`s`.`view_angle` = `s2`.`view_angle`)))) where (`s2`.`id` > `s`.`id`) order by `s`.`id`;

DROP VIEW IF EXISTS `lightpath`.`lum_lefttodo`;
CREATE ALGORITHM=UNDEFINED DEFINER=`lightpath_user`@`poudreuse.grenoble.inria.fr` SQL SECURITY DEFINER VIEW  `lightpath`.`lum_lefttodo` AS select `ct`.`temp_results` AS `temp_results`,`s`.`id` AS `id`,`s`.`modified_mie` AS `modified_mie`,`s`.`radius` AS `radius`,`s`.`density` AS `density`,`s`.`scat_order` AS `scat_order`,`s`.`thickness` AS `thickness`,`s`.`depth` AS `depth`,`s`.`view_angle` AS `view_angle`,`s`.`confidence_level` AS `confidence_level`,`s`.`lum_running` AS `lum_running`,`s`.`coll_running` AS `coll_running`,`s`.`confidence_interval` AS `confidence_interval` from (`simulations` `s` left join `temp_results` `ct` on((`s`.`id` = `ct`.`sim_id`))) order by `ct`.`temp_results` desc,`s`.`scat_order`;

DROP VIEW IF EXISTS `lightpath`.`orphan_luminances`;
CREATE ALGORITHM=UNDEFINED DEFINER=`lightpath_user`@`poudreuse.grenoble.inria.fr` SQL SECURITY DEFINER VIEW  `lightpath`.`orphan_luminances` AS select distinct `l`.`sim_id` AS `sim_id` from (`luminances` `l` left join `simulations` `s` on((`s`.`id` = `l`.`sim_id`))) where isnull(`s`.`id`);

