blueweather/api/blueweather.sql

358 lines
11 KiB
MySQL
Raw Permalink Normal View History

2019-07-27 13:41:48 +02:00
-- phpMyAdmin SQL Dump
-- version 4.9.0.1
-- https://www.phpmyadmin.net/
--
-- Host: localhost
-- Generation Time: Jul 27, 2019 at 11:36 AM
-- Server version: 5.7.27-0ubuntu0.18.04.1
-- PHP Version: 7.2.20-2+ubuntu18.04.1+deb.sury.org+1
SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
SET AUTOCOMMIT = 0;
START TRANSACTION;
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 utf8mb4 */;
--
-- Database: `Temperatur`
--
-- --------------------------------------------------------
--
-- Table structure for table `activationtokens`
--
CREATE TABLE `activationtokens` (
`id` int(11) NOT NULL,
`userid` int(11) NOT NULL,
`token` varchar(500) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
-- --------------------------------------------------------
--
-- Table structure for table `locations`
--
CREATE TABLE `locations` (
`id` int(11) NOT NULL,
`locationname` text NOT NULL,
`latitude` decimal(10,5) NOT NULL,
`longitude` decimal(10,5) NOT NULL,
`countryname` text NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
--
-- Dumping data for table `locations`
--
INSERT INTO `locations` (`id`, `locationname`, `latitude`, `longitude`, `countryname`) VALUES
(1, 'Stuttgart_Vaihingen', '48.73036', '9.10794', 'Germany'),
(2, 'Obereglfing', '47.73368', '11.17957', 'Germany'),
(3, 'Zschorna', '51.15822', '14.61245', 'Germany'),
(5, 'Stuttgart_Rohr', '48.71656', '9.08752', 'Germany1');
-- --------------------------------------------------------
--
-- Table structure for table `measvalues`
--
CREATE TABLE `measvalues` (
`id` int(11) NOT NULL,
`measvalue` float NOT NULL,
`sensorid` int(11) NOT NULL,
`timestamp` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
-- --------------------------------------------------------
--
-- Table structure for table `sensorgroupmembers`
--
CREATE TABLE `sensorgroupmembers` (
`id` int(11) NOT NULL,
`sensorid` int(11) NOT NULL,
`groupid` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
-- --------------------------------------------------------
--
-- Table structure for table `sensorgroups`
--
CREATE TABLE `sensorgroups` (
`id` int(11) NOT NULL,
`name` varchar(11) NOT NULL,
`locationid` int(11) NOT NULL,
`userid` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
-- --------------------------------------------------------
--
-- Table structure for table `sensors`
--
CREATE TABLE `sensors` (
`id` int(11) NOT NULL,
`added` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
`userid` int(11) NOT NULL,
`sensorname` text NOT NULL,
`property` set('indoor','sunny','shadow') NOT NULL DEFAULT 'sunny',
`locationid` int(11) NOT NULL,
`valuetypeid` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
-- --------------------------------------------------------
--
-- Table structure for table `sessions`
--
CREATE TABLE `sessions` (
`id` int(11) NOT NULL,
`userId` int(11) NOT NULL,
`session` varchar(500) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
-- --------------------------------------------------------
--
-- Table structure for table `users`
--
CREATE TABLE `users` (
`id` int(11) NOT NULL,
`username` text NOT NULL,
`password` text NOT NULL,
`role` set('admin','user') NOT NULL DEFAULT 'user',
`realname` text NOT NULL,
`email` text NOT NULL,
`membersince` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
`active` int(1) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
--
-- Dumping data for table `users`
--
INSERT INTO `users` (`id`, `username`, `password`, `role`, `realname`, `email`, `membersince`, `active`) VALUES
(1, 'dorian', '$2y$10$8eBebyg09FBz.hGcp/XrCeYSSzj6uEUNSg.IpRdcaN4BWVu4RX./.', 'admin', 'Dorian Zedler', 'dorian@itsblue.de', '2018-02-25 12:25:51', 1),
(2, 'fenoglio', '$2y$10$CMcVmoADyovnzjJAeGM4N.iRmjaXNtaEar3sYBQDZAe9/oQHsm2jO', 'admin', 'Jens Noack', 'jnoack@itsblue.de', '2018-02-25 12:25:51', 1),
(3, 'haupts', '$2y$10$I0KCgWlUXfc0QVxXuXNuzO/eeX/jXE8.0z6GwMhomGWyCMaA/rDI2', 'user', 'Haupt', 'hauptclan@gmx.de', '2018-04-05 20:54:49', 1),
(4, 'tester', '$2y$10$Fecdcguo5fGsaZwdOOqy3e7vi8jgr1ro01Ke2cguT3.uo4sM279jW', 'user', 'test test', 'test@test.de', '2018-04-19 17:42:21', 1),
(5, 'oliver', '$2y$10$OkpvCvBbj2wGJf3pyhSFPeE.pC2gs5LyVLZGTP13yiGfMtyWjcIMO', 'admin', 'Oliver Schappacher', 'o.schappacher@web.de', '2019-07-04 10:39:58', 1);
-- --------------------------------------------------------
--
-- Table structure for table `valuetypes`
--
CREATE TABLE `valuetypes` (
`id` int(11) NOT NULL,
`valuetype` set('temperature','humidity','pressure','fine dust') NOT NULL,
`valueunit` varchar(500) NOT NULL,
`displayproperty` varchar(500) NOT NULL DEFAULT 'fill: false, //lineTension: 0.1, pointRadius: pointradius, backgroundColor: ''rgba(29, 202, 255, 0.75)'', borderColor: ''rgba(29, 202, 255, 1)'', pointHoverBackgroundColor: ''rgba(29, 202, 255, 1)'', pointHoverBorderColor: ''rgba(29, 202, 255, 1)'','
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
--
-- Dumping data for table `valuetypes`
--
INSERT INTO `valuetypes` (`id`, `valuetype`, `valueunit`, `displayproperty`) VALUES
(1, 'temperature', '&degC', '{\"widget\":{\"type\":\"text\",\"properties\":{}},\"chart\":{\"type\":\"scatter\",\"properties\":{\"fill\":false,\"borderColor\":\"rgba(29, 202, 255, 1)\",\"pointHoverBackgroundColor\":\"rgba(29, 202, 255, 1)\",\"pointHoverBorderColor\":\"rgba(29, 202, 255, 1)\"}}}'),
(2, 'temperature', '&degF', '{\"widget\":{\"type\":\"text\",\"properties\":{}},\"chart\":{\"type\":\"scatter\",\"properties\":{\"fill\":false,\"borderColor\":\"rgba(29, 202, 255, 1)\",\"pointHoverBackgroundColor\":\"rgba(29, 202, 255, 1)\",\"pointHoverBorderColor\":\"rgba(29, 202, 255, 1)\"}}}'),
(3, 'humidity', '%', '{\"widget\":{\"type\":\"doughnut\",\"properties\":{\"backgroundColor\":[\"rgb(54, 162, 235)\",\"lightgrey\"]}},\"chart\":{\"type\":\"scatter\",\"properties\":{\"fill\":true,\"backgroundColor\":\"#DDDDDD\",\"borderColor\":\"rgba(59, 89, 152, 1)\",\"pointHoverBackgroundColor\":\"rgba(59, 89, 152, 1)\",\"pointHoverBorderColor\":\"rgba(59, 89, 152, 1)\",\"cubicInterpolationmode\":\"monotone\"}}}'),
(4, 'pressure', 'hPa', '{\"widget\":{\"type\":\"text\",\"properties\":{}},\"chart\":{\"type\":\"scatter\",\"properties\":{\"fill\":false,\"borderColor\":\"rgb(64, 237, 33)\",\"pointHoverBackgroundColor\":\"rgb(64, 237, 33)\",\"pointHoverBorderColor\":\"rgb(44, 186, 19)\"}}}'),
(5, 'fine dust', 'p10', '{\"widget\":{\"type\":\"text\",\"properties\":{}},\"chart\":{\"type\":\"scatter\",\"properties\":{\"fill\":false,\"borderColor\":\"rgb(128, 70, 70)\",\"pointHoverBackgroundColor\":\"rgb(128, 70, 70)\",\"pointHoverBorderColor\":\"rgb(74, 40, 40)\"}}}'),
(6, 'fine dust', 'p2.5', '{\"widget\":{\"type\":\"text\",\"properties\":{}},\"chart\":{\"type\":\"scatter\",\"properties\":{\"fill\":false,\"borderColor\":\"rgb(128, 70, 70)\",\"pointHoverBackgroundColor\":\"rgb(128, 70, 70)\",\"pointHoverBorderColor\":\"rgb(74, 40, 40)\"}}}');
--
-- Indexes for dumped tables
--
--
-- Indexes for table `activationtokens`
--
ALTER TABLE `activationtokens`
ADD PRIMARY KEY (`id`),
ADD KEY `activationtokens_ibfk_1` (`userid`);
--
-- Indexes for table `locations`
--
ALTER TABLE `locations`
ADD PRIMARY KEY (`id`),
ADD UNIQUE KEY `location` (`locationname`(8),`countryname`(8));
--
-- Indexes for table `measvalues`
--
ALTER TABLE `measvalues`
ADD PRIMARY KEY (`id`),
ADD UNIQUE KEY `timestamp` (`sensorid`,`timestamp`);
--
-- Indexes for table `sensorgroupmembers`
--
ALTER TABLE `sensorgroupmembers`
ADD PRIMARY KEY (`id`),
ADD KEY `groupid` (`groupid`),
ADD KEY `sensorid` (`sensorid`);
--
-- Indexes for table `sensorgroups`
--
ALTER TABLE `sensorgroups`
ADD PRIMARY KEY (`id`),
ADD KEY `userid` (`userid`),
ADD KEY `locationid` (`locationid`);
--
-- Indexes for table `sensors`
--
ALTER TABLE `sensors`
ADD PRIMARY KEY (`id`),
ADD UNIQUE KEY `sensor` (`userid`,`sensorname`(8),`locationid`,`valuetypeid`) USING BTREE,
ADD KEY `locationid` (`locationid`),
ADD KEY `valuetypeid` (`valuetypeid`);
--
-- Indexes for table `sessions`
--
ALTER TABLE `sessions`
ADD PRIMARY KEY (`id`),
ADD UNIQUE KEY `session` (`session`),
ADD KEY `userId` (`userId`);
--
-- Indexes for table `users`
--
ALTER TABLE `users`
ADD PRIMARY KEY (`id`),
ADD UNIQUE KEY `username` (`username`(8)),
ADD UNIQUE KEY `email` (`email`(8));
--
-- Indexes for table `valuetypes`
--
ALTER TABLE `valuetypes`
ADD PRIMARY KEY (`id`);
--
-- AUTO_INCREMENT for dumped tables
--
--
-- AUTO_INCREMENT for table `activationtokens`
--
ALTER TABLE `activationtokens`
MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;
--
-- AUTO_INCREMENT for table `locations`
--
ALTER TABLE `locations`
MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=6;
--
-- AUTO_INCREMENT for table `measvalues`
--
ALTER TABLE `measvalues`
MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=2277162;
--
-- AUTO_INCREMENT for table `sensorgroupmembers`
--
ALTER TABLE `sensorgroupmembers`
MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=21;
--
-- AUTO_INCREMENT for table `sensorgroups`
--
ALTER TABLE `sensorgroups`
MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=7;
--
-- AUTO_INCREMENT for table `sensors`
--
ALTER TABLE `sensors`
MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=18;
--
-- AUTO_INCREMENT for table `sessions`
--
ALTER TABLE `sessions`
MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;
--
-- AUTO_INCREMENT for table `users`
--
ALTER TABLE `users`
MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=6;
--
-- AUTO_INCREMENT for table `valuetypes`
--
ALTER TABLE `valuetypes`
MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=7;
--
-- Constraints for dumped tables
--
--
-- Constraints for table `activationtokens`
--
ALTER TABLE `activationtokens`
ADD CONSTRAINT `activationtokens_ibfk_1` FOREIGN KEY (`userid`) REFERENCES `users` (`id`) ON UPDATE NO ACTION;
--
-- Constraints for table `measvalues`
--
ALTER TABLE `measvalues`
ADD CONSTRAINT `measvalues_ibfk_1` FOREIGN KEY (`sensorid`) REFERENCES `sensors` (`id`);
--
-- Constraints for table `sensorgroupmembers`
--
ALTER TABLE `sensorgroupmembers`
ADD CONSTRAINT `sensorgroupmembers_ibfk_1` FOREIGN KEY (`groupid`) REFERENCES `sensorgroups` (`id`) ON DELETE CASCADE,
ADD CONSTRAINT `sensorgroupmembers_ibfk_2` FOREIGN KEY (`sensorid`) REFERENCES `sensors` (`id`) ON DELETE CASCADE;
--
-- Constraints for table `sensorgroups`
--
ALTER TABLE `sensorgroups`
ADD CONSTRAINT `sensorgroups_ibfk_1` FOREIGN KEY (`userid`) REFERENCES `users` (`id`),
ADD CONSTRAINT `sensorgroups_ibfk_2` FOREIGN KEY (`locationid`) REFERENCES `locations` (`id`);
--
-- Constraints for table `sensors`
--
ALTER TABLE `sensors`
ADD CONSTRAINT `sensors_ibfk_1` FOREIGN KEY (`locationid`) REFERENCES `locations` (`id`),
ADD CONSTRAINT `sensors_ibfk_2` FOREIGN KEY (`userid`) REFERENCES `users` (`id`),
ADD CONSTRAINT `sensors_ibfk_3` FOREIGN KEY (`valuetypeid`) REFERENCES `valuetypes` (`id`);
--
-- Constraints for table `sessions`
--
ALTER TABLE `sessions`
ADD CONSTRAINT `sessions_ibfk_1` FOREIGN KEY (`userId`) REFERENCES `users` (`id`);
COMMIT;
/*!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 */;