[Tutor] Proper SQLite cursor handling?

[Cameron Simpson]

On 15Jul2021 21:49, Dennis Lee Bieber <wlfraed at ix.netcom.com> wrote:
>On Fri, 16 Jul 2021 10:05:09 +1000, Cameron Simpson <cs at cskk.id.au>
>declaimed the following:
>>Can it not? From https://sqlite.org/lang_createtable.html under "The
>>DEFAULT clause":
>>
>>    If the default value of a column is an expression in parentheses,
>>    then the expression is evaluated once for each row inserted and the
>>    results used in the new row.
>>
>>You may be better off than you thought.
>
>	When I tried I got some error message about not being able to use a
>"dynamic" expression.
>
>	Didn't try further. Could have just been a syntax error on my part but
>there is this: https://www.sqlite.org/lang_createtable.html
>"""
>3.2. The DEFAULT clause
>
>...  An explicit DEFAULT clause may specify that the default value is NULL,
>a string constant, a blob constant, a signed-number, or any constant
>expression enclosed in parentheses. ... For the purposes of the DEFAULT
>clause, an expression is considered constant if it contains no sub-queries,
>column or table references, bound parameters, or string literals enclosed
>in double-quotes instead of single-quotes.
>"""
>
>	The message I'd received implies the clause I supplied was not
>considered "constant".

That seemed in conflict with the line I quoted, so I dug a bit more.

This works:

    create table z ( col1 INTEGER DEFAULT (strftime('%s', 'now', 'unixepoch')));

This is not constant:

    sqlite> create table z ( col1 INTEGER DEFAULT (strftime("%s", 'now', 'unixepoch')+""));
    Error: default value of column [col1] is not constant

Earlier in the DEFAULT specification it says:

    For the purposes of the DEFAULT clause, an expression is considered
    constant if it contains no sub-queries, column or table references,
    bound parameters, or string literals enclosed in double-quotes
    instead of single-quotes.

This implies that double quotes have a different meaning. I think 
"constant' means "not requiring a subselect of some kind". Elsewhere in 
the syntax documentation was:

    A string constant is formed by enclosing the string in single quotes (').

Double quotes enclose an identifier such as a column name, so the double 
quoted expression is a column access, not a constant.

Cheers,
Cameron Simpson <cs at cskk.id.au>