[P] Integrate additional store testing into CI pipeline

Add test for new records in DB

.gitea/workflows/tests.yaml

@@ -28,6 +28,10 @@ jobs:
       - name: Clone repository
         run: git clone --revision ${{ gitea.sha }} --depth 1 ${{ gitea.server_url }}/${{ gitea.repository }}
 
+      - name: Start postgres_db container for testing
+        working-directory: IoT-Systems
+        run: docker-compose up -d postgres_db
+
       - name: Build Store testing container
         working-directory: IoT-Systems
         run: docker build -t local/store/${{gitea.sha}} -f store/Dockerfile-test .
@@ -40,6 +44,11 @@ jobs:
         if: ${{always()}}
         run: docker image rm local/store/${{gitea.sha}}
 
+      - name: Clean up docker-compose
+        if: ${{always()}}
+        working-directory: IoT-Systems
+        run: docker-compose down -v --remove-orphans
+
   integration-smoke-test:
     name: Integration smoke testing
     runs-on: host-arch-x86_64

store/Dockerfile-test

@@ -6,6 +6,7 @@ WORKDIR /app
 COPY store/requirements.txt .
 
 RUN pip install --no-cache-dir -r requirements.txt
+RUN pip install --root-user-action ignore --no-cache-dir pytest httpx
 # Copy the entire application into the container
 COPY store/. .
 # Run the main.py script inside the container when it starts

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-entry.sh

@@ -1,3 +1,4 @@
 #!/bin/sh
 
 PYTHONPATH=$PWD python3 test/main_test.py
+PYTHONPATH=$PWD python3 -m pytest test_db.py

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": "normal",
+                "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()