예제 #1
0
def edit_table(table):
    """
    Editing single table.
    """
    t = JINJA.get_template('edit_table.template')
    conn = get_conn()
    cur = conn.cursor()
    row = cur.execute("""select rt.table_name,
                                rt.uid_column,
                                rt.description,
                                rt.brief_columns
                           from reference_tables rt
                          where table_name = '%s'""" % table).fetchone()
    cur.close()
    columns = get_table_columns(table)
    columns[0] = 'None'
    html_data = {'table': table,
                 'description': row[2],
                 'uid_column': row[1],
                 'brief_columns': row[3],
                 'key_list': get_key_list(conn),
                 'column_names': columns
                 }
    t1 = from_db_cursor(conn.execute("""
             select r.uid, k.key, k.subkey, k.description, k.data_format,
                    r.reference_column, r.error_column_low,
                    r.error_column_high, r.comment,
                    "Delete"
               from keys k
               join reference_tables_keys r on r.key = k.key
                                    and ifnull(r.subkey, '') = ifnull(k.subkey, '')
              where r.reference_table = '%s'
                  order by k.key, k.subkey""" % (table)))
    html_data['keys'] = t1.get_html_string(attributes={'border': 1,
                                                       'id': 'keys_table'})
    t2 = from_db_cursor(conn.execute("""
      select column_name, data_type, data_unit,
             output_format, description, ucd
        from reference_tables_columns
       where reference_table = '%s'""" % table))
    html_data['columns'] = t2.get_html_string(attributes={'border': 1,
                                                          'id': 'columns_table'})
    conn.close()
    return t.render(html_data)
예제 #2
0
def moc_list():
    t = JINJA.get_template('moc_list.template')
    t1 = from_db_cursor(get_conn().execute("""
        select moc_name, moc_file, description, vizier_catalog, is_full_sky
          from mocs"""))
    select_moc = []
    for row in t1._rows:
        if row[0] not in select_moc:
            select_moc.append(row[0])

    return t.render({'moc_list': t1.get_html_string(attributes={'border': 1,
                                                                'id': 'moc_list'}),
                     'select_moc': select_moc})
예제 #3
0
def key_list():
    t = JINJA.get_template('key_list.template')
    t1 = from_db_cursor(get_conn().execute("""
        select key, ifnull(subkey, '') as subkey, description, data_format
          from keys k"""))
    select_key = []
    for row in t1._rows:
        if row[0] not in select_key:
            select_key.append(row[0])

    return t.render({'key_list': t1.get_html_string(attributes={'border': 1,
                                                                'id': 'key_list'}),
                     'select_key': select_key})
예제 #4
0
def list_table(table):
    """
    List the table content.
    """
    t = JINJA.get_template('list_table.template')
    conn = get_conn()
    row = conn.execute("""select uid_column, description
                            from reference_tables
                           where table_name = '%s'""" % table).fetchone()
    sql = """select d.cluster_uid, t.*
               from [{table}] t
               join data_references d
                 on d.reference_table = '{table}'
                and d.reference_uid = t.[{uid}]""".format(table=table,
                                                          uid=row[0])
    t1 = from_db_cursor(conn.execute(sql))
    columns = conn.execute("""select column_name, data_type, output_format
      from reference_tables_columns
     where reference_table = '%s'
     order by uid""" % table).fetchall()
    columns_param = ['I'] # That is for UID
    for column_name, data_type, output_format in columns:
        if data_type.lower() in ('int', 'integer', 'long'):
            t1._int_format[column_name] = output_format
            columns_param.append('I')
        elif data_type.lower() in ('float', 'double', 'real'):
            t1._float_format[column_name] = output_format
            columns_param.append('F')
        else:
            columns_param.append('S')
    html_data = {'name': table,
                 'table': t1.get_html_string(attributes={'border': 1,
                                                         'id': 'list_table',
                                                         'columns': ''.join(columns_param)}),
                 'uid': row[0],
                 'desc': row[1],
                 'sql': sql}
    return t.render(html_data)
예제 #5
0
def vo_cone_search(args):
    """
    Performs a cluster search request.
    Possible args keys are:
        ra, decl - cone center in degrees;
        radius - cone radius in arcmin;
        fullsky - perform full-sky search. Cone constraints are ignored;
        in_table - name of the table or list of table names,
                   constraint will be set on cluster presence/
                   absence in this table(s);
        has_record - list of 'exists' or 'not exists' strings,
                     one for each value in in_table;
        condition \
        in_key    |
        constraint|
        expression/
        in_moc - name of MOC or list of MOCs,
                 constraint will be set on cluster entering/
                 not entering the MOC area;
        has_moc - list of 'exists' or 'not exists' strings,
                  one for each value in in_moc;
    """
    conn = get_conn()
    conditions = []
    if 'fullsky' not in args:
        constraint, extra_columns = get_cone_search_constraint(args)
    else:
        extra_columns = ''
    if 'in_table' in args:
        if isinstance(args['in_table'], basestring):
            conditions.append(get_table_constraint(args['has_record'],
                                                   args['in_table']))
        else:
            for itable, table in enumerate(args['in_table']):
                conditions.append(
                    get_table_constraint(args['has_record'][itable], table))

    if 'condition' in args:
        extra_counter = 0
        args = dict_values_to_list(args, ['condition', 'in_key',
                                          'constraint', 'expression'])
        for icondition, condition in enumerate(args['condition']):
            key, subkey, subkey_cond = \
                get_subkey_constraint(args['in_key'][icondition])
            if condition != 'extra':
                conditions.append("""and %s (select 1
                                       from reference_tables_keys k
                                       join data_references dr on dr.reference_table = k.reference_table
                                      where dr.cluster_uid = c.uid
                                        and key = '%s'
                                        and %s)""" % (condition, key, subkey_cond))
            else:
                expr = '%s %s' % (args['constraint'][extra_counter],
                                  args['expression'][extra_counter])
                conditions.append(build_key_constraint(conn, key, subkey_cond,
                                                       expr))
                extra_counter = extra_counter + 1
    if 'has_moc' in args:
        args = dict_values_to_list(args, ['has_moc', 'in_moc'])
        for cond, moc in zip(args['has_moc'], args['in_moc']):
            conditions.append(""" and %s (select 1
                                            from cluster_in_moc m
                                           where m.uid = c.uid
                                             and m.moc_name = '%s')
                                             """ % (cond, moc))
    if 'flag_name' in args:
        args = dict_values_to_list(args, ['flag_name', 'flag_constraint',
                                          'xid_values', 'obs_values'])
        for iflag, flag in enumerate(args['flag_name']):
            if flag == 'obs_flag':
                value = args['obs_values'][iflag]
            else:
                value = args['xid_values'][iflag]
            conditions.append("""
                and %s %s %s""" % (flag, args['flag_constraint'][iflag], value))
    t = JINJA.get_template('vo_cone_search.template')

    sql = """select c.uid, %s ra, dec, c.source, source_id, xid_flag, obs_flag,
                    group_concat(distinct r.reference_table) as Tables
               from clusters c
               join data_references r on r.cluster_uid = c.uid
              where 1=1
                %s
              group by c.uid, ra, dec, c.source, source_id
              order by c.ra""" % (extra_columns, ' '.join(conditions))
    print sql
    t1 = from_db_cursor(conn.execute(sql))
    t1.float_format['ra'] = '.5f'
    t1.float_format['dec'] = '.5f'
    html_data = {'table': t1.get_html_string(attributes={'border': 1,
                                                         'id': 'search',
                                                         'columns': 'IFFSIIIS'},
                                             unescape=[('Tables')])}
    return t.render(html_data)
예제 #6
0
def single_cluster(uid):
    t = JINJA.get_template("single_cluster.template")
    CONN = get_conn(dict_row=True)
    cur = CONN.cursor()
    html_data = {}
    result = CONN.execute(
        "select source, source_id, comment, "
        "xid_flag, xid_flag_source, xid_flag_comment,"
        "obs_flag, obs_flag_source, obs_flag_comment,"
        "ra, dec"
        " from clusters where uid = %s" % uid
    ).fetchone()
    html_data = dict(result)
    for key in ["xid_flag_source", "xid_flag_comment", "obs_flag_source", "obs_flag_comment"]:
        if html_data[key] is None:
            html_data[key] = ""
    html_data["uid"] = uid
    html_data["ra"] = "%.6f" % html_data["ra"]
    html_data["dec"] = "%.6f" % html_data["dec"]
    html_data["params"] = []
    html_data["tables"] = []
    for key in CONN.cursor().execute(
        """
    select k.key, k.subkey, k.description,
           v.value, v.value_error_low, v.value_error_high,
           v.comment,
           ifnull(k.data_format, 's') output_format
      from per_cluster_keys v
      join keys k on k.key = v.key
                 and ifnull(k.subkey, 'null') = ifnull(v.subkey, 'null')
     where v.uid = %s"""
        % uid
    ):
        if key["subkey"] is not None and key["subkey"] != "":
            key_name = "%s,%s" % (key["key"], key["subkey"])
        else:
            key_name = key["key"]
        par = {
            "name": key_name,
            "desc": key["description"],
            "value": format_value(key["value"], key["output_format"]),
            "err_low": key["value_error_low"],
            "err_high": key["value_error_high"],
            "comment": key["comment"],
            "source": "User defined",
        }
        html_data["params"].append(par)
    html_data["select_key"] = get_key_list(CONN)
    mocs = []
    for row in CONN.execute(
        """select m.moc_name,
                                      coalesce(m.description, m.moc_name) as description,
                                      m.vizier_catalog
                               from cluster_in_moc c
                            join mocs m on c.moc_name = m.moc_name
                           where uid = %s
                           union
                           select mm.moc_name, mm.description, mm.vizier_catalog
                             from mocs mm where mm.is_full_sky
                             """
        % uid
    ).fetchall():
        moc = {"moc": row[0], "description": row[1], "link": ""}
        if row[2] is not None:
            url_data = {
                "-source": row[2],
                "-out.max": "unlimited",
                "-out.form": "HTML Table",
                "-out.add": "_r,_RAJ,_DEJ",
                "-sort": "_r",
                "-oc.form": "dec",
                "-c": "%+f%+f" % (float(html_data["ra"]), float(html_data["dec"])),
                "-c.eq": "J2000",
                "-c.r": 2,
                "-c.u": "arcmin",
                "-c.geom": "r",
            }
            link = "http://vizier.u-strasbg.fr/viz-bin/VizieR-4?%s" % urlencode(url_data)
            moc["link"] = '<a href="%s">Vizier data</a>' % link
        mocs.append(moc)
    html_data["mocs"] = mocs
    for row in cur.execute(
        """select rt.table_name,
                                     rt.uid_column,
                                     rt.extra_column, rt.description,
                                     rt.brief_columns
                                from reference_tables rt
                               where exists (select 1
                                               from data_references d
                                              where d.reference_table = rt.table_name
                                                and d.cluster_uid = %s)
                                  """
        % uid
    ):
        row = dict(row)
        column_list = get_brief_columns(row["table_name"], row["brief_columns"].split(","), negate=False)
        column_list = ",".join(["x.[%s]" % x for x in column_list])

        t1 = from_db_cursor(
            CONN.execute(
                """
            select {column_list}
              from [{table_name}] x
              join data_references r on r.reference_uid = [{uid_column}]
                                    and r.reference_table = '{table_name}'
             where r.cluster_uid = {uid}""".format(
                    column_list=column_list, uid=uid, **row
                )
            )
        )
        t1.border = True
        t1.float_format = ".3e"
        for column_properties in CONN.execute(
            """
        select column_name, output_format, description,
               data_unit, lower(data_type) as data_type
          from reference_tables_columns
         where reference_table = '%s'"""
            % row["table_name"]
        ).fetchall():
            if column_properties["data_type"] == "integer":
                t1.int_format[column_properties["column_name"]] = column_properties["output_format"]
            elif column_properties["data_type"] in ("real", "double"):
                t1.float_format[column_properties["column_name"]] = column_properties["output_format"]

        # Full table record (initially hidden)
        cursor = CONN.execute(
            """
            select x.*
              from [{table_name}] x
              join data_references r on r.reference_uid = [{uid_column}]
                                    and r.reference_table = '{table_name}'
             where r.cluster_uid = {uid}""".format(
                uid=uid, **row
            )
        )
        full_table = PrettiestTable()
        full_table.add_column("Parameter", [item[0] for item in cursor.description])
        for irow, arow in enumerate(cursor.fetchall()):
            full_table.add_column(str(irow), arow)
        if len(t1._rows) > 0:
            html_data["tables"].append(
                {
                    "title": row["description"],
                    "id": row["table_name"],
                    "full_table": full_table.get_html_string(attributes={"border": 1}),
                    "html": t1.get_html_string(attributes={"border": 1, "id": row["table_name"]}),
                }
            )
            html_data["params"].extend(select_cluster_key(uid, row, CONN))
    return t.render(html_data)