This document is a list of the available SQL tables and views that you can use in the customization of Free Hero Mesh. You can also add your own tables and views. See also: [SQL functions].

(Note: This is only a draft and is likely to change in future.)

An asterisk in the heading indicates a virtual table; you cannot define triggers on a virtual table. Virtual tables also cannot usually be rolled back.

<h2>CLASSES *</h2>

This table is read-only.

<h2>INVENTORY *</h2>

This table is read-only.

<h2>LEVELS *</h2>

<h2>MESSAGES *</h2>

<h2>MOVES *</h2>

<h2>OBJECTS *</h2>

This table is read-only in game mode; it can be written in edit mode.

<h2>PICTURES</h2>
<verbatim-1>CREATE TEMPORARY TABLE "PICTURES"("ID" INTEGER PRIMARY KEY, "NAME" TEXT COLLATE NOCASE, "OFFSET" INT);</verbatim-1>

A list of all of the pictures that are available; loaded from the .xclass file. Probably only the NAME column is useful to the user; it is the lump name without the .IMG suffix.

While it is possible to write to this table, there is probably no use to do so.

<h2>USERCACHEDATA</h2>
<verbatim-1>CREATE TABLE "USERCACHEDATA"("ID" INTEGER PRIMARY KEY, "FILE" INT, "LEVEL" INT, "NAME" TEXT COLLATE NOCASE, "OFFSET" INT, "DATA" BLOB, "USERSTATE" BLOB);</verbatim-1>

Data of user cache. The following columns are available:
  *  FILE: The ID in the USERCACHEINDEX table of the file it corresponds to.
  *  LEVEL: This is null if it is an unrecognized lump, or else the level number. It is -1 for the LEVEL.IDX lump, and -2 for the CLASS.DEF lump.
  *  NAME: The lump name.
  *  OFFSET: The offset into the file where the data of this lump begins.
  *  DATA: Normally null. If the contents of this lump have been changed but it has not yet been written to the Hamster archive file, this contains the new data for this lump.
  *  USERSTATE: Initially null. Used to store such thing as the sequence of moves the player made (in case you wish to return to the puzzle later), whether or not the solution is locked, etc. This is not saved to the Hamster archive file, and is only included in the user cache. Not related to the "UserState" flag of objects.

Tampering with the data in this table can have unexpected consequences; you should only use this to clear the user state, or to clear the cache entirely (which should not be done while this puzzle set is currently loaded).

<h2>USERCACHEINDEX</h2>
<verbatim-1>CREATE TABLE "USERCACHEINDEX"("ID" INTEGER PRIMARY KEY, "NAME" TEXT, "TIME" INT);</verbatim-1>

Index for user cache. The name is the full path to that file, after all symlinks have been followed. The time is the mtime or ctime (as a UNIX timestamp value), whichever is more recent.

The user cache is used to store the offsets for the .level and .solution files, as well as data that is waiting to be written to those files.

Tampering with the data in this table can have unexpected consequences; you should only use this if you want to clear the cache entirely, or if you have renamed the file and want the cache to continue to work (neither should be done while the puzzle set is currently loaded; use the sqlite3 command-line instead of doing it within Free Hero Mesh).

<h2>VARIABLES</h2>
<verbatim-1>CREATE TEMPORARY TABLE "VARIABLES"("ID" INTEGER PRIMARY KEY, "NAME" TEXT);</verbatim-1>

List of all global and local variables. The ID is the class number left shifted by sixteen bits (the class number is zero for global variables), and then the low sixteen bits have the zero-based variable number. The name omits the or <tt>%</tt> prefix.