# HG changeset patch # User Daniele Nicolodi # Date 1313930480 -7200 # Node ID 8cf9fc08d43ddef03a9461ffcfb41ede1d53043b # Parent f2d3c99d6052e8ecc18ad414ab8cb74a6a7e90fe Rework named query support. diff -r f2d3c99d6052 -r 8cf9fc08d43d src/ltpdarepo/__init__.py --- a/src/ltpdarepo/__init__.py Mon Aug 15 20:08:25 2011 +0200 +++ b/src/ltpdarepo/__init__.py Sun Aug 21 14:41:20 2011 +0200 @@ -132,7 +132,7 @@ app.register_blueprint(module, url_prefix='/manage/databases') from .views.queries import module -app.register_blueprint(module, url_prefix='/manage/databases') +app.register_blueprint(module, url_prefix='/manage/queries') from .views.users import module app.register_blueprint(module, url_prefix='/manage/users') diff -r f2d3c99d6052 -r 8cf9fc08d43d src/ltpdarepo/install.py --- a/src/ltpdarepo/install.py Mon Aug 15 20:08:25 2011 +0200 +++ b/src/ltpdarepo/install.py Sun Aug 21 14:41:20 2011 +0200 @@ -38,9 +38,11 @@ ) ENGINE=MyISAM DEFAULT CHARSET=utf8""") curs.execute("""CREATE TABLE `queries` ( - `name` text, - `db` text, - `querystring` text + `id` int NOT NULL AUTO_INCREMENT, + `title` text NOT NULL, + `db` text NOT NULL, + `query` text NOT NULL, + PRIMARY KEY (`id`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8""") curs.execute("""CREATE TABLE `options` ( diff -r f2d3c99d6052 -r 8cf9fc08d43d src/ltpdarepo/query.py --- a/src/ltpdarepo/query.py Mon Aug 15 20:08:25 2011 +0200 +++ b/src/ltpdarepo/query.py Sun Aug 21 14:41:20 2011 +0200 @@ -1,5 +1,6 @@ +import re + from flask import g - from MySQLdb.cursors import DictCursor from ltpdarepo.form import Form @@ -7,37 +8,58 @@ from wtforms import validators from wtforms.validators import ValidationError + class IQuery(Form): query = HiddenField() - database = HiddenField() - name = TextField("Name", validators=[validators.Required()]) + title = TextField(validators=[validators.Required()]) + db = TextField('Databases', + description='Databaseses to which the query may be applied. ' + 'Comma separated list or regular expression', + validators=[validators.Required()]) - def validate_name(form, field): - curs = g.db.cursor() - query = Query.load(database=form.database.data, name=field.data) - if query is not None: - raise ValidationError(u"Query with this name already exists.") - + def validate_db(form, field): + # automagically transform comma separated list into regular expression + if not re.match(r'\^\(.*\)\$', field.data): + field.data = '^(' + '|'.join(v.strip() for v in field.data.split(',')) + ')$' + # validate regular expression + try: + re.compile(field.data) + except re.error, error: + raise ValidationError('Invalid regular expression: ' + str(error)) + class Query(dict): - def __init__(self, name=None, database=None, query=None): - super(Query, self).__init__(name=name, database=database, query=query) + def __init__(self, title=None, db=None, query=None): + super(Query, self).__init__(title=title, db=db, query=query) self.__dict__ = self - def create(self): - curs = g.db.cursor() - curs.execute("""INSERT INTO queries (name, db, querystring) - VALUES (%(name)s, %(database)s, %(query)s)""", dict(self)) - g.db.commit() - @staticmethod - def load(database, name): + def load(id): curs = g.db.cursor(DictCursor) - curs.execute("""SELECT querystring - FROM queries WHERE db=%s and name=%s""", (database, name)) + curs.execute("""SELECT id, title, db, query + FROM queries WHERE id=%s""", (id, )) query = curs.fetchone() if query is None: return None obj = Query() obj.update(query) return obj + + def create(self): + curs = g.db.cursor() + curs.execute("""INSERT INTO queries (title, db, query) + VALUES (%(title)s, %(db)s, %(query)s)""", dict(self)) + g.db.commit() + return curs.lastrowid + + def save(self): + curs = g.db.cursor() + curs.execute("""UPDATE queries + SET title=%(title)s, db=%(db)s, query=%(query)s + WHERE id=%(id)s""", dict(self)) + g.db.commit() + + def drop(self): + curs = g.db.cursor() + curs.execute("""DELETE FROM queries WHERE id=%s""", self.id) + g.db.commit() diff -r f2d3c99d6052 -r 8cf9fc08d43d src/ltpdarepo/static/style.css --- a/src/ltpdarepo/static/style.css Mon Aug 15 20:08:25 2011 +0200 +++ b/src/ltpdarepo/static/style.css Sun Aug 21 14:41:20 2011 +0200 @@ -12,7 +12,7 @@ color: #000; } -pre { +tt, pre { font-family: andale mono, monospace; } diff -r f2d3c99d6052 -r 8cf9fc08d43d src/ltpdarepo/templates/database.html --- a/src/ltpdarepo/templates/database.html Mon Aug 15 20:08:25 2011 +0200 +++ b/src/ltpdarepo/templates/database.html Sun Aug 21 14:41:20 2011 +0200 @@ -16,12 +16,12 @@ {% if queries %} -

Saved queries

-

Saved queries:

-
    - {% for name in queries %} -
  1. {{ name }}
    2. +

    Named queries

    +

    Named queries that apply to this database:

    +
+ {% endif %} {% endblock %} diff -r f2d3c99d6052 -r 8cf9fc08d43d src/ltpdarepo/templates/databases/view.html --- a/src/ltpdarepo/templates/databases/view.html Mon Aug 15 20:08:25 2011 +0200 +++ b/src/ltpdarepo/templates/databases/view.html Sun Aug 21 14:41:20 2011 +0200 @@ -7,7 +7,6 @@ {% endblock %} diff -r f2d3c99d6052 -r 8cf9fc08d43d src/ltpdarepo/templates/forms.html --- a/src/ltpdarepo/templates/forms.html Mon Aug 15 20:08:25 2011 +0200 +++ b/src/ltpdarepo/templates/forms.html Sun Aug 21 14:41:20 2011 +0200 @@ -4,15 +4,19 @@ {% for field in form if field.type not in ('SubmitField') %} {{ render_form_field(field) }} {% endfor %} + {% for field in form if field.type in ('SubmitField') %} + {{ render_form_field(field) }} + {% else %}
+ {% endfor %} {%- endmacro %} {% macro render_form_field(field) %} - {% if field.type == "HiddenField" %} + {% if field.type in ('HiddenField', 'SubmitField') %} {{ field }} {% else %} {% if field.errors %} diff -r f2d3c99d6052 -r 8cf9fc08d43d src/ltpdarepo/templates/index.html --- a/src/ltpdarepo/templates/index.html Mon Aug 15 20:08:25 2011 +0200 +++ b/src/ltpdarepo/templates/index.html Sun Aug 21 14:41:20 2011 +0200 @@ -16,6 +16,7 @@
  • Create database
  • Users
  • New user
    • +
  • Named queries
    • {% endif %} {% endblock %} diff -r f2d3c99d6052 -r 8cf9fc08d43d src/ltpdarepo/templates/namedquery.html --- a/src/ltpdarepo/templates/namedquery.html Mon Aug 15 20:08:25 2011 +0200 +++ b/src/ltpdarepo/templates/namedquery.html Sun Aug 21 14:41:20 2011 +0200 @@ -1,7 +1,3 @@ {% extends "objs.html" %} - -{% block above %} -
    - 
    {{ query }}
    -
    -{% endblock %} +{% block title %}{{ query.title }} — {{ database.id }}{% endblock %} +{% block pagetitle %}{{ query.title }} — Database «{{ database.id }}»{% endblock %} diff -r f2d3c99d6052 -r 8cf9fc08d43d src/ltpdarepo/templates/objs.html --- a/src/ltpdarepo/templates/objs.html Mon Aug 15 20:08:25 2011 +0200 +++ b/src/ltpdarepo/templates/objs.html Sun Aug 21 14:41:20 2011 +0200 @@ -2,7 +2,7 @@ {% extends "layout.html" %} {% block title %}{{ database.id }}{% endblock %} {% block body %} -

    Database «{{ database.id }}»

    +

    {% block pagetitle %}Database «{{ database.id }}»{% endblock %}

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

    {{ batch.count }} objects

    diff -r f2d3c99d6052 -r 8cf9fc08d43d src/ltpdarepo/templates/queries/create.html --- a/src/ltpdarepo/templates/queries/create.html Mon Aug 15 20:08:25 2011 +0200 +++ b/src/ltpdarepo/templates/queries/create.html Sun Aug 21 14:41:20 2011 +0200 @@ -2,8 +2,6 @@ {% extends "layout.html" %} {% block title %}Save query{% endblock %} {% block body %} -

    Query for database «{{ database.id }}»

    -{#

    {{ database.description }}

    #} -
    {{ query }}
    +

    Create named query

    {{ forms.render(form) }} {% endblock %} diff -r f2d3c99d6052 -r 8cf9fc08d43d src/ltpdarepo/templates/queries/drop.html --- /dev/null Thu Jan 01 00:00:00 1970 +0000 +++ b/src/ltpdarepo/templates/queries/drop.html Sun Aug 21 14:41:20 2011 +0200 @@ -0,0 +1,8 @@ +{% import 'forms.html' as forms %} +{% extends "layout.html" %} +{% block title %}Drop query{% endblock %} +{% block body %} +

    Drop query «{{ query.title }}»

    +

    Are you sure you want to drop this named query?

    +{{ forms.render(form) }} +{% endblock %} diff -r f2d3c99d6052 -r 8cf9fc08d43d src/ltpdarepo/templates/queries/edit.html --- /dev/null Thu Jan 01 00:00:00 1970 +0000 +++ b/src/ltpdarepo/templates/queries/edit.html Sun Aug 21 14:41:20 2011 +0200 @@ -0,0 +1,7 @@ +{% import 'forms.html' as forms %} +{% extends "layout.html" %} +{% block title %}Edit query{% endblock %} +{% block body %} +

    Edit named query

    +{{ forms.render(form) }} +{% endblock %} diff -r f2d3c99d6052 -r 8cf9fc08d43d src/ltpdarepo/templates/queries/index.html --- a/src/ltpdarepo/templates/queries/index.html Mon Aug 15 20:08:25 2011 +0200 +++ b/src/ltpdarepo/templates/queries/index.html Sun Aug 21 14:41:20 2011 +0200 @@ -2,10 +2,16 @@ {% block title %} Queries {% endblock %} {% block body %}

    Queries

    -

    Queries for database «{{ database }}»

    +

    Manage named queries:

    {% endblock %} diff -r f2d3c99d6052 -r 8cf9fc08d43d src/ltpdarepo/templates/queries/view.html --- /dev/null Thu Jan 01 00:00:00 1970 +0000 +++ b/src/ltpdarepo/templates/queries/view.html Sun Aug 21 14:41:20 2011 +0200 @@ -0,0 +1,11 @@ +{% extends "layout.html" %} +{% block title %}{{ query.title }}{% endblock %} +{% block body %} +

    Query «{{ query.title }}»

    +

    Database: {{ query.db }}

    +

    Criteria: {{ query.query }}

    + +{% endblock %} diff -r f2d3c99d6052 -r 8cf9fc08d43d src/ltpdarepo/templates/query.html --- a/src/ltpdarepo/templates/query.html Mon Aug 15 20:08:25 2011 +0200 +++ b/src/ltpdarepo/templates/query.html Sun Aug 21 14:41:20 2011 +0200 @@ -12,12 +12,16 @@ $("#save").live('click', function () { var query = $(".query form").serialize(); var form = $(document.createElement('form')).attr({ - 'action': '{{ url_for('manage.queries.create', database=database.id) }}', + 'action': '{{ url_for('manage.queries.create') }}', 'method': 'POST' }); form.append($(document.createElement('input')).attr({ 'type': 'hidden', 'name': 'query', 'value': query })); + form.append($(document.createElement('input')).attr({ + 'type': 'hidden', + 'name': 'db', + 'value': '{{ database.id }}' })); $("body").append(form); form.submit(); form.remove(); diff -r f2d3c99d6052 -r 8cf9fc08d43d src/ltpdarepo/upgrade.py --- a/src/ltpdarepo/upgrade.py Mon Aug 15 20:08:25 2011 +0200 +++ b/src/ltpdarepo/upgrade.py Sun Aug 21 14:41:20 2011 +0200 @@ -96,7 +96,10 @@ # crerate "queries" table curs.execute("""CREATE TABLE IF NOT EXISTS - queries (name TEXT, db TEXT, querystring TEXT)""") + queries (id INT NOT NULL AUTO_INCREMENT, + title TEXT NOT NULL, + db TEXT NOT NULL, + query TEXT NOT NULL) CHARSET=utf8""") # for each registered database curs.execute("SELECT db_name FROM available_dbs") diff -r f2d3c99d6052 -r 8cf9fc08d43d src/ltpdarepo/views/browse.py --- a/src/ltpdarepo/views/browse.py Mon Aug 15 20:08:25 2011 +0200 +++ b/src/ltpdarepo/views/browse.py Sun Aug 21 14:41:20 2011 +0200 @@ -8,6 +8,7 @@ from ltpdarepo.security import require, view from ltpdarepo.database import Database +from ltpdarepo.query import Query from ltpdarepo.pagination import Pagination try: @@ -149,9 +150,9 @@ if db is None: # not found abort(404) - curs = g.db.cursor() - curs.execute("""SELECT name FROM queries WHERE db=%s""", database) - queries = [row[0] for row in curs.fetchall()] + curs = g.db.cursor(DictCursor) + curs.execute("""SELECT id, title, db FROM queries WHERE %s RLIKE db""", database) + queries = curs.fetchall() return render_template('database.html', database=db, queries=queries) @@ -340,20 +341,19 @@ criteria=criteria, database=db, fields=FIELDS, batch=batch) -@app.route('//query/') +@app.route('//query/') @require('user') -def namedquery(database, name): +def namedquery(database, id): 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]) + query = Query.load(id) + params = json.loads(query.query) fields, ops, values = [], [], [] - for q in query: + for q in params: fields.append(q[0]) ops.append(q[1]) values.append(q[2]) @@ -363,14 +363,15 @@ where = ' AND '.join(q) # simple string representation of the query - querystring = ' AND '.join("%s %s %s" % tuple(v) for v in query) + querystring = ' AND '.join("%s %s %s" % tuple(v) for v in params) # 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()).limit(*batch.limits).all() - return render_template('namedquery.html', objs=objs, database=db, fields=FIELDS, batch=batch, query=querystring) + return render_template('namedquery.html', objs=objs, database=db, fields=FIELDS, batch=batch, + query=query, querystring=querystring) class Timeseries(Objs): diff -r f2d3c99d6052 -r 8cf9fc08d43d src/ltpdarepo/views/queries.py --- a/src/ltpdarepo/views/queries.py Mon Aug 15 20:08:25 2011 +0200 +++ b/src/ltpdarepo/views/queries.py Sun Aug 21 14:41:20 2011 +0200 @@ -1,4 +1,6 @@ -from flask import Blueprint, render_template, g, abort, redirect, request, json, session, url_for +from flask import Blueprint, render_template, g, abort, redirect, request, json, session, url_for, flash + +from MySQLdb.cursors import DictCursor from ltpdarepo.security import require from ltpdarepo.query import Query, IQuery @@ -9,34 +11,29 @@ from .browse import MAPPING, RMAPPING -@app.route('//queries/') +@app.route('/') @require('admin') -def index(database): - 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('queries/index.html', queries=queries, database=database) +def index(): + curs = g.db.cursor(DictCursor) + curs.execute("""SELECT id, title FROM queries""") + queries = curs.fetchall() + return render_template('queries/index.html', queries=queries) -@app.route('//queries/') +@app.route('//') @require('admin') -def view(database, query): - query = Query.load(database=database, name=query) +def view(id): + query = Query.load(id=id) if query is None: # not found abort(404) - return query.querystring + return render_template('queries/view.html', query=query) -@app.route('//queries/+', methods=['GET', 'POST']) +@app.route('/+', methods=['GET', 'POST']) @require('admin') -def create(database): - db = Database.load(database) - if db is None: - # not found - abort(404) - - if 'name' not in request.values: +def create(): + if 'title' not in request.values: from urlparse import parse_qs parsed = parse_qs(request.values['query'], keep_blank_values=True, strict_parsing=True) fields = parsed['field'] @@ -49,18 +46,57 @@ form = IQuery() form.query.data = query - form.database.data = database form.csrf.data = session['_token'] - return render_template('queries/create.html', form=form, query=querystring, database=db) + return render_template('queries/create.html', form=form) form = IQuery() if request.method == 'POST' and form.validate(): query = Query() form.update(query) query.create() - return redirect(url_for('.index', database=database)) + return redirect(url_for('.index')) return render_template('queries/create.html', form=form, query='') +@app.route('//edit', methods=['GET', 'POST']) +@require('admin') +def edit(id): + query = Query.load(id) + if query is None: + # not found + abort(404) + form = IQuery(obj=query) + if request.method == 'POST' and form.validate(): + form.update(query) + query.save() + return redirect(url_for('.index')) + return render_template('queries/edit.html', form=form) + + +from ltpdarepo.form import Form +from wtforms.fields import SubmitField +class IConfirm(Form): + ok = SubmitField() + cancel = SubmitField() + + +@app.route('//drop', methods=['GET', 'POST']) +@require('admin') +def drop(id): + query = Query.load(id) + if query is None: + # not found + abort(404) + form = IConfirm() + if request.method == 'POST' and form.validate(): + if request.form.get('ok'): + query.drop() + flash('Named query deleted.') + return redirect(url_for('manage.queries.index')) + flash('Operation cancelled.') + return redirect(url_for('manage.queries.index')) + return render_template('queries/drop.html', form=form, query=query) + + module = app