PHP script to display monthly/yearly snow depth totals from diary.db

[Dador]

I am glad that, through elimination and after many trials, the guilty party was found.
Now that we know what the cause of the problem is, we can be confident that the next CMX updates will fix it.

[sutne]

Are you aware of this change in version 4.2.0?
From change-log:

External Programs now sets the working directory to the location of the executable/script rather than the Cumulus MX home directory

[surban21]

Are you aware of this change in version 4.2.0?
From change-log:

External Programs now sets the working directory to the location of the executable/script rather than the Cumulus MX home directory

Thank you for that information. I had read the whole change log but I guess I did not remember that bit of information. That would definitely affect the operation.

[SamiS]

Are you aware of this change in version 4.2.0?
From change-log:

External Programs now sets the working directory to the location of the executable/script rather than the Cumulus MX home directory

Thank you for that information. I had read the whole change log but I guess I did not remember that bit of information. That would definitely affect the operation.

That definitely can cause problems. But when looking at the earlier messages, Dador did have the full path to both executable and php file, so in my eyes that really should work. I think we need to summon @mcrossley to this investigation.

Edit. But when thinking ahead… If you run php from c:\php, and then point to the php file located in CMX directory, the working dir still regardless causes the php script search for the diary.db from c:\php ? So in this specific use case you probably would need to modify the php code so that it points to diary.db with the full path?

[freddie]

Edit. But when thinking ahead… If you run php from c:\php, and then point to the php file located in CMX directory, the working dir still regardless causes the php script search for the diary.db from c:\php ? So in this specific use case you probably would need to modify the php code so that it points to diary.db with the full path?

This is the bit that is tripping people up - by the script assuming it is running in a certain place on the filesystem and accessing resources relative to that position. It always pays to code absolute paths in your scripts, or to 'cd' to that location within the script.

[surban21]

@SamiS - I located the issue and of course it was on my end inside snowSumData.php script. The issue is fixed and finishing testing then I'll roll out the revised script shortly.

Thank you for you help in trying to isolate the problem. If I had only remembered that change in the CMX 4.2.0 change log I could have saved us all the frustration. LOL.

[SamiS]

@SamiS - I located the issue and of course it was on my end inside snowSumData.php script. The issue is fixed and finishing testing then I'll roll out the revised script shortly.

Thank you for you help in trying to isolate the problem. If I had only remembered that change in the CMX 4.2.0 change log I could have saved us all the frustration. LOL.

Glad to help, usually there is something to learn myself also. Since this change has caused trouble also for CUtils users, this still may turn to Mark reverting the change in future CMX builds… remains to be seen.

-Sami

[surban21]

It has been reported and verified by me that the script wasn't updating snow information from CMX 4.2.0 and above. Turns out it was an oversight on my part in the snowSumData.php script when the new CMX 4.2.0 version was released. I believe I have corrected this issue and the script should run properly.

For New Users
The change(s) have already been applied to the zip file on the first page for new users of this script.

For Existing Users
The only file needing to be replaced is snowSumData.php. That file can be found below. Download the file and replace it with the one already installed.

snowSumData.zip

This should take care of the problem and update the snow information on your website.

[Dador]

I can confirm that the changes made by Steve solved the problem with the snowSumData script executed by CMX since 4.2.

[Dador]

To make it funny, recent fixes to the snowSumData.php script code don't work with CMX 4.3.
So I went back to the original code. The script works in the original version.

I was in a hurry to judge. Currently, the script does not work with CMX 4.3 in the old and new versions.

It also doesn't work with Windows Task Scheduler

Maybe there are some changes in diary.db that cause the error?
Steve Urban, I think you need to look into this.

[HansR]

To make it funny, recent fixes to the snowSumData.php script code don't work with CMX 4.3.
So I went back to the original code. The script works in the original version.

I was in a hurry to judge. Currently, the script does not work with CMX 4.3 in the old and new versions.

It also doesn't work with Windows Task Scheduler

Maybe there are some changes in diary.db that cause the error?
Steve Urban, I think you need to look into this.

As the ChangeLog of CMX notes: the 4.2. changes have been reverted to return to the old situation!
So the change has been made too fast without consulting Mark. I think it is best for the code to revert to the prior version.
(If I estimate the situation correctly: I do not use this script.)

[surban21]

Maybe there are some changes in diary.db that cause the error?
Steve Urban, I think you need to look into this.

@Dador - the diary.db in CMX 4.3.0 has been restructured so unfortunately the present snow scripts are going to fade away. They will only work on CMX 4.2.xx and below until I decide if I want to rewrite the scripts for the new versions of CMX. Thanks for using them though.

Steve

[Dador]

Thank you Steve for your commitment to this script and your willingness to help and extensive, clear advice.

I will be happy to continue using your script if you want to develop it.

Good luck!

[lse-cumulus]

I have been working on planning my upgrade to CMX 4.3 and saw that the structure of diary.db will change for sqlite table DiaryData.
So I noted that a modification of Steve's snowSumData.php script will be necessary.

To keep the required code change small, I was thinking of introducing a Sqlite VIEW that maps the new DiaryData to the old structure VDiaryData.
But I have not yet started checking out this option in more detail: need to concatenate Date+Time into old columnname Timestamp, keep column Entry as it is, map new column Snow24h to old column SnowDepth (as that was how I understood Steve was interpreting the SnowDepth as amount of falling) and then find a mapping for the old columns (no longer present in new structure) SnowFalling (1 if Snow24h > 0) and SnowLying (1 if SnowDepth > 0).

And last not least make some manual conversion to fill out Snow24h in new structure based on old data (derived from SnowDepth), as the standard CMX conversion leaves Snow24h empty and and just propagates SnowDepth, as it assumes SnowDepth was used for snowLying amount of snow and not as SnowFalling amount of snow (as I used it to follow Steve's recommendation). As I have only 17 entries from 2022 to 2024 this can be done manually. And that I have to do anyway if want to use the CMX and CU charts and show also the falling Snow24h values ...

Then the change to snowSumData.php script should only be to change the table name to the viewName ...

On the other hand CMX 4.3 and CU 7.x version from Hans will provide me with a new snow diary and even a chart ...
But still would be nice to have Steve's SnowSummary.php continue to working also with CMX 4.3 (after I just got it working a month ago for PHP 5.3 and my PW) ...
https://leonas.ddns.net/Wetterstation.S ... ummary.php

[lse-cumulus]

I think the following VIEW should compensate for the structual change in CMX v4.3 for table DiaryData in SQLite DB diary.db:

4.1.2025/leo: fixed typo in View Definition Timstamp/Timestamp; please note that sqlite versions <3.9 do not support column-name-list in Create View Statement

Code: Select all

CREATE VIEW IF NOT EXISTS VDiaryData (Timestamp, entry, snowFalling, snowLying, snowDepth) AS 
	SELECT datetime(d,t) AS Timestamp, e AS entry, sf AS snowFalling, sl AS snowLying, sh AS snowDepth FROM 
		(SELECT Date AS d, Time AS t, Entry e, 
			CASE WHEN Snow24h > 0 THEN 1 ELSE 0 END AS sf,
	        	CASE WHEN SnowDepth > 0 THEN 1 ELSE 0 END AS sl,
			Snow24h AS sh FROM DiaryData);
			
or without Column-Name-List:
CREATE VIEW IF NOT EXISTS VDiaryData AS 
	SELECT datetime(d,t) AS Timestamp, e AS entry, sf AS snowFalling, sl AS snowLying, sh AS snowDepth FROM 
		(SELECT Date AS d, Time AS t, Entry e, 
			CASE WHEN Snow24h > 0 THEN 1 ELSE 0 END AS sf,
	        	CASE WHEN SnowDepth > 0 THEN 1 ELSE 0 END AS sl,
			Snow24h AS sh FROM DiaryData);

Tested within SQLITE3.exe command line tool on a converted TEST CMX 4.3.2 database (but column Snow24h not yet manually adapted and not yet filled out after conversion).

Code: Select all

/* OLD Schema:*/
sqlite> .open C:\Wetterstation.CumulusMX\datav3\diary.db
sqlite> .schema
CREATE TABLE IF NOT EXISTS "DiaryData"(
"Timestamp" datetime primary key not null ,
"entry" varchar ,
"snowFalling" integer ,
"snowLying" integer ,
"snowDepth" float );

sqlite> select * from DiaryData;
2024-12-09 00:00:00||1|0|0.0
2024-12-06 00:00:00||1|1|1.0
2024-12-07 00:00:00||1|0|1.0
2023-11-01 00:00:00||1|0|2.0
2023-02-01 00:00:00||1|0|2.0
2022-05-01 00:00:00||1|0|2.0
2022-12-01 00:00:00||1|0|2.0

/* NEW Schema: */
sqlite> .open C:\Wetterstation.CumulusMX\data\diary.db
sqlite> .schema
CREATE TABLE IF NOT EXISTS "DiaryData" (
"Date" datetime primary key not null ,
"Time" time not null ,
"Entry" varchar ,
"Snow24h" float ,
"SnowDepth" float );
CREATE TABLE dbversion (ver INTEGER PRIMARY KEY);
CREATE VIEW VDiaryData (Timestamp, entry, snowFalling, snowLying, snowDepth) AS
SELECT datetime(d,t) AS Timstamp, e AS entry, sf AS snowFalling, sl AS snowLying, sh AS snowDepth FROM
(SELECT Date AS d, Time AS t, Entry e,
CASE WHEN Snow24h > 0 THEN 1 ELSE 0 END AS sf,
CASE WHEN SnowDepth > 0 THEN 1 ELSE 0 END AS sl,
Snow24h AS sh FROM DiaryData)
/* VDiaryData(Timestamp,entry,snowFalling,snowLying,snowDepth) */;

sqlite> select * from DiaryData;
2022-05-01|00:00:00|||2.0
2022-12-01|00:00:00|||2.0
2023-02-01|00:00:00|||2.0
2023-11-01|00:00:00|||2.0
2024-12-06|00:00:00|||1.0
2024-12-07|00:00:00|||1.0
2024-12-09|00:00:00|||0.0
2025-01-02|00:00:00||1.0|1.0

sqlite> select * from VDiaryData;
2022-05-01 00:00:00||0|1|
2022-12-01 00:00:00||0|1|
2023-02-01 00:00:00||0|1|
2023-11-01 00:00:00||0|1|
2024-12-06 00:00:00||0|1|
2024-12-07 00:00:00||0|1|
2024-12-09 00:00:00||0|0|
2025-01-02 00:00:00||1|1|1.0

Not yet tested with modified snowSumData.php script from Steve. But I will do that after my final CMX 4.3.x converion.

4.1.2025/leo: tested succesfully with CMX 4.3.2 build 4067 and modified php script snowSumData.php v1.2 by replacing table name DiaryData by View name VDiaryData; Adapted the WeatherDiary data via CMX admin interface to fill out the Snow24h values;
see link https://leonas.ddns.net/Wetterstation.S ... ummary.php

My modified version of snowSumData.php v1.2 that works with CMX 4.3.2 and the new diary.db format (after you created the view) and also works with older PHP 5.3.27 and sqllite 3.7.7.1 can be found in the attachment.