from cached_property import cached_property from sqlalchemy import Column, ForeignKey, MetaData, Table, func, join, literal from sqlalchemy.ext.declarative import declarative_base from sqlalchemy.orm import relationship from sqlalchemy.types import Float, Integer, String from .db import get_session Base = declarative_base() session = get_session() class Team(Base): __table__ = Table('teams', MetaData(bind=session.bind), autoload=True) def __repr__(self): return self.shortname.encode('utf8') class Player(Base): __table__ = Table('players', MetaData(bind=session.bind), Column('id', Integer, primary_key=True), Column('team', Integer, ForeignKey(Team.id)), autoload=True) team_ = relationship(Team, uselist=False) def __repr__(self): return ('%s %s' % (self.gname, self.sname)).encode('utf8') class AusButler(Base): __tablename__ = 'aus_butler' id = Column(Integer, primary_key=True) match = Column(Integer, primary_key=True) segment = Column(Integer, primary_key=True) score = Column(Float) cut_score = Column(Float) opp_score = Column(Float) corrected_score = Column(Float) board_count = Column(Integer) player = relationship('Player', uselist=False, foreign_keys=[id], primaryjoin='AusButler.id == Player.id') @cached_property def table(self): for table in session.query(Segment).filter( (Segment.rnd == self.match) & (Segment.segment == self.segment) ).all(): if self.id in [ table.openN, table.openS, table.openW, table.openE, table.closeN, table.closeS, table.closeW, table.closeE]: return table return None def __repr__(self): return '[%d] %d-%d: %.2f-%.2f=%.2f' % (self.id, self.match, self.segment, self.score or 0.0, self.opp_score or 0.0, self.corrected_score or 0.0) class Butler(Base): __table__ = Table('butler', MetaData(bind=session.bind), Column('id', Integer, primary_key=True), autoload=True) class Score(Base): __table__ = Table('scores', MetaData(bind=session.bind), Column('rnd', Integer, primary_key=True), Column('segment', Integer, primary_key=True), Column('tabl', Integer, primary_key=True), Column('room', Integer, primary_key=True), Column('board', Integer, primary_key=True), autoload=True) class Segment(Base): __table__ = Table('segments', MetaData(bind=session.bind), Column('rnd', Integer, primary_key=True), Column('segment', Integer, primary_key=True), Column('tabl', Integer, primary_key=True), autoload=True) count_cache = { (b.rnd, b.segment, b.tabl): { 'open': int(b.open), 'closed': int(b.closed) } for b in session.query( Score.rnd, Score.segment, Score.tabl, func.sum(Score.butler * (Score.room == 1)).label('open'), func.sum(Score.butler * (Score.room == 2)).label('closed') ).group_by(Score.rnd, Score.segment, Score.tabl).all() } @cached_property def butler_count(self): return Segment.count_cache[(self.rnd, self.segment, self.tabl)] class Translation(Base): __table__ = Table('logoh', MetaData(bind=session.bind), Column('id', Integer, primary_key=True), autoload=True) class Admin(Base): __table__ = Table('admin', MetaData(bind=session.bind), Column('shortname', String, primary_key=True), autoload=True) class Params(Base): __table__ = Table('params', MetaData(bind=session.bind), Column('datasource', Integer, primary_key=True), autoload=True) class Parameters(Base): __table__ = join(Admin, Params, literal(True))