Re: [sqlite] Version 3.20.0 coming soon...

[Yuriy M. Kaminskiy]

On 07/18/17 16:37 , Stephan Buchert wrote:
> The command history (Ctrl-R) of the shell is still intact with the new tab
> completion, but I had experienced it often as way too short (too few
> lines). So I have searched now in the source code, the line in shell.c is
>
>   if( zHistory ){
> shell_stifle_history(100);
> ...
>
> in case somebody wants to increase it to more than 100.
>
> An obvious suggestion (already for 3.20.0?) is to make the history depth
> configurable, perhaps when compiling the shell.

I posted patch about six years ago.

___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Possible inaccuracy in "Isolation In SQLite" paper

[Peter Da Silva]

I notice that “read_uncommitted pragma” is spelled “PRAGMA read_uncommitted” in 
one place. The links all match. This small inconsistency is probably a mistake.


___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Possible inaccuracy in "Isolation In SQLite" paper

[Gwendal Roué]

Gerry, David, you are both right.

The possibility of a bug is thus eliminated for good. And 
https://www.sqlite.org/isolation.html  
is, strictly speaking, accurate, even if it another document is needed to avoid 
any interpretation doubt (http://www.sqlite.org/lang_transaction.html 
).

Fact is, I spent a few days clearing things up, until I could eventually reach 
a correct program despite fuzzy premises ;-) Documentation is hard, and one 
never stops learning SQLite. Thanks for your quick and precise answers!

Gwendal


> Le 18 juil. 2017 à 15:43, David Raymond  a écrit :
> 
> I think the documentation's good. I think you're missing the whole point of a 
> deferred transaction: that it doesn't start a "transaction" until it needs 
> to. You can run "begin deferred transaction" then walk away for 3 months 
> without upsetting anything. If you need the precise timing then why not just 
> use "begin immediate"?
> 
> http://www.sqlite.org/lang_transaction.html
> 
> A deferred transaction doesn't do anything until it first accesses the file. 
> Once it does then it will lock out any writers.
> 
> "Deferred means that no locks are acquired on the database until the database 
> is first accessed. Thus with a deferred transaction, the BEGIN statement 
> itself does nothing to the filesystem. Locks are not acquired until the first 
> read or write operation. The first read operation against a database creates 
> a SHARED lock and the first write operation creates a RESERVED lock."
> 
> 
> -Original Message-
> From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On 
> Behalf Of Gwendal Roué
> Sent: Tuesday, July 18, 2017 9:10 AM
> To: SQLite mailing list
> Subject: [sqlite] Possible inaccuracy in "Isolation In SQLite" paper
> 
> Hello all,
> 
> The following sentence in https://www.sqlite.org/isolation.html does not 
> exactly describe the behavior of SQLite (since many versions):
> 
>> In WAL mode, SQLite exhibits "snapshot isolation". When a read transaction 
>> starts, that reader continues to see an unchanging "snapshot" of the 
>> database file as it existed at the moment in time when the read transaction 
>> started. Any write transactions that commit while the read transaction is 
>> active are still invisible to the read transaction, because the reader is 
>> seeing a snapshot of database file from a prior moment in time.
> 
> 
> I'll exhibit the inaccuracy below.
> 
> Meanwhile, "snapshot isolation" is a tremendous feature of SQLite. Not only 
> does it grant any read-only connection that opens a deferred transaction an 
> immutable and consistent view of the database. But when you can guarantee 
> that there is a single writer connection, snapshot isolation allows *precise 
> scheduling*, such as blocking the writer connection until a reader has 
> established snapshot isolation. With such precision, one can exactly control 
> what's available to a reader, while not blocking the writer longer than 
> necessary.
> 
> And this is where the documentation paragraph starts becoming inaccurate. For 
> the simplicity of the argument, I'll execute statements sequentially from two 
> connections W and R. To reproduce, just open two shells, and execute 
> statements in the following order:
> 
> $ sqlite3 /tmp/snapshotisolation.sqlite
> SQLite version 3.16.0 2016-11-04 19:09:39
> W> PRAGMA journal_mode=wal;
> W> CREATE TABLE t(a);
> R> BEGIN DEFERRED TRANSACTION;
> W> INSERT INTO t DEFAULT VALUES;
> R> SELECT COUNT(*) FROM t;
> 1
> 
> This is unexpected. After connection R has started a deferred transaction, it 
> should continue to see an "unchanging snapshot of the database file as it 
> existed at the moment in time when the read transaction started". Obviously, 
> this is not the case, since the insertion performed by W is visible from R 
> even though it has been performed *after* R has started its deferred 
> transaction. The "Any write transactions that commit while the read 
> transaction is active are still invisible to the read transaction" is also 
> flat wrong here.
> 
> If we continue, things behave as expected:
> 
> W> INSERT INTO t DEFAULT VALUES;
> R> SELECT COUNT(*) FROM t;
> 1
> 
> R does not see the new insertion, which means that it indeed lives in an 
> unchanging snapshot. It just happens that the snapshot was not established 
> when the transaction has started, as documented, but *later*. But when?
> 
> After a few experiments, it looks like the snapshot is established on the 
> first select:
> 
> W> PRAGMA journal_mode=wal;
> W> CREATE TABLE t(a);
> R> BEGIN DEFERRED TRANSACTION;
> R> SELECT * FROM sqlite_master LIMIT 1; -- good enough to start the snapshot
> W> INSERT INTO t DEFAULT VALUES;
> R> SELECT COUNT(*) FROM t;
> 0 -- as expected
> 
> If now we stop entering commands by hand in the CLI, and start working with 
> threads, the *precise 

Re: [sqlite] Possible inaccuracy in "Isolation In SQLite" paper

[David Raymond]

I think the documentation's good. I think you're missing the whole point of a 
deferred transaction: that it doesn't start a "transaction" until it needs to. 
You can run "begin deferred transaction" then walk away for 3 months without 
upsetting anything. If you need the precise timing then why not just use "begin 
immediate"?

http://www.sqlite.org/lang_transaction.html

A deferred transaction doesn't do anything until it first accesses the file. 
Once it does then it will lock out any writers.

"Deferred means that no locks are acquired on the database until the database 
is first accessed. Thus with a deferred transaction, the BEGIN statement itself 
does nothing to the filesystem. Locks are not acquired until the first read or 
write operation. The first read operation against a database creates a SHARED 
lock and the first write operation creates a RESERVED lock."


-Original Message-
From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On 
Behalf Of Gwendal Roué
Sent: Tuesday, July 18, 2017 9:10 AM
To: SQLite mailing list
Subject: [sqlite] Possible inaccuracy in "Isolation In SQLite" paper

Hello all,

The following sentence in https://www.sqlite.org/isolation.html does not 
exactly describe the behavior of SQLite (since many versions):

> In WAL mode, SQLite exhibits "snapshot isolation". When a read transaction 
> starts, that reader continues to see an unchanging "snapshot" of the database 
> file as it existed at the moment in time when the read transaction started. 
> Any write transactions that commit while the read transaction is active are 
> still invisible to the read transaction, because the reader is seeing a 
> snapshot of database file from a prior moment in time.


I'll exhibit the inaccuracy below.

Meanwhile, "snapshot isolation" is a tremendous feature of SQLite. Not only 
does it grant any read-only connection that opens a deferred transaction an 
immutable and consistent view of the database. But when you can guarantee that 
there is a single writer connection, snapshot isolation allows *precise 
scheduling*, such as blocking the writer connection until a reader has 
established snapshot isolation. With such precision, one can exactly control 
what's available to a reader, while not blocking the writer longer than 
necessary.

And this is where the documentation paragraph starts becoming inaccurate. For 
the simplicity of the argument, I'll execute statements sequentially from two 
connections W and R. To reproduce, just open two shells, and execute statements 
in the following order:

$ sqlite3 /tmp/snapshotisolation.sqlite
SQLite version 3.16.0 2016-11-04 19:09:39
W> PRAGMA journal_mode=wal;
W> CREATE TABLE t(a);
R> BEGIN DEFERRED TRANSACTION;
W> INSERT INTO t DEFAULT VALUES;
R> SELECT COUNT(*) FROM t;
1

This is unexpected. After connection R has started a deferred transaction, it 
should continue to see an "unchanging snapshot of the database file as it 
existed at the moment in time when the read transaction started". Obviously, 
this is not the case, since the insertion performed by W is visible from R even 
though it has been performed *after* R has started its deferred transaction. 
The "Any write transactions that commit while the read transaction is active 
are still invisible to the read transaction" is also flat wrong here.

If we continue, things behave as expected:

W> INSERT INTO t DEFAULT VALUES;
R> SELECT COUNT(*) FROM t;
1

R does not see the new insertion, which means that it indeed lives in an 
unchanging snapshot. It just happens that the snapshot was not established when 
the transaction has started, as documented, but *later*. But when?

After a few experiments, it looks like the snapshot is established on the first 
select:

W> PRAGMA journal_mode=wal;
W> CREATE TABLE t(a);
R> BEGIN DEFERRED TRANSACTION;
R> SELECT * FROM sqlite_master LIMIT 1; -- good enough to start the snapshot
W> INSERT INTO t DEFAULT VALUES;
R> SELECT COUNT(*) FROM t;
0 -- as expected

If now we stop entering commands by hand in the CLI, and start working with 
threads, the *precise scheduling* I mention at the beginning of the mail 
requires to block the writer connection W until the reader connection R has 
started a deferred transaction, and sqlite3_step() has been any executed once 
from any select statement. Only then can the writer connection be released, 
with a absolute control of the content of the reader's snapshot.

I'm not sure the behavior I've just described can be called a bug. The snapshot 
is unchanging indeed. When there are several processes connected to the 
database, a reader can't know whether a change has been performed before its 
deferred transaction has started, or between the start of the deferred 
transaction and its first select statement, and therefore should not care at 
all. With this interpretation, there is nothing wrong in the current behavior 
of SQLite.

However, when an application developper is sure that the database 

Re: [sqlite] Possible inaccuracy in "Isolation In SQLite" paper

[Gerry Snyder]

"Deferred means that no locks are acquired on the database until the
database is first accessed. Thus with a deferred transaction, the BEGIN
statement itself does nothing to the filesystem. Locks are not acquired
until the first read or write operation."

On Jul 18, 2017 6:10 AM, "Gwendal Roué"  wrote:

> Hello all,
>
> The following sentence in https://www.sqlite.org/isolation.html does not
> exactly describe the behavior of SQLite (since many versions):
>
> > In WAL mode, SQLite exhibits "snapshot isolation". When a read
> transaction starts, that reader continues to see an unchanging "snapshot"
> of the database file as it existed at the moment in time when the read
> transaction started. Any write transactions that commit while the read
> transaction is active are still invisible to the read transaction, because
> the reader is seeing a snapshot of database file from a prior moment in
> time.
>
>
> I'll exhibit the inaccuracy below.
>
> Meanwhile, "snapshot isolation" is a tremendous feature of SQLite. Not
> only does it grant any read-only connection that opens a deferred
> transaction an immutable and consistent view of the database. But when you
> can guarantee that there is a single writer connection, snapshot isolation
> allows *precise scheduling*, such as blocking the writer connection until a
> reader has established snapshot isolation. With such precision, one can
> exactly control what's available to a reader, while not blocking the writer
> longer than necessary.
>
> And this is where the documentation paragraph starts becoming inaccurate.
> For the simplicity of the argument, I'll execute statements sequentially
> from two connections W and R. To reproduce, just open two shells, and
> execute statements in the following order:
>
> $ sqlite3 /tmp/snapshotisolation.sqlite
> SQLite version 3.16.0 2016-11-04 19:09:39
> W> PRAGMA journal_mode=wal;
> W> CREATE TABLE t(a);
> R> BEGIN DEFERRED TRANSACTION;
> W> INSERT INTO t DEFAULT VALUES;
> R> SELECT COUNT(*) FROM t;
> 1
>
> This is unexpected. After connection R has started a deferred transaction,
> it should continue to see an "unchanging snapshot of the database file as
> it existed at the moment in time when the read transaction started".
> Obviously, this is not the case, since the insertion performed by W is
> visible from R even though it has been performed *after* R has started its
> deferred transaction. The "Any write transactions that commit while the
> read transaction is active are still invisible to the read transaction" is
> also flat wrong here.
>
> If we continue, things behave as expected:
>
> W> INSERT INTO t DEFAULT VALUES;
> R> SELECT COUNT(*) FROM t;
> 1
>
> R does not see the new insertion, which means that it indeed lives in an
> unchanging snapshot. It just happens that the snapshot was not established
> when the transaction has started, as documented, but *later*. But when?
>
> After a few experiments, it looks like the snapshot is established on the
> first select:
>
> W> PRAGMA journal_mode=wal;
> W> CREATE TABLE t(a);
> R> BEGIN DEFERRED TRANSACTION;
> R> SELECT * FROM sqlite_master LIMIT 1; -- good enough to start the
> snapshot
> W> INSERT INTO t DEFAULT VALUES;
> R> SELECT COUNT(*) FROM t;
> 0 -- as expected
>
> If now we stop entering commands by hand in the CLI, and start working
> with threads, the *precise scheduling* I mention at the beginning of the
> mail requires to block the writer connection W until the reader connection
> R has started a deferred transaction, and sqlite3_step() has been any
> executed once from any select statement. Only then can the writer
> connection be released, with a absolute control of the content of the
> reader's snapshot.
>
> I'm not sure the behavior I've just described can be called a bug. The
> snapshot is unchanging indeed. When there are several processes connected
> to the database, a reader can't know whether a change has been performed
> before its deferred transaction has started, or between the start of the
> deferred transaction and its first select statement, and therefore should
> not care at all. With this interpretation, there is nothing wrong in the
> current behavior of SQLite.
>
> However, when an application developper is sure that the database has a
> single writer connection, the question of the content of the snapshot
> suddenly becomes relevant. And the documentation becomes inaccurate.
>
> What do SQLite concurrency masters of this mailing list think? Besides
> that threads are evil, I mean ;-) Shouldn't the documentation be updated?
>
> Cheers to all,
> Gwendal
> PS: For the record, I've been talking about "precise scheduling" and
> "absolute control of the content of the snapshot" because they are the
> necessary conditions for some database observation features such as
> reloading fresh values from a request as soon as a transaction has modified
> its content. See https://github.com/RxSwiftCommunity/RxGRDB for some
> 

[sqlite] Version 3.20.0 coming soon...

[Stephan Buchert]

The command history (Ctrl-R) of the shell is still intact with the new tab
completion, but I had experienced it often as way too short (too few
lines). So I have searched now in the source code, the line in shell.c is

  if( zHistory ){
shell_stifle_history(100);
...

in case somebody wants to increase it to more than 100.

An obvious suggestion (already for 3.20.0?) is to make the history depth
configurable, perhaps when compiling the shell.

/Stephan
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] marketing - The world's most popular open source database

[Bob Friesenhahn]

On Tue, 18 Jul 2017, R Smith wrote:

I wouldn't dispute MySQL's claim as most popular, neither SQLite's claim as 
most widely deployed - both seem quite accurate, or at a minimum, plausible.


Sqlite likely has the longest anticipated future support out of 
available databases.  The anticipated support term is iron-clad:


"The SQLite project was started on 2000-05-09. The future is always 
hard to predict, but the intent of the developers is to support SQLite 
through the year 2050. Design decisions are made with that objective 
in mind."


Other databases might have turned to Rust by 2050.

Bob
--
Bob Friesenhahn
bfrie...@simple.dallas.tx.us, http://www.simplesystems.org/users/bfriesen/
GraphicsMagick Maintainer,http://www.GraphicsMagick.org/
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Possible inaccuracy in "Isolation In SQLite" paper

[Gwendal Roué]

Hello all,

The following sentence in https://www.sqlite.org/isolation.html does not 
exactly describe the behavior of SQLite (since many versions):

> In WAL mode, SQLite exhibits "snapshot isolation". When a read transaction 
> starts, that reader continues to see an unchanging "snapshot" of the database 
> file as it existed at the moment in time when the read transaction started. 
> Any write transactions that commit while the read transaction is active are 
> still invisible to the read transaction, because the reader is seeing a 
> snapshot of database file from a prior moment in time.


I'll exhibit the inaccuracy below.

Meanwhile, "snapshot isolation" is a tremendous feature of SQLite. Not only 
does it grant any read-only connection that opens a deferred transaction an 
immutable and consistent view of the database. But when you can guarantee that 
there is a single writer connection, snapshot isolation allows *precise 
scheduling*, such as blocking the writer connection until a reader has 
established snapshot isolation. With such precision, one can exactly control 
what's available to a reader, while not blocking the writer longer than 
necessary.

And this is where the documentation paragraph starts becoming inaccurate. For 
the simplicity of the argument, I'll execute statements sequentially from two 
connections W and R. To reproduce, just open two shells, and execute statements 
in the following order:

$ sqlite3 /tmp/snapshotisolation.sqlite
SQLite version 3.16.0 2016-11-04 19:09:39
W> PRAGMA journal_mode=wal;
W> CREATE TABLE t(a);
R> BEGIN DEFERRED TRANSACTION;
W> INSERT INTO t DEFAULT VALUES;
R> SELECT COUNT(*) FROM t;
1

This is unexpected. After connection R has started a deferred transaction, it 
should continue to see an "unchanging snapshot of the database file as it 
existed at the moment in time when the read transaction started". Obviously, 
this is not the case, since the insertion performed by W is visible from R even 
though it has been performed *after* R has started its deferred transaction. 
The "Any write transactions that commit while the read transaction is active 
are still invisible to the read transaction" is also flat wrong here.

If we continue, things behave as expected:

W> INSERT INTO t DEFAULT VALUES;
R> SELECT COUNT(*) FROM t;
1

R does not see the new insertion, which means that it indeed lives in an 
unchanging snapshot. It just happens that the snapshot was not established when 
the transaction has started, as documented, but *later*. But when?

After a few experiments, it looks like the snapshot is established on the first 
select:

W> PRAGMA journal_mode=wal;
W> CREATE TABLE t(a);
R> BEGIN DEFERRED TRANSACTION;
R> SELECT * FROM sqlite_master LIMIT 1; -- good enough to start the snapshot
W> INSERT INTO t DEFAULT VALUES;
R> SELECT COUNT(*) FROM t;
0 -- as expected

If now we stop entering commands by hand in the CLI, and start working with 
threads, the *precise scheduling* I mention at the beginning of the mail 
requires to block the writer connection W until the reader connection R has 
started a deferred transaction, and sqlite3_step() has been any executed once 
from any select statement. Only then can the writer connection be released, 
with a absolute control of the content of the reader's snapshot.

I'm not sure the behavior I've just described can be called a bug. The snapshot 
is unchanging indeed. When there are several processes connected to the 
database, a reader can't know whether a change has been performed before its 
deferred transaction has started, or between the start of the deferred 
transaction and its first select statement, and therefore should not care at 
all. With this interpretation, there is nothing wrong in the current behavior 
of SQLite.

However, when an application developper is sure that the database has a single 
writer connection, the question of the content of the snapshot suddenly becomes 
relevant. And the documentation becomes inaccurate.

What do SQLite concurrency masters of this mailing list think? Besides that 
threads are evil, I mean ;-) Shouldn't the documentation be updated?

Cheers to all,
Gwendal
PS: For the record, I've been talking about "precise scheduling" and "absolute 
control of the content of the snapshot" because they are the necessary 
conditions for some database observation features such as reloading fresh 
values from a request as soon as a transaction has modified its content. See 
https://github.com/RxSwiftCommunity/RxGRDB for some high-level implementations 
of such feature.



___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Version 3.20.0 coming soon...

[Stephan Buchert]

The tab completion in the sqlite3 shell works, which is nice, but it seems
to shadow readline's normal file name completion when hitting tab at for
example

sqlite> .read "sq

which is not so nice... (i.e. no file name completion any more for .read ,
.attach and the like...)

/Stephan
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] marketing - The world's most popular open source database

[R Smith]

I was drafting a "don't confuse `popular` [a collective human bias] with 
`most-used` or `most widely deployed` [value statements]" response, but 
I see others have already done so...


I would like to add that often if I mention "SQLite" in conversation 
with random people (even technical sometimes) I need to immediately 
follow it up with a short bio, whereas in 90% of cases those same people 
already know the term "MySQL". I think SQLite is mostly used as backbone 
data handling for really technical people and their software inventions, 
hence being so widely employed and being used near everywhere, but any 
script kiddie in a basement hacking out his/her first website is bound 
to be in the presence of MySQL - or, if they fancy themselves as 
technically-minded a cut above the rest, then perhaps PostGresSQL.


I wouldn't dispute MySQL's claim as most popular, neither SQLite's claim 
as most widely deployed - both seem quite accurate, or at a minimum, 
plausible.



On 2017/07/18 9:16 AM, Darren Duncan wrote:
I was reminded today that MySQL still prominently advertises 
themselves as "The world's most popular open source database", on 
their website and in their product announcements etc.


However, isn't that claim clearly wrong, given that SQLite for one has 
way more installations than MySQL does, and that's just for SQL DBMSs.


Is it worth having some kind of official statement from the makers of 
SQLite about this, that MySQL is using false advertising?


Or is the idea that SQLite has the most installations not easily 
provable?


-- Darren Duncan
___
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] Summarising (missing) values

[Paul Sanderson]

That simple - I was over thinking it

cheers

Paul
www.sandersonforensics.com
skype: r3scue193
twitter: @sandersonforens
Tel +44 (0)1326 572786
http://sandersonforensics.com/forum/content.php?195-SQLite-Forensic-Toolkit
-Forensic Toolkit for SQLite
email from a work address for a fully functional demo licence

On 18 July 2017 at 11:19, Clemens Ladisch  wrote:

> Paul Sanderson wrote:
> > What I would like is a single query that summarises the values that are
> > present in (or missing from) a table.
>
> A row is the start of a range if there is no previous row:
>
>   WITH ranges(first) AS (
> SELECT _id
> FROM messages
> WHERE _id - 1 NOT IN (SELECT _id FROM messages)
>   )
>
> The corresponding last row is the first row at or behind the start row
> that has no next row:
>
>   SELECT first,
>  (SELECT min(_id)
>   FROM messages
>   WHERE _id >= ranges.first
> AND _id + 1 NOT IN (SELECT _id FROM messages)
>  ) AS last
>   FROM ranges;
>
>
> Regards,
> Clemens
> ___
> 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] marketing - The world's most popular open source database

[Simon Slavin]

On 18 Jul 2017, at 8:37am, Donald Shepherd  wrote:

> I think that there's no real definition for "popular" leaves it as a
> massively ambiguous claim.

Agreed.  In addition to this, in which court would the claim be judged and why 
?  There’s no such thing as a world court.  Is someone going to fund SQLite’s 
side of the case ?  I might help fund a case SQLite was defending, but not one 
where SQLite was the prosecution.

SQLite can claim things MySQL can’t.  It can claim to be the worlds most used 
DBMS, or the DBMS with the most installations, or the DBMS chosen. by the most 
manufacturers.  If some clarity is needed then perhaps SQLite might word its 
own claims more specifically, then add that other databases claim to be the 
world most popular but nobody knows what that means if it isn’t one of the 
above.

Simon,
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Summarising (missing) values

[Clemens Ladisch]

Paul Sanderson wrote:
> What I would like is a single query that summarises the values that are
> present in (or missing from) a table.

A row is the start of a range if there is no previous row:

  WITH ranges(first) AS (
SELECT _id
FROM messages
WHERE _id - 1 NOT IN (SELECT _id FROM messages)
  )

The corresponding last row is the first row at or behind the start row
that has no next row:

  SELECT first,
 (SELECT min(_id)
  FROM messages
  WHERE _id >= ranges.first
AND _id + 1 NOT IN (SELECT _id FROM messages)
 ) AS last
  FROM ranges;


Regards,
Clemens
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Summarising (missing) values

[Paul Sanderson]

I have a table which includes a numeric "ID" column, values in the column
generally increment but there are some gaps.

I use the following query to get a list of all "missing" values


WITH RECURSIVE cte(x) AS (SELECT (SELECT Min(messages._id) FROM messages)
UNION ALL
SELECT cte.x + 1
FROM cte
WHERE cte.x < (SELECT Max(messages._id) FROM messages))
SELECT *
FROM cte
WHERE cte.x NOT IN (SELECT messages._id FROM messages)



Or to get the upper and lower bounds of the records that are present I can
use a couple of queries such as


SELECT messages._id
FROM messages
WHERE messages._id - 1 NOT IN (SELECT messages._id FROM messages)

which gives me (on my test values)

334
1077
1701
2385
2390
2393

and


SELECT messages._id
FROM messages
WHERE messages._id + 1 NOT IN (SELECT messages._id FROM messages)

334
1297
1701
2386
2390
3336





What I would like is a single query that summarises the values that are
present in (or missing from) a table. e.g.


334, 344
1077, 1297
1701, 1701
2385, 2386
2390, 2390
2393, 3336

Different approaches to this would be of interest
Paul


www.sandersonforensics.com
skype: r3scue193
twitter: @sandersonforens
Tel +44 (0)1326 572786
http://sandersonforensics.com/forum/content.php?195-SQLite-Forensic-Toolkit
-Forensic Toolkit for SQLite
email from a work address for a fully functional demo licence
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] marketing - The world's most popular open source database

[Clemens Ladisch]

Darren Duncan wrote:
> I was reminded today that MySQL still prominently advertises themselves
> as "The world's most popular open source database", on their website
> and in their product announcements etc.
>
> However, isn't that claim clearly wrong, given that SQLite for one has
> way more installations than MySQL does, and that's just for SQL DBMSs.

SQLite advertises itself as the "Most Widely Deployed and Used Database
Engine": http://www.sqlite.org/mostdeployed.html

This is not exactly the same as "popular".  It is plausible that
developers who choose a database indeed choose MySQL more often than any
other database.  (Even if most of those then end up being a single web
server, instead of an app or an embedded device with lots of instances.)


Regards,
Clemens
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] marketing - The world's most popular open source database

[Darren Duncan]

I was reminded today that MySQL still prominently advertises themselves as "The 
world's most popular open source database", on their website and in their 
product announcements etc.


However, isn't that claim clearly wrong, given that SQLite for one has way more 
installations than MySQL does, and that's just for SQL DBMSs.


Is it worth having some kind of official statement from the makers of SQLite 
about this, that MySQL is using false advertising?


Or is the idea that SQLite has the most installations not easily provable?

-- Darren Duncan
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users