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 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()]
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()
]