1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
|
from time import time
from datetime import datetime
from typing import Tuple, List
from .clickhouse import ClickhouseDatabase
from ..api.types import TemperatureSensorLocation
from ..temphum import TempHumNodes
migration_tables_map = {
'temp_diana': TempHumNodes.KBN_SH_HALL.hash(),
'temp_street': TempHumNodes.KBN_BH_2FL_STREET.hash(),
'temp_roof': TempHumNodes.KBN_BH_2FL.hash(),
'temp_room': TempHumNodes.KBN_BH_1FL_BEDROOM.hash(),
'temp_spb1': TempHumNodes.SPB_FLAT120_CABINET.hash(),
'temp': TempHumNodes.KBN_BH_1FL_LIVINGROOM.hash()
}
def get_temperature_table(sensor: TemperatureSensorLocation) -> str:
if sensor == TemperatureSensorLocation.DIANA:
return 'temp_diana'
elif sensor == TemperatureSensorLocation.STREET:
return 'temp_street'
elif sensor == TemperatureSensorLocation.BIG_HOUSE_1:
return 'temp'
elif sensor == TemperatureSensorLocation.BIG_HOUSE_2:
return 'temp_roof'
elif sensor == TemperatureSensorLocation.BIG_HOUSE_ROOM:
return 'temp_room'
elif sensor == TemperatureSensorLocation.SPB1:
return 'temp_spb1'
class SensorsDatabase(ClickhouseDatabase):
def __init__(self):
super().__init__('home')
self.db.execute("""
CREATE TABLE IF NOT EXISTS temphum (
NodeID Uint32,
ClientTime DateTime,
ReceivedTime DateTime,
Temperature Int16,
RelativeHumidity UInt16,
SensorType Enum8('Si7021' = 0, 'DHT12' = 1, 'BME280' = 2),
) ENGINE = MergeTree()
PARTITION BY toYYYYMMDD(ReceivedTime)
ORDER BY (NodeID, ReceivedTime);
""")
def migrate(self):
for table_name, node_id in migration_tables_map.items():
if table_name in ('temp_room',):
sensor_type = 'DHT12'
else:
sensor_type = 'Si7021'
self.logger.info(f'starting copying table {table_name}')
self.db.execute(f"""
INSERT INTO temphum (NodeID, ClientTime, ReceivedTime, Temperature, RelativeHumidify, SensorType)
SELECT {node_id}, ClientTime, ReceivedTime, Temperature, RelativeHumidify, '{sensor_type}' FROM {table_name}
""")
self.logger.info(f'finished copying table {table_name}')
def migrate_del_old_tables(self):
for table_name in migration_tables_map.keys():
self.db.execute(f"DROP TABLE {table_name}")
self.logger.info(f'dropped table {table_name}')
def add_temperature(self,
home_id: int,
client_time: int,
sensor: TemperatureSensorLocation,
temp: int,
rh: int):
table = get_temperature_table(sensor)
sql = """INSERT INTO """ + table + """ (
ClientTime,
ReceivedTime,
HomeID,
Temperature,
RelativeHumidity
) VALUES"""
self.db.execute(sql, [[
client_time,
int(time()),
home_id,
temp,
rh
]])
def get_temperature_recordings(self,
sensor: TemperatureSensorLocation,
time_range: Tuple[datetime, datetime],
home_id=1) -> List[tuple]:
table = get_temperature_table(sensor)
sql = f"""SELECT ClientTime, Temperature, RelativeHumidity
FROM {table}
WHERE ClientTime >= %(from)s AND ClientTime <= %(to)s
ORDER BY ClientTime"""
dt_from, dt_to = time_range
data = self.query(sql, {
'from': dt_from,
'to': dt_to
})
return [(date, temp/100, humidity/100) for date, temp, humidity in data]
|
