# HG changeset patch
# User Daniele Nicolodi <daniele@grinta.net>
# Date 1313260044 -7200
# Node ID ea3867a3c634ce6e25a7f0757ca98c42ddaf119e
# Parent  cfda4a03b2aed1235e585b4973fa7fda4563cc21
Implement seearch of 'tsdata' objects by time spanned by contained data.

diff -r cfda4a03b2ae -r ea3867a3c634 src/ltpdarepo/templates/database.html
--- a/src/ltpdarepo/templates/database.html	Sat Aug 13 20:27:24 2011 +0200
+++ b/src/ltpdarepo/templates/database.html	Sat Aug 13 20:27:24 2011 +0200
@@ -7,6 +7,7 @@
   <li><a href="{{ url_for('browse.browse', database=database.id) }}">Browse</a></li>
   <li><a href="{{ url_for('browse.activity', database=database.id) }}">Show activity</a></li>
   <li><a href="{{ url_for('browse.query', database=database.id) }}">Advanced search</a></li>
+  <li><a href="{{ url_for('browse.timeseries', database=database.id) }}">Timeseries search</a></li>
 </ul>
 <h2>Search database &laquo;{{ database.id }}&raquo;</h2>
 <p class="discrete">Search objects by name</p>
diff -r cfda4a03b2ae -r ea3867a3c634 src/ltpdarepo/templates/timerange.html
--- /dev/null	Thu Jan 01 00:00:00 1970 +0000
+++ b/src/ltpdarepo/templates/timerange.html	Sat Aug 13 20:27:24 2011 +0200
@@ -0,0 +1,10 @@
+{% extends "query.html" %}
+{% block extra %}
+    <div id="criteria" class="wrapper">
+      <div class="criteria wrapper">
+        <input class="timespan-lo" type="text" name="from" value="{{ lo|default('', True) }}" autocomplete="off" placeholder="FROM"></input>
+        <span class="timespan-label">&#x2264; TIME &#x2264;</span>
+        <input class="timespan-hi" type="text" name="to" value="{{ hi|default('', True) }}" autocomplete="off" placeholder="TO"></input>
+      </div>
+    </div>
+{% endblock %}
diff -r cfda4a03b2ae -r ea3867a3c634 src/ltpdarepo/views/browse.py
--- a/src/ltpdarepo/views/browse.py	Sat Aug 13 20:27:24 2011 +0200
+++ b/src/ltpdarepo/views/browse.py	Sat Aug 13 20:27:24 2011 +0200
@@ -376,4 +376,89 @@
         return render_template('namedquery.html', objs=objs, database=db, fields=FIELDS, batch=batch, query=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
+
+    @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
+        print query
+        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
+        print query
+        return query
+
+
+@app.route('/<database>/timeseries/')
+@require('user')
+def timeseries(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)
+
+    # 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
+
+    # 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)[batch.slice]
+
+    # collect search critaeria
+    criteria = _indexes(database, 'objmeta')
+
+    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)
+
+
 module = app