DBI-Backed Exam Storage

Store exam data in an SQL database using DBI. Note that exam data may contain sensitive information. To avoid potential privacy issues, the DBI storage provider supports hashing the user id, but this may require external identification of users for grading.

dbi_storage_provider(
  conn,
  attempts_table,
  section_data_table,
  hash_user = FALSE,
  hash_key = TRUE
)

Arguments

conn	a DBIConnection object, as returned by dbConnect()
attempts_table	the name of the table to store the attempts data in.
section_data_table	the name of the table to store the section data in.
hash_user	store the user id as a SHA256 HMAC to protect the identity.
hash_key	if `hash_user=TRUE`, the pre-shared secret key. If `TRUE` (the default), uses the concatenated exam_id and exam_version. If `NULL`, uses no HMAC but a plain SHA256 hash of the user id (strongly discouraged). Anything else is cast to character (or kept as raw vector) and used as key.

Details

Attempts are stored in a table which requires the following columns:

attempt_id: a unique identifier (a UUID) for the attempt, stored as character data.
user_id: stored as character data. The required length depends on the authentication provider and if hashing is used.
exam_id: stored as character data. Ensure it can hold enough characters for the exam ids used in your exams.
exam_version: stored as character data. Ensure it can hold enough characters for the exam version strings used in your exams.
started_at: unix timestamp at which the attempt was started.
seed: the integer used to seed the RNG for this attempt.
finished_at: unix timestamp at which the attempt was finished.
user_obj: the user object (less the identifier) as returned by the authentication provider stored as character data of arbitrary length.
points: the points awarded for the attempt, as character data of arbitrary length.

Exam data is stored in a table which requires the following columns:

attempt_id: the attempt identifier of type UUID.
section: the section identifier, as character data.
saved_at: unix timestamp at which the section data was last saved.
section_data: stored as character data of arbitrary length.

dbi_storage_provider() reads/writes unix timestamps as type numeric, and some database systems (e.g., PostgreSQL) do not accept numeric input for timestamps. For these database systems, it is best to create the columns of type double precision.

A sample definition of the tables for PostgreSQL is as follows:

CREATE TABLE attempts (
  attempt_id UUID PRIMARY KEY,
  user_id character varying(256) NOT NULL,
  exam_id character varying(64) NOT NULL,
  exam_version character varying(64) NOT NULL,
  started_at double precision NOT NULL,
  seed integer NOT NULL,
  user_obj text NOT NULL,
  finished_at double precision,
  points text);

CREATE INDEX attempts_index ON attempts
  (user_id, exam_id, exam_version);

CREATE TABLE section_data (
  id serial PRIMARY KEY,
  attempt_id UUID NOT NULL REFERENCES attempts (attempt_id)
    ON DELETE CASCADE ON UPDATE CASCADE,
  section character varying (256) NOT NULL,
  saved_at double precision NOT NULL,
  section_data text);

CREATE INDEX section_data_index ON section_data
  (attempt_id, section);

Arguments

Details

See also