# HG changeset patch
# User Daniele Nicolodi <daniele@grinta.net>
# Date 1320751337 -3600
# Node ID c52ae3f0196f55b4f4b0b34e0380d7a2a2801a7e
# Parent  90b18bfd737591e07fdd9e8487c58945ec5f76a2
Make named queries store ordering and work for timeseries searches.

diff -r 90b18bfd7375 -r c52ae3f0196f src/ltpdarepo/query.py
--- a/src/ltpdarepo/query.py	Tue Nov 08 12:19:12 2011 +0100
+++ b/src/ltpdarepo/query.py	Tue Nov 08 12:22:17 2011 +0100
@@ -1,6 +1,6 @@
 import re
 
-from flask import Markup, g, json
+from flask import g, json
 from MySQLdb.cursors import DictCursor
 from wtforms.fields import TextField, HiddenField
 from wtforms import validators
@@ -29,9 +29,32 @@
 
 
 class Query(dict):
-    def __init__(self, title=None, db=None, query=None):
-        super(Query, self).__init__(title=title, db=db, query=query)
-        self.__dict__ = self
+    __slots__ = ('id', 'title', 'db', '_query', '_order', '_times')
+
+    def __init__(self, query='', id=None, title=None, db=None):
+        self.id = id
+        self.title = title
+        self.db = db
+        self.query = query
+
+    def _getquery(self):
+        if self._times is not None:
+            return json.dumps({'query': self._query, 'order': self._order, 'times': self._times})
+        return json.dumps({'query': self._query, 'order': self._order})
+
+    def _setquery(self, string):
+        if not string:
+            return
+        obj = json.loads(string)
+        self._query = obj.get('query')
+        self._order = obj.get('order')
+        self._times = obj.get('times')
+
+    query = property(_getquery, _setquery)
+
+    @property
+    def kind(self):
+        return self._times is not None and 'timeseries' or 'query'
 
     @staticmethod
     def load(id):
@@ -41,22 +64,64 @@
         query = curs.fetchone()
         if query is None:
             return None
-        obj = Query()
-        obj.update(query)
+        obj = Query(**query)
         return obj
 
+    def parse(self, string):
+        # parse url params        
+        from urlparse import parse_qs
+        from werkzeug.datastructures import MultiDict
+
+        # parse string into dictionary
+        formdata = parse_qs(string, keep_blank_values=True, strict_parsing=True)
+        formdata = MultiDict(formdata)
+
+        # query parameters
+        fields = formdata.getlist('field')
+        operators = formdata.getlist('operator')
+        values = formdata.getlist('value')
+        self._query = zip(fields, operators, values)
+
+        # ordering
+        self._order = formdata.get('o', 'id'), formdata.get('r', 0)
+
+        # timeseries search
+        self._times = formdata.get('t1'), formdata.get('t2')
+        if self._times[0] is None and self._times[1] is None:
+            self._times = None
+
+    @property
+    def params(self, **kwargs):
+        # return url params to compose the query
+        fields = []
+        operators = []
+        values = []
+        for f, o, v in self._query:
+            fields.append(f)
+            operators.append(o)
+            values.append(v)
+        params = {'field': fields,
+                  'operator': operators,
+                  'value': values,
+                  'o': self._order[0],
+                  'r': self._order[1]}
+        if self._times:
+            params.update({'t1': self._times[0],
+                           't2': self._times[1]})
+        params.update(**kwargs)
+        return params
+
     def create(self):
         curs = g.db.cursor()
         curs.execute("""INSERT INTO queries (title, db, query)
-                        VALUES (%(title)s, %(db)s, %(query)s)""", dict(self))
+                        VALUES (%s, %s, %s)""", (self.title, self.db, self.query))
         g.db.commit()
         return curs.lastrowid
 
     def save(self):
         curs = g.db.cursor()
-        curs.execute("""UPDATE queries
-                        SET title=%(title)s, db=%(db)s, query=%(query)s
-                        WHERE id=%(id)s""", dict(self))
+        curs.execute("""UPDATE queries SET title=%s, db=%s, query=%s
+                        WHERE id=%s""", (self.title, self.db, self.query, self.id))
         g.db.commit()
 
     def drop(self):
@@ -64,8 +129,14 @@
         curs.execute("""DELETE FROM queries WHERE id=%s""", self.id)
         g.db.commit()
 
-    def tostring(self):
-        criteria = json.loads(self.query)
-        query = ["%s %s '%s'" % (field, op, value) for field, op, value in criteria]
-        string = ' AND&nbsp;'.join(s.replace(' ', '&nbsp;') for s in query)
-        return Markup(string)
+    def __str__(self):
+        query = []
+        if self._times:
+            query.append("time BETWEEN '%s' AND '%s'" % (self._times[0], self._times[1]))
+        query += ["%s %s '%s'" % (field, op, value) for field, op, value in self._query]
+        string = " AND ".join(query)
+        if self._order[0]:
+            string += " ORDER BY %s" % self._order[0]
+        if self._order[1]:
+            string += " DESC"
+        return string
diff -r 90b18bfd7375 -r c52ae3f0196f src/ltpdarepo/templates/queries/view.html
--- a/src/ltpdarepo/templates/queries/view.html	Tue Nov 08 12:19:12 2011 +0100
+++ b/src/ltpdarepo/templates/queries/view.html	Tue Nov 08 12:22:17 2011 +0100
@@ -3,8 +3,8 @@
 {% block body %}
 <h2>Query &laquo;{{ query.title }}&raquo;</h2>
 <dl class="data">
-  <dt>Database</dt><dd><tt>{{ query.db }}</tt></dd>
-  <dt>Query</dt><dd><tt>{{ query.tostring() }}</tt></dd>
+  <dt>Databases</dt><dd><tt>{{ query.db }}</tt></dd>
+  <dt>Query</dt><dd><tt>{{ query }}</tt></dd>
 </dl>
 <ul class="actions">
   <li><a href="{{ url_for('manage.queries.edit', id=query.id) }}">Edit</a></li>
diff -r 90b18bfd7375 -r c52ae3f0196f src/ltpdarepo/templates/query.html
--- a/src/ltpdarepo/templates/query.html	Tue Nov 08 12:19:12 2011 +0100
+++ b/src/ltpdarepo/templates/query.html	Tue Nov 08 12:22:17 2011 +0100
@@ -9,21 +9,22 @@
       var criteria = {{ indexes|tojson|safe }};
       $(function () {
         $.dropdown.replace($("select"));
-        $("#save").live('click', function () {
-          var query = $(".query form").serialize();
-          if (! query)
+        $("#save").bind('click', function (event) {
+          var query = $(location).attr('search');
+          if (!query)
             return false;
+          query = query.slice(1);
           var form = $(document.createElement('form')).attr({
             'action': '{{ url_for('manage.queries.create') }}',
             'method': 'POST' });
           form.append($(document.createElement('input')).attr({
             'type': 'hidden',
-            'name': 'query',
-            'value': query }));
+            'name': 'db',
+            'value': '{{ database.id }}' }));
           form.append($(document.createElement('input')).attr({
             'type': 'hidden',
-            'name': 'db',
-            'value': '{{ database.id }}' }));
+            'name': 'query',
+            'value': query }));          
           $("body").append(form);
           form.submit();
           form.remove();
@@ -80,7 +81,7 @@
 
     <input type="submit" value="search" />
 
-    {% if 'query' in request.endpoint and 'admin' in g.identity.roles %}
+    {% if 'admin' in g.identity.roles %}
     <input id="save" type="submit" name="save" value="save query" />
     {% endif %}
 
diff -r 90b18bfd7375 -r c52ae3f0196f src/ltpdarepo/tests/manage-queries.txt
--- a/src/ltpdarepo/tests/manage-queries.txt	Tue Nov 08 12:19:12 2011 +0100
+++ b/src/ltpdarepo/tests/manage-queries.txt	Tue Nov 08 12:22:17 2011 +0100
@@ -26,7 +26,7 @@
 The form contains the proper query representation in JSON format::
 
     >>> browser.getControl(name='query').value
-    '[["id", ">", 0], ["name", "LIKE", "%"]]'
+    '{"query": [["id", ">", "0"], ["name", "LIKE", "%"]], "order": ["id", 0]}'
 
 Save query setting title and the databases where it operates::
 
@@ -44,9 +44,9 @@
     >>> browser.contents
     '...<h2>Query &laquo;Query&raquo;</h2>...'
     >>> browser.contents
-    '...<dt>Database</dt><dd><tt>^(db1)$</tt></dd>...'
+    '...<dt>Databases</dt><dd><tt>^(db1)$</tt></dd>...'
     >>> browser.contents
-    '...<dt>Query</dt><dd><tt>id&nbsp;>&nbsp;\'0\' AND&nbsp;name&nbsp;LIKE&nbsp;\'%\'</tt></dd>...'
+    '...<dt>Query</dt><dd><tt>id &gt; &#39;0&#39; AND name LIKE &#39;%&#39; ORDER BY id</tt></dd>...'
 
 Edit::
 
@@ -73,7 +73,7 @@
     >>> browser.contents
     '...<h2>Query &laquo;yreuQ&raquo;</h2>...'
     >>> browser.contents
-    '...<dt>Database</dt><dd><tt>^(db1|db2)$</tt></dd>...'
+    '...<dt>Databases</dt><dd><tt>^(db1|db2)$</tt></dd>...'
 
 Drop can be cancelled::
 
@@ -98,7 +98,6 @@
     >>> browser.contents
     '...<div class="flash message">Named query deleted.</div>...'
 
-
 Create a new query for database 'db1'::
 
     >>> browser.post('/manage/queries/+', data)
@@ -113,8 +112,8 @@
 
     >>> browser.open('/browse/db1/')
     >>> browser.getLink('Query').click()
-    >>> browser.url
-    'http://localhost/browse/db1/query/2'
+    >>> browser.url # named query view redirects to query builder interface
+    'http://localhost/browse/db1/query?field=id&field=name&r=0&value=0&value=%25&operator=%3E&operator=LIKE&o=id'
 
 Edit to make quary available for database 'db2' only::
 
diff -r 90b18bfd7375 -r c52ae3f0196f src/ltpdarepo/tests/test_query.py
--- a/src/ltpdarepo/tests/test_query.py	Tue Nov 08 12:19:12 2011 +0100
+++ b/src/ltpdarepo/tests/test_query.py	Tue Nov 08 12:22:17 2011 +0100
@@ -22,7 +22,7 @@
 
     def test_query(self):
         # create
-        query = Query(title='Query', db='^(db1)$', query='[["id", ">", 0]]')
+        query = Query(title='Query', db='^(db1)$', query='{"query": [["id", ">", 0]], "order": ["id", 0]}')
         id = query.create()
         self.assertEqual(id, 1)
 
@@ -30,19 +30,19 @@
         query = Query.load(id)
         self.assertEqual(query.title, 'Query')
         self.assertEqual(query.db, '^(db1)$')
-        self.assertEqual(query.query, '[["id", ">", 0]]')
+        self.assertEqual(query.query, '{"query": [["id", ">", 0]], "order": ["id", 0]}')
 
         # edit
         query.title = 'yreuQ'
         query.db = '^(db1|db2)$'
-        query.query = '[["id", ">", 0], ["name", "LIKE", "%"]]'
+        query.query = '{"query": [["id", ">", 0], ["name", "LIKE", "%"]], "order": ["id", 0]}'
         query.save()
 
         # check
         query = Query.load(id)
         self.assertEqual(query.title, 'yreuQ')
         self.assertEqual(query.db, '^(db1|db2)$')
-        self.assertEqual(query.query, '[["id", ">", 0], ["name", "LIKE", "%"]]')
+        self.assertEqual(query.query, '{"query": [["id", ">", 0], ["name", "LIKE", "%"]], "order": ["id", 0]}')
 
         # drop
         query = Query.load(id)
@@ -50,7 +50,12 @@
         query = Query.load(id)
         self.assertIsNone(query)
 
-        # tostring
-        query = Query(title='Query', db='^(db1)$', query='[["id", ">", 0], ["name", "LIKE", "%"]]')
-        string = query.tostring()
-        self.assertEqual(unicode(string), u"id_>_'0' AND_name_LIKE_'%'".replace('_', '&nbsp;'))
+        # __str__
+        query = Query(title='', db='', query='{"query": [["id", ">", 0], ["name", "LIKE", "%"]], "order": ["id", 0]}')
+        self.assertEqual(unicode(query), u"id > '0' AND name LIKE '%' ORDER BY id")
+
+        query = Query(title='', db='', query='{"query": [["id", ">", 0], ["name", "LIKE", "%"]], "order": ["id", 1]}')
+        self.assertEqual(unicode(query), u"id > '0' AND name LIKE '%' ORDER BY id DESC")
+
+        query = Query(title='', db='', query='{"query": [["id", ">", 0]], "times": ["1970-01-01", "1970-02-02"], "order": ["id", 0]}')
+        self.assertEqual(unicode(query), u"time BETWEEN '1970-01-01' AND '1970-02-02' AND id > '0' ORDER BY id")
diff -r 90b18bfd7375 -r c52ae3f0196f src/ltpdarepo/views/browse.py
--- a/src/ltpdarepo/views/browse.py	Tue Nov 08 12:19:12 2011 +0100
+++ b/src/ltpdarepo/views/browse.py	Tue Nov 08 12:22:17 2011 +0100
@@ -5,7 +5,7 @@
 import dateutil.tz
 import dateutil.parser
 
-from flask import Blueprint, Markup, abort, g, request, render_template, json, make_response
+from flask import Blueprint, Markup, abort, g, request, render_template, json, make_response, url_for, redirect
 from MySQLdb.cursors import DictCursor
 from wtforms import Form
 from wtforms.fields import Field
@@ -329,14 +329,6 @@
     def query(self):
         return self.where, self.vals
 
-    def dumps(self):
-        dump = [(field, op, value) for field, op, value, err in self.criteria if not err]
-        return json.dumps(dump, cls=DateTimeJSONEncoder)
-
-    def loads(self, string):
-        criteria = json.loads(string)
-        self.parsequery(criteria)
-
     def tostring(self):
         query = ["%s %s '%s'" % (field, op, value) for field, op, value, err in self.criteria if not err]
         string = ' AND&nbsp;'.join(s.replace(' ', '&nbsp;') for s in query)
@@ -451,7 +443,7 @@
         row = curs.fetchone()
         if row is not None:
             obj['mat'] = True
-            
+
         # check for xml representation
         curs = g.db.cursor()
         curs.execute("""SELECT uuid, xml LIKE %%s
@@ -610,23 +602,8 @@
             # not found
             abort(404)
 
-        # applicable search criteria
-        indexes = Indexes(database, Objs.columns)
-
-        # parse request
-        r = Request(request.args, Objs.columns, indexes)
-
-        # load query
-        r.loads(query.query)
-
-        # collect objects
-        count = Objs(database).filter(*r.query).count()
-        batch = Pagination(r.page, size=r.pagesize, count=count)
-        objs  = Objs(database).filter(*r.query).orderby(*r.order).limit(*batch.limits).all()
-
-        return render_template('namedquery.html', objs=objs, batch=batch,
-                               fields=FIELDS, database=db,
-                               query=query, querystring=r.tostring())
+        # redirect to query view
+        return redirect(url_for('browse.%s' % query.kind, database=database, **query.params))
 
 
 @app.route('/<database>/timeseries')
@@ -676,7 +653,7 @@
             if when.count('-') == 1:
                 span = 'MONTH'
                 today = datetime.strptime(when, '%Y-%m').date()
-                
+
 
         if span == 'MONTH':
             begin = today + relativedelta(day=1)
@@ -685,7 +662,7 @@
             next = (begin + relativedelta(months=+1)).strftime('%Y-%m')
             increment = 'DAY'
             dt = relativedelta(days=1)
-            
+
         if span == 'DAY':
             begin = today + relativedelta(hour=0)
             end = begin + relativedelta(hour=0, days=1)
diff -r 90b18bfd7375 -r c52ae3f0196f src/ltpdarepo/views/queries.py
--- a/src/ltpdarepo/views/queries.py	Tue Nov 08 12:19:12 2011 +0100
+++ b/src/ltpdarepo/views/queries.py	Tue Nov 08 12:22:17 2011 +0100
@@ -13,7 +13,7 @@
 @require('admin')
 def index():
     curs = g.db.cursor(DictCursor)
-    curs.execute("""SELECT id, title FROM queries""")
+    curs.execute("""SELECT id, title FROM queries ORDER BY title""")
     queries = curs.fetchall()
     return render_template('queries/index.html', queries=queries)
 
@@ -31,14 +31,11 @@
 @app.route('/+', methods=['GET', 'POST'])
 @require('admin')
 def create():
-    if 'title' not in request.values:
-
-        from .browse import Indexes, Request, Objs
-        indexes = Indexes(request.values.get('db', ''), Objs.columns)
-        query = Request.fromqs(request.values.get('query', ''), Objs.columns, indexes).dumps()
-
+    if 'title' not in request.form:
+        query = Query()
+        query.parse(request.form.get('query'))
         form = IQuery()
-        form.query.data = query
+        form.query.data = query.query
         form.csrf.data = session['_token']
         return render_template('queries/create.html', form=form)