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 <https://github.com/weewx/weewx/commit/2e7f146eff654ed64a51ed854f71ba27e0b204cb> On Fri, Mar 26, 2021 at 8:12 AM Paul R Anderson <[email protected]> wrote: > 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 > > On Thu, Mar 25, 2021 at 9:12 PM Tom Keffer <[email protected]> wrote: > >> 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. >> >> On Thu, Mar 25, 2021 at 1:26 PM vince <[email protected]> wrote: >> >>> 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. >>> >>> On Thursday, March 25, 2021 at 12:17:15 PM UTC-7 vince wrote: >>> >>>> 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... >>>> >>>> >>>> On Wednesday, March 24, 2021 at 2:10:23 PM UTC-7 Tom Keffer wrote: >>>> >>>>> 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 >>>>> <https://github.com/weewx/weewx/wiki/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 >>>>> <http://weewx.com/downloads/development_versions/> for development >>>>> releases. >>>>> >>>>> Feedback is very welcome!! >>>>> >>>>> -tk >>>>> >>>>> -- >>> 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 [email protected]. >>> To view this discussion on the web visit >>> https://groups.google.com/d/msgid/weewx-user/79e7ef19-94af-4c3b-8983-7eff8889ce5cn%40googlegroups.com >>> <https://groups.google.com/d/msgid/weewx-user/79e7ef19-94af-4c3b-8983-7eff8889ce5cn%40googlegroups.com?utm_medium=email&utm_source=footer> >>> . >>> >> -- >> 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 [email protected]. >> To view this discussion on the web visit >> https://groups.google.com/d/msgid/weewx-user/CAPq0zEBvM-n6p4vsf2wS%2B0CfL96aZM0aQ8HaD51XT83iTyyfCw%40mail.gmail.com >> <https://groups.google.com/d/msgid/weewx-user/CAPq0zEBvM-n6p4vsf2wS%2B0CfL96aZM0aQ8HaD51XT83iTyyfCw%40mail.gmail.com?utm_medium=email&utm_source=footer> >> . >> > -- > 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 [email protected]. > To view this discussion on the web visit > https://groups.google.com/d/msgid/weewx-user/CAOAVAecAkLiXc6ht8h64gXYiY4J2gHcGzvXWXXA1Uc8MgwWcqA%40mail.gmail.com > <https://groups.google.com/d/msgid/weewx-user/CAOAVAecAkLiXc6ht8h64gXYiY4J2gHcGzvXWXXA1Uc8MgwWcqA%40mail.gmail.com?utm_medium=email&utm_source=footer> > . > -- 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 [email protected]. To view this discussion on the web visit https://groups.google.com/d/msgid/weewx-user/CAPq0zEBPxePHzeNte7Bxjo%2B%2BKQGYvjrdw%2B6pJYoqPP6sMHr42w%40mail.gmail.com.
