<commit_before>DO $$
    BEGIN
        BEGIN
            ALTER TABLE realtime.message ADD COLUMN is_active BOOLEAN NOT NULL DEFAULT TRUE;
        EXCEPTION
            WHEN duplicate_column THEN RAISE NOTICE 'column is_active already exists in realtime.message.';
        END;
    END;
$$;

DO $$
    BEGIN
        BEGIN
            ALTER TABLE realtime.at_perturbation ADD COLUMN is_active BOOLEAN NOT NULL DEFAULT TRUE;
        EXCEPTION
            WHEN duplicate_column THEN RAISE NOTICE 'column is_active already exists in realtime.at_perturbation.';
        END;
    END;
$$;

DO $$
    BEGIN
        BEGIN
            ALTER TABLE realtime.message ADD COLUMN message_status_id int NOT NULL REFERENCES realtime.message_status;
        EXCEPTION
            WHEN duplicate_column THEN RAISE NOTICE 'column message_status_id already exists in realtime.message.';
        END;
    END;
$$;

DO $$
    BEGIN
        BEGIN
            ALTER TABLE navitia.connection ADD COLUMN display_duration integer NOT NULL DEFAULT 0;
        EXCEPTION
            WHEN duplicate_column THEN RAISE NOTICE 'column display_duration already exists in navitia.connection.';
        END;
    END;
$$;

CREATE OR REPLACE FUNCTION add_external_code(tblname varchar) RETURNS VOID AS $BODY$
    BEGIN
        EXECUTE format('ALTER TABLE navitia.%I ADD COLUMN external_code TEXT;', tblname);
        EXECUTE format('UPDATE navitia.%I SET external_code = uri;', tblname);
        EXECUTE format('ALTER TABLE navitia.%I ALTER external_code SET NOT NULL', tblname);
    EXCEPTION
        WHEN duplicate_column THEN RAISE NOTICE 'columun external_code already exists in %', $1;
    END;
$BODY$ LANGUAGE plpgsql;

DO
$BODY$
DECLARE
    m varchar;
    arr varchar[] = array['network', 'line', 'route', 'stop_area', 'stop_point', 'vehicle_journey'];
BEGIN
        FOREACH m IN ARRAY arr
        LOOP
            PERFORM add_external_code(m);
        END LOOP;
END;
$BODY$ LANGUAGE plpgsql;


DO $$
    DECLARE count_multi int;
BEGIN
    count_multi := coalesce((select count(*) from georef.admin where geometrytype(boundary::geometry) = 'MULTIPOLYGON' limit 1), 0);
    CASE WHEN count_multi = 0 
        THEN
            -- Ajout d'une colonne temporaire
            ALTER TABLE georef.admin ADD COLUMN boundary_tmp GEOGRAPHY(POLYGON, 4326);
            -- Déplacer le contenu de la colonne boundary dans boundary_tmp
            UPDATE georef.admin set boundary_tmp=boundary;
            -- Vidage de la colonne boundary
            UPDATE georef.admin  SET boundary = NULL;
            -- Changement du type de boundary
            ALTER TABLE georef.admin ALTER COLUMN boundary TYPE GEOGRAPHY(MULTIPOLYGON, 4326);
            -- Déplacer le contenu de la colonne boundary_tmp dans boundary
            UPDATE georef.admin  SET boundary = ST_Multi(st_astext(boundary_tmp));
            -- Suppression de la colonne temporaire
            ALTER TABLE georef.admin DROP COLUMN boundary_tmp;
    ELSE
        RAISE NOTICE 'column boundary already type MULTIPOLYGON, skipping';
    END CASE;
END
$$;

DO $$
    BEGIN
        BEGIN
            ALTER TABLE navitia.line ADD COLUMN sort integer NOT NULL DEFAULT 2147483647;
        EXCEPTION
            WHEN duplicate_column THEN RAISE NOTICE 'column sort already exists in navitia.line.';
        END;
    END;
$$;

DO $$
    BEGIN
        BEGIN
            ALTER TABLE navitia.network ADD COLUMN sort integer NOT NULL DEFAULT 2147483647;
        EXCEPTION
            WHEN duplicate_column THEN RAISE NOTICE 'column sort already exists in navitia.network.';
        END;
    END;
$$;

DO $$
    BEGIN
        BEGIN
            ALTER TABLE navitia.network ADD COLUMN website TEXT;
        EXCEPTION
            WHEN duplicate_column THEN RAISE NOTICE 'column website already exists in navitia.network.';
        END;
    END;
$$;

DO $$
    BEGIN
        BEGIN
            ALTER TABLE navitia.stop_area ADD visible BOOLEAN NOT NULL DEFAULT True;
        EXCEPTION
            WHEN duplicate_column THEN RAISE NOTICE 'column visible already exists in navitia.stop_area.';
        END;
    END;
$$;

DO $$
    BEGIN
        BEGIN
            ALTER TABLE georef.poi ADD COLUMN visible BOOLEAN NOT NULL DEFAULT True;
        EXCEPTION
            WHEN duplicate_column THEN RAISE NOTICE 'column visible already exists in navitia.poi.';
        END;
    END;
$$;

DO $$
    BEGIN
        BEGIN
            ALTER TABLE georef.poi ADD COLUMN address_name TEXT;
        EXCEPTION
            WHEN duplicate_column THEN RAISE NOTICE 'column address_name already exists in navitia.poi.';
        END;
    END;
$$;

DO $$
    BEGIN
        BEGIN
            ALTER TABLE georef.poi ADD COLUMN address_number TEXT;
        EXCEPTION
            WHEN duplicate_column THEN RAISE NOTICE 'column address_number already exists in navitia.poi.';
        END;
    END;
$$;

DO $$
    DECLARE count_admin int;
BEGIN
    count_admin := coalesce((select  count(*) from  pg_catalog.pg_tables where schemaname = 'navitia' and tablename='admin'), 0);
    CASE WHEN count_admin > 0 
        THEN
			INSERT INTO georef.admin SELECT * FROM navitia.admin;
			INSERT INTO georef.rel_admin_admin SELECT * FROM navitia.rel_admin_admin;
    ELSE
        RAISE NOTICE 'table admin already exists, skipping';
    END CASE;
END
$$;

DO $$
    DECLARE count_poi int;
BEGIN
    count_poi := coalesce((select  count(*) from  pg_catalog.pg_tables where schemaname = 'navitia' and tablename='poi'), 0);
    CASE WHEN count_poi > 0 
        THEN
			INSERT INTO georef.poi_type SELECT * FROM navitia.poi_type;
			INSERT INTO georef.poi SELECT * FROM navitia.poi;
			INSERT INTO georef.poi_properties SELECT * FROM navitia.poi_properties;
    ELSE
        RAISE NOTICE 'table poi already exists, skipping';
    END CASE;
END
$$;

DO $$
    DECLARE count_synonym int;
BEGIN
    count_synonym := coalesce((select  count(*) from  pg_catalog.pg_tables where schemaname = 'navitia' and tablename='synonym'), 0);
    CASE WHEN count_synonym > 0 
        THEN
			INSERT INTO georef.synonym SELECT * FROM navitia.synonym;
    ELSE
        RAISE NOTICE 'table synonym already exists, skipping';
    END CASE;
END
$$;
DROP TABLE IF EXISTS navitia.admin CASCADE;	
DROP TABLE IF EXISTS navitia.rel_admin_admin CASCADE;
DROP TABLE IF EXISTS navitia.poi CASCADE;			
DROP TABLE IF EXISTS navitia.synonym;
			
DROP TABLE IF EXISTS navitia.alias;
DROP TABLE IF EXISTS navitia.rel_stop_point_admin;
DROP TABLE IF EXISTS navitia.rel_stop_area_admin;
DROP TABLE IF EXISTS navitia.rel_poi_admin;
DROP FUNCTION IF EXISTS georef.match_stop_area_to_admin();
DROP FUNCTION IF EXISTS georef.match_stop_point_to_admin();
DROP FUNCTION IF EXISTS georef.update_admin_coord();
DROP FUNCTION IF EXISTS georef.match_admin_to_admin();
<commit_msg>ed : move poi table in georef schema<commit_after>DO $$
    BEGIN
        BEGIN
            ALTER TABLE realtime.message ADD COLUMN is_active BOOLEAN NOT NULL DEFAULT TRUE;
        EXCEPTION
            WHEN duplicate_column THEN RAISE NOTICE 'column is_active already exists in realtime.message.';
        END;
    END;
$$;

DO $$
    BEGIN
        BEGIN
            ALTER TABLE realtime.at_perturbation ADD COLUMN is_active BOOLEAN NOT NULL DEFAULT TRUE;
        EXCEPTION
            WHEN duplicate_column THEN RAISE NOTICE 'column is_active already exists in realtime.at_perturbation.';
        END;
    END;
$$;

DO $$
    BEGIN
        BEGIN
            ALTER TABLE realtime.message ADD COLUMN message_status_id int NOT NULL REFERENCES realtime.message_status;
        EXCEPTION
            WHEN duplicate_column THEN RAISE NOTICE 'column message_status_id already exists in realtime.message.';
        END;
    END;
$$;

DO $$
    BEGIN
        BEGIN
            ALTER TABLE navitia.connection ADD COLUMN display_duration integer NOT NULL DEFAULT 0;
        EXCEPTION
            WHEN duplicate_column THEN RAISE NOTICE 'column display_duration already exists in navitia.connection.';
        END;
    END;
$$;

CREATE OR REPLACE FUNCTION add_external_code(tblname varchar) RETURNS VOID AS $BODY$
    BEGIN
        EXECUTE format('ALTER TABLE navitia.%I ADD COLUMN external_code TEXT;', tblname);
        EXECUTE format('UPDATE navitia.%I SET external_code = uri;', tblname);
        EXECUTE format('ALTER TABLE navitia.%I ALTER external_code SET NOT NULL', tblname);
    EXCEPTION
        WHEN duplicate_column THEN RAISE NOTICE 'columun external_code already exists in %', $1;
    END;
$BODY$ LANGUAGE plpgsql;

DO
$BODY$
DECLARE
    m varchar;
    arr varchar[] = array['network', 'line', 'route', 'stop_area', 'stop_point', 'vehicle_journey'];
BEGIN
        FOREACH m IN ARRAY arr
        LOOP
            PERFORM add_external_code(m);
        END LOOP;
END;
$BODY$ LANGUAGE plpgsql;


DO $$
    DECLARE count_multi int;
BEGIN
    count_multi := coalesce((select count(*) from georef.admin where geometrytype(boundary::geometry) = 'MULTIPOLYGON' limit 1), 0);
    CASE WHEN count_multi = 0 
        THEN
            -- Ajout d'une colonne temporaire
            ALTER TABLE georef.admin ADD COLUMN boundary_tmp GEOGRAPHY(POLYGON, 4326);
            -- Déplacer le contenu de la colonne boundary dans boundary_tmp
            UPDATE georef.admin set boundary_tmp=boundary;
            -- Vidage de la colonne boundary
            UPDATE georef.admin  SET boundary = NULL;
            -- Changement du type de boundary
            ALTER TABLE georef.admin ALTER COLUMN boundary TYPE GEOGRAPHY(MULTIPOLYGON, 4326);
            -- Déplacer le contenu de la colonne boundary_tmp dans boundary
            UPDATE georef.admin  SET boundary = ST_Multi(st_astext(boundary_tmp));
            -- Suppression de la colonne temporaire
            ALTER TABLE georef.admin DROP COLUMN boundary_tmp;
    ELSE
        RAISE NOTICE 'column boundary already type MULTIPOLYGON, skipping';
    END CASE;
END
$$;

DO $$
    BEGIN
        BEGIN
            ALTER TABLE navitia.line ADD COLUMN sort integer NOT NULL DEFAULT 2147483647;
        EXCEPTION
            WHEN duplicate_column THEN RAISE NOTICE 'column sort already exists in navitia.line.';
        END;
    END;
$$;

DO $$
    BEGIN
        BEGIN
            ALTER TABLE navitia.network ADD COLUMN sort integer NOT NULL DEFAULT 2147483647;
        EXCEPTION
            WHEN duplicate_column THEN RAISE NOTICE 'column sort already exists in navitia.network.';
        END;
    END;
$$;

DO $$
    BEGIN
        BEGIN
            ALTER TABLE navitia.network ADD COLUMN website TEXT;
        EXCEPTION
            WHEN duplicate_column THEN RAISE NOTICE 'column website already exists in navitia.network.';
        END;
    END;
$$;

DO $$
    BEGIN
        BEGIN
            ALTER TABLE navitia.stop_area ADD visible BOOLEAN NOT NULL DEFAULT True;
        EXCEPTION
            WHEN duplicate_column THEN RAISE NOTICE 'column visible already exists in navitia.stop_area.';
        END;
    END;
$$;

DO $$
    BEGIN
        BEGIN
            ALTER TABLE georef.poi ADD COLUMN visible BOOLEAN NOT NULL DEFAULT True;
        EXCEPTION
            WHEN duplicate_column THEN RAISE NOTICE 'column visible already exists in navitia.poi.';
        END;
    END;
$$;

DO $$
    BEGIN
        BEGIN
            ALTER TABLE georef.poi ADD COLUMN address_name TEXT;
        EXCEPTION
            WHEN duplicate_column THEN RAISE NOTICE 'column address_name already exists in navitia.poi.';
        END;
    END;
$$;

DO $$
    BEGIN
        BEGIN
            ALTER TABLE georef.poi ADD COLUMN address_number TEXT;
        EXCEPTION
            WHEN duplicate_column THEN RAISE NOTICE 'column address_number already exists in navitia.poi.';
        END;
    END;
$$;

DO $$
    DECLARE count_admin int;
BEGIN
    count_admin := coalesce((select  count(*) from  pg_catalog.pg_tables where schemaname = 'navitia' and tablename='admin'), 0);
    CASE WHEN count_admin > 0 
        THEN
			INSERT INTO georef.admin SELECT * FROM navitia.admin;
			INSERT INTO georef.rel_admin_admin SELECT * FROM navitia.rel_admin_admin;
    ELSE
        RAISE NOTICE 'table admin already exists, skipping';
    END CASE;
END
$$;

DO $$
    DECLARE count_poi int;
BEGIN
    count_poi := coalesce((select  count(*) from  pg_catalog.pg_tables where schemaname = 'navitia' and tablename='poi'), 0);
    CASE WHEN count_poi > 0
        THEN
			INSERT INTO georef.poi_type SELECT * FROM navitia.poi_type;
			insert into georef.poi (id,weight,coord,name,uri,visible,poi_type_id,address_name,address_number)
			select id,weight,coord,name,uri,visible,poi_type_id,address_name,address_number from navitia.poi;
			INSERT INTO georef.poi_properties SELECT * FROM navitia.poi_properties;
    ELSE
        RAISE NOTICE 'table poi already exists, skipping';
    END CASE;
END
$$;

DO $$
    DECLARE count_synonym int;
BEGIN
    count_synonym := coalesce((select  count(*) from  pg_catalog.pg_tables where schemaname = 'navitia' and tablename='synonym'), 0);
    CASE WHEN count_synonym > 0 
        THEN
			INSERT INTO georef.synonym SELECT * FROM navitia.synonym;
    ELSE
        RAISE NOTICE 'table synonym already exists, skipping';
    END CASE;
END
$$;
DROP TABLE IF EXISTS navitia.admin CASCADE;	
DROP TABLE IF EXISTS navitia.rel_admin_admin CASCADE;
DROP TABLE IF EXISTS navitia.poi CASCADE;			
DROP TABLE IF EXISTS navitia.synonym;
			
DROP TABLE IF EXISTS navitia.alias;
DROP TABLE IF EXISTS navitia.rel_stop_point_admin;
DROP TABLE IF EXISTS navitia.rel_stop_area_admin;
DROP TABLE IF EXISTS navitia.rel_poi_admin;
DROP FUNCTION IF EXISTS georef.match_stop_area_to_admin();
DROP FUNCTION IF EXISTS georef.match_stop_point_to_admin();
DROP FUNCTION IF EXISTS georef.update_admin_coord();
DROP FUNCTION IF EXISTS georef.match_admin_to_admin();

{'fraction_non_alphanumeric': 0.06648723357823526, 'fraction_numerical': 0.005673965228777188, 'mean_word_length': 2.6710280373831776, 'pattern_counts': {'":': 0, '<': 3, '<?xml version=': 0, '>': 9, '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': '7366788', 'n_tokens_mistral': 4542, 'n_tokens_neox': 4169, 'n_words': 1364}