Cumulusmx.db (preserving history): Difference between revisions

From Cumulus Wiki
Jump to navigationJump to search
mNo edit summary
m (HansR moved page Cumulusmx.db to Cumulusmx.db (preserving history) without leaving a redirect: Rewrite of contents)
 
(13 intermediate revisions by one other user not shown)
Line 1: Line 1:
[[Category:Log Files]]
[[Category:Cumulus MX]][[Category:Cumulus Files]]


As the name suggests, this file is only applicable for MX. It contains one table '''StandardData''' in the SQLite3 database in this file.
= Basic information =
 
Skip this section if you want details specific to a particular database table.
 
==Location of this database file==
 
* This database file/files is/are specific to Cumulus MX (as the file name suggests), not used by legacy Cumulus 1
* For MX Releases 3.0.0 to 3.11.4 inclusive, there were two databases with this name:
*# One database table with this name was held in Random Access Memory for MX Releases 3.0.0 to 3.11.4 inclusive
*# One database table with this name is stored as '''CumulusMX/data/cumulusmx.db''' (get case of each character correct unless Microsoft Windows Operating System)
* The two database tables were merged from release 3.12.0 (beta build 3134) and stored as '''/CumulusMX/data/cumulusmx.db''' (get case of each character correct unless Microsoft Windows Operating System)
 
==Database type==
 
This file/files holds a SQLite3 database. Databases of this type are based on sequential file design, i.e. each row in the database table is sequenced by when created (new rows are only appended on end), and given a sequential number to act as primary key (numbers are not automatically revised when a row is deleted).
 
The MX [[Weather Diary]] also uses a SQLite3 database stored in [[diary.db|CumulusMX/data/diary.db]].
 
== Reading/editing database table outside MX ==
 
There are many software tools available that can read/edit SQLite3, should you wish to examine this database.
 
You could use any application that can read ODBC files (e.g. Libre Office's Base functionality), these can read the MX databases.
 
You might find '''sqlitebrowser''' easiest to use in a Linux environment.
 
You might try '''phpLiteAdmin''' if your device runs a Microsoft Windows Operating System.
 
 
= Database Tables included =
 
The following table lists the tables found in this database, indicating which release added the table, and where further details can be found.
 
 
{| border="1" class="wikitable"
|-
! style="width:80px" | Table name
! style="width:120px" | How MX updates
! style="width:80px" | Introduced
! style="width:450px" | Description
! style="width:220px" | Editor within MX
! style="width:120px" | Further details
|-
| RecentData
| Updated once a minute when MX is running
| MX beta 3.0.0
* Database of this name with this table originally in RAM
* In 3.12.0 (beta build 3134) table moved to external file
| The update.txt for CumulusMX at release 3.12.0 (beta build 3134) states "Change: Cumulus MX now uses a persistent database to store the recent 1 minute data
- This means that charts, recent web tags, and internal calculations for trends and periodic values will be more accurate after a restart
- If Cumulus MX is offline for a prolonged period, data for that offline period will obviously still be at the station logging interval resolution".
| No editor for Cumulus user
| [[Recent history]] and [[#RecentData table]]
|-
| SqlCache
| Updated when certain conditions are met and SQL is queued
| MX 3.21.0
| The update.txt for CumulusMX at release 3.21.0 states "New - Failed MySQL commands can now be individually edited/deleted
 
Changed - Failed MySQL commands are now stored in the SQLite database to persist across Cumulus runs"
 
| Edit or purge the failed MySQL command queue using '''Utils menu''' -->> ''PurgeMySQL''
| [[MySqlConnect]] and [[#SqlCache table]]
|-
| StandardData
| At time of writing, the current MX release does not use this table
| MX beta 3.0.0
(always in file)
| In [https://cumulus.hosiene.co.uk/viewtopic.php?p=123594#p123594 this forum post] Steve Loft describes this table as being for a possible future enhancement.   It is believed this is the table that Steve had developed in Cumulus 2, as a replacement for the [[Monthly_log_files]].
| At time of writing, the current MX release does not use this table
| [[#StandardData table]]
|-
| sqlite_sequence
| Developer has not stated when updated
| MX 3.21.0
| Developer has not supplied a description
| Not known
| [[#sqlite_sequence table]]
|}
 
== RecentData table ==
 
This table has the following columns:
{| border="1" class="wikitable"
|-
! style="width:10px" | Column number #
! style="width:80px" | Column name
! style="width:80px" | Column type
! style="width:280px" | Properties
! style="width:450px" | Description
! style="width:120px" | 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 [[Temperature_(and_humidity)_measurement|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 [[Temperature_(and_humidity)_measurement#Cumulus_Calculated_Parameters|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 (1 decimal place)
| Not NULL, No Default Value
| The [[Temperature_(and_humidity)_measurement|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 [[Humidex|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 [[FAQ#How_is_my_rain_rate_calculated.3F|rainfall rate]] calculated for the stated time
| 303.2
|}
 
See [[Recent history|'''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.
 
 
==StandardData table==
 
At time of writing this documentation, the latest MX release does not use this table, and it has zero rows. A [https://cumulus.hosiene.co.uk/viewtopic.php?p=113475#p113475 forum post by Steve Loft] about a database based logging proposal for Cumulus 3 (MX) confirms the original purpose was to improve the ability to [[Correcting Extremes|Correct Extremes Records]] by storing the most frequent and accurate extremes possible.
 
While that does not specifically explain this table, his choice of [[Standard log files|table name]] suggests he was planning to either replace, or duplicate, the use of text files for logging data.  It appears he was actually thinking about implementing [[Monthly_log_files#Enhancement never implemented]] from his inclusion of "Lo" and "Hi" values in the column list. 
 
The only alternative theory is Steve Loft actually thought he could improve on the [[Recent history]] functionality, by storing the highest and lowest readings read from weather station in-between the one minute resolution.
 
Here is the relevant create table instruction, so this tells you the 17 fields in the table as Steve created it (these have never been changed):


Here is the relevant create table instruction, so this tells you the 17 fields in the file:
CREATE TABLE "StandardData"(
CREATE TABLE "StandardData"(
"Timestamp" datetime primary key not null ,
"Timestamp" datetime primary key not null ,
Line 24: Line 330:




It is not documented anywhere I have seen what option you have to turn on in MX to start this database being updated.  The file is not included in the release distribution to ensure any data you have in the database table is kept when you install a new MX version.


<big>Could anyone who knows what option in MX updates this file, and in what context use is made of this database, please explain it by amending this article</big>
 
==sqlite_sequence table==
 
Here is the relevant create table instruction, so this tells you the 2 fields in the table as Mark Crossley created it:
 
CREATE TABLE sqlite_sequence (`name` TEXT, `seq` TEXT)

Latest revision as of 09:09, 1 October 2022


Basic information

Skip this section if you want details specific to a particular database table.

Location of this database file

  • This database file/files is/are specific to Cumulus MX (as the file name suggests), not used by legacy Cumulus 1
  • For MX Releases 3.0.0 to 3.11.4 inclusive, there were two databases with this name:
    1. One database table with this name was held in Random Access Memory for MX Releases 3.0.0 to 3.11.4 inclusive
    2. One database table with this name is stored as CumulusMX/data/cumulusmx.db (get case of each character correct unless Microsoft Windows Operating System)
  • The two database tables were merged from release 3.12.0 (beta build 3134) and stored as /CumulusMX/data/cumulusmx.db (get case of each character correct unless Microsoft Windows Operating System)

Database type

This file/files holds a SQLite3 database. Databases of this type are based on sequential file design, i.e. each row in the database table is sequenced by when created (new rows are only appended on end), and given a sequential number to act as primary key (numbers are not automatically revised when a row is deleted).

The MX Weather Diary also uses a SQLite3 database stored in CumulusMX/data/diary.db.

Reading/editing database table outside MX

There are many software tools available that can read/edit SQLite3, should you wish to examine this database.

You could use any application that can read ODBC files (e.g. Libre Office's Base functionality), these can read the MX databases.

You might find sqlitebrowser easiest to use in a Linux environment.

You might try phpLiteAdmin if your device runs a Microsoft Windows Operating System.


Database Tables included

The following table lists the tables found in this database, indicating which release added the table, and where further details can be found.


Table name How MX updates Introduced Description Editor within MX Further details
RecentData Updated once a minute when MX is running MX beta 3.0.0
  • Database of this name with this table originally in RAM
  • In 3.12.0 (beta build 3134) table moved to external file
The update.txt for CumulusMX at release 3.12.0 (beta build 3134) states "Change: Cumulus MX now uses a persistent database to store the recent 1 minute data

- This means that charts, recent web tags, and internal calculations for trends and periodic values will be more accurate after a restart - If Cumulus MX is offline for a prolonged period, data for that offline period will obviously still be at the station logging interval resolution".

No editor for Cumulus user Recent history and #RecentData table
SqlCache Updated when certain conditions are met and SQL is queued MX 3.21.0 The update.txt for CumulusMX at release 3.21.0 states "New - Failed MySQL commands can now be individually edited/deleted

Changed - Failed MySQL commands are now stored in the SQLite database to persist across Cumulus runs"

Edit or purge the failed MySQL command queue using Utils menu -->> PurgeMySQL MySqlConnect and #SqlCache table
StandardData At time of writing, the current MX release does not use this table MX beta 3.0.0

(always in file)

In this forum post Steve Loft describes this table as being for a possible future enhancement. It is believed this is the table that Steve had developed in Cumulus 2, as a replacement for the Monthly_log_files. At time of writing, the current MX release does not use this table #StandardData table
sqlite_sequence Developer has not stated when updated MX 3.21.0 Developer has not supplied a description Not known #sqlite_sequence table

RecentData 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 (1 decimal place) 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.


StandardData table

At time of writing this documentation, the latest MX release does not use this table, and it has zero rows. A forum post by Steve Loft about a database based logging proposal for Cumulus 3 (MX) confirms the original purpose was to improve the ability to Correct Extremes Records by storing the most frequent and accurate extremes possible.

While that does not specifically explain this table, his choice of table name suggests he was planning to either replace, or duplicate, the use of text files for logging data. It appears he was actually thinking about implementing Monthly_log_files#Enhancement never implemented from his inclusion of "Lo" and "Hi" values in the column list.

The only alternative theory is Steve Loft actually thought he could improve on the Recent history functionality, by storing the highest and lowest readings read from weather station in-between the one minute resolution.

Here is the relevant create table instruction, so this tells you the 17 fields in the table as Steve created it (these have never been changed):

CREATE TABLE "StandardData"( "Timestamp" datetime primary key not null , "Interval" integer , "OutTemp" float , "LoOutTemp" float , "HiOutTemp" float , "DewPoint" float , "LoDewPoint" float , "HiDewPoint" float , "WindChill" float , "LoWindChill" float , "HiWindChill" float , "InTemp" float , "LoInTemp" float , "HiInTemp" float , "Pressure" float , "LoPressure" float , "HiPressure" float )



sqlite_sequence table

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

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