Mercurial > gemma
comparison schema/gemma.sql @ 4673:443867b548b5
Fix identifiers in layers generated from SQL views
| author | Tom Gottfried <tom@intevation.de> |
|---|---|
| date | Tue, 15 Oct 2019 15:59:38 +0200 |
| parents | 66fcd898efd9 |
| children | 9279fdb7a422 |
comparison
equal
deleted
inserted
replaced
| 4672:c7dc1a6da93d | 4673:443867b548b5 |
|---|---|
| 386 schema varchar CHECK(to_regnamespace(schema) IS NOT NULL), | 386 schema varchar CHECK(to_regnamespace(schema) IS NOT NULL), |
| 387 name varchar, | 387 name varchar, |
| 388 PRIMARY KEY (schema, name), | 388 PRIMARY KEY (schema, name), |
| 389 -- SQL statement used for an SQL view in GeoServer: | 389 -- SQL statement used for an SQL view in GeoServer: |
| 390 view_def text CHECK (is_valid_from_item(view_def)), | 390 view_def text CHECK (is_valid_from_item(view_def)), |
| 391 -- Column in output of SQL statement to be used as primary key: | |
| 392 key_column varchar, | |
| 391 -- SRID to be used with SQL view: | 393 -- SRID to be used with SQL view: |
| 392 srid int REFERENCES spatial_ref_sys, | 394 srid int REFERENCES spatial_ref_sys, |
| 393 -- SLD style document: | 395 -- SLD style document: |
| 394 style xml CHECK(style IS DOCUMENT), | 396 style xml CHECK(style IS DOCUMENT), |
| 395 as_wms boolean NOT NULL DEFAULT TRUE, | 397 as_wms boolean NOT NULL DEFAULT TRUE, |
| 573 geom geography(POINT, 4326) NOT NULL, | 575 geom geography(POINT, 4326) NOT NULL, |
| 574 -- include location in primary key, because we have no fairway code: | 576 -- include location in primary key, because we have no fairway code: |
| 575 catdis smallint REFERENCES catdis, | 577 catdis smallint REFERENCES catdis, |
| 576 position_code char(2) REFERENCES position_codes, | 578 position_code char(2) REFERENCES position_codes, |
| 577 related_enc varchar(12) | 579 related_enc varchar(12) |
| 578 ) | |
| 579 | |
| 580 -- We need to configure primary keys for the views used by | |
| 581 -- geoserver for wfs, otherwise it will generate ids on the fly, | |
| 582 -- which will change for the same feature... | |
| 583 -- See | |
| 584 -- https://docs.geoserver.org/stable/en/user/data/database/primarykey.html | |
| 585 -- for details. | |
| 586 CREATE TABLE gt_pk_metadata ( | |
| 587 table_schema VARCHAR(32) NOT NULL, | |
| 588 table_name VARCHAR(32) NOT NULL, | |
| 589 pk_column VARCHAR(32) NOT NULL, | |
| 590 pk_column_idx INTEGER, | |
| 591 pk_policy VARCHAR(32), | |
| 592 pk_sequence VARCHAR(64), | |
| 593 unique (table_schema, table_name, pk_column), | |
| 594 check (pk_policy in ('sequence', 'assigned', 'autogenerated')) | |
| 595 ) | 580 ) |
| 596 | 581 |
| 597 -- Like stretches without the countries | 582 -- Like stretches without the countries |
| 598 CREATE TABLE sections ( | 583 CREATE TABLE sections ( |
| 599 id int PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY, | 584 id int PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY, |
| 800 CHECK(measure_type = 'minimum guaranteed' | 785 CHECK(measure_type = 'minimum guaranteed' |
| 801 OR value_lifetime IS NOT NULL) | 786 OR value_lifetime IS NOT NULL) |
| 802 ) | 787 ) |
| 803 ; | 788 ; |
| 804 | 789 |
| 805 -- Configure primary keys for geoserver views | |
| 806 INSERT INTO waterway.gt_pk_metadata VALUES | |
| 807 ('waterway', 'gauges_geoserver', 'isrs_code'), | |
| 808 ('waterway', 'distance_marks_geoserver', 'location'), | |
| 809 ('waterway', 'distance_marks_ashore_geoserver', 'id'), | |
| 810 ('waterway', 'bottlenecks_geoserver', 'id'), | |
| 811 ('waterway', 'stretches_geoserver', 'id'), | |
| 812 ('waterway', 'sections_geoserver', 'id'); | |
| 813 | 790 |
| 814 -- | 791 -- |
| 815 -- Import queue and respective logging | 792 -- Import queue and respective logging |
| 816 -- | 793 -- |
| 817 CREATE TYPE import_state AS ENUM ( | 794 CREATE TYPE import_state AS ENUM ( |