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 From: [email protected] <[email protected]> On Behalf Of gjr80 Sent: Thursday, July 16, 2020 8:38 PM To: weewx-development <[email protected]> Subject: [weewx-development] Re: Stupid Database Question 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 On Friday, 17 July 2020 at 09:44:20 UTC+10 [email protected] <mailto:[email protected]> wrote: 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 -- 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 [email protected] <mailto:[email protected]> . To view this discussion on the web visit https://groups.google.com/d/msgid/weewx-development/c453ccaf-8c70-41dd-87e8-a66b556c14a2n%40googlegroups.com <https://groups.google.com/d/msgid/weewx-development/c453ccaf-8c70-41dd-87e8-a66b556c14a2n%40googlegroups.com?utm_medium=email&utm_source=footer> . -- 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 [email protected]. To view this discussion on the web visit https://groups.google.com/d/msgid/weewx-development/027f01d65c5e%24b0323550%2410969ff0%24%40gmail.com.
