Only allow floats in LonLat (and not None, None).
[philipp/winterrodeln/wrpylib.git] / 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):
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     )