MySqlConnect - Revision history

Sfws: Update for 3.21.1

2022-09-13T08:23:50Z

Update for 3.21.1

Sfws: /* Populating missing/incorrect columns in existing rows */ added cross-ref to forum

2022-08-22T13:18:03Z

Populating missing/incorrect columns in existing rows: added cross-ref to forum

← Older revision		Revision as of 13:18, 22 August 2022
Line 353:		Line 353:
	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.		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 you can generate the required SQL in this format:		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>		<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=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;

Sfws: /* Populating missing/incorrect columns in existing rows */ typo

2022-08-22T11:39:26Z

Populating missing/incorrect columns in existing rows: typo

← Older revision		Revision as of 11:39, 22 August 2022
Line 345:		Line 345:
	The only way to populate '''realtime''' is via the action described in settings page description above.		The only way to populate '''realtime''' is via the action described in settings page description above.

	===Populating missing/incorrect columns in existing rows===		==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.		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.

Sfws: new section at end

2022-08-22T11:38:57Z

new section at end

@@ Line 310: / Line 310: @@
 *Tick (value shown as 1 in Cumulus.ini)
 * Don't tick (value shown as 0 in Cumulus.ini)
-Please see begining of previous table for explanation and warning about how all defaults must be set for this to work
+Please see beginning of previous table for explanation and warning about how all defaults must be set for this to work
 |-
 | User=
@@ Line 318: / Line 318: @@
 | Database access user name, that matches with password described earlier
 |}

Sfws: /* The My SQL settings page in interface */ add screenshot

2022-08-21T11:01:55Z

The My SQL settings page in interface: add screenshot

← Older revision		Revision as of 11:01, 21 August 2022
Line 71:		Line 71:
	=The My SQL settings page in interface=		=The My SQL settings page in interface=

	MX controls settings for the relational database tables on a page called "MySQL settings" although you may be using "MariaDB".		MX controls settings for the relational database tables on a page called "MySQL settings" although you may be using "MariaDB". Here is a screenshot of that page (with all sections closed up) as applicable from release 3.20.0 (build 3202):

			[[File:MySQL settings.png]]

			Please note, the text included elsewhere on this Wiki page may be for different release.

	{{Template:WorkInProgressBanner}}		{{Template:WorkInProgressBanner}}
Line 128:		Line 132:
	* File Section is [MySQL]		* File Section is [MySQL]
	* MX page is Settings menu → MySQL settings		* MX page is Settings menu → 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"

Sfws: Correct categories

2022-08-20T19:49:01Z

Correct categories

← Older revision		Revision as of 19:49, 20 August 2022
Line 1:		Line 1:
	=Introduction=		=Introduction=

	[[Category:Configuration Files]]		[[Category:Configuration Files]][[Category:Cumulus MX]]

	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).		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).

Sfws: Created page with "=Introduction= Category:Configuration Files In May 2015, Steve Loft [https://cumulus.hosiene.co.uk/viewtopic.php?p=138868#p138868 added SQL functionality] to his Cumulu..."

2022-08-20T18:22:16Z

Created page with "=Introduction= Category:Configuration Files In May 2015, Steve Loft [https://cumulus.hosiene.co.uk/viewtopic.php?p=138868#p138868 added SQL functionality] to his Cumulu..."

New page

=Introduction=

[[Category:Configuration Files]]

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).

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").

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. 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.

==How does MX use SQL?==

MX uses two types of SQL.

MX uses SQL when updating a SQLite database (see [[Cumulusmx.db]] and [[Diary.db]], that type of database stores data in what is known technically as a sequential file. In a sequential file, the data appears as a number of lines that have to be stored in order (by row number). Because entries can be deleted, not every possible row number has to be present, but new entries are appended at end. The various fields (or columns) are named, it is possible (but rarely done) to change the order of the columns, rewriting the entire database file. In a retrieval query you can sort ascending/descending on value in any column. You can select a row by specifying content, but that gets translated into a row number to actually determine the row that is updated.

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?===

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).

Both "CumulusMX.exe" and "ExportToMySQL.exe" make use of the following settings:

{| class="wikitable" border="1"
|-
!style="width:30px" | Executable
!style="width:100px" | Function
!style="width:300px" | Description
|-
| CumulusMX.exe
| Data Logs menu
| An option "Update MySQL on Edit" allows you to decide whether MX issues SQL when you edit a data log or "dayfile.txt", so the same edit is made to the corresponding database table, provided that the table has been created with the default name and has the column names defined by default.
|-
! rowspan="7" |CumulusMX.exe
| Settings menu → MySQL settings → General Options
| An option "Buffer commands on failure" allows you to decide whether MX tries to run SQL commands again, if they fail. IMPORTANT: This is to cope with failure due to the database server going down; the option should not be ticked if you are testing new "custom" SQL and may have made a mistake in the coding. You need to ensure the SQL is either generated by MX as a fixed query, or has been tested and is correct synta before considering this option.
|-
| Settings menu → MySQL settings → Realtime.txt Upload
| An option that can be enabled that automatically updates a "Realtime" table, each new row is effectively what is stored in the file [[realtime.txt]].
|-
| Settings menu → MySQL settings → Dayfile.txt Upload
| An option that can be enabled that at end of day ensures that a new line is added to [[dayfile.txt]] and a new row (with similar data) is inserted into a "Dayfile" table.
|-
| Settings menu → MySQL settings → Monthly log file Upload
| An option that can be enabled that automatically updates a "Monthly" table, every time a new line is added to the [[Standard_log_files|standard data log file]].
|-
| Settings menu → MySQL settings → Custom Upload - seconds interval
| An option that can be enabled that runs some predetermined SQL every time the specified number of seconds has elapsed.
|-
| Settings menu → MySQL settings → Custom Upload - minutes interval
| An option that can be enabled that runs some predetermined SQL every time the specified number of minutes has elapsed.
|-
| Settings menu → MySQL settings → Custom Upload - at rollover
| An option that can be enabled that runs some predetermined SQL just after dayfile.txt has had a new line, but before other end-of-day actions occur.
|-
| ExportToMySQL.exe
| parameters to utility
| Determine which file read (and therefore which table updated, using table names set in table name parameters explained in next table)
|}

=The My SQL settings page in interface=

MX controls settings for the relational database tables on a page called "MySQL settings" although you may be using "MariaDB".

{{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 ==

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.

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.

Here is an example of what might be input as "predetermined SQL", showing how you can use conditionals and web tags:
<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>' ....);

BEGIN NOT ATOMIC
IF '<#web tag>' = '---' THEN
simpler query to run if something not numeric;
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>';
END IF
END;
</pre>

== Why are Cumulus settings now split between several Wiki pages? ==

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

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.

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.

==Structure of ''Cumulus.ini''==

The settings described here are stored in a file called "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. '''[Station]''').

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!

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.

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

* Introduced in Cumulus MX 3.0.0 beta
* File Section is [MySQL]
* MX page is Settings menu → MySQL settings

{| class="wikitable" border="1"
|-
!style="width:30px" | Parameter Line Entry
!style="width:100px" | MX Section
!style="width:60px" | Label on Settings page
!style="width:20px" | Default
!style="width:600px" | Description
|-
| BufferOnFailure=0
| General Options
| Buffer commands on failure
| 0 = ignore errors
| Simple tick or not
*Tick (value shown as 1 in Cumulus.ini) if you want MX to store any commands that fail (see note above), and try them again (stored commands lost if MX is stopped)
* Don't tick (value shown as 0 in Cumulus.ini) if you want MX to ignore any errors
|-
| CustomMySqlMinutesCommandString=
| Custom Upload - minutes interval
| Option to enter "SQL command" only shown if next parameter is ticked
| (empty)
| See [[#Predetermined SQL|"predetermined SQL"]] example above
|-
| CustomMySqlMinutesEnabled=0
| Custom Upload - minutes interval
| Custom Minutes Enabled
| 0 = don't display other options
| Simple tick or not
*Tick (value shown as 1 in Cumulus.ini) if you want the previous and next options to be displayed, and actioned
* Don't tick (value shown as 0 in Cumulus.ini) if MX is not to action the previous and next settings
|-
| CustomMySqlMinutesIntervalIndex=6
| Custom Upload - minutes interval
| Interval:
| 6 = 10 minutes
| Drop-down list of predetermined intervals:
* 0 = 1 minute
* 1 = 2 minutes
* 2 = 3 minutes
* 3 = 4 minutes
* 4 = 5 minutes
* 5 = 6 minutes
* 6 = 10 minutes
* 7 = 12 minutes
* 8 = 20 minutes
* 9 = 30 minutes
* 10 = 1 hour (60 minutes)
|-
| CustomMySqlRolloverCommandString=
| Custom Upload - at rollover
| Option to enter "SQL command" only shown if next parameter is ticked
| (empty)
| See [[#Predetermined SQL|"predetermined SQL"]] example above
|-
| CustomMySqlRolloverEnabled=0
| Custom Upload - at rollover
| Custom Rollover Enabled
| 0 = don't display other options
| Simple tick or not
*Tick (value shown as 1 in Cumulus.ini) if you want the previous, and next, options to be displayed, and actioned
* Don't tick (value shown as 0 in Cumulus.ini) if MX is not to action the previous and next settings
|-
| CustomMySqlSecondsCommandString=
| Custom Upload - seconds interval
| Option to enter "SQL command" only shown if next parameter is ticked
| (empty)
| See [[#Predetermined SQL|"predetermined SQL"]] example above
|-
| CustomMySqlSecondsEnabled=0
| Custom Upload - seconds interval
| Custom Seconds Enabled
| 0 = don't display other options
| Simple tick or not
*Tick (value shown as 1 in Cumulus.ini) if you want the previous and next options to be displayed, and actioned
* Don't tick (value shown as 0 in Cumulus.ini) if MX is not to action the previous and next settings
|-
| CustomMySqlSecondsInterval=10
| Custom Upload - seconds interval
| Interval (seconds):
| 10
| A number field, takes 1 to 59
|-
| Database=
| Server details
| Database name:
| (empty)
| A text field where you enter the name of the database your tables are to be created/updated in
|-
| DayfileMySqlEnabled=0
| Dayfile.txt upload
| Dayfile Enabled
| 0
| Simple tick or not
*Tick (value shown as 1 in Cumulus.ini) if you want MX to at end of day ensure that a new line is added to [[dayfile.txt]] and a new row (with similar data) is inserted into a "Dayfile" table
* Don't tick (value shown as 0 in Cumulus.ini) if MX is only to ensure that a new line is added to dayfile.txt
|-
| DayfileTable=
| Dayfile.txt upload
| Table name
| Dayfile
| Having defined this table, and saved the settings, click the "Create Dayfile" button to create the database table with the standard column names. If you change the name here, normal rollover updates and "ExportToMySQl.exe" will use the table name you define. Depending which MX release you use, the option to update table when you edit "dayfile.txt" may fail!
|-
| Host=Localhost
| Server details
| Host name
| (empty)
| Set to whatever host name your database server has specified, if you host [[Your_Own_Server]], then you may be able to use "Localhost".
|-
| MonthlyMySqlEnabled=0
| Monthly logfile upload
| Monthly Log Enabled
| 0
| Simple tick or not
*Tick (value shown as 1 in Cumulus.ini) if you want MX to at your standard logging interval ensure that a new line is added to [[Standard_log_files|standard data log file]] and a new row (with similar data) is inserted into a "Monthly" table.
* Don't tick (value shown as 0 in Cumulus.ini) if MX is only to ensure that a new line is added to standard data log file
|-
| MonthlyTable=
| Monthly logfile upload
| Table name
| Monthly
| Having defined this table, and saved the settings, click the "Create Monthly" button to create the database table with the standard column names. You might wish to change the name here once a year, to stop the database table getting enormous, if so you might name the table something like "standard2020". If you change the name here, normal updates and "ExportToMySQl.exe" will use the table name you define, but at some releases the option to update table when you edit "MMMyyyyLog.txt" may fail
|-
| Pass=
| Server details
| Password
| (empty)
| Set to whatever password is used for update access with the database user name (see below)
|-
| Port=3306
| Server details
| Port number
| 3306
| The port related to the host name (defined above) for update access to your database server
|-
| RealtimeMySql1MinLimit=0
| Realtime.txt upload
| Limit Inserts:
| 0 = false
| Simple tick or not
*Tick (value shown as 1 in Cumulus.ini)
* Don't tick (value shown as 0 in Cumulus.ini)
See explanation beside and below setting
|-
| RealtimeMySqlEnabled=0
| Realtime.txt upload
| Real time Enabled
| 0 = don't display other options
| Simple tick or not
*Tick (value shown as 1 in Cumulus.ini) if you want MX to at your real time interval ensure that a new realtime.txt file is created and a new row (with similar data) is inserted into a "Realtime" table.
* Don't tick (value shown as 0 in Cumulus.ini) if MX is only to ensure that a new line is added to standard data log file
|-
| RealtimeRetention=
| Realtime.txt upload
| '''Data Retention value''' and '''Data Retention unit'''
| "7" and "Days"
| The value field will take any number, the unit field is a dropdown:
* Seconds
* Minutes
* Hours
* Days
* Weeks
* Months
Taken together, these determine the period for which rows are kept in the table named in "RealtimeTable", any row older than this specification will be deleted as part of the automatic SQL
|-
| RealtimeTable=Realtime
| Realtime.txt upload
| Table name
| Realtime
| Having defined this table, and saved the settings, click the "Create Realtime" button to create the database table with the standard column names. Remember that you can tailor the contents of "realtime.txt", if you want non-standard columns in this table, you have to create the table yourself.
|-
| UpdateOnEdit=1
| General Options
| Update MySQL on Edit
| 1 = update the table when edit the file
| Simple tick or not
*Tick (value shown as 1 in Cumulus.ini)
* Don't tick (value shown as 0 in Cumulus.ini)
Please see begining of previous table for explanation and warning about how all defaults must be set for this to work
|-
| User=
| Server details
| User name
| (empty)
| Database access user name, that matches with password described earlier
|}