Subversion Repositories public

ALTER TABLE kgroup DROP CONSTRAINT "Relationship39";
ALTER TABLE kgroup DROP COLUMN kg_kunum;
ALTER TABLE kgroup
   ALTER COLUMN kg_prnum DROP NOT NULL;
ALTER TABLE kgroup ALTER COLUMN kg_prnum SET STATISTICS -1;
COMMENT ON COLUMN kgroup.kg_prnum IS 'Projektnummer';

Create table "public"."kgsammel"
(
        "ks_num" Int4 Not Null UNIQUE,
        "ks_kgnum" Int4 Not Null,
        "ks_kunum" Int4 Not Null,
constraint "pk_kgsammel" primary key ("ks_num")
);

Comment on table "public"."kgsammel" is 'Dient als Kontainer fuer mehrere Kunden.';
COMMENT ON COLUMN "public"."kgsammel"."ks_num" IS 'Interne laufende Nummer';
COMMENT ON COLUMN "public"."kgsammel"."ks_kgnum" IS 'Verknuepfung zur Kundengruppe';
COMMENT ON COLUMN "public"."kgsammel"."ks_kunum" IS 'Verknuepfung zum Kunden';

Create index "IX_Relationship57" on "public"."kgsammel" ("ks_kunum");
Alter table "kgsammel" add Constraint "Relationship57" foreign key ("ks_kunum") references "public"."kunde" ("ku_num") on update restrict on delete restrict;
Create index "IX_Relationship58" on "public"."kgsammel" ("ks_kgnum");
Alter table "kgsammel" add Constraint "Relationship58" foreign key ("ks_kgnum") references "public"."kgroup" ("kg_num") on update restrict on delete restrict;

ALTER TABLE histanteil DROP CONSTRAINT "Relationship31";
ALTER TABLE histanteil RENAME hi_kunum  TO hi_ksnum;
ALTER TABLE histanteil ADD CONSTRAINT "Relationship31" FOREIGN KEY (hi_ksnum) REFERENCES kgsammel (ks_num)    ON UPDATE RESTRICT ON DELETE RESTRICT;
ALTER TABLE histanteil ALTER COLUMN hi_ksnum SET STATISTICS -1;
COMMENT ON COLUMN histanteil.hi_ksnum IS 'Kontainernummer';
alter table histanteil drop column hi_valid;
alter table histanteil add column hi_valid_from date;
alter table histanteil add column hi_valid_to date;
alter table histanteil add column hi_proz float8;
alter table histanteil alter column hi_valid_from set not null;
alter table histanteil alter column hi_proz set not null;
comment on column histanteil.hi_valid_from is 'Datum ab dem der Eintrag gilt';
comment on column histanteil.hi_valid_to is 'Datum bis zu dem der Eintrag gilt';
comment on column histanteil.hi_proz is 'Prozentsatz des Anteils';

ALTER TABLE charge DROP COLUMN ch_date;
ALTER TABLE charge
   ADD COLUMN ch_date date;
ALTER TABLE charge
   ALTER COLUMN ch_date SET NOT NULL;
COMMENT ON COLUMN charge.ch_date IS 'Datum an dem der Aufwand angefallen ist.';
COMMENT ON COLUMN charge.ch_date IS 'Datum an dem der Aufwand angefallen ist.';

ALTER TABLE counter DROP COLUMN co_anteil;

ALTER TABLE dration DROP COLUMN dr_valid_from;
ALTER TABLE dration
   ADD COLUMN dr_valid_from date;
ALTER TABLE dration
   ALTER COLUMN dr_valid_from SET NOT NULL;
COMMENT ON COLUMN dration.dr_valid_from IS 'Datum gueltig von. Notwendig um eine Historie aufzubauen';

Create table "public"."fixanteil"
(
        "fx_num" Int4 Not Null UNIQUE,
        "fx_valid_from" Date Not Null,
        "fx_valid_to" Date,
        "fx_kunum" Int4 Not Null,
        "fx_proz" Float8 Not Null,
constraint "pk_fixanteil" primary key ("fx_num")
);

Comment on column "public"."fixanteil"."fx_num" is 'Automatisch generierte interne laufende Nummer';
Comment on column "public"."fixanteil"."fx_valid_from" is 'Datum ab dem der Eintrag gilt.';
Comment on column "public"."fixanteil"."fx_valid_to" is 'Datum bis zu dem der Eintrag gilt.';
Comment on column "public"."fixanteil"."fx_kunum" is 'Kundennummer';
Comment on column "public"."fixanteil"."fx_proz" is 'Anteile in Prozent';
Create index "IX_Relationship32" on "fixanteil" ("fx_kunum");
Alter table "fixanteil" add Constraint "Relationship32" foreign key ("fx_kunum") references "kunde" ("ku_num") on update restrict on delete restrict;

CREATE TABLE key_whg
(
   wh_num int4 NOT NULL, 
   wh_whg char(5) NOT NULL, 
   wh_bez varchar(60), 
   CONSTRAINT pk_whg PRIMARY KEY (wh_num)
) WITHOUT OIDS;
ALTER TABLE key_whg OWNER TO "www-data";
COMMENT ON COLUMN key_whg.wh_num IS 'Interne laufende Nummer';
COMMENT ON COLUMN key_whg.wh_whg IS 'ISO-Code der Waehrung';
COMMENT ON COLUMN key_whg.wh_bez IS 'Bezeichnung der Waehrung';
COMMENT ON TABLE key_whg IS 'Waehrungsschluessel';
GRANT ALL ON TABLE key_whg TO public;
GRANT ALL ON TABLE key_whg TO andreas;

ALTER TABLE dration
   ADD COLUMN dr_whg int4;
ALTER TABLE dration
   ALTER COLUMN dr_whg SET NOT NULL;
COMMENT ON COLUMN dration.dr_whg IS 'Waehrung';
ALTER TABLE dration ADD CONSTRAINT "Relationship42" FOREIGN KEY (dr_whg) REFERENCES key_whg (wh_num)    ON UPDATE RESTRICT ON DELETE RESTRICT;
COMMENT ON COLUMN dration.dr_whg IS 'Waehrung';

ALTER TABLE charge
   ADD COLUMN ch_whg int4;
ALTER TABLE charge
   ALTER COLUMN ch_whg SET NOT NULL;
COMMENT ON COLUMN charge.ch_whg IS 'Waehrung';
ALTER TABLE charge ADD CONSTRAINT "Relationship39" FOREIGN KEY (ch_whg) REFERENCES key_whg (wh_num)    ON UPDATE RESTRICT ON DELETE RESTRICT;
COMMENT ON COLUMN charge.ch_whg IS 'Waehrung';

CREATE TABLE tagsatz
(
   tg_num int4 NOT NULL, 
   tg_valid_from date NOT NULL, 
   tg_valid_to date, 
   tg_amount float8, 
   tg_nightr float8, 
   tg_ration float8, 
   tg_kmcar float8, 
   tg_kmdrain float8, 
   tg_kmplain float8, 
   tg_whg int4 NOT NULL, 
   tg_minum int4 NOT NULL, 
   CONSTRAINT pk_tgnum PRIMARY KEY (tg_num), 
   CONSTRAINT fk_whg_10 FOREIGN KEY (tg_whg) REFERENCES key_whg (wh_num)    ON UPDATE RESTRICT ON DELETE RESTRICT, 
   CONSTRAINT fk_minum_10 FOREIGN KEY (tg_minum) REFERENCES mitarbeiter (mi_num)    ON UPDATE RESTRICT ON DELETE RESTRICT
) WITHOUT OIDS;
ALTER TABLE tagsatz OWNER TO "www-data";
COMMENT ON COLUMN tagsatz.tg_num IS 'Interne laufende Nummer';
COMMENT ON COLUMN tagsatz.tg_valid_from IS 'Datum gueltig ab';
COMMENT ON COLUMN tagsatz.tg_valid_to IS 'Datum gueltig bis';
COMMENT ON COLUMN tagsatz.tg_amount IS 'Tagsatz';
COMMENT ON COLUMN tagsatz.tg_nightr IS 'Naechtigungspauschale';
COMMENT ON COLUMN tagsatz.tg_ration IS 'Tagesdiaeten';
COMMENT ON COLUMN tagsatz.tg_kmcar IS 'Kilometerpauschale PKW';
COMMENT ON COLUMN tagsatz.tg_kmdrain IS 'Kilometerpauschale Zug';
COMMENT ON COLUMN tagsatz.tg_kmplain IS 'Kilometerpauschale Flugzeug';
COMMENT ON COLUMN tagsatz.tg_whg IS 'Verknuepfung zur Waehrung';
COMMENT ON COLUMN tagsatz.tg_minum IS 'Verknuepfung zum Mitarbeiter';
COMMENT ON TABLE tagsatz IS 'Beinhaltet die Tagsaetze zu jedem einzelnen Mitarbeiter';
GRANT ALL ON TABLE tagsatz TO public;
GRANT ALL ON TABLE tagsatz TO andreas;

ALTER TABLE dration DROP COLUMN dr_ration;
ALTER TABLE dration DROP COLUMN dr_kmcar;
ALTER TABLE dration DROP COLUMN dr_kmdrain;
ALTER TABLE dration
   ADD COLUMN dr_date_start timestamp;
COMMENT ON COLUMN dration.dr_date_start IS 'Startdatum und Zeit der Reise';
ALTER TABLE dration
   ADD COLUMN dr_date_end timestamp;
COMMENT ON COLUMN dration.dr_date_end IS 'Endedatum und Zeit der Reise';
ALTER TABLE dration
   ADD COLUMN dr_target varchar(60);
COMMENT ON COLUMN dration.dr_target IS 'Zielort / Land';
ALTER TABLE dration
   ADD COLUMN dr_purpose varchar(60);
   COMMENT ON COLUMN dration.dr_purpose IS 'Zweck der Reise';
ALTER TABLE dration
   ADD COLUMN dr_pnightr bool;
COMMENT ON COLUMN dration.dr_pnightr IS 'True = Naechtigungspauschale, False = Sonderbetrag';
ALTER TABLE dration
   ADD COLUMN dr_kmcar int2;
COMMENT ON COLUMN dration.dr_kmcar IS 'Gefahrene Kilometer';
ALTER TABLE dration
   ADD COLUMN dr_kfzkenn char(16);
COMMENT ON COLUMN dration.dr_kfzkenn IS 'KFZ Kennzeichen';
ALTER TABLE dration
   ADD COLUMN dr_kmdrain int2;
COMMENT ON COLUMN dration.dr_kmdrain IS 'Gefahrene Zug-Kilometer';
ALTER TABLE dration
   ADD COLUMN dr_pkmplain bool;
COMMENT ON COLUMN dration.dr_pkmplain IS 'True = Flugpauschale, False = Einzelpreis fuer Flug';
ALTER TABLE dration
   ADD COLUMN dr_remark varchar(4096);
COMMENT ON COLUMN dration.dr_remark IS 'Bemerkung / Kommentar';
ALTER TABLE dration
   ADD COLUMN dr_costloc int4;
COMMENT ON COLUMN dration.dr_costloc IS 'Kostenstelle';
ALTER TABLE dration
   ADD COLUMN dr_permit bool;
COMMENT ON COLUMN dration.dr_permit IS 'True = Abrechnung ist genehmigt';
ALTER TABLE dration
   ADD COLUMN dr_minum int4;
COMMENT ON COLUMN dration.dr_minum IS 'Verweis auf Mitarbeiter der die Genehmigung erteilt hat';
ALTER TABLE dration ADD CONSTRAINT fk_costloc_11 FOREIGN KEY (dr_costloc) REFERENCES key_costloc (kc_num)    ON UPDATE RESTRICT ON DELETE RESTRICT;
ALTER TABLE dration ADD CONSTRAINT fk_minum_12 FOREIGN KEY (dr_minum) REFERENCES mitarbeiter (mi_num)    ON UPDATE RESTRICT ON DELETE RESTRICT;