# HG changeset patch
# User Daniele Nicolodi <daniele@grinta.net>
# Date 1309300767 -7200
# Node ID aa8b0bb724f69521283f2aa26437656fb9cb3066
# Parent  e71a85df59e048215ef1540ae64e4edb197b2e8a
Some polish to pagination and database browsing code.

diff -r e71a85df59e0 -r aa8b0bb724f6 src/ltpdarepo/__init__.py
--- 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':
diff -r e71a85df59e0 -r aa8b0bb724f6 src/ltpdarepo/pagination.py
--- 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
diff -r e71a85df59e0 -r aa8b0bb724f6 src/ltpdarepo/templates/browse.html
--- 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">&mdash;</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 %}
diff -r e71a85df59e0 -r aa8b0bb724f6 src/ltpdarepo/templates/query.html
--- 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>
diff -r e71a85df59e0 -r aa8b0bb724f6 src/ltpdarepo/templates/query/index.html
--- 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 &#x00AB;{{ database }}&#x00BB;</p>
 <ul>
 {% for query in queries %}
   <li><a href="{{ url_for('manage.queries.view', database=database, query=query) }}">{{ query }}</a></li>
diff -r e71a85df59e0 -r aa8b0bb724f6 src/ltpdarepo/views/browse.py
--- 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'])