-- phpMyAdmin SQL Dump
-- version 4.0.6deb1
-- http://www.phpmyadmin.net
--
-- Client: localhost
-- Généré le: 18 Avril 2014 à 12:39
-- Version du serveur: 5.5.35-0ubuntu0.13.10.2
-- Version de PHP: 5.5.3-1ubuntu2.3

SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
SET time_zone = "+00:00";


/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;

--
-- Base de données: `projet`
--
CREATE DATABASE IF NOT EXISTS `projet` DEFAULT CHARACTER SET latin1 COLLATE latin1_swedish_ci;
USE `projet`;

-- --------------------------------------------------------

--
-- Structure de la table `CONSOMMATION`
--

CREATE TABLE IF NOT EXISTS `CONSOMMATION` (
  `c_id` bigint(20) NOT NULL DEFAULT '0',
  `c_power` int(11) NOT NULL DEFAULT '0',
  PRIMARY KEY (`c_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

-- --------------------------------------------------------

--
-- Structure de la table `METEO`
--

CREATE TABLE IF NOT EXISTS `METEO` (
  `m_time` datetime NOT NULL,
  `m_temperature` decimal(5,2) DEFAULT NULL,
  PRIMARY KEY (`m_time`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

-- --------------------------------------------------------

--
-- Structure de la table `NETWORK`
--

CREATE TABLE IF NOT EXISTS `NETWORK` (
  `net_batiment` char(20) DEFAULT NULL,
  `net_piece` char(20) DEFAULT NULL,
  `net_id` bigint(20) NOT NULL,
  `net_type` varchar(20) NOT NULL,
  PRIMARY KEY (`net_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

-- --------------------------------------------------------

--
-- Structure de la table `PIECE`
--

CREATE TABLE IF NOT EXISTS `PIECE` (
  `p_batiment` char(20) NOT NULL DEFAULT '',
  `p_piece` char(20) NOT NULL DEFAULT '',
  `p_mode` char(15) DEFAULT NULL,
  PRIMARY KEY (`p_batiment`,`p_piece`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;


-- --------------------------------------------------------

--
-- Structure de la table `RADIATEUR`
--

CREATE TABLE IF NOT EXISTS `RADIATEUR` (
  `r_id` bigint(20) NOT NULL DEFAULT '0',
  `r_time` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  `r_status` char(10) DEFAULT NULL,
  PRIMARY KEY (`r_id`,`r_time`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;


-- --------------------------------------------------------

--
-- Structure de la table `SENSOR`
--

CREATE TABLE IF NOT EXISTS `SENSOR` (
  `s_id` bigint(20) NOT NULL DEFAULT '0',
  `s_time` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  `s_temperature` decimal(5,2) DEFAULT NULL,
  PRIMARY KEY (`s_id`,`s_time`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;


-- --------------------------------------------------------

--
-- Doublure de structure pour la vue `View_MaxTime`
--
CREATE TABLE IF NOT EXISTS `View_MaxTime` (
`r_id` bigint(20)
,`max_date` datetime
);

-- --------------------------------------------------------

--
-- Doublure de structure pour la vue `View_Rad_Room`
--
CREATE TABLE IF NOT EXISTS `View_Rad_Room` (
`net_batiment` char(20)
,`net_piece` char(20)
,`r_status` char(10)
,`p_mode` char(15)
);
-- --------------------------------------------------------

--
-- Doublure de structure pour la vue `View_Rad_Status_time`
--
CREATE TABLE IF NOT EXISTS `View_Rad_Status_time` (
`r_status` char(10)
,`r_id` bigint(20)
,`r_time` datetime
);
-- --------------------------------------------------------

--
-- Doublure de structure pour la vue `View_temp_piece`
--
CREATE TABLE IF NOT EXISTS `View_temp_piece` (
`net_batiment` char(20)
,`net_piece` char(20)
,`AVG(s_temperature)` decimal(9,6)
);
-- --------------------------------------------------------

--
-- Structure de la vue `View_MaxTime`
--
DROP TABLE IF EXISTS `View_MaxTime`;

CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `View_MaxTime` AS select `RADIATEUR`.`r_id` AS `r_id`,max(`RADIATEUR`.`r_time`) AS `max_date` from `RADIATEUR` group by `RADIATEUR`.`r_id`;

-- --------------------------------------------------------

--
-- Structure de la vue `View_Rad_Room`
--
DROP TABLE IF EXISTS `View_Rad_Room`;

CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `View_Rad_Room` AS select `NETWORK`.`net_batiment` AS `net_batiment`,`NETWORK`.`net_piece` AS `net_piece`,`View_Rad_Status_time`.`r_status` AS `r_status`,`PIECE`.`p_mode` AS `p_mode` from ((`NETWORK` join `PIECE`) join `View_Rad_Status_time`) where ((`NETWORK`.`net_id` = `View_Rad_Status_time`.`r_id`) and (`PIECE`.`p_piece` = `NETWORK`.`net_piece`) and (`PIECE`.`p_batiment` = `NETWORK`.`net_batiment`)) group by `NETWORK`.`net_batiment`,`NETWORK`.`net_piece`;

-- --------------------------------------------------------

--
-- Structure de la vue `View_Rad_Status_time`
--
DROP TABLE IF EXISTS `View_Rad_Status_time`;

CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `View_Rad_Status_time` AS select `RADIATEUR`.`r_status` AS `r_status`,`RADIATEUR`.`r_id` AS `r_id`,`RADIATEUR`.`r_time` AS `r_time` from (`RADIATEUR` join `View_MaxTime`) where ((`RADIATEUR`.`r_id` = `View_MaxTime`.`r_id`) and (`RADIATEUR`.`r_time` = `View_MaxTime`.`max_date`));

-- --------------------------------------------------------

--
-- Structure de la vue `View_temp_piece`
--
DROP TABLE IF EXISTS `View_temp_piece`;

CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `View_temp_piece` AS select distinct `NETWORK`.`net_batiment` AS `net_batiment`,`NETWORK`.`net_piece` AS `net_piece`,avg(`SENSOR`.`s_temperature`) AS `AVG(s_temperature)` from (`NETWORK` join `SENSOR`) where (`NETWORK`.`net_id` = `SENSOR`.`s_id`) group by `NETWORK`.`net_batiment`,`NETWORK`.`net_piece`;

/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
