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.