sql.doc

This document explains the SQL functions and SQL tables which are
available for the user customization in Free Hero Mesh. (You cannot
use SQL to define the rules of the game.)


=== Functions ===

Asterisks denote aggregate/window functions.

BASENAME()
  The base name, which is a copy of the first command-line argument (not
  counting the switches or the program name).

BCAT(...)
  Concatenate several blobs. Nulls are skipped, if any.

BEST_MOVE_LIST()
  Returns the best personal move list in the user cache file (only valid if
  .saveSolution.private is true), as a blob. If there isn't any saved, then
  this is null.

BEST_SCORE()
  Returns the best score from the best move list (if it has been saved).
  If no score has been explicitly specified, then this is equal to the
  number of moves. If no score has been stored, then it is null.

BYTE(...)
  Make a blob; each argument is a number, of which the low 8-bits are used
  to make the value of one byte in the blob, so the size of the blob will
  then be the same as the number of arguments.

CL(text)
  Returns the class number, given the class name. If there is no such
  class, then the result is null. (This may be useful as a shortcut when
  used with the "OBJECTS" or "CLASSES" table.)

CLASS_DATA(id,info)
  Returns data about a class.
   0 = ID
   1 = Temperature
   2 = Shape
   7 = First usable image
   12 = Misc4
   13 = Misc5
   14 = Misc6
   15 = Misc7
   18 = Arrivals
   19 = Departures
   33 = Invisible
   35 = UserState
   37 = Player
   39 = Stealthy
   40 = VisualOnly
   64 = Density
   65 = Volume
   66 = Strength
   67 = Weight
   69 = Height
   70 = Climb
   128 = Volume fraction
   130 = CollisionLayers
   132 = Compatible

CVALUE(number)
  Makes a game value of type 'class', given the class number. You can also
  specify the class name instead of the number.

GAMEOVER()
  Returns +1 if you win, -1 if you lose, or 0 otherwise. This value is not
  meaningful in the editor.

HASH(data,algorithm)
  Make the hash of the data as a binary blob. See hash.h for a list of the
  valid numbers to use as the hash algorithm numbers.

HAS_XY_INPUT()
  Returns nonzero if this puzzle set has coordinate input, or zero if it
  does not have coordinate input.

HEROMESH_ESCAPE(blob)
  Converts blob representation of a game string into escaped format.

HEROMESH_TYPE(value)
  The type of a game value, given as a 64-bit integer. The types are
  'class', 'number', 'string', 'object', and 'sound'.

HEROMESH_UNESCAPE(text)
  Converts escaped representation of a game string into text format.

INRECT(x,y)
  True if the coordinates are inside of the current selection rectangle.
  If no selection rectangle is set, then the result is null.

LEVEL()
  The one-based order number of the current level.

LEVEL_CACHEID()
  The user cache ID of the level file.

LEVEL_CODE()
  The level code number of the current level.

LEVEL_ID()
  The ID number of the current level.

LEVEL_TITLE()
  The title of the current level, as a blob.

LEVEL_VERSION()
  The version number of the current level.

LOAD_LEVEL(level_id)
  Only valid when Free Hero Mesh is invoked with the -x switch. Loads
  the level with the specified ID number.

MARK()
  The number of moves in the replay list before the mark.

MAX_LEVEL()
  The number of levels in this puzzle set (equal to the maximum valid
  level order number).

MVALUE(number)
  Makes a game value of type 'message'.

MODSTATE()
  The SDL keyboard modifier state.

MOVE_LIST()
  Return the current move list as a blob.

MOVECOUNT()
  The total number of moves in the replay list.

MOVENUMBER()
  Return the number of moves executed so far since the level restarted.

NVALUE(number)
  Makes a game value of type 'number'. Same as ZERO_EXTEND.

OVALUE(number)
  Makes a game value of type 'object'. The argument is the ID number of
  the object in the OBJECTS table.

PICTURE_SIZE()
  Tell you the picture size.

PIPE(command)
  Execute a operating system shell command and return the output of that
  command as a blob.

READ_LUMP_AT(offset,ptr)
  Used internally; there is no way to use this in user SQL codes.

RECT_X0()
  X0 coordinate of selection rectangle.

RECT_X1()
  X1 coordinate of selection rectangle.

RECT_Y0()
  Y0 coordinate of selection rectangle.

RECT_Y1()
  Y1 coordinate of selection rectangle.

RESOURCE(...)
  Given the list of resource names, read a value from the X resource
  manager. Returns null if there is no such resource value.

SIGN_EXTEND(number)
  Sign extends a 32-bit number to 64-bits.

SOLUTION_CACHEID()
  The user cache ID of the level file.

SOLUTION_MOVE_LIST(boolean)
  Returns the solution move list. If the boolean value is true, then it
  always loads it even if the level version number doesn't match.

TRACE_OFF()
  Disables tracing when called.

TRACE_ON()
  Enables tracing when called.

XY(x,y)
  Many bindings require a position as their first argument; use this
  function to combine X/Y coordinates into a single value for this.
  Coordinates are one-based. This is the same as (x+y*64).

ZERO_EXTEND(number)
  Zero extends a 32-bit number to 64-bits. Same as NVALUE.


=== Tables ===

Asterisks denote virtual tables. (The only disk tables are USERCACHEDATA
and USERCACHEINDEX; all others are eiter temporary or virtual.)

CREATE TABLE "BIZARRO_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); *
  See OBJECTS; this is for the bizarro world only.

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); *
  A list of classes in the current puzzle set; mostly read-only. Only
  QUIZ, TRACEIN, and TRACEOUT are writable. If TRACEIN is true then it
  will trace messages received by this class (if tracing is enabled). If
  TRACEOUT is true then it will trace messages sent by this class (if
  tracing is enabled).

CREATE TEMPORARY TABLE "DIVISIONS"("HEADING" BLOB NOT NULL, "FIRST" INT
NOT NULL);
  The list of level divisions. The HEADING is the (unformatted) heading
  text, and the FIRST is the order number (not ID number) of the first
  level to which this heading applies. (Note: Writing to this table by
  use of SQL statements will not automatically update the puzzle set.
  However, if you use SQL and then use the level index editor, then that
  will make the changes permanent.)

CREATE TABLE "INVENTORY"("ID" INTEGER PRIMARY KEY, "CLASS" INT, "IMAGE"
INT, "VALUE" INT); *
  This table contains the current inventory, and is read-only. It is not
  meaningful in the editor.

CREATE TEMPORARY TABLE "LEVELS"("ID" INTEGER PRIMARY KEY, "ORD" INT,
"CODE" INT, "WIDTH" INT, "HEIGHT" INT, "TITLE" BLOB, "SOLVED" INT,
"SOLVABLE" INT); *
  This table contains a list of the levels in the current puzzle set. The
  SOLVED column is 1 if the current user has solved the level or 0
  otherwise; the SOLVABLE column is 1 if a solution is stored for this
  level or 0 otherwise (it doesn't validate the solution; to do that, use
  the -a switch instead). Although you can write to this table after it
  has been read once (this table uses lazy loading), you should not do so,
  since the changes will not be saved. There may be additional columns in
  this table in some puzzle sets (defined in the (LevelTable) block in the
  class definition file); if so, these extra columns come after the ones
  listed above, and their names will always start with a underscore.

CREATE TABLE "MESSAGES"("ID" INTEGER PRIMARY KEY, "NAME" TEXT, "TRACE"
INT); *
  The list of messages in the current puzzle set; mostly read-only. Only
  TRACE is writable; if true, this message will be traced.

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); *
  List of objects. Read-only during the game, writable in the editor.
  CLASS is a class number (the ID in the CLASSES table), DIR is the
  direction (where 0 is east, 7 is southeast), and MISC1, MISC2, MISC3
  are the game values for the initial Misc values of this object; they
  must be numbers, classes, messages, or strings. Once an object is
  created, you cannot change its class; you must delete it and then add
  a new object of the correct class. DENSITY is read-only, but you can
  use ORDER BY DENSITY to sort in the stacking order of the objects.
  UP and DOWN are also read-only.

CREATE TEMPORARY TABLE "PICTURES"("ID" INTEGER PRIMARY KEY, "NAME" TEXT
COLLATE NOCASE, "OFFSET" INT, "DEPENDENT" INT, "MISC" BLOB);
  List of all pictures available in this puzzle set.

CREATE TABLE "PLAYFIELD"("X" INT, "Y" INT, "OBJ" INT, "BIZARRO_OBJ" INT); *
  All playfield cells, with their coordinates and bottom objects.

CREATE TABLE "USERCACHEDATA"("ID" INTEGER PRIMARY KEY, "FILE" INT,
"LEVEL" INT, "NAME" TEXT COLLATE NOCASE, "OFFSET" INT, "DATA" BLOB,
"USERSTATE" BLOB);
  Contains the user cache data for the .level and .solution files of each
  puzzle set, in order to speed up loading and saving. The DATA will be
  null unless the data has changed, in which case the new data is stored
  here; it will later rewrite the Hamster archive with the new data and
  then it will be set to null again. USERSTATE contains user data such as
  the list of moves made by the player. FILE is the ID in the user cache
  index, and indicates which Hamster archive file this lump belongs to.
  (The USERSTATE column of this SQL table is not related to the UserState
  variable of objects in the game.)

CREATE TABLE "USERCACHEINDEX"("ID" INTEGER PRIMARY KEY, "NAME" TEXT,
"TIME" INT);
  The user cache index; contains one record for each .level and .solution
  file of all puzzle sets which have been loaded. NAME is the full path,
  and TIME is the UNIX timestamp. If you want to delete a record, then
  you should also delete all records from USERCACHEDATA whose FILE value
  is the same as the ID value in this table for which it is deleted. You
  should not touch it while Free Hero Mesh is running, though. If it is a
  composite puzzle set, then NAME consists of the full path prefixed by
  L// for the level file or S// for the solution file (this will then
  probably be followed by a third slash indicating the root directory).

CREATE TEMPORARY TABLE "VARIABLES"("ID" INTEGER PRIMARY KEY, "NAME" TEXT);
  List of all global and local user variables used in the game.