<commit_before>-- Creates the db structure for outings tables ...
--
-- $Id: db_outings.sql 2247 2007-11-02 13:56:21Z alex $ --
--


-- Table app_outings_archives --
CREATE SEQUENCE outings_archives_seq INCREMENT BY 1 NO MAXVALUE MINVALUE 1 CACHE 1;

CREATE TABLE app_outings_archives (
    outing_archive_id integer DEFAULT nextval('outings_archives_seq'::text) NOT NULL,
    date date,
    activities smallint[],
    height_diff_up smallint,
    height_diff_down smallint,
    -- The 3 following fields are updated only when geom exists (via uploaded KML or GPX)
    outing_length integer, -- in meters. 
    min_elevation smallint, -- in meters
    max_elevation smallint, -- in meters
    partial_trip boolean,
    hut_status smallint,
    frequentation_status smallint,
    conditions_status smallint,
    access_status smallint,
    access_elevation smallint,
    lift_status smallint,
    glacier_status smallint,
    up_snow_elevation smallint,
    down_snow_elevation smallint,
    track_status smallint,
    outing_with_public_transportation boolean,
    v4_id smallint,
    v4_app varchar(3)
) INHERITS (app_documents_archives);

ALTER TABLE ONLY app_outings_archives ADD CONSTRAINT outings_archives_pkey PRIMARY KEY (outing_archive_id);
ALTER TABLE app_outings_archives ADD CONSTRAINT enforce_dims_geom CHECK (ndims(geom) = 4);
-- added here because indexes are not inherited from parent table (app_documents_archives):
CREATE INDEX app_outings_archives_id_idx ON app_outings_archives USING btree (id); 
CREATE INDEX app_outings_archives_geom_idx ON app_outings_archives USING GIST (geom GIST_GEOMETRY_OPS); 
CREATE INDEX app_outings_archives_redirects_idx ON app_outings_archives USING btree (redirects_to); -- useful for filtering on lists
CREATE INDEX app_outings_archives_v4_idx ON app_outings_archives USING btree (v4_id, v4_app); -- useful for filtering on v4 application (migrated contents)
CREATE INDEX app_outings_archives_latest_idx ON app_outings_archives USING btree (is_latest_version);
CREATE INDEX app_outings_archives_id_date_idx ON app_outings_archives USING btree (date, id);
CREATE INDEX app_outings_archives_document_archive_id_idx ON app_outings_archives USING btree (document_archive_id);

-- Table app_outings_i18n_archives --
CREATE SEQUENCE outings_i18n_archives_seq INCREMENT BY 1 NO MAXVALUE MINVALUE 1 CACHE 1;

CREATE TABLE app_outings_i18n_archives (
    outing_i18n_archive_id integer NOT NULL DEFAULT nextval('outings_i18n_archives_seq'::text),
    participants text,
    timing text,
    weather text,
    hut_comments text,
    access_comments text,
    conditions text,
    conditions_levels text
) INHERITS (app_documents_i18n_archives);

ALTER TABLE ONLY app_outings_i18n_archives ADD CONSTRAINT outings_i18n_archives_pkey PRIMARY KEY (outing_i18n_archive_id);
-- added here because indexes are not inherited from parent table (app_documents_i18n_archives):
CREATE INDEX app_outings_i18n_archives_id_culture_idx ON app_outings_i18n_archives USING btree (id, culture);
-- index for text search:
-- nb : be sure to search on the lowercased version of word typed, so that this index is useful.
CREATE INDEX app_outings_i18n_archives_name_idx ON app_outings_i18n_archives USING btree (search_name); 
CREATE INDEX app_outings_i18n_archives_latest_idx ON app_outings_i18n_archives USING btree (is_latest_version);
CREATE INDEX app_outings_i18n_archives_document_i18n_archive_id_idx ON app_outings_i18n_archives USING btree (document_i18n_archive_id);

-- Views --
-- mean elevation useful here
CREATE OR REPLACE VIEW outings AS SELECT sa.oid, sa.id, sa.lon, sa.lat, sa.elevation, sa.module, sa.is_protected, sa.redirects_to, sa.geom, sa.geom_wkt, sa.date, sa.activities, sa.height_diff_up, sa.height_diff_down, outing_length, min_elevation, max_elevation, sa.partial_trip, sa.hut_status, sa.frequentation_status, sa.conditions_status, sa.access_status, sa.access_elevation, sa.lift_status, sa.glacier_status, sa.up_snow_elevation, sa.down_snow_elevation, sa.track_status, sa.outing_with_public_transportation, sa.v4_id, sa.v4_app FROM app_outings_archives sa WHERE sa.is_latest_version; 

INSERT INTO "geometry_columns" VALUES ('','public','outings','geom',4,900913,'LINESTRING');

CREATE OR REPLACE VIEW outings_i18n AS SELECT sa.id, sa.culture, sa.name, sa.search_name, sa.description, sa.participants, sa.weather, sa.hut_comments, sa.access_comments, sa.conditions, sa.conditions_levels, sa.timing FROM app_outings_i18n_archives sa WHERE sa.is_latest_version;

-- Rules --

CREATE OR REPLACE RULE insert_outings AS ON INSERT TO outings DO INSTEAD 
(<commit_msg>- correction altitude max dans les sorties<commit_after>-- Creates the db structure for outings tables ...
--
-- $Id: db_outings.sql 2247 2007-11-02 13:56:21Z alex $ --
--


-- Table app_outings_archives --
CREATE SEQUENCE outings_archives_seq INCREMENT BY 1 NO MAXVALUE MINVALUE 1 CACHE 1;

CREATE TABLE app_outings_archives (
    outing_archive_id integer DEFAULT nextval('outings_archives_seq'::text) NOT NULL,
    date date,
    activities smallint[],
    height_diff_up smallint,
    height_diff_down smallint,
    -- The 3 following fields are updated only when geom exists (via uploaded KML or GPX)
    outing_length integer, -- in meters. 
    min_elevation smallint, -- in meters
    max_elevation smallint, -- in meters
    partial_trip boolean,
    hut_status smallint,
    frequentation_status smallint,
    conditions_status smallint,
    access_status smallint,
    access_elevation smallint,
    lift_status smallint,
    glacier_status smallint,
    up_snow_elevation smallint,
    down_snow_elevation smallint,
    track_status smallint,
    outing_with_public_transportation boolean,
    v4_id smallint,
    v4_app varchar(3)
) INHERITS (app_documents_archives);

ALTER TABLE ONLY app_outings_archives ADD CONSTRAINT outings_archives_pkey PRIMARY KEY (outing_archive_id);
ALTER TABLE app_outings_archives ADD CONSTRAINT enforce_dims_geom CHECK (ndims(geom) = 4);
-- added here because indexes are not inherited from parent table (app_documents_archives):
CREATE INDEX app_outings_archives_id_idx ON app_outings_archives USING btree (id); 
CREATE INDEX app_outings_archives_geom_idx ON app_outings_archives USING GIST (geom GIST_GEOMETRY_OPS); 
CREATE INDEX app_outings_archives_redirects_idx ON app_outings_archives USING btree (redirects_to); -- useful for filtering on lists
CREATE INDEX app_outings_archives_v4_idx ON app_outings_archives USING btree (v4_id, v4_app); -- useful for filtering on v4 application (migrated contents)
CREATE INDEX app_outings_archives_latest_idx ON app_outings_archives USING btree (is_latest_version);
CREATE INDEX app_outings_archives_id_date_idx ON app_outings_archives USING btree (date, id);
CREATE INDEX app_outings_archives_document_archive_id_idx ON app_outings_archives USING btree (document_archive_id);

-- Table app_outings_i18n_archives --
CREATE SEQUENCE outings_i18n_archives_seq INCREMENT BY 1 NO MAXVALUE MINVALUE 1 CACHE 1;

CREATE TABLE app_outings_i18n_archives (
    outing_i18n_archive_id integer NOT NULL DEFAULT nextval('outings_i18n_archives_seq'::text),
    participants text,
    timing text,
    weather text,
    hut_comments text,
    access_comments text,
    conditions text,
    conditions_levels text
) INHERITS (app_documents_i18n_archives);

ALTER TABLE ONLY app_outings_i18n_archives ADD CONSTRAINT outings_i18n_archives_pkey PRIMARY KEY (outing_i18n_archive_id);
-- added here because indexes are not inherited from parent table (app_documents_i18n_archives):
CREATE INDEX app_outings_i18n_archives_id_culture_idx ON app_outings_i18n_archives USING btree (id, culture);
-- index for text search:
-- nb : be sure to search on the lowercased version of word typed, so that this index is useful.
CREATE INDEX app_outings_i18n_archives_name_idx ON app_outings_i18n_archives USING btree (search_name); 
CREATE INDEX app_outings_i18n_archives_latest_idx ON app_outings_i18n_archives USING btree (is_latest_version);
CREATE INDEX app_outings_i18n_archives_document_i18n_archive_id_idx ON app_outings_i18n_archives USING btree (document_i18n_archive_id);

-- Views --
-- mean elevation useful here
CREATE OR REPLACE VIEW outings AS SELECT sa.oid, sa.id, sa.lon, sa.lat, sa.elevation, sa.module, sa.is_protected, sa.redirects_to, sa.geom, sa.geom_wkt, sa.date, sa.activities, sa.height_diff_up, sa.height_diff_down, sa.outing_length, min_elevation, sa.max_elevation, sa.partial_trip, sa.hut_status, sa.frequentation_status, sa.conditions_status, sa.access_status, sa.access_elevation, sa.lift_status, sa.glacier_status, sa.up_snow_elevation, sa.down_snow_elevation, sa.track_status, sa.outing_with_public_transportation, sa.v4_id, sa.v4_app FROM app_outings_archives sa WHERE sa.is_latest_version; 

INSERT INTO "geometry_columns" VALUES ('','public','outings','geom',4,900913,'LINESTRING');

CREATE OR REPLACE VIEW outings_i18n AS SELECT sa.id, sa.culture, sa.name, sa.search_name, sa.description, sa.participants, sa.weather, sa.hut_comments, sa.access_comments, sa.conditions, sa.conditions_levels, sa.timing FROM app_outings_i18n_archives sa WHERE sa.is_latest_version;

-- Rules --

CREATE OR REPLACE RULE insert_outings AS ON INSERT TO outings DO INSTEAD 
(
{'fraction_non_alphanumeric': 0.06260136230943886, 'fraction_numerical': 0.01859660503838253, 'mean_word_length': 5.751824817518248, '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': '1981824', 'n_tokens_mistral': 3318, 'n_tokens_neox': 3120, 'n_words': 824}