89 lines
3.4 KiB
Python
89 lines
3.4 KiB
Python
"""
|
|
models/base.py — declarative base & the Messages model.
|
|
"""
|
|
|
|
from sqlalchemy import create_engine, Integer, String, Text
|
|
from sqlalchemy.orm import DeclarativeBase, Mapped, mapped_column, relationship, sessionmaker
|
|
from typing import Optional
|
|
from sqlalchemy import (
|
|
Column, Integer, String, Text, Boolean, BigInteger,
|
|
ForeignKey, UniqueConstraint
|
|
)
|
|
|
|
class Base(DeclarativeBase):
|
|
pass
|
|
|
|
|
|
class Messages(Base):
|
|
__tablename__ = "messages"
|
|
|
|
id: Mapped[int] = mapped_column(Integer, primary_key=True)
|
|
channel: Mapped[str] = mapped_column(String(32), nullable=False, default="general")
|
|
ip: Mapped[str] = mapped_column(String(64), nullable=False)
|
|
content: Mapped[str] = mapped_column(Text, nullable=False)
|
|
gif_url: Mapped[Optional[str]] = mapped_column(Text, nullable=True, default=None)
|
|
poll_id: Mapped[Optional[int]] = mapped_column(Integer, nullable=True, default=None)
|
|
timestamp: Mapped[int] = mapped_column(Integer, nullable=False)
|
|
|
|
class Poll(Base):
|
|
__tablename__ = "polls"
|
|
|
|
id = Column(Integer, primary_key=True)
|
|
channel = Column(String(32), nullable=False, default="general")
|
|
creator_ip = Column(String(64), nullable=False)
|
|
question = Column(Text, nullable=False) # encrypted
|
|
created_at = Column(BigInteger, nullable=False)
|
|
expires_at = Column(BigInteger, nullable=True)
|
|
is_multiple = Column(Boolean, default=False)
|
|
is_anonymous = Column(Boolean, default=False)
|
|
status = Column(String(16), default="active") # active | closed | deleted
|
|
|
|
options = relationship("PollOption", back_populates="poll",
|
|
cascade="all, delete-orphan",
|
|
order_by="PollOption.position")
|
|
votes = relationship("PollVote", back_populates="poll",
|
|
cascade="all, delete-orphan")
|
|
|
|
|
|
class PollOption(Base):
|
|
__tablename__ = "poll_options"
|
|
|
|
id = Column(Integer, primary_key=True)
|
|
poll_id = Column(Integer, ForeignKey("polls.id"), nullable=False)
|
|
label = Column(Text, nullable=False) # encrypted
|
|
position = Column(Integer, default=0)
|
|
|
|
poll = relationship("Poll", back_populates="options")
|
|
votes = relationship("PollVote", back_populates="option",
|
|
cascade="all, delete-orphan")
|
|
|
|
|
|
class PollVote(Base):
|
|
__tablename__ = "poll_votes"
|
|
__table_args__ = (
|
|
UniqueConstraint("poll_id", "option_id", "voter_ip",
|
|
name="uq_poll_voter_option"),
|
|
)
|
|
|
|
id = Column(Integer, primary_key=True)
|
|
poll_id = Column(Integer, ForeignKey("polls.id"), nullable=False)
|
|
option_id = Column(Integer, ForeignKey("poll_options.id"), nullable=False)
|
|
voter_ip = Column(String(64), nullable=False) # IP-hash
|
|
voted_at = Column(BigInteger, nullable=False)
|
|
|
|
poll = relationship("Poll", back_populates="votes")
|
|
option = relationship("PollOption", back_populates="votes")
|
|
|
|
def make_engine(db_cfg):
|
|
"""Create a SQLAlchemy engine from DatabaseConfig."""
|
|
if db_cfg.engine == "mariadb":
|
|
url = (
|
|
f"mysql+pymysql://{db_cfg.mariadb_user}:{db_cfg.mariadb_password}"
|
|
f"@{db_cfg.mariadb_host}:{db_cfg.mariadb_port}/{db_cfg.mariadb_database}"
|
|
)
|
|
else:
|
|
url = f"sqlite:///{db_cfg.sqlite_path}"
|
|
engine = create_engine(url, echo=False)
|
|
Base.metadata.create_all(engine)
|
|
return engine
|