Re: [sqlite] INSERT vs BEGIN

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

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

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

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

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

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

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

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

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

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

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

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

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.

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

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

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

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

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

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

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

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

Re: [sqlite] [EXTERNAL] What concurrency level is of sqlite?

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

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

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

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

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

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

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.

Re: [sqlite] Window functions

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

[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

Re: [sqlite] Window functions

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

Re: [sqlite] Window functions

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

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

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

[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

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

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

Re: [sqlite] sqlite: see: encryption

> 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

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

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

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

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

Re: [sqlite] Query planner regression with FTS4: slower path is chosen

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

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

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.

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

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

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

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

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

> 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

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

> 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

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

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

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

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

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

>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