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).

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

[Autogenerated table scheme of table “wp_users] Wordpress user table.”
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

[Autogenerated table scheme of table “wp_wetterturnier_api] Currently experimental: provide API keys for data exports.”
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

[Autogenerated table scheme of table “wp_wetterturnier_bets] Wetterturnier Wordpress Plugin database which takes up the bets/forecasts of the users and the corresponding parameter-wise points.”
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

[Autogenerated table scheme of table “wp_wetterturnier_betstat] Wetterturnier Wordpress Plugin forecast stats table. Does contain the overall points (not points for single forecasted parameters).”
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

[Autogenerated table scheme of table “wp_wetterturnier_cities] Wetterturnier cities for which forecasts can be submitted.”
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

[Autogenerated table scheme of table “wp_wetterturnier_dates] Wetterturnier calendar dates. Definition of when tournaments will take place/took place.”
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

[Autogenerated table scheme of table “wp_wetterturnier_groups] Wetterturnier Wordpress Plugin which contains the 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

[Autogenerated table scheme of table “wp_wetterturnier_groupusers] Wetterturnier Wordpress Plugin containing the information which users are members of which group or were members of a specific group for a specific time period. Used to compute mean bets.”
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

[Autogenerated table scheme of table “wp_wetterturnier_obs] Wetterturnier Wordpress Plugin observation table which is used to compute the points.”
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

[Autogenerated table scheme of table “wp_wetterturnier_param] Wetterturnier Wordpress Plugin parameter table. Contains parameter specification used to forecast/judge the users.”
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

[Autogenerated table scheme of table “wp_wetterturnier_rerunrequest] Wetterturnier rerun requests, tell the backend to re-compute a specific tournament date due to changed observations/forecasts (admin modifications).”
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

[Autogenerated table scheme of table “wp_wetterturnier_stationparams] Wetterturnier Wordpress Plugin containing information which parameter is available on which station and should be used in the judging procedure.”
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

[Autogenerated table scheme of table “wp_wetterturnier_stations] Wetterturnier Wordpress Plugin containing stations in use.”
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.

[Autogenerated table scheme of table “wp_wetterturnier_webcams] Wetterturnier webcam specification.”
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

[Autogenerated table scheme of table “archive] Archive table, contains long-term observations (copy of the live table) for specified stations. Used for export tools.”
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

[Autogenerated table scheme of table “live] Rolling database for (raw) incoming observations. Used for plot/data views and export tools.”
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)