begin;

\i plpgsql_funcs/cr_func_generic.sql

COMMENT ON DATABASE shop IS 
    '<H3><U>Allgemeine Kommentare:</U></H3>'
    'die Vergabe der Triggernamen folgt einem bestimmten Schema:<BR><BR>'

    '  &nbsp;&lt;TABLE&gt;_trigger&lt;NR&gt;_on_'
        '[change|insert|update|delete]_&lt;DESCR&gt;<BR><BR>'

    'wobei &lt;TABLE&gt; der Tabellenname ist, fuer den der Trigger definiert '
    'wurde &lt;NR&gt; eine fortlaufende Nummer (1,2,...,n) fuer die zu der '
    'Tabelle definierten Trigger, "change" steht fuer "insert und update" und '
    '&lt;DESCR&gt; ist eine frei vergebbare Beschreibung dessen was der '
    'Trigger tut.  Dieses Schema dient hauptsaechlich dem Zweck eine '
    'Reihenfolge fuer die Ausfuehrung der Trigger definieren zu koennen. '
    'Da postgreSQL die Trigger zu einer Aktion in alphabetisch aufsteigender '
    'Reihenfolge ausfuehrt laesst sich uber &lt;NR&gt; bestimmen in welcher '
    'Reihenfolge die Trigger ausgefuehrt werden, <BR><BR>'

    '  &nbsp;..._trigger1_... =&gt; ..._trigger2_... =&gt; ...<BR><BR>'

    '<HR><BR>'
    'ich muss zumindest ganz am schluss aber besser noch frueher Transaktionen '
    'in meine Funktionen einbauen. Ich glaube das diese idealerweise in den '
    'plpgsql Funktionen eingebaut werden, dann kann man spaeter in PHP oder '
    'womit auch immer diese Funktionen nutzen und hat automatisch bessere '
    'Performance....aus demselben Grund ist es im Moment noch nicht unbedingt '
    'notwendig transaktionen einzubauen, wenn konsequent hauptsaechlich die '
    'Funktionen genutzt werden.<BR>'
    'OK, ich revidiere meine Meinung, Transaktionen sollten im hoeheren Level '
    'implementiert werden, alleine schon deshalb weil BEGIN WORK nicht '
    'innerhalb einer plpgsql Funktion nicht funtioniert, wenn krasse Feler in '
    'plpgsql Funktionen auftreten (solche die sich nicht beheben lassen) '
    'sollte man konsequent RAISE EXCEPTION nutzen um den ganzen Funktionsblock '
    'abzubrechen, dadurch werden auch alle aenderungen die die Funktion '
    'gemacht hat wieder zurueckgesetzt.<BR><BR>'

    '<HR><BR>'

    'alle Kommentare in der Datenbank muessen zu HTML konvertiert werden.'
    '<BR><BR><HR>';

-- generic Users
-- [
-- create group guest;
-- create group customer;
-- create group admin;

-- create user guest ENCRYPTED PASSWORD 'guest'
--    NOCREATEDB NOCREATEUSER in group guest;
-- ]

-- Schemas
-- [
create schema trigger_func; -- Schema fuer Triggerfunktionen
COMMENT ON SCHEMA trigger_func IS 'Schema fuer Triggerfunktionen';
create schema adresse;      -- alles zu Adressdaten
COMMENT ON SCHEMA adresse IS 'alles zu Adressdaten';
create schema person;       -- alles zu Personen
COMMENT ON SCHEMA person IS 'alles zu Personen';
-- nur zum testen [ --
grant USAGE ON SCHEMA person TO GROUP guest;
-- ] --
create schema firma;        -- alles zu Firmen
COMMENT ON SCHEMA firma IS 'alles zu Firmen';
create schema fibu;         -- alles zu Konten etc.
COMMENT ON SCHEMA fibu IS 'alles zu Konten etc.';
create schema waren;        -- alles zu Warengruppen und Produkten
COMMENT ON SCHEMA waren IS 'alles zu Warengruppen und Produkten';
grant USAGE ON SCHEMA waren TO GROUP guest;
create schema benutzer;     -- alles zur Benutzerverwltung
COMMENT ON SCHEMA benutzer IS 'alles zur Benutzerverwltung';
-- ]
-- ende Schemas --

-- meine Aktionshistory
-- [
CREATE TABLE history (
    "username" text NOT NULL,     -- der User der die Aktion ausgeloest hat.
    "time" timestamp NOT NULL,    -- wann wurde die Aktion ausgeloest.
    "tab" text NOT NULL,          -- der betroffene Table.
    "row_oid" int4 NOT NULL,      -- die OID der betroffenen Zeile.
    "at_time" text NOT NULL,      -- BEFORE, AFTER.
    "operation" text NOT NULL,    -- INSERT, UPDATE, DELETE.
    "col_names" text[] NOT NULL,  -- zur uebersicht die column-namen
    "col_types" text[] NOT NULL,  -- zur uebersicht die column-typen
    "col_vals" text[] NOT NULL);  -- aktuelle Feldwerte

set search_path to trigger_func;

\i trigger_funcs/cr_trigger_func_generic.sql

set search_path to public;
-- ]
-- ende History

-- Table zu Länder --
-- [
set search_path to adresse;

create table land (
    "_id_" SERIAL NOT NULL PRIMARY KEY,     -- interne id
    "land" text NOT NULL,                   -- Name(Bezeichnung) des Landes
    "lnd_kz" varchar(3) NOT NULL,           -- intern. Post-Kennzeichen
    "vorwahl_l" varchar(4) NOT NULL);       -- intern. Laendervorwahl

COMMENT ON TABLE land IS 'Tabelle zu Laender';

COMMENT ON COLUMN land._id_ IS 'interne id';
COMMENT ON COLUMN land.land IS 'Name (Bezeichnung) des Landes';
COMMENT ON COLUMN land.lnd_kz IS 'internationales Post-Kennzeichen';
COMMENT ON COLUMN land.vorwahl_l IS 'internationale Telefonvorwahl des Landes';

create unique index "land_ukey1" on land (land);
create unique index "land_ukey2" on land (lnd_kz);
create unique index "land_ukey3" on land (vorwahl_l);

\i plpgsql_funcs/cr_func_land.sql

select public.start_logging('land', 'ALL');
-- ]
-- Ende Länder --

-- Table zu Orte --
-- [
create table "ort" (
    "_id_" SERIAL NOT NULL PRIMARY KEY,     -- interne id
    "ort" text NOT NULL,                    -- Name (Bezeichnung) des Orts
    "vorwahl_o" varchar(10) NOT NULL,       -- Ortsvorwahl
    "id_land" int4 NOT NULL,                -- Land, in dem der Ort liegt
    CONSTRAINT fk_ort1 FOREIGN KEY (id_land) REFERENCES land (_id_)
        ON DELETE CASCADE
        ON UPDATE CASCADE);

COMMENT ON TABLE ort IS 'Tabelle zu Orten';

COMMENT ON COLUMN ort._id_ IS 'interne id';
COMMENT ON COLUMN ort.ort IS 'Name (Bezeichnung) des Ortes';
COMMENT ON COLUMN ort.vorwahl_o IS 'Ortsvorwahl';
COMMENT ON COLUMN ort.id_land IS 'FOREIGN KEY: Land in dem der Ort liegt';

COMMENT ON CONSTRAINT fk_ort1 ON ort IS '
wird ein Land geloescht so muessen auch alle dazu gehörenden Orte
geloescht werden da moeglicherweise Orte mit gleichen Namen und 
gleicher Vorwahl zu verschiedenen Laendern gehoeren und dadurch
Inkonsistenzen entstehen koennen.';

create unique index "ort_ukey1" on ort (vorwahl_o,id_land);

\i plpgsql_funcs/cr_func_ort.sql

select public.start_logging('ort', 'ALL');
-- ]
-- ende Orte --

-- Table zu Adressen --
-- [
create table "adresse" (
    "_id_" SERIAL NOT NULL PRIMARY KEY,   -- interne id
    "strasse" text NOT NULL,                    -- Strassenname
    "h_nr" varchar(5) NOT NULL,                 -- Hausnummer
    "plz_a" varchar(10) NOT NULL,               -- Postleitzahl
    "id_ort" int4 NULL,                         -- Ort
    "id_land" int4 NOT NULL,                    -- Land
    CONSTRAINT fk_adresse1 FOREIGN KEY (id_ort) REFERENCES ort (_id_)
        ON DELETE SET NULL
        ON UPDATE CASCADE,
    CONSTRAINT fk_adresse2 FOREIGN KEY (id_land) REFERENCES land (_id_)
        ON DELETE CASCADE
        ON UPDATE CASCADE);

COMMENT ON TABLE adresse IS 'Tabelle zu Adressen:
------------------
id_land habe ich mit aufgenommen, da es sein kann das ich den Ort zu
einer Adresse nicht kenne, aber weiß in welchem Land sie liegt, dann
kann ich nicht über den Ort das Land ermitteln, was man nach einer
ersten Ueberlegung fuer den logischeren Weg halten koennte.
Das Land zu einer Adresse muss bekannt sein, damit das passenden
Laenderkennzeichen zur Postleitzahl ergaenzt werden kann.';

COMMENT ON COLUMN adresse._id_ IS 'interne id';
COMMENT ON COLUMN adresse.strasse IS 'Strassenname';
COMMENT ON COLUMN adresse.h_nr IS 'Hausnummer';
COMMENT ON COLUMN adresse.plz_a IS 'Postleitzahl';
COMMENT ON COLUMN adresse.id_ort IS 'FOREIGN KEY: Ort';
COMMENT ON COLUMN adresse.id_land IS 'FOREIGN KEY: Land';

COMMENT ON CONSTRAINT fk_adresse1 ON adresse IS '
auch wenn ich den Ort zu einer Adresse nicht kennen so reicht doch
in der Regel auch die Postleitzahl, daher behalte ich erstmal alle
Adressen wenn der dazugehoerige Ort geloescht wird, setze aber
ort_id auf NULL. Dann kann allerdings die ort_id nicht zum
Schluessel dieser Tabelle gehoeren. Stattdessen nehme ich die plz.';
COMMENT ON CONSTRAINT fk_adresse2 ON adresse IS '
wird ein Land geloescht, so muß ich um inkonsistenzen zu vermeiden
auch alle dazu gespeicherten Adressen entfernen, da sonst genau wie
bei den Orten zwei gleiche Adressen in der DB stehen koennten.';

create unique index "adresse_ukey1" on adresse (strasse,h_nr,plz_a,id_land);

\i plpgsql_funcs/cr_func_adresse.sql

select public.start_logging('adresse', 'ALL');

set search_path to public;
-- ]
-- ende Adressen --

-- Tabellen für Personendaten
-- [
set search_path to person;

create table "person" (
    "_id_" SERIAL NOT NULL PRIMARY KEY,  -- interne id
    "anrede" varchar(10) NULL,           -- Herr, Frau, Firma
    "titel" text NULL,                   -- Dr., Prof., Dipl. Ing...
    "nachname" text NOT NULL,            -- Nachname
    "vorname" text NOT NULL,             -- Vorname
    "geb_dat" date NOT NULL,             -- Geburtsdatum
    "postfach" varchar(10) NULL,         -- Postfach
    "telefon" varchar(10) NULL,          -- Telefon persönlich
    "fax" varchar(10) NULL,              -- Fax persoenlich
    "handy" varchar(50) NULL,            -- Handy persoenlich
    "email" varchar(50) NULL,            -- E-Mail persoenlich
    "webpage" varchar(100) NULL);        -- Webseite persoenlich

COMMENT ON TABLE person IS 'Tabelle für Personendaten';

COMMENT ON COLUMN person._id_ IS 'interne id';
COMMENT ON COLUMN person.anrede IS 'Herr, Frau, Firma';
COMMENT ON COLUMN person.titel IS 'Dr., Prof., Dipl. Ing...';
COMMENT ON COLUMN person.nachname IS 'Nachname';
COMMENT ON COLUMN person.vorname IS 'Vorname';
COMMENT ON COLUMN person.geb_dat IS 'Geburtsdatum';
COMMENT ON COLUMN person.postfach IS 'Postfach';
COMMENT ON COLUMN person.telefon IS 'Telefon persönlich';
COMMENT ON COLUMN person.fax IS 'Fax persoenlich';
COMMENT ON COLUMN person.handy IS 'Handy persoenlich';
COMMENT ON COLUMN person.email IS 'E-Mail persoenlich';
COMMENT ON COLUMN person.webpage IS 'Webseite persoenlich';

create unique index "person_ukey1" on person (nachname, vorname, geb_dat);

select public.start_logging('person', 'ALL');

-- wohnt --
create table "wohnt" (
    id_person int4 NOT NULL,        -- Person
    id_adresse int4 NOT NULL,       -- Adresse
    PRIMARY KEY (id_person, id_adresse),
    FOREIGN KEY (id_person) REFERENCES person (_id_)
        -- hier wird immer die ganze zuordnung geloescht, das ist ok, da
        -- dadurch keinerlei weitere Daten verlohren gehen koennen.
        ON DELETE CASCADE
        ON UPDATE CASCADE,
    FOREIGN KEY (id_adresse) REFERENCES adresse.adresse (_id_)
        -- hier wird immer die ganze zuordnung geloescht, das ist ok, da
        -- dadurch keinerlei weitere Daten verlohren gehen koennen.
        ON DELETE CASCADE
        ON UPDATE CASCADE);

COMMENT ON TABLE wohnt IS 'Adresse zu Person';

COMMENT ON COLUMN wohnt.id_person IS 'Person';
COMMENT ON COLUMN wohnt.id_adresse IS 'Adresse';

\i plpgsql_funcs/cr_func_person.sql

select public.start_logging('wohnt', 'ALL');

set search_path to public;
-- ]
-- ende Personendaten

-- Arbeitsplatz --
-- [
set search_path to firma;

create table "firma" (
    "_id_" SERIAL NOT NULL PRIMARY KEY,       -- interne id
    "name" text NOT NULL,                     -- Name (Bezeichnung) der Firma
    "beschreibung" text NULL,                 -- Was fuer eine Firma ist das
    "plz_f" varchar(10) NULL);                -- Postleitzahl (große Firme)

COMMENT ON TABLE firma IS 'Firmengrundinformation:
-------------------------------
meine Konzeption sieht vor das jede Firma mindestens eine Abteilung hat,
die alle Mitarbeiter umfasst wenn es keine weiteren Abteilungen gibt, gibt
es weitere Abteilungen koennen alle, einige oder keine Mitarbeiter zu dieser
Abteilung erfasst sein. Diese Abteilung heisst Firma.
Die Adresse zu dieser Abteilung ist immer die Adresse des Hauptsitzes der
Firma.';

COMMENT ON COLUMN firma._id_ IS 'interne id';
COMMENT ON COLUMN firma.name IS 'Name (Bezeichnung) der Firma';
COMMENT ON COLUMN firma.beschreibung IS 'Was fuer eine Firma ist das';
COMMENT ON COLUMN firma.plz_f IS 'Postleitzahl (große Firme)';

create unique index "firma_ukey1" on firma (name,plz_f);

select public.start_logging('firma', 'ALL');

create table "abteilung" (
    "_id_" SERIAL NOT NULL PRIMARY KEY,   -- interne id
    "bezeichnung" text NOT NULL,          -- Name (Bezeichnung) der Abt.
    "beschreibung" text NULL,             -- was macht diese Abteilung
    "telefon" varchar(10) NULL,           -- Telefon (Zentrale)
    "fax" varchar(10) NULL,               -- Fax Abteilung
    "email" varchar(50) NULL,             -- E-Mail (gemeins. fuer Abtl.)
    "postfach" varchar(10) NULL,          -- Postfach
    "id_firma" int4 NOT NULL,             -- zu welcher Firma gehört sie
    "id_adresse" int4 NULL,               -- wo liegt sie.
    CONSTRAINT fk_abteilung1 
        FOREIGN KEY (id_adresse) REFERENCES adresse.adresse (_id_)
            ON DELETE SET NULL
            ON UPDATE CASCADE,
    CONSTRAINT fk_abteilung2 FOREIGN KEY (id_firma) REFERENCES firma (_id_)
        ON DELETE CASCADE
        ON UPDATE CASCADE);

COMMENT ON TABLE abteilung IS 'Abteilungen zu Firmen
(siehe auch Kommentar zu table firma)
-------------------------------------
Bislang wird die Abteilung beim anlegen einer Firma in der function 
ins_firma mit angelegt, besser waehre jedoch ein Trigger der bei jedem
insert in firma eine passende abteilung anlegt.';

COMMENT ON COLUMN abteilung._id_ IS 'interne id';
COMMENT ON COLUMN abteilung.bezeichnung IS 'Name (Bezeichnung) der Abt.';
COMMENT ON COLUMN abteilung.beschreibung IS 'was macht diese Abteilung';
COMMENT ON COLUMN abteilung.telefon IS 'Telefon (Zentrale)';
COMMENT ON COLUMN abteilung.fax IS 'Fax Abteilung';
COMMENT ON COLUMN abteilung.email IS 'E-Mail (gemeins. fuer Abtl.)';
COMMENT ON COLUMN abteilung.postfach IS 'Postfach';
COMMENT ON COLUMN abteilung.id_firma IS 
    'FOREIGN KEY: zu welcher Firma gehört sie';
COMMENT ON COLUMN abteilung.id_adresse IS 'FOREIGN KEY: wo liegt sie';

COMMENT ON CONSTRAINT fk_abteilung1 ON abteilung IS '
wird eine Adresse geloescht, so moechte ich unter umständen trotzdem
gerne alle weiteren Firmendaten halten. Daher setze ich in diesem
Fall id_adresse auf NULL.';
COMMENT ON CONSTRAINT fk_abteilung2 ON abteilung IS '
wird eine Firma gelöscht, so werden auch alle zu ihr gehoerenden
Abteilungen geloescht.';

create unique index "abteilung_ukey1" on abteilung (bezeichnung, id_firma);

select public.start_logging('abteilung', 'ALL');

create table "arbeit" (
    "id_person" int4 NOT NULL,     -- Person die arbeitet
    "id_abteilung" int4 NOT NULL,  -- Abteilung in der sie arbeitet
    "position" text NOT NULL,      -- Position innerhalb der Abt. o. Firma
    "beschreibung" text NULL,      -- Beschreibung der Taetigkeit
    "telefon" varchar(10) NULL,    -- Telefon beruflich
    "fax" varchar(10) NULL,        -- Fax beruflich
    "email" varchar(50) NULL,      -- E-Mail beruflich
    "gehalt" numeric(10,2) NULL,   -- Gehalt
    PRIMARY KEY (id_person, id_abteilung),
    CONSTRAINT fk_arbeit1 
        FOREIGN KEY (id_person) REFERENCES person.person (_id_)
            ON DELETE CASCADE
            ON UPDATE CASCADE,
    CONSTRAINT fk_arbeit2 FOREIGN KEY (id_abteilung) REFERENCES abteilung (_id_)
        ON DELETE CASCADE
        ON UPDATE CASCADE);

COMMENT ON TABLE arbeit IS 'Person arbeitet in Abteilung';

COMMENT ON COLUMN arbeit.id_person IS 'FOREIGN KEY: Person die arbeitet';
COMMENT ON COLUMN arbeit.id_abteilung IS 
    'FOREIGN KEY: Abteilung in der sie arbeitet';
COMMENT ON COLUMN arbeit.position IS 'Position innerhalb der Abt. o. Firma';
COMMENT ON COLUMN arbeit.beschreibung IS 'Beschreibung der Taetigkeit';
COMMENT ON COLUMN arbeit.telefon IS 'Telefon beruflich';
COMMENT ON COLUMN arbeit.fax IS 'Fax beruflich';
COMMENT ON COLUMN arbeit.email IS 'E-Mail beruflich';
COMMENT ON COLUMN arbeit.gehalt IS 'Gehalt';

COMMENT ON CONSTRAINT fk_arbeit1 ON arbeit IS '
da es sich hier nur um eine Relation mit Zusatzinformationen
handelt ist ein loeschen ok, denn wenn die Person geloescht wird
sind auch die Zusatzinfos uninteressant.';
COMMENT ON CONSTRAINT fk_arbeit2 ON arbeit IS '
da es sich hier nur um eine Relation mit Zusatzinformationen
handelt ist ein loeschen ok, denn wenn die Abteilung geloescht wird
sind auch die Zusatzinfos uninteressant. Die Person ist dann
quasi arbeitslos in meiner DB.';

\i plpgsql_funcs/cr_func_arbeit.sql

select public.start_logging('arbeit', 'ALL');

set search_path to public;
-- ]
-- ende Arbeitsplatz --

-- Konto --
-- [
set search_path to fibu;

create table "konten_typen" (
    "mask" bit varying NOT NULL PRIMARY KEY, -- interne id
    "name" text NOT NULL,                -- Bezeichnung des Kontentyps (AKTIVA)
    "beschreibung" text NULL);           -- eine Beschreibung wofuer der Typ ist

COMMENT ON TABLE konten_typen IS 'z.B. AKTIVA,AUWAND,EIGENKAPITAL etc.';

COMMENT ON COLUMN konten_typen.mask IS 'interne id';
COMMENT ON COLUMN konten_typen.name IS 'Bezeichnung des Kontentyps (AKTIVA)';
COMMENT ON COLUMN konten_typen.beschreibung IS 
    'eine Beschreibung wofuer der Typ ist';

create unique index "konten_typen_ukey1" on konten_typen (name);

select public.start_logging('konten_typen', 'ALL');

create table "used_konten" (
    "kto_nr" int4 NOT NULL PRIMARY KEY, -- interne id
    "used" int4 NOT NULL,               -- von wievielen genutzt
    "konto_typ" bit varying NOT NULL,   -- kontentyp
    CONSTRAINT fk_used_konten1 
        FOREIGN KEY (konto_typ) REFERENCES konten_typen (mask)
            ON DELETE CASCADE
            ON UPDATE CASCADE);

COMMENT ON TABLE used_konten IS '
Tabelle enthaelt alle zur verfuegung stehenden Kontonummern mit einem
Flag das anzeigt wie oft auf das Konto referenziert wird.';

COMMENT ON COLUMN used_konten.kto_nr IS 'interne id';
COMMENT ON COLUMN used_konten.used IS 'von wievielen genutzt';
COMMENT ON COLUMN used_konten.konto_typ IS 'FOREIGN KEY: kontentyp';

-- hier schenk ich mir das beim insert zugunsten einer besseren
-- laufzeit....spaeter bau cih moeglichkeiten ein, das logging seperat
-- fuer jedem table an und abzustellen und zwar separat fuer INSERT,UPDATE
-- und DELETE...
select public.start_logging('used_konten', 'UDPATE');
select public.start_logging('used_konten', 'DELETE');

create table "kontenplan" (
    "_id_" SERIAL NOT NULL PRIMARY KEY, -- interne id
    "konto_typ" bit varying NOT NULL,   -- Bestands-, Erfolgs- etc
    "name" text NOT NULL,               -- Bezeichnung des Kontenbereichs
    "nr_von" int4 NOT NULL,             -- Startwert der Kontonummern
    "nr_bis" int4 NOT NULL,             -- Wert von letzter gueltiger Nummer
    CONSTRAINT fk_kontenplan1
        FOREIGN KEY (konto_typ) REFERENCES konten_typen (mask)
            ON DELETE CASCADE
            ON UPDATE CASCADE);

COMMENT ON TABLE kontenplan IS 'Kontonummernbereiche zu Kontotypen:
------------------------------------
Hier gibt es ein wenig zu beachten damit die Tabelle konsistent bleibt
a) zu jedem Kontentyp darf nur ein Eintrag existieren.
   (Das scheint mir schwachsinnig zu sein.....konto_typ,name muss unique
    sein.)
b) Die Nummernbereiche duerfen sich nicht ueberschneiden
c) Zu jedem neuen Eintrag in kontenplan muessen entsprechende Eintraege
   in used_konten gemacht werden. Und schliesslich
d) wird ein Eintrag in kontenplan erfolgreich geaender (d.h. es gibt
   (keine Ueberschneidungen mit anderen Eintraegen, dann muessen die
   entsprechenden Eintraege in used Konten angepasst werde, der Status darf
   sich dort nicht aendern, wenn im neuen Bereich weniger Nummern existieren
   muessen ueberzaehlige Eintraege entfernt werden (ACHTUNG: hierbei kann
   es dann dazu kommen das auch Eintraege in Tabellen die Kontonummern
   als Fremdschluessel enthalten geloescht werden.) oder wenn der neue 
   Bereich mehr Kontonummern enthaelt weitere Eintraege in used_konten 
   eingefuegt werden.
e) wird ein Eintrag geloescht so muessen auch alle dazu gehoerenden Eintraege
   in used_konten geloescht werden (ACHTUNG: hierbei kann es dazu kommen,
   dass auch Eintraege in Tabellen die Kontonummern als Fremdschluessel
   enthalten geloescht werden. Dies betrifft insbesondete Debitoren,
   Kreditoren und Warengruppen.)';

COMMENT ON COLUMN kontenplan._id_ IS 'interne id';
COMMENT ON COLUMN kontenplan.konto_typ IS 
    'FOREIGN KEY: Bestands-, Erfolgs- etc';
COMMENT ON COLUMN kontenplan.name IS 'Bezeichnung des Kontenbereichs';
COMMENT ON COLUMN kontenplan.nr_von IS 'Startwert der Kontonummern';
COMMENT ON COLUMN kontenplan.nr_bis IS 'Wert von letzter gueltiger Nummer';

-- zu kontenplan a)
create unique index "kontenplan_ukey1" on kontenplan (konto_typ, name);

COMMENT ON INDEX kontenplan_ukey1 IS 'siehe COMMENT zu TABLE koentenplan a)';

select public.start_logging('kontenplan', 'ALL');

create table "konto" (
    "kto_nr" int4 NOT NULL PRIMARY KEY,   -- Kontonummer, intern
    "name" text NULL,                     -- Kontobezeichnung (Kasse etc.)
    "konto_typ" bit varying NOT NULL,     -- Typ (AKTIVA, DEBITOR...)
    "saldo_soll" numeric(10,2) DEFAULT 0.0 NOT NULL,  -- Saldo im soll
    "saldo_haben" numeric(10,2) DEFAULT 0.0 NOT NULL, -- Saldo im haben
    "summe" numeric(10,2) DEFAULT 0.0 NOT NULL,       -- Gesamtsumme des Kontos
    "blz" varchar(8) NULL,                -- Bankleitzahl (falls vorhanden)
    "b_kto_nr" text NULL,                 -- Bank-Kontonummer (falls vorhanden)
    "b_name" text NULL,                   -- Name der Bank
    CONSTRAINT fk_konto1
        FOREIGN KEY (konto_typ) REFERENCES konten_typen (mask)
            ON DELETE CASCADE
            ON UPDATE CASCADE);

COMMENT ON TABLE konto IS 'Repraesentation eines Kontos fuer die Buchfuehrung';

COMMENT ON COLUMN konto.kto_nr IS 'Kontonummer, intern';
COMMENT ON COLUMN konto.name IS 'Kontobezeichnung (Kasse etc.)';
COMMENT ON COLUMN konto.konto_typ IS 'FOREIGN KEY: Typ (AKTIVA, DEBITOR...)';
COMMENT ON COLUMN konto.saldo_soll IS 'Saldo im soll';
COMMENT ON COLUMN konto.saldo_haben IS 'Saldo im haben';
COMMENT ON COLUMN konto.summe IS 'Gesamtsumme des Kontos';
COMMENT ON COLUMN konto.blz IS 'Bankleitzahl (falls vorhanden)';
COMMENT ON COLUMN konto.b_kto_nr IS 'Bank-Kontonummer (falls vorhanden)';
COMMENT ON COLUMN konto.b_name IS 'Name der Bank';

select public.start_logging('konto', 'ALL');

create table "buchung" (
    "kto_soll" int4 NOT NULL,           -- Buchung von Konto
    "kto_haben" int4 NOT NULL,          -- nach Konto
    "be_nr" varchar(10) NOT NULL,       -- Belegnummer
    "bu_date" date NOT NULL,            -- Buchungsdatum
    "buchungstext" text NULL,           -- Zusatzinformationen
    "betrag" numeric(10,2) NOT NULL,    -- Betrag der Buchung
    PRIMARY KEY (be_nr, bu_date),
    CONSTRAINT fk_buchung1 FOREIGN KEY (kto_soll) REFERENCES konto (kto_nr)
        ON UPDATE CASCADE
        ON DELETE CASCADE,
    CONSTRAINT fk_buchung2 FOREIGN KEY (kto_haben) REFERENCES konto (kto_nr)
        ON UPDATE CASCADE
        ON DELETE CASCADE);

COMMENT ON TABLE buchung IS 'alle Buchungsaetze zwischen Konten:
------------------------------------
mehrteilige Buchungen werden als mehrere Buchungen zur gleichen Belegnummer 
und dem gleichen Datum erfasst. Es soll auch moeglich sein einen Beleg an
mehreren Tagen zu Buchen. Daher die wahl des Primaerschluessels als
kombination von kto_soll, kto_haben, be_nr, bu_date.';

COMMENT ON COLUMN buchung.kto_soll IS 'FOREIGN KEY: Buchung von Konto';
COMMENT ON COLUMN buchung.kto_haben IS 'FOREIGN KEY: nach Konto';
COMMENT ON COLUMN buchung.be_nr IS 'Belegnummer';
COMMENT ON COLUMN buchung.bu_date IS 'Buchungsdatum';
COMMENT ON COLUMN buchung.buchungstext IS 'Zusatzinformationen';
COMMENT ON COLUMN buchung.betrag IS 'Betrag der Buchung';

-- alle Funktionen zu Konten
\i plpgsql_funcs/cr_func_konto.sql

select public.start_logging('buchung', 'ALL');

-- Rules um die Bitmasken einheitlich zu halten.
-- Dafuer hatte ich vorher einen Trigger, aber ich denke das ist sinnvoller
-- in Rules zu implementiern.
-- Das kann ich leider nicht machen, fuer inserts funktioniert das noch,
-- aber leider muessten updates-rules nach den rewrite entweder eine andere
-- action oder eine andere Zielrelation haben, beides kann ich nicht machne,
-- da ich nur den Wert veraendern will.
-- Daher bleibt mir nichts anderes uebrig als trigger zu nehmen.

set search_path to trigger_func;

\i trigger_funcs/cr_trigger_func_konto.sql

-- Trigger
-- Dieser Trigger hat bevor ich obige Rules hatte die Bitmasken in ordnung
-- gehalten, jetzt wird er nicht mehr gebraucht!
create trigger valid_mask_trigger_before
    BEFORE INSERT OR UPDATE ON fibu.konten_typen FOR EACH ROW
    EXECUTE PROCEDURE create_valid_mask_before('fibu.konten_typen');
create trigger valid_mask_trigger_after
    AFTER INSERT OR UPDATE ON fibu.konten_typen FOR EACH ROW
    EXECUTE PROCEDURE create_valid_mask_after('fibu.konten_typen');
COMMENT ON TRIGGER valid_mask_trigger_before ON fibu.konten_typen IS
'wird ein neuer Kontentyp eingefuegt oder aber ein Kontentyp geaendert '
'so muss sicher gestellt sein, das alle Typen die gleiche laenge haben, '
'da sich sonst keine logischen Operationen mit ihnen ausfuehren lassen.';
COMMENT ON TRIGGER valid_mask_trigger_after ON fibu.konten_typen IS
'wird ein neuer Kontentyp eingefuegt oder aber ein Kontentyp geaendert '
'so muss sicher gestellt sein, das alle Typen die gleiche laenge haben, '
'da sich sonst keine logischen Operationen mit ihnen ausfuehren lassen.';

create trigger fibu_trigger1_on_ref_count_null_del_konto
    AFTER UPDATE ON fibu.used_konten FOR EACH ROW
    EXECUTE PROCEDURE on_ref_count_null_del_konto();
COMMENT ON TRIGGER fibu_trigger1_on_ref_count_null_del_konto 
    ON fibu.used_konten IS
'Wenn niemand mehr auf das konto referenziert kann der entsprechende
Eintrag in der Tabelle konto geloescht werden.';

-- zu kontenplan b)
create trigger fibu_trigger1_on_change_check_ranges
    BEFORE INSERT OR UPDATE ON fibu.kontenplan FOR EACH ROW 
    EXECUTE PROCEDURE on_change_kontenplan_check_ranges();
COMMENT ON TRIGGER fibu_trigger1_on_change_check_ranges 
    ON fibu.kontenplan IS 'siehe COMMENT zu TABLE koentenplan b)';

-- zu kontenplan c)
create trigger fibu_trigger2_on_insert_create_used_konten
    AFTER INSERT ON fibu.kontenplan FOR EACH ROW
    EXECUTE PROCEDURE on_insert_kontenplan_create_used_konten();
COMMENT ON TRIGGER fibu_trigger2_on_insert_create_used_konten 
    ON fibu.kontenplan IS 'siehe COMMENT zu TABLE koentenplan c)';

-- zu kontenplan d)
create trigger fibu_trigger3_on_update_update_used_konten
    AFTER UPDATE ON fibu.kontenplan FOR EACH ROW
    EXECUTE PROCEDURE on_update_kontenplan_upd_used_konten();
COMMENT ON TRIGGER fibu_trigger3_on_update_update_used_konten 
    ON fibu.kontenplan IS 'siehe COMMENT zu TABLE koentenplan c)';

-- zu kontenplan e)
create trigger fibu_trigger4_on_delete_del_used_konten
    AFTER DELETE ON fibu.kontenplan FOR EACH ROW
    EXECUTE PROCEDURE on_delete_kontenplan_del_used_konten();
COMMENT ON TRIGGER fibu_trigger4_on_delete_del_used_konten 
    ON fibu.kontenplan IS 'siehe COMMENT zu TABLE koentenplan e)';
-- Trigger ende

set search_path to fibu;

insert into konten_typen (mask, name) values (B'1','BESTAND');
insert into konten_typen (mask, name) values (konto_typ('BESTAND',2)|
                                              B'10', 'AKTIVA');
insert into konten_typen (mask, name) values (konto_typ('AKTIVA',3)|
                                              B'100', 'ANLAGE');
insert into konten_typen (mask, name) values (konto_typ('AKTIVA',4)|
                                              B'1000', 'UMLAUF');
insert into konten_typen (mask, name) values (konto_typ('UMLAUF',5)|
                                              B'10000','DEBITOR');
insert into konten_typen (mask, name) values (konto_typ('BESTAND',6)|
                                              B'100000','PASSIVA');
insert into konten_typen (mask, name) values (konto_typ('PASSIVA',7)|
                                              B'1000000','EIGEN');
insert into konten_typen (mask, name) values (konto_typ('PASSIVA',8)|
                                              B'10000000','FREMD');
insert into konten_typen (mask, name) values (konto_typ('FREMD',9)|
                                              B'100000000','KREDITOR');
insert into konten_typen (mask, name) values (B'1000000000','ERFOLG');
insert into konten_typen (mask, name) values (konto_typ('ERFOLG',11)|
                                              B'10000000000','ERTRAG');
insert into konten_typen (mask, name) values (konto_typ('ERFOLG',12)|
                                              B'100000000000','AUFWAND');

insert into kontenplan (konto_typ, name, nr_von, nr_bis) values
    (konto_typ('ANLAGE',konto_typ_min_len()), 'Anlagevermögen', 100, 109);

insert into kontenplan (konto_typ, name, nr_von, nr_bis) values
    (konto_typ('UMLAUF',konto_typ_min_len()), 
     'Umlaufvermögen ohne Forderungen LL', 110, 119);

insert into kontenplan (konto_typ, name, nr_von, nr_bis) values
    (konto_typ('DEBITOR',konto_typ_min_len()), 'Forderungen LL', 1000, 1999);

insert into kontenplan (konto_typ, name, nr_von, nr_bis) values
    (konto_typ('EIGEN',konto_typ_min_len()), 'Eigenkapital', 200, 209);

insert into kontenplan (konto_typ, name, nr_von, nr_bis) values
    (konto_typ('FREMD',konto_typ_min_len()), 
     'Fremdkapital ohne Verbindlichkeiten LL', 210, 219);

insert into kontenplan (konto_typ, name, nr_von, nr_bis) values
    (konto_typ('KREDITOR',konto_typ_min_len()), 
     'Verbindlichkeiten LL', 2000, 2999);

insert into kontenplan (konto_typ, name, nr_von, nr_bis) values
    (konto_typ('ERTRAG',konto_typ_min_len()), 'Erträge', 3000, 3999);

insert into kontenplan (konto_typ, name, nr_von, nr_bis) values
    (konto_typ('AUFWAND',konto_typ_min_len()), 'Aufwendungen', 4000, 4999);

select inc_konto_ref_count(
        ins_konto(NULL, 'Hardware', konto_typ('ANLAGE',konto_typ_min_len()), 
                  NULL, NULL, NULL));
select inc_konto_ref_count(
        ins_konto(NULL, 'Software', konto_typ('ANLAGE',konto_typ_min_len()), 
                  NULL, NULL, NULL));
select inc_konto_ref_count(
        ins_konto(NULL, 'Kasse', konto_typ('UMLAUF',konto_typ_min_len()), 
                  NULL, NULL, NULL));
select inc_konto_ref_count(
        ins_konto(NULL, 'Bankkonto', konto_typ('UMLAUF',konto_typ_min_len()), 
                  '20110022', '3 003 938 178', 'Postbank (Spar) Hamburg'));
select inc_konto_ref_count(
        ins_konto(NULL, 'Forderungen sonstige', 
                  konto_typ('UMLAUF',konto_typ_min_len()),
                  NULL, NULL, NULL));
select inc_konto_ref_count(
        ins_konto(NULL, 'Eigenkapital', konto_typ('EIGEN',konto_typ_min_len()),
                  NULL, NULL, NULL));
select inc_konto_ref_count(
        ins_konto(NULL, 'Privatkonto', konto_typ('EIGEN',konto_typ_min_len()),
                  NULL, NULL, NULL));
select inc_konto_ref_count(
        ins_konto(NULL, 'Kredite', konto_typ('FREMD',konto_typ_min_len()),
                  NULL, NULL, NULL));
select inc_konto_ref_count(
        ins_konto(NULL, 'Verbindlichkeiten wiedekehrend',
                  konto_typ('FREMD',konto_typ_min_len()), 
                  NULL, NULL, NULL));
select inc_konto_ref_count(
        ins_konto(NULL, 'Verbindlichkeiten sonstige',
                  konto_typ('FREMD',konto_typ_min_len()), 
                  NULL, NULL, NULL));

set search_path to public;
-- ]
-- ende Konto --

-- Benutzerdaten
-- [
set search_path to benutzer;

create table benutzer (
    "_id_" SERIAL NOT NULL PRIMARY KEY, -- interne id
    "user_name" text NOT NULL,          -- Shop- und DB-Benutzername
    -- Passwort brauchen wir nicht, wir werden ueber die DB authentifiziert.
    --"password" text NOT NULL,           -- Shop- und DB-Passwort
    "id_person" int4 NULL,              -- falls Person, sonst NULL
    "id_firma" int4 NULL,               -- falls Firma, sonst NULL
    "u_typ" bit(3) NOT NULL DEFAULT B'001',-- 001-Kunde, 010-Anbieter, 100-Admin
    CONSTRAINT fk_benutzer1 
        FOREIGN KEY (id_person) REFERENCES person.person (_id_)
            ON DELETE CASCADE
            ON UPDATE CASCADE,
    CONSTRAINT fk_benutzer2 
        FOREIGN KEY (id_firma) REFERENCES firma.firma (_id_)
            ON DELETE CASCADE
            ON UPDATE CASCADE);

COMMENT ON TABLE benutzer IS '<U>username, passwort usw.:</U><BR>'
    'bei id_kunde, id_admin, id_lieferant gilt dasselbe wie schon bei'
    'den Kunden oder den Anbietern in etwas abgewandelter form. <BR>'
    'Hier muss mindestens eines der Attribute NOT NULL sei, aber es duerfen '
    'auch alle not NULL sein.<BR>'
    'Zur Erinnerung, bei Kunden durfte immer nur entweder id_person oder '
    'id_firma einen Wert haben, das jeweils andere Attribut musste NULL sein.';

COMMENT ON COLUMN benutzer._id_ IS 'interne id';
COMMENT ON COLUMN benutzer.user_name IS 'Shop- und DB-Benutzername';
--COMMENT ON COLUMN benutzer.password IS 'Shop- und DB-Passwort';
COMMENT ON COLUMN benutzer.id_person IS 'FOREIGN KEY: falls Person, sonst NULL';
COMMENT ON COLUMN benutzer.id_firma IS 'FOREIGN KEY: falls Firma, sonst NULL';
COMMENT ON COLUMN benutzer.u_typ IS '001-Kunde, 010-Anbieter, 100-Admin';

select public.start_logging('benutzer', 'ALL');

set search_path to trigger_func,benutzer;
\i trigger_funcs/cr_trigger_func_benutzer.sql

CREATE trigger benutzer_trigger1_on_change_one_null
    BEFORE INSERT OR UPDATE ON benutzer.benutzer FOR EACH ROW 
    EXECUTE PROCEDURE on_change_one_null();
COMMENT ON TRIGGER benutzer_trigger1_on_change_one_null ON 
    benutzer.benutzer IS 'stellt sicher das immer nur entweder eine Firma '
                         'oder eine Person der Benutzer ist.';

-- OK, man koennte fragen warum eine Firma ein Benutzer sein sollte, aber
-- so kann ich zwischen Privat und fuer Firmen genutzten Benutzeraccounts
-- unterscheiden.

set search_path to benutzer;

create table rechte (
    "mask" bit varying NOT NULL PRIMARY KEY,   -- rechte-Maske
    "name" text NOT NULL,                       -- Rechte-Name
    "beschreibung" text NULL);                  -- Beschreibung

COMMENT ON TABLE rechte IS 
    'Alle Rechte die ein Admin haben kann mit den entsprechenden Masken\n'
    'Hier werden nur grundlegende Rechtegebiete gespeichert. Diese sind\n'
    'zur Zeit:<BR>\n'
    '<UL>\n'
    '<LI><B>benutzer</B> = 1 <I>Zugang zur Benutzerverwaltung</I></LI>\n'
    '<LI><B>person</B> = 10 <I>Zugang zur Personendaten</I></LI>\n'
    '<LI><B>firma</B> = 100 <I>Zugang zur Firmendaten</I></LI>\n'
    '<LI><B>fibu</B> = 1000 <I>Zugang zur Shop-Fibu</I></LI>\n'
    '<LI><B>warengruppe</B> = 10000 <I>Zugang zu WGs aller Anbieter</I></LI>\n'
    '<LI><B>produkte</B> = 100000 <I>Zugang zu Prod aller Anbieter</I></LI>\n'
    '<LI><B>rechte</B> = 1000000 <I>Zugang zu Rechtesystem</I></LI>\n'
    '</UL>\n'
    '<BR>Um zu sehen welche Rechte ein admin in einem bestimmten Bereich hat\n'
    'gibt es die Tabelle effektive_rechte. (siehe Beschreibung dort)<BR>\n'
    'Gehoeren mehrere Tabellen zu den Gebiete, so hat der Admin die gesetzten\n'
    'Rechte an allen Tabellen dieses Gebites.<BR>\n'
    'Die im Moment eingetragenen Rechte betreffen hauptsaechlich\n'
    'Datenbankzugriffe, man koennte sich vorstellen weiter Rechte wie z.B.\n'
    'Newsletteradmin einzutragen. Bei diesen wuerde dann entweder gar kein\n'
    'Eintrag in effektive Rechte noetig sein, oder aber nur das feld spicial\n'
    'ist wichtig.';

COMMENT ON COLUMN rechte.mask IS 'rechte-Maske';
COMMENT ON COLUMN rechte.name IS 'Rechte-Name';
COMMENT ON COLUMN rechte.beschreibung IS 'Beschreibung';

select public.start_logging('rechte', 'ALL');

set search_path to trigger_func,benutzer;
\i trigger_funcs/cr_trigger_func_rechte.sql

-- Hier muss genau wie bei den Kontentypen darauf geachtet werden das die
-- Bitmasken immer eine einheitlich laenge haben.
create trigger valid_mask_trigger_before
    BEFORE INSERT OR UPDATE ON benutzer.rechte FOR EACH ROW
    EXECUTE PROCEDURE create_valid_mask_before('benutzer.rechte');
create trigger valid_mask_trigger_after
    AFTER INSERT OR UPDATE ON benutzer.rechte FOR EACH ROW
    EXECUTE PROCEDURE create_valid_mask_after('benutzer.rechte');

set search_path to benutzer;

insert into rechte (mask, name, beschreibung) values 
    (B'1', 'user', 'Zugang zur Benutzerverwaltung');
insert into rechte (mask, name, beschreibung) values 
    (B'10', 'person', 'Zugang zur Personendaten');
insert into rechte (mask, name, beschreibung) values 
    (B'100', 'firma' , 'Zugang zur Firmendaten');
insert into rechte (mask, name, beschreibung) values 
    (B'1000', 'fibu' , 'Zugang zur Shop-Fibu');
insert into rechte (mask, name, beschreibung) values 
    (B'10000', 'warengruppe', 'Zugang zu WGs aller Anbieter');
insert into rechte (mask, name, beschreibung) values 
    (B'100000', 'produkt', 'Zugang zu Prod aller Anbieter');
insert into rechte (mask, name, beschreibung) values 
    (B'1000000', 'rules', 'Zugang zu Rechtesystem');

--CREATE trigger rechte_trigger2_on_chg_or_del_clean_effektive_rechte
--    BEFORE UPDATE OR DELETE ON benutzer.rechte FOR EACH ROW 
--    EXECUTE PROCEDURE clean_effektive_rechte();

-- Stattdessen bietet sich eine rule an, da ich nicht viel Logic brauche
-- sondern einfach nur nen update auf effektive rechte mache. Etwa so...
-- erstmal ne Rule um die Breite der mask konsistent zu halten....
--CREATE RULE clean_effektive_rechte_1 AS
--    ON UPDATE OR DELETE TO rechte
--    DO ( UPDATE effektive_rechte SET

create table admin (
    "id_benutzer" int4 NOT NULL PRIMARY KEY,    -- Benutzer des Admin
    "rechte" bit varying DEFAULT B'0' NOT NULL, -- kombination von RechteMasken
    CONSTRAINT fk_admin1 
        FOREIGN KEY (id_benutzer) REFERENCES benutzer (_id_)
            ON DELETE CASCADE
            ON UPDATE CASCADE);

COMMENT ON TABLE admin IS 
'<U>Hier werden alle Benutzer die Admins sind mit den passenden Attributen '
' gespeichert:</U><BR><BR>'
'zwar ist rechte kein FOREIGN KEY auf rechte, aber trotzdem muss'
'ueber einen Trigger sichergestellt werden das wenn Rechte geloescht'
'oder geandert werden die Masken hier angepasst werden.'
'Beim loeschen ist das recht einfach, man loescht einfach bei allen'
'Admins das entsprechende bit in ihrer Maske, schwere ist das wenn'
'ein Recht in rechte geaendert wird, dann habe ich quasi keine chance'
'noch zu checke ob Admins die das entsprechende Bit gesetzt haben immer'
'noch die passenden rechte haben. Die Loesung hier ist ueber einen'
'Trigger alle UPDATES auf Rechte abzuschmettern.';

COMMENT ON COLUMN admin.rechte IS 'kombination von RechteMasken';
COMMENT ON COLUMN admin.id_benutzer IS 'FOREIGN KEY: Benutzerdaten des Admin';

select public.start_logging('admin', 'ALL');

create table effektive_rechte (
    id_admin int4 NOT NULL,     -- Id des Admin zu dem die Rechte gehoeren.
    mask bit varying NOT NULL, -- Maske ueber die Rechte fuer die diese
                                -- Enstellung gelten soll, diese darf sich
                                -- nicht mit anderen recht_masken zu einer
                                -- id_admin ueberschneiden, (Trigger).
                                -- ausserdem muss zu allen Bits dieser Maske
                                -- auch ein Eintrag in rechte vorhanden sein,
                                -- (ein weitere Trigger, oder sogar mehrere,
                                --  einer beim update, einfuegen hier und einer
                                --  beim update, loeschen in rechte)
    r_add boolean NOT NULL DEFAULT 'f', -- eintraege hinzufuegen
    r_delete boolean NOT NULL DEFAULT 'f', -- eintraege loeschen
    r_update boolean NOT NULL DEFAULT 'f', -- eintraege aktualisieren
    special bit varying NULL,   -- spezielle Rechte
    PRIMARY KEY (id_admin, mask),
    CONSTRAINT fk_effektive_rechte
        FOREIGN KEY (id_admin) REFERENCES admin (id_benutzer)
            ON DELETE CASCADE
            ON UPDATE CASCADE);

COMMENT ON TABLE effektive_rechte IS
    'Diese Tablle dient dazu genau zu beschreiben was ein admin in einem\n'
    'der Gebiete (aus rechte) fuer die er Eingetragen ist fuer Rechte hat.\n'
    'insbesondere, ob er adden, deleten und/oder updaten darf.<BR>\n'
    'Die hier eingetragene recht_maske kann mehrere Rechte umfassen (im\n'
    'folgenden Text Rechtegruppe genannt), dann gelten die Einstellungen\n'
    'fuer alle Rechte dieser Rechtegruppe. Wird die Einstellung fuer ein\n'
    'Recht oder eine Rechtegruppe nachtraeglich geaendert, so wird zuerst\n'
    'nachgesehen ob bereits Eintraege zu diesen Rechten vorhanden sind.\n'
    'Diese werden aus den Rechtegruppen in denen sie waren ausgekoppelt und\n'
    'als Einzeleintraege neu eingetragen. Dabei werden dann auch gleich die\n'
    'zu aendernden Attribute neu gesetzt.<BR>\n'
    'Das Feld special dient zum speichern etwaiger besonderer Rechte, z.B.\n'
    'Wenn eine Tabelle des Gebiets nur von bestimmten Admins geandert werden\n'
    'darf kann hier nochmal ein Bit gesetzt werden das eine solche\n'
    'Unterscheidung moeglich macht.';

COMMENT ON COLUMN effektive_rechte.id_admin IS 
    'Id des Admin zu dem die Rechte gehoeren.';
COMMENT ON COLUMN effektive_rechte.mask IS 
    'Maske ueber die Rechte fuer die diese Enstellung gelten soll, diese\n'
    'darf sich nicht mit anderen recht_masken zu einer id_admin\n'
    'ueberschneiden, (Trigger).<BR>\n'
    'ausserdem muss zu allen Bits dieser Maske auch ein Eintrag in rechte\n'
    'vorhanden sein, (ein weitere Trigger, oder sogar mehrere, einer beim\n'
    'update, einfuegen hier und einer beim update, loeschen in rechte)';
COMMENT ON COLUMN effektive_rechte.r_add IS 'eintraege hinzufuegen';
COMMENT ON COLUMN effektive_rechte.r_delete IS 'eintraege loeschen';
COMMENT ON COLUMN effektive_rechte.r_update IS 'eintraege aktualisieren';
COMMENT ON COLUMN effektive_rechte.special IS 'spezielle Rechte';

select public.start_logging('effektive_rechte', 'ALL');

-- hier fehlt noch fast alles...is nur schon mal da das ichs nich vergesse :-)
-- is die frage ob ich das ueberhaupt brauch wenn ich das session management
-- von JSP/struts oder PHP nutze....sprich das SessionManagement programmatisch
-- umsetze...
create table session (
    sess_id text NOT NULL PRIMARY KEY);

COMMENT ON TABLE session IS 
    'Hier werden alle Information zu Sitzungen am Shop gespeichert...'
    'es fehlt aber quasi noch alles!';

create table "kunde" (
    "_id_" SERIAL NOT NULL PRIMARY KEY, -- interne id
    "kd_nr" text NOT NULL,              -- Kundennummer
    "konto1" int4 NOT NULL,             -- Debitor-Konto (Rechnung)
    "konto2" int4 NOT NULL,             -- Kreditor-Konto (Vorkasse)
    "id_benutzer" int4 NOT NULL,        -- Benutzerdaten des Kunden
    CONSTRAINT fk_kunde1 
        FOREIGN KEY (konto1) REFERENCES fibu.used_konten (kto_nr)
            ON DELETE CASCADE
            ON UPDATE CASCADE,
    CONSTRAINT fk_kunde2 
        FOREIGN KEY (konto2) REFERENCES fibu.used_konten (kto_nr)
            ON DELETE CASCADE
            ON UPDATE CASCADE,
    CONSTRAINT fk_kunde3 
        FOREIGN KEY (id_benutzer) REFERENCES benutzer.benutzer (_id_)
            ON DELETE CASCADE
            ON UPDATE CASCADE);

COMMENT ON TABLE kunde IS 'Kundeninformationen:
----------------------------
Hier gibt es wieder was zu beachten und zu automatisieren.
a) beim einfuegen eines kunden muss, 
   a.a) wenn kontonummern mit angegeben werden, geprueft werden ob diese
        wirklich den richtigen Typ haben, ob sie ueberhaupt schon in Konto
        engetragen sind, wenn nicht muß dies passieren vorausgesetzt der Typ
        stimmt, und in used_konten muss used auf true gesetzt werden, dies
        kann ruhig auch gemacht werden wenn die Konten bereits genutzt 
        wurden.
   a.b) wenn keine kontonummern mit angegeben werden muessen automatisch
        passende Konten angelegt werden und die Nummern eingesetzt werden.
        Auch hier muss natuerlich used_konten angepasst werden.
b) beim aendern eines kunden muss,
   b.a) wenn kontonummern mit angegeben werden, muessen diese wie beim
        einfuegen kontrolliert werden bevor sie eingefuegt werden.
        ausserdem muß der ref_count bei den alten konten runter und bei
        den neuen rauf gesetzt werden. (Das laeuft darauf hinaus das ich
        einmal ins_konto mache, das gibt mir die Kontonummer zurueck wenn
        das Konto schon existierte oder wenn es angelegt wurde und ich
        mich dann um die ref_counts kuemmere.)
   b.b) wenn keine kontonummern mit angegeben werden, die alten beibehalten,
        also in der Triggerfunc wieder auf die neuen uebertragen werden.
c) beim loeschen eines kunden, sofern keine anderer Kunde oder Anbieter
   mehr das Konto nutzt kann es geloescht werden und used in used_konten
   wieder auf false gesetzt werden. (OK, hier kommt gleich eine Schwaeche
   des aktuellen System zum vorschein, naemlich das man nicht leicht
   feststellen kann ob das Konto noch genutzt wird....abhilfe schaffe ich
   indem ich used in used_konten von boolean auf int4 umstelle und definiere
   0 ist ungenutzt und sobald einer das konto nutzt wird used um 1 
   hochgezaehlt, nutzt einer es nicht mehr wird used um 1 runtergezaehlt.
   (ist gemacht, Kommentar loeschen sobald es getestet ist!)';

COMMENT ON COLUMN kunde._id_ IS 'interne id';
COMMENT ON COLUMN kunde.kd_nr IS 'Kundennummer';
COMMENT ON COLUMN kunde.konto1 IS 'FOREIGN KEY: Debitor-Konto (Rechnung)';
COMMENT ON COLUMN kunde.konto2 IS 'FOREIGN KEY: Kreditor-Konto (Vorkasse)';
COMMENT ON COLUMN kunde.id_benutzer IS 'FOREIGN KEY: Benutzerdaten';

COMMENT ON CONSTRAINT fk_kunde1 ON kunde IS
'wird ein Konto aus used_konten geloescht oder dessen _id_ veraendert,
so soll eine entsprechende anpassung hier stattfinden, das gilt auch
fuer alle weiteren Tabellen die Konten nutzen.
ob es sinnvoll ist einen Kunden zu loeschen wenn ein entsprechnder
Eintrag in used_konten geloescht wird muss nochmal ueberdacht werden.';
COMMENT ON CONSTRAINT fk_kunde2 ON kunde IS
'wird ein Konto aus used_konten geloescht oder dessen _id_ veraendert,
so soll eine entsprechende anpassung hier stattfinden, das gilt auch
fuer alle weiteren Tabellen die Konten nutzen.
ob es sinnvoll ist einen Kunden zu loeschen wenn ein entsprechnder
Eintrag in used_konten geloescht wird muss nochmal ueberdacht werden.';

create unique index "kunde_ukey" on kunde (kd_nr);

\i plpgsql_funcs/cr_func_kunde.sql

select public.start_logging('kunde', 'ALL');

set search_path to trigger_func,benutzer;

-- Trigger
CREATE trigger kunde_trigger2_on_insert_create_konten
    BEFORE INSERT ON benutzer.kunde FOR EACH ROW 
    EXECUTE PROCEDURE on_insert_create_konten('DEBITOR','KREDITOR');
COMMENT ON TRIGGER kunde_trigger2_on_insert_create_konten 
    ON benutzer.kunde IS 'siehe COMMENT zu TABLE kunde a)';

CREATE trigger kunde_trigger3_on_update_update_konten
    BEFORE UPDATE ON benutzer.kunde FOR EACH ROW 
    EXECUTE PROCEDURE on_update_update_konten('DEBITOR','KREDITOR');
COMMENT ON TRIGGER kunde_trigger3_on_update_update_konten
    ON benutzer.kunde IS 'siehe COMMENT zu TABLE kunde b)';

CREATE trigger kunde_trigger4_on_delete_dec_konto_ref_count
    AFTER DELETE ON benutzer.kunde FOR EACH ROW 
    EXECUTE PROCEDURE on_delete_dec_konto_ref_count();
COMMENT ON TRIGGER kunde_trigger4_on_delete_dec_konto_ref_count
    ON benutzer.kunde IS 'siehe COMMENT zu TABLE kunde c)';
-- Ende Trigger
    
set search_path to benutzer;

create table "anbieter" (
    "_id_" SERIAL NOT NULL PRIMARY KEY, -- interne id
    "an_nr" text NOT NULL,              -- Anbieternummer
    "konto1" int4 NOT NULL,             -- Debitor-Konto (Vorkasse)
    "konto2" int4 NOT NULL,             -- Kreditor-Konto (Rechnung)
    "id_benutzer" int4 NOT NULL,            -- Benutzerdaten oder NULL
    CONSTRAINT fk_anbieter1
        FOREIGN KEY (konto1) REFERENCES fibu.used_konten (kto_nr)
            ON DELETE CASCADE
            ON UPDATE CASCADE,
    CONSTRAINT fk_anbieter2
        FOREIGN KEY (konto2) REFERENCES fibu.used_konten (kto_nr)
            ON DELETE CASCADE
            ON UPDATE CASCADE,
    CONSTRAINT fk_anbieter3
        FOREIGN KEY (id_benutzer) REFERENCES benutzer.benutzer (_id_)
            ON DELETE CASCADE
            ON UPDATE CASCADE);

COMMENT ON TABLE anbieter IS
'Leute die Waren in meinem Shop anbieten:<BR>';

COMMENT ON COLUMN anbieter._id_ IS 'interne id';
COMMENT ON COLUMN anbieter.an_nr IS 'Anbieternummer';
COMMENT ON COLUMN anbieter.konto1 IS 'FOREIGN KEY: Debitor-Konto (Vorkasse)';
COMMENT ON COLUMN anbieter.konto2 IS 'FOREIGN KEY: Kreditor-Konto (Rechnung)';
COMMENT ON COLUMN anbieter.id_benutzer IS 'FOREIGN KEY: Benutzerdaten:';

COMMENT ON CONSTRAINT fk_anbieter1 ON anbieter IS 
    'siehe COMMENT zu CONSTRAINT fk_kunde1';
COMMENT ON CONSTRAINT fk_anbieter2 ON anbieter IS 
    'siehe COMMENT zu CONSTRAINT fk_kunde2';

create unique index "anbieter_ukey" on anbieter (an_nr);

select public.start_logging('anbieter', 'ALL');

set search_path to trigger_func,benutzer;

-- Trigger
CREATE trigger anbieter_trigger2_on_insert_create_konten
    BEFORE INSERT ON benutzer.anbieter FOR EACH ROW 
    EXECUTE PROCEDURE on_insert_create_konten('DEBITOR','KREDITOR');
COMMENT ON TRIGGER anbieter_trigger2_on_insert_create_konten 
    ON benutzer.anbieter IS 'siehe COMMENT zu TABLE kunde a)';

CREATE trigger anbieter_trigger3_on_update_update_konten
    BEFORE UPDATE ON benutzer.anbieter FOR EACH ROW 
    EXECUTE PROCEDURE on_update_update_konten('DEBITOR','KREDITOR');
COMMENT ON TRIGGER anbieter_trigger2_on_insert_create_konten 
    ON benutzer.anbieter IS 'siehe COMMENT zu TABLE kunde b)';

CREATE trigger anbieter_trigger4_on_delete_dec_konto_ref_count
    AFTER DELETE ON benutzer.anbieter FOR EACH ROW 
    EXECUTE PROCEDURE on_delete_dec_konto_ref_count();
COMMENT ON TRIGGER anbieter_trigger2_on_insert_create_konten 
    ON benutzer.anbieter IS 'siehe COMMENT zu TABLE kunde c)';
-- Ende Trigger
    
set search_path to public;
-- ]
-- ende Benutzerdaten

-- Verkaueferdaten, Leute bei denen ich einkaufe, gehoert zu fibu.
-- [
set search_path to fibu;

create table "verkaeufer" (
    "_id_" SERIAL NOT NULL PRIMARY KEY, -- interne id
    "vk_nr" text NOT NULL,              -- Anbieternummer
    "konto1" int4 NOT NULL,             -- Debitor-Konto (Vorkasse)
    "konto2" int4 NOT NULL,             -- Kreditor-Konto (Rechnung)
    "id_person" int4 NULL,              -- falls Person, sonst NULL
    "id_firma" int4 NULL,               -- falls Firma, sonst NULL
    CONSTRAINT fk_verkaeufer1
        FOREIGN KEY (konto1) REFERENCES fibu.used_konten (kto_nr)
            ON DELETE CASCADE
            ON UPDATE CASCADE,
    CONSTRAINT fk_verkaeufer2
        FOREIGN KEY (konto2) REFERENCES fibu.used_konten (kto_nr)
            ON DELETE CASCADE
            ON UPDATE CASCADE,
    CONSTRAINT fk_verkaeufer3 
        FOREIGN KEY (id_person) REFERENCES person.person (_id_)
            ON DELETE CASCADE
            ON UPDATE CASCADE,
    CONSTRAINT fk_verkaeufer4 
        FOREIGN KEY (id_firma) REFERENCES firma.firma (_id_)
            ON DELETE CASCADE
            ON UPDATE CASCADE);

COMMENT ON TABLE verkaeufer IS 'Leute oder Firmen bei denen ich einkaufe.';

COMMENT ON COLUMN verkaeufer._id_ IS 'interne id';
COMMENT ON COLUMN verkaeufer.vk_nr IS 'Verkaeufernummer';
COMMENT ON COLUMN verkaeufer.konto1 IS 'FOREIGN KEY: Debitor-Konto (Vorkasse)';
COMMENT ON COLUMN verkaeufer.konto2 IS 'FOREIGN KEY: Kreditor-Konto (Rechnung)';
COMMENT ON COLUMN verkaeufer.id_person IS 
    'FOREIGN KEY: falls Person, sonst NULL';
COMMENT ON COLUMN verkaeufer.id_firma IS 'FOREIGN KEY: falls Firma, sonst NULL';

COMMENT ON CONSTRAINT fk_verkaeufer1 ON verkaeufer IS 
    'siehe COMMENT zu CONSTRAINT fk_kunde1';
COMMENT ON CONSTRAINT fk_verkaeufer1 ON verkaeufer IS 
    'siehe COMMENT zu CONSTRAINT fk_kunde2';

create unique index "verkaufer_ukey" on verkaeufer (vk_nr);

select public.start_logging('verkaeufer', 'ALL');

set search_path to trigger_func,fibu;

-- Trigger
CREATE trigger verkaeufer_trigger1_on_change_one_null
    BEFORE INSERT OR UPDATE ON fibu.verkaeufer FOR EACH ROW 
    EXECUTE PROCEDURE on_change_one_null();
COMMENT ON TRIGGER verkaeufer_trigger1_on_change_one_null ON 
    fibu.verkaeufer IS 'stellt sicher das immer nur entweder eine Firma '
                       'oder eine Person der Varkaeufer ist.';

CREATE trigger verkaeufer_trigger2_on_insert_create_konten
    BEFORE INSERT ON fibu.verkaeufer FOR EACH ROW 
    EXECUTE PROCEDURE on_insert_create_konten('DEBITOR','KREDITOR');
COMMENT ON TRIGGER verkaeufer_trigger2_on_insert_create_konten 
    ON fibu.verkaeufer IS 'siehe COMMENT zu TABLE kunde a)';

CREATE trigger verkaeufer_trigger3_on_update_update_konten
    BEFORE UPDATE ON fibu.verkaeufer FOR EACH ROW 
    EXECUTE PROCEDURE on_update_update_konten('DEBITOR','KREDITOR');
COMMENT ON TRIGGER verkaeufer_trigger2_on_insert_create_konten 
    ON fibu.verkaeufer IS 'siehe COMMENT zu TABLE kunde b)';

CREATE trigger verkaeufer_trigger4_on_delete_dec_konto_ref_count
    AFTER DELETE ON fibu.verkaeufer FOR EACH ROW 
    EXECUTE PROCEDURE on_delete_dec_konto_ref_count();
COMMENT ON TRIGGER verkaeufer_trigger2_on_insert_create_konten 
    ON fibu.verkaeufer IS 'siehe COMMENT zu TABLE kunde c)';
-- Ende Trigger
    
set search_path to public;
-- ]
-- ende Benutzerdaten
-- Table zu Waren, Warengruppen u. Lager --
-- [
set search_path to waren;

create table warengruppe (
    "_id_" SERIAL NOT NULL PRIMARY KEY, -- interne id
    "name" text NOT NULL,               -- Bezeichnung der Warengruppe
    "steuerkl" int4 NOT NULL,           -- Steuern zu dieser Produktgruppe
    "kurzbeschreibung" text NULL,       -- Kurzbeschreibung
    "beschreibung" text NULL,           -- Beschreibung
    "produkt_typen" bit varying NOT NULL, -- Maske der anzuzeigenden Produkte.
    "pic_path" text NULL,               -- optionaler Pfad zu einem Bild
    "tiefe" int4 DEFAULT 0 NOT NULL,    -- die wievielte Untergruppe
    "parent" int4 NULL,                 -- uebergeordnete Warengruppe
    "is_parent" boolean DEFAULT false NOT NULL, -- ist WG ein Parent??
    "konto1" int4 NOT NULL,             -- Wareneingangskonto
    "konto2" int4 NOT NULL,             -- Warenausgangskonto
    -- produkt_typen ist kein FOREIGN KEY, da es sich hierbei um eine
    -- Kombination von mehreren Typen aus der Tabelle produkt_typen handeln
    -- kann.
    CONSTRAINT fk_warengruppe1
        FOREIGN KEY (konto1) REFERENCES fibu.konto (kto_nr)
            ON DELETE CASCADE
            ON UPDATE CASCADE,
    CONSTRAINT fk_warengruppe2
        FOREIGN KEY (konto2) REFERENCES fibu.konto (kto_nr)
            ON DELETE CASCADE
            ON UPDATE CASCADE,
    CONSTRAINT fk_warengruppe3
        FOREIGN KEY (parent) REFERENCES warengruppe (_id_)
            ON UPDATE CASCADE);

COMMENT ON TABLE warengruppe IS 
    '<U>Warengruppen unter denen Produkte oder weitere Warengruppen '
    'organisiert werden:</U><BR><BR>'
    'Enthaelt eine Warengruppe weitere Warengruppen, so sind die zu den '
    'Untergruppen gehoerenden Eingangs und Ausgangskonten auch Unterkonten '
    'zu den in der Obergruppe existierenden Konten, d.h. der Saldo der '
    'Unterkonten muss immer auf das Oberkonto uebertragen werden. '
    'Ich glaube dafuer sollte ich trigger bauen....<BR>'
    'Die Konten sind natuerlich wie bei kunden automatisch anzulegen.';

COMMENT ON COLUMN warengruppe._id_ IS 'interne id';
COMMENT ON COLUMN warengruppe.name IS 'Bezeichnung der Warengruppe';
COMMENT ON COLUMN warengruppe.steuerkl IS 'Steuern zu dieser Produktgruppe';
COMMENT ON COLUMN warengruppe.kurzbeschreibung IS 'Kurzbeschreibung';
COMMENT ON COLUMN warengruppe.beschreibung IS 'Beschreibung';
COMMENT ON COLUMN warengruppe.produkt_typen IS
    'Maske der anzuzeigenden Produkte.';
COMMENT ON COLUMN warengruppe.pic_path IS 'optionaler Pfad zu einem Bild';
COMMENT ON COLUMN warengruppe.tiefe IS 'die wievielte Untergruppe';
COMMENT ON COLUMN warengruppe.parent IS 'uebergeordnete Warengruppe';
COMMENT ON COLUMN warengruppe.is_parent IS 'ist WG ein Parent??';
COMMENT ON COLUMN warengruppe.konto1 IS 'Wareneingangskonto';
COMMENT ON COLUMN warengruppe.konto2 IS 'Warenausgangskonto';

create unique index "warengruppe_ukey1" on warengruppe(name);

\i plpgsql_funcs/cr_func_waren.sql

select public.start_logging('warengruppe', 'ALL');

set search_path to trigger_func,waren;
\i trigger_funcs/cr_trigger_func_waren.sql

-- Trigger 
create trigger waren_trigger1_on_insert_create_konten
    BEFORE INSERT ON waren.warengruppe FOR EACH ROW
    EXECUTE PROCEDURE on_insert_create_konten('AUFWAND','ERTRAG');
COMMENT ON TRIGGER waren_trigger1_on_insert_create_konten
    ON waren.warengruppe IS 'siehe COMMENT zu TABLE kunde c)';

CREATE trigger waren_trigger2_on_update_update_konten
    BEFORE UPDATE ON waren.warengruppe FOR EACH ROW 
    EXECUTE PROCEDURE on_update_update_konten('AUFWAND','ERTRAG');
COMMENT ON TRIGGER waren_trigger2_on_update_update_konten
    ON waren.warengruppe IS 'siehe COMMENT zu TABLE kunde c)';
    
CREATE trigger waren_trigger3_on_delete_dec_konto_ref_count
    AFTER DELETE ON waren.warengruppe FOR EACH ROW 
    EXECUTE PROCEDURE on_delete_dec_konto_ref_count();
COMMENT ON TRIGGER waren_trigger3_on_delete_dec_konto_ref_count
    ON waren.warengruppe IS 'siehe COMMENT zu TABLE kunde c)';


create trigger waren_trigger4_on_insert_set_tiefe
    BEFORE INSERT ON waren.warengruppe FOR EACH ROW
    EXECUTE PROCEDURE set_tiefe();
COMMENT ON TRIGGER waren_trigger4_on_insert_set_tiefe
    ON waren.warengruppe IS 'setze tiefe auf parent.tiefe+1 oder 0 '
                            'wenn es eine Rootgruppe ist';

create trigger waren_trigger5_on_update_check_tiefe
    BEFORE UPDATE ON waren.warengruppe FOR EACH ROW
    EXECUTE PROCEDURE on_update_check_tiefe();
COMMENT ON TRIGGER waren_trigger5_on_update_check_tiefe
    ON waren.warengruppe IS 'wenn sich parent aendert setze tiefe=neuer '
                            'parent.tiefe+1 oder 0 wenn der neue parent '
                            'NULL ist (Rootgruppe)';

create trigger waren_trigger6_on_delete_new_parent
    BEFORE DELETE ON waren.warengruppe FOR EACH ROW
    EXECUTE PROCEDURE on_delete_new_parent();
COMMENT ON TRIGGER waren_trigger6_on_delete_new_parent
    ON waren.warengruppe IS 'wenn eine Warengruppe geloescht wird muss parent '
                            'bei allen untergeordneten Gruppen auf parent der '
                            'geloeschten Obergruppe gesetzt werden.';

create trigger waren_trigger7_on_update_delete_check_is_parent
    AFTER UPDATE OR DELETE ON waren.warengruppe FOR EACH ROW
    EXECUTE PROCEDURE on_update_delete_check_is_parent();
COMMENT ON TRIGGER waren_trigger7_on_update_delete_check_is_parent
    ON waren.warengruppe IS 'nach update oder delete muss gecheckt werden ob '
                            'is_parent noch bei allen Eintraegen stimmt.';
-- Ende Trigger

set search_path to waren;

create table produkt_typen (
    "produkt_typ" bit varying NOT NULL PRIMARY KEY, -- interne id
    "bezeichnung" text NOT NULL,            -- Bezeichnung, z.B. Kleidung, Uhren
    "typ_table" text NULL);                 -- Tabellenname fuer Typinfos

COMMENT ON TABLE produkt_typen IS 
    '<U>Enthaelt Bitmasken zu verschiedenen Produkttypen:</u></br>'
    'Eine Auswahl dieser Masken wird und-Verknuepft zu jedem Produkt und '
    'zu jeder Warengruppe gespeichert. Ergibt eine oder-Verknuepfung der '
    'Warengruppen-Maske und der Produkt-Maske einen Wert <> 0, so wird das '
    'Produkt in der Warengruppe angezeigt.';

select public.start_logging('produkt_typen', 'ALL');

set search_path to trigger_func,waren;

CREATE TRIGGER waren_trigger1_create_valid_typ_mask
    BEFORE INSERT OR UPDATE ON waren.produkt_typen FOR EACH ROW
    EXECUTE PROCEDURE create_valid_produkt_typ_mask();

set search_path to waren;

CREATE TABLE typ_table_columns (
    "produkt_typ" bit varying NOT NULL,     -- produkt-typ
    "col_name" text NOT NULL,               -- Name der Zusatzcolumn
    "col_type" text NOT NULL,               -- Typ der Zusatzcolumn
    PRIMARY KEY (produkt_typ, col_name),
    CONSTRAINT fk_typ_table_columns
        FOREIGN KEY (produkt_typ) REFERENCES produkt_typen (produkt_typ)
            ON UPDATE CASCADE
            ON DELETE CASCADE);

COMMENT ON TABLE typ_table_columns IS
    'In dieser Tabelle findet man die definition der Zusatzdatenfelder zu '
    'einem typ_table in produkt_typen.';

COMMENT ON COLUMN typ_table_columns.produkt_typ IS 'produkt-typ';
COMMENT ON COLUMN typ_table_columns.col_name IS 'Name der Zusatzcolumn';
COMMENT ON COLUMN typ_table_columns.col_type IS 'Typ der Zusatzcolumn';

select public.start_logging('typ_table_columns', 'ALL');

-- produkt ist eine allgemeine Oberklassifizierung von Produkten. Es enthaelt
-- alle Atribute, die alle Produkte gemeinsam haben.
-- Ausserdem enthaelt es eine Verknuepfung in die Tabelle produkt_typen, in 
-- der etwaige Tabellen mit Zusatzinformationen zu bestimmten Produkten
-- aufgelistet sind. Effektiv bedeutet das auch das der Admin und auch
-- der Anzeigenbereich verschiedenen Anzeige und Eingabemasken fuer die
-- verschiedenen Produkte zur verfuegung stellen muss. 
-- Das Backend liefert eine kombinierte Tabelle aus Produkt und der Speziellen
-- Produktabelle, aus der auch hervorgeht um welchen Produktyp es sich handelt.
create table produkt (
    "_id_" SERIAL NOT NULL PRIMARY KEY, -- interne id
    "name" text NOT NULL,               -- Bezeichnung des Produktes
    "kurzbeschreibung" text NULL,       -- Kruzbeschreibung
    "beschreibung" text NULL,           -- Beschreibung
    "small_pic_path" text NULL,         -- optionaler Pfad zu einem kleinen Bild
    "big_pic_path" text NULL,           -- optionaler Pfad zu einem grossen Bild
    "preis" numeric(10,2) NOT NULL,     -- Produktpreis (netto)
    "stueck" int4 DEFAULT 0 NOT NULL,   -- verfuegbare Stueckmenge
    -- ACHTUNG: Das produkt wird in allen Warengruppen angezeigt, bei denen
    -- midestens ein Bit der Typ-Maske der Warengruppe mit einem Bit der
    -- Typ-Maske des Produkts uebereinstimmt.
    "produkt_typen" bit varying NULL);  -- Produkttyp

select public.start_logging('produkt', 'ALL');

set search_path to public;
-- ]
-- Ende Waren, Warengruppen u. Lager --

-- View auf Adressdaten --
-- [
set search_path to adresse;

create view "adresse_v" (strasse,h_nr,plz_a,ort,vorwahl_o,
                         land,lnd_kz,vorwahl_l) as
    select a.strasse, a.h_nr, a.plz_a, b.ort, b.vorwahl_o, 
           c.land, c.lnd_kz, c.vorwahl_l from
        adresse.adresse a, adresse.ort b, adresse.land c where
        a.id_ort = b._id_ and
        b.id_land = c._id_;

set search_path to public;
-- ]
-- ende view --

-- View auf Personendaten --
-- [
set search_path to person;

create view "person_v" (anrede,titel,nachname,vorname,geb_dat,age,
                        postfach, telefon,fax,handy,email,webpage,
                        strasse,h_nr,plz_a,ort,vorwahl_o,
                        land,lnd_kz,vorwahl_l) as
    select a.anrede, a.titel, a. nachname, a.vorname, a.geb_dat, 
           age(a.geb_dat), a.postfach, a.telefon, a.fax, a.handy, 
           a.email, a.webpage, b.strasse, b.h_nr, b.plz_a, c.ort, 
           c.vorwahl_o, d.land, d.lnd_kz, d.vorwahl_l from
        person.person a, person.wohnt ab, 
        adresse.adresse b, adresse.ort c, adresse.land d where
        a._id_ = ab.id_person and
        b._id_ = ab.id_adresse and
        b.id_ort = c._id_ and
        c.id_land = d._id_;

grant SELECT on person.person_v TO GROUP guest;

set search_path to public;
-- ]
-- ende view --

commit;