cdS1SGzd

1-- phpMyAdmin SQL Dump
2-- version 4.0.10.20
3-- https://www.phpmyadmin.net
4--
5-- Host: localhost
6-- Generato il: Feb 15, 2018 alle 09:59
7-- Versione del server: 10.0.34-MariaDB
8-- Versione PHP: 5.3.3
9
10SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
11SET time_zone = "+00:00";
12
13
14/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
15/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
16/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
17/*!40101 SET NAMES utf8 */;
18
19--
20-- Database: `contextbrokers`
21--
22CREATE DATABASE IF NOT EXISTS `contextbrokers` DEFAULT CHARACTER SET latin1 COLLATE latin1_swedish_ci;
23USE `contextbrokers`;
24
25-- --------------------------------------------------------
26
27--
28-- Struttura della tabella `attributes`
29--
30
31CREATE TABLE IF NOT EXISTS `attributes` (
32  `sensor` varchar(40) NOT NULL,
33  `name` varchar(20) NOT NULL,
34  `type` varchar(20) NOT NULL,
35  `order` int(11) NOT NULL,
36  PRIMARY KEY (`sensor`,`name`),
37  KEY `sensor` (`sensor`)
38) ENGINE=InnoDB DEFAULT CHARSET=utf8;
39
40--
41-- Dump dei dati per la tabella `attributes`
42--
43
44INSERT INTO `attributes` (`sensor`, `name`, `type`, `order`) VALUES
45('5016928a6e5c3c', 'test', 'float', 1),
46('5b61ff40c98b9', 'test', 'float', 1),
47('5bd5d286880acc', 'test', 'float', 1),
48('6a61679f7eedc8', 'test', 'float', 1),
49('73e0d00934f91', 'test', 'float', 1),
50('8c46136facabe', 'test', 'float', 1),
51('924c7c84c48ef', 'test', 'float', 1),
52('93070cdc6cc08', 'test', 'float', 1),
53('ARDUINO_ST_4201', 'latitude', 'float', 2),
54('ARDUINO_ST_4201', 'longitude', 'float', 3),
55('ARDUINO_ST_4201', 'measure_units', 'string', 4),
56('ARDUINO_ST_4201', 'Temperature', 'float', 1),
57('ARDUINO_ST_4201', 'timestamp', 'integer', 5),
58('ARDUINO_ST_4202', 'Humidity', 'float', 1),
59('ARDUINO_ST_4202', 'latitude', 'float', 2),
60('ARDUINO_ST_4202', 'longitude', 'float', 3),
61('ARDUINO_ST_4202', 'measure_units', 'string', 4),
62('ARDUINO_ST_4202', 'timestamp', 'integer', 5),
63('ARDUINO_ST_4203', 'latitude', 'float', 2),
64('ARDUINO_ST_4203', 'Light', 'float', 1),
65('ARDUINO_ST_4203', 'longitude', 'float', 3),
66('ARDUINO_ST_4203', 'measure_units', 'string', 4),
67('ARDUINO_ST_4203', 'timestamp', 'integer', 5),
68('ARDUINO_ST_4204', 'latitude', 'float', 2),
69('ARDUINO_ST_4204', 'longitude', 'float', 3),
70('ARDUINO_ST_4204', 'measure_units', 'string', 4),
71('ARDUINO_ST_4204', 'Motion_Detection', 'integer', 1),
72('ARDUINO_ST_4204', 'timestamp', 'integer', 5),
73('ARDUINO_ST_4205', 'latitude', 'float', 2),
74('ARDUINO_ST_4205', 'longitude', 'float', 3),
75('ARDUINO_ST_4205', 'measure_units', 'string', 4),
76('ARDUINO_ST_4205', 'Sound_LV', 'float', 1),
77('ARDUINO_ST_4205', 'timestamp', 'integer', 5),
78('ARDUINO_ST_4207', 'latitude', 'float', 2),
79('ARDUINO_ST_4207', 'longitude', 'float', 3),
80('ARDUINO_ST_4207', 'measure_units', 'string', 4),
81('ARDUINO_ST_4207', 'Presence_Detection_E', 'integer', 1),
82('ARDUINO_ST_4207', 'timestamp', 'integer', 5),
83('ARDUINO_ST_4210', 'latitude', 'float', 2),
84('ARDUINO_ST_4210', 'longitude', 'float', 3),
85('ARDUINO_ST_4210', 'measure_units', 'string', 4),
86('ARDUINO_ST_4210', 'timestamp', 'integer', 5),
87('ARDUINO_ST_4210', 'Water_Flowing', 'float', 1),
88('ARDUINO_ST_4212', 'latitude', 'float', 2),
89('ARDUINO_ST_4212', 'longitude', 'float', 3),
90('ARDUINO_ST_4212', 'measure_units', 'string', 4),
91('ARDUINO_ST_4212', 'Power_Meter_M', 'float', 1),
92('ARDUINO_ST_4212', 'timestamp', 'integer', 5),
93('ARDUINO_ST_4213', 'latitude', 'float', 2),
94('ARDUINO_ST_4213', 'longitude', 'float', 3),
95('ARDUINO_ST_4213', 'measure_units', 'string', 4),
96('ARDUINO_ST_4213', 'Power_Meter_S', 'float', 1),
97('ARDUINO_ST_4213', 'timestamp', 'integer', 5),
98('test_dht11', 'button', 'boolean', 1),
99('test_dht11', 'fan', 'boolean', 2),
100('test_dht11', 'geolocalization_lat', 'Float', 3),
101('test_dht11', 'geolocalization_lon', 'Float', 4),
102('test_dht11', 'humidity', 'Float', 5),
103('test_dht11', 'model', 'string', 6),
104('test_dht11', 'temperature', 'Float', 7),
105('test_sensor01', 'geolocalization_lat', 'Float', 1),
106('test_sensor01', 'geolocalization_lon', 'Float', 2),
107('test_sensor01', 'model', 'string', 3),
108('test_sensor01', 'temperature', 'Float', 4),
109('test_sensor02', 'geolocalization_lat', 'Float', 1),
110('test_sensor02', 'geolocalization_lon', 'Float', 2),
111('test_sensor02', 'model', 'string', 3),
112('test_sensor02', 'temperature', 'Float', 4);
113
114-- --------------------------------------------------------
115
116--
117-- Struttura della tabella `contextbroker`
118--
119
120CREATE TABLE IF NOT EXISTS `contextbroker` (
121  `name` varchar(20) NOT NULL,
122  `type` set('orion','rabbitmq','moschitto','') NOT NULL,
123  `ip` varchar(20) NOT NULL,
124  `port` varchar(5) NOT NULL,
125  `login` varchar(20) DEFAULT NULL,
126  `password` varchar(20) DEFAULT NULL,
127  `latitude` varchar(20) DEFAULT NULL,
128  `longitude` varchar(20) DEFAULT NULL,
129  PRIMARY KEY (`name`)
130) ENGINE=InnoDB DEFAULT CHARSET=utf8;
131
132--
133-- Dump dei dati per la tabella `contextbroker`
134--
135
136INSERT INTO `contextbroker` (`name`, `type`, `ip`, `port`, `login`, `password`, `latitude`, `longitude`) VALUES
137('mosquittoUNIMI', 'moschitto', '159.149.129.184', '1883', NULL, NULL, '45.464211', '9.191383'),
138('orionUNIFI', 'orion', '192.168.1.9', '1026', NULL, NULL, '43.77925', '11.24626'),
139('orionUNIMI', 'orion', '159.149.129.184', '1026', NULL, NULL, '45.464211', '9.191383'),
140('rabbitUNIMI', 'rabbitmq', '159.149.129.184', '5672', 'guest', 'XXXXX', '45.464211', '9.191383');
141
142-- --------------------------------------------------------
143
144--
145-- Struttura della tabella `sensors`
146--
147
148CREATE TABLE IF NOT EXISTS `sensors` (
149  `id` varchar(40) NOT NULL,
150  `macaddress` varchar(20) DEFAULT NULL,
151  `type` varchar(20) DEFAULT NULL,
152  `model` varchar(20) DEFAULT NULL,
153  `producer` varchar(20) DEFAULT NULL,
154  `longitude` varchar(20) DEFAULT NULL,
155  `latitude` varchar(20) DEFAULT NULL,
156  `kind` set('sensor','actuator') NOT NULL,
157  `protocol` varchar(20) NOT NULL,
158  `format` set('CSV','JSON','XML','') NOT NULL,
159  `contextBroker` varchar(20) NOT NULL,
160  PRIMARY KEY (`id`),
161  KEY `contextBroker` (`contextBroker`)
162) ENGINE=InnoDB DEFAULT CHARSET=utf8;
163
164--
165-- Dump dei dati per la tabella `sensors`
166--
167
168INSERT INTO `sensors` (`id`, `macaddress`, `type`, `model`, `producer`, `longitude`, `latitude`, `kind`, `protocol`, `format`, `contextBroker`) VALUES
169('5016928a6e5c3c', NULL, 'Test', NULL, NULL, NULL, NULL, 'sensor', 'ngsi', 'JSON', 'orionUNIMI'),
170('5b61ff40c98b9', NULL, 'Test', NULL, NULL, NULL, NULL, 'sensor', 'ngsi', 'JSON', 'orionUNIMI'),
171('5bd5d286880acc', NULL, 'Test', NULL, NULL, NULL, NULL, 'sensor', 'ngsi', 'JSON', 'orionUNIMI'),
172('6a61679f7eedc8', NULL, 'Test', NULL, NULL, NULL, NULL, 'sensor', 'ngsi', 'JSON', 'orionUNIMI'),
173('73e0d00934f91', NULL, 'Test', NULL, NULL, NULL, NULL, 'sensor', 'ngsi', 'JSON', 'orionUNIMI'),
174('8c46136facabe', NULL, 'Test', NULL, NULL, NULL, NULL, 'sensor', 'ngsi', 'JSON', 'orionUNIMI'),
175('924c7c84c48ef', NULL, 'Test', NULL, NULL, NULL, NULL, 'sensor', 'ngsi', 'JSON', 'orionUNIMI'),
176('93070cdc6cc08', NULL, 'Test', NULL, NULL, NULL, NULL, 'sensor', 'ngsi', 'JSON', 'orionUNIMI'),
177('ARDUINO_ST_4201', NULL, 'Temperature', NULL, NULL, NULL, NULL, 'sensor', 'ngsi', 'JSON', 'orionUNIMI'),
178('ARDUINO_ST_4201_actuator', NULL, 'Temperature', NULL, NULL, NULL, NULL, 'actuator', '', '', 'orionUNIMI'),
179('ARDUINO_ST_4202', NULL, 'Humidity', NULL, NULL, NULL, NULL, 'sensor', 'ngsi', 'JSON', 'orionUNIMI'),
180('ARDUINO_ST_4203', NULL, 'Light', NULL, NULL, NULL, NULL, 'sensor', 'ngsi', 'JSON', 'orionUNIMI'),
181('ARDUINO_ST_4204', NULL, 'Motion_Detection', NULL, NULL, NULL, NULL, 'sensor', 'ngsi', 'JSON', 'orionUNIMI'),
182('ARDUINO_ST_4205', NULL, 'Sound_LV', NULL, NULL, NULL, NULL, 'sensor', 'ngsi', 'JSON', 'orionUNIMI'),
183('ARDUINO_ST_4207', NULL, 'Presence_Detection_E', NULL, NULL, NULL, NULL, 'sensor', 'ngsi', 'JSON', 'orionUNIMI'),
184('ARDUINO_ST_4210', NULL, 'Water_Flowing', NULL, NULL, NULL, NULL, 'sensor', 'ngsi', 'JSON', 'orionUNIMI'),
185('ARDUINO_ST_4212', NULL, 'Power_Meter_M', NULL, NULL, NULL, NULL, 'sensor', 'ngsi', 'JSON', 'orionUNIMI'),
186('ARDUINO_ST_4213', NULL, 'Power_Meter_S', NULL, NULL, NULL, NULL, 'sensor', 'ngsi', 'JSON', 'orionUNIMI'),
187('sensor21mosquitto', NULL, 'type_s21mosquitto', NULL, NULL, NULL, NULL, 'sensor', '', 'JSON', 'mosquittoUNIMI'),
188('sensor22mosquitto', NULL, 'type_s22mosquitto', NULL, NULL, NULL, NULL, 'sensor', '', 'JSON', 'mosquittoUNIMI'),
189('sensor31rabbit', NULL, 'type_s31rabbit', NULL, NULL, NULL, NULL, 'sensor', '', '', 'rabbitUNIMI'),
190('sensor32rabbit', NULL, 'type_s32rabbit', NULL, NULL, NULL, NULL, 'sensor', '', '', 'rabbitUNIMI'),
191('sensor32rabbit_actuator', NULL, 'type_s32rabbit_act', NULL, NULL, NULL, NULL, 'actuator', '', '', 'rabbitUNIMI'),
192('test_dht11', NULL, 'Thermometer', NULL, NULL, NULL, NULL, 'sensor', 'ngsi', 'JSON', 'orionUNIMI'),
193('test_sensor01', NULL, 'Thermometer', NULL, NULL, NULL, NULL, 'sensor', 'ngsi', 'JSON', 'orionUNIMI'),
194('test_sensor02', NULL, 'thermometer', NULL, NULL, NULL, NULL, 'sensor', 'ngsi', 'JSON', 'orionUNIMI');
195
196--
197-- Limiti per le tabelle scaricate
198--
199
200--
201-- Limiti per la tabella `attributes`
202--
203ALTER TABLE `attributes`
204  ADD CONSTRAINT `attributes_ibfk_1` FOREIGN KEY (`sensor`) REFERENCES `sensors` (`id`) ON DELETE CASCADE ON UPDATE CASCADE;
205
206--
207-- Limiti per la tabella `sensors`
208--
209ALTER TABLE `sensors`
210  ADD CONSTRAINT `sensors_ibfk_1` FOREIGN KEY (`contextBroker`) REFERENCES `contextbroker` (`name`) ON DELETE NO ACTION ON UPDATE CASCADE;
211
212/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
213/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
214/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;