tecronin
Junior Member
Posts: 91
Raspberry Pi: Yes
|
Post by tecronin on Mar 23, 2021 18:39:44 GMT -8
there's 2 databases being created via the SQL script and their existence checked in the skyweather2 app
SkyWeather2 and WeatherSenseWireless
both have weatherdata tables but only SkyWeather2 is being populated
there's nothing in WeatherSenseWireless but I don't have the air quality sensor yet. both DB's are referenced within the code base as well.
what is WeatherSenseWireless used for?
Also in the table defs for the primary key, id fields, are signed so you only get half the storage.
You might want to also make them bigint as well. The timestamp column has on update,
that could reek havok if you have to massage any of the data later.
|
|
|
|
Post by SDL on Mar 24, 2021 9:02:52 GMT -8
WeatherSenseWireless is used by two programs, SkyWeather2 and WeatherSense.py, that is why there are two WeatherData tables.
WeatherSenseWireless is for supporting the new WeatherSense Lighting, Air Quality, SolarMAX and other devices being developed.
Holy Cow! Your find on the timestamp is a real bug! That is exactly what that is set for. It is not supposed to update. I'll put that on the bug list to get fixed ASAP.
BP
|
|
tecronin
Junior Member
Posts: 91
Raspberry Pi: Yes
|
Post by tecronin on Mar 24, 2021 11:54:34 GMT -8
Great!
thanks for the update
|
|
|
|
Post by truefinn on Mar 25, 2021 9:21:28 GMT -8
As to "on update" extra rule for "timestamp" column, in your post, which I highly appreciated.
I've been thru that. When updating correct values to "WeatherData" table's "BarometricPressure" and "BarometricPressureSeaLevel" columns I lost my previous timeline, because "TimeStamp" column was set to current time during "UPDATE WeatherData ..." SQL command.
I'm build to make same mistakes again, so I decided to do something about it, in this case. What I did is as follows:
1. I added a new column "DateMeasured" to "WeatherData" table as a last column
2. Ran SQL -clause "UPDATE WeatherData set DateMeasured to Timestamp" (TimeStamp columns was naturally set to current time but DateMeasured retained the right timeline values)
3. Created a unique secondary index "SecKey1" on columns "DateMeasured" and "ID" (because when running queries in the future ex. to a certain day three mounts ago, the query don't have to read the whole table through)
4. Made required changes into "pclogging.py" file to insert correct value to new "DateMeasured" -column
5. Made required changes into "weather_page.py" file to retrieve rows from "WeatherData" table with WHERE -clauses pointing at a correct column "DateMeasured"
Now I'm save from my repetitive errors. And maybe Database SCHEMA is a bit cleaner?
Detailed changes are of course available, if wanted. I anyhow thought that it would be better to wait for version 025 to be released, because there's probably some extra additions in my customized code compared to the official 024 version.
|
|
tecronin
Junior Member
Posts: 91
Raspberry Pi: Yes
|
Post by tecronin on Mar 25, 2021 10:55:40 GMT -8
|
|
