4 """Contains functions that maintain/update the cache tables."""
5 from sqlalchemy import schema
6 from sqlalchemy.sql import select
7 from sqlalchemy.sql.expression import func as sqlfunc
9 from wrpylib import mwdb, wrmwdb, mwmarkup, wrmwmarkup, wrvalidators
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)
33 transaction = connection.begin()
36 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=='Rodelbahn'))
37 sledrun_pages = connection.execute(q)
39 # 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"
41 # Delete all existing entries in wrsledruncache
42 # We rely on transactions MySQL InnoDB
43 connection.execute(wrsledruncache.delete())
45 # Refill wrsledruncache table
46 for sledrun_page in sledrun_pages:
48 rodelbahnbox = wrvalidators.rodelbahnbox_from_str(sledrun_page.old_text)
49 sledrun = wrmwmarkup.sledrun_from_rodelbahnbox(rodelbahnbox, Sledrun())
50 sledrun.page_id = sledrun_page.page_id
51 sledrun.page_title = sledrun_page.page_title
52 sledrun.name_url = wrvalidators.sledrun_page_title_to_pretty_url(sledrun_page.page_title)
53 sledrun.under_construction = connection.execute(select([categorylinks], (categorylinks.c.cl_from==sledrun_page.page_id) & (categorylinks.c.cl_to == 'In_Arbeit')).alias('x').count()).fetchone()[0] > 0
54 connection.execute(wrsledruncache.insert(sledrun.__dict__))
55 except ValueError as e:
56 transaction.rollback()
57 error_msg = "Error at sled run '{0}': {1}".format(sledrun_page.page_title, str(e))
58 raise UpdateCacheError(error_msg, sledrun_page.page_title, e)
62 def update_wrinncache(connection):
63 """Updates the wrinncache table from the wiki. If convert errors occur, an UpdateCacheError exception
64 is raised. No other exception type should be raised under normal circumstances.
66 >>> from sqlalchemy.engine import create_engine
67 >>> engine = create_engine('mysql://philipp@localhost:3306/philipp_winterrodeln_wiki?charset=utf8&use_unicode=1')
68 >>> update_wrinncache(engine.connect())
70 metadata = schema.MetaData()
71 wrinncache = wrmwdb.wrinncache_table(metadata)
72 page = mwdb.page_table(metadata)
73 categorylinks = mwdb.categorylinks_table(metadata)
74 revision = mwdb.revision_table(metadata)
75 text = mwdb.text_table(metadata)
79 transaction = connection.begin()
82 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=='Gasthaus'))
83 inn_pages = connection.execute(q)
85 # Delete all existing entries in wrinncache
86 # We rely on transactions MySQL InnoDB
87 connection.execute(wrinncache.delete())
89 # Refill wrinncache table
90 for inn_page in inn_pages:
92 gasthausbox = wrvalidators.gasthausbox_from_str(inn_page.old_text)
93 inn = wrmwmarkup.inn_from_gasthausbox(gasthausbox, Inn())
94 inn.page_id = inn_page.page_id
95 inn.page_title = inn_page.page_title
96 inn.under_construction = connection.execute(select([categorylinks], (categorylinks.c.cl_from==inn_page.page_id) & (categorylinks.c.cl_to == 'In_Arbeit')).alias('x').count()).fetchone()[0] > 0 # It would be better to do this in the query above
97 connection.execute(wrinncache.insert(inn.__dict__))
98 except ValueError as e:
99 transaction.rollback()
100 error_msg = "Error as inn '{0}': {1}".format(inn_page.page_title, str(e))
101 raise UpdateCacheError(error_msg, inn_page.page_title, e)
105 def update_wrreportcache(connection, page_id=None):
106 """Updates the wrreportcache table.
107 :param connection: sqlalchemy connection
108 :param page_id: Updates only the reportcache table for the sledrun described on the Winterrodeln wiki page
109 with the specified page_id. Use None for this parameter to update the whole table.
111 >>> from sqlalchemy.engine import create_engine
112 >>> engine = create_engine('mysql://philipp@localhost:3306/philipp_winterrodeln_wiki?charset=utf8&use_unicode=1')
113 >>> update_wrreportcache(engine.connect())
115 metadata = schema.MetaData()
116 wrreport = wrmwdb.wrreport_table(metadata)
117 wrreportcache = wrmwdb.wrreportcache_table(metadata)
118 transaction = connection.begin()
120 # Delete the datasets we are going to update
121 sql_del = wrreportcache.delete()
122 if not page_id is None: sql_del = sql_del.where(wrreportcache.c.page_id == page_id)
123 connection.execute(sql_del)
125 def insert_row(connection, rowlist):
126 if len(rowlist) == 0: return
128 row = dict(rowlist[0])
129 connection.execute(wrreportcache.insert(values=row))
131 # Select the rows to update
132 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))
133 cursor = connection.execute(sql)
137 if row.page_id != page_id:
138 insert_row(connection, rowlist)
139 page_id = row.page_id
142 insert_row(connection, rowlist)
146 def update_wrmapcache(connection):
147 """Updates the wrmappointcache and wrmappathcache tables from the wiki. If convert errors occur, an UpdateCacheError exception
148 is raised. No other exception type should be raised under normal circumstances.
150 >>> from sqlalchemy.engine import create_engine
151 >>> engine = create_engine('mysql://philipp@localhost:3306/philipp_winterrodeln_wiki?charset=utf8&use_unicode=1')
152 >>> # or: engine = create_engine('mysql://philipp@localhost:3306/philipp_winterrodeln_wiki?charset=utf8&use_unicode=1&passwd=XXXXX')
153 >>> update_wrmapcache(engine.connect())
155 metadata = schema.MetaData()
156 page = mwdb.page_table(metadata)
157 categorylinks = mwdb.categorylinks_table(metadata)
158 revision = mwdb.revision_table(metadata)
159 text = mwdb.text_table(metadata)
161 transaction = connection.begin()
164 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=='Rodelbahn'))
165 sledrun_pages = connection.execute(q)
167 # 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"
169 # Delete all existing entries in wrmappointcache
170 # We rely on transactions MySQL InnoDB
171 connection.execute('delete from wrmappointcache')
172 connection.execute('delete from wrmappathcache')
174 # Refill wrmappointcache and wrmappathcache tables
175 for sledrun_page in sledrun_pages:
177 start, content, endtag, end = mwmarkup.find_tag(sledrun_page.old_text, 'wrmap')
180 geojson = wrmwmarkup.parse_wrmap(sledrun_page.old_text[start:end])
182 for feature in geojson['features']:
183 properties = feature['properties']
184 coordinates = feature['geometry']['coordinates']
187 if properties['type'] in wrmwmarkup.WRMAP_POINT_TYPES:
188 lon, lat = coordinates
189 label = properties.get('name')
190 point_types = {'gasthaus': 'hut', 'haltestelle': 'busstop', 'parkplatz': 'carpark', 'achtung': 'warning', 'foto': 'photo', 'verleih': 'rental', 'punkt': 'point'}
191 point_type = point_types[properties['type']]
192 sql = 'insert into wrmappointcache (page_id, type, point, label) values (%s, %s, POINT(%s, %s), %s)'
193 connection.execute(sql, (sledrun_page.page_id, point_type, lon, lat, label))
196 elif properties['type'] in wrmwmarkup.WRMAP_LINE_TYPES:
197 path_types = {'rodelbahn': 'sledrun', 'gehweg': 'walkup', 'alternative': 'alternative', 'lift': 'lift', 'anfahrt': 'recommendedcarroute', 'linie': 'line'}
198 path_type = path_types[properties['type']]
199 path = ", ".join(["{0} {1}".format(lon, lat) for lon, lat in coordinates])
200 path = 'LineString({0})'.format(path)
201 if path_type == 'recommendedcarroute': continue
202 sql = 'insert into wrmappathcache (path, page_id, type) values (GeomFromText(%s), %s, %s)'
203 connection.execute(sql, (path, sledrun_page.page_id, path_type))
206 raise RuntimeError('Unknown feature type {0}'.format(properties['type']))
207 except RuntimeError as e:
208 error_msg = "Error at sledrun '{0}': {1}".format(sledrun_page.page_title, str(e))
209 transaction.rollback()
210 raise UpdateCacheError(error_msg, sledrun_page.page_title, e)
214 def update_wrregioncache(connection):
215 """Updates the wrregioncache table from the wiki.
216 It relays on the table wrsledruncache to be up-to-date.
217 No exceptions should be raised under normal circumstances.
219 >>> from sqlalchemy.engine import create_engine
220 >>> engine = create_engine('mysql://philipp@localhost:3306/philipp_winterrodeln_wiki?charset=utf8&use_unicode=1')
221 >>> # or: engine = create_engine('mysql://philipp@localhost:3306/philipp_winterrodeln_wiki?charset=utf8&use_unicode=1&passwd=XXXXX')
222 >>> update_wrregioncache(engine.connect())
224 metadata = schema.MetaData()
225 wrregion = wrmwdb.wrregion_table(metadata)
226 wrsledruncache = wrmwdb.wrsledruncache_table(metadata)
227 wrregioncache = wrmwdb.wrregioncache_table(metadata)
229 transaction = connection.begin()
231 # Delete all existing entries in wrregioncache
232 # We rely on transactions MySQL InnoDB
233 connection.execute(wrregioncache.delete())
235 # Query all combinations of sledruns and regions
236 sel = select([wrregion.c.id.label('region_id'), sqlfunc.AsWKB(wrregion.c.border).label('border'), wrsledruncache.c.page_id, wrsledruncache.c.position_longitude, wrsledruncache.c.position_latitude], sqlfunc.contains(wrregion.c.border, sqlfunc.point(wrsledruncache.c.position_longitude, wrsledruncache.c.position_latitude)))
237 ins = wrregioncache.insert()
239 # Refill wrregioncache
240 point = ogr.Geometry(ogr.wkbPoint)
241 result = connection.execute(sel)
243 point.SetPoint(0, row.position_longitude, row.position_latitude)
244 if point.Within(ogr.CreateGeometryFromWkb(row.border)):
245 connection.execute(ins.values(region_id=row.region_id, page_id=row.page_id))