/* 
docgen.sql, Buch Seite 155 - 157
*/

CREATE TABLE publikation(
	p_id serial PRIMARY KEY,
	p_titel varchar(100) NOT NULL,
	p_untertitel varchar(100),
	p_abstract text,
	p_datum date,
	p_erstellt timestamp DEFAULT now() NOT NULL,
	hat_k bool NOT NULL
);

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


CREATE TABLE kapitel(
	k_id serial PRIMARY KEY,
	k_titel varchar(100) NOT NULL,
	k_nr smallint NOT NULL,
	hat_uk bool NOT NULL,
	fk_p_id integer NOT NULL REFERENCES publikation (p_id)
		ON DELETE CASCADE
		ON UPDATE CASCADE
);

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



CREATE TABLE ukapitel(
	uk_id serial PRIMARY KEY,
	uk_nr smallint NOT NULL,
	uk_titel varchar(100),
	fk_k_id integer NOT NULL REFERENCES kapitel (k_id)
		ON DELETE CASCADE
		ON UPDATE CASCADE
);

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


CREATE TABLE ptext( 
	text_id serial PRIMARY KEY, 
	inhalt text NOT NULL,
	k_id integer NOT NULL DEFAULT 0,
	uk_id integer NOT NULL DEFAULT 0,
	fk_p_id integer NOT NULL REFERENCES publikation (p_id)
		ON DELETE CASCADE
		ON UPDATE CASCADE
);

REVOKE ALL on "ptext" from PUBLIC;
GRANT ALL on "ptext" to "apache";


CREATE TABLE member(
	m_id serial PRIMARY KEY,
	m_vorname varchar(30) NOT NULL,
	m_name varchar(30) NOT NULL,
	m_ort varchar(30),
	m_email varchar(100)
);

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


CREATE TABLE autor(
	kommentar text,
	fk_p_id integer NOT NULL REFERENCES publikation (p_id)
		ON UPDATE CASCADE
		ON DELETE CASCADE,
	fk_m_id integer NOT NULL REFERENCES member (m_id)
		ON UPDATE CASCADE
		ON DELETE CASCADE,
	UNIQUE (fk_p_id, fk_m_id)
);

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


CREATE TABLE login(
	l_name varchar(6) NOT NULL UNIQUE
		CHECK (length(trim(both FROM l_name)) >= 5),
	l_pwd varchar(6) NOT NULL UNIQUE
		CHECK (length(btrim(l_pwd)) >= 5),
	fk_m_id integer NOT NULL REFERENCES member (m_id)
		ON DELETE CASCADE
		ON UPDATE CASCADE
);

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


CREATE TABLE t_katalog( 
	t_bereich varchar(20) PRIMARY KEY, 
	t_inhalt text NOT NULL 
);

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


CREATE TABLE pub_katalog(
	fk_p_id integer NOT NULL REFERENCES publikation (p_id)
		ON UPDATE CASCADE
		ON DELETE CASCADE,
	fk_t_bereich varchar(20) NOT NULL REFERENCES t_Katalog (t_bereich)
		ON UPDATE CASCADE
		ON DELETE CASCADE
);

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