5,838
edits
Category:Cumulus MX: Difference between revisions
m
→MySQL settings: tidy bullet numbering
m (→MySQL settings) |
m (→MySQL settings: tidy bullet numbering) |
||
|
** 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:
**# Save - a button after all option sections, until you click it any changes you make in this section have no effect
**# 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 before I enabled the query shown above.
** 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 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.
** 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.
** 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.
**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 ''retainVal=3 retainUnit=days''.
** 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.
**# The number of seconds between runs, the default is 10, but if your weather station updates less frequently, maybe you will choose 40 or 60 as the interval.
=== Alarms ===
| |||