3 from sqlalchemy import orm
4 from sqlalchemy.orm.session import Session
5 from wrpylib.wrorm import Page, WrIntermapsSledrun, WrIntermapsReport, WrIntermapsReportHistory
8 class UpdateError(RuntimeError):
12 def find_intermaps_sledrun(intermaps_sledrun_id: str, json_content: dict):
13 """Returns iterator of tuples (region, sledrun) for given sledrun_id"""
14 regions = json_content['items']
15 for region in regions:
16 for sledrun in region['sledruns']['items']:
17 if sledrun['id'] == intermaps_sledrun_id:
21 def region_to_region_with_sledrun_dict(region: dict):
22 region = region.copy()
23 region.update({'sledruns': {sledrun['id']: sledrun for sledrun in region['sledruns']['items']}})
27 def json_content_to_region_dict(json_content: dict) -> dict:
28 return {region['id']: region_to_region_with_sledrun_dict(region) for region in json_content['items']}
31 def update_wrintermapssledrun(session: Session, json_content: dict):
32 """Updates the wrintermapssledrun table from the wiki. By filling in the missing fields of rows as long as
33 intermaps_sledrun_id and wr_page_id are set.
34 If convert errors occur, an UpdateError exception
35 is raised. No other exception type should be raised under normal circumstances.
38 >>> from sqlalchemy.engine import create_engine
39 >>> engine = create_engine('mysql://philipp@localhost:3306/philipp_winterrodeln_wiki?charset=utf8mb4')
40 >>> Session = orm.sessionmaker(bind=engine)
41 >>> with open('2019-12-06_090500.json') as fp:
42 >>> content = json.load(fp)
43 >>> update_wrintermapssledrun(Session(), content)
45 q = session.query(WrIntermapsSledrun)
46 for intermaps_sledrun in q:
47 if intermaps_sledrun.intermaps_sledrun_name is None or intermaps_sledrun.intermaps_region_id is None or \
48 intermaps_sledrun.intermaps_region_name is None or intermaps_sledrun.intermaps_country is None:
49 region_json, sledrun_json = \
50 next(find_intermaps_sledrun(intermaps_sledrun.intermaps_sledrun_id, json_content), (None, None))
51 if region_json is None:
53 f'Sledrun with intermaps id {intermaps_sledrun.intermaps_sledrun_id} not found in JSON')
54 if intermaps_sledrun.intermaps_sledrun_name is None:
55 intermaps_sledrun.intermaps_sledrun_name = sledrun_json['name']
56 if intermaps_sledrun.intermaps_region_id is None:
57 intermaps_sledrun.intermaps_region_id = region_json['id']
58 if intermaps_sledrun.intermaps_region_name is None:
59 intermaps_sledrun.intermaps_region_name = region_json['names']['de']
60 if intermaps_sledrun.intermaps_country is None:
61 intermaps_sledrun.intermaps_country = region_json['countryCode']
63 if intermaps_sledrun.wr_page_title is None:
64 page = session.query(Page).get(intermaps_sledrun.wr_page_id)
65 intermaps_sledrun.wr_page_title = page.page_title
70 def update_wrintermapsreporthistory(session: Session):
71 """add current content of wrintermapsreport to wrintermapshistory but does not commit session"""
72 # the following lines are equivalent to
73 # insert into wrintermapsreporthistory (intermaps_sledrun_id, status, last_update, first_check, utc_offset)
74 # select intermaps_sledrun_id, status, last_update, last_check, utc_offset from wrintermapsreport r
75 # where not exists (select 1 from wrintermapsreporthistory h
76 # where h.intermaps_sledrun_id=r.intermaps_sledrun_id and h.status=r.status and
77 # h.last_update=r.last_update and h.utc_offset=r.utc_offset);
78 q = session.query(WrIntermapsReportHistory) \
79 .filter(WrIntermapsReportHistory.intermaps_sledrun_id == WrIntermapsReport.intermaps_sledrun_id) \
80 .filter(WrIntermapsReportHistory.status == WrIntermapsReport.status) \
81 .filter(WrIntermapsReportHistory.last_update == WrIntermapsReport.last_update) \
82 .filter(WrIntermapsReportHistory.utc_offset == WrIntermapsReport.utc_offset)
83 for report in session.query(WrIntermapsReport).filter(~q.exists()):
84 report_history = WrIntermapsReportHistory()
85 report_history.intermaps_sledrun_id = report.intermaps_sledrun_id
86 report_history.status = report.status
87 report_history.last_update = report.last_update
88 report_history.first_check = report.last_check
89 report_history.utc_offset = report.utc_offset
90 session.add(report_history)
93 def insert_json_to_intermapsreport(session: Session, json_content: dict, last_check: datetime.datetime):
94 """add JSON content to intermaps_report. Does not commit the session"""
95 regions = json_content_to_region_dict(json_content)
96 for sledrun in session.query(WrIntermapsSledrun):
97 region = regions.get(sledrun.intermaps_region_id)
100 sledrun_json = region['sledruns'].get(sledrun.intermaps_sledrun_id)
101 if sledrun_json is None:
103 report = WrIntermapsReport()
104 report.intermaps_sledrun_id = sledrun.intermaps_sledrun_id
105 report.status = sledrun_json['status']
106 report.last_update = region['lastUpdate']
107 report.last_check = last_check
108 report.utc_offset = int(re.match(r'UTC([-+]\d\d)\d\d', region['timeZone']).group(1))
112 def update_wrintermapsreport(session: Session, json_content: dict, last_check: datetime.datetime):
113 # add current content of wrintermapsreport to wrintermapshistory
114 update_wrintermapsreporthistory(session)
116 # delete content of intermaps_report
117 session.query(WrIntermapsReport).delete()
119 # add JSON content to intermaps_report
120 insert_json_to_intermapsreport(session, json_content, last_check)