Re: [sqlite] INSERT vs BEGIN

[Clemens Ladisch]

Rob Richardson wrote:
> I didn't know it is possible to insert multiple rows into a table using a
> command like this.  Is this just an SQLite feature, or is this part of the
> SQL standard?

This is defined since SQL-92, but only at the Full SQL conformance level.


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

Re: [sqlite] How to create a new table or overwrite an existing table in one command?

[Peng Yu]

For now, I just delete the db file if it exists already. So that I don’t
need to call DROP TABLE IF EXISTS mytab. I guess directly deleting the db
file can be faster than the latter. Is it so?

On Tue, Sep 3, 2019 at 3:06 PM Simon Slavin  wrote:

> On 3 Sep 2019, at 8:57pm, Peng Yu  wrote:
>
> > If I try to create a table that already exists, sqlite will give me an
> error. Is there way to issue one command to create a table, but if there is
> already a table with the same name, drop it then create the new table?
> Thanks.
>
> Assuming that the new table has a different structure to the old one, do
> it in two commands:
>
> DROP TABLE IF EXISTS MyTable;
> CREATE TABLE MyTable ...;
>
> Neither of those commands will generate an error.
>
> If you are sure that the new table has the same structure as the old one,
> you can do
>
> CREATE TABLE IF NOT EXISTS MyTable ...;
> DELETE FROM MyTable;
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
-- 
Regards,
Peng
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] lockfile but using db table row instead of file

[Keith Medcalf]

On Wednesday, 4 September, 2019 04:40, Peng Yu  wrote:

>The command line program `lockfile` locks based on files. I wants to do the
>same thing but based on a row in a table. For example (this is just one
>example feature, but all other features should also be preserved), if a
>given row is there, the process will hang unless some other process delete
>the given row. Multiple processes can compete for the same row. Because
>program like this should deal with concurrency, I am not clear how to make
>it bug free. Does anybody has a ready-to-use solution? Thanks.

Lockfile is a program that implements lockfiles for *nix shell scripts.  Why 
would you want to re-create that?  If you want to co-ordinate access to 
something between processes and/or threads in programs written in C then why 
not just use the OS provided named semaphore interface and skip all the extra 
complication (and let the OS do all the work, that’s why it was written)?

-- 
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] [EXTERNAL] How to create a new table or overwrite an existing table in one command?

[Hick Gunter]

There is only IF NOT EXISTS in the CREATE TABLE command. This assumes that you 
may want to keep a pre-existing table and the data it contains.

If you don't care about any old table or ist contents, just issue DROP TABLE IF 
EXISTS and CREATE TABLE in a single transaction.

-Ursprüngliche Nachricht-
Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im 
Auftrag von Peng Yu
Gesendet: Dienstag, 03. September 2019 21:58
An: SQLite mailing list 
Betreff: [EXTERNAL] [sqlite] How to create a new table or overwrite an existing 
table in one command?

Hi,

If I try to create a table that already exists, sqlite will give me an error. 
Is there way to issue one command to create a table, but if there is already a 
table with the same name, drop it then create the new table? Thanks.

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


___
 Gunter Hick | Software Engineer | Scientific Games International GmbH | 
Klitschgasse 2-4, A-1130 Vienna | FN 157284 a, HG Wien, DVR: 0430013 | (O) +43 
1 80100 - 0

May be privileged. May be confidential. Please delete if not the addressee.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] lockfile but using db table row instead of file

[Peng Yu]

The command line program `lockfile` locks based on files. I wants to do the
same thing but based on a row in a table. For example (this is just one
example feature, but all other features should also be preserved), if a
given row is there, the process will hang unless some other process delete
the given row. Multiple processes can compete for the same row. Because
program like this should deal with concurrency, I am not clear how to make
it bug free. Does anybody has a ready-to-use solution? Thanks.
-- 
Regards,
Peng
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] How to create a new table or overwrite an existing table in one command?

[Rob Willett]

Peng,

Dropping very large tables is time consuming. Dropping a 59GB table 
takes quite a long time for us even on fast hardware. Dropping smaller 
tables is faster though.


Not sure what size tables you have but something to think about. We 
experimented with new tables and changing old tables and for us, 
dropping the table and creating a new one was the fastest method but 
still took 40-60 mins from memory.


Rob

On 4 Sep 2019, at 11:18, Peng Yu wrote:

For now, I just delete the db file if it exists already. So that I 
don’t
need to call DROP TABLE IF EXISTS mytab. I guess directly deleting the 
db

file can be faster than the latter. Is it so?

On Tue, Sep 3, 2019 at 3:06 PM Simon Slavin  
wrote:



On 3 Sep 2019, at 8:57pm, Peng Yu  wrote:

If I try to create a table that already exists, sqlite will give me 
an
error. Is there way to issue one command to create a table, but if 
there is
already a table with the same name, drop it then create the new 
table?

Thanks.

Assuming that the new table has a different structure to the old one, 
do

it in two commands:

DROP TABLE IF EXISTS MyTable;
CREATE TABLE MyTable ...;

Neither of those commands will generate an error.

If you are sure that the new table has the same structure as the old 
one,

you can do

CREATE TABLE IF NOT EXISTS MyTable ...;
DELETE FROM MyTable;
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users


--
Regards,
Peng
___
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 to create a new table or overwrite an existing table in one command?

[Rob Willett]

Ingo,

I can't answer that as we have everything in one file. I suspect that in 
hindsight, putting a large table in a separate file would have been 
advantageous. However the one of cost of dropping a 59GB table has gone 
and our daily pruning and vacuuming  of the table is a few seconds. 
Hindsight is great :)


I brought this up as it was a major issue for us at the time and we 
wanted other people to be aware that deleting a table is SQLite is not 
as 'cheap' as other systems. This is the first time we have found SQLite 
to not be as good as anything else :) Please note that this is not meant 
to be criticism of SQLite but rather one of the tradeoffs we know about 
about and make. We win for some many other things that we have no 
issues.


Rob

On 4 Sep 2019, at 12:02, ingo wrote:


On 4-9-2019 12:24, Rob Willett wrote:

Peng,

Dropping very large tables is time consuming. Dropping a 59GB table
takes quite a long time for us even on fast hardware. Dropping 
smaller

tables is faster though.



When using (and dropping) this big tables, would it be of advantage to
put only that one table in a separate database and attach it when
needed. There would be no need then to drop it, one could just detach
and delete the db.

Ingo
___
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] SQLITE_BUSY, database is locked in "PRAGMA journal_mode"

[test user]

Thanks Rowan, this is useful.

Is it normal to get a `SQLITE_BUSY_RECOVERY` response from an API when:
- No processes have crashed.
- All API uses close/finalize their db/stmt objects.

I am testing some code I wrote to make sure it retires on BUSY by creating
many processes that acquire locks with `BEGIN IMMEDIATE`.

`SQLITE_BUSY_RECOVERY` occurs at around 7 processes requesting a write
lock, but not at 5 processes or below.

Does this indicate corrupted data?







On Mon, Sep 2, 2019 at 2:45 AM Rowan Worth  wrote:

> On Fri, 30 Aug 2019 at 04:18, test user 
> wrote:
>
> > B. Is there any method for determining lock transitions for connections?
> > - Is there an API?
> > - Would it be possible to use dtrace to instrument SQLite to detect
> > lock transitions?
> > - Where should I be looking?
> >
>
>  On unix sqlite uses fcntl() with cmd=F_SETLK on specific byte locations to
> acquire locks -- I'm not familiar with dtrace, but I've used strace + sed
> to watch sqlite lock activity before. eg:
>
> #!/bin/sh
>
> PID=$1
>
> replace() {
>  echo "s#F_SETLK, {type=F_$1, whence=SEEK_SET, start=$2, len=$3}#$4#"
> }
>
> strace -Ttt -ff -e trace=fcntl -p $PID 2>&1 |
> sed \
> -e "$(replace RDLCK 1073741824 1 acquireR{PENDING})" \
> -e "$(replace RDLCK 1073741825 1 acquireR{RESERVED})" \
> -e "$(replace RDLCK 1073741826 510 acquire{SHARED})" \
> -e "$(replace WRLCK 1073741824 1 acquireW{PENDING})" \
> -e "$(replace WRLCK 1073741825 1 acquireW{RESERVED})" \
> -e "$(replace WRLCK 1073741826 510 acquire{EXCLUSIVE})" \
> -e "$(replace UNLCK 1073741824 2 release{PENDING+RESERVED})" \
> -e "$(replace UNLCK 1073741824 1 release{PENDING})" \
> -e "$(replace UNLCK 1073741825 1 release{RESERVED})" \
> -e "$(replace UNLCK 1073741826 510 release{SHARED/EXCLUSIVE})" \
> -e "$(replace UNLCK 0 0 release{ALL})"
>
> -Rowan
> ___
> 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 to create a new table or overwrite an existing table in one command?

[ingo]

On 4-9-2019 12:24, Rob Willett wrote:
> Peng,
> 
> Dropping very large tables is time consuming. Dropping a 59GB table
> takes quite a long time for us even on fast hardware. Dropping smaller
> tables is faster though.
> 

When using (and dropping) this big tables, would it be of advantage to
put only that one table in a separate database and attach it when
needed. There would be no need then to drop it, one could just detach
and delete the db.

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

Re: [sqlite] How to create a new table or overwrite an existing table in one command?

[Simon Slavin]

On 4 Sep 2019, at 11:18am, Peng Yu  wrote:

> For now, I just delete the db file if it exists already. So that I don’t need 
> to call DROP TABLE IF EXISTS mytab. I guess directly deleting the db file can 
> be faster than the latter. Is it so?

The answer will change depending on your hardware and operating system.  So I 
cannot tell you "this way will always be faster".  You will have to experiment.

If the table already exists and its structure will not change (same columns, 
same indexes) then the fastest thing to do is usually

DELETE FROM MyTable;

SQLite spots the fact that there is no WHERE clause and does something special.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] How to create a new table or overwrite an existing table in one command?

[Keith Medcalf]

>OK. That makes sense. I will just delete the file. It also has the benefit
>of making the code simpler and avoiding using memory.

If you delete the database file then make sure you also delete any other files 
that might have been associated with it, such as left over journals and so 
forth.

-- 
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] [EXTERNAL] What concurrency level is of sqlite?

[Hick Gunter]

Just the same.

-Ursprüngliche Nachricht-
Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im 
Auftrag von Peng Yu
Gesendet: Dienstag, 03. September 2019 22:14
An: SQLite mailing list 
Betreff: [EXTERNAL] [sqlite] What concurrency level is of sqlite?

Hi,

In other words, if two processes write to the same sqlite file but to different 
tables, will one wait for the other? What if to the same table but different 
rows? Thanks.

--
Regards,
Peng
___
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 to create a new table or overwrite an existing table in one command?

[Peng Yu]

OK. That makes sense. I will just delete the file. It also has the benefit
of making the code simpler and avoiding using memory.

On Wed, Sep 4, 2019 at 5:25 AM Rob Willett 
wrote:

> Peng,
>
> Dropping very large tables is time consuming. Dropping a 59GB table
> takes quite a long time for us even on fast hardware. Dropping smaller
> tables is faster though.
>
> Not sure what size tables you have but something to think about. We
> experimented with new tables and changing old tables and for us,
> dropping the table and creating a new one was the fastest method but
> still took 40-60 mins from memory.
>
> Rob
>
> On 4 Sep 2019, at 11:18, Peng Yu wrote:
>
> > For now, I just delete the db file if it exists already. So that I
> > don’t
> > need to call DROP TABLE IF EXISTS mytab. I guess directly deleting the
> > db
> > file can be faster than the latter. Is it so?
> >
> > On Tue, Sep 3, 2019 at 3:06 PM Simon Slavin 
> > wrote:
> >
> >> On 3 Sep 2019, at 8:57pm, Peng Yu  wrote:
> >>
> >>> If I try to create a table that already exists, sqlite will give me
> >>> an
> >> error. Is there way to issue one command to create a table, but if
> >> there is
> >> already a table with the same name, drop it then create the new
> >> table?
> >> Thanks.
> >>
> >> Assuming that the new table has a different structure to the old one,
> >> do
> >> it in two commands:
> >>
> >> DROP TABLE IF EXISTS MyTable;
> >> CREATE TABLE MyTable ...;
> >>
> >> Neither of those commands will generate an error.
> >>
> >> If you are sure that the new table has the same structure as the old
> >> one,
> >> you can do
> >>
> >> CREATE TABLE IF NOT EXISTS MyTable ...;
> >> DELETE FROM MyTable;
> >> ___
> >> sqlite-users mailing list
> >> sqlite-users@mailinglists.sqlite.org
> >> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> >>
> > --
> > Regards,
> > Peng
> > ___
> > 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
>
-- 
Regards,
Peng
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] lockfile but using db table row instead of file

[Peng Yu]

On 9/4/19, Keith Medcalf  wrote:
>
> On Wednesday, 4 September, 2019 04:40, Peng Yu  wrote:
>
>>The command line program `lockfile` locks based on files. I wants to do the
>>same thing but based on a row in a table. For example (this is just one
>>example feature, but all other features should also be preserved), if a
>>given row is there, the process will hang unless some other process delete
>>the given row. Multiple processes can compete for the same row. Because
>>program like this should deal with concurrency, I am not clear how to make
>>it bug free. Does anybody has a ready-to-use solution? Thanks.
>
> Lockfile is a program that implements lockfiles for *nix shell scripts.  Why
> would you want to re-create that?  If you want to co-ordinate access to
> something between processes and/or threads in programs written in C then why
> not just use the OS provided named semaphore interface and skip all the
> extra complication (and let the OS do all the work, that’s why it was
> written)?

I use lockfile to ensuring one process runs when dealing with file
dependencies (as in build systems such as make which takes care of
file dependencies). Now I put some files in sqlar. Therefore, I'd like
to have something as a natural extension of lockfile's feature to deal
with files in sqlar. However, since sqlite db is locked per file,
there is no way to concurrently change two files in sqlar anyway. In
this sense, my question seems not to matter anymore.

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

Re: [sqlite] SQLITE_BUSY, database is locked in "PRAGMA journal_mode"

[Richard Hipp]

On 9/4/19, Simon Slavin  wrote:
> On 4 Sep 2019, at 12:39pm, test user  wrote:
>
>> Is it normal to get a `SQLITE_BUSY_RECOVERY`
>
> This code should only ever follow a crash,

Just to be clear, "crash" in the above statement can also mean
"program exits without calling sqlite3_close()".

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

Re: [sqlite] Window functions

[Dan Kennedy]

On 5/9/62 00:13, David Raymond wrote:

Kind of annoying that when the author shows a screenshot of the sample data he's using 
for his queries that he doesn't include 2 of the fields that are in the queries. Makes it 
harder to "play along at home"

For their ntile example (on page2) I don't think I've seen a window function used with a 
"group by". Does the ntile un-group the groups? Something just looks wrong 
there between the query and the results shown below it. But like you I don't know enough 
to say if that's right or if it's on crack.


You can run window functions on aggregate queries. The windowing step 
occurs logically after the aggregation.


It still looks right to me. Each output row contains a unique 
combination of territoryid/customerid, so no need for any "un-grouping". 
Of course, the input data doesn't feature any rows with duplicate 
territoryid/customerid values, so running the query without the GROUP BY 
and replacing "sum(subtotal)" with "subtotal" would produce the same 
results.


Dan






-Original Message-
From: sqlite-users  On Behalf Of 
Simon Slavin
Sent: Wednesday, September 04, 2019 12:15 PM
To: SQLite mailing list 
Subject: [sqlite] Window functions

I ran into this two-part article, probably on Hacker News:



I tried comparing it with



but I don't know enough to be able to tell whether the language used in the 
article is compatible with the way window functions are implemented in SQLite.  
Could someone who knows more than I do take a look and post a summary ?
___
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

[sqlite] Window functions

[Simon Slavin]

I ran into this two-part article, probably on Hacker News:



I tried comparing it with



but I don't know enough to be able to tell whether the language used in the 
article is compatible with the way window functions are implemented in SQLite.  
Could someone who knows more than I do take a look and post a summary ?
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Window functions

[Dan Kennedy]

On 4/9/62 23:14, Simon Slavin wrote:

I ran into this two-part article, probably on Hacker News:



I tried comparing it with



but I don't know enough to be able to tell whether the language used in the 
article is compatible with the way window functions are implemented in SQLite.  
Could someone who knows more than I do take a look and post a summary ?


I only skimmed it, but I think everything there is applicable to SQLite.

Although I think she's using "window frame" differently to the way we 
do. Not that it matters too much, as the term only occurs once in each 
of the two blog entries anyway.


Dan.


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

Re: [sqlite] Window functions

[David Raymond]

Kind of annoying that when the author shows a screenshot of the sample data 
he's using for his queries that he doesn't include 2 of the fields that are in 
the queries. Makes it harder to "play along at home"

For their ntile example (on page2) I don't think I've seen a window function 
used with a "group by". Does the ntile un-group the groups? Something just 
looks wrong there between the query and the results shown below it. But like 
you I don't know enough to say if that's right or if it's on crack.


-Original Message-
From: sqlite-users  On Behalf Of 
Simon Slavin
Sent: Wednesday, September 04, 2019 12:15 PM
To: SQLite mailing list 
Subject: [sqlite] Window functions

I ran into this two-part article, probably on Hacker News:



I tried comparing it with



but I don't know enough to be able to tell whether the language used in the 
article is compatible with the way window functions are implemented in SQLite.  
Could someone who knows more than I do take a look and post a summary ?
___
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 to create a new table or overwrite an existing table in one command?

[Peng Yu]

> If you delete the database file then make sure you also delete any other
> files that might have been associated with it, such as left over journals
> and so forth.

I never see those extra files in practice. Are they guaranteed to be
deleted automatically once an SQLite session is finished?

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

[sqlite] Differentiate between an empty result set and an error using Python3

[Rob Sciuk]

Forgive me if this is an FAQ, but in looking over the python3 interface to 
SQLITE3, I cannot see a way to get the result code (SQLITE_OK) after an

execute() command.

My use case is to differentiate between an empty row set (OK) vs an error 
of some kind in the query.


Anyone figured this out?

Cheers,
Rob.

--
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
Robert S. Sciuk r...@controlq.com
Principal Consultant905.706.1354
Control-Q Research  97 Village Rd. Wellesley, ON N0B 2T0
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] lockfile but using db table row instead of file

[Stephen Chrzanowski]

What Keith said, plus, SQLite does not do row locking, period.  It's a full
file lock when required.  SQLite has some embedded functionality to deal
with locking things like this, and your program will  need to be written to
be aware of how to deal with locking.

If you're attempting to fix race conditions, then you need to look at how
your programs are working with each other, either by communicating with
each other through FIFO files (If linux) or through DDE (Dynamic Data
Exchange) if Windows, or rely on the OS and SQLite provisions already out
there.  I've done some LIGHT work with DDE back in the Delphi 5 days, and
from what I remember, it was pretty interesting.

Work with the tools you have, and don't go reinvent the wheel.  (Unless the
purpose is to reinvent the wheel just for fun.)

On Wed, Sep 4, 2019 at 6:40 AM Peng Yu  wrote:

> The command line program `lockfile` locks based on files. I wants to do the
> same thing but based on a row in a table. For example (this is just one
> example feature, but all other features should also be preserved), if a
> given row is there, the process will hang unless some other process delete
> the given row. Multiple processes can compete for the same row. Because
> program like this should deal with concurrency, I am not clear how to make
> it bug free. Does anybody has a ready-to-use solution? Thanks.
> --
> Regards,
> Peng
> ___
> 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] sqlite: see: encryption

[Jens Alfke]

> On Sep 2, 2019, at 11:41 PM, Vadiraj Villivalam 
>  wrote:
> 
> With the open os like android providing keystore and key generation
> mechanism, we want to switch to this secure key generation mechanism and
> avoid generating  key ourselves. As the key store does not allow the key
> itself to be exported out, I would like to know if sqlite has a mechanism
> to leverage the key store way of en/decrypting it

I'm not familiar with Android's keystore, but I know the iOS/macOS Keychain 
quite well.

If the keystore _only_ supports keys that it creates & manages internally and 
won't export, then you can:
1. Create your own key for SQLite, e.g. by generating 32 securely-random bytes.
2. Ask the keystore to create a symmetric key.
3. Ask the keystore to use its key to encrypt the SQLite key [from step 1].
4. Store the resulting encrypted data somewhere, e.g. in a file.

To recover the SQLite key on the next launch:
1. Read the encrypted data [from step 4 above]
2. Ask the keystore to decrypt it using its managed key.
3. Use the resulting key to open the SQLite database.

But check the docs to see if there's a keystore API that lets you simply store 
a key you've generated yourself. If not, it probably has an API for storing 
passwords; you can then just base64-encode the key and store that as though it 
were a password. That's simpler than going through the above steps.

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

Re: [sqlite] How to create a new table or overwrite an existing table in one command?

[David Raymond]

> If you delete the database file then make sure you also delete any other
> files that might have been associated with it, such as left over journals
> and so forth.

I never see those extra files in practice. Are they guaranteed to be
deleted automatically once an SQLite session is finished?


Nope. If there was a problem in closing down they can hang around (which is 
their whole point for recovery). Also if a journal mode of "persit" was used. 
But mostly from incorrect closure.

So check for any -journal, -wal, or -shm files of the same name if you want to 
obliterate a database.
(Am I missing any others?)
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Differentiate between an empty result set and an error using Python3

[David Raymond]

If you run a query that returns no results, then cursor.fetchone() will return 
None, or cursor.fetchall() will return an empty list. If there is an error 
during the processing then some sort of exception should be raised.


-Original Message-
From: sqlite-users  On Behalf Of 
Rob Sciuk
Sent: Wednesday, September 04, 2019 2:18 PM
To: sqlite-users@mailinglists.sqlite.org
Subject: [sqlite] Differentiate between an empty result set and an error using 
Python3


Forgive me if this is an FAQ, but in looking over the python3 interface to 
SQLITE3, I cannot see a way to get the result code (SQLITE_OK) after an
execute() command.

My use case is to differentiate between an empty row set (OK) vs an error 
of some kind in the query.

Anyone figured this out?

Cheers,
Rob.

-- 
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
Robert S. Sciuk r...@controlq.com
Principal Consultant905.706.1354
Control-Q Research  97 Village Rd. Wellesley, ON N0B 2T0
___
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] Query planner regression with FTS4: slower path is chosen

[Dan Kennedy]

On 2/9/62 16:57, Paul wrote:

I has been a while without response, so I just bumping this message.


19 July 2019, 14:21:27, by "Paul" :


I have a test case when the regression can be observed in queries that
use JOINs with FTS4 tables, somewhere in between 3.22.0 and 3.23.0.
For some reason the planner decides to search non-FTS table first then
scan the whole FTS table. Version 3.22.0 is the last unaffected, while
issue is still present in HEAD.

Probably it has something to do with a fact that, according to EXPLAIN,
new version of planner ignores LEFT join and considers it just a JOIN.



Suspect that that is the change. The LEFT JOIN is equivalent to a 
regular join in this case due to the "bar = 1" term in the WHERE clause.


Running ANALYZE after the index is created in the example script causes 
SQLite to pick a better plan.


Or, changing the LEFT JOIN to CROSS JOIN works to force SQLite to pick 
the plan you want.


FTS5 does a little better with the query, but only because it runs 
faster - it still picks the slow plan. There might be room to improve 
this in FTS5, but probably not for FTS3/4, which are now focused very 
much on backwards compatibility.


Dan.





At least it feels that way, anyway.

Test case:


CREATE VIRTUAL TABLE search USING FTS4(text);

WITH RECURSIVE
  cnt(x) AS (
 SELECT 1
 UNION ALL
 SELECT x+1 FROM cnt
  LIMIT 2000
  )
INSERT INTO search(docid, text) SELECT x, 'test' || x FROM cnt;

CREATE TABLE foo(s_docid integer primary key, bar integer);

WITH RECURSIVE
  cnt(x) AS (
 SELECT 1
 UNION ALL
 SELECT x+1 FROM cnt
  LIMIT 2000
  )
INSERT INTO foo(s_docid, bar) SELECT x, 1 FROM cnt;

.timer on

-- Fast
SELECT COUNT() FROM search LEFT JOIN foo
  ON s_docid = docid
  WHERE bar = 1 AND search MATCH 'test*';

-- Fast
SELECT COUNT() FROM foo
  WHERE bar = 1
  AND s_docid IN (
SELECT docid FROM search WHERE search MATCH 'test*'
  );

-- Create index, as some real-life queries use searches by `bar`
CREATE INDEX foo_bar_idx ON foo (bar);

-- Slow
SELECT COUNT() FROM search LEFT JOIN foo
  ON s_docid = docid
  WHERE bar = 1 AND search MATCH 'test*';

-- As fast as before (current workaround)
SELECT COUNT() FROM foo
  WHERE bar = 1
  AND s_docid IN (
SELECT docid FROM search WHERE search MATCH 'test*'
  );


___
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] SQLITE_BUSY, database is locked in "PRAGMA journal_mode"

[Simon Slavin]

On 4 Sep 2019, at 12:39pm, test user  wrote:

> Is it normal to get a `SQLITE_BUSY_RECOVERY` 

This code should only ever follow a crash, or some operation which has 
corrupted a database.  If your hardware does not crash you should never see it. 
 Something is wrong.



> - No processes have crashed.
> - All API uses close/finalize their db/stmt objects.

Are you checking the result returned by all your operations to make sure they 
are SQLITE_OK ?

Are you interfering with SQLite by manually deleting database files, or 
deleting journal files, or by using fcntl() on SQLite datbases ?
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Differentiate between an empty result set and an error using Python3

[Keith Medcalf]

On Wednesday, 4 September, 2019 12:18, Rob Sciuk  wrote:

>Forgive me if this is an FAQ, but in looking over the python3 interface to
>SQLITE3, I cannot see a way to get the result code (SQLITE_OK) after an
>execute() command.

>My use case is to differentiate between an empty row set (OK) vs an error
>of some kind in the query.

>Anyone figured this out?

If there is an error then an exception will be thrown.  No exception means no 
error.  Otherwise, iterating over the cursor will return the rows.  If there 
are no rows then it is like iterating over an empty list -- there is nothing to 
return (the cursor object is a generator that yields row tuples and it will 
internally raise StopIteration when it is out of data to return, just like any 
other generator).

-- 
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] How to create a new table or overwrite an existing table in one command?

[Keith Medcalf]

On Wednesday, 4 September, 2019 11:22, Peng Yu  wrote:

>> If you delete the database file then make sure you also delete any other
>> files that might have been associated with it, such as left over journals
>> and so forth.

>I never see those extra files in practice. Are they guaranteed to be
>deleted automatically once an SQLite session is finished?

They are deleted when the last connection to a database is closed by an 
sqlite3_close() call.  Unless of course the program requests they stick around.

And of course you can never guarantee that the CPU will not be hit by a stray 
dark-matter particle causing a program to abort without cleaning itself up, or 
that the power will never fail, or any of a number of other reasons that those 
extra files might be present.  Do you want to accept the risk thst you will 
have to travel to fix something that is broken at 2:30 in the morning in the 
middle of a holiday while you are busy making sex on the beach when you could 
have with just a little tiny bit of aforethough avoided that inconvenience 
altogether?  Then again, perhaps I am just lazy and prefer things that "just 
work".

-- 
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] How to create a new table or overwrite an existing table in one command?

[Peng Yu]

> Nope. If there was a problem in closing down they can hang around (which is
> their whole point for recovery). Also if a journal mode of "persit" was
> used. But mostly from incorrect closure.
>
> So check for any -journal, -wal, or -shm files of the same name if you want
> to obliterate a database.
> (Am I missing any others?)

Is there a minimal work example (in software way but not hardware
failure way) to make these extra files stick around upon closing a
sqlite3 session so that I can have a proper test case to make sure I
always delete them? Thanks.

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

Re: [sqlite] Differentiate between an empty result set and an error using Python3

[E.Pasma]

> Op 5 sep. 2019, om 00:10 heeft Keith Medcalf  het 
> volgende geschreven:
> 
> 
> On Wednesday, 4 September, 2019 12:18, Rob Sciuk  wrote:
> 
>> Forgive me if this is an FAQ, but in looking over the python3 interface to
>> SQLITE3, I cannot see a way to get the result code (SQLITE_OK) after an
>> execute() command.
> 
>> My use case is to differentiate between an empty row set (OK) vs an error
>> of some kind in the query.
> 
>> Anyone figured this out?
> 
> If there is an error then an exception will be thrown.  No exception means no 
> error.  Otherwise, iterating over the cursor will return the rows.  If there 
> are no rows then it is like iterating over an empty list -- there is nothing 
> to return (the cursor object is a generator that yields row tuples and it 
> will internally raise StopIteration when it is out of data to return, just 
> like any other generator).
> 
> -- 
Possibly the Python documentation is overwhelming here as data can also be 
retrieved with explicit fetch steps. The example where the cursor is treated as 
a generator shows how simple it is:
>>> for row in c.execute('SELECT * FROM stocks ORDER BY price'):
print(row)

('2006-01-05', 'BUY', 'RHAT', 100, 35.14)
('2006-03-28', 'BUY', 'IBM', 1000, 45.0)
('2006-04-06', 'SELL', 'IBM', 500, 53.0)
('2006-04-05', 'BUY', 'MSFT', 1000, 72.0)

(from https://docs.python.org/3/library/sqlite3.html 
)

Personal note: even more simple is to use the execute method directly from the 
connection instance.

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

Re: [sqlite] How to create a new table or overwrite an existing table in one command?

[Keith Medcalf]

On Wednesday, 4 September, 2019 16:36, Peng Yu  wrote:

>> Nope. If there was a problem in closing down they can hang around (which
>> is their whole point for recovery). Also if a journal mode of "persit" was
>> used. But mostly from incorrect closure.

>> So check for any -journal, -wal, or -shm files of the same name if you
>> want to obliterate a database.
>> (Am I missing any others?)

>Is there a minimal work example (in software way but not hardware
>failure way) to make these extra files stick around upon closing a
>sqlite3 session so that I can have a proper test case to make sure I
>always delete them? Thanks.

The easiest way is to just terminate without calling sqlite3_close on an open 
database ... as in:

#include 
void main(int argc, char **argv)
{
sqlite3* db = 0;
sqlite3_stmt* stmt = 0;
if (sqlite3_open_v2(argv[1], , SQLITE_OPEN_READWRITE, NULL) == SQLITE_OK)
{
printf("Opened database %s\n", argv[1]);
if (sqlite3_prepare_v2(db, "BEGIN IMMEDIATE", -1, , NULL) == 
SQLITE_OK)
if (sqlite3_step(stmt) == SQLITE_DONE)
{
printf("BEGIN IMMEDIATE\n");
sqlite3_finalize(stmt);
if (sqlite3_prepare_v2(db, "create table crap(crap);", -1, 
, NULL) == SQLITE_OK)
if (sqlite3_step(stmt) == SQLITE_DONE)
printf("Leaving behind open transaction\n");
}
}
}

If the main file is "test.db" then you also have to delete "test.db-journal", 
"test.db-shm" and "test.db-wal" if they exist.

-- 
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] How to create a new table or overwrite an existing table in one command?

[Simon Slavin]

On 4 Sep 2019, at 11:36pm, Peng Yu  wrote:

> Is there a minimal work example (in software way but not hardware
> failure way) to make these extra files stick around upon closing a
> sqlite3 session so that I can have a proper test case to make sure I
> always delete them?

Perform an INSERT operation.  Have the program quit without calling _reset(), 
_finalize(), or closing the connection.

What happens depends on whether you're using WAL mode.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Differentiate between an empty result set and an error using Python3

[Keith Medcalf]

>Possibly the Python documentation is overwhelming here as data can also be
>retrieved with explicit fetch steps.

The APSW documentation is better.  The sqlite3 documentation is somewhat 
limited.

>>> import sqlite3
>>> db = sqlite3.connect('', isolation_level=None) # isolation_level=None turns 
>>> off automagic which does not work anyway
>>> db.execute('create table x(x)')


.fetchone() returns None if there is no rows, else it returns the row tuple:

>>> print(db.execute('select x from x').fetchone())
None

.fetchall() returns an list of rows (which will be empty if there are no rows):

>>> print(db.execute('select x from x').fetchall())
[]

Calling the iterator step function manually will throw StopIteration if there 
are no more rows (that is how "for row in iter(...)" knows to stop iterating -- 
the for catches the StopIteration exception and stops executing the loop):

>>> print(next(db.execute('select x from x')))
Traceback (most recent call last):
  File "", line 1, in 
StopIteration

>>> print(db.execute('select x from x').__next__())
Traceback (most recent call last):
  File "", line 1, in 
StopIteration


APSW works the same way.  https://github.com/rogerbinns/apsw

>>> import apsw
>>> db = apsw.Connection('')
>>> db.cursor().execute('create table x(x)')


>>> print(db.cursor().execute('select x from x').fetchone())
None

>>> print(db.cursor().execute('select x from x').fetchall())
[]

>>> print(next(db.cursor().execute('select x from x')))
Traceback (most recent call last):
  File "", line 1, in 
StopIteration

>>> print(db.cursor().execute('select x from x').__next__())
Traceback (most recent call last):
  File "", line 1, in 
StopIteration

--  
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