Mercurial > hg > ltpdarepo
changeset 106:0d259fd52ca0
Implement validation of advanced query parameters.
While at it also rework the query infrastructure, I'm almost happy
with it now. Named queries are broken by this change and will be fixed
soon. Add dependency to `dateutil` package used for datetimes parsing.
| author | Daniele Nicolodi <daniele@grinta.net> |
|---|---|
| date | Tue, 23 Aug 2011 14:38:17 +0200 |
| parents | f4cbab388ed0 |
| children | 22b950fee548 |
| files | buildout.cfg src/ltpdarepo/static/querywidget.js src/ltpdarepo/static/style.css src/ltpdarepo/templates/query.html src/ltpdarepo/templates/timerange.html src/ltpdarepo/tests/test_objs.py src/ltpdarepo/views/browse.py |
| diffstat | 7 files changed, 372 insertions(+), 228 deletions(-) [+] |
line wrap: on
line diff
--- a/buildout.cfg Tue Aug 23 14:34:44 2011 +0200 +++ b/buildout.cfg Tue Aug 23 14:38:17 2011 +0200 @@ -1,4 +1,5 @@ [buildout] +include-site-packages = false parts = flask develop = src @@ -10,6 +11,7 @@ ordereddict argparse itsdangerous + python-dateutil < 2.0 ltpdarepo interpreter = python scripts = run admin
--- a/src/ltpdarepo/static/querywidget.js Tue Aug 23 14:34:44 2011 +0200 +++ b/src/ltpdarepo/static/querywidget.js Tue Aug 23 14:38:17 2011 +0200 @@ -16,7 +16,7 @@ var id = val; var name = val; if (typeof(val.name) !== 'undefined') { - id = val.val; + id = val.name; name = val.name; } // create options @@ -37,7 +37,7 @@ // create field name $.querywidget.createQueryField = function (index) { - var value = $.querywidget.config.indexes[index].val; + var value = $.querywidget.config.indexes[index].value; return $(document.createElement('input')).attr({ 'type': 'hidden', 'name': 'field', @@ -88,6 +88,10 @@ // configuration $.querywidget.config.indexes = criteria; + $("input.error").live('focus', function () { + $(this).removeClass('error'); + }); + $('#add').live('change', function () { var index = $(this).find(':selected')[0].value; if (! index)
--- a/src/ltpdarepo/static/style.css Tue Aug 23 14:34:44 2011 +0200 +++ b/src/ltpdarepo/static/style.css Tue Aug 23 14:38:17 2011 +0200 @@ -558,21 +558,21 @@ background: #CFF09E; } -/* timespan selector */ +/* timerange selector */ -input.timespan-lo { +input.timerange-t1 { width: 197px; padding: 5px 10px; margin: 0; } -input.timespan-hi { +input.timerange-t2 { width: 197px; padding: 5px 10px; margin: 0; } -.timespan-label { +#timerange span { padding: 5px 10px; width: 60px; border-right: none; @@ -580,7 +580,6 @@ text-align: center; } - /* object display */ .obj {
--- a/src/ltpdarepo/templates/query.html Tue Aug 23 14:34:44 2011 +0200 +++ b/src/ltpdarepo/templates/query.html Tue Aug 23 14:38:17 2011 +0200 @@ -6,7 +6,7 @@ <script type="text/javascript" src="{{ url_for('static', filename='dropdown.js') }}"></script> <script type="text/javascript" src="{{ url_for('static', filename='querywidget.js') }}"></script> <script type="text/javascript"> - var criteria = {{ criteria|tojson|safe }}; + var criteria = {{ indexes|tojson|safe }}; $(function () { $.dropdown.replace($("select")); $("#save").live('click', function () { @@ -36,12 +36,14 @@ <form method="GET" action=""> {% block extra %}{% endblock %} <div id="criteria" class="wrapper"> - {% for field, op, value in query %} + {% for field, op, value, error in query %} + {% set cssclass = '' %} + {% if error %}{% set cssclass = cssclass + 'error' %}{% endif %} <div class="criteria wrapper"> - <span class="querywidget field">{{ criteria[field]['name'] }}</span> + <span class="querywidget field">{{ indexes[field]['name'] }}</span> <input type="hidden" name="field" value="{{ field }}" /> <select class="querywidget operator" name="operator"> - {% for name in criteria[field]['operators'] %} + {% for name in indexes[field]['operators'] %} {% if op == name %} <option value="{{ name }}" selected="selected">{{ name }}</option> {% else %} @@ -49,9 +51,9 @@ {% endif %} {% endfor %} </select> - {% if criteria[field]['type'] == 'enum' %} + {% if indexes[field]['type'] == 'enum' %} <select class="querywidget value" name="value"> - {% for name in criteria[field]['values'] %} + {% for name in indexes[field]['values'] %} {% if value == name %} <option value="{{ name }}" selected="selected">{{ name }}</option> {% else %} @@ -60,7 +62,7 @@ {% endfor %} </select> {% else %} - <input class="querywidget value" autocomplete="off" type="text" name="value" value="{{ value }}" /> + <input class="querywidget value {{ cssclass }}" autocomplete="off" type="text" name="value" value="{{ value }}" /> {% endif %} <input class="querywidget remove" type="button" name="remove" value="×" /> </div> @@ -69,8 +71,8 @@ <select id="add"> <option value="" selected="selected">Add criteria…</option> - {% for c in criteria.values() %} - <option value="{{ c['val'] }}">{{ c['name'] }}</option> + {% for key, index in indexes.iteritems() %} + <option value="{{ key }}">{{ index['name'] }}</option> {% endfor %} </select>
--- a/src/ltpdarepo/templates/timerange.html Tue Aug 23 14:34:44 2011 +0200 +++ b/src/ltpdarepo/templates/timerange.html Tue Aug 23 14:38:17 2011 +0200 @@ -1,10 +1,18 @@ {% extends "query.html" %} {% block extra %} - <div id="timespan" class="wrapper"> + <div id="timerange" class="wrapper"> <div class="criteria wrapper"> - <input class="timespan-lo" type="text" name="from" value="{{ lo|default('', True) }}" autocomplete="off" placeholder="FROM" /> - <span class="timespan-label">≤ TIME ≤</span> - <input class="timespan-hi" type="text" name="to" value="{{ hi|default('', True) }}" autocomplete="off" placeholder="TO" /> + {% if timerange.t1.errors -%} + {{ timerange.t1(class_='timerange-t1 error', autocomplete='off', placeholder='FROM') }} + {% else -%} + {{ timerange.t1(class_='timerange-t1', autocomplete='off', placeholder='FROM') }} + {%- endif %} + <span>≤ TIME ≤</span> + {% if timerange.t2.errors -%} + {{ timerange.t2(class_='timerange-t2 error', autocomplete='off', placeholder='TO') }} + {% else -%} + {{ timerange.t2(class_='timerange-t2', autocomplete='off', placeholder='TO') }} + {%- endif %} </div> </div> {% endblock %}
--- a/src/ltpdarepo/tests/test_objs.py Tue Aug 23 14:34:44 2011 +0200 +++ b/src/ltpdarepo/tests/test_objs.py Tue Aug 23 14:38:17 2011 +0200 @@ -1,15 +1,14 @@ -import unittest2 as unittest - from ltpdarepo.tests.utils import RequestContextTestCase from ltpdarepo.views.browse import Objs + class TestCase(RequestContextTestCase): @classmethod def setUpClass(self): from ltpdarepo.admin import wipe, setup wipe() - setup() + setup() def test_simple(self): objs = Objs(database='db1') @@ -22,7 +21,7 @@ objs = Objs(database='db1').limit(20, 2) self.assertEqual(objs.all()[0]['id'], 21) self.assertEqual(len(objs.all()), 2) - + def test_orderby(self): objs = Objs(database='db1').orderby('obj_id') self.assertEqual(objs[0]['id'], 1) @@ -31,6 +30,5 @@ self.assertEqual(objs[0]['id'], 30) def test_where(self): - objs = Objs(database='db1').filter('obj_id > %s AND obj_id < %s', 10, 20) + objs = Objs(database='db1').filter('obj_id > %s AND obj_id < %s', (10, 20)) self.assertEqual(objs[0]['id'], 11) -
--- a/src/ltpdarepo/views/browse.py Tue Aug 23 14:34:44 2011 +0200 +++ b/src/ltpdarepo/views/browse.py Tue Aug 23 14:38:17 2011 +0200 @@ -1,8 +1,15 @@ import re -from operator import itemgetter +from datetime import datetime + +import dateutil.tz +import dateutil.parser from flask import Blueprint, abort, g, request, render_template, json, make_response from MySQLdb.cursors import DictCursor +from wtforms import Form +from wtforms.fields import Field +from wtforms.widgets import TextInput +from wtforms.validators import ValidationError, Optional from ltpdarepo.security import require, view from ltpdarepo.database import Database @@ -15,61 +22,32 @@ from ordereddict import OrderedDict -PAGESIZE = 20 - -FIELDS = ('id', - 'name', - 'type', - 'quantity', - 'keywords', - 'submitted', - 'title', - 'description',) -EXTRA = ( 'analysis', - 'author', - 'additional authors', - 'comments', - 'reference ids', - 'created', ) - +FIELDS = ('id', 'name', 'type', 'quantity', 'keywords', 'submitted', 'title', 'description',) +EXTRA = ('analysis', 'author', 'additional_authors', 'comments', 'reference_ids', 'created', ) +MORE = ('version', 'ip', 'hostname', 'os', 'validated', 'vdate') +TIMESERIESFIELDS = ('id', 'name', 't0', 'nsecs', 'quantity', 'keywords', 'title', 'description') ALLFIELDS = FIELDS + EXTRA -# 'version', 'ip', 'hostname', 'os', 'validated', 'vdate' - -RMAPPING = { 'id': 'objmeta.obj_id', - 'name': 'objmeta.name', - 'type': 'objmeta.obj_type', - 'quantity': 'objmeta.quantity', - 'keywords': 'objmeta.keywords', - 'submitted': 'objmeta.submitted', - 'title': 'objmeta.experiment_title', - 'description': 'objmeta.experiment_desc', - 'analysis': 'objmeta.analysis_desc', - 'author': 'objmeta.author', - 'additional authors': 'objmeta.additional_authors', - 'comments': 'objmeta.additional_comments', - 'reference ids': 'objmeta.reference_ids', - 'created': 'objmeta.created', - 't0': 'tsdata.t0', - 'nsecs': 'tsdata.nsecs', } - -MAPPING = dict(((v.split('.')[1], k) for k, v in RMAPPING.iteritems())) - - -def _current_page(): - """return the current page in a paginated view""" - return int(request.args.get('p', 1)) - - -def _current_ordering(): - """return the current page in a paginated view""" - order = request.args.get('o', 'id') - order = RMAPPING[order] - reverse = int(request.args.get('r', 0)) - return (order, reverse) +PAGESIZE = 20 class Objs(object): + columns = OrderedDict([ + ('id', 'objmeta.obj_id'), + ('name', 'objmeta.name'), + ('type', 'objmeta.obj_type'), + ('quantity', 'objmeta.quantity'), + ('keywords', 'objmeta.keywords'), + ('submitted', 'objmeta.submitted'), + ('title', 'objmeta.experiment_title'), + ('description', 'objmeta.experiment_desc'), + ('analysis', 'objmeta.analysis_desc'), + ('author', 'objmeta.author'), + ('additional_authors', 'objmeta.additional_authors'), + ('comments', 'objmeta.additional_comments'), + ('reference_ids', 'objmeta.reference_ids'), + ('created', 'objmeta.created')]) + def __init__(self, database): self._database = database self._orderby = None @@ -80,7 +58,7 @@ @property def _query(self): - columns = ", ".join("%s AS `%s`" % (RMAPPING[x], x) for x in FIELDS) + columns = ", ".join("%s AS `%s`" % (col, name) for name, col in self.columns.iteritems()) query = "SELECT " + columns + " FROM `%s`.objmeta" % self._database if self._where: query += " WHERE %s" % self._where @@ -101,7 +79,7 @@ query += " LIMIT %d,%d" % self._limit return query - def filter(self, where, *values): + def filter(self, where, values): self._where = where self._values = values return self @@ -116,7 +94,7 @@ return self.limit(0, start) self._limit = (start, count) return self - + def all(self): curs = g.db.cursor(DictCursor) curs.execute(self._query, self._values) @@ -137,6 +115,250 @@ return objs[0] +class Timeseries(Objs): + columns = Objs.columns.copy() + columns.update([ + ('t0', 'tsdata.t0'), + ('nsecs', 'tsdata.nsecs')]) + + def __init__(self, *args, **kwargs): + super(Timeseries, self).__init__(*args, **kwargs) + self._start = None + self._end = None + + def timespan(self, start=None, end=None): + self._start = start + self._end = end + return self + + @property + def _query(self): + columns = ", ".join("%s AS `%s`" % (col, name) for name, col in self.columns.iteritems()) + query = "SELECT " + columns + " FROM `%s`.objmeta, `%s`.tsdata" % (self._database, self._database) + query += " WHERE objmeta.obj_id=tsdata.obj_id" + if self._end: + query += " AND tsdata.t0 <= '%s'" % self._end + if self._start: + query += " AND tsdata.t0 + INTERVAL tsdata.nsecs SECOND >= '%s'" % self._start + if self._where: + query += " AND %s" % self._where + if self._orderby: + query += " ORDER BY %s" % self._orderby + if self._reverse: + query += " DESC" + if self._limit: + query += " LIMIT %d,%d" % self._limit + return query + + @property + def _count(self): + query = "SELECT COUNT(*) FROM `%s`.objmeta, `%s`.tsdata" % (self._database, self._database) + query += " WHERE objmeta.obj_id=tsdata.obj_id" + if self._end: + query += " AND tsdata.t0 <= '%s'" % self._end + if self._start: + query += " AND tsdata.t0 + INTERVAL tsdata.nsecs SECOND >= '%s'" % self._start + if self._where: + query += " AND %s" % self._where + if self._limit: + query += " LIMIT %d,%d" % self._limit + return query + + +class Index(dict): + def __init__(self, **kwargs): + self.__dict__ = self + self.update(**kwargs) + + +class Indexes(OrderedDict): + + operators = {'int': ('=', '>', '<'), + 'tinyint': ('=', '>', '<'), + 'double': ('=', '>', '<'), + 'text': ('LIKE', '='), + 'enum': ('=',), + 'datetime': ('=', '>', '<')} + + def __init__(self, database, columns): + super(Indexes, self).__init__() + indexes = [] + + # map column name to index name + mapping = dict(((v.split('.')[1], k) for k, v in columns.iteritems())) + # map index name to ordering position + order = dict((v, i) for i, v in enumerate(columns.keys())) + + curs = g.db.cursor() + curs.execute("""DESCRIBE `%s`.`%s`""" % (database, 'objmeta')) + + for column, kind, values in [self.parse(x) for x in curs.fetchall()]: + name = mapping.get(column) + if name: + indexes.append(Index(name=name.replace('_', ' '), + value=name, + column='.'.join(('objmeta', column)), + type=kind, + operators=Indexes.operators[kind], + values=values)) + + for index in sorted(indexes, key=lambda v: order.get(v.name, 100)): + self[index.name] = index + + def parse(self, desc): + column = desc[0] + # extract column type + kind = re.match(r'(\w+)', desc[1]).group(1) + values = None + if kind == 'enum': + # extract enum values + values = re.match(r'(\w+)\((.*)\)', desc[1]).group(2).split(',') + values = [v.strip("'") for v in values] + return column, kind, values + + def column(self, name): + return self[name].column + + +class datetimefield(datetime): + """Timezone aware subclass of `datetime.datetime` with a new + constructor that parses strings and a default string + representation including the timezone name.""" + + def __new__(cls, string): + # parsing default is midnight today in UTC timezone + default = datetime.utcnow().replace( + tzinfo=dateutil.tz.tzutc(), hour=0, minute=0, second=0, microsecond=0) + + value = dateutil.parser.parse(string, dayfirst=True, yearfirst=True, default=default) + return datetime.__new__(cls, value.year, value.month, value.day, value.hour, + value.minute, value.second, value.microsecond, value.tzinfo) + + def __str__(self): + return self.strftime('%Y-%m-%d %H:%M:%S %Z') + + +class Request(object): + """Retrieves and validates query parameters from the request""" + + validators = {'int': int, + 'tinyint': int, + 'double': float, + 'text': unicode, + 'enum': unicode, + 'datetime': datetimefield} + + def __init__(self, request, columns, indexes={}): + # incoming data + self.formdata = request.args + # column names mapping used to validate orderby parameter + self.columns = columns + # indexes description used to validate query parameters + self.indexes = indexes + # process incoming data + self.process(self.formdata) + + def process(self, formdata): + fields = formdata.getlist('field') + ops = formdata.getlist('operator') + values = formdata.getlist('value') + self.where, self.vals, self.criteria = self.parsequery(fields, ops, values) + + def parsequery(self, fields, ops, values): + + query, vals, parsed = [], [], [] + + for field, op, value in zip(fields, ops, values): + # index description + index = self.indexes[field] + validate = self.validators[index['type']] + # validate operator + if op not in index['operators']: op = '=' + # validate value + error = False + try: + value = validate(value) + # add to query parameters + vals.append(value) + query.append('%s %s %%s' % (index.column, op)) + except ValueError: + error = True + finally: + # append to parse results + parsed.append((field, op, value, error)) + + return ' AND '.join(query), vals, parsed + + @property + def order(self): + """Current ordering in an ordered view""" + # validation by dict lookup + order = self.formdata.get('o', 'id') + order = self.columns[order] + # validation by conversion to int + reverse = self.formdata.get('r', 0, type=int) + return order, reverse + + @property + def page(self): + """Current page in a paginated view""" + # validation by conversion to int + return self.formdata.get('p', 1, type=int) + + @property + def query(self): + return self.where, self.vals + + +class DateTimeField(Field): + """Text input and that uses `dateutil.parser.parse` to parse the + entered value into a `datetime.datetime` object. This differs from + `wtforms.ext.dateutil.field.DateTimeField` in parameters used for + the datetime parsing and in the display format of the field value.""" + + widget = TextInput() + + def __init__(self, label=None, validators=None, **kwargs): + super(DateTimeField, self).__init__(label, validators, **kwargs) + self.parseargs = dict(dayfirst=True, yearfirst=True) + + def _value(self): + if self.data: + return self.data.strftime('%Y-%m-%d %H:%M:%S %Z') + return self.raw_data and u' '.join(self.raw_data) or u'' + + def process_formdata(self, valuelist): + if valuelist: + datestr = u' '.join(valuelist) + if not datestr: + self.data = None + raise ValidationError(self.gettext(u'Input a datetime value')) + + # parsing default is midnight today in UTC timezone + default = datetime.utcnow().replace( + tzinfo=dateutil.tz.tzutc(), hour=0, minute=0, second=0, microsecond=0) + try: + self.data = dateutil.parser.parse(datestr, default=default, **self.parseargs) + except ValueError: + self.data = None + raise ValidationError(self.gettext(u'Invalid datetime input')) + + +class Timerange(Form): + """Form used to specify time ranges in timeseries search query""" + + t1 = DateTimeField(validators=[Optional()]) + t2 = DateTimeField(validators=[Optional()]) + + def __init__(self, *args, **kwargs): + super(Timerange, self).__init__(*args, **kwargs) + self.validate() + + @property + def range(self): + return self.t1.data, self.t2.data + + app = Blueprint('browse', __name__) @@ -144,7 +366,7 @@ @require('user') def database(database): with view('database', database): - db = Database().load(database) + db = Database.load(database) if db is None: # not found abort(404) @@ -158,38 +380,42 @@ @require('user') def browse(database): with view('database', database): - db = Database().load(database) + db = Database.load(database) if db is None: # not found abort(404) - count = Objs(database=database).count() - batch = Pagination(_current_page(), size=PAGESIZE, count=count) - objs = Objs(database=database).orderby(*_current_ordering()).limit(*batch.limits).all() + # parse request + r = Request(request, Objs.columns) - return render_template('browse.html', objs=objs, fields=FIELDS, database=db, batch=batch) + count = Objs(database).count() + batch = Pagination(r.page, size=PAGESIZE, count=count) + objs = Objs(database).orderby(*r.order).limit(*batch.limits).all() + + return render_template('browse.html', objs=objs, batch=batch, + fields=FIELDS, database=db) @app.route('/<database>/<int:objid>') @require('user') def obj(database, objid): with view('database', database): - db = Database().load(database) + db = Database.load(database) if db is None: # not found abort(404) - obj = Objs(database=database).filter('obj_id=%s', objid)[0] + obj = Objs(database).filter('obj_id=%s', objid)[0] if obj is None: # not found abort(404) - return render_template('obj.html', obj=obj, database=db, fields=ALLFIELDS) + return render_template('obj.html', obj=obj, database=db, fields=FIELDS+EXTRA+MORE) @app.route('/<database>/<int:objid>/<frmt>') @require('user') def download(database, objid, frmt): with view('database', database): - db = Database().load(database) + db = Database.load(database) if db is None: # not found abort(404) @@ -225,93 +451,54 @@ @require('user') def search(database): with view('database', database): - db = Database().load(database) + db = Database.load(database) if db is None: # not found abort(404) - # search criteria - q = request.args.get('q', '') - q = '%%%s%%' % q - - # query - count = Objs(database=database).filter('name LIKE %s', q).count() - batch = Pagination(_current_page(), size=PAGESIZE, count=count) - objs = Objs(database=database).filter('name LIKE %s', q).orderby(*_current_ordering()).limit(*batch.limits).all() - - return render_template('browse.html', objs=objs, fields=FIELDS, database=db, batch=batch) - - -def _column_desc(desc): - val = desc[0] - kind = re.match(r'(\w+)', desc[1]).group(1) - values = None - if kind == 'enum': - values = re.match(r'(\w+)\((.*)\)', desc[1]).group(2).split(',') - values = [v.strip("'") for v in values] + # parse request + r = Request(request, Objs.columns) + q = '%' + request.args.get('q', '') + '%' - name = MAPPING.get(val) - if name is None: - return None - - ops = {'int': ('=', '>', '<'), - 'tinyint': ('=', '>', '<'), - 'text': ('LIKE', '='), - 'enum': ('=',), - 'datetime': ('=', '>', '<')}.get(kind) - - order = dict((v, i) for i, v in enumerate(ALLFIELDS)).get(name, 100) + # collect objects + count = Objs(database).filter('name LIKE %s', q).count() + batch = Pagination(r.page, size=PAGESIZE, count=count) + objs = Objs(database).filter('name LIKE %s', q).orderby(*r.order).limit(*batch.limits).all() - return {'val': val, - 'name': name, - 'type': kind, - 'operators': ops, - 'values': values, - 'order': order} - - -def _indexes(database, table): - curs = g.db.cursor() - curs.execute("""DESCRIBE `%s`.`%s`""" % (database, table)) - indexes = filter(None, [_column_desc(desc) for desc in curs.fetchall()]) - return OrderedDict((x['val'], x) for x in sorted(indexes, key=itemgetter('order'))) + return render_template('browse.html', objs=objs, batch=batch, + fields=FIELDS, database=db) @app.route('/<database>/query') @require('user') def query(database): with view('database', database): - db = Database().load(database) + db = Database.load(database) if db is None: # not found abort(404) - fields = request.args.getlist('field') - ops = request.args.getlist('operator') - values = request.args.getlist('value') - query = zip(fields, ops, values) + # applicable search criteria + indexes = Indexes(database, Objs.columns) - # build query string - q = ['`%s` %s %%s' % v for v in zip(fields, ops)] - where = ' AND '.join(q) + # parse request + r = Request(request, Objs.columns, indexes) - # get objects - count = Objs(database=database).filter(where, *values).count() - batch = Pagination(_current_page(), size=PAGESIZE, count=count) - objs = Objs(database=database).filter(where, *values).orderby(*_current_ordering()).limit(*batch.limits).all() + # collect objects + count = Objs(database).filter(*r.query).count() + batch = Pagination(r.page, size=PAGESIZE, count=count) + objs = Objs(database).filter(*r.query).orderby(*r.order).limit(*batch.limits).all() - # collect search critaeria - criteria = _indexes(database, 'objmeta') - - return render_template('query.html', query=query, objs=objs, - criteria=criteria, database=db, fields=FIELDS, batch=batch) + return render_template('query.html', objs=objs, batch=batch, + fields=FIELDS, database=db, + indexes=indexes, query=r.criteria) @app.route('/<database>/query/<int:id>') @require('user') def namedquery(database, id): with view('database', database): - db = Database().load(database) + db = Database.load(database) if db is None: # not found abort(404) @@ -332,95 +519,39 @@ querystring = ' AND '.join("%s %s %s" % tuple(v) for v in params) # get objects - count = Objs(database=database).filter(where, *values).count() - batch = Pagination(_current_page(), size=PAGESIZE, count=count) - objs = Objs(database=database).filter(where, *values).orderby(*_current_ordering()).limit(*batch.limits).all() - - return render_template('namedquery.html', objs=objs, database=db, fields=FIELDS, batch=batch, - query=query, querystring=querystring) - - -class Timeseries(Objs): - def __init__(self, database): - super(Timeseries, self).__init__(database) - self._start = None - self._end = None - - def timespan(self, start=None, end=None): - self._start = start - self._end = end - return self + r = Request() + count = Objs(database).filter(where, *values).count() + batch = Pagination(r.page, size=PAGESIZE, count=count) + objs = Objs(database).filter(where, *values).orderby(*r.order).limit(*batch.limits).all() - @property - def _query(self): - columns = ", ".join("%s AS `%s`" % (RMAPPING[x], x) for x in FIELDS + ('t0', 'nsecs')) - query = "SELECT " + columns + " FROM `%s`.objmeta, `%s`.tsdata" % (self._database, self._database) - query += " WHERE objmeta.obj_id=tsdata.obj_id" - if self._end is not None: - query += " AND tsdata.t0 <= '%s'" % self._end - if self._start is not None: - query += " AND tsdata.t0 + INTERVAL tsdata.nsecs SECOND >= '%s'" % self._start - if self._where: - query += " AND %s" % self._where - if self._orderby: - query += " ORDER BY %s" % self._orderby - if self._reverse: - query += " DESC" - if self._limit: - query += " LIMIT %d,%d" % self._limit - return query - - @property - def _count(self): - query = "SELECT COUNT(*) FROM `%s`.objmeta, `%s`.tsdata" % (self._database, self._database) - query += " WHERE objmeta.obj_id=tsdata.obj_id" - if self._end is not None: - query += " AND tsdata.t0 <= '%s'" % self._end - if self._start is not None: - query += " AND tsdata.t0 + INTERVAL tsdata.nsecs SECOND >= '%s'" % self._start - if self._where: - query += " AND %s" % self._where - if self._limit: - query += " LIMIT %d,%d" % self._limit - return query + return render_template('namedquery.html', objs=objs, batch=batch, + fields=FIELDS, database=db, + query=query, querystring=querystring) @app.route('/<database>/timeseries') @require('user') def timeseries(database): - db = Database().load(database) + db = Database.load(database) if db is None: # not found abort(404) - fields = request.args.getlist('field') - ops = request.args.getlist('operator') - values = request.args.getlist('value') - query = zip(fields, ops, values) + # applicable search criteria + indexes = Indexes(database, Timeseries.columns) - # build query string - q = ['`%s` %s %%s' % v for v in zip(fields, ops)] - where = ' AND '.join(q) - - start = request.args.get('from', None) or None - end = request.args.get('to', None) or None + # parse reuest + r = Request(request, Timeseries.columns, indexes) + t = Timerange(request.args) - # get objects - count = Timeseries(database=database).filter(where, *values).timespan(start, end).count() - batch = Pagination(_current_page(), size=PAGESIZE, count=count) - objs = Timeseries(database=database).filter(where, *values).orderby(*_current_ordering()).timespan(start, end).limit(*batch.limits).all() - - # collect search critaeria - criteria = _indexes(database, 'objmeta') + # collect objects + count = Timeseries(database).filter(*r.query).timespan(*t.range).count() + batch = Pagination(r.page, size=PAGESIZE, count=count) + objs = Timeseries(database).filter(*r.query).timespan(*t.range).orderby(*r.order).limit(*batch.limits).all() - fields = list(FIELDS) - fields.pop(2) - fields.pop(4) - fields.insert(2, 't0') - fields.insert(3, 'nsecs') - - return render_template('timerange.html', query=query, objs=objs, lo=start, hi=end, - criteria=criteria, database=db, fields=fields, batch=batch) + return render_template('timerange.html', objs=objs, batch=batch, + fields=TIMESERIESFIELDS, database=db, + indexes=indexes, query=r.criteria, timerange=t) module = app