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
On Sat, Mar 27, 2021 at 9:13 AM Tom Keffer <tkef...@gmail.com> wrote:
> 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 <p...@pauland.net> 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 <tkef...@gmail.com> 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 <vinceska...@gmail.com> 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 weewx-user+unsubscr...@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
>>>> <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 weewx-user+unsubscr...@googlegroups.com.
>>> 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 weewx-user+unsubscr...@googlegroups.com.
>> 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 weewx-user+unsubscr...@googlegroups.com.
> To view this discussion on the web visit
> https://groups.google.com/d/msgid/weewx-user/CAPq0zEBPxePHzeNte7Bxjo%2B%2BKQGYvjrdw%2B6pJYoqPP6sMHr42w%40mail.gmail.com
> <https://groups.google.com/d/msgid/weewx-user/CAPq0zEBPxePHzeNte7Bxjo%2B%2BKQGYvjrdw%2B6pJYoqPP6sMHr42w%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 weewx-user+unsubscr...@googlegroups.com.
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.
