def refresh_from_staging(inclusions_select): # Refuse to follow through if the staging table does not appear to have been loaded. count = JsonCacheStaging.query.filter(text(inclusions_select)).count() std_commit() if count == 0: app.logger.warn(f'Will not refresh; staging cache has no matches for {inclusions_select}') return 0 app.logger.info(f'Will refresh cache from {count} matches on {inclusions_select}') started = time.perf_counter() sql = f""" BEGIN; SET LOCAL lock_timeout = '10s'; LOCK TABLE json_cache; DELETE FROM json_cache WHERE {inclusions_select}; INSERT INTO json_cache SELECT * FROM json_cache_staging WHERE {inclusions_select}; COMMIT; """ try: db.engine.connect().execute(text(sql)) # Like some other DDL commands, 'VACUUM' can only be managed by psycopg2 if the connection # isolation level is set to autocommit. SQLalchemy's autocommit flag is not sufficient. sql = 'VACUUM ANALYZE json_cache' db.engine.connect().execution_options(isolation_level='AUTOCOMMIT').execute(text(sql)) elapsed = round(time.perf_counter() - started, 2) app.logger.info(f'JSON Cache refreshed and vacuumed in {elapsed} secs') log_table_sizes() return count except SQLAlchemyError as err: app.logger.error(f'SQL {sql} threw {err}') return 0
def create_or_update_membership( cls, university_dept, authorized_user, is_advisor, is_director, is_scheduler, automate_membership=True, ): dept_id = university_dept.id user_id = authorized_user.id existing_membership = cls.query.filter_by( university_dept_id=dept_id, authorized_user_id=user_id).first() if existing_membership: membership = existing_membership membership.is_advisor = is_advisor membership.is_director = is_director membership.is_scheduler = is_scheduler membership.automate_membership = automate_membership else: membership = cls( is_advisor=is_advisor, is_director=is_director, is_scheduler=is_scheduler, automate_membership=automate_membership, ) membership.authorized_user = authorized_user membership.university_dept = university_dept authorized_user.department_memberships.append(membership) university_dept.authorized_users.append(membership) db.session.add(membership) std_commit() return membership
def mock_note_template(app, db): """Create advising note template with attachment (mock s3).""" with mock_advising_note_s3_bucket(app): base_dir = app.config['BASE_DIR'] path_to_file = f'{base_dir}/fixtures/mock_note_template_attachment_1.txt' timestamp = datetime.now().timestamp() with open(path_to_file, 'r') as file: note_template = NoteTemplate.create( creator_id=AuthorizedUser.get_id_per_uid('242881'), title=f'Potholes in my lawn ({timestamp})', subject= f'It\'s unwise to leave my garden untended ({timestamp})', body=f""" See, I've found that everyone's sayin' What to do when suckers are preyin' """, topics=['Three Feet High', 'Rising'], attachments=[ { 'name': path_to_file.rsplit('/', 1)[-1], 'byte_stream': file.read(), }, ], ) std_commit(allow_test_environment=True) return note_template
def activate(self, preserve_creation_date=False): self.deleted_at = None # Some alert types, such as withdrawals and midpoint deficient grades, don't include a time-shifted message # and shouldn't be treated as updated after creation. if preserve_creation_date: self.updated_at = self.created_at std_commit()
def create(cls, creator_id, subject, title, attachments=(), body='', is_private=False, topics=()): creator = AuthorizedUser.find_by_id(creator_id) if creator: note_template = cls(body=body, creator_id=creator_id, is_private=is_private, subject=subject, title=title) for topic in topics: note_template.topics.append( NoteTemplateTopic.create( note_template.id, titleize(vacuum_whitespace(topic))), ) for byte_stream_bundle in attachments: note_template.attachments.append( NoteTemplateAttachment.create( note_template_id=note_template.id, name=byte_stream_bundle['name'], byte_stream=byte_stream_bundle['byte_stream'], uploaded_by=creator.uid, ), ) db.session.add(note_template) std_commit() return note_template
def refresh_alert_counts_for_owner(cls, owner_id): query = text(f""" UPDATE cohort_filters SET alert_count = updated_cohort_counts.alert_count FROM ( SELECT cohort_filters.id AS cohort_filter_id, count(*) AS alert_count FROM alerts JOIN cohort_filters ON alerts.sid = ANY(cohort_filters.sids) AND alerts.key LIKE :key AND alerts.active IS TRUE JOIN cohort_filter_owners ON cohort_filters.id = cohort_filter_owners.cohort_filter_id AND cohort_filter_owners.user_id = :owner_id LEFT JOIN alert_views ON alert_views.alert_id = alerts.id AND alert_views.viewer_id = :owner_id WHERE alert_views.dismissed_at IS NULL GROUP BY cohort_filters.id ) updated_cohort_counts WHERE cohort_filters.id = updated_cohort_counts.cohort_filter_id """) result = db.session.execute(query, { 'owner_id': owner_id, 'key': current_term_id() + '_%' }) std_commit() return result
def load_csv(cls, filename): with open(filename) as csvfile: reader = csv.DictReader(csvfile) for csvrow in reader: record = cls(**csvrow) db.session.add(record) std_commit()
def test_removes_coe_advisors(self, app): """Removes COE advisors not found in the loch.""" dept_coe = UniversityDept.query.filter_by(dept_code='COENG').first() bad_user = AuthorizedUser.create_or_restore(uid='666', created_by='2040') UniversityDeptMember.create_or_update_membership( dept_coe.id, bad_user.id, role='advisor', ) std_commit(allow_test_environment=True) coe_users = [au.authorized_user for au in dept_coe.authorized_users] coe_user_count = len(coe_users) assert coe_user_count assert next(u for u in coe_users if u.uid == '666') assert AuthorizedUser.query.filter_by( uid='666').first().deleted_at is None from boac.api.cache_utils import refresh_department_memberships refresh_department_memberships() std_commit(allow_test_environment=True) coe_users = [au.authorized_user for au in dept_coe.authorized_users] assert len(coe_users) == coe_user_count - 1 assert next((u for u in coe_users if u.uid == '666'), None) is None assert AuthorizedUser.query.filter_by(uid='666').first().deleted_at
def test_allows_advisor_to_change_departments(self): """Updates membership for a former CoE advisor who switches to L&S.""" user = AuthorizedUser.find_by_uid('242881') dept_coe = UniversityDept.query.filter_by(dept_code='COENG').first() UniversityDeptMember.create_or_update_membership( dept_coe.id, user.id, role='advisor', ) dept_ucls = UniversityDept.query.filter_by( dept_code='QCADVMAJ').first() UniversityDeptMember.delete_membership(dept_ucls.id, user.id) std_commit(allow_test_environment=True) ucls_users = [au.authorized_user for au in dept_ucls.authorized_users] assert len(ucls_users) == 2 from boac.api.cache_utils import refresh_department_memberships refresh_department_memberships() std_commit(allow_test_environment=True) ucls_users = [au.authorized_user for au in dept_ucls.authorized_users] assert len(ucls_users) == 3 assert next(u for u in ucls_users if u.uid == '242881') updated_user = AuthorizedUser.query.filter_by(uid='242881').first() assert updated_user.deleted_at is None assert updated_user.created_by == '0' assert updated_user.department_memberships[ 0].university_dept_id == dept_ucls.id
def test_adds_coe_advisors(self, app): """Adds COE advisors newly found in the loch.""" # Note: You will not find this UID in development_db (test data setup). It is seeded in loch.sql test data. coe_uid = '1234567' if AuthorizedUser.find_by_uid(coe_uid): AuthorizedUser.query.filter_by(uid=coe_uid).delete() std_commit(allow_test_environment=True) dept_coe = UniversityDept.query.filter_by(dept_code='COENG').first() coe_users = [au.authorized_user for au in dept_coe.authorized_users] assert len(coe_users) assert next((u for u in coe_users if u.uid == coe_uid), None) is None from boac.api.cache_utils import refresh_department_memberships refresh_department_memberships() std_commit(allow_test_environment=True) coe_users = [au.authorized_user for au in dept_coe.authorized_users] assert next((u for u in coe_users if u.uid == coe_uid), None) user = AuthorizedUser.query.filter_by(uid=coe_uid).first() assert user.can_access_canvas_data is False assert user.can_access_advising_data is False assert user.degree_progress_permission == 'read' assert user.deleted_at is None assert user.created_by == '0' assert user.department_memberships[0].automate_membership is True
def test_restores_coe_advisors(self, app): """Restores previously deleted COE advisors found in the loch.""" deleted_user = AuthorizedUser.delete(uid=coe_advisor_uid) UniversityDeptMember.query.filter_by( authorized_user_id=deleted_user.id).delete() std_commit(allow_test_environment=True) dept_coe = UniversityDept.find_by_dept_code(dept_code='COENG') coe_users = [au.authorized_user for au in dept_coe.authorized_users] coe_user_count = len(coe_users) assert coe_user_count assert next( (u for u in coe_users if u.uid == coe_advisor_uid), None) is None from boac.api.cache_utils import refresh_department_memberships refresh_department_memberships() std_commit(allow_test_environment=True) coe_users = [au.authorized_user for au in dept_coe.authorized_users] assert len(coe_users) == coe_user_count + 1 assert next(u for u in coe_users if u.uid == coe_advisor_uid) user = AuthorizedUser.find_by_uid(uid=coe_advisor_uid, ignore_deleted=False) assert user.can_access_canvas_data is False assert user.can_access_advising_data is False # And degree_progress_permission persists assert user.degree_progress_permission == 'read_write' assert user.deleted_at is None assert user.created_by == '0' assert user.department_memberships[0].automate_membership is True
def test_delete(self, client, fake_auth, mock_template): """COE advisor can delete degree category.""" fake_auth.login(coe_advisor_read_write_uid) original_updated_at = mock_template.updated_at category = _api_create_category( category_type='Category', client=client, name='Blister in the sun', position=3, template_id=mock_template.id, ) subcategory = _api_create_category( category_type='Subcategory', client=client, name='Gone Daddy Gone', parent_category_id=category['id'], position=3, template_id=mock_template.id, ) course = _api_create_category( category_type='Course Requirement', client=client, name='Blister in the sun', parent_category_id=subcategory['id'], position=3, template_id=mock_template.id, ) category_id = category['id'] assert client.delete(f'/api/degree/category/{category_id}').status_code == 200 # Verify that all were deleted. for object_id in (category_id, subcategory['id'], course['id']): assert client.get(f'/api/degree/category/{object_id}').status_code == 404 # Verify update of updated_at std_commit(allow_test_environment=True) assert DegreeProgressTemplate.find_by_id(mock_template.id).updated_at != original_updated_at
def refresh_department_memberships(): from boac.models.authorized_user import AuthorizedUser from boac.models.authorized_user_extension import DropInAdvisor, SameDayAdvisor, Scheduler from boac.models.university_dept import UniversityDept from boac.models.university_dept_member import UniversityDeptMember depts = UniversityDept.query.all() for dept in depts: dept.delete_automated_members() std_commit(allow_test_environment=True) for dept in depts: for membership in dept.memberships_from_loch(): # A non-numeric "uid" indicates a row from SIS advising tables best ignored. if not re.match(r'^\d+$', membership['uid']): continue user = AuthorizedUser.create_or_restore( uid=membership['uid'], created_by='0', can_access_advising_data=membership['can_access_advising_data'], can_access_canvas_data=membership['can_access_canvas_data'], degree_progress_permission=membership['degree_progress_permission'], ) if user: UniversityDeptMember.create_or_update_membership( university_dept_id=dept.id, authorized_user_id=user.id, role='advisor', ) DropInAdvisor.delete_orphans() SameDayAdvisor.delete_orphans() Scheduler.delete_orphans()
def _create_curated_groups(): admin_user = AuthorizedUser.find_by_uid('2040') CuratedGroup.create(admin_user.id, 'My Students') asc_advisor = AuthorizedUser.find_by_uid('6446') CuratedGroup.create(asc_advisor.id, 'My Students') curated_group = CuratedGroup.create(asc_advisor.id, 'Four students') CuratedGroup.add_student(curated_group.id, '3456789012') CuratedGroup.add_student(curated_group.id, '5678901234') CuratedGroup.add_student(curated_group.id, '11667051') CuratedGroup.add_student(curated_group.id, '7890123456') coe_advisor = AuthorizedUser.find_by_uid('1133399') curated_group = CuratedGroup.create(coe_advisor.id, 'I have one student') CuratedGroup.add_student(curated_group.id, '7890123456') ce3_advisor = AuthorizedUser.find_by_uid('2525') curated_group = CuratedGroup.create( domain='admitted_students', name="My 'admitted_students' group", owner_id=ce3_advisor.id, ) CuratedGroup.add_student(curated_group.id, '7890123456') std_commit(allow_test_environment=True)
def _create_topics(): Topic.create_topic( 'Other / Reason not listed', available_in_notes=True, available_in_appointments=True, ) for index in range(10): true_for_both = index in [1, 5, 7] available_in_appointments = true_for_both or index % 2 == 0 available_in_notes = true_for_both or index % 3 == 0 if true_for_both: topic = f'Topic for all, {index}' elif available_in_appointments: topic = f'Topic for appointments, {index}' else: topic = f'Topic for notes, {index}' Topic.create_topic( topic=topic, available_in_appointments=available_in_appointments, available_in_notes=available_in_notes, ) Topic.delete( Topic.create_topic('Topic for all, deleted', available_in_appointments=True).id) Topic.delete( Topic.create_topic('Topic for appointments, deleted', available_in_appointments=True).id) Topic.delete( Topic.create_topic('Topic for notes, deleted', available_in_notes=True).id) std_commit(allow_test_environment=True)
def test_deletes_drop_in_advisor_orphans(self): """Cleans up drop-in advisor record for a department membership that no longer exists.""" from boac.models.authorized_user_extension import DropInAdvisor dept_ucls = UniversityDept.query.filter_by( dept_code='QCADVMAJ').first() bad_user = AuthorizedUser.create_or_restore(uid='666', created_by='2040') UniversityDeptMember.create_or_update_membership( dept_ucls.id, bad_user.id, role='advisor', ) DropInAdvisor.create_or_update_membership(dept_ucls.dept_code, bad_user.id) std_commit(allow_test_environment=True) ucls_drop_in_advisors = DropInAdvisor.advisors_for_dept_code( dept_ucls.dept_code) assert len(ucls_drop_in_advisors) == 2 assert bad_user.id in [ d.authorized_user_id for d in ucls_drop_in_advisors ] from boac.api.cache_utils import refresh_department_memberships refresh_department_memberships() std_commit(allow_test_environment=True) ucls_drop_in_advisors = DropInAdvisor.advisors_for_dept_code( dept_ucls.dept_code) assert len(ucls_drop_in_advisors) == 1
def delete(self): row = JsonCache.query.filter_by(key=self.key()).first() job_state = row.json if row else None if row: db.session.query(JsonCache).filter(JsonCache.key == self.key()).delete() std_commit() return job_state
def test_removes_and_restores(self, app): from boac.api.cache_utils import refresh_calnet_attributes from boac.models import json_cache from boac.models.json_cache import JsonCache removed_advisor = coe_advisor_uid removed_ldap_record = '2040' all_active_uids = { u.uid for u in AuthorizedUser.get_all_active_users() } assert {removed_advisor, removed_ldap_record}.issubset(all_active_uids) calnet_filter = JsonCache.key.like('calnet_user_%') all_cached_uids = { r.json['uid'] for r in JsonCache.query.filter(calnet_filter).all() } assert {removed_advisor, removed_ldap_record}.issubset(all_cached_uids) AuthorizedUser.query.filter_by(uid=removed_advisor).delete() JsonCache.query.filter_by( key=f'calnet_user_for_uid_{removed_ldap_record}').delete() std_commit(allow_test_environment=True) refresh_calnet_attributes() assert json_cache.fetch( f'calnet_user_for_uid_{removed_ldap_record}') is not None assert json_cache.fetch( f'calnet_user_for_uid_{removed_advisor}') is None
def create_or_update_membership( cls, university_dept_id, authorized_user_id, role=None, automate_membership=True, ): existing_membership = cls.query.filter_by( university_dept_id=university_dept_id, authorized_user_id=authorized_user_id, ).first() if existing_membership: membership = existing_membership membership.role = role membership.automate_membership = automate_membership else: membership = cls( university_dept_id=university_dept_id, authorized_user_id=authorized_user_id, role=role, automate_membership=automate_membership, ) db.session.add(membership) std_commit() return membership
def test_create_and_delete_cohort(self): """Cohort_filter record to Flask-Login for recognized UID.""" owner = AuthorizedUser.find_by_uid(asc_advisor_uid).uid # Check validity of UID assert owner # Create cohort group_codes = ['MFB-DB', 'MFB-DL', 'MFB-MLB', 'MFB-OLB'] cohort = CohortFilter.create( uid=owner, name='Football, Defense', filter_criteria={ 'groupCodes': group_codes, }, ) cohort_id = cohort['id'] assert CohortFilter.find_by_id(cohort_id)['owner']['uid'] == owner assert cohort['totalStudentCount'] == len( CohortFilter.get_sids(cohort_id)) # Delete cohort and verify previous_owner_count = cohort_count(owner) CohortFilter.delete(cohort_id) std_commit(allow_test_environment=True) assert cohort_count(owner) == previous_owner_count - 1
def find_or_create(cls, sid): manually_added_advisee = cls.query.filter(cls.sid == sid).one_or_none() if not manually_added_advisee: manually_added_advisee = cls(sid) db.session.add(manually_added_advisee) std_commit() return manually_added_advisee
def create_or_restore(cls, uid, created_by, is_admin=False, can_access_canvas_data=True): existing_user = cls.query.filter_by(uid=uid).first() if existing_user: if existing_user.is_blocked: return False # If restoring a previously deleted user, respect passed-in attributes. if existing_user.deleted_at: existing_user.is_admin = is_admin existing_user.can_access_canvas_data = can_access_canvas_data existing_user.created_by = created_by existing_user.deleted_at = None # If the user currently exists in a non-deleted state, attributes passed in as True # should replace existing attributes set to False, but not vice versa. else: if can_access_canvas_data and not existing_user.can_access_canvas_data: existing_user.can_access_canvas_data = True if is_admin and not existing_user.is_admin: existing_user.is_admin = True existing_user.created_by = created_by user = existing_user else: user = cls( uid=uid, created_by=created_by, is_admin=is_admin, in_demo_mode=False, can_access_canvas_data=can_access_canvas_data, ) std_commit() return user
def update( cls, body, note_template_id, subject, attachments=(), delete_attachment_ids=(), is_private=False, topics=(), ): note_template = cls.find_by_id(note_template_id) if note_template: creator = AuthorizedUser.find_by_id(note_template.creator_id) note_template.body = body note_template.is_private = is_private note_template.subject = subject cls._update_note_template_topics(note_template, topics) if delete_attachment_ids: cls._delete_attachments(note_template, delete_attachment_ids) for byte_stream_bundle in attachments: cls._add_attachment(note_template, byte_stream_bundle, creator.uid) std_commit() db.session.refresh(note_template) return note_template else: return None
def delete_and_block(cls, uid): now = utc_now() user = cls.query.filter_by(uid=uid).first() user.deleted_at = now user.is_blocked = True std_commit() return user
def refresh_department_memberships(): from boac.models.authorized_user import AuthorizedUser from boac.models.university_dept import UniversityDept from boac.models.university_dept_member import UniversityDeptMember depts = UniversityDept.query.all() for dept in depts: dept.delete_automated_members() std_commit(allow_test_environment=True) for dept in depts: for membership in dept.memberships_from_loch(): # A non-numeric "uid" indicates a row from SIS advising tables best ignored. if not re.match(r'^\d+$', membership['uid']): continue user = AuthorizedUser.create_or_restore( uid=membership['uid'], created_by='0', can_access_canvas_data=membership['can_access_canvas_data'], ) if user: UniversityDeptMember.create_or_update_membership( dept, user, is_advisor=True, is_director=False, is_scheduler=False, )
def add_students(cls, curated_group_id, sids): curated_group = cls.query.filter_by(id=curated_group_id).first() if curated_group: CuratedGroupStudent.add_students(curated_group_id=curated_group_id, sids=sids) std_commit() _refresh_related_cohorts(curated_group)
def mock_advising_note(app, db): """Create advising note with attachment (mock s3).""" with mock_advising_note_s3_bucket(app): note_author_uid = '90412' base_dir = app.config['BASE_DIR'] path_to_file = f'{base_dir}/fixtures/mock_advising_note_attachment_1.txt' with open(path_to_file, 'r') as file: note = Note.create( author_uid=note_author_uid, author_name='Joni Mitchell', author_role='Director', author_dept_codes=['UWASC'], sid='11667051', subject='In France they kiss on main street', body=""" My darling dime store thief, in the War of Independence Rock 'n Roll rang sweet as victory, under neon signs """, attachments=[ { 'name': path_to_file.rsplit('/', 1)[-1], 'byte_stream': file.read(), }, ], ) db.session.add(note) std_commit(allow_test_environment=True) yield note Note.delete(note_id=note.id) std_commit(allow_test_environment=True)
def _load_schemas(): """Create DB schema from SQL file.""" with open(f"{app.config['BASE_DIR']}/scripts/db/schema.sql", 'r') as ddlfile: ddltext = ddlfile.read() db.session().execute(text(ddltext)) std_commit()
def unassign(cls, course_id, ignore=False): course = cls.query.filter_by(id=course_id).first() course.category_id = None course.ignore = ignore std_commit() DegreeProgressCourseUnitRequirement.delete(course_id) return course
def drop_staging_table(): sql = 'DROP TABLE IF EXISTS json_cache_staging CASCADE;' try: db.engine.connect().execute(text(sql)) std_commit() except SQLAlchemyError as err: app.logger.error(f'SQL {sql} threw {err}')