opsdb.py

from inventory_provider import db


equipment_location_query = """SELECT
                          e.absid,
                          e.name AS equipment_name,
                          p.name AS pop_name,
                          p.abbreviation AS pop_abbreviation,
                          p.site_id AS pop_site_id,
                          p.country,
                          g.longitude,
                          g.latitude
                        FROM
                          equipment e
                        INNER JOIN pop p
                          ON p.absid = e.PTR_pop
                        INNER JOIN geocoding g
                          ON g.absid = p.PTR_geocoding
                        WHERE
                          e.status != 'terminated'
                          AND e.status != 'disposed'
                        ORDER BY
                         FIELD(e.status,
                          'spare',
                          'planned',
                          'ordered',
                          'installed',
                          'operational')"""


circuit_hierarchy_query = """SELECT
                       pc.name AS parent_circuit,
                       pc.absid AS parent_circuit_id,
                       LOWER(pc.status) AS parent_circuit_status,
                       cc.name AS child_circuit,
                       cc.absid AS child_circuit_id,
                       LOWER(cc.status) AS child_circuit_status,
                       cg.segment_group AS segment_group
                     FROM circuit_glue cg
                     INNER JOIN circuit pc ON pc.absid = cg.PTR_circuit
                     INNER JOIN circuit cc ON cc.absid = cg.PTR_component"""


retrieve_services_query = """SELECT *
                    FROM (SELECT
                      c.absid AS id,
                      c.name,
                      LOWER(c.status) AS status,
                      LOWER(c.circuit_type) AS circuit_type,
                      LOWER(c.service_type) AS service_type,
                      events.short_descr AS project,
                      e.name AS equipment,
                      cc.port_a AS port,
                      cc.int_LU_a AS logical_unit,
                      LOWER(o.name) AS manufacturer,
                      LOWER(ec.card_id) AS card_id,
                      LOWER(
                        IF(pp.interface_name IS NULL,
                        '', pp.interface_name)) AS interface_name
                    FROM circuit c
                      INNER JOIN circuit_connections cc
                        ON cc.circ_absid = c.absid
                      INNER JOIN equipment e
                        ON e.absid = cc.PTR_equip_a
                      LEFT JOIN events
                        ON events.absid = cc.PTR_project
                      INNER JOIN equipment_card ec
                        ON ec.absid = cc.PTR_card_a
                      LEFT JOIN organisation o
                        ON o.absid = ec.manufacturer
                      LEFT JOIN port_plugin pp
                        ON pp.PTR_card = cc.PTR_card_a AND pp.port = cc.port_a
                    WHERE c.status != 'terminated' AND is_circuit = 1
                    UNION
                    SELECT
                      c.absid AS id,
                      c.name,
                      LOWER(c.status) AS status,
                      LOWER(c.circuit_type) AS circuit_type,
                      LOWER(c.service_type) AS service_type,
                      events.short_descr AS project,
                      e.name AS equipment,
                      cc.port_b AS port,
                      cc.int_LU_b AS logical_unit,
                      LOWER(o.name) AS manufacturer,
                      LOWER(ec.card_id) AS card_id,
                      LOWER(
                        IF(pp.interface_name IS NULL,
                        '', pp.interface_name)) AS interface_name
                    FROM circuit c
                      INNER JOIN circuit_connections cc
                        ON cc.circ_absid = c.absid
                      INNER JOIN equipment e
                        ON e.absid = cc.PTR_equip_b
                      LEFT JOIN events
                        ON events.absid = cc.PTR_project
                      INNER JOIN equipment_card ec
                        ON ec.absid = cc.PTR_card_b
                      LEFT JOIN organisation o
                        ON o.absid = ec.manufacturer
                      LEFT JOIN port_plugin pp
                        ON pp.PTR_card = cc.PTR_card_b AND pp.port = cc.port_b
                    WHERE c.status != 'terminated' AND is_circuit = 1
                    UNION
                    SELECT
                      c.absid AS id,
                      c.name,
                      LOWER(c.status) AS status,
                      LOWER(c.circuit_type) AS circuit_type,
                      LOWER(c.service_type) AS service_type,
                      events.short_descr AS project,
                      e.name AS equipment,
                      cc.port_a_OUT AS port,
                      cc.int_LU_a AS logical_unit,
                      LOWER(o.name) AS manufacturer,
                      LOWER(ec.card_id) AS card_id,
                      LOWER(
                        IF(pp.interface_name IS NULL,
                        '', pp.interface_name)) AS interface_name
                    FROM circuit c
                      INNER JOIN circuit_connections cc
                        ON cc.circ_absid = c.absid
                      INNER JOIN equipment e
                        ON e.absid = cc.PTR_equip_a
                      LEFT JOIN events
                        ON events.absid = cc.PTR_project
                      INNER JOIN equipment_card ec
                        ON ec.absid = cc.PTR_card_a_OUT
                      LEFT JOIN organisation o
                        ON o.absid = ec.manufacturer
                      LEFT JOIN port_plugin pp
                        ON pp.PTR_card = cc.PTR_card_a_OUT
                         AND pp.port = cc.port_a_OUT
                    WHERE c.status != 'terminated' AND is_circuit = 1
                    UNION
                    SELECT
                      c.absid AS id,
                      c.name,
                      LOWER(c.status) AS status,
                      LOWER(c.circuit_type) AS circuit_type,
                      LOWER(c.service_type) AS service_type,
                      events.short_descr AS project,
                      e.name AS equipment,
                      cc.port_b_OUT AS port,
                      cc.int_LU_b AS logical_unit,
                      LOWER(o.name) AS manufacturer,
                      LOWER(ec.card_id) AS card_id,
                      LOWER(
                        IF(pp.interface_name IS NULL,
                        '', pp.interface_name)) AS interface_name
                    FROM circuit c
                      INNER JOIN circuit_connections cc
                        ON cc.circ_absid = c.absid
                      INNER JOIN equipment e
                        ON e.absid = cc.PTR_equip_b
                      LEFT JOIN events
                        ON events.absid = cc.PTR_project
                      INNER JOIN equipment_card ec
                        ON ec.absid = cc.PTR_card_b_OUT
                      LEFT JOIN organisation o
                        ON o.absid = ec.manufacturer
                      LEFT JOIN port_plugin pp
                        ON pp.PTR_card = cc.PTR_card_b_OUT
                         AND pp.port = cc.port_b_OUT
                    WHERE
                     c.status != 'terminated'
                      AND is_circuit = 1)
                    AS inner_query
                    WHERE circuit_type IN ('path', 'service', 'l2circuit')
                    ORDER BY
                     FIELD(status,
                      'spare',
                      'planned',
                      'ordered',
                      'installed',
                      'operational')"""


def _convert_to_dict(crs):
    return [dict((crs.description[i][0], "" if value is None else value)
                 for i, value in enumerate(row)) for row in crs.fetchall()]


def _infinera_field_update(record):
    equipment_parts = record["equipment"].rsplit("-", 1)
    card_parts = record["card_id"].split("-", 1)
    record["interface_name"] = ""
    record["equipment"] = equipment_parts[0]
    try:
        record["interface_name"] = equipment_parts[1] + "-"
    except IndexError:
        pass  # Nothing to see here
    try:
        record["interface_name"] += card_parts[1]
    except IndexError:
        record["interface_name"] += card_parts[0]
    if record["port"] is not None and record["port"] != "":
        record["interface_name"] += "-" + record["port"]
    record["interface_name"] = record["interface_name"] \
        .replace("--", "-").upper()
    return record


def _juniper_field_update(record):
    if not record["interface_name"]:
        record["interface_name"] = record["card_id"]
        if record["port"] is not None and record["port"] != "":
            separator = "/" if "-" in record["interface_name"] else ""
            record["interface_name"] += separator + str(record["port"])
    if record["logical_unit"] is not None and record["logical_unit"] != "":
        record["interface_name"] += "." + str(record["logical_unit"])
    return record


def _coriant_field_update(record):
    record["interface_name"] = record["card_id"]
    if record["port"] is not None and record["port"] != "":
        separator = "/" if "-" in record["interface_name"] else ""
        record["interface_name"] += separator + str(record["port"])
    return record


def _update_fields(r):
    func = globals().get("_" + r["manufacturer"] + "_field_update")
    return func(r) if func else r


def get_circuits(connection):
    with db.cursor(connection) as crs:
        crs.execute(retrieve_services_query)
        r = _convert_to_dict(crs)
    r = list(map(_update_fields, r))
    return r


def get_circuit_hierarchy(connection):
    with db.cursor(connection) as crs:
        crs.execute(circuit_hierarchy_query)
        r = _convert_to_dict(crs)
    return r


def get_equipment_location_data(connection):
    with db.cursor(connection) as crs:
        crs.execute(equipment_location_query)
        r = _convert_to_dict(crs)
    return r