Structure of an SQL game

An SQL game combines a database with a story and SQL tasks. It contains a game name, a short teaser, a copyright notice, the initial database and at least one game scene. The SQL Game Editor manual explains how to create, edit and save this content.

Game file

The Game Editor saves games as .xml files. The database and images are embedded in the file, so this single file is all you need to play in the SQL Game Console. Games can also come as .eskuelgame packages with additional information about provenance and licensing.

When you open an .eskuelgame package in the editor, it is converted into an editable XML game file. When saving or exporting, package metadata and included license files are not preserved. You can view them by clicking Licenses in the import notice; keep any required provenance and licensing information separately when sharing the game.

Database

The initial database defines the tables and data the game starts with. The editor supports SQL scripts (.sql) for SQLite and PostgreSQL, binary SQLite database files (such as .db or .sqlite) and Eskuel database packages (.eskueldb). The loaded database is embedded in the game file. Changes during the game are defined by the SQL solutions of manipulation scenes.

You can find examples in the database collection. The SQL Browser manual explains the supported formats and the metadata required for your own PostgreSQL script.

Game scenes

Scenes tell the story in the order in which they are played. There are four scene types: Text, Image, SELECT query and Manipulation. The following examples show how they appear in the Game Editor.

Text scene

A text scene tells the story or explains something. In the Game Console, click Next to move to the next scene. The text can include simple formatting.

Image scene

An image scene displays an image loaded using Open image. PNG, JPEG, WebP, AVIF and GIF are supported. Here too, click Next to move to the next scene.

Large static images are automatically resized when inserted, if necessary. Animated images that exceed the limits must be resized beforehand. Images and the database affect the size of the game file: as it approaches the allowed limit, the editor displays an estimate. If the limit is exceeded, the content must be reduced in size before saving or exporting.

SELECT query

A SELECT task contains task text and an SQL solution. During play, the result of the query entered by the player is compared with the result of this sample solution. What matters is the result, not the wording of the SQL statement; other approaches can also be correct.

You can optionally provide a placeholder for input in the query field, for example SELECT ... FROM passwords. Three options determine how closely the result tables must match. They are disabled by default:

Manipulation scene

A manipulation task asks the player to change the database, for example using INSERT, UPDATE or DELETE. It contains task text, an SQL solution for the intended change and an SQL check query. A placeholder for the SQL field can also be provided.

The check query reads the database state used to identify a correct change. The Game Console compares its result after the player's attempt with its result after the sample solution. Choose a check that also reveals unwanted changes to other affected rows or columns. For example, if a table has been changed, SELECT * FROM inventory can check its entire contents. A query that reads only a single target value may miss side effects.

When testing in the editor, the check query must return different results before and after the sample solution. The message The SQL check query does not show any change caused by the SQL solution means that it does not demonstrate a change. Check the initial data, the solution and the check query.

In the Game Console, you can reset the database to the state appropriate for the current scene if SQL attempts have left it unusable. Your task progress is preserved.

Hints and sample solution

For SELECT and manipulation tasks, you can add help under Hints in the scene dialog:

• Text: Adds a text hint in your own words. Multiple hints can guide the player step by step.
• Expected result: Shows the target result table. For a manipulation task, the check query and its expected result after the correct change are displayed. This hint can be added once per task.
• Sample solution: Adds the final hint Apply sample solution. During play, it appears as Show solution after the other hints. It displays the SQL solution and marks the task as solved using the solution hint.

You can reorder text hints and the expected result hint by drag and drop, and delete them individually. This order determines how they are offered during play; the sample solution always comes last. After a player solves a task independently, the sample solution is available for comparison even without this solution hint.

Text formatting

Scene text and text hints support simple formatting, which appears in the editor preview and the Game Console:

• `SELECT * FROM inventory` displays SQL code with syntax highlighting.
• *italic* displays italic text.
• **bold** displays bold text.

Line breaks and blank lines are preserved. Headings, lists, links and HTML are not interpreted as formatting. Place a backslash before an asterisk or backtick to display it literally, for example \*asterisk\*.