Esempio n. 1
0
def intersection(geom_a, geom_b):
    """Computes the interesction of two bounding boxes."""

    intersection_score = func.greatest(0,
                                       (func.least(geom_a.x2, geom_b.x2) -
                                        func.greatest(geom_a.x1, geom_b.x1))) * \
        func.greatest(0,
                      (func.least(geom_a.y2, geom_b.y2) -
                       func.greatest(geom_a.y1, geom_b.y1)))

    return intersection_score
Esempio n. 2
0
def intersection(geom_a, geom_b):
    """Computes the interesction of two bounding boxes."""

    intersection_score = func.greatest(0,
                                       (func.least(geom_a.x2, geom_b.x2) -
                                        func.greatest(geom_a.x1, geom_b.x1))) * \
        func.greatest(0,
                      (func.least(geom_a.y2, geom_b.y2) -
                       func.greatest(geom_a.y1, geom_b.y1)))

    return intersection_score
Esempio n. 3
0
 def _insert_or_update(self, timestamp, vals, lastseen=None):
     stmt = postgresql.insert(self.tables.passive).values(vals)
     index = [
         'addr', 'sensor', 'recontype', 'port', 'source', 'value',
         'targetval', 'info'
     ]
     upsert = {
         'firstseen':
         func.least(
             self.tables.passive.firstseen,
             timestamp,
         ),
         'lastseen':
         func.greatest(
             self.tables.passive.lastseen,
             lastseen or timestamp,
         ),
         'count':
         self.tables.passive.count + stmt.excluded.count,
     }
     self.db.execute(
         stmt.on_conflict_do_update(
             index_elements=index,
             set_=upsert,
         ))
Esempio n. 4
0
def find_matches(dataset, text, filter=None, exclude=None):
    entities = Entity.__table__
    match_text = normalize(text, dataset)[:254]

    # select text column and apply necesary transformations
    text_field = entities.c.name
    if dataset.normalize_text:
        text_field = entities.c.normalized
    if dataset.ignore_case:
        text_field = func.lower(text_field)
    text_field = func.left(text_field, 254)
    
    # calculate the difference percentage
    l = func.greatest(1.0, func.least(len(match_text), func.length(text_field)))
    score = func.greatest(0.0, ((l - func.levenshtein(text_field, match_text)) / l) * 100.0)
    score = func.max(score).label('score')

    # coalesce the canonical identifier
    id_ = func.coalesce(entities.c.canonical_id, entities.c.id).label('id')
    
    # apply filters
    filters = [entities.c.dataset_id==dataset.id,
               entities.c.invalid==False]
    if not dataset.match_aliases:
        filters.append(entities.c.canonical_id==None)
    if exclude is not None:
        filters.append(entities.c.id!=exclude)
    if filter is not None:
        filters.append(text_field.ilike('%%%s%%' % filter))

    q = select([id_, score], and_(*filters), [entities],
        group_by=[id_], order_by=[score.desc()])
    return Matches(q)
Esempio n. 5
0
def top_10_fields_by_prob():
    a = LocalizationTile
    b = FieldTile
    a_lo = a.nested_lo.label('a_lo')
    a_hi = a.nested_hi.label('a_hi')
    b_lo = b.nested_lo.label('b_lo')
    b_hi = b.nested_hi.label('b_hi')

    query1 = db.session.query(
        a_lo, a_hi, b_lo, b_hi, FieldTile.field_id.label('field_id'),
        LocalizationTile.localization_id.label('localization_id'),
        LocalizationTile.probdensity.label('probdensity'))

    query2 = union(
        query1.join(b, a_lo.between(b_lo, b_hi)),
        query1.join(b, b_lo.between(a_lo, a_hi)),
    ).cte()

    lo = func.greatest(query2.c.a_lo, query2.c.b_lo)
    hi = func.least(query2.c.a_hi, query2.c.b_hi)
    area = (hi - lo + 1) * healpix.PIXEL_AREA
    prob = func.sum(query2.c.probdensity * area).label('probability')

    query = db.session.query(query2.c.localization_id, query2.c.field_id,
                             prob).group_by(query2.c.localization_id,
                                            query2.c.field_id).order_by(
                                                prob.desc()).limit(10)

    return query.all()
Esempio n. 6
0
 def _insert_or_update(self, timestamp, values, lastseen=None):
     stmt = insert(self.tables.passive)\
         .values(dict(values, addr=utils.force_int2ip(values['addr'])))
     try:
         self.db.execute(stmt)
     except IntegrityError:
         whereclause = and_(
             self.tables.passive.addr == values['addr'],
             self.tables.passive.sensor == values['sensor'],
             self.tables.passive.recontype == values['recontype'],
             self.tables.passive.source == values['source'],
             self.tables.passive.value == values['value'],
             self.tables.passive.targetval == values['targetval'],
             self.tables.passive.info == values['info'],
             self.tables.passive.port == values['port'])
         upsert = {
             'firstseen':
             func.least(
                 self.tables.passive.firstseen,
                 timestamp,
             ),
             'lastseen':
             func.greatest(
                 self.tables.passive.lastseen,
                 lastseen or timestamp,
             ),
             'count':
             self.tables.passive.count + values['count'],
         }
         updt = update(
             self.tables.passive).where(whereclause).values(upsert)
         self.db.execute(updt)
Esempio n. 7
0
 def _insert_or_update(self, timestamp, values, lastseen=None):
     stmt = insert(self.tables.passive)\
         .values(dict(values, addr=utils.force_int2ip(values['addr'])))
     try:
         self.db.execute(stmt)
     except IntegrityError:
         whereclause = and_(
             self.tables.passive.addr == values['addr'],
             self.tables.passive.sensor == values['sensor'],
             self.tables.passive.recontype == values['recontype'],
             self.tables.passive.source == values['source'],
             self.tables.passive.value == values['value'],
             self.tables.passive.targetval == values['targetval'],
             self.tables.passive.info == values['info'],
             self.tables.passive.port == values['port']
         )
         upsert = {
             'firstseen': func.least(
                 self.tables.passive.firstseen,
                 timestamp,
             ),
             'lastseen': func.greatest(
                 self.tables.passive.lastseen,
                 lastseen or timestamp,
             ),
             'count': self.tables.passive.count + values['count'],
         }
         updt = update(
             self.tables.passive
         ).where(whereclause).values(upsert)
         self.db.execute(updt)
def get_observation_nearest_query(args):
    '''Get an observation of the specified feature from the node nearest
    to the provided long, lat coordinates.

    :param args: (ValidatorResult) validated query arguments
    '''

    # TODO(heyzoos)
    # [ ] Test me! Specifically test property filtering.

    lng = args.data['lng']
    lat = args.data['lat']
    feature = args.data['feature']
    network = args.data['network']
    point_dt = args.data['datetime'] if args.data.get('datetime') else datetime.now()
    conditions = args.data.get('filter')

    nearest_nodes_rp = NodeMeta.nearest_neighbor_to(
        lng, lat, network=network.name, features=[feature.name]
    )

    if not nearest_nodes_rp:
        return 'No nodes could be found nearby with your target feature.'

    feature_str = '{}__{}'.format(network.name, feature.name)
    feature = redshift_base.metadata.tables[feature_str]

    result = None
    for row in nearest_nodes_rp:
        query = redshift_session.query(feature).filter(and_(
            feature.c.node_id == row.node,
            feature.c.datetime <= point_dt + timedelta(hours=12),
            feature.c.datetime >= point_dt - timedelta(hours=12)
        ))

        if conditions is not None:
            query = query.filter(conditions)

        query = query.order_by(
            asc(
                # Ensures that the interval values is always positive,
                # since the abs() function doesn't work for intervals
                sqla_fn.greatest(point_dt, feature.c.datetime) -
                sqla_fn.least(point_dt, feature.c.datetime)
            )
        )

        # Magic number 3 because IFTTT tests require at least three results
        result = query.limit(3).all()

        if result is not None:
            break

    if result is None:
        return 'Your feature has not been reported on by the nearest 10 ' \
               'nodes at the time provided.'
    return [format_observation(obs, feature) for obs in result]
Esempio n. 9
0
def get_band(session, dataset, freq_eff, freq_bw, freq_bw_max=.0):
    """
    Returns the frequency band for the given frequency parameters. Will create a new frequency band entry in the
    database if no match is found. You can limit the bandwidth of the band association with the freq_bw_max.

    args:
        session (sqlalchemy.orm.session.Session): a SQLAlchemy session object
        dataset (tkp.db.model.Dataset): the TraP dataset
        freq_eff (float): The central frequency of image to get band for
        freq_bw (float): The bandwidth of image to get band for
        freq_bw_max (float): The maximum bandwith used for band association. Not used if 0.0 (default).

    returns:
        tkp.db.model.Frequencyband: a frequency band object
    """
    
    if freq_bw_max == .0:
        bw_half = freq_bw / 2
        low = freq_eff - bw_half
        high = freq_eff + bw_half
    else:
        bw_half = freq_bw_max / 2
        low = freq_eff - bw_half
        high = freq_eff + bw_half

    w1 = high - low
    w2 = Frequencyband.freq_high - Frequencyband.freq_low
    max_ = func.greatest(high, Frequencyband.freq_high)
    min_ = func.least(low, Frequencyband.freq_low)

    band = session.query(Frequencyband).filter(
        (Frequencyband.dataset == dataset) & (max_ - min_ < w1 + w2)
    ).first()
    
    if not band:
        # no match so we create a new band
        band = Frequencyband(freq_central=freq_eff, freq_low=low, freq_high=high, dataset=dataset)
        session.add(band)

    return band
Esempio n. 10
0
def decayed_score(score,
                  created_at,
                  peak=5,
                  nominal_timestamp=14 * 24 * 60 * 60):
    """
    Creates a decaying (over time) version of the provided `score`. The returned
    value is score * a multiplier determined by `peak` and `nominal_timestamp`.

    Args:
        score: (number) The base score to modify
        created_at: (timestamp) The timestamp the score is attributed to
        peak?: (number) The peak multipler possible

    Returns:
        A SQLAlchemy expression representing decayed score (score * multipler)
        where multipler is represented by:
        max(0.2, 5 ^ 1 - min(time_ago / nominal_timestamp, 1))
    """
    return score * func.greatest(
        func.pow(
            5, 1 - func.least(seconds_ago(created_at) / nominal_timestamp, 1)),
        0.2)
Esempio n. 11
0
def find_matches(dataset, text, filter=None, exclude=None):
    entities = Entity.__table__
    match_text = (normalize(text) or '')[:254]

    # select text column and apply necesary transformations
    text_field = entities.c.name
    if dataset.normalize_text:
        text_field = entities.c.normalized
    if dataset.ignore_case:
        text_field = func.lower(text_field)
    text_field = func.left(text_field, 254)

    # calculate the difference percentage
    min_l = func.greatest(1.0,
                          func.least(len(match_text), func.length(text_field)))
    score = func.greatest(
        0.0,
        ((min_l - func.levenshtein(text_field, match_text)) / min_l) * 100.0)
    score = func.max(score).label('score')

    # coalesce the canonical identifier
    id_ = func.coalesce(entities.c.canonical_id, entities.c.id).label('id')

    # apply filters
    filters = [
        entities.c.dataset_id == dataset.id, entities.c.invalid == False
    ]  # noqa
    if not dataset.match_aliases:
        filters.append(entities.c.canonical_id == None)  # noqa
    if exclude is not None:
        filters.append(entities.c.id != exclude)
    if filter is not None:
        filters.append(text_field.ilike('%%%s%%' % filter))

    q = select([id_, score],
               and_(*filters), [entities],
               group_by=[id_],
               order_by=[score.desc()])
    return Matches(q)
Esempio n. 12
0
def find_matches(project, account, text, schemata=[], properties=[]):
    main = aliased(Property)
    ent = aliased(Entity)
    q = db.session.query(main.entity_id)
    q = q.filter(main.name == "name")
    q = q.filter(main.entity_id == ent.id)
    q = q.join(ent)
    q = q.filter(ent.project_id == project.id)

    for schema in schemata:
        obj = aliased(Schema)
        q = q.join(obj, ent.schema_id == obj.id)
        q = q.filter(obj.name == schema)

    for name, value in properties:
        p = aliased(Property)
        q = q.join(p, p.entity_id == ent.id)
        q = q.filter(p.active == True)  # noqa
        q = q.filter(p.name == name)
        attr = project.get_attribute("entity", name)
        column = getattr(p, attr.value_column)
        q = q.filter(column == value)

    # prepare text fields (todo: further normalization!)
    text_field = func.left(func.lower(main.value_string), 254)
    match_text = text.lower().strip()[:254]
    match_text_db = cast(match_text, types.Unicode)

    # calculate the difference percentage
    l = func.greatest(1.0, func.least(len(match_text), func.length(text_field)))
    score = func.greatest(0.0, ((l - func.levenshtein(text_field, match_text_db)) / l) * 100.0)
    score = score.label("score")
    q = q.add_columns(score)
    q = q.order_by(score.desc())
    q = q.filter(score > 50)

    return Matches(q, account)
Esempio n. 13
0
 def _insert_or_update(self,
                       timestamp,
                       values,
                       lastseen=None,
                       replacecount=False):
     stmt = insert(self.tables.passive).values(
         dict(values, addr=utils.force_int2ip(values["addr"])))
     try:
         self.db.execute(stmt)
     except IntegrityError:
         whereclause = and_(
             self.tables.passive.addr == values["addr"],
             self.tables.passive.sensor == values["sensor"],
             self.tables.passive.recontype == values["recontype"],
             self.tables.passive.source == values["source"],
             self.tables.passive.value == values["value"],
             self.tables.passive.targetval == values["targetval"],
             self.tables.passive.info == values["info"],
             self.tables.passive.port == values["port"],
         )
         upsert = {
             "firstseen":
             func.least(
                 self.tables.passive.firstseen,
                 timestamp,
             ),
             "lastseen":
             func.greatest(
                 self.tables.passive.lastseen,
                 lastseen or timestamp,
             ),
             "count": (values["count"] if replacecount else
                       self.tables.passive.count + values["count"]),
         }
         updt = update(
             self.tables.passive).where(whereclause).values(upsert)
         self.db.execute(updt)
Esempio n. 14
0
def find_matches(project, account, text, schemata=[], properties=[]):
    main = aliased(Property)
    ent = aliased(Entity)
    q = db.session.query(main.entity_id)
    q = q.filter(main.name == 'name')
    q = q.filter(main.entity_id == ent.id)
    q = q.join(ent)
    q = q.filter(ent.project_id == project.id)

    if len(schemata):
        obj = aliased(Schema)
        q = q.join(obj, ent.schema_id == obj.id)
        q = q.filter(obj.name.in_(schemata))

    for name, value in properties:
        p = aliased(Property)
        q = q.join(p, p.entity_id == ent.id)
        q = q.filter(p.active == True) # noqa
        q = q.filter(p.name == name)
        column = getattr(p, p.type_column(value))
        q = q.filter(column == value)

    # prepare text fields (todo: further normalization!)
    text_field = func.left(func.lower(main.value_string), 254)
    match_text = text.lower().strip()[:254]
    match_text_db = cast(match_text, types.Unicode)

    # calculate the difference percentage
    l = func.greatest(1.0, func.least(len(match_text), func.length(text_field)))
    score = func.greatest(0.0, ((l - func.levenshtein(text_field, match_text_db)) / l) * 100.0)
    score = score.label('score')
    q = q.group_by(main.entity_id)
    q = q.add_columns(func.max(score))
    q = q.order_by(func.max(score).desc())
    q = q.filter(score > 50)
    return Matches(q, project, account)
Esempio n. 15
0
              'product_code': pct.code,
              'product_name': pct.name,
              'product_type': pt.name,
              'promo': pro.code,
              'qty': oi.qty,
              'reference': o.reference_number,
              'rework_date': r.rework_date,
              'rework_qty': r.qty,
              'rework_reason': rr.name,
              'ship_country': c.name,
              'ship_date': i.ship_date,
              'ship_method': sm.name,
              'state': s.name,
              'theme': t.name,
              'turnaround':
                select([func.least(func.sum(cal.is_business_day), 7)])
                .where((cal.calendar_date >= func.date(o.order_date)) &
                       (cal.calendar_date <= i.ship_date))}
for d in DIMENSIONS: DIMENSIONS[d] = DIMENSIONS[d].label(d)

JOINS = {a:   a.address_id == o.shipping_address_id,
         act: wi.activity_id == act.activity_id,
         c:   a.country == c.country_code,
         can: (can.order_item_id == oi.order_item_id) &
              can.activity_id.in_([42, 43]),
         cu:  cu.user_id == can.user_id,
         cur: cur.currency_id == o.currency_id,
         cus: cus.customer_id == o.customer_id,
         cc:  oif.cover_color_id == cc.cover_color_id,
         cm:  oif.cover_material_id == cm.cover_material_id,
         dwn: pi.download_path_id == dwn.uri_resource_id,
Esempio n. 16
0
 def _least(cls, *args):
     """
     Produce a "least" sqlalchemy expression (minimum between values)
     """
     #return cls._if(a < b, a, b)
     return func.least(*args)
Esempio n. 17
0
                 primary_key=True, nullable=False,
                 onupdate="CASCADE"),
          Column('created', DateTime(timezone=False), default=datetime.utcnow,
                 nullable=False),
          UniqueConstraint('question_parent_id', 'question_child_id',
                           name='question_parent_child_uix')
          )

# We want to prevent redundant, bidirectional links in tbl
# e.g.: (question_parent_id, question_child_id) ==
#       (question_child_id, question_parent_id)
# i.e.:
# create unique index on question_to_questions (least(A,B), greatest(A,B));
Index(
    'question_to_questions_uix',
    func.least(question_questions.c.question_parent_id,
               question_questions.c.question_child_id),
    func.greatest(question_questions.c.question_parent_id,
                  question_questions.c.question_child_id))


class User(core.Base):
    __tablename__ = "users"

    id = Column(BigInteger, primary_key=True)
    username = Column(Unicode, unique=True)
    email = Column(Unicode, unique=True)
    tags_subscriptions = relationship('Tag',
                                      'user_to_tags',
                                      backref="subscribed_users")

    def dict(self, verbose=False, minimal=False):
Esempio n. 18
0
def fetch_billing_for_all_services():
    breakdown_period = 'quarter'

    free_allowances = fetch_sms_free_allowances().subquery()
    query_cte = dao_get_priced_billing_data().cte()

    # date breakdowns
    breakdown_aet = func.date_trunc(breakdown_period,
                                    query_cte.c.aet_date).cast(Date())
    breakdown_fy = func.date_trunc(breakdown_period,
                                   query_cte.c.fy_date).cast(Date())
    financial_year = func.date_part('year',
                                    query_cte.c.fy_date).cast(Integer())

    # calculate in credits in order to work backwards to get available units
    # after removing cost usage
    credits_free = free_allowances.c.free_sms_fragment_limit * FRAGMENT_UNIT_RATE

    # coalesce and sum these totals by month
    cost = func.sum(query_cte.c.cost)
    notifications = func.sum(query_cte.c.notifications_sent)
    notifications_email = func.sum(query_cte.c.notifications_sent).filter(
        query_cte.c.notification_type == EMAIL_TYPE)
    notifications_sms = func.sum(query_cte.c.notifications_sent).filter(
        query_cte.c.notification_type == SMS_TYPE)
    fragments_domestic = func.sum(query_cte.c.domestic_units)
    fragments_international = func.sum(query_cte.c.international_units)

    # cumulative figures for entire year
    cost_cumulative = func.sum(cost).over(order_by=breakdown_fy,
                                          partition_by=(Service.id,
                                                        financial_year))
    cost_starting = cost_cumulative - cost
    credits_remaining = func.greatest(credits_free - cost_cumulative, 0)
    credits_available = func.greatest(credits_free - cost_starting, 0)
    cost_chargeable = func.greatest(cost - credits_available, 0)
    cost_chargeable_cumulative = func.greatest(cost_cumulative - credits_free,
                                               0)

    credits_used = func.least(cost, credits_available)
    units = cost / FRAGMENT_UNIT_RATE
    units_cumulative = cost_cumulative / FRAGMENT_UNIT_RATE
    units_chargeable = cost_chargeable / FRAGMENT_UNIT_RATE
    units_free_used = credits_used / FRAGMENT_UNIT_RATE
    units_free_available = credits_available / FRAGMENT_UNIT_RATE
    units_free_remaining = credits_remaining / FRAGMENT_UNIT_RATE
    units_chargeable_cumulative = cost_chargeable_cumulative / FRAGMENT_UNIT_RATE

    qry = db.session.query(
        Service.id.label('service_id'),
        Service.name.label('service_name'),
        breakdown_aet.label('breakdown_aet'),
        breakdown_fy.label('breakdown_fy'),
        func.date_part('year', breakdown_fy).cast(
            Integer()).label('breakdown_fy_year'),
        func.date_part('quarter', breakdown_fy).cast(
            Integer()).label('breakdown_fy_quarter'),
        free_allowances.c.free_sms_fragment_limit.label(
            'fragments_free_limit'),
        fragments_domestic.label('fragments_domestic'),
        fragments_international.label('fragments_international'),
        notifications.label('notifications'),
        notifications_sms.label('notifications_sms'),
        notifications_email.label('notifications_email'),
        cost.label('cost'),
        cost_chargeable.label('cost_chargeable'),
        cost_cumulative.label('cost_cumulative'),
        cost_chargeable_cumulative.label('cost_chargeable_cumulative'),
        units.label('units'),
        units_cumulative.label('units_cumulative'),
        units_chargeable.label('units_chargeable'),
        units_chargeable_cumulative.label('units_chargeable_cumulative'),
        units_free_available.label('units_free_available'),
        units_free_remaining.label('units_free_remaining'),
        units_free_used.label('units_free_used'),
        literal(DOMESTIC_UNIT_RATE * 100).label('unit_rate_domestic'),
        literal(INTERNATIONAL_UNIT_RATE *
                100).label('unit_rate_international'),
    ).select_from(Service, ).join(
        query_cte,
        query_cte.c.service_id == Service.id,
    ).join(
        free_allowances,
        and_(
            Service.id == free_allowances.c.service_id,
            financial_year == free_allowances.c.financial_year_start,
        )).group_by(
            Service.id,
            Service.name,
            financial_year,
            breakdown_aet,
            breakdown_fy,
            free_allowances.c.free_sms_fragment_limit,
        ).order_by(
            Service.name,
            breakdown_fy,
        )
    return qry.all()
Esempio n. 19
0
    def get_direct_messages_previews(cls, user_id, team_id):
        chats = db.session.query(ChatTableEntry).filter(
            ChatTableEntry.user_id == user_id).subquery("sq1")

        last_messages_mixed = db.session.query(
            func.least(MessageTableEntry.sender_id,
                       MessageTableEntry.receiver_id).label("user1"),
            func.greatest(MessageTableEntry.sender_id,
                          MessageTableEntry.receiver_id).label("user2"),
            func.max(
                MessageTableEntry.timestamp).label("maxtimestamp")).filter(
                    and_(
                        or_(MessageTableEntry.receiver_id == user_id,
                            MessageTableEntry.sender_id == user_id),
                        MessageTableEntry.team_id == team_id,
                        MessageTableEntry.send_type ==
                        SendMessageType.DIRECT.value)).group_by(
                            func.least(MessageTableEntry.sender_id,
                                       MessageTableEntry.receiver_id),
                            func.greatest(
                                MessageTableEntry.sender_id,
                                MessageTableEntry.receiver_id)).subquery("sq2")

        last_messages = db.session.query(
            MessageTableEntry.message_id, MessageTableEntry.sender_id,
            MessageTableEntry.receiver_id, UserTableEntry.username,
            UserTableEntry.first_name, UserTableEntry.last_name,
            UserTableEntry.profile_pic, UserTableEntry.online,
            BotTableEntry.bot_name, MessageTableEntry.content,
            MessageTableEntry.message_type, MessageTableEntry.timestamp,
            UserTableEntry.user_id.label("is_user"), chats.c.unseen
        ).join(
            last_messages_mixed,
            and_(
                or_(
                    MessageTableEntry.sender_id == last_messages_mixed.c.user1,
                    MessageTableEntry.sender_id == last_messages_mixed.c.user2,
                ),
                or_(
                    MessageTableEntry.receiver_id ==
                    last_messages_mixed.c.user1,
                    MessageTableEntry.receiver_id ==
                    last_messages_mixed.c.user2,
                ), MessageTableEntry.timestamp ==
                last_messages_mixed.c.maxtimestamp,
                MessageTableEntry.send_type == SendMessageType.DIRECT.value)
        ).join(
            chats,
            and_(
                or_(
                    MessageTableEntry.sender_id == chats.c.chat_id,
                    MessageTableEntry.receiver_id == chats.c.chat_id,
                ),
                or_(
                    MessageTableEntry.sender_id == chats.c.user_id,
                    MessageTableEntry.receiver_id == chats.c.user_id,
                ))
        ).outerjoin(
            UserTableEntry,
            or_(
                and_(UserTableEntry.user_id == last_messages_mixed.c.user1,
                     UserTableEntry.user_id != user_id),
                and_(UserTableEntry.user_id == last_messages_mixed.c.user2,
                     UserTableEntry.user_id != user_id))
        ).outerjoin(
            BotTableEntry,
            or_(
                and_(BotTableEntry.bot_id == last_messages_mixed.c.user1,
                     BotTableEntry.bot_id != user_id),
                and_(BotTableEntry.bot_id == last_messages_mixed.c.user2,
                     BotTableEntry.bot_id != user_id))).all()

        return MessageModelMapper.to_direct_messages_previews(last_messages)
Esempio n. 20
0
 def topvalues(self,
               field,
               flt=None,
               topnbr=10,
               sort=None,
               limit=None,
               skip=None,
               least=False):
     """
     This method makes use of the aggregation framework to produce
     top values for a given field or pseudo-field. Pseudo-fields are:
       - category / label / asnum / country / net[:mask]
       - port
       - port:open / :closed / :filtered / :<servicename>
       - portlist:open / :closed / :filtered
       - countports:open / :closed / :filtered
       - service / service:<portnbr>
       - product / product:<portnbr>
       - cpe / cpe.<part> / cpe:<cpe_spec> / cpe.<part>:<cpe_spec>
       - devicetype / devicetype:<portnbr>
       - script:<scriptid> / script:<port>:<scriptid>
         / script:host:<scriptid>
       - cert.* / smb.* / sshkey.*
       - httphdr / httphdr.{name,value} / httphdr:<name>
       - modbus.* / s7.* / enip.*
       - mongo.dbs.*
       - vulns.*
       - screenwords
       - file.* / file.*:scriptid
       - hop
     """
     if flt is None:
         flt = self.flt_empty
     base = flt.query(
         select([self.tables.scan.id
                 ]).select_from(flt.select_from)).cte("base")
     order = "count" if least else desc("count")
     outputproc = None
     if field == "port":
         field = self._topstructure(
             self.tables.port,
             [self.tables.port.protocol, self.tables.port.port],
             self.tables.port.state == "open")
     elif field == "ttl":
         field = self._topstructure(
             self.tables.port,
             [self.tables.port.state_reason_ttl],
             self.tables.port.state_reason_ttl != None,
             # noqa: E711 (BinaryExpression)
         )
     elif field == "ttlinit":
         field = self._topstructure(
             self.tables.port,
             [
                 func.least(
                     255,
                     func.power(
                         2,
                         func.ceil(
                             func.log(2,
                                      self.tables.port.state_reason_ttl))))
             ],
             self.tables.port.state_reason_ttl != None,
             # noqa: E711 (BinaryExpression)
         )
         outputproc = int
     elif field.startswith('port:'):
         info = field[5:]
         field = self._topstructure(
             self.tables.port,
             [self.tables.port.protocol, self.tables.port.port],
             (self.tables.port.state == info)
             if info in ['open', 'filtered', 'closed', 'open|filtered'] else
             (self.tables.port.service_name == info),
         )
     elif field.startswith('countports:'):
         info = field[11:]
         return (
             {"count": result[0], "_id": result[1]}
             for result in self.db.execute(
                 select([func.count().label("count"),
                         column('cnt')])
                 .select_from(
                     select([func.count().label('cnt')])
                     .select_from(self.tables.port)
                     .where(and_(
                         self.tables.port.state == info,
                         # self.tables.port.scan.in_(base),
                         exists(
                             select([1])\
                             .select_from(base)\
                             .where(
                                 self.tables.port.scan == base.c.id
                             )
                         ),
                     ))\
                     .group_by(self.tables.port.scan)\
                     .alias('cnt')
                 ).group_by('cnt').order_by(order).limit(topnbr)
             )
         )
     elif field.startswith('portlist:'):
         ### Deux options pour filtrer:
         ###   -1- self.tables.port.scan.in_(base),
         ###   -2- exists(select([1])\
         ###       .select_from(base)\
         ###       .where(
         ###         self.tables.port.scan == base.c.id
         ###       )),
         ###
         ### D'après quelques tests, l'option -1- est plus beaucoup
         ### rapide quand (base) est pas ou peu sélectif, l'option
         ### -2- un peu plus rapide quand (base) est très sélectif
         ###
         ### TODO: vérifier si c'est pareil pour:
         ###  - countports:open
         ###  - tous les autres
         info = field[9:]
         return (
             {
                 "count":
                 result[0],
                 "_id": [(proto, int(port)) for proto, port in (
                     elt.split(',')
                     for elt in result[1][3:-3].split(')","('))]
             } for result in self.db.execute(
                 select([func.count().label("count"),
                         column('ports')]).
                 select_from(
                     select([
                         func.array_agg(
                             postgresql.aggregate_order_by(
                                 tuple_(self.tables.port.protocol, self.
                                        tables.port.port).label('a'),
                                 tuple_(
                                     self.tables.port.protocol, self.tables.
                                     port.port).label('a'))).label('ports'),
                     ]).where(
                         and_(
                             self.tables.port.state == info,
                             self.tables.port.scan.in_(
                                 base),
                             # exists(select([1])\
                             #        .select_from(base)\
                             #        .where(
                             #            self.tables.port.scan == base.c.id
                             #        )),
                         )).group_by(self.tables.port.scan).alias('ports')
                 ).group_by('ports').order_by(order).limit(topnbr)))
     elif field == "service":
         field = self._topstructure(self.tables.port,
                                    [self.tables.port.service_name],
                                    self.tables.port.state == "open")
     elif field.startswith("service:"):
         info = field[8:]
         if '/' in info:
             info = info.split('/', 1)
             field = self._topstructure(
                 self.tables.port,
                 [self.tables.port.service_name],
                 and_(self.tables.port.protocol == info[0],
                      self.tables.port.port == int(info[1])),
             )
         else:
             field = self._topstructure(self.tables.port,
                                        [self.tables.port.service_name],
                                        self.tables.port.port == int(info))
     elif field == "product":
         field = self._topstructure(
             self.tables.port,
             [
                 self.tables.port.service_name,
                 self.tables.port.service_product
             ],
             self.tables.port.state == "open",
         )
     elif field.startswith("product:"):
         info = field[8:]
         if info.isdigit():
             info = int(info)
             flt = self.flt_and(flt, self.searchport(info))
             field = self._topstructure(
                 self.tables.port,
                 [
                     self.tables.port.service_name,
                     self.tables.port.service_product
                 ],
                 and_(self.tables.port.state == "open",
                      self.tables.port.port == info),
             )
         elif info.startswith('tcp/') or info.startswith('udp/'):
             info = (info[:3], int(info[4:]))
             flt = self.flt_and(flt,
                                self.searchport(info[1], protocol=info[0]))
             field = self._topstructure(
                 self.tables.port,
                 [
                     self.tables.port.service_name,
                     self.tables.port.service_product
                 ],
                 and_(self.tables.port.state == "open",
                      self.tables.port.port == info[1],
                      self.tables.port.protocol == info[0]),
             )
         else:
             flt = self.flt_and(flt, self.searchservice(info))
             field = self._topstructure(
                 self.tables.port,
                 [
                     self.tables.port.service_name,
                     self.tables.port.service_product
                 ],
                 and_(self.tables.port.state == "open",
                      self.tables.port.service_name == info),
             )
     elif field == "devicetype":
         field = self._topstructure(self.tables.port,
                                    [self.tables.port.service_devicetype],
                                    self.tables.port.state == "open")
     elif field.startswith("devicetype:"):
         info = field[11:]
         if info.isdigit():
             info = int(info)
             flt = self.flt_and(flt, self.searchport(info))
             field = self._topstructure(
                 self.tables.port, [self.tables.port.service_devicetype],
                 and_(self.tables.port.state == "open",
                      self.tables.port.port == info))
         elif info.startswith('tcp/') or info.startswith('udp/'):
             info = (info[:3], int(info[4:]))
             flt = self.flt_and(flt,
                                self.searchport(info[1], protocol=info[0]))
             field = self._topstructure(
                 self.tables.port, [self.tables.port.service_devicetype],
                 and_(self.tables.port.state == "open",
                      self.tables.port.port == info[1],
                      self.tables.port.protocol == info[0]))
         else:
             flt = self.flt_and(flt, self.searchservice(info))
             field = self._topstructure(
                 self.tables.port, [self.tables.port.service_devicetype],
                 and_(self.tables.port.state == "open",
                      self.tables.port.service_name == info))
     elif field == "version":
         field = self._topstructure(
             self.tables.port,
             [
                 self.tables.port.service_name,
                 self.tables.port.service_product,
                 self.tables.port.service_version
             ],
             self.tables.port.state == "open",
         )
     elif field.startswith("version:"):
         info = field[8:]
         if info.isdigit():
             info = int(info)
             flt = self.flt_and(flt, self.searchport(info))
             field = self._topstructure(
                 self.tables.port,
                 [
                     self.tables.port.service_name,
                     self.tables.port.service_product,
                     self.tables.port.service_version
                 ],
                 and_(self.tables.port.state == "open",
                      self.tables.port.port == info),
             )
         elif info.startswith('tcp/') or info.startswith('udp/'):
             info = (info[:3], int(info[4:]))
             flt = self.flt_and(flt,
                                self.searchport(info[1], protocol=info[0]))
             field = self._topstructure(
                 self.tables.port,
                 [
                     self.tables.port.service_name,
                     self.tables.port.service_product,
                     self.tables.port.service_version
                 ],
                 and_(self.tables.port.state == "open",
                      self.tables.port.port == info[1],
                      self.tables.port.protocol == info[0]),
             )
         elif ':' in info:
             info = info.split(':', 1)
             flt = self.flt_and(
                 flt, self.searchproduct(info[1], service=info[0]))
             field = self._topstructure(
                 self.tables.port,
                 [
                     self.tables.port.service_name,
                     self.tables.port.service_product,
                     self.tables.port.service_version
                 ],
                 and_(self.tables.port.state == "open",
                      self.tables.port.service_name == info[0],
                      self.tables.port.service_product == info[1]),
             )
         else:
             flt = self.flt_and(flt, self.searchservice(info))
             field = self._topstructure(
                 self.tables.port,
                 [
                     self.tables.port.service_name,
                     self.tables.port.service_product,
                     self.tables.port.service_version
                 ],
                 and_(self.tables.port.state == "open",
                      self.tables.port.service_name == info),
             )
     elif field == "asnum":
         field = self._topstructure(self.tables.scan,
                                    [self.tables.scan.info["as_num"]])
     elif field == "as":
         field = self._topstructure(self.tables.scan, [
             self.tables.scan.info["as_num"],
             self.tables.scan.info["as_name"]
         ])
     elif field == "country":
         field = self._topstructure(self.tables.scan, [
             self.tables.scan.info["country_code"],
             self.tables.scan.info["country_name"]
         ])
     elif field == "city":
         field = self._topstructure(self.tables.scan, [
             self.tables.scan.info["country_code"],
             self.tables.scan.info["city"]
         ])
     elif field == "net" or field.startswith("net:"):
         info = field[4:]
         info = int(info) if info else 24
         field = self._topstructure(
             self.tables.scan,
             [func.set_masklen(text("scan.addr::cidr"), info)],
         )
     elif field == "script" or field.startswith("script:"):
         info = field[7:]
         if info:
             field = self._topstructure(self.tables.script,
                                        [self.tables.script.output],
                                        self.tables.script.name == info)
         else:
             field = self._topstructure(self.tables.script,
                                        [self.tables.script.name])
     elif field in ["category", "categories"]:
         field = self._topstructure(self.tables.category,
                                    [self.tables.category.name])
     elif field.startswith('cert.'):
         subfield = field[5:]
         field = self._topstructure(
             self.tables.script,
             [self.tables.script.data['ssl-cert'][subfield]],
             and_(self.tables.script.name == 'ssl-cert',
                  self.tables.script.data['ssl-cert'].has_key(
                      subfield)))  # noqa: W601 (BinaryExpression)
     elif field == "source":
         field = self._topstructure(self.tables.scan,
                                    [self.tables.scan.source])
     elif field == "domains":
         field = self._topstructure(
             self.tables.hostname,
             [func.unnest(self.tables.hostname.domains)])
     elif field.startswith("domains:"):
         level = int(field[8:]) - 1
         base1 = (select([
             func.unnest(self.tables.hostname.domains).label("domains")
         ]).where(
             exists(
                 select([1]).select_from(base).where(
                     self.tables.hostname.scan == base.c.id))).cte("base1"))
         return ({
             "count": result[1],
             "_id": result[0]
         } for result in self.db.execute(
             select([base1.c.domains,
                     func.count().label("count")]).where(
                         base1.c.domains.op('~')
                         ('^([^\\.]+\\.){%d}[^\\.]+$' %
                          level)).group_by(base1.c.domains).order_by(
                              order).limit(topnbr)))
     elif field == "hop":
         field = self._topstructure(self.tables.hop,
                                    [self.tables.hop.ipaddr])
     elif field.startswith('hop') and field[3] in ':>':
         ttl = int(field[4:])
         field = self._topstructure(
             self.tables.hop,
             [self.tables.hop.ipaddr],
             (self.tables.hop.ttl > ttl) if field[3] == '>' else
             (self.tables.hop.ttl == ttl),
         )
     elif field == 'file' or (field.startswith('file')
                              and field[4] in '.:'):
         if field.startswith('file:'):
             scripts = field[5:]
             if '.' in scripts:
                 scripts, field = scripts.split('.', 1)
             else:
                 field = 'filename'
             scripts = scripts.split(',')
             flt = (self.tables.script.name == scripts[0] if len(scripts)
                    == 1 else self.tables.script.name.in_(scripts))
         else:
             field = field[5:] or 'filename'
             flt = True
         field = self._topstructure(
             self.tables.script,
             [
                 func.jsonb_array_elements(
                     func.jsonb_array_elements(
                         self.tables.script.data['ls']['volumes']).op('->')
                     ('files')).op('->>')(field).label(field)
             ],
             and_(
                 flt,
                 self.tables.script.data.op('@>')(
                     '{"ls": {"volumes": [{"files": []}]}}'),
             ),
         )
     elif field.startswith('modbus.'):
         subfield = field[7:]
         field = self._topstructure(
             self.tables.script,
             [self.tables.script.data['modbus-discover'][subfield]],
             and_(
                 self.tables.script.name == 'modbus-discover',
                 self.tables.script.data['modbus-discover'].has_key(
                     subfield)),
             # noqa: W601 (BinaryExpression)
         )
     elif field.startswith('s7.'):
         subfield = field[3:]
         field = self._topstructure(
             self.tables.script,
             [self.tables.script.data['s7-info'][subfield]],
             and_(self.tables.script.name == 's7-info',
                  self.tables.script.data['s7-info'].has_key(subfield)),
             # noqa: W601 (BinaryExpression)
         )
     elif field == 'httphdr':
         flt = self.flt_and(flt, self.searchscript(name="http-headers"))
         field = self._topstructure(
             self.tables.script,
             [
                 column("hdr").op('->>')('name').label("name"),
                 column("hdr").op('->>')('value').label("value")
             ],
             self.tables.script.name == 'http-headers',
             [column("name"), column("value")],
             func.jsonb_array_elements(
                 self.tables.script.data['http-headers']).alias('hdr'),
         )
     elif field.startswith('httphdr.'):
         flt = self.flt_and(flt, self.searchscript(name="http-headers"))
         field = self._topstructure(
             self.tables.script,
             [column("hdr").op('->>')(field[8:]).label("topvalue")],
             self.tables.script.name == 'http-headers',
             [column("topvalue")],
             func.jsonb_array_elements(
                 self.tables.script.data['http-headers']).alias('hdr'),
         )
     elif field.startswith('httphdr:'):
         flt = self.flt_and(flt, self.searchhttphdr(name=field[8:].lower()))
         field = self._topstructure(
             self.tables.script,
             [column("hdr").op('->>')("value").label("value")],
             and_(self.tables.script.name == 'http-headers',
                  column("hdr").op('->>')("name") == field[8:].lower()),
             [column("value")],
             func.jsonb_array_elements(
                 self.tables.script.data['http-headers']).alias('hdr'),
         )
     else:
         raise NotImplementedError()
     s_from = {
         self.tables.script:
         join(self.tables.script, self.tables.port),
         self.tables.port:
         self.tables.port,
         self.tables.category:
         join(self.tables.association_scan_category, self.tables.category),
         self.tables.hostname:
         self.tables.hostname,
         self.tables.hop:
         join(self.tables.trace, self.tables.hop),
     }
     where_clause = {
         self.tables.script:
         self.tables.port.scan == base.c.id,
         self.tables.port:
         self.tables.port.scan == base.c.id,
         self.tables.category:
         self.tables.association_scan_category.scan == base.c.id,
         self.tables.hostname:
         self.tables.hostname.scan == base.c.id,
         self.tables.hop:
         self.tables.trace.scan == base.c.id
     }
     if field.base == self.tables.scan:
         req = flt.query(
             select([func.count().label("count")] +
                    field.fields).select_from(
                        self.tables.scan).group_by(*field.fields))
     else:
         req = (select([func.count().label("count")] +
                       field.fields).select_from(s_from[field.base]))
         if field.extraselectfrom is not None:
             req = req.select_from(field.extraselectfrom)
         req = (req.group_by(
             *(field.fields if field.group_by is None else field.group_by
               )).where(
                   exists(
                       select([1]).select_from(base).where(
                           where_clause[field.base]))))
     if field.where is not None:
         req = req.where(field.where)
     if outputproc is None:
         return ({
             "count": result[0],
             "_id": result[1:] if len(result) > 2 else result[1]
         } for result in self.db.execute(req.order_by(order).limit(topnbr)))
     else:
         return ({
             "count":
             result[0],
             "_id":
             outputproc(result[1:] if len(result) > 2 else result[1])
         } for result in self.db.execute(req.order_by(order).limit(topnbr)))
Esempio n. 21
0
 def _store_host(self, host):
     addr = self.convert_ip(host['addr'])
     info = host.get('infos')
     if 'coordinates' in (info or {}).get('loc', {}):
         info['coordinates'] = info.pop('loc')['coordinates'][::-1]
     source = host.get('source', [])
     host_tstart = utils.all2datetime(host['starttime'])
     host_tstop = utils.all2datetime(host['endtime'])
     insrt = postgresql.insert(self.tables.scan)
     scanid, scan_tstop = self.db.execute(
         insrt.values(
             addr=addr,
             source=source,
             info=info,
             time_start=host_tstart,
             time_stop=host_tstop,
             **dict(
                 (key, host.get(key))
                 for key in ['state', 'state_reason', 'state_reason_ttl']
                 if key in host)).on_conflict_do_update(
                     index_elements=['addr'],
                     set_={
                         'source':
                         self.tables.scan.source + insrt.excluded.source,
                         'time_start':
                         func.least(
                             self.tables.scan.time_start,
                             insrt.excluded.time_start,
                         ),
                         'time_stop':
                         func.greatest(
                             self.tables.scan.time_stop,
                             insrt.excluded.time_stop,
                         ),
                     },
                 ).returning(self.tables.scan.id,
                             self.tables.scan.time_stop)).fetchone()
     newest = scan_tstop <= host_tstop
     for category in host.get("categories", []):
         insrt = postgresql.insert(self.tables.category)
         catid = self.db.execute(
             insrt.values(name=category).on_conflict_do_update(
                 index_elements=['name'],
                 set_={
                     'name': insrt.excluded.name
                 }).returning(self.tables.category.id)).fetchone()[0]
         self.db.execute(
             postgresql.insert(
                 self.tables.association_scan_category).values(
                     scan=scanid, category=catid).on_conflict_do_nothing())
     for port in host.get('ports', []):
         scripts = port.pop('scripts', [])
         # FIXME: handle screenshots
         for fld in [
                 'screendata', 'screenshot', 'screenwords', 'service_method'
         ]:
             try:
                 del port[fld]
             except KeyError:
                 pass
         if 'service_servicefp' in port:
             port['service_fp'] = port.pop('service_servicefp')
         if 'state_state' in port:
             port['state'] = port.pop('state_state')
         if 'state_reason_ip' in port:
             port['state_reason_ip'] = self.convert_ip(
                 port['state_reason_ip'])
         insrt = postgresql.insert(self.tables.port)
         portid = self.db.execute(
             insrt.values(scan=scanid, **port).on_conflict_do_update(
                 index_elements=['scan', 'port', 'protocol'],
                 set_=dict(scan=scanid,
                           **(port if newest else {}))).returning(
                               self.tables.port.id)).fetchone()[0]
         for script in scripts:
             name, output = script.pop('id'), script.pop('output')
             if newest:
                 insrt = postgresql.insert(self.tables.script)
                 self.bulk.append(
                     insrt.values(port=portid,
                                  name=name,
                                  output=output,
                                  data=script).on_conflict_do_update(
                                      index_elements=['port', 'name'],
                                      set_={
                                          "output": insrt.excluded.output,
                                          "data": insrt.excluded.data,
                                      },
                                  ))
             else:
                 insrt = postgresql.insert(self.tables.script)
                 self.bulk.append(
                     insrt.values(port=portid,
                                  name=name,
                                  output=output,
                                  data=script).on_conflict_do_nothing())
     for trace in host.get('traces', []):
         traceid = self.db.execute(
             postgresql.insert(self.tables.trace).values(
                 scan=scanid,
                 port=trace.get('port'),
                 protocol=trace['protocol']).on_conflict_do_nothing().
             returning(self.tables.trace.id)).fetchone()[0]
         for hop in trace.get('hops'):
             hop['ipaddr'] = self.convert_ip(hop['ipaddr'])
             self.bulk.append(
                 postgresql.insert(self.tables.hop).values(
                     trace=traceid,
                     ipaddr=self.convert_ip(hop['ipaddr']),
                     ttl=hop["ttl"],
                     rtt=None if hop["rtt"] == '--' else hop["rtt"],
                     host=hop.get("host"),
                     domains=hop.get("domains"),
                 ))
     for hostname in host.get('hostnames', []):
         self.bulk.append(
             postgresql.insert(self.tables.hostname).values(
                 scan=scanid,
                 domains=hostname.get('domains'),
                 name=hostname.get('name'),
                 type=hostname.get('type'),
             ).on_conflict_do_nothing())
     utils.LOGGER.debug("VIEW STORED: %r", scanid)
     return scanid
Esempio n. 22
0
    def execute(self, message, user, params):

        alliance = Alliance()
        race = None
        size_mod = None
        size = None
        value_mod = None
        value = None
        bash = False
        attacker = user.planet
        cluster = None

        params = params.group(1).split()

        for p in params:
            m = self.bashre.match(p)
            if m and not bash:
                bash = True
                continue
            m = self.clusterre.match(p)
            if m and not cluster:
                cluster = int(m.group(1))
            m = self.racere.match(p)
            if m and not race:
                race = m.group(1)
                continue
            m = self.rangere.match(p)
            if m and not size and int(m.group(2)) < 32768:
                size_mod = m.group(1) or '>'
                size = m.group(2)
                continue
            m = self.rangere.match(p)
            if m and not value:
                value_mod = m.group(1) or '<'
                value = m.group(2)
                continue
            m = self.alliancere.match(p)
            if m and not alliance.name and not self.clusterre.match(p):
                alliance = Alliance(name="Unknown") if m.group(
                    1).lower() == "unknown" else Alliance.load(m.group(1))
                if alliance is None:
                    message.reply("No alliance matching '%s' found" %
                                  (m.group(1), ))
                    return
                continue

        maxcap = PA.getfloat("roids", "maxcap")
        mincap = PA.getfloat("roids", "mincap")
        modifier = (cast(Planet.value,
                         Float).op("/")(float(attacker.value))).op("^")(0.5)
        caprate = func.greatest(mincap,
                                func.least(modifier.op("*")(maxcap), maxcap))
        maxcap = cast(func.floor(cast(Planet.size, Float).op("*")(caprate)),
                      Integer)
        bravery = func.greatest(0.2,func.least(2.2, cast(Planet.score,Float).op("/")(float(attacker.score)))-0.2) \
                   * func.greatest(0.2,func.least(1.8, cast(Planet.value,Float).op("/")(float(attacker.value)))-0.1) \
                   / ((6+max(4.0, float(attacker.score)/float(attacker.value)))/10.0)

        xp_gain = cast(func.floor(maxcap.op("*")(bravery.op("*")(10.0))),
                       Integer)

        Q = session.query(Planet, Intel, xp_gain.label("xp_gain"))
        if alliance.id:
            Q = Q.join(Planet.intel)
            Q = Q.filter(Intel.alliance == alliance)
        else:
            Q = Q.outerjoin(Planet.intel)
            if alliance.name:
                Q = Q.filter(Intel.alliance == None)
        Q = Q.filter(Planet.active == True)
        if race:
            Q = Q.filter(Planet.race.ilike(race))
        if size:
            Q = Q.filter(Planet.size.op(size_mod)(size))
        if value:
            Q = Q.filter(Planet.value.op(value_mod)(value))
        if bash:
            Q = Q.filter(
                or_(
                    Planet.value.op(">")(attacker.value *
                                         PA.getfloat("bash", "value")),
                    Planet.score.op(">")(attacker.score *
                                         PA.getfloat("bash", "score"))))
        if cluster:
            Q = Q.filter(Planet.x == cluster)
        Q = Q.order_by(desc("xp_gain"))
        Q = Q.order_by(desc(Planet.idle))
        Q = Q.order_by(desc(Planet.value))
        result = Q[:6]

        if len(result) < 1:
            reply = "No"
            if race:
                reply += " %s" % (race, )
            reply += " planets"
            if alliance.name:
                reply += " in intel matching Alliance: %s" % (alliance.name, )
            else:
                reply += " matching"
            if size:
                reply += " Size %s %s" % (size_mod, size)
            if value:
                reply += " Value %s %s" % (value_mod, value)
            message.reply(reply)
            return

        replies = []
        for planet, intel, xp_gain in result[:5]:
            reply = "%s:%s:%s (%s)" % (planet.x, planet.y, planet.z,
                                       planet.race)
            reply += " Value: %s Size: %s Scoregain: %d" % (
                planet.value, planet.size,
                xp_gain * PA.getint("numbers", "xp_value"))
            if intel:
                if intel.nick:
                    reply += " Nick: %s" % (intel.nick, )
                if not alliance.name and intel.alliance:
                    reply += " Alliance: %s" % (intel.alliance.name, )
            replies.append(reply)
        if len(result) > 5:
            replies[
                -1] += " (Too many results to list, please refine your search)"
        message.reply("\n".join(replies))
Esempio n. 23
0
    def insert_or_update_bulk(self,
                              specs,
                              getinfos=None,
                              separated_timestamps=True):
        """Like `.insert_or_update()`, but `specs` parameter has to be an
        iterable of `(timestamp, spec)` (if `separated_timestamps` is
        True) or `spec` (if it is False) values. This will perform
        PostgreSQL COPY FROM inserts with the major drawback that the
        `getinfos` parameter will be called (if it is not `None`) for
        each spec, even when the spec already exists in the database
        and the call was hence unnecessary.

        It's up to you to decide whether having bulk insert is worth
        it or if you want to go with the regular `.insert_or_update()`
        method.

        """
        more_to_read = True
        tmp = self.create_tmp_table(self.tables.passive)
        if config.DEBUG_DB:
            total_upserted = 0
            total_start_time = time.time()
        while more_to_read:
            if config.DEBUG_DB:
                start_time = time.time()
            with PassiveCSVFile(specs,
                                self.convert_ip,
                                tmp,
                                getinfos=getinfos,
                                separated_timestamps=separated_timestamps,
                                limit=config.POSTGRES_BATCH_SIZE) as fdesc:
                self.copy_from(fdesc, tmp.name)
                more_to_read = fdesc.more_to_read
                if config.DEBUG_DB:
                    count_upserted = fdesc.count
            insrt = postgresql.insert(self.tables.passive)
            self.db.execute(
                insrt.from_select(
                    [column(col) for col in [
                        'addr',
                        # sum / min / max
                        'count', 'firstseen', 'lastseen',
                        # grouped
                        'sensor', 'port', 'recontype', 'source', 'targetval',
                        'value', 'fullvalue', 'info', 'moreinfo'
                    ]],
                    select([tmp.columns['addr'],
                            func.sum_(tmp.columns['count']),
                            func.min_(tmp.columns['firstseen']),
                            func.max_(tmp.columns['lastseen'])] + [
                                tmp.columns[col] for col in [
                                    'sensor', 'port', 'recontype', 'source',
                                    'targetval', 'value', 'fullvalue', 'info',
                                    'moreinfo']])\
                    .group_by(*(tmp.columns[col] for col in [
                        'addr', 'sensor', 'port', 'recontype', 'source',
                        'targetval', 'value', 'fullvalue', 'info', 'moreinfo'
                    ]))
                )\
                .on_conflict_do_update(
                    index_elements=['addr', 'sensor', 'recontype', 'port',
                                    'source', 'value', 'targetval', 'info'],
                    set_={
                        'firstseen': func.least(
                            self.tables.passive.firstseen,
                            insrt.excluded.firstseen,
                        ),
                        'lastseen': func.greatest(
                            self.tables.passive.lastseen,
                            insrt.excluded.lastseen,
                        ),
                        'count':
                        self.tables.passive.count + insrt.excluded.count,
                    },
                )
            )
            self.db.execute(delete(tmp))
            if config.DEBUG_DB:
                stop_time = time.time()
                time_spent = stop_time - start_time
                total_upserted += count_upserted
                total_time_spent = stop_time - total_start_time
                utils.LOGGER.debug(
                    "DB:PERFORMANCE STATS %s upserts, %f s, %s/s\n"
                    "\ttotal: %s upserts, %f s, %s/s",
                    utils.num2readable(count_upserted),
                    time_spent,
                    utils.num2readable(count_upserted / time_spent),
                    utils.num2readable(total_upserted),
                    total_time_spent,
                    utils.num2readable(total_upserted / total_time_spent),
                )
Esempio n. 24
0
def fetch_annual_billing(service_id, year):
    start_date, end_date = get_financial_year_range(year)
    fragments_free_limit = literal(
        fetch_sms_free_allowance_for_financial_year(service_id, year))
    query = dao_get_priced_billing_data().filter(
        FactBilling.service_id == service_id,
        FactBilling.aet_date >= start_date,
        FactBilling.aet_date <= end_date,
    )

    # break down usage by month with empty gaps by generating a series
    months_series = func.generate_series(start_date, end_date,
                                         '1 months').alias('month')
    months_series_c = column('month').cast(Date()).label('month')
    query_cte = query.cte()

    # calculate in credits in order to work backwards to get available units
    # after removing cost usage
    credits_free = fragments_free_limit * FRAGMENT_UNIT_RATE

    # coalesce and sum these totals by month
    cost = total(query_cte.c.cost)
    notifications = total(query_cte.c.notifications_sent)
    notifications_email_ = func.sum(query_cte.c.notifications_sent).filter(
        query_cte.c.notification_type == EMAIL_TYPE)
    notifications_sms_ = func.sum(query_cte.c.notifications_sent).filter(
        query_cte.c.notification_type == SMS_TYPE)
    notifications_email = func.coalesce(notifications_email_, 0)
    notifications_sms = func.coalesce(notifications_sms_, 0)

    fragments_domestic = total(query_cte.c.domestic_units)
    fragments_international = total(query_cte.c.international_units)

    # cumulative figures for entire year
    cost_cumulative = func.sum(cost).over(order_by=months_series_c)
    cost_starting = cost_cumulative - cost
    credits_remaining = func.greatest(credits_free - cost_cumulative, 0)
    credits_available = func.greatest(credits_free - cost_starting, 0)
    cost_chargeable = func.greatest(cost - credits_available, 0)
    cost_chargeable_cumulative = func.greatest(cost_cumulative - credits_free,
                                               0)

    credits_used = func.least(cost, credits_available)
    units = cost / FRAGMENT_UNIT_RATE
    units_cumulative = cost_cumulative / FRAGMENT_UNIT_RATE
    units_chargeable = cost_chargeable / FRAGMENT_UNIT_RATE
    units_free_used = credits_used / FRAGMENT_UNIT_RATE
    units_free_available = credits_available / FRAGMENT_UNIT_RATE
    units_free_remaining = credits_remaining / FRAGMENT_UNIT_RATE
    units_chargeable_cumulative = cost_chargeable_cumulative / FRAGMENT_UNIT_RATE

    gapfilled_query = db.session.query(
        months_series_c,
        fragments_free_limit.label('fragments_free_limit'),
        fragments_domestic.label('fragments_domestic'),
        fragments_international.label('fragments_international'),
        notifications.label('notifications'),
        notifications_sms.label('notifications_sms'),
        notifications_email.label('notifications_email'),
        cost.label('cost'),
        cost_chargeable.label('cost_chargeable'),
        cost_cumulative.label('cost_cumulative'),
        cost_chargeable_cumulative.label('cost_chargeable_cumulative'),
        units.label('units'),
        units_cumulative.label('units_cumulative'),
        units_chargeable.label('units_chargeable'),
        units_chargeable_cumulative.label('units_chargeable_cumulative'),
        units_free_available.label('units_free_available'),
        units_free_remaining.label('units_free_remaining'),
        units_free_used.label('units_free_used'),
        literal(DOMESTIC_UNIT_RATE * 100).label('unit_rate_domestic'),
        literal(INTERNATIONAL_UNIT_RATE *
                100).label('unit_rate_international'),
    ).select_from(months_series, ).outerjoin(
        query_cte,
        query_cte.c.aet_month == months_series_c,
    ).group_by(months_series_c, ).order_by(months_series_c)
    return gapfilled_query
Esempio n. 25
0
def centerline_query(session, detection):
    """Finds the centerline orientation that most closely agrees with
       detection-intersected roadbeds."""

    # pylint: disable-msg=E1101
    car_polygon = Detection.geom
    car_polygon102718 = func.ST_Transform(car_polygon, 102718)
    car_filter = func.ST_Intersects(
        Roadbed.geom,
        car_polygon102718
    )

    query = session.query(
        Roadbed.gid) \
        .filter(Detection.id == detection.id) \
        .filter(car_filter)
    road_gids = query.all()

    if len(road_gids) == 0:
        return

    lat, lon, alt = session.query(
        func.ST_Y(Detection.lla),
        func.ST_X(Detection.lla),
        func.ST_Z(Detection.lla)) \
        .filter(Detection.id == detection.id) \
        .one()
    lla = numpy.array([[lat, lon, alt]])
    enu = pygeo.LLAToENU(lla).reshape((3, 3))

    roadbeds4326 = func.ST_Transform(Roadbed.geom, 4326)

    centerlines4326 = PlanetOsmLine.way
    centerline_filter = func.ST_Intersects(roadbeds4326, centerlines4326)
    centerline_frac = func.ST_Line_Locate_Point(
        centerlines4326, Detection.lla)
    centerline_start_frac = func.least(1, centerline_frac + 0.01)
    centerline_end_frac = func.greatest(0, centerline_frac - 0.01)
    centerline_start = func.ST_Line_Interpolate_Point(centerlines4326,
                                                      centerline_start_frac)
    centerline_end = func.ST_Line_Interpolate_Point(centerlines4326,
                                                    centerline_end_frac)

    segments = session.query(
        func.ST_Y(centerline_start).label('lats'),
        func.ST_X(centerline_start).label('lons'),

        func.ST_Y(centerline_end).label('late'),
        func.ST_X(centerline_end).label('lone'),

        PlanetOsmLine.oneway) \
        .filter(Detection.id == detection.id) \
        .filter(centerline_filter) \
        .filter(Roadbed.gid.in_(road_gids)) \
        .filter(PlanetOsmLine.osm_id >= 0) \
        .filter(PlanetOsmLine.railway.__eq__(None))
    # pylint: enable-msg=E1101

    for segment in segments:
        segment_start = pygeo.LLAToECEF(numpy.array(
            [[segment.lats, segment.lons, alt]],
            dtype=numpy.float64
        ))
        segment_end = pygeo.LLAToECEF(numpy.array(
            [[segment.late, segment.lone, alt]],
            dtype=numpy.float64
        ))

        segment_dir = (segment_end - segment_start)
        segment_dir /= numpy.linalg.norm(segment_dir)

        segment_rot = enu.T.dot(segment_dir.T)

        segment_angle = math.atan2(segment_rot[1], segment_rot[0])

        yield segment_angle, segment.oneway
Esempio n. 26
0
    def execute(self, message, user, params):
        
        alliance=Alliance()
        race=None
        size_mod=None
        size=None
        value_mod=None
        value=None
        bash=False
        attacker=user.planet
        cluster=None

        params=params.group(1).split()

        for p in params:
            m=self.bashre.match(p)
            if m and not bash:
                bash=True
                continue
            m=self.clusterre.match(p)
            if m and not cluster:
                cluster=int(m.group(1))
            m=self.racere.match(p)
            if m and not race:
                race=m.group(1)
                continue
            m=self.rangere.match(p)
            if m and not size and int(m.group(2)) < 32768:
                size_mod=m.group(1) or '>'
                size=m.group(2)
                continue
            m=self.rangere.match(p)
            if m and not value:
                value_mod=m.group(1) or '<'
                value=m.group(2)
                continue
            m=self.alliancere.match(p)
            if m and not alliance.name and not self.clusterre.match(p):
                alliance = Alliance(name="Unknown") if m.group(1).lower() == "unknown" else Alliance.load(m.group(1))
                if alliance is None:
                    message.reply("No alliance matching '%s' found" % (m.group(1),))
                    return
                continue

        maxcap = PA.getfloat("roids","maxcap")
        mincap = PA.getfloat("roids","mincap")
        modifier = (cast(Planet.value,Float).op("/")(float(attacker.value))).op("^")(0.5)
        caprate = func.greatest(mincap,func.least(modifier.op("*")(maxcap),maxcap))
        maxcap = cast(func.floor(cast(Planet.size,Float).op("*")(caprate)),Integer)
        
        bravery = (func.greatest(0.0,( func.least(2.0, cast(Planet.value,Float).op("/")(float(attacker.value)))-0.1) * (func.least(2.0, cast(Planet.score,Float).op("/")(float(attacker.score)))-0.2))).op("*")(10.0)
        xp_gain = cast(func.floor(maxcap.op("*")(bravery)),Integer)
        
        Q = session.query(Planet, Intel, xp_gain.label("xp_gain"))
        if alliance.id:
            Q = Q.join(Planet.intel)
            Q = Q.filter(Intel.alliance == alliance)
        else:
            Q = Q.outerjoin(Planet.intel)
            if alliance.name:
                Q = Q.filter(Intel.alliance == None)
        Q = Q.filter(Planet.active == True)
        if race:
            Q = Q.filter(Planet.race.ilike(race))
        if size:
            Q = Q.filter(Planet.size.op(size_mod)(size))
        if value:
            Q = Q.filter(Planet.value.op(value_mod)(value))
        if bash:
            Q = Q.filter(or_(Planet.value.op(">")(attacker.value*PA.getfloat("bash","value")),
                             Planet.score.op(">")(attacker.score*PA.getfloat("bash","score"))))
        if cluster:
            Q = Q.filter(Planet.x == cluster)
        Q = Q.order_by(desc("xp_gain"))
        Q = Q.order_by(desc(Planet.idle))
        Q = Q.order_by(desc(Planet.value))
        result = Q[:6]
        
        if len(result) < 1:
            reply="No"
            if race:
                reply+=" %s"%(race,)
            reply+=" planets"
            if alliance.name:
                reply+=" in intel matching Alliance: %s"%(alliance.name,)
            else:
                reply+=" matching"
            if size:
                reply+=" Size %s %s" % (size_mod,size)
            if value:
                reply+=" Value %s %s" % (value_mod,value)
            message.reply(reply)
            return
        
        replies = []
        for planet, intel, xp_gain in result[:5]:
            reply="%s:%s:%s (%s)" % (planet.x,planet.y,planet.z,planet.race)
            reply+=" Value: %s Size: %s Scoregain: %d" % (planet.value,planet.size, xp_gain*PA.getint("numbers", "xp_value"))
            if intel:
                if intel.nick:
                    reply+=" Nick: %s" % (intel.nick,)
                if not alliance.name and intel.alliance:
                    reply+=" Alliance: %s" % (intel.alliance.name,)
            replies.append(reply)
        if len(result) > 5:
            replies[-1]+=" (Too many results to list, please refine your search)"
        message.reply("\n".join(replies))
Esempio n. 27
0
    def execute(self, message, user, params):
        
        alliance=Alliance()
        race=None
        size_mod=None
        size=None
        value_mod=None
        value=None
        bash=False
        attacker=user.planet
        cluster=None
        limit=5

        params=params.group(1).split()

        for p in params:
            m=self.bashre.match(p)
            if m and not bash:
                bash=True
                continue
            m=self.clusterre.match(p)
            if m and not cluster:
                cluster=int(m.group(1))
            m=self.racere.match(p)
            if m and not race:
                race=m.group(1)
                continue
            m=self.rangere.match(p)
            if m and not size and int(m.group(2)) < 32768:
                size_mod=m.group(1) or '>'
                size=m.group(2)
                continue
            m=self.rangere.match(p)
            if m and not value:
                value_mod=m.group(1) or '<'
                value=m.group(2)
                continue
            m=self.alliancere.match(p)
            if m and not alliance.name and not self.clusterre.match(p):
                alliance = Alliance(name="Unknown") if m.group(1).lower() == "unknown" else Alliance.load(m.group(1))
                if alliance is None:
                    message.reply("No alliance matching '%s' found" % (m.group(1),))
                    return
                continue
            if p[:4] == "lots" and user.is_admin():
                limit = int(p[4:])

        maxcap = PA.getfloat("roids","maxcap")
        mincap = PA.getfloat("roids","mincap")
        modifier = (cast(Planet.value,Float).op("/")(float(attacker.value))).op("^")(0.5)
        caprate = func.greatest(mincap,func.least(modifier.op("*")(maxcap),maxcap))
        maxcap = cast(func.floor(cast(Planet.size,Float).op("*")(caprate)),Integer)
        
        Q = session.query(Planet, Intel, maxcap.label("maxcap"))
        if alliance.id:
            Q = Q.join(Planet.intel)
            Q = Q.filter(Intel.alliance == alliance)
        else:
            Q = Q.outerjoin(Planet.intel)
            if alliance.name:
                Q = Q.filter(Intel.alliance == None)
        Q = Q.filter(Planet.active == True)
        if race:
            Q = Q.filter(Planet.race.ilike(race))
        if size:
            Q = Q.filter(Planet.size.op(size_mod)(size))
        if value:
            Q = Q.filter(Planet.value.op(value_mod)(value))
        if bash:
            Q = Q.filter(or_(Planet.value.op(">")(attacker.value*PA.getfloat("bash","value")),
                             Planet.score.op(">")(attacker.score*PA.getfloat("bash","score"))))
        if cluster:
            Q = Q.filter(Planet.x == cluster)
        Q = Q.order_by(desc("maxcap"))
        Q = Q.order_by(desc(Planet.size))
        Q = Q.order_by(desc(Planet.value))
        result = Q[:(limit+1)]
        
        if len(result) < 1:
            reply="No"
            if race:
                reply+=" %s"%(race,)
            reply+=" planets"
            if alliance.name:
                reply+=" in intel matching Alliance: %s"%(alliance.name,)
            else:
                reply+=" matching"
            if size:
                reply+=" Size %s %s" % (size_mod,size)
            if value:
                reply+=" Value %s %s" % (value_mod,value)
            message.reply(reply)
            return
        
        replies = []
        for planet, intel, maxcap in result[:limit]:
            reply="%s:%s:%s (%s)" % (planet.x,planet.y,planet.z,planet.race)
            reply+=" Value: %s Size: %s MaxCap: %s" % (planet.value,planet.size, maxcap)
            if intel:
                if intel.nick:
                    reply+=" Nick: %s" % (intel.nick,)
                if not alliance.name and intel.alliance:
                    reply+=" Alliance: %s" % (intel.alliance.name,)
            replies.append(reply)
            if len(replies) == 5:
                message.reply("\n".join(replies))
                replies = []
                sleep(3)
        if len(result) > limit:
            replies.append("(Too many results to list, please refine your search)")
        message.reply("\n".join(replies))
Esempio n. 28
0
 def store_host(self, host, merge=False):
     addr = self.convert_ip(host['addr'])
     info = host.get('infos')
     if 'coordinates' in (info or {}).get('loc', {}):
         info['coordinates'] = info.pop('loc')['coordinates'][::-1]
     source = host.get('source', '')
     if merge:
         insrt = postgresql.insert(Scan)
         scanid, scan_tstop, merge = self.db.execute(
             insrt.values(
                 addr=addr,
                 source=source,
                 info=info,
                 time_start=host['starttime'],
                 time_stop=host['endtime'],
                 archive=0,
                 merge=False,
                 **dict(
                     (key, host.get(key)) for key in
                     ['state', 'state_reason', 'state_reason_ttl']
                     if key in host
                 )
             )
             .on_conflict_do_update(
                 index_elements=['addr', 'source', 'archive'],
                 set_={
                     'time_start': func.least(
                         Scan.time_start,
                         insrt.excluded.time_start,
                     ),
                     'time_stop': func.greatest(
                         Scan.time_stop,
                         insrt.excluded.time_stop,
                     ),
                     'merge': True,
                 },
             )
             .returning(Scan.id, Scan.time_stop,
                        Scan.merge)).fetchone()
         if merge:
             # Test should be ==, using <= in case of rounding
             # issues.
             newest = scan_tstop <= host['endtime']
         else:
             newest = None
     else:
         curarchive = self.db.execute(select([func.max(Scan.archive)])
                                      .where(and_(Scan.addr == addr,
                                                  Scan.source == source)))\
                             .fetchone()[0]
         if curarchive is not None:
             self.db.execute(update(Scan).where(and_(
                 Scan.addr == addr,
                 Scan.source == source,
                 Scan.archive == 0,
             )).values(archive=curarchive + 1))
         scanid = self.db.execute(
             insert(Scan).values(
                 addr=addr,
                 source=source,
                 info=info,
                 time_start=host['starttime'],
                 time_stop=host['endtime'],
                 state=host['state'],
                 state_reason=host['state_reason'],
                 state_reason_ttl=host.get('state_reason_ttl'),
                 archive=0,
                 merge=False,
             ).returning(Scan.id)
         ).fetchone()[0]
     insrt = postgresql.insert(Association_Scan_ScanFile)
     self.db.execute(insrt
                     .values(scan=scanid,
                             scan_file=utils.decode_hex(host['scanid']))
                     .on_conflict_do_nothing())
     for category in host.get("categories", []):
         insrt = postgresql.insert(Category)
         catid = self.db.execute(
             insrt.values(name=category)
             .on_conflict_do_update(
                 index_elements=['name'],
                 set_={'name': insrt.excluded.name}
             )
             .returning(Category.id)
         ).fetchone()[0]
         self.db.execute(postgresql.insert(Association_Scan_Category)
                         .values(scan=scanid, category=catid)
                         .on_conflict_do_nothing())
     for port in host.get('ports', []):
         scripts = port.pop('scripts', [])
         # FIXME: handle screenshots
         for fld in ['screendata', 'screenshot', 'screenwords',
                     'service_method']:
             try:
                 del port[fld]
             except KeyError:
                 pass
         if 'service_servicefp' in port:
             port['service_fp'] = port.pop('service_servicefp')
         if 'state_state' in port:
             port['state'] = port.pop('state_state')
         if 'state_reason_ip' in port:
             port['state_reason_ip'] = self.convert_ip(
                 port['state_reason_ip']
             )
         if merge:
             insrt = postgresql.insert(Port)
             portid = self.db.execute(
                 insrt.values(scan=scanid, **port)
                 .on_conflict_do_update(
                     index_elements=['scan', 'port',
                                     'protocol'],
                     set_=dict(
                         scan=scanid,
                         **(port if newest else {})
                     )
                 )
                 .returning(Port.id)
             ).fetchone()[0]
         else:
             portid = self.db.execute(insert(Port).values(scan=scanid,
                                                          **port)
                                      .returning(Port.id)).fetchone()[0]
         for script in scripts:
             name, output = script.pop('id'), script.pop('output')
             if merge:
                 if newest:
                     insrt = postgresql.insert(Script)
                     self.bulk.append(insrt
                                      .values(
                                          port=portid,
                                          name=name,
                                          output=output,
                                          data=script
                                      )
                                      .on_conflict_do_update(
                                          index_elements=['port', 'name'],
                                          set_={
                                              "output":
                                              insrt.excluded.output,
                                              "data": insrt.excluded.data,
                                          },
                                      ))
                 else:
                     insrt = postgresql.insert(Script)
                     self.bulk.append(insrt
                                      .values(
                                          port=portid,
                                          name=name,
                                          output=output,
                                          data=script
                                      )
                                      .on_conflict_do_nothing())
             else:
                 self.bulk.append(insert(Script).values(
                     port=portid,
                     name=name,
                     output=output,
                     data=script
                 ))
     if not merge:
         # FIXME: handle traceroutes on merge
         for trace in host.get('traces', []):
             traceid = self.db.execute(insert(Trace).values(
                 scan=scanid,
                 port=trace.get('port'),
                 protocol=trace['protocol']
             ).returning(Trace.id)).fetchone()[0]
             for hop in trace.get('hops'):
                 hop['ipaddr'] = self.convert_ip(hop['ipaddr'])
                 self.bulk.append(insert(Hop).values(
                     trace=traceid,
                     ipaddr=self.convert_ip(hop['ipaddr']),
                     ttl=hop["ttl"],
                     rtt=None if hop["rtt"] == '--' else hop["rtt"],
                     host=hop.get("host"),
                     domains=hop.get("domains"),
                 ))
         # FIXME: handle hostnames on merge
         for hostname in host.get('hostnames', []):
             self.bulk.append(insert(Hostname).values(
                 scan=scanid,
                 domains=hostname.get('domains'),
                 name=hostname.get('name'),
                 type=hostname.get('type'),
             ))
     utils.LOGGER.debug("HOST STORED: %r", scanid)