Place editing

mo/db.py

@@ -5,9 +5,10 @@ from enum import Enum as PythonEnum, auto
 from sqlalchemy import \
     Boolean, Column, DateTime, Enum, ForeignKey, Integer, String, Text, UniqueConstraint, \
     text, \
-    create_engine
+    create_engine, inspect
 from sqlalchemy.engine import Engine
-from sqlalchemy.orm import relationship, sessionmaker, Session
+from sqlalchemy.orm import relationship, sessionmaker, Session, class_mapper
+from sqlalchemy.orm.attributes import get_history
 from sqlalchemy.dialects.postgresql import JSONB
 from sqlalchemy.ext.declarative import declarative_base
 from typing import Optional, List
@@ -28,11 +29,24 @@ Base = declarative_base()
 metadata = Base.metadata
 
 
-class PlaceType(PythonEnum):
+class PlaceType(str, PythonEnum):
     region = auto()
     school = auto()
     site = auto()
 
+    @classmethod
+    def choices(enum):
+        return [('region', 'Region'), ('school', 'Škola'), ('site', 'Soutěžní místo')]
+
+    @classmethod
+    def coerce(enum, name):
+        if isinstance(name, enum):
+            return name
+        try:
+            return enum[name]
+        except KeyError:
+            raise ValueError(name)
+
 
 place_level_names = ['stát', 'kraj', 'okres', 'obec', 'škola']
 
@@ -62,16 +76,18 @@ class Place(Base):
             return "region"
 
 
-class School(Place):
+class School(Base):
     __tablename__ = 'schools'
 
-    place_id = Column(ForeignKey('places.place_id', ondelete='CASCADE'), primary_key=True)
+    place_id = Column(Integer, ForeignKey('places.place_id', ondelete='CASCADE'), primary_key=True)
     red_izo = Column(String(255), server_default=text("NULL::character varying"))
     official_name = Column(String(255), server_default=text("NULL::character varying"))
     address = Column(String(255), server_default=text("NULL::character varying"))
     is_zs = Column(Boolean, nullable=False, server_default=text("false"))
     is_ss = Column(Boolean, nullable=False, server_default=text("false"))
 
+    place = relationship('Place')
+
 
 class Round(Base):
     __tablename__ = 'rounds'
@@ -106,12 +122,25 @@ class User(Base):
 
     roles = relationship('UserRole', primaryjoin='UserRole.user_id == User.user_id')
 
+class Contest(Base):
+    __tablename__ = 'contests'
+    __table_args__ = (
+        UniqueConstraint('round', 'region'),
+    )
+
+    contest_id = Column(Integer, primary_key=True, server_default=text("nextval('contests_contest_id_seq'::regclass)"))
+    round = Column(Integer, ForeignKey('rounds.round_id'), nullable=False)
+    region = Column(Integer, ForeignKey('places.place_id'), nullable=False)
+
+    region_object = relationship('Place')
+    round_object = relationship('Round')
 
-class LogType(PythonEnum):
+class LogType(str, PythonEnum):
     general = auto()
     user = auto()
     place = auto()
     round = auto()
+    contest = auto()
     participant = auto()
     task = auto()
     user_role = auto()
@@ -143,7 +172,7 @@ class Participant(Base):
     user = relationship('User')
 
 
-class PartState(PythonEnum):
+class PartState(str, PythonEnum):
     invited = auto()
     refused = auto()
     present = auto()
@@ -154,14 +183,12 @@ class Participation(Base):
     __tablename__ = 'participations'
 
     user_id = Column(Integer, ForeignKey('users.user_id'), primary_key=True, nullable=False)
-    round_id = Column(Integer, ForeignKey('rounds.round_id'), primary_key=True, nullable=False)
-    region = Column(Integer, ForeignKey('places.place_id'), nullable=False)
-    place = Column(Integer, ForeignKey('places.place_id'), nullable=False)
+    contest_id = Column(Integer, ForeignKey('contests.contest_id'), primary_key=True, nullable=False)
+    place_id = Column(Integer, ForeignKey('places.place_id'), nullable=False)
     state = Column(Enum(PartState, name='part_state'), nullable=False)
 
-    place_object = relationship('Place', primaryjoin='Participation.place == Place.place_id')
-    region_object = relationship('Place', primaryjoin='Participation.region == Place.place_id')
-    round = relationship('Round')
+    contest = relationship('Contest', primaryjoin='Participation.contest_id == Contest.contest_id')
+    place = relationship('Place', primaryjoin='Participation.place_id == Place.place_id')
     user = relationship('User')
 
 
@@ -179,7 +206,7 @@ class Task(Base):
     round = relationship('Round')
 
 
-class RoleType(PythonEnum):
+class RoleType(str, PythonEnum):
     garant = auto()
     garant_kraj = auto()
     garant_okres = auto()
@@ -195,17 +222,17 @@ class UserRole(Base):
     place_id = Column(Integer, ForeignKey('places.place_id'), nullable=False)
     role = Column(Enum(RoleType, name='role_type'), nullable=False)
     category = Column(String(2), server_default=text("NULL::character varying"))
-    round_id = Column(Integer, ForeignKey('rounds.round_id'))
+    year = Column(Integer)
+    seq = Column(Integer)
     assigned_by = Column(Integer, ForeignKey('users.user_id'))
     assigned_at = Column(DateTime(True))
 
     user = relationship('User', primaryjoin='UserRole.user_id == User.user_id')
     assigned_by_user = relationship('User', primaryjoin='UserRole.assigned_by == User.user_id')
-    place_object = relationship('Place')
-    round = relationship('Round')
+    place = relationship('Place')
 
 
-class PaperType(PythonEnum):
+class PaperType(str, PythonEnum):
     solution = auto()
     feedback = auto()
 
@@ -289,3 +316,38 @@ def get_place_parents(place: Place) -> List[Place]:
     recq = topq.union(botq)
 
     return sess.query(recq).all()
+
+
+def get_object_changes(obj):
+    """ Given a model instance, returns dict of pending
+    changes waiting for database flush/commit.
+
+    e.g. {
+        'some_field': {
+            'before': *SOME-VALUE*,
+            'after': *SOME-VALUE*
+        },
+        ...
+    }
+
+    Source: https://stackoverflow.com/questions/15952733/sqlalchemy-logging-of-changes-with-date-and-user
+    """
+    inspection = inspect(obj)
+    changes = {}
+    for attr in class_mapper(obj.__class__).column_attrs:
+        if getattr(inspection.attrs, attr.key).history.has_changes():
+            if get_history(obj, attr.key)[2]:
+                before = get_history(obj, attr.key)[2].pop()
+                after = getattr(obj, attr.key)
+                if before != after:
+                    if before or after:
+                        changes[attr.key] = {'before': before, 'after': after}
+    return changes
+
+
+def row2dict(row):
+    d = {}
+    for column in row.__table__.columns:
+        d[column.name] = getattr(row, column.name)
+
+    return d