Pro dbvex, FILE ;+ ; NAME: ; dbvex ; ; EFFECT: ; Reads Virtis VEx extended index, parse parameters, writes an SQL routine to write the database ; This is one step in the creation of an EPN-TAP service, can be used as a template for other PDS3 datasets ; ; EXAMPLE ; dbvex, [FILE] ; ; KEYWORDS: ; FILE: name of index file, default is to use indexdb.txt ; also writes a file 'vex_db.sql' ??? ; ; USAGE: ; - Create database VVEx in pgAdmin ; - run this IDL procedure, then (in terminal): ; cd /whatever/Virtis_VEx ; sudo -s -u postgresql ; psql VVEX < vex_db.sql ; - refresh VEx db in PgAdmin to see the new db ; - create views (dataset & epn_core) with routine vex_views.sql ; psql VVEX < vex_views.sql ; (- transform ascii data files in VOTable using catiksfiles.pro) ; - instal in gavo with file q.rd (hand written) ; - declare service in registry with file vopdc_obspm-lesia-epn-vex.xml (hand written) ; ; SPECIAL: ; use unit = dateTime to introduce an ISO time string => xtype='dateTime' (XML std, not VOTable) ; SQL: 'float' stands for double; 'real' stands for simple precision ; ascii 9 = HT, used as separator in an SQL data table (when input with COPY) ; ; Last steps (done): ; - fix final url/names for files (votable at VOParis) ; - compute/add spectral sampling step + spectral resolution ; ; HISTORY: ; dbiks.pro ; Stephane Erard, nov 2013 (first SQL-writing routine, partly derived from catIKS) ; SE, May 2014: corrections and modif in table and SQL routine => @Cor2014 ; dbvex.pro ; SE, may 2015: adapted to Virtis VEx, in progress ; ------------------------------------------------------------------------------------ dir = '/Volumes/Data3/VEx/archive_model_deliv1e/INDEX/' ; Flist = file_search(dir, '*.LBL') cd, dir Flist= '/Volumes/Data3/VEx/archive_model_deliv1e/INDEX/VIRTIS_INDEX.LBL' ; doesn't work ; il doit y avoir un truc pourri vers la fin du fichier dir = '/Volumes/Data3/RSI/perso_IDL/MaPomme/VO-essais/VIRTIS_VEx/' ; Flist = file_search(dir, '*.LBL') cd, dir Flist= 'BID2_INDEX.LBL' ; this one is OK - a dummy mini file fdima=Flist(0) temp=v_readpds(fdima,/silent, lab) Nf = (size(temp.index_table.column1))(1) ; nb of files described Nparam = (size(temp.index_table.column_names))(1) ; nb of param in index ; 1) Select files of interest ; only preserve *.CAL ;toto= (strsplit(temp.index_table.(1), '.', /ext)) ;NomTot = (toto.ToArray())(*,1) ;indfich = where(Nomtot EQ 'CAL"', Nf) ; trs compliqu, il vaut mieux trier dans le shell et preparer un fichier ; Structure pour les donnes/granules VExparam = replicate({id:1,Filename:'',orbit:1L, time_start:'', time_end:'',target:'',target_distance:1.,lat_min:1.,lat_max:1.,lon_min:1.,lon_max:1., local_t_min:1.,local_t_max:1.,sp_min:1.,sp_max:1.,sp_step_min:1.,sp_step_max:1.,sp_res_min:1.,sp_res_max:1., exp_time:1., Inst_Name:'',Inst_Host_name:'', ref:'NULL',a_url:'',a_format:''},Nf) ; 2) Get parameters from index file, build parameter structure ; faire Nf : nb de fichiers dans la struct ; ensuite c'est seulement ; iksparam.filename = temp.index_table.(1) VExparam.id = indgen(Nf)+1 ;VExparam.Inst_name = replicate('"VIRTIS"', Nf) VExparam.Inst_name = replicate('VIRTIS', Nf) ;VExparam.Inst_host_name = replicate('"Venus Express"', Nf) VExparam.Inst_host_name = replicate('Venus Express', Nf) ; parse filename ;VExparam.Filename = temp.index_table.(1) ;nomout = (strsplit(temp.index_table.(1), '/', /extr))(-1) ;nomout= (strmid(temp.index_table.(1), strlen(temp.index_table.(1))-15,14)) ;nomout= (strmid(temp.index_table.(1), strsplit(temp.index_table.(1), '/')(-1),14)) toto= (strsplit(temp.index_table.(1), '/')) ; this is a list in IDL 8.2! refslash = (toto.ToArray())(*,4) ; location of last / nomF =strarr(Nf) ;for ii = 0, Nf-1 do nomF(ii)= '"'+ (strmid(temp.index_table.((1))(ii), refslash(ii),14)) ; with quotes for ii = 0, Nf-1 do nomF(ii)= (strmid(temp.index_table.((1))(ii), refslash(ii),13)) ; no quotes VExparam.Filename = nomF VExparam.Orbit = temp.index_table.(8) VExparam.time_start = strmid((temp.index_table.(6)),1,23) VExparam.time_end = strmid((temp.index_table.(7)),1,23) toto = (strsplit(temp.index_table.(9), '"', /extr)) VExparam.target = (toto.ToArray())(*,0) VExparam.target = strlowcase(VExparam.target) VExparam.target_distance = temp.index_table.(29) VExparam.lat_min = temp.index_table.(24) VExparam.lat_max = temp.index_table.(23) VExparam.lon_min = temp.index_table.(26) VExparam.lon_max = temp.index_table.(25) ; VExparam.local_t = (temp.index_table.(28)+24.*(temp.index_table.(28) LT temp.index_table.(27)) + temp.index_table.(27))/2. mod 24. ; separate VExparam.local_t_min = temp.index_table.(27) VExparam.local_t_max = temp.index_table.(28) VExparam.ref =replicate('ftp://psa.esac.esa.int/pub/mirror/VENUS-EXPRESS/VIRTIS/VEX-V-VIRTIS-2-3-V3.0/DOCUMENT/VIRTIS_EAICD.PDF',Nf) ; URL of PDS file ; adjust according to mission phase/orbit (from PSA ftp + data workshop) indorb0 = where(VExparam.Orbit LE 521 and VExparam.Orbit GE 23, c0) indorb1 = where(VExparam.Orbit LE 1132 and VExparam.Orbit GE 563, c1) indorb2 = where(VExparam.Orbit LE 1575 and VExparam.Orbit GE 1139, c2) indorb3 = where(VExparam.Orbit GE 1587, c3) PSAurl = 'ftp://psa.esac.esa.int/pub/mirror/VENUS-EXPRESS/VIRTIS/' ext0 = PSAurl+"VEX-V-VIRTIS-2-3-V3.0/" ext1 = PSAurl+"VEX-V-VIRTIS-2-3-EXT1-V2.0/" ext2 = PSAurl+"VEX-V-VIRTIS-2-3-EXT2-V2.0/" ext3 = PSAurl+"VEX-V-VIRTIS-2-3-EXT3-V2.0/" toto = (strsplit(temp.index_table.(1), '"', /extr)) NomTot = (toto.ToArray())(*,0) ; this actually works! If c0 NE 0 then NomTot(indorb0) = ext0+NomTot(indorb0) If c1 NE 0 then NomTot(indorb1) = ext1+NomTot(indorb1) If c2 NE 0 then NomTot(indorb2) = ext2+NomTot(indorb2) If c3 NE 0 then NomTot(indorb3) = ext3+NomTot(indorb3) VExparam.a_url = nomtot ;VExparam.a_url = 'ftp://psa.esac.esa.int/pub/mirror/VENUS-EXPRESS/VIRTIS/'+ext+temp.index_table.(1) VExparam.a_format = 'PDS3' ; **** still need to check M spectral values (depending on mode) ***** channel = strmid(nomf,1,1) indI = where(channel EQ 'I', cI) indV = where(channel EQ 'V', cV) lam= v_lamh(wid=wid) ; VEx bid = replicate(min(lam),Nf) ; defaut to H if cI NE 0 then bid(indI) = 1.025 if cV NE 0 then bid(indV) = 0.288147 VEXparam.sp_min = float(bid) bid = replicate(max(lam),Nf) ; defaut to H if cI NE 0 then bid(indI) = 5.116 if cV NE 0 then bid(indV) = 1.11470 VEXparam.sp_max = float(bid) bid1 = (lam)- shift(lam, -1) bid = replicate(min(bid1(0+432*7:430+432*7)), Nf) if cI NE 0 then bid(indI) = 0.00949705 if cV NE 0 then bid(indV) = 0.002 VEXparam.sp_step_min = float(bid) bid = replicate(max(bid1(0:430)), Nf) if cI NE 0 then bid(indI) = 0.00949705 if cV NE 0 then bid(indV) = 0.002 VEXparam.sp_step_max = float(bid) ; actual spectral resolution, from ?? bid = replicate(min(wid(0+432*7:431+432*7)), Nf) if cI NE 0 then bid(indI) = 0.0121839 if cV NE 0 then bid(indV) = 0.00191775 VEXparam.sp_res_min = float(bid) bid = replicate(max(wid(07:431)), Nf) if cI NE 0 then bid(indI) = 0.0121839 if cV NE 0 then bid(indV) = 0.00191775 VEXparam.sp_res_max = float(bid) indHobs = where(channel EQ 'H' or channel EQ 'S' or channel EQ 'T' and temp.index_table.(15) NE -1) bid = temp.index_table.(15) bid(indHobs) = bid(indHobs) /1000. ; int time in ms for H VExparam.exp_time = bid ; 3) Write an SQL procedure ; (Adapted from massedb.sql, EPN-TEP tutorial) Nschema= "VVEX" Ntable = "data_table" File= 'vex_db.sql' openw, LUN, /get_lun, FILE cstsql= $ ['-- SQL procedure to define the draft VVEx service datatable ',$ '-- Stephane Erard, LESIA/OVPDC, May 2015 (written by IDL routine dbvex.pro)',$ '-- Can be used as a template for other light services ',$ ' ',$ '-- DATABASE "VVEX", must be created first; ',$ ' ',$ '-- Name: '+ Nschema+'; Type: SCHEMA; Schema: '+ Nschema+'; Owner: postgres',$ '',$ 'DROP SCHEMA IF EXISTS '+Nschema+' cascade;',$ '-- line above to be commented for tests only',$ 'CREATE SCHEMA '+Nschema+';',$ 'SET search_path = public, pg_catalog;',$ '',$ "SET default_tablespace = '';",$ '',$ 'SET default_with_oids = false;',$ '',$ '-- Name: '+Ntable+'; Type: TABLE; Schema: '+Nschema+'; Owner: postgres; Tablespace: ',$ '',$ 'CREATE TABLE '+Nschema+'.'+Ntable+' (',$ ; must follow IKSparam structure fields ' id integer NOT NULL,',$ ' filename character varying(13),',$ ' orbit character varying(6),',$ ' time_start character varying(23),',$ ' time_end character varying(23),',$ ' target character varying(6),',$ ' target_distance real,',$ ' lat_min real,',$ ' lat_max real,',$ ' lon_min real,',$ ' lon_max real,',$ ' local_t_min real,',$ ' local_t_max real,',$ ' sp_min float,',$ ; means double-precision in SQL ' sp_max float,',$ ' sp_step_min float,',$ ' sp_step_max float,',$ ' sp_res_min float,',$ ' sp_res_max float,',$ ' exp_time real,',$ ; means simple-precision in SQL ' inst_name character varying(6),',$ ' inst_host_name character varying(13),',$ ' ref character varying(110),',$ ' a_url character varying(130),',$ ' a_format character varying(7)',$ ');'] printf, Lun, ' ' ; -- the lines after CREATE TABLE could be derived from vexparam structure definition + SQL type for ii = 0, N_elements(cstsql)-1 do $ printf, Lun, cstsql(ii) printf, Lun, ' ' dir= '/Volumes/Data3/Comets/vega1-c-iks-3-rdr-halley-processed-v1.0/data/' columns = strlowcase(tag_names(VEXparam)) Nfield = N_tags(vexparam) ; sql = "INSERT INTO "+Nschema+'.'+Ntable+" (" ; sql = sql + strjoin(columns[indf], ", ") ; sql = sql + ") VALUES " ;printf, Lun, sql ;for ii=0, Nf-1 do begin ; sql = '(' ; for jj=0, Nfield-2 do begin ; sql = sql + string((IKSparam.(jj))(ii))+ ", " ; endfor ; sql = sql + string((IKSparam.(jj))(ii))+ ") " ;printf, Lun, sql ;endfor ;printf, Lun, '' ; a va pas : faut mettre des quotes sur les string, et virer les espaces en trop ; alt (similaire la version de Pierre) indf = indgen(Nfield) sql = "COPY "+Nschema+'.'+Ntable+" (" sql = sql + strjoin(columns[indf], ", ") sql = sql + ") FROM stdin;" printf, Lun, sql for ii=0, Nf-1 do begin for jj=0, Nfield-2 do $ printf, lun, strtrim((VEXparam.(jj))(ii),2),string(9B), format = '(A,1A, $)' ; ascii 9 = HT, required as separator printf, lun, strtrim((VEXparam.(jj))(ii),2), format = '(A)' ; last item endfor printf, Lun, '\.' ; terminates data entry, must be there printf, Lun, ' ' cstsql= $ ['ALTER TABLE ONLY '+Nschema+'.'+Ntable , $ ' ADD CONSTRAINT '+Ntable+'_pkey PRIMARY KEY (id);'] printf, Lun, cstsql printf, Lun, '' cstsql= $ ['-- Set access/ownership of schema and table',$ ' ',$ 'REVOKE ALL ON SCHEMA "' + Nschema+'" FROM PUBLIC;',$ ' ',$ 'REVOKE ALL ON SCHEMA "' + Nschema+'" FROM postgres;',$ ' ',$ 'GRANT ALL ON SCHEMA "'+Nschema+'" TO postgres;',$ ' ',$ 'GRANT ALL PRIVILEGES ON SCHEMA '+Nschema+' TO gavo WITH GRANT OPTION; ',$ 'GRANT ALL PRIVILEGES ON SCHEMA '+Nschema+' TO gavoadmin WITH GRANT OPTION; ',$ 'GRANT ALL PRIVILEGES ON '+Nschema+'.'+Ntable+' TO gavo WITH GRANT OPTION; ',$ 'GRANT ALL PRIVILEGES ON '+Nschema+'.'+Ntable+' TO gavoadmin WITH GRANT OPTION; ',$ 'GRANT ALL PRIVILEGES ON '+Nschema+'.dataset TO gavo WITH GRANT OPTION; ',$ 'GRANT ALL PRIVILEGES ON '+Nschema+'.dataset TO gavoadmin WITH GRANT OPTION; ',$ 'GRANT ALL PRIVILEGES ON '+Nschema+'.epn_core TO gavo WITH GRANT OPTION; ',$ 'GRANT ALL PRIVILEGES ON '+Nschema+'.epn_core TO gavoadmin WITH GRANT OPTION; '] printf, Lun, cstsql printf, Lun, ' ' close, Lun free_lun, Lun End