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)
77 transaction = connection.begin()
80 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'))
81 inn_pages = connection.execute(q)
83 # Delete all existing entries in wrinncache
84 # We rely on transactions MySQL InnoDB
85 connection.execute(wrinncache.delete())
87 # Refill wrinncache table
88 for inn_page in inn_pages:
90 start, end, inn = wrmwmarkup.gasthausbox_to_inn(inn_page.old_text)
91 inn.page_id = inn_page.page_id
92 inn.page_title = inn_page.page_title
93 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
94 connection.execute(wrinncache.insert(inn.__dict__))
95 except (RuntimeError, formencode.Invalid) as e:
96 transaction.rollback()
97 error_msg = "Error as inn '{0}': {1}".format(inn_page.page_title, str(e))
98 raise UpdateCacheError(error_msg, inn_page.page_title, e)
102 def update_wrreportcache(connection, page_id=None):
103 """Updates the wrreportcache table.
104 :param connection: sqlalchemy connection
105 :param page_id: Updates only the reportcache table for the sledrun described on the Winterrodeln wiki page
106 with the specified page_id. Use None for this parameter to update the whole table.
108 >>> from sqlalchemy.engine import create_engine
109 >>> engine = create_engine('mysql://philipp@localhost:3306/philipp_winterrodeln_wiki?charset=utf8&use_unicode=1')
110 >>> update_wrreportcache(engine.connect())
112 metadata = schema.MetaData()
113 wrreport = wrmwdb.wrreport_table(metadata)
114 wrreportcache = wrmwdb.wrreportcache_table(metadata)
115 transaction = connection.begin()
117 # Delete the datasets we are going to update
118 sql_del = wrreportcache.delete()
119 if not page_id is None: sql_del = sql_del.where(wrreportcache.c.page_id == page_id)
120 connection.execute(sql_del)
122 def insert_row(connection, rowlist):
123 if len(rowlist) == 0: return
125 row = dict(rowlist[0])
126 connection.execute(wrreportcache.insert(values=row))
128 # Select the rows to update
129 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))
130 cursor = connection.execute(sql)
134 if row.page_id != page_id:
135 insert_row(connection, rowlist)
136 page_id = row.page_id
139 insert_row(connection, rowlist)
143 def update_wrmapcache(connection):
144 """Updates the wrmappointcache and wrmappathcache tables from the wiki. If convert errors occur, an UpdateCacheError exception
145 is raised. No other exception type should be raised under normal circumstances.
147 >>> from sqlalchemy.engine import create_engine
148 >>> engine = create_engine('mysql://philipp@localhost:3306/philipp_winterrodeln_wiki?charset=utf8&use_unicode=1')
149 >>> # or: engine = create_engine('mysql://philipp@localhost:3306/philipp_winterrodeln_wiki?charset=utf8&use_unicode=1&passwd=XXXXX')
150 >>> update_wrmapcache(engine.connect())
152 metadata = schema.MetaData()
153 page = mwdb.page_table(metadata)
154 categorylinks = mwdb.categorylinks_table(metadata)
155 revision = mwdb.revision_table(metadata)
156 text = mwdb.text_table(metadata)
158 transaction = connection.begin()
161 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'))
162 sledrun_pages = connection.execute(q)
164 # 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"
166 # Delete all existing entries in wrmappointcache
167 # We rely on transactions MySQL InnoDB
168 connection.execute('delete from wrmappointcache')
169 connection.execute('delete from wrmappathcache')
171 # Refill wrmappointcache and wrmappathcache tables
172 for sledrun_page in sledrun_pages:
174 start, content, endtag, end = mwmarkup.find_tag(sledrun_page.old_text, 'wrmap')
177 geojson = wrmwmarkup.parse_wrmap(sledrun_page.old_text[start:end])
179 for feature in geojson['features']:
180 properties = feature['properties']
181 coordinates = feature['geometry']['coordinates']
184 if properties['type'] in wrmwmarkup.WRMAP_POINT_TYPES:
185 lon, lat = coordinates
186 label = properties.get('name')
187 point_types = {'gasthaus': 'hut', 'haltestelle': 'busstop', 'parkplatz': 'carpark', 'achtung': 'warning', 'foto': 'photo', 'verleih': 'rental', 'punkt': 'point'}
188 point_type = point_types[properties['type']]
189 sql = 'insert into wrmappointcache (page_id, type, point, label) values (%s, %s, POINT(%s, %s), %s)'
190 connection.execute(sql, (sledrun_page.page_id, point_type, lon, lat, label))
193 elif properties['type'] in wrmwmarkup.WRMAP_LINE_TYPES:
194 path_types = {'rodelbahn': 'sledrun', 'gehweg': 'walkup', 'alternative': 'alternative', 'lift': 'lift', 'anfahrt': 'recommendedcarroute', 'linie': 'line'}
195 path_type = path_types[properties['type']]
196 path = ", ".join(["{0} {1}".format(lon, lat) for lon, lat in coordinates])
197 path = 'LineString({0})'.format(path)
198 if path_type == 'recommendedcarroute': continue
199 sql = 'insert into wrmappathcache (path, page_id, type) values (GeomFromText(%s), %s, %s)'
200 connection.execute(sql, (path, sledrun_page.page_id, path_type))
203 raise RuntimeError('Unknown feature type {0}'.format(properties['type']))
204 except RuntimeError as e:
205 error_msg = "Error at sledrun '{0}': {1}".format(sledrun_page.page_title, str(e))
206 transaction.rollback()
207 raise UpdateCacheError(error_msg, sledrun_page.page_title, e)
211 def update_wrregioncache(connection):
212 """Updates the wrregioncache table from the wiki.
213 It relays on the table wrsledruncache to be up-to-date.
214 No exceptions should be raised under normal circumstances.
216 >>> from sqlalchemy.engine import create_engine
217 >>> engine = create_engine('mysql://philipp@localhost:3306/philipp_winterrodeln_wiki?charset=utf8&use_unicode=1')
218 >>> # or: engine = create_engine('mysql://philipp@localhost:3306/philipp_winterrodeln_wiki?charset=utf8&use_unicode=1&passwd=XXXXX')
219 >>> update_wrregioncache(engine.connect())
221 metadata = schema.MetaData()
222 wrregion = wrmwdb.wrregion_table(metadata)
223 wrsledruncache = wrmwdb.wrsledruncache_table(metadata)
224 wrregioncache = wrmwdb.wrregioncache_table(metadata)
226 transaction = connection.begin()
228 # Delete all existing entries in wrregioncache
229 # We rely on transactions MySQL InnoDB
230 connection.execute(wrregioncache.delete())
232 # Query all combinations of sledruns and regions
233 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)))
234 ins = wrregioncache.insert()
236 # Refill wrregioncache
237 point = ogr.Geometry(ogr.wkbPoint)
238 result = connection.execute(sel)
240 point.SetPoint(0, row.position_longitude, row.position_latitude)
241 if point.Within(ogr.CreateGeometryFromWkb(row.border)):
242 connection.execute(ins.values(region_id=row.region_id, page_id=row.page_id))