-- create a foreighn table in postgres from a mysql view or table
-- you must have postgres 9.1 minimum + foreighn data wraper  https://github.com/dpage/mysql_fdw/releases/tag/REL-1_0_1
-- under postgres sudo -u postgres psql -p 5435 -h localhost gavo  (just because i use port 5435)
CREATE EXTENSION mysql_fdw;

CREATE SERVER mysql_svr FOREIGN DATA WRAPPER mysql_fdw
OPTIONS (address '127.0.0.1', port '3306');
-- use a mysql user that have select right
CREATE USER MAPPING FOR PUBLIC
SERVER mysql_svr
OPTIONS (username 'root', password 'kissou1');

DROP SCHEMA helio cascade;
CREATE SCHEMA helio;

-- DROP TABLE "VIEW_AR_HQI";

CREATE FOREIGN TABLE helio.view_ar_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),
  "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,
  "R_SUN" double precision,
  "BITPIX" integer,
  "BSCALE" double precision,
  "BZERO" double precision,
  "EXP_TIME" real,
  "FILE_FORMAT" character varying(20),
  "DATA_TYPE" character varying(50),
  "QCLK_URL" text,
  "QCLK_FNAME" character varying(200),
  "ID_AR" integer NOT NULL DEFAULT 0,
  "NOAA_NUMBER" integer,
  "FEAT_X_PIX" integer,
  "FEAT_Y_PIX" integer,
  "FEAT_X_ARCSEC" double precision NOT NULL,
  "FEAT_Y_ARCSEC" double precision NOT NULL,
  "FEAT_HG_LONG_DEG" real NOT NULL,
  "FEAT_HG_LAT_DEG" real NOT NULL,
  "FEAT_CARR_LONG_DEG" real,
  "FEAT_CARR_LAT_DEG" real,
  "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_ARCSEC" double precision NOT NULL,
  "BR_Y0_ARCSEC" double precision NOT NULL,
  "BR_X1_ARCSEC" double precision NOT NULL,
  "BR_Y1_ARCSEC" double precision NOT NULL,
  "BR_X2_ARCSEC" double precision NOT NULL,
  "BR_Y2_ARCSEC" double precision NOT NULL,
  "BR_X3_ARCSEC" double precision NOT NULL,
  "BR_Y3_ARCSEC" double precision NOT NULL,
  "BR_HG_LONG0_DEG" real,
  "BR_HG_LAT0_DEG" real,
  "BR_HG_LONG1_DEG" real,
  "BR_HG_LAT1_DEG" real,
  "BR_HG_LONG2_DEG" real,
  "BR_HG_LAT2_DEG" real,
  "BR_HG_LONG3_DEG" real,
  "BR_HG_LAT3_DEG" real,
  "BR_CARR_LONG0_DEG" real,
  "BR_CARR_LAT0_DEG" real,
  "BR_CARR_LONG1_DEG" real,
  "BR_CARR_LAT1_DEG" real,
  "BR_CARR_LONG2_DEG" real,
  "BR_CARR_LAT2_DEG" real,
  "BR_CARR_LONG3_DEG" real,
  "BR_CARR_LAT3_DEG" real,
  "FEAT_AREA_PIX" integer NOT NULL,
  "FEAT_AREA_DEG2" real,
  "FEAT_AREA_MM2" real,
  "FEAT_MAX_INT" real,
  "FEAT_MIN_INT" real,
  "FEAT_MEAN_INT" real,
  "FEAT_MEAN2QSUN" double precision,
  "FEAT_MIN_BZ" real,
  "FEAT_MAX_BZ" real,
  "FEAT_MEAN_BZ" real,
  "FEAT_MEAN_GRAD" real,
  "FEAT_MEDIAN_GRAD" real,
  "FEAT_MAX_GRAD" real,
  "FEAT_LENGTH_NL" real,
  "FEAT_LENGTH_SG" real,
  "CC_X_PIX" integer NOT NULL,
  "CC_Y_PIX" integer NOT NULL,
  "CC_X_ARCSEC" double precision NOT NULL,
  "CC_Y_ARCSEC" double precision NOT NULL,
  "CC" text NOT NULL,
  "CC_LENGTH" integer,
  "SNAPSHOT" character varying(400),
  "TRACK_ID" integer,
  "PHENOM" integer,
  "REF_FEAT" integer,
  "TRACK_LVL_TRUST" integer
)
SERVER mysql_svr
options (query'select * from helio.VIEW_AR_HQI');

drop view helio.epn_core;
CREATE OR REPLACE VIEW helio.epn_core AS SELECT
	TEXT 'granule'								AS resource_type,
	TEXT '1' 								AS dataset_id,
	generate_series(1, count(a)) 						AS index,
	TEXT 'sp' 								AS dataproduct_type
	from helio.view_ar_hqi "a";

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


ALTER TABLE "VIEW_AR_HQI"
  OWNER TO pierre;
