wrpylib/wrmwdb.py

   1 """This module contains code to make tha access of winterrodeln
   2 tables in MediaWiki easy. The module uses sqlalchemy to access the database.
   3 """
   4 from sqlalchemy import Table, Column, types, schema
   5 from sqlalchemy.dialects.mysql import ENUM
   6
   7
   8 intermaps_sledrun_id_type = types.Unicode(50)
   9 intermaps_sledrun_status_type = ENUM('open', 'closed', 'in_preparation', 'unknown')
  10
  11
  12 def wrreport_table(metadata):
  13     """Returns the sqlalchemy Table representing the "wrreport" Winterrodeln table in MediaWiki.
  14     Current table definition.
  15     * version 1.2
  16     * version 1.3 (no changes)
  17     * version 1.4 (no changes)
  18     * version 1.5 added time_report
  19     :param metadata: metadata = sqlalchemy.MetaData()
  20     """
  21     return Table(
  22         "wrreport", metadata,
  23         Column("id", types.Integer, primary_key=True),
  24         Column("page_id", types.Integer, schema.ForeignKey('wrsledruncache.page_id')),
  25         Column("page_title", types.Unicode(255), nullable=False),
  26         Column("date_report", types.Date),
  27         Column("time_report", types.Time),
  28         Column("date_entry", types.DateTime, nullable=False),
  29         Column("date_invalid", types.DateTime),
  30         Column("condition", types.Integer),
  31         Column("description", types.Unicode),
  32         Column("author_name", types.Unicode(30)),
  33         Column("author_ip", types.Unicode(15)),
  34         Column("author_userid", types.Integer),
  35         Column("author_username", types.Unicode(30)),
  36         Column("delete_date", types.DateTime),
  37         Column("delete_person_name", types.Unicode(30)),
  38         Column("delete_person_ip", types.Unicode(15)),
  39         Column("delete_person_userid", types.Integer),
  40         Column("delete_person_username", types.Unicode(30)),
  41         Column("delete_reason_public", types.Unicode),
  42         Column("delete_invisible", types.Boolean),
  43     )
  44
  45
  46 def wrsledruncache_table(metadata) -> Table:
  47     """Returns the sqlalchemy Table representing the "wrsledruncache" Winterrodeln table in MediaWiki.
  48     Current table definition
  49     * version 1.4 (renamed table and added column walkup_possible)
  50     :param metadata: metadata = sqlalchemy.MetaData()
  51     """
  52     return Table(
  53         "wrsledruncache", metadata,
  54         Column("page_id", types.Integer, schema.ForeignKey('wrreportcache.page_id'), primary_key=True),
  55         Column("page_title", types.Unicode(255)),
  56         Column("name_url", types.Unicode(255)),
  57         Column("position_latitude", types.Float),
  58         Column("position_longitude", types.Float),
  59         Column("top_latitude", types.Float),
  60         Column("top_longitude", types.Float),
  61         Column("top_elevation", types.Integer),
  62         Column("bottom_latitude", types.Float),
  63         Column("bottom_longitude", types.Float),
  64         Column("bottom_elevation", types.Integer),
  65         Column("length", types.Integer),
  66         Column("difficulty", types.Integer),
  67         Column("avalanches", types.Integer),
  68         Column("operator", types.Unicode(255)),
  69         Column("public_transport", types.Integer),
  70         Column("walkup_possible", types.Boolean),
  71         Column("walkup_time", types.Integer),
  72         Column("walkup_separate", types.Float),
  73         Column("walkup_separate_comment", types.Unicode(255)),
  74         Column("lift", types.Boolean),
  75         Column("lift_details", types.Unicode(255)),
  76         Column("night_light", types.Float),
  77         Column("night_light_comment", types.Unicode(255)),
  78         Column("night_light_days", types.Integer),
  79         Column("night_light_days_comment", types.Unicode(255)),
  80         Column("sled_rental", types.Boolean),
  81         Column("sled_rental_comment", types.Unicode(255)),
  82         Column("cachet", types.Unicode(255)),
  83         Column("information_web", types.Unicode(255)),
  84         Column("information_phone", types.Unicode(255)),
  85         Column("image", types.Unicode(255)),
  86         Column("show_in_overview", types.Boolean),
  87         Column("forum_id", types.Integer),
  88         Column("under_construction", types.Boolean),
  89     )
  90
  91
  92 def wrinncache_table(metadata):
  93     """Returns the sqlalchemy Table representing the "wrinncache" Winterrodeln table in MediaWiki.
  94     Current table definition
  95     * version 1.3 (changes made from version 1.2)
  96     * version 1.4 (no changes)
  97     :param metadata: metadata = sqlalchemy.MetaData()
  98     """
  99     return Table(
 100         "wrinncache", metadata,
 101         Column("page_id", types.Integer, primary_key=True),
 102         Column("page_title", types.Unicode(255)),
 103         Column("position_latitude", types.Float),
 104         Column("position_longitude", types.Float),
 105         Column("position_elevation", types.Integer),
 106         Column("operator", types.Unicode(255)),
 107         Column("seats", types.Integer),
 108         Column("overnight", types.Boolean),
 109         Column("overnight_comment", types.Unicode(255)),
 110         Column("smoker_area", types.Boolean),
 111         Column("nonsmoker_area", types.Boolean),
 112         Column("sled_rental", types.Boolean),
 113         Column("sled_rental_comment", types.Unicode(255)),
 114         Column("mobile_provider", types.Unicode),
 115         Column("homepage", types.Unicode(255)),
 116         Column("email_list", types.Unicode),
 117         Column("phone_list", types.Unicode),
 118         Column("image", types.Unicode(255)),
 119         Column("sledding_list", types.Unicode),
 120         Column("under_construction", types.Boolean),
 121     )
 122
 123
 124 def wrreportcache_table(metadata):
 125     """Returns the sqlalchemy Table representing the "wrreportcache" Winterrodeln table in MediaWiki.
 126     Current table definition.
 127     * version 1.5 (introduction)
 128     :param metadata: metadata = sqlalchemy.MetaData()
 129     """
 130     return Table(
 131         "wrreportcache", metadata,
 132         Column("page_id", types.Integer, primary_key=True),
 133         Column("page_title", types.Unicode(255), nullable=False),
 134         Column("report_id", types.Integer, schema.ForeignKey('wrreport.id')),
 135         Column("date_report", types.Date),
 136         Column("condition", types.Integer),
 137         Column("description", types.Unicode),
 138         Column("author_name", types.Unicode(30)),
 139         Column("author_username", types.Unicode(30))
 140     )
 141
 142
 143 def wrregion_table(metadata):
 144     """Returns the sqlalchemy Table representing the "wrregion" Winterrodeln table in MediaWiki.
 145     Current table definition.
 146     * version 1.5 (introduction)
 147     :param metadata: metadata = sqlalchemy.MetaData()
 148     """
 149     return Table(
 150         "wrregion", metadata,
 151         Column("id", types.Integer, primary_key=True),
 152         Column("name", types.Unicode(50)),
 153         Column("page_id", types.Integer),
 154         Column("border", types.LargeBinary)  # MultiPolygon(2, 4326)
 155     )
 156
 157
 158 def wrregioncache_table(metadata):
 159     """Returns the sqlalchemy Table representing the "wrregioncache" Winterrodeln table in MediaWiki.
 160     Current table definition.
 161     * version 1.5 (introduction)
 162     :param metadata: metadata = sqlalchemy.MetaData()
 163     """
 164     return Table(
 165         "wrregioncache", metadata,
 166         Column("id", types.Integer, primary_key=True),
 167         Column("region_id", types.Integer, schema.ForeignKey('wrregion.id')),
 168         Column("page_id", types.Integer)
 169     )
 170
 171
 172 def wrintermapssledrun_table(metadata):
 173     """Returns the sqlalchemy table representing the "wrintermapssledrun" Winterrodeln table in MediaWiki.
 174     Current table definition.
 175     * version 1.5 (introduction)
 176     :param metadata: metadata = sqlalchemy.MetaData()
 177     """
 178     return Table(
 179         "wrintermapssledrun", metadata,
 180         Column("intermaps_sledrun_id", intermaps_sledrun_id_type, primary_key=True),
 181         Column("intermaps_sledrun_name", types.Unicode(255)),
 182         Column("intermaps_region_id", types.Integer),
 183         Column("intermaps_region_name", types.Unicode(255)),
 184         Column("intermaps_country", types.Unicode(10)),
 185         Column("wr_page_id", types.Integer, schema.ForeignKey("page.page_id")),
 186         Column("wr_page_title", types.Unicode(255)),
 187         Column("show_status", types.Boolean, nullable=False)
 188     )
 189
 190
 191 def wrintermapsreport_table(metadata):
 192     """Returns the sqlalchemy table representing the "wrintermapsreport" Winterrodeln table in MediaWiki.
 193     Current table definition.
 194     * version 1.5 (introduction)
 195     :param metadata: metadata = sqlalchemy.MetaData()
 196     """
 197     return Table(
 198         "wrintermapsreport", metadata,
 199         Column("intermaps_sledrun_id", intermaps_sledrun_id_type, primary_key=True),
 200         Column("status", intermaps_sledrun_status_type, nullable=False),
 201         Column("last_update", types.DateTime, nullable=False),
 202         Column("last_check", types.DateTime, nullable=False),
 203         Column("utc_offset", types.Integer, nullable=False)
 204     )
 205
 206
 207 def wrintermapsreporthistory_table(metadata):
 208     """Returns the sqlalchemy table representing the "wrintermapsreporthistory" Winterrodeln table in MediaWiki.
 209     Current table definition.
 210     * version 1.5 (introduction)
 211     :param metadata: metadata = sqlalchemy.MetaData()
 212     """
 213     return Table(
 214         "wrintermapsreporthistory", metadata,
 215         Column("id", types.Integer, primary_key=True),
 216         Column("intermaps_sledrun_id", intermaps_sledrun_id_type, nullable=False),
 217         Column("status", intermaps_sledrun_status_type, nullable=False),
 218         Column("last_update", types.DateTime, nullable=False),
 219         Column("first_check", types.DateTime, nullable=False),
 220         Column("utc_offset", types.Integer, nullable=False)
 221     )