Database Tables¶
We do have two databases in use to provide all functionalities.
One database is the database on which wordpress
is running. In this documentation this database will be called
wpwt for WordPress WetterTurnier”.
The data tables for the wetterturnier plugin are stored using
the wordpress database prefix (classically wp_
but depends on your installation).
The second database is the database where all the live observations
are coming in. Depending on the station we are retrieving the observations
on an hourly temporal resolution, many parameters, and even more stations,
from which only a very specific small subset is used for the
tournament itself. To keep the data structure clean the live observations
are stored in a second database, from now on called obs
database
for OBServation database.
There is a python script in the wetterturnier backend which loads the required observational subset from the obs
database
and stores them in the wpwt
database for operational use (compute the points).
Note
Please note that, as we have two separate databases, you need to
grant SELECT access to the wordpress database user to the obs
database
to be able to use all wordpress wetterturnier plugin functions. This is
no requirement, if you do not have such a data access you simply have to
disable all features using this (mainly Widgets and some data views).
- Wordpress Databse contains the Wetterturnier Plugin Database tables:
- Table wp_users: Text …
- Table wp_wetterturnier_api: Text …
- Table wp_wetterturnier_bets: Text …
- Table wp_wetterturnier_betstat: Text …
- Table wp_wetterturnier_cities: Text …
- Table wp_wetterturnier_dates: Text …
- Table wp_wetterturnier_groups: Text …
- Table wp_wetterturnier_groupusers: Text …
- Table wp_wetterturnier_obs: Text …
- Table wp_wetterturnier_param: Text …
- Table wp_wetterturnier_rerunrequest: Text …
- Table wp_wetterturnier_stationparams: Text …
- Table wp_wetterturnier_stations: Text …
- Table wp_wetterturnier_webcams: Text …
- Observation Database contains the following important tables:
Relationship¶
Not yet having a visual representation of the database structure but trying
to explain the relationship in some words. All user data are linked to the
wordpress users table usint the user ID
. The wetterturnier
structure can be summarized as follows:
- Several cities can be specified for which forecasts can be submitted. Each cities has its own unique city ID.
- To each city one or more weather stations can be linked to identified by a unique station ID.
- A set of parameters to forecast can be specified which have to be forecasted. Note that for each individual city or station the parameters can be disabled/enabled (e.g., when no observations for this parameter are available). As cities and stations parameters have their unique parameter ID.
- Tournament dates are specified via tournament dates
(
tdate
’s) using days since 1970-01-01. Tournaments can therefore be held on a daily basis (but not sub-daily, not two rounds per day). - User forecasts are made for a specific city, day, and parameter using the wordpress user ID to link the forecasts to the users.
- Forecasts are summarized and stored in the betstat table where the sum of the points will be added by the wetterturnier backend judging.
Wordpress Database¶
wp_users¶
Field | Type | Null | Key | Default | Extra |
---|---|---|---|---|---|
ID | bigint(20) unsigned | NO | PRI | None | auto_increment |
user_login | varchar(60) | NO | MUL | ||
user_nicename | varchar(50) | NO | MUL | ||
user_email | varchar(100) | NO | MUL | ||
… | … | … | … | … | … |
- Non-unique key named user_nicename on
(user_nicename)
- Unique-key named PRIMARY on
(ID)
- Non-unique key named user_email on
(user_email)
- Non-unique key named user_login_key on
(user_login)
wp_wetterturnier_api¶
Field | Type | Null | Key | Default | Extra |
---|---|---|---|---|---|
ID | smallint(5) unsigned | NO | PRI | None | auto_increment |
APIKEY | varchar(20) | NO | UNI | None | |
APITYPE | enum(‘obslive’,’obsarchive’,’bets’) | NO | None | ||
APICONFIG | varchar(100) | NO | None | ||
ISPUBLIC | tinyint(1) | NO | 0 | ||
name | varchar(50) | NO | None | ||
description | varchar(200) | NO | None | ||
since | timestamp | NO | CURRENT_TIMESTAMP | ||
until | int(11) | YES | None | ||
active | tinyint(4) | YES | 1 |
- Unique-key named APIKEY on
(APIKEY)
- Unique-key named PRIMARY on
(ID)
wp_wetterturnier_bets¶
Field | Type | Null | Key | Default | Extra |
---|---|---|---|---|---|
userID | bigint(20) unsigned | NO | PRI | None | |
cityID | smallint(5) unsigned | NO | PRI | None | |
paramID | smallint(5) unsigned | NO | PRI | None | |
tdate | smallint(5) unsigned | NO | PRI | None | |
betdate | smallint(5) unsigned | NO | PRI | None | |
value | smallint(6) | NO | None | ||
points | float | YES | None | ||
placed | timestamp | NO | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP | |
placedby | bigint(20) unsigned | NO | 0 |
- Non-unique key named wp_wetterturnier_bets_idx_cityID on
(cityID)
- Non-unique key named wp_wetterturnier_bets_idx_betdate on
(betdate)
- Unique-key named userID on
(userID, cityID, paramID, tdate, betdate)
- Non-unique key named wp_wetterturnier_bets_idx_tournamentdate on
(tdate)
Partitions on:
PARTITION part2000 VALUES LESS THAN (10957) ENGINE = InnoDB
PARTITION part2001 VALUES LESS THAN (11323) ENGINE = InnoDB
PARTITION part2002 VALUES LESS THAN (11688) ENGINE = InnoDB
PARTITION part2003 VALUES LESS THAN (12053) ENGINE = InnoDB
PARTITION part2004 VALUES LESS THAN (12418) ENGINE = InnoDB
PARTITION part2005 VALUES LESS THAN (12784) ENGINE = InnoDB
PARTITION part2006 VALUES LESS THAN (13149) ENGINE = InnoDB
PARTITION part2007 VALUES LESS THAN (13514) ENGINE = InnoDB
PARTITION part2008 VALUES LESS THAN (13879) ENGINE = InnoDB
PARTITION part2009 VALUES LESS THAN (14245) ENGINE = InnoDB
PARTITION part2010 VALUES LESS THAN (14610) ENGINE = InnoDB
PARTITION part2011 VALUES LESS THAN (14975) ENGINE = InnoDB
PARTITION part2012 VALUES LESS THAN (15340) ENGINE = InnoDB
PARTITION part2013 VALUES LESS THAN (15706) ENGINE = InnoDB
PARTITION part2014 VALUES LESS THAN (16071) ENGINE = InnoDB
PARTITION part2015 VALUES LESS THAN (16436) ENGINE = InnoDB
PARTITION part2016 VALUES LESS THAN (16801) ENGINE = InnoDB
PARTITION part2017 VALUES LESS THAN (17167) ENGINE = InnoDB
PARTITION part2018 VALUES LESS THAN (17532) ENGINE = InnoDB
PARTITION part2019 VALUES LESS THAN (17897) ENGINE = InnoDB
PARTITION part2020 VALUES LESS THAN (18262) ENGINE = InnoDB
PARTITION part2021 VALUES LESS THAN (18628) ENGINE = InnoDB
PARTITION part2022 VALUES LESS THAN (18993) ENGINE = InnoDB
PARTITION part2023 VALUES LESS THAN (19358) ENGINE = InnoDB
PARTITION part2024 VALUES LESS THAN (19723) ENGINE = InnoDB
PARTITION part2025 VALUES LESS THAN (20089) ENGINE = InnoDB
PARTITION part2026 VALUES LESS THAN (20454) ENGINE = InnoDB
PARTITION part2027 VALUES LESS THAN (20819) ENGINE = InnoDB
PARTITION part2028 VALUES LESS THAN (21184) ENGINE = InnoDB
PARTITION part2029 VALUES LESS THAN (21550) ENGINE = InnoDB
wp_wetterturnier_betstat¶
Field | Type | Null | Key | Default | Extra |
---|---|---|---|---|---|
userID | int(11) | NO | PRI | None | |
cityID | smallint(5) unsigned | NO | PRI | None | |
tdate | smallint(6) | NO | PRI | None | |
points_d1 | float | YES | None | ||
points_d2 | float | YES | None | ||
points | float | YES | None | ||
rank | smallint(5) unsigned | YES | None | ||
updated | timestamp | NO | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP | |
submitted | timestamp | YES | None |
- Non-unique key named wp_wetterturnier_betstat_cityID on
(cityID)
- Non-unique key named wp_wetterturnier_betstat_tdate on
(tdate)
- Unique-key named data on
(userID, cityID, tdate)
Partitions on:
PARTITION part2000 VALUES LESS THAN (10957) ENGINE = InnoDB
PARTITION part2001 VALUES LESS THAN (11323) ENGINE = InnoDB
PARTITION part2002 VALUES LESS THAN (11688) ENGINE = InnoDB
PARTITION part2003 VALUES LESS THAN (12053) ENGINE = InnoDB
PARTITION part2004 VALUES LESS THAN (12418) ENGINE = InnoDB
PARTITION part2005 VALUES LESS THAN (12784) ENGINE = InnoDB
PARTITION part2006 VALUES LESS THAN (13149) ENGINE = InnoDB
PARTITION part2007 VALUES LESS THAN (13514) ENGINE = InnoDB
PARTITION part2008 VALUES LESS THAN (13879) ENGINE = InnoDB
PARTITION part2009 VALUES LESS THAN (14245) ENGINE = InnoDB
PARTITION part2010 VALUES LESS THAN (14610) ENGINE = InnoDB
PARTITION part2011 VALUES LESS THAN (14975) ENGINE = InnoDB
PARTITION part2012 VALUES LESS THAN (15340) ENGINE = InnoDB
PARTITION part2013 VALUES LESS THAN (15706) ENGINE = InnoDB
PARTITION part2014 VALUES LESS THAN (16071) ENGINE = InnoDB
PARTITION part2015 VALUES LESS THAN (16436) ENGINE = InnoDB
PARTITION part2016 VALUES LESS THAN (16801) ENGINE = InnoDB
PARTITION part2017 VALUES LESS THAN (17167) ENGINE = InnoDB
PARTITION part2018 VALUES LESS THAN (17532) ENGINE = InnoDB
PARTITION part2019 VALUES LESS THAN (17897) ENGINE = InnoDB
PARTITION part2020 VALUES LESS THAN (18262) ENGINE = InnoDB
PARTITION part2021 VALUES LESS THAN (18628) ENGINE = InnoDB
PARTITION part2022 VALUES LESS THAN (18993) ENGINE = InnoDB
PARTITION part2023 VALUES LESS THAN (19358) ENGINE = InnoDB
PARTITION part2024 VALUES LESS THAN (19723) ENGINE = InnoDB
PARTITION part2025 VALUES LESS THAN (20089) ENGINE = InnoDB
PARTITION part2026 VALUES LESS THAN (20454) ENGINE = InnoDB
PARTITION part2027 VALUES LESS THAN (20819) ENGINE = InnoDB
PARTITION part2028 VALUES LESS THAN (21184) ENGINE = InnoDB
PARTITION part2029 VALUES LESS THAN (21550) ENGINE = InnoDB
wp_wetterturnier_cities¶
Field | Type | Null | Key | Default | Extra |
---|---|---|---|---|---|
ID | smallint(5) unsigned | NO | PRI | None | auto_increment |
name | varchar(20) | NO | PRI | None | |
hash | varchar(20) | NO | None | ||
paramconfig | varchar(50) | YES | None | ||
sort | tinyint(4) | YES | None | ||
since | timestamp | NO | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP | |
until | timestamp | NO | 0000-00-00 00:00:00 | ||
active | tinyint(4) | NO | 1 |
- Unique-key named PRIMARY on
(ID, name)
wp_wetterturnier_dates¶
Field | Type | Null | Key | Default | Extra |
---|---|---|---|---|---|
tdate | smallint(11) unsigned | NO | PRI | None | |
status | tinyint(4) | NO | 1 |
- Unique-key named PRIMARY on
(tdate)
wp_wetterturnier_groups¶
Field | Type | Null | Key | Default | Extra |
---|---|---|---|---|---|
groupID | smallint(5) unsigned | NO | PRI | None | auto_increment |
groupName | varchar(50) | NO | None | ||
groupDesc | varchar(100) | YES | None | ||
since | timestamp | NO | CURRENT_TIMESTAMP | ||
until | timestamp | NO | 0000-00-00 00:00:00 | ||
active | tinyint(4) | YES | 1 |
- Unique-key named PRIMARY on
(groupID)
wp_wetterturnier_groupusers¶
Field | Type | Null | Key | Default | Extra |
---|---|---|---|---|---|
ID | int(11) unsigned | NO | PRI | None | auto_increment |
userID | bigint(5) unsigned | NO | None | ||
groupID | smallint(5) unsigned | NO | None | ||
application | text | NO | None | ||
since | timestamp | YES | None | ||
until | timestamp | YES | None | ||
active | tinyint(4) | YES | 1 |
- Unique-key named PRIMARY on
(ID)
- Unique-key named ID on
(ID)
wp_wetterturnier_obs¶
Field | Type | Null | Key | Default | Extra |
---|---|---|---|---|---|
station | smallint(5) unsigned | NO | PRI | None | |
paramID | smallint(5) unsigned | NO | PRI | None | |
betdate | smallint(5) unsigned | NO | PRI | None | |
placed | timestamp | NO | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP | |
placedby | bigint(20) unsigned | NO | 0 | ||
value | smallint(6) | YES | None |
- Non-unique key named wp_wetterturnier_obs_idx_betdate on
(betdate)
- Unique-key named station on
(station, paramID, betdate)
- Non-unique key named wp_wetterturnier_obs_idx_station on
(station)
Partitions on:
PARTITION part2000 VALUES LESS THAN (10957) ENGINE = InnoDB
PARTITION part2001 VALUES LESS THAN (11323) ENGINE = InnoDB
PARTITION part2002 VALUES LESS THAN (11688) ENGINE = InnoDB
PARTITION part2003 VALUES LESS THAN (12053) ENGINE = InnoDB
PARTITION part2004 VALUES LESS THAN (12418) ENGINE = InnoDB
PARTITION part2005 VALUES LESS THAN (12784) ENGINE = InnoDB
PARTITION part2006 VALUES LESS THAN (13149) ENGINE = InnoDB
PARTITION part2007 VALUES LESS THAN (13514) ENGINE = InnoDB
PARTITION part2008 VALUES LESS THAN (13879) ENGINE = InnoDB
PARTITION part2009 VALUES LESS THAN (14245) ENGINE = InnoDB
PARTITION part2010 VALUES LESS THAN (14610) ENGINE = InnoDB
PARTITION part2011 VALUES LESS THAN (14975) ENGINE = InnoDB
PARTITION part2012 VALUES LESS THAN (15340) ENGINE = InnoDB
PARTITION part2013 VALUES LESS THAN (15706) ENGINE = InnoDB
PARTITION part2014 VALUES LESS THAN (16071) ENGINE = InnoDB
PARTITION part2015 VALUES LESS THAN (16436) ENGINE = InnoDB
PARTITION part2016 VALUES LESS THAN (16801) ENGINE = InnoDB
PARTITION part2017 VALUES LESS THAN (17167) ENGINE = InnoDB
PARTITION part2018 VALUES LESS THAN (17532) ENGINE = InnoDB
PARTITION part2019 VALUES LESS THAN (17897) ENGINE = InnoDB
PARTITION part2020 VALUES LESS THAN (18262) ENGINE = InnoDB
PARTITION part2021 VALUES LESS THAN (18628) ENGINE = InnoDB
PARTITION part2022 VALUES LESS THAN (18993) ENGINE = InnoDB
PARTITION part2023 VALUES LESS THAN (19358) ENGINE = InnoDB
PARTITION part2024 VALUES LESS THAN (19723) ENGINE = InnoDB
PARTITION part2025 VALUES LESS THAN (20089) ENGINE = InnoDB
PARTITION part2026 VALUES LESS THAN (20454) ENGINE = InnoDB
PARTITION part2027 VALUES LESS THAN (20819) ENGINE = InnoDB
PARTITION part2028 VALUES LESS THAN (21184) ENGINE = InnoDB
PARTITION part2029 VALUES LESS THAN (21550) ENGINE = InnoDB
wp_wetterturnier_param¶
Field | Type | Null | Key | Default | Extra |
---|---|---|---|---|---|
paramID | smallint(5) unsigned | NO | PRI | None | auto_increment |
sort | int(10) unsigned | YES | None | ||
paramName | varchar(10) | NO | PRI | None | |
EN | varchar(50) | NO | None | ||
DE | varchar(50) | NO | None | ||
helpEN | text | NO | None | ||
helpDE | text | NO | None | ||
valformat | varchar(10) | YES | None | ||
vallength | smallint(6) | YES | 5 | ||
valmin | smallint(6) | NO | None | ||
valmax | smallint(6) | NO | None | ||
format | varchar(10) | YES | None | ||
active | tinyint(1) unsigned | NO | 1 | ||
decimals | tinyint(3) unsigned | YES | 1 | ||
unit | varchar(5) | YES |
- Unique-key named PRIMARY on
(paramID, paramName)
wp_wetterturnier_rerunrequest¶
Field | Type | Null | Key | Default | Extra |
---|---|---|---|---|---|
ID | int(10) unsigned | NO | PRI | None | auto_increment |
cityID | smallint(5) unsigned | NO | None | ||
tdate | smallint(5) unsigned | NO | None | ||
userID | bigint(20) unsigned | NO | None | ||
placed | timestamp | NO | CURRENT_TIMESTAMP | ||
done | timestamp | YES | None |
- Unique-key named PRIMARY on
(ID)
wp_wetterturnier_stationparams¶
Field | Type | Null | Key | Default | Extra |
---|---|---|---|---|---|
ID | smallint(5) unsigned | NO | PRI | None | auto_increment |
stationID | smallint(5) unsigned | NO | None | ||
paramID | smallint(5) unsigned | NO | None | ||
since | timestamp | NO | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP | |
until | timestamp | NO | 0000-00-00 00:00:00 |
- Unique-key named PRIMARY on
(ID)
wp_wetterturnier_stations¶
Field | Type | Null | Key | Default | Extra |
---|---|---|---|---|---|
ID | smallint(5) unsigned | NO | PRI | None | auto_increment |
cityID | smallint(5) unsigned | NO | None | ||
wmo | smallint(5) unsigned | NO | UNI | None | |
name | varchar(20) | NO | None | ||
changed | timestamp | NO | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |
- Unique-key named wmo on
(wmo)
- Unique-key named PRIMARY on
(ID)
wp_wetterturnier_webcams¶
The *wetterturnier_webcams table contains a list of webcams displayed by the webcam widget if there are any.
Todo
Provide detailed table description here and what to find where and why and who fills in/deletes the data sets.
Field | Type | Null | Key | Default | Extra |
---|---|---|---|---|---|
ID | smallint(6) | NO | PRI | None | auto_increment |
cityID | smallint(11) | NO | None | ||
uri | varchar(200) | NO | None | ||
source | varchar(100) | NO | None | ||
desc | text | NO | None | ||
modified | timestamp | NO | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |
- Unique-key named PRIMARY on
(ID)
Observation Database¶
archive¶
Field | Type | Null | Key | Default | Extra |
---|---|---|---|---|---|
statnr | int(11) | NO | MUL | None | |
datum | int(8) | NO | MUL | None | |
datumsec | int(11) | NO | MUL | None | |
stdmin | smallint(4) | NO | None | ||
msgtyp | enum(‘na’,’bufr’,’synop’) | YES | na | ||
stint | enum(‘na’,’essential’,’additional’) | YES | na | ||
utime | timestamp | NO | MUL | CURRENT_TIMESTAMP | |
ucount | tinyint(3) unsigned | YES | 0 | ||
… | … | … | … | … | … |
- Non-unique key named bufr_statnr on
(statnr)
- Non-unique key named bufr_datumsec on
(datumsec)
- Non-unique key named bufr_datum on
(datum)
- Non-unique key named bufr_einspiel on
(utime)
- Unique-key named bufr_statnr_datumsec_msgtyp on
(statnr, datumsec, msgtyp)
live¶
Field | Type | Null | Key | Default | Extra |
---|---|---|---|---|---|
statnr | int(11) | NO | MUL | None | |
datum | int(8) | NO | MUL | None | |
datumsec | int(11) | NO | MUL | None | |
stdmin | smallint(4) | NO | None | ||
msgtyp | enum(‘na’,’bufr’,’synop’) | YES | na | ||
stint | enum(‘na’,’essential’,’additional’) | YES | na | ||
utime | timestamp | NO | MUL | CURRENT_TIMESTAMP | |
ucount | tinyint(3) unsigned | YES | 0 | ||
… | … | … | … | … | … |
- Non-unique key named bufr_statnr on
(statnr)
- Non-unique key named bufr_datumsec on
(datumsec)
- Non-unique key named bufr_datum on
(datum)
- Non-unique key named bufr_einspiel on
(utime)
- Unique-key named bufr_statnr_datumsec_msgtyp on
(statnr, datumsec, msgtyp)