# HG changeset patch # User Daniele Nicolodi # Date 1308134986 -7200 # Node ID ceb5df800c512199d286954ca7a0b852d0a960fa # Parent 0f59922d36d2a4be6ef366575df6138c815a01d8 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. diff -r 0f59922d36d2 -r ceb5df800c51 src/ltpdarepo/__init__.py --- 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__) diff -r 0f59922d36d2 -r ceb5df800c51 src/ltpdarepo/query.py --- /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)) diff -r 0f59922d36d2 -r ceb5df800c51 src/ltpdarepo/static/querywidget.js --- /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); + diff -r 0f59922d36d2 -r ceb5df800c51 src/ltpdarepo/static/style.css --- 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; +} diff -r 0f59922d36d2 -r ceb5df800c51 src/ltpdarepo/templates/database.html --- 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 @@

Search database «{{ database.id }}»

Search objects by name

-
-
-
-
-
+
+
+
+
+
+
+
- +{% if queries %} +

Saved queries

+
    + {% for query in queries %} +
  1. {{ query }}
    2. + {% endfor %} +
+{% endif %} {% endblock %} diff -r 0f59922d36d2 -r ceb5df800c51 src/ltpdarepo/templates/query.html --- /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 %} + + + +{% endblock %} +{% block body %} +

Database «{{ database.id }}»

+

{{ database.description|default('—'|safe, true) }}

+
+
+
+ {% for field, op, value in query %} +
+ {{ criteria[field]['name'] }} + + + {% if criteria[field]['type'] == 'enum' %} + + {% else %} + + {% endif %} + +
+ {% endfor %} +
+ +
+ +
+ +
+ +
+ {% if 'admin' in g.identity.roles %} +
+ +
+ {% endif %} + +
+
+ +{% if objs %} + + + + {% for field in fields %} + + {% endfor %} + + + + {% for obj in objs %} + + {% for field in fields %} + {% if field == 'name' %} + + {% else %} + + {% endif %} + {% endfor %} + + + + + {% endfor %} + +
{{ field }}
{{ obj[field] }}{{ obj[field]|string|truncate(60, False, '…') }}
details
+{% endif %} +{% endblock %} diff -r 0f59922d36d2 -r ceb5df800c51 src/ltpdarepo/templates/query/save.html --- /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 %} +

Save query

+{{ forms.render(form) }} +{% endblock %} diff -r 0f59922d36d2 -r ceb5df800c51 src/ltpdarepo/upgrade.py --- 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() diff -r 0f59922d36d2 -r ceb5df800c51 src/ltpdarepo/views/browse.py --- 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('//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('//query/') +@app.route('//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('//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