2 # -*- coding: iso-8859-15 -*-
5 """Contains functions that maintain/update the cache tables."""
6 from sqlalchemy import schema
7 from sqlalchemy.sql import select
9 from wrpylib import mwdb, wrmwdb, mwmarkup, wrmwmarkup
12 class UpdateCacheError(RuntimeError):
16 def update_wrsledruncache(connection):
17 """Updates the wrsledruncache table from the wiki. If convert errors occur, an UpdateCacheError exception
18 is raised. No other exception type should be raised under normal circumstances.
20 >>> from sqlalchemy.engine import create_engine
21 >>> engine = create_engine('mysql://philipp@localhost:3306/philipp_winterrodeln_wiki?charset=utf8&use_unicode=1')
22 >>> update_wrsledruncache(engine.connect())
24 metadata = schema.MetaData()
25 wrsledruncache = wrmwdb.wrsledruncache_table(metadata)
26 page = mwdb.page_table(metadata)
27 categorylinks = mwdb.categorylinks_table(metadata)
28 revision = mwdb.revision_table(metadata)
29 text = mwdb.text_table(metadata)
31 transaction = connection.begin()
34 q = select([page, categorylinks, revision, text], (page.c.page_latest==revision.c.rev_id) & (text.c.old_id==revision.c.rev_text_id) & (categorylinks.c.cl_from==page.c.page_id) & (categorylinks.c.cl_to==u'Rodelbahn'))
35 sledrun_pages = connection.execute(q)
37 # sql = u"select page_id, rev_id, old_id, page_title, old_text, 'In_Arbeit' in (select cl_to from categorylinks where cl_from=page_id) as under_construction from page, revision, text, categorylinks where page_latest=rev_id and old_id=rev_text_id and cl_from=page_id and cl_to='Rodelbahn' order by page_title"
39 # Delete all existing entries in wrsledruncache
40 # We rely on transactions MySQL InnoDB
41 connection.execute(wrsledruncache.delete())
43 # Refill wrsledruncache table
44 for sledrun_page in sledrun_pages:
46 start, end, sledrun = wrmwmarkup.rodelbahnbox_to_sledrun(sledrun_page.old_text)
47 sledrun.page_id = sledrun_page.page_id
48 sledrun.page_title = sledrun_page.page_title
49 sledrun.under_construction = connection.execute(select([categorylinks], (categorylinks.c.cl_from==sledrun_page.page_id) & (categorylinks.c.cl_to == u'In_Arbeit')).alias('x').count()).fetchone()[0] > 0 # It would be better to do this in the query above
50 connection.execute(wrsledruncache.insert(sledrun.__dict__))
51 except (RuntimeError, formencode.Invalid) as e:
52 transaction.rollback()
53 error_msg = u"Error at sled run '{0}': {1}".format(sledrun_page.page_title, unicode(e))
54 raise UpdateCacheError(error_msg, sledrun_page.page_title, e)
58 def update_wrinncache(connection):
59 """Updates the wrinncache table from the wiki. If convert errors occur, an UpdateCacheError exception
60 is raised. No other exception type should be raised under normal circumstances.
62 >>> from sqlalchemy.engine import create_engine
63 >>> engine = create_engine('mysql://philipp@localhost:3306/philipp_winterrodeln_wiki?charset=utf8&use_unicode=1')
64 >>> update_wrinncache(engine.connect())
66 metadata = schema.MetaData()
67 wrinncache = wrmwdb.wrinncache_table(metadata)
68 page = mwdb.page_table(metadata)
69 categorylinks = mwdb.categorylinks_table(metadata)
70 revision = mwdb.revision_table(metadata)
71 text = mwdb.text_table(metadata)
73 transaction = connection.begin()
76 q = select([page, categorylinks, revision, text], (page.c.page_latest==revision.c.rev_id) & (text.c.old_id==revision.c.rev_text_id) & (categorylinks.c.cl_from==page.c.page_id) & (categorylinks.c.cl_to==u'Gasthaus'))
77 inn_pages = connection.execute(q)
79 # Delete all existing entries in wrinncache
80 # We rely on transactions MySQL InnoDB
81 connection.execute(wrinncache.delete())
83 # Refill wrinncache table
84 for inn_page in inn_pages:
86 start, end, inn = wrmwmarkup.gasthausbox_to_inn(inn_page.old_text)
87 inn.page_id = inn_page.page_id
88 inn.page_title = inn_page.page_title
89 inn.under_construction = connection.execute(select([categorylinks], (categorylinks.c.cl_from==inn_page.page_id) & (categorylinks.c.cl_to == u'In_Arbeit')).alias('x').count()).fetchone()[0] > 0 # It would be better to do this in the query above
90 connection.execute(wrinncache.insert(inn.__dict__))
91 except (RuntimeError, formencode.Invalid) as e:
92 transaction.rollback()
93 error_msg = u"Error as inn '{0}': {1}".format(inn_page.page_title, unicode(e))
94 raise UpdateCacheError(error_msg, inn_page.page_title, e)
98 def update_wrreportcache(connection, page_id=None):
99 """Updates the wrreportcache table.
100 :param connection: sqlalchemy connection
101 :param page_id: Updates only the reportcache table for the sledrun described on the Winterrodeln wiki page
102 with the specified page_id. Use None for this parameter to update the whole table.
104 >>> from sqlalchemy.engine import create_engine
105 >>> engine = create_engine('mysql://philipp@localhost:3306/philipp_winterrodeln_wiki?charset=utf8&use_unicode=1')
106 >>> update_wrreportcache(engine.connect())
108 metadata = schema.MetaData()
109 wrreport = wrmwdb.wrreport_table(metadata)
110 wrreportcache = wrmwdb.wrreportcache_table(metadata)
111 transaction = connection.begin()
113 # Delete the datasets we are going to update
114 sql_del = wrreportcache.delete()
115 if not page_id is None: sql_del = sql_del.where(wrreportcache.c.page_id == page_id)
116 connection.execute(sql_del)
118 def insert_row(connection, rowlist):
119 if len(rowlist) == 0: return
121 row = dict(rowlist[0])
122 connection.execute(wrreportcache.insert(values=row))
124 # Select the rows to update
125 sql = 'select page_id, page_title, wrreport.id as report_id, date_report, `condition`, description, author_name, if(author_userid is null, null, author_username) as author_username from wrreport where {0}`condition` is not null and date_invalid > now() and delete_date is null order by page_id, date_report desc, date_entry desc'.format('' if page_id is None else 'page_id={0} and '.format(page_id))
126 cursor = connection.execute(sql)
130 if row.page_id != page_id:
131 insert_row(connection, rowlist)
132 page_id = row.page_id
135 insert_row(connection, rowlist)
139 def update_wrmapcache(connection):
140 """Updates the wrmappointcache and wrmappathcache tables from the wiki. If convert errors occur, an UpdateCacheError exception
141 is raised. No other exception type should be raised under normal circumstances.
143 >>> from sqlalchemy.engine import create_engine
144 >>> engine = create_engine('mysql://philipp@localhost:3306/philipp_winterrodeln_wiki?charset=utf8&use_unicode=1')
145 >>> # or: engine = create_engine('mysql://philipp@localhost:3306/philipp_winterrodeln_wiki?charset=utf8&use_unicode=1&passwd=XXXXX')
146 >>> update_wrmapcache(engine.connect())
148 metadata = schema.MetaData()
149 page = mwdb.page_table(metadata)
150 categorylinks = mwdb.categorylinks_table(metadata)
151 revision = mwdb.revision_table(metadata)
152 text = mwdb.text_table(metadata)
154 transaction = connection.begin()
157 q = select([page, categorylinks, revision, text], (page.c.page_latest==revision.c.rev_id) & (text.c.old_id==revision.c.rev_text_id) & (categorylinks.c.cl_from==page.c.page_id) & (categorylinks.c.cl_to==u'Rodelbahn'))
158 sledrun_pages = connection.execute(q)
160 # sql = u"select page_id, rev_id, old_id, page_title, old_text, 'In_Arbeit' in (select cl_to from categorylinks where cl_from=page_id) as under_construction from page, revision, text, categorylinks where page_latest=rev_id and old_id=rev_text_id and cl_from=page_id and cl_to='Rodelbahn' order by page_title"
162 # Delete all existing entries in wrmappointcache
163 # We rely on transactions MySQL InnoDB
164 connection.execute('delete from wrmappointcache')
165 connection.execute('delete from wrmappathcache')
167 # Refill wrmappointcache and wrmappathcache tables
168 for sledrun_page in sledrun_pages:
170 start, content, endtag, end = mwmarkup.find_tag(sledrun_page.old_text, 'wrmap')
173 geojson = wrmwmarkup.parse_wrmap(sledrun_page.old_text[start:end])
175 for feature in geojson['features']:
176 properties = feature['properties']
177 coordinates = feature['geometry']['coordinates']
180 if properties['type'] in wrmwmarkup.WRMAP_POINT_TYPES:
181 lon, lat = coordinates
182 label = properties.get('name')
183 point_types = {u'gasthaus': u'hut', u'haltestelle': u'busstop', u'parkplatz': u'carpark', u'achtung': u'warning', u'punkt': u'point'}
184 point_type = point_types[properties['type']]
185 sql = u'insert into wrmappointcache (page_id, type, point, label) values (%s, %s, POINT(%s, %s), %s)'
186 connection.execute(sql, (sledrun_page.page_id, point_type, lon, lat, label))
189 elif properties['type'] in wrmwmarkup.WRMAP_LINE_TYPES:
190 path_types = {u'rodelbahn': u'sledrun', u'gehweg': u'walkup', u'alternative': u'alternative', u'lift': u'lift', u'anfahrt': u'recommendedcarroute', u'linie': u'line'}
191 path_type = path_types[properties['type']]
192 path = u", ".join(["{0} {1}".format(lon, lat) for lon, lat in coordinates])
193 path = u'LineString({0})'.format(path)
194 if path_type == u'recommendedcarroute': continue
195 sql = u'insert into wrmappathcache (path, page_id, type) values (GeomFromText(%s), %s, %s)'
196 connection.execute(sql, (path, sledrun_page.page_id, path_type))
199 raise RuntimeError(u'Unknown feature type {0}'.format(properties['type']))
200 except RuntimeError as e:
201 error_msg = u"Error at sledrun '{0}': {1}".format(sledrun_page.page_title, unicode(e))
202 transaction.rollback()
203 raise UpdateCacheError(error_msg, sledrun_page.page_title, e)