Implement adding reports from wrintermapsreport to wrintermapsreportshistory.
[philipp/winterrodeln/wrpylib.git] / wrpylib / wrintermaps.py
1 import datetime
2 import json
3 import re
4 from sqlalchemy import orm
5 from wrpylib.wrorm import Page, WrIntermapsSledrun, WrIntermapsReport, WrIntermapsReportHistory
6
7
8 class UpdateError(RuntimeError):
9     pass
10
11
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:
18                 yield region, sledrun
19
20
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']}})
24     return region
25
26
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']}
29
30
31 def update_wrintermapssledrun(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.
36
37     >>> import json
38     >>> from sqlalchemy.engine import create_engine
39     >>> engine = create_engine('mysql://philipp@localhost:3306/philipp_winterrodeln_wiki?charset=utf8&use_unicode=1')
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)
44     """
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:
52                 raise UpdateError(
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']
62
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
66
67     session.commit()
68
69
70 def update_wrintermapsreport(session, json_content: dict, last_check: datetime.datetime):
71     # add current content of intermaps_report to intermaps_history
72     # the following lines are equivilent 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)
91
92     # delete content of intermaps_report
93     session.query(WrIntermapsReport).delete()
94
95     # add JSON content to intermaps_report
96     regions = json_content_to_region_dict(json_content)
97     for sledrun in session.query(WrIntermapsSledrun):
98         region = regions.get(sledrun.intermaps_region_id)
99         if region is None:
100             continue
101         sledrun_json = region['sledruns'].get(sledrun.intermaps_sledrun_id)
102         if sledrun_json is None:
103             continue
104         report = WrIntermapsReport()
105         report.intermaps_sledrun_id = sledrun.intermaps_sledrun_id
106         report.status = sledrun_json['status']
107         report.last_update = region['lastUpdate']
108         report.last_check = last_check
109         report.utc_offset = int(re.match(r'UTC([-+]\d\d)\d\d', region['timeZone']).group(1))
110         session.add(report)
111     session.commit()
112
113
114 if __name__ == '__main__':
115     from sqlalchemy.engine import create_engine
116     engine = create_engine('mysql://philipp@localhost:3306/philipp_winterrodeln_wiki?charset=utf8&use_unicode=1')
117     Session = orm.sessionmaker(bind=engine)
118     filename = '/home/philipp/daten/Winterrodeln/Intermaps/intermaps_winterrodeln_2019-12-06_090501.json'
119     with open(filename) as fp:
120         content = json.load(fp)
121     session = Session()
122     update_wrintermapssledrun(session, content)
123     update_wrintermapsreport(session, content, datetime.datetime.now())