Cumulus Wiki

ImportCumulusFile: Difference between revisions

ImportCumulusFile (view source)

Revision as of 20:47, 4 May 2020

7,002 bytes added ,  20:47, 4 May 2020
no edit summary
(→‎Schema: new)
No edit summary
Line 1: Line 1:
{{AddOnBanner|name=ImportCumulusFile|type=PHP|author=David A Jamieson / Mark Crossley|contact='DAJ' / 'mcrossley' via the forum|updated=11 September 2018|version=3.2}}
{{AddOnBanner|name=ImportCumulusFile|type=PHP|author=David A Jamieson / Mark Crossley|contact='DAJ' / 'mcrossley' via the forum|updated=04 May 2020|version=4.0}}


This is a PHP script designed to run on your webserver and import Cumulus log files into a MySQL database on your server.
This is a PHP script designed to run on your webserver and import Cumulus log files into a MySQL database on your server.
Line 9: Line 9:
*A My SQL database, username and password
*A My SQL database, username and password
*a Cumulus Log already uploaded
*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+=
=IMPORTANT NOTE FOR VERSION 2.0+=
Line 19: Line 22:
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.
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 3.0 and higher =
= Schema for version 4.0 and later =
 
== 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 =


== dayfile table ==
== Dayfile table ==
The database table that mirrors dayfile.txt (as at version 1.9.4) has the following schema (column names and their format):
The database table that mirrors dayfile.txt (as at version 1.9.4) has the following schema (column names and their format):
<pre>
<pre>
Line 133: Line 291:


= Version Control =
= 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]]
*3.2 - 11 September 2018 [[https://cumuluswiki.org/files/ImportCumulusFile_v3-2.txt Download]]
**Fixed inserting of WindbearingSym and CurrWindBearingSym
**Fixed inserting of WindbearingSym and CurrWindBearingSym
Retrieved from "https://www.cumuluswiki.org/a/Special:MobileDiff/5528"