Re: [sqlite] CASE and NULL
On 5 Jul 2018, at 6:22am, Andy Goth wrote: > Or equivalently, "ISNULL" instead of "IS NULL". There is no ISNULL in sqlite3. Also, the functions ifnull() and nullif() are not useful for actually testing for NULL. Continuing to eliminate options, regular expressions do not match with NULL in a useful manner. As your examples show, you are reduced to variations on CASE WHEN x IS NULL THEN ... ELSE ... END > CASE age > WHEN < 1 THEN 'baby' > WHEN < 3 THEN 'toddler' > WHEN < 5 THEN 'preschooler' > WHEN < 12 THEN 'gradeschooler' > WHEN < 18 THEN 'teenager' > WHEN < 21 THEN 'young adult' > ELSE 'adult' END You can replace this with a table lookup. Create a table like the following CREATE TABLE AgeNames (age INTEGER UNIQUE, name TEXT COLLATE NOCASE); INSERT INTO AgeNames (1, 'baby'),(3,'toddler'),(5 ... then do a SELECT looking for SELECT name FROM AgeNames WHERE ?1 >= age ORDER BY rowid LIMIT 1 You will have to find a value for "age" which deals with the case where you're looking up NULL. It might work just to set "age" to NULL. Similar things can be done with the other CASE statements that match on numbers. Simon. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] CASE and NULL
I'd like to use CASE to compare an expression x against a number of candidate values. That's the typical use for "CASE x WHEN", which avoids repeating x for each condition. The trouble is that one of the possible values is NULL, yet the comparison against each candidate value is done with the = operator. The expression "x = NULL" is meaningless since it will always evaluate to NULL, which CASE interprets as untruth, hence "WHEN NULL THEN" will never accomplish anything. The workaround is to not use "CASE x WHEN" and instead use "CASE WHEN" and repeat x every time, using = for all non-NULL values and IS for NULL. But this means repeating x for each condition, which is the whole reason I'd prefer "CASE x WHEN". A compromise is to do both, as follows: CASE x WHEN 1 THEN 11 WHEN 2 THEN 22 WHEN 3 THEN 33 WHEN 4 THEN 44 ELSE CASE WHEN x IS NULL THEN 55 ELSE 66 END END Or the other way around, so that both instances of x are near each other: CASE WHEN x IS NULL THEN 55 ELSE CASE x WHEN 1 THEN 11 WHEN 2 THEN 22 WHEN 3 THEN 33 WHEN 4 THEN 44 ELSE 66 END END If the CASE statement enumerates all possible values of x (whose range is perhaps guaranteed with a CHECK or FOREIGN KEY constraint), then the ELSE case can stand in for the NULL comparison. But otherwise, the ELSE case will unavoidably collect not only NULL but any other unhandled values. So I'm wondering: can we do better? I wouldn't want to risk changing the meaning of any existing queries, but it is generally possible to extend from the error space: take something that's currently a syntax error and give it meaning. How about the following? CASE x WHEN 1 THEN 11 WHEN 2 THEN 22 WHEN 3 THEN 33 WHEN 4 THEN 44 WHEN IS NULL THEN 55 ELSE 66 END Or equivalently, "ISNULL" instead of "IS NULL". This treatment could also be applied to numerous other operators that take an expression as their left-hand side and produce a truth result: ?NOT? LIKE|GLOB|REGEXP|MATCH expr ?ESCAPE expr? NOTNULL NOT NULL < <= > >= != <> = == (for completeness, even though it's implied) ?NOT? BETWEEN expr AND expr ?NOT? IN list-generation-expression Giving us syntax such as: CREATE TABLE fruits (name); [... veggies ... meats ...] CASE food WHEN IN fruits THEN 'fruit' WHEN IN veggies THEN 'veggie' WHEN IN meats THEN 'meat' WHEN ISNULL THEN 'unspecified' ELSE 'candy' END CASE filename WHEN GLOB '.*' THEN 'hidden' WHEN GLOB '*.png' THEN 'image' WHEN GLOB '*.html' THEN 'webpage' WHEN REGEXP '\.docx?$' THEN 'MS-Word' ELSE 'data' END CASE age WHEN < 1 THEN 'baby' WHEN < 3 THEN 'toddler' WHEN < 5 THEN 'preschooler' WHEN < 12 THEN 'gradeschooler' WHEN < 18 THEN 'teenager' WHEN < 21 THEN 'young adult' ELSE 'adult' END CASE hour WHEN BETWEEN 6 AND 6.5 THEN 'wake' WHEN BETWEEN 7 AND 7.5 THEN 'breakfast' WHEN BETWEEN 8 AND 8.5 THEN 'commute' WHEN BETWEEN 11.5 AND 12.5 THEN 'lunch' WHEN BETWEEN 9 AND 17 THEN 'work' WHEN BETWEEN 17.5 AND 18 THEN 'commute' WHEN BETWEEN 19 AND 19.5 THEN 'dinner' WHEN BETWEEN 22 AND 24 THEN 'sleep' WHEN BETWEEN 0 AND 6 THEN 'sleep' END To make the above examples more compelling, replace food, filename, age, and hour with complex expressions such as nested queries. The next evolution in bloat is to also support AND, OR, NOT, and parentheses, allowing the LHS operand of any operator in a complex expression to be omitted, defaulting to CASE's first argument. In the last example above this would allow the two "sleep" cases (or the "commute" cases) to be combined with OR: "WHEN BETWEEN 22 AND 24 OR BETWEEN 0 and 6 THEN 'sleep'". But I imagine this would complicate the parser far beyond any practical benefit. -- Andy Goth | ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Automatic numbering
2018-07-05 5:37 GMT+02:00 Simon Slavin : > On 5 Jul 2018, at 3:22am, Cecil Westerhof wrote: > > > I only want to store a date with a record. But it is possible that more > as > > one record will be inserted, so I want to use another field to use as an > > index. So that the first gets an one, the second a two, etc. > > Is this possible, or do I just have to check if there is already a date > and > > fetch the highest index and increase this with one? > > There's no magical shortcut. > > I would create an index on (theDate, dateEventNumber). Then do > > BEGIN > SELECT MAX(dateEventNumber) FROM MyTable WHERE theDate = ?1 > [ in your code see whether you got NULL back, substitute 0 ] > INERT INTO MyTable ... ?1 + 1 > COMMIT > > You can combine the two commands into one more complicated thing, but I'd > do that only if I was sure nobody would ever have to figure out why my code > wasn't working. > OK, thank you. I am going to play with it. -- Cecil Westerhof ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Automatic numbering
On 5 Jul 2018, at 3:22am, Cecil Westerhof wrote: > I only want to store a date with a record. But it is possible that more as > one record will be inserted, so I want to use another field to use as an > index. So that the first gets an one, the second a two, etc. > Is this possible, or do I just have to check if there is already a date and > fetch the highest index and increase this with one? There's no magical shortcut. I would create an index on (theDate, dateEventNumber). Then do BEGIN SELECT MAX(dateEventNumber) FROM MyTable WHERE theDate = ?1 [ in your code see whether you got NULL back, substitute 0 ] INERT INTO MyTable ... ?1 + 1 COMMIT You can combine the two commands into one more complicated thing, but I'd do that only if I was sure nobody would ever have to figure out why my code wasn't working. Simon. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Automatic numbering
I only want to store a date with a record. But it is possible that more as one record will be inserted, so I want to use another field to use as an index. So that the first gets an one, the second a two, etc. Is this possible, or do I just have to check if there is already a date and fetch the highest index and increase this with one? -- Cecil Westerhof ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] 3.24 database table is locked
On 7/4/18, Wojtek Mamrak wrote: > Creating a separate connection for the SELECTs does not seem to solve > the problem. What is the preferred way of tackling such a scenario? I > guarded all the calls with a mutex, but it did not help. The change was a bug fix. Any write to an r-tree might cause the parts of the r-tree to be reorganized. If that where to happen while another thread where reading from the part being reorganized, incorrect answers might result. You can probably work around the problem by adding something like "ORDER BY +rowid" to each query against the r-tree. The "ORDER BY +rowid" will force the query against the rtree to run to completion on the first call too sqlite3_step(), storing the results in temporary storage (for sorting). Then result rows will be handed out via subsequent sqlite3_step() calls from temporary storage, rather than from cursors on the rtree. This approach ensures that there are no read cursors on rtree tables when they are written. -- 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] 3.24 database table is locked
Actually, there might be some unreset SELECT while inserting new records. I will investigate how to ensure it is not the case. Thanks for help! czw., 5 lip 2018 o 02:10 Wojtek Mamrak napisał(a): > > Yes. Yes. > czw., 5 lip 2018 o 00:28 Simon Slavin napisał(a): > > > > > > > > On 4 Jul 2018, at 11:03pm, Wojtek Mamrak wrote: > > > > > Creating a separate connection for the SELECTs does not seem to solve > > > the problem. What is the preferred way of tackling such a scenario? I > > > guarded all the calls with a mutex, but it did not help. > > > > Do you actually need to make changes while you have an active SELECT ? Are > > you using _reset() or _finalize() on your SELECT when you're done with it ? > > > > Simon. > > ___ > > 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] 3.24 database table is locked
Yes. Yes. czw., 5 lip 2018 o 00:28 Simon Slavin napisał(a): > > > > On 4 Jul 2018, at 11:03pm, Wojtek Mamrak wrote: > > > Creating a separate connection for the SELECTs does not seem to solve > > the problem. What is the preferred way of tackling such a scenario? I > > guarded all the calls with a mutex, but it did not help. > > Do you actually need to make changes while you have an active SELECT ? Are > you using _reset() or _finalize() on your SELECT when you're done with it ? > > Simon. > ___ > 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] 3.24 database table is locked
On 4 Jul 2018, at 11:03pm, Wojtek Mamrak wrote: > Creating a separate connection for the SELECTs does not seem to solve > the problem. What is the preferred way of tackling such a scenario? I > guarded all the calls with a mutex, but it did not help. Do you actually need to make changes while you have an active SELECT ? Are you using _reset() or _finalize() on your SELECT when you're done with it ? Simon. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] 3.24 database table is locked
Creating a separate connection for the SELECTs does not seem to solve the problem. What is the preferred way of tackling such a scenario? I guarded all the calls with a mutex, but it did not help. śr., 4 lip 2018 o 19:34 Wojtek Mamrak napisał(a): > > That is the case, thank you! > śr., 4 lip 2018 o 18:15 Dan Kennedy napisał(a): > > > > On 07/04/2018 11:06 PM, Wojtek Mamrak wrote: > > > Hello, > > > > > > I have encountered a significant change in behavior between versions > > > 3.24 and 3.23. > > > I have compiled SQLite using msvc 15.7.4 with SQLITE_THREADSAFE and I > > > am setting threading mode option to SQLITE_CONFIG_SERIALIZED before > > > initializing SQLite. I have a single database connection handle which > > > is used by several threads which insert records into a single table. > > > Up to version 3.23 there were no issues with this approach, even for > > > multiple working threads. In version 3.24 I am getting a "database > > > table is locked" error. Strangely enough, many times it happens during > > > the first INSERT execution. The insert takes place in an insert > > > trigger, and records are added to an rtree_i32 table. > > > > There was this change: > > > >https://sqlite.org/src/info/d4ce66610851c825 > > > > R-tree now prevents you from writing if the same connection currently > > has an active SELECT on the same r-tree table. If you don't think this > > is the case (if your app doesn't have an active SELECT) it might be a > > bug. In that case can you post the database schema and the SQL statement > > being executed? > > > > Dan. > > > > > > > > > > > > > > regards > > > ___ > > > sqlite-users mailing list > > > sqlite-users@mailinglists.sqlite.org > > > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > > > > > > ___ > > sqlite-users mailing list > > sqlite-users@mailinglists.sqlite.org > > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Idea: defining table-valued functions directly in SQL
Revisiting this topic...
On 06/10/18 08:04, sql...@zzo38computer.org wrote:
* Perhaps move PARAMETERS before AS, which may make the syntax easier.
Like so?
CREATE VIEW double PARAMETERS (arg) AS SELECT arg * 2;
SELECT * FROM numbers, double(x);
This is a readability improvement because universally I see function
names and parameters defined before function bodies. Moving the
PARAMETERS to the left of AS also represents a conceptual shift from
PARAMETERS modifying SELECT (which is what I had in mind) to PARAMETERS
modifying CREATE VIEW (which could well be a better way to look at it).
Now, let's examine the common table expression variant. I'll repeat the
baseline syntax proposal from my original post:
WITH double AS (SELECT arg * 2 PARAMETERS (arg))
SELECT * FROM numbers, double(x);
Would your suggestion be the following?
WITH double PARAMETERS (arg) AS (SELECT arg * 2)
SELECT * FROM numbers, double(x);
Next, what is the interaction with an explicit column-name list? Does
the PARAMETERS clause come before or after that? Compare:
CREATE VIEW double (result) PARAMETERS (arg) AS SELECT arg * 2;
SELECT * FROM numbers, double(x);
WITH double (result) PARAMETERS (arg) AS (SELECT arg * 2)
SELECT * FROM numbers, double(x);
Versus:
CREATE VIEW double PARAMETERS (arg) (result) AS SELECT arg * 2;
SELECT * FROM numbers, double(x);
WITH double PARAMETERS (arg) (result) AS (SELECT arg * 2)
SELECT * FROM numbers, double(x);
I don't think there's any question the former is superior, but I bring
this up for two reasons. One, every syntax question needs an answer.
Two, I wish to highlight the fact that the (existing) syntax for
column-name list looks very much like what most languages use for a
parameter list, so there's potential confusion, hence the need for the
PARAMETERS token.
* I do agree that defining table-valued functions in these way can be
useful though; I have wanted to define views that take parameters
before, and was unable to.
I would love to be able to create functions without writing extensions
in C, plus this way functions won't require recursive invocation of
SQLite and won't have to be a barrier to the SQLite optimizer. Rather,
native functions would be inlined right into the bytecodes of whichever
queries use them.
* Another (separate) idea can be "CREATE FUNCTION name(args) AS
select_stmt;" to define your own function. [...] Both of these are
separate from table-valued functions (parameterized views) though.
Aside from the syntax, is this really a separate idea? What does this
do that views can't?
If you write "CREATE AGGREGATE FUNCTION" then the function name can be
used as a table name within the select_stmt.
I don't think I understand the part about letting the function name be
used as a table name. Parameters would already be bound, so there's no
need for a FROM clause to get at them. That's central to the concept of
parameters as explored by this email thread; I'm hunting for a practical
use for situations that would currently give a "no such column" error.
However, you bring up an interesting question, though it's a potential
issue whether or not the function is an aggregate function. What if a
parameter name happens to match a column name in one (or more) of the
tables being pulled in by a FROM clause? A table name qualifier is
needed to disambiguate. It could be the function name, though it could
be clearer to do like upsert ("excluded") and have a special token, e.g.
"parameters". (cf. https://sqlite.org/lang_UPSERT.html)
The above applies to parameterized views and common table expressions as
well, so replace "function name" with "view name" or "common table
expression name".
As for having to declare a function as an aggregate, I think that could
be inferred from the fact that the function uses aggregate functions on
its parameters. Maybe it would have to also not use GROUP BY on those
parameters, not sure there. What makes an aggregate function, anyway?
It always map multiple input rows to a single output row? Then does
that mean having LIMIT 1 would also make it an aggregate function? But
before we get bogged down in semantics, I ask whether or not this
distinction even matters.
--
Andy Goth |
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] 3.24 database table is locked
That is the case, thank you! śr., 4 lip 2018 o 18:15 Dan Kennedy napisał(a): > > On 07/04/2018 11:06 PM, Wojtek Mamrak wrote: > > Hello, > > > > I have encountered a significant change in behavior between versions > > 3.24 and 3.23. > > I have compiled SQLite using msvc 15.7.4 with SQLITE_THREADSAFE and I > > am setting threading mode option to SQLITE_CONFIG_SERIALIZED before > > initializing SQLite. I have a single database connection handle which > > is used by several threads which insert records into a single table. > > Up to version 3.23 there were no issues with this approach, even for > > multiple working threads. In version 3.24 I am getting a "database > > table is locked" error. Strangely enough, many times it happens during > > the first INSERT execution. The insert takes place in an insert > > trigger, and records are added to an rtree_i32 table. > > There was this change: > >https://sqlite.org/src/info/d4ce66610851c825 > > R-tree now prevents you from writing if the same connection currently > has an active SELECT on the same r-tree table. If you don't think this > is the case (if your app doesn't have an active SELECT) it might be a > bug. In that case can you post the database schema and the SQL statement > being executed? > > Dan. > > > > > > > > regards > > ___ > > sqlite-users mailing list > > sqlite-users@mailinglists.sqlite.org > > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > > > ___ > sqlite-users mailing list > sqlite-users@mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] 3.24 database table is locked
On 07/04/2018 11:06 PM, Wojtek Mamrak wrote: Hello, I have encountered a significant change in behavior between versions 3.24 and 3.23. I have compiled SQLite using msvc 15.7.4 with SQLITE_THREADSAFE and I am setting threading mode option to SQLITE_CONFIG_SERIALIZED before initializing SQLite. I have a single database connection handle which is used by several threads which insert records into a single table. Up to version 3.23 there were no issues with this approach, even for multiple working threads. In version 3.24 I am getting a "database table is locked" error. Strangely enough, many times it happens during the first INSERT execution. The insert takes place in an insert trigger, and records are added to an rtree_i32 table. There was this change: https://sqlite.org/src/info/d4ce66610851c825 R-tree now prevents you from writing if the same connection currently has an active SELECT on the same r-tree table. If you don't think this is the case (if your app doesn't have an active SELECT) it might be a bug. In that case can you post the database schema and the SQL statement being executed? Dan. regards ___ 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] 3.24 database table is locked
Hello, I have encountered a significant change in behavior between versions 3.24 and 3.23. I have compiled SQLite using msvc 15.7.4 with SQLITE_THREADSAFE and I am setting threading mode option to SQLITE_CONFIG_SERIALIZED before initializing SQLite. I have a single database connection handle which is used by several threads which insert records into a single table. Up to version 3.23 there were no issues with this approach, even for multiple working threads. In version 3.24 I am getting a "database table is locked" error. Strangely enough, many times it happens during the first INSERT execution. The insert takes place in an insert trigger, and records are added to an rtree_i32 table. regards ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] [EXTERNAL] documentation for sqlite3_index_info-ídxStr typo.
Indeed SQLite does not care about what the VT implementations xBestIndex routine stuffs in there, as long as the xFilter routine can handle it. AFA SQLite is concerned, this is an opaque value of pointer size. -Ursprüngliche Nachricht- Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im Auftrag von Ben Manashirov Gesendet: Dienstag, 03. Juli 2018 02:47 An: sqlite-users@mailinglists.sqlite.org Betreff: [EXTERNAL] [sqlite] documentation for sqlite3_index_info-ídxStr typo. https://www.sqlite.org/c3ref/index_info.html Instead of referring to idxStr there is text that talks about idxPtr. It should mention if idxStr must point to a valid string or can it point to arbitrary data. If the latter than idxStr type should change to void* perhaps. Reading this doc it sounds like idxStr can point to anything and sqlite will treat it as arbitrary data instead of a string. Happy Canada Day :) Ben Manashirov ___ 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
[sqlite] (no subject)
___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] documentation for sqlite3_index_info-ídxStr typo.
https://www.sqlite.org/c3ref/index_info.html Instead of referring to idxStr there is text that talks about idxPtr. It should mention if idxStr must point to a valid string or can it point to arbitrary data. If the latter than idxStr type should change to void* perhaps. Reading this doc it sounds like idxStr can point to anything and sqlite will treat it as arbitrary data instead of a string. Happy Canada Day :) Ben Manashirov ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
