adding new observation to an existing secondary db

[vince]

I'm looking to add a field to the archive table for my purpleair.sdb file, and create the matching archive_day_whatever table and backfill it with data.

Does wee_database support operating on just one database ?  It isn't clear from the docs and --help info whether I can specify just one database to operate on.

The binding is:

    [[purpleair_binding]]

        manager = weewx.manager.DaySummaryManager

        schema = user.purpleair.schema

        table_name = archive

        database = purpleair_sqlite

I can probably get mostly there via low-level sqlite3 commands ala:

# create the table

echo "CREATE TABLE archive_day_aqi (dateTime INTEGER NOT NULL UNIQUE PRIMARY KEY, min REAL, mintime INTEGER, max REAL, maxtime INTEGER, sum REAL, count INTEGER, wsum REAL, sumtime INTEGER);" | sqlite3 foo.sdb

# alter the archive schema

echo "alter table archive add column aqi INTEGER;" | sqlite3 foo.sdb

But I'd still need to backfill the new archive_day_aqi table with its data.   I have 12+ years of archive records in my normal weewx.sdb, so obviously I don't want to rebuild all the databases for all of known time.  I'm just looking to build the archive_day_aqi table in the purpleair.sdb from the known data.   

Am I missing some switch combinations for wee_database perhaps ?

[gjr80]

Vince,

wee_database only operates on one database at at time. The database is specified by the optional —binding command line option. If omitted wee_database defaults to using the binding wx_binding which for most is weewx.sdb or the weewx (MySQL) database.

Gary

[vince]

On Sunday, June 30, 2019 at 4:18:30 PM UTC-7, gjr80 wrote:

wee_database only operates on one database at at time. The database is specified by the optional —binding command line option. If omitted wee_database defaults to using the binding wx_binding which for most is weewx.sdb or the weewx (MySQL) database.

Great.  Thanks ! 

[vince]

On Sunday, June 30, 2019 at 5:48:05 PM UTC-7, vince wrote:

On Sunday, June 30, 2019 at 4:18:30 PM UTC-7, gjr80 wrote:

wee_database only operates on one database at at time. The database is specified by the optional —binding command line option. If omitted wee_database defaults to using the binding wx_binding which for most is weewx.sdb or the weewx (MySQL) database.

One nit possible bug report.....

If you neglect to specify a date or date-range to rebuild, wee_database asks you to confirm then returns extremely quickly, rebuilding just the current day (it seems).   I'd expect to have it require that the user specify a time period combination or else it should return the help message to remind/nag them.

[gjr80]

If no date of any type is specified the daily summaries will be rebuilt from the time of last update through until the most recent archive record. For a normal functioning install these times will likely be the same and hence nothing is rebuilt (there is no need) so wee_database returns to the command prompt very quickly. On the other hand, the other extreme is if you drop the daily summaries first, all daily summaries are rebuilt from first to last archive record. Specific dates or date ranges are only rebuilt if a —date or —from and —to are provided. The console output should reflect what was done.

Is there anything making you suspect the current day is being rebuilt other than a quick return to the command prompt. A quick return does not necessarily mean anything was rebuilt.

Gary

[vince]

On Tuesday, July 2, 2019 at 3:07:28 PM UTC-7, gjr80 wrote:

If no date of any type is specified the daily summaries will be rebuilt from the time of last update through until the most recent archive record.

Uncertain what that means.  Update of what ?  The archive table ?  The day summary tables ?

 

For a normal functioning install these times will likely be the same and hence nothing is rebuilt (there is no need) so wee_database returns to the command prompt very quickly.

How'bout a me-hacked-brutally install ??? :-) 

 

On the other hand, the other extreme is if you drop the daily summaries first, all daily summaries are rebuilt from first to last archive record. Specific dates or date ranges are only rebuilt if a —date or —from and —to are provided. The console output should reflect what was done.

I didn't drop the tables, but I did try deleting all the records from the archive_day_myfieldname table.

With no options other than --binding and --rebuild-daily the console output nothing after I hit 'y' to proceed.

When I added the --from and --to values and dates that covered the whole range of archive table dates, it 'did' provide nice input and did the right thing.

 

Is there anything making you suspect the current day is being rebuilt other than a quick return to the command prompt. A quick return does not necessarily mean anything was rebuilt.

Yes - I deleted all records from the archive_day_<fieldname> table and ran wee_database --rebuild-daily --binding='mybindinghere' and it created one record in the archive_day_<fieldname> table, just the day summary for today.   It didn't do the previous days that the archive table were for.

So the behavior seemed to require specifying a date or date-range for it to work (and display to the console) as expected.

[gjr80]

On Tuesday, 2 July 2019 18:57:31 UTC-4, vince wrote:
> On Tuesday, July 2, 2019 at 3:07:28 PM UTC-7, gjr80 wrote:If no date of any type is specified the daily summaries will be rebuilt from the time of last update through until the most recent archive record.
>
>
> Uncertain what that means.  Update of what ?  The archive table ?  The day summary tables ?

—rebuild-daily only affects the daily summary tables, it has no effect on the archive.

>  For a normal functioning install these times will likely be the same and hence nothing is rebuilt (there is no need) so wee_database returns to the command prompt very quickly.
>
>
> How'bout a me-hacked-brutally install ??? :-) 

Me-hacked-brutally or not, wee_database applies the same logic. I am guessing that since there was no advice that any days were rebuilt the timestamp of the last daily summary update matched that of the latest record in the archive.

>  On the other hand, the other extreme is if you drop the daily summaries first, all daily summaries are rebuilt from first to last archive record. Specific dates or date ranges are only rebuilt if a —date or —from and —to are provided. The console output should reflect what was done.
>
> I didn't drop the tables, but I did try deleting all the records from the archive_day_myfieldname table.
>

Deleting records from the daily summaries has no affect on wee_database, well other than giving the user an expectation that one thing will happen when in fact it will not. This is one of the reasons why handling the daily summaries is best done through wee_database rather than direct manipulation.

>
> With no options other than --binding and --rebuild-daily the console output nothing after I hit 'y' to proceed.
>
>
> When I added the --from and --to values and dates that covered the whole range of archive table dates, it 'did' provide nice input and did the right thing.
>

As I would expect.

> Is there anything making you suspect the current day is being rebuilt other than a quick return to the command prompt. A quick return does not necessarily mean anything was rebuilt.
>
> Yes - I deleted all records from the archive_day_<fieldname> table and ran wee_database --rebuild-daily --binding='mybindinghere' and it created one record in the archive_day_<fieldname> table, just the day summary for today.   It didn't do the previous days that the archive table were for.
>
> So the behavior seemed to require specifying a date or date-range for it to work (and display to the console) as expected.

No, it just requires that you don’t separately manipulate the daily summary tables :) I have not seen anything that wee_database has done that is not expected behaviour.

Gary

[vince]

On Tuesday, July 2, 2019 at 4:38:04 PM UTC-7, gjr80 wrote:

I am guessing that since there was no advice that any days were rebuilt the timestamp of the last daily summary update matched that of the latest record in the archive.

That is a good guess.   It's also kinda undocumented previously, perhaps.

More details.

I was using the purpleair extension which works fine.   I wanted to add the AQI calculation to that extension, so I did so, saving that in a 'new' element in the database.  I added the field to the archive table manually via a sqlite3 command, and similarly created the archive_day_aqi2_5 table manually via sqlite3 (as I could find no weewx option for doing so, the existing docs talk about extending the primary db, but do not mention the same thing on a secondary db).

I then started weewx and my (modified) extension and it worked fine, populating the (new) field in the archive records as time moved forward, and weewx started populating the (new) archive_day_aqi2_5 table from that point forward.

The problem was catching the old months of archive records up with calculated AQI data, and catching up the resulting day summaries for that field.

Cooking up a python script to do sqlite 'update' on the aqi_2_5 element in the (modified) archive schema was straightforward.   At that point I had an updated set of archive records, but no matching day summaries for the new element in the records.

So.... when I ran --rebuild-daily with no options, it is true that the current day was correct, as weewx had generated the summary record for the 'current' day.  

Forcing the 'old' days to regenerate the summary tables is where I got hung up.  I was not aware that there's a time check in there for the most-recent data, nor that wee_database silently returns without any user feedback.  That's the confusion factor I ran into.

FWIW - my aqi page from the modified purpleair extension is at https://www.skahan.net/weewx/purpleair.html and it all looks good.

I also sent a PR to Ken Baker in case he wants to add AQI to his extension.  If anybody else wants a copy, email me.

Thanks.