Dariush Debian Diary

CREATE TABLE domains (
    id serial NOT NULL,
    name character varying(255) NOT NULL,
    master character varying(20),
    last_check integer,
    "type" character varying(6) NOT NULL,
    notified_serial integer,
    account character varying(40)
);


CREATE TABLE records (
    id serial NOT NULL,
    domain_id integer,
    name character varying(255),
    "type" character varying(6),
    content character varying(255),
    ttl integer,
    prio integer,
    change_date integer
);

CREATE TABLE supermasters (
    ip character varying(25) NOT NULL,
    nameserver character varying(255) NOT NULL,
    account character varying(40)
);



COPY domains (id, name, master, last_check, "type", notified_serial, account) FROM stdin;
1       kuszelas.eu    \N      \N      NATIVE  \N      \N
\.


--
-- Data for TOC entry 22 (OID 17153)
-- Name: records; Type: TABLE DATA; Schema: public; Owner: postgres
--

COPY records (id, domain_id, name, "type", content, ttl, prio, change_date) FROM stdin;
2       1       localhost.kuszelas.eu  A       127.0.0.1       120     \N      \N
20      1       kuszelas.eu    MX      mail2.kuszelas.eu      300     10      \N
21      1       mail2.kuszelas.eu      A       212.182.115.24  120     \N      \N
1       1       kuszelas.eu    SOA     localhost user.kuszelas.eu 1   86400   \N      \N
17      1       kuszelas.eu    NS      ns.kuszelas.eu 300     \N      \N
18      1       kuszelas.eu    MX      mail.kuszelas.eu       300     5       \N
12      1       _jabber._tcp.jabber.kuszelas.eu        SRV     0 5269 kuszelas.eu     300     10      \N
13      1       _xmpp-server._tcp.jabber.kuszelas.eu   SRV     0 5269 kuszelas.eu     300     10      \N
14      1       _xmpp-client._tcp.jabber.kuszelas.eu   SRV     0 5222 kuszelas.eu     300     10      \N
16      1       kuszelas.eu    TXT     Serwer  300     \N      \N
3       1       www.kuszelas.eu        A       195.242.124.71  120     \N      \N
5       1       dns.kuszelas.eu        A       195.242.124.71  120     \N      \N
6       1       ftp.kuszelas.eu        A       195.242.124.71  120     \N      \N
7       1       poczta.kuszelas.eu     A       195.242.124.71  120     \N      \N
8       1       pop3.kuszelas.eu       A       195.242.124.71  120     \N      \N
9       1       smtp.kuszelas.eu       A       195.242.124.71  120     \N      \N
10      1       ssh.kuszelas.eu        A       195.242.124.71  120     \N      \N
11      1       jabber.kuszelas.eu     A       195.242.124.71  120     \N      \N
4       1       mail.kuszelas.eu       A       195.242.124.71  120     \N      \N
\.

Audit trail:

CREATE TABLE audit_domains (
    operation         char(1)   NOT NULL,
    stamp             timestamp NOT NULL,
    userid            text      NOT NULL,
    id serial NOT NULL,
    name character varying(255) NOT NULL,
    master character varying(20),
    last_check integer,
    "type" character varying(6) NOT NULL,
    notified_serial integer,
    account character varying(40)
);


CREATE TABLE audit_records (
    operation         char(1)   NOT NULL,
    stamp             timestamp NOT NULL,
    userid            text      NOT NULL,
    id serial NOT NULL,
    domain_id integer,
    name character varying(255),
    "type" character varying(6),
    content character varying(255),
    ttl integer,
    prio integer,
    change_date integer
);

CREATE OR REPLACE FUNCTION process_audit_domains() RETURNS TRIGGER AS $audit_domains$
    BEGIN
        --
        -- Create a row in audit_domains to reflect the operation performed on domains,
        -- make use of the special variable TG_OP to work out the operation.
        --
        IF (TG_OP = 'DELETE') THEN
            INSERT INTO audit_domains SELECT 'D', now(), user, OLD.*;
            RETURN OLD;
        ELSIF (TG_OP = 'UPDATE') THEN
            INSERT INTO audit_domains SELECT 'U', now(), user, NEW.*;
            RETURN NEW;
        ELSIF (TG_OP = 'INSERT') THEN
            INSERT INTO audit_domains SELECT 'I', now(), user, NEW.*;
            RETURN NEW;
        END IF;
        RETURN NULL; -- result is ignored since this is an AFTER trigger
    END;
$audit_domains$ LANGUAGE plpgsql;

CREATE TRIGGER audit_domains
AFTER INSERT OR UPDATE OR DELETE ON domains
    FOR EACH ROW EXECUTE PROCEDURE process_audit_domains();

--

CREATE OR REPLACE FUNCTION process_audit_records() RETURNS TRIGGER AS $audit_records$
    BEGIN
        --
        -- Create a row in audit_records to reflect the operation performed on records,
        -- make use of the special variable TG_OP to work out the operation.
        --
        IF (TG_OP = 'DELETE') THEN
            INSERT INTO audit_records SELECT 'D', now(), user, OLD.*;
            RETURN OLD;
        ELSIF (TG_OP = 'UPDATE') THEN
            INSERT INTO audit_records SELECT 'U', now(), user, NEW.*;
            RETURN NEW;
        ELSIF (TG_OP = 'INSERT') THEN
            INSERT INTO audit_records SELECT 'I', now(), user, NEW.*;
            RETURN NEW;
        END IF;
        RETURN NULL; -- result is ignored since this is an AFTER trigger
    END;
$audit_records$ LANGUAGE plpgsql;

CREATE TRIGGER audit_records
AFTER INSERT OR UPDATE OR DELETE ON records
    FOR EACH ROW EXECUTE PROCEDURE process_audit_records();