
DROP TABLE IF EXISTS ROOMS;
CREATE TABLE ROOMS(
  DBREF		INT NOT NULL,
  UNIQUE (DBREF),
  NAME		VARCHAR(256)
);

DROP TABLE IF EXISTS PEOPLE;
CREATE TABLE PEOPLE(
  DBREF		INT NOT NULL,
  UNIQUE (DBREF),
  NAME		VARCHAR(256),
  WIZARD	BOOLEAN,
  OWNER		VARCHAR(256)
);

DROP TABLE IF EXISTS COMMANDS;
CREATE TABLE COMMANDS(
  TIME		DATETIME,
  PERSON	INT REFERENCES PEOPLE (DBREF) MATCH FULL ON DELETE RESTRICT ON UPDATE CASCADE,
  ROOM		INT REFERENCES ROOMS (DBREF) MATCH FULL ON DELETE RESTRICT ON UPDATE CASCADE,
  FORCEDBY	INT REFERENCES PEOPLE (DBREF) MATCH FULL ON DELETE RESTRICT ON UPDATE CASCADE,
  COMMAND	TEXT,
  UNIQUE (TIME,PERSON,COMMAND(100))
);

DROP TABLE IF EXISTS CONNECTS;
CREATE TABLE CONNECTS(
  TIME		DATETIME,
  PERSON	INT REFERENCES PEOPLE (DBREF) MATCH FULL ON DELETE RESTRICT ON UPDATE CASCADE,
  SOCKET	INT,
  EVENT		VARCHAR(4),
  UNIQUE (TIME,SOCKET,EVENT)
);

