class Mail(BaseModel):
__tablename__ = 'mail'
subject = db.Column(db.Text, nullable=False)
content = db.Column(db.Text, nullable=False)
sender_id = db.Column(db.Integer,
db.ForeignKey('users.id'),
nullable=False)
receiver_id = db.Column(db.Integer,
db.ForeignKey('users.id'),
nullable=False)
read = db.Column(db.Integer, nullable=False, default=0)
def serialize(self):
return {
'id': self.id,
'created': self.created_as_string,
'subject': self.subject,
'content': self.content,
'read': self.read,
'sender': self.sender.serialize(),
'receiver': self.receiver.serialize(),
}
def __repr__(self):
return "<Mail '{}'>".format(self.id)
class Membership(BaseModel):
__tablename__ = 'memberships'
user_id = db.Column(db.Integer, db.ForeignKey('users.id'))
room_id = db.Column(db.Integer, db.ForeignKey('rooms.id'))
level = db.Column(db.Integer, nullable=False, default=1)
user = db.relationship("User",
backref=db.backref('memberships',
lazy='dynamic',
cascade="all, delete-orphan"))
room = db.relationship("Room",
backref=db.backref('memberships',
lazy='dynamic',
cascade="all, delete-orphan"))
__table_args__ = (db.UniqueConstraint('user_id',
'room_id',
name='membership_id'), )
def serialize(self, include_results=False):
return {
'id': self.id,
'created': self.created_as_string,
'level': self.level,
}
def __repr__(self):
return "<Membership '{}'>".format(self.id)
class Message(BaseModel):
__tablename__ = 'messages'
comment = db.Column(db.Text, nullable=False)
user_id = db.Column(db.Integer, db.ForeignKey('users.id'), nullable=False)
room_id = db.Column(db.Integer, db.ForeignKey('rooms.id'), nullable=False)
def serialize(self):
return {
'id': self.id,
'created': self.created_as_string,
'comment': self.comment,
'author': {
'id': self.author.id,
'name': self.author.name,
'username': self.author.username,
'avatar': self.author.avatar_or_default,
},
'room': {
'id': self.room.id,
'name': self.room.name,
},
}
def __repr__(self):
return "<Message '{}'>".format(self.id)
class Bug(BaseModel):
__tablename__ = 'bugs'
title = db.Column(db.String(255), nullable=False)
vuln_id = db.Column(db.Integer, nullable=False, default=0)
severity = db.Column(db.Integer, nullable=False, default=0)
description = db.Column(db.Text, nullable=False)
impact = db.Column(db.Text, nullable=False)
status = db.Column(db.Integer, nullable=False, default=0)
submitter_id = db.Column(db.Integer,
db.ForeignKey('users.id'),
nullable=False)
reviewer_id = db.Column(db.Integer,
db.ForeignKey('users.id'),
nullable=False)
@property
def vulnerability_as_string(self):
return VULNERABILITIES[self.vuln_id][0]
@property
def severity_as_string(self):
return SEVERITY[self.severity]
@property
def status_as_string(self):
return BUG_STATUSES[self.status]
@property
def bounty(self):
return VULNERABILITIES[self.vuln_id][1] * self.severity
@staticmethod
def is_unique(signature):
for bug in Bug.query.all():
s = ' '.join((bug.title, bug.description, bug.impact))
js = get_jaccard_sim(signature, s)
if js > 0.5:
return False
return True
@property
def is_validated(self):
# includes any validation result
# rejected, confirmed, and fixed
if self.status > 0:
return True
return False
@property
def is_accepted(self):
# includes confirmed and fixed
if self.status > 1:
return True
return False
def __repr__(self):
return "<Bug '{}'>".format(self.title)
class Friendship(db.Model):
"""A simple table of one-to-one friendships."""
__tablename__ = 'friendships'
__table_args__ = (UniqueConstraint('sender_id', 'receiver_id'), )
VALID = 0
REQUEST = 1
PENDING = 2
id = db.Column(db.Integer, primary_key=True)
sender_id = db.Column(db.Integer,
db.ForeignKey('users.id'),
nullable=False)
receiver_id = db.Column(db.Integer,
db.ForeignKey('users.id'),
nullable=False)
sender = db.relationship('User',
uselist=False,
foreign_keys='Friendship.sender_id')
receiver = db.relationship('User',
uselist=False,
foreign_keys='Friendship.receiver_id')
valid = db.Column(db.Boolean, nullable=False, default=False)
@staticmethod
def create(sender, receiver):
"""Create an invalid friendship entry."""
assert not Friendship.get(sender, receiver)
friendship = Friendship(sender=sender, receiver=receiver, valid=False)
db.session.add(friendship)
return friendship
@staticmethod
def get(first, second):
"""Get the existing friendship entry, regardless of validity."""
return Friendship.query.filter(
or_(
and_(Friendship.sender == first,
Friendship.receiver == second),
and_(Friendship.sender == second,
Friendship.receiver == first))).first()
@staticmethod
def get_all(user):
"""Get all of a user's friendships."""
return Friendship.query.filter(
or_(Friendship.sender == user, Friendship.receiver == user)).all()
def set_valid(self, valid):
"""Change the validity of a friendship."""
self.valid = valid
def __repr__(self):
return '<friendship sender={} receiver={} valid={}>'.format(
self.sender.email, self.receiver.email, self.valid)
class Note(BaseModel):
__tablename__ = 'notes'
name = db.Column(db.String(255), nullable=False)
content = db.Column(db.Text)
user_id = db.Column(db.Integer, db.ForeignKey('users.id'), nullable=False)
def serialize(self):
return {
'id': self.id,
'name': self.name,
'content': self.content,
'created': self.created_as_string,
'modified': self.modified_as_string,
}
def __repr__(self):
return "<Note '{}'>".format(self.name)
class Scan(BaseModel):
__tablename__ = 'scans'
id = db.Column(db.String(36), primary_key=True)
command = db.Column(db.String(255), nullable=False)
results = db.Column(db.Text)
complete = db.Column(db.Boolean, default=False, nullable=False)
user_id = db.Column(db.Integer, db.ForeignKey('users.id'), nullable=False)
def serialize(self, include_results=False):
return {
'id': self.id,
'created': self.created_as_string,
'modified': self.modified_as_string,
'command': self.command,
'complete': self.complete,
}
def __repr__(self):
return "<Scan '{}'>".format(self.name)
class Game(db.Model):
"""
The game model.
columns
~~~~~
| name | max_points | max_players | status | random | previous_round |
|------|------------|-------------|--------|--------|----------------|
| str | int | int | enum | bool | pickle |
relationships
~~~~~
host : user -> game
black_card: card -> game
judge : player -> game
players : player <- game
used_cards: card <-> game
"""
__tablename__ = 'games'
ONGOING = 0
PENDING = 1
ENDED = 2
id = db.Column(db.Integer, primary_key=True)
host_id = db.Column(db.Integer, db.ForeignKey('users.id'), nullable=False)
name = db.Column(db.String(80), nullable=False)
max_points = db.Column(db.Integer, nullable=False)
max_players = db.Column(db.Integer, nullable=False)
status = db.Column(db.Integer, nullable=False)
random = db.Column(db.Boolean, nullable=False)
black_card_id = db.Column(db.Integer, db.ForeignKey('cards.id'))
black_card = db.relationship('Card')
judge_id = db.Column(db.Integer, db.ForeignKey('players.id'))
judge = db.relationship('Player',
foreign_keys='Game.judge_id',
post_update=True)
players = db.relationship('Player',
backref='game',
foreign_keys='Player.game_id')
previous_round = db.Column(db.PickleType)
used_cards = db.relationship('Card', uselist=True)
end_time = db.Column(db.DateTime, nullable=True)
@staticmethod
def create(host, name, max_points, max_players, random):
"""Create an uninitialized (pending) match."""
game = Game(host_id=host.id,
name=name,
max_points=max_points,
max_players=max_players,
random=random,
status=Game.PENDING,
previous_round={},
used_cards=[])
db.session.add(game)
return game
@staticmethod
def get(id):
"""Get a game by id."""
return Game.query.get(id)
@staticmethod
def find_random(user):
"""
Finds a valid optimal random game
for the user. This is very inefficient
and there is a better way to do this.
TODO: optimize this
"""
games = Game.query.filter(
Game.random == True, # this is not a mistake
Game.status == Game.PENDING,
~Game.players.any(Player.user_id == user.id)).all()
try:
game = max(games,
key=lambda g: len(
[p for p in g.players
if p.status == Player.JOINED]))
except ValueError:
game = None
return game
@staticmethod
def find_n_random(n, user):
"""Finds the top n optimal random games. TODO: optimize THE SHIT OUT OF THIS"""
games = Game.query.filter(
Game.random == True, # this is not a mistake
Game.status == Game.PENDING,
~Game.players.any(Player.user_id == user.id)).all()
try:
games = sorted(
games,
key=lambda g: len(
[p for p in g.players if p.status == Player.JOINED]))
except ValueError:
games = []
return games if len(games) < n else games[:n]
def start(self):
"""Begins the game."""
self.status = self.ONGOING
[
player.delete() for player in self.players
if player.status != Player.JOINED
]
self.players = [
player for player in self.players if player.status == Player.JOINED
]
self.new_round(None)
def end(self):
"""Ends the game."""
for player in self.players:
if player.user.num_random > 0:
player.user.num_random -= 1
self.status = Game.ENDED
self.end_time = datetime.now()
def new_round(self, winner):
"""
Starts a new round.
- Store previous round as Pickled dictionary
- Fill every player's hand
- Discard and play a new black card
- Find a new judge and increment
his judge count
"""
if winner:
self.previous_round = {
'judge': {
'name': self.judge.user.name,
'email': self.judge.user.email
},
'winner': {
'name': winner.user.name,
'email': winner.user.email
},
'black_card': {
'id': self.black_card.id,
'text': self.black_card.text,
'answers': self.black_card.answers
},
'table': [{
'name':
player.user.name,
'email':
player.user.email,
'picture':
player.user.picture,
'cards': [{
'id': card.id,
'text': card.text
} for card in player.get_played()]
} for player in self.players
if player.hand and player != self.judge]
}
self.winner = None
if any(player.score == self.max_points for player in self.players):
self.end()
return
white_cards = Card.get_all(black=False)
# fill every player's hand and remove their played card
for player in self.players:
player.played = []
while len(player.hand) < 10:
white_card = choice(white_cards)
while white_card in set(self.used_cards):
white_card = choice(white_cards)
player.hand.append(white_card)
self.used_cards.append(white_card)
# play a new black card and choose a new judge
black_cards = Card.get_all(black=True)
self.black_card = choice(black_cards)
while self.black_card in set(self.used_cards):
self.black_card = choice(black_cards)
self.used_cards.append(self.black_card)
self.judge = min(self.players, key=lambda player: player.judged)
self.judge.played = [self.black_card.id]
self.judge.judged += 1
def invite_all(self, players):
"""Invites a list of players to this game."""
self.players += players
def invite(self, player):
"""Invites a single player to this game."""
self.players.append(player)
def get_description(self):
"""Returns the description of the game."""
if self.status == Game.ONGOING:
players = sorted(self.players, key=lambda p: p.user.name)
names = [player.user.name.split(' ')[0] for player in players]
count = len(names)
return '{}, {}, and {} others...'.format(names[0], names[1],
count - 2)
elif self.status == Game.PENDING:
joined = [
player for player in self.players
if player.status == Player.JOINED
]
names = [player.user.name.split(' ')[0] for player in joined]
count = len(names)
if count == 1:
return 'This game is hosted by {}!'.format(names[0])
elif count == 2:
return '{} and {} have joined this game!'.format(
names[0], names[1])
elif count == 3:
return '{}, {}, and {} have joined this game!'.format(
names[0], names[1], names[2])
return '{}, {}, and {} others have joined this game!'.format(
names[0], names[1], count - 2)
elif self.status == Game.ENDED:
winner = max(self.players, key=lambda p: p.score)
name = winner.user.name.split(' ')[0]
return '{} won the game with {} points!'.format(name, winner.score)
return 'Something went horribly wrong...'
def __repr__(self):
return '<game id={} players={}>'.format(
self.id, [player.user.id for player in self.players])
# coding: utf-8
from common import login_manager, db, ModelBase, ma
from itsdangerous import TimedJSONWebSignatureSerializer as Serializer
from itsdangerous.exc import SignatureExpired, BadSignature
from flask import current_app
from flask_restful import fields
from flask_security import RoleMixin, UserMixin, SQLAlchemyUserDatastore
from flask_security.utils import hash_password, verify_password
roles_users = db.Table(
"users_roles",
db.Column("user_id", db.Integer, db.ForeignKey("user.id")),
db.Column("role_id", db.Integer(), db.ForeignKey("role.id")),
)
class Role(db.Model, RoleMixin, ModelBase):
__tablename__ = "role"
name = db.Column(db.String(80), unique=True)
description = db.Column(db.String(80))
class User(db.Model, UserMixin, ModelBase):
__tablename__ = "user"
name = db.Column(db.String(255), unique=True, nullable=False)
email = db.Column(db.String(255), unique=True, nullable=False)
password_hash = db.Column(db.String(255), nullable=False)
class Player(db.Model):
"""
Player model containing
functions which encapsulate
gameplay state changes.
columns
~~~~~
| status | score | judged | seen | played |
|--------|-------|--------|------|--------|
| enum | int | int | bool | pickle |
relationships
~~~~~
user : user <- player
game : game <- player
hand : player - card
"""
__tablename__ = 'players'
PENDING = 0
JOINED = 1
REJECTED = 2
id = db.Column(db.Integer, primary_key=True)
user_id = db.Column(db.Integer, db.ForeignKey('users.id', name='user_to_player'), nullable=False)
game_id = db.Column(db.Integer, db.ForeignKey('games.id', name='game_to_player'), nullable=False)
status = db.Column(db.Integer, nullable=False)
score = db.Column(db.Integer, nullable=False)
judged = db.Column(db.Integer, nullable=False)
seen = db.Column(db.Boolean, nullable=False, default=False)
played = db.Column(db.PickleType, nullable=True)
hand = db.relationship('Card', secondary=hands)
@staticmethod
def create(user, game):
"""Creates a single uninitialized player."""
player = Player(
user=user, game=game, status=Player.PENDING, score=0, judged=0
)
db.session.add(player)
return player
@staticmethod
def create_all(users, game):
"""Creates many players for a given game."""
players = [
Player(
user=user, game=game, status=Player.PENDING, score=0, judged=0
)
for user in users
]
db.session.add_all(players)
return players
@staticmethod
def get(user, game):
"""Returns the player associated with a specific user and game."""
return Player.query.filter_by(user=user, game=game).first()
def delete(self):
"""Deletes a player. Typically used when declining to join a game."""
db.session.delete(self)
def get_played(self):
"""
Store the player's played cards as a list of
card ids. This is necessary for efficiently
enforcing played order. The nested loop below
is still faster than making one query for each card.
"""
if not self.played:
return []
cards = Card.query.filter(
or_(Card.id == card_id for card_id in self.played)
).all()
played = []
for card_id in self.played:
card = next(
(c for c in cards if c.id == card_id),
None
)
played.append(card)
return played
def play_cards(self, cards):
assert not self.played
self.played = [card.id for card in cards]
[self.hand.remove(card) for card in cards]
def set_status_joined(self):
"""Change player status to JOINED."""
self.status = Player.JOINED
def set_status_denied(self):
"""Change player status to DENIED."""
self.status = Player.DENIED
def add_points(self, n):
"""Add n points to the player's score."""
self.score += n
def __repr__(self):
return '<player email={} game={} status={}>'.format(
self.user.email, self.game.id, self.status
)
models.player
~~~~~
Player model and assoc
tables, which represent
a user's state inside a game.
"""
from sqlalchemy import or_
from common import db
from models import Card
hands = db.Table(
'hands',
db.Model.metadata,
db.Column('player', db.Integer, db.ForeignKey('players.id')),
db.Column('card', db.Integer, db.ForeignKey('cards.id'))
)
class Player(db.Model):
"""
Player model containing
functions which encapsulate
gameplay state changes.
columns
~~~~~
| status | score | judged | seen | played |
|--------|-------|--------|------|--------|
| enum | int | int | bool | pickle |
from common import db, app, migrate
from exceptions import DBException
# Volunteer - Classroom: One to many
volunteer_classroom = db.Table(
'volunteer_classroom',
db.Column('volunteer_id',
db.Integer,
db.ForeignKey('volunteer.id'),
primary_key=True),
db.Column('classroom_id',
db.Integer,
db.ForeignKey('classroom.id'),
primary_key=True))
# Student - Classroom: One to many
student_classroom = db.Table(
'student_classroom',
db.Column('student_id',
db.Integer,
db.ForeignKey('student.id'),
primary_key=True),
db.Column('classroom_id',
db.Integer,
db.ForeignKey('classroom.id'),
primary_key=True))
class Volunteer(db.Model):
__tablename__ = 'volunteer'
