Use db for exchange mechanism

[ryanlovett]

Perhaps for Edinburgh-Hackathon...

The exchange directory ties assignment movement to a shared filesystem. I'm wondering if storing files in a database (or first pass just having a higher level abstraction) would be more flexible for hubs with distributed notebook servers.

[perllaghu]

This is a good call.... and we've discovered there are three different solutions for the problem. There is, indeed, a group working on it..

…

On Thu, 30 May 2019, 6:16 am Ryan Lovett, ***@***.***> wrote: Perhaps for Edinburgh-Hackathon... The exchange directory ties assignment movement to a shared filesystem. I'm wondering if storing files in a database (or first pass just having a higher level abstraction) would be more flexible for hubs with distributed notebook servers. — You are receiving this because you are subscribed to this thread. Reply to this email directly, view it on GitHub <#1095?email_source=notifications&email_token=AALJCDHXTKZ4PYPIWXFFGSTPX5PL7A5CNFSM4HQ5ND52YY3PNVWWK3TUL52HS4DFUVEXG43VMWVGG33NNVSW45C7NFSM4GWUQBSQ>, or mute the thread <https://github.com/notifications/unsubscribe-auth/AALJCDCZB7JYZ6ZRWUAO4HTPX5PL7ANCNFSM4HQ5ND5Q> .

[ryanlovett]

Hi @perllaghu , do you have pointers to these three solutions or the place where they're being discussed?

[krinsman]

@perllaghu At least two courses at Berkeley would like to use one of the three solutions for next semester, so we'd really appreciate any feedback you might have about whom we should coordinate with in order to implement a solution that could be merged upstream into master.

[perllaghu]

@perllaghu At least two courses at Berkeley would like to use one of the three solutions for next semester, so we'd really appreciate any feedback you might have about whom we should coordinate with in order to implement a solution that could be merged upstream into master.

So what we have is an external service that GETs/PUTs .zip files - our exchange.

We then created a parallel version of the files in nbgrader/exchange files, and everywhere that stuff references inbound or outbound, the code makes an API call to this external service - the API for that was suggested at #1130

The exchange service uses a database to track things.... and note the use of relationships in the ORM:

assignments are released, fetched, submitted, or collected (we've not done feedback yet)

We have an actions table:

    id = Column(Integer, primary_key=True, autoincrement=True)
    user_id = Column(Integer, ForeignKey("user.id", ondelete="CASCADE"), index=True)
    assignment_id = Column(
        Integer, ForeignKey("assignment.id", ondelete="CASCADE"), index=True
    )
    action = Column(Enum(AssignmentActions), nullable=False, index=True)
    location = Column(
        Unicode(200), nullable=True
    )  # Loction for the file of this action
    timestamp = Column(DateTime, default=datetime.utcnow)
    # These are the relationship handles: a specific subscription has a single user to a single course
    user = relationship("User", back_populates="actions")
    assignment = relationship("Assignment", back_populates="actions")

assignments table:

    id = Column(Integer, primary_key=True, autoincrement=True)
    assignment_code = Column(Unicode(50), nullable=False, index=True)
    active = Column(Boolean, default=True, nullable=False)

    ## course <-> assignment mappings
    # each assignment has just one parent course
    course_id = Column(Integer, ForeignKey("course.id", ondelete="CASCADE"), index=True)
    # can set 'course.assignments'
    course = relationship("Course", back_populates="assignments")

    # Maps this assignment to multiple actions [thence to users]
    actions = relationship("Action", back_populates="assignment")

    # Tracks the notebooks in each assignment
    notebooks = relationship("Notebook", backref="assignment", order_by="Notebook.name")

Courses:

    id = Column(Integer, primary_key=True, autoincrement=True)
    org_id = Column(Integer, nullable=False, index=True)
    course_code = Column(Unicode(200), nullable=False, index=True)
    course_title = Column(Unicode(200), nullable=True, index=True)
    ## course <-> user relationship
    # One to Many. One Course to many users. Each relationship has additional data
    subscribers = relationship("Subscription", back_populates="course")

    ## course <-> assignment relationship
    # One to Many. One Course, many assignments. Can set assignmnt.course
    assignments = relationship("Assignment", back_populates="course")

Notebooks (note, this is a bad setup, and we need to change notebooks to relate to actions, not assignments - as the number & names of notebooks may actually change!)

    #: Unique id of the notebook (automatically incremented)
    id = Column(Integer(), primary_key=True, autoincrement=True)

    #: Unique human-readable name for the notebook, such as "Problem 1". Note
    #: the uniqueness is only constrained within assignments (e.g. it is ok for
    #: two different assignments to both have notebooks called "Problem 1", but
    #: the same assignment cannot have two notebooks with the same name).
    name = Column(Unicode(128), nullable=False)

    assignment = None
    #: Unique id of :attr:`~nbexchange.orm.Notebook.assignment`
    assignment_id = Column(Integer(), ForeignKey("assignment.id"))

subscriptions:

    id = Column(Integer, primary_key=True, autoincrement=True)
    user_id = Column(Integer, ForeignKey("user.id", ondelete="CASCADE"), index=True)
    course_id = Column(Integer, ForeignKey("course.id", ondelete="CASCADE"), index=True)
    role = Column(Unicode(50), nullable=False)
    # These are the relationship handles: a specific subscription has a single user to a single course
    user = relationship("User", back_populates="courses")
    course = relationship("Course", back_populates="subscribers")

... and users:

    id = Column(Integer, primary_key=True, autoincrement=True)
    name = Column(Unicode(200), nullable=False, index=True)
    org_id = Column(Integer, nullable=False, index=True)
    ## User <-> Course Relationship
    # One to Many. One user has multiple courses
    courses = relationship("Subscription", back_populates="user")

    ## User <-> Assignments Relationship
    # One to Many. One user has multiple assignments
    actions = relationship("Action", back_populates="user")

To get the external service to return a list of assignments, we have code like this [this has been stripped back for here]

class Assignments(BaseHandler):
    """.../assignments/
    parmas:
        course_id: course_code

    GET: gets list of assignments for $course_code
    """
    urls = ["assignments"]

    @authenticated
    def get(self):
        models = []
        [course_code] = self.get_params(["course_id"])

        if not course_code:
            note = f"Assigment call requires a course id"
            self.finish({"success": False, "note": note})
            return

        # Who is my user?
        this_user = self.get_user

        # Is our user subscribed to this course?
        if course_code not in this_user["courses"]:
            note = f"User not subscribed to course {course_code}"
            self.finish({"success": False, "note": note})
            return

        # Find the course being referred to
        with scoped_session() as session:
            course = nbexchange.models.courses.Course.find_by_code(
                db=session, code=course_code, org_id=this_user["org_id"], log=self.log
            )
            if not course:
                note = f"Course {course_code} does not exist"
                self.finish({"success": False, "note": note})
                return

            assignments = nbexchange.models.assignments.Assignment.find_for_course(
                db=session, course_id=course.id, log=self.log
            )

            for assignment in assignments:
                for action in assignment.actions:
                    # For every action that is not "released" checked if the user id matches
                    if (
                        action.action != 'released'
                        and this_user.get("id") != action.user_id
                    ):
                        # Action does not belong to user, skip action
                        continue
                    models.append(
                        {
                            "assignment_id": assignment.assignment_code,
                            "course_id": assignment.course.course_code,
                            "status": action.action.value,  # currently called 'action' in our db
                            "path": action.location,
                            "notebooks": [
                                {"name": x.name} for x in assignment.notebooks
                            ],
                            "timestamp": action.timestamp.strftime(
                                "%Y-%m-%d %H:%M:%S.%f %Z"
                            ),
                        }
                    )

        self.finish({"success": True, "value": models})

(We do plan to release this code as an extension to nbgrader - however we need to work out how to make the exchange service a plugin, so we others can cleanly use our code. and correct the [wrong] notebook <-> assignment mapping)

[ryanlovett]

Awesome, thanks for this and for the link to the other issue!

[ryanlovett]

@perllaghu Is the full code in a public repo somewhere?

[perllaghu]

Sadly, not yet.... we plan to, but other stuff gets in the way :(

[perllaghu]

@ryanlovett nbgrader now supports an external exchange service, so you can write your own plugin to use a database.

Our version is an external exchange is here: https://github.com/edina/nbexchange
UC Davis made something too: https://github.com/LibreTexts/ngshare

[ryanlovett]

Hi @perllaghu , do you have pointers to these three solutions or the place where they're being discussed?

[krinsman]

@perllaghu At least two courses at Berkeley would like to use one of the three solutions for next semester, so we'd really appreciate any feedback you might have about whom we should coordinate with in order to implement a solution that could be merged upstream into master.

[perllaghu]

@perllaghu At least two courses at Berkeley would like to use one of the three solutions for next semester, so we'd really appreciate any feedback you might have about whom we should coordinate with in order to implement a solution that could be merged upstream into master.

So what we have is an external service that GETs/PUTs .zip files - our exchange.

We then created a parallel version of the files in nbgrader/exchange files, and everywhere that stuff references inbound or outbound, the code makes an API call to this external service - the API for that was suggested at #1130

The exchange service uses a database to track things.... and note the use of relationships in the ORM:

assignments are released, fetched, submitted, or collected (we've not done feedback yet)

We have an actions table:

    id = Column(Integer, primary_key=True, autoincrement=True)
    user_id = Column(Integer, ForeignKey("user.id", ondelete="CASCADE"), index=True)
    assignment_id = Column(
        Integer, ForeignKey("assignment.id", ondelete="CASCADE"), index=True
    )
    action = Column(Enum(AssignmentActions), nullable=False, index=True)
    location = Column(
        Unicode(200), nullable=True
    )  # Loction for the file of this action
    timestamp = Column(DateTime, default=datetime.utcnow)
    # These are the relationship handles: a specific subscription has a single user to a single course
    user = relationship("User", back_populates="actions")
    assignment = relationship("Assignment", back_populates="actions")

assignments table:

    id = Column(Integer, primary_key=True, autoincrement=True)
    assignment_code = Column(Unicode(50), nullable=False, index=True)
    active = Column(Boolean, default=True, nullable=False)

    ## course <-> assignment mappings
    # each assignment has just one parent course
    course_id = Column(Integer, ForeignKey("course.id", ondelete="CASCADE"), index=True)
    # can set 'course.assignments'
    course = relationship("Course", back_populates="assignments")

    # Maps this assignment to multiple actions [thence to users]
    actions = relationship("Action", back_populates="assignment")

    # Tracks the notebooks in each assignment
    notebooks = relationship("Notebook", backref="assignment", order_by="Notebook.name")

Courses:

    id = Column(Integer, primary_key=True, autoincrement=True)
    org_id = Column(Integer, nullable=False, index=True)
    course_code = Column(Unicode(200), nullable=False, index=True)
    course_title = Column(Unicode(200), nullable=True, index=True)
    ## course <-> user relationship
    # One to Many. One Course to many users. Each relationship has additional data
    subscribers = relationship("Subscription", back_populates="course")

    ## course <-> assignment relationship
    # One to Many. One Course, many assignments. Can set assignmnt.course
    assignments = relationship("Assignment", back_populates="course")

Notebooks (note, this is a bad setup, and we need to change notebooks to relate to actions, not assignments - as the number & names of notebooks may actually change!)

    #: Unique id of the notebook (automatically incremented)
    id = Column(Integer(), primary_key=True, autoincrement=True)

    #: Unique human-readable name for the notebook, such as "Problem 1". Note
    #: the uniqueness is only constrained within assignments (e.g. it is ok for
    #: two different assignments to both have notebooks called "Problem 1", but
    #: the same assignment cannot have two notebooks with the same name).
    name = Column(Unicode(128), nullable=False)

    assignment = None
    #: Unique id of :attr:`~nbexchange.orm.Notebook.assignment`
    assignment_id = Column(Integer(), ForeignKey("assignment.id"))

subscriptions:

    id = Column(Integer, primary_key=True, autoincrement=True)
    user_id = Column(Integer, ForeignKey("user.id", ondelete="CASCADE"), index=True)
    course_id = Column(Integer, ForeignKey("course.id", ondelete="CASCADE"), index=True)
    role = Column(Unicode(50), nullable=False)
    # These are the relationship handles: a specific subscription has a single user to a single course
    user = relationship("User", back_populates="courses")
    course = relationship("Course", back_populates="subscribers")

... and users:

    id = Column(Integer, primary_key=True, autoincrement=True)
    name = Column(Unicode(200), nullable=False, index=True)
    org_id = Column(Integer, nullable=False, index=True)
    ## User <-> Course Relationship
    # One to Many. One user has multiple courses
    courses = relationship("Subscription", back_populates="user")

    ## User <-> Assignments Relationship
    # One to Many. One user has multiple assignments
    actions = relationship("Action", back_populates="user")

To get the external service to return a list of assignments, we have code like this [this has been stripped back for here]

class Assignments(BaseHandler):
    """.../assignments/
    parmas:
        course_id: course_code

    GET: gets list of assignments for $course_code
    """
    urls = ["assignments"]

    @authenticated
    def get(self):
        models = []
        [course_code] = self.get_params(["course_id"])

        if not course_code:
            note = f"Assigment call requires a course id"
            self.finish({"success": False, "note": note})
            return

        # Who is my user?
        this_user = self.get_user

        # Is our user subscribed to this course?
        if course_code not in this_user["courses"]:
            note = f"User not subscribed to course {course_code}"
            self.finish({"success": False, "note": note})
            return

        # Find the course being referred to
        with scoped_session() as session:
            course = nbexchange.models.courses.Course.find_by_code(
                db=session, code=course_code, org_id=this_user["org_id"], log=self.log
            )
            if not course:
                note = f"Course {course_code} does not exist"
                self.finish({"success": False, "note": note})
                return

            assignments = nbexchange.models.assignments.Assignment.find_for_course(
                db=session, course_id=course.id, log=self.log
            )

            for assignment in assignments:
                for action in assignment.actions:
                    # For every action that is not "released" checked if the user id matches
                    if (
                        action.action != 'released'
                        and this_user.get("id") != action.user_id
                    ):
                        # Action does not belong to user, skip action
                        continue
                    models.append(
                        {
                            "assignment_id": assignment.assignment_code,
                            "course_id": assignment.course.course_code,
                            "status": action.action.value,  # currently called 'action' in our db
                            "path": action.location,
                            "notebooks": [
                                {"name": x.name} for x in assignment.notebooks
                            ],
                            "timestamp": action.timestamp.strftime(
                                "%Y-%m-%d %H:%M:%S.%f %Z"
                            ),
                        }
                    )

        self.finish({"success": True, "value": models})

(We do plan to release this code as an extension to nbgrader - however we need to work out how to make the exchange service a plugin, so we others can cleanly use our code. and correct the [wrong] notebook <-> assignment mapping)

[ryanlovett]

Awesome, thanks for this and for the link to the other issue!

[ryanlovett]

@perllaghu Is the full code in a public repo somewhere?

[perllaghu]

Sadly, not yet.... we plan to, but other stuff gets in the way :(

[perllaghu]

@ryanlovett nbgrader now supports an external exchange service, so you can write your own plugin to use a database.

Our version is an external exchange is here: https://github.com/edina/nbexchange
UC Davis made something too: https://github.com/LibreTexts/ngshare

[ryanlovett]

Thanks @perllaghu ! I had been following the Davis project. Congrats on the success of your two efforts!