database layout discussion

[pdawyndt]

wiki page: database layout proposal

Questions

• why are some table names plural (users, repositories, judges, tags) and other singular (exercise, submission, course, deadline?
• course organisation: does a course have series of exercises (like milestones in a github repository) ? where is the information on a series stored? what is the role of deadlines ?
• will the available containers be represented in the database ?
• will the available programming languages (with versions and packages) be represented in the database ? programming language/version could then be linked to the container used as the execution environment
• exercises & judges could be linked to one or more programming language (with versions and packages)

Suggestions

• [ ] users: is there a need to make a distinction between users that can submit solutions (students) and users that can create new exercises or create new courses (teachers) ?
• [ ] judges: seems logical to also allow that judge definitions may come from a github repository, just as we do with exercise definitions; this would allow to make updates to judges through the repository; requires a fixed directory layout for the judges; one judge per repository or multiple judges? the same could hold for courses (e.g. the description of the course, the assignment of exercises, the organisation of exercises into series, ...)
• [ ] exercises
  • administrators: users that can make modifications to the exercise
  • who can include exercise in courses ? only admins, open to all
  • testable: check this option if you want to allow submitting solutions to this exercise
  • available for use in 3rd party contests: is it possible to use the exercise in other courses?
  • available for use in 3rd party software: is it possible to use the exercise in other online judges?
  • open-ended link between natural language and name of the exercises (in the current schema, supported languages are limited to nl and en because of direct mapping between languages and table columns
  • SPOJ uses a restriction on submission size (source code limit) to prevent accepting (and storing) high-volume submissions; this is also something we do in the Flemish programming contest
  • supported natural languages (with option for an open-ended list)
  • supported programming languages (with option for an open-ended list)
  • we might provide a generic way to share media files together with the problem description (as is done in Indianio)
• [ ] courses: I would use a more general term than Course, because the system might be used for other purposes (trainings, workshops, competitions, ...); along the same lines, I would not have a specific field year because it is to tightly linked to a course; some attributes of generic course might be:
  • administrators: users that can make modifications to the course
  • subscribers: users subscribed to the course
  • timestamps: visible from, visible until, submissions from, submissions until, source code visible from (to make sample source code of solutions available to users)
  • how do users subscribe to a course: only admins can assign, users can apply but need approval, users are automatically assigned upon first view / first submission
  • visibility of courses: open for all, only open for subscribers, closed (only open for admins)
  • natural_language (default language of the course; can courses support multiple languages ?)
  • programming_language: further restricts supported programming languages, on top of programming languages supported by the exercises / judges
  • time zone (needed for reporting of timestamps in web interface)
• [ ] priority of configuration settings for judging a submission (from lowest to highest priority)
  • system-wide settings
  • repository-wide settings
  • judge-specific settings
  • exercise-specific settings
  • submission-specific settings (dynamically set upon submission: programming language, natural_language, user, timestamp, ...)
  • view submissions: can users only see their own submissions or all submissions in the course (social aspects of coding)
• [ ] for a submission, we might store timestamp for the actual submission, the start of the evaluation and the end of the evaluation; these three time stamps can be used to evaluate the performance of the platform: how long do users need to wait until their submissions are evaluated; how long does the actual evaluation for a specific programming language / exercise, ...
• [ ] add categories to tags (programming langauges, programming constructs, ...)
• [ ] allow definition of custom skin for courses (low priority)
• [ ] integrate discussion channels in courses
  • subscribers can ask questions about exercises to the admins (see "clarifications" in progrmaming contests)
  • users can report issues about the exercise (spelling errors in descriptions; unclear descriptions; wrong settings for judging the exercises)
  • forum linked to exercise / course
• [ ] submissions and judges should share the properties that relate to the fact that both can be executed in a container (with information sent to stdin and results written to stdout/stderr)

Scenarios

• for an exam, we want to set specific timestamps when the exercises must become visible, what happens with the exercises when the deadline has passed, we might even open/close submissions per user (e.g. scan student card upon entrance in room to open account for submissions, scan student card upon handing in the exam to close the account for submissions; this also gives electronic signatures at the start and end of the exam session for the student); prevent students to continue submitting after leaving the exam room; deal with students that have to stop earlier or later (e.g. students that can work longer on the exam)
• assign exercises to a course, either by direct assignment (fixed assignment), by putting exercises in series (fixed assignment) or by using specific tags (open assignment); impose order on exercises in series; the linkage of exercises to courses might have its own settings:
  • timestamps: visible from, visible until, submissions from, submissions until, source code visible from (to make sample source code of solutions available to users)
  • series
  • score
• rejudge all submissions (restriction: exercise, course, ...), e.g. when definition of judge or definition or exercise have been modified
• admin exports description of all exercises in a course (e.g. in PDF format, organized per series)
• user exports latest submission (description + source code) of all exercises solved in a course
• admin exports description + source code of all exercises in a series after the deadline (publication may be online or in PDF)
• maybe we also should make an attempt to see if we can implement an "Indianio judge" in the Dodona platform, which mimicks the workflow of Indianio; not necessarily to replace Indianio, but to check if the Dodona platform is flexible enough
• print submitted solutions directly from Dodona (as is done by Indianio; e.g. as part of ending the session of a user in a course at the end of the exam)
• allow manual judging instead of automatic judging; in the process, teachers can do the grading and add feedback to the submission
• allow manual judging on top of automatic judging; e.g. adjust the grading, add comments (just like automatic linters) to the source code, ...; for an exam we could for example also play with a workflow where we push all solutions of an exercise submitted for the exam to a github repository, use the code review tools of the repository to add annotations to the submitted source code, and then add these annotations to the submission as part of the grading process
• generic way to add new reports to the system
• integrate plagiarism detection into the system

Original issue by @pdawyndt on Wed Jul 13 2016 at 10:08. Closed by @bmesuere on Mon Dec 19 2016 at 09:33.

[bmesuere]

• All actual table names are plural by RoR convention.
• Series and deadlines are "leftovers" from an initial brainstorm in april. I didn't delete them because we'll probably need something like that. This won't be included in the 0.5 release (august).
• The 3-level permission system (actually 4, because "not signed in" is also possible) is already used in the current website. Is there a problem with this?
• Updating the judges is indeed the plan. I didn't add the columns for this yet because I first wanted to see how thing went with auto-updating the exercises.
• Config: I don't think this has anything to do with the database.

Original comment by @bmesuere on Wed Jul 13 2016 at 10:54.