1#!/usr/bin/env python3
2
3"""Store and retrieve Events in the database EVENTS table."""
4
5import logging
6import operator
7from fnmatch import fnmatch
8from datetime import datetime
9from datetime import timedelta
10from typing import Union
11from typing import List
12from typing import Any
13
14from chart.db.connection import db_connect
15from chart.common.traits import is_listlike
16from chart.common.traits import name_of_thing
17from chart.db.settings import DatabaseEngine
18from chart.db.func import Reversed
19
20from chart.project import SID
21from chart.common.xml import parse_xml
22from chart.common.xml import xml_to_str
23from chart.common.xml import XMLElement
24from chart.events.eventclass import EventClass
25from chart.events.event import Event
26from chart.events.eventclass import EventTimeFilter
27from chart import settings
28from chart.web.user import User
29
30logger = logging.getLogger()
31
32db_conn = db_connect('EVENTS')
33
34if settings.EVENTVIEWER_MULTITABLE_SUPPORT:
35 # Classes which can support reading from tables into pseudo-events
36 from chart.products.events import mof_events
37 from chart.products.events import tc_events
38 from chart.products.events import tm_events
39 HANDLER_MODULES = (mof_events, tc_events, tm_events)
40 # These are the default handler modules for non-event tables. Event class XMLs can also define
41 # a custom module in the '<decoder>' field, that is then used to handle events
42 # of that class.
43
44else:
45 HANDLER_MODULES = tuple()
46
47def store_event(event, commit=True, quiet=False): # (unused argument) pylint: disable=W0613
48 """Store instance of event in the database."""
49 # was called store_in_db
50 event.check()
51 prop_str = xml_to_str(event.xml_properties(), xmlcharref=True)
52
53 # query_insert_with_return occasionally fails on Oracle 10.
54 # however we need it since ingester only sends event notifications if the event has an ID
55 sid_vars, sid_binds = SID.sql_sys_insert('EVENTS')
56 sid_vals = SID.bind_sys_insert('EVENTS', event.sid)
57 if event.gen_method is not None:
58 event.event_id = db_conn.query_insert_with_return(
59 ('INSERT INTO EVENTS '
60 '(event_class, gen_method, gen_time, start_time, stop_time, properties{sid_vars}) '
61 'VALUES '
62 '(:event_class, :gen_method, :gen_time, :start_time, :stop_time, '
63 ':properties{sid_binds})').format(sid_vars=sid_vars, sid_binds=sid_binds),
64 'id',
65 start_time=event.start_time,
66 stop_time=event.stop_time,
67 gen_time=event.gen_time,
68 event_class=event.event_classname,
69 gen_method=event.gen_method,
70 properties=prop_str,
71 **sid_vals)
72
73 else:
74 event.event_id = db_conn.query_insert_with_return(
75 ('INSERT INTO EVENTS '
76 '(event_class, gen_time, start_time, stop_time, properties{sid_vars}) '
77 'VALUES '
78 '(:event_class, :gen_time, :start_time, :stop_time, '
79 ':properties{sid_binds})').format(sid_vars=sid_vars, sid_binds=sid_binds),
80 'id',
81 start_time=event.start_time,
82 stop_time=event.stop_time,
83 gen_time=event.gen_time,
84 event_class=event.event_classname,
85 properties=prop_str,
86 **sid_vals)
87
88 # if not quiet:
89 # logger.debug('Recorded as event id {id}'.format(id=event.event_id))
90
91 if commit:
92 db_conn.commit()
93
94
95def update_event(event, properties=None):
96 """Update event "in place" in the database. Used for OPERATOR events only.
97
98 If `properties` is a list of strings (property names), only update those properties.
99 """
100 # was called update_in_db
101
102 if properties is not None:
103 # this is not fully implemented
104 # if len(properties) != 1:
105 # raise ValueError('update_in_db() properties parameter is only implemented '
106 # 'for a single parameter')
107
108 # updates = []
109 bindvars = {'event_id': event.event_id}
110 for p in properties:
111 if p == 'gen_time':
112 # value = self.gen_time
113 # updates.append((p. self.gen_time))
114 bindvars[p] = event.gen_time
115
116 # stop_time for OPERATOR events can be updated in place
117 elif p == 'stop_time':
118 bindvars[p] = event.stop_time
119
120 else:
121 raise ValueError(
122 'update_in_db(): request for quickupdate to unhandled property: {p}'.format(
123 p=p))
124
125 db_conn.query('UPDATE events SET {updates} WHERE id=:event_id'.format(
126 updates=','.join('{k}=:{k}'.format(k=k) for k in properties)),
127 **bindvars)
128 db_conn.commit()
129 return
130
131 # check if we are already in DB
132 count, = db_conn.query('SELECT count(*) FROM events WHERE id=:id',
133 id=event.event_id).fetchone()
134 if count != 1:
135 raise ValueError('Event id {id} found {count} times, cannot update'.format(
136 id=event.event_id, count=count))
137
138 event.check()
139 sid_vars = SID.sql_sys_update('EVENTS')
140 sid_vals = SID.bind_sys_insert('EVENTS', event.sid)
141 db_conn.query(
142 'UPDATE events SET '
143 'event_class=:event_classname, '
144 'start_time=:start_time, '
145 'stop_time=:stop_time, '
146 'gen_time=:gen_time, '
147 'properties=:properties{sid} '
148 'WHERE id=:event_id'.format(sid=sid_vars),
149 event_id=event.event_id,
150 start_time=event.start_time,
151 stop_time=event.stop_time,
152 gen_time=event.gen_time,
153 event_classname=event.event_classname,
154 properties=xml_to_str(event.xml_properties()),
155 **sid_vals)
156 db_conn.commit()
157
158
159def include_event_check(properties, inst_properties) -> bool:
160 """Check to see if event matches include critera.
161
162 Args:
163 `properties`: (list of tuples): Filter results by property match, (name, value, op).
164 `inst_properties`: (xml): Event properties to check
165
166 Returns:
167 True if the event is accepted for current render.
168 """
169
170 accept = None
171
172 if properties is not None:
173 for name, search_value, op in properties:
174 if name not in str(inst_properties): # convert from byte array (oracle) to string
175 accept = False
176 break
177
178 # find filter parameter and extract value
179 elem = XMLElement(from_text=inst_properties)
180
181 # logger.debug('search search for ' + name)
182 # This shouldn't happen with a well-populated database, but sometimes it just happens
183 # that Events get into the database with missing Required instance properties, and to
184 # avoid runtime crashes we just ignore it
185 value = ''
186 for prop_elem in elem.findall('property'):
187 prop_name = prop_elem.parse_str('name')
188 if prop_name == name:
189 value = prop_elem.parse_str('value')
190 if value is None:
191 value = ''
192
193 break
194
195 # perform check
196 if op == operator.eq:
197 accept = fnmatch(value.upper(), search_value.upper())
198
199 else:
200 accept = op(value.upper(), str(search_value).upper())
201
202 if not accept:
203 # do not bother with anymore checks
204 break
205
206 else:
207 # no filter, get all events
208 accept = True
209
210 return accept
211
212
213def find_events_events_table(event_id,
214 start_time,
215 start_time_lt,
216 stop_time,
217 min_duration,
218 max_duration,
219 event_classes,
220 sid,
221 properties,
222 ordering,
223 from_to,
224 count):
225 """Looks for events only in the EVENTS table.
226
227 Args:
228 `start_time` (datetime): Start of time range to search
229 `stop_time` (datetime): End of time range to search
230 `properties`: (list): list of property triplets (name, value, op)
231 `sid` (SID) Source-ID to search for
232 `count` (bool): just return the number of events found
233 `ordering` (list of str | callable): Columns to order results by
234
235 Returns:
236 events: list of events which match input requirements
237 """
238 # logger.debug('find_events_events_table id {id} start {strt} stop {stop} cls {cls}'.format(
239 # id=event_id, strt=start_time, stop=stop_time, cls=event_classes))
240 clauses = []
241 bindvars = {}
242
243 if event_id is not None:
244 clauses.append('id=:event_id')
245 bindvars['event_id'] = event_id
246
247 # test for timerange clauses
248 if start_time is not None and stop_time is not None:
249 # test for intersection with start_time, stop_time
250 # check for start and stop equal
251 if start_time == stop_time:
252 clauses.append('(start_time=:start_time)')
253 bindvars.update({'start_time': start_time})
254
255 else:
256 # This algorithm scans for all events whose start time lies within the time range
257 # we're scanning.
258 # A previous version of this algorithm tool event stop time into account also
259 # and returned all events which intersect with the scan time range.
260 # This was changed for performance reasons to allow EVENTS table index to be used
261 # and also because the old functionality is believed to be unnecessary
262 clauses.append('start_time>=:start_time AND start_time<:stop_time')
263 bindvars.update({'start_time': start_time,
264 'stop_time': stop_time})
265
266 elif start_time is not None and start_time_lt is not None:
267 # test for start time in range start_time, start_time_lt
268 clauses.append('start_time>=:start_time AND start_time<:start_time_lt')
269 bindvars['start_time'] = start_time
270 bindvars['start_time_lt'] = start_time_lt
271
272 elif start_time is not None and stop_time is None:
273 # lower bounded
274 clauses.append('start_time>=:start_time')
275 bindvars['start_time'] = start_time
276
277 elif start_time is None and stop_time is not None:
278 # upper bounded
279 clauses.append('start_time<:stop_time')
280 bindvars['stop_time'] = stop_time
281
282 if min_duration is not None:
283 clauses.extend(('stop_time IS NOT NULL',
284 '(stop_time-start_time) >= :min_duration'))
285 bindvars['min_duration'] = min_duration
286
287 if max_duration is not None:
288 clauses.extend(('stop_time IS NOT NULL',
289 '(stop_time-start_time) <= :max_duration'))
290 bindvars['max_duration'] = max_duration
291
292 if not is_listlike(event_classes):
293 event_classes = (event_classes, )
294
295 cls_sql = []
296 for i, cls in enumerate(event_classes):
297 # allow client to pass in a list containing None (events digest does this...)
298 if cls is None:
299 continue
300
301 varname = 'cls{i}'.format(i=i)
302 if isinstance(cls, str) and '%' in cls:
303 # the user has specified `cls` as a string
304 cls_sql.append('event_class LIKE :{var}'.format(var=varname))
305
306 else:
307 # the user has passed in an EventClass instance
308 cls_sql.append('event_class=:{var}'.format(var=varname))
309
310 bindvars[varname] = name_of_thing(cls)
311
312 if len(cls_sql) > 0:
313 clauses.append('({clauses})'.format(clauses=' OR '.join(cls_sql)))
314
315 # test for sid clauses
316 if sid is not None:
317 clauses.append(SID.sql_sys_where('EVENTS', sid))
318
319 if len(clauses) == 0:
320 # allow the query to proceed with no WHERE clauses
321 clauses.append('1=1')
322
323 events = []
324 events_count = 0
325 if from_to is not None:
326 page_start, page_stop = from_to
327
328 order_clauses = []
329 for o in ordering:
330 if callable(o):
331 order_clauses.append(o())
332
333 else:
334 order_clauses.append(o)
335
336 sql = ('SELECT event_class, start_time, stop_time, {props}, id, {sid} '
337 'FROM EVENTS e '
338 'WHERE {clauses} ORDER BY {ordering}').format(
339 props='e.properties.getBlobVal(871)' if db_conn.engine is DatabaseEngine.ORACLE else\
340 'e.properties',
341 clauses=' AND '.join(clauses),
342 ordering=','.join(order_clauses),
343 sid=','.join(SID.sql_sys_select('EVENTS')))
344
345 for event_classname, start_time, stop_time, properties_db, event_id, *sid_db in db_conn.query(
346 sql, **bindvars):
347 sid = SID.from_sys_select('EVENTS', sid_db)
348
349 # ensure properties passed as string
350 if isinstance(properties_db, str):
351 # postgres returns xml as str
352 inst_properties = properties_db
353
354 else:
355 # oracle returns xml as a handle
356 inst_properties = properties_db.read()
357
358 # filter properites in inst_properties before creating event, much faster than creating
359 # event first
360 if not include_event_check(properties, inst_properties):
361 continue
362
363 events_count += 1
364
365 # if we are counting, just bump the counter, else yield event
366 if not count:
367 # build event
368 if from_to is None or (page_start <= events_count <= page_stop):
369 events.append(Event(event_classname=event_classname,
370 start_time=start_time,
371 stop_time=stop_time,
372 event_id=event_id,
373 instance_properties_elem=parse_xml(inst_properties),
374 sid=sid))
375
376 return events_count, events
377
378
379def find_events(start_time: datetime = None,
380 start_time_eq: datetime = None,
381 stop_time: datetime = None,
382 event_class: Union[str, List[str]] = None,
383 ordering: str = 'start_time',
384 reverse_ordering: str = None,
385 properties: List = None,
386 from_to: tuple = None, # (start, stop)
387 count: bool = False,
388 min_duration: timedelta = None,
389 max_duration: timedelta = None,
390 start_time_lt: datetime = None,
391 sid: SID = None,
392 single: bool = False,
393 filtering: EventTimeFilter = EventTimeFilter.SENSING_TIME,
394 multitable_extra_params: Any = None,
395 event_id: int = None,
396 user: User = None):
397 """Search EVENTS table and yield matching Event objects.
398
399 If start_time and stop_time are given we look for any event either:
400 - with no stop time, and a start time at least `start_time` but less than `stop_time` or
401 - where stop time is at least `start_time` and start time less than `stop_time`.
402
403 If the client wants to scan specifically and only for events where the start time
404 is within a time range, specify `start_time` and `start_time_lt`.
405
406 Args:
407 `start_time` (datetime): Start of time range to search
408 `start_time_eq` (datetime): Search for events with exact start time
409 `stop_time` (datetime): End of time range to search
410 `start_time_lt` (datetime): End of start time window
411 `sid` (SID): Source-ID to search for
412 `event_class` (str|list of str): Event class name(s) to search for
413 `gen_time` (datetime): Gen_time value to search for
414 `ordering` (str): Field to sort by
415 `from_to` (tuple): Return paginated results, from (first_row_number, last_row_number)
416 `count` (bool): just return the number of events found
417 `properties` (list of tuples): Filter results by property match. List elements contain
418 clauses in the format (name, value, op) where:
419 `name` (str): Property name
420 `value` (object): Parameter
421 `op` (functions): binary functions from operator module
422 `single` (bool): just return the first event which matches input requirements
423 `filtering` (EventOrdering): Specify order and which timestamp to filter on for retrieved Events
424 `event_id` (int): Numerical event ID if reading from the EVENTS table
425 'user': This is used to block access to TM parameters.
426
427 Yields:
428 If `single` is set, yield one Event object.
429 If `count` is set, yield a single integer giving the number of events
430 Otherwise, yield a series of Event objects in with the ones from the EVENTS table
431 first then the others
432 Finally, if `from_to` is set, the final value yielded is a cont of total events
433 """
434 # Normalise `ordering` to a list of predicates,
435 # which could be an empty list, and each item could be a string or a callable
436 if ordering is None:
437 ordering = []
438
439 elif isinstance(ordering, str):
440 ordering = [ordering]
441
442 # logger.debug('find_events uid {id} filtering {fil} start {strt} start_eq {strteq}'.format(
443 # id=event_id, fil=filtering, strt=start_time, strteq=start_time_eq))
444 # Allow clients to specify a single event class or a list
445 # (plot viewer events overlay does this, maybe others)
446 if not is_listlike(event_class):
447 if event_class is None:
448 event_classes = []
449
450 else:
451 event_classes = [event_class]
452
453 else:
454 event_classes = event_class
455
456 # test for event class filter clauses
457 # if len(event_classes) == 0:
458 # the user has specified empty list of classes to search for ...
459 # if count:
460 # ... so return zero if counting ...
461 # yield 0
462 # return
463
464 # ... and yield no results otherwise
465 # return
466
467 ts_count = 0
468 ts_events = []
469 processed = []
470
471 # convert any string items in event_classes to EventClass objects
472 # (future tidyup - convert all the client code to pass in EventClass objects)
473 event_classes = [ec if isinstance(ec, EventClass) else EventClass(ec) for ec in event_classes]
474
475 # process non-standard storage classes
476 for event_class in event_classes:
477 if event_class.db_table is not None:
478 # Check for custom handler module
479 handler_module = event_class.decoder
480
481 # Check for default handler modules
482 if handler_module is None:
483 for mod in HANDLER_MODULES:
484 if event_class.db_table == mod.DECODER_ID:
485 handler_module = mod
486
487 if handler_module is None:
488 raise ValueError("No handler module was found for event class " + event_class.name)
489
490 if single:
491 # check for the first Event in the list of selected event types
492 classnames = [ec if isinstance(ec, EventClass) else
493 EventClass(ec) for ec in event_classes]
494 else:
495 classnames = [event_class]
496
497 t_count, t_events = handler_module.find_events(
498 sid=sid,
499 start_time=start_time,
500 start_time_eq=start_time_eq,
501 stop_time=stop_time,
502 event_classes=classnames,
503 ordering=ordering,
504 from_to=from_to,
505 properties=properties,
506 count=count,
507 single=single,
508 filtering=filtering,
509 multitable_extra_params=multitable_extra_params,
510 user=user)
511
512 ts_count += t_count
513 ts_events += t_events
514
515 processed.append(event_class)
516
517 if single:
518 if len(ts_events) > 0:
519 yield ts_events[0]
520 return
521
522 # We know we have to look in the original EVENTS table is either, the original request included
523 # non-multitable event classes, or if event_id was set (meaning search for a specific known event)
524 if len(event_classes) > len(processed) or event_id is not None:
525 # TBD simpler to remove items from event_classes as they are used, above.
526 # And avoid this loop
527 event_table_classes = []
528 for e in event_classes:
529 if e not in processed:
530 event_table_classes.append(e)
531
532 # now handle events from EVENTS table
533 ev_count, ev_events = find_events_events_table(event_id=event_id,
534 start_time=start_time,
535 start_time_lt=start_time_lt,
536 stop_time=stop_time,
537 min_duration=min_duration,
538 max_duration=max_duration,
539 event_classes=event_table_classes,
540 sid=sid,
541 properties=properties,
542 ordering=ordering,
543 from_to=from_to,
544 count=count)
545
546 else:
547 ev_count = 0
548 ev_events = []
549
550 # TBD fix return values... either count or generator?
551
552 # now yield results
553 if count:
554 # yield total event count
555 yield ts_count + ev_count
556
557 else:
558 # Return all events whether from EVENTS table or custom tables
559 # The client code must sort them
560 yield from ts_events
561 yield from ev_events
562
563 # TBD do we need this weird return value
564 # Maybe something like "return chain(ts_events, ev_events), ts_count + ev_count"
565 # would be saner
566 if from_to is not None:
567 # finally, yield total event count as this is not the same as 'page full' of
568 # events returned
569 yield ts_count + ev_count
570
571
572def find_single_event(start_time=None,
573 start_time_eq=None,
574 stop_time=None,
575 event_class=None,
576 # gen_time=None, # (unused arg) pylint: disable=W0613
577 ordering='start_time',
578 reverse_ordering=None,
579 properties=None,
580 from_to=None,
581 sid=None,
582 multitable_extra_params=None,
583 event_id=None,
584 user: User=None):
585 """Return the first matching event, or None if none are found."""
586 try:
587 # Remember find_events currently returns tuples of events list, events count
588 # (except when it doesn't)
589 return next(find_events(start_time=start_time,
590 start_time_eq=start_time_eq,
591 stop_time=stop_time,
592 event_class=event_class,
593 # gen_time,
594 ordering=ordering,
595 reverse_ordering=reverse_ordering,
596 properties=properties,
597 from_to=from_to,
598 sid=sid,
599 single=True,
600 multitable_extra_params=multitable_extra_params,
601 event_id=event_id,
602 user=user))
603
604 except StopIteration:
605 return None
606
607
608def count_events(sid=None,
609 start_time=None,
610 stop_time=None,
611 event_class=None,
612 gen_time=None, # (unused arg) pylint: disable=W0613
613 properties=None,
614 from_to=None,
615 filtering=EventTimeFilter.SENSING_TIME):
616 """Return the number of matching Events using find_events function.
617
618 Args:
619 `start_time` (datetime): Start of time range to search
620 `stop_time` (datetime): End of time range to search
621 `sid` (SID): Source-ID
622 `event_class` (str|list of str): Event class name(s) to search for
623 `gen_time` (str): Gen_time value to search for
624 `from_to` (touple): (page_start, page_stop)
625 `count`: (bool): just return the number of events found
626 `properties`: (list): list of property triplets (name, value, op)
627
628 Returns:
629 int: Number of events
630
631 """
632 res = next(find_events(sid=sid,
633 start_time=start_time,
634 stop_time=stop_time,
635 event_class=event_class,
636 properties=properties,
637 from_to=from_to,
638 count=True,
639 filtering=filtering))
640 # logger.info('Count got {cc}'.format(cc=res))
641 return res
642
643
644def delete_event(event_id, commit=True):
645 """Delete an event and properties."""
646 # logger.info('Deleting existing event id {0}'.format(event_id))
647 db_conn.query('DELETE FROM events WHERE id=:id', id=event_id)
648 if commit:
649 db_conn.commit()
650
651
652def delete_events(sid,
653 event_class,
654 start_time_ge=None,
655 start_time_lt=None,
656 start_time_le=None,
657 commit=False):
658 """Delete all events matching args."""
659 clauses = []
660 bindvars = {}
661 if sid is not None:
662 clauses.append(SID.sql_sys_where_bind('EVENTS'))
663 bindvars.update(sid.bind_sys_where('EVENTS'))
664
665 if start_time_ge is not None:
666 clauses.append('START_TIME>=:start_time_ge')
667 bindvars['start_time_ge'] = start_time_ge
668
669 if start_time_lt is not None:
670 clauses.append('START_TIME<:start_time_lt')
671 bindvars['start_time_lt'] = start_time_lt
672
673 if start_time_le is not None:
674 clauses.append('START_TIME<=:start_time_le')
675 bindvars['start_time_le'] = start_time_le
676
677 clauses.append('EVENT_CLASS IN ({classes})'.format(
678 classes=','.join('\'{cls}\''.format(cls=cls) for cls in event_class)))
679
680 cur = db_conn.query('DELETE FROM EVENTS WHERE {clauses}'.format(clauses=' AND '.join(clauses)),
681 **bindvars)
682
683 if commit:
684 db_conn.commit()
685
686 return cur.rowcount