From 234a8c6b2ba79fd6e4586506076693a8d523cb02 Mon Sep 17 00:00:00 2001 From: philipp Date: Sun, 27 Oct 2013 20:09:37 +0000 Subject: [PATCH] Made SQL generation in function create_feed more flexible. git-svn-id: http://www.winterrodeln.org/svn/wrfeed/trunk@1758 7aebc617-e5e2-0310-91dc-80fb5f6d2477 --- wrfeed/controllers/berichte.py | 34 +++++++++++++++++++--------------- 1 file changed, 19 insertions(+), 15 deletions(-) diff --git a/wrfeed/controllers/berichte.py b/wrfeed/controllers/berichte.py index 0815056..7018a9d 100644 --- a/wrfeed/controllers/berichte.py +++ b/wrfeed/controllers/berichte.py @@ -19,6 +19,7 @@ from sqlalchemy.engine import create_engine import logging +import webob.exc from pylons import request, response, session, config, tmpl_context as c, url from pylons.controllers.util import abort, redirect @@ -36,27 +37,30 @@ def create_feed(page_title=None, page_ids=None): limit = int(config['feedentrylimit']) conn = engine.connect() - select = "select wrreport.page_id, wrreport.page_title, wrreport.id, date_report, date_entry, `condition`, description, author_name, author_userid, author_username, position_longitude, position_latitude from wrreport left outer join wrsledruncache on wrreport.page_id=wrsledruncache.page_id " + select = 'select wrreport.page_id, wrreport.page_title, wrreport.id, date_report, date_entry, `condition`, description, author_name, author_userid, author_username, position_longitude, position_latitude from wrreport left outer join wrsledruncache on wrreport.page_id=wrsledruncache.page_id' + where = 'where date_invalid > now() and delete_date is null' + order = 'order by id desc' + limit = 'limit {0}'.format(limit) + params = [] + if not page_title is None: # page_title is given page_title = page_title.replace('_', ' ') - sql = select + "where lcase(wrreport.page_title) = lcase(%s) and date_invalid > now() and delete_date is null order by id desc limit {0}".format(limit) - result = conn.execute(sql, page_title) + where += ' and lcase(wrreport.page_title) = lcase(%s)' + params += [page_title] elif not page_ids is None: # a list of page_ids is given - sql = [select + "where "] - if len(page_ids) > 0: - sql += '(' - sql += " or ".join(['wrreport.page_id=%s' for page_id in page_ids]) - sql += ') ' - sql += 'and date_invalid > now() and delete_date is null order by id desc limit {0}'.format(limit) - page_ids_str = [str(page_id) for page_id in page_ids] - result = conn.execute("".join(sql), *page_ids_str) + if len(page_ids) == 0: + raise webob.exc.HTTPBadRequest() + where += ' and (' + where += " or ".join(['wrreport.page_id=%s' for page_id in page_ids]) + where += ')' + params += map(str, page_ids) else: # user wants to have all reports - sql = select + "where date_invalid > now() and delete_date is null order by id desc limit {0}".format(limit) - result = conn.execute(sql) - + pass + sql = ' '.join([select, where, order, limit]) + result = conn.execute(sql, *params) feed = Element("feed", xmlns="http://www.w3.org/2005/Atom", attrib={'xmlns:georss': 'http://www.georss.org/georss', 'xmlns:wr': 'http://www.winterrodeln.org/schema/wrreport'}) feed_title = SubElement(feed, "title") @@ -65,7 +69,7 @@ def create_feed(page_title=None, page_ids=None): if not page_title is None: feed_id.text = url(qualified=True, controller='berichte', action='bahn', id=page_title) elif not page_ids is None: - feed_id.text = url(qualified=True, controller='berichte', action='bahnen', id="+".join(page_ids_str)) + feed_id.text = url(qualified=True, controller='berichte', action='bahnen', id="+".join(map(str, page_ids))) else: feed_id.text = url(qualified=True, controller='berichte', action='alle') feed_updated = SubElement(feed, "updated") -- 2.39.5