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