65 lines
2.5 KiB
Python
65 lines
2.5 KiB
Python
from sqlalchemy import create_engine, Column, Integer, String, DateTime, Float, ForeignKey
|
|
from sqlalchemy.ext.declarative import declarative_base
|
|
from sqlalchemy.orm import sessionmaker, relationship
|
|
from geoalchemy2 import Geometry
|
|
import datetime
|
|
import os
|
|
|
|
DATABASE_URL = os.getenv("DATABASE_URL", "postgresql://user:password@db:5432/memories_db")
|
|
|
|
engine = create_engine(DATABASE_URL)
|
|
SessionLocal = sessionmaker(autocommit=False, autoflush=False, bind=engine)
|
|
Base = declarative_base()
|
|
|
|
class Location(Base):
|
|
__tablename__ = "locations"
|
|
|
|
id = Column(Integer, primary_key=True, index=True)
|
|
latitude = Column(Float, nullable=False)
|
|
longitude = Column(Float, nullable=False)
|
|
timestamp = Column(DateTime, default=datetime.datetime.utcnow, nullable=False)
|
|
geom = Column(Geometry(geometry_type='POINT', srid=4326), nullable=False)
|
|
|
|
# Optional: Link to a user if multi-user is implemented
|
|
# user_id = Column(Integer, ForeignKey("users.id"))
|
|
# user = relationship("User")
|
|
|
|
def __repr__(self):
|
|
return f"<Location(id={self.id}, lat={self.latitude}, lon={self.longitude}, time={self.timestamp})>"
|
|
|
|
class Visit(Base):
|
|
__tablename__ = "visits"
|
|
|
|
id = Column(Integer, primary_key=True, index=True)
|
|
start_time = Column(DateTime, nullable=False)
|
|
end_time = Column(DateTime, nullable=False)
|
|
# A visit can be represented by a polygon or a centroid, for simplicity we'll use a point for now
|
|
centroid_geom = Column(Geometry(geometry_type='POINT', srid=4326), nullable=True)
|
|
|
|
# Optional: Link to a user
|
|
# user_id = Column(Integer, ForeignKey("users.id"))
|
|
# user = relationship("User")
|
|
|
|
def __repr__(self):
|
|
return f"<Visit(id={self.id}, start={self.start_time}, end={self.end_time})>"
|
|
|
|
class Trip(Base):
|
|
__tablename__ = "trips"
|
|
|
|
id = Column(Integer, primary_key=True, index=True)
|
|
start_time = Column(DateTime, nullable=False)
|
|
end_time = Column(DateTime, nullable=False)
|
|
transport_mode = Column(String, default="unknown")
|
|
# A trip can be represented by a LineString
|
|
path_geom = Column(Geometry(geometry_type='LINESTRING', srid=4326), nullable=True)
|
|
|
|
# Optional: Link to a user
|
|
# user_id = Column(Integer, ForeignKey("users.id"))
|
|
# user = relationship("User")
|
|
|
|
def __repr__(self):
|
|
return f"<Trip(id={self.id}, mode={self.transport_mode}, start={self.start_time}, end={self.end_time})>"
|
|
|
|
# Function to create all tables
|
|
def create_db_tables():
|
|
Base.metadata.create_all(bind=engine) |