Mercurial > hg > ltpdarepo
changeset 184:c52ae3f0196f
Make named queries store ordering and work for timeseries searches.
| author | Daniele Nicolodi <daniele@grinta.net> |
|---|---|
| date | Tue, 08 Nov 2011 12:22:17 +0100 |
| parents | 90b18bfd7375 |
| children | 5e2d298e01ef |
| files | src/ltpdarepo/query.py src/ltpdarepo/templates/queries/view.html src/ltpdarepo/templates/query.html src/ltpdarepo/tests/manage-queries.txt src/ltpdarepo/tests/test_query.py src/ltpdarepo/views/browse.py src/ltpdarepo/views/queries.py |
| diffstat | 7 files changed, 127 insertions(+), 77 deletions(-) [+] |
line wrap: on
line diff
--- 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 '.join(s.replace(' ', ' ') 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
--- 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 «{{ query.title }}»</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>
--- 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 %}
--- 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 «Query»</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 > \'0\' AND name LIKE \'%\'</tt></dd>...' + '...<dt>Query</dt><dd><tt>id > '0' AND name LIKE '%' ORDER BY id</tt></dd>...' Edit:: @@ -73,7 +73,7 @@ >>> browser.contents '...<h2>Query «yreuQ»</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::
--- 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('_', ' ')) + # __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")
--- 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 '.join(s.replace(' ', ' ') 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)
--- 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)