pyrot/database.schema.sql

--
-- PostgreSQL database dump
--

-- Dumped from database version 9.6.6
-- Dumped by pg_dump version 9.6.6

SET statement_timeout = 0;
SET lock_timeout = 0;
SET idle_in_transaction_session_timeout = 0;
SET client_encoding = 'UTF8';
SET standard_conforming_strings = on;
SET check_function_bodies = false;
SET client_min_messages = warning;
SET row_security = off;

--
-- Name: plpgsql; Type: EXTENSION; Schema: -; Owner: 
--

CREATE EXTENSION IF NOT EXISTS plpgsql WITH SCHEMA pg_catalog;


--
-- Name: EXTENSION plpgsql; Type: COMMENT; Schema: -; Owner: 
--

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


SET search_path = public, pg_catalog;

SET default_tablespace = '';

SET default_with_oids = false;

--
-- Name: channels; Type: TABLE; Schema: public; Owner: pyRot
--

CREATE TABLE channels (
    id integer NOT NULL,
    name character varying(64) NOT NULL,
    network character varying(40) NOT NULL,
    autojoin boolean,
    join_greeting boolean,
    statistics_commands boolean,
    games boolean,
    aggressiveness character varying(20) DEFAULT 'defense_only'::character varying,
    chat boolean,
    key character varying(32),
    last_lame timestamp without time zone
);


ALTER TABLE channels OWNER TO "pyRot";

--
-- Name: channel_id_seq; Type: SEQUENCE; Schema: public; Owner: pyRot
--

CREATE SEQUENCE channel_id_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


ALTER TABLE channel_id_seq OWNER TO "pyRot";

--
-- Name: channel_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: pyRot
--

ALTER SEQUENCE channel_id_seq OWNED BY channels.id;


--
-- Name: joins; Type: TABLE; Schema: public; Owner: pyRot
--

CREATE TABLE joins (
    id integer NOT NULL,
    channel character varying(64) NOT NULL,
    channel_network character varying(40) NOT NULL,
    "user" character varying(31) NOT NULL,
    user_network character varying(40) NOT NULL,
    joins integer DEFAULT 0,
    stopgreet boolean
);


ALTER TABLE joins OWNER TO "pyRot";

--
-- Name: joins_id_seq; Type: SEQUENCE; Schema: public; Owner: pyRot
--

CREATE SEQUENCE joins_id_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


ALTER TABLE joins_id_seq OWNER TO "pyRot";

--
-- Name: joins_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: pyRot
--

ALTER SEQUENCE joins_id_seq OWNED BY joins.id;


--
-- Name: kicks; Type: TABLE; Schema: public; Owner: pyRot
--

CREATE TABLE kicks (
    id integer NOT NULL,
    channel character varying(64) NOT NULL,
    channel_network character varying(40) NOT NULL,
    "user" character varying(31) NOT NULL,
    user_network character varying(40) NOT NULL,
    given integer DEFAULT 0,
    received integer DEFAULT 0
);


ALTER TABLE kicks OWNER TO "pyRot";

--
-- Name: kicks_id_seq; Type: SEQUENCE; Schema: public; Owner: pyRot
--

CREATE SEQUENCE kicks_id_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


ALTER TABLE kicks_id_seq OWNER TO "pyRot";

--
-- Name: kicks_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: pyRot
--

ALTER SEQUENCE kicks_id_seq OWNED BY kicks.id;


--
-- Name: messages; Type: TABLE; Schema: public; Owner: pyRot
--

CREATE TABLE messages (
    id integer NOT NULL,
    channel character varying(64) NOT NULL,
    channel_network character varying(40) NOT NULL,
    "user" character varying(31) NOT NULL,
    user_network character varying(40) NOT NULL,
    messages integer DEFAULT 0,
    messages_words bigint DEFAULT 0,
    messages_characters bigint DEFAULT 0,
    actions integer DEFAULT 0,
    actions_words bigint DEFAULT 0,
    actions_characters bigint DEFAULT 0,
    notices integer DEFAULT 0,
    notices_words bigint DEFAULT 0,
    notices_characters bigint DEFAULT 0
);


ALTER TABLE messages OWNER TO "pyRot";

--
-- Name: messages_id_seq; Type: SEQUENCE; Schema: public; Owner: pyRot
--

CREATE SEQUENCE messages_id_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


ALTER TABLE messages_id_seq OWNER TO "pyRot";

--
-- Name: messages_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: pyRot
--

ALTER SEQUENCE messages_id_seq OWNED BY messages.id;


--
-- Name: networks; Type: TABLE; Schema: public; Owner: pyRot
--

CREATE TABLE networks (
    id integer NOT NULL,
    name character varying(40) NOT NULL,
    host character varying(60) NOT NULL,
    port integer DEFAULT 6667,
    use_ssl boolean,
    nickname character varying(31) DEFAULT 'RotBot'::character varying,
    username character varying(31) DEFAULT 'pyRot'::character varying,
    password character varying(31),
    home_channel character varying(64) DEFAULT '#RotBot'::character varying NOT NULL,
    command_character character(1) DEFAULT '!'::bpchar,
    help_character character(1) DEFAULT '@'::bpchar
);


ALTER TABLE networks OWNER TO "pyRot";

--
-- Name: networks_id_seq; Type: SEQUENCE; Schema: public; Owner: pyRot
--

CREATE SEQUENCE networks_id_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


ALTER TABLE networks_id_seq OWNER TO "pyRot";

--
-- Name: networks_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: pyRot
--

ALTER SEQUENCE networks_id_seq OWNED BY networks.id;


--
-- Name: users; Type: TABLE; Schema: public; Owner: pyRot
--

CREATE TABLE users (
    id integer NOT NULL,
    name character varying(31) NOT NULL,
    network character varying(40) NOT NULL,
    last_act_type character varying(20),
    last_act_datetime timestamp without time zone,
    last_act_channel character varying(64),
    last_act_channel_network character varying(40),
    last_act character varying(510),
    last_act_auxiliary character varying(255),
    away boolean,
    away_reason character varying(510),
    xp_spent integer DEFAULT 0,
    level integer DEFAULT 0,
    coin numeric DEFAULT 10
);


ALTER TABLE users OWNER TO "pyRot";

--
-- Name: users_id_seq; Type: SEQUENCE; Schema: public; Owner: pyRot
--

CREATE SEQUENCE users_id_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


ALTER TABLE users_id_seq OWNER TO "pyRot";

--
-- Name: users_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: pyRot
--

ALTER SEQUENCE users_id_seq OWNED BY users.id;


--
-- Name: channels id; Type: DEFAULT; Schema: public; Owner: pyRot
--

ALTER TABLE ONLY channels ALTER COLUMN id SET DEFAULT nextval('channel_id_seq'::regclass);


--
-- Name: joins id; Type: DEFAULT; Schema: public; Owner: pyRot
--

ALTER TABLE ONLY joins ALTER COLUMN id SET DEFAULT nextval('joins_id_seq'::regclass);


--
-- Name: kicks id; Type: DEFAULT; Schema: public; Owner: pyRot
--

ALTER TABLE ONLY kicks ALTER COLUMN id SET DEFAULT nextval('kicks_id_seq'::regclass);


--
-- Name: messages id; Type: DEFAULT; Schema: public; Owner: pyRot
--

ALTER TABLE ONLY messages ALTER COLUMN id SET DEFAULT nextval('messages_id_seq'::regclass);


--
-- Name: networks id; Type: DEFAULT; Schema: public; Owner: pyRot
--

ALTER TABLE ONLY networks ALTER COLUMN id SET DEFAULT nextval('networks_id_seq'::regclass);


--
-- Name: users id; Type: DEFAULT; Schema: public; Owner: pyRot
--

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


--
-- Name: channels channel_pkey; Type: CONSTRAINT; Schema: public; Owner: pyRot
--

ALTER TABLE ONLY channels
    ADD CONSTRAINT channel_pkey PRIMARY KEY (id);


--
-- Name: channels channels_name_network_key; Type: CONSTRAINT; Schema: public; Owner: pyRot
--

ALTER TABLE ONLY channels
    ADD CONSTRAINT channels_name_network_key UNIQUE (name, network);


--
-- Name: joins joins_channel_channel_network_user_user_network_key; Type: CONSTRAINT; Schema: public; Owner: pyRot
--

ALTER TABLE ONLY joins
    ADD CONSTRAINT joins_channel_channel_network_user_user_network_key UNIQUE (channel, channel_network, "user", user_network);


--
-- Name: joins joins_pkey; Type: CONSTRAINT; Schema: public; Owner: pyRot
--

ALTER TABLE ONLY joins
    ADD CONSTRAINT joins_pkey PRIMARY KEY (id);


--
-- Name: kicks kicks_channel_channel_network_user_user_network_key; Type: CONSTRAINT; Schema: public; Owner: pyRot
--

ALTER TABLE ONLY kicks
    ADD CONSTRAINT kicks_channel_channel_network_user_user_network_key UNIQUE (channel, channel_network, "user", user_network);


--
-- Name: kicks kicks_pkey; Type: CONSTRAINT; Schema: public; Owner: pyRot
--

ALTER TABLE ONLY kicks
    ADD CONSTRAINT kicks_pkey PRIMARY KEY (id);


--
-- Name: messages messages_channel_channel_network_user_user_network_key; Type: CONSTRAINT; Schema: public; Owner: pyRot
--

ALTER TABLE ONLY messages
    ADD CONSTRAINT messages_channel_channel_network_user_user_network_key UNIQUE (channel, channel_network, "user", user_network);


--
-- Name: messages messages_pkey; Type: CONSTRAINT; Schema: public; Owner: pyRot
--

ALTER TABLE ONLY messages
    ADD CONSTRAINT messages_pkey PRIMARY KEY (id);


--
-- Name: networks networks_host_key; Type: CONSTRAINT; Schema: public; Owner: pyRot
--

ALTER TABLE ONLY networks
    ADD CONSTRAINT networks_host_key UNIQUE (host);


--
-- Name: networks networks_name_key; Type: CONSTRAINT; Schema: public; Owner: pyRot
--

ALTER TABLE ONLY networks
    ADD CONSTRAINT networks_name_key UNIQUE (name);


--
-- Name: networks networks_pkey; Type: CONSTRAINT; Schema: public; Owner: pyRot
--

ALTER TABLE ONLY networks
    ADD CONSTRAINT networks_pkey PRIMARY KEY (id);


--
-- Name: users users_name_network_key; Type: CONSTRAINT; Schema: public; Owner: pyRot
--

ALTER TABLE ONLY users
    ADD CONSTRAINT users_name_network_key UNIQUE (name, network);


--
-- Name: users users_pkey; Type: CONSTRAINT; Schema: public; Owner: pyRot
--

ALTER TABLE ONLY users
    ADD CONSTRAINT users_pkey PRIMARY KEY (id);


--
-- Name: channels channels_network_fkey; Type: FK CONSTRAINT; Schema: public; Owner: pyRot
--

ALTER TABLE ONLY channels
    ADD CONSTRAINT channels_network_fkey FOREIGN KEY (network) REFERENCES networks(name) ON UPDATE CASCADE ON DELETE CASCADE;


--
-- Name: joins joins_channel_fkey; Type: FK CONSTRAINT; Schema: public; Owner: pyRot
--

ALTER TABLE ONLY joins
    ADD CONSTRAINT joins_channel_fkey FOREIGN KEY (channel, channel_network) REFERENCES channels(name, network) ON UPDATE CASCADE ON DELETE CASCADE;


--
-- Name: joins joins_user_fkey; Type: FK CONSTRAINT; Schema: public; Owner: pyRot
--

ALTER TABLE ONLY joins
    ADD CONSTRAINT joins_user_fkey FOREIGN KEY ("user", user_network) REFERENCES users(name, network) ON UPDATE CASCADE ON DELETE CASCADE;


--
-- Name: kicks kicks_channel_fkey; Type: FK CONSTRAINT; Schema: public; Owner: pyRot
--

ALTER TABLE ONLY kicks
    ADD CONSTRAINT kicks_channel_fkey FOREIGN KEY (channel, channel_network) REFERENCES channels(name, network) ON UPDATE CASCADE ON DELETE CASCADE;


--
-- Name: kicks kicks_user_fkey; Type: FK CONSTRAINT; Schema: public; Owner: pyRot
--

ALTER TABLE ONLY kicks
    ADD CONSTRAINT kicks_user_fkey FOREIGN KEY ("user", user_network) REFERENCES users(name, network) ON UPDATE CASCADE ON DELETE CASCADE;


--
-- Name: messages messages_channel_fkey; Type: FK CONSTRAINT; Schema: public; Owner: pyRot
--

ALTER TABLE ONLY messages
    ADD CONSTRAINT messages_channel_fkey FOREIGN KEY (channel, channel_network) REFERENCES channels(name, network) ON UPDATE CASCADE ON DELETE CASCADE;


--
-- Name: messages messages_user_fkey; Type: FK CONSTRAINT; Schema: public; Owner: pyRot
--

ALTER TABLE ONLY messages
    ADD CONSTRAINT messages_user_fkey FOREIGN KEY ("user", user_network) REFERENCES users(name, network) ON UPDATE CASCADE ON DELETE CASCADE;


--
-- Name: users users_last_act_channel_fkey; Type: FK CONSTRAINT; Schema: public; Owner: pyRot
--

ALTER TABLE ONLY users
    ADD CONSTRAINT users_last_act_channel_fkey FOREIGN KEY (last_act_channel, last_act_channel_network) REFERENCES channels(name, network) ON UPDATE CASCADE ON DELETE CASCADE;


--
-- Name: users users_network_fkey; Type: FK CONSTRAINT; Schema: public; Owner: pyRot
--

ALTER TABLE ONLY users
    ADD CONSTRAINT users_network_fkey FOREIGN KEY (network) REFERENCES networks(name) ON UPDATE CASCADE ON DELETE CASCADE;



-- new stuff

CREATE RULE update_username AS ON UPDATE TO users WHERE OLD.name <> NEW.name DO INSTEAD (
    INSERT INTO users (name, network, last_act_type, last_act_datetime, last_act_channel, last_act_channel_network, last_act, last_act_auxiliary, away, away_reason, xp_spent, level, coin) VALUES (NEW.name, NEW.network, NEW.last_act_type, NEW.last_act_datetime, NEW.last_act_channel, NEW.last_act_channel_network, NEW.last_act, NEW.last_act_auxiliary, NEW.away, NEW.away_reason, NEW.xp_spent, NEW.level, NEW.coin);
    UPDATE joins SET "user"=NEW.name WHERE "user"=OLD.name AND user_network=OLD.network;
    UPDATE kicks SET "user"=NEW.name WHERE "user"=OLD.name AND user_network=OLD.network;
    UPDATE messages SET "user"=NEW.name WHERE "user"=OLD.name AND user_network=OLD.network;
    DELETE FROM users WHERE name=OLD.name AND network=OLD.network;
);
    

CREATE RULE update_channel_name AS ON UPDATE TO channels WHERE OLD.name <> NEW.name DO INSTEAD (
    INSERT INTO channels (name, network, autojoin, join_greeting, statistics_commands, games, aggressiveness, chat, key, last_lame) VALUES (NEW.name, NEW.network, NEW.autojoin, NEW.join_greeting, NEW.statistics_commands, NEW.games, NEW.aggressiveness, NEW.chat, NEW.key, NEW.last_lame);
    UPDATE joins SET "user"=NEW.name WHERE "user"=OLD.name AND user_network=OLD.network;
    UPDATE kicks SET "user"=NEW.name WHERE "user"=OLD.name AND user_network=OLD.network;
    UPDATE messages SET "user"=NEW.name WHERE "user"=OLD.name AND user_network=OLD.network;
    UPDATE users SET last_act_channel=NEW.name WHERE last_act_channel=OLD.name AND network=OLD.network;
    DELETE FROM channels WHERE name=OLD.name AND network=OLD.network;
);


--
-- PostgreSQL database dump complete
--