CREATE EXTENSION mysql_fdw; CREATE SERVER mysql_svr FOREIGN DATA WRAPPER mysql_fdw OPTIONS (address '127.0.0.1', port '3306'); CREATE USER MAPPING FOR PUBLIC SERVER mysql_svr OPTIONS (username 'visite', password 'visite'); DROP SCHEMA heliot3 cascade; CREATE SCHEMA heliot3; CREATE FOREIGN TABLE heliot3.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 helio.VIEW_T3_HQI'); drop sequence epn_core_seqt3; create sequence epn_core_seqt3; CREATE MATERIALIZED VIEW heliot3.epn_core AS SELECT TEXT 'granule' AS resource_type, TEXT '1' AS dataset_id, nextval('epn_core_seqt3'::regclass) 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(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, "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 heliot3.view_t3_hqi UNION ALL SELECT TEXT 'dataset' AS resource_type, TEXT '1' AS dataset_id, INTEGER '-1' 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 heliot3.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 heliot3.view_t3_hqi),' ') AS instrument_host_name, array_to_string(array(select distinct "INSTRUME" from heliot3.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 heliot3.view_t3_hqi),' ') AS publisher, array_to_string(array(select distinct "REFERENCE" from heliot3.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 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 heliot3.view_t3_hqi; GRANT ALL PRIVILEGES ON SCHEMA heliot3 TO gavo WITH GRANT OPTION; GRANT ALL PRIVILEGES ON SCHEMA heliot3 TO gavoadmin WITH GRANT OPTION; GRANT ALL PRIVILEGES ON heliot3.epn_core to gavoadmin WITH GRANT OPTION; GRANT ALL PRIVILEGES ON heliot3.epn_core to gavo WITH GRANT OPTION;