Model

The model uses Postgresql and its server side programing language plpgsql

Database schema

These are specific to each puzzle or game, prefaced with name_* for each table or view.

CREATE TABLE IF NOT EXISTS blocks_states (
    id            SERIAL PRIMARY KEY, 
    state         TEXT UNIQUE,
    reward        TEXT,
    terminal      BOOLEAN
);

CREATE TABLE IF NOT EXISTS blocks_moves (
    id            SERIAL PRIMARY KEY,
    move          TEXT UNIQUE
);

CREATE TABLE IF NOT EXISTS blocks_nexts (
    state_id      INTEGER REFERENCES blocks_states(id),
    move_id       INTEGER REFERENCES blocks_moves(id),
    next_id       INTEGER REFERENCES blocks_states(id),
    PRIMARY KEY   (next_id, move_id)
);

CREATE OR REPLACE VIEW blocks_transitions AS
  SELECT states.state AS state,
         blocks_moves.move AS move, 
         nexts.state AS next
  FROM blocks_states AS states,
       blocks_moves,
       blocks_states AS nexts,
       blocks_nexts
  WHERE blocks_nexts.state_id = states.id AND
        blocks_nexts.move_id = blocks_moves.id AND
        blocks_nexts.next_id = nexts.id;

Server side functions

These are kept generic, with the name of the puzzle or game passed as the first parameter.

functions.sql

CREATE OR REPLACE FUNCTION add_transition(tablename TEXT, state0 TEXT, state_reward TEXT, state_terminal BOOLEAN, 
                                                          move0 TEXT, 
                                                          next TEXT, next_reward TEXT, next_terminal BOOLEAN) 
  RETURNS void AS $$
  DECLARE
    state_id0 INTEGER;
    move_id0 INTEGER;
    next_id0 INTEGER;
    dummy INTEGER;
  BEGIN
    EXECUTE format('SELECT id FROM %1$s_states WHERE state = %2$L', tablename, state0) INTO state_id0;
    IF state_id0 IS NULL THEN
      EXECUTE format('INSERT INTO %1$s_states (state, reward, terminal)
        VALUES (%2$L, %3$L, %4$L)
        RETURNING id', tablename, state0, state_reward, state_terminal) INTO state_id0;
    END IF;
    EXECUTE format('SELECT id FROM %1$s_moves WHERE %1$s_moves.move = %2$L', tablename, move0) INTO move_id0;
    IF move_id0 IS NULL THEN
      EXECUTE format('INSERT INTO %1$s_moves (move) VALUES (%2$L) RETURNING id', tablename, move0) INTO move_id0;
    END IF;
    EXECUTE format('SELECT id FROM %1$s_states WHERE %1$s_states.state = %2$L', tablename, next) INTO next_id0;
    IF next_id0 IS NULL THEN
      EXECUTE format('INSERT INTO %1$s_states (state, reward, terminal)
        VALUES (%2$L, %3$L, %4$L) RETURNING id', tablename, next, next_reward, next_terminal) INTO next_id0;
      -- need to update all parents up to root if next_terminal is true
      IF next_terminal THEN
        -- debugging
        RAISE NOTICE '%', next;
      END IF;
    END IF;
    EXECUTE format('SELECT state_id FROM %1$s_nexts
      WHERE %1$s_nexts.state_id = %2$L
        AND %1$s_nexts.move_id = %3$L
        AND %1$s_nexts.next_id = %4$L', tablename, state_id0, move_id0, next_id0) INTO dummy;
    IF dummy IS NULL THEN
      EXECUTE format('INSERT INTO %1$s_nexts (state_id, move_id, next_id)
        VALUES (%2$L, %3$L, %4$L)', tablename, state_id0, move_id0, next_id0); 
    END IF;
  END; 
$$ LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION unexplored(tablename TEXT)
RETURNS SETOF TEXT AS $$
  BEGIN
  RETURN QUERY EXECUTE format('SELECT %1$s_transitions.next 
    FROM %1$s_transitions, %1$s_states 
    WHERE %1$s_transitions.next = %1$s_states.state 
    AND %1$s_states.terminal = FALSE
  EXCEPT
  SELECT %1$s_transitions.state FROM %1$s_transitions', tablename);
  END;
$$ LANGUAGE plpgsql;

/*
SELECT substring(reward, 'goal\((\w+)') AS player, cast(substring(reward, '\d+') AS INTEGER) AS value
FROM %1$s_states
WHERE terminal;

*/