""" define some usefull query function specific of oivis data base Examples: --------- all the add_* methods takes the keys, linkto, linkall keywords: keys : the keys to link they have defaults related to added table linkall : link one or several tables to the entry tables linkto : list, should have the same size than the entries it define all the pair of links In the following example replace None par the db to run execute ########################### # for instance to get flux of AT1 vs flux of AT2 q = query(None, tbls="flux as f1,flux as f2") q.add_arrays("ar1", "ar2", linkto=["f1","f2"], stations=[1,1]) q.add_conds("ar1.TEL_NAME = 'AT1' AND ar2.TEL_NAME = 'AT2'") str(q) ########################### # link vis2 with arrays q = query(None, tbls="vis2") q.add_arrays("ar1", "ar2", linkall="vis2") # here by default stations is [1,2] str(q) ########################### # FT vs SC q = query(db) q.add_vis2("v_ft", "v_sc", linkall="v_ft") q.add_waveindex("w_ft", "w_sc", linkto='v_ft,v_sc', linkall='waveindex as w_ft', keys=False) # keys=False means that it looks into the default defined link pairs inside the db # waveindex as w_ft is necessary because w_ft is not yet in table q.add_conds("v_ft.INSNAME like '%FT%' AND v_sc.INSNAME like '%SC%'") str(q) ft,sc = zip(*q.execute(columns="median(v_ft.VIS2DATA,w_ft.WImin,w_ft.WImax),median(v_sc.VIS2DATA,w_sc.WImin,w_sc.WImax)")) ft_err,sc_err = zip(*q.execute(columns="median(v_ft.VIS2ERR,w_ft.WImin,w_ft.WImax),median(v_sc.VIS2ERR,w_sc.WImin,w_sc.WImax)")) # etc .... ########################### ### One target vs an other ... why not ? q = query(None, tbls=["vis2 as v_herbig", "vis2 as v_ttauri"]) q.add_targets( "t_herbig", "t_ttauri", linkto=["v_herbig", "v_ttauri"]) ### OR #### q = query(None) q.add_targets( "t_herbig", "t_ttauri", linkto=["vis2 as v_herbig", "vis2 as v_ttauri"]) q.add_conds("t_herbig.TARGET = 'HD-3456' AND t_ttauri.TARGET = 'R_For'") str(q) ########################## # Simple use q = query(None) q.add_vis2() # add vis2 dable q.add_headers(linkto="vis2") q.add_waveindex(linkall="*", keys=False) # = autolink # the '*', link wave to all the avaiable tables in the query # keys=False means that it looks into the default defined link pairs inside the db sqtr(q) """ ## debug from . import oivis_definition as oidef from . import oivisdb from . import query as qr import importlib try: from importlib import reload except: pass importlib.reload(qr) #### from .query import query as _query, _parse_table_name, first, row, merge, merge_ #known_tables = ["headers", "vis", "vis2", "t3", "flux", "targets", # "wavelengths", "waveindex", "arrays"] def _build_links(cls): global known_tables, link_tables for table in cls.tables: t1 = table.name known_tables.append(t1) for t2, keys in table.links: keys = [(key,key) for key in keys] link_tables[(t1,t2)] = keys # supose that they are symetric link_tables[(t2,t1)] = keys try: known_tables except: known_tables = [] link_tables = {} _build_links(oidef.OIVIS) def _get_default_links(q, t1,t2 ): sups = [] # if t2 == "arrays": # if "vis" in t1: # N=2 # elif "flux" in t1: # N=1 # elif t1 is "t3": # N=3 # sups = [("STA%d_INDEX"%i, "STA_INDEX") for i in range(1,N+1)] # elif t1 == "arrays": # if "vis" in t2: # N=2 # elif "flux" in t2: # N=1 # elif t2 is "t3": # N=3 # sups = [("STA_INDEX", "STA%d_INDEX"%i) for i in range(1,N+1)] return sups+list(link_tables.get((t1,t2), [])) # c = q.execute("SELECT DISTINCT column FROM LINKS WHERE tbl1 = ? AND tbl2 = ?",(t1,t2)) # links = c.fetchall() # if links: # links, = zip(*links) # else: # links = [] # return links def link_arrays(*names, **kwargs): """ add several array tables with given replacement name call signature: # just add the 'arrays' table link_arrays() # add arrays as ar1, arrays as ar2 , etc .... link_arrays("ar1", "ar2", ...) # num / format, add ar_1, ar_2, ar_3 link_arrays(3, "ar_%d") Accept two keywords ------------------- linkto: string to link the array to a table: q.add_arrays( "ar1_v", "ar2_v", linkto="vis2 as v2") stations: int, list of int, optional The stations number to link to each tbl array entry, chould be of the size of the number of arrays. 1,..N will be STA1_INDEX, STA2_INDEX, ... keys : int, list of string extra keys to match, default is ["FILEID"] Returns ------- A list of tables , list of conditions """ linkto = kwargs.pop("linkto", None) linkall = kwargs.pop("linkall", None) stations = kwargs.pop("stations", None) keys = kwargs.pop("keys", ["FILEID"]) if len(kwargs): raise ValueError("link_arrays takes only 2 keyword arguments: 'linkto' and 'stations'") if not names: names = ["arrays"] tbls = names if isinstance( names[0], int): if len(names)!=2 or not isinstance(names[1],str): raise ValueError("expecting only a format string after a int") num, fmt = names names = [(fmt%(i+1)) for i in range(num)] tbls = ["arrays as "+n for n in names] conds = [] if stations is None: stations = list(range(1,len(names)+1)) elif len(stations) != len(names): raise ValueError("stations should be a list of the same number than entries %d != %d"%(len(stations),len(names))) if linkto: if isinstance(linkto,str): linkto = [s.strip() for s in linkto.split(",")] if len(linkto)!=len(names): raise ValueError("linkto array should have the same length than number of entries") for name,lt in zip(names,linkto): tbls += [lt] link_replacement, link_name = _parse_table_name(lt) conds.extend([ "%s.STA%d_INDEX = %s.STA_INDEX"%(link_replacement, s, name) for s in stations] ) for k in keys: conds.append("%s.[%s] = %s.[%s]"%(link_replacement,k, name,k)) if linkall: if isinstance(linkall,str): linkall = [s.strip() for s in linkall.split(",")] for lt in linkall: tbls += [lt] link_replacement, link_name = _parse_table_name(lt) conds.extend(["%s.STA%d_INDEX = %s.STA_INDEX"%(link_replacement, s, a) for s,a in zip(stations,names) ]) for k in keys: conds.extend(["%s.[%s] = %s.[%s]"%(link_replacement,k , a, k) for a in names ]) return tbls, conds def _simple_linker(qr, tbname, *names, **kwargs): linkto = kwargs.pop("linkto", None) linkall = kwargs.pop("linkall", None) keys = kwargs.pop("keys", False) if len(kwargs): raise ValueError("Takes only one keyword argument 'linkto'") if not names: names = [tbname] if isinstance( names[0], int): if len(names)!=2 or not isinstance(names[1],str): raise ValueError("expecting only a format string after a int") num, fmt = names names = [(fmt%(i+1)) for i in range(num)] if isinstance(tbname, str): tbnames = {name:tbname for name in names} elif tbname is None: tbnames = dict(_parse_table_name(name) for name in names) names = list(tbnames.keys()) else: raise ValueError("tbname must be string or None") tbls = ["%s as %s"%(tbnames[n],n) for n in names] conds = [] tbsup = [] if linkto: if isinstance(linkto,str): linkto = [s.strip() for s in linkto.split(",")] if len(linkto)!=len(names): raise ValueError("linkto array should have the same length than number of entries") for lt,name in zip(linkto,names): tbsup.append(lt) link_replacement, link_name = _parse_table_name(lt) if keys in [None,False]: try: newkeys = qr._get_default_links(lt, tbnames[name]) except KeyError as e: print((e.message+"No autmatic links made")) newkeys = [] else: newkeys = [(key,key) for key in keys] for k1, k2 in newkeys: conds.append("%s.[%s] = %s.[%s]"%(link_replacement, k1, name, k2)) if linkall: if isinstance(linkall,str): linkall = [s.strip() for s in linkall.split(",")] for lt in linkall: tbsup.append(lt) link_replacement, link_name = _parse_table_name(lt) if keys in [None,False]: try: newkeys = qr._get_default_links(lt, tbnames[name]) except KeyError as e: print((e.message+"No autmatic links made")) newkeys = [] else: newkeys = [(key,key) for key in keys] for k1, k2 in newkeys: conds.extend(["%s.[%s] = %s.[%s]"%(link_replacement, k1, n, k2) for n in names ]) return tbsup, tbls, conds _add_doc = """ Add {tbname} table eventualy linked to some tables Parameter --------- *names : strings alternative names linkto: string/list, optional list of tables to link to should be of size of number of names linkall: string/list, optional list of tables where all elements will be linked to all entries """ def addoc(**kwargs): def _(f): f.__doc__ = _add_doc.format(**kwargs) return f return _ _link_doc = """ link {tbname} table to others """ def linkdoc(**kwargs): def _(f): f.__doc__ = _link_doc.format(**kwargs) return f return _ class query(_query): _get_default_links = _get_default_links def _is_table(self,tbl): return tbl in known_tables def _table_of(self, tbname, text): rep, tbl = _parse_table_name(text) if tbl!=rep and tbl!=tbname: raise ValueError("table defined as '%s' is not a '%s' table"%(t,text)) return "%s AS %s"%(tbname,rep) def _tables_of(self, tbname, lst): if isinstance(lst, str): lst = [t.strip() for t in lst.split(",")] return [self._table_of(tbname, s) for s in lst] def _parse_names(self,names): if isinstance(names[0], int): if len(names)!=2: raise ValueError("If list start with an number, second argument must be a format string") n, fmt = names return [fmt%i for i in range(1,n+1)] return names def _add_keys_of(self, tbname, names, linkto=None, linkall=None, keys=None): if not names: names = [tbname] names = self._parse_names(names) names = self._tables_of(tbname, names) self.add_tbls(names) if linkto: self.link(names, linkto, keys) if linkall: self.linkall(names, linkall, keys) def add_arrays(self, *names, **kwargs): """ add arrays table and eventualy link than to to others Parameters ---------- *names : list names of the arrays to add linkall : string, list, optional list of tables to link (see link_arrays) keys : string/ list, optional additional keys to link (see link_arrays) stations: list, optional stations list number to send to link_arrays """ if not names: names = ["arrays"] names = self._parse_names(names) names = self._tables_of("arrays", names) self.add_tbls(names) linkto = kwargs.get("linkto", None) linkall = kwargs.get("linkall", None) if linkto: raise ValueError("linkto is embigous for add_arrays, use linkall instead") if linkall: self.link_arrays(names,linkall,keys=kwargs.get("keys"),stations=kwargs.get("stations") ) def link_arrays(self, arrays, tbls, keys=None, stations=None): """ link a list of arrays table to other tables contrary to other link methods, the second argument can have a different length than the first one. Parameters ---------- arrays : string/list list of arrays table names tbls : string, list one or more table to link to the arrays keys : list, optional additional keys to link than the station index if none takes the default links stations : list must be of the same size than arrays default is [1,2, ..., N] where N is len(arrays) link_arrays( ["ar1","ar2], "vis2") will build ar1.STA_INDEX = vis2.STA1_INDEX AND ar2.STA_INDEX = vis2.STA2_INDEX link_arrays( ["ar1","ar2], ["v1","v2"]) will build ar1.STA_INDEX = v1.STA1_INDEX AND ar2.STA_INDEX = v1.STA2_INDEX AND ar1.STA_INDEX = v2.STA1_INDEX AND ar2.STA_INDEX = v2.STA2_INDEX """ if isinstance(arrays, str): arrays = [t.strip() for t in arrays.split(",")] arrays = self._tables_of("arrays", arrays) if stations is None: stations = list(range(1,len(arrays)+1)) k1 = ["STA_INDEX"]*len(arrays) k2 = ["STA%i_INDEX"%st for st in stations] self.linksequence(arrays,k1,tbls,k2) self.linkall(arrays,tbls) @addoc(tbname="headers") def add_headers(self, *names, **kwargs): return self._add_keys_of("headers", names, **kwargs) @linkdoc(tbname="headers") def link_headers(self, tables1, tables2, keys=None): tables1 = self._tables_of("headers", tables1) self.link(tables1, tables2, keys) @addoc(tbname="wavelengths") def add_wavelengths(self,*names,**kwargs): return self._add_keys_of("wavelengths", names, **kwargs) @linkdoc(tbname="wavelengths") def link_wavelengths(self, tables1, tables2, keys=None): tables1 = self._tables_of("wavelengths", tables1) self.link(tables1, tables2, keys) @addoc(tbname="targets") def add_targets(self,*names,**kwargs): return self._add_keys_of("targets", names, **kwargs) @linkdoc(tbname="targets") def link_targets(self, tables1, tables2, keys=None): tables1 = self._tables_of("targets", tables1) self.link(tables1, tables2, keys) @addoc(tbname="flux") def add_flux(self,*names,**kwargs): return self._add_keys_of("flux", names, **kwargs) @linkdoc(tbname="flux") def link_flux(self, tables1, tables2, keys=None): """ link headers tables """ tables1 = self._tables_of("flux", tables1) self.link(tables1, tables2, keys) @addoc(tbname="vis2") def add_vis2(self,*names,**kwargs): return self._add_keys_of("vis2", names, **kwargs) @linkdoc(tbname="vis2") def link_vis2(self, tables1, tables2, keys=None): """ link headers tables """ tables1 = self._tables_of("vis2", tables1) self.link(tables1, tables2, keys) @addoc(tbname="vis") def add_vis(self,*names,**kwargs): return self._add_keys_of("vis", names, **kwargs) @linkdoc(tbname="vis") def link_vis(self, tables1, tables2, keys=None): """ link headers tables """ tables1 = self._tables_of("vis", tables1) self.link(tables1, tables2, keys) @addoc(tbname="t3") def add_t3(self,*names,**kwargs): return self._add_keys_of("t3", names, **kwargs) @linkdoc(tbname="t3") def link_t3(self, tables1, tables2, keys=None): """ link headers tables """ tables1 = self._tables_of("t3", tables1) self.link(tables1, tables2, keys) oivisdb.OIVisDB.query = lambda self,*args, **kwargs :query(self,*args, **kwargs) # def link_bases(self, vis1, vis2, keys=["INSNAME","FILEID", "STA1_INDEX", "STA2_INDEX"]): # self.add_tbls(vis) # self.add_vis2(vis2, linkto=vis, keys=keys) # def link_vis2_vis(self, vis2, vis, keys=["INSNAME","FILEID", "STA1_INDEX", "STA2_INDEX"]): # self.add_tbls(vis2) # self.add_vis(vis, linkto=vis2, keys=keys) # def link_target_vis(self, vis2, vis, keys=["INSNAME","FILEID", "STA1_INDEX", "STA2_INDEX"]): # self.add_tbls(vis2) # self.add_vis(vis, linkto=vis2, keys=keys)