class Team(db.Model): __tablename__ = 'teams' id = create_id_column('team') name = create_name_column('team') tag = db.Column('tag', db.String(255), nullable=False) url = db.Column('url', db.String(255), nullable=True) join_password = db.Column('join_password', db.String(255), nullable=False) time_zone = db.Column('time_zone', db.String(255), nullable=False, server_default='US/Eastern') date_created = db.Column('date_created', db.DateTime, nullable=False) players = db.relation('TeamPlayer', order_by='TeamPlayer.status.asc()', lazy='dynamic', cascade="delete") matches = db.relation(Match, primaryjoin=Match.team_id == id, cascade="delete") opponents = db.relation('Opponent', lazy='dynamic', cascade='delete') servers = db.relation('Server', cascade='delete') posts = db.relation("ForumBotQueuedPost", cascade="delete") forum_bots = db.relation("ForumBot", cascade="delete") results = db.relation("CompletedMatch", cascade="delete", lazy="dynamic")
class MatchPlayer(db.Model): __tablename__ = 'match_players' match_id = db.Column('match_id', db.Integer(unsigned=True), \ db.ForeignKey('matches.match_id'), nullable=False, primary_key=True) user_id = db.Column('user_id', db.Integer(unsigned=True), \ db.ForeignKey('users.user_id'), nullable=False, primary_key=True) status = db.Column('status', db.Integer(unsigned=True), nullable=False) date_updated = db.Column('date_updated', db.DateTime, nullable=False) #lineup_slot = db.Column('lineup_slot', db.Integer(unsigned=True), # nullable=False, server_default='0') user = db.relation('User') StatusAvailable = 10 StatusMaybe = 20 StatusUnavailable = 30 StatusPrettyNames = { StatusAvailable: "Available", StatusMaybe: "Maybe", StatusUnavailable: "Unavailable" } StatusChoices = [(StatusAvailable, "Available"), (StatusMaybe, "Maybe"), (StatusUnavailable, "Unavailable")] def __init__(self, match_id=0, user_id=0, status=StatusAvailable): self.match_id = match_id self.user_id = user_id self.status = status @property def pretty_status(self): return self.__class__.StatusPrettyNames.get(self.status, "")
class Competition(db.Model): __tablename__ = 'competitions' id = create_id_column('competition') name = create_name_column('competition') abbr = db.Column('abbreviation', db.String(255), nullable=False) url = db.Column('url', db.String(255), nullable=False) game_id = db.Column('game_id', id_type, db.ForeignKey('games.game_id'), \ nullable=False) game = db.relation("Game")
class MatchMap(db.Model): __tablename__ = 'match_maps' match_id = db.Column('match_id', id_type, \ db.ForeignKey('matches.match_id'), nullable=False, primary_key=True) order = db.Column('order', db.Integer(unsigned=True), nullable=False, \ primary_key=True, autoincrement=False) map_id = db.Column('map_id', id_type, db.ForeignKey('maps.map_id'), \ nullable=False) side_id = db.Column('side_id', id_type, db.ForeignKey('sides.side_id'), \ nullable=False) gametype_id = db.Column('gametype_id', id_type, \ db.ForeignKey('gametypes.gametype_id'), nullable=False)
class Opponent(db.Model): __tablename__ = 'opponents' id = create_id_column('opponent') name = create_name_column('opponent',unique=False) tag = db.Column('tag', db.String(255), nullable=False) team_id = db.Column('team_id', db.Integer(unsigned=True), db.ForeignKey('teams.team_id'), index=True, nullable=False) matches = db.relation(Match, primaryjoin=Match.opponent_id == id, cascade="delete") team = db.relation("Team", primaryjoin="Team.id == Opponent.team_id") __table_args__ = ( db.UniqueConstraint("team_id", "opponent_name"), {} )
class CompletedMatchRound(db.Model): __tablename__ = 'completed_match_rounds' cmatch_id = db.Column('cmatch_id', id_type, db.ForeignKey('completed_matches.cmatch_id'), nullable=False, primary_key=True, autoincrement=False) round_id = db.Column('round_id', db.Integer(unsigned=True), nullable=False, primary_key=True, autoincrement=False) map_id = db.Column('map_id', id_type, db.ForeignKey('maps.map_id'), \ nullable=False) side_id = db.Column('side_id', id_type, db.ForeignKey('sides.side_id'), \ nullable=False) gametype_id = db.Column('gametype_id', id_type, \ db.ForeignKey('gametypes.gametype_id'), nullable=False) wins = db.Column('wins', db.Integer(), nullable=False) losses = db.Column('losses', db.Integer(), nullable=False) draws = db.Column('draws', db.Integer(), nullable=False) map = db.relation(Map) side = db.relation(Side) gametype = db.relation(GameType) players = db.relation("CompletedMatchPlayer", cascade="delete,delete-orphan,save-update,merge")
class CompletedMatchPlayer(db.Model): __tablename__ = 'completed_match_players' cmatch_id = db.Column('cmatch_id', db.Integer(unsigned=True), \ nullable=False, primary_key=True) round_id = db.Column('round_id', db.Integer(unsigned=True), primary_key=True) user_id = db.Column('user_id', db.Integer(unsigned=True), \ db.ForeignKey('users.user_id'), nullable=False, primary_key=True) __table_args__ = (db.ForeignKeyConstraint(['cmatch_id', 'round_id'], [ 'completed_match_rounds.cmatch_id', 'completed_match_rounds.round_id' ]), {}) kills = db.Column('kills', db.Integer(unsigned=True), nullable=False) deaths = db.Column('deaths', db.Integer(unsigned=True), nullable=False) off_objs = db.Column('offensive_objectives', db.Integer(unsigned=True), nullable=False) def_objs = db.Column('defensive_objectives', db.Integer(unsigned=True), nullable=False) score = db.Column('score', db.Integer(unsigned=False), nullable=False) user = db.relation("User")
class Server(db.Model): __tablename__ = 'servers' id = create_id_column('server') name = create_name_column('server', unique=False) address = db.Column('address', db.String(255), nullable=False) team_id = db.Column('team_id', db.Integer(unsigned=True), db.ForeignKey('teams.team_id'), index=True, nullable=False) matches = db.relation(Match, primaryjoin=Match.server_id == id, cascade="delete") team = db.relation("Team", primaryjoin="Team.id == Server.team_id") __table_args__ = ( db.UniqueConstraint("team_id", "server_name"), {} )
class Game(db.Model): __tablename__ = 'games' id = create_id_column('game') name = create_name_column('game') abbr = db.Column('abbreviation', db.String(255), nullable=False, unique=True) thumbnail_url = db.Column('thumbnail_column', db.String(255), nullable=True) server_copy_format = db.Column('server_copy_format', db.String(255), nullable=True) stats_columns = db.Column('stats_columns', db.String(255), nullable=True) def uses_stats_column(self, column): cols = self.stats_columns.split(',') return column in cols
class TeamPlayer(db.Model): __tablename__ = 'team_players' team_id = db.Column('team_id', id_type, db.ForeignKey('teams.team_id'), nullable=False, primary_key=True) user_id = db.Column('user_id', id_type, db.ForeignKey('users.user_id'), nullable=False, primary_key=True) user = db.relation(User) team = db.relation(Team, order_by=Team.name.asc()) date_joined = db.Column('date_joined', db.DateTime, nullable=False) StatusFounder = 10 StatusLeader = 20 StatusNormal = 30 status = db.Column('status', db.Integer, nullable=False, server_default='0') StatusPrettyNames = { StatusNormal: "Normal", StatusFounder: "Founder", StatusLeader: "Leader" } StatusChoices = [ (StatusNormal, "Normal"), (StatusFounder, "Founder"), (StatusLeader, "Leader") ] def __init__(self, **kwargs): kwargs.setdefault('team_id', 0) kwargs.setdefault('user_id', 0) kwargs.setdefault('status', self.StatusNormal) kwargs.setdefault('date_joined', datetime.utcnow()) self.date_joined = kwargs['date_joined'] self.team_id = kwargs['team_id'] self.user_id = kwargs['user_id'] self.status = kwargs['status'] @property def pretty_status(self): return self.__class__.StatusPrettyNames.get(self.status, "") def is_team_leader(self): return self.status in (self.StatusLeader, self.StatusFounder) def is_founder(self): return self.status == self.StatusFounder
class ForumBot(db.Model): __tablename__ = 'forum_bots' id = create_id_column('forum_bot') type = db.Column('type', db.Integer(unsigned=True), nullable=False) url = db.Column('url', db.String(255), nullable=False) forum_id = db.Column('forum_id', db.Integer(unsigned=True), nullable=True) user_name = db.Column('user_name', db.String(255), nullable=True) password = db.Column('password', db.String(255), nullable=True) team_id = db.Column('team_id', db.Integer(unsigned=True), db.ForeignKey('teams.team_id'), index=True, nullable=False) game_id = db.Column('game_id', id_type, db.ForeignKey('games.game_id'), \ index=True, nullable=True) TypeVBulletin3_7 = 1 TypePrettyNames = { TypeVBulletin3_7 : "vBulletin 3.7" } TypeChoices = [ (TypeVBulletin3_7, "vBulletin 3.7") ] team = db.relation("Team", primaryjoin="Team.id == ForumBot.team_id") game = db.relation("Game", primaryjoin="Game.id == ForumBot.game_id") @property def pretty_type(self): return self.__class__.TypePrettyNames.get(self.type, "")
class ForumBotQueuedPost(db.Model): __tablename__ = 'forum_bot_queued_posts' id = create_id_column('forum_bot_queued_post') date_created = db.Column('date_created', db.DateTime, nullable=False, default=datetime.utcnow()) team_id = db.Column('team_id', db.Integer(unsigned=True), db.ForeignKey('teams.team_id'), index=True, nullable=False) game_id = db.Column('game_id', db.Integer(unsigned=True), db.ForeignKey('games.game_id'), nullable=False) match_id = db.Column('match_id', id_type, \ db.ForeignKey('matches.match_id'), nullable=False) subject = db.Column('subject', db.String(255), nullable=False) message = db.Column('message', db.Text, nullable=False) match = db.relation('Match', primaryjoin="Match.id == ForumBotQueuedPost.match_id") team = db.relation(Team, primaryjoin="Team.id == ForumBotQueuedPost.team_id")
class Match(db.Model): __tablename__ = 'matches' id = db.Column('match_id', db.Integer(unsigned=True), primary_key=True, autoincrement=True, nullable=False) team_id = db.Column('team_id', db.Integer(unsigned=True), db.ForeignKey('teams.team_id'), index=True, nullable=False) opponent_id = db.Column('opponent_id', db.Integer(unsigned=True), db.ForeignKey('opponents.opponent_id'), index=True, nullable=True) competition_id = db.Column('competition_id', db.Integer(unsigned=True), db.ForeignKey('competitions.competition_id'), index=True, nullable=False) server_id = db.Column('server_id', db.Integer(unsigned=True), db.ForeignKey('servers.server_id'), index=True, nullable=False) size = db.Column('size', db.Integer(unsigned=True), nullable=False) date = db.Column('date', db.DateTime, nullable=False) password = db.Column('password', db.String(255), nullable=False) comments = db.Column('comments', db.String(255), nullable=False) date_created = db.Column('date_created', db.DateTime, nullable=False) creator_user_id = db.Column('creator_user_id', id_type, db.ForeignKey('users.user_id'), nullable=False) forum_post_url = db.Column('forum_post_url', db.String(255), nullable=True) user = db.relation("User") team = db.relation("Team", primaryjoin="Team.id == Match.team_id") opponent = db.relation("Opponent", primaryjoin="Opponent.id == Match.opponent_id") competition = db.relation("Competition") server = db.relation("Server") players = db.relation( "MatchPlayer", order_by="MatchPlayer.status.asc(), MatchPlayer.date_updated.asc()", cascade="delete", lazy='dynamic') posts = db.relation("ForumBotQueuedPost", cascade="delete") def __init__(self, **kwargs): kwargs.setdefault('team_id', None) kwargs.setdefault('opponent_id', None) kwargs.setdefault('competition_id', None) kwargs.setdefault('server_id', None) kwargs.setdefault('size', 4) kwargs.setdefault('date_created', datetime.utcnow()) kwargs.setdefault('password', '') kwargs.setdefault('comments', '') self.team_id = kwargs['team_id'] self.creator_user_id = kwargs['creator_user_id'] self.opponent_id = kwargs['opponent_id'] self.competition_id = kwargs['competition_id'] self.server_id = kwargs['server_id'] self.size = kwargs['size'] self.date = kwargs['date'] self.password = kwargs['password'] self.comments = kwargs['comments'] self.date_created = kwargs['date_created'] @property def server_copy_text(self): fmt = self.competition.game.server_copy_format if not fmt: return '' fmt = fmt.replace("%ADDR%", self.server.address) fmt = fmt.replace("%PW%", self.password) return fmt def historical_records(self): opponent_rec = db.session.query(\ db.func.sum(\ db.cast(CompletedMatch.wins >\ CompletedMatch.losses, db.INT)), \ db.func.sum(\ db.cast(CompletedMatch.wins < \ CompletedMatch.losses, db.INT)), \ db.func.sum(\ db.cast(CompletedMatch.wins == \ CompletedMatch.losses, db.INT))\ ).\ filter(CompletedMatch.team_id == self.team_id).\ filter(CompletedMatch.opponent_id == self.opponent_id).\ first() competition_rec = db.session.query(\ db.func.sum(\ db.cast(CompletedMatch.wins >\ CompletedMatch.losses, db.INT)), \ db.func.sum(\ db.cast(CompletedMatch.wins < \ CompletedMatch.losses, db.INT)), \ db.func.sum(\ db.cast(CompletedMatch.wins == \ CompletedMatch.losses, db.INT))\ ).\ filter(CompletedMatch.team_id == self.team_id).\ filter(CompletedMatch.competition_id == self.competition_id).\ first() server_rec = db.session.query(\ db.func.sum(\ db.cast(CompletedMatch.wins >\ CompletedMatch.losses, db.INT)), \ db.func.sum(\ db.cast(CompletedMatch.wins < \ CompletedMatch.losses, db.INT)), \ db.func.sum(\ db.cast(CompletedMatch.wins == \ CompletedMatch.losses, db.INT))\ ).\ filter(CompletedMatch.team_id == self.team_id).\ filter(CompletedMatch.server_id == self.server_id).\ first() opp_comp_rec = db.session.query(\ db.func.sum(\ db.cast(CompletedMatch.wins >\ CompletedMatch.losses, db.INT)), \ db.func.sum(\ db.cast(CompletedMatch.wins < \ CompletedMatch.losses, db.INT)), \ db.func.sum(\ db.cast(CompletedMatch.wins == \ CompletedMatch.losses, db.INT))\ ).\ filter(CompletedMatch.team_id == self.team_id).\ filter(CompletedMatch.opponent_id == self.opponent_id).\ filter(CompletedMatch.competition_id == self.competition_id).\ first() def zero(rec): if rec == (None, None, None): return (0, 0, 0) return rec opponent_rec = zero(opponent_rec) competition_rec = zero(competition_rec) server_rec = zero(server_rec) opp_comp_rec = zero(opp_comp_rec) return (opponent_rec, competition_rec, server_rec, opp_comp_rec)
def create_name_column(name,unique=True): return db.Column(name+'_name', db.String(255), nullable=False, unique=unique)
def create_id_column(name): return db.Column(name + '_id', id_type, primary_key=True, \ autoincrement=True, nullable=False)
class Side(db.Model): __tablename__ = 'sides' id = create_id_column('side') game_id = db.Column('game_id', id_type, db.ForeignKey('games.game_id'), \ nullable=False) name = create_name_column('side')
class User(db.Model): __tablename__ = 'users' TimeZoneChoices = [(tz, unicode(tz)) for tz in common_timezones] id = db.Column('user_id', db.Integer(unsigned=True), primary_key=True, \ autoincrement=True, nullable=False) openid = db.Column('openid', db.String(255), unique=True, nullable=False) name = db.Column('username', db.String(255), unique=True, nullable=False) email = db.Column('email', db.String(255), unique=True, nullable=False) time_zone = db.Column('time_zone', db.String(255), nullable=False, server_default='US/Eastern') email_settings = db.Column('email_settings', db.Integer(unsigned=True), nullable=False, server_default='0') date_joined = db.Column('date_joined', db.DateTime, nullable=False) team_statuses = db.relation("TeamPlayer", cascade="delete") # email settings EmailNone = 1 EmailMatchAdded = 2 EmailSettings = (EmailNone, EmailMatchAdded) EmailSettingsChoices = [(EmailNone, "No emails"), (EmailMatchAdded, "Send an email when a new match is added")] def __init__(self, *args, **kwargs): kwargs.setdefault('id', 0) kwargs.setdefault('name', 'guest') kwargs.setdefault('date_joined', datetime.utcnow()) kwargs.setdefault('time_zone', "US/Eastern") kwargs.setdefault('email_settings', self.EmailNone) kwargs.setdefault('team_statuses', []) self.date_joined = kwargs.get('date_joined') self.id = kwargs.get('id') self.name = kwargs.get('name') self.email = kwargs.get('email') self.openid = kwargs.get('openid') self.time_zone = kwargs.get('time_zone') self.email_settings = kwargs.get('email_settings') self.team_statuses = kwargs.get('team_statuses') @property def is_guest(self): return self.id == 0 @property def founder_teams(self): ft = [] for t in self.team_statuses: if t.is_founder(): ft.append(t.team_id) return ft @property def team_leader_teams(self): return [t.team_id for t in self.team_statuses if t.is_team_leader()] @property def teams(self): tdict = {} for t in self.team_statuses: tdict[t.team_id] = t.team return tdict @property def one_team(self): if len(self.teams): return self.teams.values()[0] return None def is_on_team(self, team_id=None): if team_id is None: return len(self.team_statuses) ids = [t.team_id for t in self.team_statuses] return team_id in ids def is_founder(self, team_id=None): if team_id is None: return len(self.founder_teams) > 0 return team_id in self.founder_teams def is_team_leader(self, team_id=None): if team_id is None: return self.is_admin() return team_id in self.team_leader_teams def is_admin(self): return len(self.team_leader_teams) > 0 def can_edit_team(self, team_id=None): return self.is_team_leader(team_id) def can_edit_match(self, team_id=None): return self.is_team_leader(team_id) @classmethod def create_guest_user(cls): return cls()
class CompletedMatch(db.Model): __tablename__ = 'completed_matches' id = create_id_column('cmatch') team_id = db.Column('team_id', db.Integer(unsigned=True), db.ForeignKey('teams.team_id'), index=True, nullable=False) opponent_id = db.Column('opponent_id', db.Integer(unsigned=True), db.ForeignKey('opponents.opponent_id'), index=True, nullable=True) match_id = db.Column('match_id', db.Integer(unsigned=True), db.ForeignKey('matches.match_id'), index=True, nullable=True) competition_id = db.Column('competition_id', db.Integer(unsigned=True), db.ForeignKey('competitions.competition_id'), index=True, nullable=False) server_id = db.Column('server_id', db.Integer(unsigned=True), db.ForeignKey('servers.server_id'), index=True, nullable=False) comments = db.Column('comments', db.String(255), nullable=False) date_played = db.Column('date_played', db.DateTime, nullable=False, index=True, default=datetime.utcnow()) date_created = db.Column('date_created', db.DateTime, nullable=False, default=datetime.utcnow()) creator_user_id = db.Column('creator_user_id', id_type, db.ForeignKey('users.user_id'), nullable=False) final_result_method = db.Column('final_result_method', db.Integer(), nullable=False) wins = db.Column('wins', db.Integer(), nullable=False) losses = db.Column('losses', db.Integer(), nullable=False) draws = db.Column('draws', db.Integer(), nullable=False) FinalResultByScore = 1 FinalResultByRound = 2 FinalResultByForfeit = 3 FinalResultChoices = [(FinalResultByScore, "By Score"), (FinalResultByRound, "By Round")] user = db.relation("User") team = db.relation("Team", primaryjoin="Team.id == CompletedMatch.team_id") opponent = db.relation( "Opponent", primaryjoin="Opponent.id == CompletedMatch.opponent_id") competition = db.relation("Competition") server = db.relation("Server") avail_match = db.relation("Match", backref="results") rounds = db.relation("CompletedMatchRound", cascade="delete,delete-orphan,save-update,merge", lazy='dynamic') @property def is_forfeit(self): return self.final_result_method == self.__class__.FinalResultByForfeit