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
On Sat, Mar 27, 2021 at 8:38 AM Paul R Anderson <[email protected]> wrote:
> 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 <[email protected]> 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 <[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
>> <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 [email protected].
> 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
> <https://groups.google.com/d/msgid/weewx-user/CAOAVAedxMv%2BPSj-C-8xDRF%2BwVUz96_ZpCax0K0wa9XdWLp%3DW8Q%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/CAPq0zEBTb_QdYJRXSLY49aUW7SYNQK95zeXhsubOMf6dB3Byyw%40mail.gmail.com.
