Mercurial > lada > lada-server

changeset 1014:6b204c617c1f schema-update

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
Replaced explicid sequences with serials.
author Raimund Renkert <raimund.renkert@intevation.de>
date Mon, 04 Jul 2016 11:46:42 +0200
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);
This site is hosted by Intevation GmbH (Datenschutzerklärung und Impressum | Privacy Policy and Imprint)