Mercurial > hg > ltpdarepo
changeset 38:aa8b0bb724f6
Some polish to pagination and database browsing code.
| author | Daniele Nicolodi <daniele@grinta.net> |
|---|---|
| date | Wed, 29 Jun 2011 00:39:27 +0200 |
| parents | e71a85df59e0 |
| children | 532d9d070ef5 |
| files | src/ltpdarepo/__init__.py src/ltpdarepo/pagination.py src/ltpdarepo/templates/browse.html src/ltpdarepo/templates/query.html src/ltpdarepo/templates/query/index.html src/ltpdarepo/views/browse.py |
| diffstat | 6 files changed, 171 insertions(+), 131 deletions(-) [+] |
line wrap: on
line diff
--- a/src/ltpdarepo/__init__.py Mon Jun 27 21:05:24 2011 +0200 +++ b/src/ltpdarepo/__init__.py Wed Jun 29 00:39:27 2011 +0200 @@ -74,6 +74,14 @@ return '' +def url_for_other_page(page): + args = request.view_args.copy() + args.update(request.args) + args.update(p=page) + return url_for(request.endpoint, **args) +app.jinja_env.globals['url_for_other_page'] = url_for_other_page + + @app.route('/login', methods=['GET', 'POST']) def login(): if request.method == 'POST':
--- a/src/ltpdarepo/pagination.py Mon Jun 27 21:05:24 2011 +0200 +++ b/src/ltpdarepo/pagination.py Wed Jun 29 00:39:27 2011 +0200 @@ -1,8 +1,8 @@ from math import ceil, floor class Pagination(object): - def __init__(self, current, size, count, items=9): - self.current = current + def __init__(self, page=1, count=10, size=10, items=9): + self.current = page self.size = size self.count = count self.items = items @@ -24,8 +24,9 @@ return self.current < self.pages @property - def limits(self): - return ((self.current - 1) * self.size, self.size) + def slice(self): + start = (self.current - 1) * self.size + return slice(start, start + self.size) def __iter__(self): # cache number of pages
--- a/src/ltpdarepo/templates/browse.html Mon Jun 27 21:05:24 2011 +0200 +++ b/src/ltpdarepo/templates/browse.html Wed Jun 29 00:39:27 2011 +0200 @@ -18,6 +18,7 @@ {% if not objs %} <p class="important">—</p> {% else %} +<p class="discrete">{{ batch.count }} objects</p> <table class="listing"> <thead> <tr> @@ -43,9 +44,9 @@ {% endfor %} </tbody> </table> -{% import "pagination.html" as p %} +{% import "pagination.html" as pagination %} {% if pagination is defined %} -{{ p.render(pagination) }} +{{ pagination.render(batch) }} {% endif %} {% endif %} {% endblock %}
--- a/src/ltpdarepo/templates/query.html Mon Jun 27 21:05:24 2011 +0200 +++ b/src/ltpdarepo/templates/query.html Wed Jun 29 00:39:27 2011 +0200 @@ -66,6 +66,7 @@ </div> {% if objs %} +<p class="discrete">{{ batch.count }} objects</p> <table class="listing"> <thead> <tr>
--- a/src/ltpdarepo/templates/query/index.html Mon Jun 27 21:05:24 2011 +0200 +++ b/src/ltpdarepo/templates/query/index.html Wed Jun 29 00:39:27 2011 +0200 @@ -2,7 +2,7 @@ {% block title %} Queries {% endblock %} {% block body %} <h2>Queries</h2> -<p class="discrete">Manage queries for database &llaquo;{{ database }}&rlaquo;:</p> +<p class="discrete">Queries for database «{{ database }}»</p> <ul> {% for query in queries %} <li><a href="{{ url_for('manage.queries.view', database=database, query=query) }}">{{ query }}</a></li>
--- a/src/ltpdarepo/views/browse.py Mon Jun 27 21:05:24 2011 +0200 +++ b/src/ltpdarepo/views/browse.py Wed Jun 29 00:39:27 2011 +0200 @@ -1,8 +1,9 @@ import re -from textwrap import dedent +import sys +import datetime from operator import itemgetter -from flask import Module, abort, g, request, render_template, redirect, current_app, url_for, json, make_response +from flask import Module, abort, g, request, render_template, redirect, url_for, json, make_response from MySQLdb.cursors import DictCursor from ltpdarepo.security import require, view @@ -17,6 +18,7 @@ PAGESIZE = 20 + FIELDS = ('id', 'name', 'type', @@ -31,54 +33,101 @@ 'comments', 'reference ids', 'created', 'version', 'ip', 'hostname', 'os',) + ALLFIELDS = FIELDS + EXTRA -BLACKLIST = ('validated', 'vdate',) + +MAPPING = { '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': '', + } + +MAPPING = dict(((v, k) for k, v in MAPPING.iteritems())) + + +def _current_page(): + """return the current page in a paginated view""" + return int(request.args.get('p', 1)) + class Objs(object): + def __init__(self, database): + self._database = database + self._orderby = None + self._limit = None + self._where = None + self._values = None - _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): + columns = ", ".join("`%s` AS `%s`" % x for x in MAPPING.iteritems()) + 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 + if self._limit: + query += " LIMIT %d,%d" % self._limit + return query @property - def query(self): - return str(self) + def _count(self): + 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 + + def filter(self, where, values): + self._where = where + self._values = values + return self + + def orderby(self, order): + self._orderby = order + return self + + def __getitem__(self, item): + if isinstance(item, slice): + start, stop, step = item.indices(sys.maxint) + count = stop - start + self._limit = (start, count) + return self.__call__() + else: + start, count = item, 1 + self._limit = (start, count) + objs = self.__call__() + if objs is not None: + return objs[0] + + def __call__(self): + curs = g.db.cursor(DictCursor) + curs.execute(self._query, self._values) + return curs.fetchall() + + def count(self): + curs = g.db.cursor() + curs.execute(self._count, self._values) + return curs.fetchone()[0] app = Module(__name__, 'browse') @@ -107,30 +156,18 @@ # not found abort(404) - curs = g.db.cursor() - curs.execute("""SELECT COUNT(*) FROM `%s`.objmeta""" % database) - count = curs.fetchone()[0] - page = int(request.args.get('p', 1)) - pagination = Pagination(page, PAGESIZE, count) + count = Objs(database=database).count() + batch = Pagination(_current_page(), size=PAGESIZE, count=count) + objs = Objs(database=database).orderby('obj_id')[batch.slice] - def url_for_other_page(page): - args = request.view_args.copy() - args.update(p=page) - return url_for(request.endpoint, **args) - current_app.jinja_env.globals['url_for_other_page'] = url_for_other_page - - curs = g.db.cursor(DictCursor) - objs = Objs(database=database, orderby='id', limit=((page-1)*PAGESIZE, PAGESIZE)) - curs.execute(objs.query) - objs = curs.fetchall() - - return render_template('browse.html', objs=objs, fields=FIELDS, - database=db, pagination=pagination) + return render_template('browse.html', objs=objs, + fields=FIELDS, database=db, batch=batch) @app.route('/<database>/activity') +@app.route('/<database>/activity/<period>/<date>') @require('user') -def activity(database): +def activity(database, period='week', date=None): with view('database', database): db = Database().load(database) if db is None: @@ -139,9 +176,10 @@ curs = g.db.cursor() - import datetime - today = datetime.date.today() - activity = OrderedDict() + if date is None: + today = datetime.date.today() + else: + today = datetime.datetime.strptime(date, '%Y-%m-%d').date() activity = [] @@ -155,8 +193,7 @@ n = curs.fetchone()[0] activity.append((str(start), n)) - return render_template('activity.html', database=db, - activity=activity) + return render_template('activity.html', database=db, activity=activity) @app.route('/<database>/<int:objid>') @@ -167,11 +204,11 @@ if db is None: # not found abort(404) - objs = Objs(database=database, where='obj_id=%s') - curs = g.db.cursor(DictCursor) - curs.execute(objs.query, objid) - obj = curs.fetchone() - return render_template('obj.html', database=db, obj=obj, fields=ALLFIELDS) + obj = Objs(database=database).filter('obj_id=%s', objid)[0] + if obj is None: + # not found + abort(404) + return render_template('obj.html', obj=obj, database=db, fields=ALLFIELDS) @app.route('/<database>/<int:objid>/<frmt>') @@ -186,28 +223,29 @@ if frmt not in ('xml', 'mat'): # not found abort(404) - + if frmt == 'xml': curs = g.db.cursor() - curs.execute("""SELECT xml FROM `%s`.objs WHERE id=%%s""" % database, objid) + curs.execute("SELECT xml FROM `%s`.objs WHERE id=%%s" % database, objid) mimetype = 'text/xml' if frmt == 'mat': curs = g.db.cursor() - curs.execute("""SELECT mat FROM `%s`.bobjs WHERE obj_id=%%s""" % database, objid) + curs.execute("SELECT mat FROM `%s`.bobjs WHERE obj_id=%%s" % database, objid) mimetype = 'application/matlab' - data = curs.fetchone()[0] + data = curs.fetchone() if data is None: # not found abort(404) + data = data[0] or '' # construct response response = make_response(data) response.mimetype = mimetype filename = '%s-%s.%s' % (database, objid, frmt) - response.headers.add('Content-Disposition' , 'attachment', filename=filename) + response.headers.add('Content-Disposition', 'attachment', filename=filename) return response - + @app.route('/<database>/search') @require('user') @@ -220,59 +258,49 @@ # search criteria q = request.args.get('q', '') + q = ('%%%s%%' % q) - # 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) + # query + count = Objs(database=database).filter('name LIKE %s', q).count() + batch = Pagination(_current_page(), size=PAGESIZE, count=count) + objs = Objs(database=database).filter('name LIKE %s', q).orderby('obj_id')[batch.slice] + + return render_template('browse.html', objs=objs, + fields=FIELDS, database=db, batch=batch) def _column_desc(desc): val = desc[0] kind = re.match(r'(\w+)', desc[1]).group(1) - values = '' + values = None 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: + + name = MAPPING.get(val) + if name is None: return None - kind = {'text': 'str', - 'tinyint': 'int'}.get(kind, kind) - ops = {'int': ('=', '>', '<'), - 'str': ('=', 'LIKE'), - 'enum': ('=',), + + ops = {'int': ('=', '>', '<'), + 'tinyint': ('=', '>', '<'), + 'text': ('=', 'LIKE'), + 'enum': ('=',), 'datetime': ('=', '>', '<')}.get(kind) - order = dict((v, i) for i, v in enumerate(ALLFIELDS)).get(name, 100); + + order = dict((v, i) for i, v in enumerate(ALLFIELDS)).get(name, 100) - return {'name': name, 'val': val, 'operators': ops, - 'type': kind, 'values': values, 'order': order} + return {'val': val, + 'name': name, + 'type': kind, + 'operators': ops, + 'values': values, + 'order': order} 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() ]) + indexes = filter(None, [_column_desc(desc) for desc in curs.fetchall()]) return OrderedDict((x['val'], x) for x in sorted(indexes, key=itemgetter('order'))) @@ -307,19 +335,20 @@ form.action = url_for('browse.save', database=database) return render_template('query/save.html', form=form) - 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() + # build query string + q = ['`%s` %s %%s' % (field, op) for field, op in zip(fields, ops)] + where = ' AND '.join(q) + + # 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('obj_id')[batch.slice] # collect search critaeria criteria = _indexes(database, 'objmeta') - return render_template('query.html', database=db, objs=objs, - criteria=criteria, fields=FIELDS, query=query) + return render_template('query.html', query=query, objs=objs, + criteria=criteria, database=db, fields=FIELDS, batch=batch) @app.route('/<database>/query/save', methods=['GET', 'POST'])