<commit_before>SET statement_timeout = 0;
SET lock_timeout = 0;
SET client_encoding = 'UTF8';
SET standard_conforming_strings = on;
SET check_function_bodies = false;
SET client_min_messages = warning;

--DROP SCHEMA suivi CASCADE;
CREATE SCHEMA gn_monitoring;

SET search_path = gn_monitoring, pg_catalog;
SET default_with_oids = false;

---------
--TABLE--
---------
CREATE TABLE t_base_sites
(
  id_base_site serial NOT NULL,
  id_inventor integer,
  id_digitiser integer,
  id_nomenclature_type_site integer NOT NULL,
  base_site_name character varying(255) NOT NULL,
  base_site_description text,
  base_site_code character varying(25) DEFAULT NULL::character varying,
  first_use_date date,
  geom public.geometry NOT NULL,
  meta_create_date timestamp without time zone DEFAULT now(),
  meta_update_date timestamp without time zone
);

CREATE TABLE t_base_visits
(
  id_base_visit serial NOT NULL,
  id_base_site integer,
  id_digitiser integer,
  visit_date date NOT NULL,
  geom public.geometry,
  comments text,
  meta_create_date timestamp without time zone DEFAULT now(),
  meta_update_date timestamp without time zone
);

CREATE TABLE cor_visit_observer
(
  id_base_visit integer NOT NULL,
  id_role integer NOT NULL
);

CREATE TABLE cor_site_application (
  id_base_site integer NOT NULL,
  id_application integer NOT NULL
);


---------------
--PRIMARY KEY--
---------------
ALTER TABLE ONLY t_base_sites
    ADD CONSTRAINT pk_t_base_sites PRIMARY KEY (id_base_site);

ALTER TABLE ONLY t_base_visits
    ADD CONSTRAINT pk_t_base_visits PRIMARY KEY (id_base_visit);

ALTER TABLE ONLY cor_visit_observer
    ADD CONSTRAINT pk_cor_visit_observer PRIMARY KEY (id_base_visit, id_role);

ALTER TABLE ONLY cor_site_application
    ADD CONSTRAINT pk_cor_site_application PRIMARY KEY (id_base_site, id_application);


---------------
--FOREIGN KEY--
---------------
ALTER TABLE ONLY t_base_sites
  ADD CONSTRAINT  fk_t_base_sites_id_inventor FOREIGN KEY (id_inventor) REFERENCES utilisateurs.t_roles (id_role) ON UPDATE CASCADE;

ALTER TABLE ONLY t_base_sites
    ADD CONSTRAINT fk_t_base_sites_type_site FOREIGN KEY (id_nomenclature_type_site) REFERENCES ref_nomenclatures.t_nomenclatures(id_nomenclature) ON UPDATE CASCADE;

ALTER TABLE ONLY t_base_sites
    ADD CONSTRAINT fk_t_base_sites_id_digitiser FOREIGN KEY (id_digitiser) REFERENCES utilisateurs.t_roles(id_role) ON UPDATE CASCADE;


ALTER TABLE ONLY t_base_visits
  ADD CONSTRAINT  fk_t_base_visits_id_base_site FOREIGN KEY (id_base_site) REFERENCES t_base_sites (id_base_site) ON UPDATE NO ACTION ON DELETE CASCADE;

ALTER TABLE ONLY t_base_visits
    ADD CONSTRAINT fk_t_base_visits_id_digitiser FOREIGN KEY (id_digitiser) REFERENCES utilisateurs.t_roles(id_role) ON UPDATE CASCADE;


ALTER TABLE ONLY cor_visit_observer
  ADD CONSTRAINT fk_cor_visit_observer_id_base_visit FOREIGN KEY (id_base_visit) REFERENCES t_base_visits (id_base_visit) ON UPDATE CASCADE ON DELETE CASCADE;

ALTER TABLE ONLY cor_visit_observer
  ADD CONSTRAINT fk_cor_visit_observer_id_role FOREIGN KEY (id_role) REFERENCES utilisateurs.t_roles (id_role) ON UPDATE CASCADE;


ALTER TABLE ONLY cor_site_application
  ADD CONSTRAINT fk_cor_site_application_id_base_site FOREIGN KEY (id_base_site) REFERENCES t_base_sites (id_base_site) ON UPDATE CASCADE ON DELETE CASCADE;

ALTER TABLE ONLY cor_site_application
  ADD CONSTRAINT fk_cor_site_application_id_application FOREIGN KEY (id_application) REFERENCES utilisateurs.t_applications (id_application);


--------------
--CONSTRAINS--
--------------
ALTER TABLE t_base_sites
  ADD CONSTRAINT enforce_srid_geom CHECK ((public.st_srid(geom) = 4326));

ALTER TABLE t_base_sites
  ADD CONSTRAINT enforce_dims_geom CHECK ((public.st_ndims(geom) = 2));

ALTER TABLE t_base_sites
  ADD CONSTRAINT check_t_base_sites_type_site CHECK (ref_nomenclatures.check_nomenclature_type(id_nomenclature_type_site,116));<commit_msg>SQL MONITORING - ajout de la correspondance cor_site_area<commit_after>SET statement_timeout = 0;
SET lock_timeout = 0;
SET client_encoding = 'UTF8';
SET standard_conforming_strings = on;
SET check_function_bodies = false;
SET client_min_messages = warning;

--DROP SCHEMA suivi CASCADE;
CREATE SCHEMA gn_monitoring;

SET search_path = gn_monitoring, pg_catalog;
SET default_with_oids = false;

---------
--TABLE--
---------
CREATE TABLE t_base_sites
(
  id_base_site serial NOT NULL,
  id_inventor integer,
  id_digitiser integer,
  id_nomenclature_type_site integer NOT NULL,
  base_site_name character varying(255) NOT NULL,
  base_site_description text,
  base_site_code character varying(25) DEFAULT NULL::character varying,
  first_use_date date,
  geom public.geometry NOT NULL,
  meta_create_date timestamp without time zone DEFAULT now(),
  meta_update_date timestamp without time zone
);

CREATE TABLE t_base_visits
(
  id_base_visit serial NOT NULL,
  id_base_site integer,
  id_digitiser integer,
  visit_date date NOT NULL,
  geom public.geometry,
  comments text,
  meta_create_date timestamp without time zone DEFAULT now(),
  meta_update_date timestamp without time zone
);

CREATE TABLE cor_visit_observer
(
  id_base_visit integer NOT NULL,
  id_role integer NOT NULL
);

CREATE TABLE cor_site_application (
  id_base_site integer NOT NULL,
  id_application integer NOT NULL
);

CREATE TABLE cor_site_area (
  id_base_site integer NOT NULL,
  id_area integer NOT NULL
);


---------------
--PRIMARY KEY--
---------------
ALTER TABLE ONLY t_base_sites
    ADD CONSTRAINT pk_t_base_sites PRIMARY KEY (id_base_site);

ALTER TABLE ONLY t_base_visits
    ADD CONSTRAINT pk_t_base_visits PRIMARY KEY (id_base_visit);

ALTER TABLE ONLY cor_visit_observer
    ADD CONSTRAINT pk_cor_visit_observer PRIMARY KEY (id_base_visit, id_role);

ALTER TABLE ONLY cor_site_application
    ADD CONSTRAINT pk_cor_site_application PRIMARY KEY (id_base_site, id_application);

ALTER TABLE ONLY cor_site_area
    ADD CONSTRAINT pk_cor_site_area PRIMARY KEY (id_base_site, id_area);


---------------
--FOREIGN KEY--
---------------
ALTER TABLE ONLY t_base_sites
  ADD CONSTRAINT  fk_t_base_sites_id_inventor FOREIGN KEY (id_inventor) REFERENCES utilisateurs.t_roles (id_role) ON UPDATE CASCADE;

ALTER TABLE ONLY t_base_sites
    ADD CONSTRAINT fk_t_base_sites_type_site FOREIGN KEY (id_nomenclature_type_site) REFERENCES ref_nomenclatures.t_nomenclatures(id_nomenclature) ON UPDATE CASCADE;

ALTER TABLE ONLY t_base_sites
    ADD CONSTRAINT fk_t_base_sites_id_digitiser FOREIGN KEY (id_digitiser) REFERENCES utilisateurs.t_roles(id_role) ON UPDATE CASCADE;


ALTER TABLE ONLY t_base_visits
  ADD CONSTRAINT  fk_t_base_visits_id_base_site FOREIGN KEY (id_base_site) REFERENCES t_base_sites (id_base_site) ON UPDATE NO ACTION ON DELETE CASCADE;

ALTER TABLE ONLY t_base_visits
    ADD CONSTRAINT fk_t_base_visits_id_digitiser FOREIGN KEY (id_digitiser) REFERENCES utilisateurs.t_roles(id_role) ON UPDATE CASCADE;


ALTER TABLE ONLY cor_visit_observer
  ADD CONSTRAINT fk_cor_visit_observer_id_base_visit FOREIGN KEY (id_base_visit) REFERENCES t_base_visits (id_base_visit) ON UPDATE CASCADE ON DELETE CASCADE;

ALTER TABLE ONLY cor_visit_observer
  ADD CONSTRAINT fk_cor_visit_observer_id_role FOREIGN KEY (id_role) REFERENCES utilisateurs.t_roles (id_role) ON UPDATE CASCADE;


ALTER TABLE ONLY cor_site_application
  ADD CONSTRAINT fk_cor_site_application_id_base_site FOREIGN KEY (id_base_site) REFERENCES t_base_sites (id_base_site) ON UPDATE CASCADE ON DELETE CASCADE;

ALTER TABLE ONLY cor_site_application
  ADD CONSTRAINT fk_cor_site_application_id_application FOREIGN KEY (id_application) REFERENCES utilisateurs.t_applications (id_application);


ALTER TABLE ONLY cor_site_area
  ADD CONSTRAINT fk_cor_site_area_id_base_site FOREIGN KEY (id_base_site) REFERENCES t_base_sites (id_base_site) ON UPDATE CASCADE ON DELETE CASCADE;

ALTER TABLE ONLY cor_site_area
  ADD CONSTRAINT fk_cor_site_area_id_area FOREIGN KEY (id_area) REFERENCES ref_geo.l_areas (id_area);


--------------
--CONSTRAINS--
--------------
ALTER TABLE t_base_sites
  ADD CONSTRAINT enforce_srid_geom CHECK ((public.st_srid(geom) = 4326));

ALTER TABLE t_base_sites
  ADD CONSTRAINT enforce_dims_geom CHECK ((public.st_ndims(geom) = 2));

ALTER TABLE t_base_sites
  ADD CONSTRAINT check_t_base_sites_type_site CHECK (ref_nomenclatures.check_nomenclature_type(id_nomenclature_type_site,116));
{'fraction_non_alphanumeric': 0.06957561267184698, 'fraction_numerical': 0.003825463239689181, 'mean_word_length': 5.18329637841833, 'pattern_counts': {'":': 0, '<': 3, '<?xml version=': 0, '>': 3, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 0, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'dir': 'git-commits-cleaned', 'id': '4519233', 'n_tokens_mistral': 3097, 'n_tokens_neox': 2861, 'n_words': 765}