create.sql

DROP TABLE octoe_events CASCADE;
DROP TABLE octoe_transitions CASCADE;
DROP TABLE octoe_states CASCADE;
DROP TYPE octoe_state CASCADE;
DROP TYPE octoe_vector CASCADE;
DROP TYPE octoe_event CASCADE;
DROP FUNCTION octoe_vclock();
DROP DOMAIN octoe_token CASCADE;

CREATE DOMAIN octoe_token as int4 CHECK(VALUE >= 0 and VALUE <= 65536);

CREATE TYPE octoe_state as (
  new octoe_token,
  turn_x octoe_token,
  turn_o octoe_token,
  m00 octoe_token,
  m01 octoe_token,
  m02 octoe_token,
  m10 octoe_token,
  m11 octoe_token,
  m12 octoe_token,
  m20 octoe_token,
  m21 octoe_token,
  m22 octoe_token,
  complete octoe_token
);

CREATE TYPE octoe_vector as (
  new int4,
  turn_x int4,
  turn_o int4,
  m00 int4,
  m01 int4,
  m02 int4,
  m10 int4,
  m11 int4,
  m12 int4,
  m20 int4,
  m21 int4,
  m22 int4,
  complete int4
);

CREATE TYPE octoe_event as (
  id varchar(32),
  oid varchar(255),
  seq int4
);

CREATE TABLE octoe_states (
  oid VARCHAR(255) PRIMARY KEY,
  state octoe_state DEFAULT (1,0,0, 1,1,1, 1,1,1, 1,1,1, 0)
);

CREATE TABLE octoe_transitions (
  action VARCHAR(255) PRIMARY KEY,
  vector octoe_vector
);

CREATE TABLE octoe_events (
  oid VARCHAR(255) REFERENCES octoe_states(oid) ON DELETE CASCADE,
  seq SERIAL,
  action VARCHAR(255) NOT NULL,
  hash VARCHAR(32) NOT NULL,
  payload json DEFAULT '{}'
);

ALTER TABLE octoe_events ADD CONSTRAINT oid_seq_pkey PRIMARY KEY (oid, seq);
CREATE INDEX CONCURRENTLY hash_idx on octoe_events (hash);

CREATE OR REPLACE FUNCTION octoe_vclock() RETURNS TRIGGER
AS $$
    DECLARE
        txn octoe_vector;
    BEGIN
        SELECT (vector).* INTO STRICT txn from octoe_transitions where action = NEW.action;

        UPDATE octoe_states set 
            state = (
              (state).new + txn.new,
              (state).turn_x + txn.turn_x,
              (state).turn_o + txn.turn_o,
              (state).m00 + txn.m00,
              (state).m01 + txn.m01,
              (state).m02 + txn.m02,
              
              (state).m10 + txn.m10,
              (state).m11 + txn.m11,
              (state).m12 + txn.m12,
              
              (state).m20 + txn.m20,
              (state).m21 + txn.m21,
              (state).m22 + txn.m22,
              (state).complete + txn.complete
            ) 

        WHERE oid = NEW.oid;

        NEW.hash = md5(row_to_json(NEW)::TEXT);

        RETURN NEW;
      END
$$ LANGUAGE plpgsql;

CREATE TRIGGER octoe_dispatch
BEFORE INSERT on octoe_events
  FOR EACH ROW EXECUTE PROCEDURE octoe_vclock();

INSERT INTO octoe_transitions VALUES ( 'BEGIN', (-1, 1, 0,  0, 0, 0,  0, 0, 0,  0, 0, 0,  0) );
INSERT INTO octoe_transitions VALUES ( 'END_X', (0,-1, 0,   0, 0, 0,  0, 0, 0,  0, 0, 0,  1) );
INSERT INTO octoe_transitions VALUES ( 'END_O', (0, 0,-1,   0, 0, 0,  0, 0, 0,  0, 0, 0,  1) );

INSERT INTO octoe_transitions VALUES ( 'X00',   (0,-1, 1,  -1, 0, 0,  0, 0, 0,  0, 0, 0,  0) );
INSERT INTO octoe_transitions VALUES ( 'X01',   (0,-1, 1,   0,-1, 0,  0, 0, 0,  0, 0, 0,  0) );
INSERT INTO octoe_transitions VALUES ( 'X02',   (0,-1, 1,   0, 0,-1,  0, 0, 0,  0, 0, 0,  0) );

INSERT INTO octoe_transitions VALUES ( 'X10',   (0,-1, 1,   0, 0, 0, -1, 0, 0,  0, 0, 0,  0) );
INSERT INTO octoe_transitions VALUES ( 'X11',   (0,-1, 1,   0, 0, 0,  0,-1, 0,  0, 0, 0,  0) );
INSERT INTO octoe_transitions VALUES ( 'X12',   (0,-1, 1,   0, 0, 0,  0, 0,-1,  0, 0, 0,  0) );

INSERT INTO octoe_transitions VALUES ( 'X20',   (0,-1, 1,   0, 0, 0,  0, 0, 0, -1, 0, 0,  0) );
INSERT INTO octoe_transitions VALUES ( 'X21',   (0,-1, 1,   0, 0, 0,  0, 0, 0,  0,-1, 0,  0) );
INSERT INTO octoe_transitions VALUES ( 'X22',   (0,-1, 1,   0, 0, 0,  0, 0, 0,  0, 0,-1,  0) );

INSERT INTO octoe_transitions VALUES ( 'O00',   (0, 1,-1,  -1, 0, 0,  0, 0, 0,  0, 0, 0,  0) );
INSERT INTO octoe_transitions VALUES ( 'O01',   (0, 1,-1,   0,-1, 0,  0, 0, 0,  0, 0, 0,  0) );
INSERT INTO octoe_transitions VALUES ( 'O02',   (0, 1,-1,   0, 0,-1,  0, 0, 0,  0, 0, 0,  0) );

INSERT INTO octoe_transitions VALUES ( 'O10',   (0, 1,-1,   0, 0, 0, -1, 0, 0,  0, 0, 0,  0) );
INSERT INTO octoe_transitions VALUES ( 'O11',   (0, 1,-1,   0, 0, 0,  0,-1, 0,  0, 0, 0,  0) );
INSERT INTO octoe_transitions VALUES ( 'O12',   (0, 1,-1,   0, 0, 0,  0, 0,-1,  0, 0, 0,  0) );

INSERT INTO octoe_transitions VALUES ( 'O20',   (0, 1,-1,   0, 0, 0,  0, 0, 0, -1, 0, 0,  0) );
INSERT INTO octoe_transitions VALUES ( 'O21',   (0, 1,-1,   0, 0, 0,  0, 0, 0,  0,-1, 0,  0) );
INSERT INTO octoe_transitions VALUES ( 'O22',   (0, 1,-1,   0, 0, 0,  0, 0, 0,  0, 0,-1,  0) );