CREATE EXTENSION mysql_fdw; CREATE SERVER mysql_svr FOREIGN DATA WRAPPER mysql_fdw OPTIONS (address '145.238.193.14', port '3306'); -- use a mysql user that have select right CREATE USER MAPPING FOR PUBLIC SERVER mysql_svr OPTIONS (username 'visite', password 'visite'); DROP SCHEMA hf1t3 cascade; CREATE SCHEMA hf1t3; CREATE FOREIGN TABLE hf1t3.view_t3_hqi ( "INSTITUT" character varying(150) NOT NULL, "CODE" character varying(100) NOT NULL, "VERSION" character varying(50) NOT NULL, "FEATURE_NAME" character varying(100) NOT NULL, "ENC_MET" character varying(50), "PERSON" character varying(150), "CONTACT" character varying(150) NOT NULL, "REFERENCE" character varying(150), "OBSERVAT" character varying(255) NOT NULL, "INSTRUME" character varying(150) NOT NULL, "TELESCOP" character varying(150), "OBSINST_KEY" character varying(150), "UNITS" character varying(100) NOT NULL, "WAVEMIN" real, "WAVEMAX" real, "WAVENAME" character varying(50), "WAVEUNIT" character varying(10), "FREQMIN" real, "FREQMAX" real, "SPECTRAL_DOMAIN" character varying(100), "OBS_TYPE" character varying(50), "OBS_CAT" character varying(50), "DATE_OBS" timestamp without time zone NOT NULL, "DATE_END" timestamp without time zone NOT NULL, "JDINT" integer, "JDFRAC" double precision, "C_ROTATION" integer, "FILENAME" character varying(100) NOT NULL, "URL" text, "CDELT1" double precision NOT NULL, "CDELT2" double precision NOT NULL, "NAXIS1" integer, "NAXIS2" integer, "CENTER_X" double precision, "CENTER_Y" double precision, "FILE_FORMAT" character varying(20), "DATA_TYPE" character varying(50), "QCLK_URL" text, "QCLK_FNAME" character varying(200), "ID_TYPE_III" integer, "FEAT_MAX_INT" float, "FEAT_MEAN_INT" float, "MULTIPLE" float, "FEAT_FILENAME" character varying(150) NOT NULL, "SNAPSHOT" character varying(400), "CC_X_PIX" integer NOT NULL, "CC_Y_PIX" integer NOT NULL, "CC_X_UTC" double precision NOT NULL, "CC_Y_MHZ" double precision NOT NULL, "CC" text NOT NULL, "CC_LENGTH" integer, "SKE_CC_X_PIX" integer NOT NULL, "SKE_CC_Y_PIX" integer NOT NULL, "SKE_CC_X_UTC" double precision NOT NULL, "SKE_CC_Y_MHZ" double precision NOT NULL, "SKE_CC" text NOT NULL, "SKE_CC_LENGTH" integer NOT NULL, "BR_X0_PIX" integer, "BR_Y0_PIX" integer, "BR_X1_PIX" integer, "BR_Y1_PIX" integer, "BR_X2_PIX" integer, "BR_Y2_PIX" integer, "BR_X3_PIX" integer, "BR_Y3_PIX" integer, "BR_X0_UTC" double precision NOT NULL, "BR_Y0_MHZ" double precision NOT NULL, "BR_X1_UTC" double precision NOT NULL, "BR_Y1_MHZ" double precision NOT NULL, "BR_X2_UTC" double precision NOT NULL, "BR_Y2_MHZ" double precision NOT NULL, "BR_X3_UTC" double precision NOT NULL, "BR_Y3_MHZ" double precision NOT NULL, "TIME_START" timestamp without time zone NOT NULL, "TIME_END" timestamp without time zone NOT NULL, "DRIFT_START" real NOT NULL, "DRIFT_END" real NOT NULL, "FIT_A0" real NOT NULL, "FIT_A1" real NOT NULL, "LVL_TRUST" integer ) SERVER mysql_svr options (query 'select INSTITUT, CODE, VERSION, FEATURE_NAME, ENC_MET, PERSON, CONTACT, REFERENCE, OBSERVAT, INSTRUME, TELESCOP, OBSINST_KEY, UNITS, WAVEMIN, WAVEMAX, WAVENAME, WAVEUNIT, FREQMIN, FREQMAX, SPECTRAL_DOMAIN, OBS_TYPE, OBS_CAT, DATE_OBS, DATE_END, JDINT, JDFRAC, C_ROTATION, FILENAME, URL, CDELT1, CDELT2, NAXIS1, NAXIS2, CENTER_X, CENTER_Y, FILE_FORMAT, DATA_TYPE, QCLK_URL, QCLK_FNAME, ID_TYPE_III, FEAT_MAX_INT, FEAT_MEAN_INT, MULTIPLE, FEAT_FILENAME, SNAPSHOT, CC_X_PIX, CC_Y_PIX, CC_X_UTC, CC_Y_MHZ, CC, CC_LENGTH, SKE_CC_X_PIX, SKE_CC_Y_PIX, SKE_CC_X_UTC, SKE_CC_Y_MHZ, SKE_CC, SKE_CC_LENGTH, BR_X0_PIX, BR_Y0_PIX, BR_X1_PIX, BR_Y1_PIX, BR_X2_PIX, BR_Y2_PIX, BR_X3_PIX, BR_Y3_PIX, BR_X0_UTC, BR_Y0_MHZ, BR_X1_UTC, BR_Y1_MHZ, BR_X2_UTC, BR_Y2_MHZ, BR_X3_UTC, BR_Y3_MHZ, TIME_START, TIME_END, DRIFT_START, DRIFT_END, FIT_A0, FIT_A1, LVL_TRUST from hfc1.VIEW_T3_HQI'); drop sequence epn_core_seqt3; create sequence epn_core_seqt3; CREATE MATERIALIZED VIEW hf1t3.epn_core AS SELECT TEXT 'granule' AS resource_type, TEXT '1' AS dataset_id, -- ROW_NUMBER() OVER (ORDER BY resource_type) AS index2, nextval('epn_core_seqt3'::regclass) AS index, -- generate_series(1, select count(*) from view_ar_hqi) AS index, CASE "DATA_TYPE" WHEN 'IMAGE' THEN CAST('im' AS TEXT) WHEN 'SPECTRUM' THEN CAST('ds' AS TEXT) WHEN 'DYNAMIC SPECTRUM' THEN CAST('ds' AS TEXT) WHEN 'SPECTROGRAM' THEN CAST('sp' AS TEXT) ELSE CAST(NULL AS TEXT) END AS dataproduct_type, TEXT 'sun' AS target_name, TEXT 'star' AS target_class, cast(to_char("DATE_OBS"::timestamp at time zone 'UTC','J') as double precision) + EXTRACT(EPOCH FROM "DATE_OBS"::time)/86400 -0.5 AS time_min, cast(to_char("DATE_END"::timestamp at time zone 'UTC','J') as double precision) + EXTRACT(EPOCH FROM "DATE_END"::time)/86400 -0.5 AS time_max, -- CAST(to_char(to_timestamp(a."DATE_OBS", 'YYYY-MM-DD HH:MI:SS')::date,'J') AS double precision) AS time_min, -- CAST(to_char(to_timestamp(a."DATE_END", 'YYYY-MM-DD HH:MI:SS')::date,'J') AS double precision) AS time_max, CAST(NULL AS DOUBLE PRECISION) AS time_sampling_step_min, CAST(NULL AS DOUBLE PRECISION) AS time_sampling_step_max, CAST(NULL AS DOUBLE PRECISION) AS time_exp_min, CAST(NULL AS DOUBLE PRECISION) AS time_exp_max, CAST((299792458E9/"WAVEMAX") AS DOUBLE PRECISION) AS spectral_range_min, CAST((299792458E9/"WAVEMIN") AS DOUBLE PRECISION) AS spectral_range_max, CAST(NULL AS DOUBLE PRECISION) AS spectral_sampling_step_min, CAST(NULL AS DOUBLE PRECISION) AS spectral_sampling_step_max, CAST(NULL AS DOUBLE PRECISION) AS spectral_resolution_min, CAST(NULL AS DOUBLE PRECISION) AS spectral_resolution_max, CAST(NULL AS DOUBLE PRECISION) AS c1min, CAST(NULL AS DOUBLE PRECISION) AS c1max, CAST(NULL AS DOUBLE PRECISION) AS c2min, CAST(NULL AS DOUBLE PRECISION) AS c2max, CAST(NULL AS DOUBLE PRECISION) AS c3min, CAST(NULL AS DOUBLE PRECISION) AS c3max, CAST(NULL AS DOUBLE PRECISION) AS c1_resol_min, CAST(NULL AS DOUBLE PRECISION) AS c1_resol_max, CAST(NULL AS DOUBLE PRECISION) AS c2_resol_min, CAST(NULL AS DOUBLE PRECISION) AS c2_resol_max, CAST(NULL AS DOUBLE PRECISION) AS c3_resol_min, CAST(NULL AS DOUBLE PRECISION) AS c3_resol_max, TEXT 'body' AS spatial_frame_type, CAST(NULL AS DOUBLE PRECISION) as incidence_min, CAST(NULL AS DOUBLE PRECISION) as incidence_max, CAST(NULL AS DOUBLE PRECISION) as emergence_min, CAST(NULL AS DOUBLE PRECISION) as emergence_max, CAST(NULL AS DOUBLE PRECISION) as phase_min, CAST(NULL AS DOUBLE PRECISION) as phase_max, "OBSERVAT" AS instrument_host_name, "INSTRUME" AS instrument_name, TEXT 'type_III.nubmer_of_components,"type_II.drift' AS measurement_type, "URL" AS access_url, TEXT 'fits' AS access_format, INTEGER '11' AS access_estsize, integer '3' AS processing_level, "INSTITUT" AS publisher, "REFERENCE" AS reference, TEXT 'Helio type 3 event' AS service_title, TEXT 'sun' AS target_region, "SNAPSHOT" AS preview_url, "INSTITUT" AS INSTITUT, "CODE" AS CODE, "VERSION" AS VERSION, "FEATURE_NAME" AS FEATURE_NAME, "ENC_MET" AS ENC_MET, "PERSON" AS PERSON, "CONTACT" AS CONTACT, "OBSERVAT" AS OBSERVAT, "INSTRUME" AS INSTRUME, "TELESCOP" AS TELESCOP, "OBSINST_KEY" AS OBSINST_KEY, "UNITS" AS UNITS, "WAVEMIN" AS WAVEMIN, "WAVEMAX" AS WAVEMAX, "WAVENAME" AS WAVENAME, "WAVEUNIT" AS WAVEUNIT, "FREQMIN" AS FREQMIN, "FREQMAX" AS FREQMAX, "FREQUNIT" AS FREQUNIT, "SPECTRAL_DOMAIN" AS SPECTRAL_DOMAIN, "OBS_TYPE" AS OBS_TYPE, "OBS_CAT" AS OBS_CAT, "DATE_OBS" AS DATE_OBS, "DATE_END" AS DATE_END, "JDINT" AS JDINT, "JDFRAC" AS JDFRAC, "C_ROTATION" AS C_ROTATION, "FILENAME" AS FILENAME, "URL" AS URL, "CDELT1" AS CDELT1, "CDELT2" AS CDELT2, "NAXIS1" AS NAXIS1, "NAXIS2" AS NAXIS2, "CENTER_X" AS CENTER_X, "CENTER_Y" AS CENTER_Y, "FILE_FORMAT" AS FILE_FORMAT, "DATA_TYPE" AS DATA_TYPE, "QCLK_URL" AS QCLK_URL, "QCLK_FNAME" AS QCLK_FNAME, "ID_TYPE_III" AS ID_TYPE_III, "FEAT_MAX_INT" AS FEAT_MAX_INT, "FEAT_MEAN_INT" AS FEAT_MEAN_INT, "MULTIPLE" AS MULTIPLE, "FEAT_FILENAME" AS FEAT_FILENAME, "SNAPSHOT" AS SNAPSHOT, "CC_X_PIX" AS CC_X_PIX, "CC_Y_PIX" AS CC_Y_PIX, "CC_X_UTC" AS CC_X_UTC, "CC_Y_MHZ" AS CC_Y_MHZ, "CC" AS CC, "CC_LENGTH" AS CC_LENGTH, "SKE_CC_X_PIX" AS SKE_CC_X_PIX, "SKE_CC_Y_PIX" AS SKE_CC_Y_PIX, "SKE_CC_X_UTC" AS SKE_CC_X_UTC, "SKE_CC_Y_MHZ" AS SKE_CC_Y_UTC, "SKE_CC" AS SKE_CC, "SKE_CC_LENGTH" AS SKE_CC_LENGTH, "BR_X0_PIX" AS BR_X0_PIX, "BR_Y0_PIX" AS BR_Y0_PIX, "BR_X1_PIX" AS BR_X1_PIX, "BR_Y1_PIX" AS BR_Y1_PIX, "BR_X2_PIX" AS BR_X2_PIX, "BR_Y2_PIX" AS BR_Y2_PIX, "BR_X3_PIX" AS BR_X3_PIX, "BR_Y3_PIX" AS BR_Y3_PIX, "BR_X0_UTC" AS BR_X0_UTC, "BR_Y0_MHZ" AS BR_Y0_MHZ, "BR_X1_UTC" AS BR_X1_UTC, "BR_Y1_MHZ" AS BR_Y1_MHZ, "BR_X2_UTC" AS BR_X2_UTC, "BR_Y2_MHZ" AS BR_Y2_MHZ, "BR_X3_UTC" AS BR_X3_UTC, "BR_Y3_MHZ" AS BR_Y3_MHZ, "TIME_START" AS TIME_START, "TIME_END" AS TIME_END, "DRIFT_START" AS DRIFT_START, "DRIFT_END" AS DRIFT_END, "FIT_A0" AS FIT_A0, "FIT_A1" AS FIT_A1, "LVL_TRUST" AS LVL_TRUST from hf1t3.view_t3_hqi UNION ALL SELECT TEXT 'dataset' AS resource_type, TEXT '1' AS dataset_id, INTEGER '-1' AS index, -- generate_series(1, select count(*) from view_ar_hqi) AS index, array_to_string(array(select distinct CASE "DATA_TYPE" WHEN 'IMAGE' THEN CAST('im' AS TEXT) WHEN 'SPECTRUM' THEN CAST('ds' AS TEXT) WHEN 'DYNAMIC SPECTRUM' THEN CAST('ds' AS TEXT) WHEN 'SPECTROGRAM' THEN CAST('sp' AS TEXT) ELSE CAST(NULL AS TEXT) END from hf1t3.view_t3_hqi),' ') AS dataproduct_type, TEXT 'sun' AS target_name, TEXT 'star' AS target_class, min(CAST(to_char("DATE_OBS"::timestamp at time zone 'UTC','J') as double precision) + EXTRACT(EPOCH FROM "DATE_OBS"::time)/86400 -0.5) AS time_min, max(cast(to_char( "DATE_END"::timestamp at time zone 'UTC','J') as double precision) + EXTRACT(EPOCH FROM "DATE_END"::time)/86400 -0.5) AS time_max, CAST(NULL AS DOUBLE PRECISION) AS time_sampling_step_min, CAST(NULL AS DOUBLE PRECISION) AS time_sampling_step_max, CAST(NULL AS DOUBLE PRECISION) AS time_exp_min, CAST(NULL AS DOUBLE PRECISION) AS time_exp_max, min(CAST((299792458E9/"WAVEMAX") AS DOUBLE PRECISION)) AS spectral_range_min, max(CAST((299792458E9/"WAVEMIN") AS DOUBLE PRECISION)) AS spectral_range_max, CAST(NULL AS DOUBLE PRECISION) AS spectral_sampling_step_min, CAST(NULL AS DOUBLE PRECISION) AS spectral_sampling_step_max, CAST(NULL AS DOUBLE PRECISION) AS spectral_resolution_min, CAST(NULL AS DOUBLE PRECISION) AS spectral_resolution_max, CAST(NULL AS DOUBLE PRECISION) AS c1min, CAST(NULL AS DOUBLE PRECISION) AS c1max, CAST(NULL AS DOUBLE PRECISION) AS c2min, CAST(NULL AS DOUBLE PRECISION) AS c2max, CAST(NULL AS DOUBLE PRECISION) AS c3min, CAST(NULL AS DOUBLE PRECISION) AS c3max, CAST(NULL AS DOUBLE PRECISION) AS c1_resol_min, CAST(NULL AS DOUBLE PRECISION) AS c1_resol_max, CAST(NULL AS DOUBLE PRECISION) AS c2_resol_min, CAST(NULL AS DOUBLE PRECISION) AS c2_resol_max, CAST(NULL AS DOUBLE PRECISION) AS c3_resol_min, CAST(NULL AS DOUBLE PRECISION) AS c3_resol_max, TEXT 'body' AS spatial_frame_type, CAST(NULL AS DOUBLE PRECISION) as incidence_min, CAST(NULL AS DOUBLE PRECISION) as incidence_max, CAST(NULL AS DOUBLE PRECISION) as emergence_min, CAST(NULL AS DOUBLE PRECISION) as emergence_max, CAST(NULL AS DOUBLE PRECISION) as phase_min, CAST(NULL AS DOUBLE PRECISION) as phase_max, array_to_string(array(select distinct "OBSERVAT" from hf1t3.view_t3_hqi),' ') AS instrument_host_name, array_to_string(array(select distinct "INSTRUME" from hf1t3.view_t3_hqi),' ') AS instrument_name, TEXT 'type_III.nubmer_of_components,"type_II.drift' AS measurement_type, CAST(NULL AS TEXT) access_url, TEXT 'fits' AS access_format, INTEGER '11' AS access_estsize, integer '3' AS processing_level, array_to_string(array(select distinct "INSTITUT" from hf1t3.view_t3_hqi),' ') AS publisher, array_to_string(array(select distinct "REFERENCE" from hf1t3.view_t3_hqi),' ') AS reference, TEXT 'Helio type 3 event' AS service_title, TEXT 'sun' AS target_region, CAST(NULL AS TEXT) AS preview_url, CAST(NULL AS TEXT) AS INSTITUT, CAST(NULL AS TEXT) AS CODE, CAST(NULL AS TEXT) AS VERSION, CAST(NULL AS TEXT) AS FEATURE_NAME, CAST(NULL AS TEXT) AS ENC_MET, CAST(NULL AS TEXT) AS PERSON, CAST(NULL AS TEXT) AS CONTACT, CAST(NULL AS TEXT) AS OBSERVAT, CAST(NULL AS TEXT) AS INSTRUME, CAST(NULL AS TEXT) AS TELESCOP, CAST(NULL AS TEXT) AS OBSINST_KEY, CAST(NULL AS TEXT) AS UNITS, CAST(NULL AS REAL) AS WAVEMIN, CAST(NULL AS REAL) AS WAVEMAX, CAST(NULL AS TEXT) AS WAVENAME, CAST(NULL AS TEXT) AS WAVEUNIT, CAST(NULL AS REAL) AS FREQMIN, CAST(NULL AS REAL) AS FREQMAX, CAST(NULL AS REAL) AS FREQUNIT, CAST(NULL AS TEXT) AS SPECTRAL_DOMAIN, CAST(NULL AS TEXT) AS OBS_TYPE, CAST(NULL AS TEXT) AS OBS_CAT, CAST(NULL AS timestamp without time zone) AS DATE_OBS, CAST(NULL AS timestamp without time zone) AS DATE_END, CAST(NULL AS INTEGER) AS JDINT, CAST(NULL AS DOUBLE PRECISION) AS JDFRAC, CAST(NULL AS INTEGER) AS C_ROTATION, CAST(NULL AS TEXT) AS FILENAME, CAST(NULL AS TEXT) AS URL, CAST(NULL AS DOUBLE PRECISION) AS CDELT1, CAST(NULL AS DOUBLE PRECISION) AS CDELT2, CAST(NULL AS INTEGER) AS NAXIS1, CAST(NULL AS INTEGER) AS NAXIS2, CAST(NULL AS DOUBLE PRECISION) AS CENTER_X, CAST(NULL AS DOUBLE PRECISION) AS CENTER_Y, CAST(NULL AS TEXT) AS FILE_FORMAT, CAST(NULL AS TEXT) AS DATA_TYPE, CAST(NULL AS TEXT) AS QCLK_URL, CAST(NULL AS TEXT) AS QCLK_FNAME, CAST(NULL AS INTEGER) AS ID_TYPE_III, CAST(NULL AS REAL) AS FEAT_MAX_INT, CAST(NULL AS REAL) AS FEAT_MEAN_INT, CAST(NULL AS REAL) AS MULTIPLE, CAST(NULL AS TEXT) AS FEAT_FILENAME, CAST(NULL AS TEXT) AS SNAPSHOT, CAST(NULL AS INTEGER) AS CC_X_PIX, CAST(NULL AS INTEGER) AS CC_Y_PIX, CAST(NULL AS DOUBLE PRECISION) AS CC_X_UTC, CAST(NULL AS DOUBLE PRECISION) AS CC_Y_MHZ, CAST(NULL AS TEXT) AS CC, CAST(NULL AS INTEGER) AS CC_LENGTH, CAST(NULL AS INTEGER) AS SKE_CC_X_PIX, CAST(NULL AS INTEGER) AS SKE_CC_Y_PIX, CAST(NULL AS DOUBLE PRECISION) AS SKE_CC_X_UTC, CAST(NULL AS DOUBLE PRECISION) AS SKE_CC_Y_MHZ, CAST(NULL AS TEXT) AS SKE_CC, CAST(NULL AS INTEGER) AS SKE_CC_LENGTH, CAST(NULL AS INTEGER) AS BR_X0_PIX, CAST(NULL AS INTEGER) AS BR_Y0_PIX, CAST(NULL AS INTEGER) AS BR_X1_PIX, CAST(NULL AS INTEGER) AS BR_Y1_PIX, CAST(NULL AS INTEGER) AS BR_X2_PIX, CAST(NULL AS INTEGER) AS BR_Y2_PIX, CAST(NULL AS INTEGER) AS BR_X3_PIX, CAST(NULL AS INTEGER) AS BR_Y3_PIX, CAST(NULL AS DOUBLE PRECISION) AS BR_X0_UTC, CAST(NULL AS DOUBLE PRECISION) AS BR_Y0_MHZ, CAST(NULL AS DOUBLE PRECISION) AS BR_X1_UTC, CAST(NULL AS DOUBLE PRECISION) AS BR_Y1_MHZ, CAST(NULL AS DOUBLE PRECISION) AS BR_X2_UTC, CAST(NULL AS DOUBLE PRECISION) AS BR_Y2_MHZ, CAST(NULL AS DOUBLE PRECISION) AS BR_X3_UTC, CAST(NULL AS DOUBLE PRECISION) AS BR_Y3_MHZ, CAST(NULL AS timestamp without time zone) AS TIME_START, CAST(NULL AS timestamp without time zone) AS TIME_END, CAST(NULL AS REAL) AS DRIFT_START, CAST(NULL AS REAL) AS DRIFT_END, CAST(NULL AS REAL) AS FIT_A0, CAST(NULL AS REAL) AS FIT_A1, CAST(NULL AS INTEGER) AS LVL_TRUST from hf1t3.view_t3_hqi; GRANT ALL PRIVILEGES ON SCHEMA hf1t3 TO gavo WITH GRANT OPTION; GRANT ALL PRIVILEGES ON SCHEMA hf1t3 TO gavoadmin WITH GRANT OPTION; GRANT ALL PRIVILEGES ON SCHEMA hf1t3 TO dachsroot WITH GRANT OPTION; GRANT ALL PRIVILEGES ON hf1t3.view_t3_hqi to gavoadmin WITH GRANT OPTION; GRANT ALL PRIVILEGES ON hf1t3.view_t3_hqi to gavo WITH GRANT OPTION; GRANT ALL PRIVILEGES ON hf1t3.view_t3_hqi to dachsroot WITH GRANT OPTION; GRANT ALL PRIVILEGES ON hf1t3.epn_core to dachsroot WITH GRANT OPTION; GRANT ALL PRIVILEGES ON hf1t3.epn_core to gavoadmin WITH GRANT OPTION; GRANT ALL PRIVILEGES ON hf1t3.epn_core to gavo WITH GRANT OPTION;