--
-- Selected TOC Entries:
--
\connect - conni

-------------------------------------------------
CREATE SEQUENCE "publikation_p_id_seq" start 1 increment 1 maxvalue 2147483647 minvalue 1  cache 1 ;

REVOKE ALL on "publikation_p_id_seq" from PUBLIC;
GRANT ALL on "publikation_p_id_seq" to "conni";
GRANT ALL on "publikation_p_id_seq" to "apache";

CREATE TABLE "publikation" (
	"p_id" integer DEFAULT nextval('"publikation_p_id_seq"'::text) NOT NULL,
	"p_titel" character varying(100) NOT NULL,
	"p_untertitel" character varying(100),
	"p_abstract" text,
	"p_datum" date,
	"p_erstellt" timestamp with time zone DEFAULT now() NOT NULL,
	"len" integer,
	"hat_k" boolean NOT NULL,
	Constraint "publikation_pkey" Primary Key ("p_id")
);

REVOKE ALL on "publikation" from PUBLIC;
GRANT ALL on "publikation" to "conni";
GRANT ALL on "publikation" to "apache";
-------------------------------------------------

CREATE SEQUENCE "kapitel_k_id_seq" start 1 increment 1 maxvalue 2147483647 minvalue 1  cache 1 ;

REVOKE ALL on "kapitel_k_id_seq" from PUBLIC;
GRANT ALL on "kapitel_k_id_seq" to "conni";
GRANT ALL on "kapitel_k_id_seq" to "apache";

CREATE TABLE "kapitel" (
	"k_id" integer DEFAULT nextval('"kapitel_k_id_seq"'::text) NOT NULL,
	"k_titel" character varying(100) NOT NULL,
	"k_nr" smallint NOT NULL,
	"hat_uk" boolean NOT NULL,
	"fk_p_id" integer NOT NULL,
	Constraint "kapitel_pkey" Primary Key ("k_id")
);

REVOKE ALL on "kapitel" from PUBLIC;
GRANT ALL on "kapitel" to "conni";
GRANT ALL on "kapitel" to "apache";
-------------------------------------------------

CREATE SEQUENCE "ukapitel_uk_id_seq" start 1 increment 1 maxvalue 2147483647 minvalue 1  cache 1 ;

REVOKE ALL on "ukapitel_uk_id_seq" from PUBLIC;
GRANT ALL on "ukapitel_uk_id_seq" to "conni";
GRANT ALL on "ukapitel_uk_id_seq" to "apache";

CREATE TABLE "ukapitel" (
	"uk_id" integer DEFAULT nextval('"ukapitel_uk_id_seq"'::text) NOT NULL,
	"uk_nr" smallint NOT NULL,
	"uk_titel" character varying(100),
	"fk_k_id" integer NOT NULL,
	Constraint "ukapitel_pkey" Primary Key ("uk_id")
);

REVOKE ALL on "ukapitel" from PUBLIC;
GRANT ALL on "ukapitel" to "conni";
GRANT ALL on "ukapitel" to "apache";
-------------------------------------------------

CREATE TABLE "daten" (
	"k_id" integer DEFAULT 0 NOT NULL,
	"uk_id" integer DEFAULT 0 NOT NULL,
	"fk_p_id" integer DEFAULT 0 NOT NULL
);

REVOKE ALL on "daten" from PUBLIC;
GRANT ALL on "daten" to "conni";
GRANT ALL on "daten" to "apache";

CREATE SEQUENCE "bilder_bild_id_seq" start 1 increment 1 maxvalue 2147483647 minvalue 1  cache 1 ;

REVOKE ALL on "bilder_bild_id_seq" from PUBLIC;
GRANT ALL on "bilder_bild_id_seq" to "conni";
GRANT ALL on "bilder_bild_id_seq" to "apache";
-------------------------------------------------

CREATE TABLE "bilder" (
	"bild_id" integer DEFAULT nextval('"bilder_bild_id_seq"'::text) NOT NULL,
	"bild" oid,
	"name" character varying(20),
	"size" integer,
	"typ" character varying(20),
	"htmlstr" character varying(30),
	Constraint "bilder_pkey" Primary Key ("bild_id")
)
INHERITS ("daten");

REVOKE ALL on "bilder" from PUBLIC;
GRANT ALL on "bilder" to "conni";
GRANT ALL on "bilder" to "apache";
-------------------------------------------------

CREATE SEQUENCE "texte_text_id_seq" start 14 increment 1 maxvalue 2147483647 minvalue 1  cache 1 ;

REVOKE ALL on "texte_text_id_seq" from PUBLIC;
GRANT ALL on "texte_text_id_seq" to "conni";
GRANT ALL on "texte_text_id_seq" to "apache";

CREATE TABLE "texte" (
	"text_id" integer DEFAULT nextval('"texte_text_id_seq"'::text) NOT NULL,
	"inhalt" text NOT NULL,
	Constraint "texte_pkey" Primary Key ("text_id")
)
INHERITS ("daten");

REVOKE ALL on "texte" from PUBLIC;
GRANT ALL on "texte" to "conni";
GRANT ALL on "texte" to "apache";
-------------------------------------------------

CREATE SEQUENCE "member_m_id_seq" start 1 increment 1 maxvalue 2147483647 minvalue 1  cache 1 ;

REVOKE ALL on "member_m_id_seq" from PUBLIC;
GRANT ALL on "member_m_id_seq" to "conni";
GRANT ALL on "member_m_id_seq" to "apache";

CREATE TABLE "member" (
	"m_id" integer DEFAULT nextval('"member_m_id_seq"'::text) NOT NULL,
	"m_vorname" character varying(30) NOT NULL,
	"m_name" character varying(30) NOT NULL,
	"m_ort" character varying(30),
	"m_email" character varying(100),
	Constraint "member_pkey" Primary Key ("m_id")
);

REVOKE ALL on "member" from PUBLIC;
GRANT ALL on "member" to "conni";
GRANT ALL on "member" to "apache";
-------------------------------------------------

CREATE TABLE "autor" (
	"kommentar" text,
	"fk_p_id" integer NOT NULL,
	"fk_m_id" integer NOT NULL
);

REVOKE ALL on "autor" from PUBLIC;
GRANT ALL on "autor" to "conni";
GRANT ALL on "autor" to "apache";
-------------------------------------------------


CREATE TABLE "login" (
	"l_name" character varying(10) NOT NULL,
	"l_pwd" character varying(10) NOT NULL,
	"fk_m_id" integer NOT NULL,
	"ad" boolean DEFAULT 'f',
	CONSTRAINT "login_l_name" CHECK ((length(btrim((l_name)::text)) >= 5)),
	CONSTRAINT "login_l_pwd" CHECK ((length(btrim((l_pwd)::text)) >= 5))
);

REVOKE ALL on "login" from PUBLIC;
GRANT ALL on "login" to "conni";
GRANT ALL on "login" to "apache";
-------------------------------------------------

CREATE TABLE "t_katalog" (
	"t_bereich" character varying(20) NOT NULL,
	"t_inhalt" text NOT NULL,
	Constraint "t_katalog_pkey" Primary Key ("t_bereich")
);

REVOKE ALL on "t_katalog" from PUBLIC;
GRANT ALL on "t_katalog" to "conni";
GRANT ALL on "t_katalog" to "apache";
-------------------------------------------------

CREATE TABLE "pub_katalog" (
	"fk_p_id" integer NOT NULL,
	"fk_t_bereich" character varying(20) NOT NULL
);

REVOKE ALL on "pub_katalog" from PUBLIC;
GRANT ALL on "pub_katalog" to "conni";
GRANT ALL on "pub_katalog" to "apache";
-------------------------------------------------

CREATE SEQUENCE "byteatest_bild_id_seq" start 1 increment 1 maxvalue 2147483647 minvalue 1  cache 1 ;

REVOKE ALL on "byteatest_bild_id_seq" from PUBLIC;
GRANT ALL on "byteatest_bild_id_seq" to "conni";
GRANT ALL on "byteatest_bild_id_seq" to "apache";

CREATE TABLE "byteatest" (
	"bild_id" integer DEFAULT nextval('"byteatest_bild_id_seq"'::text) NOT NULL,
	"bild" bytea,
	"name" character varying(20),
	"size" integer,
	"typ" character varying(20),
	"htmlstr" character varying(30),
	Constraint "byteatest_pkey" Primary Key ("bild_id")
)
INHERITS ("daten");

REVOKE ALL on "byteatest" from PUBLIC;
GRANT ALL on "byteatest" to "conni";
GRANT ALL on "byteatest" to "apache";
-------------------------------------------------

CREATE FUNCTION "heute" () RETURNS text AS 'select to_char(now(),''DD.MM.YYYY'');' LANGUAGE 'sql';

CREATE FUNCTION "autor2titel" (character varying,character varying) RETURNS SETOF character varying AS 'select p_titel from publikation join autor on (p_id=fk_p_id) join member on (m_id=fk_m_id) where m_vorname = $1 and m_name = $2' LANGUAGE 'sql';

CREATE FUNCTION "datum" (timestamp with time zone) RETURNS text AS 'select to_char($1,''DD.MM.YYYY'');' LANGUAGE 'sql';

CREATE FUNCTION "ka" (member) RETURNS member AS 'update member set m_email = ''unbekannt'' where m_email =''keine Angabe''; select * from member;' LANGUAGE 'sql';

CREATE FUNCTION "autor2pid" (character varying,character varying) RETURNS SETOF integer AS 'select p_id as pid  from publikation join autor on (p_id=fk_p_id) join member on (fk_m_id=m_id) where m_vorname = $1 and m_name = $2;' LANGUAGE 'sql';

CREATE FUNCTION "suche" (character varying) RETURNS SETOF integer AS 'select p_id as pid from publikation join autor on (p_id=fk_p_id) join member on (m_id=fk_m_id) where (trim(m_vorname)||'' ''||trim(m_name)) = $1 or (trim(m_name)||'' ''||trim(m_vorname)) = $1 or m_vorname = $1 or m_name = $1' LANGUAGE 'sql';

CREATE FUNCTION "suche_pid" (character varying) RETURNS integer AS 'declare
aut alias for $1;

begin
return p_id from publikation join autor on (p_id=fk_p_id) join member on (m_id=fk_m_id) where (trim(m_vorname)||'' ''||trim(m_name)) = aut or m_name =  aut or m_vorname = aut;

end;

' LANGUAGE 'plpgsql';


CREATE FUNCTION "aalogin" (character varying,character varying) RETURNS text AS 'declare
benutzer alias for $1;
passwort alias for $2;
btmp varchar;
ptmp varchar;
begin
select l_name,l_pwd into btmp,ptmp from login where l_name = benutzer and l_pwd = passwort;
if found then
  return ''Login korrekt'';
else
  return ''Login inkorrekt'';
end if;
end;

' LANGUAGE 'plpgsql';


CREATE FUNCTION "name2id" (character varying,character varying) RETURNS integer AS 'declare
vorname alias for $1;
nachname alias for $2;
id integer;
begin
  select m_id into id FROM member where m_vorname = vorname and m_name = nachname;
if not found then
return -1;
else
return id;
end if;
end;

' LANGUAGE 'plpgsql';


CREATE FUNCTION "id2name" (integer) RETURNS text AS 'declare
zeile record;
begin
select into zeile * from member where m_id = $1;
if found then
return zeile.m_vorname||'' ''||zeile.m_name;
else
return ''Nichts gefunden'';
end if;
end;

' LANGUAGE 'plpgsql';


CREATE FUNCTION "aatitel" () RETURNS text AS 'declare
  ausgabe text := '''';
  zeile publikation%ROWTYPE;
  umbruch varchar := ''
'';
begin
 for zeile in select  * from publikation loop
  ausgabe := ausgabe||'' ''||zeile.p_titel||umbruch;
 end loop;
 return ausgabe;
end;

' LANGUAGE 'plpgsql';


CREATE FUNCTION "aagarbage" () RETURNS opaque AS 'declare
m_zeile record;
k_zeile record;
begin
  raise notice ''Publikation % geloescht'',old.p_id;
  for m_zeile in
    select * from member where m_id not in
    (select fk_m_id from autor where fk_p_id != old.p_id)
    and m_id not in
    (select fk_m_id from login)
  loop
    delete from member where m_id = m_zeile.m_id;
    raise notice ''Datensatz mit Member_id % wurde geloescht'',m_zeile.m_id;
  end loop;
  for k_zeile in
    select * from t_katalog where t_bereich not in
    (select fk_t_bereich from pub_katalog where fk_p_id != old.p_id)
  loop
    delete from t_katalog where t_bereich = k_zeile.t_bereich;
    raise notice ''Das Schlagwort % wurde geloescht'',k_zeile.t_bereich;
  end loop;
  return null;
end;

' LANGUAGE 'plpgsql';


CREATE  INDEX "uk_k_idx" on "ukapitel" using btree ( "fk_k_id" "int4_ops" );

CREATE  INDEX "uk_nr_idx" on "ukapitel" using btree ( "uk_nr" "int2_ops" );

CREATE  INDEX "texte_k_idx" on "texte" using btree ( "k_id" "int4_ops" );

CREATE  INDEX "texte_uk_idx" on "texte" using btree ( "uk_id" "int4_ops" );

CREATE  INDEX "texte_p_idx" on "texte" using btree ( "fk_p_id" "int4_ops" );

CREATE  INDEX "members_idx" on "member" using btree ( "m_name" "varchar_ops", "m_vorname" "varchar_ops" );

CREATE UNIQUE INDEX "autor_fk_p_id_key" on "autor" using btree ( "fk_p_id" "int4_ops", "fk_m_id" "int4_ops" );

CREATE  INDEX "autor_memb_idx" on "autor" using btree ( "fk_m_id" "int4_ops" );

CREATE  INDEX "autor_pub_idx" on "autor" using btree ( "fk_p_id" "int4_ops" );

CREATE UNIQUE INDEX "login_l_name_key" on "login" using btree ( "l_name" "varchar_ops", "l_pwd" "varchar_ops" );

CREATE UNIQUE INDEX "memberlogin_idx" on "login" using btree ( "fk_m_id" "int4_ops" );

CREATE  INDEX "pk_bereich_idx" on "pub_katalog" using btree ( "fk_t_bereich" "varchar_ops" );

CREATE  INDEX "pk_titelid_idx" on "pub_katalog" using btree ( "fk_p_id" "int4_ops" );

CREATE CONSTRAINT TRIGGER "<unnamed>" AFTER INSERT OR UPDATE ON "kapitel"  FROM "publikation" NOT DEFERRABLE INITIALLY IMMEDIATE FOR EACH ROW EXECUTE PROCEDURE "RI_FKey_check_ins" ('<unnamed>', 'kapitel', 'publikation', 'UNSPECIFIED', 'fk_p_id', 'p_id');

CREATE CONSTRAINT TRIGGER "<unnamed>" AFTER DELETE ON "publikation"  FROM "kapitel" NOT DEFERRABLE INITIALLY IMMEDIATE FOR EACH ROW EXECUTE PROCEDURE "RI_FKey_cascade_del" ('<unnamed>', 'kapitel', 'publikation', 'UNSPECIFIED', 'fk_p_id', 'p_id');

CREATE CONSTRAINT TRIGGER "<unnamed>" AFTER UPDATE ON "publikation"  FROM "kapitel" NOT DEFERRABLE INITIALLY IMMEDIATE FOR EACH ROW EXECUTE PROCEDURE "RI_FKey_cascade_upd" ('<unnamed>', 'kapitel', 'publikation', 'UNSPECIFIED', 'fk_p_id', 'p_id');

CREATE CONSTRAINT TRIGGER "<unnamed>" AFTER INSERT OR UPDATE ON "ukapitel"  FROM "kapitel" NOT DEFERRABLE INITIALLY IMMEDIATE FOR EACH ROW EXECUTE PROCEDURE "RI_FKey_check_ins" ('<unnamed>', 'ukapitel', 'kapitel', 'UNSPECIFIED', 'fk_k_id', 'k_id');

CREATE CONSTRAINT TRIGGER "<unnamed>" AFTER DELETE ON "kapitel"  FROM "ukapitel" NOT DEFERRABLE INITIALLY IMMEDIATE FOR EACH ROW EXECUTE PROCEDURE "RI_FKey_cascade_del" ('<unnamed>', 'ukapitel', 'kapitel', 'UNSPECIFIED', 'fk_k_id', 'k_id');

CREATE CONSTRAINT TRIGGER "<unnamed>" AFTER UPDATE ON "kapitel"  FROM "ukapitel" NOT DEFERRABLE INITIALLY IMMEDIATE FOR EACH ROW EXECUTE PROCEDURE "RI_FKey_cascade_upd" ('<unnamed>', 'ukapitel', 'kapitel', 'UNSPECIFIED', 'fk_k_id', 'k_id');

CREATE CONSTRAINT TRIGGER "<unnamed>" AFTER INSERT OR UPDATE ON "autor"  FROM "publikation" NOT DEFERRABLE INITIALLY IMMEDIATE FOR EACH ROW EXECUTE PROCEDURE "RI_FKey_check_ins" ('<unnamed>', 'autor', 'publikation', 'UNSPECIFIED', 'fk_p_id', 'p_id');

CREATE CONSTRAINT TRIGGER "<unnamed>" AFTER DELETE ON "publikation"  FROM "autor" NOT DEFERRABLE INITIALLY IMMEDIATE FOR EACH ROW EXECUTE PROCEDURE "RI_FKey_cascade_del" ('<unnamed>', 'autor', 'publikation', 'UNSPECIFIED', 'fk_p_id', 'p_id');

CREATE CONSTRAINT TRIGGER "<unnamed>" AFTER UPDATE ON "publikation"  FROM "autor" NOT DEFERRABLE INITIALLY IMMEDIATE FOR EACH ROW EXECUTE PROCEDURE "RI_FKey_cascade_upd" ('<unnamed>', 'autor', 'publikation', 'UNSPECIFIED', 'fk_p_id', 'p_id');

CREATE CONSTRAINT TRIGGER "<unnamed>" AFTER INSERT OR UPDATE ON "autor"  FROM "member" NOT DEFERRABLE INITIALLY IMMEDIATE FOR EACH ROW EXECUTE PROCEDURE "RI_FKey_check_ins" ('<unnamed>', 'autor', 'member', 'UNSPECIFIED', 'fk_m_id', 'm_id');

CREATE CONSTRAINT TRIGGER "<unnamed>" AFTER DELETE ON "member"  FROM "autor" NOT DEFERRABLE INITIALLY IMMEDIATE FOR EACH ROW EXECUTE PROCEDURE "RI_FKey_cascade_del" ('<unnamed>', 'autor', 'member', 'UNSPECIFIED', 'fk_m_id', 'm_id');

CREATE CONSTRAINT TRIGGER "<unnamed>" AFTER UPDATE ON "member"  FROM "autor" NOT DEFERRABLE INITIALLY IMMEDIATE FOR EACH ROW EXECUTE PROCEDURE "RI_FKey_cascade_upd" ('<unnamed>', 'autor', 'member', 'UNSPECIFIED', 'fk_m_id', 'm_id');

CREATE CONSTRAINT TRIGGER "<unnamed>" AFTER INSERT OR UPDATE ON "login"  FROM "member" NOT DEFERRABLE INITIALLY IMMEDIATE FOR EACH ROW EXECUTE PROCEDURE "RI_FKey_check_ins" ('<unnamed>', 'login', 'member', 'UNSPECIFIED', 'fk_m_id', 'm_id');

CREATE CONSTRAINT TRIGGER "<unnamed>" AFTER DELETE ON "member"  FROM "login" NOT DEFERRABLE INITIALLY IMMEDIATE FOR EACH ROW EXECUTE PROCEDURE "RI_FKey_cascade_del" ('<unnamed>', 'login', 'member', 'UNSPECIFIED', 'fk_m_id', 'm_id');

CREATE CONSTRAINT TRIGGER "<unnamed>" AFTER UPDATE ON "member"  FROM "login" NOT DEFERRABLE INITIALLY IMMEDIATE FOR EACH ROW EXECUTE PROCEDURE "RI_FKey_cascade_upd" ('<unnamed>', 'login', 'member', 'UNSPECIFIED', 'fk_m_id', 'm_id');

CREATE CONSTRAINT TRIGGER "<unnamed>" AFTER INSERT OR UPDATE ON "pub_katalog"  FROM "publikation" NOT DEFERRABLE INITIALLY IMMEDIATE FOR EACH ROW EXECUTE PROCEDURE "RI_FKey_check_ins" ('<unnamed>', 'pub_katalog', 'publikation', 'UNSPECIFIED', 'fk_p_id', 'p_id');

CREATE CONSTRAINT TRIGGER "<unnamed>" AFTER DELETE ON "publikation"  FROM "pub_katalog" NOT DEFERRABLE INITIALLY IMMEDIATE FOR EACH ROW EXECUTE PROCEDURE "RI_FKey_cascade_del" ('<unnamed>', 'pub_katalog', 'publikation', 'UNSPECIFIED', 'fk_p_id', 'p_id');

CREATE CONSTRAINT TRIGGER "<unnamed>" AFTER UPDATE ON "publikation"  FROM "pub_katalog" NOT DEFERRABLE INITIALLY IMMEDIATE FOR EACH ROW EXECUTE PROCEDURE "RI_FKey_cascade_upd" ('<unnamed>', 'pub_katalog', 'publikation', 'UNSPECIFIED', 'fk_p_id', 'p_id');

CREATE CONSTRAINT TRIGGER "<unnamed>" AFTER INSERT OR UPDATE ON "pub_katalog"  FROM "t_katalog" NOT DEFERRABLE INITIALLY IMMEDIATE FOR EACH ROW EXECUTE PROCEDURE "RI_FKey_check_ins" ('<unnamed>', 'pub_katalog', 't_katalog', 'UNSPECIFIED', 'fk_t_bereich', 't_bereich');

CREATE CONSTRAINT TRIGGER "<unnamed>" AFTER DELETE ON "t_katalog"  FROM "pub_katalog" NOT DEFERRABLE INITIALLY IMMEDIATE FOR EACH ROW EXECUTE PROCEDURE "RI_FKey_cascade_del" ('<unnamed>', 'pub_katalog', 't_katalog', 'UNSPECIFIED', 'fk_t_bereich', 't_bereich');

CREATE CONSTRAINT TRIGGER "<unnamed>" AFTER UPDATE ON "t_katalog"  FROM "pub_katalog" NOT DEFERRABLE INITIALLY IMMEDIATE FOR EACH ROW EXECUTE PROCEDURE "RI_FKey_cascade_upd" ('<unnamed>', 'pub_katalog', 't_katalog', 'UNSPECIFIED', 'fk_t_bereich', 't_bereich');

CREATE TRIGGER "garbage" AFTER DELETE ON "publikation"  FOR EACH ROW EXECUTE PROCEDURE "aagarbage" ();