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 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 start, content, endtag, end = mwmarkup.find_tag(sledrun_page.old_text, 'wrmap')
179 geojson = wrmwmarkup.parse_wrmap(sledrun_page.old_text[start:end])
181 for feature in geojson['features']:
182 properties = feature['properties']
183 coordinates = feature['geometry']['coordinates']
186 if properties['type'] in wrmwmarkup.WRMAP_POINT_TYPES:
187 lon, lat = coordinates
188 label = properties.get('name')
189 point_types = {'gasthaus': 'hut', 'haltestelle': 'busstop', 'parkplatz': 'carpark', 'achtung': 'warning', 'foto': 'photo', 'verleih': 'rental', 'punkt': 'point'}
190 point_type = point_types[properties['type']]
191 sql = 'insert into wrmappointcache (page_id, type, point, label) values (%s, %s, POINT(%s, %s), %s)'
192 connection.execute(sql, (sledrun_page.page_id, point_type, lon, lat, label))
195 elif properties['type'] in wrmwmarkup.WRMAP_LINE_TYPES:
196 path_types = {'rodelbahn': 'sledrun', 'gehweg': 'walkup', 'alternative': 'alternative', 'lift': 'lift', 'anfahrt': 'recommendedcarroute', 'linie': 'line'}
197 path_type = path_types[properties['type']]
198 path = ", ".join(["{0} {1}".format(lon, lat) for lon, lat in coordinates])
199 path = 'LineString({0})'.format(path)
200 if path_type == 'recommendedcarroute': continue
201 sql = 'insert into wrmappathcache (path, page_id, type) values (GeomFromText(%s), %s, %s)'
202 connection.execute(sql, (path, sledrun_page.page_id, path_type))
205 raise RuntimeError('Unknown feature type {0}'.format(properties['type']))
206 except RuntimeError as e:
207 error_msg = "Error at sledrun '{0}': {1}".format(sledrun_page.page_title, str(e))
208 transaction.rollback()
209 raise UpdateCacheError(error_msg, sledrun_page.page_title, e)
213 def update_wrregioncache(connection):
214 """Updates the wrregioncache table from the wiki.
215 It relays on the table wrsledruncache to be up-to-date.
216 No exceptions should be raised under normal circumstances.
218 >>> from sqlalchemy.engine import create_engine
219 >>> engine = create_engine('mysql://philipp@localhost:3306/philipp_winterrodeln_wiki?charset=utf8&use_unicode=1')
220 >>> # or: engine = create_engine('mysql://philipp@localhost:3306/philipp_winterrodeln_wiki?charset=utf8&use_unicode=1&passwd=XXXXX')
221 >>> update_wrregioncache(engine.connect())
223 metadata = schema.MetaData()
224 wrregion = wrmwdb.wrregion_table(metadata)
225 wrsledruncache = wrmwdb.wrsledruncache_table(metadata)
226 wrregioncache = wrmwdb.wrregioncache_table(metadata)
228 transaction = connection.begin()
230 # Delete all existing entries in wrregioncache
231 # We rely on transactions MySQL InnoDB
232 connection.execute(wrregioncache.delete())
234 # Query all combinations of sledruns and regions
235 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)))
236 ins = wrregioncache.insert()
238 # Refill wrregioncache
239 point = ogr.Geometry(ogr.wkbPoint)
240 result = connection.execute(sel)
242 point.SetPoint(0, row.position_longitude, row.position_latitude)
243 if point.Within(ogr.CreateGeometryFromWkb(row.border)):
244 connection.execute(ins.values(region_id=row.region_id, page_id=row.page_id))