ImportCumulusFile: Difference between revisions

From Cumulus Wiki
Jump to navigationJump to search
m (→‎Version Control: made clearer which relate to legacy Cumulus, which to MX)
m (→‎Monthly table (2.0): Minor clarification)
 
Line 259: Line 259:
</pre>
</pre>


== Monthly table (old) ==
== Monthly table (2.0 version) ==
For versions below 2.0, the first column as shown below did not exist, instead there was one column for date and one column for time.
For versions below 2.0, the first combined column as shown with both date and time did not exist, instead there was one column for date and one column for time.


As for the MX version as pointed out by Water01 in the support forum, the RG-11 field in the code and in the schema below is wrong, it should have 2 decimal places for those using inches like the other rainfall fields.
As pointed out by Water01 in the support forum, the RG-11 field in the code and in the schema below is wrong, it should have 2 decimal places for those using inches like the other rainfall fields.


<pre>
<pre>

Latest revision as of 10:43, 14 January 2022

Name: ImportCumulusFile
Type: PHP
Author: David A Jamieson / Mark Crossley
Contact: 'DAJ' / 'mcrossley' via the forum
Last update: 01 September2020
Version: 4.3

This is a PHP script designed to run on your web server and will import Cumulus log files into a MySQL database table on your server.

Applicability to Cumulus 1 and MX

This script was originally written for Cumulus 1, and all versions (except 4.x and later) will work with Cumulus 1. Please only use the latest version (currently 4.x) of the script with Cumulus MX.

Please be aware that for Cumulus MX the daily and monthly tables in the database can also be updated with rows for past dates by using ExportMySQL.exe. For information on how Cumulus MX updates the database tables using that instead of the script on this page please see the admin interface article.

Requirements

The web server must have..

  • PHP
  • MySQL
  • A My SQL database, username and password
  • a Cumulus Log already uploaded

This script will create a table if no table exists for the selected log file.

Be aware that as the schema varies for different script versions, if a table already exists it must already have the number of columns in the table to match the number of columns populated in the version of the script you choose to use.

It does not matter if the table has more column than the number of fields in the Cumulus log file, because from version 1.3(b) of the script all schemas only prohibit nulls in the columns that are present in log file for all cumulus versions. Columns for fields only present in newer Cumulus versions permit nulls, so any columns where no filed is available will be populated with a null.

The requirements state that you must use MySQL, because the syntax used to specify the required type for each column in the CREATE syntax is specific to that database. If all you are doing is updating an existing table, then the code will work for other database types as well because the INSERT syntax works with most databases accepting SQL instructions.

IMPORTANT NOTE FOR VERSION 4.0+

Designed for MX.

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.

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

'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)'
'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 */
'MaxHumidex',     'decimal(5,1)'   /* New in v4.2 */
'TMaxHumidex',    'varchar(5)'     /* New in v4.2 */
'HWindGBearSym',  'varchar(3)'
'DomWindDirSym',  'varchar(3)'

Monthly table

It has been pointed out by "water01" in the support forum, that the specifier for the RG-11 field should match the other rainfall fields, and allow 2 decimal places for those using inches as their unit for rainfall, but this script (checked at version 4.1) restricts that field to just one decimal place.

'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)'
'FeelsLike',          'decimal(4,1)'   /* New in 4.0 */
'Humidex',            'decimal(4,1)'   /* New in 4.2 */
'WindbearingSym',     'varchar(3)'
'CurrWindBearingSym', 'varchar(3)'

Realtime table

'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 */


IMPORTANT NOTE FOR VERSION 2.x and 3.x

Designed for Cumulus 1.

Version 2.0 changes the SQL table structures from version 1.x. If you are currently using version 1.x and wish to upgrade, then you should DROP the existing tables and let the version 2 script recreate them. If this is unacceptable DO NOT UPGRADE, stay with version 1.x

The new table formats use a combined date/time field instead of separate, this makes constructing queries much simpler, it is worth making the change.

Of course if you do change, then any existing queries you are using will also have to be rewritten - take this into account. It may be possible to create VIEWs of the new tables that mimic the old table format, and for you to use those views with your existing queries, that is an exercise for you!

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 versions 2.0 to 3.0

Dayfile table (old)

The database table that mirrors dayfile.txt (with its fields as at Cumulus version 1.9.4) has the following schema at version 1.3 to 3.2 inclusive (column names and their format), for earlier Cumulus 1 versions with fewer fields, the schema will also work because all columns after 'TotWindRun' can default to null values:

'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'),                  
'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)'

Monthly table (2.0 version)

For versions below 2.0, the first combined column as shown with both date and time did not exist, instead there was one column for date and one column for time.

As pointed out by Water01 in the support forum, the RG-11 field in the code and in the schema below is wrong, it should have 2 decimal places for those using inches like the other rainfall fields.

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

Realtime table (old)

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

Installation

  • Download the PHP script ... there are download links beside each version in the version history below.
  • Edit the downloaded file using a text editor -- there are five entries to be edited
servername, username, password, database and security key
these are documented in the file at the top.
There are three further variables to consider and may require editing. Look at the text file you are importing. How is each field separated/delimited? How is the date delimited? How is a decimal number shown? In the UK there is nothing else needs changed. If for example your delimiter is a semi-colon and a dash then change the lines $field_delimiter and $date_delimiter accordingly.
if your decimal separator is a comma this also needs to be changed in the file using the $decimal_separator variable
  • save the file as ImportCumulusFile.php (or whatever name you prefer) and upload it to your web server which is where it should be run.
  • you will also need to upload any files it reads to your web server.

Using the script

Currently you can import two file types from Cumulus -- the dayfile and the Monthly log files.


Firstly decide the table to be populated in your SQL database. If the table does not exist the script will create it. Typically you have one table for the dayfile, and one or more for the Monthly Logs. You could import every monthly log file into one large SQL table.

If you re-import existing data the script will update the data in the table so you can run the import every day on the same file, dayfile, for example


You must pass several options with your URL... (They can be in any order but the first one must start with ? other with &)

  • type=xxxx
this must be either the phrase dayfile, monthly or realtime
  • file=xxxxx
the location on your webserver, relative to this script location, of your Cumulus File
example file=dayfile.txt or file=../data/Jan10log.txt
  • table=xxxx
the table within SQL to import the data. If it does not exist the script will create it
  • key=xxxxx
A security key, unique to you, to pass as part of the URL. This stops others from running the script on your server if the do not know the key.


An example URL...

http://www.myserver.com/ImportCumulusFile.php?type=dayfile&key=letmein&file=./data/dayfile.txt

The result

If all goes well it will create or update the table with the relevant data and report back the word "Done". If the script fails there are a number of outputs it could return to explain the problem.


Further automation

Using the above script, in combination with the Toolbox you could set an automated FTP rule to upload the dayfile.txt from Cumulus each day at 00:15 and then run an HTTP remote command (this script) to import it into the SQL database

Version 2.0 of the script can also be invoked from a command line. So for example importing the realtime file from the command line would look like this:

 php importcumulusfilev2-0.php file=realtime.txt type=realtime key=secretKey retainVal=7 retainUnit=day

This allows you to run the script as cron job on a Linux server, or as a scheduled task on a Windows server.

Version 4.3 of the script allows you to automate the generation of the monthly log file name rather than passing it as a variable. You need to edit the following entries to enable this feature...

 $auto_month = false;            // True: generate from date, false: read from params
 $month_file_dir= './data/';   // Directory where monthly log file is stored

Version Control

Latest MX download:

  • 4.3 - 1 September 2020 Download
    • Steinar Utne: If monthly and $auto_month=true then construct filename from date (i.e. <dir>MonYYlog.txt) rather than read from parameters

Earlier MX Downloads:

  • 4.2 - 18 August 2020 Download
    • Steinar Utne: Added Humidex in dayfile and monthly
  • 4.1 - 5 May 2020 (Download, right click, Save as)
    • Fix for files that do not have the required number of fields
  • 4.0 - 4 May 2020 Download removed
    • Updated for CMX 3.6.0 addition of Feels Like
  • 3.2 - 11 September 2018 [Download]
    • Fixed inserting of WindbearingSym and CurrWindBearingSym
  • 3.1 - 1 March 2018 [Download]
    • Fix variable typos in realtime table creation status messages
    • Remove some HTTP headers that were causing problems on some servers
  • 3.0 - 6 November 2016 [Download]
    • Converted to use prepared statements - more efficient for bulk inserts (the main use for script with CumulusMX?)
    • SQL injection protection improved - requires the table names to be defined in this script
  • 2.8 - 27 April 2015 [Download]
    • Added additional configuration parameter $rainUnit, default is 'mm'
    • Added 'rain since midnight' to the monthly log file table
    • This version is REQUIRED to support CumulusMX direct inserts
    • Altered layout the SQL statements to make finding/fixing stuff easier!
    • To updated existing monthly tables use the following SQL command [if you use 'mm' for rain change (4,2) to (4,1)]:
      • ALTER TABLE <<YOUR_MONTHLY_TABLENAME>> ADD COLUMN RainSinceMidnight DECIMAL(4,2) NULL AFTER RG11rain;

Legacy Cumulus 1 downloads:

  • 2.7 - 23 February 2015 [Download]
    • Fixed real-time/monthly/day tables creates, to make rainfall 2dp to allow for inches
    • Fixed monthly table create, to make evapotrans 2 dp as it uses same units as rainfall
    • Converted from depreciated mysql to mysqli
  • 2.6 - 02 April 2014, Mark Crossley [Download]
    • Fixed a PHP vulnerability that could reveal your passcode
  • 2.5 - 03 December 2013 (uploaded 02 Feb 2014), Mark Crossley [Download]
    • Changed day file HighSolarRad from varchar(5) to decimal(5,1)
      • To alter an existing table...
      • ALTER TABLE `dayfile` CHANGE `HighSolarRad` `HighSolarRad` DECIMAL(5,1) NULL DEFAULT NULL
  • 2.4 - 23 October 2013, Mark Crossley [Download]
    • Changed day file humidity fields from varchar(3) to decimal(4,1)
  • 2.3 - 05 June 2013, Mark Crossley [Download]
    • Fixed dayfile & monthfile table column HoursSun was decimal(2,1) to decimal(3,1)
  • 2.2 - 21 April 2013, Mark Crossley
    • Made dayfile UV a decimal(3,1) field rather than varchar(4)
  • 2.1 - 29 March 2013, Mark Crossley [Download]
    • Fixed typo in day file table field name LowDewPint -> LowDewPoint
  • 2.0 - 13 March 2013, Mark Crossley [Download]
    • Change monthly and realtime tables to use combined date/time columns rather than separate
    • Added optional retention time for realtime table. Records older than the specified age will be deleted. The query-string parameters are retainVal=NNN retainUnit=XXXX
    • Added checking to allow running of the script from a command line as well as via http
    • Added 'extra' columns to record wind directions as compass points where they are only provided as bearings:
Dayfile:  Added the following columns:- HWindGBearSym, DomWindDirSym
Monthly:  Added the following columns:- WindbearingSym, CurrWindBearingSym
  • 1.4a - 3 December 2012, Mark Crossley. Fixes syntax errors in 1.4, adds parameter presence checking. [Download]
  • 1.4 - 3 December 2012, Mark Crossley. Added ability to create and log data to a 'realtime' table.
  • 1.3b - 30 Nov 2012, Mark Crossley, changed so ALL missing values from the dayfile are entered into the table as NULL. If you have used the Cumulus editor, then they will be present in the file as blank fields. [Download]
  • 1.3a - 30 Nov 2012, Mark Crossley, added line breaks to output text [Download]
  • 1.3 - 27 Nov 2012, Mark Crossley, updated for Cumulus b1050 [Download]
  • 1.2 - 6 May 2011, Mark Crossley, updated for Cumulus 1.9.1
  • 1.1c - 11 Mar 2010,David A Jamieson, Small modification to code to handle date delimiter with a dot [Download]
  • 1.1b - 04 Feb 2010, David A Jamieson, Added a variable to consider decimal separator
  • 1.1a - David A Jamieson, Added date delimiter
  • 1.1 - David A Jamieson, Added the facility to identify the delimiter used on the import file
  • 1.0 - David A Jamieson, First Release, see top of page for details