--
-- PostgreSQL database dump
--

-- Dumped from database version 13.6 (Debian 13.6-1.pgdg110+1)
-- Dumped by pg_dump version 13.7 (Ubuntu 13.7-1.pgdg20.04+1)

SET statement_timeout = 0;
SET lock_timeout = 0;
SET idle_in_transaction_session_timeout = 0;
SET client_encoding = 'UTF8';
SET standard_conforming_strings = on;
SELECT pg_catalog.set_config('search_path', '', false);
SET check_function_bodies = false;
SET xmloption = content;
SET client_min_messages = warning;
SET row_security = off;

--
-- Name: increase_revision(text, text); Type: FUNCTION; Schema: public; Owner: root
--

CREATE FUNCTION public.increase_revision(v_table_name text, v_identity_zone_id text) RETURNS integer
    LANGUAGE plpgsql
    AS $$

DECLARE
    rev          INTEGER;
    updated_rows INTEGER;
BEGIN
    SELECT nextval('xs_cache_revisions_seq') INTO rev;
    UPDATE xs_cache_revisions
    SET revision = rev
    WHERE table_name = v_table_name
      AND identity_zone_id = v_identity_zone_id
      AND revision =  (SELECT revision
                       FROM xs_cache_revisions
                       WHERE table_name = v_table_name
                         AND identity_zone_id = v_identity_zone_id FOR UPDATE SKIP LOCKED LIMIT 1);

    GET DIAGNOSTICS updated_rows = ROW_COUNT;

    IF (updated_rows = 0) THEN
        INSERT INTO xs_cache_revisions(table_name, identity_zone_id, revision)
        values (v_table_name, v_identity_zone_id, rev);
    END IF;

    delete from xs_cache_revisions where (table_name,identity_zone_id,revision) in (select table_name,identity_zone_id,revision from xs_cache_revisions where (table_name = v_table_name and identity_zone_id = v_identity_zone_id and revision < rev) for update SKIP LOCKED);

    RETURN rev;
END;
$$;


--ALTER FUNCTION public.increase_revision(v_table_name text, v_identity_zone_id text) OWNER TO root;

--
-- Name: update_cache_revision(); Type: FUNCTION; Schema: public; Owner: root
--

CREATE FUNCTION public.update_cache_revision() RETURNS trigger
    LANGUAGE plpgsql SECURITY DEFINER
    AS $$
DECLARE
    v_zone_id    TEXT;
BEGIN
    IF (TG_OP = 'UPDATE') or (TG_OP = 'INSERT') THEN
        v_zone_id := NEW.identity_zone_id;

        PERFORM increase_revision(tg_table_name::TEXT, v_zone_id);

        RETURN NEW;
    ELSIF (TG_OP = 'DELETE') THEN
        v_zone_id := OLD.identity_zone_id;

        PERFORM increase_revision(tg_table_name::TEXT, v_zone_id);

        RETURN OLD;
    END IF;

END;

$$;


--ALTER FUNCTION public.update_cache_revision() OWNER TO root;

SET default_tablespace = '';

--SET default_table_access_method = heap;

--
-- Name: identity_provider; Type: TABLE; Schema: public; Owner: root
--

CREATE TABLE public.identity_provider (
    id character varying(36) NOT NULL,
    created timestamp without time zone DEFAULT CURRENT_TIMESTAMP,
    lastmodified timestamp without time zone DEFAULT CURRENT_TIMESTAMP,
    version bigint DEFAULT 0,
    identity_zone_id character varying(36) NOT NULL,
    name character varying(255) NOT NULL,
    origin_key character varying(36) NOT NULL,
    type character varying(255) NOT NULL,
    config text,
    active boolean DEFAULT true NOT NULL
);


--ALTER TABLE public.identity_provider OWNER TO root;

--
-- Name: xs_cache_revisions; Type: TABLE; Schema: public; Owner: root
--

CREATE TABLE public.xs_cache_revisions (
    table_name character varying(48) NOT NULL,
    identity_zone_id character varying(36) NOT NULL,
    revision bigint NOT NULL,
    created timestamp without time zone DEFAULT now()
);


--ALTER TABLE public.xs_cache_revisions OWNER TO root;

--
-- Name: xs_cache_revisions_revision_seq; Type: SEQUENCE; Schema: public; Owner: root
--

CREATE SEQUENCE public.xs_cache_revisions_revision_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


--ALTER TABLE public.xs_cache_revisions_revision_seq OWNER TO root;

--
-- Name: xs_cache_revisions_revision_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: root
--

ALTER SEQUENCE public.xs_cache_revisions_revision_seq OWNED BY public.xs_cache_revisions.revision;


--
-- Name: xs_cache_revisions_seq; Type: SEQUENCE; Schema: public; Owner: root
--

CREATE SEQUENCE public.xs_cache_revisions_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


--ALTER TABLE public.xs_cache_revisions_seq OWNER TO root;

--
-- Name: xs_cache_revisions revision; Type: DEFAULT; Schema: public; Owner: root
--

ALTER TABLE ONLY public.xs_cache_revisions ALTER COLUMN revision SET DEFAULT nextval('public.xs_cache_revisions_revision_seq'::regclass);


--
-- Name: identity_provider identity_provider_pkey; Type: CONSTRAINT; Schema: public; Owner: root
--

ALTER TABLE ONLY public.identity_provider
    ADD CONSTRAINT identity_provider_pkey PRIMARY KEY (id);


--
-- Name: xs_cache_revisions xs_cache_revisions_constr; Type: CONSTRAINT; Schema: public; Owner: root
--

ALTER TABLE ONLY public.xs_cache_revisions
    ADD CONSTRAINT xs_cache_revisions_constr UNIQUE (table_name, identity_zone_id, revision);


--
-- Name: active_in_zone; Type: INDEX; Schema: public; Owner: root
--

CREATE INDEX active_in_zone ON public.identity_provider USING btree (identity_zone_id, active);


--
-- Name: key_in_zone; Type: INDEX; Schema: public; Owner: root
--

CREATE UNIQUE INDEX key_in_zone ON public.identity_provider USING btree (identity_zone_id, origin_key);


--
-- Name: xs_cache_revisions_zone_table_rev; Type: INDEX; Schema: public; Owner: root
--

CREATE INDEX xs_cache_revisions_zone_table_rev ON public.xs_cache_revisions USING btree (identity_zone_id, table_name, revision);


--
-- Name: identity_provider t_identity_provider_change_trigger; Type: TRIGGER; Schema: public; Owner: root
--

CREATE TRIGGER t_identity_provider_change_trigger AFTER INSERT OR DELETE OR UPDATE ON public.identity_provider FOR EACH ROW EXECUTE PROCEDURE public.update_cache_revision('false');


--
-- PostgreSQL database dump complete
--