Welcome to the Cumulus Support forum.

Latest Cumulus MX V3 release 3.28.6 (build 3283) - 21 March 2024

Cumulus MX V4 beta test release 4.0.0 (build 4017) - 17 March 2024

Legacy Cumulus 1 release v1.9.4 (build 1099) - 28 November 2014 (a patch is available for 1.9.4 build 1099 that extends the date range of drop-down menus to 2030)

Download the Software (Cumulus MX / Cumulus 1 and other related items) from the Wiki

Adding MySQL fields

From build 3044 the development baton passed to Mark Crossley. Mark has been responsible for all the Builds since. He has made the code available on GitHub. It is Mark's hope that others will join in this development, but at the very least he welcomes your ideas for future developments (see Cumulus MX Development suggestions).

Moderator: mcrossley

ahagadorn
Posts: 29
Joined: Thu 11 Aug 2022 1:24 pm
Weather Station: Davis Vantage Pro 2
Operating System: Debian Linux
Location: Piedmont, SC
Contact:

Adding MySQL fields

Post by ahagadorn »

I would like to add some fields to the Realtime MySQL table that get updated by the normal Cumulus MySQL update process, if possible.

My question is, if I add an extra template file such as realtime-xT.txt and set it to be processed via the admin interface, will it automatically be added to the MySQL Realtime table insert in addition to the realtime.txt file? And if so, will I need to add the new column to the table manually or will it create the new column automatically the first time it runs?

I was going to just go ahead and try it, but decided I would see if anyone has done it already.
Mapantz
Posts: 1774
Joined: Sat 17 Dec 2011 11:55 am
Weather Station: Davis Vantage Pro2
Operating System: Windows 11 x64
Location: Dorset - UK
Contact:

Re: Adding MySQL fields

Post by Mapantz »

You can't add extra data to the realtime/monthly/dayfile sql tables.

I tried it with the realtime table ages ago, by adding a couple of new columns and adding a custom sql command to fill those columns. They basically overwrite the data and you end up with a complete mess.

You'll need to make your own tables and then use the custom sql commands in CMX to add to them.
Image
cliftonweather
Posts: 25
Joined: Sat 14 Nov 2009 12:34 pm
Weather Station: Vantage pro+
Operating System: RPi3 Model 3 running Buster
Location: Clifton, Beds
Contact:

Re: Adding MySQL fields

Post by cliftonweather »

I added extra columns to the Dayfile table and populate these using a procedure, you could also use a trigger. I don't see why this would not work with other tables like realtime.
That said you should be able to do something like this in the Custom upload - minutes interval:-

UPDATE Realtime SET *colname* = <#tagname> WHERE DATE_FORMAT(LogDateTime, '%Y-%m-%d') = DATE_FORMAT(CURDATE(), '%Y-%m-%d') ORDER BY LogDateTime DESC limit 1

This will populate the desired field in the last inserted row.


Maybe this is something for a future release, the ability to add custom columns to tables & populate then with whatever tags.
User avatar
HansR
Posts: 5870
Joined: Sat 20 Oct 2012 6:53 am
Weather Station: GW1100 (WS80/WH40)
Operating System: Raspberry OS/Bullseye
Location: Wagenborgen (NL)
Contact:

Re: Adding MySQL fields

Post by HansR »

cliftonweather wrote: Fri 12 Aug 2022 8:01 am I added extra columns to the Dayfile table and populate these using a procedure, you could also use a trigger. I don't see why this would not work with other tables like realtime.
That said you should be able to do something like this in the Custom upload - minutes interval:-

UPDATE Realtime SET *colname* = <#tagname> WHERE DATE_FORMAT(LogDateTime, '%Y-%m-%d') = DATE_FORMAT(CURDATE(), '%Y-%m-%d') ORDER BY LogDateTime DESC limit 1

This will populate the desired field in the last inserted row.

Maybe this is something for a future release, the ability to add custom columns to tables & populate then with whatever tags.
Suit yourself but modifying system tables is - in good prectice - the privilege of development. And besides that, if development decides to add new tables/files you can be sure he will not ask you for permission meaning that your modifications will bring you in [update] troubles.

In short: modifying system tables / files is asking for trouble (not for others but for yourself).

CMX is an inviting environment for user developments but you should never touch the core unless permitted or asked for by development (unless you wish to make a development branch independent of the core). @mcrossley currently is in charge and sets the goals.
Hans

https://meteo-wagenborgen.nl
CMX build 4017+ ● RPi 3B+ ● Raspbian Linux 6.1.21-v7+ armv7l ● dotnet 8.0.3
sfws
Posts: 1183
Joined: Fri 27 Jul 2012 11:29 am
Weather Station: Chas O, Maplin N96FY, N25FR
Operating System: rPi 3B+ with Buster (full)

Re: Adding MySQL fields

Post by sfws »

NOTE: When release 3.20.0 becomes available, there is some extra "MySQLConnect" functionality regarding changes to columns. The MySQL settings page has some substantial changes in that release which will no doubt make more of the Wiki out of date.
I do not believe that will affect anything in this thread, but I mention that because my answers below are based on Current MX functionality, i.e. up to current MX 3.19.3 release
cliftonweather wrote: Fri 12 Aug 2022 8:01 am Maybe this is something for a future release, the ability to add custom columns to tables & populate then with whatever tags
MX has offered SQL for tables with your own choice of columns, plus use of tags in Custom queries, in every release including Steve Loft's original beta Cumulus 3/MX build 3025. POST EDIT: Made that into cross-reference link.

I need to stress to everyone, (contradicts an earlier reply), having non-standard tables has never conflicted with Cumulus development, and several Cumulus users do customise their databases. All my custom tables are updated using custom MySqlConnect functionality (where column corresponds with tag) and/or the external programs functionality (columns that don't exactly match a tag) of Cumulus. My non-standard tables have been in use since 2014, although I have sometimes needed to tweak my scripts because of changes made by Cumulus developer.
ahagadorn wrote: Thu 11 Aug 2022 1:51 pm if I add an extra template file such as realtime-xT.txt and set it to be processed via the admin interface, will it automatically be added to the MySQL Realtime table insert in addition to the realtime.txt file? And if so, will I need to add the new column to the table manually or will it create the new column automatically the first time it runs?
MX functionality does offer two ways to achieve your desired outcome, neither are implemented exactly as you suggest in that quote.

OPTION 1: The best solution is to effectively create a new table realtime_x that mimics the fields in realtime-xT.txt. Instead of creating a new table, it is easiest to add the extra columns (as you propose) to the existing default realtime table (and optionally rename that table). Next on the "MySQL settings page" disable "Realtime.txt Upload", and then enable "Custom upload - Seconds interval". Now you have to define a custom query to INSERT rows into your table as mentioned in previous replies at chosen interval (set in seconds) automatically; so this option is not that far off what you suggested. The Wiki documentation could offer further help as it gives an example query involving both an INSERT and an UPDATE based on 3.12.0 functionality.
You probably need to include a query to delete older rows as obviously this option does require some action to control number of rows in a table that gains new rows (with datetime based primary key) at a very frequent interval. (As mentioned earlier, the settings page is changing at 3.20.0, it will offer 10 separate queries at seconds, minutes, and rollover intervals; but currently as the Wiki explains more than one query can be entered as a single input).
The alternative is to only have one row with fixed primary key (chosen content can be any fixed text), and frequently replace that row (i.e. mimic how file works).

OPTION 2: It is possible to add columns EDIT: (that have null content as default) to a Cumulus default table, the default MySQLConnect commands always define column names in any insert/update, so whatever MX does by default just ignores the extra columns. The rule (up to 3.19.3 release) is that every column mentioned by MX must exist in the destination table, so that does not exclude possibility of additional columns. (I believe 3.20.0 may include code to automatically create columns it wants to insert/update so MX can be used with existing tables that might have been created by an older release with fewer columns).

As the default query does not affect any user added extra columns, you use the ability of Cumulus to run a script at realtime/standard/rollover intervals to populate the additional columns using UPDATE after MX has INSERTed the relevant row.
I am not convinced this is viable for the realtime interval. As mentioned in an earlier reply, this can be messy, you need to have the timing right for running that script (I believe MX does run the MySQLConnect first in a cycle at any interval, and then initiates "External programs" (within Internet settings) later in that interval cycle), that script needs to access a file containing the values (a processed template), and you need to have the timing right for anything that reads the database table.

It works in my setup at the normal standard interval (my table differs from default in that I start a new table each year) as after MX has INSERTed its default columns for a row, I have a script that I initiate outside MX which UPDATEs my extra columns in any rows created since its previous run. POST EDIT: Clarification tweak to previous sentence.
Last edited by sfws on Sat 20 Aug 2022 6:36 am, edited 2 times in total.
User avatar
mcrossley
Posts: 12689
Joined: Thu 07 Jan 2010 9:44 pm
Weather Station: Davis VP2/WLL
Operating System: Bullseye Lite rPi
Location: Wilmslow, Cheshire, UK
Contact:

Re: Adding MySQL fields

Post by mcrossley »

Personally, I would add the additional fields to your own tables rather than alter the standard tables for the reasons sfws gives.

If you want all the data presented in a single select, I'd create views that combined that data from the standard CMX tables with your custom tables. This will be efficient if your tables use the same primary keys of LogDate (Dayfile) or LogDateTime (Monthly & Realtime)
cliftonweather
Posts: 25
Joined: Sat 14 Nov 2009 12:34 pm
Weather Station: Vantage pro+
Operating System: RPi3 Model 3 running Buster
Location: Clifton, Beds
Contact:

Re: Adding MySQL fields

Post by cliftonweather »

HansR wrote: Fri 12 Aug 2022 8:31 am Suit yourself but modifying system tables is - in good prectice - the privilege of development. And besides that, if development decides to add new tables/files you can be sure he will not ask you for permission meaning that your modifications will bring you in [update] troubles.

In short: modifying system tables / files is asking for trouble (not for others but for yourself).

CMX is an inviting environment for user developments but you should never touch the core unless permitted or asked for by development (unless you wish to make a development branch independent of the core). @mcrossley currently is in charge and sets the goals.
Surely the MySQL tables are not system tables, as they are just an additional option, CMX doesn't read or need the tables.

Any new fields added by the developers I just add before the fields I have added.

Just make sure any fields you do add can have NULL values.

Yes you could create new tables, which I also have done, but adding extra sensors to the Dayfile table works for the way I build my website, it's been working with no problems for the last 5 years.
sfws
Posts: 1183
Joined: Fri 27 Jul 2012 11:29 am
Weather Station: Chas O, Maplin N96FY, N25FR
Operating System: rPi 3B+ with Buster (full)

Re: Adding MySQL fields

Post by sfws »

cliftonweather wrote: Sat 13 Aug 2022 7:34 am Just make sure any fields you do add can have NULL values.
Excellent point - sorry my Option 2 did not stress that. If you add extra columns, they must default to null values, so any standard MX MySQLConnect inserts can ignore those columns.
cliftonweather wrote: Sat 13 Aug 2022 7:34 am Any new fields added by the developers I just add before the fields I have added.
Any of the MX standard tables can have columns in any order. The table creation functionality in MX places the columns in the same order as the underlying file, i.e. based on historical sequence of adding each field to line in Cumulus file. As each standard MX MySQLConnect insert, or update, names all columns, it does not have a dependence upon that sequence. In any MX standard table, all wind related columns could be together, followed by all rainfall related columns, etc.
I believe some Cumulus users have rearranged columns in database tables to either achieve that grouping, or to show columns of most interest earlier than those of least interest.
mcrossley wrote: Fri 12 Aug 2022 6:37 pm If you want all the data presented in a single select, I'd create views that combined that data from the standard CMX tables with your custom tables.
In the suggestions part of this forum, Freddie points out that the current MX design involves mixing (not his words) static and transient content; e.g. the default realtime table repeats in every row some static information (like units) that should only exist once. It would be better if MX had a redesign such that people were encouraged to use View approach to combine a table holding static data (his suggestion for that table is a new upload only at MX start up), with any table holding transient data (current observations or derivatives of those). As both Freddie and I have remarked before, this mixing of stale and fresh content applies to the design of the .json file based data upload for the default web site.

In my earlier post, I deliberately did not mention a potential Option 3 to satisfy the request that started this topic by combining data from the standard CMX tables with a custom table for columns not in the default realtime table. MX cannot synchronise the realtime interval that optionally generates the insert of a row into the default realtime table with the seconds interval used for any customs seconds MySQLConnect actions, this is a messy design as the two tables could slowly drift out of correspondence, although that is unlikely to have any practical implications.

More to the point, third party functionality either uses the internally generated optional realtime.txt file or asks Cumulus to process (via Extra Files) one or more customised template files (from CUtagsT.php, realtimetagsT.php, realtime-xT.txt, some XML options). No third party uses both the internally generated default file and their own file(s) as far as I am aware. Therefore in the original post the request is presumably considering swapping from uploading each resulting realtime-x.txt file to using a database equivalent, and it makes sense for the (my Option 1) custom table to hold all the columns in a single table to mimic the file.
freddie
Posts: 2434
Joined: Wed 08 Jun 2011 11:19 am
Weather Station: Davis Vantage Pro 2 + Ecowitt
Operating System: GNU/Linux Ubuntu 22.04 LXC
Location: Alcaston, Shropshire, UK
Contact:

Re: Adding MySQL fields

Post by freddie »

sfws wrote: Sun 14 Aug 2022 5:47 amMX cannot synchronise the realtime interval that optionally generates the insert of a row into the default realtime table with the seconds interval used for any customs seconds MySQLConnect actions
I thought of this earlier in the year and, instead of using date/time tags for my LogDateTime value in the INSERT statements for my additional realtime tables, I use this:

Code: Select all

(SELECT MAX(LogDateTime) from Realtime)
This synchronises LogDateTime in all realtime tables so that views across the tables will work properly. Admittedly the LogDateTime value from the Realtime table is approximately 0.3 to 0.5 seconds different to the time the data was read, but I don't think that makes a real difference to the data being stored.
Freddie
Image
User avatar
mcrossley
Posts: 12689
Joined: Thu 07 Jan 2010 9:44 pm
Weather Station: Davis VP2/WLL
Operating System: Bullseye Lite rPi
Location: Wilmslow, Cheshire, UK
Contact:

Re: Adding MySQL fields

Post by mcrossley »

I did think about adding a special tag for last insert datetime for each table which would solve this.

And yes, it is not optimal having reference and dynamic data mixed in the same tables, but changing the schema now would be fraught.
freddie
Posts: 2434
Joined: Wed 08 Jun 2011 11:19 am
Weather Station: Davis Vantage Pro 2 + Ecowitt
Operating System: GNU/Linux Ubuntu 22.04 LXC
Location: Alcaston, Shropshire, UK
Contact:

Re: Adding MySQL fields

Post by freddie »

mcrossley wrote: Sun 14 Aug 2022 11:58 am I did think about adding a special tag for last insert datetime for each table which would solve this.
That would be really useful as I'm not keen on doing a select for every insert - especially at the realtime interval.
mcrossley wrote: Sun 14 Aug 2022 11:58 amAnd yes, it is not optimal having reference and dynamic data mixed in the same tables, but changing the schema now would be fraught.
Indeed. Could we still have a "start-up" custom upload interval though, please? :)
Freddie
Image
sfws
Posts: 1183
Joined: Fri 27 Jul 2012 11:29 am
Weather Station: Chas O, Maplin N96FY, N25FR
Operating System: rPi 3B+ with Buster (full)

Re: Adding MySQL fields

Post by sfws »

Post edited to rearrange unchanged text under sub-headings for easier readability.
mcrossley wrote: Sun 14 Aug 2022 11:58 am changing the schema now would be fraught.
Really?
I don't use your "realtime" table, so it is irrelevant to me.
I have not studied your code, but I can't believe changing the schema for that simple table implies that "fraught" is the right descriptor.


THIRD-PARTY AUTHORS:
I don't know how many third-party authors have written SQL to read your "realtime" table, but I would guess it is a extremely small number, and each of these authors must have some SQL expertise to have written their existing query. Each of those people needs to:
1) Write a one-off utility for a CREATE VIEW.
2) Make a simple change to their retrieval SQL to read the VIEW instead of reading the TABLE.


CUMULUS MX EDITS:
MX does not include any functionality that reads this table, so there is nothing in MX to edit on retrieval side.
Adding code that creates a table for what you call reference data should be easy, it can be populated with data at time of table creation, as we are talking about data that rarely changes. This change might even be combined with satisfying Freddie's request for a "start-up" custom upload, as both require some changes to MySQL settings page (already changing significantly in your 3.12.0 coding).
Your current CREATE table command for your "realtime" table should be straightforward edit, just delete the column names/type specifiers that relate to "reference" data.
I am guessing there is just one INSERT statement in cumulus.cs that adds rows to this table. There the only change is to remove those column names and values that refer to "reference" data.


AFFECTED USERS:
Any Cumulus user who uses code provided by the extremely small number of third-party tools that incorporate use of your "realtime" table, could update their installation, as normal for a change in any third-party functionality they use.

The number of users affected by the MX create/insert edits would be tiny proportion of people compared to numbers of users using functionality affected by impact of edits in many past MX builds.


POSSIBLE PROBLEMS:
Anticipating you might say the problem is that the unwanted columns are currently defined as NOT NULL, then a solution is to write a one-off utility for altering any existing "realtime" table. The utility would issue ALTER realtime ... (There could then be two options, either DROP column-names that relate to units etc, or MODIFY column-name to allow the unwanted columns to default to null, so INSERT can ignore them). (The MODIFY column-name alternative would let existing retrieval SQL continue to work, although it would see NULLs in no longer used columns, until new code from the third-party as above to read the View has been installed).
Last edited by sfws on Mon 15 Aug 2022 5:41 am, edited 1 time in total.
freddie
Posts: 2434
Joined: Wed 08 Jun 2011 11:19 am
Weather Station: Davis Vantage Pro 2 + Ecowitt
Operating System: GNU/Linux Ubuntu 22.04 LXC
Location: Alcaston, Shropshire, UK
Contact:

Re: Adding MySQL fields

Post by freddie »

I think the "fraught" comment would be more related to the effect on end users. Changing the schema is straightforward - it's the knock-on effect (and the subsequent increase in support queries) which is the tricky bit. Personally, I would be faced with changes to the API that my web server uses to access data from the database. Not that I would mind in the slightest, but it wouldn't be the smooth and quick upgrade of regular new releases.
Freddie
Image
User avatar
mcrossley
Posts: 12689
Joined: Thu 07 Jan 2010 9:44 pm
Weather Station: Davis VP2/WLL
Operating System: Bullseye Lite rPi
Location: Wilmslow, Cheshire, UK
Contact:

Re: Adding MySQL fields

Post by mcrossley »

freddie wrote: Sun 14 Aug 2022 4:19 pm I think the "fraught" comment would be more related to the effect on end users.
Quite, changing MX itself would be fairly trivial. It's the knock on effects of doing that that would concern me.
User avatar
mcrossley
Posts: 12689
Joined: Thu 07 Jan 2010 9:44 pm
Weather Station: Davis VP2/WLL
Operating System: Bullseye Lite rPi
Location: Wilmslow, Cheshire, UK
Contact:

Re: Adding MySQL fields

Post by mcrossley »

I was thinking about this one, and...
Q: When at start-up?
Before station initialisation.
After station initialisation, before catch-up
After station initialisation, after catch-up, before normal running (timers start)
After normal running

In the meantime, could you use the newly added start-up task to run a MySQL command? That would be run at option one above, Before station initialisation.
Post Reply