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);

See also

Other storage configuration: setup_database(), storage_provider