# HG changeset patch
# User Daniele Nicolodi <daniele@grinta.net>
# Date 1314103097 -7200
# Node ID 0d259fd52ca0f0823e53f5515b9480fef47fd867
# Parent  f4cbab388ed01dfcfd820a689e3d25173ee0f7ac
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.

diff -r f4cbab388ed0 -r 0d259fd52ca0 buildout.cfg
--- 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
diff -r f4cbab388ed0 -r 0d259fd52ca0 src/ltpdarepo/static/querywidget.js
--- 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)
diff -r f4cbab388ed0 -r 0d259fd52ca0 src/ltpdarepo/static/style.css
--- 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 {
diff -r f4cbab388ed0 -r 0d259fd52ca0 src/ltpdarepo/templates/query.html
--- 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="&times;" />
       </div>
@@ -69,8 +71,8 @@
 
     <select id="add">
       <option value="" selected="selected">Add criteria&hellip;</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>
 
diff -r f4cbab388ed0 -r 0d259fd52ca0 src/ltpdarepo/templates/timerange.html
--- 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">&#x2264; TIME &#x2264;</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>&#x2264; TIME &#x2264;</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 %}
diff -r f4cbab388ed0 -r 0d259fd52ca0 src/ltpdarepo/tests/test_objs.py
--- 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)
-        
diff -r f4cbab388ed0 -r 0d259fd52ca0 src/ltpdarepo/views/browse.py
--- 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