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')