1,154
edits
ImportCumulusFile: Difference between revisions
no edit summary
(→Schema: new) |
No edit summary |
||
|
{{AddOnBanner|name=ImportCumulusFile|type=PHP|author=David A Jamieson / Mark Crossley|contact='DAJ' / 'mcrossley' via the forum|updated=
This is a PHP script designed to run on your webserver and import Cumulus log files into a MySQL database on your server.
*A My SQL database, username and password
*a Cumulus Log already uploaded
=IMPORTANT NOTE FOR VERSION 4.0+=
Version 4.0 changes the SQL table structures from version 2 & 3. If you are currently using version 2 or 3 and wish to upgrade, then you should DROP the existing tables and let the version 4 script recreate them, or alter the table structures to add the new columns.
=IMPORTANT NOTE FOR VERSION 2.0+=
You could of course run 1.x and 2.x in parallel whilst you made the switch over gradually. Just alter the table names in v2 of the script slightly to avoid name collisions.
= Schema for version
== Dayfile table ==
The database table that mirrors dayfile.txt (as at version 3.6.0) has the following schema (column names and their format):
<pre>
'LogDate', 'date NOT NULL'
'HighWindGust', 'decimal(4,1) NOT NULL'
'HWindGBear', 'smallint(3) unsigned zerofill NOT NULL'
'THWindG', 'varchar(5) NOT NULL'
'MinTemp', 'decimal(5,1) NOT NULL'
'TMinTemp', 'varchar(5) NOT NULL'
'MaxTemp', 'decimal(5,1) NOT NULL'
'TMaxTemp', 'varchar(5) NOT NULL'
'MinPress', 'decimal(6,2) NOT NULL'), // 8
'TMinPress', 'varchar(5) NOT NULL'
'MaxPress', 'decimal(6,2) NOT NULL'
'TMaxPress', 'varchar(5) NOT NULL'
'MaxRainRate', "decimal(4,$rainDec) NOT NULL" /* Note the number of decimal places is a variable, set to what suits you */
'TMaxRR', 'varchar(5) NOT NULL'
'TotRainFall', "decimal(6,$rainDec) NOT NULL" /* Note the number of decimal places is a variable, set to what suits you */
'AvgTemp', 'decimal(4,2) NOT NULL'
'TotWindRun', 'decimal(5,1) NOT NULL'
'HighAvgWSpeed', 'decimal(3,1)'
'THAvgWSpeed', 'varchar(5)'
'LowHum', 'decimal(4,1)'
'TLowHum', 'varchar(5)'
'HighHum', 'decimal(4,1)'
'THighHum', 'varchar(5)'
'TotalEvap', "decimal(5,$rainDec)" /* Note the number of decimal places is a variable, set to what suits you */
'HoursSun', 'decimal(3,1)'
'HighHeatInd', 'decimal(4,1)'
'THighHeatInd', 'varchar(5)'
'HighAppTemp', 'decimal(4,1)'
'THighAppTemp', 'varchar(5)'
'LowAppTemp', 'decimal(4,1)'
'TLowAppTemp', 'varchar(5)'
'HighHourRain', "decimal(4,$rainDec)" /* Note the number of decimal places is a variable, set to what suits you */
'THighHourRain', 'varchar(5)'
'LowWindChill', 'decimal(4,1)'
'TLowWindChill', 'varchar(5)'
'HighDewPoint', 'decimal(4,1)'
'THighDewPoint', 'varchar(5)'
'LowDewPoint', 'decimal(4,1)'
'TLowDewPoint', 'varchar(5)'
'DomWindDir', 'smallint(3) unsigned zerofill'
'HeatDegDays', 'decimal(4,1)'
'CoolDegDays', 'decimal(4,1)'
'HighSolarRad', 'decimal(5,1)'
'THighSolarRad', 'varchar(5)'
'HighUV', 'decimal(3,1)'
'THighUV', 'varchar(5)'
'HWindGBearSym', 'varchar(3)'
'DomWindDirSym', 'varchar(3)'
'MaxFeelsLike', 'decimal(4,1)' /* New in v4.0 */
'TMaxFeelsLike', 'varchar(5)' /* New in v4.0 */
'MinFeelsLike', 'decimal(4,1)' /* New in v4.0 */
'TMinFeelsLike', 'varchar(5)' /* New in v4.0 */
</pre>
== Monthly table ==
<pre>
'LogDateTime', 'DATETIME NOT NULL'
'Temp', 'decimal(4,1) NOT NULL'
'Humidity', 'decimal(4,1) NOT NULL'
'Dewpoint', 'decimal(4,1) NOT NULL'
'Windspeed', 'decimal(4,1) NOT NULL'
'Windgust', 'decimal(4,1) NOT NULL'
'Windbearing' 'smallint(3) unsigned zerofill NOT NULL'
'RainRate', 'decimal(4,$rainDec) NOT NULL'
'TodayRainSoFar', 'decimal(4,$rainDec) NOT NULL'
'Pressure', 'decimal(6,2) NOT NULL'
'Raincounter', 'decimal(6,2) NOT NULL'
'InsideTemp', 'decimal(4,1) NOT NULL'
'InsideHumidity', 'decimal(4,1) NOT NULL'
'LatestWindGust', 'decimal(5,1) NOT NULL'
'WindChill', 'decimal(4,1) NOT NULL'
'HeatIndex', 'decimal(4,1) NOT NULL'
'UVindex', 'decimal(4,1)'
'SolarRad', 'decimal(5,1)'
'Evapotrans', 'decimal(4,1)'
'AnnualEvapTran', 'decimal(5,1)'
'ApparentTemp', 'decimal(4,1)'
'MaxSolarRad', 'decimal(5,1)'
'HrsSunShine', 'decimal(3,1)'
'CurrWindBearing', 'varchar(3)'
'RG11rain', 'decimal(4,1)'
'RainSinceMidnight', 'decimal(4,1)'
'WindbearingSym', 'varchar(3)'
'CurrWindBearingSym', 'varchar(3)'
'FeelsLike', 'decimal(4,1)' /* New in 4.0 */
</pre>
== Realtime table ==
<pre>
'LogDateTime', 'DATETIME NOT NULL'
'temp', 'decimal(4,1) NOT NULL'
'hum', 'decimal(4,1) NOT NULL'
'dew', 'decimal(4,1) NOT NULL'
'wspeed', 'decimal(4,1) NOT NULL'
'wlatest', 'decimal(4,1) NOT NULL'
'bearing', 'smallint(3) zerofill unsigned NOT NULL'
'rrate', 'decimal(4,$rainDec) NOT NULL'
'rfall', 'decimal(4,$rainDec) NOT NULL'
'press', 'decimal(6,2) NOT NULL'
'currentwdir', 'varchar(3) NOT NULL'
'beaufortnumber', 'varchar(2) NOT NULL'
'windunit', 'varchar(4) NOT NULL'
'tempunitnodeg', 'varchar(1) NOT NULL'
'pressunit', 'varchar(3) NOT NULL'
'rainunit', 'varchar(2) NOT NULL'
'windrun', 'decimal(4,1) NOT NULL'
'presstrendval', 'varchar(6) NOT NULL'
'rmonth', 'decimal(4,$rainDec) NOT NULL'
'ryear', 'decimal(4,$rainDec) NOT NULL'
'rfallY', 'decimal(4,$rainDec) NOT NULL'
'intemp', 'decimal(4,1) NOT NULL'
'inhum', 'decimal(4,1) NOT NULL'
'wchill', 'decimal(4,1) NOT NULL'
'temptrend', 'varchar(5) NOT NULL'
'tempTH', 'decimal(4,1) NOT NULL'
'TtempTH', 'varchar(5) NOT NULL'
'tempTL', 'decimal(4,1) NOT NULL'
'TtempTL', 'varchar(5) NOT NULL'
'windTM', 'decimal(4,1) NOT NULL'
'TwindTM', 'varchar(5) NOT NULL'
'wgustTM', 'decimal(4,1) NOT NULL'
'TwgustTM', 'varchar(5) NOT NULL'
'pressTH', 'decimal(6,2) NOT NULL'
'TpressTH', 'varchar(5) NOT NULL'
'pressTL', 'decimal(6,2) NOT NULL'
'TpressTL', 'varchar(5) NOT NULL'
'version', 'varchar(8) NOT NULL'
'build', 'varchar(5) NOT NULL'
'wgust', 'decimal(4,1) NOT NULL'
'heatindex', 'decimal(4,1) NOT NULL'
'humidex', 'decimal(4,1) NOT NULL'
'UV', 'decimal(3,1) NOT NULL'
'ET', 'decimal(4,$rainDec) NOT NULL'
'SolarRad', 'decimal(5,1) NOT NULL'
'avgbearing', 'smallint(3) zerofill unsigned NOT NULL'
'rhour', 'decimal(4,$rainDec) NOT NULL'
'forecastnumber', 'tinyint(2) unsigned NOT NULL'
'isdaylight', 'tinyint(1) unsigned NOT NULL'
'SensorContactLost', 'tinyint(1) unsigned NOT NULL'
'wdir', 'varchar(3) NOT NULL'
'cloudbasevalue', 'int NOT NULL'
'cloudbaseunit', 'varchar(2) NOT NULL'
'apptemp', 'decimal(4,1) NOT NULL'
'SunshineHours', 'decimal(3,1) NOT NULL'
'CurrentSolarMax', 'decimal(5,1) NOT NULL'
'IsSunny', 'tinyint(1) unsigned NOT NULL'
'FeelsLike', 'decimal(4,1)' /* New in 4.0 */
</pre>
= Schema for version 3.0 =
==
The database table that mirrors dayfile.txt (as at version 1.9.4) has the following schema (column names and their format):
<pre>
= Version Control =
*4.0 - 4 May 2020 [[https://cumuluswiki.org/files/ImportCumulusFile_v4-0.txt Download]]
**Updated for CMX 3.6.0 addition of Feels Like
*3.2 - 11 September 2018 [[https://cumuluswiki.org/files/ImportCumulusFile_v3-2.txt Download]]
**Fixed inserting of WindbearingSym and CurrWindBearingSym
| |||