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