1#!/usr/bin/env python3
2
3"""AuxList widget."""
4
5import re
6from collections import OrderedDict
7
8from chart.common.prettyprint import Table
9from chart.reports.widget import Widget
10from chart.db.aux import mysql_query
11from chart.common.exceptions import ConfigError
12from chart.project import settings
13from chart.reports.widget import WidgetOptionChoice
14
15
16class AuxList(Widget):
17 """Populates an HTML table from an SQL query on an aux database."""
18
19 name = 'aux-list'
20
21 image = 'widgets/eventslist.png'
22 thumbnail = 'widgets/eventslist_sm.png'
23
24 url = 'http://tctrac/projects/chart/wiki/AuxListWidget'
25
26 options = OrderedDict([
27 ('source', {'type': str,
28 'description': 'Data source to read from',
29 'choices': [WidgetOptionChoice(name='tmrep',
30 description='Read from TMREP database'),
31 WidgetOptionChoice(name='tchist',
32 description='Read from TCHIST database'),
33 WidgetOptionChoice(name='events',
34 description='Read from EVENTS database'),
35 ]}),
36 ('heading', {'type': str,
37 'description': 'Specify column headings. Repeat element once per column',
38 'multiple': True}),
39 ('sql', {'type': str,
40 'description': ('Database query. Use :sensing_start and :sensing_stop special '
41 'variables for time filtering')}),
42 ])
43
44 document_options = OrderedDict([
45 ('sid', {'type': 'string'}),
46 ('sensing_start', {'type': 'datetime'}),
47 ('sensing_stop', {'type': 'datetime'})])
48
49 def html(self, document):
50 """Render ourselves."""
51 c = self.config
52 dc = document.config
53 html = document.html
54
55 source = '{source}_{sid}'.format(source=c['source'], sid=dc['sid']).lower()
56
57 # original full SQL statement
58 sql = c['sql']
59
60 # search for bind variables referenced in the SQL
61 var_finder = re.compile(r':(\w+)')
62
63 # prepare list of required bind variables for db engine
64 bindvars = []
65
66 # search the original SQL for :var
67 for var in var_finder.finditer(sql):
68 var = var.groups(0)[0]
69 # we only handle 2 variables
70 if var == 'sensing_start':
71 bindvars.append(dc['sensing_start'])
72
73 elif var == 'sensing_stop':
74 bindvars.append(dc['sensing_stop'])
75
76 else:
77 raise ConfigError('Unknown bind variable :{var}'.format(var=var))
78
79 # substitute :var for %s
80 sql = re.sub(r':([a-zA-Z0-9_]+)', r'%s', sql)
81
82 # prepare final table
83 title = 'List from ' + '{source}_{sid}'.format(source=c['source'], sid=dc['sid']).upper()
84 result = Table(title=title)
85
86 # we may need the cursor to access field descriptions
87 cur = mysql_query(source, sql, *bindvars)
88
89 # see if the user supplied fixed column headings ...
90 if 'heading' in c:
91 if len(c['heading']) != len(cur.description):
92 raise ConfigError(
93 'List widget defined {elem} headings but SQL returns {sql} columns'.format(
94 elem=len(c['heading']), sql=len(cur.description)))
95
96 # ... yes, use user values
97 result.append_headers(c['heading'])
98
99 else:
100 # ... no, read the field names from the cursor
101 result.append_headers(column[0] for column in cur.description)
102
103 # populate the final table
104 for row in cur:
105 result.append(row)
106
107 result.write_html(html)