D 2018-11-29T02:50:38.454
L SQL\stables
P 2389a67c6dcd1558e1c495d716d386de030aa74b
U zzo38
W 7218
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 (eponymous only) virtual table; you cannot define triggers on a virtual table. Virtual tables also cannot usually be rolled back.
<h2>CLASSES *</h2>
<verbatim-1>CREATE TABLE "CLASSES"("ID" INTEGER PRIMARY KEY, "NAME" TEXT, "EDITORHELP" TEXT, "HELP" TEXT,
"INPUT" INT, "QUIZ" INT, "TRACEIN" INT, "TRACEOUT" INT, "GROUP" TEXT, "PLAYER" INT);</verbatim-1>
List of all classes. This table is mostly read-only; only the "QUIZ", "TRACEIN", and "TRACEOUT" columns can be altered, and their values must be one or zero. Inserting and deleting rows is not possible.
The columns are:
* ID: The class ID number.
* NAME: The class name (without $ at first).
* EDITORHELP: Help text for editing.
* HELP: Help text for playing.
* INPUT: One if objects of this class receive input (without a popup quiz), or zero if it does not.
* QUIZ: One to hide the variables of this object from the player, or zero if it will not hide.
* TRACEIN: One to enable tracing when objects of this class receive messages (if tracing is enabled), zero if not.
* TRACEOUT: One to enable tracing when objects of this class send messages (if tracing is enabled), zero if not.
* GROUP: Normally null. If this is a group of classes, then it contains the names of the classes that the group consists of, with spaces in between, and a space before and after.
* PLAYER: One if this class "is the player", or zero if not.
<h2>INVENTORY *</h2>
This table is read-only.
<h2>LEVELS *</h2>
<h2>MESSAGES *</h2>
<verbatim-1>CREATE TABLE "MESSAGES"("ID" INTEGER PRIMARY KEY, "NAME" TEXT, "TRACE" INT);</verbatim-1>
List of all built-in and user-defined messages. The "TRACE" column initially stores zero, but you may store either zero or one. If zero, tracing this message is suppressed; if one, tracing this message is enabled if tracing has been enabled.
<h2>MOVES *</h2>
<h2>OBJECTS *</h2>
<verbatim-1>CREATE TABLE "OBJECTS"("ID" INTEGER PRIMARY KEY, "CLASS" INT, "MISC1" INT, "MISC2" INT, "MISC3" INT,
"IMAGE" INT, "DIR" INT, "X" INT, "Y" INT, "UP" INT, "DOWN" INT, "DENSITY" INT HIDDEN);</verbatim-1>
List of all objects. Meaning of columns:
* ID: The object ID number. Use the OVALUE() function to create the game value representing a pointer to this object.
* CLASS: Class number (the ID from the CLASSES table).
* MISC1, MISC2, MISC3: The Misc1, Misc2, and Misc3 values (which are 64-bit game values). When setting these values, use the CVALUE(), MVALUE(), and NVALUE() functions. Note that numeric values are limited to 16-bits when setting them in the editor. (In the game play, these values are read-only but are not limited to the values specified here.)
* IMAGE: Zero-based image number. Valid range depends on the class.
* DIR: Direction from 0 (east) to 7 (southeast).
* X, Y: The one-based X and Y coordinates of this object. If these values are updated, the object is automatically unlinked and relinked into the board, changing the UP and DOWN values of not only this but also other objects, too.
* UP, DOWN: The object above and below this one; null if there isn't any.
* DENSITY: The density of the object (lower numbers are on top). If you use ORDER BY DENSITY or ORDER BY DENSITY DESC it will automatically order objects with the same density correctly relative to each other too (according to the UP and DOWN values).
This table is read-only in game mode; it can be written in edit mode. Once an object is created, you cannot change its CLASS (attempting to do so with a UPDATE statement is an error). Any values specified for UP and DOWN in a INSERT or UPDATE statement are ignored (both for INSERT and UPDATE) (they are automatically set when necessary).
Note: SQLite currently does not implement the truncate optimization for virtual tables. This means that a "DELETE FROM OBJECTS;" SQL statement will not be as efficient as using Free Hero Mesh's "clear level" command.
<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 user 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 <tt>@</tt> or <tt>%</tt> prefix.
Z ef771c24f3caf7f911a19479100b715b