def __initialize_connection():
connection = __create_engine()
# Add multiprocessing safeguards as recommended by
# https://docs.sqlalchemy.org/en/13/core/pooling.html#using-connection-pools-with-multiprocessing
@event.listens_for(connection, "connect")
def connect(dbapi_connection, connection_record):
connection_record.info["pid"] = os.getpid()
@event.listens_for(connection, "checkout")
def checkout(dbapi_connection, connection_record, connection_proxy):
pid = os.getpid()
if connection_record.info["pid"] != pid:
connection_record.connection = connection_proxy.connection = None
raise exc.DisconnectionError(
"Connection record belongs to pid %s, attempting to check out in pid %s"
% (connection_record.info["pid"], pid))
# Don't make a connection before we've added the multiprocessing guards
# as otherwise we will have a connection that doesn't have the 'pid' attribute set.
# check if the database is corrupted:
try:
connection.execute(
"SELECT name FROM sqlite_master WHERE type='table';")
except (exc.DatabaseError, TypeError):
repair_sqlite_db(connection)
return connection
def get_queryset(self):
"""
Returns the notifications in reverse-chronological order, filtered by the query parameters.
By default it sends only notifications from the past day.
If a 'page_size' parameter is used, that sets a maximum number of results.
If a 'page' parameter is used, the past day limit is not applied.
Some url examples:
/coach/api/notifications/?collection_id=9da65157a8603788fd3db890d2035a9f
/coach/api/notifications/?collection_id=9da65157a8603788fd3db890d2035a9f&after=8&page=2
/coach/api/notifications/?page_size=5&page=2&collection_id=9da65157a8603788fd3db890d2035a9f&learner_id=94117bb5868a1ef529b8be60f17ff41a
/coach/api/notifications/?collection_id=9da65157a8603788fd3db890d2035a9f&page=2
:param: collection_id uuid: classroom or learner group identifier (mandatory)
:param: learner_id uuid: user identifier
:param: after integer: all the notifications after this id will be sent.
:param: page_size integer: sets the number of notifications to provide for pagination (defaults: 10)
:param: page integer: sets the page to provide when paginating.
"""
collection_id = self.kwargs["collection_id"]
if collection_id:
try:
collection = Collection.objects.get(pk=collection_id)
except (Collection.DoesNotExist, ValueError):
return []
if collection.kind == collection_kinds.CLASSROOM:
classroom_groups = list(LearnerGroup.objects.filter(parent=collection))
classroom_groups += list(AdHocGroup.objects.filter(parent=collection))
learner_groups = [group.id for group in classroom_groups]
learner_groups.append(collection_id)
notifications_query = LearnerProgressNotification.objects.filter(
classroom_id__in=learner_groups
)
else:
notifications_query = LearnerProgressNotification.objects.filter(
classroom_id=collection_id
)
notifications_query = self.apply_learner_filter(notifications_query)
after = self.check_after()
self.remove_default_page_size()
if after:
notifications_query = notifications_query.filter(id__gt=after)
elif self.request.query_params.get("page", None) is None:
try:
last_id_record = notifications_query.latest("id")
# returns all the notifications 24 hours older than the latest
last_24h = last_id_record.timestamp - datetime.timedelta(days=1)
notifications_query = notifications_query.filter(
timestamp__gte=last_24h
)
except (LearnerProgressNotification.DoesNotExist):
return []
except DatabaseError:
repair_sqlite_db(connections["notifications_db"])
return []
return notifications_query.order_by("-id")
def get_queryset(self):
"""
Returns the notifications in reverse-chronological order, filtered by the query parameters.
By default it sends only notifications from the past day.
If a 'page_size' parameter is used, that sets a maximum number of results.
If a 'page' parameter is used, the past day limit is not applied.
Some url examples:
/coach/api/notifications/?classroom_id=9da65157a8603788fd3db890d2035a9f
/coach/api/notifications/?classroom_id=9da65157a8603788fd3db890d2035a9f&after=8&limit=10
/coach/api/notifications/?limit=5&classroom_id=9da65157a8603788fd3db890d2035a9f&learner_id=94117bb5868a1ef529b8be60f17ff41a
/coach/api/notifications/?classroom_id=9da65157a8603788fd3db890d2035a9f
:param: classroom_id uuid: classroom or learner group identifier (mandatory)
:param: learner_id uuid: user identifier
:param: group_id uuid: group identifier
:param: after integer: all the notifications after this id will be sent.
:param: limit integer: sets the number of notifications to provide
"""
classroom_id = self.kwargs["classroom_id"]
notifications_query = LearnerProgressNotification.objects.filter(
classroom_id=classroom_id)
notifications_query = self.apply_learner_filter(notifications_query)
notifications_query = self.apply_group_filter(notifications_query)
after = self.check_after()
if after:
notifications_query = notifications_query.filter(id__gt=after)
before = self.check_before()
if not after and not before:
try:
last_id_record = notifications_query.latest("id")
# returns all the notifications 24 hours older than the latest
last_24h = last_id_record.timestamp - datetime.timedelta(
days=1)
notifications_query = notifications_query.filter(
timestamp__gte=last_24h)
except (LearnerProgressNotification.DoesNotExist):
return LearnerProgressNotification.objects.none()
except DatabaseError:
repair_sqlite_db(connections["notifications_db"])
return LearnerProgressNotification.objects.none()
limit = self.check_limit()
if before and limit:
# Don't allow arbitrary backwards lookups
notifications_query = notifications_query.filter(id__lt=before)
return notifications_query
def run(self):
"""
Execute any log saving functions in the self.running list
"""
if self.running:
# Do this conditionally to avoid opening an unnecessary transaction
with transaction.atomic():
for fn in self.running:
try:
fn()
except OperationalError:
repair_sqlite_db(connections["notifications_db"])
except Exception as e:
# Catch all exceptions and log, otherwise the background process will end
# and no more logs will be saved!
logging.warn(
"Exception raised during background notification calculation: %s",
e,
)
connection.close()
def list(self, request, *args, **kwargs):
"""
It provides the list of ClassroomNotificationsViewset from DRF.
Then it fetches and saves the needed information to know how many coaches
are requesting notifications in the last five minutes
"""
try:
queryset = self.filter_queryset(
self.prefetch_queryset(self.get_queryset()))
except (OperationalError, DatabaseError):
repair_sqlite_db(connections["notifications_db"])
# L
logging_interval = datetime.datetime.now() - datetime.timedelta(
minutes=5)
try:
logged_notifications = (NotificationsLog.objects.filter(
timestamp__gte=logging_interval).values(
"coach_id").distinct().count())
except (OperationalError, DatabaseError):
logged_notifications = 0
repair_sqlite_db(connections["notifications_db"])
# if there are more than 10 notifications we limit the answer to 10
if logged_notifications < 10:
notification_info = NotificationsLog()
notification_info.coach_id = request.user.id
notification_info.save()
NotificationsLog.objects.filter(
timestamp__lt=logging_interval).delete()
more_results = False
limit = self.check_limit()
if limit:
# If we are limiting responses, check if more results are available
more_results = queryset.order_by("-id")[limit:].exists()
return Response({
"results": self.serialize(queryset),
"coaches_polling": logged_notifications,
"more_results": more_results,
})
def list(self, request, *args, **kwargs):
"""
It provides the list of ClassroomNotificationsViewset from DRF.
Then it fetches and saves the needed information to know how many coaches
are requesting notifications in the last five minutes
"""
# Use super on the parent class to prevent an infinite recursion.
try:
response = super(viewsets.ReadOnlyModelViewSet, self).list(
request, *args, **kwargs
)
except (OperationalError, DatabaseError):
repair_sqlite_db(connections["notifications_db"])
# L
logging_interval = datetime.datetime.now() - datetime.timedelta(minutes=5)
try:
logged_notifications = (
NotificationsLog.objects.filter(timestamp__gte=logging_interval)
.values("coach_id")
.distinct()
.count()
)
except (OperationalError, DatabaseError):
logged_notifications = 0
repair_sqlite_db(connections["notifications_db"])
# if there are more than 10 notifications we limit the answer to 10
if logged_notifications < 10:
notification_info = NotificationsLog()
notification_info.coach_id = request.user.id
notification_info.save()
NotificationsLog.objects.filter(timestamp__lt=logging_interval).delete()
if "results" not in response.data:
response.data = {
"results": response.data,
"coaches_polling": logged_notifications,
}
else:
response.data["coaches_polling"] = logged_notifications
return response
def activate_pragmas_per_connection(sender, connection, **kwargs):
"""
Activate SQLite3 PRAGMAs that apply on a per-connection basis. A no-op
right now, but kept around as infrastructure if we ever want to add
PRAGMAs in the future.
"""
if connection.vendor == "sqlite":
if connection.alias == "notifications_db":
broken_db = False
try:
cursor = connection.cursor()
quick_check = cursor.execute("PRAGMA quick_check").fetchone()[0]
broken_db = quick_check != "ok"
except DatabaseError:
broken_db = True
if broken_db:
repair_sqlite_db(connection)
cursor = connection.cursor()
# Shorten the default WAL autocheckpoint from 1000 pages to 500
cursor.executescript(CONNECTION_PRAGMAS)
def __initialize_connection():
db_url = create_db_url(
conf.OPTIONS["Database"]["DATABASE_ENGINE"],
path=os.path.join(conf.KOLIBRI_HOME, "job_storage.sqlite3"),
name=conf.OPTIONS["Database"]["DATABASE_NAME"],
password=conf.OPTIONS["Database"]["DATABASE_PASSWORD"],
user=conf.OPTIONS["Database"]["DATABASE_USER"],
host=conf.OPTIONS["Database"]["DATABASE_HOST"],
port=conf.OPTIONS["Database"]["DATABASE_PORT"],
)
connection = make_connection(
conf.OPTIONS["Database"]["DATABASE_ENGINE"],
db_url,
)
# Check if the database is corrupted
try:
check_sqlite_integrity(connection)
except (exc.DatabaseError, sqlite3.DatabaseError):
logger.warn("Job storage database has been corrupted, regenerating")
repair_sqlite_db(connection)
return connection
def content_status_serializer(lesson_data, learners_data, classroom): # noqa C901
# First generate a unique set of content node ids from all the lessons
lesson_node_ids = set()
for lesson in lesson_data:
lesson_node_ids |= set(lesson.get("node_ids"))
# Now create a map of content_id to node_id so that we can map between lessons, and notifications
# which use the node id, and summary logs, which use content_id. Note that many node_ids may map
# to the same content_id.
content_map = {
n[0]: n[1]
for n in ContentNode.objects.filter_by_uuids(lesson_node_ids).values_list(
"id", "content_id"
)
}
# Get all the values we need from the summary logs to be able to summarize current status on the
# relevant content items.
content_log_values = (
logger_models.ContentSummaryLog.objects.filter(
content_id__in=set(content_map.values()),
user__in=[learner["id"] for learner in learners_data],
)
.annotate(attempts=Count("masterylogs__attemptlogs"))
.values(
"user_id",
"content_id",
"end_timestamp",
"time_spent",
"progress",
"kind",
"attempts",
)
)
# In order to make the lookup speedy, generate a unique key for each user/node that we find
# listed in the needs help notifications that are relevant. We can then just check
# existence of this key in the set in order to see whether this user has been flagged as needing
# help.
lookup_key = "{user_id}-{node_id}"
try:
notifications = LearnerProgressNotification.objects.filter(
Q(notification_event=NotificationEventType.Completed)
| Q(notification_event=NotificationEventType.Help),
classroom_id=classroom.id,
lesson_id__in=[lesson["id"] for lesson in lesson_data],
).values_list("user_id", "contentnode_id", "timestamp", "notification_event")
needs_help = {
lookup_key.format(user_id=n[0], node_id=n[1]): n[2]
for n in notifications
if n[3] == NotificationEventType.Help
}
except OperationalError:
notifications = []
repair_sqlite_db(connections["notifications_db"])
# In case a previously flagged learner has since completed an exercise, check all the completed
# notifications also
completed = {
lookup_key.format(user_id=n[0], node_id=n[1]): n[2]
for n in notifications
if n[3] == NotificationEventType.Completed
}
def get_status(log):
"""
Read the dict from a content summary log values query and return the status
In the case that we have found a needs help notification for the user and content node
in question, return that they need help, otherwise return status based on their
current progress.
"""
content_id = log["content_id"]
if content_id in content_map.values():
# Don't try to lookup anything if we don't know the content_id
# node_id mapping - might happen if a channel has since been deleted
content_ids = [
key for key, value in content_map.items() if value == content_id
]
for c_id in content_ids:
key = lookup_key.format(user_id=log["user_id"], node_id=c_id)
if key in needs_help:
# Now check if we have not already registered completion of the content node
# or if we have and the timestamp is earlier than that on the needs_help event
if key not in completed or completed[key] < needs_help[key]:
return HELP_NEEDED
if log["progress"] == 1:
return COMPLETED
if log["kind"] == content_kinds.EXERCISE:
# if there are no attempt logs for this exercise, status is NOT_STARTED
if log["attempts"] == 0:
return NOT_STARTED
return STARTED
def map_content_logs(log):
"""
Parse the content logs to return objects in the expected format.
"""
return {
"learner_id": log["user_id"],
"content_id": log["content_id"],
"status": get_status(log),
"last_activity": log["end_timestamp"],
"time_spent": log["time_spent"],
}
return map(map_content_logs, content_log_values)