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 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 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 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 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 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 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 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 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 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 Key(db.Model):
__tablename__ = 'keys'
id = db.Column(db.Integer, primary_key=True)
organization_id = db.Column(db.ForeignKey('resources.id'), nullable=False)
creation_user_id = db.Column(db.ForeignKey('users.id'), nullable=False)
revocation_user_id = db.Column(db.ForeignKey('users.id'),
comment='null if not revoked')
creation_timestamp = db.Column(db.DateTime, nullable=False)
revocation_timestamp = db.Column(db.DateTime,
comment='null if not revoked')
access_as_user_id = db.Column(db.ForeignKey('users.id'),
comment='null if access as controller')
access_as_controller_id = db.Column(db.ForeignKey('resources.id'),
comment='null if access as user')
key_part = db.Column(db.String(8), nullable=False)
key_hash = db.Column(db.String(128),
nullable=False,
comment='like a password hash, but for a key')
# fix(soon): remove these and just use key_hash
key_storage = db.Column(db.String(), nullable=True)
key_storage_nonce = db.Column(db.String(), nullable=True)
def as_dict(self):
return {
'id':
self.id,
'organization_id':
self.organization_id,
'access_as_user_id':
self.access_as_user_id,
'access_as_controller_id':
self.access_as_controller_id,
'creation_timestamp':
self.creation_timestamp.isoformat() +
'Z' if self.creation_timestamp else '',
'revocation_timestamp':
self.revocation_timestamp.isoformat() +
'Z' if self.revocation_timestamp else '',
'key_part':
self.key_part,
}
class AccountRequest(db.Model):
__tablename__ = 'account_requests'
id = db.Column(db.Integer, primary_key=True)
organization_name = db.Column(db.String(100)) # used for new organization
organization_id = db.Column(
db.ForeignKey('resources.id')) # used to join existing organization
organization = db.relationship(
'Resource') # used to join existing organization
inviter_id = db.Column(
db.ForeignKey('users.id')) # used to join existing organization
creation_timestamp = db.Column(db.DateTime, nullable=False)
redeemed_timestamp = db.Column(db.DateTime)
access_code = db.Column(db.String(40), nullable=False)
email_address = db.Column(db.String, nullable=False)
email_sent = db.Column(db.Boolean,
nullable=False) # True if sent successfully
email_failed = db.Column(db.Boolean,
nullable=False) # True if given up on sending
attributes = db.Column(
db.String, nullable=False) # JSON field containing extra attributes
from main.app import db
import datetime
scout_messages = db.Table(
'scout_messages',
db.Column('scout_id', db.Integer, db.ForeignKey('scout.id')),
db.Column('message_id', db.Integer, db.ForeignKey('message.id')),
)
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
manager_messages = db.Table(
'manager_messages',
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
id_photos = db.Table('id_photos',
db.Column('account_id', db.Integer, db.ForeignKey('account.id')),
db.Column('photo_id', db.Integer, db.ForeignKey('photo.id'))
)
# User Account
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')
)
class Resource(db.Model):
__tablename__ = 'resources'
id = db.Column(db.Integer, primary_key = True)
last_revision_id = db.Column(db.Integer) # can be null for folder resources or empty files/sequences
organization_id = db.Column(db.ForeignKey('resources.id')) # would like this to be non-null, but how else do we create first organization?
creation_timestamp = db.Column(db.DateTime)
modification_timestamp = db.Column(db.DateTime) # fix(later): remove this and use revision timestamp? what about folders? should we track modification timestamps for this?
# meta-data that could eventually have change tracking (probably best to move into a separate table e.g. resource_meta_revisions)
parent_id = db.Column(db.ForeignKey('resources.id'), index = True)
parent = db.relationship('Resource', remote_side = [id], foreign_keys = [parent_id])
name = db.Column(db.String, nullable = False)
type = db.Column(db.Integer, nullable = False) # fix(later): add index for this?
permissions = db.Column(db.String) # JSON; NULL -> inherit from parent
system_attributes = db.Column(db.String, nullable = False, default = '{}') # JSON dictionary; additional attributes of the resource (system-defined)
user_attributes = db.Column(db.String) # JSON dictionary; additional attributes of the resource (user-defined)
deleted = db.Column(db.Boolean, nullable = False, default = False)
# fix(soon): remove after migrate
file_type = db.Column(db.String(20))
hash = db.Column(db.String(50))
size = db.Column(db.BigInteger)
# resource types
BASIC_FOLDER = 10
ORGANIZATION_FOLDER = 11
CONTROLLER_FOLDER = 12
REMOTE_FOLDER = 13
FILE = 20
SEQUENCE = 21
APP = 22
# sequence data types
NUMERIC_SEQUENCE = 1
TEXT_SEQUENCE = 2
IMAGE_SEQUENCE = 3
# ======== methods ========
# get information about the resource in a json-ready dictionary
def as_dict(self, extended = False):
d = {
'id': self.id,
'name': self.name,
'type': self.type,
}
if extended:
d['parent_id'] = self.parent_id
if self.creation_timestamp: # fix(clean): remove this after make sure everything has timestamp
d['creationTimestamp'] = self.creation_timestamp.isoformat() + 'Z' # fix(soon): remove
d['creation_timestamp'] = self.creation_timestamp.isoformat() + 'Z'
if self.modification_timestamp: # fix(clean): remove this after make sure everything has timestamp
d['modificationTimestamp'] = self.modification_timestamp.isoformat() + 'Z' # fix(soon): remove
d['modification_timestamp'] = self.modification_timestamp.isoformat() + 'Z'
d['permissions'] = json.loads(self.permissions) if self.permissions else []
d['settings'] = json.loads(self.system_attributes) if self.system_attributes else {} # fix(soon): remove
d['system_attributes'] = json.loads(self.system_attributes) if self.system_attributes else {}
d['user_attributes'] = json.loads(self.user_attributes) if self.user_attributes else {}
d['lastRevisionId'] = self.last_revision_id # fix(soon): remove
d['last_revision_id'] = self.last_revision_id
if self.last_revision_id:
d['storage_path'] = self.storage_path(self.last_revision_id)
if self.type == self.FILE: # fix(later): remove this case after migrate DB and update client sync code (and browser display code) to use direct system_attributes
d['hash'] = d['system_attributes'].get('hash') or self.hash
d['size'] = d['system_attributes'].get('size') or self.size
return d
# get the path of the resource (including it's own name)
# includes leading slash
def path(self):
if self.parent:
path = self.parent.path() + '/' + self.name
else:
path = '/' + self.name
return path
# returns True if this resource is a child/grandchild/etc. of the specified resource
def is_descendent_of(self, resource_id):
if not self.parent_id:
return False
elif self.parent_id == resource_id:
return True
else:
return self.parent.is_descendent_of(resource_id)
# get a list of folders contained within this folder (recursively)
# fix(clean): maybe this is too specialized; move elsewhere?
def descendent_folder_ids(self):
ids = []
children = Resource.query.filter(Resource.parent_id == self.id, Resource.deleted == False)
for child in children:
if child.type >= 10 and child.type < 20:
ids.append(child.id)
ids += child.descendent_folder_ids()
return ids
# the root of a hierachy of resources; this will generally be an organization (or system folder such as 'doc' or 'system')
def root(self):
if self.parent:
return self.parent.root()
else:
return self
# get a list of permission applied to this resource (including inherited from parents)
def query_permissions(self):
permissions = json.loads(self.permissions) if self.permissions else None
if self.parent:
parent_permissions = self.parent.query_permissions()
# if we have permissions at this level, we need to merge in the parent permissions
if self.permissions:
permission_dict = {(type, id): level for (type, id, level) in self.permissions}
for permission_record in parent_permissions:
(type, id, level) = permission_record
if not (type, id) in permission_dict:
permissions.append((type, id, level))
# otherwise, just use the parent permissions (the common case)
else:
permissions = parent_permissions
return permissions
# get the path of the resource in the bulk storage system
def storage_path(self, revision_id):
org_id = self.organization_id
if not org_id: # fix(clean): remove this
org_id = self.root().id
id_str = '%09d' % self.id
return '%d/%s/%s/%s/%d_%d' % (org_id, id_str[-9:-6], id_str[-6:-3], id_str[-3:], self.id, revision_id)
