Re: [sqlite] sqlite3_db_filename returns an empty string when null pointer is promised

I recently ran into this as well. NULL is not the same as “”, and it took me a really long time of debugging before I realized it was the sqlite3 documentation at fault. > On Mar 13, 2019, at 6:03 PM, Alex Alabuzhev wrote: > > Hi, > > https://www.sqlite.org/c3ref/db_filename.html: > >> If

Re: [sqlite] FTS5 Transaction Leads to OOB Read

Chu wrote: > The code: > > ``` > CREATE VIRTUAL TABLE t1 USING fts5(content); > > BEGIN; > INSERT INTO t1 (content) VALUES(''); > SELECT * FROM > t1('*'); > END; > ``` > > As you

Re: [sqlite] Backing up a SQLite database without the CLI

On 18 Mar 2019, at 3:21pm, Jonathan Moules wrote: > At this point I'm starting to think that the best option is to create a new > database with the requisite structure and copy the data across via an ATTACH > (there are only two tables and one will almost always be empty at this point). That

Re: [sqlite] picking random subset of rows

> On 18 Mar 2019, at 16:15, Dan Kennedy wrote: > > > In SQLite, a correlated sub-query on the RHS of an IN(...) operator may be > rerun every time the IN(...) test is required. And if that sub-query contains > "random()" it might return a different result every time. > > Your words suggest

Re: [sqlite] picking random subset of rows

On 18/3/62 17:36, Kevin Martin wrote: Hi, I am trying to use a correlated subquery with an 'order by random() limit 2' to pick upto two random rows for each value in the outer query. I am not sure if I am doing this correctly, but the number of rows I am getting seems to vary randomly which

Re: [sqlite] Backing up a SQLite database without the CLI

If the php sqlite bindings are incomplete and don't support the backup functions, write a small program in C that uses them to copy a database, and execute that from the php code? On Mon, Mar 18, 2019, 8:24 AM Jonathan Moules wrote: > Hi Simon, > > Thanks for your thoughts. Sorry, I should have

Re: [sqlite] Backing up a SQLite database without the CLI

Hi Simon, Thanks for your thoughts. Sorry, I should have been clearer: I have no way of knowing if there are other open connections to the file - there may be as it's a web-application. So I'll assume there are connections. At this point I'm starting to think that the best option is to

Re: [sqlite] Backing up a SQLite database without the CLI

On 18 Mar 2019, at 1:10pm, Jonathan Moules wrote: > I was wondering if there was a good way of backing up an SQLite database if > you do *not* have access to the SQLite command line tool (which I know has > .backup - https://stackoverflow.com/a/25684912). [snip] > I've considered simply

[sqlite] Backing up a SQLite database without the CLI

Hi List, I was wondering if there was a good way of backing up an SQLite database if you do *not* have access to the SQLite command line tool (which I know has .backup - https://stackoverflow.com/a/25684912). The new VACUUM INTO (https://www.sqlite.org/lang_vacuum.html#vacuuminto) is not an

[sqlite] FTS5 Transaction Leads to NULL Pointer

The code: ``` CREATE VIRTUAL TABLE t1 USING fts5(content); INSERT INTO t1 VALUES(''); BEGIN ; DELETE FROM t1 WHERE rowid = 1; SELECT * FROM t1 WHERE content MATCH ''; INSERT INTO t1 VALUES(''); SELECT * FROM t1 WHERE

[sqlite] FTS5 Transaction Leads to OOB Read

The code: ``` CREATE VIRTUAL TABLE t1 USING fts5(content); BEGIN; INSERT INTO t1 (content) VALUES(''); SELECT * FROM t1('*'); END; ``` As you can see, it creates a virtual table

Re: [sqlite] Query planner: Scanning subqueries vs using automatic covering index

Thanks to all who have replied, very informative! :) This is just a database for own personal use so it's not a big deal in any way, mainly trying to get a better understanding of how Sqlite works here. I'll note that the sql queries are not static inside my application but they are generated

[sqlite] picking random subset of rows

Hi, I am trying to use a correlated subquery with an 'order by random() limit 2' to pick upto two random rows for each value in the outer query. I am not sure if I am doing this correctly, but the number of rows I am getting seems to vary randomly which doesn't make sense to me. If i replace

Re: [sqlite] Recursive CTE on tree with doubly linked items

On Mon, Mar 18, 2019 at 10:21 AM Keith Medcalf wrote: > requires a "gentlemen's agreement" to only put positive values in the > position column (meaning the database cannot enforce this, you need to do > it at the application level) > Can't this be done with a before insert trigger? sqlite>

Re: [sqlite] Recursive CTE on tree with doubly linked items

The trigger program will have update anomalies (violation of the UNIQUE constraint for example) as well as performance issues unless the data in the tree is tiny (since it must visit every row in the tree even if it is not being updated). This will fix those issues (and also requires a

Re: [sqlite] Recursive CTE on tree with doubly linked items

Another way of implementing ordered siblings is to use a floating point “position” column instead of maintaining links to siblings via foreign keys. The advantage of a “position” column is that the data model maintains consistency automatically—you don’t need to painstakingly make sure all