SET statement_timeout = 0;

SET lock_timeout = 0;

SET client_encoding = 'UTF8';

SET standard_conforming_strings = on;

SET check_function_bodies = false;

SET client_min_messages = warning;

CREATE DATABASE robotgame WITH TEMPLATE = template0 ENCODING = 'UTF8' LC_COLLATE = 'en_US.UTF-8' LC_CTYPE = 'en_US.UTF-8';

ALTER DATABASE robotgame OWNER TO robot;

\connect robotgame

SET statement_timeout = 0;

SET lock_timeout = 0;

SET client_encoding = 'UTF8';

SET standard_conforming_strings = on;

SET check_function_bodies = false;

SET client_min_messages = warning;

CREATE EXTENSION IF NOT EXISTS plpgsql WITH SCHEMA pg_catalog;

COMMENT ON EXTENSION plpgsql IS 'PL/pgSQL procedural language';

SET search_path = public, pg_catalog;

SET default_tablespace = '';

SET default_with_oids = false;

CREATE TABLE fail_bots (

id integer NOT NULL,

hash character(32) NOT NULL,

code text NOT NULL

);

ALTER TABLE fail_bots OWNER TO robot;

CREATE SEQUENCE fail_bots_id_seq

START WITH 1

INCREMENT BY 1

NO MINVALUE

NO MAXVALUE

CACHE 1;

ALTER TABLE fail_bots_id_seq OWNER TO robot;

ALTER SEQUENCE fail_bots_id_seq OWNED BY fail_bots.id;

CREATE TABLE history (

id integer NOT NULL,

match_id integer NOT NULL,

data text,

"timestamp" integer NOT NULL

);

ALTER TABLE history OWNER TO robot;

CREATE SEQUENCE history_id_seq

START WITH 1

INCREMENT BY 1

NO MINVALUE

NO MAXVALUE

CACHE 1;

ALTER TABLE history_id_seq OWNER TO robot;

ALTER SEQUENCE history_id_seq OWNED BY history.id;

CREATE TABLE matches (

id integer NOT NULL,

r1_id integer,

r2_id integer,

winner integer,

"timestamp" integer DEFAULT date_part('epoch'::text, now()),

ranked boolean DEFAULT false,

state smallint DEFAULT 0,

r1_score integer,

r2_score integer,

r1_rating double precision,

r2_rating double precision,

seed integer DEFAULT 0,

r1_ranking integer,

r2_ranking integer,

k_factor integer DEFAULT 32,

r1_time double precision,

r2_time double precision

);

ALTER TABLE matches OWNER TO robot;

CREATE SEQUENCE matches_id_seq

START WITH 1

INCREMENT BY 1

NO MINVALUE

NO MAXVALUE

CACHE 1;

ALTER TABLE matches_id_seq OWNER TO robot;

ALTER SEQUENCE matches_id_seq OWNED BY matches.id;

CREATE TABLE os_bots (

id integer NOT NULL,

hash character(32) NOT NULL,

code text NOT NULL

);

ALTER TABLE os_bots OWNER TO robot;

CREATE SEQUENCE os_bots_id_seq

START WITH 1

INCREMENT BY 1

NO MINVALUE

NO MAXVALUE

CACHE 1;

ALTER TABLE os_bots_id_seq OWNER TO robot;

ALTER SEQUENCE os_bots_id_seq OWNED BY os_bots.id;

CREATE TABLE robots (

id integer NOT NULL,

user_id integer,

code text DEFAULT ''::text,

rating double precision,

name text,

saved boolean DEFAULT false,

compiled_code text DEFAULT ''::text,

passed boolean DEFAULT false,

changed_since_sbtest boolean DEFAULT true,

compiled boolean DEFAULT false,

open_source boolean DEFAULT false,

disabled boolean DEFAULT false,

automatch boolean DEFAULT true,

last_updated integer DEFAULT date_part('epoch'::text, now()),

last_opponent integer,

deleted boolean DEFAULT false,

last_rating double precision DEFAULT 1200 NOT NULL,

priority double precision DEFAULT 1.0 NOT NULL,

last_match integer,

"time" double precision DEFAULT 0 NOT NULL,

fast boolean,

short boolean,

winrate double precision DEFAULT 1.0

);

ALTER TABLE robots OWNER TO robot;

CREATE SEQUENCE robots_id_seq

START WITH 1

INCREMENT BY 1

NO MINVALUE

NO MAXVALUE

CACHE 1;

ALTER TABLE robots_id_seq OWNER TO robot;

ALTER SEQUENCE robots_id_seq OWNED BY robots.id;

CREATE TABLE users (

id integer NOT NULL,

username character varying(40) NOT NULL,

pw_hash character varying(40) NOT NULL,

pw_salt text,

about text DEFAULT ''::text,

challenges integer DEFAULT 0,

vip boolean DEFAULT false,

account_type smallint DEFAULT 0,

registered_on integer DEFAULT date_part('epoch'::text, now()),

last_active integer DEFAULT 0,

extra_bots integer DEFAULT 0

);

ALTER TABLE users OWNER TO robot;

CREATE SEQUENCE users_id_seq

START WITH 1

INCREMENT BY 1

NO MINVALUE

NO MAXVALUE

CACHE 1;

ALTER TABLE users_id_seq OWNER TO robot;

ALTER SEQUENCE users_id_seq OWNED BY users.id;

ALTER TABLE ONLY fail_bots ALTER COLUMN id SET DEFAULT nextval('fail_bots_id_seq'::regclass);

ALTER TABLE ONLY history ALTER COLUMN id SET DEFAULT nextval('history_id_seq'::regclass);

ALTER TABLE ONLY matches ALTER COLUMN id SET DEFAULT nextval('matches_id_seq'::regclass);

ALTER TABLE ONLY os_bots ALTER COLUMN id SET DEFAULT nextval('os_bots_id_seq'::regclass);

ALTER TABLE ONLY robots ALTER COLUMN id SET DEFAULT nextval('robots_id_seq'::regclass);

ALTER TABLE ONLY users ALTER COLUMN id SET DEFAULT nextval('users_id_seq'::regclass);

ALTER TABLE ONLY fail_bots

ADD CONSTRAINT fail_bots_hash_key UNIQUE (hash);

ALTER TABLE ONLY fail_bots

ADD CONSTRAINT fail_bots_pkey PRIMARY KEY (id);

ALTER TABLE ONLY history

ADD CONSTRAINT history_pkey PRIMARY KEY (id);

ALTER TABLE ONLY matches

ADD CONSTRAINT matches_pkey PRIMARY KEY (id);

ALTER TABLE ONLY os_bots

ADD CONSTRAINT os_bots_hash_key UNIQUE (hash);

ALTER TABLE ONLY os_bots

ADD CONSTRAINT os_bots_pkey PRIMARY KEY (id);

ALTER TABLE ONLY robots

ADD CONSTRAINT robots_pkey PRIMARY KEY (id);

ALTER TABLE ONLY users

ADD CONSTRAINT users_pkey PRIMARY KEY (id);

CREATE INDEX history_timestamp_idx ON history USING btree ("timestamp");

CREATE INDEX match_id ON history USING btree (match_id);

CREATE INDEX matches_id_idx ON matches USING btree (id) WHERE ((state = 0) OR (state = 1));

CREATE INDEX matches_timestamp ON matches USING btree ("timestamp");

CREATE INDEX r1_id ON matches USING btree (r1_id);

CREATE INDEX r2_id ON matches USING btree (r2_id);

CREATE INDEX rating ON robots USING btree (rating);

CREATE INDEX robots_id_idx ON robots USING btree (id) WHERE (fast OR short);

CREATE INDEX user_id ON robots USING btree (user_id);

ALTER TABLE ONLY history

ADD CONSTRAINT history_match_id_fkey FOREIGN KEY (match_id) REFERENCES matches(id);

ALTER TABLE ONLY matches

ADD CONSTRAINT matches_r1_id_fkey FOREIGN KEY (r1_id) REFERENCES robots(id);

ALTER TABLE ONLY matches

ADD CONSTRAINT matches_r2_id_fkey FOREIGN KEY (r2_id) REFERENCES robots(id);

ALTER TABLE ONLY robots

ADD CONSTRAINT robots_user_id_fkey FOREIGN KEY (user_id) REFERENCES users(id) ON UPDATE CASCADE ON DELETE CASCADE;

REVOKE ALL ON SCHEMA public FROM PUBLIC;

REVOKE ALL ON SCHEMA public FROM postgres;

GRANT ALL ON SCHEMA public TO postgres;

GRANT ALL ON SCHEMA public TO robot;

GRANT ALL ON SCHEMA public TO PUBLIC;

REVOKE ALL ON TABLE matches FROM PUBLIC;

REVOKE ALL ON TABLE matches FROM robot;

GRANT ALL ON TABLE matches TO robot;

REVOKE ALL ON SEQUENCE matches_id_seq FROM PUBLIC;

REVOKE ALL ON SEQUENCE matches_id_seq FROM robot;

GRANT ALL ON SEQUENCE matches_id_seq TO robot;

REVOKE ALL ON TABLE robots FROM PUBLIC;

REVOKE ALL ON TABLE robots FROM robot;

GRANT ALL ON TABLE robots TO robot;

REVOKE ALL ON SEQUENCE robots_id_seq FROM PUBLIC;

REVOKE ALL ON SEQUENCE robots_id_seq FROM robot;

GRANT ALL ON SEQUENCE robots_id_seq TO robot;

REVOKE ALL ON TABLE users FROM PUBLIC;

REVOKE ALL ON TABLE users FROM robot;

GRANT ALL ON TABLE users TO robot;

REVOKE ALL ON SEQUENCE users_id_seq FROM PUBLIC;

REVOKE ALL ON SEQUENCE users_id_seq FROM robot;

GRANT ALL ON SEQUENCE users_id_seq TO robot;