def setUpClass(cls):
        metadata = MetaData()

        md = models.create_md_model(metadata)
        gc_columns = [md.c.name + text("'='") + md.c.value]
        cls.group_concat_md = (select([
            md.c.dimension_set_id,
            models.group_concat(gc_columns).label('dimensions')
        ]).select_from(md).group_by(md.c.dimension_set_id))

        cls.group_concat_md_order = (select([
            md.c.dimension_set_id,
            models.group_concat(gc_columns,
                                order_by=[md.c.name.asc()]).label('dimensions')
        ]).select_from(md).group_by(md.c.dimension_set_id))

        cls.order_by_field = (select(
            [md.c.dimension_set_id]).select_from(md).order_by(
                asc(
                    models.field_sort(md.c.dimension_set_id,
                                      map(text, ["'A'", "'B'", "'C'"])))))
    def setUp(self):
        super(TestModelsDB, self).setUp()
        metadata = MetaData()

        md = models.create_md_model(metadata)
        gc_columns = [md.c.name + text("'='") + md.c.value]
        self.group_concat_md = (
            select([md.c.dimension_set_id,
                    models.group_concat(gc_columns).label('dimensions')])
            .select_from(md)
            .group_by(md.c.dimension_set_id))

        self.group_concat_md_order = (
            select([md.c.dimension_set_id,
                    models.group_concat(gc_columns,
                                        order_by=[md.c.name.asc()]).label('dimensions')])
            .select_from(md)
            .group_by(md.c.dimension_set_id))

        self.order_by_field = (select([md.c.dimension_set_id]) .select_from(md) .order_by(
            asc(models.field_sort(md.c.dimension_set_id, map(text, ["'A'", "'B'", "'C'"])))))
Example #3
0
    def get_alarms(self, tenant_id, query_parms=None, offset=None, limit=None):
        if not query_parms:
            query_parms = {}

        with self._db_engine.connect() as conn:
            parms = {}
            ad = self.ad
            am = self.am
            mdd = self.mdd
            md = self.md
            a = self.a

            query = (self.base_subquery_list
                     .where(ad.c.tenant_id == bindparam('b_tenant_id')))

            parms['b_tenant_id'] = tenant_id

            if 'alarm_definition_id' in query_parms:
                query = query.where(ad.c.id ==
                                    bindparam('b_alarm_definition_id'))
                parms['b_alarm_definition_id'] = query_parms['alarm_definition_id']

            if 'metric_name' in query_parms:
                query = query.where(a.c.id.in_(self.get_a_am_query))
                parms['b_md_name'] = query_parms['metric_name'].encode('utf8')

            if 'severity' in query_parms:
                severities = query_parms['severity'].split('|')
                query = query.where(
                    or_(ad.c.severity == bindparam('b_severity' + str(i)) for i in xrange(len(severities))))
                for i, s in enumerate(severities):
                    parms['b_severity' + str(i)] = s.encode('utf8')

            if 'state' in query_parms:
                query = query.where(a.c.state == bindparam('b_state'))
                parms['b_state'] = query_parms['state'].encode('utf8')

            if 'lifecycle_state' in query_parms:
                query = (query
                         .where(a.c.lifecycle_state ==
                                bindparam('b_lifecycle_state')))
                parms['b_lifecycle_state'] = query_parms['lifecycle_state'].encode('utf8')

            if 'link' in query_parms:
                query = query.where(a.c.link == bindparam('b_link'))
                parms['b_link'] = query_parms['link'].encode('utf8')

            if 'state_updated_start_time' in query_parms:
                query = (query
                         .where(a.c.state_updated_at >=
                                bindparam('b_state_updated_at')))
                date_str = query_parms['state_updated_start_time'].encode('utf8')
                date_param = datetime.strptime(date_str,
                                               '%Y-%m-%dT%H:%M:%S.%fZ')
                parms['b_state_updated_at'] = date_param

            if 'metric_dimensions' in query_parms:
                sub_query = select([a.c.id])
                sub_query_from = (a.join(am, am.c.alarm_id == a.c.id)
                                  .join(mdd,
                                        mdd.c.id ==
                                        am.c.metric_definition_dimensions_id))

                sub_query_md_base = select([md.c.dimension_set_id]).select_from(md)

                for i, metric_dimension in enumerate(query_parms['metric_dimensions'].items()):

                    md_name = "b_md_name_{}".format(i)

                    values_cond = None
                    values_cond_flag = False

                    if metric_dimension and metric_dimension[1]:
                        if '|' in metric_dimension[1]:
                            values = metric_dimension[1].encode('utf8').split('|')
                            sub_values_cond = []
                            for j, value in enumerate(values):
                                sub_md_value = "b_md_value_{}_{}".format(i, j)
                                sub_values_cond.append(md.c.value == bindparam(sub_md_value))
                                parms[sub_md_value] = value
                            values_cond = or_(*sub_values_cond)
                            values_cond_flag = True
                        else:
                            md_value = "b_md_value_{}".format(i)
                            values_cond = (md.c.value == bindparam(md_value))
                            values_cond_flag = True
                            parms[md_value] = metric_dimension[1]

                    sub_query_md = (sub_query_md_base
                                    .where(md.c.name == bindparam(md_name)))
                    if values_cond_flag:
                        sub_query_md = (sub_query_md
                                        .where(values_cond))

                    sub_query_md = (sub_query_md
                                    .distinct()
                                    .alias('md_{}'.format(i)))

                    sub_query_from = (sub_query_from
                                      .join(sub_query_md,
                                            sub_query_md.c.dimension_set_id ==
                                            mdd.c.metric_dimension_set_id))

                    parms[md_name] = metric_dimension[0].encode('utf8')

                    sub_query = (sub_query
                                 .select_from(sub_query_from)
                                 .distinct())
                    query = query.where(a.c.id.in_(sub_query))

            order_columns = []
            if 'sort_by' in query_parms:
                columns_mapper = {'alarm_id': a.c.id,
                                  'alarm_definition_id': ad.c.id,
                                  'alarm_definition_name': ad.c.name,
                                  'alarm_definition_description': ad.c.description,
                                  'state_updated_timestamp': a.c.state_updated_at,
                                  'updated_timestamp': a.c.updated_at,
                                  'created_timestamp': a.c.created_at,
                                  'severity': models.field_sort(ad.c.severity, map(text, ["'LOW'",
                                                                                          "'MEDIUM'",
                                                                                          "'HIGH'",
                                                                                          "'CRITICAL'"])),
                                  'state': models.field_sort(a.c.state, map(text, ["'OK'",
                                                                                   "'UNDETERMINED'",
                                                                                   "'ALARM'"]))}

                order_columns, received_cols = self._remap_columns(query_parms['sort_by'], columns_mapper)

                if not received_cols.get('alarm_id', False):
                    order_columns.append(a.c.id)
            else:
                order_columns = [a.c.id]

            if limit:
                query = query.limit(bindparam('b_limit'))
                parms['b_limit'] = limit + 1

            if offset:
                query = query.offset(bindparam('b_offset'))
                parms['b_offset'] = offset

            query = (query
                     .order_by(*order_columns)
                     .alias('alarm_id_list'))

            main_query = (self.base_query
                          .select_from(self.base_query_from
                                       .join(query, query.c.id == a.c.id))
                          .distinct())

            main_query = main_query.order_by(*order_columns)

            return [dict(row) for row in conn.execute(main_query, parms).fetchall()]
Example #4
0
    def get_alarms(self, tenant_id, query_parms=None, offset=None, limit=None):
        if not query_parms:
            query_parms = {}

        with self._db_engine.connect() as conn:
            parms = {}
            ad = self.ad
            am = self.am
            mdd = self.mdd
            md = self.md
            a = self.a

            query = (self.base_subquery_list.where(
                ad.c.tenant_id == bindparam('b_tenant_id')))

            parms['b_tenant_id'] = tenant_id

            if 'alarm_definition_id' in query_parms:
                query = query.where(
                    ad.c.id == bindparam('b_alarm_definition_id'))
                parms['b_alarm_definition_id'] = query_parms[
                    'alarm_definition_id']

            if 'metric_name' in query_parms:
                query = query.where(a.c.id.in_(self.get_a_am_query))
                parms['b_md_name'] = query_parms['metric_name'].encode('utf8')

            if 'severity' in query_parms:
                severities = query_parms['severity'].split('|')
                query = query.where(
                    or_(ad.c.severity == bindparam('b_severity' + str(i))
                        for i in range(len(severities))))
                for i, s in enumerate(severities):
                    parms['b_severity' + str(i)] = s.encode('utf8')

            if 'state' in query_parms:
                query = query.where(a.c.state == bindparam('b_state'))
                parms['b_state'] = query_parms['state'].encode('utf8')

            if 'lifecycle_state' in query_parms:
                query = (query.where(
                    a.c.lifecycle_state == bindparam('b_lifecycle_state')))
                parms['b_lifecycle_state'] = query_parms[
                    'lifecycle_state'].encode('utf8')

            if 'link' in query_parms:
                query = query.where(a.c.link == bindparam('b_link'))
                parms['b_link'] = query_parms['link'].encode('utf8')

            if 'state_updated_start_time' in query_parms:
                query = (query.where(
                    a.c.state_updated_at >= bindparam('b_state_updated_at')))
                date_str = query_parms['state_updated_start_time'].encode(
                    'utf8')
                date_param = datetime.strptime(date_str,
                                               '%Y-%m-%dT%H:%M:%S.%fZ')
                parms['b_state_updated_at'] = date_param

            if 'metric_dimensions' in query_parms:
                sub_query = select([a.c.id])
                sub_query_from = (a.join(am, am.c.alarm_id == a.c.id).join(
                    mdd, mdd.c.id == am.c.metric_definition_dimensions_id))

                sub_query_md_base = select([md.c.dimension_set_id
                                            ]).select_from(md)

                for i, metric_dimension in enumerate(
                        query_parms['metric_dimensions'].items()):

                    md_name = "b_md_name_{}".format(i)

                    values_cond = None
                    values_cond_flag = False

                    if metric_dimension and metric_dimension[1]:
                        if '|' in metric_dimension[1]:
                            values = metric_dimension[1].encode('utf8').split(
                                '|')
                            sub_values_cond = []
                            for j, value in enumerate(values):
                                sub_md_value = "b_md_value_{}_{}".format(i, j)
                                sub_values_cond.append(
                                    md.c.value == bindparam(sub_md_value))
                                parms[sub_md_value] = value
                            values_cond = or_(*sub_values_cond)
                            values_cond_flag = True
                        else:
                            md_value = "b_md_value_{}".format(i)
                            values_cond = (md.c.value == bindparam(md_value))
                            values_cond_flag = True
                            parms[md_value] = metric_dimension[1]

                    sub_query_md = (sub_query_md_base.where(
                        md.c.name == bindparam(md_name)))
                    if values_cond_flag:
                        sub_query_md = (sub_query_md.where(values_cond))

                    sub_query_md = (sub_query_md.distinct().alias(
                        'md_{}'.format(i)))

                    sub_query_from = (sub_query_from.join(
                        sub_query_md, sub_query_md.c.dimension_set_id ==
                        mdd.c.metric_dimension_set_id))

                    parms[md_name] = metric_dimension[0].encode('utf8')

                    sub_query = (
                        sub_query.select_from(sub_query_from).distinct())
                    query = query.where(a.c.id.in_(sub_query))

            order_columns = []
            if 'sort_by' in query_parms:
                columns_mapper = {
                    'alarm_id':
                    a.c.id,
                    'alarm_definition_id':
                    ad.c.id,
                    'alarm_definition_name':
                    ad.c.name,
                    'state_updated_timestamp':
                    a.c.state_updated_at,
                    'updated_timestamp':
                    a.c.updated_at,
                    'created_timestamp':
                    a.c.created_at,
                    'severity':
                    models.field_sort(
                        ad.c.severity,
                        map(text,
                            ["'LOW'", "'MEDIUM'", "'HIGH'", "'CRITICAL'"])),
                    'state':
                    models.field_sort(
                        a.c.state,
                        map(text, ["'OK'", "'UNDETERMINED'", "'ALARM'"]))
                }

                order_columns, received_cols = self._remap_columns(
                    query_parms['sort_by'], columns_mapper)

                if not received_cols.get('alarm_id', False):
                    order_columns.append(a.c.id)
            else:
                order_columns = [a.c.id]

            if limit:
                query = query.limit(bindparam('b_limit'))
                parms['b_limit'] = limit + 1

            if offset:
                query = query.offset(bindparam('b_offset'))
                parms['b_offset'] = offset

            query = (query.order_by(*order_columns).alias('alarm_id_list'))

            main_query = (self.base_query.select_from(
                self.base_query_from.join(query,
                                          query.c.id == a.c.id)).distinct())

            main_query = main_query.order_by(*order_columns)

            return [
                dict(row)
                for row in conn.execute(main_query, parms).fetchall()
            ]