# HG changeset patch # User Daniele Nicolodi # Date 1313260044 -7200 # Node ID cfda4a03b2aed1235e585b4973fa7fda4563cc21 # Parent 38afb05e347193bc13966737bc218316407eeef2 Clean database browsing code. Remove query saving code. diff -r 38afb05e3471 -r cfda4a03b2ae src/ltpdarepo/templates/query.html --- a/src/ltpdarepo/templates/query.html Sat Aug 13 20:27:24 2011 +0200 +++ b/src/ltpdarepo/templates/query.html Sat Aug 13 20:27:24 2011 +0200 @@ -55,11 +55,6 @@
- {% if 'admin' in g.identity.roles %} -
- -
- {% endif %} diff -r 38afb05e3471 -r cfda4a03b2ae 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 @@ -3,13 +3,12 @@ import datetime from operator import itemgetter -from flask import Blueprint, abort, g, request, render_template, redirect, url_for, json, make_response +from flask import Blueprint, abort, g, request, render_template, json, make_response 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 @@ -32,33 +31,30 @@ 'additional authors', 'comments', 'reference ids', - 'created', 'version', 'ip', 'hostname', 'os',) + 'created', ) ALLFIELDS = FIELDS + EXTRA -RMAPPING = { 'id': 'obj_id', - 'name': 'name', - 'type': 'obj_type', - 'quantity': 'quantity', - 'keywords': 'keywords', - 'submitted': 'submitted', - 'title': 'experiment_title', - 'description': 'experiment_desc', - 'analysis': 'analysis_desc', - 'author': 'author', - 'additional authors': 'additional_authors', - 'comments': 'additional_comments', - 'reference ids': 'reference_ids', - 'created': 'created', - 'version': 'version', - 'ip': 'ip', - 'hostname': 'hostname', - 'os': 'os', - # 'validated': '', - # 'vdate': '', - } +# 'version', 'ip', 'hostname', 'os', 'validated', 'vdate' -MAPPING = dict(((v, k) for k, v in RMAPPING.iteritems())) +RMAPPING = { 'id': 'objmeta.obj_id', + 'name': 'objmeta.name', + 'type': 'objmeta.obj_type', + 'quantity': 'objmeta.quantity', + 'keywords': 'objmeta.keywords', + 'submitted': 'objmeta.submitted', + 'title': 'objmeta.experiment_title', + 'description': 'objmeta.experiment_desc', + 'analysis': 'objmeta.analysis_desc', + 'author': 'objmeta.author', + 'additional authors': 'objmeta.additional_authors', + 'comments': 'objmeta.additional_comments', + 'reference ids': 'objmeta.reference_ids', + 'created': 'objmeta.created', + 't0': 'tsdata.t0', + 'nsecs': 'tsdata.nsecs', } + +MAPPING = dict(((v.split('.')[1], k) for k, v in RMAPPING.iteritems())) def _current_page(): @@ -85,14 +81,14 @@ @property def _query(self): - columns = ", ".join("`%s` AS `%s`" % x for x in MAPPING.iteritems()) + columns = ", ".join("%s AS `%s`" % (RMAPPING[x], x) for x in FIELDS) query = "SELECT " + columns + " FROM `%s`.objmeta" % self._database if self._where: query += " WHERE %s" % self._where if self._orderby: - query += " ORDER BY `%s`" % self._orderby + query += " ORDER BY %s" % self._orderby if self._reverse: - query += " DESC" + query += " DESC" if self._limit: query += " LIMIT %d,%d" % self._limit return query @@ -102,8 +98,6 @@ query = "SELECT COUNT(*) FROM `%s`.objmeta" % 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 @@ -143,6 +137,8 @@ curs.execute(self._count, self._values) return curs.fetchone()[0] + def __len__(self): + return self.count() app = Blueprint('browse', __name__) @@ -172,11 +168,10 @@ abort(404) count = Objs(database=database).count() - batch = Pagination(_current_page(), size=PAGESIZE, count=count) + batch = Pagination(_current_page(), size=PAGESIZE, count=count) objs = Objs(database=database).orderby(_current_ordering())[batch.slice] - return render_template('browse.html', objs=objs, - fields=FIELDS, database=db, batch=batch) + return render_template('browse.html', objs=objs, fields=FIELDS, database=db, batch=batch) @app.route('//activity') @@ -280,8 +275,7 @@ batch = Pagination(_current_page(), size=PAGESIZE, count=count) objs = Objs(database=database).filter('name LIKE %s', q).orderby(_current_ordering())[batch.slice] - return render_template('browse.html', objs=objs, - fields=FIELDS, database=db, batch=batch) + return render_template('browse.html', objs=objs, fields=FIELDS, database=db, batch=batch) def _column_desc(desc): @@ -298,7 +292,7 @@ ops = {'int': ('=', '>', '<'), 'tinyint': ('=', '>', '<'), - 'text': ('=', 'LIKE'), + 'text': ('LIKE', '='), 'enum': ('=',), 'datetime': ('=', '>', '<')}.get(kind) @@ -319,39 +313,22 @@ return OrderedDict((x['val'], x) for x in sorted(indexes, key=itemgetter('order'))) -@app.route('//query/') -@app.route('//query/') +@app.route('//query') @require('user') -def query(database, name=None): +def query(database): 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) + 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' % (field, op) for field, op in zip(fields, ops)] + q = ['`%s` %s %%s' % v for v in zip(fields, ops)] where = ' AND '.join(q) # get objects @@ -366,19 +343,37 @@ criteria=criteria, database=db, fields=FIELDS, batch=batch) -@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)) +@app.route('//query/') +@require('user') +def namedquery(database, name): + with view('database', database): + db = Database().load(database) + if db is None: + # not found + abort(404) + + 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]) + + # build query string + q = ['`%s` %s %%s' % v for v in zip(fields, ops)] + where = ' AND '.join(q) + + # simple string representation of the query + querystring = ' AND '.join("%s %s %s" % tuple(v) for v in query) + + # get objects + count = Objs(database=database).filter(where, values).count() + batch = Pagination(_current_page(), size=PAGESIZE, count=count) + objs = Objs(database=database).filter(where, values).orderby(_current_ordering())[batch.slice] + + return render_template('namedquery.html', objs=objs, database=db, fields=FIELDS, batch=batch, query=querystring) module = app