--
-- PostgreSQL database dump
--

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

--
-- 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;

--
-- Name: incr_zone_serial_delete(); Type: FUNCTION; Schema: public; Owner: -
--

CREATE FUNCTION incr_zone_serial_delete() RETURNS trigger
    LANGUAGE plpgsql
    AS $$
BEGIN
	UPDATE zones SET serial = serial + 1 WHERE name = OLD.zone;
	RETURN NULL;
END
$$;


--
-- Name: incr_zone_serial_insert(); Type: FUNCTION; Schema: public; Owner: -
--

CREATE FUNCTION incr_zone_serial_insert() RETURNS trigger
    LANGUAGE plpgsql
    AS $$
BEGIN
	UPDATE zones SET serial = serial + 1 WHERE name = NEW.zone;
	RETURN NULL;
END
$$;


--
-- Name: incr_zone_serial_update(); Type: FUNCTION; Schema: public; Owner: -
--

CREATE FUNCTION incr_zone_serial_update() RETURNS trigger
    LANGUAGE plpgsql
    AS $$
BEGIN
	UPDATE zones SET serial = serial + 1 WHERE name = NEW.zone;
	IF (NEW.zone IS DISTINCT FROM OLD.zone) THEN
		UPDATE zones SET serial = serial + 1 WHERE name = OLD.zone;
	END IF;
	RETURN NULL;
END
$$;


--
-- Name: update_serial(); Type: FUNCTION; Schema: public; Owner: -
--

CREATE FUNCTION update_serial() RETURNS trigger
    LANGUAGE plpgsql
    AS $$
BEGIN
	IF (NEW.serial IS NOT DISTINCT FROM OLD.serial) THEN
		NEW.serial = OLD.serial + 1;
	END IF;
	RETURN NEW;
END
$$;


SET default_tablespace = '';

SET default_with_oids = false;

--
-- Name: records; Type: TABLE; Schema: public; Owner: -; Tablespace: 
--

CREATE TABLE records (
    id integer NOT NULL,
    zone character varying(255) NOT NULL,
    host character varying(255) NOT NULL,
    ttl integer DEFAULT 3600 NOT NULL,
    rdtype character varying(9) NOT NULL,
    mx_prio integer,
    data text
);


--
-- Name: zones; Type: TABLE; Schema: public; Owner: -; Tablespace: 
--

CREATE TABLE zones (
    name character varying(254) NOT NULL,
    ttl integer DEFAULT 3600 NOT NULL,
    source character varying(254) NOT NULL,
    contact character varying(254) NOT NULL,
    serial integer DEFAULT 1024 NOT NULL,
    refresh integer DEFAULT 900 NOT NULL,
    retry integer DEFAULT 600 NOT NULL,
    expire integer DEFAULT 86400 NOT NULL,
    minimum integer DEFAULT 3600 NOT NULL
);


--
-- Name: authority; Type: VIEW; Schema: public; Owner: -
--

CREATE VIEW authority AS
 SELECT zones.name AS zone,
    zones.ttl,
    'SOA'::character varying AS rdtype,
    concat_ws(' '::text, zones.source, zones.contact, zones.serial, zones.refresh, zones.retry, zones.expire, zones.minimum) AS data
   FROM zones
UNION
 SELECT records.zone,
    records.ttl,
    records.rdtype,
    records.data
   FROM records
  WHERE ((records.rdtype)::text = 'NS'::text);


--
-- Name: allnodes; Type: VIEW; Schema: public; Owner: -
--

CREATE VIEW allnodes AS
 SELECT records.zone,
    records.ttl,
    records.rdtype,
    records.host,
    concat_ws(' '::text, records.mx_prio, records.data) AS data
   FROM records
UNION
 SELECT authority.zone,
    authority.ttl,
    authority.rdtype,
    '@'::character varying AS host,
    authority.data
   FROM authority;


--
-- Name: records_id_seq; Type: SEQUENCE; Schema: public; Owner: -
--

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


--
-- Name: records_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: -
--

ALTER SEQUENCE records_id_seq OWNED BY records.id;


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

ALTER TABLE ONLY records ALTER COLUMN id SET DEFAULT nextval('records_id_seq'::regclass);


--
-- Name: records_pkey; Type: CONSTRAINT; Schema: public; Owner: -; Tablespace: 
--

ALTER TABLE ONLY records
    ADD CONSTRAINT records_pkey PRIMARY KEY (id);


--
-- Name: zones_pkey; Type: CONSTRAINT; Schema: public; Owner: -; Tablespace: 
--

ALTER TABLE ONLY zones
    ADD CONSTRAINT zones_pkey PRIMARY KEY (name);


--
-- Name: incr_zone_serial_delete; Type: TRIGGER; Schema: public; Owner: -
--

CREATE TRIGGER incr_zone_serial_delete AFTER DELETE ON records FOR EACH ROW EXECUTE PROCEDURE incr_zone_serial_delete();


--
-- Name: incr_zone_serial_insert; Type: TRIGGER; Schema: public; Owner: -
--

CREATE TRIGGER incr_zone_serial_insert AFTER INSERT ON records FOR EACH ROW EXECUTE PROCEDURE incr_zone_serial_insert();


--
-- Name: incr_zone_serial_update; Type: TRIGGER; Schema: public; Owner: -
--

CREATE TRIGGER incr_zone_serial_update AFTER UPDATE ON records FOR EACH ROW EXECUTE PROCEDURE incr_zone_serial_update();


--
-- Name: update_serial; Type: TRIGGER; Schema: public; Owner: -
--

CREATE TRIGGER update_serial BEFORE UPDATE ON zones FOR EACH ROW EXECUTE PROCEDURE update_serial();


--
-- Name: records_zone_fkey; Type: FK CONSTRAINT; Schema: public; Owner: -
--

ALTER TABLE ONLY records
    ADD CONSTRAINT records_zone_fkey FOREIGN KEY (zone) REFERENCES zones(name);


--
-- PostgreSQL database dump complete
--