-- -- 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 (id, 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, coin_spent, coin_given) VALUES (NEW.id, 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, NEW.coin_spent, NEW.coin_given); 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 id=OLD.id; ); CREATE RULE update_channel_name AS ON UPDATE TO channels WHERE OLD.name <> NEW.name DO INSTEAD ( INSERT INTO channels (id, name, network, autojoin, join_greeting, statistics_commands, games, aggressiveness, chat, key, last_lame) VALUES (NEW.id, 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 id=OLD.id; ); -- -- PostgreSQL database dump complete --