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 On Fri, Jul 17, 2020 at 10:21 AM <radion7...@gmail.com> wrote: > 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:* weewx-development@googlegroups.com < > weewx-development@googlegroups.com> *On Behalf Of *gjr80 > *Sent:* Thursday, July 16, 2020 8:38 PM > *To:* weewx-development <weewx-development@googlegroups.com> > *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 cl...@n7qnm.net 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 weewx-development+unsubscr...@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 > <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 weewx-development+unsubscr...@googlegroups.com. > To view this discussion on the web visit > https://groups.google.com/d/msgid/weewx-development/027f01d65c5e%24b0323550%2410969ff0%24%40gmail.com > <https://groups.google.com/d/msgid/weewx-development/027f01d65c5e%24b0323550%2410969ff0%24%40gmail.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 weewx-development+unsubscr...@googlegroups.com. To view this discussion on the web visit https://groups.google.com/d/msgid/weewx-development/CAPq0zEAyoRg1isGRu6d7Di_BgjZevk8NxNYFeQaGYp2KBeDUUA%40mail.gmail.com.
