Database Tables

live

The live table is used to store incoming observations. Please note that only a subset of all columns is shown in the table below. The script processing the observations and saving them into this database table automatically creates additional columns if there are data. ... in the table indicate the data columns (e.g,. temperature observations, cloud cover observations, …).

The live table is a rolling database containing the latest observations for all incoming stations. This is a rolling database, older observations are deleted from time to time whereof the data for some selected stations are moved into the archive database table.

[Autogenerated table scheme of table “live] Rolling database for (raw) incoming observations.”
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)

archive

The archive table has the same structure as the live database table and contains long-term archive data for a set of specified stations. We keep the data for the tournament stations and drop all others as we don’t want to keep a copy of all observations (would be a huge database and an unnecessary and unused copy of everything).

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

wp_wetterturnier_obs

The script Observations.py is reading the observations from the live database table and prepares the observations as they will be used for the ranking. These observations are stored in this table used by the Wetterturnier Wordpress Plugin plugin to display the latest observations and by Persistenz.py to submit the Persistence reference forecast.

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

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

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)

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