source: trunk/LATMOS-Accounts/annexes/sql.schema.sql @ 2393

--
-- PostgreSQL database dump
--

SET client_encoding = 'UTF8';
SET standard_conforming_strings = off;
SET check_function_bodies = false;
SET client_min_messages = warning;
SET escape_string_warning = off;

--
-- Name: plpgsql; Type: PROCEDURAL LANGUAGE; Schema: -; Owner: -
--

CREATE PROCEDURAL LANGUAGE plpgsql;


SET search_path = public, pg_catalog;

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

CREATE FUNCTION rev_tg_f() RETURNS "trigger"
    AS $$begin

IF (TG_OP = 'DELETE') THEN
  return old;
else
  new.rev := nextval('revisions_rev_seq'::regclass);
  new.date := now();
  return new;
END IF;

end;$$
    LANGUAGE plpgsql;


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

CREATE FUNCTION users_groups_update_ref() RETURNS "trigger"
    AS $$begin

IF (TG_OP != 'INSERT') then
update person  set date = now() where person.uid = old.uid;
update "group" set date = now() where "group".gid = old.gid;
end if;
IF (TG_OP != 'DELETE') then
update person  set date = now() where person.uid = new.uid;
update "group" set date = now() where "group".gid = new.gid;
end if;

IF (TG_OP = 'DELETE') then
  return old;
ELSE
  return new;
END IF;

END;$$
    LANGUAGE plpgsql;


SET default_tablespace = '';

SET default_with_oids = false;

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

CREATE TABLE revisions (
    rev integer NOT NULL,
    date timestamp with time zone DEFAULT now() NOT NULL,
    "create" timestamp with time zone DEFAULT now() NOT NULL
);


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

CREATE SEQUENCE revisions_rev_seq
    INCREMENT BY 1
    NO MAXVALUE
    NO MINVALUE
    CACHE 1;


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

ALTER SEQUENCE revisions_rev_seq OWNED BY revisions.rev;


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

CREATE TABLE "group" (
    groupname text NOT NULL,
    gid integer NOT NULL
)
INHERITS (revisions);


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

CREATE TABLE person (
    name text NOT NULL,
    firstname text NOT NULL,
    login text NOT NULL,
    uid integer NOT NULL,
    mail text,
    shell text DEFAULT '/bin/bash'::text,
    home text,
    gid integer NOT NULL
)
INHERITS (revisions);


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

CREATE TABLE users_groups (
    uid integer NOT NULL,
    gid integer NOT NULL
)
INHERITS (revisions);


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

ALTER TABLE revisions ALTER COLUMN rev SET DEFAULT nextval('revisions_rev_seq'::regclass);


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

ALTER TABLE ONLY "group"
    ADD CONSTRAINT goup_pkey PRIMARY KEY (groupname);


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

ALTER TABLE ONLY "group"
    ADD CONSTRAINT group_gid_uniq UNIQUE (gid);


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

ALTER TABLE ONLY person
    ADD CONSTRAINT person_pkey PRIMARY KEY (login);


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

ALTER TABLE ONLY person
    ADD CONSTRAINT person_uid_key UNIQUE (uid);


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

ALTER TABLE ONLY users_groups
    ADD CONSTRAINT users_group_pkey PRIMARY KEY (uid, gid);


--
-- Name: fki_users_group_gid_fkey; Type: INDEX; Schema: public; Owner: -; Tablespace: 
--

CREATE INDEX fki_users_group_gid_fkey ON users_groups USING btree (gid);


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

CREATE TRIGGER group_rev_tg
    BEFORE DELETE OR UPDATE ON "group"
    FOR EACH ROW
    EXECUTE PROCEDURE rev_tg_f();


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

CREATE TRIGGER person_rev_tg
    BEFORE DELETE OR UPDATE ON person
    FOR EACH ROW
    EXECUTE PROCEDURE rev_tg_f();


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

CREATE TRIGGER revisions_rev_tg
    AFTER DELETE OR UPDATE ON revisions
    FOR EACH ROW
    EXECUTE PROCEDURE rev_tg_f();


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

CREATE TRIGGER users_groups_update_ref_tg
    AFTER INSERT OR DELETE OR UPDATE ON users_groups
    FOR EACH ROW
    EXECUTE PROCEDURE users_groups_update_ref();


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

CREATE TRIGGER usres_groups_rev_tg
    BEFORE DELETE OR UPDATE ON users_groups
    FOR EACH ROW
    EXECUTE PROCEDURE rev_tg_f();


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

ALTER TABLE ONLY users_groups
    ADD CONSTRAINT users_group_gid_fkey FOREIGN KEY (gid) REFERENCES "group"(gid);


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

ALTER TABLE ONLY users_groups
    ADD CONSTRAINT users_group_uid_fkey FOREIGN KEY (uid) REFERENCES person(uid);


--
-- Name: public; Type: ACL; Schema: -; Owner: -
--

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


--
-- PostgreSQL database dump complete
--