drop view radiojove.epn_core;
CREATE or REPLACE VIEW radiojove.epn_core AS SELECT 
cast (id as text)   AS granule_uid,
cast (filetype_id as text)   AS granule_gid,
cast (observation_id as text)   AS obs_id,
regexp_replace(dataproduct_type,'DS>Dynamic Spectra','ds') AS dataproduct_type, 
cast(target_name as text) AS target_name, 
cast(target_class as text) AS target_class, 
cast( time_min as double precision) AS time_min,
cast(time_max as double precision) AS time_max,
cast(time_sampling_step_min as double precision) AS time_sampling_step_min,
cast(time_sampling_step_max 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(spectral_range_min as double precision) AS spectral_range_min,
cast(spectral_range_max 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,
CAST(NULL AS TEXT)  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(instrument_host_name as text) AS instrument_host_name,
cast(instrument_name as text) AS instrument_name, 
cast(measurement_type as text) AS measurement_type,
cast(access_url as text)  AS access_url,
CASE
    when length(access_format)=3 then
        regexp_replace(access_format,'cdf','application/x-cdf')::text
    ELSE
        access_format::text
    END
     AS access_format,
size AS access_estsize,
integer '3' AS processing_level,
cast(target_region as text)  AS target_region,
cast(target_element as text) AS target_element,
cast(instrument_type as text) AS instrument_type,
cast(observer_name as text) AS observer_name,
cast(observatory_location as text) AS observatory_location,
cast(observatory_longitude as double precision) AS observatory_longitude,
cast(observatory_latitude as double precision) AS observatory_latitude,
cast(n_channel as integer) AS n_channel,
cast(dipole_orientation_angle as text) AS dipole_orientation_angle,
cast(thumbnail_url as text) AS thumbnail_url,
modified AS release_date,
created AS creation_date,
modified AS modification_date,
CAST(NULL AS spoly) AS s_region,
TEXT 'radiojove' as service_title

-- FROM dblink('dbname=postgres host=127.0.0.1 user=cakephp password=toto','SELECT 
-- FROM dblink('dbname=radiojove host=voplus.obspm.fr user=epn_user password=epn_user','SELECT 
FROM dblink('dbname=radiojove host=voparis-radiojove.obspm.fr user=epn_user password=epn_user','SELECT 
f.id, f.observation_id, f.filetype_id, f.size, f.dataproduct_type, f.target_name, f.target_class, f.target_region, f.target_element, f.time_min, f.time_max, f.time_sampling_step_min, f.time_sampling_step_max, f.spectral_range_min, f.spectral_range_max, f.instrument_host_name, f.instrument_name, f.instrument_type, f.measurement_type, f.access_format, f.observer_name, f.observatory_location, f.observatory_longitude, f.observatory_latitude, f.access_url, f.n_channel, f.dipole_orientation_angle, f.preview_url, f.thumbnail_url, f.created, f.modified
FROM files as f, filestatuses where filestatuses.id=f.filestatus_id and filestatuses.name=''validated'' ') AS mytab(
"id" integer,
"observation_id" integer,
"filetype_id" integer,
"size" integer,
"dataproduct_type" varchar(200),
"target_name" varchar(200),
"target_class" varchar(200),
"target_region" varchar(200),
"target_element" varchar(200),
"time_min" numeric(17,10),
"time_max" numeric(17,10),
"time_sampling_step_min" double precision,
"time_sampling_step_max" double precision,
"spectral_range_min" double precision,
"spectral_range_max" double precision,
"instrument_host_name" varchar(200),
"instrument_name" varchar(200),
"instrument_type" varchar(200),
"measurement_type" varchar(200),
"access_format" varchar(200),
"observer_name" varchar(200),
"observatory_location" varchar(200),
"observatory_longitude" double precision,
"observatory_latitude" double precision,
"access_url" varchar(400),
"n_channel" integer,
"dipole_orientation_angle" varchar(200),
"preview_url" varchar(400),
"thumbnail_url" varchar(400),
"created" timestamp,
"modified" timestamp
)
Union all
SELECT 
cast (id as text)||'_preview'   AS granule_uid,
cast (filetype_id as text)||'_preview'   AS granule_gid,
cast (observation_id as text)   AS obs_id,
regexp_replace(dataproduct_type,'DS>Dynamic Spectra','ds') AS dataproduct_type, 
target_name  AS target_name, 
target_class  AS target_class, 
cast(time_min as double precision) AS time_min,
cast(time_max as double precision) AS time_max,
time_sampling_step_min AS time_sampling_step_min,
time_sampling_step_max AS time_sampling_step_max,
CAST(NULL AS DOUBLE PRECISION) AS time_exp_min,
CAST(NULL AS DOUBLE PRECISION) AS time_exp_max,
spectral_range_min AS spectral_range_min,
spectral_range_max 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,
CAST(NULL AS TEXT)  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,
instrument_host_name AS instrument_host_name,
instrument_name AS instrument_name, 
measurement_type AS measurement_type,
preview_url  AS access_url,
text 'image/x-png'  AS access_format,
size AS access_estsize,
integer '3' AS processing_level,
target_region AS target_region,
target_element AS target_element,
instrument_type AS instrument_type,
observer_name AS observer_name,
observatory_location AS observatory_location,
observatory_longitude AS observatory_longitude,
observatory_latitude AS observatory_latitude,
n_channel AS n_channel,
dipole_orientation_angle AS dipole_orientation_angle,
thumbnail_url AS thumbnail_url,
modified AS release_date,
created AS creation_date,
modified AS modification_date,
CAST(NULL AS spoly) AS s_region,
TEXT 'radiojove' as service_title

-- FROM dblink('dbname=postgres host=127.0.0.1 user=cakephp password=toto','SELECT 
-- FROM dblink('dbname=radiojove host=voplus.obspm.fr user=epn_user password=epn_user','SELECT 
FROM dblink('dbname=radiojove host=voparis-radiojove.obspm.fr user=epn_user password=epn_user','SELECT 
f.id, f.observation_id, f.filetype_id, f.size, f.dataproduct_type, f.target_name, f.target_class, f.target_region, f.target_element, f.time_min, f.time_max, f.time_sampling_step_min, f.time_sampling_step_max, f.spectral_range_min, f.spectral_range_max, f.instrument_host_name, f.instrument_name, f.instrument_type, f.measurement_type, f.access_format, f.observer_name, f.observatory_location, f.observatory_longitude, f.observatory_latitude, f.access_url, f.n_channel, f.dipole_orientation_angle, f.preview_url, f.thumbnail_url, f.created, f.modified
FROM files as f, filestatuses where filestatuses.id=f.filestatus_id and filestatuses.name=''validated'' ') AS mytab(
"id" integer,
"observation_id" integer,
"filetype_id" integer,
"size" integer,
"dataproduct_type" varchar(200),
"target_name" varchar(200),
"target_class" varchar(200),
"target_region" varchar(200),
"target_element" varchar(200),
"time_min" numeric(17,10),
"time_max" numeric(17,10),
"time_sampling_step_min" double precision,
"time_sampling_step_max" double precision,
"spectral_range_min" double precision,
"spectral_range_max" double precision,
"instrument_host_name" varchar(200),
"instrument_name" varchar(200),
"instrument_type" varchar(200),
"measurement_type" varchar(200),
"access_format" varchar(200),
"observer_name" varchar(200),
"observatory_location" varchar(200),
"observatory_longitude" double precision,
"observatory_latitude" double precision,
"access_url" varchar(400),
"n_channel" integer,
"dipole_orientation_angle" varchar(200),
"preview_url" varchar(400),
"thumbnail_url" varchar(400),
"created" timestamp,
"modified" timestamp
)
;



GRANT ALL PRIVILEGES ON SCHEMA radiojove TO gavo WITH GRANT OPTION;
GRANT ALL PRIVILEGES ON SCHEMA radiojove TO gavoadmin WITH GRANT OPTION;
GRANT ALL PRIVILEGES ON SCHEMA radiojove TO dachsroot WITH GRANT OPTION;
GRANT ALL PRIVILEGES ON radiojove.epn_core to gavoadmin WITH GRANT OPTION;
GRANT ALL PRIVILEGES ON radiojove.epn_core to gavo WITH GRANT OPTION;
GRANT ALL PRIVILEGES ON radiojove.epn_core to dachsroot WITH GRANT OPTION;



