Cumulusmx.db

From Cumulus Wiki
Jump to navigationJump to search

This page is the new Cumulusmx.db page. This page and subpages hold a complete rewrite of the Webtags related pages.

The information that was on this old Cumulusmx.db page has been moved to Cumulusmx.db (preserving history). You are advised to use that page until this page is ready and this message has been removed. If any questions or remarks please send a PM to HansR on the forum. Do not use the discussion tab unless accompanied by a PM: there is no automated signal to HansR if you use that page.

This page and its subpages will document the Cumulusmx.db from the point of view of the CumulusMX software and not the Cumulus 1 legacy software. Cumulus 1 is no longer maintained and will eventually disappear. If you are still using it, please refer to the original Cumulusmx.db (preserving history) page where @sfws has tried to give both packages equal value. That effort is no longer maintained in this Webtags page. We hope you will understand this approach.

Please do not edit this page while it is being worked on i.e. while this message is still here.


Introduction

In CumulusMX version 3, this database contains the Recent History. The SqlCache and the sqlite_sequence tables are not for normal usage. It is an SQLite datebase only to be maintained by CumulusMX. When removed it will be rebuilt. The database is stored in the data directory so its relative path is data/cumulusmx.db (case dependent on Linux).

A good tool to inspect this database is DB Browser for SQLite (external site). Editing the default tables takes you to unsupported territory but it might be educational to inspect.

Note: The CumulusMX Weather Diary also uses a SQLite database stored in data/diary.db.

The Recent Data Table

This table has the following columns:

Column number # Column name Column type Properties Description Example column value
0 Timestamp datetime Primary Key, can never be NULL, No Default Value, Dates and times for the last 7 days, at whatever interval available (at logging interval for historic archive data, at one minute intervals while MX is running) 2021-07-31 19:44:00
1 WindSpeed float Not NULL, No Default Value The average wind speed (see Wind_measurement#Weather_Stations_and_Cumulus for links) calculated at the stated time, if necessary converted to units requested 2.2593056529
2 WindGust float Not NULL, No Default Value The spot gust speed recorded at the stated time, if necessary converted to units requested 6.99691241605804
3 WindLatest float Not NULL, No Default Value The spot latest wind speed recorded at the stated time, if necessary converted to units requested 3.15989597323001
4 WindDir integer Not NULL, No Default Value The wind bearing (0 =calm), (1 to 360 = number of degrees) 315
5 WindAvgDir integer Not NULL, No Default Value The average wind bearing over the defined period 270
6 OutsideTemp float Not NULL, No Default Value The measured outside air temperature, if necessary converted to units requested 17.0
7 WindChill float Not NULL, No Default Value The wind chill, either calculated by MX, or supplied by weather station. (Note: strictly only valid for low temperatures and significant wind speeds) (In the example used here, temperature in previous column was too high for wind chill, so the value was set to agree with 'OutsideTemp' column instead)
8 DewPoint float Not NULL, No Default Value The dew point, either calculated by MX, or supplied by weather station, for the stated time. 11.9134549845367
9 HeatIndex float Not NULL, No Default Value The USA Heat index as calculated by Cumulus for the stated time (if the temperature is high enough) (In the example used here, temperature in 'OutsideTemp' column was too low for heat index, so the value was set to agree with 'OutsideTemp' column instead)
10 Humidity float Not NULL, No Default Value The Relative Humidity reported by the station at the stated time 72.0
11 Pressure float Not NULL, No Default Value The sea level pressure at the stated time, either as reported by the station or calculated by Cumulus from the absolute pressure reported by the station, if necessary converted to units requested 996.400024414062
12 RainToday float Not NULL, No Default Value The rainfall for today so far, calculated by Cumulus from the rain counter (column 15) now minus the rain counter at start of day, if necessary converted to units requested 4.5
13 SolarRad integer Not NULL, No Default Value Solar radiation value reported by station at stated time 200
14 UV float Not NULL, No Default Value The Ultra Violet Index reported by station at stated time 3.0
15 raincounter float Not NULL, No Default Value The rain counter is used by Cumulus for all rainfall outputs, this is its value at stated time 550.4
16 FeelsLike float Not NULL, No Default Value The Feels Like temperature calculated by MX at stated time 17.0782773978147
17 Humidex float Not NULL, No Default Value The Canadian Humidity Index, calculated by Cumulus at the stated time 19.1775362738161
18 AppTemp float Not NULL, No Default Value The Apparent temperature, calculated by MX at the stated time 16.8864476063546
19 IndoorTemp float Not NULL, No Default Value The indoor temperature directly as reported by station at stated time 120.89999961853
20 IndoorHumidity integer (0 decimal places, contrast with column 10) Not NULL, No Default Value The indoor relative humidity directly as reported by station at stated time 61
21 SolarMax integer Not NULL, No Default Value The calculated maximum value for solar radiation at stated time 1026
22 Pm2p5 float Not NULL, No Default Value Air Quality expressed in terms of particulate matter of 2.5 μm, or less, (?? Rolling 24 hours average ??) 1.5
23 Pm10 float Not NULL, No Default Value Air Quality expressed in terms of particulate matter of 10 μm, or less, (?? Rolling 24 hours average ??) 1.7
24 RainRate float Not NULL, No Default Value The rainfall rate calculated for the stated time 303.2

See Recent history page for more details.

SqlCache table

There is a MX setting (Settings -->> MySQL settings -->> General Options -->> Buffer commands on failure) that when enabled allows failed commands to be stored here.

This table holds a list of MySQL commands that are in the queue for MySqlConnect to run again.

Commands that fail because the database server is down are added to the queue when the setting is enabled (although MX will not save more than one query referencing same primary key). Because the error returned by the server cannot be predetermined, MX instead checks the error returned is not one of the following reasons before adding it to queue:

  • ParseError (the query does not make sense)
  • EmptyQuery (query has not been specified)
  • TooBigSelect (there is a limit to how much data can be selected)
  • InvalidUseOfNull (the schema has to say for each column whether null is permitted)
  • MixOfGroupFunctionAndFields (syntax can't mix these)
  • SyntaxError (an error in the SQL syntax)
  • TooLongString (there is a limit to what can be input)
  • WrongColumnName (the column name must exist in the referenced table/view)
  • DuplicateUnique (you can't insert a new row with same primary key as an existing row)
  • PrimaryCannotHaveNull (the primary key must be set to a specific value)
  • DivisionByZero (certain mathematical operations cannot be handled)


Here is the relevant create table instruction, so this tells you the 2 fields in the table as Mark Crossley created it:

CREATE TABLE "SqlCache"( "key" integer primary key autoincrement not null, "statement" varchar)

The table contents are simple to explain, each time a new command is stored it is assigned a new number (that is automatically incremented from last number used) and the command itself is stored in a variable text length entry.


CREATE TABLE sqlite_sequence (`name` TEXT, `seq` TEXT)