Beta of V4.5.0 available

[Tom Keffer]

Several interesting new features. Most notably, you can add, remove, or rename columns in the main database with the utility wee_database. No need to use --reconfigure with all the database shuffling involved! For example, to remove the type soilMoist2:

wee_database --drop-columns=soilMoist2

If you use this feature, remember that this is a beta release. Be sure to do a backup first!!

There is also support for using series in the templates, including generating JSON. For example, a JSON series of the maximum temperature for each day of the month would be:

$month.outTemp.series(aggregate_type='max', aggregate_interval='day').json

This generates something like:

[[1614585600, 1614672000, 58.2], [1614672000, 1614758400, 55.8], [1614758400, 1614844800, 59.6], ... ]

This is a series of 3-way tuples, where each tuple consists of the start time of the day, stop time of the day, and (in this case) maximum temperature for the day.

See the Wiki article Tags for series for more details. Please note: this is still experimental, and subject to change!

Lots of miscellaneous bug fixes. 

Find it in the usual place for development releases. 

Feedback is very welcome!!

-tk

[michael.k...@gmx.at]

This sounds like a great feature. No more signalX!?

[vince]

I know 'drop' is a database term, but I'm wondering if --delete-column might be better wording here generically.

We have drop-daily and rebuild-daily for dealing with summary tables, so I'm thinking maybe the terminology meaning different things in different context might be confusing.  Just a thought.

Similarly, we have lots of cases where folks get bad data in their archive for rain/whatever and need to clean those items up.  Is there a "drop-field" or "rename-field" or the like to help people drop/rebuild just the summary tables for a particular database element ?   Apologies if that already exists...

[vince]

oops - I meant 'drop-field' and 'rebuild-field' there of course in the last paragraph...intent was to handle the case where somebody doesn't want to drop all summary tables, just the one matching the field they cleaned up in the archive table.

[Tom Keffer]

Well, MySQL calls it "DROP COLUMN." SQLite doesn't offer it at all. 

Rather than invent new terminology, I'd like to stick with what's already in use.

--
You received this message because you are subscribed to the Google Groups "weewx-user" group.
To unsubscribe from this group and stop receiving emails from it, send an email to weewx-user+...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/weewx-user/79e7ef19-94af-4c3b-8983-7eff8889ce5cn%40googlegroups.com.

[Paul R Anderson]

Column naming curiosity

Tried the add, and rename columns feature of the wee_database utility. Both seemed to work fine, however I noticed a peculiarity in the column names that were renamed, or created .
Normally a sqlite3 query of .schema archive shows ALL column names surrounded by what seems to be the U+0060 GRAVE ACCENT Character.
However a sqlite3 query after --rename-column shows the added column surrounded by double quotes.
Also a sqlite3 query after --add-column shows a naked column name not surrounded at all?
Please note that what I perceive as a column naming peculiarity, seems to have no noticeable effect on the database, it functions normally.
My wild speculation is that there's an inconsistency between handing of string to unicode in the table create and new add column rename column function.
Spied this in /weedb/sqlite.py
 # Extract the table name. Sqlite returns unicode, so always
 # convert to a regular string:  

I am sure this is hard to follow, so here is an outline of my test methodology hopefully it helps,and doesn't make it more confusing!

Let WeeWx create new database with a few fields using wview_small.py reduced to these fields:

table = [('dateTime',             'INTEGER NOT NULL UNIQUE PRIMARY KEY'),
         ('usUnits',              'INTEGER NOT NULL'),
         ('interval',             'INTEGER NOT NULL'),
         ('altimeter',            'REAL'),
         ('barometer',            'REAL'),
         ('dewpoint',             'REAL'),
         ('ET',                   'REAL'),
         ('heatindex',            'REAL'),
         ]

sqlite3 query of .schema archive after database creation

sqlite> .schema archive
CREATE TABLE archive (`dateTime` INTEGER NOT NULL UNIQUE PRIMARY KEY, `usUnits` INTEGER NOT NULL, `interval` INTEGER NOT NULL, `altimeter` REAL, `barometer` REAL, `dewpoint` REAL, `ET` REAL, `heatindex` REAL);

*** Note what seems to be the U+0060 GRAVE ACCENT Character surrounding the column names.

Rename heatindex to renamedheatindex
wee_database --rename-column=heatindex --to-name=renamedheatindex
Added addedcolumn
wee_database --add-column=addedcolumn

sqlite3 query of .schema archive after the Rename and add column operation

sqlite> .schema archive
CREATE TABLE archive (`dateTime` INTEGER NOT NULL UNIQUE PRIMARY KEY, `usUnits` INTEGER NOT NULL, `interval` INTEGER NOT NULL, `altimeter` REAL, `barometer` REAL, `dewpoint` REAL, `ET` REAL, "renamedheatindex" REAL, addedcolumn REAL);

*** Note the name of the renamedheatindex column is surrounded by double quotes, and the added column addedcolumn isn't surrounded by anything.

Thanks!

Paul

To view this discussion on the web visit https://groups.google.com/d/msgid/weewx-user/CAPq0zEBvM-n6p4vsf2wS%2B0CfL96aZM0aQ8HaD51XT83iTyyfCw%40mail.gmail.com.

[Tom Keffer]

When the database is created, the column names are put in backquotes. This is because, under MySQL, the name "interval" is a reserved keyword, so it must be "escaped" with backquotes. 

However, when adding a column, the name is not put in backquotes.

I've changed the "add column" code to include the backquotes. Hopefully, the results will be consistent now!

commit 2e7f146

To view this discussion on the web visit https://groups.google.com/d/msgid/weewx-user/CAOAVAecAkLiXc6ht8h64gXYiY4J2gHcGzvXWXXA1Uc8MgwWcqA%40mail.gmail.com.

[Paul R Anderson]

Hi Tom

Verified that when adding a column it now gets created with the backquotes.

The 2nd issue that I mentioned was that when renaming a column it gets renamed to a  name enclosed in double quotes.

So when running my example of adding  a column named addedcolumn,  and renaming column heatindex to renamedheatindex we get:

sqlite> .schema archive
CREATE TABLE archive (`dateTime` INTEGER NOT NULL UNIQUE PRIMARY KEY, `usUnits` INTEGER NOT NULL, `interval` INTEGER NOT NULL, `altimeter` REAL, `barometer` REAL, `dewpoint` REAL, `ET` REAL, "renamedheatindex" REAL, `addedcolumn` REAL);

I tried modifying line 478 manager.py from:

cursor.execute("ALTER TABLE %s RENAME COLUMN %s TO %s"

to:

cursor.execute("ALTER TABLE %s RENAME COLUMN %s TO `%s`"

But that made no difference, wish I had better python skills!

Thanks,

Paul

To view this discussion on the web visit https://groups.google.com/d/msgid/weewx-user/CAPq0zEBPxePHzeNte7Bxjo%2B%2BKQGYvjrdw%2B6pJYoqPP6sMHr42w%40mail.gmail.com.

[Tom Keffer]

Unfortunately, there doesn't seem to be anything that can be done about that. At least, without getting rid of the backquotes for the initial table creation.

echo "CREATE TABLE archive (`foo` REAL); ALTER TABLE archive RENAME foo to `baz`;" |sqlite3 test.sdb

followed by

echo ".schema" | sqlite3 test.sdb

yields

CREATE TABLE archive ("baz" REAL);

The good news is that the pragma "table_info()" does the right thing:

echo "pragma table_info(archive)" | sqlite3 test.sdb

0|baz|REAL|0||0

To view this discussion on the web visit https://groups.google.com/d/msgid/weewx-user/CAOAVAedxMv%2BPSj-C-8xDRF%2BwVUz96_ZpCax0K0wa9XdWLp%3DW8Q%40mail.gmail.com.