1,154
edits
ImportCumulusFile: Difference between revisions
Undo revision 4291 by Mcrossley (talk)
|
{{AddOnBanner|name=ImportCumulusFile|type=PHP|author=David A Jamieson / Mark Crossley|contact='DAJ' / 'mcrossley' via the forum|updated=11
This is a PHP script designed to run on your webserver and import Cumulus log files into a MySQL database on your server.
*a Cumulus Log already uploaded
=IMPORTANT NOTE FOR VERSION 2.0+=
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.
=Installation=
*Download the PHP script ... [http://wiki.sandaysoft.com/files/
*Save the file and edit it using a text editor -- there are five entries to be edited
:servername, username, password, database and security key
*type=xxxx
::this must be either the phrase ''dayfile'', ''monthly'' or ''
*file=xxxxx
An example URL...
=The result=
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 Control =
*3.2 - 11 September 2018 [[http://wiki.sandaysoft.com/files/ImportCumulusFile_v3-2.txt Download]]
**Fixed inserting of WindbearingSym and CurrWindBearingSym
*1.1c Small modification to code to handle date delimiter with a dot▼
*3.1 - 1 March 2018 [[http://wiki.sandaysoft.com/files/ImportCumulusFile_v3-1.txt Download]]
*1.1b Added a variable to consider decimal separator▼
**Fix variable typos in realtime table creation status messages
*1.1a Added date delimiter▼
**Remove some HTTP headers that were causing problems on some servers
*1.1 Added the facility to identify the delimiter used on the import file▼
*3.0 - 6 November 2016 [[http://wiki.sandaysoft.com/files/ImportCumulusFile_v3-0.txt Download]]
*1.0 First Release▼
**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 [[http://wiki.sandaysoft.com/files/ImportCumulusFile_v2-8.txt Download]]
**Added addtional 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;
*2.7 - 23 February 2015 [[http://wiki.sandaysoft.com/files/ImportCumulusFile_v2-7.txt Download]]
**Fixed realtime/monthly/day tables creates, to make rainfall 2dp to allow for inches
**Fixed monthly table create, to make evapotrans 2 dp
**Converted from depreciated mysql to mysqli
*2.6 - 02 April 2014, Mark Crossley [[http://wiki.sandaysoft.com/files/ImportCumulusFile_v2-6.txt Download]]
**Fixed a PHP vulnerability that could reveal your passcode
*2.5 - 03 December 2013 (uploaded 02 Feb 2014), Mark Crossley [[http://wiki.sandaysoft.com/files/ImportCumulusFile_v2-5.txt 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 [[http://wiki.sandaysoft.com/files/ImportCumulusFile_v2-4.txt Download]]
**Changed day file humidity fields from varchar(3) to decimal(4,1)
*2.3 - 05 June 2013, Mark Crossley [[http://wiki.sandaysoft.com/files/ImportCumulusFile_v2-3.txt 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 [[http://wiki.sandaysoft.com/files/ImportCumulusFile_v2-1.txt Download]]
**Fixed typo in day file table field name LowDewPint -> LowDewPoint
*2.0 - 13 March 2013, Mark Crossley [[http://wiki.sandaysoft.com/files/ImportCumulusFile_v2-0.txt 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. 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. [[http://wiki.sandaysoft.com/files/ImportCumulusFile_v1-4a.txt 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. [[http://wiki.sandaysoft.com/files/ImportCumulusFile_v1-3b.txt Download]]
*1.3a - 30 Nov 2012, Mark Crossley, added line breaks to output text [[http://wiki.sandaysoft.com/files/ImportCumulusFile_v1-3a.txt Download]]
*1.3 - 27 Nov 2012, Mark Crossley, updated for Cumulus b1050 [[http://wiki.sandaysoft.com/files/ImportCumulusFile_v1-3.txt Download]]
*1.2 - 6 May 2011, Mark Crossley, updated for Cumulus 1.9.1
▲*1.1c Small modification to code to handle date delimiter with a dot [[http://wiki.sandsoft.com/files/ImportCumulusFile_v1-1c.txt Download]]
▲*1.1b Added a variable to consider decimal separator
▲*1.1a Added date delimiter
[[Category:WebTools]]
| |||