From: Roman Bazalevsky Date: Mon, 17 Mar 2014 16:33:51 +0000 (+0400) Subject: Storet routines and MySQL data example added X-Git-Url: https://git.rvb.name/weathermon.git/commitdiff_plain/b9870da67ab44b1dbba4a9f5467b0aeaa2fb3e4f?hp=3a48d89e76fd2ab87dabf08632474c529cf77ad4 Storet routines and MySQL data example added --- diff --git a/mysql/meteo_routines.sql b/mysql/meteo_routines.sql new file mode 100644 index 0000000..bdfa3cc --- /dev/null +++ b/mysql/meteo_routines.sql @@ -0,0 +1,93 @@ +CREATE DATABASE IF NOT EXISTS `meteo` /*!40100 DEFAULT CHARACTER SET utf8 */; +USE `meteo`; +-- MySQL dump 10.13 Distrib 5.5.35, for debian-linux-gnu (i686) +-- +-- Host: estia Database: meteo +-- ------------------------------------------------------ +-- Server version 5.5.35-0ubuntu0.13.10.2 + +/*!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 */; +/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */; +/*!40103 SET TIME_ZONE='+00:00' */; +/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */; +/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */; +/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */; +/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */; + +-- +-- Dumping routines for database 'meteo' +-- +/*!50003 DROP FUNCTION IF EXISTS `unitconv` */; +/*!50003 SET @saved_cs_client = @@character_set_client */ ; +/*!50003 SET @saved_cs_results = @@character_set_results */ ; +/*!50003 SET @saved_col_connection = @@collation_connection */ ; +/*!50003 SET character_set_client = utf8 */ ; +/*!50003 SET character_set_results = utf8 */ ; +/*!50003 SET collation_connection = utf8_general_ci */ ; +/*!50003 SET @saved_sql_mode = @@sql_mode */ ; +/*!50003 SET sql_mode = '' */ ; +DELIMITER ;; +CREATE DEFINER=`admin`@`%` FUNCTION `unitconv`(pValue float, pFromUnit integer, pToUnit integer) RETURNS float +BEGIN + declare result float; + declare a,b,c float; + if pFromUnit=pToUnit then + set result = pValue; + else + select u.a,u.b,u.c into a,b,c from unit_conv u where from_unit=pFromUnit and to_unit=pToUnit; + set result=b; + set result = (pValue+a)*b+c; + end if; + return result; +END ;; +DELIMITER ; +/*!50003 SET sql_mode = @saved_sql_mode */ ; +/*!50003 SET character_set_client = @saved_cs_client */ ; +/*!50003 SET character_set_results = @saved_cs_results */ ; +/*!50003 SET collation_connection = @saved_col_connection */ ; +/*!50003 DROP PROCEDURE IF EXISTS `submit_value` */; +/*!50003 SET @saved_cs_client = @@character_set_client */ ; +/*!50003 SET @saved_cs_results = @@character_set_results */ ; +/*!50003 SET @saved_col_connection = @@collation_connection */ ; +/*!50003 SET character_set_client = utf8 */ ; +/*!50003 SET character_set_results = utf8 */ ; +/*!50003 SET collation_connection = utf8_general_ci */ ; +/*!50003 SET @saved_sql_mode = @@sql_mode */ ; +/*!50003 SET sql_mode = '' */ ; +DELIMITER ;; +CREATE DEFINER=`admin`@`%` PROCEDURE `submit_value`(pSType varchar(32),pSID varchar(32),pParam varchar(32),pValue float) +BEGIN + declare lSTID int; + declare lSID int; + declare lSTPID int; + select max(id) into lSTID from sensor_types where st_name=pSType; + if lSTID is not null then + select max(id) into lSID from sensors where st_id=lSTID and s_id=pSID; + if lSID is not null then + select max(id) into lSTPID from st_parameters where st_id=lSTID and st_name=pParam; + if lSTPID is not null then + insert into sensor_values(sensor_id,parameter_id,timestamp,value) + values(lSID,lSTPID,current_timestamp(),pValue); + end if; + end if; + end if; +END ;; +DELIMITER ; +/*!50003 SET sql_mode = @saved_sql_mode */ ; +/*!50003 SET character_set_client = @saved_cs_client */ ; +/*!50003 SET character_set_results = @saved_cs_results */ ; +/*!50003 SET collation_connection = @saved_col_connection */ ; +/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */; + +/*!40101 SET SQL_MODE=@OLD_SQL_MODE */; +/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */; +/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */; +/*!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 */; +/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */; + +-- Dump completed on 2014-03-17 20:31:48 diff --git a/mysql/meteo_sensor_types.sql b/mysql/meteo_sensor_types.sql new file mode 100644 index 0000000..726f1ec --- /dev/null +++ b/mysql/meteo_sensor_types.sql @@ -0,0 +1,55 @@ +CREATE DATABASE IF NOT EXISTS `meteo` /*!40100 DEFAULT CHARACTER SET utf8 */; +USE `meteo`; +-- MySQL dump 10.13 Distrib 5.5.35, for debian-linux-gnu (i686) +-- +-- Host: estia Database: meteo +-- ------------------------------------------------------ +-- Server version 5.5.35-0ubuntu0.13.10.2 + +/*!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 */; +/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */; +/*!40103 SET TIME_ZONE='+00:00' */; +/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */; +/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */; +/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */; +/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */; + +-- +-- Table structure for table `sensor_types` +-- + +DROP TABLE IF EXISTS `sensor_types`; +/*!40101 SET @saved_cs_client = @@character_set_client */; +/*!40101 SET character_set_client = utf8 */; +CREATE TABLE `sensor_types` ( + `id` int(11) NOT NULL AUTO_INCREMENT, + `st_name` varchar(32) CHARACTER SET utf8 NOT NULL, + `st_description` varchar(255) CHARACTER SET utf8 DEFAULT NULL, + PRIMARY KEY (`id`), + UNIQUE KEY `st_name` (`st_name`) +) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=latin1; +/*!40101 SET character_set_client = @saved_cs_client */; + +-- +-- Dumping data for table `sensor_types` +-- + +LOCK TABLES `sensor_types` WRITE; +/*!40000 ALTER TABLE `sensor_types` DISABLE KEYS */; +INSERT INTO `sensor_types` VALUES (1,'INDOOR','Внутренний датчик влажности'),(2,'OUTDOOR','Внешний датчик температуры и влажности'),(3,'BARO','Внутренний датчик давления и температуры'); +/*!40000 ALTER TABLE `sensor_types` ENABLE KEYS */; +UNLOCK TABLES; +/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */; + +/*!40101 SET SQL_MODE=@OLD_SQL_MODE */; +/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */; +/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */; +/*!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 */; +/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */; + +-- Dump completed on 2014-03-17 20:31:42 diff --git a/mysql/meteo_sensor_values.sql b/mysql/meteo_sensor_values.sql new file mode 100644 index 0000000..77aa5d2 --- /dev/null +++ b/mysql/meteo_sensor_values.sql @@ -0,0 +1,60 @@ +CREATE DATABASE IF NOT EXISTS `meteo` /*!40100 DEFAULT CHARACTER SET utf8 */; +USE `meteo`; +-- MySQL dump 10.13 Distrib 5.5.35, for debian-linux-gnu (i686) +-- +-- Host: estia Database: meteo +-- ------------------------------------------------------ +-- Server version 5.5.35-0ubuntu0.13.10.2 + +/*!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 */; +/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */; +/*!40103 SET TIME_ZONE='+00:00' */; +/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */; +/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */; +/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */; +/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */; + +-- +-- Table structure for table `sensor_values` +-- + +DROP TABLE IF EXISTS `sensor_values`; +/*!40101 SET @saved_cs_client = @@character_set_client */; +/*!40101 SET character_set_client = utf8 */; +CREATE TABLE `sensor_values` ( + `id` int(11) NOT NULL AUTO_INCREMENT, + `sensor_id` int(11) NOT NULL, + `timestamp` datetime NOT NULL, + `value` float NOT NULL, + `parameter_id` int(11) NOT NULL, + PRIMARY KEY (`id`), + KEY `fk_sensor_values_sens_idx` (`sensor_id`), + KEY `sensor_values_idx` (`sensor_id`,`timestamp`), + KEY `fk_sensor_values_param_idx` (`parameter_id`), + CONSTRAINT `fk_sensor_values_param` FOREIGN KEY (`parameter_id`) REFERENCES `st_parameters` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION, + CONSTRAINT `fk_sensor_values_sens` FOREIGN KEY (`sensor_id`) REFERENCES `sensors` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION +) ENGINE=InnoDB AUTO_INCREMENT=223906 DEFAULT CHARSET=utf8; +/*!40101 SET character_set_client = @saved_cs_client */; + +-- +-- Dumping data for table `sensor_values` +-- + +LOCK TABLES `sensor_values` WRITE; +/*!40000 ALTER TABLE `sensor_values` DISABLE KEYS */; +/*!40000 ALTER TABLE `sensor_values` ENABLE KEYS */; +UNLOCK TABLES; +/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */; + +/*!40101 SET SQL_MODE=@OLD_SQL_MODE */; +/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */; +/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */; +/*!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 */; +/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */; + +-- Dump completed on 2014-03-17 20:31:41 diff --git a/mysql/meteo_sensors.sql b/mysql/meteo_sensors.sql new file mode 100644 index 0000000..6b17ae1 --- /dev/null +++ b/mysql/meteo_sensors.sql @@ -0,0 +1,58 @@ +CREATE DATABASE IF NOT EXISTS `meteo` /*!40100 DEFAULT CHARACTER SET utf8 */; +USE `meteo`; +-- MySQL dump 10.13 Distrib 5.5.35, for debian-linux-gnu (i686) +-- +-- Host: estia Database: meteo +-- ------------------------------------------------------ +-- Server version 5.5.35-0ubuntu0.13.10.2 + +/*!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 */; +/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */; +/*!40103 SET TIME_ZONE='+00:00' */; +/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */; +/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */; +/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */; +/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */; + +-- +-- Table structure for table `sensors` +-- + +DROP TABLE IF EXISTS `sensors`; +/*!40101 SET @saved_cs_client = @@character_set_client */; +/*!40101 SET character_set_client = utf8 */; +CREATE TABLE `sensors` ( + `id` int(11) NOT NULL AUTO_INCREMENT, + `st_id` int(11) NOT NULL, + `s_id` varchar(32) CHARACTER SET utf8 NOT NULL, + `s_description` varchar(255) CHARACTER SET utf8 DEFAULT NULL, + PRIMARY KEY (`id`), + UNIQUE KEY `UNIQUE` (`st_id`,`s_id`), + KEY `fk_sensors_st_idx` (`st_id`), + CONSTRAINT `fk_sensors_st` FOREIGN KEY (`st_id`) REFERENCES `sensor_types` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION +) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=latin1; +/*!40101 SET character_set_client = @saved_cs_client */; + +-- +-- Dumping data for table `sensors` +-- + +LOCK TABLES `sensors` WRITE; +/*!40000 ALTER TABLE `sensors` DISABLE KEYS */; +INSERT INTO `sensors` VALUES (1,1,'DEFAULT','Комната'),(2,2,'44F','Улица'),(3,3,'DEFAULT','Комната'); +/*!40000 ALTER TABLE `sensors` ENABLE KEYS */; +UNLOCK TABLES; +/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */; + +/*!40101 SET SQL_MODE=@OLD_SQL_MODE */; +/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */; +/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */; +/*!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 */; +/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */; + +-- Dump completed on 2014-03-17 20:31:46 diff --git a/mysql/meteo_st_parameters.sql b/mysql/meteo_st_parameters.sql new file mode 100644 index 0000000..2eb093a --- /dev/null +++ b/mysql/meteo_st_parameters.sql @@ -0,0 +1,65 @@ +CREATE DATABASE IF NOT EXISTS `meteo` /*!40100 DEFAULT CHARACTER SET utf8 */; +USE `meteo`; +-- MySQL dump 10.13 Distrib 5.5.35, for debian-linux-gnu (i686) +-- +-- Host: estia Database: meteo +-- ------------------------------------------------------ +-- Server version 5.5.35-0ubuntu0.13.10.2 + +/*!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 */; +/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */; +/*!40103 SET TIME_ZONE='+00:00' */; +/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */; +/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */; +/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */; +/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */; + +-- +-- Table structure for table `st_parameters` +-- + +DROP TABLE IF EXISTS `st_parameters`; +/*!40101 SET @saved_cs_client = @@character_set_client */; +/*!40101 SET character_set_client = utf8 */; +CREATE TABLE `st_parameters` ( + `id` int(11) NOT NULL AUTO_INCREMENT, + `st_id` int(11) NOT NULL, + `st_name` varchar(32) CHARACTER SET utf8 NOT NULL, + `st_unit` int(11) NOT NULL, + `st_description` varchar(255) CHARACTER SET utf8 DEFAULT NULL, + `st_dot_color` varchar(45) DEFAULT 'red', + `st_fill_color_top` varchar(45) DEFAULT 'red', + `st_fill_color_bottom` varchar(45) DEFAULT 'blue', + `st_line_color` varchar(45) DEFAULT 'navy', + PRIMARY KEY (`id`), + UNIQUE KEY `unique` (`st_id`,`st_name`), + KEY `fk_st_parameters_st_idx` (`st_id`), + KEY `fk_st_parameters_units_idx` (`st_unit`), + CONSTRAINT `fk_st_parameters_st` FOREIGN KEY (`st_id`) REFERENCES `sensor_types` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION, + CONSTRAINT `fk_st_parameters_units` FOREIGN KEY (`st_unit`) REFERENCES `units` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION +) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=latin1; +/*!40101 SET character_set_client = @saved_cs_client */; + +-- +-- Dumping data for table `st_parameters` +-- + +LOCK TABLES `st_parameters` WRITE; +/*!40000 ALTER TABLE `st_parameters` DISABLE KEYS */; +INSERT INTO `st_parameters` VALUES (1,3,'TEMPERATURE',1,'Температура','red','red','navy','darkred'),(2,1,'HUMIDITY',2,'Влажность','skyblue','skyblue','blue','royalblue'),(3,2,'TEMPERATURE',1,'Температура','red','red','navy','darkred'),(4,2,'HUMIDITY',2,'Влажность','skyblue','skyblue','blue','royalblue'),(5,3,'PRESSURE',3,'Атмосферное давление','green','green','gold','gold'); +/*!40000 ALTER TABLE `st_parameters` ENABLE KEYS */; +UNLOCK TABLES; +/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */; + +/*!40101 SET SQL_MODE=@OLD_SQL_MODE */; +/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */; +/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */; +/*!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 */; +/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */; + +-- Dump completed on 2014-03-17 20:31:44 diff --git a/mysql/meteo_unit_conv.sql b/mysql/meteo_unit_conv.sql new file mode 100644 index 0000000..f58fa83 --- /dev/null +++ b/mysql/meteo_unit_conv.sql @@ -0,0 +1,57 @@ +CREATE DATABASE IF NOT EXISTS `meteo` /*!40100 DEFAULT CHARACTER SET utf8 */; +USE `meteo`; +-- MySQL dump 10.13 Distrib 5.5.35, for debian-linux-gnu (i686) +-- +-- Host: estia Database: meteo +-- ------------------------------------------------------ +-- Server version 5.5.35-0ubuntu0.13.10.2 + +/*!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 */; +/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */; +/*!40103 SET TIME_ZONE='+00:00' */; +/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */; +/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */; +/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */; +/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */; + +-- +-- Table structure for table `unit_conv` +-- + +DROP TABLE IF EXISTS `unit_conv`; +/*!40101 SET @saved_cs_client = @@character_set_client */; +/*!40101 SET character_set_client = utf8 */; +CREATE TABLE `unit_conv` ( + `id` int(11) NOT NULL AUTO_INCREMENT, + `from_unit` int(11) NOT NULL, + `to_unit` int(11) NOT NULL, + `a` float NOT NULL, + `b` float NOT NULL, + `c` float NOT NULL, + PRIMARY KEY (`id`) +) ENGINE=InnoDB AUTO_INCREMENT=15 DEFAULT CHARSET=utf8; +/*!40101 SET character_set_client = @saved_cs_client */; + +-- +-- Dumping data for table `unit_conv` +-- + +LOCK TABLES `unit_conv` WRITE; +/*!40000 ALTER TABLE `unit_conv` DISABLE KEYS */; +INSERT INTO `unit_conv` VALUES (1,1,4,0,1.4,32),(2,4,1,-32,0.5555,0),(3,3,5,0,0.0075,0),(4,5,3,0,133.322,0),(5,3,6,0,0.01,0),(6,6,3,0,100,0),(7,3,7,0,0.0002953,0),(8,7,3,0,3386,0),(9,5,6,0,1.3332,0),(10,6,5,0,0.75,0),(11,5,7,0,0.03937,0),(12,7,5,0,25.4,0),(13,6,7,0,0.02952,0),(14,7,6,0,33.86,0); +/*!40000 ALTER TABLE `unit_conv` ENABLE KEYS */; +UNLOCK TABLES; +/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */; + +/*!40101 SET SQL_MODE=@OLD_SQL_MODE */; +/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */; +/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */; +/*!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 */; +/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */; + +-- Dump completed on 2014-03-17 20:31:31 diff --git a/mysql/meteo_unit_groups.sql b/mysql/meteo_unit_groups.sql new file mode 100644 index 0000000..660af13 --- /dev/null +++ b/mysql/meteo_unit_groups.sql @@ -0,0 +1,53 @@ +CREATE DATABASE IF NOT EXISTS `meteo` /*!40100 DEFAULT CHARACTER SET utf8 */; +USE `meteo`; +-- MySQL dump 10.13 Distrib 5.5.35, for debian-linux-gnu (i686) +-- +-- Host: estia Database: meteo +-- ------------------------------------------------------ +-- Server version 5.5.35-0ubuntu0.13.10.2 + +/*!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 */; +/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */; +/*!40103 SET TIME_ZONE='+00:00' */; +/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */; +/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */; +/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */; +/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */; + +-- +-- Table structure for table `unit_groups` +-- + +DROP TABLE IF EXISTS `unit_groups`; +/*!40101 SET @saved_cs_client = @@character_set_client */; +/*!40101 SET character_set_client = utf8 */; +CREATE TABLE `unit_groups` ( + `id` int(11) NOT NULL AUTO_INCREMENT, + `name` varchar(45) NOT NULL, + PRIMARY KEY (`id`) +) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8; +/*!40101 SET character_set_client = @saved_cs_client */; + +-- +-- Dumping data for table `unit_groups` +-- + +LOCK TABLES `unit_groups` WRITE; +/*!40000 ALTER TABLE `unit_groups` DISABLE KEYS */; +INSERT INTO `unit_groups` VALUES (1,'Температура'),(2,'Влажность'),(3,'Давление'); +/*!40000 ALTER TABLE `unit_groups` ENABLE KEYS */; +UNLOCK TABLES; +/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */; + +/*!40101 SET SQL_MODE=@OLD_SQL_MODE */; +/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */; +/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */; +/*!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 */; +/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */; + +-- Dump completed on 2014-03-17 20:31:30 diff --git a/mysql/meteo_units.sql b/mysql/meteo_units.sql new file mode 100644 index 0000000..227d044 --- /dev/null +++ b/mysql/meteo_units.sql @@ -0,0 +1,57 @@ +CREATE DATABASE IF NOT EXISTS `meteo` /*!40100 DEFAULT CHARACTER SET utf8 */; +USE `meteo`; +-- MySQL dump 10.13 Distrib 5.5.35, for debian-linux-gnu (i686) +-- +-- Host: estia Database: meteo +-- ------------------------------------------------------ +-- Server version 5.5.35-0ubuntu0.13.10.2 + +/*!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 */; +/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */; +/*!40103 SET TIME_ZONE='+00:00' */; +/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */; +/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */; +/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */; +/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */; + +-- +-- Table structure for table `units` +-- + +DROP TABLE IF EXISTS `units`; +/*!40101 SET @saved_cs_client = @@character_set_client */; +/*!40101 SET character_set_client = utf8 */; +CREATE TABLE `units` ( + `id` int(11) NOT NULL AUTO_INCREMENT, + `name` varchar(32) CHARACTER SET utf8 NOT NULL, + `name_short` varchar(8) CHARACTER SET utf8 NOT NULL, + `unit_group` int(11) NOT NULL, + PRIMARY KEY (`id`), + KEY `fk_units_group_idx` (`unit_group`), + CONSTRAINT `fk_units_group` FOREIGN KEY (`unit_group`) REFERENCES `unit_groups` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION +) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=latin1; +/*!40101 SET character_set_client = @saved_cs_client */; + +-- +-- Dumping data for table `units` +-- + +LOCK TABLES `units` WRITE; +/*!40000 ALTER TABLE `units` DISABLE KEYS */; +INSERT INTO `units` VALUES (1,'Градусы Цельсия','C',1),(2,'Проценты','%',2),(3,'Паскали','Па',3),(4,'Градусы Фаренгейта','F',1),(5,'Миллиметры ртутного столба','мм',3),(6,'Миллибары','мбар',3),(7,'Дюймы ртутного столба','inHg',3); +/*!40000 ALTER TABLE `units` ENABLE KEYS */; +UNLOCK TABLES; +/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */; + +/*!40101 SET SQL_MODE=@OLD_SQL_MODE */; +/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */; +/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */; +/*!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 */; +/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */; + +-- Dump completed on 2014-03-17 20:31:43 diff --git a/mysql/mysql.dump b/mysql/mysql.dump deleted file mode 100644 index 46e6ffd..0000000 --- a/mysql/mysql.dump +++ /dev/null @@ -1,160 +0,0 @@ --- MySQL dump 10.13 Distrib 5.5.35, for debian-linux-gnu (i686) --- --- Host: estia Database: meteo --- ------------------------------------------------------ --- Server version 5.5.35-0ubuntu0.13.10.2 - -/*!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 */; -/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */; -/*!40103 SET TIME_ZONE='+00:00' */; -/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */; -/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */; -/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */; -/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */; - --- --- Table structure for table `sensor_types` --- - -DROP TABLE IF EXISTS `sensor_types`; -/*!40101 SET @saved_cs_client = @@character_set_client */; -/*!40101 SET character_set_client = utf8 */; -CREATE TABLE `sensor_types` ( - `id` int(11) NOT NULL AUTO_INCREMENT, - `st_name` varchar(32) CHARACTER SET utf8 NOT NULL, - `st_description` varchar(255) CHARACTER SET utf8 DEFAULT NULL, - PRIMARY KEY (`id`), - UNIQUE KEY `st_name` (`st_name`) -) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=latin1; -/*!40101 SET character_set_client = @saved_cs_client */; - --- --- Table structure for table `sensor_values` --- - -DROP TABLE IF EXISTS `sensor_values`; -/*!40101 SET @saved_cs_client = @@character_set_client */; -/*!40101 SET character_set_client = utf8 */; -CREATE TABLE `sensor_values` ( - `id` int(11) NOT NULL AUTO_INCREMENT, - `sensor_id` int(11) NOT NULL, - `timestamp` datetime NOT NULL, - `value` float NOT NULL, - `parameter_id` int(11) NOT NULL, - PRIMARY KEY (`id`), - KEY `fk_sensor_values_sens_idx` (`sensor_id`), - KEY `sensor_values_idx` (`sensor_id`,`timestamp`), - KEY `fk_sensor_values_param_idx` (`parameter_id`), - CONSTRAINT `fk_sensor_values_param` FOREIGN KEY (`parameter_id`) REFERENCES `st_parameters` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION, - CONSTRAINT `fk_sensor_values_sens` FOREIGN KEY (`sensor_id`) REFERENCES `sensors` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION -) ENGINE=InnoDB AUTO_INCREMENT=223855 DEFAULT CHARSET=utf8; -/*!40101 SET character_set_client = @saved_cs_client */; - --- --- Table structure for table `sensors` --- - -DROP TABLE IF EXISTS `sensors`; -/*!40101 SET @saved_cs_client = @@character_set_client */; -/*!40101 SET character_set_client = utf8 */; -CREATE TABLE `sensors` ( - `id` int(11) NOT NULL AUTO_INCREMENT, - `st_id` int(11) NOT NULL, - `s_id` varchar(32) CHARACTER SET utf8 NOT NULL, - `s_description` varchar(255) CHARACTER SET utf8 DEFAULT NULL, - PRIMARY KEY (`id`), - UNIQUE KEY `UNIQUE` (`st_id`,`s_id`), - KEY `fk_sensors_st_idx` (`st_id`), - CONSTRAINT `fk_sensors_st` FOREIGN KEY (`st_id`) REFERENCES `sensor_types` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION -) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=latin1; -/*!40101 SET character_set_client = @saved_cs_client */; - --- --- Table structure for table `st_parameters` --- - -DROP TABLE IF EXISTS `st_parameters`; -/*!40101 SET @saved_cs_client = @@character_set_client */; -/*!40101 SET character_set_client = utf8 */; -CREATE TABLE `st_parameters` ( - `id` int(11) NOT NULL AUTO_INCREMENT, - `st_id` int(11) NOT NULL, - `st_name` varchar(32) CHARACTER SET utf8 NOT NULL, - `st_unit` int(11) NOT NULL, - `st_description` varchar(255) CHARACTER SET utf8 DEFAULT NULL, - `st_dot_color` varchar(45) DEFAULT 'red', - `st_fill_color_top` varchar(45) DEFAULT 'red', - `st_fill_color_bottom` varchar(45) DEFAULT 'blue', - `st_line_color` varchar(45) DEFAULT 'navy', - PRIMARY KEY (`id`), - UNIQUE KEY `unique` (`st_id`,`st_name`), - KEY `fk_st_parameters_st_idx` (`st_id`), - KEY `fk_st_parameters_units_idx` (`st_unit`), - CONSTRAINT `fk_st_parameters_st` FOREIGN KEY (`st_id`) REFERENCES `sensor_types` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION, - CONSTRAINT `fk_st_parameters_units` FOREIGN KEY (`st_unit`) REFERENCES `units` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION -) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=latin1; -/*!40101 SET character_set_client = @saved_cs_client */; - --- --- Table structure for table `unit_conv` --- - -DROP TABLE IF EXISTS `unit_conv`; -/*!40101 SET @saved_cs_client = @@character_set_client */; -/*!40101 SET character_set_client = utf8 */; -CREATE TABLE `unit_conv` ( - `id` int(11) NOT NULL AUTO_INCREMENT, - `from_unit` int(11) NOT NULL, - `to_unit` int(11) NOT NULL, - `a` float NOT NULL, - `b` float NOT NULL, - `c` float NOT NULL, - PRIMARY KEY (`id`) -) ENGINE=InnoDB AUTO_INCREMENT=15 DEFAULT CHARSET=utf8; -/*!40101 SET character_set_client = @saved_cs_client */; - --- --- Table structure for table `unit_groups` --- - -DROP TABLE IF EXISTS `unit_groups`; -/*!40101 SET @saved_cs_client = @@character_set_client */; -/*!40101 SET character_set_client = utf8 */; -CREATE TABLE `unit_groups` ( - `id` int(11) NOT NULL AUTO_INCREMENT, - `name` varchar(45) NOT NULL, - PRIMARY KEY (`id`) -) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8; -/*!40101 SET character_set_client = @saved_cs_client */; - --- --- Table structure for table `units` --- - -DROP TABLE IF EXISTS `units`; -/*!40101 SET @saved_cs_client = @@character_set_client */; -/*!40101 SET character_set_client = utf8 */; -CREATE TABLE `units` ( - `id` int(11) NOT NULL AUTO_INCREMENT, - `name` varchar(32) CHARACTER SET utf8 NOT NULL, - `name_short` varchar(8) CHARACTER SET utf8 NOT NULL, - `unit_group` int(11) NOT NULL, - PRIMARY KEY (`id`), - KEY `fk_units_group_idx` (`unit_group`), - CONSTRAINT `fk_units_group` FOREIGN KEY (`unit_group`) REFERENCES `unit_groups` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION -) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=latin1; -/*!40101 SET character_set_client = @saved_cs_client */; -/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */; - -/*!40101 SET SQL_MODE=@OLD_SQL_MODE */; -/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */; -/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */; -/*!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 */; -/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */; - --- Dump completed on 2014-03-17 20:17:36