279
300
:param ttl: Number of seconds to keep records for.
282
304
session = sqlalchemy_session.get_session()
283
query = session.query(models.Meter.id)
284
end = timeutils.utcnow() - datetime.timedelta(seconds=ttl)
285
query = query.filter(models.Meter.timestamp < end)
288
query = session.query(models.User.id).filter(~models.User.id.in_(
289
session.query(models.Meter.user_id).group_by(models.Meter.user_id)
291
query.delete(synchronize_session='fetch')
293
query = session.query(models.Project.id)\
294
.filter(~models.Project.id.in_(
295
session.query(models.Meter.project_id).group_by(
296
models.Meter.project_id)))
297
query.delete(synchronize_session='fetch')
299
query = session.query(models.Resource.id)\
300
.filter(~models.Resource.id.in_(
301
session.query(models.Meter.resource_id).group_by(
302
models.Meter.resource_id)))
303
query.delete(synchronize_session='fetch')
305
with session.begin():
306
end = timeutils.utcnow() - datetime.timedelta(seconds=ttl)
307
meter_query = session.query(models.Meter)\
308
.filter(models.Meter.timestamp < end)
309
for meter_obj in meter_query.all():
310
session.delete(meter_obj)
312
query = session.query(models.User).filter(
313
~models.User.id.in_(session.query(models.Meter.user_id)
314
.group_by(models.Meter.user_id)),
315
~models.User.id.in_(session.query(models.Alarm.user_id)
316
.group_by(models.Alarm.user_id)),
317
~models.User.id.in_(session.query(models.AlarmChange.user_id)
318
.group_by(models.AlarmChange.user_id))
320
for user_obj in query.all():
321
session.delete(user_obj)
323
query = session.query(models.Project)\
324
.filter(~models.Project.id.in_(
325
session.query(models.Meter.project_id)
326
.group_by(models.Meter.project_id)),
327
~models.Project.id.in_(
328
session.query(models.Alarm.project_id)
329
.group_by(models.Alarm.project_id)),
330
~models.Project.id.in_(
331
session.query(models.AlarmChange.project_id)
332
.group_by(models.AlarmChange.project_id)),
333
~models.Project.id.in_(
334
session.query(models.AlarmChange.on_behalf_of)
335
.group_by(models.AlarmChange.on_behalf_of))
337
for project_obj in query.all():
338
session.delete(project_obj)
340
query = session.query(models.Resource)\
341
.filter(~models.Resource.id.in_(
342
session.query(models.Meter.resource_id).group_by(
343
models.Meter.resource_id)))
344
for res_obj in query.all():
345
session.delete(res_obj)
306
348
def get_users(source=None):
925
954
return problem_events
927
956
def get_events(self, event_filter):
928
"""Return an iterable of model.Event objects. The event model objects
929
have their Trait model objects available -- filtered by any traits
957
"""Return an iterable of model.Event objects.
932
959
:param event_filter: EventFilter instance
935
start = utils.dt_to_decimal(event_filter.start)
936
end = utils.dt_to_decimal(event_filter.end)
962
start = event_filter.start_time
963
end = event_filter.end_time
937
964
session = sqlalchemy_session.get_session()
965
LOG.debug(_("Getting events that match filter: %s") % event_filter)
938
966
with session.begin():
939
sub_query = session.query(models.Event.id)\
940
.join(models.EventType,
941
models.Event.event_type_id == models.EventType.id)\
943
models.Trait.event_id == models.Event.id)\
944
.filter(models.Event.generated >= start,
945
models.Event.generated <= end)
967
event_query = session.query(models.Event)
969
# Build up the join conditions
970
event_join_conditions = [models.EventType.id ==
971
models.Event.event_type_id]
947
973
if event_filter.event_type:
948
event_type = event_filter.event_type
949
sub_query = sub_query\
950
.filter(models.EventType.desc == event_type)
974
event_join_conditions\
975
.append(models.EventType.desc == event_filter.event_type)
977
event_query = event_query.join(models.EventType,
978
and_(*event_join_conditions))
980
# Build up the where conditions
981
event_filter_conditions = []
982
if event_filter.message_id:
983
event_filter_conditions\
984
.append(models.Event.message_id == event_filter.message_id)
986
event_filter_conditions.append(models.Event.generated >= start)
988
event_filter_conditions.append(models.Event.generated <= end)
990
if event_filter_conditions:
991
event_query = event_query\
992
.filter(and_(*event_filter_conditions))
952
994
event_models_dict = {}
953
if event_filter.traits:
954
sub_query = sub_query.join(models.TraitType,
955
models.TraitType.id ==
956
models.Trait.trait_type_id)
957
for key, value in event_filter.traits.iteritems():
959
sub_query = sub_query.filter(models.TraitType.desc ==
961
elif key == 't_string':
962
sub_query = sub_query.filter(
963
models.Trait.t_string == value)
965
sub_query = sub_query.filter(
966
models.Trait.t_int == value)
967
elif key == 't_datetime':
968
dt = utils.dt_to_decimal(value)
969
sub_query = sub_query.filter(
970
models.Trait.t_datetime == dt)
971
elif key == 't_float':
972
sub_query = sub_query.filter(
973
models.Trait.t_datetime == value)
995
if event_filter.traits_filter:
996
for trait_filter in event_filter.traits_filter:
998
# Build a sub query that joins Trait to TraitType
999
# where the trait name matches
1000
trait_name = trait_filter.pop('key')
1001
conditions = [models.Trait.trait_type_id ==
1002
models.TraitType.id,
1003
models.TraitType.desc == trait_name]
1005
for key, value in trait_filter.iteritems():
1007
conditions.append(models.Trait.t_string == value)
1008
elif key == 'integer':
1009
conditions.append(models.Trait.t_int == value)
1010
elif key == 'datetime':
1011
conditions.append(models.Trait.t_datetime == value)
1012
elif key == 'float':
1013
conditions.append(models.Trait.t_float == value)
1015
trait_query = session.query(models.Trait.event_id)\
1016
.join(models.TraitType, and_(*conditions)).subquery()
1018
event_query = event_query\
1020
models.Event.id == trait_query.c.event_id)
975
# Pre-populate event_models_dict to cover Events without traits
976
events = session.query(models.Event)\
977
.filter(models.Event.generated >= start)\
978
.filter(models.Event.generated <= end)
979
if event_filter.event_type:
981
.join(models.EventType,
982
models.EventType.id ==
983
models.Event.event_type_id)\
984
.filter(models.EventType.desc ==
985
event_filter.event_type)
986
for db_event in events.all():
987
generated = utils.decimal_to_dt(db_event.generated)
988
api_event = api_models.Event(db_event.message_id,
989
db_event.event_type.desc,
991
event_models_dict[db_event.id] = api_event
993
sub_query = sub_query.subquery()
995
all_data = session.query(models.Trait)\
996
.join(sub_query, models.Trait.event_id == sub_query.c.id)
1022
# If there are no trait filters, grab the events from the db
1023
query = session.query(models.Event.id,
1024
models.Event.generated,
1025
models.Event.message_id,
1026
models.EventType.desc)\
1027
.join(models.EventType,
1028
and_(*event_join_conditions))
1029
if event_filter_conditions:
1030
query = query.filter(and_(*event_filter_conditions))
1031
for (id, generated, message_id, desc) in query.all():
1032
event_models_dict[id] = api_models.Event(message_id,
1037
# Build event models for the events
1038
event_query = event_query.subquery()
1039
query = session.query(models.Trait)\
1040
.join(models.TraitType,
1041
models.Trait.trait_type_id == models.TraitType.id)\
1042
.join(event_query, models.Trait.event_id == event_query.c.id)
998
1044
# Now convert the sqlalchemy objects back into Models ...
999
for trait in all_data.all():
1045
for trait in query.all():
1000
1046
event = event_models_dict.get(trait.event_id)
1002
generated = utils.decimal_to_dt(trait.event.generated)
1003
event = api_models.Event(trait.event.message_id,
1004
trait.event.event_type.desc,
1048
event = api_models.Event(
1049
trait.event.message_id,
1050
trait.event.event_type.desc,
1051
trait.event.generated, [])
1006
1052
event_models_dict[trait.event_id] = event
1007
value = trait.get_value()
1008
1053
trait_model = api_models.Trait(trait.trait_type.desc,
1009
1054
trait.trait_type.data_type,
1011
1056
event.append_trait(trait_model)
1013
1058
event_models = event_models_dict.values()
1014
1059
return sorted(event_models, key=operator.attrgetter('generated'))
1062
def get_event_types():
1063
"""Return all event types as an iterable of strings.
1066
session = sqlalchemy_session.get_session()
1067
with session.begin():
1068
query = session.query(models.EventType.desc)\
1069
.order_by(models.EventType.desc)
1070
for name in query.all():
1071
# The query returns a tuple with one element.
1075
def get_trait_types(event_type):
1076
"""Return a dictionary containing the name and data type of
1077
the trait type. Only trait types for the provided event_type are
1080
:param event_type: the type of the Event
1082
session = sqlalchemy_session.get_session()
1084
LOG.debug(_("Get traits for %s") % event_type)
1085
with session.begin():
1086
query = (session.query(models.TraitType.desc,
1087
models.TraitType.data_type)
1089
models.Trait.trait_type_id ==
1090
models.TraitType.id)
1093
models.Trait.event_id)
1094
.join(models.EventType,
1095
and_(models.EventType.id ==
1097
models.EventType.desc ==
1099
.group_by(models.TraitType.desc,
1100
models.TraitType.data_type)
1103
for desc, type in query.all():
1104
yield {'name': desc, 'data_type': type}
1107
def get_traits(event_type, trait_type=None):
1108
"""Return all trait instances associated with an event_type. If
1109
trait_type is specified, only return instances of that trait type.
1111
:param event_type: the type of the Event to filter by
1112
:param trait_type: the name of the Trait to filter by
1115
session = sqlalchemy_session.get_session()
1116
with session.begin():
1117
trait_type_filters = [models.TraitType.id ==
1118
models.Trait.trait_type_id]
1120
trait_type_filters.append(models.TraitType.desc == trait_type)
1122
query = (session.query(models.Trait)
1123
.join(models.TraitType, and_(*trait_type_filters))
1125
models.Event.id == models.Trait.event_id)
1126
.join(models.EventType,
1127
and_(models.EventType.id ==
1128
models.Event.event_type_id,
1129
models.EventType.desc == event_type)))
1131
for trait in query.all():
1132
type = trait.trait_type
1133
yield api_models.Trait(name=type.desc,
1134
dtype=type.data_type,
1135
value=trait.get_value())