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)