--
-- PostgreSQL database dump
--

SET client_encoding = 'SQL_ASCII';
SET check_function_bodies = false;

SET SESSION AUTHORIZATION 'postgres';

SET search_path = public, pg_catalog;

--
-- TOC entry 26 (OID 17178)
-- Name: plpgsql_call_handler(); Type: FUNC PROCEDURAL LANGUAGE; Schema: public; Owner: postgres
--

CREATE FUNCTION plpgsql_call_handler() RETURNS language_handler
    AS '$libdir/plpgsql', 'plpgsql_call_handler'
    LANGUAGE c;


SET SESSION AUTHORIZATION DEFAULT;

--
-- TOC entry 25 (OID 17179)
-- Name: plpgsql; Type: PROCEDURAL LANGUAGE; Schema: public; Owner: 
--

CREATE TRUSTED PROCEDURAL LANGUAGE plpgsql HANDLER plpgsql_call_handler;


SET SESSION AUTHORIZATION 'postgres';

--
-- TOC entry 4 (OID 2200)
-- Name: public; Type: ACL; Schema: -; Owner: postgres
--

REVOKE ALL ON SCHEMA public FROM PUBLIC;
GRANT ALL ON SCHEMA public TO PUBLIC;


SET SESSION AUTHORIZATION 'postgres';

--
-- TOC entry 5 (OID 17143)
-- Name: sp_domains; Type: TABLE; Schema: public; Owner: postgres
--

CREATE TABLE sp_domains (
    pno integer NOT NULL,
    domain_name text
);


--
-- TOC entry 6 (OID 17161)
-- Name: x_sps; Type: TABLE; Schema: public; Owner: postgres
--

CREATE TABLE x_sps (
    pno integer NOT NULL,
    spid text NOT NULL
);


--
-- TOC entry 7 (OID 17174)
-- Name: x_tgtids; Type: TABLE; Schema: public; Owner: postgres
--

CREATE TABLE x_tgtids (
    pno integer NOT NULL,
    regid character(32),
    tgtid character(32)
);


--
-- TOC entry 27 (OID 17193)
-- Name: test(); Type: FUNCTION; Schema: public; Owner: postgres
--

CREATE FUNCTION test() RETURNS text
    AS '
declare
  m boolean;
begin
  select am_i_master() into m; 
  if am_i_master() then
     return ''master'';
  else
     return ''slave'';
  end if;
end
'
    LANGUAGE plpgsql;


--
-- TOC entry 8 (OID 17218)
-- Name: sps; Type: TABLE; Schema: public; Owner: postgres
--

CREATE TABLE sps (
    pno serial NOT NULL,
    spid text NOT NULL
);


--
-- TOC entry 9 (OID 17218)
-- Name: sps; Type: ACL; Schema: public; Owner: postgres
--

REVOKE ALL ON TABLE sps FROM PUBLIC;
GRANT INSERT,SELECT ON TABLE sps TO shib;


SET SESSION AUTHORIZATION 'postgres';

--
-- TOC entry 14 (OID 17218)
-- Name: sps_pno_seq; Type: ACL; Schema: public; Owner: postgres
--

REVOKE ALL ON TABLE sps_pno_seq FROM PUBLIC;
GRANT SELECT,UPDATE ON TABLE sps_pno_seq TO shib;


SET SESSION AUTHORIZATION 'postgres';

--
-- TOC entry 10 (OID 17232)
-- Name: tgtids; Type: TABLE; Schema: public; Owner: postgres
--

CREATE TABLE tgtids (
    tno serial NOT NULL,
    pno integer NOT NULL,
    regid character(32),
    tgtid character(32)
);


--
-- TOC entry 11 (OID 17232)
-- Name: tgtids; Type: ACL; Schema: public; Owner: postgres
--

REVOKE ALL ON TABLE tgtids FROM PUBLIC;
GRANT INSERT,SELECT ON TABLE tgtids TO shib;
GRANT SELECT ON TABLE tgtids TO shibtest;


SET SESSION AUTHORIZATION 'postgres';

--
-- TOC entry 15 (OID 17232)
-- Name: tgtids_tno_seq; Type: ACL; Schema: public; Owner: postgres
--

REVOKE ALL ON TABLE tgtids_tno_seq FROM PUBLIC;
GRANT SELECT,UPDATE ON TABLE tgtids_tno_seq TO shib;


SET SESSION AUTHORIZATION 'postgres';

--
-- TOC entry 12 (OID 17266)
-- Name: sv_status; Type: TABLE; Schema: public; Owner: postgres
--

CREATE TABLE sv_status (
    host text NOT NULL,
    status integer,
    master_priority integer,
    master_ts timestamp without time zone,
    log_tno integer,
    log_pno integer
);


--
-- TOC entry 13 (OID 17266)
-- Name: sv_status; Type: ACL; Schema: public; Owner: postgres
--

REVOKE ALL ON TABLE sv_status FROM PUBLIC;
GRANT SELECT,UPDATE ON TABLE sv_status TO shib;
GRANT SELECT ON TABLE sv_status TO shibtest;


SET SESSION AUTHORIZATION 'postgres';

--
-- TOC entry 28 (OID 17332)
-- Name: am_i_master(); Type: FUNCTION; Schema: public; Owner: postgres
--

CREATE FUNCTION am_i_master() RETURNS boolean
    AS '
declare
  st integer;
begin
  select into st status from sv_status;
  return st = 2; 
end
'
    LANGUAGE plpgsql;


--
-- TOC entry 29 (OID 17333)
-- Name: tid(character, text); Type: FUNCTION; Schema: public; Owner: postgres
--

CREATE FUNCTION tid(character, text) RETURNS character
    AS '
declare
  my_tgtid char(32);
  my_pno integer;
begin
  select into my_tgtid tgtid from tgtids,sps where regid=$1 and sps.spid=$2 and tgtids.pno=sps.pno;
  if not found then
     if am_i_master() then
        select into my_pno pno from sps where spid=$2;
        if not found then
           select into my_pno nextval(''sps_pno_seq'');
           INSERT INTO sps values (my_pno, $2);
           if not found then
               return my_tgtid;
           end if;
        end if;
        select into my_tgtid md5($1||$2||''some_long_random_password_here'');
        INSERT INTO tgtids values (default, my_pno, $1, my_tgtid);
        if not found then
            return my_tgtid;
        end if;
    end if;
  end if;
  return my_tgtid;
end;
'
    LANGUAGE plpgsql;


--
-- TOC entry 17 (OID 17148)
-- Name: sp_domains_pno_key; Type: CONSTRAINT; Schema: public; Owner: postgres
--

ALTER TABLE ONLY sp_domains
    ADD CONSTRAINT sp_domains_pno_key UNIQUE (pno);


--
-- TOC entry 16 (OID 17150)
-- Name: sp_domains_domain_name_key; Type: CONSTRAINT; Schema: public; Owner: postgres
--

ALTER TABLE ONLY sp_domains
    ADD CONSTRAINT sp_domains_domain_name_key UNIQUE (domain_name);


--
-- TOC entry 18 (OID 17166)
-- Name: x_sps_pno_key; Type: CONSTRAINT; Schema: public; Owner: postgres
--

ALTER TABLE ONLY x_sps
    ADD CONSTRAINT x_sps_pno_key UNIQUE (pno);


--
-- TOC entry 19 (OID 17168)
-- Name: x_sps_spid_key; Type: CONSTRAINT; Schema: public; Owner: postgres
--

ALTER TABLE ONLY x_sps
    ADD CONSTRAINT x_sps_spid_key UNIQUE (spid);


--
-- TOC entry 20 (OID 17224)
-- Name: sps_pno_key; Type: CONSTRAINT; Schema: public; Owner: postgres
--

ALTER TABLE ONLY sps
    ADD CONSTRAINT sps_pno_key UNIQUE (pno);


--
-- TOC entry 21 (OID 17226)
-- Name: sps_spid_key; Type: CONSTRAINT; Schema: public; Owner: postgres
--

ALTER TABLE ONLY sps
    ADD CONSTRAINT sps_spid_key UNIQUE (spid);


--
-- TOC entry 23 (OID 17235)
-- Name: tgtids_tno_key; Type: CONSTRAINT; Schema: public; Owner: postgres
--

ALTER TABLE ONLY tgtids
    ADD CONSTRAINT tgtids_tno_key UNIQUE (tno);


--
-- TOC entry 22 (OID 17237)
-- Name: tgtids_pno_key; Type: CONSTRAINT; Schema: public; Owner: postgres
--

ALTER TABLE ONLY tgtids
    ADD CONSTRAINT tgtids_pno_key UNIQUE (pno, regid);


--
-- TOC entry 24 (OID 17271)
-- Name: sv_status_host_key; Type: CONSTRAINT; Schema: public; Owner: postgres
--

ALTER TABLE ONLY sv_status
    ADD CONSTRAINT sv_status_host_key UNIQUE (host);


--
-- TOC entry 3 (OID 2200)
-- Name: SCHEMA public; Type: COMMENT; Schema: -; Owner: postgres
--

COMMENT ON SCHEMA public IS 'Standard public schema';