Re: [sqlite] [EXTERNAL] Re: Default isolation_level for sqlite3.connect?
"Autocommit" means that each SQL Statement executes in it's own transaction. Just as if you were to execute "begin; ; commit;" each time. -Ursprüngliche Nachricht- Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im Auftrag von Peng Yu Gesendet: Freitag, 31. Januar 2020 02:14 An: SQLite mailing list Betreff: [EXTERNAL] Re: [sqlite] Default isolation_level for sqlite3.connect? 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. https://www.sqlite.org/lang_transaction.html On the above page, I only see - BEGIN - BEGIN DEFERRED - BEGIN IMMEDIATE - BEGIN EXCLUSIVE , where BEGIN is just BEGIN DEFERRED. But what is isolation_level = None? https://docs.python.org/3.8/library/sqlite3.html#sqlite3-controlling-transactions "The underlying sqlite3 library operates in autocommit mode by default, but the Python sqlite3 module by default does not." According to the above, since isolation_level="" is the default in python, so it is not autocommit and it is BEGIN. But what is autocommit mode? The following doesn't provide a definition. Is there a definition on sqlite.com website? https://www.sqlite.org/c3ref/get_autocommit.html > The default is an empty string (ie, ''). It can take the value None, > '', 'DEFERRED', 'IMMEDIATE', 'EXCLUSIVE' and the value (if not None) > is appended to the BEGIN when a magical transaction is started, and if > None, then you are indicating that you will be using manual > transaction control > > isolation_level command > None None > '' (default) BEGIN > 'DEFERRED'BEGIN DEFERRED > 'IMMEDIATE' BEGIN IMMEDIATE > 'EXCLUSIVE' BEGIN EXCLUSIVE > > You can set the isolation_level in the connect call (the default is > '') or read/change it with the .isolation_level property of a connection > object. -- Regards, Peng ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users ___ Gunter Hick | Software Engineer | Scientific Games International GmbH | Klitschgasse 2-4, A-1130 Vienna | FN 157284 a, HG Wien, DVR: 0430013 | (O) +43 1 80100 - 0 May be privileged. May be confidential. Please delete if not the addressee. ___ 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?
At any given instant in time a connection can either (a) have a transaction in progress or (b) have no transaction in progress. An SQL statement cannot be executed EXCEPT inside of a transaction. "autocommit" means that the SQLite3 database engine (not the sqlite3 wrapper) will start the necessary transaction when a statement commences executing and commit (end the transaction) automatically when the statement finishes execution. You may issue one of the various BEGIN or SAVEPOINT commands to the SQLite3 database engine (not the sqlite3 wrapper) to START a transaction. If a transaction is in process when a statement begins execution then that statement will not start a transaction, nor will the statements completion autocommit the transaction. You must do this yourself with the COMMIT / ROLLBACK / RELEASE commands. The isolation_level setting of the sqlite3 wrapper for a connection tells the sqlite3 wrapper (not the SQLite3 engine) whether or not it should execute commands to BEGIN transactions magically or not, and what type of magical BEGIN commands to emit. Setting the isolation_level to None disables the crystal ball in the sqlite3 wrapper so that it *never* issues BEGIN commands of any type (by itself) and only executes the commands you tell it to execute when you tell it to execute them, placing you in control of the transaction and autocommit mode of the SQLite3 library and not the sqlite3 wrapper. The current version of pysqlite2 (2.8.2 and later) uses the following method to determine whether to automagically start a transaction: 1) The isolation_level is not None 2) The statement is not a DDL statement (the definition of DDL being that the first token on the line is "create", "drop", or "reindex") 3) The underlying SQLite3 engine connection is in autocommit mode (not in a transaction) 4) The underlying SQLite3 engine indicates the statement is not readonly. If all the above conditions are met then the sqlite3 wrapper issues the applicable BEGIN command before executing the requested SQL statement. This means that some DDL statements (eg ALTER) are treated as DML not DDL. However, the version pysqlite2 (sqlite3) distributed with even the most recent 3.8.1 version of Python still uses the old (2.6.0 version of pysqlite2) unreliable crystal ball which uses the following: 1) The isolation_level is not None 2) The statement is a DML statement (first token is "insert", "update", "delete", "replace") 3) The underlying SQLite3 connection is in autocommit mode. If all the above conditions are met then the sqlite3 wrapper issues the applicable BEGIN command before executing the requested SQL statement. This means that it is possible for some statements (example WITH ... INSERT) to not be recognized as performing updates and therefore not starting a transaction. In all cases you can still issue the various BEGIN and SAVEPOINT commands manually. Since you cannot always be sure when the sqlite3/pysqlite2 wrapper will automagically start transactions (especially since the magic can change at any time from version to version), you are better off setting isolation_level=None and doing transaction management yourself. -- 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: Thursday, 30 January, 2020 18:14 >To: SQLite mailing list >Subject: Re: [sqlite] Default isolation_level for sqlite3.connect? > >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. > >https://www.sqlite.org/lang_transaction.html > >On the above page, I only see > >- BEGIN >- BEGIN DEFERRED >- BEGIN IMMEDIATE >- BEGIN EXCLUSIVE > >, where BEGIN is just BEGIN DEFERRED. > >But what is isolation_level = None? > >https://docs.python.org/3.8/library/sqlite3.html#sqlite3-controlling- >transactions > >"The underlying sqlite3 library operates in autocommit mode by >default, but the Python sqlite3 module by default does not." > >According to the above, since isolation_level="" is the default in >python, so it is not autocommit and it is BEGIN. > >But what is autocommit mode? The following doesn't provide a >definition. Is there a definition on sqlite.com website? > >https://www.sqlite.org/c3ref/get_autocommit.html > >> The default is an empty string (ie, ''). It can take the value None, >'', >> 'DEFERRED', 'IMMEDIATE', 'EXCLUSIVE' and the value (if not None) is >appended >> to the BEGIN when a magical transaction is started, and if None, then >you >> are indicating that you will be using manual transaction control >> >> isolation_level command >> None None >> '' (default) BEGIN >> 'DEFERRED'BEGIN DEFERRED >> 'IMMEDIATE'
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
Re: [sqlite] Default isolation_level for sqlite3.connect?
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. https://www.sqlite.org/lang_transaction.html On the above page, I only see - BEGIN - BEGIN DEFERRED - BEGIN IMMEDIATE - BEGIN EXCLUSIVE , where BEGIN is just BEGIN DEFERRED. But what is isolation_level = None? https://docs.python.org/3.8/library/sqlite3.html#sqlite3-controlling-transactions "The underlying sqlite3 library operates in autocommit mode by default, but the Python sqlite3 module by default does not." According to the above, since isolation_level="" is the default in python, so it is not autocommit and it is BEGIN. But what is autocommit mode? The following doesn't provide a definition. Is there a definition on sqlite.com website? https://www.sqlite.org/c3ref/get_autocommit.html > The default is an empty string (ie, ''). It can take the value None, '', > 'DEFERRED', 'IMMEDIATE', 'EXCLUSIVE' and the value (if not None) is appended > to the BEGIN when a magical transaction is started, and if None, then you > are indicating that you will be using manual transaction control > > isolation_level command > None None > '' (default) BEGIN > 'DEFERRED'BEGIN DEFERRED > 'IMMEDIATE' BEGIN IMMEDIATE > 'EXCLUSIVE' BEGIN EXCLUSIVE > > You can set the isolation_level in the connect call (the default is '') or > read/change it with the .isolation_level property of a connection object. -- Regards, Peng ___ 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: 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 other
bits can appear in pretty much any ordering you heart desires (it is a Dim Sum
of clauses).
If the of the column not specified then its "type affinity" is
BLOB (None).
If no COLLATE is specified for the column, then the default collation BINARY
applies.
If no NULL constraint is specified then NULL values are permitted.
If no DEFAULT expression is specified then the default value is NULL.
Why would you think that the COLLATE applied to the evaluation of an expression
would "carry out" to the column itself? Especially in light of the fact that
the type/collation of the expression in a "DEFAULT ()" does not
carry out to the column definition?
The collation sequence applies to "comparison" operations of TEXT values, and
not to anything else (it is ignored for all other value types). It is an
attribute of a column (like column affinity) and not an attribute of a value.
In other words,
x AS (y == 'yahoo' collate nocase)
applies the affinity nocase to the evaluation of the == comparison operation.
The result of evaluation of the expression is a data value with a concrete type
(integer) and no associated collation. This value then has whatever column
affinity is specified applied to it and inherits the collation of the
containing column.
x AS (y collate nocase)
is the same thing as
x AS (y)
since there is no "comparison" operation in the expression to which the
collation can be applied (it is a useless attribute to the expression). The
result of the expression is a value of the concrete type of y with no
associated collation. This value then has the whatever column affinity is
specified applied to it and inherits the collation of the containing column.
ie, "values" do not have a collation associated with them, column definitions
and comparison operators on text have collation sequences associated with them.
>SQLite version 3.31.1 2020-01-27 19:55:54
>Enter ".help" for usage hints.
>Connected to a transient in-memory database.
>Use ".open FILENAME" to reopen on a persistent database.
>sqlite> CREATE TABLE tmp (str VARCHAR(255), str_nc1 GENERATED ALWAYS AS
>(str COLLATE NOCASE), str_nc2 GENERATED ALWAYS AS (str) COLLATE NOCASE);
>sqlite> INSERT INTO tmp VALUES ('a'), ('A'), ('b'), ('B');
>sqlite> SELECT * FROM tmp ORDER BY str;
>A|A|A
>B|B|B
>a|a|a
>b|b|b
>sqlite> SELECT * FROM tmp ORDER BY str_nc1;
>A|A|A
>B|B|B
>a|a|a
>b|b|b
>sqlite> SELECT * FROM tmp ORDER BY str_nc2;
>a|a|a
>A|A|A
>b|b|b
>B|B|B
>sqlite> SELECT * FROM tmp ORDER BY (str COLLATE NOCASE);
>a|a|a
>A|A|A
>b|b|b
>B|B|B
>sqlite>
>I believe the "ORDER BY str_nc1” should yield the same row order as the
>last two queries.
--
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] "Standard SQL" ?
> On 30 Jan 2020, at 21:12, Keith Medcalf wrote: > > On Thursday, 30 January, 2020 12:20, Simon Slavin > wrote: > >> I would appreciate your help. Reading a technical article today, I came >> across a casual reference to "Standard SQL" as if it was a well-known >> thing. This worried me since I've never heard the term and I'm meant to >> know about such things. I’m an author using that term. What I personally mean with “Standard SQL” is the language described by ISO/IEC 9075. (Sorry, I’m saying ISO/IEC 9075 a lot in my response to avoid the term “standard” and the related confusion). > > I would say that "Standard SQL" is that variant of SQL is that "minimal > common subset" dialect of SQL that is understood by every single > implementation claiming to be "SQL compliant" that has ever existed until the > present. In other words, it is the dialect of SQL that will be understood by > *any* software claiming to be "SQL compliant". I think the word for what you describe is “Core SQL”. I wrote about it here: https://modern-sql.com/standard/levels > >> It doesn't seem to refer to the official standard for SQL, which is huge >> and contains a plethora of features implemented once or never. The >> author seemed to think it was a sort of 'core SQL' – features identically >> implemented by all, or most, of the well-known SQL engines. Google, for example, is also using the term “Standard SQL” for products like BigQuery. Although Googles “Standard SQL” is more close to ISO/IEC 9075 that the other “SQL” dialects they offer, they are sill not conforming to ISO/IEC 9075 in pretty basic ways. > > There is a core SQL. It is not defined. It is defined in ISO/IEC 9075. There are tables called (quoting): “Feature taxonomy and definition for mandatory features”, contains a taxonomy of the features of SQL language in Core SQL that are specified in this part of ISO/IEC 9075. There is an explicit list of mandatory features for Core SQL. And there are many optional features. > Most SQL (particularly DDL) is "implementation specific". About the only > things that comprise "standard SQL" are the DML constructs: ISO/IEC 9075 also defines DDL. Generally ISO/IEC 9075 only describes semantics, but no implementation details. E.g. CREATE TABLE is defined, CREATE INDEX not. ISO/IEC 9075 allows language extensions as long as they use a syntax **not** described in ISO/IEC 9075. > > INSERT INTO () VALUES (); > SELECT FROM [WHERE ] [GROUP BY list> [HAVING ]] [ORDER BY ] > DELETE FROM [WHERE ] > UPDATE SET = [, = ]... [WHERE > ] > >> The one possibility I can think of is SQL:1999. This is the first >> version which has features marked as 'mandatory' or 'optional'. A full >> implementation of all mandatory features could, I suppose, be called >> "Standard SQL", but I've never heard of that term being used for that. > > This is very Johnny-lately. I think the first standard was SQL-85 but even > that was chock full of vendor implementation specifics and light on being > "standard". Subsequent versions of the SQL Standard committee output simply > became more a practice in documenting vendor implementation specifics rather > than defining a standard. Most standards suffer from this problem. > >> Have any of you been using this term for a meaning other than "Fully >> conforming to SQL:2019 (or whatever version you think current) ? Do you >> have documentation somewhere ? Or are my suspicions correct and there's >> no such thing ? > > Yes. "standard SQL" is that subset of SQL that is understood by every > implemetation ever without making changes. Well, that’s now how I’m using this term. Not does Google use it in this way. -markus ___ 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 30 Jan 2020, at 18:20, Richard Hipp wrote: > > On 1/30/20, Markus Winand wrote: >> >> Unfortunately, the “what would PostgreSQL do” approach doesn’t provide >> guidance here. > > Maybe it does. PostgreSQL doesn't allow typeless columns, but it does > allow columns with unspecified collating sequences, does it not? If you do not specify a COLLATE clause in a column definition, the default collation of the effective character set is used. The name ‘default’ is still a collation. Quoting https://www.postgresql.org/docs/current/collation.html: The collation of an expression can be the "default" collation, which means the locale settings defined for the database. > What > if you have a normal column X with some collating sequence C and then > a generated column Y that as just "AS(X)”. It depends on whether the generated column specifies a or not (see below). > If you do comparisons on > column Y, which collating sequence does it use - the default or C? > > Can you run that experiment for us? David Raymond did. Here is what happens, split into the three relevant cases. > testing=> create table foo ( > testing(> a text, > testing(> b text collate "C", > testing(> c text collate "en-US-x-icu”, > […] > attnum | attname | attgenerated | collname > +-+--+- > 1 | a | | default > 2 | b | | C > 3 | c | | en-US-x-icu Pretty obvious. The next columns: > testing(> d text generated always as (a) stored, > testing(> e text generated always as (b) stored, > testing(> f text generated always as (c) stored, > 4 | d | s| default > 5 | e | s| default > 6 | f | s| default The generated column definitions mention a , in that case the types character set’s default collation is used — coincidentally called “default”. Thus, it is the same case as column “a”. Finally: > testing(> g text collate "C" generated always as (c) stored, > testing(> h text generated always as (c collate "C") stored, > testing(> i text collate "C" generated always as (c collate "en-US-x-icu") > stored > testing(> ); > 7 | g | s| C > 8 | h | s| default > 9 | i | s| C Again, is specified, thus this collation is used. The collation of “h” is not “C” because there is an explicit type definition “text”, which includes a character set and a default collation. The collation of the expression is really only relevant if there is no (and no COLLATE outside the expression). > > Or maybe you are thinking the collating sequence of the expression in > the AS clause should only be carried through into the generated column > if it is explicitly stated, and not implied? There is only one case when the type of the expression, including the character set and collation should be carried through into the generated column: if the generated column doesn’t explicitly define a type. > > What happens if there is a collating sequence specified in the AS > clause and also another collating sequence on the column definitions? > > CREATE TABLE t1(x TEXT, y TEXT AS (x COLLATE nocase) COLLATE rtree); > > Which collating sequence should be used for "SELECT * FROM t1 WHERE y='xyz';"? There are three places where a COLLATE clause in the definition of a generated column is allowed: (1) After the data type (see 6.1, in the BNF) (2) Inside the expression (see 6.31, in the BNF) (3) At the very end (see 11.4, in the BNF). There is a syntax rule prohibiting (1) and (3) being used at the same time (11.4 SR12b). The same rule says that the effect of (1) and (3) is the same: > • Otherwise, shall not be both specified in type> and immediately contained in . If > is immediately contained in , then it is equivalent to > specifying an equivalent in . This is the case you were just are asking about. Case (2) might affect the collation of the expression, which is only relevant in case there is neither (1) or (3) specified. In that case, also the collation is taken from the expression—via the data type (11.4 SR13c). > 13) The declared type of the column is > Case: > • a) If is specified, then that data type. If clause> is also specified, then the declared type of > shall be assignable to the declared type of the column. > • b) If is specified, […] > • c) If is specified, then the declared type of GE. As you see, as soon as a is specified, the collation of the expression is irrelevant. This is what can be demonstrated in PostgreSQL. Skipping is currently not possible in PostgreSQL, thus PostgreSQL cannot be used to demonstrate c). > Regarding WWPD: If you can convince Tom Lane and/or Bruce Momjian to > send me an email that says "An explicit collating sequence at the > top-level of a GENERATED ALWAYS AS clause should be carried
Re: [sqlite] "Standard SQL" ?
On Thursday, 30 January, 2020 12:20, Simon Slavin wrote: >I would appreciate your help. Reading a technical article today, I came >across a casual reference to "Standard SQL" as if it was a well-known >thing. This worried me since I've never heard the term and I'm meant to >know about such things. I would say that "Standard SQL" is that variant of SQL is that "minimal common subset" dialect of SQL that is understood by every single implementation claiming to be "SQL compliant" that has ever existed until the present. In other words, it is the dialect of SQL that will be understood by *any* software claiming to be "SQL compliant". >It doesn't seem to refer to the official standard for SQL, which is huge >and contains a plethora of features implemented once or never. The >author seemed to think it was a sort of 'core SQL' – features identically >implemented by all, or most, of the well-known SQL engines. There is a core SQL. It is not defined. Most SQL (particularly DDL) is "implementation specific". About the only things that comprise "standard SQL" are the DML constructs: INSERT INTO () VALUES (); SELECT FROM [WHERE ] [GROUP BY [HAVING ]] [ORDER BY ] DELETE FROM [WHERE ] UPDATE SET = [, = ]... [WHERE ] >The one possibility I can think of is SQL:1999. This is the first >version which has features marked as 'mandatory' or 'optional'. A full >implementation of all mandatory features could, I suppose, be called >"Standard SQL", but I've never heard of that term being used for that. This is very Johnny-lately. I think the first standard was SQL-85 but even that was chock full of vendor implementation specifics and light on being "standard". Subsequent versions of the SQL Standard committee output simply became more a practice in documenting vendor implementation specifics rather than defining a standard. Most standards suffer from this problem. >Have any of you been using this term for a meaning other than "Fully >conforming to SQL:2019 (or whatever version you think current) ? Do you >have documentation somewhere ? Or are my suspicions correct and there's >no such thing ? Yes. "standard SQL" is that subset of SQL that is understood by every implemetation ever without making changes. It is a very small subset of the language. Any "standard SQL" statement will operate the same way on every "SQL compliant" bit of software that ever existed or will exist in the future. No. It comes from experience using more than one SQL language implementation. No. THere is most assuredly a "standard SQL" that is understood by every implemetation of SQL that ever existed or even will exist. That it is not documented is entirely beside the point. -- 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] "Standard SQL" ?
A few resources I found from https://www.google.com/search?q=sql+standard : - https://en.wikipedia.org/wiki/SQL - https://blog.ansi.org/2018/10/sql-standard-iso-iec-9075-2016-ansi-x3-135/#gref - https://dev.to/0xcrypto/who-owns-the-sql-standard-76m - https://modern-sql.com/standard - https://docs.oracle.com/cd/B28359_01/server.111/b28286/intro002.htm It seems to me like the last ANSI standard was published in 2016. It sound to me like implementors would generally implement a ``common SQL subset'' or ``common subset of the ANSI SQL standard''. My (US) $0.02 worth. On Thu, Jan 30, 2020 at 2:20 PM Simon Slavin wrote: > I would appreciate your help. Reading a technical article today, I came > across a casual reference to "Standard SQL" as if it was a well-known > thing. This worried me since I've never heard the term and I'm meant to > know about such things. > > It doesn't seem to refer to the official standard for SQL, which is huge > and contains a plethora of features implemented once or never. The author > seemed to think it was a sort of 'core SQL' – features identically > implemented by all, or most, of the well-known SQL engines. > > The one possibility I can think of is SQL:1999. This is the first version > which has features marked as 'mandatory' or 'optional'. A full > implementation of all mandatory features could, I suppose, be called > "Standard SQL", but I've never heard of that term being used for that. > > Have any of you been using this term for a meaning other than "Fully > conforming to SQL:2019 (or whatever version you think current) ? Do you > have documentation somewhere ? Or are my suspicions correct and there's no > such thing ? > ___ > 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] "Standard SQL" ?
I would appreciate your help. Reading a technical article today, I came across a casual reference to "Standard SQL" as if it was a well-known thing. This worried me since I've never heard the term and I'm meant to know about such things. It doesn't seem to refer to the official standard for SQL, which is huge and contains a plethora of features implemented once or never. The author seemed to think it was a sort of 'core SQL' – features identically implemented by all, or most, of the well-known SQL engines. The one possibility I can think of is SQL:1999. This is the first version which has features marked as 'mandatory' or 'optional'. A full implementation of all mandatory features could, I suppose, be called "Standard SQL", but I've never heard of that term being used for that. Have any of you been using this term for a meaning other than "Fully conforming to SQL:2019 (or whatever version you think current) ? Do you have documentation somewhere ? Or are my suspicions correct and there's no such thing ? ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] conditional insert operations
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
Re: [sqlite] Generated columns and COLLATE in the AS parens
From a Windows installation of 12.0 testing=> select version(); version PostgreSQL 12.0, compiled by Visual C++ build 1914, 64-bit (1 row) Time: 0.283 ms testing=> create table foo ( testing(> a text, testing(> b text collate "C", testing(> c text collate "en-US-x-icu", testing(> d text generated always as (a) stored, testing(> e text generated always as (b) stored, testing(> f text generated always as (c) stored, testing(> g text collate "C" generated always as (c) stored, testing(> h text generated always as (c collate "C") stored, testing(> i text collate "C" generated always as (c collate "en-US-x-icu") stored testing(> ); CREATE TABLE Time: 81.025 ms testing=> \d foo Table "public.foo" Column | Type | Collation | Nullable | Default +--+-+--+-- a | text | | | b | text | C | | c | text | en-US-x-icu | | d | text | | | generated always as (a) stored e | text | | | generated always as (b) stored f | text | | | generated always as (c) stored g | text | C | | generated always as (c) stored h | text | | | generated always as (c COLLATE "C") stored i | text | C | | generated always as (c COLLATE "en-US-x-icu") stored testing=> select attnum, attname, attgenerated, collname from pg_attribute left outer join pg_collation on pg_attribute.attcollation = pg_collation.oid where attrelid = 'foo'::regclass and attnum > 0 order by attnum; attnum | attname | attgenerated | collname +-+--+- 1 | a | | default 2 | b | | C 3 | c | | en-US-x-icu 4 | d | s| default 5 | e | s| default 6 | f | s| default 7 | g | s| C 8 | h | s| default 9 | i | s| C (9 rows) Time: 0.590 ms testing=> -Original Message- From: sqlite-users On Behalf Of Richard Hipp Sent: Thursday, January 30, 2020 12:21 PM To: SQLite mailing list Subject: Re: [sqlite] Generated columns and COLLATE in the AS parens On 1/30/20, Markus Winand wrote: > > Unfortunately, the “what would PostgreSQL do” approach doesn’t provide > guidance here. Maybe it does. PostgreSQL doesn't allow typeless columns, but it does allow columns with unspecified collating sequences, does it not? What if you have a normal column X with some collating sequence C and then a generated column Y that as just "AS(X)". If you do comparisons on column Y, which collating sequence does it use - the default or C? Can you run that experiment for us? Or maybe you are thinking the collating sequence of the expression in the AS clause should only be carried through into the generated column if it is explicitly stated, and not implied? What happens if there is a collating sequence specified in the AS clause and also another collating sequence on the column definitions? CREATE TABLE t1(x TEXT, y TEXT AS (x COLLATE nocase) COLLATE rtree); Which collating sequence should be used for "SELECT * FROM t1 WHERE y='xyz';"? Regarding WWPD: If you can convince Tom Lane and/or Bruce Momjian to send me an email that says "An explicit collating sequence at the top-level of a GENERATED ALWAYS AS clause should be carried through as the default collating sequence of the generated column itself", then I'll change 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 ___ 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 default is an empty string (ie, ''). It can take the value None, '',
'DEFERRED', 'IMMEDIATE', 'EXCLUSIVE' and the value (if not None) is appended to
the BEGIN when a magical transaction is started, and if None, then you are
indicating that you will be using manual transaction control
isolation_level command
None None
'' (default) BEGIN
'DEFERRED'BEGIN DEFERRED
'IMMEDIATE' BEGIN IMMEDIATE
'EXCLUSIVE' BEGIN EXCLUSIVE
You can set the isolation_level in the connect call (the default is '') or
read/change it with the .isolation_level property of a connection object.
>>> import sqlite3
>>> db = sqlite3.connect(':memory:')
>>> db.isolation_level
''
>>> db.isolation_level = None
>>> db.isolation_level
>>>
>>> db = sqlite3.connect(':memory:', isolation_level=None)
>>> db.isolation_level
>>>
--
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: Thursday, 30 January, 2020 09:55
>To: SQLite mailing list
>Subject: Re: [sqlite] Default isolation_level for sqlite3.connect?
>
>On 1/30/20, Keith Medcalf wrote:
>>
>> The isolation_level specifies the default suffix to put after 'BEGIN'
>when
>> beginning a transaction. Inside the library the following is used when
>the
>> magic wants to start a transaction:
>>
>> if isolation_level is not None:
>>.execute('BEGIN %s' % isolation_level)
>>
>> This is so that you can set isolation_level to 'IMMEDIATE' so that all
>> magically initiated transactions are BEGIN IMMEDIATE rather than the
>default
>> BEGIN DEFERRED
>
>Are you saying the default isolation_level is equivalent to IMMEDIATE?
>Or you are not answering my specific question of what is the default
>of isolation_level? I think the default paremeter value of
>isolation_level of the function sqlite3.connect() is just None. It is
>not the case?
>
>--
>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] Generated columns and COLLATE in the AS parens
On 1/30/20, Markus Winand wrote: > > Unfortunately, the “what would PostgreSQL do” approach doesn’t provide > guidance here. Maybe it does. PostgreSQL doesn't allow typeless columns, but it does allow columns with unspecified collating sequences, does it not? What if you have a normal column X with some collating sequence C and then a generated column Y that as just "AS(X)". If you do comparisons on column Y, which collating sequence does it use - the default or C? Can you run that experiment for us? Or maybe you are thinking the collating sequence of the expression in the AS clause should only be carried through into the generated column if it is explicitly stated, and not implied? What happens if there is a collating sequence specified in the AS clause and also another collating sequence on the column definitions? CREATE TABLE t1(x TEXT, y TEXT AS (x COLLATE nocase) COLLATE rtree); Which collating sequence should be used for "SELECT * FROM t1 WHERE y='xyz';"? Regarding WWPD: If you can convince Tom Lane and/or Bruce Momjian to send me an email that says "An explicit collating sequence at the top-level of a GENERATED ALWAYS AS clause should be carried through as the default collating sequence of the generated column itself", then I'll change 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] 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
Re: [sqlite] Default isolation_level for sqlite3.connect?
On 1/30/20, Keith Medcalf wrote:
>
> The isolation_level specifies the default suffix to put after 'BEGIN' when
> beginning a transaction. Inside the library the following is used when the
> magic wants to start a transaction:
>
> if isolation_level is not None:
>.execute('BEGIN %s' % isolation_level)
>
> This is so that you can set isolation_level to 'IMMEDIATE' so that all
> magically initiated transactions are BEGIN IMMEDIATE rather than the default
> BEGIN DEFERRED
Are you saying the default isolation_level is equivalent to IMMEDIATE?
Or you are not answering my specific question of what is the default
of isolation_level? I think the default paremeter value of
isolation_level of the function sqlite3.connect() is just None. It is
not the case?
--
Regards,
Peng
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] conditional insert operations
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
Re: [sqlite] Default isolation_level for sqlite3.connect?
The isolation_level specifies the default suffix to put after 'BEGIN' when
beginning a transaction. Inside the library the following is used when the
magic wants to start a transaction:
if isolation_level is not None:
.execute('BEGIN %s' % isolation_level)
This is so that you can set isolation_level to 'IMMEDIATE' so that all
magically initiated transactions are BEGIN IMMEDIATE rather than the default
BEGIN DEFERRED
--
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: Thursday, 30 January, 2020 08:59
>To: SQLite mailing list
>Subject: [sqlite] Default isolation_level for sqlite3.connect?
>
>Hi,
>
>I don't see what is the default isolation_level here. Is it None? Thanks.
>
>https://docs.python.org/3/library/sqlite3.html#module-functions-and-
>constants
>sqlite3.connect(database[, timeout, detect_types, isolation_level,
>check_same_thread, factory, cached_statements, uri])¶
>
>--
>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?
Incorrect. "Not specifying one" is sqlite3.connect(fi) And the connection will still start implicit transactions for you. (with "begin;") "Setting it to None" is sqlite3.connect(fi, isolation_level = None) Which will turn off all implicit transactions, put it in autocommit mode, and you have to do all transaction management yourself. -Original Message- From: sqlite-users On Behalf Of Peng Yu Sent: Thursday, January 30, 2020 11:29 AM To: SQLite mailing list Subject: Re: [sqlite] Default isolation_level for sqlite3.connect? > and if you don't specify one it issues a plain "begin;" So that is basically isolation_level = None? 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
Re: [sqlite] Default isolation_level for sqlite3.connect?
> and if you don't specify one it issues a plain "begin;" So that is basically isolation_level = None? Thanks. -- Regards, Peng ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[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
Re: [sqlite] Does .commit() ensure the .execute()'s and .executemany()'s called before are run atomically?
That's just my own personal paranoia wanting to make sure the cursor always gets closed, even on an exception. As I don't think the normal context manager on a cursor closes it when it exits. In the real world it's probably overkill as a) The destructors probably take care of that b) It's the connection you're more worried about and not the cursor c) SQLite is designed to handle crashes, etc. -Original Message- From: sqlite-users On Behalf Of Peng Yu Sent: Thursday, January 30, 2020 11:02 AM To: SQLite mailing list Subject: Re: [sqlite] Does .commit() ensure the .execute()'s and .executemany()'s called before are run atomically? Thanks. What is the purpose of contextlib. If I just use `cur = conn.cursor()`, what problems it will cause? > with contextlib.closing(conn.cursor()) as cur: -- 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] Is it necessary to encode() for file names in sqlar format?
Yes. If it is bytes type then the data stored by the database will be a BLOB,
not 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 Peng Yu
>Sent: Thursday, 30 January, 2020 02:24
>To: SQLite mailing list
>Subject: Re: [sqlite] Is it necessary to encode() for file names in sqlar
>format?
>
>So to confirm. In python 3, the str type should be used for name? Thanks.
>
>On Thu, Jan 30, 2020 at 12:58 AM Keith Medcalf
>wrote:
>
>>
>> sys.argv is a list of unicode text strings. There is no need to
>> specifically encode or decode it so long as sys.getdefaultencoding()
>> returns 'utf-8'. If your version of Python is so old that it returns
>> something else then you need to modify site.py and have it set the
>default
>> encoding to 'utf-8' otherwise you may end up with MBCS or some other
>> invalid text encoding in your database text fields.
>>
>> --
>> 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: Wednesday, 29 January, 2020 22:57
>> >To: SQLite mailing list
>> >Subject: [sqlite] Is it necessary to encode() for file names in sqlar
>> >format?
>> >
>> >I use the following python3 code to create sqlar file. Is it necessary
>> >to sys.argv[2].encode('utf-8') in the line of execute()? In other
>> >word, does the native sqlar tools inteprete the name column as an
>> >encoded value or a non-encode value? Thanks.
>> >
>> >import sqlite3
>> >conn=sqlite3.connect(sys.argv[1])
>> >c=conn.cursor()
>> >c.execute('''
>> >CREATE TABLE IF NOT EXISTS sqlar(
>> >name TEXT PRIMARY KEY
>> >, mode INT
>> >, mtime INT
>> >, sz INT
>> >, data BLOB)
>> >''')
>> >
>> >import zlib
>> >data = sys.stdin.buffer.read()
>> >c.execute('REPLACE INTO sqlar VALUES(?, ?, ?, ?, ?)', [sys.argv[2], 0,
>> >0, len(data), zlib.compress(data)])
>> >conn.commit()
>> >
>> >--
>> >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?
https://docs.python.org/3.8/library/sqlite3.html#sqlite3-controlling-transactions "You can control which kind of BEGIN statements sqlite3 implicitly executes via the isolation_level parameter to the connect() call, or via the isolation_level property of connections. If you specify no isolation_level, a plain BEGIN is used, which is equivalent to specifying DEFERRED. Other possible values are IMMEDIATE and EXCLUSIVE." https://www.sqlite.org/lang_transaction.html So basically if you explicitly specify one it will issue either a "begin deferred;", "begin immediate;", or "begin exclusive;", and if you don't specify one it issues a plain "begin;" -Original Message- From: sqlite-users On Behalf Of Peng Yu Sent: Thursday, January 30, 2020 10:59 AM To: SQLite mailing list Subject: [sqlite] Default isolation_level for sqlite3.connect? Hi, I don't see what is the default isolation_level here. Is it None? Thanks. https://docs.python.org/3/library/sqlite3.html#module-functions-and-constants sqlite3.connect(database[, timeout, detect_types, isolation_level, check_same_thread, factory, cached_statements, uri])¶ -- 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] Does .commit() ensure the .execute()'s and .executemany()'s called before are run atomically?
Thanks. What is the purpose of contextlib. If I just use `cur = conn.cursor()`, what problems it will cause? > with contextlib.closing(conn.cursor()) as cur: -- Regards, Peng ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Default isolation_level for sqlite3.connect?
Hi, I don't see what is the default isolation_level here. Is it None? Thanks. https://docs.python.org/3/library/sqlite3.html#module-functions-and-constants sqlite3.connect(database[, timeout, detect_types, isolation_level, check_same_thread, factory, cached_statements, uri])¶ -- Regards, Peng ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] [EXTERNAL] Either a bug or I don't understand SQL update
You have fallen into the double quote trap. SQLite uses double quotes to denote COLUMN NAMES, and single quotes to delimit STRING CONSTANTS. When asking for "M" or "G", you get the contents of the column named m and g respectively (column names are case insensitive). When asking for "P" or "R", there is no column with that name, so SQLite pretends that you meant to provide a string constant. BTW: You have columns with NUMERIC affinity obviously representing amounts, which will get stored as REAL numbers. Don't do that if you expect to do anything financially relevant. You will incur rounding errors. Instead, store integer values of cents. -Ursprüngliche Nachricht- Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im Auftrag von Paul Ausbeck Gesendet: Donnerstag, 30. Januar 2020 00:11 An: sqlite-users@mailinglists.sqlite.org Betreff: [EXTERNAL] [sqlite] Either a bug or I don't understand SQL update I've been using sqlite for some time but haven't used SQL update until recently. On my first real use, I've encountered a problem that I can't understand at all. To pare this down, I have two tables, citytax and taxitems2, and I've attached a database with just these two tables, total size ~12Kbytes. I perform the following update using the sqlite3 command line interface: update citytax set salesm = (select sum(amount) from taxitems2 where taxitems2.citynum = citytax.number and taxitems2.taxrate = "M"); The result is that the salesm column is set to NULL for all citytax rows. However, I would expect that rows 9 and 22 would be non-NULL. I get the the same all-NULL result for a taxrate of "G" as well, though I would expect that rows 14, 19, and 58 would be non-NULL. The strangest part is that if I specify taxrates of "P", "R", or "", I get the result that I expect. I've included a listing of the taxitems2 table below. As one can see, there are rows where the second column, taxrate, is all of "", "P", "R", "G", and "M". The last column is the amount column, non-null for all rows. I must admit that I don't understand what is going on here. Perhaps even stranger is if I extract the select from the update and give it an appropriate row number, I get the expected non-NULL result: sqlite> select sum(amount) from taxitems2 where taxitems2.citynum = 9 and taxitems2.taxrate = "M"; 1176.72 I've included the contents of the taxitems2 table as well as the schema for tables taxitems2 and citytax below. One the one hand, this seems almost certainly to be a bug, but on the other, it is so basic, that I can't believe that I'm the first to encounter it. Any assistance that anyone can provide would of course be much appreciated. I'm using sqlite 3.30 under Windows 7 cygwin and 3.27 under Debian linux, both environments behave the same in this regard. As I was writing I had yet another test idea: update citytax set salesm = (select sum(amount) from taxitems2 where taxitems2.citynum = citytax.number and unicode(taxitems2.taxrate) = 77); /* taxrate = "M" */ and update citytax set salesm = (select sum(amount) from taxitems2 where taxitems2.citynum = citytax.number and unicode(taxitems2.taxrate) = 71); /* taxrate = "G" */ Both of these updates perform as expected. Regards, Paul Ausbeck sqlite> select * from taxitems2; WAL,,68,10,2,1,4,0.75,2,1.5,4,142.5 SUM,,34,36,2,1,3.5,0.75,2,1.5,4,0 USX,P,58,14,1,0,0,0,0,0,0,1133.0 USX,P,58,14,1,0,0,0,0,0,0,1133.0 USX,P,58,14,1,0,0,0,0,0,0,1133.0 USX,P,58,14,1,0,0,0,0,0,0,1133.0 USX,P,58,14,1,0,0,0,0,0,0,1133.0 USX,P,58,14,1,0,0,0,0,0,0,1133.0 DNT,,9,10,2,2,4,0.75,2,1.5,4,206.0 USX,P,58,14,1,0,0,0,0,0,0,7104.0 TRI,R,22,10,2,1,4,0.75,2,1.5,4,28.8499945 TRI,R,22,10,2,1,4,0.75,2,1.5,4,28.8499945 TRI,R,22,10,2,1,4,0.75,2,1.5,4,115.399978 TRI,R,22,10,2,1,4,0.75,2,1.5,4,115.399978 USX,P,58,14,1,0,0,0,0,0,0,5446.4 USX,P,58,14,1,0,0,0,0,0,0,1657.6 AAM,G,58,36,2,0,0,0.75,2,1.5,4,34.4 PEM,G,58,10,2,0,0,0.75,2,1.5,4,107.6 PEM,G,58,10,2,0,0,0.75,2,1.5,4,134.4 PEM,G,58,10,2,0,0,0.75,2,1.5,4,6.3 PEM,G,58,10,2,0,0,0.75,2,1.5,4,8 PEM,G,58,10,2,0,0,0.75,2,1.5,4,134.4 HVY,R,58,14,1,0,0,0,0,0,0,72.5 HVY,R,58,14,1,0,0,0,0,0,0,176.4 VEI,G,14,10,2,1,4,0.75,2,1.5,4,134.4 PEM,G,58,10,2,0,0,0.75,2,1.5,4,134.4 PEM,G,58,10,2,0,0,0.75,2,1.5,4,168 CSH,G,19,10,2,2,4,0.75,2,1.5,4,33.2 CSH,G,19,10,2,2,4,0.75,2,1.5,4,25.0 WAT,M,9,10,2,2,4,0.75,2,1.5,4,9.5 WAT,M,9,10,2,2,4,0.75,2,1.5,4,65 WAT,M,9,10,2,2,4,0.75,2,1.5,4,125.0 WAT,M,9,10,2,2,4,0.75,2,1.5,4,43.9 WAT,M,9,10,2,2,4,0.75,2,1.5,4,107.4 WAT,M,9,10,2,2,4,0.75,2,1.5,4,46.72 WAT,M,9,10,2,2,4,0.75,2,1.5,4,52.5 WAT,M,9,10,2,2,4,0.75,2,1.5,4,12.5 WAT,M,9,10,2,2,4,0.75,2,1.5,4,63.3 ICM,M,22,10,2,1,4,0.75,2,1.5,4,120.2000394 DNT,M,9,10,2,2,4,0.75,2,1.5,4,334.4 DNT,M,9,10,2,2,4,0.75,2,1.5,4,155.8 DNT,M,9,10,2,2,4,0.75,2,1.5,4,160.7 sqlite> .schema taxitems2 CREATE TABLE taxitems2( code TEXT, taxrate TEXT, citynum INT, countynum INT, statenum INT, citym NUM, cityg NUM, countym
Re: [sqlite] Either a bug or I don't understand SQL update
The mailing list strips all attachments, so you'll have to either provide a link to it on some external source, or give more create table/insert statements like at the bottom. Without having all the data I'll say: Remember to use single quotes for text literals. You have ...and taxitem2.taxrate = "M"... which with the double quotes means "equals the field named M", and hey, the citytax table just so happens to have a field with the name of "m", so my guess is that's your problem. -Original Message- From: sqlite-users On Behalf Of Paul Ausbeck Sent: Wednesday, January 29, 2020 6:11 PM To: sqlite-users@mailinglists.sqlite.org Subject: [sqlite] Either a bug or I don't understand SQL update I've been using sqlite for some time but haven't used SQL update until recently. On my first real use, I've encountered a problem that I can't understand at all. To pare this down, I have two tables, citytax and taxitems2, and I've attached a database with just these two tables, total size ~12Kbytes. I perform the following update using the sqlite3 command line interface: update citytax set salesm = (select sum(amount) from taxitems2 where taxitems2.citynum = citytax.number and taxitems2.taxrate = "M"); The result is that the salesm column is set to NULL for all citytax rows. However, I would expect that rows 9 and 22 would be non-NULL. I get the the same all-NULL result for a taxrate of "G" as well, though I would expect that rows 14, 19, and 58 would be non-NULL. The strangest part is that if I specify taxrates of "P", "R", or "", I get the result that I expect. I've included a listing of the taxitems2 table below. As one can see, there are rows where the second column, taxrate, is all of "", "P", "R", "G", and "M". The last column is the amount column, non-null for all rows. I must admit that I don't understand what is going on here. Perhaps even stranger is if I extract the select from the update and give it an appropriate row number, I get the expected non-NULL result: sqlite> select sum(amount) from taxitems2 where taxitems2.citynum = 9 and taxitems2.taxrate = "M"; 1176.72 I've included the contents of the taxitems2 table as well as the schema for tables taxitems2 and citytax below. One the one hand, this seems almost certainly to be a bug, but on the other, it is so basic, that I can't believe that I'm the first to encounter it. Any assistance that anyone can provide would of course be much appreciated. I'm using sqlite 3.30 under Windows 7 cygwin and 3.27 under Debian linux, both environments behave the same in this regard. As I was writing I had yet another test idea: update citytax set salesm = (select sum(amount) from taxitems2 where taxitems2.citynum = citytax.number and unicode(taxitems2.taxrate) = 77); /* taxrate = "M" */ and update citytax set salesm = (select sum(amount) from taxitems2 where taxitems2.citynum = citytax.number and unicode(taxitems2.taxrate) = 71); /* taxrate = "G" */ Both of these updates perform as expected. Regards, Paul Ausbeck sqlite> select * from taxitems2; WAL,,68,10,2,1,4,0.75,2,1.5,4,142.5 SUM,,34,36,2,1,3.5,0.75,2,1.5,4,0 USX,P,58,14,1,0,0,0,0,0,0,1133.0 USX,P,58,14,1,0,0,0,0,0,0,1133.0 USX,P,58,14,1,0,0,0,0,0,0,1133.0 USX,P,58,14,1,0,0,0,0,0,0,1133.0 USX,P,58,14,1,0,0,0,0,0,0,1133.0 USX,P,58,14,1,0,0,0,0,0,0,1133.0 DNT,,9,10,2,2,4,0.75,2,1.5,4,206.0 USX,P,58,14,1,0,0,0,0,0,0,7104.0 TRI,R,22,10,2,1,4,0.75,2,1.5,4,28.8499945 TRI,R,22,10,2,1,4,0.75,2,1.5,4,28.8499945 TRI,R,22,10,2,1,4,0.75,2,1.5,4,115.399978 TRI,R,22,10,2,1,4,0.75,2,1.5,4,115.399978 USX,P,58,14,1,0,0,0,0,0,0,5446.4 USX,P,58,14,1,0,0,0,0,0,0,1657.6 AAM,G,58,36,2,0,0,0.75,2,1.5,4,34.4 PEM,G,58,10,2,0,0,0.75,2,1.5,4,107.6 PEM,G,58,10,2,0,0,0.75,2,1.5,4,134.4 PEM,G,58,10,2,0,0,0.75,2,1.5,4,6.3 PEM,G,58,10,2,0,0,0.75,2,1.5,4,8 PEM,G,58,10,2,0,0,0.75,2,1.5,4,134.4 HVY,R,58,14,1,0,0,0,0,0,0,72.5 HVY,R,58,14,1,0,0,0,0,0,0,176.4 VEI,G,14,10,2,1,4,0.75,2,1.5,4,134.4 PEM,G,58,10,2,0,0,0.75,2,1.5,4,134.4 PEM,G,58,10,2,0,0,0.75,2,1.5,4,168 CSH,G,19,10,2,2,4,0.75,2,1.5,4,33.2 CSH,G,19,10,2,2,4,0.75,2,1.5,4,25.0 WAT,M,9,10,2,2,4,0.75,2,1.5,4,9.5 WAT,M,9,10,2,2,4,0.75,2,1.5,4,65 WAT,M,9,10,2,2,4,0.75,2,1.5,4,125.0 WAT,M,9,10,2,2,4,0.75,2,1.5,4,43.9 WAT,M,9,10,2,2,4,0.75,2,1.5,4,107.4 WAT,M,9,10,2,2,4,0.75,2,1.5,4,46.72 WAT,M,9,10,2,2,4,0.75,2,1.5,4,52.5 WAT,M,9,10,2,2,4,0.75,2,1.5,4,12.5 WAT,M,9,10,2,2,4,0.75,2,1.5,4,63.3 ICM,M,22,10,2,1,4,0.75,2,1.5,4,120.2000394 DNT,M,9,10,2,2,4,0.75,2,1.5,4,334.4 DNT,M,9,10,2,2,4,0.75,2,1.5,4,155.8 DNT,M,9,10,2,2,4,0.75,2,1.5,4,160.7 sqlite> .schema taxitems2 CREATE TABLE taxitems2( code TEXT, taxrate TEXT, citynum INT, countynum INT, statenum INT, citym NUM, cityg NUM, countym NUM, countyg NUM, statem NUM, stateg NUM, amount ); sqlite> .schema citytax CREATE TABLE citytax( number INT, code INT, g NUM, m NUM, comment TEXT, taxg NUM, taxm NUM, salesg NUM, salesm NUM,
Re: [sqlite] Either a bug or I don't understand SQL update
"M" refers to a column named M, specifically, citytax.m . Similarly, "G" is a reference to citytax.g. String literals in SQL are enclosed in single quotes, as in 'M' and 'G' ; double quotes are used to enclose names (helpful for names that contain spaces or other characters not allowed in identifiers). SQLite has a feature for backward compatibility, whereby a string enclosed in double quotes is taken as a string literal if it doesn't match any name in scope. That's why "P" and "R" work, but "M" and "G" don't. Igor Tandetnik On 1/29/2020 6:11 PM, Paul Ausbeck wrote: I've been using sqlite for some time but haven't used SQL update until recently. On my first real use, I've encountered a problem that I can't understand at all. To pare this down, I have two tables, citytax and taxitems2, and I've attached a database with just these two tables, total size ~12Kbytes. I perform the following update using the sqlite3 command line interface: update citytax set salesm = (select sum(amount) from taxitems2 where taxitems2.citynum = citytax.number and taxitems2.taxrate = "M"); The result is that the salesm column is set to NULL for all citytax rows. However, I would expect that rows 9 and 22 would be non-NULL. I get the the same all-NULL result for a taxrate of "G" as well, though I would expect that rows 14, 19, and 58 would be non-NULL. The strangest part is that if I specify taxrates of "P", "R", or "", I get the result that I expect. I've included a listing of the taxitems2 table below. As one can see, there are rows where the second column, taxrate, is all of "", "P", "R", "G", and "M". The last column is the amount column, non-null for all rows. I must admit that I don't understand what is going on here. Perhaps even stranger is if I extract the select from the update and give it an appropriate row number, I get the expected non-NULL result: sqlite> select sum(amount) from taxitems2 where taxitems2.citynum = 9 and taxitems2.taxrate = "M"; 1176.72 I've included the contents of the taxitems2 table as well as the schema for tables taxitems2 and citytax below. One the one hand, this seems almost certainly to be a bug, but on the other, it is so basic, that I can't believe that I'm the first to encounter it. Any assistance that anyone can provide would of course be much appreciated. I'm using sqlite 3.30 under Windows 7 cygwin and 3.27 under Debian linux, both environments behave the same in this regard. As I was writing I had yet another test idea: update citytax set salesm = (select sum(amount) from taxitems2 where taxitems2.citynum = citytax.number and unicode(taxitems2.taxrate) = 77); /* taxrate = "M" */ and update citytax set salesm = (select sum(amount) from taxitems2 where taxitems2.citynum = citytax.number and unicode(taxitems2.taxrate) = 71); /* taxrate = "G" */ Both of these updates perform as expected. Regards, Paul Ausbeck sqlite> select * from taxitems2; WAL,,68,10,2,1,4,0.75,2,1.5,4,142.5 SUM,,34,36,2,1,3.5,0.75,2,1.5,4,0 USX,P,58,14,1,0,0,0,0,0,0,1133.0 USX,P,58,14,1,0,0,0,0,0,0,1133.0 USX,P,58,14,1,0,0,0,0,0,0,1133.0 USX,P,58,14,1,0,0,0,0,0,0,1133.0 USX,P,58,14,1,0,0,0,0,0,0,1133.0 USX,P,58,14,1,0,0,0,0,0,0,1133.0 DNT,,9,10,2,2,4,0.75,2,1.5,4,206.0 USX,P,58,14,1,0,0,0,0,0,0,7104.0 TRI,R,22,10,2,1,4,0.75,2,1.5,4,28.8499945 TRI,R,22,10,2,1,4,0.75,2,1.5,4,28.8499945 TRI,R,22,10,2,1,4,0.75,2,1.5,4,115.399978 TRI,R,22,10,2,1,4,0.75,2,1.5,4,115.399978 USX,P,58,14,1,0,0,0,0,0,0,5446.4 USX,P,58,14,1,0,0,0,0,0,0,1657.6 AAM,G,58,36,2,0,0,0.75,2,1.5,4,34.4 PEM,G,58,10,2,0,0,0.75,2,1.5,4,107.6 PEM,G,58,10,2,0,0,0.75,2,1.5,4,134.4 PEM,G,58,10,2,0,0,0.75,2,1.5,4,6.3 PEM,G,58,10,2,0,0,0.75,2,1.5,4,8 PEM,G,58,10,2,0,0,0.75,2,1.5,4,134.4 HVY,R,58,14,1,0,0,0,0,0,0,72.5 HVY,R,58,14,1,0,0,0,0,0,0,176.4 VEI,G,14,10,2,1,4,0.75,2,1.5,4,134.4 PEM,G,58,10,2,0,0,0.75,2,1.5,4,134.4 PEM,G,58,10,2,0,0,0.75,2,1.5,4,168 CSH,G,19,10,2,2,4,0.75,2,1.5,4,33.2 CSH,G,19,10,2,2,4,0.75,2,1.5,4,25.0 WAT,M,9,10,2,2,4,0.75,2,1.5,4,9.5 WAT,M,9,10,2,2,4,0.75,2,1.5,4,65 WAT,M,9,10,2,2,4,0.75,2,1.5,4,125.0 WAT,M,9,10,2,2,4,0.75,2,1.5,4,43.9 WAT,M,9,10,2,2,4,0.75,2,1.5,4,107.4 WAT,M,9,10,2,2,4,0.75,2,1.5,4,46.72 WAT,M,9,10,2,2,4,0.75,2,1.5,4,52.5 WAT,M,9,10,2,2,4,0.75,2,1.5,4,12.5 WAT,M,9,10,2,2,4,0.75,2,1.5,4,63.3 ICM,M,22,10,2,1,4,0.75,2,1.5,4,120.2000394 DNT,M,9,10,2,2,4,0.75,2,1.5,4,334.4 DNT,M,9,10,2,2,4,0.75,2,1.5,4,155.8 DNT,M,9,10,2,2,4,0.75,2,1.5,4,160.7 sqlite> .schema taxitems2 CREATE TABLE taxitems2( code TEXT, taxrate TEXT, citynum INT, countynum INT, statenum INT, citym NUM, cityg NUM, countym NUM, countyg NUM, statem NUM, stateg NUM, amount ); sqlite> .schema citytax CREATE TABLE citytax( number INT, code INT, g NUM, m NUM, comment TEXT, taxg NUM, taxm NUM, salesg NUM, salesm NUM, salesr NUM, salesp NUM, stamp INT ); ___ sqlite-users mailing list
[sqlite] Either a bug or I don't understand SQL update
I've been using sqlite for some time but haven't used SQL update until recently. On my first real use, I've encountered a problem that I can't understand at all. To pare this down, I have two tables, citytax and taxitems2, and I've attached a database with just these two tables, total size ~12Kbytes. I perform the following update using the sqlite3 command line interface: update citytax set salesm = (select sum(amount) from taxitems2 where taxitems2.citynum = citytax.number and taxitems2.taxrate = "M"); The result is that the salesm column is set to NULL for all citytax rows. However, I would expect that rows 9 and 22 would be non-NULL. I get the the same all-NULL result for a taxrate of "G" as well, though I would expect that rows 14, 19, and 58 would be non-NULL. The strangest part is that if I specify taxrates of "P", "R", or "", I get the result that I expect. I've included a listing of the taxitems2 table below. As one can see, there are rows where the second column, taxrate, is all of "", "P", "R", "G", and "M". The last column is the amount column, non-null for all rows. I must admit that I don't understand what is going on here. Perhaps even stranger is if I extract the select from the update and give it an appropriate row number, I get the expected non-NULL result: sqlite> select sum(amount) from taxitems2 where taxitems2.citynum = 9 and taxitems2.taxrate = "M"; 1176.72 I've included the contents of the taxitems2 table as well as the schema for tables taxitems2 and citytax below. One the one hand, this seems almost certainly to be a bug, but on the other, it is so basic, that I can't believe that I'm the first to encounter it. Any assistance that anyone can provide would of course be much appreciated. I'm using sqlite 3.30 under Windows 7 cygwin and 3.27 under Debian linux, both environments behave the same in this regard. As I was writing I had yet another test idea: update citytax set salesm = (select sum(amount) from taxitems2 where taxitems2.citynum = citytax.number and unicode(taxitems2.taxrate) = 77); /* taxrate = "M" */ and update citytax set salesm = (select sum(amount) from taxitems2 where taxitems2.citynum = citytax.number and unicode(taxitems2.taxrate) = 71); /* taxrate = "G" */ Both of these updates perform as expected. Regards, Paul Ausbeck sqlite> select * from taxitems2; WAL,,68,10,2,1,4,0.75,2,1.5,4,142.5 SUM,,34,36,2,1,3.5,0.75,2,1.5,4,0 USX,P,58,14,1,0,0,0,0,0,0,1133.0 USX,P,58,14,1,0,0,0,0,0,0,1133.0 USX,P,58,14,1,0,0,0,0,0,0,1133.0 USX,P,58,14,1,0,0,0,0,0,0,1133.0 USX,P,58,14,1,0,0,0,0,0,0,1133.0 USX,P,58,14,1,0,0,0,0,0,0,1133.0 DNT,,9,10,2,2,4,0.75,2,1.5,4,206.0 USX,P,58,14,1,0,0,0,0,0,0,7104.0 TRI,R,22,10,2,1,4,0.75,2,1.5,4,28.8499945 TRI,R,22,10,2,1,4,0.75,2,1.5,4,28.8499945 TRI,R,22,10,2,1,4,0.75,2,1.5,4,115.399978 TRI,R,22,10,2,1,4,0.75,2,1.5,4,115.399978 USX,P,58,14,1,0,0,0,0,0,0,5446.4 USX,P,58,14,1,0,0,0,0,0,0,1657.6 AAM,G,58,36,2,0,0,0.75,2,1.5,4,34.4 PEM,G,58,10,2,0,0,0.75,2,1.5,4,107.6 PEM,G,58,10,2,0,0,0.75,2,1.5,4,134.4 PEM,G,58,10,2,0,0,0.75,2,1.5,4,6.3 PEM,G,58,10,2,0,0,0.75,2,1.5,4,8 PEM,G,58,10,2,0,0,0.75,2,1.5,4,134.4 HVY,R,58,14,1,0,0,0,0,0,0,72.5 HVY,R,58,14,1,0,0,0,0,0,0,176.4 VEI,G,14,10,2,1,4,0.75,2,1.5,4,134.4 PEM,G,58,10,2,0,0,0.75,2,1.5,4,134.4 PEM,G,58,10,2,0,0,0.75,2,1.5,4,168 CSH,G,19,10,2,2,4,0.75,2,1.5,4,33.2 CSH,G,19,10,2,2,4,0.75,2,1.5,4,25.0 WAT,M,9,10,2,2,4,0.75,2,1.5,4,9.5 WAT,M,9,10,2,2,4,0.75,2,1.5,4,65 WAT,M,9,10,2,2,4,0.75,2,1.5,4,125.0 WAT,M,9,10,2,2,4,0.75,2,1.5,4,43.9 WAT,M,9,10,2,2,4,0.75,2,1.5,4,107.4 WAT,M,9,10,2,2,4,0.75,2,1.5,4,46.72 WAT,M,9,10,2,2,4,0.75,2,1.5,4,52.5 WAT,M,9,10,2,2,4,0.75,2,1.5,4,12.5 WAT,M,9,10,2,2,4,0.75,2,1.5,4,63.3 ICM,M,22,10,2,1,4,0.75,2,1.5,4,120.2000394 DNT,M,9,10,2,2,4,0.75,2,1.5,4,334.4 DNT,M,9,10,2,2,4,0.75,2,1.5,4,155.8 DNT,M,9,10,2,2,4,0.75,2,1.5,4,160.7 sqlite> .schema taxitems2 CREATE TABLE taxitems2( code TEXT, taxrate TEXT, citynum INT, countynum INT, statenum INT, citym NUM, cityg NUM, countym NUM, countyg NUM, statem NUM, stateg NUM, amount ); sqlite> .schema citytax CREATE TABLE citytax( number INT, code INT, g NUM, m NUM, comment TEXT, taxg NUM, taxm NUM, salesg NUM, salesm NUM, salesr NUM, salesp NUM, stamp INT ); ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Please unsubscribe me
On 30 Jan 2020, at 15:34, kuppappa wrote: > Regards, > Kuppappa > Mobile: +91 8050095558 > ___ > sqlite-users mailing list > sqlite-users@mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users You do that yourself using the above website. -- Cheers -- Tim ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Please unsubscribe me
Regards, Kuppappa Mobile: +91 8050095558 ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] New word to replace "serverless"
Someone should put all the proposals into a vote. The voting system could be driven by a serverless database I presume. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] New word to replace "serverless"
I thougth about self-service, self-serve or self-served. Thanks, E. Pasma ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Behavior change around triggers between 3.19 and 3.30
On Thu, Jan 30, 2020 at 3:38 PM Graham Holden wrote: > Thursday, January 30, 2020, 12:24:40 PM, Dominique Devienne > wrote: > > The strange thing though, is that I can't repro on a small example. > > Despite using not_there in the trigger, and doing DML and ALTER TABLE, > > still doesn't fail the same way as in production. What could be the cause? > > --DD > > I suspect it may only kick-in if you use ALTER TABLE to rename either > a table or column. As I understand it, the old behaviour was to JUST > rename the table/column; the new behaviour also scan triggers etc. and > renames any references to the table/column as well. Since a new column > cannot (shouldn't?) be referenced by a trigger, there's no real need > to check them. Good point. Confirmed below it seems. Thanks Graham. --DD 3.19.3 happily renamed the table, and the trigger is still invalid. 3.30.1 error'd out with: Error: error in trigger t2_on_update_ko: no such column: OLD.not_there Console#1 on 3.19.3 sqlite> select name, sql from sqlite_master where type = 'trigger'; t2_on_insert_ok|CREATE TRIGGER t2_on_insert_ok after insert on t2 begin insert into t2bis(id,v) values(NEW.id, NEW.v); END t2_on_update_ko|CREATE TRIGGER t2_on_update_ko after update of not_there on t2 when OLD.not_there != NEW.not_there begin update t2bis set v=NEW.v where id=OLD.id; END sqlite> alter table t2 rename to t2a; sqlite> select name, sql from sqlite_master where type = 'trigger'; t2_on_insert_ok|CREATE TRIGGER t2_on_insert_ok after insert on "t2a" begin insert into t2bis(id,v) values(NEW.id, NEW.v); END t2_on_update_ko|CREATE TRIGGER t2_on_update_ko after update of not_there on "t2a" when OLD.not_there != NEW.not_there begin update t2bis set v=NEW.v where id=OLD.id; END sqlite> Console#2 on 3.30.1 sqlite> select name, sql from sqlite_master where type = 'trigger'; t2_on_insert_ok|CREATE TRIGGER t2_on_insert_ok after insert on "t2a" begin insert into t2bis(id,v) values(NEW.id, NEW.v); END t2_on_update_ko|CREATE TRIGGER t2_on_update_ko after update of not_there on "t2a" when OLD.not_there != NEW.not_there begin update t2bis set v=NEW.v where id=OLD.id; END sqlite> alter table t2a rename to t2b; Error: error in trigger t2_on_update_ko: no such column: OLD.not_there sqlite> select name, sql from sqlite_master where type = 'trigger'; t2_on_insert_ok|CREATE TRIGGER t2_on_insert_ok after insert on "t2a" begin insert into t2bis(id,v) values(NEW.id, NEW.v); END t2_on_update_ko|CREATE TRIGGER t2_on_update_ko after update of not_there on "t2a" when OLD.not_there != NEW.not_there begin update t2bis set v=NEW.v where id=OLD.id; END sqlite> ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Behavior change around triggers between 3.19 and 3.30
Thursday, January 30, 2020, 12:24:40 PM, Dominique Devienne wrote: > The strange thing though, is that I can't repro on a small example. > Despite using not_there in the trigger, and doing DML and ALTER TABLE, > still doesn't fail the same way as in production. What could be the cause? > --DD I suspect it may only kick-in if you use ALTER TABLE to rename either a table or column. As I understand it, the old behaviour was to JUST rename the table/column; the new behaviour also scan triggers etc. and renames any references to the table/column as well. Since a new column cannot (shouldn't?) be referenced by a trigger, there's no real need to check them. Graham ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Does .commit() ensure the .execute()'s and .executemany()'s called before are run atomically?
-Original Message-
From: sqlite-users On Behalf Of
Peng Yu
Sent: Thursday, January 30, 2020 5:16 AM
To: SQLite mailing list
Subject: Re: [sqlite] Does .commit() ensure the .execute()'s and
.executemany()'s called before are run atomically?
Could you show a python example on how to make multiple entries either
being all inserted (each entry done by an insert statement) or none on any
error (e.g. ctrl-c)? I also want want to make sure no two python processes
simultaneously editing these entries.
I am not sure I fully understand how to implement this correctly.
Using the manual transactions (isolation_level = None) it might look something
along the lines of this:
import sqlite3
import contextlib
conn = sqlite3.connect(myFile, isolation_level = None)
try:
with contextlib.closing(conn.cursor()) as cur:
#stuff
cur.execute("begin;")
try:
for thing in otherThing:
cur.execute("insert into myTable values (?,?,?);", thing)
except:
conn.rollback()
print("Error message here")
return someCode #or re-raise the exception
else:
conn.commit()
finally:
conn.close()
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Where is the journal file for locking_mode=EXCLUSIVE?
> Le 30 janv. 2020 à 15:05, Peng Yu a écrit : > > https://www.sqlite.org/tempfiles.html > > The above page says that there should be a journal file. > > "The PERSIST journal mode foregoes the deletion of the journal file > and instead overwrites the rollback journal header with zeros, which > prevents other processes from rolling back the journal and thus has > the same effect as deleting the journal file, though without the > expense of actually removing the file from disk." > > But I don't see it. Does anybody why I don't see the journal file? > What does "normal" printed mean? > > $ sqlite3 /tmp/dbfile -header -separator $'\t' < pragma locking_mode=persist; Minor confusion between the pragma locking_mode and pragma journal_mode. You want pragma journal_mode=persist here, not pragma locking_mode=persist, which does not exists. ⏤ Best Regards, Meilleures salutations, Met vriendelijke groeten, Mit freundlichen Grüßen, Olivier Mascia ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Where is the journal file for locking_mode=EXCLUSIVE?
https://www.sqlite.org/tempfiles.html The above page says that there should be a journal file. "The PERSIST journal mode foregoes the deletion of the journal file and instead overwrites the rollback journal header with zeros, which prevents other processes from rolling back the journal and thus has the same effect as deleting the journal file, though without the expense of actually removing the file from disk." But I don't see it. Does anybody why I don't see the journal file? What does "normal" printed mean? $ sqlite3 /tmp/dbfile -header -separator $'\t'
Re: [sqlite] Behavior change around triggers between 3.19 and 3.30
On Thu, Jan 30, 2020 at 1:09 PM Richard Hipp wrote: > On 1/30/20, Dominique Devienne wrote: > > My first question would be to ask whether there's a pragma or > > compile-time option to get back to the old behavior? > > Did you try "PRAGMA legacy_alter_table=ON;"? BINGO!!! Thanks a bunch Richard. > > Second, any idea when this was introduced? > People have been requesting enhanced ALTER TABLE support. In order to > provide that, we had to change ALTER TABLE to do a full parse of the > entire schema, so that it can find all of the bits and pieces that > need altering. This means that ALTER TABLE now also finds latent > syntax errors in the schema. The strange thing though, is that I can't repro on a small example. Despite using not_there in the trigger, and doing DML and ALTER TABLE, still doesn't fail the same way as in production. What could be the cause? --DD Console #1 c:\Users\ddevienne\SQLite>sqlite3 with-3.19.3.db SQLite version 3.19.3 2017-06-08 14:26:16 Enter ".help" for usage hints. sqlite> create table t1(v); sqlite> create table t2(id INTEGER PRIMARY KEY, v); sqlite> create table t2bis(id INTEGER PRIMARY KEY, v); sqlite> create trigger t2_on_insert_ok after insert on t2 begin insert into t2bis(id,v) values(NEW.id, NEW.v); END; sqlite> create trigger t2_on_update_ko after update of not_there on t2 when OLD.not_there != NEW.not_there begin update t2bis set v=NEW.v where id=OLD.id; END; sqlite> .exit c:\Users\ddevienne\SQLite>sqlite3 with-3.19.3.db SQLite version 3.19.3 2017-06-08 14:26:16 Enter ".help" for usage hints. sqlite> select name, type from sqlite_master; t1|table t2|table t2bis|table t2_on_insert_ok|trigger t2_on_update_ko|trigger sqlite> sqlite> insert into t1(v) values (1); sqlite> insert into t2(v) values (1, 'one'); Error: 2 values for 1 columns sqlite> insert into t2(id, v) values (1, 'one'); sqlite> update t2 set v = 'uno' where id = 1; sqlite> alter table t1 add column v2; sqlite> alter table t2 add column v2; sqlite> pragma legacy_alter_table; sqlite> Console #2 C:\Users\ddevienne\SQLite>sqlite3 with-3.19.3.db SQLite version 3.30.1 2019-10-10 20:19:45 Enter ".help" for usage hints. sqlite> select name, type from sqlite_master; t1|table t2|table t2bis|table t2_on_insert_ok|trigger t2_on_update_ko|trigger sqlite> insert into t1(v) values (2); sqlite> insert into t2(id, v) values (2, 'two'); sqlite> update t2 set v = 'dos' where id = 2; sqlite> alter table t1 add column v3; sqlite> alter table t2 add column v3; sqlite> pragma legacy_alter_table; 0 sqlite> ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] What is a "journal"?
In programming a journal is a file or other data structure containing a series of change records but can be replayed to reconstruct an operation. On Thu, 30 Jan 2020, 05:29 Peng Yu, wrote: > Hi, > > https://www.sqlite.org/lockingv3.html#rollback > > "When a process wants to change a database file (and it is not in WAL > mode), it first records the original unchanged database content in a > rollback journal. The rollback journal is an ordinary disk file that > is always located in the same directory or folder as the database file > and has the same name as the database file with the addition of a > -journal suffix. The rollback journal also records the initial size of > the database so that if the database file grows it can be truncated > back to its original size on a rollback." > > I got confused about the above statement about the rollback journal. > The sentence "The rollback journal ..." appears to be a definition. > But it is not. > > I think that the following can be considered as a definition. Given > the above sentence appears in a section named "4.0 The Rollback > Journal". I think it should be changed to a definition to avoid > confusion. > > https://www.sqlite.org/tempfiles.html > > "A rollback journal is a temporary file used to implement atomic > commit and rollback capabilities in SQLite." > > Regarding the choice of the word "journal", the only relevant entry > about "journal" in the Oxford dictionary is this. Is "journal" just a > fancy way of saying "record file"? Thanks. > > """ > 2 a daily record of news and events of a personal nature; a diary. > • Nautical a logbook. > • (the Journals) a record of the daily proceedings in the British > Houses of Parliament. > • (in bookkeeping) a daily record of business transactions with a > statement of the accounts to which each is to be debited and credited. > """ > > -- > 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] Behavior change around triggers between 3.19 and 3.30
On 1/30/20, Dominique Devienne wrote: > > My first question would be to ask whether there's a pragma or > compile-time option to get back to the old behavior? Did you try "PRAGMA legacy_alter_table=ON;"? > > Second, any idea when this was introduced? > People have been requesting enhanced ALTER TABLE support. In order to provide that, we had to change ALTER TABLE to do a full parse of the entire schema, so that it can find all of the bits and pieces that need altering. This means that ALTER TABLE now also finds latent syntax errors in the schema. -- 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] New word to replace "serverless"
On 29 Jan 2020, at 22:54, Brian Curley wrote: > The marketing buzzword usage will disappear... long before we’ll have the bike shed painted! /N ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] What is a "journal"?
Hi, https://www.sqlite.org/lockingv3.html#rollback "When a process wants to change a database file (and it is not in WAL mode), it first records the original unchanged database content in a rollback journal. The rollback journal is an ordinary disk file that is always located in the same directory or folder as the database file and has the same name as the database file with the addition of a -journal suffix. The rollback journal also records the initial size of the database so that if the database file grows it can be truncated back to its original size on a rollback." I got confused about the above statement about the rollback journal. The sentence "The rollback journal ..." appears to be a definition. But it is not. I think that the following can be considered as a definition. Given the above sentence appears in a section named "4.0 The Rollback Journal". I think it should be changed to a definition to avoid confusion. https://www.sqlite.org/tempfiles.html "A rollback journal is a temporary file used to implement atomic commit and rollback capabilities in SQLite." Regarding the choice of the word "journal", the only relevant entry about "journal" in the Oxford dictionary is this. Is "journal" just a fancy way of saying "record file"? Thanks. """ 2 a daily record of news and events of a personal nature; a diary. • Nautical a logbook. • (the Journals) a record of the daily proceedings in the British Houses of Parliament. • (in bookkeeping) a daily record of business transactions with a statement of the accounts to which each is to be debited and credited. """ -- Regards, Peng ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] New word to replace "serverless"
Am 28.01.2020 um 12:18 schrieb Richard Hipp: On 1/28/20, Howard Chu wrote: Wait, really? AFAICS embedded means in-process, no IPC required to operate. Things like MySQL-embedded and H2 run a "server" as a thread instead of as a separate process. Clients then use Inter-Thread Communication rather than Inter-Process Communication to send their queries to, and get their results from, the database thread. So this is really the same thing as a server using IPC except that the server runs in the same address space as the client. The point of using the term "serverless" is to indicate that SQLite does not work that way. I've always found "serverless" kinda "misleading" (technically). Technically, SQLite is "embeddable InProcess". And thus such a Process (similar to what was described for MySQL- embedded above), could "very well be a DB-Server itself". Your own Fossil-engine would be a good example for such a Server-App, since it allows (very server-like): - concurrent access of multiple Users via sockets... - to access and manage data, which is stored in an SQLite-DB-File I guess it boils down to "what amount of config- or coding-efforts" are needed, to produce/compile a true ServerProcess with the help of an embeddable "InProcess-Library". E.g. my COM-wrapper for SQLite is implemented as a Dll as well (and thus "embeddable InProcess") - but it allows with only two lines of UserCode (just starting a COM-Lib-internal, threaded socket- listener), to transform "any Host-Process" into an SQLite-Resultset- read/write-supporting AppServer (which in many concurrent scenarios outperforms MySQL and also the MS-SQLServer). With the WAL-extension SQLite is usable in many different (concurrent) Server-scenarios (and "Server-Processes") ...with a relative small amount of "extra-code". So in that sense, the word "serverless" kinda suggests that "special usecase only" - which is not (only), what SQLite is used for in reality. Olaf ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Does .commit() ensure the .execute()'s and .executemany()'s called before are run atomically?
Could you show a python example on how to make multiple entries either
being all inserted (each entry done by an insert statement) or none on any
error (e.g. ctrl-c)? I also want want to make sure no two python processes
simultaneously editing these entries.
I am not sure I fully understand how to implement this correctly.
On Thu, Jan 30, 2020 at 12:43 AM Keith Medcalf wrote:
>
> On Wednesday, 29 January, 2020 22:45, Peng Yu wrote:
>
> >In python sqlite3 program, if I call .execute() multiple times then
> >call .commit(). Does it ensure that all the sqlite3 commands specified
> >by execute()'s either all take effect or none effect?
>
> Mayhaps yes, mayhaps no. .commit() is merely syntactic sugar for
> .execute('COMMIT')
>
> Basically, the method does the following:
>
> if (sqlite3_get_autocommit(connection) == 0) {
>sqlite3_stmt* stmt = 0;
>sqlite3_prepare(connection, "COMMIT", -1, , NULL);
>if (stmt) {
> sqlite3_step(stmt);
> sqlite3_finalize(stmt);
>}
> }
>
> That is, if there is a transaction in progress on the connection, it does
> a commit. Mutatis mutandis for .rollback() doing a 'ROLLBACK' rather than
> commit.
>
> It makes sure that all changes made in the current transaction are
> committed to the database. If you are using "magical mode" then hopefully
> a transaction was started when you updated something however this is not
> guaranteed (such is the nature of magic). The "magical mode" of the
> sqlite3 wrapper decides when to BEGIN and COMMIT transactions on its own.
> Sometimes the magician is a little daffy though and gets it wrong so it may
> begin/commit/rollback whenever the magician feels like it, which may or may
> not be when you want.
>
> If you want to ensure that transactions BEGIN and COMMIT/ROLLBACK when
> *you* want them to then use explicit .execute('BEGIN') (or .execute('BEGIN
> IMMEDIATE') to start an immediate transaction) command to start a
> transaction on a connection opened with isolation_level=None. You can
> still use .commit() for .execute('COMMIT') and .rollback() for
> .execute('ROLLBAC') if you want. The connection has a property
> in_transaction that lets you test whether a transaction is in progress
> (True) or the database is in autocommit (False) mode.
>
> >In other words, if any error occurs while running the sqlite3 commands
> >specified in execute(), what happens?
>
> That depends on the nature of the error. If you put in a bad SQL
> statement (got a syntax error) then that statement did nothing. Otherwise
> it depends on the conflict resolution method in effect for the statement
> causing the error, which is usually ABORT (by default) and it just ABORTs
> the current statement.
>
> >Also, is .executemany() the same as calling .execute() many times?
>
> Yes. x.executemany(sql, stuff) is syntactic sugar for
>
> for e in stuff:
> x.execute(sql, e):
>
> --
> 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
[sqlite] Behavior change around triggers between 3.19 and 3.30
BEFORE 3.19.3 2017-06-08 14:26:16 0ee482a1e0eae22e08edc8978c9733a96603d4509645f348ebf55b579e89636b AFTER 3.30.1 2019-10-10 20:19:45 18db032d058f1436ce3dea84081f4ee5a0f2259ad97301d43c426bc7f3dfalt2 Every 18 to 24 months we upgrade SQLite in a large commercial software suite. Such a recent upgrade surfaced a major backward-compatibility issue in the custom upgrade mechanism of that software suite, when restoring old projects. The problem was traced back to a bad trigger definition, using a WHEN clause on an invalid column (that does NOT exist). It's of course trivial to get rid of that trigger in the latest version of the schema, but the fact the upgrade fails when processed using 3.30.1 is a major issue and new. The same upgrade, processed with 3.19.3, goes through without errors. The upgrade executes an ordered linear series of +1 version upgrade scripts (pretty common), and the first few pure-DDL scripts execute OK, but a subsequent upgrade script with DML fails, with an error about the non-existing column referenced by the trigger. That one error of course fails the whole custom upgrade process. My first question would be to ask whether there's a pragma or compile-time option to get back to the old behavior? Second, any idea when this was introduced? My guess would be that the first DML "triggers" the parsing of the schema, which discovers the invalid trigger definition, which starts failing in 3.30.1, while was "OK" in 3.19.1 (as in no errors reported). Is there anything we can do, short of reverting back to 3.19.3? Note that all the SQL is processed by the official shell, not via custom code. Also note we build from the amalgamation, mostly with default options, adding JSON1. Thanks for any help on the above. --DD ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Is it necessary to encode() for file names in sqlar format?
So to confirm. In python 3, the str type should be used for name? Thanks.
On Thu, Jan 30, 2020 at 12:58 AM Keith Medcalf wrote:
>
> sys.argv is a list of unicode text strings. There is no need to
> specifically encode or decode it so long as sys.getdefaultencoding()
> returns 'utf-8'. If your version of Python is so old that it returns
> something else then you need to modify site.py and have it set the default
> encoding to 'utf-8' otherwise you may end up with MBCS or some other
> invalid text encoding in your database text fields.
>
> --
> 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: Wednesday, 29 January, 2020 22:57
> >To: SQLite mailing list
> >Subject: [sqlite] Is it necessary to encode() for file names in sqlar
> >format?
> >
> >I use the following python3 code to create sqlar file. Is it necessary
> >to sys.argv[2].encode('utf-8') in the line of execute()? In other
> >word, does the native sqlar tools inteprete the name column as an
> >encoded value or a non-encode value? Thanks.
> >
> >import sqlite3
> >conn=sqlite3.connect(sys.argv[1])
> >c=conn.cursor()
> >c.execute('''
> >CREATE TABLE IF NOT EXISTS sqlar(
> >name TEXT PRIMARY KEY
> >, mode INT
> >, mtime INT
> >, sz INT
> >, data BLOB)
> >''')
> >
> >import zlib
> >data = sys.stdin.buffer.read()
> >c.execute('REPLACE INTO sqlar VALUES(?, ?, ?, ?, ?)', [sys.argv[2], 0,
> >0, len(data), zlib.compress(data)])
> >conn.commit()
> >
> >--
> >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
Re: [sqlite] New word to replace "serverless"
Jim Dodgen wrote: > I vote for ignoring the marketing types and stick with "serverless" The word is intended to communicate a specific meaning to readers. Ignoring that the marketing types have changed the common meaning of "serverless" will just lead to confusion. Originally, "serverless" was a plain description without jargon, so it can be replaced with any other description with exactly the same meaning, such as "server-free", "without server", or "sans-server". Regards, Clemens ___ 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 29 Jan 2020, at 14:59, Richard Hipp wrote:
>
> On 1/29/20, Markus Winand wrote:
>> Hi!
>>
>> 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.
>
> I think the current behavior is correct.
>
> If you want a column to have a non-standard collating sequence, you
> should add a COLLATE constraint to that column definition. The fact
> that there is a COLLATE operator on the expression that determines the
> value of that column seems irrelevant.
>
> Consider this:
>
> CREATE TABLE t1(a TEXT DEFAULT('xyzzy' COLLATE nocase));
>
> Would you expect the COLLATE operator in the DEFAULT clause to change
> the collating sequence associated with column a? Why should a
> GENERATED ALWAYS AS constraint work differently from a DEFAULT
> constraint?
The critical point here is that SQLite accepts a GENERATED clause without
explicit type:
CREATE TABLE tmp (str VARCHAR(255), str_nc1 GENERATED ALWAYS AS (str COLLATE
NOCASE), str_nc2 GENERATED ALWAYS AS (str) COLLATE NOCASE);
In that case, the type of the generated column should taken from the result
type of the expression[0]. As collations are part of the character string
types[1], also the collation is taken from the expression. The COLLATE clause
applied to expressions (as opposed to following the name of a data type) just
changes the collation in of the character string type of that expression[2].
If the GENERATED clause explicitly sets a data type, then the collation of the
expression is irrelevant as it doesn’t affect the type of the generated column,
similar to your example with DEFAULT (it is only a value assignment in that
case).
At the end everything boils down to this question:
What is the type, including the character set and collation, of generated
columns that don’t specify a type explicitly? I think the only sensible answer
is that it is the type of the expression, including its character set and
collation.
Unfortunately, the “what would PostgreSQL do” approach doesn’t provide guidance
here as PostgreSQL requires an explicit type for generated columns (and so do
MySQL and MariaDB). In SQL Server, however, it works like I would expect it.
I guess the SQLite approach to types might affect all of my reasoning, but in
other cases like the following it seems that SQLite also “transports” the
applicable collation together with the result (type?) of an expression:
sqlite> CREATE TABLE tmp (str VARCHAR(255));
sqlite> INSERT INTO tmp VALUES ('a'), ('A'), ('b'), ('B');
sqlite> SELECT * FROM (SELECT str COLLATE NOCASE FROM tmp) ORDER BY str;
a
A
b
B
-markus
References to SQL:2016, Part 2. I know that this is not a strong argument to
you, but nevertheless ;)
[0] 11.4 SR 13 c:
• If is specified, then the declared type of GE.
Whereas GE is defined in SR 10 a:
• Let GE be the contained in GC.
[1] 4.2.1
A character string type descriptor contains:
[….]
• — The catalog name, schema name, and collation name of the collation
of the character string type.
[2] 6.31 SR 4a:
• If is specified, then the declared type of the
is the declared type of the , except that
the declared type collation is the collation identified by ,
and its collation derivation is explicit.
>
> --
> D. Richard Hipp
> drh at sqlite.org
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
