Mercurial > hg > ltpdarepo
changeset 6:ceb5df800c51
Add advanced query and query parameters save functionality.
This require a new 2.5 to 2.6 database schema version upgrade to add a
table where to store persistent query parameters.
| author | Daniele Nicolodi <nicolodi@science.unitn.it> |
|---|---|
| date | Wed, 15 Jun 2011 12:49:46 +0200 |
| parents | 0f59922d36d2 |
| children | 9a835002fe49 |
| files | src/ltpdarepo/__init__.py src/ltpdarepo/query.py src/ltpdarepo/static/querywidget.js src/ltpdarepo/static/style.css src/ltpdarepo/templates/database.html src/ltpdarepo/templates/query.html src/ltpdarepo/templates/query/save.html src/ltpdarepo/upgrade.py src/ltpdarepo/views/browse.py |
| diffstat | 9 files changed, 529 insertions(+), 82 deletions(-) [+] |
line wrap: on
line diff
--- a/src/ltpdarepo/__init__.py Wed Jun 15 11:07:20 2011 +0200 +++ b/src/ltpdarepo/__init__.py Wed Jun 15 12:49:46 2011 +0200 @@ -5,7 +5,7 @@ from .security import secure, require, authenticate -SCHEMA = 2.5 +SCHEMA = 2.6 app = Flask(__name__)
--- /dev/null Thu Jan 01 00:00:00 1970 +0000 +++ b/src/ltpdarepo/query.py Wed Jun 15 12:49:46 2011 +0200 @@ -0,0 +1,20 @@ +from flask import g + +from ltpdarepo.form import Form +from wtforms.fields import TextField, HiddenField + + +class IQuery(Form): + querystring = HiddenField() + name = TextField("Name") + + +class Query(dict): + def __init__(self, name=None, db=None, querystring=None): + super(Query, self).__init__(name=name, db=db, querystring=querystring) + self.__dict__ = self + + def create(self): + curs = g.db.cursor() + curs.execute("""INSERT INTO queries (name, db, querystring) + VALUES (%(name)s, %(db)s, %(querystring)s)""", dict(self))
--- /dev/null Thu Jan 01 00:00:00 1970 +0000 +++ b/src/ltpdarepo/static/querywidget.js Wed Jun 15 12:49:46 2011 +0200 @@ -0,0 +1,119 @@ +;(function($) { + + // namespace + if (typeof($.querywidget) == "undefined") { + $.querywidget = { + config: {}, + initialized: false + }; + } + + // create a select menu + $.querywidget.createSelect = function (values, selectedvalue, classname, name) { + // create select + var select = $(document.createElement('select')).addClass(classname).attr('name', name); + $.each(values, function (i, val) { + var id = val; + var name = val; + if (typeof(val.name) !== 'undefined') { + id = val.val; + name = val.name; + } + // create options + var option = $(document.createElement('option')).attr('value', id).html(name); + if (i == selectedvalue) { + option.attr('selected', 'selected'); + } + select.append(option); + }); + return select; + }; + + // create label + $.querywidget.createQueryLabel = function (index) { + var name = $.querywidget.config.indexes[index].name; + return $(document.createElement('span')).html(name).addClass('querywidget field'); + }; + + // create field name + $.querywidget.createQueryField = function (index) { + var value = $.querywidget.config.indexes[index].val; + return $(document.createElement('input')).attr({ + 'type': 'hidden', + 'name': 'field', + 'value': value}) + }; + + // create operator select menu + $.querywidget.createQueryOperator = function (index, value) { + return $.querywidget.createSelect($.querywidget.config.indexes[index].operators, + value, + 'querywidget operator', + 'operator'); + }; + + // create value field + $.querywidget.createWidget = function (index) { + var type = $.querywidget.config.indexes[index].type; + switch (type) { + case 'enum': + return $.querywidget.createSelect($.querywidget.config.indexes[index].values, '', 'value', 'value'); + break; + default: + return $(document.createElement('input')).attr({ + 'autocomplete': 'off', + 'type': 'text', + 'name': 'value' + }).addClass('querywidget value'); + break; + } + }; + + // init + $(document).ready(function () { + $.querywidget.init(); + }); + + // init widget + $.querywidget.init = function () { + + // check if already initialized + if ($.querywidget.initialized == true) { + // return nothing done + return false; + } + + // set initialized + $.querywidget.initialized = true; + + // configuration + $.querywidget.config.indexes = criteria; + + $('#add').live('change', function () { + var index = $(this).find(':selected')[0].value; + var newcriteria = $(document.createElement('div')).addClass('criteria'); + newcriteria.append($.querywidget.createQueryLabel(index)); + newcriteria.append($.querywidget.createQueryField(index)); + newcriteria.append($.querywidget.createQueryOperator(index,'')); + newcriteria.append($.querywidget.createWidget(index)); + newcriteria.append( + $(document.createElement('input')) + .attr({ + 'value': '\u00D7', + 'type': 'button', + 'name': 'remove'}) + .addClass('querywidget remove') + ); + $('#criteria').append(newcriteria); + $(this).val(''); + }); + + $('.remove').live('click', function () { + $(this).parents('.criteria').remove(); + return false; + }); + + }; + + })(jQuery); +
--- a/src/ltpdarepo/static/style.css Wed Jun 15 11:07:20 2011 +0200 +++ b/src/ltpdarepo/static/style.css Wed Jun 15 12:49:46 2011 +0200 @@ -6,6 +6,10 @@ padding: 0; } +.hidden { + display: none; +} + div.left { float: left; } @@ -237,7 +241,7 @@ margin: 0.3em; } -input[type=submit]:hover, #submit:hover { +input[type=submit]:hover, input[type=button]:hover, #submit:hover { background: #CFF09E; } @@ -362,8 +366,8 @@ .search { margin: 1.2em 0; + float: left; border: 4px solid #EEE; - float: left; -moz-border-radius: 4px; } @@ -411,6 +415,81 @@ /* actions */ ul.actions li { - margin-left: 2em; - line-height: 1.6em; + line-height: 1.6em; +} + +ul.actions li a { + text-decoration: none; +} + +ul.actions li a:hover { + border-bottom: 1px solid; +} + +/* query builder */ + +div.criteria { + margin: 3px; + border: 4px solid #EEE; + -moz-border-radius: 4px; + float: left; + clear: both; + height: 27px; +} + +div.criteria > * { + border: 1px solid #BFBFBF; + margin: 0; + float: left; + line-height: 1.2em; + font-size: 90%; +} + +div.criteria .field { + padding: 5px 10px; + width: 12em; + border-right: none; + line-height: 1.3em; } + +div.criteria .operator { + width: 5em; + border-right: none; +} + +div.criteria select { + padding: 4px; +} + +div.criteria .value { + width: 24em; + padding: 5px 10px; + border-right: none; + line-height: 13px !important; + vertical-align: bottom; +} + +div.criteria select.value { + padding: 4px; +} + +div.criteria .remove { + padding: 4px 5px; +} + +div.query { + margin: 0.5em; +} + +div.criteria select, div.criteria input { + font-size: 90%; +} + +select, input { + font-size: 100%; + margin: 3px; +} + +#save-search-criteria { + float: right; +}
--- a/src/ltpdarepo/templates/database.html Wed Jun 15 11:07:20 2011 +0200 +++ b/src/ltpdarepo/templates/database.html Wed Jun 15 12:49:46 2011 +0200 @@ -6,14 +6,24 @@ <ul class="actions"> <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> </ul> <h2>Search database «{{ database.id }}»</h2> <p class="discrete">Search objects by name</p> -<div class="search wrapper"> - <form method="GET" action="{{ url_for('browse.search', database=database.id) }}"> - <div class="left"><input type="text" name="q"></input></div> - <div class="right"><input type="submit" value="»"></input></div> - </form> +<div class="wrapper"> + <div class="search wrapper"> + <form method="GET" action="{{ url_for('browse.search', database=database.id) }}"> + <div class="left"><input type="text" name="q"></input></div> + <div class="right"><input type="submit" value="»"></input></div> + </form> + </div> </div> - +{% if queries %} +<p class="discrete">Saved queries</p> +<ol> + {% for query in queries %} + <li><a href="{{ url_for('browse.query', database=database.id, name=query) }}">{{ query }}</a></li> + {% endfor %} +</ol> +{% endif %} {% endblock %}
--- /dev/null Thu Jan 01 00:00:00 1970 +0000 +++ b/src/ltpdarepo/templates/query.html Wed Jun 15 12:49:46 2011 +0200 @@ -0,0 +1,95 @@ +{% extends "layout.html" %} +{% block title %}{{ database.id }}{% endblock %} +{% block head %} + <script type="text/javascript" src="/static/jquery.js"></script> + <script type="text/javascript" src="/static/querywidget.js"></script> + <script type="text/javascript"> + var criteria = {{ criteria|tojson|safe }}; + </script> +{% endblock %} +{% block body %} +<h2>Database «{{ database.id }}»</h2> +<p class="discrete">{{ database.description|default('—'|safe, true) }}</p> +<div class="query"> + <form method="GET" action="{{ url_for('browse.query', database=database.id) }}"> + <div id="criteria" class="wrapper"> + {% for field, op, value in query %} + <div class="criteria wrapper"> + <span class="querywidget field">{{ criteria[field]['name'] }}</span> + <input type="hidden" name="field" value="{{ field }}"></input> + <select class="querywidget operator" name="operator"> + {% for name in criteria[field]['operators'] %} + {% if op == name %} + <option name="{{ name }}" selected="selected">{{ name }}</option> + {% else %} + <option name="{{ name }}">{{ name }}</option> + {% endif %} + {% endfor %} + </select> + {% if criteria[field]['type'] == 'enum' %} + <select class="querywidget value" name="value"> + {% for name in criteria[field]['values'] %} + {% if value == name %} + <option name="{{ name }}" selected="selected">{{ name }}</option> + {% else %} + <option name="{{ name }}">{{ name }}</option> + {% endif %} + {% endfor %} + </select> + {% else %} + <input class="querywidget value" autocomplete="off" type="text" name="value" value="{{ value }}"></input> + {% endif %} + <input class="querywidget remove" type="button" name="remove" value="×"></input> + </div> + {% endfor %} + </div> + + <div> + <select id="add"> + <option value="" selected="selected">Add criteria…</option> + {% for c in criteria.values() %} + <option value="{{ c['val'] }}">{{ c['name'] }}</option> + {% endfor %} + </select> + </div> + + <div> + <input type="submit" value="»" class="search"></input> + </div> + {% if 'admin' in g.identity.roles %} + <div id="save-search-criteria"> + <input id="save" type="submit" name="save" value="save"></input> + </div> + {% endif %} + + </form> +</div> + +{% if objs %} +<table class="listing"> + <thead> + <tr> + {% for field in fields %} + <th>{{ field }}</th> + {% endfor %} + </tr> + </thead> + <tbody> + {% for obj in objs %} + <tr class="data {{ loop.cycle('odd', 'even') }}" id="{{ loop.index }}"> + {% for field in fields %} + {% if field == 'name' %} + <td class="{{ field }}"><a href="{{ url_for('browse.obj', database=database.id, objid=obj.id) }}">{{ obj[field] }}</a></td> + {% else %} + <td class="{{ field }}">{{ obj[field]|string|truncate(60, False, '…') }}</td> + {% endif %} + {% endfor %} + </tr> + <tr class="details" id="{{ loop.index }}"> + <td colspan="{{ fields|length }}" style="text-align: left;">details</td> + </tr> + {% endfor %} + </tbody> +</table> +{% endif %} +{% endblock %}
--- /dev/null Thu Jan 01 00:00:00 1970 +0000 +++ b/src/ltpdarepo/templates/query/save.html Wed Jun 15 12:49:46 2011 +0200 @@ -0,0 +1,7 @@ +{% import 'forms.html' as forms %} +{% extends "layout.html" %} +{% block title %}Save query{% endblock %} +{% block body %} +<h2>Save query</h2> +{{ forms.render(form) }} +{% endblock %}
--- a/src/ltpdarepo/upgrade.py Wed Jun 15 11:07:20 2011 +0200 +++ b/src/ltpdarepo/upgrade.py Wed Jun 15 12:49:46 2011 +0200 @@ -81,8 +81,13 @@ conn.commit() -# @register(2.5, 2.6) +@register(2.5, 2.6) def upgrade_25_to_26(conn): curs = conn.cursor() + # crerate queries table + curs.execute("""CREATE TABLE queries (name TEXT, + db TEXT, + querystring TEXT)"""); + conn.commit()
--- a/src/ltpdarepo/views/browse.py Wed Jun 15 11:07:20 2011 +0200 +++ b/src/ltpdarepo/views/browse.py Wed Jun 15 12:49:46 2011 +0200 @@ -1,9 +1,14 @@ -from flask import Module, abort, g, request, render_template, current_app, url_for +import re +from textwrap import dedent +from operator import itemgetter + +from flask import Module, abort, g, request, render_template, redirect, current_app, url_for, json from MySQLdb.cursors import DictCursor from ltpdarepo.security import require, view from ltpdarepo.database import Database from ltpdarepo.pagination import Pagination +from ltpdarepo.query import Query, IQuery try: from collections import OrderedDict @@ -12,15 +17,68 @@ PAGESIZE = 20 -FIELDS = ('id', 'name', 'type', 'quantity', 'keywords', - 'submitted', 'title', 'description',) -#extra = ( 'analysis', -# 'additional_authors', -# 'comments', -# 'created', -# 'reference_ids', -# 'version', 'ip', 'hostname', 'os',) -#unused = ('validated', 'vdate', 'author',) +FIELDS = ('id', + 'name', + 'type', + 'quantity', + 'keywords', + 'submitted', + 'title', + 'description',) +EXTRA = ( 'analysis', + 'author', + 'additional authors', + 'comments', + 'reference ids', + 'created', 'version', 'ip', 'hostname', 'os',) +ALLFIELDS = FIELDS + EXTRA +BLACKLIST = ('validated', 'vdate',) + +class Objs(object): + + _query = dedent(""" + SELECT obj_id AS id, + obj_type AS type, + name, + created, + version, + ip, + hostname, + os, + submitted, + experiment_title AS title, + experiment_desc AS description, + analysis_desc AS analysis, + quantity, + additional_authors, + additional_comments AS comments, + keywords, + reference_ids, + validated, + vdate, + author + FROM `%s`.objmeta""") + + def __init__(self, database='', orderby=None, where=None, limit=None): + self.database = database + self.orderby = orderby + self.where = where + self.limit = limit + + def __str__(self): + query = [] + query.append(self._query % self.database) + if self.where: + query.append("WHERE %s" % self.where) + if self.orderby: + query.append("ORDER BY %s" % self.orderby) + if self.limit: + query.append("LIMIT %d,%d""" % self.limit) + return " ".join(query) + + @property + def query(self): + return str(self) app = Module(__name__, 'browse') @@ -34,7 +92,10 @@ if db is None: # not found abort(404) - return render_template('database.html', database=db) + curs = g.db.cursor() + curs.execute("""SELECT name FROM queries WHERE db=%s""", database) + queries = [row[0] for row in curs.fetchall()] + return render_template('database.html', database=db, queries=queries) @app.route('/<database>/objs') @@ -54,7 +115,7 @@ def url_for_other_page(page): args = request.view_args.copy() - args['p'] = page + args.update(p=page) return url_for(request.endpoint, **args) current_app.jinja_env.globals['url_for_other_page'] = url_for_other_page @@ -124,71 +185,122 @@ abort(404) # search criteria - q = request.args.get('q', None) + q = request.args.get('q', '') - if q is not None: - # build query - curs = g.db.cursor(DictCursor) - objs = Objs(database=database, orderby='id', where='name LIKE %s', limit=(0, 50)) - curs.execute(objs.query, ('%%%s%%' % q,)) - objs = curs.fetchall() + # build query + curs = g.db.cursor(DictCursor) + objs = Objs(database=database, orderby='id', where='name LIKE %s', limit=(0, 50)) + curs.execute(objs.query, ('%%%s%%' % q,)) + objs = curs.fetchall() + + def url_for_other_page(page): + args = {} + for key, value in request.args.iteritems(): + args[key] = value + args.update(request.view_args.copy()) + args['p'] = page + return url_for(request.endpoint, **args) + current_app.jinja_env.globals['url_for_other_page'] = url_for_other_page + + return render_template('browse.html', objs=objs, fields=FIELDS, database=db) + - def url_for_other_page(page): - print request.args.get('q') - args = {} - for key, value in request.args.iteritems(): - args[key] = value - args.update(request.view_args.copy()) - #args = dict(request.args) - print args - args['p'] = page - return url_for(request.endpoint, **args) - current_app.jinja_env.globals['url_for_other_page'] = url_for_other_page +def _column_desc(desc): + val = desc[0] + kind = re.match(r'(\w+)', desc[1]).group(1) + values = '' + if kind == 'enum': + values = re.match(r'(\w+)\((.*)\)', desc[1]).group(2).split(',') + values = [v.strip("'") for v in values] + + name = {'obj_id': 'id', + 'obj_type': 'type', + 'experiment_title': 'title', + 'experiment_desc': 'description', + 'additional_comments': 'comments', + 'analysis_desc': 'analysis', + 'additional_authors': 'additional authors', + 'reference_ids': 'reference ids'}.get(val, val) + if name in BLACKLIST: + return None + kind = {'text': 'str', + 'tinyint': 'int'}.get(kind, kind) + ops = {'int': ('=', '>', '<'), + 'str': ('=', 'LIKE'), + 'enum': ('=',), + 'datetime': ('=', '>', '<')}.get(kind) + order = dict((v, i) for i, v in enumerate(ALLFIELDS)).get(name, 100); - return render_template('browse.html', objs=objs, fields=FIELDS, database=db) + return {'name': name, 'val': val, 'operators': ops, + 'type': kind, 'values': values, 'order': order} + -module = app +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'))) -class Objs(object): +@app.route('/<database>/query/<name>') +@app.route('/<database>/query/') +@require('user') +def query(database, name=None): + with view('database', database): + db = Database().load(database) + if db is None: + # not found + abort(404) + + if name is not None: + curs = g.db.cursor() + curs.execute("""SELECT querystring FROM queries WHERE db=%s AND name=%s""", + (database, name)) + query = json.loads(curs.fetchone()[0]) + fields, ops, values = [], [], [] + for q in query: + fields.append(q[0]) + ops.append(q[1]) + values.append(q[2]) + else: + fields = request.args.getlist('field') + ops = request.args.getlist('operator') + values = request.args.getlist('value') + query = zip(fields, ops, values) + + if 'save' in request.args: + form = IQuery(querystring=json.dumps(query)) + form.action = url_for('browse.save', database=database) + return render_template('query/save.html', form=form) - _query = """SELECT obj_id AS id, - obj_type AS type, - name, - created, - version, - ip, - hostname, - os, - submitted, - experiment_title AS title, - experiment_desc AS description, - analysis_desc AS analysis, - quantity, - additional_authors, - additional_comments AS comments, - keywords, - reference_ids, - validated, - vdate, - author FROM `%s`.objmeta""" + objs = Objs(database=database, orderby='id', limit=(0, 20)) + q = ['%s %s %%s' % (field, op) for field, op in zip(fields, ops)] + objs.where = ' AND '.join(q) + + curs = g.db.cursor(DictCursor) + curs.execute(objs.query, values) + objs = curs.fetchall() + + # collect search critaeria + criteria = _indexes(database, 'objmeta') + + return render_template('query.html', database=db, objs=objs, + criteria=criteria, fields=FIELDS, query=query) + - def __init__(self, database='', orderby=None, where=None, limit=None): - self.database = database - self.orderby = orderby - self.where = where - self.limit = limit +@app.route('/<database>/query/save', methods=['GET', 'POST']) +@require('admin') +def save(database): + db = Database().load(database) + if db is None: + # not found + abort(404) + form = IQuery() + if request.method == 'POST' and form.validate(): + query = Query(db=database) + form.update(query) + query.create() + return redirect(url_for('browse.database', database=database)) - def __str__(self): - query = self._query % self.database - if self.where: - query += " WHERE %s" % self.where - if self.orderby: - query += " ORDER BY %s" % self.orderby - if self.limit: - query += " LIMIT %d,%d""" % self.limit - return query - @property - def query(self): - return str(self) +module = app