Re: [sqlite] Ensure a snapshot remains readable

[Gwendal Roué]

Hello Adam,

You may enjoy reading this recent thread, which is exactly about the same
topic:
http://mailinglists.sqlite.org/cgi-bin/mailman/private/sqlite-users/2019-September/086099.html

The crux of your issue is that sqlite3_snapshot_get() is not involved in
transaction management at all. It does not take any lock. It does not
prevent checkpointing. It won't prevent an external connection from writing
and triggering an automatic checkpoint, for example. Checkpoints invalidate
snapshots returned by sqlite3_snapshot_get(), making them unable to be used
with sqlite3_snapshot_open(). This is a given, and I learned in the
previously linked thread that it is unlikely to change.

If you want a read-only long-time access to a given state of the database,
then you need a dedicated transaction. The access will be guaranteed for
the duration of the transaction. Checkpoints won't be able to invalidate
it. It will be super robust.

You can, for example:

1. open a dedicated read-only connection
2. run BEGIN DEFERRED TRANSACTION
3. perform your reads
4. run COMMIT or ROLLBACK at the end of your reads.

This technique actually opens the transaction, "locks" a state of the
database for unlimited future accesses, on the first read. Not on the BEGIN
DEFERRED TRANSACTION statement.

Sometimes this is good enough (think about it for a while). But sometimes
you want to control the exact state of the snapshot. For example, you may
want to take a snapshot after what you call the "next official state
update".

A way to achieve this with the most extreme precision and robustness is the
following:

1. In a "writer" connection, COMMIT the "next official state update". Now
prevent any write in the database until step 4.
2. In the read-only "snapshot" connection, BEGIN DEFERRED TRANSACTION
3. In the read-only "snapshot" connection, perform *any kind of read* in
order to start the transaction for good. SELECT * FROM sqlite_master LIMIT
1 is good enough. Anything goes.
4. Now you can accept further writes in the "writer" connection.
5. And now you can read from the "snapshot" connection and access a
guaranteed "official state" until the end of the "snapshot" transaction.

Hope this helps,
Gwendal Roué
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] How to to use a custom FTS5 Tokenizer in .NET (C#)

[Agaric Perdereau]

Hi Everyone,

I am trying to implement a custom .net Tokenizer for FTS5 in .Net but I
have absolutely ni idea where to start. Is there any documentation
somewhere on that subject?

Thanks for your help.


-- 
---
A. Perdereau
http://www.neomobili.com
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] rationale for private archives of the SQLite mailing list and suppressed reply-to

[Peter da Silva]

I think you're conflating things. A mail server speaks SMTP for both
inbound and outbound, IMAP/POP/webmail is all part of the user interface
stack... as would be the webforum component in any mailing list/web forum
scheme.

On Mon, 14 Oct 2019, 20:45 Warren Young,  wrote:

> On Oct 14, 2019, at 3:04 PM, Keith Medcalf  wrote:
> >
> > On Monday, 14 October, 2019 14:18, Warren Young 
> wrote:
> >
> >> Fossil Forums allow you to subscribe to email notifications.  From the
> >> reader’s perspective, it’s really very little different from the current
> >> Mailman based scheme.
> >
> > The preceding paragraph is completely at odds with the following
> paragraph, and taken together, they are completely illogical and
> inconsistent.
>
> You’re conflating inbound and outbound paths.  The ability to send email
> implies but does not require the ability to receive email.
>
> ...Which is why they’re often entirely different stacks, speaking
> different protocols!  E.g. SMTP outbound via Postfix, IMAP inbound via
> Dovecot.
>
> > All it needs is to be able to "read and process" RFC-2822 formatted
> message files that are found in an "inbound for me” directory
>
> That’s certainly one way that some email servers work.  The most common
> such scheme is called Maildir.
>
> But there’s probably at least half a dozen other ways it can work: mbox,
> MySQL store, PostgreSQL store, whatever it is that MS Exchange does that’s
> incompatible with the rest of the world…
>
> There are currently four supported outbound email setups in Fossil, and a
> stub for a fifth:
>
> https://fossil-scm.org/home/doc/trunk/www/alerts.md#advanced
>
> Why would inbound be different?
>
> Fossil isn’t in a position where it can require a specific SMTP server.
> It has to run on pretty much every common desktop and server platform.  You
> have to get pretty far down the long tail of OSes before you find one that
> Fossil doesn’t get used on daily by someone.  Therefore, we have to support
> approximately everything.
>
> On top of integrating with all common SMTP stacks, drh long ago stated a
> wish to write his own SMTP server.  (The latter being why Fossil has the
> start of one included!)  This should not surprise you if you’ve followed
> his career. :)
>
> The last time I counted up the pages of RFCs you have to implement to
> speak to a large fraction of the Internet email infrastructure — which was
> one of the times this argument came up on this mailing list! — it was
> something like 500 pages of standardese.  It is not just RFC-2822.  Getting
> to something useful will take time, which comes out of the time budget for
> SQLite, Fossil, etc.
>
> There is the option of writing glue software between Fossil and whatever
> SMTP infrastructure you already have, but no one’s bothered to do that in
> the year or so that Fossil Forums have been in steady use.  To me, that
> speaks more of the desirability of inbound email submission than about its
> inherent difficulty.
> ___
> 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] Last record

[Philippe RIO]

A short question : how could I know if I am reading the last record with
sqlite  (sqlite3_step)? sqlite3_step only returns SQLITE_ROW. Is there a
function for that case which returns SQLITE_DONE? A function which is one
record in advance from sqlite3_step.

Thank every one



-

Kenavo

https://md5finder.blogspot.com/
(Never be pleased, always improve)
--
Sent from: http://sqlite.1065341.n5.nabble.com/
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Last record

[Simon Slavin]

On 15 Oct 2019, at 4:34pm, Philippe RIO <51...@protonmail.ch> wrote:

> how could I know if I am reading the last record with
> sqlite  (sqlite3_step)?

Sorry, there's no way to do that for some arbitrary SELECT.  Because SQLite 
itself may not know.

SQLite does not always process your query and store all the results in memory.  
If there's an ideal index for your query, each call to _step() just one more 
row.  SQLite itself doesn't know it has reached the end until it gets an error 
because it runs off the end of the index.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Last record

[Philippe RIO]

The only way is to make a query for getting the number of records and in the
second query I have to count the number of records retrieved to know if it
is the last one.

Thank you for the quick answer.



-

Kenavo

https://md5finder.blogspot.com/
(Never be pleased, always improve)
--
Sent from: http://sqlite.1065341.n5.nabble.com/
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Last record

[Simon Slavin]

On 15 Oct 2019, at 5:38pm, Philippe RIO <51...@protonmail.ch> wrote:

> The only way is to make a query for getting the number of records and in the 
> second query I have to count the number of records retrieved to know if it is 
> the last one.

Assume your intended query specifies an order which has unique keys (in other 
words, you know exactly what order rows will be answered in.  Suppose it is

SELECT ... ORDER BY a, b DESC, c

You can find the last row which will be returned by reversing the order and 
adding LIMIT 1

SELECT ... ORDER BY a DESC, b ASC, c DESC LIMIT 1

You can then remember the value(s) of some column(s) of the row returned, and 
watch for the same one(s) when you do your desired SELECT.

Depending on the number of rows in your result set, this may or may not be 
faster than counting the number of rows.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Last record

[Jim Morris]

In your application you can create a wrapping iterator that pre-reads
the next value instead or directly accessing the low level step
function.  Then you can ask if it is the last.

On 10/15/2019 9:44 AM, Simon Slavin wrote:
> On 15 Oct 2019, at 5:38pm, Philippe RIO <51...@protonmail.ch> wrote:
>
>> The only way is to make a query for getting the number of records and in the 
>> second query I have to count the number of records retrieved to know if it 
>> is the last one.
> Assume your intended query specifies an order which has unique keys (in other 
> words, you know exactly what order rows will be answered in.  Suppose it is
>
> SELECT ... ORDER BY a, b DESC, c
>
> You can find the last row which will be returned by reversing the order and 
> adding LIMIT 1
>
> SELECT ... ORDER BY a DESC, b ASC, c DESC LIMIT 1
>
> You can then remember the value(s) of some column(s) of the row returned, and 
> watch for the same one(s) when you do your desired SELECT.
>
> Depending on the number of rows in your result set, this may or may not be 
> faster than counting the number of rows.
> ___
> 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] Last record

[Jens Alfke]

> On Oct 15, 2019, at 9:44 AM, Simon Slavin  wrote:
> 
> You can then remember the value(s) of some column(s) of the row returned, and 
> watch for the same one(s) when you do your desired SELECT.

As long as all the result rows are unique…

All of these workaround seem more expensive/complex than just adapting your 
code so it doesn't have to know the last row in advance.

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

Re: [sqlite] Last record

[Don V Nielsen]

Pardon me for being thick. But the end of what? The end of the sqlite file?
The end of a table? The end of a select?

I always thought there was no such thing as "a start or an end" as the
database is basically air until you request something from it. Even when
you have something, it could change in the very next exec of the query. So
what is the "start" and what is the "end"?

Looking at a sqlite file as a whole, is it the row at the very end of the
file or the last row inserted, which could be located in some page that is
not even the end of the file.

I don't think I understand too well.

On Tue, Oct 15, 2019 at 11:53 AM Jens Alfke  wrote:

>
>
> > On Oct 15, 2019, at 9:44 AM, Simon Slavin  wrote:
> >
> > You can then remember the value(s) of some column(s) of the row
> returned, and watch for the same one(s) when you do your desired SELECT.
>
> As long as all the result rows are unique…
>
> All of these workaround seem more expensive/complex than just adapting
> your code so it doesn't have to know the last row in advance.
>
> —Jens
> ___
> 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] Last record

[Chris Green]

Philippe RIO <51...@protonmail.ch> wrote:
> A short question : how could I know if I am reading the last record with
> sqlite  (sqlite3_step)? sqlite3_step only returns SQLITE_ROW. Is there a
> function for that case which returns SQLITE_DONE? A function which is one
> record in advance from sqlite3_step.
> 
What do you mean by "last record"?

-- 
Chris Green
·

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

Re: [sqlite] Last record

[Keith Medcalf]

On Tuesday, 15 October, 2019 09:35, Philippe RIO <51...@protonmail.ch> wrote:

>A short question : how could I know if I am reading the last record with
>sqlite  (sqlite3_step)? sqlite3_step only returns SQLITE_ROW. Is there a
>function for that case which returns SQLITE_DONE? A function which is one
>record in advance from sqlite3_step.

When sqlite3_step returns SQLITE_DONE there are no more rows.

A prepared statement is a row generator.  Each time you ask it to produce a row 
(sqlite3_step) it does whatever needs to be done to get you the next row and 
gives it to you (SQLITE_ROW).  Eventually it cannot generate another row and 
you get the message that the generator is empty (SQLITE_DONE).

Think of sqlite3 as a maid.  You place your order for peanuts with the maid 
(sqlite3_prepare).  The maid then tells you whether the order was accepted or 
not (SQLITE_OK or some other return code).  Every time you want a peanut you 
tell the maid to give you a peanut (sqlite3_exec).  If this is the first time 
that you have asked for a peanut, then the maid will have to run to the store 
and buy a tin of peanuts, open the tin, and give you a peanut (SQLITE_ROW) -- 
though it might be discovered that the tin is empty to begin with, in which 
case the maid will tell you so when you first ask for a peanut (SQLITE_DONE).  
Each time subsequently that you ask for a peanut the maid will simply give you 
another peanut from the tin.  Eventually, the tin will be empty and the maid 
will toss the empty tin in the rubbish and tell you that the tin is empty 
(SQLITE_DONE).  You may ask for another peanut in which case the maid will 
start all over again and go to the store for another tin of peanuts.  Lather, 
rinse, repeat.  Sometimes the store may be out of tins of peanuts, or the maid 
may get hit by a bus and killed on the way to and from the store, or may die of 
a heart attack in between you requesting peanuts.  In this case the butler will 
give you an error indication (SQLITE_ERROR) telling you what went awry.

Your only way of getting peanuts is to ask the maid for one, you cannot see the 
tin nor can you look inside it.  The only way that you have to know that the 
tin is empty is that when you ask for a peanut the maid replies "Sorry luv, but 
the tin is empty", rather than giving you a peanut.

-- 
The fact that there's a Highway to Hell but only a Stairway to Heaven says a 
lot about anticipated traffic volume.



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

Re: [sqlite] Last record

[Jose Isaias Cabrera]

Keith Medcalf, on Tuesday, October 15, 2019 02:26 PM, wrote...
>
>
> On Tuesday, 15 October, 2019 09:35, Philippe RIO, on
>
> >A short question : how could I know if I am reading the last record with
> >sqlite  (sqlite3_step)? sqlite3_step only returns SQLITE_ROW. Is there a
> >function for that case which returns SQLITE_DONE? A function which is one
> >record in advance from sqlite3_step.
>
> When sqlite3_step returns SQLITE_DONE there are no more rows.
>
> A prepared statement is a row generator.  Each time you ask it to produce a
> row (sqlite3_step) it does whatever needs to be done to get you the next row
> and gives it to you (SQLITE_ROW).  Eventually it cannot generate another row
> and you get the message that the generator is empty (SQLITE_DONE).

[]clip]

> The only way that you have to know that the tin is empty is that when you ask
> for a peanut the maid replies "Sorry luv, but the tin is empty", rather than
> giving you a peanut.

Keith, may I say thank you.  Your parables are so teach-friendly, and more than 
that, funny.  I appreciate them. Thanks.

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

Re: [sqlite] Last record

[Don V Nielsen]

Keith, what if one has a peanut allergy?

On Tue, Oct 15, 2019 at 1:33 PM Jose Isaias Cabrera 
wrote:

>
>
> Keith Medcalf, on Tuesday, October 15, 2019 02:26 PM, wrote...
> >
> >
> > On Tuesday, 15 October, 2019 09:35, Philippe RIO, on
> >
> > >A short question : how could I know if I am reading the last record with
> > >sqlite  (sqlite3_step)? sqlite3_step only returns SQLITE_ROW. Is there a
> > >function for that case which returns SQLITE_DONE? A function which is
> one
> > >record in advance from sqlite3_step.
> >
> > When sqlite3_step returns SQLITE_DONE there are no more rows.
> >
> > A prepared statement is a row generator.  Each time you ask it to
> produce a
> > row (sqlite3_step) it does whatever needs to be done to get you the next
> row
> > and gives it to you (SQLITE_ROW).  Eventually it cannot generate another
> row
> > and you get the message that the generator is empty (SQLITE_DONE).
>
> []clip]
>
> > The only way that you have to know that the tin is empty is that when
> you ask
> > for a peanut the maid replies "Sorry luv, but the tin is empty", rather
> than
> > giving you a peanut.
>
> Keith, may I say thank you.  Your parables are so teach-friendly, and more
> than that, funny.  I appreciate them. Thanks.
>
> josé
> ___
> 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] rationale for private archives of the SQLite mailing list and suppressed reply-to

[Warren Young]

On Oct 15, 2019, at 8:07 AM, Peter da Silva  wrote:

> A mail server speaks SMTP for both inbound and outbound

That’s only useful if you’ve configured Fossil to integrate with a third-party 
bidirectional SMTP server, which is *not* the only way to configure Fossil’s 
email integration:

https://fossil-scm.org/home/doc/trunk/www/alerts.md#advanced

Even if you did decide to tie your Fossil instance to a separate SMTP server 
for outbound email alerts, it’s a separate software integration for the inbound 
path, as I pointed out in my prior reply.  Outbound requires writing an RFC 
2822 message to a file or pipe, then calling “sendmail -ti” or similar on it.  
Inbound requires integrating with mbox, Maildir, DMBS storage, etc.  You don’t 
get one for free by writing the other.

Are you volunteering to write at least one of these inbound integrations?  drh 
hasn’t decided to write any of them yet, and none of the other big Fossil 
contributors have decided they want to take such a project on, either.  Someone 
needs to have the itch; who?

Then with one of those written, we’ll want several more, because there’s half a 
dozen common alternatives to the way this can work.

As I said in the prior reply, my impression is that drh simply isn’t interested 
in doing any of these, because he’d rather finish his own SMTP server, the 
partially-complete one currently built into Fossil.  But his time isn’t 
infinite, so he’s apparently got better things to do than finishing it.

Maybe he’ll eventually give up on that project and write one or more of these 
integrations with a third-party SMTP server.  But that also has yet to 
eventuate.

> IMAP/POP/webmail is all part of the user interface stack…

That was just an illustration to show that the ability to send email via SMTP 
doesn’t mean you automatically get the ability to receive email via SMTP.  Even 
when the same software is used for both communication directions, it’s a 
separate integration to do both tasks.

In any case, a web forum is also part of the UI stack, so it is no surprise 
that Fossil Forums should also exhibit the sorts of asymmetries you find in 
other email-to-thingy systems.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Last record

[Keith Medcalf]

On Tuesday, 15 October, 2019 13:52, Don V Nielsen  wrote:

>Keith, what if one has a peanut allergy?

Well, if one were allergic to beans (colloquially called peanuts for some 
reason, even though they are not nuts) then I suppose real nuts would do.

I have a big tin of mixed Honey Roasted Nuts (Cashews, Almonds and Pistachios, 
so says the label) right beside me.  Though it does say that besides those and 
other nuts it might also contain beans (peanuts) and bean (peanut) oil ... so 
obviously YMMV :)

Interestingly I also have nearby an empty generic (Presidents Choice) plastic 
bottle of Honey Roasted Peanuts (the maid is not so efficient here at throwing 
the empties in the refuse).  The label, interestingly enough, warns that it 
might also contain actual nuts.

Perhaps I should have used Gummy Bears.  Are people allergic to those?  Do they 
even come in a tin (so you cannot see what is inside)?  
Or perhaps Sardines.  For sure those come in tins and do not contain beans 
(peanuts) or even real nuts ... or at least the Brunswick ones don't.

:)

-- 
The fact that there's a Highway to Hell but only a Stairway to Heaven says a 
lot about anticipated traffic volume.



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

[sqlite] Table was deleted on macOS

[t...@qvgps.com]

Hi Group,

here is a very strange and rare fault which one of our users experienced on 
macOS .

He was working in our app for a while creating a lot of new data.
So over several hours many INSERT INTO mytable 
Without any errors.

Then he did a query SELECT COUNT(*) FROM mytable WHERE  (deleted=0 OR deleted 
IS NULL) AND IdCat=2
and an exception occurs:
"no such table: mytable"

The client sent us the database and the table "mytable" was really gone.
The database also seems to be fine, no errors.
How can this happen?
In our app is no DROP TABLE command, what else can cause a table to be deleted?

We have a few hundred copies of our app out, and this is the first time this 
error happens.
Its on macOS and the sqlite-version is 3024000


Thanks
Tom



/
**   Flemming Software Development CC
**   Thomas Flemming
**   PO Box 81244
**   Windhoek, Namibia
**   http://www.quovadis-gps.com
**   mail  t...@qvgps.com
**   +264 (0)81 3329923Nam mobile
**   +49  (0)175 7313081   D mobile
**   +49  (0)6182 8492599  D office
***/

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

Re: [sqlite] Last record

[Doug]

How about something like this that costs more to run:

Given a table T with columns A, B, C,..
BEGIN TRANSACTION
SELECT Count(*) AS Count [filter spec];
SELECT A,B,C,... [filter spec];
ROLLBACK or COMMIT

Doug

> -Original Message-
> From: sqlite-users 
> On Behalf Of Simon Slavin
> Sent: Tuesday, October 15, 2019 8:35 AM
> To: SQLite mailing list 
> Subject: Re: [sqlite] Last record
> 
> On 15 Oct 2019, at 4:34pm, Philippe RIO <51...@protonmail.ch>
> wrote:
> 
> > how could I know if I am reading the last record with
> > sqlite  (sqlite3_step)?
> 
> Sorry, there's no way to do that for some arbitrary SELECT.
> Because SQLite itself may not know.
> 
> SQLite does not always process your query and store all the
> results in memory.  If there's an ideal index for your query, each
> call to _step() just one more row.  SQLite itself doesn't know it
> has reached the end until it gets an error because it runs off the
> end of the index.
> ___
> 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] Table was deleted on macOS

[Simon Slavin]

On 15 Oct 2019, at 10:11pm, t...@qvgps.com wrote:

> "no such table: mytable"
> 
> The client sent us the database and the table "mytable" was really gone.  The 
> database also seems to be fine, no errors.

Do you mean by that that you ran integrity_check() ?

Is everything else there, or did another table, or rows from another table, 
disappear ?

Did the client's computer crashed or otherwise failed at any point ?

Is there a chance that the client tried to mess with the table using a database 
editor tool ?

Does the client have backups ?  Can you look at them and isolate the daterange 
during which the problem occurred ?

> In our app is no DROP TABLE command, what else can cause a table to be 
> deleted?

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

Re: [sqlite] Last record

[Adam Levy]

Why can't the knowledge of the "last row" be obtained and used by the
application after _step returns SQLITE_DONE?

Although what Doug suggested could work, it doesn't make sense to me to add
an extra count or max query just to know how many results will be returned
when those results will be queried anyway.


On Tue, Oct 15, 2019, 1:16 PM Doug  wrote:

> How about something like this that costs more to run:
>
> Given a table T with columns A, B, C,..
> BEGIN TRANSACTION
> SELECT Count(*) AS Count [filter spec];
> SELECT A,B,C,... [filter spec];
> ROLLBACK or COMMIT
>
> Doug
>
> > -Original Message-
> > From: sqlite-users 
> > On Behalf Of Simon Slavin
> > Sent: Tuesday, October 15, 2019 8:35 AM
> > To: SQLite mailing list 
> > Subject: Re: [sqlite] Last record
> >
> > On 15 Oct 2019, at 4:34pm, Philippe RIO <51...@protonmail.ch>
> > wrote:
> >
> > > how could I know if I am reading the last record with
> > > sqlite  (sqlite3_step)?
> >
> > Sorry, there's no way to do that for some arbitrary SELECT.
> > Because SQLite itself may not know.
> >
> > SQLite does not always process your query and store all the
> > results in memory.  If there's an ideal index for your query, each
> > call to _step() just one more row.  SQLite itself doesn't know it
> > has reached the end until it gets an error because it runs off the
> > end of the index.
> > ___
> > 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] Table was deleted on macOS

[t...@qvgps.com]

-- Originalnachricht --
Von: "Simon Slavin" mailto:slav...@bigfraud.org>>
An: "SQLite mailing list" 
mailto:sqlite-users@mailinglists.sqlite.org>>
Gesendet: 15.10.2019 23:24:17
Betreff: Re: [sqlite] Table was deleted on macOS

On 15 Oct 2019, at 10:11pm, t...@qvgps.com wrote:

"no such table: mytable"

The client sent us the database and the table "mytable" was really gone. The 
database also seems to be fine, no errors.

Do you mean by that that you ran integrity_check() ?
is ok



Is everything else there, or did another table, or rows from another table, 
disappear ?

everything else is there, just this one table is gone.


Did the client's computer crashed or otherwise failed at any point ?
He didn't report that, I believe its quiet unlikely with a 2015 MacBookPro.



Is there a chance that the client tried to mess with the table using a database 
editor tool ?
no



Does the client have backups ? Can you look at them and isolate the daterange 
during which the problem occurred ?
no



In our app is no DROP TABLE command, what else can cause a table to be deleted?

ALTER TABLE RENAME
There is no code in the app, which is altering or dropping any table.

Tom


___
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] Table was deleted on macOS

[Simon Slavin]

Sorry, I have no other ideas.  There is no reason for a table to disappear.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Last record

[Warren Young]

On Oct 15, 2019, at 1:52 PM, Don V Nielsen  wrote:
> 
> what if one has a peanut allergy?

You’re joking, but it gives us cause to extend the fable profitably: use the 
proper WHERE clause.

SELECT * FROM food WHERE type != 'peanuts'

The maid delivers whatever you ask for, within the limits specified within the 
fable.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Table was deleted on macOS

[Peter da Silva]

Check the local time machine backups? Even if you don't have a TM backup
drive Mojave maintains on-drive backups.

On Tue, 15 Oct 2019, 16:54 Simon Slavin,  wrote:

> Sorry, I have no other ideas.  There is no reason for a table to disappear.
> ___
> 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] Last record

[Wolfgang Enzinger]

Am Tue, 15 Oct 2019 13:36:37 -0800 schrieb Adam Levy:

> Although what Doug suggested could work, it doesn't make sense to me to add
> an extra count or max query just to know how many results will be returned
> when those results will be queried anyway.

One typical use case I can think of is that one wants to display a progress
bar during population of a list control with a query result. Impossible as
long as you don't know beforehand what 100% is by means of an absolute
number.

Maybe that's the reason why those percentage based progress bars are more
and more replaced by animation controls nowadays that just indicate that
something is still going on, without any kind of prediction how much more
time it will take. ;-)

Wolfgang

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

[sqlite] Network file system that support sqlite3 well

[Peng Yu]

Hi,

I'd like to use sqlite3 db files on many compute nodes. But they
should access the same storage device for the sqlite3 db files. The
directory storing the db files looks the same on any compute node
logically---the storage is mounted at the same mount point on the
compute nodes.

To achieve this, I think a network file system (could be distributed
(parallel) file system). But I know NFS is very bad at supporting
sqlite3. Many other distributed (parallel) file systems also are not
designed to support sqlite3, either.

Is there a solution that are known to fill in this niche? Thanks.

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

Re: [sqlite] Table was deleted on macOS

[Jens Alfke]

Was the database in a folder managed by Dropbox or Google Drive or iCloud 
Drive, i.e. where some background agent could replace it with a 'newer' copy 
from another computer?

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

Re: [sqlite] Network file system that support sqlite3 well

[Jens Alfke]

> On Oct 15, 2019, at 3:47 PM, Peng Yu  wrote:
> 
> I'd like to use sqlite3 db files on many compute nodes. But they
> should access the same storage device for the sqlite3 db files.

Why not use an actual client-server database system like MySQL? It's optimized 
for this use case, so it incurs a lot less disk (network) I/O.

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

Re: [sqlite] Network file system that support sqlite3 well

[Simon Slavin]

On 15 Oct 2019, at 11:47pm, Peng Yu  wrote:

> Is there a solution that are known to fill in this niche? Thanks.

Unfortunately, no.  Multiuser SQLite depends on locking being implemented 
correctly.  The developers haven't found any Network File Systems which do 
this.  Unless one of the readers of this list wants to tell me otherwise.

Jens' post suggesting that you use a proper client/server database system is my 
only solution.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Network file system that support sqlite3 well

[Roman Fleysher]

I know for sure that IBM's GPFS guarantees locking. I think GPFS is "global 
parallel file system". It is a distributed file system. But it will be rather 
slow. If only few jobs run in parallel,  all will be ok. Locking will always 
guarantee database integrity.

With lots of jobs,  you will see you have to increase sqlite timeouts to hours. 
Waiting for a lock will be much longer than transaction,  obviously. If 
transaction takes 1 second,  with 1000 jobs,  you will need timeout of 1000 
seconds. But locking adds very large overhead. Timeout will have to be 2 or 3 
times that.

If GPFS is loaded by other jobs (from other users,  not even sqlite users)  the 
wait times will increase.

Roman



Sent from my T-Mobile 4G LTE Device


 Original message 
From: Simon Slavin 
Date: 10/16/19 12:51 AM (GMT-05:00)
To: SQLite mailing list 
Subject: Re: [sqlite] Network file system that support sqlite3 well

On 15 Oct 2019, at 11:47pm, Peng Yu  wrote:

> Is there a solution that are known to fill in this niche? Thanks.

Unfortunately, no.  Multiuser SQLite depends on locking being implemented 
correctly.  The developers haven't found any Network File Systems which do 
this.  Unless one of the readers of this list wants to tell me otherwise.

Jens' post suggesting that you use a proper client/server database system is my 
only solution.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
https://nam02.safelinks.protection.outlook.com/?url=http%3A%2F%2Fmailinglists.sqlite.org%2Fcgi-bin%2Fmailman%2Flistinfo%2Fsqlite-users&data=02%7C01%7Croman.fleysher%40einstein.yu.edu%7C8e768b55426246b957cb08d751f481a2%7C04c70eb48f2648079934e02e89266ad0%7C1%7C1%7C637067982900220847&sdata=PXmUPthCG64C1kmJ1RmTySnsYV1GCaaz1LIoO7g3cQU%3D&reserved=0
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] [EXTERNAL] Last record

[Hick Gunter]

The order of rows returned by a query is undefined - i.e. from the point of 
view of the application, a random member of the result set will be returned 
last - unless you include an ORDER BY clause that uniquely defines the order of 
the records to be returned. Given the latter, it is easy to define an exactly 
opposite ORDER BY clause and retrieve only the first record via LIMIT 1.

Why do you think you need to know if/how many records remain?

-Ursprüngliche Nachricht-
Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im 
Auftrag von Philippe RIO
Gesendet: Dienstag, 15. Oktober 2019 17:35
An: sqlite-users@mailinglists.sqlite.org
Betreff: [EXTERNAL] [sqlite] Last record

A short question : how could I know if I am reading the last record with sqlite 
 (sqlite3_step)? sqlite3_step only returns SQLITE_ROW. Is there a function for 
that case which returns SQLITE_DONE? A function which is one record in advance 
from sqlite3_step.

Thank every one



-

Kenavo

https://md5finder.blogspot.com/
(Never be pleased, always improve)
--
Sent from: http://sqlite.1065341.n5.nabble.com/
___
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] [EXTERNAL] Re: Last record

[Hick Gunter]

Then the first peanut may well be the last one, irrespective of the cardinality 
of the tin.

-Ursprüngliche Nachricht-
Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im 
Auftrag von Don V Nielsen
Gesendet: Dienstag, 15. Oktober 2019 21:52
An: SQLite mailing list 
Betreff: [EXTERNAL] Re: [sqlite] Last record

Keith, what if one has a peanut allergy?

On Tue, Oct 15, 2019 at 1:33 PM Jose Isaias Cabrera 
wrote:

>
>
> Keith Medcalf, on Tuesday, October 15, 2019 02:26 PM, wrote...
> >
> >
> > On Tuesday, 15 October, 2019 09:35, Philippe RIO, on
> >
> > >A short question : how could I know if I am reading the last record
> > >with sqlite  (sqlite3_step)? sqlite3_step only returns SQLITE_ROW.
> > >Is there a function for that case which returns SQLITE_DONE? A
> > >function which is
> one
> > >record in advance from sqlite3_step.
> >
> > When sqlite3_step returns SQLITE_DONE there are no more rows.
> >
> > A prepared statement is a row generator.  Each time you ask it to
> produce a
> > row (sqlite3_step) it does whatever needs to be done to get you the
> > next
> row
> > and gives it to you (SQLITE_ROW).  Eventually it cannot generate
> > another
> row
> > and you get the message that the generator is empty (SQLITE_DONE).
>
> []clip]
>
> > The only way that you have to know that the tin is empty is that
> > when
> you ask
> > for a peanut the maid replies "Sorry luv, but the tin is empty",
> > rather
> than
> > giving you a peanut.
>
> Keith, may I say thank you.  Your parables are so teach-friendly, and
> more than that, funny.  I appreciate them. Thanks.
>
> josé
> ___
> 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


___
 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