Sqltrainer

NOTE: This repository has been replaced with a new one

Interactive exercises and material for University of Helsinki Introduction to Databases course.

Production site: sqltrainer.fi

Development

Docker is used during development, run in git root directory:

docker compose up --build -w

Site can then be accessed at localhost:1337

Production

Production deployment is done with docker compose.

Configs etc. are updated in the git which CI then builds and deploys to the production server.

See deploy/ directory, deploy/README.md and github actions CI for details.

To deploy code to server, push changes to deploy branch, github CI will then build new versions which propagate to production server.

Grafana is used for monitoring.

Testing

Front

To run tests in front/ directory run npm run test-once

To test model solutions, set MODEL_SOLUTIONS environment variable to point into model solution repo, for example MODEL_SOLUTIONS=/home/user/sqltrainer_modelsolutions/ npm run test-once.

Configuration

How to create course instances and material

All tasks and material is stored in src/coursedata/

Each course has its own directory src/coursedata/[course-id] such as src/coursedata/spring-2025

In addition, basic information of each course is stored in src/coursedata/courses.ts, such as course name and list of text sections in order.

Course

Text

Text sections are stored at src/coursedata/[course-id]/texts/.

Text sections are markdown files.

In the beginning of each file is so called "front matter".

Front matter contains following data:

• title human readable name of the section
• courseId same as id of the course
• id must be unique number per-course instance, used in src/coursedata/courses.ts to refer text sections of the course
• showTypes also display column type in addition to name, when showing task submission input & results.

After front matter comes the text content of the text section.

Github-flavored markdown syntax is supported.

In addition the following syntax is supported:

• sqlite_console language in code blocks highlights sqlite console syntax.

• Table specific syntax:

  • To specify table title specify <!--rehype:title=Henkilot=--> after markdown table.
  • To make a table inline specify <!--rehype:inline=-->
  • To combine both <!--rehype:title=Henkilot&inline=-->

• LaTeX is supported with $$ $$

• Notes, boxes to emphasize content are supported, they consist of title and content, note that its important to have newlines after <div> before the content begins for markdown syntax to work.

  <div class="note-title">
      Note title
  </div>
  <div class="note">
  
      Note content
  
  </div>

• Code block titles work by prepending code blocks with

  <div class="code-title">commands.sql</div>

Tasks

Tasks are stored at src/coursedata/[course-id]/tasks/[task-id]

Problem statement is kept in statement.md file.

Each task consists of an example input, an example output and a function that will generate a list of testcases.

Example input and example output are used solely to generate tables in the problem statement.

Each test case in the list fulfills EvaluationTestcase interface in src/scripts/judgeLib.ts

Essentially, test case consists of 4 functions:

• init(db) used to initialize the database before running the user query. Used to create tables and insert initial data.

• check(db, rows) Check if user solution is correct, parameters include rows returned by the user query, and also database instance itself for checking if state of the database is correct after user query e.g. rows inserted, tables created.

• modelSolution() The intended output of the query displayed to the user, displayed to the user if test fails

• inputData() Input of the test case, displayed to the user if test fails

When adding a new task, the task must also be added into the mapping at src/coursedata/[course-id]/tasks.ts. In addition for the task to be visible in the tasklist, id of the task should be in some content section of the course config.

See existing tasks for detailed examples on how they work.

Backend

Admins

Access to backend admin endpoints is restricted based on tmc id admin ids are stored in back/course_config.json and are course speficic.

Model solutions

A model solution is displayed to the user after a task is successfully solved. Model solution is the latest passed submission from any of the model solution users. A list of tmc ids in back/course_config.json specifies which users are model solution users.