class ConvertRequest(Base):
"""
Represents a conversion job submitted via the API.
"""
__tablename__ = 'conversions'
id = Column(Integer, primary_key=True, autoincrement=False)
output_format = Column(EnumType(OutputFormat), nullable=True)
file_count = Column(Integer, nullable=False, default=1)
files_uploaded = Column(Integer, nullable=False, default=0)
status = Column(EnumType(ConversionStatus), nullable=True)
submission_date = Column(Integer, nullable=False)
completion_date = Column(Integer)
component_options = relationship("ComponentOptions",
uselist=False,
back_populates="conversions")
layout_options = relationship("LayoutOptions",
uselist=False,
back_populates="conversions")
def __init__(self, d):
if d is not None and 'component_options' in d:
self.component_options = ComponentOptions(d['component_options'])
self.component_options.id = self.id
if d is not None and 'layout_options' in d:
self.layout_options = LayoutOptions(d['layout_options'])
self.layout_options.id = self.id
if d is not None:
self.__dict__.update(d)
def __eq__(self, other):
return self.__dict__ == other.__dict__
def upgrade():
op.add_column(
'comparison',
sa.Column('pairing_algorithm',
EnumType(PairingAlgorithm, name='pairing_algorithm'),
nullable=True))
op.add_column(
'assignment',
sa.Column('pairing_algorithm',
EnumType(PairingAlgorithm, name='pairing_algorithm'),
nullable=True))
def upgrade():
# Refer http://alembic.zzzcomputing.com/en/latest/ops.html#alembic.operations.Operations.alter_column
# MySQL can't ALTER a column without a full spec.
# So including existing_type, existing_server_default, and existing_nullable
for table_name in _table_names:
with op.batch_alter_table(table_name,
naming_convention=convention) as batch_op:
batch_op.alter_column('pairing_algorithm',
type_=EnumType(_NewPairingAlgorithm),
existing_type=EnumType(_OldPairingAlgorithm),
existing_server_default=null(),
existing_nullable=True)
def upgrade():
with op.batch_alter_table('comparison',
naming_convention=convention) as batch_op:
batch_op.add_column(
sa.Column('pairing_algorithm',
EnumType(PairingAlgorithm),
nullable=True))
with op.batch_alter_table('assignment',
naming_convention=convention) as batch_op:
batch_op.add_column(
sa.Column('pairing_algorithm',
EnumType(PairingAlgorithm),
nullable=True))
class Transaction(Base):
id = Column(UUIDType, primary_key=True, default=uuid.uuid4)
created_at = Column(UtcDateTime, nullable=False, default=utcnow())
type = Column(
EnumType(TransactionType, name='transaction_type'),
nullable=False,
index=True,
)
user_id = Column(
UUIDType,
ForeignKey('user.id'),
index=True,
nullable=False,
)
user = relationship('User')
currency = Column(
Unicode,
ForeignKey('currency.id'),
index=True,
nullable=False,
)
amount = Column(Numeric(36, 18), nullable=False)
__tablename__ = 'transaction'
__table_args__ = (CheckConstraint(amount != 0, 'ck_transaction_amount'), )
__mapper_args__ = {
'polymorphic_on': type,
'polymorphic_identity': None,
'with_polymorphic': '*',
}
def upgrade():
op.create_table('third_party_user',
sa.Column('id', sa.Integer(), nullable=False),
sa.Column('third_party_type',
EnumType(ThirdPartyType),
nullable=False),
sa.Column('unique_identifier',
sa.String(length=255),
nullable=False),
sa.Column('user_id', sa.Integer(), nullable=False),
sa.Column('modified_user_id', sa.Integer(), nullable=True),
sa.Column('modified', sa.DateTime(), nullable=False),
sa.Column('created_user_id', sa.Integer(), nullable=True),
sa.Column('created', sa.DateTime(), nullable=False),
sa.ForeignKeyConstraint(['created_user_id'], ['user.id'],
ondelete='SET NULL'),
sa.ForeignKeyConstraint(['modified_user_id'], ['user.id'],
ondelete='SET NULL'),
sa.ForeignKeyConstraint(['user_id'], ['user.id'],
ondelete='CASCADE'),
sa.PrimaryKeyConstraint('id'),
sa.UniqueConstraint(
'third_party_type',
'unique_identifier',
name='_unique_third_party_type_and_unique_identifier'),
mysql_charset='utf8',
mysql_collate='utf8_unicode_ci',
mysql_engine='InnoDB')
class TopicUpgradeTest(UpgradeTestCase):
m0 = MetaData()
Topic0 = Table("topics", m0, Column('id', Integer, primary_key=True),
Column('display_name', String, unique=True, nullable=False),
Column('url', String, nullable=False, unique=True),
Column('last_update', UTCDateTime, nullable=True),
Column('type', String))
m1 = MetaData()
Topic1 = Table(
"topics", m1, Column('id', Integer, primary_key=True),
Column('display_name', String, unique=True, nullable=False),
Column('url', String, nullable=False, unique=True),
Column('last_update', UTCDateTime, nullable=True),
Column('type', String),
Column('status',
EnumType(Status, by_name=True),
nullable=False,
server_default=Status.Ok.__str__()))
versions = [(Topic0, ), (Topic1, )]
def upgrade_func(self, engine, operation_factory):
upgrade(engine, operation_factory)
def _get_current_version(self):
return get_current_version(self.engine)
def test_empty_db_test(self):
self._test_empty_db_test()
def test_updage_empty_from_version_0(self):
self._upgrade_from(None, 0)
def test_updage_empty_from_version_1(self):
self._upgrade_from(None, 1)
def test_updage_filled_from_version_0(self):
topic1 = {'url': 'http://1', 'display_name': '1'}
topic2 = {'url': 'http://2', 'display_name': '2'}
topic3 = {'url': 'http://3', 'display_name': '3'}
topic4 = {'url': 'http://4', 'display_name': '4'}
topic5 = {
'url': 'http://5',
'display_name': '5',
'last_update': datetime.now(pytz.utc)
}
self._upgrade_from([[topic1, topic2, topic3, topic4, topic5]], 0)
session_factory = sessionmaker(class_=Session, bind=self.engine)
session = scoped_session(session_factory)
db = session()
try:
topics = db.query(self.Topic1).all()
for topic in topics:
self.assertEqual(topic.status, Status.Ok)
finally:
db.close()
class UploadedMediaFile(db.Model):
id = db.Column(db.Integer(), primary_key=True)
state = db.Column(EnumType(UploadedMediaFileState, name="uploaded_media_file_state"))
tmp_path = db.Column(db.Text(), nullable=True)
media_file_id = db.Column(db.Integer(), db.ForeignKey("media_file.id"), nullable=True)
media_file = db.relationship("MediaFile", foreign_keys=[media_file_id])
def downgrade():
with op.batch_alter_table('third_party_user', naming_convention=convention) as batch_op:
batch_op.alter_column('third_party_type',
type_=EnumType(IntermediateThirdPartyType),
existing_type=EnumType(NewThirdPartyType))
connection = op.get_bind()
connection.execute(
intermediate_party_user_table.update()
.where(intermediate_party_user_table.c.third_party_type == IntermediateThirdPartyType.cas)
.values(third_party_type=IntermediateThirdPartyType.cwl)
)
with op.batch_alter_table('third_party_user', naming_convention=convention) as batch_op:
batch_op.alter_column('third_party_type',
type_=EnumType(OldThirdPartyType),
existing_type=EnumType(IntermediateThirdPartyType))
batch_op.drop_column('_params')
def upgradeEnum(
tableName,
colName,
oldEnum,
renameTmpEnum,
renameNewEnum
):
# convert column to the rename tmp enum
with op.batch_alter_table(tableName, naming_convention=convention) as batch_op:
# renameTmpEnum just tacks on a 'tmp_' prefix to the values, to avoid
# the case insensitivity duplicate issues
batch_op.alter_column(colName,
nullable=False,
type_=EnumType(renameTmpEnum),
existing_type=EnumType(oldEnum))
# update existing data from old enum to the tmp values in the tmp enum
conn = op.get_bind()
table = sa.table(tableName, sa.column(colName, sa.Enum(renameTmpEnum)))
for entry in oldEnum:
conn.execute(table.update()
.where(getattr(table.c, colName) == entry.value)
.values({colName: 'tmp_' + entry.name}))
# convert column to the rename new enum
with op.batch_alter_table(tableName, naming_convention=convention) as batch_op:
# renameNewEnum contains the new & tmp enum values
batch_op.alter_column(colName,
nullable=False,
type_=EnumType(renameNewEnum),
existing_type=EnumType(renameTmpEnum))
# update existing data from the tmp enum to new enum
conn = op.get_bind()
table = sa.table(tableName, sa.column(colName, sa.Enum(renameNewEnum)))
for entry in oldEnum:
conn.execute(table.update()
.where(getattr(table.c, colName) == 'tmp_' + entry.name)
.values({colName: entry.name}))
# finally, can set the column type to just the new enum
with op.batch_alter_table(tableName, naming_convention=convention) as batch_op:
batch_op.alter_column(colName,
nullable=False,
type_=sa.Enum(oldEnum),
existing_type=EnumType(renameNewEnum))
def downgradeEnum(
tableName,
colName,
oldEnum,
renameTmpEnum,
renameNewEnum
):
# undo the new enum conversion, change column to rename new enum
with op.batch_alter_table(tableName, naming_convention=convention) as batch_op:
# renameNewEnum contains the new & tmp enum values
batch_op.alter_column(colName,
nullable=False,
type_=EnumType(renameNewEnum),
existing_type=sa.Enum(oldEnum))
# undo rename to new enum, so we rename from new enum to tmp enum
conn = op.get_bind()
table = sa.table(tableName, sa.column(colName, sa.Enum(renameNewEnum)))
for entry in oldEnum:
conn.execute(table.update()
.where(getattr(table.c, colName) == entry.name)
.values({colName: 'tmp_' + entry.name}))
# convert column back to tmp enum
with op.batch_alter_table(tableName, naming_convention=convention) as batch_op:
batch_op.alter_column(colName,
nullable=False,
type_=EnumType(renameTmpEnum),
existing_type=EnumType(renameNewEnum))
conn = op.get_bind()
# undo rename to tmp enum, so we rename from tmp enum to old enum
table = sa.table(tableName, sa.column(colName, sa.Enum(renameTmpEnum)))
for entry in oldEnum:
conn.execute(table.update()
.where(getattr(table.c, colName) == 'tmp_' + entry.name)
.values({colName: entry.value}))
# can now ditch tmp enum and convert the column back to old enum
with op.batch_alter_table(tableName, naming_convention=convention) as batch_op:
batch_op.alter_column(colName,
nullable=False,
type_=EnumType(oldEnum),
existing_type=EnumType(renameTmpEnum))
def upgrade():
with op.batch_alter_table('assignment',
naming_convention=convention) as batch_op:
batch_op.add_column(
sa.Column('scoring_algorithm',
EnumType(ScoringAlgorithm),
nullable=True))
# set scoring algorithm to first answer score found for assignment (default elo if no scoring has occurred yet)
connection = op.get_bind()
assignment_table = sa.table(
'assignment', sa.column('id', sa.Integer),
sa.column('scoring_algorithm', EnumType(ScoringAlgorithm)))
answer_score_table = sa.table(
'answer_score', sa.column('id', sa.Integer),
sa.column('assignment_id', sa.Integer),
sa.column('scoring_algorithm', EnumType(ScoringAlgorithm)))
scoring_algorithm_assignment_ids = {
ScoringAlgorithm.comparative_judgement.value: [],
ScoringAlgorithm.elo.value: [],
ScoringAlgorithm.true_skill.value: []
}
for assignment in connection.execute(assignment_table.select()):
answer_scores = connection.execute(answer_score_table.select().where(
answer_score_table.c.assignment_id == assignment.id).limit(1))
scoring_algorithm = ScoringAlgorithm.elo
for answer_score in answer_scores:
scoring_algorithm = answer_score.scoring_algorithm
scoring_algorithm_assignment_ids[scoring_algorithm.value].append(
assignment.id)
for scoring_algorithm, assignment_ids in scoring_algorithm_assignment_ids.items(
):
if len(assignment_ids) > 0:
connection.execute(assignment_table.update().where(
assignment_table.c.id.in_(assignment_ids)).values(
scoring_algorithm=ScoringAlgorithm(scoring_algorithm)))
def upgrade():
with op.batch_alter_table('user',
naming_convention=convention) as batch_op:
batch_op.add_column(
sa.Column('email_notification_method',
EnumType(EmailNotificationMethod),
default=EmailNotificationMethod.enable,
server_default=EmailNotificationMethod.enable.value,
nullable=False))
op.create_index(op.f('ix_user_email_notification_method'),
'user', ['email_notification_method'],
unique=False)
class ComparisonCriterion(DefaultTableMixin, UUIDMixin, WriteTrackingMixin):
__tablename__ = 'comparison_criterion'
# table columns
comparison_id = db.Column(db.Integer, db.ForeignKey('comparison.id', ondelete="CASCADE"),
nullable=False)
criterion_id = db.Column(db.Integer, db.ForeignKey('criterion.id', ondelete="CASCADE"),
nullable=False)
winner = db.Column(EnumType(WinningAnswer, name="winner"), nullable=True)
content = db.Column(db.Text)
# relationships
# comparison via Comparison Model
# criterion via Criterion Model
# hyprid and other functions
criterion_uuid = association_proxy('criterion', 'uuid')
comparison_uuid = association_proxy('comparison', 'uuid')
answer1_id = association_proxy('comparison', 'answer1_id')
answer2_id = association_proxy('comparison', 'answer2_id')
answer1_uuid = association_proxy('comparison', 'answer1_uuid')
answer2_uuid = association_proxy('comparison', 'answer2_uuid')
@classmethod
def get_by_uuid_or_404(cls, model_uuid, joinedloads=[], title=None, message=None):
if not title:
title = "Criterion Unavailable"
if not message:
message = "Sorry, this criterion was deleted or is no longer accessible."
return super(cls, cls).get_by_uuid_or_404(model_uuid, joinedloads, title, message)
@classmethod
def __declare_last__(cls):
super(cls, cls).__declare_last__()
def comparison_pair_winner(self):
from . import WinningAnswer
winner = None
if self.winner == WinningAnswer.answer1:
winner = ComparisonWinner.key1
elif self.winner == WinningAnswer.answer2:
winner = ComparisonWinner.key2
return winner
def convert_to_comparison_pair(self):
return ComparisonPair(
key1=self.answer1_id,
key2=self.answer2_id,
winner=self.comparison_pair_winner()
)
class Link(Base):
__tablename__ = 'link'
url = Column(String(1022), primary_key=True)
depth = Column(Integer, nullable=False)
status = Column(EnumType(Status, name='url_status'),
nullable=False,
default=Status.new,
index=True)
def __init__(self, url, depth=1, status=Status.new):
self.url = url
self.depth = depth
self.status = status
def downgrade():
connection = op.get_bind()
for table_name in _table_names:
# first update the adaptive_min_delta algo to adaptive algo
table = sa.table(
table_name, sa.Column('id', sa.Integer()),
sa.Column('pairing_algorithm', EnumType(_NewPairingAlgorithm)))
stmt = update(table).\
where(table.c.pairing_algorithm == bindparam('from_algo')).\
values(pairing_algorithm = bindparam('to_algo'))
connection.execute(stmt, [{
'from_algo': _NewPairingAlgorithm.adaptive_min_delta,
'to_algo': _NewPairingAlgorithm.adaptive
}])
# then modify the enum type
with op.batch_alter_table(table_name,
naming_convention=convention) as batch_op:
batch_op.alter_column('pairing_algorithm',
type_=EnumType(_OldPairingAlgorithm),
existing_type=EnumType(_NewPairingAlgorithm),
existing_server_default=null(),
existing_nullable=True)
def upgrade():
with op.batch_alter_table('third_party_user',
naming_convention=convention) as batch_op:
batch_op.add_column(sa.Column('_params', sa.Text, nullable=True))
batch_op.alter_column('third_party_type',
type_=EnumType(IntermediateThirdPartyType,
name="third_party_type"),
existing_type=EnumType(OldThirdPartyType,
name="third_party_type"))
connection = op.get_bind()
connection.execute(intermediate_party_user_table.update().where(
intermediate_party_user_table.c.third_party_type ==
IntermediateThirdPartyType.cwl).values(
third_party_type=IntermediateThirdPartyType.cas))
with op.batch_alter_table('third_party_user',
naming_convention=convention) as batch_op:
batch_op.alter_column('third_party_type',
type_=EnumType(NewThirdPartyType,
name="third_party_type"),
existing_type=EnumType(
IntermediateThirdPartyType,
name="third_party_type"))
def upgrade():
op.add_column(
'score',
sa.Column('scoring_algorithm',
EnumType(ScoringAlgorithm, name='scoring_algorithm'),
nullable=True))
op.add_column('score', sa.Column('variable1', sa.Float(), nullable=True))
op.add_column('score', sa.Column('variable2', sa.Float(), nullable=True))
op.add_column(
'score',
sa.Column('loses',
sa.Integer(),
nullable=False,
default='0',
server_default='0'))
def upgrade():
with op.batch_alter_table('score',
naming_convention=convention) as batch_op:
batch_op.add_column(
sa.Column('scoring_algorithm',
EnumType(ScoringAlgorithm),
nullable=True))
batch_op.add_column(sa.Column('variable1', sa.Float(), nullable=True))
batch_op.add_column(sa.Column('variable2', sa.Float(), nullable=True))
batch_op.add_column(
sa.Column('loses',
sa.Integer(),
nullable=False,
default='0',
server_default='0'))
class Word(Base):
"""단어에 대한 정보를 저장하는 테이블"""
#: (:class:`uuid.UUID`) 고유 식별자.
id = Column(UUIDType, primary_key=True, default=uuid.uuid4)
#: (:class:`int`) 우리말샘 API 에서 사용되는 고유 식별자.
target_code = Column(Integer, unique=True)
#: (:class:`WordPart`) 단어의 품사
part = Column(EnumType(WordPart, name='word_part'), nullable=False)
#: (:class:`str`) 단어의 의미
contents = Column(Unicode, nullable=False)
#: (:class:`uuid.UUID`) 발음에 대한 고유 식별자.
pronunciation_id = Column(
UUIDType,
ForeignKey(Pronunciation.id),
nullable=False,
)
#: (:class:`Pronunciation`) 발음
pronunciation = relationship(
Pronunciation,
uselist=False,
back_populates='words',
)
word_relation = relationship(
'IncludeWordRelation',
uselist=True,
primaryjoin="IncludeWordRelation.criteria_id == Word.id",
back_populates='criteria_words',
)
@property
def related_include_pronunciations(self) -> typing.Sequence[str]:
pronunciation = []
for relation in self.word_relation:
pronunciation += [
p.pronunciation for p in relation.related_pronunciations
]
return pronunciation
__tablename__ = 'word'
class Topic(Base):
__tablename__ = 'topics'
id = Column(Integer, primary_key=True)
display_name = Column(String, unique=True, nullable=False)
url = Column(String, nullable=False, unique=True)
last_update = Column(UTCDateTime, nullable=True)
type = Column(String)
status = Column(EnumType(Status, by_name=True),
nullable=False,
server_default=Status.Ok.__str__())
__mapper_args__ = {
'polymorphic_identity': 'topic',
'polymorphic_on': type,
'with_polymorphic': '*',
'_polymorphic_map': TopicPolymorphicMap()
}
class Content(db.Model):
id = db.Column(db.Integer, primary_key=True)
json = db.Column(JsonEncodedDict)
contentType = db.Column(EnumType(ContentTypeEnum))
update_date = db.Column(db.DateTime)
watched = db.Column(db.Boolean)
plays = db.Column(db.Integer)
uniqueIds = db.relationship('UniqueId',
secondary=uniqueid_to_content,
backref='uniqueId',
lazy=True)
show_id = db.Column(db.Integer, db.ForeignKey('content.id'), nullable=True)
episodes = db.relationship('Content',
remote_side=[show_id],
lazy='dynamic')
user_id = db.Column(db.Integer, db.ForeignKey('user.id'), nullable=False)
def __init__(self, json, contentType, user_id, watched=False, show=None):
self.json = json
self.contentType = contentType
self.user_id = user_id
self.watched = watched
self.plays = 1 if watched else 0
self.show = show
def __repr__(self):
return '<Content %d, type=%s>' % (self.id, self.contentType)
def to_json(self):
result = copy.copy(self.json)
result.update({
'ids':
dict((uniqueId.source, uniqueId.value)
for uniqueId in list(self.uniqueIds))
})
result.update({'show_id': self.show_id})
result.update({'id': self.id})
result.update({'watched': self.watched})
return result
class WordRelation(Base):
"""
단어간의 관계를 나타내는 테이블.
`사랑`이라는 단어와 `애정`이라는 단어가 서로 유의어 관계일 경우,
`word_id`에는 `사랑`의 id가, `relation_word_id`에는 `애정`의 id가 들어가게 되며,
이때 `type`은 `synonyms`이 들어가게 됩니다.
"""
#: (:class:`uuid.UUID`) 고유 식별자.
id = Column(UUIDType, primary_key=True, default=uuid.uuid4)
#: (:class:`WordRelationType`) 단어간의 관계 종류
type = Column(
EnumType(WordRelationType, name='word_relation_type'),
nullable=False,
)
__mapper_args__ = {'polymorphic_on': type}
__tablename__ = 'word_relation'
class ThirdPartyUser(DefaultTableMixin, WriteTrackingMixin):
__tablename__ = 'third_party_user'
# table columns
third_party_type = db.Column(EnumType(ThirdPartyType,
name="third_party_type"),
nullable=False)
unique_identifier = db.Column(db.String(255), nullable=False)
user_id = db.Column(db.Integer,
db.ForeignKey("user.id", ondelete="CASCADE"),
nullable=False)
_params = db.Column(db.Text)
# relationships
# user via User Model
# hyprid and other functions
@property
def params(self):
return json.loads(self._params) if self._params else None
@params.setter
def params(self, params):
self._params = json.dumps(params) if params else None
@classmethod
def __declare_last__(cls):
super(cls, cls).__declare_last__()
__table_args__ = (
# prevent duplicate user in course
db.UniqueConstraint(
'third_party_type',
'unique_identifier',
name='_unique_third_party_type_and_unique_identifier'),
DefaultTableMixin.default_table_args)
class UserCourse(DefaultTableMixin, WriteTrackingMixin):
__tablename__ = 'user_course'
# table columns
user_id = db.Column(db.Integer,
db.ForeignKey("user.id", ondelete="CASCADE"),
nullable=False)
course_id = db.Column(db.Integer,
db.ForeignKey("course.id", ondelete="CASCADE"),
nullable=False)
group_id = db.Column(db.Integer,
db.ForeignKey('group.id', ondelete="SET NULL"),
nullable=True)
course_role = db.Column(EnumType(CourseRole), nullable=False, index=True)
# relationships
# user many-to-many course with association user_course
user = db.relationship("User",
foreign_keys=[user_id],
back_populates="user_courses")
course = db.relationship("Course", back_populates="user_courses")
group = db.relationship("Group", back_populates="user_courses")
# hybrid and other functions
user_uuid = association_proxy('user', 'uuid')
course_uuid = association_proxy('course', 'uuid')
@classmethod
def __declare_last__(cls):
super(cls, cls).__declare_last__()
__table_args__ = (
# prevent duplicate user in course
db.UniqueConstraint('course_id',
'user_id',
name='_unique_user_and_course'),
DefaultTableMixin.default_table_args)
class LTIUser(DefaultTableMixin, UUIDMixin, WriteTrackingMixin):
__tablename__ = 'lti_user'
# table columns
lti_consumer_id = db.Column(db.Integer, db.ForeignKey("lti_consumer.id", ondelete="CASCADE"),
nullable=False)
user_id = db.Column(db.String(191), nullable=False)
lis_person_name_given = db.Column(db.String(255), nullable=True)
lis_person_name_family = db.Column(db.String(255), nullable=True)
lis_person_name_full = db.Column(db.String(255), nullable=True)
lis_person_contact_email_primary = db.Column(db.String(255), nullable=True)
global_unique_identifier = db.Column(db.String(255), nullable=True)
compair_user_id = db.Column(db.Integer, db.ForeignKey("user.id", ondelete="CASCADE"),
nullable=True)
system_role = db.Column(EnumType(SystemRole), nullable=False)
student_number = db.Column(db.String(255), nullable=True)
lis_person_sourcedid = db.Column(db.String(255), nullable=True)
# relationships
# compair_user via User Model
# lti_consumer via LTIConsumer Model
lti_memberships = db.relationship("LTIMembership", backref="lti_user", lazy="dynamic")
lti_user_resource_links = db.relationship("LTIUserResourceLink", backref="lti_user", lazy="dynamic")
# hybrid and other functions
lti_consumer_uuid = association_proxy('lti_consumer', 'uuid')
oauth_consumer_key = association_proxy('lti_consumer', 'oauth_consumer_key')
compair_user_uuid = association_proxy('compair_user', 'uuid')
def is_linked_to_user(self):
return self.compair_user_id != None
def generate_or_link_user_account(self):
from . import SystemRole, User
if self.compair_user_id == None and self.global_unique_identifier:
self.compair_user = User.query \
.filter_by(global_unique_identifier=self.global_unique_identifier) \
.one_or_none()
if not self.compair_user:
self.compair_user = User(
username=None,
password=None,
system_role=self.system_role,
firstname=self.lis_person_name_given,
lastname=self.lis_person_name_family,
email=self.lis_person_contact_email_primary,
global_unique_identifier=self.global_unique_identifier
)
if self.compair_user.system_role == SystemRole.student:
self.compair_user.student_number = self.student_number
# instructors can have their display names set to their full name by default
if self.compair_user.system_role != SystemRole.student and self.compair_user.fullname != None:
self.compair_user.displayname = self.compair_user.fullname
else:
self.compair_user.displayname = display_name_generator(self.compair_user.system_role.value)
db.session.commit()
@classmethod
def get_by_lti_consumer_id_and_user_id(cls, lti_consumer_id, user_id):
return LTIUser.query \
.filter_by(
lti_consumer_id=lti_consumer_id,
user_id=user_id
) \
.one_or_none()
@classmethod
def get_by_tool_provider(cls, lti_consumer, tool_provider):
from . import SystemRole
if tool_provider.user_id == None:
return None
lti_user = LTIUser.get_by_lti_consumer_id_and_user_id(
lti_consumer.id, tool_provider.user_id)
if not lti_user:
lti_user = LTIUser(
lti_consumer_id=lti_consumer.id,
user_id=tool_provider.user_id,
system_role=SystemRole.instructor \
if tool_provider.roles and any(
role.lower().find("instructor") >= 0 or
role.lower().find("faculty") >= 0 or
role.lower().find("staff") >= 0
for role in tool_provider.roles
) \
else SystemRole.student
)
db.session.add(lti_user)
lti_user.lis_person_name_given = tool_provider.lis_person_name_given
lti_user.lis_person_name_family = tool_provider.lis_person_name_family
lti_user.lis_person_name_full = tool_provider.lis_person_name_full
lti_user.handle_fullname_with_missing_first_and_last_name()
lti_user.lis_person_contact_email_primary = tool_provider.lis_person_contact_email_primary
lti_user.lis_person_sourcedid = tool_provider.lis_person_sourcedid
if lti_consumer.global_unique_identifier_param and lti_consumer.global_unique_identifier_param in tool_provider.launch_params:
lti_user.global_unique_identifier = tool_provider.launch_params[lti_consumer.global_unique_identifier_param]
if lti_consumer.custom_param_regex_sanitizer and lti_consumer.global_unique_identifier_param.startswith('custom_'):
regex = re.compile(lti_consumer.custom_param_regex_sanitizer)
lti_user.global_unique_identifier = regex.sub('', lti_user.global_unique_identifier)
if lti_user.global_unique_identifier == '':
lti_user.global_unique_identifier = None
else:
lti_user.global_unique_identifier = None
if lti_consumer.student_number_param and lti_consumer.student_number_param in tool_provider.launch_params:
lti_user.student_number = tool_provider.launch_params[lti_consumer.student_number_param]
if lti_consumer.custom_param_regex_sanitizer and lti_consumer.student_number_param.startswith('custom_'):
regex = re.compile(lti_consumer.custom_param_regex_sanitizer)
lti_user.student_number = regex.sub('', lti_user.student_number)
if lti_user.student_number == '':
lti_user.student_number = None
else:
lti_user.student_number = None
if not lti_user.is_linked_to_user() and lti_user.global_unique_identifier:
lti_user.generate_or_link_user_account()
db.session.commit()
return lti_user
@classmethod
def get_by_uuid_or_404(cls, model_uuid, joinedloads=[], title=None, message=None):
if not title:
title = "LTI User Unavailable"
if not message:
message = "Sorry, this LTI user was deleted or is no longer accessible."
return super(cls, cls).get_by_uuid_or_404(model_uuid, joinedloads, title, message)
# relationships
def update_user_profile(self):
if self.compair_user and self.compair_user.system_role == SystemRole.student:
# overwrite first/last name if student not allowed to change it
if not current_app.config.get('ALLOW_STUDENT_CHANGE_NAME'):
self.compair_user.firstname = self.lis_person_name_given
self.compair_user.lastname = self.lis_person_name_family
# overwrite email if student not allowed to change it
if not current_app.config.get('ALLOW_STUDENT_CHANGE_EMAIL'):
self.compair_user.email = self.lis_person_contact_email_primary
# overwrite student number if student not allowed to change it and lti_consumer has a student_number_param
if not current_app.config.get('ALLOW_STUDENT_CHANGE_STUDENT_NUMBER') and self.lti_consumer.student_number_param:
self.compair_user.student_number = self.student_number
def upgrade_system_role(self):
# upgrade system role is needed
if self.compair_user:
if self.compair_user.system_role == SystemRole.student and self.system_role in [SystemRole.instructor, SystemRole.sys_admin]:
self.compair_user.system_role = self.system_role
elif self.compair_user.system_role == SystemRole.instructor and self.system_role == SystemRole.sys_admin:
self.compair_user.system_role = self.system_role
db.session.commit()
def handle_fullname_with_missing_first_and_last_name(self):
if self.lis_person_name_full and (not self.lis_person_name_given or not self.lis_person_name_family):
full_name_parts = self.lis_person_name_full.split(" ")
if len(full_name_parts) >= 2:
# assume lis_person_name_given is all but last part
self.lis_person_name_given = " ".join(full_name_parts[:-1])
self.lis_person_name_family = full_name_parts[-1]
else:
# not sure what is first or last name, just assignment both to full name
self.lis_person_name_given = self.lis_person_name_full
self.lis_person_name_family = self.lis_person_name_full
@classmethod
def __declare_last__(cls):
super(cls, cls).__declare_last__()
__table_args__ = (
# prevent duplicate resource link in consumer
db.UniqueConstraint('lti_consumer_id', 'user_id', name='_unique_lti_consumer_and_lti_user'),
DefaultTableMixin.default_table_args
)
class User(DefaultTableMixin, UUIDMixin, WriteTrackingMixin, UserMixin):
__tablename__ = 'user'
# table columns
global_unique_identifier = db.Column(
db.String(191),
nullable=True) #should be treated as write once and only once
username = db.Column(db.String(191), unique=True, nullable=True)
_password = db.Column(db.String(255), unique=False, nullable=True)
system_role = db.Column(EnumType(SystemRole), nullable=False, index=True)
displayname = db.Column(db.String(255), nullable=False)
email = db.Column(db.String(254),
nullable=True) # email addresses are max 254 characters
firstname = db.Column(db.String(255), nullable=True)
lastname = db.Column(db.String(255), nullable=True)
student_number = db.Column(db.String(50), unique=True, nullable=True)
last_online = db.Column(db.DateTime)
email_notification_method = db.Column(
EnumType(EmailNotificationMethod),
nullable=False,
default=EmailNotificationMethod.enable,
index=True)
# relationships
# user many-to-many course with association user_course
user_courses = db.relationship("UserCourse",
foreign_keys='UserCourse.user_id',
back_populates="user")
course_grades = db.relationship("CourseGrade",
foreign_keys='CourseGrade.user_id',
backref="user",
lazy='dynamic')
assignments = db.relationship("Assignment",
foreign_keys='Assignment.user_id',
backref="user",
lazy='dynamic')
assignment_grades = db.relationship("AssignmentGrade",
foreign_keys='AssignmentGrade.user_id',
backref="user",
lazy='dynamic')
answers = db.relationship("Answer",
foreign_keys='Answer.user_id',
backref="user",
lazy='dynamic')
answer_comments = db.relationship("AnswerComment",
foreign_keys='AnswerComment.user_id',
backref="user",
lazy='dynamic')
criteria = db.relationship("Criterion",
foreign_keys='Criterion.user_id',
backref="user",
lazy='dynamic')
files = db.relationship("File",
foreign_keys='File.user_id',
backref="user",
lazy='dynamic')
kaltura_files = db.relationship("KalturaMedia",
foreign_keys='KalturaMedia.user_id',
backref="user",
lazy='dynamic')
comparisons = db.relationship("Comparison",
foreign_keys='Comparison.user_id',
backref="user",
lazy='dynamic')
# third party authentification
third_party_auths = db.relationship("ThirdPartyUser",
foreign_keys='ThirdPartyUser.user_id',
backref="user",
lazy='dynamic')
# lti authentification
lti_user_links = db.relationship("LTIUser",
foreign_keys='LTIUser.compair_user_id',
backref="compair_user",
lazy='dynamic')
# hybrid and other functions
@property
def password(self):
return self._password
@password.setter
def password(self, password):
self._password = hash_password(password) if password != None else None
@hybrid_property
def fullname(self):
if self.firstname and self.lastname:
return '%s %s' % (self.firstname, self.lastname)
elif self.firstname: # only first name provided
return self.firstname
elif self.lastname: # only last name provided
return self.lastname
elif self.displayname:
return self.displayname
else:
return None
@hybrid_property
def fullname_sortable(self):
if self.firstname and self.lastname and self.system_role == SystemRole.student and self.student_number:
return '%s, %s (%s)' % (self.lastname, self.firstname,
self.student_number)
elif self.firstname and self.lastname:
return '%s, %s' % (self.lastname, self.firstname)
elif self.firstname: # only first name provided
return self.firstname
elif self.lastname: # only last name provided
return self.lastname
elif self.displayname:
return self.displayname
else:
return None
@hybrid_property
def avatar(self):
"""
According to gravatar's hash specs
1.Trim leading and trailing whitespace from an email address
2.Force all characters to lower-case
3.md5 hash the final string
"""
hash_input = None
if self.system_role != SystemRole.student and self.email:
hash_input = self.email
elif self.uuid:
hash_input = self.uuid + "@compair"
m = hashlib.md5()
m.update(hash_input.strip().lower().encode('utf-8'))
return m.hexdigest()
@hybrid_property
def uses_compair_login(self):
# third party auth users may have their username not set
return self.username != None and current_app.config['APP_LOGIN_ENABLED']
@hybrid_property
def lti_linked(self):
return self.lti_user_link_count > 0
@hybrid_property
def has_third_party_auth(self):
return self.third_party_auth_count > 0
def verify_password(self, password):
if self.password == None or not current_app.config['APP_LOGIN_ENABLED']:
return False
pwd_context = getattr(security, current_app.config['PASSLIB_CONTEXT'])
return pwd_context.verify(password, self.password)
def update_last_online(self):
self.last_online = datetime.utcnow()
db.session.add(self)
db.session.commit()
def generate_session_token(self):
"""
Generate a session token that identifies the user login session. Since the flask
wll generate the same session _id for the same IP and browser agent combination,
it is hard to distinguish the users by session from the activity log
"""
key = str(self.id) + '-' + str(time.time())
return hashlib.md5(key.encode('UTF-8')).hexdigest()
# This could be used for token based authentication
# def generate_auth_token(self, expiration=60):
# s = Serializer(current_app.config['SECRET_KEY'], expires_in=expiration)
# return s.dumps({'id': self.id})
@classmethod
def get_user_course_role(cls, user_id, course_id):
from . import UserCourse
user_course = UserCourse.query \
.filter_by(
course_id=course_id,
user_id=user_id
) \
.one_or_none()
return user_course.course_role if user_course else None
def get_course_role(self, course_id):
""" Return user's course role by course id """
for user_course in self.user_courses:
if user_course.course_id == course_id:
return user_course.course_role
return None
@classmethod
def get_user_course_group(cls, user_id, course_id):
from . import UserCourse
user_course = UserCourse.query \
.options(joinedload('group')) \
.filter_by(
course_id=course_id,
user_id=user_id
) \
.one_or_none()
return user_course.group if user_course else None
def get_course_group(self, course_id):
""" Return user's course group by course id """
for user_course in self.user_courses:
if user_course.course_id == course_id:
return user_course.group
return None
@classmethod
def get_by_uuid_or_404(cls,
model_uuid,
joinedloads=[],
title=None,
message=None):
if not title:
title = "User Unavailable"
if not message:
message = "Sorry, this user was deleted or is no longer accessible."
return super(cls, cls).get_by_uuid_or_404(model_uuid, joinedloads,
title, message)
@classmethod
def get_active_by_uuid_or_404(cls,
model_uuid,
joinedloads=[],
title=None,
message=None):
if not title:
title = "User Unavailable"
if not message:
message = "Sorry, this user was deleted or is no longer accessible."
return super(cls,
cls).get_active_by_uuid_or_404(model_uuid, joinedloads,
title, message)
@classmethod
def __declare_last__(cls):
from .lti_models import LTIUser
from . import ThirdPartyUser
super(cls, cls).__declare_last__()
cls.third_party_auth_count = column_property(select([
func.count(ThirdPartyUser.id)
]).where(ThirdPartyUser.user_id == cls.id).scalar_subquery(),
deferred=True,
group="counts")
cls.lti_user_link_count = column_property(select([
func.count(LTIUser.id)
]).where(LTIUser.compair_user_id == cls.id).scalar_subquery(),
deferred=True,
group="counts")
__table_args__ = (
# prevent duplicate user in course
db.UniqueConstraint('global_unique_identifier',
name='_unique_global_unique_identifier'),
DefaultTableMixin.default_table_args)
class Comparison(DefaultTableMixin, UUIDMixin, WriteTrackingMixin):
__tablename__ = 'comparison'
# table columns
assignment_id = db.Column(db.Integer,
db.ForeignKey('assignment.id',
ondelete="CASCADE"),
nullable=False)
user_id = db.Column(db.Integer,
db.ForeignKey('user.id', ondelete="CASCADE"),
nullable=False)
answer1_id = db.Column(db.Integer,
db.ForeignKey('answer.id', ondelete="CASCADE"),
nullable=False)
answer2_id = db.Column(db.Integer,
db.ForeignKey('answer.id', ondelete="CASCADE"),
nullable=False)
winner = db.Column(EnumType(WinningAnswer, name="winner"), nullable=True)
comparison_example_id = db.Column(db.Integer,
db.ForeignKey('comparison_example.id',
ondelete="SET NULL"),
nullable=True)
round_compared = db.Column(db.Integer, default=0, nullable=False)
completed = db.Column(db.Boolean(name='completed'),
default=False,
nullable=False,
index=True)
pairing_algorithm = db.Column(EnumType(PairingAlgorithm,
name="pairing_algorithm"),
nullable=True,
default=PairingAlgorithm.random)
# relationships
# assignment via Assignment Model
# user via User Model
# comparison_example via ComparisonExample Model
comparison_criteria = db.relationship("ComparisonCriterion",
backref="comparison",
lazy='immediate')
answer1 = db.relationship("Answer", foreign_keys=[answer1_id])
answer2 = db.relationship("Answer", foreign_keys=[answer2_id])
# hyprid and other functions
course_id = association_proxy(
'assignment',
'course_id',
creator=lambda course_id: import_module(
'compair.models.assignment').Assignment(course_id=course_id))
course_uuid = association_proxy('assignment', 'course_uuid')
assignment_uuid = association_proxy('assignment', 'uuid')
answer1_uuid = association_proxy('answer1', 'uuid')
answer2_uuid = association_proxy('answer2', 'uuid')
user_avatar = association_proxy('user', 'avatar')
user_uuid = association_proxy('user', 'uuid')
user_displayname = association_proxy('user', 'displayname')
user_fullname = association_proxy('user', 'fullname')
user_fullname_sortable = association_proxy('user', 'fullname_sortable')
user_system_role = association_proxy('user', 'system_role')
@classmethod
def get_by_uuid_or_404(cls,
model_uuid,
joinedloads=[],
title=None,
message=None):
if not title:
title = "Comparison Unavailable"
if not message:
message = "Sorry, this comparison was deleted or is no longer accessible."
return super(cls, cls).get_by_uuid_or_404(model_uuid, joinedloads,
title, message)
@classmethod
def __declare_last__(cls):
super(cls, cls).__declare_last__()
def comparison_pair_winner(self):
from . import WinningAnswer
winner = None
if self.winner == WinningAnswer.answer1:
winner = ComparisonWinner.key1
elif self.winner == WinningAnswer.answer2:
winner = ComparisonWinner.key2
elif self.winner == WinningAnswer.draw:
winner = ComparisonWinner.draw
return winner
def convert_to_comparison_pair(self):
return ComparisonPair(key1=self.answer1_id,
key2=self.answer2_id,
winner=self.comparison_pair_winner())
@classmethod
def _get_new_comparison_pair(cls, course_id, assignment_id, user_id,
pairing_algorithm, comparisons):
from . import Assignment, UserCourse, CourseRole, Answer, AnswerScore, PairingAlgorithm
# ineligible authors - eg. instructors, TAs, dropped student, current user
non_students = UserCourse.query \
.filter(and_(
UserCourse.course_id == course_id,
UserCourse.course_role != CourseRole.student
))
ineligible_user_ids = [non_student.user_id \
for non_student in non_students]
ineligible_user_ids.append(user_id)
answers_with_score = Answer.query \
.with_entities(Answer, AnswerScore.score ) \
.outerjoin(AnswerScore) \
.filter(and_(
Answer.user_id.notin_(ineligible_user_ids),
Answer.assignment_id == assignment_id,
Answer.active == True,
Answer.practice == False,
Answer.draft == False
)) \
.all()
scored_objects = []
for answer_with_score in answers_with_score:
scored_objects.append(
ScoredObject(key=answer_with_score.Answer.id,
score=answer_with_score.score,
rounds=answer_with_score.Answer.round,
variable1=None,
variable2=None,
wins=None,
loses=None,
opponents=None))
comparison_pairs = [
comparison.convert_to_comparison_pair()
for comparison in comparisons
]
comparison_pair = generate_pair(package_name=pairing_algorithm.value,
scored_objects=scored_objects,
comparison_pairs=comparison_pairs,
log=current_app.logger)
return comparison_pair
@classmethod
def create_new_comparison(cls, assignment_id, user_id,
skip_comparison_examples):
from . import Assignment, ComparisonExample, ComparisonCriterion
# get all comparisons for the user
comparisons = Comparison.query \
.filter_by(
user_id=user_id,
assignment_id=assignment_id
) \
.all()
is_comparison_example_set = False
answer1 = None
answer2 = None
comparison_example_id = None
round_compared = 0
assignment = Assignment.query.get(assignment_id)
pairing_algorithm = assignment.pairing_algorithm
if pairing_algorithm == None:
pairing_algorithm = PairingAlgorithm.random
if not skip_comparison_examples:
# check comparison examples first
comparison_examples = ComparisonExample.query \
.filter_by(
assignment_id=assignment_id,
active=True
) \
.all()
# check if user has not completed all comparison examples
for comparison_example in comparison_examples:
comparison = next(
(c for c in comparisons
if c.comparison_example_id == comparison_example.id),
None)
if comparison == None:
is_comparison_example_set = True
answer1 = comparison_example.answer1
answer2 = comparison_example.answer2
comparison_example_id = comparison_example.id
break
if not is_comparison_example_set:
comparison_pair = Comparison._get_new_comparison_pair(
assignment.course_id, assignment_id, user_id,
pairing_algorithm, comparisons)
answer1 = Answer.query.get(comparison_pair.key1)
answer2 = Answer.query.get(comparison_pair.key2)
round_compared = min(answer1.round + 1, answer2.round + 1)
# update round counters
answers = [answer1, answer2]
for answer in answers:
answer.round += 1
db.session.add(answer)
comparison = Comparison(assignment_id=assignment_id,
user_id=user_id,
answer1_id=answer1.id,
answer2_id=answer2.id,
winner=None,
round_compared=round_compared,
comparison_example_id=comparison_example_id,
pairing_algorithm=pairing_algorithm)
db.session.add(comparison)
for criterion in assignment.criteria:
comparison_criterion = ComparisonCriterion(
comparison=comparison,
criterion_id=criterion.id,
winner=None,
content=None,
)
db.session.commit()
return comparison
@classmethod
def update_scores_1vs1(cls, comparison):
from . import AnswerScore, AnswerCriterionScore, \
ComparisonCriterion, ScoringAlgorithm
assignment_id = comparison.assignment_id
answer1_id = comparison.answer1_id
answer2_id = comparison.answer2_id
# get all other comparisons for the answers not including the ones being calculated
other_comparisons = Comparison.query \
.options(load_only('winner', 'answer1_id', 'answer2_id')) \
.filter(and_(
Comparison.assignment_id == assignment_id,
Comparison.id != comparison.id,
or_(
Comparison.answer1_id.in_([answer1_id, answer2_id]),
Comparison.answer2_id.in_([answer1_id, answer2_id])
)
)) \
.all()
scores = AnswerScore.query \
.filter( AnswerScore.answer_id.in_([answer1_id, answer2_id]) ) \
.all()
# get all other criterion comparisons for the answers not including the ones being calculated
other_criterion_comparisons = ComparisonCriterion.query \
.join("comparison") \
.filter(and_(
Comparison.assignment_id == assignment_id,
~Comparison.id == comparison.id,
or_(
Comparison.answer1_id.in_([answer1_id, answer2_id]),
Comparison.answer2_id.in_([answer1_id, answer2_id])
)
)) \
.all()
criteria_scores = AnswerCriterionScore.query \
.filter( AnswerCriterionScore.answer_id.in_([answer1_id, answer2_id]) ) \
.all()
#update answer criterion scores
updated_criteria_scores = []
for comparison_criterion in comparison.comparison_criteria:
criterion_id = comparison_criterion.criterion_id
score1 = next((criterion_score
for criterion_score in criteria_scores
if criterion_score.answer_id == answer1_id
and criterion_score.criterion_id == criterion_id),
AnswerCriterionScore(assignment_id=assignment_id,
answer_id=answer1_id,
criterion_id=criterion_id))
updated_criteria_scores.append(score1)
key1_scored_object = score1.convert_to_scored_object(
) if score1 != None else ScoredObject(
key=answer1_id,
score=None,
variable1=None,
variable2=None,
rounds=0,
wins=0,
opponents=0,
loses=0,
)
score2 = next((criterion_score
for criterion_score in criteria_scores
if criterion_score.answer_id == answer2_id
and criterion_score.criterion_id == criterion_id),
AnswerCriterionScore(assignment_id=assignment_id,
answer_id=answer2_id,
criterion_id=criterion_id))
updated_criteria_scores.append(score2)
key2_scored_object = score2.convert_to_scored_object(
) if score2 != None else ScoredObject(
key=answer2_id,
score=None,
variable1=None,
variable2=None,
rounds=0,
wins=0,
opponents=0,
loses=0,
)
result_1, result_2 = calculate_score_1vs1(
package_name=ScoringAlgorithm.elo.value,
key1_scored_object=key1_scored_object,
key2_scored_object=key2_scored_object,
winner=comparison_criterion.comparison_pair_winner(),
other_comparison_pairs=[
c.convert_to_comparison_pair()
for c in other_criterion_comparisons
if c.criterion_id == criterion_id
],
log=current_app.logger)
for score, result in [(score1, result_1), (score2, result_2)]:
score.score = result.score
score.variable1 = result.variable1
score.variable2 = result.variable2
score.rounds = result.rounds
score.wins = result.wins
score.loses = result.loses
score.opponents = result.opponents
updated_scores = []
score1 = next(
(score for score in scores if score.answer_id == answer1_id),
AnswerScore(assignment_id=assignment_id, answer_id=answer1_id))
updated_scores.append(score1)
key1_scored_object = score1.convert_to_scored_object(
) if score1 != None else ScoredObject(
key=answer1_id,
score=None,
variable1=None,
variable2=None,
rounds=0,
wins=0,
opponents=0,
loses=0,
)
score2 = next(
(score for score in scores if score.answer_id == answer2_id),
AnswerScore(assignment_id=assignment_id, answer_id=answer2_id))
updated_scores.append(score2)
key2_scored_object = score2.convert_to_scored_object(
) if score2 != None else ScoredObject(
key=answer2_id,
score=None,
variable1=None,
variable2=None,
rounds=0,
wins=0,
opponents=0,
loses=0,
)
result_1, result_2 = calculate_score_1vs1(
package_name=ScoringAlgorithm.elo.value,
key1_scored_object=key1_scored_object,
key2_scored_object=key2_scored_object,
winner=comparison.comparison_pair_winner(),
other_comparison_pairs=[
c.convert_to_comparison_pair() for c in other_comparisons
],
log=current_app.logger)
for score, result in [(score1, result_1), (score2, result_2)]:
score.score = result.score
score.variable1 = result.variable1
score.variable2 = result.variable2
score.rounds = result.rounds
score.wins = result.wins
score.loses = result.loses
score.opponents = result.opponents
db.session.add_all(updated_criteria_scores)
db.session.add_all(updated_scores)
db.session.commit()
return updated_scores
@classmethod
def calculate_scores(cls, assignment_id):
from . import AnswerScore, AnswerCriterionScore, \
AssignmentCriterion, ScoringAlgorithm
# get all comparisons for this assignment and only load the data we need
comparisons = Comparison.query \
.filter(Comparison.assignment_id == assignment_id) \
.all()
assignment_criteria = AssignmentCriterion.query \
.with_entities(AssignmentCriterion.criterion_id) \
.filter_by(assignment_id=assignment_id, active=True) \
.all()
comparison_criteria = []
comparison_pairs = []
answer_ids = set()
for comparison in comparisons:
answer_ids.add(comparison.answer1_id)
answer_ids.add(comparison.answer2_id)
comparison_criteria.extend(comparison.comparison_criteria)
comparison_pairs.append(comparison.convert_to_comparison_pair())
# calculate answer score
comparison_results = calculate_score(
package_name=ScoringAlgorithm.elo.value,
comparison_pairs=comparison_pairs,
log=current_app.logger)
scores = AnswerScore.query \
.filter(AnswerScore.answer_id.in_(answer_ids)) \
.all()
updated_answer_scores = update_answer_scores(scores, assignment_id,
comparison_results)
db.session.add_all(updated_answer_scores)
# calculate answer criterion scores
criterion_comparison_results = {}
for assignment_criterion in assignment_criteria:
comparison_pairs = []
for comparison_criterion in comparison_criteria:
if comparison_criterion.criterion_id != assignment_criterion.criterion_id:
continue
comparison_pairs.append(
comparison_criterion.convert_to_comparison_pair())
criterion_comparison_results[
assignment_criterion.criterion_id] = calculate_score(
package_name=ScoringAlgorithm.elo.value,
comparison_pairs=comparison_pairs,
log=current_app.logger)
scores = AnswerCriterionScore.query \
.filter(AnswerCriterionScore.answer_id.in_(answer_ids)) \
.all()
updated_answer_criteria_scores = update_answer_criteria_scores(
scores, assignment_id, criterion_comparison_results)
db.session.add_all(updated_answer_criteria_scores)
db.session.commit()
def upgrade():
# Rename score table to answer_criterion_score
with op.batch_alter_table('comparison',
naming_convention=convention) as batch_op:
batch_op.drop_constraint(
'fk_comparison_comparison_example_id_comparison_example',
'foreignkey')
batch_op.drop_index('ix_comparison_completed')
batch_op.drop_constraint("uq_comparison_uuid", type_='unique')
try:
# expected foreign key to follow naming conventions
with op.batch_alter_table('comparison',
naming_convention=convention) as batch_op:
# drop the fk before altering the column
batch_op.drop_constraint('fk_comparison_assignment_id_assignment',
'foreignkey')
batch_op.drop_constraint('fk_comparison_user_id_user',
'foreignkey')
batch_op.drop_constraint('fk_comparison_criterion_id_criterion',
'foreignkey')
batch_op.drop_constraint('fk_comparison_answer1_id_answer',
'foreignkey')
batch_op.drop_constraint('fk_comparison_answer2_id_answer',
'foreignkey')
batch_op.drop_constraint('fk_comparison_modified_user_id_user',
'foreignkey')
batch_op.drop_constraint('fk_comparison_created_user_id_user',
'foreignkey')
except sa.exc.InternalError:
# if not, it is likely this name
with op.batch_alter_table('comparison') as batch_op:
# drop the fk before altering the column
batch_op.drop_constraint('comparison_ibfk_1', 'foreignkey')
batch_op.drop_constraint('comparison_ibfk_2', 'foreignkey')
batch_op.drop_constraint('comparison_ibfk_3', 'foreignkey')
batch_op.drop_constraint('comparison_ibfk_4', 'foreignkey')
batch_op.drop_constraint('comparison_ibfk_5', 'foreignkey')
batch_op.drop_constraint('comparison_ibfk_7', 'foreignkey')
batch_op.drop_constraint('comparison_ibfk_8', 'foreignkey')
# remname comparison table comparison_temp
op.rename_table('comparison', 'comparison_temp')
# create new tables
comparison_table = op.create_table(
'comparison',
sa.Column('id', sa.Integer(), nullable=False),
sa.Column('uuid', sa.CHAR(length=22), nullable=False),
sa.Column('assignment_id', sa.Integer(), nullable=False),
sa.Column('user_id', sa.Integer(), nullable=False),
sa.Column('answer1_id', sa.Integer(), nullable=False),
sa.Column('answer2_id', sa.Integer(), nullable=False),
sa.Column('winner', EnumType(WinningAnswer), nullable=True),
sa.Column('comparison_example_id', sa.Integer(), nullable=True),
sa.Column('round_compared', sa.Integer(), nullable=False),
sa.Column('completed', sa.Boolean(), nullable=False),
sa.Column('pairing_algorithm',
EnumType(PairingAlgorithm),
nullable=True),
sa.Column('modified_user_id', sa.Integer(), nullable=True),
sa.Column('modified', sa.DateTime(), nullable=False),
sa.Column('created_user_id', sa.Integer(), nullable=True),
sa.Column('created', sa.DateTime(), nullable=False),
sa.ForeignKeyConstraint(['answer1_id'], ['answer.id'],
ondelete='CASCADE'),
sa.ForeignKeyConstraint(['answer2_id'], ['answer.id'],
ondelete='CASCADE'),
sa.ForeignKeyConstraint(['assignment_id'], ['assignment.id'],
ondelete='CASCADE'),
sa.ForeignKeyConstraint(['comparison_example_id'],
['comparison_example.id'],
ondelete='SET NULL'),
sa.ForeignKeyConstraint(['created_user_id'], ['user.id'],
ondelete='SET NULL'),
sa.ForeignKeyConstraint(['modified_user_id'], ['user.id'],
ondelete='SET NULL'),
sa.ForeignKeyConstraint(['user_id'], ['user.id'], ondelete='CASCADE'),
sa.PrimaryKeyConstraint('id'),
sa.UniqueConstraint('uuid'),
mysql_charset='utf8',
mysql_collate='utf8_unicode_ci',
mysql_engine='InnoDB')
op.create_index(op.f('ix_comparison_completed'),
'comparison', ['completed'],
unique=False)
comparison_criterion_table = op.create_table(
'comparison_criterion',
sa.Column('id', sa.Integer(), nullable=False),
sa.Column('uuid', sa.CHAR(length=22), nullable=False),
sa.Column('comparison_id', sa.Integer(), nullable=False),
sa.Column('criterion_id', sa.Integer(), nullable=False),
sa.Column('winner', EnumType(WinningAnswer), nullable=True),
sa.Column('content', sa.Text(), nullable=True),
sa.Column('modified_user_id', sa.Integer(), nullable=True),
sa.Column('modified', sa.DateTime(), nullable=False),
sa.Column('created_user_id', sa.Integer(), nullable=True),
sa.Column('created', sa.DateTime(), nullable=False),
sa.ForeignKeyConstraint(['comparison_id'], ['comparison.id'],
ondelete='CASCADE'),
sa.ForeignKeyConstraint(['created_user_id'], ['user.id'],
ondelete='SET NULL'),
sa.ForeignKeyConstraint(['criterion_id'], ['criterion.id'],
ondelete='CASCADE'),
sa.ForeignKeyConstraint(['modified_user_id'], ['user.id'],
ondelete='SET NULL'),
sa.PrimaryKeyConstraint('id'),
sa.UniqueConstraint('uuid'),
mysql_charset='utf8',
mysql_collate='utf8_unicode_ci',
mysql_engine='InnoDB')
# migrate data
connection = op.get_bind()
comparison_temp_table = sa.table(
'comparison_temp',
sa.column('id', sa.Integer),
sa.column('uuid', sa.CHAR(22)),
sa.Column('assignment_id', sa.Integer),
sa.Column('user_id', sa.Integer),
sa.Column('criterion_id', sa.Integer),
sa.Column('answer1_id', sa.Integer),
sa.Column('answer2_id', sa.Integer),
sa.Column('winner_id', sa.Integer),
sa.Column('comparison_example_id', sa.Integer),
sa.Column('round_compared', sa.Integer),
sa.Column('content', sa.Text),
sa.Column('completed', sa.Boolean),
sa.Column('pairing_algorithm', EnumType(PairingAlgorithm)),
sa.Column('modified_user_id', sa.Integer),
sa.Column('created_user_id', sa.Integer),
sa.Column('modified', sa.DateTime),
sa.Column('created', sa.DateTime),
)
# key is assignment_id+user_id+answer1_id+answer2_id
# data is criteria wins per answer
# if record pre-exists in this hash, then do no add new comparison table row
comparison_aggregate_data = {}
chunk_size = 5000
select_result = connection.execution_options(stream_results=True).execute(
comparison_temp_table.select())
chunk = select_result.fetchmany(chunk_size)
while chunk:
for comparison in chunk:
key = str(comparison.assignment_id) + "+" + str(
comparison.user_id) + "+" + str(
comparison.answer1_id) + "+" + str(comparison.answer2_id)
comparison_data = comparison_aggregate_data.get(key)
modified = comparison.modified if comparison.modified else datetime.utcnow(
)
created = comparison.created if comparison.created else datetime.utcnow(
)
winner = None
if comparison.completed:
if comparison.winner_id == comparison.answer1_id:
winner = WinningAnswer.answer1
elif comparison.winner_id == comparison.answer2_id:
winner = WinningAnswer.answer2
if not comparison_data:
# populate comparison table
result = connection.execute(comparison_table.insert().values(
uuid=base64.urlsafe_b64encode(
uuid.uuid4().bytes).decode('ascii').replace('=', ''),
assignment_id=comparison.assignment_id,
user_id=comparison.user_id,
answer1_id=comparison.answer1_id,
answer2_id=comparison.answer2_id,
winner=None, #to be decided after talling all comparisons
comparison_example_id=comparison.comparison_example_id,
round_compared=comparison.round_compared,
completed=comparison.completed,
pairing_algorithm=comparison.pairing_algorithm,
modified=modified,
created=created))
comparison_data = {
'comparison_id': result.lastrowid,
'completed': comparison.completed
}
if comparison.completed:
comparison_data['answer1'] = 0
comparison_data['answer2'] = 0
comparison_aggregate_data[key] = comparison_data
if winner == WinningAnswer.answer1:
comparison_data['answer1'] += 1
elif winner == WinningAnswer.answer2:
comparison_data['answer2'] += 1
# populate comparison_criterion table
connection.execute(comparison_criterion_table.insert().values(
uuid=comparison.uuid,
comparison_id=comparison_data.get('comparison_id'),
criterion_id=comparison.criterion_id,
winner=winner,
content=comparison.content,
modified=modified,
created=created))
# fetch next chunk
chunk = select_result.fetchmany(chunk_size)
# only update completed comparisons
for key, comparison_data in comparison_aggregate_data.items():
if comparison_data.get('completed'):
comparison_id = comparison_data.get('comparison_id')
answer1 = comparison_data.get('answer1')
answer2 = comparison_data.get('answer2')
winner = WinningAnswer.draw
if answer1 > answer2:
winner = WinningAnswer.answer1
elif answer2 > answer1:
winner = WinningAnswer.answer2
connection.execute(comparison_table.update().where(
comparison_table.c.id == comparison_id).values(winner=winner))
# drop old data table
op.drop_table('comparison_temp')
def downgrade():
# expected foreign key to follow naming conventions
with op.batch_alter_table('comparison',
naming_convention=convention) as batch_op:
# drop the fk before altering the column
batch_op.drop_constraint('fk_comparison_assignment_id_assignment',
'foreignkey')
batch_op.drop_constraint('fk_comparison_user_id_user', 'foreignkey')
batch_op.drop_constraint('fk_comparison_answer1_id_answer',
'foreignkey')
batch_op.drop_constraint('fk_comparison_answer2_id_answer',
'foreignkey')
batch_op.drop_constraint('fk_comparison_modified_user_id_user',
'foreignkey')
batch_op.drop_constraint('fk_comparison_created_user_id_user',
'foreignkey')
batch_op.drop_constraint(
'fk_comparison_comparison_example_id_comparison_example',
'foreignkey')
batch_op.drop_index('ix_comparison_completed')
batch_op.drop_constraint("uq_comparison_uuid", type_='unique')
# remname comparison_temp table comparison
op.rename_table('comparison', 'comparison_temp')
# create old comparison table
comparison_table = op.create_table(
'comparison',
sa.Column('id', sa.Integer(), nullable=False),
sa.Column('uuid', sa.CHAR(22), nullable=False),
sa.Column('assignment_id', sa.Integer(), nullable=False),
sa.Column('user_id', sa.Integer(), nullable=False),
sa.Column('criterion_id', sa.Integer(), nullable=False),
sa.Column('answer1_id', sa.Integer(), nullable=False),
sa.Column('answer2_id', sa.Integer(), nullable=False),
sa.Column('winner_id', sa.Integer(), nullable=True),
sa.Column('comparison_example_id', sa.Integer(), nullable=True),
sa.Column('round_compared', sa.Integer(), nullable=False),
sa.Column('content', sa.Text(), nullable=True),
sa.Column('completed', sa.Boolean(), nullable=False),
sa.Column('pairing_algorithm',
EnumType(PairingAlgorithm),
nullable=True),
sa.Column('modified_user_id', sa.Integer(), nullable=True),
sa.Column('modified', sa.DateTime(), nullable=False),
sa.Column('created_user_id', sa.Integer(), nullable=True),
sa.Column('created', sa.DateTime(), nullable=False),
sa.ForeignKeyConstraint(['answer1_id'], ['answer.id'],
ondelete='CASCADE'),
sa.ForeignKeyConstraint(['answer2_id'], ['answer.id'],
ondelete='CASCADE'),
sa.ForeignKeyConstraint(['assignment_id'], ['assignment.id'],
ondelete='CASCADE'),
sa.ForeignKeyConstraint(['comparison_example_id'],
['comparison_example.id'],
ondelete='SET NULL'),
sa.ForeignKeyConstraint(['created_user_id'], ['user.id'],
ondelete='SET NULL'),
sa.ForeignKeyConstraint(['criterion_id'], ['criterion.id'],
ondelete='CASCADE'),
sa.ForeignKeyConstraint(['modified_user_id'], ['user.id'],
ondelete='SET NULL'),
sa.ForeignKeyConstraint(['user_id'], ['user.id'], ondelete='CASCADE'),
sa.ForeignKeyConstraint(['winner_id'], ['answer.id'],
ondelete='CASCADE'),
sa.PrimaryKeyConstraint('id'),
sa.UniqueConstraint('uuid'),
mysql_collate='utf8_unicode_ci',
mysql_default_charset='utf8',
mysql_engine='InnoDB')
op.create_index(op.f('ix_comparison_completed'),
'comparison', ['completed'],
unique=False)
# migrate data
connection = op.get_bind()
comparison_temp_table = sa.table(
'comparison_temp',
sa.column('id', sa.Integer),
sa.Column('assignment_id', sa.Integer),
sa.Column('user_id', sa.Integer),
sa.Column('answer1_id', sa.Integer),
sa.Column('answer2_id', sa.Integer),
sa.Column('comparison_example_id', sa.Integer),
sa.Column('round_compared', sa.Integer),
sa.Column('completed', sa.Boolean),
sa.Column('pairing_algorithm', EnumType(PairingAlgorithm)),
sa.Column('modified_user_id', sa.Integer),
sa.Column('created_user_id', sa.Integer),
sa.Column('modified', sa.DateTime),
sa.Column('created', sa.DateTime),
)
comparison_criterion_table = sa.table(
'comparison_criterion',
sa.column('id', sa.Integer),
sa.column('uuid', sa.CHAR(22)),
sa.Column('comparison_id', sa.Integer),
sa.Column('criterion_id', sa.Integer),
sa.Column('winner', EnumType(WinningAnswer)),
sa.Column('content', sa.Text),
sa.Column('modified_user_id', sa.Integer),
sa.Column('created_user_id', sa.Integer),
sa.Column('modified', sa.DateTime),
sa.Column('created', sa.DateTime),
)
chunk_size = 5000
select_result = connection.execution_options(stream_results=True).execute(
comparison_criterion_table.select())
chunk = select_result.fetchmany(chunk_size)
while chunk:
for comparison_criterion in chunk:
comparison = None
comparisons = connection.execute(
comparison_temp_table.select().where(
comparison_temp_table.c.id ==
comparison_criterion.comparison_id))
for c in comparisons:
comparison = c
if comparison == None:
continue
modified = comparison_criterion.modified if comparison_criterion.modified else datetime.utcnow(
)
created = comparison_criterion.created if comparison_criterion.created else datetime.utcnow(
)
winner_id = None
if comparison_criterion.winner == WinningAnswer.answer1:
winner_id = comparison.answer1_id
elif comparison_criterion.winner == WinningAnswer.answer2:
winner_id = comparison.answer2_id
# populate comparison table
connection.execute(comparison_table.insert().values(
uuid=comparison_criterion.uuid,
assignment_id=comparison.assignment_id,
user_id=comparison.user_id,
criterion_id=comparison_criterion.criterion_id,
answer1_id=comparison.answer1_id,
answer2_id=comparison.answer2_id,
winner_id=winner_id,
comparison_example_id=comparison.comparison_example_id,
round_compared=comparison.round_compared,
content=comparison_criterion.content,
completed=comparison.completed,
pairing_algorithm=comparison.pairing_algorithm,
modified=modified,
created=created))
# fetch next chunk
chunk = select_result.fetchmany(chunk_size)
# drop new tables
op.drop_table('comparison_criterion')
op.drop_table('comparison_temp')
