--
-- PostgreSQL database dump
--
-- Started on 2009-11-12 08:57:15 MSK
SET statement_timeout = 0;
SET client_encoding = 'UTF8';
SET standard_conforming_strings = off;
SET check_function_bodies = false;
SET client_min_messages = warning;
SET escape_string_warning = off;
--
-- TOC entry 1840 (class 1262 OID 16388)
-- Name: pyfights; Type: DATABASE; Schema: -; Owner: superme
--
--CREATE DATABASE pyfights WITH TEMPLATE = template0 ENCODING = 'UTF8' LC_COLLATE = 'en_US.UTF-8' LC_CTYPE = 'en_US.UTF-8';
--ALTER DATABASE pyfights OWNER TO superme;
--\connect pyfights
SET statement_timeout = 0;
SET client_encoding = 'UTF8';
SET standard_conforming_strings = off;
SET check_function_bodies = false;
SET client_min_messages = warning;
SET escape_string_warning = off;
--
-- TOC entry 321 (class 2612 OID 16391)
-- Name: plpgsql; Type: PROCEDURAL LANGUAGE; Schema: -; Owner: postgres
--
--CREATE PROCEDURAL LANGUAGE plpgsql;
--ALTER PROCEDURAL LANGUAGE plpgsql OWNER TO postgres;
SET search_path = public, pg_catalog;
--
-- TOC entry 304 (class 1247 OID 16393)
-- Dependencies: 6
-- Name: game_status; Type: TYPE; Schema: public; Owner: superme
--
CREATE TYPE game_status AS ENUM (
'in_queue',
'playing',
'finished',
'failed'
);
ALTER TYPE public.game_status OWNER TO superme;
--
-- TOC entry 19 (class 1255 OID 16398)
-- Dependencies: 6 321
-- Name: update_game_score(); Type: FUNCTION; Schema: public; Owner: superme
--
CREATE FUNCTION update_game_score() RETURNS trigger
LANGUAGE plpgsql
AS $$
begin
if TG_OP = 'INSERT' then
update games set
score1 = score1 + NEW.score1,
score2 = score2 + NEW.score2,
score3 = score3 + NEW.score3,
score4 = score4 + NEW.score4
where
id = NEW.game_id;
return NEW;
elsif TG_OP = 'UPDATE' then
update games set
score1 = score1 + NEW.score1 - OLD.score1,
score2 = score2 + NEW.score2 - OLD.score2,
score3 = score3 + NEW.score3 - OLD.score3,
score4 = score4 + NEW.score4 - OLD.score4
where
id = NEW.game_id;
return NEW;
elsif TG_OP = 'DELETE' then
update games set
score1 = score1 - OLD.score1,
score2 = score2 - OLD.score2,
score3 = score3 - OLD.score3,
score4 = score4 - OLD.score4
where
id = OLD.game_id;
return OLD;
end if;
end;
$$;
ALTER FUNCTION public.update_game_score() OWNER TO superme;
--
-- TOC entry 20 (class 1255 OID 16399)
-- Dependencies: 321 6
-- Name: update_game_time(); Type: FUNCTION; Schema: public; Owner: superme
--
CREATE FUNCTION update_game_time() RETURNS trigger
LANGUAGE plpgsql
AS $$
begin
if TG_OP = 'INSERT' then
if NEW.status = 'finished'::game_status then
NEW.played_date = current_timestamp;
end if;
elsif TG_OP = 'UPDATE' then
if OLD.status != NEW.status AND NEW.status = 'finished'::game_status then
NEW.played_date = current_timestamp;
end if;
end if;
return NEW;
end;
$$;
ALTER FUNCTION public.update_game_time() OWNER TO superme;
SET default_tablespace = '';
SET default_with_oids = false;
--
-- TOC entry 1508 (class 1259 OID 16400)
-- Dependencies: 1793 1794 1795 1796 1797 1798 1799 1800 6 304
-- Name: games; Type: TABLE; Schema: public; Owner: superme; Tablespace:
--
CREATE TABLE games (
id bigint NOT NULL,
python1_id bigint NOT NULL,
python2_id bigint NOT NULL,
python3_id bigint,
python4_id bigint,
schedule_date timestamp without time zone DEFAULT now() NOT NULL,
played_date timestamp without time zone,
score1 smallint DEFAULT 0 NOT NULL,
score2 smallint DEFAULT 0 NOT NULL,
score3 smallint DEFAULT 0 NOT NULL,
score4 smallint DEFAULT 0 NOT NULL,
round_num smallint DEFAULT 20 NOT NULL,
status game_status DEFAULT 'in_queue'::game_status NOT NULL,
seed smallint DEFAULT (random() * (1000)::double precision) NOT NULL
);
ALTER TABLE public.games OWNER TO superme;
--
-- TOC entry 1509 (class 1259 OID 16411)
-- Dependencies: 1508 6
-- Name: games_id_seq; Type: SEQUENCE; Schema: public; Owner: superme
--
CREATE SEQUENCE games_id_seq
START WITH 1000
INCREMENT BY 1
NO MAXVALUE
MINVALUE 1000
CACHE 1;
ALTER TABLE public.games_id_seq OWNER TO superme;
--
-- TOC entry 1844 (class 0 OID 0)
-- Dependencies: 1509
-- Name: games_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: superme
--
ALTER SEQUENCE games_id_seq OWNED BY games.id;
--
-- TOC entry 1510 (class 1259 OID 16413)
-- Dependencies: 1802 6
-- Name: memslots; Type: TABLE; Schema: public; Owner: superme; Tablespace:
--
CREATE TABLE memslots (
weight smallint DEFAULT 0 NOT NULL,
python_id bigint NOT NULL,
slot_data character varying(1000) NOT NULL
);
ALTER TABLE public.memslots OWNER TO superme;
--
-- TOC entry 1511 (class 1259 OID 16420)
-- Dependencies: 1803 1804 1805 6
-- Name: pythons; Type: TABLE; Schema: public; Owner: superme; Tablespace:
--
CREATE TABLE pythons (
id bigint NOT NULL,
name character varying(300) NOT NULL,
owner_id integer NOT NULL,
create_date timestamp without time zone DEFAULT now() NOT NULL,
last_change_date timestamp without time zone DEFAULT now() NOT NULL,
version smallint DEFAULT 0 NOT NULL
);
ALTER TABLE public.pythons OWNER TO superme;
--
-- TOC entry 1512 (class 1259 OID 16426)
-- Dependencies: 6 1511
-- Name: pythons_id_seq; Type: SEQUENCE; Schema: public; Owner: superme
--
CREATE SEQUENCE pythons_id_seq
START WITH 1000
INCREMENT BY 1
NO MAXVALUE
MINVALUE 1000
CACHE 1;
ALTER TABLE public.pythons_id_seq OWNER TO superme;
--
-- TOC entry 1848 (class 0 OID 0)
-- Dependencies: 1512
-- Name: pythons_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: superme
--
ALTER SEQUENCE pythons_id_seq OWNED BY pythons.id;
--
-- TOC entry 1513 (class 1259 OID 16428)
-- Dependencies: 1807 1808 1809 1810 1811 6
-- Name: rounds; Type: TABLE; Schema: public; Owner: superme; Tablespace:
--
CREATE TABLE rounds (
move_order character varying(4) NOT NULL,
game_id bigint NOT NULL,
num smallint DEFAULT 0 NOT NULL,
move_data character varying(5000) NOT NULL,
score1 smallint DEFAULT 0 NOT NULL,
score2 smallint DEFAULT 0 NOT NULL,
score3 smallint DEFAULT 0 NOT NULL,
score4 smallint DEFAULT 0 NOT NULL
);
ALTER TABLE public.rounds OWNER TO superme;
--
-- TOC entry 1514 (class 1259 OID 16439)
-- Dependencies: 1812 6
-- Name: users; Type: TABLE; Schema: public; Owner: superme; Tablespace:
--
CREATE TABLE users (
id integer NOT NULL,
name character varying(300) NOT NULL,
create_date timestamp without time zone DEFAULT now() NOT NULL
);
ALTER TABLE public.users OWNER TO superme;
--
-- TOC entry 1515 (class 1259 OID 16443)
-- Dependencies: 6 1514
-- Name: users_id_seq; Type: SEQUENCE; Schema: public; Owner: superme
--
CREATE SEQUENCE users_id_seq
START WITH 1000
INCREMENT BY 1
NO MAXVALUE
MINVALUE 1000
CACHE 1;
ALTER TABLE public.users_id_seq OWNER TO superme;
--
-- TOC entry 1852 (class 0 OID 0)
-- Dependencies: 1515
-- Name: users_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: superme
--
ALTER SEQUENCE users_id_seq OWNED BY users.id;
--
-- TOC entry 1801 (class 2604 OID 16445)
-- Dependencies: 1509 1508
-- Name: id; Type: DEFAULT; Schema: public; Owner: superme
--
ALTER TABLE games ALTER COLUMN id SET DEFAULT nextval('games_id_seq'::regclass);
--
-- TOC entry 1806 (class 2604 OID 16446)
-- Dependencies: 1512 1511
-- Name: id; Type: DEFAULT; Schema: public; Owner: superme
--
ALTER TABLE pythons ALTER COLUMN id SET DEFAULT nextval('pythons_id_seq'::regclass);
--
-- TOC entry 1813 (class 2604 OID 16447)
-- Dependencies: 1515 1514
-- Name: id; Type: DEFAULT; Schema: public; Owner: superme
--
ALTER TABLE users ALTER COLUMN id SET DEFAULT nextval('users_id_seq'::regclass);
--
-- TOC entry 1815 (class 2606 OID 16449)
-- Dependencies: 1508 1508
-- Name: games_pkey; Type: CONSTRAINT; Schema: public; Owner: superme; Tablespace:
--
ALTER TABLE ONLY games
ADD CONSTRAINT games_pkey PRIMARY KEY (id);
--
-- TOC entry 1817 (class 2606 OID 16451)
-- Dependencies: 1510 1510 1510
-- Name: memslots_pkey; Type: CONSTRAINT; Schema: public; Owner: superme; Tablespace:
--
ALTER TABLE ONLY memslots
ADD CONSTRAINT memslots_pkey PRIMARY KEY (weight, python_id);
--
-- TOC entry 1820 (class 2606 OID 16453)
-- Dependencies: 1511 1511
-- Name: python_name_unique; Type: CONSTRAINT; Schema: public; Owner: superme; Tablespace:
--
ALTER TABLE ONLY pythons
ADD CONSTRAINT python_name_unique UNIQUE (name);
--
-- TOC entry 1822 (class 2606 OID 16455)
-- Dependencies: 1511 1511
-- Name: pythons_pkey; Type: CONSTRAINT; Schema: public; Owner: superme; Tablespace:
--
ALTER TABLE ONLY pythons
ADD CONSTRAINT pythons_pkey PRIMARY KEY (id);
--
-- TOC entry 1824 (class 2606 OID 16457)
-- Dependencies: 1513 1513 1513
-- Name: rounds_pkey; Type: CONSTRAINT; Schema: public; Owner: superme; Tablespace:
--
ALTER TABLE ONLY rounds
ADD CONSTRAINT rounds_pkey PRIMARY KEY (game_id, num);
--
-- TOC entry 1826 (class 2606 OID 16459)
-- Dependencies: 1514 1514
-- Name: user_name_unique; Type: CONSTRAINT; Schema: public; Owner: superme; Tablespace:
--
ALTER TABLE ONLY users
ADD CONSTRAINT user_name_unique UNIQUE (name);
--
-- TOC entry 1828 (class 2606 OID 16461)
-- Dependencies: 1514 1514
-- Name: users_pkey; Type: CONSTRAINT; Schema: public; Owner: superme; Tablespace:
--
ALTER TABLE ONLY users
ADD CONSTRAINT users_pkey PRIMARY KEY (id);
--
-- TOC entry 1818 (class 1259 OID 16462)
-- Dependencies: 1511
-- Name: fki_python_owner; Type: INDEX; Schema: public; Owner: superme; Tablespace:
--
CREATE INDEX fki_python_owner ON pythons USING btree (owner_id);
--
-- TOC entry 1837 (class 2620 OID 16463)
-- Dependencies: 19 1513
-- Name: t_update_game_score; Type: TRIGGER; Schema: public; Owner: superme
--
CREATE TRIGGER t_update_game_score
AFTER INSERT OR DELETE OR UPDATE ON rounds
FOR EACH ROW
EXECUTE PROCEDURE update_game_score();
--
-- TOC entry 1836 (class 2620 OID 16464)
-- Dependencies: 20 1508
-- Name: t_update_game_time; Type: TRIGGER; Schema: public; Owner: superme
--
CREATE TRIGGER t_update_game_time
BEFORE INSERT OR UPDATE ON games
FOR EACH ROW
EXECUTE PROCEDURE update_game_time();
--
-- TOC entry 1829 (class 2606 OID 16465)
-- Dependencies: 1508 1511 1821
-- Name: game_first_python; Type: FK CONSTRAINT; Schema: public; Owner: superme
--
ALTER TABLE ONLY games
ADD CONSTRAINT game_first_python FOREIGN KEY (python1_id) REFERENCES pythons(id) ON UPDATE CASCADE ON DELETE CASCADE;
--
-- TOC entry 1830 (class 2606 OID 16470)
-- Dependencies: 1508 1511 1821
-- Name: game_fourth_python; Type: FK CONSTRAINT; Schema: public; Owner: superme
--
ALTER TABLE ONLY games
ADD CONSTRAINT game_fourth_python FOREIGN KEY (python4_id) REFERENCES pythons(id) ON UPDATE CASCADE ON DELETE CASCADE;
--
-- TOC entry 1835 (class 2606 OID 16475)
-- Dependencies: 1814 1508 1513
-- Name: game_round; Type: FK CONSTRAINT; Schema: public; Owner: superme
--
ALTER TABLE ONLY rounds
ADD CONSTRAINT game_round FOREIGN KEY (game_id) REFERENCES games(id) ON UPDATE CASCADE ON DELETE CASCADE;
--
-- TOC entry 1831 (class 2606 OID 16480)
-- Dependencies: 1511 1821 1508
-- Name: game_second_python; Type: FK CONSTRAINT; Schema: public; Owner: superme
--
ALTER TABLE ONLY games
ADD CONSTRAINT game_second_python FOREIGN KEY (python2_id) REFERENCES pythons(id) ON UPDATE CASCADE ON DELETE CASCADE;
--
-- TOC entry 1832 (class 2606 OID 16485)
-- Dependencies: 1508 1511 1821
-- Name: game_third_python; Type: FK CONSTRAINT; Schema: public; Owner: superme
--
ALTER TABLE ONLY games
ADD CONSTRAINT game_third_python FOREIGN KEY (python3_id) REFERENCES pythons(id) ON UPDATE CASCADE ON DELETE CASCADE;
--
-- TOC entry 1834 (class 2606 OID 16490)
-- Dependencies: 1827 1511 1514
-- Name: python_owner; Type: FK CONSTRAINT; Schema: public; Owner: superme
--
ALTER TABLE ONLY pythons
ADD CONSTRAINT python_owner FOREIGN KEY (owner_id) REFERENCES users(id) ON UPDATE CASCADE ON DELETE CASCADE;
--
-- TOC entry 1833 (class 2606 OID 16495)
-- Dependencies: 1510 1821 1511
-- Name: slots_python; Type: FK CONSTRAINT; Schema: public; Owner: superme
--
ALTER TABLE ONLY memslots
ADD CONSTRAINT slots_python FOREIGN KEY (python_id) REFERENCES pythons(id) ON UPDATE CASCADE ON DELETE CASCADE;
--
-- TOC entry 1842 (class 0 OID 0)
-- Dependencies: 6
-- Name: public; Type: ACL; Schema: -; Owner: postgres
--
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 PUBLIC;
--
-- TOC entry 1843 (class 0 OID 0)
-- Dependencies: 1508
-- Name: games; Type: ACL; Schema: public; Owner: superme
--
REVOKE ALL ON TABLE games FROM PUBLIC;
REVOKE ALL ON TABLE games FROM superme;
GRANT ALL ON TABLE games TO superme;
GRANT ALL ON TABLE games TO pyfights_users;
GRANT ALL ON TABLE games TO fighter;
--
-- TOC entry 1845 (class 0 OID 0)
-- Dependencies: 1509
-- Name: games_id_seq; Type: ACL; Schema: public; Owner: superme
--
REVOKE ALL ON SEQUENCE games_id_seq FROM PUBLIC;
REVOKE ALL ON SEQUENCE games_id_seq FROM superme;
GRANT ALL ON SEQUENCE games_id_seq TO superme;
GRANT ALL ON SEQUENCE games_id_seq TO pyfights_users;
--
-- TOC entry 1846 (class 0 OID 0)
-- Dependencies: 1510
-- Name: memslots; Type: ACL; Schema: public; Owner: superme
--
REVOKE ALL ON TABLE memslots FROM PUBLIC;
REVOKE ALL ON TABLE memslots FROM superme;
GRANT ALL ON TABLE memslots TO superme;
GRANT ALL ON TABLE memslots TO pyfights_users;
GRANT ALL ON TABLE memslots TO fighter;
--
-- TOC entry 1847 (class 0 OID 0)
-- Dependencies: 1511
-- Name: pythons; Type: ACL; Schema: public; Owner: superme
--
REVOKE ALL ON TABLE pythons FROM PUBLIC;
REVOKE ALL ON TABLE pythons FROM superme;
GRANT ALL ON TABLE pythons TO superme;
GRANT ALL ON TABLE pythons TO pyfights_users;
GRANT ALL ON TABLE pythons TO fighter;
--
-- TOC entry 1849 (class 0 OID 0)
-- Dependencies: 1512
-- Name: pythons_id_seq; Type: ACL; Schema: public; Owner: superme
--
REVOKE ALL ON SEQUENCE pythons_id_seq FROM PUBLIC;
REVOKE ALL ON SEQUENCE pythons_id_seq FROM superme;
GRANT ALL ON SEQUENCE pythons_id_seq TO superme;
GRANT ALL ON SEQUENCE pythons_id_seq TO pyfights_users;
--
-- TOC entry 1850 (class 0 OID 0)
-- Dependencies: 1513
-- Name: rounds; Type: ACL; Schema: public; Owner: superme
--
REVOKE ALL ON TABLE rounds FROM PUBLIC;
REVOKE ALL ON TABLE rounds FROM superme;
GRANT ALL ON TABLE rounds TO superme;
GRANT ALL ON TABLE rounds TO pyfights_users;
GRANT ALL ON TABLE rounds TO fighter;
--
-- TOC entry 1851 (class 0 OID 0)
-- Dependencies: 1514
-- Name: users; Type: ACL; Schema: public; Owner: superme
--
REVOKE ALL ON TABLE users FROM PUBLIC;
REVOKE ALL ON TABLE users FROM superme;
GRANT ALL ON TABLE users TO superme;
GRANT ALL ON TABLE users TO pyfights_users;
GRANT ALL ON TABLE users TO fighter;
--
-- TOC entry 1853 (class 0 OID 0)
-- Dependencies: 1515
-- Name: users_id_seq; Type: ACL; Schema: public; Owner: superme
--
REVOKE ALL ON SEQUENCE users_id_seq FROM PUBLIC;
REVOKE ALL ON SEQUENCE users_id_seq FROM superme;
GRANT ALL ON SEQUENCE users_id_seq TO superme;
GRANT ALL ON SEQUENCE users_id_seq TO pyfights_users;
-- Completed on 2009-11-12 08:57:15 MSK
--
-- PostgreSQL database dump complete
--
