[P] Add database update after marker deletion.

Add test for new records in DB

MapView/datasource.py

@@ -4,6 +4,8 @@ from datetime import datetime
 import websockets
 from kivy import Logger
 from pydantic import BaseModel, field_validator
+from websockets.asyncio.connection import Connection
+
 from config import STORE_HOST, STORE_PORT
 
 
@@ -33,10 +35,12 @@ class ProcessedAgentData(BaseModel):
 
 class Datasource:
     def __init__(self, user_id: int):
+        self.websocket: Connection | None = None
         self.index = 0
         self.user_id = user_id
         self.connection_status = None
         self._new_points = []
+        self._active_markers = []
         asyncio.ensure_future(self.connect_to_server())
 
     def get_new_points(self):
@@ -51,6 +55,7 @@ class Datasource:
             Logger.debug("CONNECT TO SERVER")
             async with websockets.connect(uri) as websocket:
                 self.connection_status = "Connected"
+                self.websocket = websocket 
                 try:
                     while True:
                         data = await websocket.recv()
@@ -60,6 +65,26 @@ class Datasource:
                     self.connection_status = "Disconnected"
                     Logger.debug("SERVER DISCONNECT")
 
+    async def update_db_records(self):
+        if self.websocket:
+            data = json.dumps({"id": self.user_id})
+            await self.websocket.send(data, True)
+
+    def map_lat_lon_to_processed_agent_data(self, lat: float, lon: float) -> ProcessedAgentData | None:
+        distances = tuple((abs(lon - marker.latitude) ** 2 + abs(lat - marker.longitude) ** 2) ** 0.5 for marker in
+                          self._active_markers)
+
+        if len(distances) == 0:
+            return None
+
+        min_distance = min(distances)
+        marker = self._active_markers[distances.index(min_distance)]
+
+        if min_distance < 0.005:
+            return marker
+        else:
+            return None
+
     def handle_received_data(self, data):
         # Update your UI or perform actions with received data here
         Logger.debug(f"Received data: {data}")
@@ -70,6 +95,9 @@ class Datasource:
             ],
             key=lambda v: v.timestamp,
         )
+
+        self._active_markers += [i for i in processed_agent_data_list if i.road_state != 'normal']
+
         new_points = [
             (
                 processed_agent_data.longitude,

MapView/main.py

@@ -15,18 +15,31 @@ line_layer_colors = [
     [1, 0, 1, 1],
 ]
 
+
+def get_lat_lon(point: dict[str, float] | list[float] | tuple[float, float]) -> tuple[float, float] | None:
+    if isinstance(point, dict):
+        lat = point.get("lat")
+        lon = point.get("lon")
+    else:
+        lat, lon = point
+
+    if lat is None or lon is None:
+        return None
+    return lat, lon
+
+
 class MapViewApp(App):
     def __init__(self, **kwargs):
         super().__init__(**kwargs)
 
-        self.mapview = None
+        self.mapview: MapView | None = None
         self.datasource = Datasource(user_id=1)
         self.line_layers = dict()
         self.car_markers = dict()
 
         # додати необхідні змінні
-        self.bump_markers = []
-        self.pothole_markers = []
+        self.bump_markers: list[MapMarker] = []
+        self.pothole_markers: list[MapMarker] = []
 
     def on_start(self):
         """
@@ -50,7 +63,7 @@ class MapViewApp(App):
 
             # Оновлює лінію маршрута
             if user_id not in self.line_layers:
-                self.line_layers[user_id] = LineMapLayer(color = line_layer_colors[user_id % len(line_layer_colors)])
+                self.line_layers[user_id] = LineMapLayer(color=line_layer_colors[user_id % len(line_layer_colors)])
                 self.mapview.add_layer(self.line_layers[user_id])
 
             self.line_layers[user_id].add_point((lat, lon))
@@ -91,13 +104,16 @@ class MapViewApp(App):
         if user_id == config.TRACK_ID:
             self.mapview.center_on(lat, lon)
 
-    def set_pothole_marker(self, point):
-        if isinstance(point, dict):
-            lat = point.get("lat")
-            lon = point.get("lon")
-        else:
-            lat, lon = point
+    def map_lat_lon_to_marker(self, lat: float, lon: float) -> MapMarker | None:
+        flt = filter(lambda marker: lon == marker.lat and lat == marker.lon, self.pothole_markers + self.bump_markers)
 
+        try:
+            return next(flt)
+        except StopIteration:
+            return None
+
+    def set_pothole_marker(self, point):
+        lat, lon = get_lat_lon(point)
         if lat is None or lon is None:
             return
 
@@ -111,12 +127,7 @@ class MapViewApp(App):
         self.pothole_markers.append(marker)
 
     def set_bump_marker(self, point):
-        if isinstance(point, dict):
-            lat = point.get("lat")
-            lon = point.get("lon")
-        else:
-            lat, lon = point
-
+        lat, lon = get_lat_lon(point)
         if lat is None or lon is None:
             return
 
@@ -129,6 +140,35 @@ class MapViewApp(App):
         self.mapview.add_marker(marker)
         self.bump_markers.append(marker)
 
+    def delete_pothole_marker(self, point):
+        lat, lon = get_lat_lon(point)
+        if lat is None or lon is None:
+            return
+
+        clicked_marker_data = self.datasource.map_lat_lon_to_processed_agent_data(lat, lon)
+
+        if not clicked_marker_data:
+            return
+
+        clicked_marker = self.map_lat_lon_to_marker(clicked_marker_data.latitude, clicked_marker_data.longitude)
+
+        if clicked_marker is None:
+            return
+
+        self.mapview.remove_marker(clicked_marker)
+
+        if clicked_marker in self.pothole_markers:
+            self.pothole_markers.pop(self.pothole_markers.index(clicked_marker))
+        elif clicked_marker in self.bump_markers:
+            self.bump_markers.pop(self.bump_markers.index(clicked_marker))
+
+        self.datasource.update_db_records()
+
+    def on_touch_down(self, widget, touch):
+        if touch.button == "right":
+            coordinate = self.mapview.get_latlon_at(touch.x, touch.y, self.mapview.zoom)
+            self.delete_pothole_marker(coordinate)
+            return True
 
     def build(self):
         """
@@ -141,6 +181,8 @@ class MapViewApp(App):
             lon=30.5234
         )
 
+        self.mapview.bind(on_touch_down=self.on_touch_down)
+
         return self.mapview
 
 

store/docker/db/structure.sql

@@ -7,5 +7,6 @@ CREATE TABLE processed_agent_data (
     z FLOAT,
     latitude FLOAT,
     longitude FLOAT,
-    timestamp TIMESTAMP
+    timestamp TIMESTAMP,
+    visible BOOLEAN
 );

store/main.py

@@ -8,12 +8,13 @@ from sqlalchemy import (
     Integer,
     String,
     Float,
+    Boolean,
     DateTime,
 )
 from sqlalchemy.sql import select
 
 from database import metadata, SessionLocal
-from schemas import ProcessedAgentData, ProcessedAgentDataInDB
+from schemas import ProcessedAgentData, ProcessedAgentDataInDB, WebSocketData
 
 # FastAPI app setup
 app = FastAPI()
@@ -30,6 +31,7 @@ processed_agent_data = Table(
     Column("latitude", Float),
     Column("longitude", Float),
     Column("timestamp", DateTime),
+    Column("visible", Boolean),
 )
 
 # WebSocket subscriptions
@@ -46,7 +48,9 @@ async def websocket_endpoint(websocket: WebSocket, user_id: int):
     try:
         # send already available data
         r = processed_agent_data.select().order_by(processed_agent_data.c.timestamp)
-        stored_data = SessionLocal().execute(r).fetchall()
+        session = SessionLocal()
+        stored_data = session.execute(r).fetchall()
+        session.close() 
 
         jsonable_data = [{c.name: getattr(i, c.name) for c in processed_agent_data.columns} for i in stored_data]
         for i in jsonable_data:
@@ -57,7 +61,24 @@ async def websocket_endpoint(websocket: WebSocket, user_id: int):
 
         # receive forever
         while True:
-            await websocket.receive_text()
+            data = await websocket.receive_text()
+            try:
+                if (data):
+                    ws_data = WebSocketData.model_validate(json.loads(data))
+                    session = SessionLocal()
+                    update_query = (
+                        processed_agent_data.update()
+                        .where(processed_agent_data.c.id == ws_data.id)
+                        .values(visible=False)
+                    ).returning(processed_agent_data)
+                    res = session.execute(update_query).fetchone()
+                    if (not res):
+                        session.rollback()
+                        raise Exception("Error while websocket PUT")
+                    session.commit()
+            finally:
+                session.close()                  
+         
     except WebSocketDisconnect:
         subscriptions.remove(websocket)
 
@@ -81,6 +102,7 @@ def ProcessedAgentData_to_td(data: List[ProcessedAgentData]):
                 "latitude": item.agent_data.gps.latitude,
                 "longitude": item.agent_data.gps.longitude,
                 "timestamp": item.agent_data.timestamp,
+                "visible": True,
             }
             for item in data
         ]
@@ -178,9 +200,13 @@ def update_processed_agent_data(processed_agent_data_id: int, data: ProcessedAge
         session.commit()
 
         updated_result = session.execute(query).fetchone()
-
         return ProcessedAgentDataInDB(**updated_result._mapping)
         
+    except Exception as err:
+        session.rollback()
+        print(f"Database error: {err}")
+        raise HTTPException(status_code=500, detail="Internal Server Error")
+
     finally:
         session.close()
 
@@ -211,6 +237,11 @@ def delete_processed_agent_data(processed_agent_data_id: int):
 
         return ProcessedAgentDataInDB(**result._mapping)
         
+    except Exception as err:
+        session.rollback()
+        print(f"Database error: {err}")
+        raise HTTPException(status_code=500, detail="Internal Server Error")
+        
     finally:
         session.close()
 

store/schemas.py

@@ -49,3 +49,7 @@ class AgentData(BaseModel):
 class ProcessedAgentData(BaseModel):
     road_state: str
     agent_data: AgentData
+    
+class WebSocketData(BaseModel):
+    id: int
+

store/test_db.py

@@ -0,0 +1,45 @@
+from fastapi.testclient import TestClient
+from main import app, processed_agent_data
+from database import SessionLocal
+from sqlalchemy import select
+from datetime import datetime
+
+client = TestClient(app)
+
+
+def test_create_processed_agent_data():
+    db = SessionLocal()
+    before = db.execute(select(processed_agent_data)).fetchall()
+    before_count = len(before)
+    payload = {
+        "user_id": 123,
+        "data": [
+            {
+                "road_state": "good",
+                "agent_data": {
+                    "user_id": 999, 
+                    "accelerometer": {
+                        "x": 1.1,
+                        "y": 2.2,
+                        "z": 3.3
+                    },
+                    "gps": {
+                        "latitude": 50.45,
+                        "longitude": 30.52
+                    },
+                    "timestamp": datetime.now().isoformat()
+                }
+            }
+        ]
+    }
+    
+    response = client.post("/processed_agent_data/", json=payload)
+
+    assert response.status_code == 200
+
+    after = db.execute(select(processed_agent_data)).fetchall()
+    after_count = len(after)
+
+    assert after_count == before_count + 1
+
+    db.close()