Cumulusmx.db: Difference between revisions

4,000 bytes removed ,  1 October 2022
m
'''Note''': The CumulusMX [[Weather Diary]] also uses a SQLite database stored in [[diary.db|data/diary.db]].
 
= The Recent Data Table =
= 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:
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.
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):
 
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)