Re: [sqlite] Site error

The error is back I think. I am getting same message. Also, since I have your attention, why not just make the search box always visible vs. having to click it. Seems like an unnessary step. Richard On Fri, Mar 16, 2018 at 1:03 PM Richard Hipp wrote: > On 3/16/18, David

Re: [sqlite] possible bug: select clause column alias shadowing

Jean-Christophe Deschamps wrote: > At 23:36 18/03/2018, you wrote: >> In other words, aliases in the SELECT clause are evaluated _after_ the >> FROM and WHERE clauses are done. > > I must be misinterpreting: I was talking about the SQL standard. (I might have mentioned that somewhere ...) >

Re: [sqlite] UPSERT

If you have multiple candidate keys for a single row that match more than one row (or the alternate candidate keys match different rows), your application should explode immediately! There is no need to "decide" which row is the correct one to update, you are already in a fatal error

[sqlite] sqlite server-process-edition and begin-concurrent

I am looking for a way to increase concurrency from within cubeSQL. I am highly interested in a couple of sqlite branches: - begin-concurrent seems interesting and updated - sqlite server-process-edition branch has been updated on August 2017 Based on documentation seems like that sqlite

Re: [sqlite] UPSERT

I would suggest using the PostgreSQL way: https://www.postgresql.org/docs/9.5/static/sql-insert.html INSERT INTO ... ON CONFLICT [()] DO UPDATE SET foo = ... , bar = ... ; This approach is really cool, because we can specify which key is more important and discard other conflicts as

Re: [sqlite] UPSERT

On 2018/03/19 1:50 PM, Olivier Mascia wrote: I don't know what any 'standard' SQL defines about this. I know that FirebirdSQL (where I came from, before meeting SQLite) did/does it this way: UPDATE OR INSERT INTO {tablename | viewname} [()] VALUES () [MATCHING ()] [RETURNING

Re: [sqlite] possible bug: select clause column alias shadowing

Compared to PostgreSQL, SQLite does a better job here when there is no input column collision. The column collision case below returns no rows in both SQLite and PostgreSQL: WITH t(a) AS (VALUES ('foo')) SELECT a||'!' AS a FROM t WHERE a='foo!'; But the following edit with intermediating alias

Re: [sqlite] How many AUTOINCREMENT tables are in your schema?

0 On Friday, March 16, 2018, Richard Hipp wrote: > This is a survey, the results of which will help us to make SQLite faster. > > How many tables in your schema(s) use AUTOINCREMENT? > > I just need a single integer, the count of uses of the AUTOINCREMENT > in your overall

Re: [sqlite] UPSERT

On 3/19/18, 3:37 AM, "sqlite-users on behalf of Paul" wrote: > Fort me personally, the most sad thing is an annoyance. Because I have to > maintain two almost identical queries and manually tweak strategies. I almost

[sqlite] Problem: FTS5 prefix search getting the mis-match result

Hi, When I use FTS5, I have met that, there are some cases which will get mis-match results with prefix search. Like "select * from tbl_fts where tbl_fts match 'lucy*';"，which I want to get records like "lucya","lucyabc" etc, and "lux" or "lulu" is not what I want but returned. Such problems are

Re: [sqlite] possible bug: select clause column alias shadowing

On 3/18/2018 5:31 AM, Moritz Bruder wrote: I just came across a strange behaviour in sqlite3 (3.22.0 2018-01-22 18:45:57 0c55d179733b46d8d0ba4d88e01a25e10677046ee3da1d5b1581e86726f2171d). Consider the following test case:     CREATE TABLE test (name varchar);     INSERT INTO test VALUES

Re: [sqlite] possible bug: select clause column alias shadowing

At 23:36 18/03/2018, you wrote: In other words, aliases in the SELECT clause are evaluated _after_ the FROM and WHERE clauses are done. The order of the SELECT/WHERE clauses in the SQL syntax is misleading; the actual behaviour would be better represented by something like this: ( FROM

Re: [sqlite] UPSERT

> Le 19 mars 2018 à 11:28, R Smith a écrit : > > On 2018/03/19 10:36 AM, Paul wrote: >> Fort me personally, the most sad thing is an annoyance. Because I have to >> maintain two almost identical queries and manually tweak strategies. > > I think that there presents the

Re: [sqlite] sqlite3_serialize / sqlite3_deserialize (3.23.0 draft)

On Sun, Mar 18, 2018 at 4:48 PM, Richard Hipp wrote: > These APIs support the concept of using small databases (small enough > to fit in memory) as a container for passing information around. I very much like the concept. Thank you for this addition. But then, this is begging

Re: [sqlite] UPSERT

On 2018/03/19 10:36 AM, Paul wrote: Fort me personally, the most sad thing is an annoyance. Because I have to maintain two almost identical queries and manually tweak strategies. I think that there presents the entire UPSERT argument's only real motivation, because it really is negligible

Re: [sqlite] [EXTERNAL] Lazy virtual table creation

Interesting. How do you discern between "names of virtual tables that are not yet loaded" and "names of virtual tables that do not exist"? We have two strategies here: 1) "Cloned" general tables: These have identical structures, but contents is partitioned by one or more fields. The backing

Re: [sqlite] How many AUTOINCREMENT tables are in your schema?

On Fri, Mar 16, 2018 at 4:37 PM, Richard Hipp wrote: > This is a survey, the results of which will help us to make SQLite faster. > > How many tables in your schema(s) use AUTOINCREMENT? > > I just need a single integer, the count of uses of the AUTOINCREMENT > in your overall

Re: [sqlite] [EXTERNAL] Re: Lazy virtual table creation

IIRC it is NOT safe to call sqlite3_prepare() or sqlite3_step() withtin the authorizer callback, so schema changes are out of the question, as you would have to prepare/step a "CREATE VIRTUAL TABLE" statement for on-the-fly virtual table creation. "The authorizer callback must not do anything

Re: [sqlite] Lazy virtual table creation

On Sat, Mar 17, 2018 at 11:42 PM, Marco Bambini wrote: > with a bit of work you can use the authorize api in order to know when an > access to a non existing table is performed. > https://sqlite.org/c3ref/set_authorizer.html Interesting work-around, if that works. I.e.

Re: [sqlite] UPSERT

19 March 2018, 09:26:15, by "Rowan Worth" : > On 16 March 2018 at 21:44, Paul <> de...@ukr.net> wrote: > > > A few years back I've been asking the same question. To be honest, there's > > no more > > efficient alternative, than the one that can be implemented within library > >

Re: [sqlite] How many AUTOINCREMENT tables are in your schema?

1 very important one. I use it to insert events into a queue with a version and the version has to monotonously increase. However, if I had to maintain the known max manually, that wouldn't really be a problem. On Mon, Mar 19, 2018, 3:52 AM Rowan Worth, wrote: > 5/10 > 1/11 >