class Project(db.Model):
__tablename__ = "project"
id = db.Column("id", db.Integer(), primary_key=True)
name = db.Column("name", db.String(32), nullable=False, unique=True)
creator_user_id = db.Column("creator_user_id",
db.Integer(),
db.ForeignKey("user.id"),
nullable=False)
api_key = db.Column("api_key", db.String(32), nullable=False, unique=True)
created_at = db.Column("created_at",
db.DateTime(),
nullable=False,
default=db.func.now())
last_modified = db.Column(
"last_modified",
db.DateTime(),
nullable=False,
default=db.func.now(),
onupdate=db.func.utc_timestamp(),
)
users = db.relationship("User",
secondary=user_project_table,
back_populates="projects")
data = db.relationship("Data", backref="Project")
labels = db.relationship("Label", backref="Project")
creator_user = db.relationship("User")
class UserSong(db.Model):
__tablename__ = 'user_song'
user_id = db.Column('user_id',
db.Integer(),
db.ForeignKey('user.id', ondelete='CASCADE'),
primary_key=True)
song_id = db.Column('song_id',
db.Integer(),
db.ForeignKey('song.id', ondelete='RESTRICT'),
primary_key=True)
time_played = db.Column('time_played', db.DateTime(), primary_key=True)
user = db.relationship("User",
backref=db.backref('user_song', lazy='dynamic'))
song = db.relationship("Song")
def __init__(self, user, song, time_played):
self.user = user
self.song = song
self.time_played = time_played
def __repr__(self):
return '<User %s Song %s>' % (self.user.username, self.song.spot_id)
class Segmentation(db.Model):
__tablename__ = "segmentation"
id = db.Column("id", db.Integer(), primary_key=True)
data_id = db.Column(
"data_id", db.Integer(), db.ForeignKey("data.id"), nullable=False
)
start_time = db.Column("start_time", db.Float(), nullable=False)
end_time = db.Column("end_time", db.Float(), nullable=False)
transcription = db.Column("transcription", db.Text(), nullable=True)
created_at = db.Column(
"created_at", db.DateTime(), nullable=False, default=db.func.now()
)
last_modified = db.Column(
"last_modified",
db.DateTime(),
nullable=False,
default=db.func.now(),
onupdate=db.func.utc_timestamp(),
)
values = db.relationship(
"LabelValue", secondary=annotation_table, back_populates="segmentations",
)
def set_start_time(self, start_time):
self.start_time = start_time
def set_end_time(self, end_time):
self.end_time = end_time
def set_transcription(self, transcription):
self.transcription = transcription
def to_dict(self):
return {
"start_time": self.start_time,
"end_time": self.end_time,
"transcription": self.transcription,
"created_at": self.created_at,
"last_modified": self.last_modified,
}
class WallMessages(db.Model):
id = db.Column(db.Integer, primary_key=True, autoincrement=True)
date = db.Column(db.Integer)
sender = db.Column(db.Integer, db.ForeignKey("users.id"))
destination = db.Column(db.Integer,
db.ForeignKey("user_type_destinations.id"))
param_value = db.Column(db.Integer())
message = db.Column(db.Text())
def set_fields(self, fields):
self.date = fields['date']
self.sender = fields['sender']
self.destination = fields['user_type_destination_id']
self.param_value = fields['parameter']
self.message = format_urls_in_text(fields['message'])
def get_sender(self):
return Users.query.filter_by(id=self.sender).all()
def get_destinations(self):
_dict = {}
query = UserTypeDestinations.query.filter_by(
id=self.destination).first().users_query
query = str(query).replace('$', str(self.param_value))
exec(query, _dict)
return _dict['users']
class User(db.Model):
__tablename__ = "user"
id = db.Column("id", db.Integer(), primary_key=True)
username = db.Column("username",
db.String(128),
index=True,
unique=True,
nullable=False)
password = db.Column("password", db.String(100), nullable=False)
role_id = db.Column("role_id",
db.Integer(),
db.ForeignKey("role.id"),
nullable=False)
created_at = db.Column("created_at",
db.DateTime(),
nullable=False,
default=db.func.now())
last_modified = db.Column(
"last_modified",
db.DateTime(),
nullable=False,
default=db.func.now(),
onupdate=db.func.utc_timestamp(),
)
role = db.relationship("Role")
projects = db.relationship("Project",
secondary=user_project_table,
back_populates="users")
def set_role(self, role_id):
self.role_id = role_id
def set_password(self, password):
self.password = generate_password_hash(password)
def check_password(self, password):
return check_password_hash(self.password, password)
class Role(db.Model, RoleMixin):
id = db.Column(db.Integer(), primary_key=True)
name = db.Column(db.String(80), unique=True)
description = db.Column(db.String(255))
def __init__(self, name):
self.name = name
def __str__(self):
return self.name
class Database(db.Model):
id = db.Column(db.Integer, primary_key=True)
server = db.Column(db.String(128), index=True)
database = db.Column(db.String(128), index=True, unique=True)
db_type = db.Column(db.String(32))
db_role = db.Column(db.String(32))
port = db.Column(db.Integer())
user = db.Column(db.String(128))
password = db.Column(db.String(128))
def __repr__(self):
return '<Database {}\\{}>'.format(self.server, self.database)
class Label(db.Model):
__tablename__ = "label"
id = db.Column("id", db.Integer(), primary_key=True)
name = db.Column("name", db.String(32), nullable=False)
project_id = db.Column("project_id",
db.Integer(),
db.ForeignKey("project.id"),
nullable=False)
type_id = db.Column("type_id",
db.Integer(),
db.ForeignKey("label_type.id"),
nullable=False)
created_at = db.Column("created_at",
db.DateTime(),
nullable=False,
default=db.func.now())
last_modified = db.Column(
"last_modified",
db.DateTime(),
nullable=False,
default=db.func.now(),
onupdate=db.func.utc_timestamp(),
)
label_type = db.relationship("LabelType", backref="Label")
label_values = db.relationship("LabelValue", backref="Label")
__table_args__ = (db.UniqueConstraint("name",
"project_id",
name="_name_project_id_uc"), )
def set_label_type(self, label_type_id):
self.type_id = label_type_id
class Song(db.Model):
id = db.Column(db.Integer, primary_key=True)
spot_id = db.Column(db.String(50), unique=True, nullable=False)
dance = db.Column(db.Float(), nullable=False)
energy = db.Column(db.Float(), nullable=False)
key = db.Column(db.Integer(), nullable=False)
loud = db.Column(db.Float(), nullable=False)
mode = db.Column(db.Integer(), nullable=False)
speech = db.Column(db.Float(), nullable=False)
acoustic = db.Column(db.Float(), nullable=False)
instrument = db.Column(db.Float(), nullable=False)
live = db.Column(db.Float(), nullable=False)
valence = db.Column(db.Float(), nullable=False)
tempo = db.Column(db.Float(), nullable=False)
duration = db.Column(db.Integer(), nullable=False)
time = db.Column(db.Integer(), nullable=False)
def __init__(self, spot_id, dance, energy, key, loud, mode, speech,
acoustic, instrument, live, valence, tempo, duration, time):
self.spot_id = spot_id
self.dance = dance
self.energy = energy
self.key = key
self.loud = loud
self.mode = mode
self.speech = speech
self.acoustic = acoustic
self.instrument = instrument
self.live = live
self.valence = valence
self.tempo = tempo
self.duration = duration
self.time = time
def __repr__(self):
return '<Song %s>' % self.spot_id
class SectionTimes(db.Model):
id = db.Column(db.Integer, primary_key=True)
course_section_id = db.Column(db.Integer, db.ForeignKey('course_sections.id'), nullable=False)
week_day = db.Column(db.Integer())
section_time_start_time = db.Column(db.Time())
section_time_finish_time = db.Column(db.Time())
def __repr__(self):
return str('{0}').format(self.week_day)
def set_fields(self, fields):
self.course_section_id = fields['course_section_id']
self.section_time_start_time = datetime.datetime.strptime(fields['section_time_start_time'], "%H:%M:%S").time()
self.section_time_finish_time = datetime.datetime.strptime(fields['section_time_finish_time'], "%H:%M:%S", ).time()
self.week_day = fields['week_day']
class LabelValue(db.Model):
__tablename__ = "label_value"
id = db.Column("id", db.Integer(), primary_key=True)
label_id = db.Column("label_id",
db.Integer(),
db.ForeignKey("label.id"),
nullable=False)
value = db.Column("value", db.String(200), nullable=False)
created_at = db.Column("created_at",
db.DateTime(),
nullable=False,
default=db.func.now())
last_modified = db.Column(
"last_modified",
db.DateTime(),
nullable=False,
default=db.func.now(),
onupdate=db.func.utc_timestamp(),
)
__table_args__ = (db.UniqueConstraint("label_id",
"value",
name="_label_id_value_uc"), )
label = db.relationship("Label", backref="LabelValue")
segmentations = db.relationship("Segmentation",
secondary=annotation_table,
back_populates="values")
def set_label_value(self, value):
self.value = value
class LabelType(db.Model):
__tablename__ = "label_type"
id = db.Column("id", db.Integer(), primary_key=True)
type = db.Column("type", db.String(32), nullable=False, unique=True)
created_at = db.Column(
"created_at", db.DateTime(), nullable=False, default=db.func.now()
)
last_modified = db.Column(
"last_modified",
db.DateTime(),
nullable=False,
default=db.func.now(),
onupdate=db.func.utc_timestamp(),
)
class Role(db.Model):
__tablename__ = "role"
id = db.Column("id", db.Integer(), primary_key=True)
role = db.Column("role", db.String(30), index=True, unique=True, nullable=False)
created_at = db.Column(
"created_at", db.DateTime(), nullable=False, default=db.func.now()
)
last_modified = db.Column(
"last_modified",
db.DateTime(),
nullable=False,
default=db.func.now(),
onupdate=db.func.utc_timestamp(),
)
class Message(db.Model):
message_id = db.Column(db.Integer(), primary_key=True)
content = db.Column(db.String(400), unique=False, nullable=False)
sender_id = db.Column(db.String(120), unique=False, nullable=False)
receiver_id = db.Column(db.String(120), unique=False, nullable=False)
timestamp = db.Column(db.DateTime(timezone=True),
default=datetime.datetime.now())
conversation_id = db.Column(db.Integer,
db.ForeignKey('conversation.conversation_id'))
#attachments
def save_to_db(self):
db.session.add(self)
db.session.commit()
@staticmethod
def convert_to_datatime(time):
return datetime.datetime.fromtimestamp(time / 1000.0)
class SectionTimes(db.Model):
id = db.Column(db.Integer, primary_key=True)
course_section_id = db.Column(db.Integer,
db.ForeignKey('course_sections.id'),
nullable=False)
week_day = db.Column(db.Integer())
section_time_start_time = db.Column(db.Time())
section_time_finish_time = db.Column(db.Time())
student_attendance = db.relationship("StudentAttendance")
def set_fields(self, fields):
self.course_section_id = fields['course_section_id']
self.section_time_start_time = datetime.datetime.strptime(
fields['section_time_start_time'], "%H:%M:%S").time()
self.section_time_finish_time = datetime.datetime.strptime(
fields['section_time_finish_time'],
"%H:%M:%S",
).time()
self.week_day = fields['week_day']
class Image(db.Model):
__tablename__ = "images" #tab name
#tab columns and props
id = db.Column(db.Integer, primary_key=True)
image = db.Column(db.LargeBinary())
infos = db.Column(db.Integer())
def __init__(self, image, infos):
self.image = image
self.infos = infos
def __repr__(self):
return '<id {}>'.format(self.id)
def serialize(self):
return {
'id': self.id,
'image': self.image,
'infos': self.infos,
}
class User(db.Model):
__tablename__ = 'user'
id = db.Column(db.Integer(), primary_key = True)
email = db.Column(db.String(255), unique = True)
password = db.Column(db.String(255))
def __init__(self, email, password):
self.email = email
self.active = True
self.password = User.hashed_password(password)
@staticmethod
def hashed_password(password):
return bcrypt.generate_password_hash(password).decode("utf-8")
@staticmethod
def get_user_with_email_and_password(email, password):
user = User.query.filter_by(email = email).first()
if user and bcrypt.check_password_hash(user.password, password):
return user
else:
return None
class Role(db.Model, RoleMixin):
id = db.Column(db.Integer(), primary_key=True)
name = db.Column(db.String(80), unique=True)
description = db.Column(db.String(255))
from werkzeug.security import generate_password_hash, check_password_hash
from backend import db
annotation_table = db.Table(
"annotation",
db.metadata,
db.Column("id", db.Integer(), primary_key=True),
db.Column(
"segmentation_id",
db.Integer(),
db.ForeignKey("segmentation.id"),
nullable=False,
),
db.Column(
"label_value_id", db.Integer(), db.ForeignKey("label_value.id"), nullable=False
),
db.Column("created_at", db.DateTime(), nullable=False, default=db.func.now()),
db.Column(
"last_modified",
db.DateTime(),
nullable=False,
default=db.func.now(),
onupdate=db.func.utc_timestamp(),
),
)
user_project_table = db.Table(
"user_project",
db.metadata,
db.Column("id", db.Integer(), primary_key=True),
in d.items()
if k not in exclude and not isinstance(v, list)
}
class DictBase:
def to_dict(self):
return dictonify(self.__dict__)
def __init__(self, **kwargs):
for k, v in kwargs.items():
self.__dict__[k] = v
roles_users = db.Table(
'roles_users',
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):
id = db.Column(db.Integer(), primary_key=True)
name = db.Column(db.String(80), unique=True)
description = db.Column(db.String(255))
def __init__(self, name):
self.name = name
def __str__(self):
return self.name
class Data(db.Model):
__tablename__ = "data"
id = db.Column("id", db.Integer(), primary_key=True)
project_id = db.Column(
"project_id", db.Integer(), db.ForeignKey("project.id"), nullable=False
)
assigned_user_id = db.Column(
"assigned_user_id", db.Integer(), db.ForeignKey("user.id"), nullable=False
)
filename = db.Column("filename", db.String(100), nullable=False, unique=True)
original_filename = db.Column("original_filename", db.String(100), nullable=False)
reference_transcription = db.Column(
"reference_transcription", db.Text(), nullable=True
)
is_marked_for_review = db.Column(
"is_marked_for_review", db.Boolean(), nullable=False, default=False
)
created_at = db.Column(
"created_at", db.DateTime(), nullable=False, default=db.func.now()
)
last_modified = db.Column(
"last_modified",
db.DateTime(),
nullable=False,
default=db.func.now(),
onupdate=db.func.utc_timestamp(),
)
project = db.relationship("Project")
assigned_user = db.relationship("User")
segmentations = db.relationship("Segmentation", backref="Data")
def update_marked_review(self, marked_review):
self.is_marked_for_review = marked_review
def set_segmentations(self, segmentations):
self.segmentations = segmentations
def to_dict(self):
return {
"original_filename": self.original_filename,
"filename": self.filename,
"url": f"/audios/{self.filename}",
"reference_transcription": self.reference_transcription,
"is_marked_for_review": self.is_marked_for_review,
"created_at": self.created_at,
"last_modified": self.last_modified,
"assigned_user": {
"id": self.assigned_user_id,
"username": self.assigned_user.username,
"role": self.assigned_user.role.role,
},
}
from werkzeug.security import generate_password_hash, check_password_hash
from backend import db
annotation_table = db.Table(
"annotation",
db.metadata,
db.Column("id", db.Integer(), primary_key=True),
db.Column(
"segmentation_id",
db.Integer(),
db.ForeignKey("segmentation.id"),
nullable=False,
),
db.Column("label_value_id",
db.Integer(),
db.ForeignKey("label_value.id"),
nullable=False),
db.Column("created_at",
db.DateTime(),
nullable=False,
default=db.func.now()),
db.Column(
"last_modified",
db.DateTime(),
nullable=False,
default=db.func.now(),
onupdate=db.func.utc_timestamp(),
),
)
