Category:Cumulus MX: Difference between revisions

Category:Cumulus MX (view source)

Revision as of 07:30, 11 April 2020

1,420 bytes added , 07:30, 11 April 2020

m

→‎MySQL settings: Made more consistent

Sfws

5,838

edits

@@ Line 315: / Line 315: @@
 *''Custom upload - at rollover''
 ** In the previous option, you have no ability to vary the schema, it will update a column for Total Evaporation even if your weather station cannot calculate that. It will update columns for total hours of sunshine, highest solar radiation level, and the maximum UV in the day even if you cannot measure these. It will not record whether snow was falling or lying, or the depth of snow even if you are recording those.
-** MX provides this alternative option, again doing an upload as part of roll over to next day, but here you can specify the schema, and say which columns are to be updated with two options:
+** MX provides this alternative option, again doing an upload as part of roll over to next day, but here you can specify the schema, and say which columns are to be updated with three selections:
 *# A tick box to enable or disable this upload (so you can leave the SQL recorded, but stop running it when you like.
 *# The SQL you want to run, what you type in this small text box should include INSERT IGNORE (or REPLACE) to insert a row,  or include UPDATE to change columns in a row that already exists, like any SQL it must include the name of the table, the columns to be updated, and the values you want to insert into the columns are either expressed as web tags or as a sub-query reading the values from somewhere else.
 *#* Here is an example of a suitable query that MX can process for you (note I have had to include some yesterday tags e.g. for primary key ('''<#metdateyesterday format=yyyy-MM-dd>''') and that I have added a second query with sub-query after the first):
 <pre>INSERT IGNORE INTO `test_daily_summary` (`MaxRainRate`, `TMaxRainRate`, `HighHourRain`, `THighHourRain`, `TotRainFall`, `SnowFalling`, `SnowLying`, `SnowDepth`,  `CumChillHours`, `LogDate`, `RollOver`,  `MinTemp`, `TMinTemp`, `HeatDegDays`, `AvgTemp`, `MaxTemp`, `TMaxTemp`, `CoolDegDays`, `LowDewPoint`, `TLowDewPoint`, `LowHum`, `TLowHum`, `HighHum`, `THighHum`, `HighDewPoint`, `THighDewPoint`, `GreatWindChill`, `TGreatWindChill`, `LowAppTemp`, `TLowAppTemp`, `HighAppTemp`, `THighAppTemp`, `HighHeatInd`, `THighHeatInd`, `MinPress`, `TMinPress`, `MaxPress`, `TMaxPress`, `HighAvgWSpeed`, `THighAvgWSpeed`, `StrongestWindGust`, `TStrongestWindGust`, `BearStrongestWindGust`,  `BearDomWind`, `BearDomWindSym`,  `TotWindRun`) VALUES ('<#rrateTM>', '<#TrrateTM>', '<#hourlyrainTH>', '<#ThourlyrainTH>', '<#rfall> ', '<#snowfalling>', '<#snowlying>', '<#snowdepth>', '<#chillhours>', '<#metdateyesterday format=yyyy-MM-dd>',  'SUBSTRING(<#rollovertime>,0,2)', '<#tempYL>', '<#TtempYL> ', '<#heatdegdays> ', '<#avgtemp>', '<#tempTH>', '<#TtempTH> ', '<#cooldegdays> ', '<#dewpointTL>', '<#TdewpointTL>', '<#humTL>', '<#ThumTL>', '<#humTH>', '<#ThumTH>', '<#dewpointTH>', '<#TdewpointTH>', '<#wchillTL>', '<#TwchillTL>', '<#apptempTL>', '<#TapptempTL>', '<#apptempTH>', '<#TapptempTH>', '<#heatindexTH>', '<#TheatindexTH>', '<#pressTL>', '<#TpressTL>', '<#pressTH>', '<#TpressTH>', '<#windTM>', '<#TwindTM>', '<#wgustTM>', '<#TwgustTM>', '<#bearingTM>', '<#domwindbearing>', '<#domwinddir>', '<#windrun>'); UPDATE  `test_daily_summary`  SET `DailyChillHours`= '<#chillhours> - (SELECT `CumChillHours` FROM `test_daily_summary` WHERE `LogDate` = DATE_SUB(<#metdateyesterday format=yyyy-MM-dd>, INTERVAL 1 DAY));</pre>
-** Again before you enable this option, there is a facility lower down this setting page (under the heading '''Create database table''') where you can type some SQL to be run immediately, that can create the table you want this option to update, (although it could even populate any table with historic data, it is only intended for a small query). I am using a table that already exists as I have used it for testing changes to my PHP scripts, so I did not need to create a table.
+**# Save - a button after all option sections, until you click it any changes you make in this section have no effect
+** Again before you enable this option, there is a facility lower down this setting page (under the heading '''Create database table''') where you can type some SQL to be run immediately, that can create the table you want this option to update, (although it could even populate any table with historic data, it is only intended for a small query). I am using a table that already exists as I have used it for testing changes to my PHP scripts, so I did not need to create a table before I enabled the query shown above.
 *''Monthly log file upload''
-** Before you enable this option, there is a separate option, lower down this settings page, to create this table (you must save the settings here to tell Cumulus what table name you want), then click '''Create Monthly'''.
+** Just like with the dayfile.txt upload option, you select the table name in this option and click Save button, then use a separate option, lower down this settings page, to create the necessary table and that option is '''Create Monthly'''.
-** This feature allows you to upload the file that Cumulus creates each month to log measurements on a regular basis, it has just two items:
+** This feature allows you to upload the file that Cumulus creates each month to log detailed measurements on a regular basis, apart from the Save button below it there are just two items:
 *# A tick box, where you tick if you want a standard table structure to be used to reflect the fields in the [[Monthly_log_files|detailed log file]]
 *# A text box where you can change the default table name to one that suits you better. Do not leave this blank, SQL requires a table name.
-** If the table does not exist it will be created, before the new row is added
+** The upload you select here will happen every time MX creates a new line in the monthly log file, it might be every 10 minutes, but you may have configured a different interval.
-** If the table already has rows, the upload just creates a new row
 *''Custom upload - minutes interval''
-** This feature allows you to specify your own SQL for an upload to be repeated every NN minutes. There are 3 items:
-*# A tick box to enable or disable this upload (so you can leave the SQL recorded, but stop running it when you like.
-*# The SQL you want to run, it should include INSERT IGNORE (or REPLACE or UPDATE) to insert a row, the name of the table, the columns to be updated and the values you include in your SQL are expressed as web tags.
-*# A drop down for the number of minutes between runs, the default is 10, but if your weather station updates less frequently, maybe you will choose 15, 20, 30, or 60 as the interval out of the 11 available in drop down.
 ** One way you could use this option, is to replace the monthly log file upload if you wanted to change the schema, by leaving out some columns if your weather station is not able to measure all the derivatives included in the standard schema.
+** This feature allows you to specify your own SQL for an upload to be repeated every NN minutes. Unlike the Monthly log file upload option you choose what schema (columns) are in the table that you are uploading a new row to and indeed exactly what SQL is used.
+**Apart from the need to press the Save button that follows all the options, there are 3 items just for this option:
+**# A tick box to enable or disable this upload (so you can leave the SQL recorded, but stop running it when you like.
+**# The SQL you want to run, it should include INSERT IGNORE (or REPLACE or UPDATE) to insert/replace/update a row, include as all SQL needs the name of the table, include the columns to be updated and include the values either expressed as web tags or derived from a sub-query.
+**# A drop down for the number of minutes between runs, the default is 10, but if your weather station updates less frequently, maybe you will choose 15, 20, 30, or 60 as the interval out of the 11 available in drop down.
 * ''Realtime.txt upload''
-** This feature allows you to upload the file that Cumulus recreates on the most frequent basis.
+** This feature allows you to upload the file that Cumulus recreates on the most frequent basis. MX does not use the realtime.txt file in any of its supplied components, so that file by default is not available on your web server. There is an option elsewhere (Internet Settings screen) to upload this file, but an alternative is to get MX to put the values it would put into that file into a database table and this option is to do that.
-**There are 3 items for this option:
+**Apart from the Save button below all options, there are 3 items specifically for this option:
 *# A tick box to enable this very frequent upload
 *# A text box where you can change the default table name
 *# A text box where you enter a retention string in format '''retainVal=NNN retainUnit=XXXX''' where NNN is a number from 1 to 3 digits long, and XXX is a time unit like "days"
-** Because the updates are so frequent this database table grows very quickly, and you need to say when it should delete the older rows so the table never has too many rows. If you think about it, after a few days, you probably do not need to look at this very detailed level of values information within a day. In that case set retention to delete after a few days.
+** Because the updates are so frequent this database table grows very quickly, and you need to say when it should delete the older rows so the table never has too many rows. If you think about it, after a few days, you probably do not need to look at this very detailed level of values information within a day. In that case set retention to delete after a few days ''retainVal=3 retainUnit=days''.
 *''Custom upload - seconds interval''
-** This feature allows you to specify your own SQL for an upload to be repeated every NN seconds. There are 3 items:
+** This feature allows you to specify your own SQL for an upload to be repeated every NN seconds. This caters for when you want something like the values in "realtime.txt" but want to specify your own schema (set of column names) or own interval between updates. In theory the number of seconds specified here might represent anything between how frequently your weather station reports readings and several hours.
+**Apart from the Save button below all options, there are 3 items specifically for this option:
 *# A tick box to enable or disable this upload (so you can leave the SQL recorded, but stop running it when you like.
 *# The SQL you want to run, it should include INSERT IGNORE (or REPLACE or UPDATE) to insert a row, the name of the table, the columns to be updated and the values you include in your SQL are expressed as web tags. You can have more than one SQL statement in this box (end each with semi-colon) so you might want to add a delete "DELETE FROM YourTableName WHERE LogDateTime < DATE_SUB(NOW(), INTERVAL 7 DAY);" after your update/insert command to replicate the retention option of the previous feature, in this case deleting rows over a week old.