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'"])))))
    def __init__(self):
        super(AlarmDefinitionsRepository, self).__init__()

        metadata = MetaData()
        self.a = models.create_a_model(metadata)
        self.aa = models.create_aa_model(metadata)
        self.ad = models.create_ad_model(metadata)
        self.am = models.create_am_model(metadata)
        self.nm = models.create_nm_model(metadata)
        self.md = models.create_md_model(metadata)
        self.mde = models.create_mde_model(metadata)
        self.mdd = models.create_mdd_model(metadata)
        self.sa = models.create_sa_model(metadata)
        self.sad = models.create_sad_model(metadata)
        self.sadd = models.create_sadd_model(metadata)
        a = self.a
        aa = self.aa
        ad = self.ad
        am = self.am
        nm = self.nm
        md = self.md
        sa = self.sa
        mdd = self.mdd
        mde = self.mde
        sad = self.sad
        sadd = self.sadd

        a_s = a.alias('a')
        ad_s = ad.alias('ad')
        self.ad_s = ad_s
        am_s = am.alias('am')
        nm_s = nm.alias('nm')
        md_s = md.alias('md')
        sa_s = sa.alias('sa')
        mdd_s = mdd.alias('mdd')
        mde_s = mde.alias('mde')
        sad_s = sad.alias('sad')
        sadd_s = sadd.alias('sadd')

        aaa_aa = aa.alias('aaa_aa')
        aaa = (select([
            aaa_aa.c.alarm_definition_id,
            models.group_concat([aaa_aa.c.action_id]).label('alarm_actions')
        ]).select_from(aaa_aa).where(
            aaa_aa.c.alarm_state == text("'ALARM'")).group_by(
                aaa_aa.c.alarm_definition_id).alias('aaa'))

        aao_aa = aa.alias('aao_aa')
        aao = (select([
            aao_aa.c.alarm_definition_id,
            models.group_concat([aao_aa.c.action_id]).label('ok_actions')
        ]).select_from(aao_aa).where(
            aao_aa.c.alarm_state == text("'OK'")).group_by(
                aao_aa.c.alarm_definition_id).alias('aao'))

        aau_aa = aa.alias('aau_aa')
        aau = (select([
            aau_aa.c.alarm_definition_id,
            models.group_concat([aau_aa.c.action_id
                                 ]).label('undetermined_actions')
        ]).select_from(aau_aa).where(
            aau_aa.c.alarm_state == text("'UNDETERMINED'")).group_by(
                aau_aa.c.alarm_definition_id).alias('aau'))

        self.base_query_from = (ad_s.outerjoin(
            aaa, aaa.c.alarm_definition_id == ad_s.c.id).outerjoin(
                aao, aao.c.alarm_definition_id == ad_s.c.id).outerjoin(
                    aau, aau.c.alarm_definition_id == ad_s.c.id))

        self.base_query = (select([
            ad_s.c.id, ad_s.c.name, ad_s.c.description, ad_s.c.expression,
            ad_s.c.match_by, ad_s.c.severity, ad_s.c.actions_enabled,
            aaa.c.alarm_actions, aao.c.ok_actions, aau.c.undetermined_actions
        ]))

        self.get_sub_alarms_query = (select([
            sa_s.c.id.label('sub_alarm_id'), sa_s.c.alarm_id, sa_s.c.expression
        ]).select_from(
            sa_s.join(a_s, a_s.c.id == sa_s.c.alarm_id).join(
                ad_s, ad_s.c.id == a_s.c.alarm_definition_id)).where(
                    ad_s.c.tenant_id == bindparam('b_tenant_id')).where(
                        ad_s.c.id == bindparam('b_id')).distinct())

        mdg = (select([
            md_s.c.dimension_set_id,
            models.group_concat([md_s.c.name + text("'='") + md_s.c.value
                                 ]).label('dimensions')
        ]).select_from(md_s).group_by(md_s.c.dimension_set_id).alias('mdg'))

        self.get_alarm_metrics_query = (select([
            a_s.c.id.label('alarm_id'), mde_s.c.name, mdg.c.dimensions
        ]).select_from(
            a_s.join(ad_s, ad_s.c.id == a_s.c.alarm_definition_id).join(
                am_s, am_s.c.alarm_id == a_s.c.id).join(
                    mdd_s,
                    mdd_s.c.id == am_s.c.metric_definition_dimensions_id).join(
                        mde_s,
                        mde_s.c.id == mdd_s.c.metric_definition_id).outerjoin(
                            mdg, mdg.c.dimension_set_id ==
                            mdd_s.c.metric_dimension_set_id)
        ).where(ad_s.c.tenant_id == bindparam('b_tenant_id')).where(
            ad_s.c.id == bindparam('b_id')).order_by(a_s.c.id).distinct())

        self.soft_delete_ad_query = (update(ad).where(
            ad.c.tenant_id == bindparam('b_tenant_id')).where(
                ad.c.id == bindparam('b_id')).where(
                    ad.c.deleted_at == null()).values(
                        deleted_at=datetime.datetime.utcnow()))

        self.delete_a_query = (delete(a).where(
            a.c.alarm_definition_id == bindparam('b_id')))

        columns_gc = [sadd_s.c.dimension_name + text("'='") + sadd_s.c.value]
        saddg = (select([
            sadd_s.c.sub_alarm_definition_id,
            models.group_concat(columns_gc).label('dimensions')
        ]).select_from(sadd_s).group_by(
            sadd_s.c.sub_alarm_definition_id).alias('saddg'))

        self.get_sub_alarm_definitions_query = (select([
            sad_s, saddg.c.dimensions
        ]).select_from(
            sad_s.outerjoin(
                saddg, saddg.c.sub_alarm_definition_id == sad_s.c.id)).where(
                    sad_s.c.alarm_definition_id == bindparam(
                        'b_alarm_definition_id')))

        self.create_alarm_definition_insert_ad_query = (insert(ad).values(
            id=bindparam('b_id'),
            tenant_id=bindparam('b_tenant_id'),
            name=bindparam('b_name'),
            description=bindparam('b_description'),
            expression=bindparam('b_expression'),
            severity=bindparam('b_severity'),
            match_by=bindparam('b_match_by'),
            actions_enabled=bindparam('b_actions_enabled'),
            created_at=bindparam('b_created_at'),
            updated_at=bindparam('b_updated_at')))

        self.create_alarm_definition_insert_sad_query = (insert(sad).values(
            id=bindparam('b_id'),
            alarm_definition_id=bindparam('b_alarm_definition_id'),
            function=bindparam('b_function'),
            metric_name=bindparam('b_metric_name'),
            operator=bindparam('b_operator'),
            threshold=bindparam('b_threshold'),
            period=bindparam('b_period'),
            periods=bindparam('b_periods'),
            is_deterministic=bindparam('b_is_deterministic'),
            created_at=bindparam('b_created_at'),
            updated_at=bindparam('b_updated_at')))

        b_sad_id = bindparam('b_sub_alarm_definition_id')
        self.create_alarm_definition_insert_sadd_query = (insert(sadd).values(
            sub_alarm_definition_id=b_sad_id,
            dimension_name=bindparam('b_dimension_name'),
            value=bindparam('b_value')))

        self.update_or_patch_alarm_definition_update_ad_query = (
            update(ad).where(ad.c.tenant_id == bindparam('b_tenant_id')).where(
                ad.c.id == bindparam('b_id')))

        self.update_or_patch_alarm_definition_delete_sad_query = (
            delete(sad).where(sad.c.id == bindparam('b_id')))

        self.update_or_patch_alarm_definition_update_sad_query = (
            update(sad).where(sad.c.id == bindparam('b_id')).values(
                operator=bindparam('b_operator'),
                threshold=bindparam('b_threshold'),
                is_deterministic=bindparam('b_is_deterministic'),
                updated_at=bindparam('b_updated_at')))

        b_ad_id = bindparam('b_alarm_definition_id'),
        self.update_or_patch_alarm_definition_insert_sad_query = (
            insert(sad).values(
                id=bindparam('b_id'),
                alarm_definition_id=b_ad_id,
                function=bindparam('b_function'),
                metric_name=bindparam('b_metric_name'),
                operator=bindparam('b_operator'),
                threshold=bindparam('b_threshold'),
                period=bindparam('b_period'),
                periods=bindparam('b_periods'),
                is_deterministic=bindparam('b_is_deterministic'),
                created_at=bindparam('b_created_at'),
                updated_at=bindparam('b_updated_at')))

        self.update_or_patch_alarm_definition_insert_sadd_query = (
            insert(sadd).values(sub_alarm_definition_id=b_sad_id,
                                dimension_name=bindparam('b_dimension_name'),
                                value=bindparam('b_value')))

        self.delete_aa_query = (delete(aa).where(
            aa.c.alarm_definition_id == bindparam('b_alarm_definition_id')))

        self.delete_aa_state_query = (delete(aa).where(
            aa.c.alarm_definition_id == bindparam('b_alarm_definition_id')
        ).where(aa.c.alarm_state == bindparam('b_alarm_state')))

        self.select_nm_query = (select([
            nm_s.c.id
        ]).select_from(nm_s).where(nm_s.c.id == bindparam('b_id')))

        self.insert_aa_query = (insert(aa).values(
            alarm_definition_id=bindparam('b_alarm_definition_id'),
            alarm_state=bindparam('b_alarm_state'),
            action_id=bindparam('b_action_id')))
Example #4
0
    def __init__(self):
        super(AlarmsRepository, self).__init__()

        metadata = MetaData()
        self.a_du = models.create_a_model(metadata)
        self.aa = models.create_aa_model(metadata).alias('aa')
        self.sa = models.create_sa_model(metadata).alias('sa')
        self.ad = models.create_ad_model(metadata).alias('ad')
        self.am = models.create_am_model(metadata).alias('am')
        self.md = models.create_md_model(metadata).alias('md')
        self.mdd = models.create_mdd_model(metadata).alias('mdd')
        self.mde = models.create_mde_model(metadata).alias('mde')
        self.sad = models.create_sad_model(metadata).alias('sad')
        self.sadd = models.create_sadd_model(metadata).alias('sadd')
        a = self.a_du
        self.a = a.alias('a')
        a_s = self.a
        sa = self.sa
        ad = self.ad
        am = self.am
        md = self.md
        mdd = self.mdd
        mde = self.mde

        gc_columns = [md.c.name + text("'='") + md.c.value]

        mdg = (select([md.c.dimension_set_id,
                      models.group_concat(gc_columns).label('dimensions')])
               .select_from(md)
               .group_by(md.c.dimension_set_id).alias('mdg'))

        self.base_query_from = (a_s.join(ad, ad.c.id == a_s.c.alarm_definition_id)
                                .join(am, am.c.alarm_id == a_s.c.id)
                                .join(mdd, mdd.c.id == am.c.metric_definition_dimensions_id)
                                .join(mde, mde.c.id == mdd.c.metric_definition_id)
                                .outerjoin(mdg, mdg.c.dimension_set_id == mdd.c.metric_dimension_set_id))

        self.base_query = select([a_s.c.id.label('alarm_id'),
                                  a_s.c.state,
                                  a_s.c.state_updated_at.
                                  label('state_updated_timestamp'),
                                  a_s.c.updated_at.label('updated_timestamp'),
                                  a_s.c.created_at.label('created_timestamp'),
                                  a_s.c.lifecycle_state,
                                  a_s.c.link,
                                  ad.c.id.label('alarm_definition_id'),
                                  ad.c.name.label('alarm_definition_name'),
                                  ad.c.description.label('alarm_definition_description'),
                                  ad.c.severity,
                                  mde.c.name.label('metric_name'),
                                  mdg.c.dimensions.label('metric_dimensions')])

        self.base_subquery_list = (select([a_s.c.id])
                                   .select_from(a_s.join(ad, a_s.c.alarm_definition_id == ad.c.id)))

        self.get_ad_query = (select([ad])
                             .select_from(ad.join(a, ad.c.id == a.c.alarm_definition_id))
                             .where(ad.c.tenant_id == bindparam('b_tenant_id'))
                             .where(a.c.id == bindparam('b_id')))

        self.get_am_query = (select([a_s.c.id.label('alarm_id'),
                                     mde.c.name,
                                     mdg.c.dimensions])
                             .select_from(a_s.join(am, am.c.alarm_id == a_s.c.id)
                                          .join(mdd,
                                                mdd.c.id ==
                                                am.c.metric_definition_dimensions_id)
                                          .join(mde, mde.c.id == mdd.c.metric_definition_id)
                                          .outerjoin(mdg,
                                                     mdg.c.dimension_set_id ==
                                                     mdd.c.metric_dimension_set_id))
                             .where(a_s.c.id == bindparam('b_id'))
                             .order_by(a_s.c.id)
                             .distinct())

        self.get_sa_query = (select([sa.c.id.label('sub_alarm_id'),
                                     sa.c.alarm_id,
                                     sa.c.expression,
                                     ad.c.id.label('alarm_definition_id')])
                             .select_from(sa.join(a_s,
                                                  a_s.c.id == sa.c.alarm_id)
                                          .join(ad,
                                                ad.c.id == a_s.c.alarm_definition_id))
                             .where(ad.c.tenant_id == bindparam('b_tenant_id'))
                             .where(a_s.c.id == bindparam('b_id'))
                             .distinct())

        self.get_a_query = (select([a_s.c.state, a_s.c.link, a_s.c.lifecycle_state])
                            .select_from(a_s.join(ad,
                                                  ad.c.id == a_s.c.alarm_definition_id))
                            .where(ad.c.tenant_id == bindparam('b_tenant_id'))
                            .where(a_s.c.id == bindparam('b_id')))

        self.get_a_ad_query = (select([a_s.c.id])
                               .select_from(a_s.join(ad,
                                                     ad.c.id ==
                                                     a_s.c.alarm_definition_id))
                               .where(ad.c.tenant_id == bindparam('b_tenant_id'))
                               .where(a_s.c.id == bindparam('b_id'))
                               .alias('a_ad'))

        select_tmp = (select([literal_column('id')])
                      .select_from(self.get_a_ad_query)
                      .distinct()
                      .alias('temporarytable'))

        self.delete_alarm_query = (delete(a)
                                   .where(a.c.id.in_(select_tmp)))

        md_ = (select([mde.c.id])
               .where(mde.c.name == bindparam('b_md_name')).alias('md_'))

        self.get_a_am_query = (select([a_s.c.id])
                               .select_from(a_s.join(am,
                                                     am.c.alarm_id ==
                                                     a_s.c.id)
                                            .join(mdd,
                                                  mdd.c.id ==
                                                  am.c.metric_definition_dimensions_id)
                                            .join(md_,
                                                  md_.c.id ==
                                                  mdd.c.metric_definition_id)))
Example #5
0
    def __init__(self):
        super(AlarmsRepository, self).__init__()

        metadata = MetaData()
        self.a_du = models.create_a_model(metadata)
        self.aa = models.create_aa_model(metadata).alias('aa')
        self.sa = models.create_sa_model(metadata).alias('sa')
        self.ad = models.create_ad_model(metadata).alias('ad')
        self.am = models.create_am_model(metadata).alias('am')
        self.md = models.create_md_model(metadata).alias('md')
        self.mdd = models.create_mdd_model(metadata).alias('mdd')
        self.mde = models.create_mde_model(metadata).alias('mde')
        self.sad = models.create_sad_model(metadata).alias('sad')
        self.sadd = models.create_sadd_model(metadata).alias('sadd')
        a = self.a_du
        self.a = a.alias('a')
        a_s = self.a
        sa = self.sa
        ad = self.ad
        am = self.am
        md = self.md
        mdd = self.mdd
        mde = self.mde

        gc_columns = [md.c.name + text("'='") + md.c.value]

        mdg = (select([
            md.c.dimension_set_id,
            models.group_concat(gc_columns).label('dimensions')
        ]).select_from(md).group_by(md.c.dimension_set_id).alias('mdg'))

        self.base_query_from = (a_s.join(
            ad, ad.c.id == a_s.c.alarm_definition_id).join(
                am, am.c.alarm_id == a_s.c.id).join(
                    mdd,
                    mdd.c.id == am.c.metric_definition_dimensions_id).join(
                        mde, mde.c.id == mdd.c.metric_definition_id).outerjoin(
                            mdg, mdg.c.dimension_set_id ==
                            mdd.c.metric_dimension_set_id))

        self.base_query = select([
            a_s.c.id.label('alarm_id'), a_s.c.state,
            a_s.c.state_updated_at.label('state_updated_timestamp'),
            a_s.c.updated_at.label('updated_timestamp'),
            a_s.c.created_at.label('created_timestamp'), a_s.c.lifecycle_state,
            a_s.c.link,
            ad.c.id.label('alarm_definition_id'),
            ad.c.name.label('alarm_definition_name'), ad.c.severity,
            mde.c.name.label('metric_name'),
            mdg.c.dimensions.label('metric_dimensions')
        ])

        self.base_subquery_list = (select([a_s.c.id]).select_from(
            a_s.join(ad, a_s.c.alarm_definition_id == ad.c.id)))

        self.get_ad_query = (select([ad]).select_from(
            ad.join(a, ad.c.id == a.c.alarm_definition_id)).where(
                ad.c.tenant_id == bindparam('b_tenant_id')).where(
                    a.c.id == bindparam('b_id')))

        self.get_am_query = (select([
            a_s.c.id.label('alarm_id'), mde.c.name, mdg.c.dimensions
        ]).select_from(
            a_s.join(am, am.c.alarm_id == a_s.c.id).join(
                mdd, mdd.c.id == am.c.metric_definition_dimensions_id).join(
                    mde, mde.c.id == mdd.c.metric_definition_id).outerjoin(
                        mdg, mdg.c.dimension_set_id ==
                        mdd.c.metric_dimension_set_id)).where(
                            a_s.c.id == bindparam('b_id')).order_by(
                                a_s.c.id).distinct())

        self.get_sa_query = (select([
            sa.c.id.label('sub_alarm_id'), sa.c.alarm_id, sa.c.expression,
            ad.c.id.label('alarm_definition_id')
        ]).select_from(
            sa.join(a_s, a_s.c.id == sa.c.alarm_id).join(
                ad, ad.c.id == a_s.c.alarm_definition_id)).where(
                    ad.c.tenant_id == bindparam('b_tenant_id')).where(
                        a_s.c.id == bindparam('b_id')).distinct())

        self.get_a_query = (select(
            [a_s.c.state, a_s.c.link, a_s.c.lifecycle_state]).select_from(
                a_s.join(ad, ad.c.id == a_s.c.alarm_definition_id)).where(
                    ad.c.tenant_id == bindparam('b_tenant_id')).where(
                        a_s.c.id == bindparam('b_id')))

        self.get_a_ad_query = (select([a_s.c.id]).select_from(
            a_s.join(ad, ad.c.id == a_s.c.alarm_definition_id)).where(
                ad.c.tenant_id == bindparam('b_tenant_id')).where(
                    a_s.c.id == bindparam('b_id')).alias('a_ad'))

        select_tmp = (select([literal_column('id')]).select_from(
            self.get_a_ad_query).distinct().alias('temporarytable'))

        self.delete_alarm_query = (delete(a).where(a.c.id.in_(select_tmp)))

        md_ = (select([
            mde.c.id
        ]).where(mde.c.name == bindparam('b_md_name')).alias('md_'))

        self.get_a_am_query = (select([a_s.c.id]).select_from(
            a_s.join(am, am.c.alarm_id == a_s.c.id).join(
                mdd, mdd.c.id == am.c.metric_definition_dimensions_id).join(
                    md_, md_.c.id == mdd.c.metric_definition_id)))
    def __init__(self):
        super(AlarmDefinitionsRepository, self).__init__()

        metadata = MetaData()
        self.a = models.create_a_model(metadata)
        self.aa = models.create_aa_model(metadata)
        self.ad = models.create_ad_model(metadata)
        self.am = models.create_am_model(metadata)
        self.nm = models.create_nm_model(metadata)
        self.md = models.create_md_model(metadata)
        self.mde = models.create_mde_model(metadata)
        self.mdd = models.create_mdd_model(metadata)
        self.sa = models.create_sa_model(metadata)
        self.sad = models.create_sad_model(metadata)
        self.sadd = models.create_sadd_model(metadata)
        a = self.a
        aa = self.aa
        ad = self.ad
        am = self.am
        nm = self.nm
        md = self.md
        sa = self.sa
        mdd = self.mdd
        mde = self.mde
        sad = self.sad
        sadd = self.sadd

        a_s = a.alias('a')
        ad_s = ad.alias('ad')
        self.ad_s = ad_s
        am_s = am.alias('am')
        nm_s = nm.alias('nm')
        md_s = md.alias('md')
        sa_s = sa.alias('sa')
        mdd_s = mdd.alias('mdd')
        mde_s = mde.alias('mde')
        sad_s = sad.alias('sad')
        sadd_s = sadd.alias('sadd')

        aaa_aa = aa.alias('aaa_aa')
        aaa = (select([aaa_aa.c.alarm_definition_id,
                       models.group_concat([aaa_aa.c.action_id]).label('alarm_actions')])
               .select_from(aaa_aa)
               .where(aaa_aa.c.alarm_state == text("'ALARM'"))
               .group_by(aaa_aa.c.alarm_definition_id)
               .alias('aaa'))

        aao_aa = aa.alias('aao_aa')
        aao = (select([aao_aa.c.alarm_definition_id,
                       models.group_concat([aao_aa.c.action_id]).label('ok_actions')])
               .select_from(aao_aa)
               .where(aao_aa.c.alarm_state == text("'OK'"))
               .group_by(aao_aa.c.alarm_definition_id)
               .alias('aao'))

        aau_aa = aa.alias('aau_aa')
        aau = (select([aau_aa.c.alarm_definition_id,
                       models.group_concat([aau_aa.c.action_id]).label('undetermined_actions')])
               .select_from(aau_aa)
               .where(aau_aa.c.alarm_state == text("'UNDETERMINED'"))
               .group_by(aau_aa.c.alarm_definition_id)
               .alias('aau'))

        self.base_query_from = (ad_s.outerjoin(aaa, aaa.c.alarm_definition_id == ad_s.c.id)
                                .outerjoin(aao, aao.c.alarm_definition_id == ad_s.c.id)
                                .outerjoin(aau, aau.c.alarm_definition_id == ad_s.c.id))

        self.base_query = (select([ad_s.c.id,
                                   ad_s.c.name,
                                   ad_s.c.description,
                                   ad_s.c.expression,
                                   ad_s.c.match_by,
                                   ad_s.c.severity,
                                   ad_s.c.actions_enabled,
                                   aaa.c.alarm_actions,
                                   aao.c.ok_actions,
                                   aau.c.undetermined_actions]))

        self.get_sub_alarms_query = (
            select(
                [
                    sa_s.c.id.label('sub_alarm_id'),
                    sa_s.c.alarm_id,
                    sa_s.c.expression]) .select_from(
                sa_s.join(
                    a_s,
                    a_s.c.id == sa_s.c.alarm_id) .join(
                        ad_s,
                        ad_s.c.id == a_s.c.alarm_definition_id)) .where(
                            ad_s.c.tenant_id == bindparam('b_tenant_id')) .where(
                                ad_s.c.id == bindparam('b_id')) .distinct())

        mdg = (select([md_s.c.dimension_set_id,
                       models.group_concat(
                           [md_s.c.name + text("'='") + md_s.c.value]).label('dimensions')])
               .select_from(md_s)
               .group_by(md_s.c.dimension_set_id)
               .alias('mdg'))

        self.get_alarm_metrics_query = (
            select(
                [a_s.c.id.label('alarm_id'),
                 mde_s.c.name,
                 mdg.c.dimensions]) .select_from(
                a_s.join(
                    ad_s,
                    ad_s.c.id == a_s.c.alarm_definition_id) .join(
                    am_s,
                    am_s.c.alarm_id == a_s.c.id) .join(
                    mdd_s,
                    mdd_s.c.id == am_s.c.metric_definition_dimensions_id) .join(
                    mde_s,
                    mde_s.c.id == mdd_s.c.metric_definition_id) .outerjoin(
                    mdg,
                    mdg.c.dimension_set_id == mdd_s.c.metric_dimension_set_id)) .where(
                ad_s.c.tenant_id == bindparam('b_tenant_id')) .where(
                ad_s.c.id == bindparam('b_id')) .order_by(
                a_s.c.id) .distinct())

        self.soft_delete_ad_query = (update(ad)
                                     .where(ad.c.tenant_id == bindparam('b_tenant_id'))
                                     .where(ad.c.id == bindparam('b_id'))
                                     .where(ad.c.deleted_at == null())
                                     .values(deleted_at=datetime.datetime.utcnow()))

        self.delete_a_query = (delete(a)
                               .where(a.c.alarm_definition_id == bindparam('b_id')))

        columns_gc = [sadd_s.c.dimension_name + text("'='") + sadd_s.c.value]
        saddg = (select([sadd_s.c.sub_alarm_definition_id,
                         models.group_concat(columns_gc).label('dimensions')])
                 .select_from(sadd_s)
                 .group_by(sadd_s.c.sub_alarm_definition_id)
                 .alias('saddg'))

        self.get_sub_alarm_definitions_query = (
            select(
                [
                    sad_s,
                    saddg.c.dimensions]) .select_from(
                sad_s.outerjoin(
                    saddg,
                    saddg.c.sub_alarm_definition_id == sad_s.c.id)) .where(
                        sad_s.c.alarm_definition_id == bindparam('b_alarm_definition_id')))

        self.create_alarm_definition_insert_ad_query = (
            insert(ad) .values(
                id=bindparam('b_id'),
                tenant_id=bindparam('b_tenant_id'),
                name=bindparam('b_name'),
                description=bindparam('b_description'),
                expression=bindparam('b_expression'),
                severity=bindparam('b_severity'),
                match_by=bindparam('b_match_by'),
                actions_enabled=bindparam('b_actions_enabled'),
                created_at=bindparam('b_created_at'),
                updated_at=bindparam('b_updated_at')))

        self.create_alarm_definition_insert_sad_query = (
            insert(sad) .values(
                id=bindparam('b_id'),
                alarm_definition_id=bindparam('b_alarm_definition_id'),
                function=bindparam('b_function'),
                metric_name=bindparam('b_metric_name'),
                operator=bindparam('b_operator'),
                threshold=bindparam('b_threshold'),
                period=bindparam('b_period'),
                periods=bindparam('b_periods'),
                is_deterministic=bindparam('b_is_deterministic'),
                created_at=bindparam('b_created_at'),
                updated_at=bindparam('b_updated_at')))

        b_sad_id = bindparam('b_sub_alarm_definition_id')
        self.create_alarm_definition_insert_sadd_query = (
            insert(sadd) .values(
                sub_alarm_definition_id=b_sad_id,
                dimension_name=bindparam('b_dimension_name'),
                value=bindparam('b_value')))

        self.update_or_patch_alarm_definition_update_ad_query = (
            update(ad) .where(
                ad.c.tenant_id == bindparam('b_tenant_id')) .where(
                ad.c.id == bindparam('b_id')))

        self.update_or_patch_alarm_definition_delete_sad_query = (
            delete(sad) .where(sad.c.id == bindparam('b_id')))

        self.update_or_patch_alarm_definition_update_sad_query = (
            update(sad) .where(
                sad.c.id == bindparam('b_id')) .values(
                operator=bindparam('b_operator'),
                threshold=bindparam('b_threshold'),
                is_deterministic=bindparam('b_is_deterministic'),
                updated_at=bindparam('b_updated_at')))

        b_ad_id = bindparam('b_alarm_definition_id'),
        self.update_or_patch_alarm_definition_insert_sad_query = (
            insert(sad) .values(
                id=bindparam('b_id'),
                alarm_definition_id=b_ad_id,
                function=bindparam('b_function'),
                metric_name=bindparam('b_metric_name'),
                operator=bindparam('b_operator'),
                threshold=bindparam('b_threshold'),
                period=bindparam('b_period'),
                periods=bindparam('b_periods'),
                is_deterministic=bindparam('b_is_deterministic'),
                created_at=bindparam('b_created_at'),
                updated_at=bindparam('b_updated_at')))

        self.update_or_patch_alarm_definition_insert_sadd_query = (
            insert(sadd) .values(
                sub_alarm_definition_id=b_sad_id,
                dimension_name=bindparam('b_dimension_name'),
                value=bindparam('b_value')))

        self.delete_aa_query = (delete(aa)
                                .where(aa.c.alarm_definition_id
                                       == bindparam('b_alarm_definition_id')))

        self.delete_aa_state_query = (
            delete(aa) .where(
                aa.c.alarm_definition_id == bindparam('b_alarm_definition_id')) .where(
                aa.c.alarm_state == bindparam('b_alarm_state')))

        self.select_nm_query = (select([nm_s.c.id])
                                .select_from(nm_s)
                                .where(nm_s.c.id == bindparam('b_id')))

        self.insert_aa_query = (insert(aa)
                                .values(
                                    alarm_definition_id=bindparam('b_alarm_definition_id'),
                                    alarm_state=bindparam('b_alarm_state'),
                                    action_id=bindparam('b_action_id')))