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

2019-03-18 Thread Joshua Thomas Wise
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

[sqlite] Backing up a SQLite database without the CLI

2019-03-18 Thread Jonathan Moules
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

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

2019-03-18 Thread Simon Slavin
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

2019-03-18 Thread Dan Kennedy
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] picking random subset of rows

2019-03-18 Thread Kevin Martin
> 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] Backing up a SQLite database without the CLI

2019-03-18 Thread Shawn Wagner
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

2019-03-18 Thread Simon Slavin
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

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

2019-03-18 Thread Jonathan Moules
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] FTS5 Transaction Leads to OOB Read

2019-03-18 Thread Dominique Pellé
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] sqlite3_db_filename returns an empty string when null pointer is promised

2019-03-18 Thread Joshua Thomas Wise
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] Recursive CTE on tree with doubly linked items

2019-03-18 Thread Keith Medcalf
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

2019-03-18 Thread Wout Mertens
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>

[sqlite] FTS5 Transaction Leads to NULL Pointer

2019-03-18 Thread Chu
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

2019-03-18 Thread Chu
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

[sqlite] picking random subset of rows

2019-03-18 Thread Kevin Martin
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] Query planner: Scanning subqueries vs using automatic covering index

2019-03-18 Thread niklas
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