class User(UserMixin, db.Model):
__tablename__ = 'users'
id = db.Column(db.Integer, primary_key=True)
user_name = db.Column(db.String(50), nullable=True) # optional
email_address = db.Column(db.String(100), nullable=False, unique=True)
password_hash = db.Column(db.String(128), nullable=False)
full_name = db.Column(db.String(100), nullable=False)
info_status = db.Column(
db.String, nullable=False
) # JSON string indicating which info messages have been displayed to user
attributes = db.Column(
db.String,
nullable=False) # JSON string of additional information about user
deleted = db.Column(db.Boolean, nullable=False)
creation_timestamp = db.Column(db.DateTime, nullable=False)
role = db.Column(db.Integer, nullable=False)
def __repr__(self):
return '%d: %s' % (self.id, self.email_address)
def as_dict(self):
return {
'id': self.id,
'user_name': self.user_name,
'email_address': self.email_address,
'full_name': self.full_name,
'role': self.role,
'creation_timestamp': self.creation_timestamp.isoformat() + 'Z',
'deleted': self.deleted,
}
# roles
STANDARD_USER = 0
SYSTEM_ADMIN = 2 # this is an admin role for the entire site; organization admins are specified in OrganizationUser model
class ActionThrottle(db.Model):
__tablename__ = 'action_throttles'
id = db.Column(db.Integer, primary_key = True)
controller_id = db.Column(db.ForeignKey('resources.id')) # the controller being throttled (null if user)
user_id = db.Column(db.ForeignKey('users.id')) # the user being throttled (null if controller)
type = db.Column(db.String(50), nullable = False) # type of action being throttled
recent_usage = db.Column(db.String, nullable = False) # list of timestamp numbers
class ResourceView(db.Model):
__tablename__ = 'resource_views'
id = db.Column(db.Integer, primary_key=True)
resource_id = db.Column(db.ForeignKey('resources.id'),
nullable=False,
index=True)
user_id = db.Column(db.ForeignKey('users.id'), nullable=False, index=True)
view = db.Column(db.String, nullable=False, comment='JSON string')
class Comment(db.Model): # Класс комментария
id = db.Column(db.Integer, primary_key=True)
author = db.Column(db.String(100), unique=False, nullable=False)
content = db.Column(db.String(1000), unique=False, nullable=False)
article_id = db.Column(db.Integer,
db.ForeignKey('article.id'),
nullable=False)
article = db.relationship('Article',
backref=db.backref('Comments', lazy=True))
class Counter(db.Model): id = db.Column(db.Integer, primary_key=True) count = db.Column(db.Integer, unique=True) def __init__(self, *args, **kwargs): super(Counter, self).__init__(*args, **kwargs) def __repr__(self): return '<Counter: %s' % str(self.count)
class ResourceRevision(db.Model):
__tablename__ = 'resource_revisions'
id = db.Column(db.Integer,
primary_key=True) # upgrade to 64-bit at some point?
resource_id = db.Column(db.ForeignKey('resources.id'),
nullable=False,
index=True)
timestamp = db.Column(db.DateTime, nullable=False, index=True)
data = db.Column(db.LargeBinary, nullable=True)
class Face(db.Model): id = db.Column(db.Integer, primary_key=True) data_name = db.Column(db.String(400)) def __init__(self, *args, **kwargs): super(Face, self).__init__(*args, **kwargs) def __repr__(self): return '<Face: %s' % self.data_name
class Performance(db.Model):
id = db.Column(db.Integer, primary_key=True)
week = db.Column(db.Integer, default=0)
performance = db.Column(db.Float)
def __init__(self, *args, **kwargs):
super(Performance, self).__init__(*args, **kwargs)
def __repr__(self):
return '<Performance: %s>' % self.week
class Notification(
): # preliminary model for review; will inherit from db.Model when ready to create table
__tablename__ = 'notifications'
id = db.Column(db.BigInteger, primary_key=True)
timestamp = db.Column(db.DateTime, nullable=False)
controller_id = db.Column(
db.ForeignKey('resources.id'),
comment='the originating controller (null if user)')
user_id = db.Column(db.ForeignKey('users.id'),
comment='the originating user (null if controller)')
message = db.Column(db.String, nullable=False)
source = db.Column(
db.String,
nullable=False,
comment='human-friendly version of originating message source')
show_in_ui = db.Column(db.Boolean, nullable=False)
recipients = db.Column(
db.String,
nullable=False,
comment=
'JSON list of phone numbers and emails; list can be empty if show_in_ui is True'
)
attributes = db.Column(
db.String,
nullable=False,
comment='JSON field containing extra message attributes')
class Solution(db.Model): # Класс решения
id = db.Column(db.Integer, primary_key=True)
code = db.Column(db.String(1000), unique=False, nullable=False)
status = db.Column(db.String(50), unique=False, nullable=False)
student_id = db.Column(db.Integer,
db.ForeignKey('student.id'),
nullable=False)
student = db.relationship('Student',
backref=db.backref('Solutions', lazy=True))
task_id = db.Column(db.Integer, db.ForeignKey('task.id'), nullable=False)
task = db.relationship('Task', backref=db.backref('Solutions', lazy=True))
class Link(db.Model):
"""This class represents the links table."""
id = db.Column(db.Integer, primary_key=True)
original_url = db.Column(db.String, index=True, unique=True)
target_url = db.Column(db.String, index=True, unique=True)
created = db.Column(db.DateTime, default=datetime.utcnow)
def __repr__(self):
return '<Link from==>{} to==>{} >'.format(self.original_url,
self.target_url)
class FeedbackComment(db.Model):
id = db.Column(db.Integer, primary_key=True)
body = db.Column(db.String(1000),
default='Edit this text to add your own feedback comment')
created_time = db.Column(db.DateTime, default=datetime.datetime.now)
updated_time = db.Column(db.DateTime,
default=datetime.datetime.now,
onupdate=datetime.datetime.now)
def __init__(self, *args, **kwargs):
super(FeedbackComment, self).__init__(*args, **kwargs)
def __repr__(self):
return '<Feedback Comment: %s >' % self.body
class Photo(db.Model):
id = db.Column(db.Integer, primary_key=True)
identifier = db.Column(db.String(100))
photo_name = db.Column(db.String(100))
created_time = db.Column(db.DateTime, default=datetime.datetime.now)
faces = db.relationship('Face', secondary=faces,
backref=db.backref('photos', lazy='dynamic')
)
def __init__(self, *args, **kwargs):
super(Photo, self).__init__(*args, **kwargs)
def __repr__(self):
return '<Photo: %s>' % self.identifier
class Pin(db.Model):
__tablename__ = 'pins'
id = db.Column(db.Integer, primary_key = True)
pin = db.Column(db.Integer, nullable = False)
code = db.Column(db.String(80), nullable = False) # a key used to make sure only the original controller can check this PIN
creation_timestamp = db.Column(db.DateTime, nullable = False)
enter_timestamp = db.Column(db.DateTime)
user_id = db.Column(db.ForeignKey('users.id')) # null if not yet entered
controller_id = db.Column(db.ForeignKey('resources.id')) # null if not yet entered
key_created = db.Column(db.Boolean)
attributes = db.Column(db.String, nullable = False) # JSON field containing extra attributes
class OrganizationUser(db.Model):
__tablename__ = 'organization_users'
id = db.Column(db.Integer, primary_key=True)
organization_id = db.Column(db.ForeignKey('resources.id'), nullable=False)
organization = db.relationship('Resource')
user_id = db.Column(db.ForeignKey('users.id'), nullable=False)
user = db.relationship('User')
is_admin = db.Column(db.Boolean, default=False, nullable=False)
def as_dict(self):
return {
'organization_id': self.organization_id,
'user_id': self.user_id,
'is_admin': self.is_admin,
}
class ControllerStatus(db.Model):
__tablename__ = 'controller_status'
id = db.Column(db.ForeignKey('resources.id'), primary_key=True)
client_version = db.Column(db.String(80), nullable=False)
web_socket_connected = db.Column(
db.Boolean,
nullable=False) # not used currently (may be too brittle); remove?
last_connect_timestamp = db.Column(
db.DateTime, comment='last time the controler connected')
last_watchdog_timestamp = db.Column(
db.DateTime,
comment='last time the controller sent good watchdog message')
watchdog_notification_sent = db.Column(db.Boolean, nullable=False)
attributes = db.Column(
db.String,
nullable=False,
comment='JSON field containing extra controller status information')
def as_dict(self, extended=False):
d = {
'client_version':
self.client_version,
'web_socket_connected':
self.web_socket_connected,
'last_connect_timestamp':
self.last_connect_timestamp.isoformat() +
' Z' if self.last_connect_timestamp else '',
'last_watchdog_timestamp':
self.last_watchdog_timestamp.isoformat() +
' Z' if self.last_watchdog_timestamp else '',
}
if extended:
d['status'] = json.loads(self.attributes)
return d
class Message(db.Model):
__tablename__ = 'messages'
id = db.Column(db.BigInteger().with_variant(db.Integer, "sqlite"), primary_key = True)
timestamp = db.Column(db.DateTime, nullable = False)
sender_controller_id = db.Column(db.ForeignKey('resources.id')) # nullable (null if sent by user or server system)
sender_user_id = db.Column(db.ForeignKey('users.id')) # nullable (null if sent by controller or server system)
folder_id = db.Column(db.ForeignKey('resources.id'), nullable = False) # not nullable; all messages must belong to a folder
type = db.Column(db.String(40), nullable = False)
parameters = db.Column(db.String, nullable = False) # JSON dictionary
attributes = db.Column(db.String) # JSON field containing extra message attributes
def __repr__(self):
return '%d: %s, %s' % (self.id, self.timestamp, self.type)
class Student(db.Model): # Класс пользователя
id = db.Column(db.Integer, primary_key=True)
username = db.Column(db.String(80), unique=True, nullable=False)
password_hash = db.Column(db.String(80), unique=False, nullable=False)
name = db.Column(db.String(80), unique=False, nullable=False)
surname = db.Column(db.String(80), unique=False, nullable=False)
email = db.Column(db.String(120), unique=True, nullable=False)
group = db.Column(db.String(80), unique=False, nullable=False)
teacher = db.Column(db.Boolean, unique=False, nullable=False)
def check_password(self, password): # Проверка пароля
return check_password_hash(self.password_hash, password)
class OutgoingMessage(db.Model):
__tablename__ = 'outgoing_messages'
id = db.Column(db.Integer, primary_key = True)
controller_id = db.Column(db.ForeignKey('resources.id')) # the originating controller (null if user)
user_id = db.Column(db.ForeignKey('users.id')) # the originating user (null if controller)
timestamp = db.Column(db.DateTime, nullable = False)
recipients = db.Column(db.String, nullable = False) # e.g. email addresses or phone numbers
message = db.Column(db.String, nullable = False) # e.g. message content or subject
attributes = db.Column(db.String, nullable = False) # JSON field containing extra message attributes
class Message(db.Model):
id = db.Column(db.Integer, primary_key=True)
tag = db.Column(db.String(100))
sender_status = db.Column(db.String(100))
receiver_status = db.Column(db.String(100))
receiver_id = db.Column(db.Integer)
sender_id = db.Column(db.Integer)
body = db.Column(db.Text)
timestamp = db.Column(db.DateTime, default=datetime.datetime.now)
def __init__(self, *args, **kwargs):
super(Message, self).__init__(*args, **kwargs)
def __repr__(self):
return '<Message: %s>' % self.body
class Thumbnail(db.Model):
__tablename__ = 'thumbnails'
id = db.Column(db.Integer, primary_key = True)
resource_id = db.Column(db.ForeignKey('resources.id'), nullable = False, index = True)
width = db.Column(db.Integer, nullable = False)
height = db.Column(db.Integer, nullable = False)
format = db.Column(db.String(4), nullable = False)
data = db.Column(db.LargeBinary, nullable = False)
class Usage(db.Model):
__tablename__ = 'usage'
id = db.Column(db.Integer, primary_key = True)
organization_id = db.Column(db.ForeignKey('resources.id'), nullable = False)
period = db.Column(db.String(10), nullable = False)
message_count = db.Column(db.BigInteger, nullable = False)
data_bytes = db.Column(db.BigInteger, nullable = False)
attributes = db.Column(db.String, nullable = False) # JSON field containing extra attributes
class Pending(db.Model): id = db.Column(db.Integer, primary_key=True) name = db.Column(db.String(100)) email = db.Column(db.String(100)) password = db.Column(db.String(100)) code = db.Column(db.String(10)) created_time = db.Column(db.DateTime) def __init__(self, *args, **kwargs): super(Pending, self).__init__(*args, **kwargs) def __repr__(self): return '<Pending: %s>' % self.name
class Account(db.Model):
id = db.Column(db.Integer, primary_key=True)
name = db.Column(db.String(100))
email = db.Column(db.String(100))
phone = db.Column(db.String(20))
password = db.Column(db.String(100))
photo_name = db.Column(db.String(400), default='avatar.png')
created_time = db.Column(db.DateTime)
id_photos = db.relationship('Photo', secondary=id_photos,
backref=db.backref('users', lazy='dynamic')
)
def __init__(self, *args, **kwargs):
super(Account, self).__init__(*args, **kwargs)
def __repr__(self):
return '<Account: %s>' % self.name
class Player(db.Model):
id = db.Column(db.Integer, primary_key=True)
first_name = db.Column(db.String(100))
last_name = db.Column(db.String(100))
role = db.Column(db.String(100), nullable=False)
curr_perf = db.Column(db.Float, default=0.00)
photo_name = db.Column(db.String(400), unique=True, nullable=False)
date_of_birth = db.Column(db.DateTime, nullable=False)
week = db.Column(db.Integer, default=0)
num_matches = db.Column(db.Integer, default=0)
stats = db.relationship('Stats',
secondary=player_stats,
backref=db.backref('players', lazy='dynamic'))
performance = db.relationship(
'Performance',
secondary=player_performance,
backref=db.backref('players', lazy='dynamic'),
)
def __init__(self, *args, **kwargs):
super(Player, self).__init__(*args, **kwargs)
def __repr__(self):
return '<Player: %s >' % self.first_name
class Scout(db.Model):
id = db.Column(db.Integer, primary_key=True)
first_name = db.Column(db.String(100))
last_name = db.Column(db.String(100))
username = db.Column(db.String(100))
email = db.Column(db.String(100), unique=True, nullable=False)
password = db.Column(db.String(100), nullable=False)
image_name = db.Column(db.String(400), default='avatar.png')
messages = db.relationship('Message',
secondary=scout_messages,
backref=db.backref('scouts', lazy='dynamic'))
def __init__(self, *args, **kwargs):
super(Scout, self).__init__(*args, **kwargs)
def __repr__(self):
return '<Scout: %s >' % self.username
class Club(db.Model):
id = db.Column(db.Integer, primary_key=True)
name = db.Column(db.String(100), unique=True, nullable=False)
league = db.Column(db.String(100), default='Uganda Premier League')
logo_name = db.Column(db.String(200), default='avatar.png')
home_rating = db.Column(db.Float, default=0.0)
away_rating = db.Column(db.Float, default=0.0)
ave_rating = db.Column(db.Float, default=0.0)
manager = db.relationship('Manager',
secondary=club_manager,
backref=db.backref('clubs', lazy='dynamic'))
players = db.relationship(
'Player',
secondary=club_players,
backref=db.backref('clubs', lazy='dynamic'),
)
def __init__(self, *args, **kwargs):
super(Club, self).__init__(*args, **kwargs)
def __repr__(self):
return '<Club: %s >' % self.name
class TestQuestion(db.Model): # Класс вопроса в тесте
id = db.Column(db.Integer, primary_key=True)
question = db.Column(db.String(500), unique=True, nullable=False)
answer = db.Column(db.String(100), unique=False, nullable=False)
form = db.Column(db.String(30), unique=False, nullable=False)
class Task(db.Model): # Класс задачи
id = db.Column(db.Integer, primary_key=True)
title = db.Column(db.String(80), unique=True, nullable=False)
condition = db.Column(db.String(80), unique=False, nullable=False)
handheld = db.Column(db.Boolean, unique=False, nullable=False)
class Article(db.Model): # Класс новости
id = db.Column(db.Integer, primary_key=True)
title = db.Column(db.String(100), unique=True, nullable=False)
content = db.Column(db.String(500), unique=False, nullable=False)
text = db.Column(db.String(10000), unique=False, nullable=False)