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, 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 wrreportcache = wrmwdb.wrreportcache_table(metadata)
117 transaction = connection.begin()
119 # Delete the datasets we are going to update
120 sql_del = wrreportcache.delete()
121 if not page_id is None: sql_del = sql_del.where(wrreportcache.c.page_id == page_id)
122 connection.execute(sql_del)
124 def insert_row(connection, rowlist):
125 if len(rowlist) == 0: return
127 row = dict(rowlist[0])
128 connection.execute(wrreportcache.insert(values=row))
130 # Select the rows to update
131 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))
132 cursor = connection.execute(sql)
136 if row.page_id != page_id:
137 insert_row(connection, rowlist)
138 page_id = row.page_id
141 insert_row(connection, rowlist)
145 def update_wrmapcache(connection):
146 """Updates the wrmappointcache and wrmappathcache tables from the wiki. If convert errors occur, an UpdateCacheError exception
147 is raised. No other exception type should be raised under normal circumstances.
149 >>> from sqlalchemy.engine import create_engine
150 >>> engine = create_engine('mysql://philipp@localhost:3306/philipp_winterrodeln_wiki?charset=utf8&use_unicode=1')
151 >>> # or: engine = create_engine('mysql://philipp@localhost:3306/philipp_winterrodeln_wiki?charset=utf8&use_unicode=1&passwd=XXXXX')
152 >>> update_wrmapcache(engine.connect())
154 metadata = schema.MetaData()
155 page = mwdb.page_table(metadata)
156 categorylinks = mwdb.categorylinks_table(metadata)
157 revision = mwdb.revision_table(metadata)
158 text = mwdb.text_table(metadata)
160 transaction = connection.begin()
163 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'))
164 sledrun_pages = connection.execute(q)
166 # 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"
168 # Delete all existing entries in wrmappointcache
169 # We rely on transactions MySQL InnoDB
170 connection.execute('delete from wrmappointcache')
171 connection.execute('delete from wrmappathcache')
173 # Refill wrmappointcache and wrmappathcache tables
174 for sledrun_page in sledrun_pages:
176 import mwparserfromhell
177 wikicode = mwparserfromhell.parse(sledrun_page.old_text)
178 wrmap_list = wikicode.filter_tags(recursive=False, matches=lambda tag: tag.tag == 'wrmap')
179 if len(wrmap_list) == 0:
180 continue # not wrmap in page
181 if len(wrmap_list) > 1:
182 raise UpdateCacheError('{} <wrmap ...> entries found in article "{}"'.format(len(wrmap_list), sledrun_page.page_title))
183 wrmap = wrmap_list[0]
184 geojson = wrmwmarkup.parse_wrmap(str(wrmap))
186 for feature in geojson['features']:
187 properties = feature['properties']
188 coordinates = feature['geometry']['coordinates']
191 if properties['type'] in wrmwmarkup.WRMAP_POINT_TYPES:
192 lon, lat = coordinates
193 label = properties.get('name')
194 point_types = {'gasthaus': 'hut', 'haltestelle': 'busstop', 'parkplatz': 'carpark', 'achtung': 'warning', 'foto': 'photo', 'verleih': 'rental', 'punkt': 'point'}
195 point_type = point_types[properties['type']]
196 sql = 'insert into wrmappointcache (page_id, type, point, label) values (%s, %s, POINT(%s, %s), %s)'
197 connection.execute(sql, (sledrun_page.page_id, point_type, lon, lat, label))
200 elif properties['type'] in wrmwmarkup.WRMAP_LINE_TYPES:
201 path_types = {'rodelbahn': 'sledrun', 'gehweg': 'walkup', 'alternative': 'alternative', 'lift': 'lift', 'anfahrt': 'recommendedcarroute', 'linie': 'line'}
202 path_type = path_types[properties['type']]
203 path = ", ".join(["{0} {1}".format(lon, lat) for lon, lat in coordinates])
204 path = 'LineString({0})'.format(path)
205 if path_type == 'recommendedcarroute': continue
206 sql = 'insert into wrmappathcache (path, page_id, type) values (GeomFromText(%s), %s, %s)'
207 connection.execute(sql, (path, sledrun_page.page_id, path_type))
210 raise RuntimeError('Unknown feature type {0}'.format(properties['type']))
211 except RuntimeError as e:
212 error_msg = "Error at sledrun '{0}': {1}".format(sledrun_page.page_title, str(e))
213 transaction.rollback()
214 raise UpdateCacheError(error_msg, sledrun_page.page_title, e)
218 def update_wrregioncache(connection):
219 """Updates the wrregioncache table from the wiki.
220 It relays on the table wrsledruncache to be up-to-date.
221 No exceptions should be raised under normal circumstances.
223 >>> from sqlalchemy.engine import create_engine
224 >>> engine = create_engine('mysql://philipp@localhost:3306/philipp_winterrodeln_wiki?charset=utf8&use_unicode=1')
225 >>> # or: engine = create_engine('mysql://philipp@localhost:3306/philipp_winterrodeln_wiki?charset=utf8&use_unicode=1&passwd=XXXXX')
226 >>> update_wrregioncache(engine.connect())
228 metadata = schema.MetaData()
229 wrregion = wrmwdb.wrregion_table(metadata)
230 wrsledruncache = wrmwdb.wrsledruncache_table(metadata)
231 wrregioncache = wrmwdb.wrregioncache_table(metadata)
233 transaction = connection.begin()
235 # Delete all existing entries in wrregioncache
236 # We rely on transactions MySQL InnoDB
237 connection.execute(wrregioncache.delete())
239 # Query all combinations of sledruns and regions
240 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)))
241 ins = wrregioncache.insert()
243 # Refill wrregioncache
244 point = ogr.Geometry(ogr.wkbPoint)
245 result = connection.execute(sel)
247 point.SetPoint(0, row.position_longitude, row.position_latitude)
248 if point.Within(ogr.CreateGeometryFromWkb(row.border)):
249 connection.execute(ins.values(region_id=row.region_id, page_id=row.page_id))