Re: [sqlite] How many AUTOINCREMENT tables are in your schema?
When needed I use a declared INTEGER PRIMARY KEY. > > MAYBE THAT WOULD HAVE BEEN IN THE SURVEY TOO BUT I GUESS THERE WAS NO WAY TO INCLUDE A SMALL PIECE OF SQL TO RELIABLY CHECK FOR INTEGER PRIMARY KEY YES I AM SHOUTING ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
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 Raymond wrote: > > Getting same error message trying to search today. > > Fixed. > > Apparently one of the automatic site-update scripts is messing up the > file permissions on the full-text search databases... > > -- > 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] 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
...)
> select a int, printf('<%5i>', a) fmt from t where fmt like '%>';
>
> Here WHERE understands what fmt refers to.
SQLite tries to be helpful. But when in doubt (i.e., when an alias tries to
shadow a real column), it chooses the standard-conforming interpretation.
Regards,
Clemens
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
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 situation and need to revisit your database design (it
is probably insufficiently normalized) and already self-inconsistent and
suffering update anomalies (or you are treating a pseudo-key as a candidate
key, which for the purposes of UPDATE or INSERT it is not -- the rowid is a
pseudo-key -- (one of/any of) the "other" candidate keys in the row is the true
primary key).
CREATE TABLE foo
(
idinteger primary key,
foo_key text not null unique,
some_data blob
);
SAVEPOINT UpdateFoo;
UPDATE foo
SET some_data = :some_data
WHERE foo_key = :foo_key;
INSERT OR
IGNORE INTO foo (foo_key, some_data)
VALUES (:foo_key, :some_data);
SELECT id
FROM foo
WHERE foo_key = :foo_key;
RELEASE UpdateFoo;
---
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 [mailto:sqlite-users-
>boun...@mailinglists.sqlite.org] On Behalf Of Paul
>Sent: Monday, 19 March, 2018 11:08
>To: SQLite mailing list
>Subject: 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 an error. For example, given
>the following table:
>
>CREATE TABLE foo(
>idINTEGER NOT NULL,
>foo_key TEXT NOT NULL,
>some_data TEXT,
>
>PRIMARY KEY(id),
>UNIQUE (foo_key)
>);
>
>INSERT INTO foo(id, foo_key, some_data) VALUES(1, "XXX", "...");
>INSERT INTO foo(id, foo_key, some_data) VALUES(2, "YYY", "...");
>
>If we are performing a query:
>
>
>INSERT INTO foo(id, foo_key, some_data) VALUES(1, "YYY", "...")
>
>
>Which record should we update and what columns?
>
>Having the ability to specify a specific column on which the conflict
>is actually an acceptable event lets the developer to make a decision
>how to resolve it:
>
>INSERT INTO foo(id, foo_key, some_data) VALUES(1, "YYY", "...")
>
>
>
>19 March 2018, 18:41:34, by "R Smith" :
>
>> 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 [INTO ]]
>>
>> Quite right, and the statement in MSSQL is even more convoluted,
>which,
>> if it was in SQLite like this, would require a dynamically created
>SQL
>> statement that is worse than simply computing an UPDATE and an
>INSERT -
>> which a previous poster already lamented.
>>
>> My suggestion for UPSERT would be the very simple already SQLite-
>like
>> syntax of:
>>
>> INSERT OR UPDATE INTO t (k1, k2, ... , kn, f1, f2, ... , fn)
>> followed by the usual VALUES clause or SELECT query.
>>
>> Any record found to exist with the exact same value in the Primary
>Key
>> field(s) [ k1 .. kn ] has all other fields (that are NOT Primary
>Key
>> fields) updated to the new values, and if no such record is found,
>the
>> row simply gets inserted. If the inserted row OR updated values
>cause
>> any other constraint to break, then FAIL hard, the same way (and
>> possibly with the same ON CONFLICT options) as any other single
>INSERT
>> or UPDATE would be subjected to.
>>
>> This is far better than INSERT OR REPLACE since there is no delete,
>and
>> no multiple-row delete on constraint violations.
>> It is simple in terms of converting any current INSERT OR REPLACE
>query
>> to an INSERT OR UPDATE query requires changing 1 word only.
>>
>> Triggers should fire for ON INSERT and ON UPDATE according to
>whatever
>> actually is required during the operation.
>>
>> Adding this has no backward compatibility to break, this did not
>exist
>> before and it is not schema-specific.
>>
>>
>> One possible added refinement might be an optional second field-
>group
>> that should be ignored over-and-above the PK fields during the
>UPDATE.
>> (During the INSERT of course all fields MUST be added).
>>
>> 2 ways this can be done easily:
>>
>> A - Use a separate 2nd prototype group for Non-Updating fields,
>Like
>> this perhaps:
>>
>> INSERT OR UPDATE INTO t (pk1, pk2, f1, f2, f3, f4) NOT (f1, f2)
>VALUES
>> (...); -- This example updates only f3 and f4 if the record
>already exists.
>>
>> I just picked "NOT" as the separator, perhaps "KEEP" gives better
>> clarity (see next example), but any good word would do.
>> Primar
[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 server-process-edition would be a better option for my case but is the branch still maintained and updated? Are the branches mutually exclusive or can be used both in the same process? -- Marco Bambini http://www.sqlabs.com http://twitter.com/sqlabs ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
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 an error. For example, given
the following table:
CREATE TABLE foo(
idINTEGER NOT NULL,
foo_key TEXT NOT NULL,
some_data TEXT,
PRIMARY KEY(id),
UNIQUE (foo_key)
);
INSERT INTO foo(id, foo_key, some_data) VALUES(1, "XXX", "...");
INSERT INTO foo(id, foo_key, some_data) VALUES(2, "YYY", "...");
If we are performing a query:
INSERT INTO foo(id, foo_key, some_data) VALUES(1, "YYY", "...")
Which record should we update and what columns?
Having the ability to specify a specific column on which the conflict
is actually an acceptable event lets the developer to make a decision
how to resolve it:
INSERT INTO foo(id, foo_key, some_data) VALUES(1, "YYY", "...")
19 March 2018, 18:41:34, by "R Smith" :
> 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 [INTO ]]
>
> Quite right, and the statement in MSSQL is even more convoluted, which,
> if it was in SQLite like this, would require a dynamically created SQL
> statement that is worse than simply computing an UPDATE and an INSERT -
> which a previous poster already lamented.
>
> My suggestion for UPSERT would be the very simple already SQLite-like
> syntax of:
>
> INSERT OR UPDATE INTO t (k1, k2, ... , kn, f1, f2, ... , fn)
> followed by the usual VALUES clause or SELECT query.
>
> Any record found to exist with the exact same value in the Primary Key
> field(s) [ k1 .. kn ] has all other fields (that are NOT Primary Key
> fields) updated to the new values, and if no such record is found, the
> row simply gets inserted. If the inserted row OR updated values cause
> any other constraint to break, then FAIL hard, the same way (and
> possibly with the same ON CONFLICT options) as any other single INSERT
> or UPDATE would be subjected to.
>
> This is far better than INSERT OR REPLACE since there is no delete, and
> no multiple-row delete on constraint violations.
> It is simple in terms of converting any current INSERT OR REPLACE query
> to an INSERT OR UPDATE query requires changing 1 word only.
>
> Triggers should fire for ON INSERT and ON UPDATE according to whatever
> actually is required during the operation.
>
> Adding this has no backward compatibility to break, this did not exist
> before and it is not schema-specific.
>
>
> One possible added refinement might be an optional second field-group
> that should be ignored over-and-above the PK fields during the UPDATE.
> (During the INSERT of course all fields MUST be added).
>
> 2 ways this can be done easily:
>
> A - Use a separate 2nd prototype group for Non-Updating fields, Like
> this perhaps:
>
> INSERT OR UPDATE INTO t (pk1, pk2, f1, f2, f3, f4) NOT (f1, f2) VALUES
> (...); -- This example updates only f3 and f4 if the record already exists.
>
> I just picked "NOT" as the separator, perhaps "KEEP" gives better
> clarity (see next example), but any good word would do.
> Primary key fields pk1 and pk2 along with specified non-updating fields
> f1 and f2 are all ignored during an update, but still used during an
> insert.
> Adding a PK field to the second set is a no-op as some might like it for
> legibility. i.e this next query is equivalent to the above:
>
> INSERT OR UPDATE INTO t (pk1, pk2, f1, f2, f3, f4) KEEP (pk1, pk2, f1,
> f2) VALUES (...); -- This example updates only f3 and f4, same as above.
>
>
> B - Use a Marker of sorts for Non-Updating fields, Like this perhaps
> using the Exclamation mark:
>
> INSERT OR UPDATE INTO t (pk1, !pk2, !f1, !f2, f3, f4) VALUES (...); --
> Again update only f3 and f4 if the record already exists.
>
> (Adding the marker to a PK field is a no-op).
> Escaping is not needed since a fieldname starting with the same marker
> will be in the list of field-names, no ambiguity, and in the case where
> a set of fields contain fields starting with both one and two markers
> (for which the programmer should be shot, but let's assume it possible)
> then the field can simply be enclosed in quotes as is the norm for
> disambiguation in SQLite. This next example has fields named !f and !!f:
>
> INSERT OR UPDATE INTO t (pk1, pk2, !"!f", !!f) VALUES (...); -- Here
> updating only !!f if the record already exists.
>
>
> Personally, I'm partial to option A.
>
> I know it's a bit of work, but it seems less so than many of the other
> additions - perhaps let's first have another show-of-hands t
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 [INTO ]]
Quite right, and the statement in MSSQL is even more convoluted, which,
if it was in SQLite like this, would require a dynamically created SQL
statement that is worse than simply computing an UPDATE and an INSERT -
which a previous poster already lamented.
My suggestion for UPSERT would be the very simple already SQLite-like
syntax of:
INSERT OR UPDATE INTO t (k1, k2, ... , kn, f1, f2, ... , fn)
followed by the usual VALUES clause or SELECT query.
Any record found to exist with the exact same value in the Primary Key
field(s) [ k1 .. kn ] has all other fields (that are NOT Primary Key
fields) updated to the new values, and if no such record is found, the
row simply gets inserted. If the inserted row OR updated values cause
any other constraint to break, then FAIL hard, the same way (and
possibly with the same ON CONFLICT options) as any other single INSERT
or UPDATE would be subjected to.
This is far better than INSERT OR REPLACE since there is no delete, and
no multiple-row delete on constraint violations.
It is simple in terms of converting any current INSERT OR REPLACE query
to an INSERT OR UPDATE query requires changing 1 word only.
Triggers should fire for ON INSERT and ON UPDATE according to whatever
actually is required during the operation.
Adding this has no backward compatibility to break, this did not exist
before and it is not schema-specific.
One possible added refinement might be an optional second field-group
that should be ignored over-and-above the PK fields during the UPDATE.
(During the INSERT of course all fields MUST be added).
2 ways this can be done easily:
A - Use a separate 2nd prototype group for Non-Updating fields, Like
this perhaps:
INSERT OR UPDATE INTO t (pk1, pk2, f1, f2, f3, f4) NOT (f1, f2) VALUES
(...); -- This example updates only f3 and f4 if the record already exists.
I just picked "NOT" as the separator, perhaps "KEEP" gives better
clarity (see next example), but any good word would do.
Primary key fields pk1 and pk2 along with specified non-updating fields
f1 and f2 are all ignored during an update, but still used during an
insert.
Adding a PK field to the second set is a no-op as some might like it for
legibility. i.e this next query is equivalent to the above:
INSERT OR UPDATE INTO t (pk1, pk2, f1, f2, f3, f4) KEEP (pk1, pk2, f1,
f2) VALUES (...); -- This example updates only f3 and f4, same as above.
B - Use a Marker of sorts for Non-Updating fields, Like this perhaps
using the Exclamation mark:
INSERT OR UPDATE INTO t (pk1, !pk2, !f1, !f2, f3, f4) VALUES (...); --
Again update only f3 and f4 if the record already exists.
(Adding the marker to a PK field is a no-op).
Escaping is not needed since a fieldname starting with the same marker
will be in the list of field-names, no ambiguity, and in the case where
a set of fields contain fields starting with both one and two markers
(for which the programmer should be shot, but let's assume it possible)
then the field can simply be enclosed in quotes as is the norm for
disambiguation in SQLite. This next example has fields named !f and !!f:
INSERT OR UPDATE INTO t (pk1, pk2, !"!f", !!f) VALUES (...); -- Here
updating only !!f if the record already exists.
Personally, I'm partial to option A.
I know it's a bit of work, but it seems less so than many of the other
additions - perhaps let's first have another show-of-hands to see if
this a real need, but it is asked for here more frequently than any
other feature (to my perception at least).
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
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 column b produces 'ERROR:
column "b" does not exist' in PostgreSQL:
sqlite> WITH t(a) AS (VALUES ('foo')) SELECT a||'!' AS b FROM t WHERE
b='foo!';
b
foo!
A safer coding style would be to use an intermediating query/view/cte when
any input column's meaning is being modified:
sqlite> WITH t(a) AS (VALUES ('foo')), u AS (SELECT a||'!' AS a FROM t)
SELECT a FROM u WHERE a='foo!';
a
foo!
Peter
On Sun, Mar 18, 2018 at 2:31 AM, Moritz Bruder
wrote:
> Hi,
>
> 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 ("foo"),("bar");
>
> -- Returns a single row with a single column: 'foo!'
> SELECT (test.name || '!') AS tname
> FROM test
> WHERE tname = 'foo!'
>
> --Returns an empty result.
> SELECT (test.name || '!') AS name
> FROM test
> WHERE name = 'foo!';
>
> What happens is that the identifier "name", defined in the SELECT-clause,
> gets shadowed by the table's column "name". I'm not exactly sure what the
> SQL standard says but it is wrong in my opinion. I expect it to be the
> other way round.Let me know whether you consider it a bug.
>
>
> Best wishes,
>
> Moritz
> ___
> 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] 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 schema. You might compute this using: > >sqlite3 yourfile.db '.schema --indent' | grep -i autoincrement | wc -l > > Private email to me is fine. Thanks for participating in this survey! > -- > 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 > -- *Jim Dodgen* ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
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 always generate queries dynamically if they're "almost identical". Only one-offs get handcrafted queries. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[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 not common, But I have tried to build such test case which
can lead to this problem very easy. Here is how I generate it:
1) create an fts5 table. and insert some record like "lucya","lucyb".
2) prepare some records: a) lusheng b)lulu; c)lunix; d)luma; e) pengyu.
a,b,c,d are have some same prefix(lu), e is some other random case.
3) before insert into the fts table with 2) records, appending some random
letter to make each record different.
Like: "lulu","luluabc","luluefg", also "lunix","lunixabc",etc
4) for-loop insert, and each loop trying to lantch the query 'lucy*', \
check the match result will finding the mis-match result, the corrent results
should be "lucya","lucyb", not "luluabc"...
When mis-match happen, I try to analyze the prefix search mechanism and find
that, there are 2 points which I think have problems:
1) fts5LeafSeek, when search failed, and exec goto search_failed, in
search_failed, the 2 if condition will not satisfy commonly. In my mind, I
think it should return,
but not, and then the search_success logic exec.
2) fts5SetupPrefixIter, when gather results, the logic to set the flag bNewTerm
has some leak, which will set bNewTerm=false,
but the record is not what we want indeed.
These 2 logic problems lead to mis-match results. I try to remove the bNewTerm
logic directly, and make it compare every loog,
then, the mis-match results disappear.
// relevant code
Change below
if (bNewTerm) {
if (nTerm < nToken || memcmp(pToken, pTerm, nToken)) break;
}
to
if (nTerm < nToken || memcmp(pToken, pTerm, nToken)) break;
Need your help to recheck the FTS5 prefix search logic, thank you very much.
Yours,
xiaojin zheng
获取 Outlook for Android
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
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 ("foo"),("bar");
-- Returns a single row with a single column: 'foo!'
SELECT (test.name || '!') AS tname
FROM test
WHERE tname = 'foo!'
--Returns an empty result.
SELECT (test.name || '!') AS name
FROM test
WHERE name = 'foo!';
What happens is that the identifier "name", defined in the SELECT-clause, gets shadowed
by the table's column "name".
If I recall correctly, SQL standard doesn't allow aliases from SELECT to be
used in WHERE clause, only in ORDER BY and, possibly, HAVING (I'm not sure of
the latter). SQLite allows aliases in WHERE as an extension, but prefers the
real column name in case of conflict, so as to match the behavior of other DBMS.
--
Igor Tandetnik
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
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 test
WHERE name = 'foo!' )
SELECT test.name || '!' AS name;
I must be misinterpreting:
create temp table t (a int);
insert into t values (1), (2), (6);
select a int, printf('<%5i>', a) fmt from t where fmt like '%>';
int fmt
1 <1>
2 <2>
6 <6>
Here WHERE understands what fmt refers to.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
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 entire UPSERT argument's only real
> motivation, because it really is negligible from a Database performance point
> of view. Never the less, many a lazy programmer do yearn for it (I don't
> specifically need it, but I am a lazy programmer too, and probably would use
> it if it existed!), and it seems trivial to implement db-side.
>
> We keep going in circles between those of us explaining why it's not really
> better and those of us who really feel warranted in asking for it.
>
> I never see a dev speak up on this - Is there any dev thoughts on why this
> isn't considered yet?
>
> I mean, do we keep lobbying for it, or is there a reason we should all just
> drop it (and that we might explain to the next person coming on here and
> asking for it)?.
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 [INTO ]]
Setting aside the RETURNING option and also its INTO clause only meant for
stored procedures, clearly the syntax UPDATE OR INSERT gets its feet on SQLite
UPDATE OR ROLLBACK/ABORT/REPLACE/FAIL/IGNORE construction which have absolutely
not the same kind of semantic attached to them (these are conflict resolutions
on verb UPDATE). This probably complicates things significantly, and tend to
weight on the 'Lite' part of the name 'SQLite'.
I would have like to have it in SQLite at some point in past time (would have
made the transition easier), though learned easily to live without it. :)
--
Best Regards, Meilleures salutations, Met vriendelijke groeten,
Olivier Mascia
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
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 for a JSON1-like extension to access an SQLite DB inside a blob-column :) Sure, that's denormalized, we all know that happens (case in point JSON1) and for some types of data like arrays (whose elements have no "natural keys"), that would be one way. But then the 100 bytes SQLite header, 1-page sqlite_master (for 1 table to store something into), and at east 1 page for that table puts the overhead of the first byte of data to 100+512+512 = 1124 byte, too much for smallish data... > On 3/18/18, Olivier Mascia wrote: > > Is the serialized format quite compact, or full > > of void unused space on 'pages'? (that obviously a good external > compression > > would get rid of). Or said differently, how far or close is the > serialized > > format to the on-disk SQLite file format? > > The serialization format is exactly the on-disk format. So you can > write the serialization into a file, then open that file as a > database. Or you can read a file off of disk into memory then > "deserialize" that blob into a database. > > Normally when you open a :memory: database, the pages are spread out > in memory at arbitrary locations. But with sqlite3_deserialize(), > SQLite keeps all the pages in one contiguous blob. > > As for size, I have found that using page_size=512 gives maximum space > efficiency. > I guess Olivier's "compactness" question could also be viewed in terms of "empty" pages after deletes, or partially empty pages, i.e. are in-memory DBs always implicitly in a "vaccumed" state, w/o any "unused" pages in the middle? --DD ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
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 from a Database performance point of view. Never the less, many a lazy programmer do yearn for it (I don't specifically need it, but I am a lazy programmer too, and probably would use it if it existed!), and it seems trivial to implement db-side. We keep going in circles between those of us explaining why it's not really better and those of us who really feel warranted in asking for it. I never see a dev speak up on this - Is there any dev thoughts on why this isn't considered yet? I mean, do we keep lobbying for it, or is there a reason we should all just drop it (and that we might explain to the next person coming on here and asking for it)?. Cheers, Ryan ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
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 store (typically one ctree file per table) and SQLite
virtual table are created/destroyed ("cloned" und "uncloned") together. There
is usually another virtual table (called the "partition" table) that works as a
single point of access for queries.
E.g. a table of customer cards organized per jurisdiction. There will be one
(or more, for a multi-jurisdiction application) customer card file
"/...cust_card_01.dat" with a corresponding SQLite virtual table "CREATE
VIRTUAL TABLE cust_card_01 USING ctree(...);" and one partition table for SQL
access "CREATE VIRUTAL TABLE cust_card USING partition(...);"
This strategy is mostly used for tables that typically stick around for a long
time. Application processes typically directly access the backing store,
because this is faster; reports and queries typically access the partition
table, because they are independant of the jurisdiction.
2) Speciality tables:
The backing store is composed of files that are typically created per day and
persist only for a short period of time. There is only one virtual table, and
the virtual table module handles which files are present internally.
E.g. a transaction log table, organised by daily files that are kept for a
certai number of days. "CREATE VIRTUAL TABLE txlog USING txlog(...);" Which
checks internally if the requested day's file is present or not.
-Ursprüngliche Nachricht-
Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im
Auftrag von Philippe Riand
Gesendet: Samstag, 17. März 2018 15:53
An: sqlite-users@mailinglists.sqlite.org
Betreff: [EXTERNAL] [sqlite] Lazy virtual table creation
We are using virtual tables to provide an SQL access to our data set and this
works very well. But we have potentially "a lot” of virtual tables, with some
even yet unknown when we start the DB. We’d like to create them lazily, on
first access.
Is there a hook we can use so when an SQL statement refers to a non existing
table it asks a callback for a VT definition? It is fine if these dynamic table
requires a specific prefix similar to the "temp” one.
Regards,
Phil.
___
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] 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 schema. You might compute this using: > >sqlite3 yourfile.db '.schema --indent' | grep -i autoincrement | wc -l > > Private email to me is fine. Thanks for participating in this survey! > sqlite3 schema-v.db '.schema --indent' | grep -i autoincrement | wc -l 28 sqlite3 schema-i.db '.schema --indent' | grep -i autoincrement | wc -l 117 sqlite3 schema-p.db '.schema --indent' | grep -i autoincrement | wc -l 55 sqlite3 schema-g.db '.schema --indent' | grep -i autoincrement | wc -l 14 The 4 different main "data" schemas used by my employer's commercial offering. There are hundreds/thousands of those DBs at all our client sites. Note that I'm not directly associated to the design of those schemas, but I know the SQLite-assigned auto-inc'd rowid PK is heavily depended on, in the very SQLite-heavy application-suite. PS: And that new tables, for new data types, are regularly added each release, so these numbers will only increase, until an unlikely redesign to rely on Guid PKs is made for example. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
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 that will modify the database connection that invoked the authorizer callback. Note that sqlite3_prepare_v2() and sqlite3_step() both modify their database connections for the meaning of "modify" in this paragraph." -Ursprüngliche Nachricht- Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im Auftrag von Dominique Devienne Gesendet: Montag, 19. März 2018 10:45 An: SQLite mailing list Betreff: [EXTERNAL] 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. whether name resolution to table/column happens before or after the authorizer is called. But it brings up the more general question of knowing which APIs are safe to call inside other APIs, notably when doing schema changes for example. In this case for example, we'd typically be inside a prepare statement, and is it OK to change the schema, potentially affecting that very statement we are currently parsing/resolving/compiling? I.e. API re-entrance but outside the context of multi-threading I guess. --DD ___ 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] 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. whether name resolution to table/column happens before or after the authorizer is called. But it brings up the more general question of knowing which APIs are safe to call inside other APIs, notably when doing schema changes for example. In this case for example, we'd typically be inside a prepare statement, and is it OK to change the schema, potentially affecting that very statement we are currently parsing/resolving/compiling? I.e. API re-entrance but outside the context of multi-threading I guess. --DD ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
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 > > itself. > > Both performance-wise and productivity-wise. > > > > Doing hacks with INSERT + UPDATE or UPDATE + INSERT leaves us with > > problems: > > * Which strategy to choose, INSERT + UPDATE or the reverse? No way to > > make this generic without hurting performance > > * No matter the strategy, we end up with two queries which leads to extra > > code that has to be maintained and kept in sync plus a penalty from > > preparing two statements > > * Existence of two statements leaves us vulnerable to race conditions, > > which adds two extra statements to BEGIN and COMMIT a transaction > > > > I agree with your overall sentiment, but BEGIN/COMMIT actually eliminates > two statements because in the standard mode of operation (ie. autocommit) > you're essentially doing: > > (implicit) BEGIN > INSERT ... > (implicit) COMMIT > (implicit) BEGIN > UPDATE ... > (implicit) COMMIT > > By making the BEGIN/COMMIT explicit you reduce the overall work when two > statements are required. I agree with you here. But then again you have to issue both BEGIN and COMMIT through SQL statements, ie through the parser. Even if overhead is small, it's still present. It all depends on the scenario that database is used in. If you have a lot of scenarios when you INSERT or UPDATE some data quite frequently then overhead is visible. By manually tweaking strategies in various places (whether to use UPDATE first or INSERT) I've managed to improve performance by tens of percents. Then again, it depends on scenario. Users that use this model not very often will definitely not benefit that much from UPSERT. But the ability to work around, and potentially small benefit to an average user should not be the arguments to dismiss its implementation. After all, half of the features in SQLite3 are not that useful to an average user. > > It does seem like sqlite could avoid an extra btree lookup if it > implemented UPSERT itself, but since the required pages are practically > guaranteed to be in cache for the second query I wonder how many rows you'd > need in a table for it to make a significant difference. As you say the > main benefit would be to avoid synthesising two statements in user code. It's easy to calculate, exactly twice as much time as it takes to do a B-Tree lookup. How big is the piece of the pie, again, depends on the scenario. Fort me personally, the most sad thing is an annoyance. Because I have to maintain two almost identical queries and manually tweak strategies. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
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 > 5/10 > > Always in conjunction with INTEGER PRIMARY KEY fwiw. > > > Also the following command is perhaps more portable: > > sqlite3 yourfile.db .schema | grep -ic autoincrement > > The sqlite3 shell on my system is too old to understand .schema --indent > and doesn't output anything so there's always zero lines to count :) > > -Rowan > > > On 16 March 2018 at 23:37, 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 schema. You might compute this using: > > > >sqlite3 yourfile.db '.schema --indent' | grep -i autoincrement | wc -l > > > > Private email to me is fine. Thanks for participating in this survey! > > -- > > 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 > ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
