-- https://www.phpmyadmin.net/
--




--
-- Database: `sensors`
--

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

--
-- Table structure for table `locations`
--

CREATE TABLE `locations` (
  `location_id` int(11) NOT NULL,
  `location_name` varchar(64) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

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

--
-- Table structure for table `log`
--

CREATE TABLE `log` (
  `log_id` int(11) NOT NULL,
  `log_timestamp` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `sensor_id` int(11) DEFAULT NULL,
  `log_temperature` decimal(4,2) DEFAULT NULL,
  `log_humidity` decimal(4,1) DEFAULT NULL,
  `log_battery` tinyint(1) DEFAULT NULL,
  `log_test` tinyint(1) DEFAULT NULL,
  `log_freq` mediumint(8) NOT NULL,
  `log_rssi` smallint(6) NOT NULL,
  `log_snr` smallint(5) UNSIGNED NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

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

--
-- Table structure for table `meaning`
--

CREATE TABLE `meaning` (
  `meaning_id` int(11) NOT NULL,
  `location_id` int(11) NOT NULL,
  `sensor_id` int(11) NOT NULL,
  `first_log_id` int(11) NOT NULL,
  `prev_meaning_id` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

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

--
-- Stand-in structure for view `meanings`
-- (See below for the actual view)
--
CREATE TABLE `meanings` (
`location_id` int(11)
,`sensor_id` int(11)
,`first_log_id` int(11)
,`next_log_id` bigint(11)
);

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

--
-- Table structure for table `sensors`
--

CREATE TABLE `sensors` (
  `sensor_id` int(11) NOT NULL,
  `sensor_name` varchar(128) NOT NULL,
  `sensor_channel` int(11) DEFAULT NULL,
  `sensor_epheremal_id` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

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

--
-- Structure for view `meanings`
--

CREATE ALGORITHM=UNDEFINED DEFINER=`sensors`@`%` SQL SECURITY INVOKER VIEW `meanings`  AS  select `meaning`.`location_id` AS `location_id`,`meaning`.`sensor_id` AS `sensor_id`,`meaning`.`first_log_id` AS `first_log_id`,ifnull(`next_meaning`.`first_log_id`,2147483647) AS `next_log_id` from (`meaning` left join `meaning` `next_meaning` on((`next_meaning`.`prev_meaning_id` = `meaning`.`meaning_id`))) where (`meaning`.`location_id` is not null) ;

--
-- Indexes for dumped tables
--

--
-- Indexes for table `locations`
--
ALTER TABLE `locations`
  ADD PRIMARY KEY (`location_id`),
  ADD UNIQUE KEY `location_name` (`location_name`);

--
-- Indexes for table `log`
--
ALTER TABLE `log`
  ADD PRIMARY KEY (`log_id`),
  ADD KEY `log_ibfk_1` (`sensor_id`);

--
-- Indexes for table `meaning`
--
ALTER TABLE `meaning`
  ADD PRIMARY KEY (`meaning_id`),
  ADD UNIQUE KEY `prev_meaning_id` (`prev_meaning_id`),
  ADD KEY `location_id` (`location_id`),
  ADD KEY `meaning_ibfk_2` (`sensor_id`);

--
-- Indexes for table `sensors`
--
ALTER TABLE `sensors`
  ADD PRIMARY KEY (`sensor_id`),
  ADD UNIQUE KEY `sensor_name` (`sensor_name`,`sensor_channel`,`sensor_epheremal_id`);

--
-- AUTO_INCREMENT for dumped tables
--

--
-- AUTO_INCREMENT for table `locations`
--
ALTER TABLE `locations`
  MODIFY `location_id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=7;
--
-- AUTO_INCREMENT for table `log`
--
ALTER TABLE `log`
  MODIFY `log_id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=14303;
--
-- AUTO_INCREMENT for table `meaning`
--
ALTER TABLE `meaning`
  MODIFY `meaning_id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=8;
--
-- AUTO_INCREMENT for table `sensors`
--
ALTER TABLE `sensors`
  MODIFY `sensor_id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=6341;
--
-- Constraints for dumped tables
--

--
-- Constraints for table `log`
--
ALTER TABLE `log`
  ADD CONSTRAINT `log_ibfk_1` FOREIGN KEY (`sensor_id`) REFERENCES `sensors` (`sensor_id`) ON UPDATE CASCADE;

--
-- Constraints for table `meaning`
--
ALTER TABLE `meaning`
  ADD CONSTRAINT `meaning_ibfk_1` FOREIGN KEY (`location_id`) REFERENCES `locations` (`location_id`),
  ADD CONSTRAINT `meaning_ibfk_2` FOREIGN KEY (`sensor_id`) REFERENCES `sensors` (`sensor_id`) ON UPDATE CASCADE,
  ADD CONSTRAINT `meaning_ibfk_3` FOREIGN KEY (`prev_meaning_id`) REFERENCES `meaning` (`meaning_id`);

