1 files changed, 42 insertions, 0 deletions
diff --git a/src/home/database/sensors.py b/src/home/database/sensors.py
index 8155108..34b44bf 100644
--- a/src/home/database/sensors.py
+++ b/src/home/database/sensors.py
@@ -3,6 +3,17 @@ 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:
@@ -28,6 +39,37 @@ def get_temperature_table(sensor: TemperatureSensorLocation) -> str:
 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,