Mercurial > lada > lada-server
changeset 1014:6b204c617c1f schema-update
Replaced explicid sequences with serials.
author | Raimund Renkert <raimund.renkert@intevation.de> |
---|---|
date | Mon, 04 Jul 2016 11:46:42 +0200 (2016-07-04) |
parents | 48c07fc5d2d1 |
children | 9f903c54141b |
files | db_schema/stammdaten_schema.sql |
diffstat | 1 files changed, 23 insertions(+), 230 deletions(-) [+] |
line wrap: on
line diff
--- a/db_schema/stammdaten_schema.sql Mon Jul 04 11:46:20 2016 +0200 +++ b/db_schema/stammdaten_schema.sql Mon Jul 04 11:46:42 2016 +0200 @@ -85,15 +85,8 @@ $$; -CREATE SEQUENCE staat_id_seq - START WITH 1 - INCREMENT BY 1 - NO MINVALUE - NO MAXVALUE - CACHE 1; - CREATE TABLE staat ( - id integer PRIMARY KEY DEFAULT nextval('staat_id_seq'::regclass), + id serial PRIMARY KEY, staat character varying(50) NOT NULL, hkl_id smallint NOT NULL, staat_iso character varying(2) NOT NULL, @@ -104,8 +97,6 @@ kda_id integer ); -ALTER SEQUENCE staat_id_seq OWNED BY staat.id; - CREATE TABLE verwaltungseinheit ( id character varying(8) PRIMARY KEY, @@ -147,15 +138,8 @@ ); -CREATE SEQUENCE auth_id_seq - START WITH 1 - INCREMENT BY 1 - NO MINVALUE - NO MAXVALUE - CACHE 1; - CREATE TABLE auth ( - id integer PRIMARY KEY DEFAULT nextval('auth_id_seq'::regclass), + id serial PRIMARY KEY, ldap_group character varying(40) NOT NULL, netzbetreiber_id character varying(2), mst_id character varying(5), @@ -163,8 +147,6 @@ funktion_id smallint ); -ALTER SEQUENCE auth_id_seq OWNED BY auth.id; - CREATE TABLE auth_funktion ( id smallint PRIMARY KEY, @@ -177,48 +159,22 @@ INSERT INTO auth_funktion VALUES (4, 'Stammdatenpflege-Land'); -CREATE SEQUENCE auth_lst_umw_id_seq - START WITH 1 - INCREMENT BY 1 - NO MINVALUE - NO MAXVALUE - CACHE 1; - CREATE TABLE auth_lst_umw ( - id integer PRIMARY KEY DEFAULT nextval('auth_lst_umw_id_seq'::regclass), + id serial PRIMARY KEY, mst_id character varying(5), umw_id character varying(3) ); -ALTER SEQUENCE auth_lst_umw_id_seq OWNED BY auth_lst_umw.id; - - -CREATE SEQUENCE datenbasis_id_seq - START WITH 1 - INCREMENT BY 1 - NO MINVALUE - NO MAXVALUE - CACHE 1; CREATE TABLE datenbasis ( - id integer PRIMARY KEY DEFAULT nextval('datenbasis_id_seq'::regclass), + id serial PRIMARY KEY, beschreibung character varying(30), datenbasis character varying(6) ); -ALTER SEQUENCE datenbasis_id_seq OWNED BY datenbasis.id; - - -CREATE SEQUENCE datensatz_erzeuger_id_seq - START WITH 1 - INCREMENT BY 1 - NO MINVALUE - NO MAXVALUE - CACHE 1; CREATE TABLE datensatz_erzeuger ( - id integer PRIMARY KEY - DEFAULT nextval('datensatz_erzeuger_id_seq'::regclass), + id serial PRIMARY KEY, netzbetreiber_id character varying(2) NOT NULL REFERENCES netz_betreiber, datensatz_erzeuger_id character varying(2) NOT NULL, mst_id character varying(5) NOT NULL REFERENCES mess_stelle, @@ -228,18 +184,9 @@ ); CREATE TRIGGER letzte_aenderung_datensatz_erzeuger BEFORE UPDATE ON datensatz_erzeuger FOR EACH ROW EXECUTE PROCEDURE update_letzte_aenderung(); -ALTER SEQUENCE datensatz_erzeuger_id_seq OWNED BY datensatz_erzeuger.id; - - -CREATE SEQUENCE de_vg_id_seq - START WITH 1 - INCREMENT BY 1 - NO MINVALUE - NO MAXVALUE - CACHE 1; CREATE TABLE de_vg ( - id integer PRIMARY KEY DEFAULT nextval('de_vg_id_seq'::regclass), + id serial PRIMARY KEY, use double precision, rs character varying(12), gf double precision, @@ -258,8 +205,6 @@ geom public.geometry(MultiPolygon,4326) ); -ALTER SEQUENCE de_vg_id_seq OWNED BY de_vg.id; - CREATE TABLE deskriptor_umwelt ( id integer PRIMARY KEY, @@ -280,15 +225,8 @@ ); -CREATE SEQUENCE deskriptoren_id_seq - START WITH 1 - INCREMENT BY 1 - NO MINVALUE - NO MAXVALUE - CACHE 1; - CREATE TABLE deskriptoren ( - id integer PRIMARY KEY DEFAULT nextval('deskriptoren_id_seq'::regclass), + id serial PRIMARY KEY, vorgaenger integer, ebene smallint, s_xx integer, @@ -297,67 +235,31 @@ bedeutung character varying(300) ); -ALTER SEQUENCE deskriptoren_id_seq OWNED BY deskriptoren.id; - - -CREATE SEQUENCE lada_user_id_seq - START WITH 1 - INCREMENT BY 1 - NO MINVALUE - NO MAXVALUE - CACHE 1; CREATE TABLE lada_user ( - id integer PRIMARY KEY DEFAULT nextval('lada_user_id_seq'::regclass), + id serial PRIMARY KEY, name character varying(80) NOT NULL ); -ALTER SEQUENCE lada_user_id_seq OWNED BY lada_user.id; - - -CREATE SEQUENCE query_id_seq - START WITH 1 - INCREMENT BY 1 - NO MINVALUE - NO MAXVALUE - CACHE 1; CREATE TABLE query ( - id integer PRIMARY KEY DEFAULT nextval('query_id_seq'::regclass), + id serial PRIMARY KEY, name character varying(80) NOT NULL, type character varying(30) NOT NULL, sql character varying(1500) NOT NULL, description character varying(100) ); -ALTER SEQUENCE query_id_seq OWNED BY query.id; - - -CREATE SEQUENCE favorite_id_seq - START WITH 1 - INCREMENT BY 1 - NO MINVALUE - NO MAXVALUE - CACHE 1; CREATE TABLE favorite ( - id integer PRIMARY KEY DEFAULT nextval('favorite_id_seq'::regclass), + id serial PRIMARY KEY, user_id integer NOT NULL REFERENCES lada_user, query_id integer NOT NULL REFERENCES query ON DELETE CASCADE ); -ALTER SEQUENCE favorite_id_seq OWNED BY favorite.id; - - -CREATE SEQUENCE filter_id_seq - START WITH 1 - INCREMENT BY 1 - NO MINVALUE - NO MAXVALUE - CACHE 1; CREATE TABLE filter ( - id integer PRIMARY KEY DEFAULT nextval('filter_id_seq'::regclass), + id serial PRIMARY KEY, query_id integer NOT NULL REFERENCES query ON DELETE CASCADE, data_index character varying(50) NOT NULL, type character varying(10) NOT NULL, @@ -365,59 +267,30 @@ multiselect boolean ); -ALTER SEQUENCE filter_id_seq OWNED BY filter.id; - - -CREATE SEQUENCE filter_value_id_seq - START WITH 1 - INCREMENT BY 1 - NO MINVALUE - NO MAXVALUE - CACHE 1; CREATE TABLE filter_value ( - id integer PRIMARY KEY DEFAULT nextval('filter_value_id_seq'::regclass), + id serial PRIMARY KEY, user_id integer NOT NULL REFERENCES lada_user, filter_id integer NOT NULL REFERENCES filter ON DELETE CASCADE, value text ); -ALTER SEQUENCE filter_value_id_seq OWNED BY filter_value.id; - - -CREATE SEQUENCE koordinaten_art_id_seq - START WITH 1 - INCREMENT BY 1 - NO MINVALUE - NO MAXVALUE - CACHE 1; CREATE TABLE koordinaten_art ( - id integer PRIMARY KEY DEFAULT nextval('koordinaten_art_id_seq'::regclass), + id serial PRIMARY KEY, koordinatenart character varying(50), idf_geo_key character varying(1) ); -ALTER SEQUENCE koordinaten_art_id_seq OWNED BY koordinaten_art.id; - - -CREATE SEQUENCE mess_einheit_id_seq - START WITH 1 - INCREMENT BY 1 - NO MINVALUE - NO MAXVALUE - CACHE 1; CREATE TABLE mess_einheit ( - id integer PRIMARY KEY DEFAULT nextval('mess_einheit_id_seq'::regclass), + id serial PRIMARY KEY, beschreibung character varying(50), einheit character varying(12), eudf_messeinheit_id character varying(8), umrechnungs_faktor_eudf bigint ); -ALTER SEQUENCE mess_einheit_id_seq OWNED BY mess_einheit.id; - CREATE TABLE mess_methode ( id character varying(2) PRIMARY KEY, @@ -426,15 +299,8 @@ ); -CREATE SEQUENCE messgroesse_id_seq - START WITH 1 - INCREMENT BY 1 - NO MINVALUE - NO MAXVALUE - CACHE 1; - CREATE TABLE messgroesse ( - id integer PRIMARY KEY DEFAULT nextval('messgroesse_id_seq'::regclass), + id serial PRIMARY KEY, beschreibung character varying(300), messgroesse character varying(50) NOT NULL, default_farbe character varying(9), @@ -444,36 +310,16 @@ kennung_bvl character varying(7) ); -ALTER SEQUENCE messgroesse_id_seq OWNED BY messgroesse.id; - - -CREATE SEQUENCE messgroessen_gruppe_id_seq - START WITH 1 - INCREMENT BY 1 - NO MINVALUE - NO MAXVALUE - CACHE 1; CREATE TABLE messgroessen_gruppe ( - id integer PRIMARY KEY - DEFAULT nextval('messgroessen_gruppe_id_seq'::regclass), + id serial PRIMARY KEY, bezeichnung character varying(80), ist_leitnuklidgruppe character(1) DEFAULT NULL::bpchar ); -ALTER SEQUENCE messgroessen_gruppe_id_seq OWNED BY messgroessen_gruppe.id; - - -CREATE SEQUENCE messprogramm_kategorie_id_seq - START WITH 1 - INCREMENT BY 1 - NO MINVALUE - NO MAXVALUE - CACHE 1; CREATE TABLE messprogramm_kategorie ( - id integer PRIMARY KEY - DEFAULT nextval('messprogramm_kategorie_id_seq'::regclass), + id serial PRIMARY KEY, netzbetreiber_id character varying(2) NOT NULL REFERENCES netz_betreiber, code character varying(3) NOT NULL, bezeichnung character varying(120) NOT NULL, @@ -482,9 +328,6 @@ ); CREATE TRIGGER letzte_aenderung_messprogramm_kategorie BEFORE UPDATE ON messprogramm_kategorie FOR EACH ROW EXECUTE PROCEDURE update_letzte_aenderung(); -ALTER SEQUENCE messprogramm_kategorie_id_seq - OWNED BY messprogramm_kategorie.id; - CREATE TABLE mg_grp ( messgroessengruppe_id integer NOT NULL, @@ -512,15 +355,8 @@ ); -CREATE SEQUENCE ort_id_seq - START WITH 1 - INCREMENT BY 1 - NO MINVALUE - NO MAXVALUE - CACHE 1; - CREATE TABLE ort ( - id integer PRIMARY KEY DEFAULT nextval('ort_id_seq'::regclass), + id serial PRIMARY KEY, netzbetreiber_id character varying(2) NOT NULL REFERENCES netz_betreiber, ort_id character varying(10) NOT NULL, langtext character varying(100) NOT NULL, @@ -557,8 +393,6 @@ ALTER TABLE ONLY ort ADD CONSTRAINT ort_oz_fkey FOREIGN KEY (oz_id) REFERENCES ort(id); -ALTER SEQUENCE ort_id_seq OWNED BY ort.id; - CREATE TABLE ortszuordnung_typ ( id character(1) PRIMARY KEY, @@ -566,24 +400,14 @@ ); -CREATE SEQUENCE pflicht_messgroesse_id_seq - START WITH 1 - INCREMENT BY 1 - NO MINVALUE - NO MAXVALUE - CACHE 1; - CREATE TABLE pflicht_messgroesse ( - id integer PRIMARY KEY - DEFAULT nextval('pflicht_messgroesse_id_seq'::regclass), + id serial PRIMARY KEY, messgroesse_id integer, mmt_id character varying(2), umw_id character varying(3), datenbasis_id smallint NOT NULL ); -ALTER SEQUENCE pflicht_messgroesse_id_seq OWNED BY pflicht_messgroesse.id; - CREATE TABLE proben_zusatz ( id character varying(3) PRIMARY KEY, @@ -594,32 +418,16 @@ ); -CREATE SEQUENCE probenart_id_seq - START WITH 1 - INCREMENT BY 1 - NO MINVALUE - NO MAXVALUE - CACHE 1; - CREATE TABLE probenart ( - id integer PRIMARY KEY DEFAULT nextval('probenart_id_seq'::regclass), + id serial PRIMARY KEY, beschreibung character varying(30), probenart character varying(5) NOT NULL, probenart_eudf_id character varying(1) NOT NULL ); -ALTER SEQUENCE probenart_id_seq OWNED BY probenart.id; - - -CREATE SEQUENCE probenehmer_id_seq - START WITH 1 - INCREMENT BY 1 - NO MINVALUE - NO MAXVALUE - CACHE 1; CREATE TABLE probenehmer ( - id integer PRIMARY KEY DEFAULT nextval('probenehmer_id_seq'::regclass), + id serial PRIMARY KEY, netzbetreiber_id character varying(2) NOT NULL REFERENCES netz_betreiber, prn_id character varying(9) NOT NULL, bearbeiter character varying(25), @@ -638,18 +446,9 @@ ); CREATE TRIGGER letzte_aenderung_probenehmer BEFORE UPDATE ON probenehmer FOR EACH ROW EXECUTE PROCEDURE update_letzte_aenderung(); -ALTER SEQUENCE probenehmer_id_seq OWNED BY probenehmer.id; - - -CREATE SEQUENCE result_id_seq - START WITH 1 - INCREMENT BY 1 - NO MINVALUE - NO MAXVALUE - CACHE 1; CREATE TABLE result ( - id integer PRIMARY KEY DEFAULT nextval('result_id_seq'::regclass), + id serial PRIMARY KEY, query_id integer NOT NULL REFERENCES query ON DELETE CASCADE, data_index character varying(50) NOT NULL, header character varying(50) NOT NULL, @@ -659,9 +458,6 @@ UNIQUE (query_id, data_index) ); -ALTER SEQUENCE result_id_seq OWNED BY result.id; - - -- Status workflow CREATE TABLE status_stufe ( @@ -708,9 +504,8 @@ INSERT INTO status_kombi VALUES (13, 3, 4); -CREATE SEQUENCE status_reihenfolge_id_seq; CREATE TABLE status_reihenfolge ( - id integer PRIMARY KEY DEFAULT nextval('status_reihenfolge_id_seq'), + id serial PRIMARY KEY, von_id integer REFERENCES status_kombi NOT NULL, zu_id integer REFERENCES status_kombi NOT NULL, UNIQUE(von_id, zu_id) @@ -786,8 +581,6 @@ ); - - ALTER TABLE ONLY lada_user ADD CONSTRAINT lada_user_name_key UNIQUE (name);