Skip to content

Instantly share code, notes, and snippets.

@ponceta

ponceta/qwat_od.valve.sql

Created July 26, 2018 14:08
Show Gist options
  • Save ponceta/101c03969088e559897010738437f27f to your computer and use it in GitHub Desktop.
Save ponceta/101c03969088e559897010738437f27f to your computer and use it in GitHub Desktop.
Download ZIP
QWAT OD valve
Raw
qwat_od.valve.sql
-- Table: qwat_od.valve
-- DROP TABLE qwat_od.valve;
CREATE TABLE qwat_od.valve
(
id integer NOT NULL DEFAULT nextval('qwat_od.valve_id_seq'::regclass),
fk_valve_type integer NOT NULL,
fk_valve_function integer NOT NULL,
fk_valve_actuation integer NOT NULL,
fk_pipe integer,
fk_handle_precision integer,
fk_handle_precisionalti integer,
fk_maintenance integer[],
diameter_nominal character varying(10),
closed boolean DEFAULT false,
networkseparation boolean DEFAULT false,
handle_altitude numeric(10,3),
handle_geometry geometry(PointZ,21781),
fk_district integer,
fk_pressurezone integer,
fk_distributor integer NOT NULL,
fk_precision integer NOT NULL,
fk_precisionalti integer,
fk_status integer NOT NULL,
fk_object_reference integer,
fk_folder integer,
year smallint,
year_end smallint,
altitude numeric(10,3) DEFAULT NULL::numeric,
orientation double precision,
fk_locationtype integer[],
identification character varying(50),
remark text,
fk_printmap integer[],
_geometry_alt1_used boolean,
_geometry_alt2_used boolean,
_pipe_node_type qwat_od.pipe_connection,
_pipe_orientation double precision DEFAULT 0,
_pipe_schema_visible boolean DEFAULT false,
_printmaps text,
geometry geometry(PointZ,21781) NOT NULL,
geometry_alt1 geometry(PointZ,21781),
geometry_alt2 geometry(PointZ,21781),
update_geometry_alt1 boolean,
update_geometry_alt2 boolean,
label_1_visible smallint DEFAULT 1,
label_1_x double precision,
label_1_y double precision,
label_1_rotation double precision,
label_1_text character varying(120),
label_2_visible smallint DEFAULT 1,
label_2_x double precision,
label_2_y double precision,
label_2_rotation double precision,
label_2_text character varying(120),
schema_force_visible boolean,
_schema_visible boolean, -- define if the element is visible on schematic view. Update by trigger as COALESCE(schema_force_visible, valve_function.schema_visible)
qwat_ext_ch_vd_sire_etat_exploitation smallint,
qwat_ext_ch_vd_sire_remarque text,
qwat_ext_ch_vd_sire_adesafecter smallint,
qwat_ext_ch_vd_eca_subvention boolean, -- Subvention ECA
pully_id_topobase integer,
CONSTRAINT valve_pkey PRIMARY KEY (id),
CONSTRAINT valve_fk_distributor FOREIGN KEY (fk_distributor)
REFERENCES qwat_od.distributor (id) MATCH FULL
ON UPDATE NO ACTION ON DELETE NO ACTION,
CONSTRAINT valve_fk_district FOREIGN KEY (fk_district)
REFERENCES qwat_od.district (id) MATCH FULL
ON UPDATE NO ACTION ON DELETE NO ACTION,
CONSTRAINT valve_fk_folder FOREIGN KEY (fk_folder)
REFERENCES qwat_od.folder (id) MATCH FULL
ON UPDATE NO ACTION ON DELETE NO ACTION,
CONSTRAINT valve_fk_function FOREIGN KEY (fk_valve_function)
REFERENCES qwat_vl.valve_function (id) MATCH FULL
ON UPDATE NO ACTION ON DELETE NO ACTION,
CONSTRAINT valve_fk_handle_precision FOREIGN KEY (fk_handle_precision)
REFERENCES qwat_vl."precision" (id) MATCH FULL
ON UPDATE NO ACTION ON DELETE NO ACTION,
CONSTRAINT valve_fk_handle_precisionalti FOREIGN KEY (fk_handle_precisionalti)
REFERENCES qwat_vl.precisionalti (id) MATCH FULL
ON UPDATE NO ACTION ON DELETE NO ACTION,
CONSTRAINT valve_fk_object_reference FOREIGN KEY (fk_object_reference)
REFERENCES qwat_vl.object_reference (id) MATCH FULL
ON UPDATE NO ACTION ON DELETE NO ACTION,
CONSTRAINT valve_fk_pipe FOREIGN KEY (fk_pipe)
REFERENCES qwat_od.pipe (id) MATCH FULL
ON UPDATE NO ACTION ON DELETE NO ACTION,
CONSTRAINT valve_fk_precision FOREIGN KEY (fk_precision)
REFERENCES qwat_vl."precision" (id) MATCH FULL
ON UPDATE NO ACTION ON DELETE NO ACTION,
CONSTRAINT valve_fk_precisionalti FOREIGN KEY (fk_precisionalti)
REFERENCES qwat_vl.precisionalti (id) MATCH FULL
ON UPDATE NO ACTION ON DELETE NO ACTION,
CONSTRAINT valve_fk_pressurezone FOREIGN KEY (fk_pressurezone)
REFERENCES qwat_od.pressurezone (id) MATCH FULL
ON UPDATE NO ACTION ON DELETE NO ACTION,
CONSTRAINT valve_fk_status FOREIGN KEY (fk_status)
REFERENCES qwat_vl.status (id) MATCH FULL
ON UPDATE NO ACTION ON DELETE NO ACTION,
CONSTRAINT valve_fk_type FOREIGN KEY (fk_valve_type)
REFERENCES qwat_vl.valve_type (id) MATCH FULL
ON UPDATE NO ACTION ON DELETE NO ACTION,
CONSTRAINT valve_fk_valve_actuation FOREIGN KEY (fk_valve_actuation)
REFERENCES qwat_vl.valve_actuation (id) MATCH FULL
ON UPDATE NO ACTION ON DELETE NO ACTION,
CONSTRAINT valve_label_1_visible FOREIGN KEY (label_1_visible)
REFERENCES qwat_vl.visible (vl_code_int) MATCH SIMPLE
ON UPDATE NO ACTION ON DELETE NO ACTION,
CONSTRAINT valve_label_2_visible FOREIGN KEY (label_2_visible)
REFERENCES qwat_vl.visible (vl_code_int) MATCH SIMPLE
ON UPDATE NO ACTION ON DELETE NO ACTION,
CONSTRAINT valve_schema_force_visible FOREIGN KEY (schema_force_visible)
REFERENCES qwat_vl.visible (vl_code) MATCH FULL
ON UPDATE NO ACTION ON DELETE NO ACTION,
CONSTRAINT valve_year_check CHECK (year IS NULL OR year > 1800 AND year < 2100),
CONSTRAINT valve_year_end_check CHECK (year_end IS NULL OR year_end > 1800 AND year_end < 2100)
)
WITH (
OIDS=FALSE
);
ALTER TABLE qwat_od.valve
OWNER TO postgres;
GRANT ALL ON TABLE qwat_od.valve TO postgres;
GRANT SELECT, REFERENCES, TRIGGER ON TABLE qwat_od.valve TO qwat_viewer;
GRANT ALL ON TABLE qwat_od.valve TO qwat_user;
GRANT ALL ON TABLE qwat_od.valve TO qwat_manager;
COMMENT ON TABLE qwat_od.valve
IS 'Table for valve. Inherits from node.';
COMMENT ON COLUMN qwat_od.valve._schema_visible IS 'define if the element is visible on schematic view. Update by trigger as COALESCE(schema_force_visible, valve_function.schema_visible)';
COMMENT ON COLUMN qwat_od.valve.qwat_ext_ch_vd_eca_subvention IS 'Subvention ECA';
-- Index: qwat_od.fki_valve_fk_distributor
-- DROP INDEX qwat_od.fki_valve_fk_distributor;
CREATE INDEX fki_valve_fk_distributor
ON qwat_od.valve
USING btree
(fk_distributor);
-- Index: qwat_od.fki_valve_fk_district
-- DROP INDEX qwat_od.fki_valve_fk_district;
CREATE INDEX fki_valve_fk_district
ON qwat_od.valve
USING btree
(fk_district);
-- Index: qwat_od.fki_valve_fk_folder
-- DROP INDEX qwat_od.fki_valve_fk_folder;
CREATE INDEX fki_valve_fk_folder
ON qwat_od.valve
USING btree
(fk_folder);
-- Index: qwat_od.fki_valve_fk_function
-- DROP INDEX qwat_od.fki_valve_fk_function;
CREATE INDEX fki_valve_fk_function
ON qwat_od.valve
USING btree
(fk_valve_function);
-- Index: qwat_od.fki_valve_fk_handle_precision
-- DROP INDEX qwat_od.fki_valve_fk_handle_precision;
CREATE INDEX fki_valve_fk_handle_precision
ON qwat_od.valve
USING btree
(fk_handle_precision);
-- Index: qwat_od.fki_valve_fk_handle_precisionalti
-- DROP INDEX qwat_od.fki_valve_fk_handle_precisionalti;
CREATE INDEX fki_valve_fk_handle_precisionalti
ON qwat_od.valve
USING btree
(fk_handle_precisionalti);
-- Index: qwat_od.fki_valve_fk_object_reference
-- DROP INDEX qwat_od.fki_valve_fk_object_reference;
CREATE INDEX fki_valve_fk_object_reference
ON qwat_od.valve
USING btree
(fk_object_reference);
-- Index: qwat_od.fki_valve_fk_pipe
-- DROP INDEX qwat_od.fki_valve_fk_pipe;
CREATE INDEX fki_valve_fk_pipe
ON qwat_od.valve
USING btree
(fk_pipe);
-- Index: qwat_od.fki_valve_fk_precision
-- DROP INDEX qwat_od.fki_valve_fk_precision;
CREATE INDEX fki_valve_fk_precision
ON qwat_od.valve
USING btree
(fk_precision);
-- Index: qwat_od.fki_valve_fk_precisionalti
-- DROP INDEX qwat_od.fki_valve_fk_precisionalti;
CREATE INDEX fki_valve_fk_precisionalti
ON qwat_od.valve
USING btree
(fk_precisionalti);
-- Index: qwat_od.fki_valve_fk_pressurezone
-- DROP INDEX qwat_od.fki_valve_fk_pressurezone;
CREATE INDEX fki_valve_fk_pressurezone
ON qwat_od.valve
USING btree
(fk_pressurezone);
-- Index: qwat_od.fki_valve_fk_status
-- DROP INDEX qwat_od.fki_valve_fk_status;
CREATE INDEX fki_valve_fk_status
ON qwat_od.valve
USING btree
(fk_status);
-- Index: qwat_od.fki_valve_fk_type
-- DROP INDEX qwat_od.fki_valve_fk_type;
CREATE INDEX fki_valve_fk_type
ON qwat_od.valve
USING btree
(fk_valve_type);
-- Index: qwat_od.fki_valve_fk_valve_actuation
-- DROP INDEX qwat_od.fki_valve_fk_valve_actuation;
CREATE INDEX fki_valve_fk_valve_actuation
ON qwat_od.valve
USING btree
(fk_valve_actuation);
-- Index: qwat_od.fki_valve_label_1_visible
-- DROP INDEX qwat_od.fki_valve_label_1_visible;
CREATE INDEX fki_valve_label_1_visible
ON qwat_od.valve
USING btree
(label_1_visible);
-- Index: qwat_od.fki_valve_label_2_visible
-- DROP INDEX qwat_od.fki_valve_label_2_visible;
CREATE INDEX fki_valve_label_2_visible
ON qwat_od.valve
USING btree
(label_2_visible);
-- Index: qwat_od.fki_valve_schema_force_visible
-- DROP INDEX qwat_od.fki_valve_schema_force_visible;
CREATE INDEX fki_valve_schema_force_visible
ON qwat_od.valve
USING btree
(schema_force_visible);
-- Index: qwat_od.valve_geoidx
-- DROP INDEX qwat_od.valve_geoidx;
CREATE INDEX valve_geoidx
ON qwat_od.valve
USING gist
(geometry);
-- Index: qwat_od.valve_geoidx_alt1
-- DROP INDEX qwat_od.valve_geoidx_alt1;
CREATE INDEX valve_geoidx_alt1
ON qwat_od.valve
USING gist
(geometry_alt1);
-- Index: qwat_od.valve_geoidx_alt2
-- DROP INDEX qwat_od.valve_geoidx_alt2;
CREATE INDEX valve_geoidx_alt2
ON qwat_od.valve
USING gist
(geometry_alt2);
-- Trigger: audit_trigger_row on qwat_od.valve
-- DROP TRIGGER audit_trigger_row ON qwat_od.valve;
CREATE TRIGGER audit_trigger_row
AFTER INSERT OR UPDATE OR DELETE
ON qwat_od.valve
FOR EACH ROW
EXECUTE PROCEDURE qwat_sys.if_modified_func('true');
-- Trigger: audit_trigger_stm on qwat_od.valve
-- DROP TRIGGER audit_trigger_stm ON qwat_od.valve;
CREATE TRIGGER audit_trigger_stm
AFTER TRUNCATE
ON qwat_od.valve
FOR EACH STATEMENT
EXECUTE PROCEDURE qwat_sys.if_modified_func('true');
-- Trigger: tr_valve_add_pipe_vertex_insert on qwat_od.valve
-- DROP TRIGGER tr_valve_add_pipe_vertex_insert ON qwat_od.valve;
CREATE TRIGGER tr_valve_add_pipe_vertex_insert
AFTER INSERT
ON qwat_od.valve
FOR EACH ROW
EXECUTE PROCEDURE qwat_od.ft_valve_add_pipe_vertex();
COMMENT ON TRIGGER tr_valve_add_pipe_vertex_insert ON qwat_od.valve IS 'Trigger: updates auto fields after insert.';
-- Trigger: tr_valve_add_pipe_vertex_update on qwat_od.valve
-- DROP TRIGGER tr_valve_add_pipe_vertex_update ON qwat_od.valve;
CREATE TRIGGER tr_valve_add_pipe_vertex_update
AFTER UPDATE OF geometry
ON qwat_od.valve
FOR EACH ROW
WHEN ((st_equals(st_force2d(new.geometry), st_force2d(old.geometry)) IS FALSE))
EXECUTE PROCEDURE qwat_od.ft_valve_add_pipe_vertex();
COMMENT ON TRIGGER tr_valve_add_pipe_vertex_update ON qwat_od.valve IS 'Trigger: updates auto fields after geom update.';
-- Trigger: tr_valve_altgeom_alt on qwat_od.valve
-- DROP TRIGGER tr_valve_altgeom_alt ON qwat_od.valve;
CREATE TRIGGER tr_valve_altgeom_alt
BEFORE UPDATE OF geometry_alt1, geometry_alt2
ON qwat_od.valve
FOR EACH ROW
EXECUTE PROCEDURE qwat_od.ft_geometry_alternative_aux();
COMMENT ON TRIGGER tr_valve_altgeom_alt ON qwat_od.valve IS 'Trigger: when updating, check if alternative geometries are different to fill the boolean fields.';
-- Trigger: tr_valve_altgeom_insert on qwat_od.valve
-- DROP TRIGGER tr_valve_altgeom_insert ON qwat_od.valve;
CREATE TRIGGER tr_valve_altgeom_insert
BEFORE INSERT
ON qwat_od.valve
FOR EACH ROW
EXECUTE PROCEDURE qwat_od.ft_geometry_alternative_main();
COMMENT ON TRIGGER tr_valve_altgeom_insert ON qwat_od.valve IS 'Trigger: handle alternative geometries on insert';
-- Trigger: tr_valve_altgeom_update on qwat_od.valve
-- DROP TRIGGER tr_valve_altgeom_update ON qwat_od.valve;
CREATE TRIGGER tr_valve_altgeom_update
BEFORE UPDATE OF geometry
ON qwat_od.valve
FOR EACH ROW
WHEN ((st_equals(st_force2d(new.geometry), st_force2d(old.geometry)) IS FALSE))
EXECUTE PROCEDURE qwat_od.ft_geometry_alternative_main();
COMMENT ON TRIGGER tr_valve_altgeom_update ON qwat_od.valve IS 'Trigger: handle alternative geometries on update';
-- Trigger: tr_valve_infos_insert_trigger on qwat_od.valve
-- DROP TRIGGER tr_valve_infos_insert_trigger ON qwat_od.valve;
CREATE TRIGGER tr_valve_infos_insert_trigger
BEFORE INSERT
ON qwat_od.valve
FOR EACH ROW
EXECUTE PROCEDURE qwat_od.ft_valve_geom();
COMMENT ON TRIGGER tr_valve_infos_insert_trigger ON qwat_od.valve IS 'Trigger: when inserting a valve, assign pipe.';
-- Trigger: tr_valve_infos_update_trigger on qwat_od.valve
-- DROP TRIGGER tr_valve_infos_update_trigger ON qwat_od.valve;
CREATE TRIGGER tr_valve_infos_update_trigger
BEFORE UPDATE
ON qwat_od.valve
FOR EACH ROW
WHEN ((NOT st_equals(old.geometry, new.geometry)))
EXECUTE PROCEDURE qwat_od.ft_valve_geom();
COMMENT ON TRIGGER tr_valve_infos_update_trigger ON qwat_od.valve IS 'Trigger: when updating a valve, assign pipe.';
-- Trigger: tr_valve_schema_visible_insert on qwat_od.valve
-- DROP TRIGGER tr_valve_schema_visible_insert ON qwat_od.valve;
CREATE TRIGGER tr_valve_schema_visible_insert
BEFORE INSERT
ON qwat_od.valve
FOR EACH ROW
EXECUTE PROCEDURE qwat_od.ft_valve_schema_visible();
-- Trigger: tr_valve_schema_visible_update on qwat_od.valve
-- DROP TRIGGER tr_valve_schema_visible_update ON qwat_od.valve;
CREATE TRIGGER tr_valve_schema_visible_update
BEFORE UPDATE OF fk_valve_function
ON qwat_od.valve
FOR EACH ROW
EXECUTE PROCEDURE qwat_od.ft_valve_schema_visible();
-- Trigger: tr_valve_update_trigger on qwat_od.valve
-- DROP TRIGGER tr_valve_update_trigger ON qwat_od.valve;
CREATE TRIGGER tr_valve_update_trigger
AFTER UPDATE
ON qwat_od.valve
FOR EACH ROW
EXECUTE PROCEDURE qwat_od.ft_valve_update();
COMMENT ON TRIGGER tr_valve_update_trigger ON qwat_od.valve IS 'Trigger: when updating a valve, reevaluate old and new pipes.';
-- Trigger: valve_handle_altitude_insert_trigger on qwat_od.valve
-- DROP TRIGGER valve_handle_altitude_insert_trigger ON qwat_od.valve;
CREATE TRIGGER valve_handle_altitude_insert_trigger
BEFORE INSERT
ON qwat_od.valve
FOR EACH ROW
EXECUTE PROCEDURE qwat_od.ft_valve_handle_altitude();
COMMENT ON TRIGGER valve_handle_altitude_insert_trigger ON qwat_od.valve IS 'Trigger: when updating, check if altitude or Z value of geometry changed and synchronize them.';
-- Trigger: valve_handle_altitude_update_trigger on qwat_od.valve
-- DROP TRIGGER valve_handle_altitude_update_trigger ON qwat_od.valve;
CREATE TRIGGER valve_handle_altitude_update_trigger
BEFORE UPDATE OF handle_altitude, handle_geometry
ON qwat_od.valve
FOR EACH ROW
WHEN (((new.handle_altitude <> old.handle_altitude) OR (st_z(new.handle_geometry) <> st_z(old.handle_geometry))))
EXECUTE PROCEDURE qwat_od.ft_valve_handle_altitude();
COMMENT ON TRIGGER valve_handle_altitude_update_trigger ON qwat_od.valve IS 'Trigger: when updating, check if altitude or Z value of geometry changed and synchronize them.';
-- Trigger: valve_main_altitude_insert_trigger on qwat_od.valve
-- DROP TRIGGER valve_main_altitude_insert_trigger ON qwat_od.valve;
CREATE TRIGGER valve_main_altitude_insert_trigger
BEFORE INSERT
ON qwat_od.valve
FOR EACH ROW
EXECUTE PROCEDURE qwat_od.ft_valve_main_altitude();
COMMENT ON TRIGGER valve_main_altitude_insert_trigger ON qwat_od.valve IS 'Trigger: when updating, check if altitude or Z value of geometry changed and synchronize them.';
-- Trigger: valve_main_altitude_update_trigger on qwat_od.valve
-- DROP TRIGGER valve_main_altitude_update_trigger ON qwat_od.valve;
CREATE TRIGGER valve_main_altitude_update_trigger
BEFORE UPDATE OF altitude, geometry
ON qwat_od.valve
FOR EACH ROW
WHEN (((new.altitude <> old.altitude) OR (st_z(new.geometry) <> st_z(old.geometry))))
EXECUTE PROCEDURE qwat_od.ft_valve_main_altitude();
COMMENT ON TRIGGER valve_main_altitude_update_trigger ON qwat_od.valve IS 'Trigger: when updating, check if altitude or Z value of geometry changed and synchronize them.';
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment