Maintain submission history for assignments that includes submission contents

[Brian Baillargeon]

This is a challenging problem, would love to hear your opinions on the
matter:
https://jira.sakaiproject.org/browse/SAK-29893

Description:
Every time a student resubmits or saves a draft, it updates their row in
the assignment_submission table. These updates overwrite the student's
previous submission, so all previous submission content is gone and
unrecoverable. We would like to retain the full submission content for
all submissions rather than overwriting the previous one.
It would be nice to give students and instructors the ability to refer
to the full content of previous submissions. This would also solve the
problem that occurs when saving a draft after submitting, as the draft
replaces the submission, resulting in the instructor not being able to
view the original submission if the student does not submit the draft.
This has lead to support calls along the lines of “Student claims they
submitted; instructor cannot view submission”.

Given that we would need to store a separate row for each submission /
draft, there are some challenges to overcome. For example, rather than
getting the single submission object for the instructor's view, we would
need to pull the most recent non-draft submission. Other concerns include:
• performance concerns with storing everything in the XML column
• maintaining compatibility with legacy data vs converting legacy data

Performance:
From a performance perspective, we wouldn't want to grab all submissions
for an assignment and then filter out the drafts in application code. It
would be more efficient to have the database perform the filtering for
us. We propose moving the draft attribute out of the XML blob and
storing it in a dedicated column in the table. This would for example
allow us to efficiently query for a student's most recent non-draft
submission for the instructor view.

Compatibility:
If a new draft column is introduced, we need to deal with legacy
submissions. Some options include:
Introducing an attribute (call it draft_version) on the assignment which
determines whether its associated submissions' draft information is
stored in the XML blob vs in the draft column. Code would need to
determine which is the case and run the appropriate logic.
Script to read the draft value out of the blob and populate the column
for all existing submissions.

But is there interest in the community for the removal of all / most of
the XML attributes into designated table columns? Considering the
proposal to extract the 'draft' attribute from XML into its own column,
and introduce a 'draft_version' column to maintain legacy data, if we
then discover that we need similarly to extract other attributes, we
would continue introducing new 'version' attributes for each attribute
we want to pull out of the XML. It would be best to do it all at once,
and either do a full conversion of existing rows, or set the version
attribute one time for legacy vs current.

[Adam Marshall]

From a user's point of view this looks like a good idea.

I'm not a developer (IANAD) but in this day and age, should we be saving any chunks of XML in the database? Would this be an opportunity to tidying things up?

adam

--

** Note change of email address to adam.m...@it.ox.ac.uk **

Dr A C Marshall, WebLearn Service Manager, University of Oxford.
IT Services, 13 Banbury Rd, Oxford. OX2 6NN.

--
You received this message because you are subscribed to the Google Groups "Sakai Development" group.
To unsubscribe from this group and stop receiving emails from it, send an email to sakai-dev+...@apereo.org.
To post to this group, send email to saka...@apereo.org.
Visit this group at http://groups.google.com/a/apereo.org/group/sakai-dev/.

[Sam Ottenhoff]

But is there interest in the community for the removal of all / most of
the XML attributes into designated table columns? 

There is interest. I'm willing to contribute help to the effort especially around conversions.