wrpylib/wrintermaps.py

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