Now border is not used anymore to select sledruns for regions.

git-svn-id: http://www.winterrodeln.org/svn/wrfeed/trunk@2800 7aebc617-e5e2-0310-91dc-80fb5f6d2477

diff --git a/setup.py b/setup.py
index 35cfb75d46d496458e5f15d181e1fd3a16a3fec7..4afae8e2dfcbf90e03fee09819a0cb3fd26ef25f 100644 (file)
--- a/setup.py
+++ b/setup.py
@@ -10,6 +10,7 @@ setup(
     install_requires=[
         "flask>=0.12",
         "SQLAlchemy>=0.5",
     install_requires=[
         "flask>=0.12",
         "SQLAlchemy>=0.5",

+        "flask_sqlalchemy",

     ],
     packages=['wrfeed'],
     include_package_data=True,
     ],
     packages=['wrfeed'],
     include_package_data=True,

diff --git a/wrfeed/berichte.py b/wrfeed/berichte.py
index 34cbb9d89e8fe8737741beb11db9efc41d459f19..29425cf3df2953e8831d5a2cea80eefb497d2955 100644 (file)
--- 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
 import datetime
 from xml.etree.ElementTree import Element, SubElement, tostring
 from flask import url_for

+from sqlalchemy import func, desc

 
 
 class CreateFeedError(RuntimeError):
 
 
 class CreateFeedError(RuntimeError):


@@ -23,48 +24,52 @@ class RegionNotFoundError(CreateFeedError):
     pass
 
 
     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."""
     """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('_', ' ')
         # 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
         # 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
         # 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)
             # 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
     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")
 
     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:
 
     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 = 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.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 = 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")
         # 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")
         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 = 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 = 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 = 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")
         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 = 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 = 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'<?xml version="1.0" encoding="utf-8"?>\n' + tostring(feed)
     feed_updated.text = last_updated.isoformat() + "+01:00"
 
     return b'<?xml version="1.0" encoding="utf-8"?>\n' + tostring(feed)

diff --git a/wrfeed/wrfeed.py b/wrfeed/wrfeed.py
index 9f0cf1124e0e1702bad6c269d395912e5efb2ffc..d160c33e5401b10bb122723c7c83fe85dadabcb8 100644 (file)
--- a/wrfeed/wrfeed.py
+++ b/wrfeed/wrfeed.py
@@ -1,29 +1,14 @@
 # http://flask.pocoo.org/
 # FLASK_APP=wrfeed FLASK_DEBUG=1 WRFEED_SETTINGS=development.cfg python3 -m flask run -p 5002
 # FLASK_APP=wrfeed WRFEED_SETTINGS=production.cfg python3 -m flask run
 # http://flask.pocoo.org/
 # FLASK_APP=wrfeed FLASK_DEBUG=1 WRFEED_SETTINGS=development.cfg python3 -m flask run -p 5002
 # FLASK_APP=wrfeed WRFEED_SETTINGS=production.cfg python3 -m flask run

-from flask import Flask, send_from_directory, abort, g
-from sqlalchemy.engine import create_engine
+from flask import Flask, send_from_directory, abort
+from flask_sqlalchemy import SQLAlchemy

 from wrfeed.berichte import create_feed
 
 app = Flask(__name__)
 app.config.from_envvar('WRFEED_SETTINGS')
 from wrfeed.berichte import create_feed
 
 app = Flask(__name__)
 app.config.from_envvar('WRFEED_SETTINGS')

-
-
-def get_db():
-    """Opens a new database connection if there is none yet for the
-    current application context.
-    """
-    if not hasattr(g, 'db'):
-        g.db_engine = create_engine(app.config['DATABASE_URI'])
-        g.db = g.db_engine.connect()
-    return g.db
-
-
-@app.teardown_appcontext
-def close_db(error):
-    """Closes the database again at the end of the request."""
-    if hasattr(g, 'db'):
-        g.db.close()
+db = SQLAlchemy(app)
+db.reflect()

 
 
 @app.route("/")
 
 
 @app.route("/")


@@ -37,7 +22,7 @@ def alle():
     http://127.0.0.1:5000/berichte/alle
     http://www.winterrodeln.org/feed/berichte/alle
     """
     http://127.0.0.1:5000/berichte/alle
     http://www.winterrodeln.org/feed/berichte/alle
     """

-    return app.response_class(create_feed(get_db(), app.config['FEEDENTRYLIMIT']), mimetype='application/atom+xml')
+    return app.response_class(create_feed(db, app.config['FEEDENTRYLIMIT']), mimetype='application/atom+xml')

 
 
 @app.route("/berichte/bahn/<bahn>")
 
 
 @app.route("/berichte/bahn/<bahn>")


@@ -46,7 +31,7 @@ def bahn(bahn):
     http://127.0.0.1:5000/berichte/bahn/kemater_alm
     http://www.winterrodeln.org/feed/berichte/bahn/kemater_alm
     """
     http://127.0.0.1:5000/berichte/bahn/kemater_alm
     http://www.winterrodeln.org/feed/berichte/bahn/kemater_alm
     """

-    return app.response_class(create_feed(get_db(), app.config['FEEDENTRYLIMIT'], page_title=bahn),
+    return app.response_class(create_feed(db, app.config['FEEDENTRYLIMIT'], page_title=bahn),

                               mimetype='application/atom+xml')
 
 
                               mimetype='application/atom+xml')
 
 


@@ -61,7 +46,7 @@ def bahnen(bahnen):
         page_ids = [int(page_id) for page_id in page_ids]
     except ValueError:
         abort(400) # bad request
         page_ids = [int(page_id) for page_id in page_ids]
     except ValueError:
         abort(400) # bad request

-    return app.response_class(create_feed(get_db(), app.config['FEEDENTRYLIMIT'], page_ids=page_ids),
+    return app.response_class(create_feed(db, app.config['FEEDENTRYLIMIT'], page_ids=page_ids),

                               mimetype='application/atom+xml')
 
 
                               mimetype='application/atom+xml')
 
 


@@ -70,5 +55,5 @@ def region(region):
     """Handles URLs like
     http://www.winterrodeln.org/feed/berichte/region/osttirol
     """
     """Handles URLs like
     http://www.winterrodeln.org/feed/berichte/region/osttirol
     """

-    return app.response_class(create_feed(get_db(), app.config['FEEDENTRYLIMIT'], region_name=region),
+    return app.response_class(create_feed(db, app.config['FEEDENTRYLIMIT'], region_name=region),

                               mimetype='application/atom+xml')
                               mimetype='application/atom+xml')