Cumulus Wiki

MySqlConnect: Difference between revisions

MySqlConnect (view source)

Revision as of 08:23, 13 September 2022

6,586 bytes added ,  08:23, 13 September 2022
m
Update for 3.21.1
m (Update for 3.21.1)
 
Line 1: Line 1:
=Introduction=  
[[Category:Configuration Files]][[Category:Cumulus MX]]
 
This Wiki page provides a brief introduction to SQL, and describes the settings that define how CMX functionality is controlled.
 
 
= Why are Cumulus settings now split between several Wiki pages? =


[[Category:Configuration Files]][[Category:Cumulus MX]]
The basic answer is because there are a lot of settings, some get changed, and more are added as Cumulus develops!


In May 2015, Steve Loft [https://cumulus.hosiene.co.uk/viewtopic.php?p=138868#p138868 added SQL functionality] to his Cumulus 3 software (MX beta 3.0.0).
MX has been developed very dramatically by Mark Crossley:
#  The MySQL settings for the original 3.0.0 beta were previously documented on [[Cumulus.ini (Beta)]] page, but that page has been cleared.
# The dramatic development of MX produced considerable differences between that original MX beta, and the configuration that applied up to 3.7.0, [[Cumulus.ini (MX 3.0.0 to 3.7.0)|that latter documentation remains available here]]
# Even more dramatic changes to the MX configuration have been happening since 3.8.0, with the biggest changes at 3.9.2, 3.10.0, and 3.12.0; therefore, <big>the decision was taken in June 2021 to abandon maintaining the page previously called "Cumulus.ini", and start again with a brand new page now found [[Cumulus.ini|here]]!</big>
# Since the MySQL settings have continued to change, since 2021 they have been moved here from that last mentioned page.


Essentially, MX can ''either'' create and update one, or more, of three tables (by default called "realtime", "monthly", and "dayfile"; although these names can be changed by the Cumulus user) where it determines the '''schema''' (what columns appear in the database table); ''or'' MX can run SQL (devised by user) against tables (created by user) that can have any "schema" (columns in the table) chosen by user at one, or more, of three intervals (MX calls these "custom seconds", "custom minutes", and "custom rollover").
All the pages, previously called "Cumulus.ini", can be found from the owning [[:Category:Configuration Files|category]].  The original page was preserved at [[Cumulus.ini_(preserving_history)]] so look there for its editing history.  That page also expands on the above summary of why that old page was replaced.


This Wiki page provides a brief introduction to SQL, and describes the settings that define how this functionality is controlled.


At last update, this Wiki page shows position as at beta build 3135, (missing some changes included in the 3.12.0 release build of 3141).  It is therefore somewhat out of date, in regard to latest MX release, but it is hoped that presenting it as a separate Wiki page will make it more likely that somebody will bring it up to date.
=What is SQL?=


SQL is an abbreviation of "Structured Query Language", SQL is structured in the sense that keywords have to appear in the correct order, and there are rules about which words are mandatory.


==What is SQL?==
SQL is not just for running queries that read database tables, it can create database tables, give and revoke permissions, and do many more maintenance type tasks.


SQL is an abbreviation of "Structured Query Language", SQL is structured in the sense that keywords have to appear in the correct order, and there are rules about which words are mandatory. SQL is not just for running queries that read database tables, it can create database tables, give and revoke permissions, and do many more maintenance type tasks. SQL is a language in that each variant of SQL has a vocabulary and set of rules that are specific to that database server, although there is a sub-set, that is set by "ANSI", of words and constructions that all SQL dialects should obey.
SQL is a collection of languages. Each variant of SQL has a vocabulary and set of rules that are specific to that database server, although there is a sub-set, that is set by "ANSI", of words (and constructions) that all SQL dialects should obey.


==How does MX use SQL?==
==How does MX use SQL?==
Line 24: Line 33:
MX also uses SQL when creating, or updating, a relational database type. The commands issued by "MySqlConnect" software work with two types of relational databases,  MySQL (commercial software by Oracle) and MariaDB (free software from an independent provider).  A relational database also uses the row, column, and field terminology; but there is no control over the order in which rows are stored, there is an order for columns (although you can change the column order), and rows are identified by a unique key (known as "primary key" as a row can contain a secondary key that links to data in another table).  Any retrieval query can specify that what is returned from that query should be sorted in an ascending/descending order of the value(s) in specified column(s).
MX also uses SQL when creating, or updating, a relational database type. The commands issued by "MySqlConnect" software work with two types of relational databases,  MySQL (commercial software by Oracle) and MariaDB (free software from an independent provider).  A relational database also uses the row, column, and field terminology; but there is no control over the order in which rows are stored, there is an order for columns (although you can change the column order), and rows are identified by a unique key (known as "primary key" as a row can contain a secondary key that links to data in another table).  Any retrieval query can specify that what is returned from that query should be sorted in an ascending/descending order of the value(s) in specified column(s).


As mentioned earlier, MX supports (default) tables where it determines the columns in the table , and (custom) tables where the user determines the columns.  For the default tables, every MySqlConnect command issued by MX specifies column names, so those columns must be present in the table, but can be in any order.  (If the user adds extra columns, those extra columns must be defined with null as default value, so MX can ignore them).  Some MX releases add columns that were not present in earlier MX releases, and these releases should provide a utility that will add the extra columns to existing tables.
==How does MX use MySqlConnect?==
 
===How does MX use MySqlConnect?===


MySqlConnect is used by both [[Software#Current_Release|CumulusMX.exe]] and [[Software#Export_To_MySQL|ExportToMySQL.exe]], but the latter only works with two of the default tables (those called "monthly" and "dayfile", or as renamed by the user).
MySqlConnect is used by both [[Software#Current_Release|CumulusMX.exe]] and [[Software#Export_To_MySQL|ExportToMySQL.exe]], but the latter only works with two of the default tables (those called "monthly" and "dayfile", or as renamed by the user).
Line 76: Line 83:


Please note, the text included elsewhere on this Wiki page may be for different release.
Please note, the text included elsewhere on this Wiki page may be for different release.
==Changes by MX release==
This Wiki page was created 20 August 2022, by doing a "cut" from the [[Cumulus.ini]] page as it had existed prior to then, and a "paste" onto this page. 
# In May 2015, Steve Loft [https://cumulus.hosiene.co.uk/viewtopic.php?p=138868#p138868 added SQL functionality] to his Cumulus 3 software (MX beta 3.0.0).
# The information transferred in the cut/paste mentioned above related to the beta build 3135 of 3.12.0 (before subsequent development that led to formal release on 29 July 2021).
# Further development of MX, by Mark Crossley, resulted in changes at releases between 3.20.0 and 3.21.1, which have been reported below. 


{{Template:WorkInProgressBanner}}
{{Template:WorkInProgressBanner}}
Since this Wiki page describes settings that frequently change, this page may be out of date, unless somebody is willing to update the Wiki when a MX release makes a change.


== Predetermined SQL ==
Whether this page reflects the latest MX release, depends on whether there have been any further developments, and whether any contributor has kept this page up to date.
 
 
{| class="wikitable" border="1"
|-
!style="width:30px" | Release
!style="width:50px" | Date
!style="width:300px" | Description
|-
| 3.0.0 b.3025
| Fri 22 May 2015 (Beta release)
| Cumulus can now update a MySQL database. There are six options:
* The first three are for [[realtime.txt]] data, [[Standard log files|monthly logfile data]], and [[dayfile.txt]] data; schema as [[ImportCumulusFile]] script. If respective option enabled, the appropriate SQL upload will take place when Cumulus creates the realtime.txt file, or appends a line to a monthly log or dayfile.txt. The MySQL settings screen has buttons for creating the tables, once you have submitted the configuration details.
* The last three options are for 'custom' MySQL uploads. For these, you need to supply the SQL insert statement, using webtags for the data, and you need to have created the table yourself. See [https://cumulus.hosiene.co.uk/viewtopic.php?p=138868#p138868 forum announcement] for example SQL.
|-
| 3.12.0 b.3134-BETA
| Thu 29 July 2021 (Released as b.3140)
| New: Adds the ability to buffer failed MySQL commands until the MySQL server becomes available again, or Cumulus MX is restarted - when they will be lost
# Enabled via an option in MySQL Settings
# Note: Whilst Realtime updates are buffered, the uploading of failed queries is only performed by the Log updates
|-
| 3.20.0 b.3199 commit a (31 July 2022)
| Sun 21 Aug 2022 (Released as b.3202)
| Multiple changes:
# New: Custom MySQLConnector Uploads (seconds, minutes, rollover) can now each have up to 10 commands:
#* The 10 commands are not available if MX is stopped and restarted, due to bugs:
#*# A copy and paste of code from 3.20.0 - b3197 beta (which changed Custom HTTP seconds/minutes/rollover strings so set to 10 URLs) error, meant the 10 SQL custom seconds, custom minute, commands are stored in <nowiki>[</nowiki>Http<nowiki>]</nowiki> section of file
#*# Another copy and paste error meant that custom rollover comands are only stored if custom minutes are enabled
# Change: Changes to dayfile MySQLConnector query, add specific reference to dayfile table, add extra columns for rain24hour
# A one-off MySQL script in the MXutils folder to alter existing dayfile table adding the 2 new extra columns for rain24hour
# Change: In interface, the MySQL Settings page gets new functions for checking number of columns, allowing updating of existing tables by adding columns to match the current schema
|-
| 3.21.0 b.3204
| Fri 2 Sept 2022
| Failed MySQL commands are stored in [[Cumulusmx.db|SQLite RecentData database table "SqlCache"]] to persist when CMX stopped/restarted  (30 Aug commit 1) and can be individually edited/deleted  (29 Aug commit 3 and 30 Aug commit 3)
|-
| 3.21.1 b.3205
| Sun 4 Sept 2022
| The 10 custom MySQLConnector Uploads (seconds, minutes, rollover) are now stored in/read from <nowiki>[</nowiki>MySql<nowiki>]</nowiki> section of '''Cumulus.ini''', so now preserved when CMX stopped/restarted. Also the bug re custom rollover is fixed, its commands are stored if custom rollover is enabled.
|}
 
 
==Introduction to the 6 SQL update options==
 
MX supports (default) tables where it determines the columns in the table , and (custom) tables where it determines when actions take place. 
 
===The MX default database tables===
 
There are three default tables (by default called "realtime", "monthly", and "dayfile"; although these names can be changed by the Cumulus user) where Cumulus determines the '''schema''' (what columns appear in the database table)
 
For the default tables, every MySqlConnect command issued by MX specifies column names.  Some MX releases add columns that were not present in earlier MX releases, and these releases should provide a utility that will add the extra columns to existing tables. The commands generated by CMX will fail if all named columns are not present in the table.
 
 
 
====Creation of default tables====
 
You must define how to access your database server, enable the particular table and indicate what the table is to be called first. Then you must click '''Save settings''' so all those details are registered before any create command will work.
 
For each default table, MX provides a button which (after database server details, table name and other settings have been saved) can send a '''CREATE table_name''' command to your database server. If a table with that name already exists, or certain other standard errors happen, MX can give you feedback.  MX will also tell you when the SQL has worked, and the table has been created. 
 
Note that as MX names the columns in any commands it generates, the named columns can be in any order in the table.  The default order (as named in '''CREATE table_name''' command) represents when the relevant derivative was added to the corresponding Cumulus file.  If you have the knowledge of the SQL command required to, or can use a tool (such as ''PhpMyAdmin'') that provides a user-friendly interface to do this, reorganise column order; then you can collect all wind-related columns together, all rain-related columns together, and so on, or simply put what you regard as most important columns before others.
 
 
 
 
 
====Modifying schema (columns in table)====
 
Some release announcements for MX mention that extra columns have been added to a particular table.  A script may be provided (either in release announcement, or in the '''MXutils'''  directory within the download, that you can run to add the extra columns. For example '''b3089-AlterMySqlTables.sql''' was provided in the '''MXutils'''  directory with build 3089 to add the ''Feels Like temperature'' columns
 
In release 3.20.0, '''v3.20.0-AlterMySqlTables.sql''' was provided to edit the "dayfile" table and add 3 new columns (cumulative chill hours, highest 24 hour rainfall, and time when highest 24 hour rainfall ended).
 
Release 3.20.0 as seen on screenshot below provides buttons (under heading of '''Update database table''') for each of the default tables.  The code here is rather crude, it counts the number of columns currently defined in the table (does not check what names those columns have, nor what properties those columns have) and compares against number of columns that MX can automatically insert/update at that release in that table.  It assumes columns appear in same order as the fields in related file, and modifies the table to add the extra columns in the correct position to match the respective file.
 
 
(If a Cumulus user adds extra columns to a default table, those extra columns must be defined with null as default value, so MX can ignore them, but the columns can be in any position).
 
====Populating rows that do not exist====
 
Use the utility described at [[Software#Export_To_MySQL]] for '''monthly''' or '''dayfile''' tables.
 
The only way to populate '''realtime''' is via the action described in settings page description.
 
====Populating missing/incorrect columns in existing rows====


The custom table options in MX require you to specify the SQL in advance of it being used, and are therefore intended for use to predetermine all the SQL you might use (conditionals can be included to decide what queries actually get executed if there are alternatives). You can enable/disable individual settings, but the mechanism is not designed to do any one-off actions like creating the custom tables.
The options provided in the ''Data logs'' menu of the interface can be used to edit a single line of a file, and there is a setting that lets that edit also update the corresponding single row of a default table.


Whilst it may not take long to send SQL for a single line update to your database server, it will take a lot of time to select each line in file in turn, and to send the SQL to insert every line to your database server.


WARNING: The SQL syntax for [https://mysqlconnector.net/ "My SQL Connector"] used by .NET and therefore by MX, differs in various ways from the MySQL Client or MariaDB syntax.
To update one or more columns in multiple rows, you need to generate a succession of UPDATE queries, keeping the text to send to the database server as short as possible. 
 
One way to do this is to open the relevant file using a spreadsheet (e.g. Libre Office has a "calc" option, this is free and available for most operating systems).  Create an extra column after existing columns in the spreadsheet for the "primary key",  you should be able to generate this from the first one or two columns of the spreadsheet with some manipulation.  Now "Hide" all the columns except those with data that you want to include in the update, and the primary key column.  With some spreadsheet skills (see https://cumulus.hosiene.co.uk/viewtopic.php?p=165767#p165767) you can generate the required SQL in this format:
<pre>
UPDATE name-of-table SET first-column-name=first-row-and-first-column-value, second-column-name=first-row-and-second-column-value WHERE primary-key-column-name=first-row-primary-key-value;
UPDATE name-of-table SET first-column-name=second-row-and-first-column-value, second-column-name=second-row-and-second-column-value WHERE primary-key-column-name=second-row-primary-key-value;
</pre>
 
Alternatively, if you have skills in a script language like PHP Hypertext Preprocessor (PHP) you can write a small script that reads the file within a loop, picks the fields required from the line of the file, and generates the SQL (as above), and after ending loop closes file and sends the SQL to the database server.
 
 
===Custom Interval actions on tables===
 
MX can run SQL (using MySqlConnect commands) that work with Oracle's MySQL, or the MariaDB, database servers at one, or more, of three intervals (MX calls these "custom seconds", "custom minutes", and "custom rollover").
 
You can enable/disable individual settings, but the mechanism is not designed to do any one-off actions like creating the custom tables.
 
The Cumulus user can create any number of custom tables, where the user chooses the "schema" (columns in the table). At each custom interval, actions can affect any number of these pre-created tables; and in fact you can define several commands to run in succession against any one table.
 
 
==== Predetermined SQL ====
 
The custom table options in MX require you to specify the SQL in advance of it being used.
 
This means you have to predetermine all the SQL you might use (conditionals can be included to decide what queries actually get executed if there are alternatives).
 
WARNING: The SQL syntax for [https://mysqlconnector.net/ "My SQL Connector"] used by .NET and therefore by MX, differs in various ways from the Oracle MySQL Client or MariaDB syntax.


Here is an example of what might be input as "predetermined SQL", showing how you can use conditionals and web tags:
Here is an example of what might be input as "predetermined SQL", showing how you can use conditionals and web tags:
<pre>
<pre>
INSERT IGNORE INTO table_name_1 (primary_key, column_name_1, column_name_2 ....) VALUES ('<#primary_key>', '<#web tag_name_1 modifier_1>', '<#web tag_name_2 modifier_2>'  ....);
INSERT IGNORE INTO table_name_1 (primary_key, column_name_1, column_name_2 ....) VALUES ('<#primary_key>', '<#tag_name_1 modifier_1>', '<#tag_name_2 modifier_2>'  ....);




BEGIN NOT ATOMIC
BEGIN NOT ATOMIC
IF '<#web tag>' = '---' THEN  
IF '<#tag_name>' = '---' THEN  
simpler query to run if something not numeric;
simpler query to run (perhaps creating a row for a particular Cumulus tag value);
ELSE  
ELSE  
UPDATE table_name_2 SET  column_name_1 = '<#web tag_name_1 modifier_1>', column_name_2 =  '<#web tag_name_2 modifier_2>'  ....  WHERE primary_key = '<#primary_key>';
UPDATE table_name_2 SET  column_name_1 = '<#tag_name_1 modifier_1>', column_name_2 =  '<#tag_name_2 modifier_2>'  ....  WHERE primary_key = '<#primary_key>';
END IF
END IF
END;
END;
</pre>
</pre>


== Why are Cumulus settings now split between several Wiki pages? ==
From MX release 3.21.1, the INSERT and conditional can be defined in separate command boxes on the settings page.
 


The basic answer is because there are a lot of settings, some get changed, and more are added as Cumulus develops!
=Structure of ''Cumulus.ini''=


MX has been developed very dramatically by Mark Crossley:
The settings described here are held internally (stored in RAM) while CMX is running.
#  The MySQL settings for the original 3.0.0 beta were previously documented on [[Cumulus.ini (Beta)]] page, but that page has been cleared.
# The dramatic development of MX produced considerable differences between that original MX beta, and the configuration that applied up to 3.7.0, [[Cumulus.ini (MX 3.0.0 to 3.7.0)|that latter documentation remains available here]]
# Even more dramatic changes to the MX configuration have been happening since 3.8.0, with the biggest changes at 3.9.2, 3.10.0, and 3.12.0; therefore, <big>the decision was taken in June 2021 to abandon maintaining the page previously called "Cumulus.ini", and start again with a brand new page now found [[Cumulus.ini|here]]!</big>
# Since the MySQL settings have continued to change, since 2021 they have been moved here from that last mentioned page.


All the pages, previously called "Cumulus.ini", can be found from the owning [[:Category:Configuration Files|category]].  The original page was preserved at [[Cumulus.ini_(preserving_history)]] so look there for its editing history.  That page also expands on the above summary of why that old page was replaced.
If you click ''Save settings'' on the '''Settings &rarr; MySQL settings''' page, then the settings are also stored in a file called "Cumulus.ini".


==File sections==


==Structure of ''Cumulus.ini''==
The file is divided into "File sections", each File section name is on a separate line (with no other content) and enclosed in square brackets (e.g. '''[MySQL]''').


The settings described here are stored in a file called "Cumulus.ini".
These File sections can appear in any order, MX has a default order set by the order in which the sections appear in the code that writes to the file.


The file is divided into "File sections", each File section name is on a separate line (with no other content) and enclosed in square brackets (e.g. '''[Station]''').
If you prefer the File sections in another order, you can stop MX, and then rearrange the file section order. On restart of the software, MX will still find them, although it may spend more time searching! However, some MX releases discard existing file, and create a new file, so then order will revert too what MX chooses.


These File sections can appear in any order, by default new File sections are appended after all previous File sections, but if you prefer the File sections in another order, MX can still find them, although it may spend more time searching!
==Parameters==


Within each File section, there are parameters. Each parameter is in format '''Attribute=Value''', and appears on a line to itself.  
Within each File section, there are parameters. Each parameter is in format '''Attribute=Value''', and appears on a line to itself.  


The parameters, within a File section, can be in any order, by default new parameters are appended at end of the relevant File section.
The parameters, within a File section, can be in any order, by default new parameters are appended at end of the relevant File section.
Steve Loft recommended that the user sorted the parameters alphabetically, this was because the file used to have to be edited manually as many settings were not included on settings pages. Having the parameters in alphabetical order made it easier to find what to edit, and to check a parameter did not appear more than once, as Cumulus ignores any duplicates. Now all settings are controlled by the interface, CMX determines the parameter order.




==Content of "Cumulus.ini" relating to MySQL==
==Content of "Cumulus.ini" relating to MySQL==


* Introduced in Cumulus MX 3.0.0 beta
* File Section is [MySQL]
* File Section is [MySQL]
* MX page is Settings menu &rarr; MySQL settings
* MX page is Settings menu &rarr; MySQL settings
* The table below relates to a beta for 3.12.0 (see top of this page), there will have been changes in later MX releases that are not documented


{| class="wikitable" border="1"
{| class="wikitable" border="1"
|-
|-
!style="width:30px" | Parameter Line Entry
!style="width:30px" | Parameter Line Entry
!style="width:30px" | Applicability
!style="width:100px" | MX Section
!style="width:100px" | MX Section
!style="width:60px" | Label on Settings page
!style="width:60px" | Label on Settings page
Line 143: Line 268:
|-
|-
| BufferOnFailure=0
| BufferOnFailure=0
| 3.12.0 onwards
| General Options
| General Options
| Buffer commands on failure
| Buffer commands on failure
Line 151: Line 277:
|-
|-
| CustomMySqlMinutesCommandString=
| CustomMySqlMinutesCommandString=
| 3.0.0 to 3.21.0
| Custom Upload - minutes interval
| Custom Upload - minutes interval
| Option to enter "SQL command" only shown if next parameter is ticked
| Option to enter "SQL command" only shown if ''Custom Minutes Enabled'' parameter is ticked
| (empty)
| (empty)
| See [[#Predetermined SQL|"predetermined SQL"]] example above
|-
|  10 commands:
*  CustomMySqlMinutesCommandString=
*  CustomMySqlMinutesCommandString1=
* to
*  CustomMySqlMinutesCommandString9=
| 3.21.1 onwards
| Custom Upload - minutes interval
| Option to enter "SQL command" only shown if ''Custom Minutes Enabled'' parameter is ticked
| (empty commands don't appear)
| See [[#Predetermined SQL|"predetermined SQL"]] example above
| See [[#Predetermined SQL|"predetermined SQL"]] example above
|-
|-
| CustomMySqlMinutesEnabled=0
| CustomMySqlMinutesEnabled=0
| 3.0.0 onwards
| Custom Upload - minutes interval
| Custom Upload - minutes interval
| Custom Minutes Enabled
| Custom Minutes Enabled
Line 165: Line 304:
|-
|-
| CustomMySqlMinutesIntervalIndex=6
| CustomMySqlMinutesIntervalIndex=6
| 3.0.0 onwards
| Custom Upload - minutes interval
| Custom Upload - minutes interval
| Interval:
| Interval:
Line 182: Line 322:
|-
|-
| CustomMySqlRolloverCommandString=
| CustomMySqlRolloverCommandString=
| 3.0.0 to 3.21.0
| Custom Upload - at rollover
| Custom Upload - at rollover
| Option to enter "SQL command" only shown if next parameter is ticked
| Option to enter "SQL command" only shown if ''Custom Rollover Enabled'' parameter is ticked
| (empty)
| (empty)
| See [[#Predetermined SQL|"predetermined SQL"]] example above
|-
|  10 commands:
*  CustomMySqlRolloverCommandString=
*  CustomMySqlRolloverCommandString1=
* to
*  CustomMySqlRolloverCommandString9=
| 3.21.1 onwards
| Custom Upload - at rollover
| Option to enter "SQL command" only shown if ''Custom Rollover Enabled'' parameter is ticked
| (empty commands don't appear)
| See [[#Predetermined SQL|"predetermined SQL"]] example above
| See [[#Predetermined SQL|"predetermined SQL"]] example above
|-
|-
| CustomMySqlRolloverEnabled=0
| CustomMySqlRolloverEnabled=0
| 3.0.0 onwards
| Custom Upload - at rollover
| Custom Upload - at rollover
| Custom Rollover Enabled
| Custom Rollover Enabled
Line 196: Line 349:
|-
|-
| CustomMySqlSecondsCommandString=
| CustomMySqlSecondsCommandString=
| 3.0.0 to 3.21.0
| Custom Upload - seconds interval
| Custom Upload - seconds interval
| Option to enter "SQL command" only shown if next parameter is ticked
| Option to enter "SQL command" only shown if '''Custom Seconds Enabled''' is ticked
| (empty)
| (empty)
| See [[#Predetermined SQL|"predetermined SQL"]] example above
|-
|  10 commands:
*  CustomMySqlSecondsCommandString=
*  CustomMySqlSecondsCommandString1=
* to
*  CustomMySqlSecondsCommandString9=
| 3.21.1 onwards
| Custom Upload - at Seconds interval
| Option to enter "SQL command" only shown if ''Custom Seconds Enabled'' parameter is ticked
| (empty commands don't appear)
| See [[#Predetermined SQL|"predetermined SQL"]] example above
| See [[#Predetermined SQL|"predetermined SQL"]] example above
|-
|-
| CustomMySqlSecondsEnabled=0
| CustomMySqlSecondsEnabled=0
| 3.0.0 onwards
| Custom Upload - seconds interval
| Custom Upload - seconds interval
| Custom Seconds Enabled
| Custom Seconds Enabled
Line 210: Line 376:
|-
|-
| CustomMySqlSecondsInterval=10
| CustomMySqlSecondsInterval=10
| 3.0.0 onwards
| Custom Upload - seconds interval
| Custom Upload - seconds interval
| Interval (seconds):
| Interval (seconds):
Line 222: Line 389:
|-
|-
| DayfileMySqlEnabled=0
| DayfileMySqlEnabled=0
| 3.0.0 onwards
| Dayfile.txt upload
| Dayfile.txt upload
| Dayfile Enabled
| Dayfile Enabled
Line 230: Line 398:
|-
|-
| DayfileTable=
| DayfileTable=
| 3.0.0 onwards
| Dayfile.txt upload
| Dayfile.txt upload
| Table name
| Table name
Line 236: Line 405:
|-
|-
| Host=Localhost
| Host=Localhost
| 3.0.0 onwards
| Server details
| Server details
| Host name
| Host name
Line 242: Line 412:
|-
|-
| MonthlyMySqlEnabled=0
| MonthlyMySqlEnabled=0
| 3.0.0 onwards
| Monthly logfile upload
| Monthly logfile upload
| Monthly Log Enabled
| Monthly Log Enabled
Line 250: Line 421:
|-
|-
| MonthlyTable=
| MonthlyTable=
| 3.0.0 onwards
| Monthly logfile upload
| Monthly logfile upload
| Table name
| Table name
Line 256: Line 428:
|-
|-
| Pass=
| Pass=
| 3.0.0 onwards
| Server details
| Server details
| Password
| Password
Line 262: Line 435:
|-
|-
| Port=3306
| Port=3306
| 3.0.0 onwards
| Server details
| Server details
| Port number
| Port number
Line 268: Line 442:
|-
|-
| RealtimeMySql1MinLimit=0
| RealtimeMySql1MinLimit=0
| 3.0.0 onwards
| Realtime.txt upload
| Realtime.txt upload
| Limit Inserts:
| Limit Inserts:
Line 277: Line 452:
|-
|-
| RealtimeMySqlEnabled=0
| RealtimeMySqlEnabled=0
| 3.0.0 onwards
| Realtime.txt upload
| Realtime.txt upload
| Real time Enabled
| Real time Enabled
Line 285: Line 461:
|-
|-
| RealtimeRetention=
| RealtimeRetention=
| 3.0.0 onwards
| Realtime.txt upload
| Realtime.txt upload
| '''Data Retention value''' and '''Data Retention unit'''
| '''Data Retention value''' and '''Data Retention unit'''
Line 298: Line 475:
|-
|-
| RealtimeTable=Realtime
| RealtimeTable=Realtime
| 3.0.0 onwards
| Realtime.txt upload
| Realtime.txt upload
| Table name
| Table name
Line 304: Line 482:
|-
|-
| UpdateOnEdit=1
| UpdateOnEdit=1
| 3.12.0 - b3134 - BETA onwards
| General Options
| General Options
| Update MySQL on Edit
| Update MySQL on Edit
Line 313: Line 492:
|-
|-
| User=
| User=
| 3.0.0 onwards
| Server details
| Server details
| User name
| User name
Line 318: Line 498:
| Database access user name, that matches with password described earlier
| Database access user name, that matches with password described earlier
|}
|}
=The MX default database tables=
Further work is needed here, but this is a quick attempt to include some notes that might help you, written because of the extensive changes in release 3.20.0
==Creation of default tables==
The settings page as described above includes the ability to issue the SQL command to your server to create whichever default table you wish.
You must define how to access your database server, enable the particular table and indicate what the table is to be called first. Then you must click '''Save settings''' so all those details are registered before the create command will work.
If a table with that name already exists, or certain other standard errors happen, MX can give you feedback.  MX will also tell you when the SQL has worked, and thetable has been created.
==Modifying schema (columns in table)==
Some release announcements for MX mention that extra columns have been added to a particular table.  A script may be provided (either in release announcement, or in the '''MXutils'''  directory within the download, that you can run to add the extra columns. For example '''b3089-AlterMySqlTables.sql''' was provided in the '''MXutils'''  directory with build 3089 to add the ''Feels Like temperature'' columns
In release 3.20.0, '''v3.20.0-AlterMySqlTables.sql''' was provided to edit the "dayfile" table and add 3 new columns (cumulative chill hours, highest 24 hour rainfall, and time when highest 24 hour rainfall ended).
Release 3.20.0 as seen on screenshot above provides buttons (under heading of '''Update database table''') for each of the default tables.  The code here is rather crude, it counts the number of columns currently defined in the table (does not check what names those columns have, nor what properties those columns have) and compares against number of columns that MX can automatically insert/update at that release in that table.  It assumes columns appear in same order as the fields in related file, and modifies the table to add the extra columns in the correct position to match the respective file.
==Populating rows that do not exist==
Use the utility described at [[Software#Export_To_MySQL]] for '''monthly''' or '''dayfile''' tables.
The only way to populate '''realtime''' is via the action described in settings page description above.
==Populating missing/incorrect columns in existing rows==
The options provided in the ''Data logs'' menu of the interface can be used to edit a single line of a file, and there is a setting that lets that edit also update the corresponding single row of a default table.
Whilst it may not take long to send SQL for a single line update to your database server, it will take a lot of time to select each line in file in turn, and to send the SQL to insert every line to your database server.
To update one or more columns in multiple rows, you need to generate a succession of UPDATE queries, keeping the text to send to the database server as short as possible. 
One way to do this is to open the relevant file using a spreadsheet (e.g. Libre Office has a "calc" option, this is free and available for most operating systems).  Create an extra column after existing columns in the spreadsheet for the "primary key",  you should be able to generate this from the first one or two columns of the spreadsheet with some manipulation.  Now "Hide" all the columns except those with data that you want to include in the update, and the primary key column.  With some spreadsheet skills (see https://cumulus.hosiene.co.uk/viewtopic.php?p=165767#p165767) you can generate the required SQL in this format:
<pre>
UPDATE name-of-table SET first-column-name=first-row-and-first-column-value, second-column-name=first-row-and-second-column-value WHERE primary-key-column-name=first-row-primary-key-value;
UPDATE name-of-table SET first-column-name=second-row-and-first-column-value, second-column-name=second-row-and-second-column-value WHERE primary-key-column-name=second-row-primary-key-value;
</pre>
Alternatively, if you have skills in a script language like PHP Hypertext Preprocessor (PHP) you can write a small script that reads the file within a loop, picks the fields required from the line of the file, and generates the SQL (as above), and after ending loop closes file and sends the SQL to the database server.
Sfws
5,838

edits

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