0,0 → 1,184 |
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; |
|