X-Git-Url: https://git.toastfreeware.priv.at/philipp/winterrodeln/wrfeed.git/blobdiff_plain/721d10a185cf986d8de3074c2b91e617e244ebf5..b33b034f12eced31219f19b2254f9bb78c03cf79:/wrfeed/berichte.py diff --git a/wrfeed/berichte.py b/wrfeed/berichte.py index 34cbb9d..29425cf 100644 --- a/wrfeed/berichte.py +++ b/wrfeed/berichte.py @@ -13,6 +13,7 @@ See: import datetime from xml.etree.ElementTree import Element, SubElement, tostring from flask import url_for +from sqlalchemy import func, desc class CreateFeedError(RuntimeError): @@ -23,48 +24,52 @@ class RegionNotFoundError(CreateFeedError): pass -def create_feed(conn, limit, page_title=None, page_ids=None, region_name=None): +def create_feed(db, limit, page_title=None, page_ids=None, region_name=None): """If a page_title is given, only the reports for the given sledrun are shown. If a list of page_ids is given, only the reports for the selected pages are shown. If a region name (lower case) is given, the reports just for this region are shown. Otherwise, all reports are shown.""" - 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: + wrreport = db.metadata.tables['wrreport'] + wrsledruncache = db.metadata.tables['wrsledruncache'] + wrregion = db.metadata.tables['wrregion'] + wrregioncache = db.metadata.tables['wrregioncache'] + + sql = db.select([ + wrreport, + wrsledruncache.c.position_longitude.label('lon'), + wrsledruncache.c.position_latitude.label('lat') + ]). \ + select_from(wrreport.outerjoin(wrsledruncache, wrreport.c.page_id == wrsledruncache.c.page_id)). \ + where(wrreport.c.date_invalid > func.now()). \ + where(wrreport.c.delete_date.is_(None)). \ + order_by(desc(wrreport.c.id)). \ + limit(limit) + + if page_title is not None: # page_title is given page_title = page_title.replace('_', ' ') - where += ' and lcase(wrreport.page_title) = lcase(%s)' - params += [page_title] - elif not page_ids is None: + sql = sql.where(func.lcase(wrreport.c.page_title) == func.lcase(page_title)) + elif page_ids is not None: # a list of page_ids is given - assert len(page_ids) > 0 - where += ' and (' - where += " or ".join(['wrreport.page_id=%s' for page_id in page_ids]) - where += ')' - params += list(map(str, page_ids)) - elif not region_name is None: + sql = sql.where(wrreport.c.page_id.in_(page_ids)) + elif region_name is not None: # a name of a region is given # (1) find out whether the region exists - subselect = 'select aswkb(border) as border_wkb from wrregion where name=lcase(%s)' - subresult = conn.execute(subselect, region_name) - if subresult.rowcount == 0: + sub_sql = db.select([wrregion]).where(wrregion.c.name == func.lcase(region_name)) + sub_result = db.session.connection().execute(sub_sql) + if sub_result.rowcount == 0: # no region with such a name raise RegionNotFoundError(region_name) - assert subresult.rowcount == 1 - row = subresult.fetchone() - # (2) now we have the border - border_wkb = row['border_wkb'] # border as WKB - where += ' and contains(geomfromwkb(%s), point(position_longitude, position_latitude))' - params += [border_wkb] + assert sub_result.rowcount == 1 + sub_row = sub_result.fetchone() + region_id = sub_row.id + # (2) now we have the id of the region + sql = sql.where(wrreport.c.page_id.in_(db.select([wrregioncache.c.page_id]).where(region_id == wrregioncache.c.region_id))) else: # user wants to have all reports pass - sql = ' '.join([select, where, order, limit]) - result = conn.execute(sql, *params) + result = db.session.connection().execute(sql) 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") @@ -83,18 +88,16 @@ def create_feed(conn, limit, page_title=None, page_ids=None, region_name=None): last_updated = None for row in result: - page_id, page_title, report_id, date_report, date_entry, condition, description, author_name, author_userid, author_username, lon, lat = row - - page_title_url = page_title.replace(' ', '_') + page_title_url = row.page_title.replace(' ', '_') entry = SubElement(feed, "entry") entry_title = SubElement(entry, "title") - entry_title.text = page_title + entry_title.text = row.page_title entry.append(Element("link", rel="alternate", href="http://www.winterrodeln.org/wiki/{0}".format(page_title_url), type="text/html", hreflang="de")) entry_id = SubElement(entry, "id") - entry_id.text = "http://www.winterrodeln.org/wiki/{0}#{1}".format(page_title_url, report_id) + entry_id.text = "http://www.winterrodeln.org/wiki/{0}#{1}".format(page_title_url, row.id) entry_updated = SubElement(entry, "updated") - entry_updated.text = date_entry.isoformat() + "+01:00" - if last_updated is None: last_updated = date_entry + entry_updated.text = row.date_entry.isoformat() + "+01:00" + if last_updated is None: last_updated = row.date_entry # entry_summary = SubElement(entry, "summary") # entry_summary.text = str(condition) entry_content = SubElement(entry, "content") @@ -102,33 +105,36 @@ def create_feed(conn, limit, page_title=None, page_ids=None, region_name=None): entry_content_div = SubElement(entry_content, "div") entry_content_div.attrib["xmlns"] = "http://www.w3.org/1999/xhtml" entry_content_ul = SubElement(entry_content_div, "ul") - if not date_report is None: + if not row.date_report is None: entry_content_date = SubElement(entry_content_ul, "li") - entry_content_date.text = "Bericht für " + date_report.isoformat() - if not condition is None: + entry_content_date.text = "Bericht für " + row.date_report.isoformat() + if not row.condition is None: entry_content_condition = SubElement(entry_content_ul, "li") - entry_content_condition.text = "Schneelage: " + {1: 'Sehr gut', 2: 'Gut', 3: 'Mittelmäßig', 4: 'Schlecht', 5: 'Geht nicht'}[condition] + entry_content_condition.text = "Schneelage: " + {1: 'Sehr gut', 2: 'Gut', 3: 'Mittelmäßig', 4: 'Schlecht', 5: 'Geht nicht'}[row.condition] entry_content_description = SubElement(entry_content_ul, "li") - entry_content_description.text = description + entry_content_description.text = row.description entry_author = SubElement(entry, "author") entry_author_name = SubElement(entry_author, "name") - if author_name is None or len(author_name.strip()) == 0: entry_author_name.text = "Anonymous user" - else: entry_author_name.text = author_name - if not lon is None and not lat is None: + if row.author_name is None or len(row.author_name.strip()) == 0: + entry_author_name.text = "Anonymous user" + else: + entry_author_name.text = row.author_name + if not row.lon is None and not row.lat is None: entry_geo = SubElement(entry, "georss:point") - entry_geo.text = "{lat} {lon}".format(lat=lat, lon=lon) + entry_geo.text = "{lat} {lon}".format(lat=row.lat, lon=row.lon) entry_wrreport = SubElement(entry, "wr:report") - entry_wrreport.attrib['report_id'] = str(report_id) - entry_wrreport.attrib['page_id'] = str(page_id) - entry_wrreport.attrib['page_title'] = page_title - entry_wrreport.attrib['date_report'] = str(date_report) - entry_wrreport.attrib['date_entry'] = str(date_entry) - entry_wrreport.attrib['condition'] = "0" if condition is None else str(condition) - entry_wrreport.attrib['author_name'] = author_name - entry_wrreport.attrib['author_username'] = "" if author_userid is None else author_username - entry_wrreport.text = description + entry_wrreport.attrib['report_id'] = str(row.id) + entry_wrreport.attrib['page_id'] = str(row.page_id) + entry_wrreport.attrib['page_title'] = row.page_title + entry_wrreport.attrib['date_report'] = str(row.date_report) + entry_wrreport.attrib['date_entry'] = str(row.date_entry) + entry_wrreport.attrib['condition'] = "0" if row.condition is None else str(row.condition) + entry_wrreport.attrib['author_name'] = row.author_name + entry_wrreport.attrib['author_username'] = "" if row.author_userid is None else row.author_username + entry_wrreport.text = row.description - if last_updated is None: last_updated = datetime.datetime.now() + if last_updated is None: + last_updated = datetime.datetime.now() feed_updated.text = last_updated.isoformat() + "+01:00" return b'\n' + tostring(feed)