Stupid Database Question

[radio...@gmail.com]

Hi!    My day job was (Oracle) DBA for a number of years and I have what’s probably a stupid question about modifying the WeeWx database. 

 

Specifically, I want to add some data around solar power generation (watts consumed, watts generated, panel efficiency) and possibly rename some of the “extraTemp” columns to be more descriptive (like “SolarCollectorTemp”).    I get the idea of modifying the schema file and then using wee_database to create a new archive table; but, I have over 300K archive records, and that takes a while.   Then there’s the whole daily summary process.

 

So – my question is:

Instead of taking weewx down while doing all of the “data movement”, why not:

1. Change the schema files (for documentation and in case a rebuild IS required)
2. Back up the database, which could be done “live
3. Use a database ALTER TABLE command to add the column(s) – which could be done “live”.
4. Stop weewx
5. Do any ALTER TABLEs that might be required to RENAME columns
6. Add/change “collectors” required for the new/changed columns
7. Restart weewx

 

Seems to me the only possible downsides to this would be:

1. Forgetting to change the schema files (“Bad DBA, no cookie!”)
2. The columns would eventually not be organized alphabetically (but this could be fixed by periodically, perhaps once/release, doing the wee_database thing.

 

Comments?

 

Clay Jackson

 

 

[Vince Skahan]

On Thursday, July 16, 2020 at 4:44:20 PM UTC-7, cl...@n7qnm.net wrote:

Specifically, I want to add some data around solar power generation (watts consumed, watts generated, panel efficiency) and possibly rename some of the “extraTemp” columns to be more descriptive (like “SolarCollectorTemp”).    I get the idea of modifying the schema file and then using wee_database to create a new archive table; but, I have over 300K archive records, and that takes a while.   Then there’s the whole daily summary process.

 

If you don't like the element names, you have full control over things of course, but you'll need to line up any skins you use to match.  If you use things off the shelf from others, you created a fork hell for yourself probably.   There's also some risk of getting stomped in future upgrades of weewx.

That's one of the reasons that weewx v3 added the ability to have alternate user-defined db.  In your case, I'd suggest you think about going that way rather than hacking on weewx core too much.  The ability to have your own separate db is pretty nice.  I use it a lot here.

But your way works of course as well if your db-fu is strong.

 

[gjr80]

My advice; by all means restructure your archive table and schema as required to suit but don't try manually adjusting the daily summary tables. Far easier and safer to use wee_database to drop and then rebuild them - almost certain to get yourself tied in knots one way or another otherwise. 300k of records is not much and will not take too long at all to rebuild on any half way decent machine.

Gary

[radio...@gmail.com]

Thanks, Gary (and others that responded).    I would absolutely NOT try messing with the summary tables; WAY too much “business logic” around those.    It was the archive table I was thinking about. 

 

Actually, this thread brings up some thoughts I’ve had for a while (at least since starting to use WeeWx).

 

Before WeeWx, I used some software out of Switzerland (which I can’t quickly find, I have archives, but…..) called “meteo” that had what (as a “data guy”) I considered to be a “most elegant” database design.   It used a concept of “Stations”,  “Sensors” and “observations”, all “normalized”.    The “station” table held attributes like the location of the station and so on, for example “Prosser, WA, USA, Lat, Long, Elevation, Description, TimeZone”    The “sensor” table defined what sensors were available, the units the sensor used, and other information relative to that “type” of sensor, for example “Acurite 5n1 Outside Temperature, Degrees F, .1 (precision)”.    Each observation was a “time series”, for example “StationID, SensorID, timestamp, value”.    Using indexes it was really fast to insert new records and to retrieve observations, storage required was minimal, and adding a new sensor, or even station, was pretty easy.  Reporting was a BIT more “challenging”; but, once the templates were right, it wasn’t THAT bad

 

If/when I ever find myself with lots of time and “nothing better to do”; I might do the analysis and see what it would take to change the weewx database to that sort of schema

--
You received this message because you are subscribed to the Google Groups "weewx-development" group.
To unsubscribe from this group and stop receiving emails from it, send an email to weewx-developm...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/weewx-development/c453ccaf-8c70-41dd-87e8-a66b556c14a2n%40googlegroups.com.

[Tom Keffer]

Unfortunately, we inherited the database schema from wview, the predecessor to WeeWX.

Nevertheless, many years ago, I explored using a "sixth normal form" database with WeeWX. The biggest difference from the present schema is that there was a separate column for the observation type, allowing new types to be introduced on the fly. I'm no db expert, and maybe I didn't get the indexes right, but it was frightfully slow. 

If I were to start from scratch, I would use InfluxDB, although it is still missing a few key features, most notably the ability to do queries in local time.

-tk

To view this discussion on the web visit https://groups.google.com/d/msgid/weewx-development/027f01d65c5e%24b0323550%2410969ff0%24%40gmail.com.