Re: [sqlite] Efficient ways to maintaining order rank / row_number() in a rather large set ?

2018-01-09 Thread Wade, William
If you were going to do this entirely in memory (perhaps in C, or some similar language), you would likely use some tree structure where each node keeps track of the number of descendants (direct and indirect) of that node. That allows the operations you describe to occur in O(log(N)) time.

Re: [sqlite] Best way to wipe out data of a closed database

2016-10-24 Thread Wade, William
It sounds like you've got a way forward on leaks via the malloc() system within the process space. Be aware that depending on your system (and the attackers' capabilities), you might have to worry about other leaks. For instance, if I did a query that involved a FLOAT index, and then closed

Re: [sqlite] Risk of damaging a non-sqlite file with `sqlite3 `?

2016-10-21 Thread Wade, William
> From: Clemens Ladisch [mailto:clem...@ladisch.de] > Sent: Friday, October 21, 2016 1:31 AM > It would be possible to open a 'wrong' file only if someone had created a > fake database file deliberately. I think that is generally correct, but "possible ... only if" is perhaps a bit strong.

Re: [sqlite] newbie has waited days for a DB build to complete. what's up with this.

2016-08-04 Thread Wade, William
A lot of speculation here. I am certainly no SQLite expert. Your input has 1g positions, taking 187gb, so averaging 187b/position. From your CREATE TABLE, it looks like to get that size most of your qfrom and qto are fairly long strings. I'm assuming there are a great many duplications in

Re: [sqlite] The upcoming "pi" release of SQLite

2016-07-25 Thread Wade, William
If too late for this release, consider it a request for the next release: Provide a way (pragma, or additional syntax on the command/virtual table) to have the command-line .import, and the new CSV virtual table, use NUMERIC column affinity when creating a new table. I like to think that 10 is

[sqlite] Incremental backup/sync facility?

2016-05-09 Thread Wade, William
> From: Stephan Buchert [mailto:stephanb007 at gmail.com] > Sent: Saturday, May 07, 2016 12:10 AM > Copying the WAL files is probably more efficient than the SQL text solutions > (considering that roughly 5 GB of binary data are weekly added), and it seems > easy to implement, so I'll probably

[sqlite] Random-access sequences

2016-03-01 Thread Wade, William
In RAM, the simple implementation would be to have a balanced tree, ordered by index, where every node knows how many elements are below it, and has pointers to its children. The tree will have O(logN) depth, following a pointer is O(1), and, and all of your operations involve a small

[sqlite] IS a SQLite db of small size as good as reliable cache?

2016-02-01 Thread Wade, William
For cache eviction, below are you referring to writing dirty pages to disk? If you are talking about cached reads, the evidence seems to point to many such pages remaining in some kind of cache. I perform a recursive grep (Cygwin) on a large directory tree. It takes 4.5m. I wait five minutes

[sqlite] INSERT OR IGNORE consuming too many rowids

2015-12-09 Thread Wade, William
I may be reading different documentation (or perhaps just misreading it). https://www.sqlite.org/autoinc.html says (for the no AUTOINCREMENT case, which applies to the original post) "If no ROWID is specified on the insert ... [the] usual algorithm is to give the newly created row a ROWID that

[sqlite] Non-transitive numeric equality

2015-11-06 Thread Wade, William
I have an engineering application, where double can be part of a key. In cases where I do a comparison that implies a test for equality, I don't necessarily care what happens in the really close cases. SELECT x,y FROM mytable WHERE x >= 1234 AND x < 5678 There may be about a 1 records in

[sqlite] How would sqlite read this from disk?

2015-10-29 Thread Wade, William

[sqlite] Any database unique ID across multiple connections ?

2015-09-25 Thread Wade, William
It depends a lot on what kind of duplications you are concerned with. For instance, a file-system copy would, presumably, have copied any "unique identifier" that may have been in the file, so it will no longer be unique. To detect that kind of duplication, I think you really need support at

[sqlite] Read data from database slow the first time but then very fast thereafter. How can I improve the speed for the first time read?

2015-08-06 Thread Wade, William
I'm an SQLite novice, so if I'm wrong about this, I hope someone will correct me. The first query will be slower than later queries for the reason Simon mentioned. However, it may still be possible to get some speed up of the first query, if it can avoid reading stuff not in the result set and

[sqlite] Replace an open database

2015-05-27 Thread Wade, William
I have an application file that I'm considering moving to sqlite. Some current behavior is: 1) Only one writing thread for the lifetime of a logical dataset. 2) The writing thread opens the file with exclusive write access (open fails if there is another writer, no other writers allowed, but