Cumulus Wiki

Cumulusmx.db (preserving history): Difference between revisions

Cumulusmx.db (preserving history) (view source)

Revision as of 04:45, 5 September 2022

3,767 bytes added ,  04:45, 5 September 2022
Rewritten for MX release 3.21.0
mNo edit summary
(Rewritten for MX release 3.21.0)
Line 1: Line 1:
[[Category:Cumulus Files]]
[[Category:Cumulus MX]][[Category:Cumulus Files]]


As the name suggests, this file '''/CumulusMX/data/cumulusmx.db''' (note case for Linux environments) is only applicable for MX. 
= Basic information =


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


This file holds a SQLite3 database.
==Location of this database file==


There are many software tools available that can read/edit SQLite3, this is not the place to get too technical, but take a look at '''sqlitebrowser''' in a Linux environment, and '''phpLiteAdmin''' in Microsoft Windows. Also, you could use any application that can read ODBC files (e.g. Libre Office's Base functionality), these can read the MX databases.
* This database file is specific to Cumulus MX (as the file name suggests), not used by legacy Cumulus 1
* The file is held in Random Access Memory in MX Releases 3.0.0 to 3.11.4 inclusive
* This database file is stored as '''/CumulusMX/data/cumulusmx.db''' (get case of each character correct unless Microsoft Windows Operating System) from release 3.12.0 (beta build 3134)


=Releases 3.0.0 to 3.11.4 inclusive=
==Database type==


The database only contains one table '''StandardData'''. 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]]. In [https://cumulus.hosiene.co.uk/viewtopic.php?p=113475#p113475 another forum post] he does give a little more detail about his proposal for Cumulus 3 (MX), and this is backed up by his choice of [[Standard log files|table name]] which 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.  Despite the evidence supporting that idea, it is a possible alternative that 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.  Whatever context it was to have been implemented in, its purpose was to improve the ability to [[Correcting Extremes|Correct Extremes Records]] by storing the most frequent and accurate extremes possible.
This file 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).


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):
The MX [[weather diary]] also uses a SQLite3 database stored in [[diary.db]].
CREATE TABLE "StandardData"(
 
"Timestamp" datetime primary key not null ,
== Reading/editing database table outside MX ==
"Interval" integer ,
 
"OutTemp" float ,
There are many software tools available that can read/edit SQLite3, should you wish to examine this database.
"LoOutTemp" float ,
 
"HiOutTemp" float ,
You could use any application that can read ODBC files (e.g. Libre Office's Base functionality), these can read the MX databases.
"DewPoint" float ,
 
"LoDewPoint" float ,
You might find '''sqlitebrowser''' easiest to use in a Linux environment.
"HiDewPoint" float ,
"WindChill" float ,
"LoWindChill" float ,
"HiWindChill" float ,
"InTemp" float ,
"LoInTemp" float ,
"HiInTemp" float ,
"Pressure" float ,
"LoPressure" float ,
"HiPressure" float )


Whenever I have viewed this table (with phpLiteAdmin), there has been zero rows within the table.  This is because although MX creates the table, currently it does not actually use it.
You might try '''phpLiteAdmin''' if your device runs a Microsoft Windows Operating System.




This table still exists in release 3.12.0 that introduces the second table described below.
= Database Tables included =


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


The first table, described above, remains in the database, but is still not being used.  I wonder whether MX in the future will actually implement the intended use of this first table, as described above.


Release 3.12.0 introduces a second table [[Recent history|'''RecentData''']] into the same database.  This is documented in the update.txt for CumulusMX at release 3.12.0 (beta build 3134) as "Change: Cumulus MX now uses a persistent database to store the recent 1 minute data
{| 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 in RAM
* 3.12.0 (beta build 3134) as 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
- 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".
- 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 are now 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
| 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]]
|}


This second table has the following columns:
== RecentData table ==
 
This table has the following columns:
{| border="1" class="wikitable"
{| border="1" class="wikitable"
|-
|-
Line 233: Line 266:
| 303.2
| 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):
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)
Sfws
5,838

edits

Retrieved from "https://www.cumuluswiki.org/a/Special:MobileDiff/10669"