-- SQL procedure to define the MDISC service view for EPN-TAP v2 -- Can be used as a template for other light services -- Creates one view per granule group (here, 2 of them), -- then merge to epn_core view (EPN-TAP v2) CREATE OR REPLACE VIEW `mdisc.matlab` AS SELECT -- mandatory parameters CAST(`filename` || '.mat' AS TEXT) AS `granule_uid`, TEXT 'MDISC' AS `granule_gid`, CAST(`filename` AS TEXT) AS `obs_id`, TEXT 'im' AS dataproduct_type, CAST(`target_name` AS TEXT) AS target_name, TEXT 'planet' AS target_class, --- time CAST(NULL AS DOUBLE PRECISION) AS time_min, CAST(NULL 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, --- spectral CAST(NULL AS DOUBLE PRECISION) AS spectral_range_min, CAST(NULL 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, --- spatial coordinates CAST(c1min AS DOUBLE PRECISION) AS c1min, CAST(c1max AS DOUBLE PRECISION) AS c1max, CAST(c2min AS DOUBLE PRECISION) AS c2min, CAST(c2max AS DOUBLE PRECISION) AS c2max, DOUBLE PRECISION 0 AS c3min, DOUBLE PRECISION 360 AS c3max, CAST(NULL AS TEXT) AS s_region, CAST(c1_resol AS DOUBLE PRECISION) AS c1_resol_min, CAST(c1_resol AS DOUBLE PRECISION) AS c1_resol_max, CAST(c2_resol_min AS DOUBLE PRECISION) AS c2_resol_min, CAST(c2_resol_max AS DOUBLE PRECISION) AS c2_resol_max, DOUBLE PRECISION 360 AS c3_resol_min, DOUBLE PRECISION 360 AS c3_resol_max, TEXT 'spherical' 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, --- CAST(NULL AS TEXT) AS instrument_host_name, CAST(NULL AS TEXT) AS instrument_name, --- TEXT '#phys.magField#phys.magField.potential#phys.flux;phys.electron phys.flux;phys.atmol.ionStage#phys.pressure#' AS measurement_type, CAST(NULL AS INTEGER) AS processing_level CAST(creation_date AS DATE) AS creation_date, CAST(creation_date AS DATE) AS modification_date, CAST(creation_date AS DATE) AS release_date, --- optional parameters TEXT 'UCL-MDISC' AS service_title, CAST(access_url || '.mat' AS TEXT) AS access_url, TEXT 'matlab7' AS access_format, CAST(matlab_size AS INTEGER) AS access_estsize, TEXT 'magnetosphere' AS target_region, TEXT 'doi:10.1111/j.1365-2966.2009.15865.x' AS bib_reference, FROM mdisc.metadata; CREATE OR REPLACE VIEW `mdisc.netcdf` AS SELECT -- mandatory parameters CAST(`filename` || '.nc' AS TEXT) AS `granule_uid`, TEXT 'MDISC' AS `granule_gid`, CAST(`filename` AS TEXT) AS `obs_id`, TEXT 'im' AS dataproduct_type, CAST(`target_name` AS TEXT) AS target_name, TEXT 'planet' AS target_class, --- time CAST(NULL AS DOUBLE PRECISION) AS time_min, CAST(NULL 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, --- spectral CAST(NULL AS DOUBLE PRECISION) AS spectral_range_min, CAST(NULL 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, --- spatial coordinates CAST(c1min AS DOUBLE PRECISION) AS c1min, CAST(c1max AS DOUBLE PRECISION) AS c1max, CAST(c2min AS DOUBLE PRECISION) AS c2min, CAST(c2max AS DOUBLE PRECISION) AS c2max, DOUBLE PRECISION 0 AS c3min, DOUBLE PRECISION 360 AS c3max, CAST(NULL AS TEXT) AS s_region, CAST(c1_resol AS DOUBLE PRECISION) AS c1_resol_min, CAST(c1_resol AS DOUBLE PRECISION) AS c1_resol_max, CAST(c2_resol_min AS DOUBLE PRECISION) AS c2_resol_min, CAST(c2_resol_max AS DOUBLE PRECISION) AS c2_resol_max, DOUBLE PRECISION 360 AS c3_resol_min, DOUBLE PRECISION 360 AS c3_resol_max, TEXT 'spherical' 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, --- CAST(NULL AS TEXT) AS instrument_host_name, CAST(NULL AS TEXT) AS instrument_name, --- TEXT '#phys.magField#phys.magField.potential#phys.flux;phys.electron phys.flux;phys.atmol.ionStage#phys.pressure#' AS measurement_type, CAST(NULL AS INTEGER) AS processing_level CAST(creation_date AS DATE) AS creation_date, CAST(creation_date AS DATE) AS modification_date, CAST(creation_date AS DATE) AS release_date, --- optional parameters TEXT 'UCL-MDISC' AS service_title, CAST(access_url || '.mat' AS TEXT) AS access_url, TEXT 'netcdf' AS access_format, CAST(netcdf_size AS INTEGER) AS access_estsize, TEXT 'magnetosphere' AS target_region, TEXT 'doi:10.1111/j.1365-2966.2009.15865.x' AS bib_reference, FROM mdisc.metadata; -- merge the above views and interleave groups CREATE OR REPLACE VIEW mdisc.epn_core AS ( SELECT * FROM mdisc.matlab UNION SELECT * FROM mdisc.netcdf ORDER BY granule_uid ); GRANT ALL PRIVILEGES ON SCHEMA public TO gavo WITH GRANT OPTION; GRANT ALL PRIVILEGES ON SCHEMA public TO gavoadmin WITH GRANT OPTION; GRANT ALL PRIVILEGES ON mdisc.epn_core to gavoadmin WITH GRANT OPTION; GRANT ALL PRIVILEGES ON mdisc.epn_core to gavo WITH GRANT OPTION;