Re: [sqlite] Is there something like PATH to search for extension libraries?
OK. This was not clear to me by reading https://www.sqlite.org/loadext.html. Maybe it should be included by the maintainer of that page. On 2/1/20, Keith Medcalf wrote: > > On Friday, 31 January, 2020 21:15, Peng Yu wrote: > >>I have to specify either a full path (either relative or absolution) >>to use .load. But it would be more convenient if there is something >>like PATH (may be named as something like SQLITE3_LIB_PATH) to search >>for library files. Is it available in sqlite3? Thanks. > >>https://www.sqlite.org/loadext.html > > This is an Operating System matter, not an SQLite3 issue. > > On Windows loadext (.load) uses the standard search path. I would imagine > that Linux uses whatever the standard .so file search path is. > > See http://man7.org/linux/man-pages/man8/ld.so.8.html > and https://tldp.org/HOWTO/Program-Library-HOWTO/shared-libraries.html > > Basically put the library in a standard location and use ldconfig else try > setting the LD_LIBRARY_PATH > > -- > The fact that there's a Highway to Hell but only a Stairway to Heaven says a > lot about anticipated traffic volume. > > > > > ___ > sqlite-users mailing list > sqlite-users@mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > -- Regards, Peng ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Is there something like PATH to search for extension libraries?
On Friday, 31 January, 2020 21:15, Peng Yu wrote: >I have to specify either a full path (either relative or absolution) >to use .load. But it would be more convenient if there is something >like PATH (may be named as something like SQLITE3_LIB_PATH) to search >for library files. Is it available in sqlite3? Thanks. >https://www.sqlite.org/loadext.html This is an Operating System matter, not an SQLite3 issue. On Windows loadext (.load) uses the standard search path. I would imagine that Linux uses whatever the standard .so file search path is. See http://man7.org/linux/man-pages/man8/ld.so.8.html and https://tldp.org/HOWTO/Program-Library-HOWTO/shared-libraries.html Basically put the library in a standard location and use ldconfig else try setting the LD_LIBRARY_PATH -- The fact that there's a Highway to Hell but only a Stairway to Heaven says a lot about anticipated traffic volume. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Is there something like PATH to search for extension libraries?
Hi, I have to specify either a full path (either relative or absolution) to use .load. But it would be more convenient if there is something like PATH (may be named as something like SQLITE3_LIB_PATH) to search for library files. Is it available in sqlite3? Thanks. https://www.sqlite.org/loadext.html -- Regards, Peng ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] log() in sqlite3
On Friday, 31 January, 2020 17:59, Peng Yu wrote:
>How to use extension-functions.c? It means that I have to compile it?
Yes. Either as a loadable extension or as core builtin functions extending the
amalgamation.
>How to use it with python?
db.load_extension()
for each connection db into which you want to load the extension.
>For python, create_function should be a better solution? Thanks.
Mayhaps yes, mayhaps no.
Depends on your definition of "better". If you mean "simpler" then the answer
is yes. If you mean many orders of magnitude slower than the C version, then
the answer is also yes.
>>> import sqlite3
>>> db=sqlite3.connect(':memory:')
>>> def log(*arg):
... from math import log
... return log(*arg)
...
>>> db.create_function('log', -1, log)
>>> import math
>>> db.execute('select log(1000)').fetchone()
(6.907755278982137,)
>>> math.log(1000)
6.907755278982137
>>> db.execute('select log(1000,10)').fetchone()
(2.9996,)
>>> math.log(1000,10)
2.9996
>>> db.execute('select log(1000,2)').fetchone()
(9.965784284662087,)
>>> math.log(1000,2)
9.965784284662087
sqlite3 also does not let you set a function as deterministic, while APSW does,
functions defined in python using the sqlite3 wrapper are somewhat limited in
where you can use them. APSW also lets you write virtual tables and vfs's in
python, should you wish. I don't think sqlite3 can do that.
--
The fact that there's a Highway to Hell but only a Stairway to Heaven says a
lot about anticipated traffic volume.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Is there a way to yield an error when opening a nonexistent dbfile?
You could use the second method (opening the file by handle) if you do not want
your code to be portable.
Yes, APSW is far superior to sqlite3. It does not have any "magic" and wraps
SQLite3 into Python so that it works like SQLite3 works, so the interface works
as documented for the equivalent "C" code on the sqlite.org website (or on the
APSW documentation website at https://rogerbinns.github.io/apsw/ and the
documentation is MUCH more complete than the paltry stuff available for the
pysqlite2/sqlite3 wrapper).
The author of APSW also happens to be on this mailing list, and APSW is
generally updated shortly after a new version of SQLite3 is released (or you
can compile it yourself if you wish). If you find a bug it is also highly
likely that it will be fixed (unlike the pysqlite2/sqlite3 wrapper in Python
which has not been updated in years and is 20-odd versions behind its parent
project code). APSW can be found here: https://github.com/rogerbinns/apsw
With APSW you simply pass the open flags you want to use on the Connection
constructor just like you would do in C.
About the only things that APSW does not have that the sqlite3 does is the Row
object and the data adapters and converters.
--
The fact that there's a Highway to Hell but only a Stairway to Heaven says a
lot about anticipated traffic volume.
>-Original Message-
>From: sqlite-users On
>Behalf Of Peng Yu
>Sent: Friday, 31 January, 2020 17:49
>To: SQLite mailing list
>Subject: Re: [sqlite] Is there a way to yield an error when opening a
>nonexistent dbfile?
>
>What is the recommended way to do so in python?
>
>I see the following two ways.
>
>```
>db = sqlite3.connect('file:/path/to/database?mode=ro', uri=True)
>
>fd = os.open(filename, os.O_RDONLY)
>c = sqlite3.connect('/dev/fd/%d' % fd)
>os.close(fd)
>```
>
>https://stackoverflow.com/questions/10205744/opening-sqlite3-database-
>from-python-in-read-only-mode
>
>But can the first have problems when the filename is actually
>something like 'file:database?mode=ro'?
>
>For my applications, I only unix like platform. So the second way may be
>better?
>
>Any other better solutiosn with the python sqlite3 library?
>
>The stackoverflow link also mentions APSW. It seems that it is quite
>updated. Is this a good replacement for the python sqlite3 module in
>general?
>
>Thanks.
>
>> $ sqlite3 -readonly testx.db
>> SQLite version 3.31.1 2020-01-27 19:55:54
>> Enter ".help" for usage hints.
>> sqlite> .tables
>> Error: unable to open database "testx.db": unable to open database file
>
>--
>Regards,
>Peng
>___
>sqlite-users mailing list
>sqlite-users@mailinglists.sqlite.org
>http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Check constrain execution timing change?
On 1 Feb 2020, at 1:28am, Simon Slavin wrote: > How do you feel about that ? Sorry, I didn't read the rest of the list first. I see it's already answered. Thanks. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Check constrain execution timing change?
On 31 Jan 2020, at 10:51pm, Richard Hipp wrote:
> CREATE TABLE t1(x INTEGER CHECK(typeof(x)=='text'));
> INSERT INTO t1 VALUES('123');
> PRAGMA integrity_check;
Short and sweet. That's what I was talking about.
How do you feel about that ? Should a programmer be able to create a failure
in integrity_check that easily ? It's just two legit lines of SQL. My gut
feeling is that it points to something wrong with SQLite. But there are lot of
people here who know more about it than me.
Would be interesting to see an example which depends only on features of SQL,
avoiding typeof(). Might make it more obvious whether it's a bug.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] log() in sqlite3
How to use extension-functions.c? It means that I have to compile it? How to use it with python? For python, create_function should be a better solution? Thanks. On 1/31/20, Chris Brody wrote: > The log function does seem to be supported by extension-functions.c which > is available from here: https://www.sqlite.org/contrib > > On Fri, Jan 31, 2020 at 1:30 PM David Raymond > wrote: > >> The core functions of the library are fairly unlikely to get expanded upon >> at this point, so it's up to the application using the SQLite library to >> expand on them. >> >> You can load an extension which has those functions. Or the C API gives >> you means to add your own functions. >> >> For Python, the built in sqlite3 module has the create_function() method >> for a connection which will register a function. >> >> https://docs.python.org/3.8/library/sqlite3.html#sqlite3.Connection.create_function >> >> Once you register the function you can use it right in your sql text like >> any other function, >> "update tbl set field2 = log(field1);" >> >> And you don't need to do the more tedious: >> a) Get original data with statement 1 >> b) Do calculations in the outside program >> c) Use the result in statement 2 >> >> >> -Original Message- >> From: sqlite-users On >> Behalf Of Peng Yu >> Sent: Friday, January 31, 2020 12:42 PM >> To: SQLite mailing list >> Subject: [sqlite] log() in sqlite3 >> >> Hi, >> >> I see that many math functions (like log()) are not in sqlite3. It >> seems that SQL standard doesn't have them. >> >> https://www.sqlite.org/lang_corefunc.html >> >> But since sqlite3 contains non-standard functions anyway. Would it be >> considered to add those functions? >> >> Given the current version of sqlite3, is the only choice of computing >> log() to get the data into another language (e.g., python) and compute >> the log over there? Thanks. >> >> -- >> Regards, >> Peng >> ___ >> sqlite-users mailing list >> sqlite-users@mailinglists.sqlite.org >> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users >> ___ >> sqlite-users mailing list >> sqlite-users@mailinglists.sqlite.org >> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users >> > ___ > sqlite-users mailing list > sqlite-users@mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > -- Regards, Peng ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Is there a way to yield an error when opening a nonexistent dbfile?
What is the recommended way to do so in python?
I see the following two ways.
```
db = sqlite3.connect('file:/path/to/database?mode=ro', uri=True)
fd = os.open(filename, os.O_RDONLY)
c = sqlite3.connect('/dev/fd/%d' % fd)
os.close(fd)
```
https://stackoverflow.com/questions/10205744/opening-sqlite3-database-from-python-in-read-only-mode
But can the first have problems when the filename is actually
something like 'file:database?mode=ro'?
For my applications, I only unix like platform. So the second way may be better?
Any other better solutiosn with the python sqlite3 library?
The stackoverflow link also mentions APSW. It seems that it is quite
updated. Is this a good replacement for the python sqlite3 module in
general?
Thanks.
> $ sqlite3 -readonly testx.db
> SQLite version 3.31.1 2020-01-27 19:55:54
> Enter ".help" for usage hints.
> sqlite> .tables
> Error: unable to open database "testx.db": unable to open database file
--
Regards,
Peng
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Check constrain execution timing change? (Now a bug)
On 1/31/20, Keith Medcalf wrote: > > That would elevate this to the status of a bug since it should be impossible > to do this. > It is also not something that is fixable, so the solution will likely be to simply document it. -- D. Richard Hipp d...@sqlite.org ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Check constrain execution timing change? (Now a bug)
On Friday, 31 January, 2020 14:39, Simon Slavin wrote: >On 31 Jan 2020, at 9:27pm, Keith Medcalf wrote: >> You are however correct that this is an "application consistency" >problem more than an SQLite problem and it is a lot of change for little >actual benefit. >How about this ? >A program (possibly part of the SQLite precompiled suite, possibly from a >third party) goes through any SQLite database as an integrity checker. >One of its jobs is to check that column constraints are not violated. >This cannot possible be wrong. A SQLite database with violated CHECK >clauses is, by definition, corrupt. pragma integrity_check and pragma quick_check already do this. >However, because the checks are performed on the values input, not the >values stored, some data in the database does violate a CHECK constraint. >Can this happen, given the behaviour Keith identified ? If so, I would >say that something is wrong. Yes. It is possible to "craft" a table and a check constraint such that the INSERT passes the constraint yet the integrity check fails: sqlite> create table x(x text not null check (typeof(x) == 'integer')); sqlite> insert into x values (1); sqlite> select x, typeof(x) from x; 1|text sqlite> pragma integrity_check; CHECK constraint failed in x That would elevate this to the status of a bug since it should be impossible to do this. -- The fact that there's a Highway to Hell but only a Stairway to Heaven says a lot about anticipated traffic volume. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Check constrain execution timing change?
CREATE TABLE t1(x INTEGER CHECK(typeof(x)=='text'));
INSERT INTO t1 VALUES('123');
PRAGMA integrity_check;
On 1/31/20, Graham Holden wrote:
> Friday, January 31, 2020, 9:39:07 PM, Simon Slavin
> wrote:
>
>> On 31 Jan 2020, at 9:27pm, Keith Medcalf wrote:
>
>>> You are however correct that this is an "application consistency"
>>> problem more than an SQLite problem and it is a lot of change for
>>> little actual benefit.
>
>> How about this ?
>
>> A program (possibly part of the SQLite precompiled suite, possibly
>> from a third party) goes through any SQLite database as an integrity
>> checker. One of its jobs is to check that column constraints
>> are not violated. This cannot possible be wrong. A SQLite database
>> with violated CHECK clauses is, by definition, corrupt.
>
>> However, because the checks are performed on the values input, not
>> the values stored, some data in the database does violate a CHECK
>> constraint.
>
>> Can this happen, given the behaviour Keith identified ? If so, I
>> would say that something is wrong.
>
> Probably not, at least not with the example he used.
>
> In Keith's example (assuming I understand it correctly), you are
> stopped from inserting/updating something that -- if you did the
> insertion/update WITHOUT the checks -- would be stored in such a
> way that it would later pass "pragma integrity_check" if the checks
> were in place.
>
> So, inserting '1' (a string) into a field with integer affinity, but
> no constraints would be allowed, and result in 1 (an integer) being
> stored.
>
> If that column were to magically gain a "check (typeof(x) == 'integer')"
> constraint, it would pass "pragma integrity_check" (because by now it
> contains an integer, not the string that was originally inserted).
>
> If you now repeated the original insert (of the string '1') with the
> check constraint in place it will now REJECT the insertion, because
> the type-of-the-thing-being-inserted doesn't meet the constraint (it's
> a string).
>
> So it doesn't allow you to create an inconsistent database (as defined
> by check constraints), but does stop some ways of inserting/modifying
> data that would have created valid data if the checks weren't there.
>
> Graham
>
>
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
--
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Check constrain execution timing change?
Friday, January 31, 2020, 9:39:07 PM, Simon Slavin wrote: > On 31 Jan 2020, at 9:27pm, Keith Medcalf wrote: >> You are however correct that this is an "application consistency" >> problem more than an SQLite problem and it is a lot of change for >> little actual benefit. > How about this ? > A program (possibly part of the SQLite precompiled suite, possibly > from a third party) goes through any SQLite database as an integrity > checker. One of its jobs is to check that column constraints > are not violated. This cannot possible be wrong. A SQLite database > with violated CHECK clauses is, by definition, corrupt. > However, because the checks are performed on the values input, not > the values stored, some data in the database does violate a CHECK > constraint. > Can this happen, given the behaviour Keith identified ? If so, I > would say that something is wrong. Probably not, at least not with the example he used. In Keith's example (assuming I understand it correctly), you are stopped from inserting/updating something that -- if you did the insertion/update WITHOUT the checks -- would be stored in such a way that it would later pass "pragma integrity_check" if the checks were in place. So, inserting '1' (a string) into a field with integer affinity, but no constraints would be allowed, and result in 1 (an integer) being stored. If that column were to magically gain a "check (typeof(x) == 'integer')" constraint, it would pass "pragma integrity_check" (because by now it contains an integer, not the string that was originally inserted). If you now repeated the original insert (of the string '1') with the check constraint in place it will now REJECT the insertion, because the type-of-the-thing-being-inserted doesn't meet the constraint (it's a string). So it doesn't allow you to create an inconsistent database (as defined by check constraints), but does stop some ways of inserting/modifying data that would have created valid data if the checks weren't there. Graham ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Check constrain execution timing change?
On 31 Jan 2020, at 9:27pm, Keith Medcalf wrote: > You are however correct that this is an "application consistency" problem > more than an SQLite problem and it is a lot of change for little actual > benefit. How about this ? A program (possibly part of the SQLite precompiled suite, possibly from a third party) goes through any SQLite database as an integrity checker. One of its jobs is to check that column constraints are not violated. This cannot possible be wrong. A SQLite database with violated CHECK clauses is, by definition, corrupt. However, because the checks are performed on the values input, not the values stored, some data in the database does violate a CHECK constraint. Can this happen, given the behaviour Keith identified ? If so, I would say that something is wrong. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Check constrain execution timing change?
On Friday, 31 January, 2020 13:58, Richard Hipp wrote:
>On 1/31/20, Keith Medcalf wrote:
>> The check should occur AFTER defaults and
>> column affinity is applied before the data record is stored
>Why do you think this? Is it documented somewhere? I ask because
>your test case gives the same answer (doing the first insert but
>failing the other two) for every version of SQLite I checked from
>trunk going back to 3.1.0 (2005-01-21). Hence, for backwards
>compatibility, even it is documented to do something differently, I
>should probably change the documentation rather than the behavior.
You are absolutely correct in that this is the way SQLite has worked forever,
and is the way it is documented (or not, I am not sure). However, this means
that the result of a CHECK/NOT NULL contstraint differs between an INSERT and
when run on existing data (via pragma integrity_check). This means that, for
example, the construct
CHECK (typeof(x) in ('integer', 'real'))
enforces different table invariants between UPDATE/INSERT and when the data is
already in the table since in the case of INSERT/UPDATE it checks the type of
the value input to the UPDATE/INSERT rather than the value actually stored and
prohibits the INSERT/UPDATE of values that would pass the table checks later.
You are however correct that this is an "application consistency" problem more
than an SQLite problem and it is a lot of change for little actual benefit.
--
The fact that there's a Highway to Hell but only a Stairway to Heaven says a
lot about anticipated traffic volume.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Check constrain execution timing change?
>collect input data row >apply column affinity >fire before triggers >apply defaults, generated always, rowid etc. >apply column affinity to above columns >run constraints >store actul row >fire after triggers Actually, with generated columns it is a bit more complicated. I think: collect input data row run generated column logic including not allowing specification of values for generated columns apply column affinity fire before triggers apply defaults, rowid etc. apply column affinity to defaults, rowid, etc. changed above run constraints store actul row fire after triggers -- The fact that there's a Highway to Hell but only a Stairway to Heaven says a lot about anticipated traffic volume. >collect input data row >apply column affinity >fire before triggers >apply defaults, generated always, rowid etc. >apply column affinity to above columns >run constraints >store actul row >fire after triggers ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Check constrain execution timing change?
On 1/31/20, Keith Medcalf wrote: > > The check should occur AFTER defaults and > column affinity is applied before the data record is stored > Why do you think this? Is it documented somewhere? I ask because your test case gives the same answer (doing the first insert but failing the other two) for every version of SQLite I checked from trunk going back to 3.1.0 (2005-01-21). Hence, for backwards compatibility, even it is documented to do something differently, I should probably change the documentation rather than the behavior. -- D. Richard Hipp d...@sqlite.org ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Check constrain execution timing change?
I believe that when inserting a row into a table the CHECK constraints (which
includes any NOT NULL constraint) are checked at the wrong time, or at least
with the wrong data. The check should occur AFTER defaults and column affinity
is applied before the data record is stored, meaning that the constraints
should apply to the row as actually stored. Consider:
create table x (x integer default ('1') check (typeof(x) == 'integer'));
insert into x values (1);
insert into x values ('1');
insert into x default values;
The last two statements raise constraint errors, even though the values that
end up in the database would in fact pass the constraint:
create table x (x integer default ('1'));
insert into x values (1);
insert into x values ('1');
insert into x default values;
select x, typeof(x) from x;
1|integer
1|integer
1|integer
Similarly for the NOT NULL column constraint. If should apply to the data
actually stored, not the contents of the 'insert' the value may be modified
before it is actually inserted (the working of the PRIMARY KEY on a ROWID
table, or a default clause, for example) may modify the value before it is
stored.
Presently, the NOT NULL column constraint is to the input data, and not to the
row actually stored:
sqlite> create table x(x integer not null default (1));
sqlite> insert into x values (NULL);
Error: NOT NULL constraint failed: x.x
which also raises a NOT NULL constraint error even though the value stored will
not be NULL and therefore passes the constraint.
I do not know exactly where before triggers fire, but they should probably fire
directly on the input data after affinity is applied and before the check
contraints run. You could then use before triggers to limit or require
specifying NULL inputs on an insert even if those NULL values would be changed
to a default or computed value afterwards.
ie, the processing for inserting a record should be:
collect input data row
apply column affinity
fire before triggers
apply defaults, generated always, rowid etc.
apply column affinity to above columns
run constraints
store actul row
fire after triggers
I don't know if this would constitute a breaking change, but I don't think so
...
--
The fact that there's a Highway to Hell but only a Stairway to Heaven says a
lot about anticipated traffic volume.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Generated columns and COLLATE in the AS parens
On 1/31/20, Markus Winand wrote: > > - Expressions also have a declared type. More elaborate: the result value of > an expression has a type that is statically determined (again the “declared > type”). SQLite is a generalization of "Standard SQL" that allows expressions that can have an arbitrary type. Some (most?) expressions in SQLite will always return the same type, but it is not hard to construct an SQLite expression that returns different types based on its inputs. You can also create application-defined functions including table-valued functions that return different types based on their inputs. Internally, SQLite makes no attempt to track the datatype of an expression. Such tracking could be added, but doing so would just make the library bigger and slower without serving any useful purpose. > > - If a generated column doesn’t have an explicit data type mentioned, the > the data type of the result of the expression is used. Having no explicit data type is a kind of explicit data type in SQLite, since any SQLite column (generated or otherwise) can omit the data type specification and the data type affinity becomes the equivalent of "ANY". From that point of view, SQLite is like PG in that it requires an explicit data type on generated columns, though SQLite extends PG by allowing that explicit data type to be the unnamed "ANY" data type. And since the data type is always implicit in the column definition, there is never an occasion to inherit the data type from the AS expression. Furthermore, SQLite expressions do not have data types, so even if a generated column were said to omit the data type, there would be no way of capturing it from the AS expression. -- D. Richard Hipp d...@sqlite.org ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Generated columns and COLLATE in the AS parens
If the standard requires static typing then anything it says about static types
does not apply to SQLite3 because SQLite3 uses dynamic strong typing and does
not use static fixed typing.
That means that the "type" assigned in a column is merely a "suggestion" on how
to store the data for this column, and that data will be converted to this type
if possible (this is called the column affinity). A particular "value" (stored
at the intersection of a column and a row) may be of any "type",
notwithstanding what the column declaration of the table has to say about it.
The supported "types" are:
NULL - a NULL value
INTEGER a 64-bit signed integer
REAL - a 64-bit IEEE-754 double precision float
TEXT - a sequence of bytes in the encoding of the underlying database (set by
pragma encoding)
BLOB - a sequence of bytes with no meaning
A "value" has a concrete "type" associated with it and nothing else.
A "column" has a preferred storage "type" (called the affinity) and a bunch of
constraints, plus a collation specifying the collation sequence to apply when
the "value" contained in some particular row of that column contains TEXT.
For example:
sqlite> create table x(x integer default ('1'));
sqlite> insert into x default values;
sqlite> select typeof(x), x from x;
integer|1
The column "x" in the table "x" has integer affinity (would prefer data to be
stored as integers if it can be).
The default value is a text string '1'.
When you execute the insert, x takes the default value '1'.
When this value is stored the column affinity is applied and the value that is
actually stored is the integer 1.
Similarly,
sqlite> drop table x;
sqlite> create table x(x integer default ('test'));
sqlite> insert into x default values;
sqlite> select typeof(x), x from x;
text|test
The application of affinity integer for the column cannot convert the value
'test' into an integer, so the value is stored as text.
--
The fact that there's a Highway to Hell but only a Stairway to Heaven says a
lot about anticipated traffic volume.
>-Original Message-
>From: sqlite-users On
>Behalf Of Markus Winand
>Sent: Friday, 31 January, 2020 03:29
>To: SQLite mailing list
>Subject: Re: [sqlite] Generated columns and COLLATE in the AS parens
>
>Let me first explain how collations work in standard SQL. I think my
>answers below make more sense then.
>
>In the SQL standard….
>
>- character string **types** have properties such as fix-length vs.
>variable length, the length (limit), character set and also the
>collation.
>
>- columns have types, which include the collation if it is a character
>string type.
> However, it is not the column that has a collation. The column has a
>type, which might have a collation.
>
>- values have a static type (the so-called “declared type”). Static means
>it is determined at “compile time” from the syntax and the data
>dictionary.
>
>- Expressions also have a declared type. More elaborate: the result value
>of an expression has a type that is statically determined (again the
>“declared type”).
>
>- expressions of which the declared type is a character string type, will
>also have all the properties of character string types (including the
>collation).
>
>- When character strings are compared, the effective collation is
>determined from the declared types of the operands.
> The SQL standard defines rules how to do that (e.g. in 9075-2 9.15
>"Collation determination”)
> The most important rule is the "collation derivation” order: explicit,
>implicit, none. That’s the rule that says
> the COLLATE clause on expressions (“explicit” derivation) is stronger
>than the COLLATE clause following type names (“implicit” derivation).
>
>But note that the last statement is not the reason the collation of the
>generation expression takes precedence of the data type — IT DOESN’T!
>
>
>As far as I know, this is basically unchanged sind SQL-92. If you do not
>have access to the current release of the standard (of 2016, to which my
>references apply), you can also have a look at SQL-92 here (search for
>"4.2 Character strings”):
>
>https://www.contrib.andrew.cmu.edu/~shadow/sql/sql1992.txt
>
>Generated columns where introduced with SQL:2003 and are thus not in the
>linked document.
>
>
>Putting these mechanics of working with collations in context of
>generated columns:
>
>- Generated columns need a type like any other column.
> If that type happens to be a character string type, the type includes
>the collation as part of the types properties.
>
>- Even though generated columns need a type, they allow skipping the type
>in the definition of the generated column—that’s very unique to generated
>columns.
> E.g. specifying a DEFAULT clause does not lift the requirement to
>explicitly state the type of the column.
>
>- If a generated column doesn’t have an explicit data type mentioned, the
>the data type of the result of the expression is used.
> Whatever data type that is, whatever properties
Re: [sqlite] log() in sqlite3
The log function does seem to be supported by extension-functions.c which is available from here: https://www.sqlite.org/contrib On Fri, Jan 31, 2020 at 1:30 PM David Raymond wrote: > The core functions of the library are fairly unlikely to get expanded upon > at this point, so it's up to the application using the SQLite library to > expand on them. > > You can load an extension which has those functions. Or the C API gives > you means to add your own functions. > > For Python, the built in sqlite3 module has the create_function() method > for a connection which will register a function. > > https://docs.python.org/3.8/library/sqlite3.html#sqlite3.Connection.create_function > > Once you register the function you can use it right in your sql text like > any other function, > "update tbl set field2 = log(field1);" > > And you don't need to do the more tedious: > a) Get original data with statement 1 > b) Do calculations in the outside program > c) Use the result in statement 2 > > > -Original Message- > From: sqlite-users On > Behalf Of Peng Yu > Sent: Friday, January 31, 2020 12:42 PM > To: SQLite mailing list > Subject: [sqlite] log() in sqlite3 > > Hi, > > I see that many math functions (like log()) are not in sqlite3. It > seems that SQL standard doesn't have them. > > https://www.sqlite.org/lang_corefunc.html > > But since sqlite3 contains non-standard functions anyway. Would it be > considered to add those functions? > > Given the current version of sqlite3, is the only choice of computing > log() to get the data into another language (e.g., python) and compute > the log over there? Thanks. > > -- > Regards, > Peng > ___ > sqlite-users mailing list > sqlite-users@mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > ___ > sqlite-users mailing list > sqlite-users@mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Compile error with SQLITE_OMIT_CTE (3.28)
> On Jan 31, 2020, at 9:55 AM, Jens Alfke wrote: > > If I define SQLITE_OMIT_CTE and compile the amalgamation (3.28.0), the build > fails due to two remaining calls to > sqlite3WithAdd() in the parser. This function is neither declared nor > implemented when SQLITE_OMIT_CTE is defined. Never mind — I had somehow overlooked the bold italic "Important Note:" warning in the same section that lists the OMIT flags — Important Note: The SQLITE_OMIT_* options may not work with the amalgamation. SQLITE_OMIT_* compile-time options usually work correctly only when SQLite is built from canonical source files. Sorry for the noise :) —Jens ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] log() in sqlite3
The core functions of the library are fairly unlikely to get expanded upon at this point, so it's up to the application using the SQLite library to expand on them. You can load an extension which has those functions. Or the C API gives you means to add your own functions. For Python, the built in sqlite3 module has the create_function() method for a connection which will register a function. https://docs.python.org/3.8/library/sqlite3.html#sqlite3.Connection.create_function Once you register the function you can use it right in your sql text like any other function, "update tbl set field2 = log(field1);" And you don't need to do the more tedious: a) Get original data with statement 1 b) Do calculations in the outside program c) Use the result in statement 2 -Original Message- From: sqlite-users On Behalf Of Peng Yu Sent: Friday, January 31, 2020 12:42 PM To: SQLite mailing list Subject: [sqlite] log() in sqlite3 Hi, I see that many math functions (like log()) are not in sqlite3. It seems that SQL standard doesn't have them. https://www.sqlite.org/lang_corefunc.html But since sqlite3 contains non-standard functions anyway. Would it be considered to add those functions? Given the current version of sqlite3, is the only choice of computing log() to get the data into another language (e.g., python) and compute the log over there? Thanks. -- Regards, Peng ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Compile error with SQLITE_OMIT_CTE (3.28)
If I define SQLITE_OMIT_CTE and compile the amalgamation (3.28.0), the build
fails due to two remaining calls to
sqlite3WithAdd() in the parser. This function is neither declared nor
implemented when SQLITE_OMIT_CTE is defined.
case 286: /* wqlist ::= nm eidlist_opt AS LP select RP */
{
yymsp[-5].minor.yy59 = sqlite3WithAdd(pParse, 0, [-5].minor.yy0,
yymsp[-4].minor.yy434, yymsp[-1].minor.yy457); /*A-overwrites-X*/
}
break;
case 287: /* wqlist ::= wqlist COMMA nm eidlist_opt AS LP select RP */
{
yymsp[-7].minor.yy59 = sqlite3WithAdd(pParse, yymsp[-7].minor.yy59,
[-5].minor.yy0, yymsp[-4].minor.yy434, yymsp[-1].minor.yy457);
}
—Jens
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] log() in sqlite3
Hi, I see that many math functions (like log()) are not in sqlite3. It seems that SQL standard doesn't have them. https://www.sqlite.org/lang_corefunc.html But since sqlite3 contains non-standard functions anyway. Would it be considered to add those functions? Given the current version of sqlite3, is the only choice of computing log() to get the data into another language (e.g., python) and compute the log over there? Thanks. -- Regards, Peng ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Is there a way to yield an error when opening a nonexistent dbfile?
On Fri, 31 Jan 2020 09:02:10 -0600, Peng wrote: > Hi, > > By default the command sqlite3 will just open a dbfile if it does not exist. > > Suppose that I just want to perform read-only operations in a sqlite3 > session, I will not need to create a non-exsitent file. Rather, I want > the sqlite3 to fail when the dbfile does not exist. Is there a way to > achieve this? $ sqlite3 -readonly testx.db SQLite version 3.31.1 2020-01-27 19:55:54 Enter ".help" for usage hints. sqlite> .tables Error: unable to open database "testx.db": unable to open database file -- Regards, Kees Nuyt ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Is there a way to yield an error when opening a nonexistent dbfile?
Hi, By default the command sqlite3 will just open a dbfile if it does not exist. Suppose that I just want to perform read-only operations in a sqlite3 session, I will not need to create a non-exsitent file. Rather, I want the sqlite3 to fail when the dbfile does not exist. Is there a way to achieve this? Thanks. -- Regards, Peng ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Import data from stdin to sqlite3 in python
Hi, I see this post uses Pandas to import data to sqlite3. https://datatofish.com/create-database-python-using-sqlite3/ But I don't want to make my code depend on Pandas. I'd like to use something like ".import /dev/stdin" to directly import data from stdin in python. Is it possible? Thanks. -- Regards, Peng ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Default isolation_level for sqlite3.connect?
> Is there a complete list that defines what are "the statements that > modify the database"? What is the difference between "the statements > that modify the database" and "Data Modification Language (DML) > statement"? Are they the same? If you dig into their code you can attempt to find what it is actually doing, but the documentation is somewhat ambiguous. (Keith included his findings from their code in one of his responses) > Whether or not they are the same or not, providing a complete list of > such statements for each case should be far less confusing. Fully agreed. You're basically discovering for yourself firsthand why we recommend using autocommit/setting isolation_level = None when opening a connection in Python. It basically comes down to "ambiguity is frustrating and prone to error, so do it yourself explicitly to be clear and safe." > BTW, who is maintaining the python doc? Is it somebody from the > sqlite3 community or the python community? I hope that someone will go > over the document and resolve all the inconsistencies and excessive > "referring to other places references". That's the Python folks, since it's their wrapper. You'll have to dig a little to find whichever is the correct mailing list to raise your concerns on over there. -Original Message- From: sqlite-users On Behalf Of Peng Yu Sent: Friday, January 31, 2020 9:11 AM To: SQLite mailing list Subject: Re: [sqlite] Default isolation_level for sqlite3.connect? The following is still very confusing. https://docs.python.org/3.8/library/sqlite3.html#controlling-transactions """ autocommit mode means that statements that modify the database take effect immediately." ... The Python sqlite3 module by default issues a BEGIN statement implicitly before a Data Modification Language (DML) statement (i.e. INSERT/UPDATE/DELETE/REPLACE). """ Is there a complete list that defines what are "the statements that modify the database"? What is the difference between "the statements that modify the database" and "Data Modification Language (DML) statement"? Are they the same? Whether or not they are the same or not, providing a complete list of such statements for each case should be far less confusing. BTW, who is maintaining the python doc? Is it somebody from the sqlite3 community or the python community? I hope that someone will go over the document and resolve all the inconsistencies and excessive "referring to other places references". > This operates in "auto-commit" mode. When a statement is executed that > is not already inside an EXPLICIT (=user-created) transaction, then it > is wrapped inside an IMPLICIT (=engine-created) transaction. Therefore: > >INSERT INTO TABLE_A ... >BEGIN >INSERT INTO TABLE_B ... >DELETE FROM TABLE_C ... >COMMIT >SELECT ... FROM TABLE_D > > will essentially be turned into: > >BEGIN >INSERT INTO TABLE_A ... >COMMIT >BEGIN >INSERT INTO TABLE_B ... >DELETE FROM TABLE_C ... >COMMIT >BEGIN >SELECT ... FROM TABLE_D >COMMIT > > where the auto-generated BEGINs are the equivalent of BEGIN DEFERRED > (the SQLite engine's default if you just use BEGIN). -- Regards, Peng ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] conditional insert operations
"What is v?"
That's the name you gave your third field in your example
> Suppose the table is this (the first line is just header)
>
> h1,h2,v
/*
Ensure there is an actual explicitly defined unique constraint on h1, h2
*/
create unique index tbl_uidx_h1_h2 on tbl (h1, h2);
/*
Attempt to insert the new data
*/
insert into tbl (h1, h2, v) values ('a', '', 'X')
/*
If you hit a unique/primary key constraint on (h1, h2)...
*/
on conflict (h1, h2)
/*
then instead of inserting the new record, update the existing record that
caused the unique violation,
*/
do update
/*
and update its "v" field to be the value you were trying to insert
*/
set v = excluded.v
/*
But only bother doing this is the new value for "v" is different than the old
one.
i.e. Don't bother going through the motions of updating the record if you're
not actually going to change any values.
(I used "is not" rather than != so it would work with nulls (or at least, that
was my intent))
*/
where v is not excluded.v;
-Original Message-
From: sqlite-users On Behalf Of
Peng Yu
Sent: Thursday, January 30, 2020 8:21 PM
To: SQLite mailing list
Subject: Re: [sqlite] conditional insert operations
h1, h2 pair are unique.
I don't quite understand your example. Could you explain what it does
in plain English so that I can be sure it does what I want? (What is
v?)
On 1/30/20, David Raymond wrote:
> Is the combo of h1, h2 unique? If so you could do an upsert
> (https://www.sqlite.org/lang_UPSERT.html)
>
> create unique index tbl_uidx_h1_h2 on tbl (h1, h2);
>
> insert into tbl values ('a', '', 'X')
> on conflict (h1, h2)
> do update set v = excluded.v
> where v is not excluded.v;
>
>
> -Original Message-
> From: sqlite-users On Behalf
> Of Peng Yu
> Sent: Thursday, January 30, 2020 12:02 PM
> To: SQLite mailing list
> Subject: Re: [sqlite] conditional insert operations
>
> Suppose the table is this (the first line is just header)
>
> h1,h2,v
> a,,Y
> a,C,3
>
> Since v of h1=a and h2="" is Y which is not X, the table should be updated
> to
>
> h1,h2,v
> a,,X
> a,A,1
> a,B,2
> ...
>
>
> Suppose the table is this, as v of h1=a and h2="" is X, the table is
> not changed.
>
> h1,h2,v
> a,,X
> a,C,3
>
> Suppose the table is this.
>
> h1,h2,v
> a,C,3
>
> since there is no row with h1=a and h2="", the table is updated to
>
> h1,h2,v
> a,,X
> a,A,1
> a,B,2
> ...
>
>
> On 1/30/20, David Raymond wrote:
>> I'm not quite following what you're trying to do here. Could you provide
>> a
>> few examples of "here's what used to be in there", "here's what I want to
>> insert", "here's what it should like in the end"
>>
>>
>>
>> -Original Message-
>> From: sqlite-users On
>> Behalf
>> Of Peng Yu
>> Sent: Thursday, January 30, 2020 11:27 AM
>> To: SQLite mailing list
>> Subject: [sqlite] conditional insert operations
>>
>> Hi,
>>
>> Suppose that I have a table with three columns h1, h2, v. I want to
>> delete all rows with h1=a, and insert rows like the following (data
>> shown in TSV format), only if there is not an entry with h1=a and
>> h2="" (empty), it exists but its v is not equal to a value X.
>>
>> a,A,v1
>> a,B,v2
>> ...
>>
>> https://www.sqlite.org/lang_insert.html
>>
>> I am not sure if there is a way to check an entry to know whether new
>> entries can be inserted. Could anybody show me an example if this can
>> be done?
>>
>> --
>> Regards,
>> Peng
>> ___
>> sqlite-users mailing list
>> sqlite-users@mailinglists.sqlite.org
>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>> ___
>> sqlite-users mailing list
>> sqlite-users@mailinglists.sqlite.org
>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>>
>
>
> --
> Regards,
> Peng
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
--
Regards,
Peng
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Default isolation_level for sqlite3.connect?
The following is still very confusing. https://docs.python.org/3.8/library/sqlite3.html#controlling-transactions """ autocommit mode means that statements that modify the database take effect immediately." ... The Python sqlite3 module by default issues a BEGIN statement implicitly before a Data Modification Language (DML) statement (i.e. INSERT/UPDATE/DELETE/REPLACE). """ Is there a complete list that defines what are "the statements that modify the database"? What is the difference between "the statements that modify the database" and "Data Modification Language (DML) statement"? Are they the same? Whether or not they are the same or not, providing a complete list of such statements for each case should be far less confusing. BTW, who is maintaining the python doc? Is it somebody from the sqlite3 community or the python community? I hope that someone will go over the document and resolve all the inconsistencies and excessive "referring to other places references". > This operates in "auto-commit" mode. When a statement is executed that > is not already inside an EXPLICIT (=user-created) transaction, then it > is wrapped inside an IMPLICIT (=engine-created) transaction. Therefore: > >INSERT INTO TABLE_A ... >BEGIN >INSERT INTO TABLE_B ... >DELETE FROM TABLE_C ... >COMMIT >SELECT ... FROM TABLE_D > > will essentially be turned into: > >BEGIN >INSERT INTO TABLE_A ... >COMMIT >BEGIN >INSERT INTO TABLE_B ... >DELETE FROM TABLE_C ... >COMMIT >BEGIN >SELECT ... FROM TABLE_D >COMMIT > > where the auto-generated BEGINs are the equivalent of BEGIN DEFERRED > (the SQLite engine's default if you just use BEGIN). -- Regards, Peng ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Is Comment column in 'EXPLAIN' blank?
Thanks David, getting them now. From: sqlite-users on behalf of David Raymond Sent: Friday, January 31, 2020 1:45:41 PM To: SQLite mailing list Subject: Re: [sqlite] Is Comment column in 'EXPLAIN' blank? To get the comments the library needs to have been compiled with SQLITE_ENABLE_EXPLAIN_COMMENTS flag https://www.sqlite.org/compile.html#_options_to_enable_features_normally_turned_off That setting is off by default, but for the pre-compiled CLI they do include it. I'm not seeing it in the list when I do a "pragma compile_options;" though. Is that setting one that's not reported by that pragma? -Original Message- From: sqlite-users On Behalf Of x Sent: Friday, January 31, 2020 6:22 AM To: sqlite-users@mailinglists.sqlite.org Subject: [sqlite] Is Comment column in 'EXPLAIN' blank? I get the comments using the shell but running an explain query in c++ using sqlite3.c (from 3.30 amalgamation) column 7 is returning blank in every row. Is something up or is it my code? ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Is Comment column in 'EXPLAIN' blank?
To get the comments the library needs to have been compiled with SQLITE_ENABLE_EXPLAIN_COMMENTS flag https://www.sqlite.org/compile.html#_options_to_enable_features_normally_turned_off That setting is off by default, but for the pre-compiled CLI they do include it. I'm not seeing it in the list when I do a "pragma compile_options;" though. Is that setting one that's not reported by that pragma? -Original Message- From: sqlite-users On Behalf Of x Sent: Friday, January 31, 2020 6:22 AM To: sqlite-users@mailinglists.sqlite.org Subject: [sqlite] Is Comment column in 'EXPLAIN' blank? I get the comments using the shell but running an explain query in c++ using sqlite3.c (from 3.30 amalgamation) column 7 is returning blank in every row. Is something up or is it my code? ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Is Comment column in 'EXPLAIN' blank?
I get the comments using the shell but running an explain query in c++ using sqlite3.c (from 3.30 amalgamation) column 7 is returning blank in every row. Is something up or is it my code? ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Generated columns and COLLATE in the AS parens
Let me first explain how collations work in standard SQL. I think my answers below make more sense then. In the SQL standard…. - character string **types** have properties such as fix-length vs. variable length, the length (limit), character set and also the collation. - columns have types, which include the collation if it is a character string type. However, it is not the column that has a collation. The column has a type, which might have a collation. - values have a static type (the so-called “declared type”). Static means it is determined at “compile time” from the syntax and the data dictionary. - Expressions also have a declared type. More elaborate: the result value of an expression has a type that is statically determined (again the “declared type”). - expressions of which the declared type is a character string type, will also have all the properties of character string types (including the collation). - When character strings are compared, the effective collation is determined from the declared types of the operands. The SQL standard defines rules how to do that (e.g. in 9075-2 9.15 "Collation determination”) The most important rule is the "collation derivation” order: explicit, implicit, none. That’s the rule that says the COLLATE clause on expressions (“explicit” derivation) is stronger than the COLLATE clause following type names (“implicit” derivation). But note that the last statement is not the reason the collation of the generation expression takes precedence of the data type — IT DOESN’T! As far as I know, this is basically unchanged sind SQL-92. If you do not have access to the current release of the standard (of 2016, to which my references apply), you can also have a look at SQL-92 here (search for "4.2 Character strings”): https://www.contrib.andrew.cmu.edu/~shadow/sql/sql1992.txt Generated columns where introduced with SQL:2003 and are thus not in the linked document. Putting these mechanics of working with collations in context of generated columns: - Generated columns need a type like any other column. If that type happens to be a character string type, the type includes the collation as part of the types properties. - Even though generated columns need a type, they allow skipping the type in the definition of the generated column—that’s very unique to generated columns. E.g. specifying a DEFAULT clause does not lift the requirement to explicitly state the type of the column. - If a generated column doesn’t have an explicit data type mentioned, the the data type of the result of the expression is used. Whatever data type that is, whatever properties that type has. If it happens to be a character string type, it will also have a “collation” property. However, the declared type of the expression (including its properties) is only relevant if the column definition doesn’t explicitly state a data type. These are two different cases in standard SQL: C1 TEXT GENERATED ALWAYS AS (…) C2 GENERATED ALWAYS AS (…) The type of C1 is TEXT, including all its default properties. The type of C2 is the type of the result of the expression, including all its properties. The “what’s new” paper for SQL:2003 mentions that case explicitly. http://sigmodrecord.org/publications/sigmodRecord/0403/E.JimAndrew-standard.pdf The example on the last page: > CREATE TABLE EMPLOYEES ( > EMP_ID INTEGER, > SALARY DECIMAL(7,2), > BONUS DECIMAL(7,2), > TOTAL_COMP GENERATED ALWAYS AS (SALARY + BONUS) > ) > > TOTAL_COMP is a generated column of the EMPLOYEES table. The data type of the > TOTAL_COMP is the data type of the expression (SALARY_BONUS). I think that’s a typo and should read (SALARY + BONUS). > Users may optionally specify a data type for a generated column, in which > case the specified data type must match with the data type of the associated > expression. The last part is not 100% in line with the current standard. The types don’t need to match, they need to be assignable. I don’t know if this is just a simplification for this paper or if SQL:2003 really hat that requirement. Further comments to your statements below… > On 30 Jan 2020, at 23:06, Keith Medcalf wrote: > > > On: Wednesday, 29 January, 2020 06:45, Markus Winand > wrote: > >> I think there might be a glitch in the way SQLite 3.31.x derives the >> collation information from the expression of a generated column. > >> In particular, COLLATE inside the AS parens seems to be ignored, but it >> is honoured after the parens: > > Carrying the COLLATE from an expression into the column definition is > incorrect. The definition of a generated column is: > > [type affinity] [GENERATED ALWAYS AS ()] [COLLATE > ] [ ...] > > so why would the so including a COLLATE as part of the expression applies to > the expression, and not to the column. Except for the ordering of > which must be first, followed by the type which must come > second, the
Re: [sqlite] Default isolation_level for sqlite3.connect?
Friday, January 31, 2020, 1:14:26 AM, Peng Yu wrote: > I still have a hard time to understand what the difference is > according to the python manual. It keeps saying see somewhere else in > the python manual. But I don't see where it explains the differences > between the differences comprehensively and clearly. Let me offer another answer to go alongside Keith's excellent response. CORE SQLITE3 ENGINE (Ignoring Python; as used from "C" or the command-line shell). This operates in "auto-commit" mode. When a statement is executed that is not already inside an EXPLICIT (=user-created) transaction, then it is wrapped inside an IMPLICIT (=engine-created) transaction. Therefore: INSERT INTO TABLE_A ... BEGIN INSERT INTO TABLE_B ... DELETE FROM TABLE_C ... COMMIT SELECT ... FROM TABLE_D will essentially be turned into: BEGIN INSERT INTO TABLE_A ... COMMIT BEGIN INSERT INTO TABLE_B ... DELETE FROM TABLE_C ... COMMIT BEGIN SELECT ... FROM TABLE_D COMMIT where the auto-generated BEGINs are the equivalent of BEGIN DEFERRED (the SQLite engine's default if you just use BEGIN). To me, this behaviour is straightforward, predictable, and (mostly) does what you (as a programmer) wants (but see the notes at the end). PYTHON WRAPPER Driven by what I assume were the best intentions, the Python wrapper will, by default, try to be "helpful" and decide when IT will wrap your SQL statements in BEGIN...COMMIT statements. How, and when it does this is controlled by the ISOLATION_LEVEL. If isolation_level is explicitly set to "None", then the Python wrapper does nothing, and you get the "normal" behaviour of the core SQLite3 engine as described above. As Keith recommends, and from what I've seen on this mailing list, this is highly recommended when using the Python wrapper. It makes everything done in Python work the same way as in C or the command-line shell, with no unexpected surprises. If you DON'T use isolation_level=None, then the Python wrapper will enter what Keith calls "magic mode". It will SOMETIMES add its own BEGIN ... COMMIT statements around your Python calls to SQLite. By default (if you don't specify isolation_level) these added calls will be simply BEGIN (which, the core SQLite engine treats as BEGIN DEFERRED). Otherwise, you can explicitly specify an isolation_level of DEFERRED (the same as leaving it blank), IMMEDIATE or EXCLUSIVE. As Keith notes, the problem with letting Python insert these BEGIN statements is that (a) different versions of the Python wrapper do things in different ways, and (b) at least some of those ways "get it wrong" (e.g. by not understanding things like WITH ... INSERT). Overall, it seems far better to disable the Python wrapper's "magic" mode (by using isolation_level=None) and have a far more predictable environment where YOU manage transactions yourself. FINAL NOTES I said above that the auto-commit behaviour of the SQLite engine "mostly" does what you want. One alternate behaviour would be to insist that EVERY statement was inside an EXPLICIT transaction (and raise an error if not). Most of the time, this would be somewhat annoying (especially when using the command-line tool), but would force the user/programmer to consciously think about when to use transactions. For example, if you have: SELECT data1 FROM ... SELECT data2 FROM ... then because each SELECT will be automatically wrapped inside their own BEGIN ... COMMIT, it is entirely possible (="guaranteed to happen the first time it is deployed in production") that some other process will alter the data between the two SELECTs and you end up with inconsistent values for "data1" and "data2". The answer, of course, is to do: BEGIN [something] SELECT data1 FROM ... SELECT data2 FROM ... COMMIT which guarantees consistency. From my (moderate) use of SQL, (almost) always using explicit transactions (rather than relying on the auto-commit behaviour of the core) falls in to the same sort of list of "good practices" as explicitly declaring the columns in a SELECT statement (as opposed to using SELECT *) and (depending on the programming language) always using braces in an IF statement, even if there's only one statement to conditionally execute. They're not essential, but tend to guard against silly mistakes. Regards, Graham Holden ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
