Re: [sqlite] bug: failure to write journal reported as "disk I/O error"

On Tue, Sep 26, 2017 at 01:37:42PM -0700, Jens Alfke wrote: > > On Sep 26, 2017, at 1:17 PM, Guy Harris wrote: > > A user wouldn't know what to do with "you've exceeded your stored data > > quota”? > > A Turkish or Chinese user likely wouldn’t. (SQLite’s error messages > are

Re: [sqlite] [EXTERNAL] Common subexpression optimization of deterministic functions

On Thu, Sep 14, 2017 at 1:10 PM Simon Slavin wrote: > > > On 14 Sep 2017, at 5:55pm, R Smith wrote: > > > Richard, wouldn't it be possible to supply a wrapping function (perhaps > a hint function, like the likelihood() function), that takes another >

Re: [sqlite] JOIN vs. INTERSECT vs. WHERE IN (...) - speed and efficiency differences

On Sat, Sep 09, 2017 at 11:26:35PM +0200, R Smith wrote: > I think you are missing something or my explanation was not clear. > When I say "first test" I mean of THIS test suite, not the previous set from > 3 days ago. I meant the opposite. ___

Re: [sqlite] JOIN vs. INTERSECT vs. WHERE IN (...) - speed and efficiency differences

On Sat, Sep 09, 2017 at 05:56:03PM +0200, R Smith wrote: > *Findings* pertaining to JOIN, INTERSECT and WHERE IN (...) type queries on > tables *WITHOUT Row_ids*: > (This is the full test posted below because it is the one that matters most) > INTERSECT AND WHERE IN (...) queries posted similar

Re: [sqlite] Proof that a line has been modified

On Thu, Sep 07, 2017 at 10:16:15AM +0200, Paxdo wrote: > For security reasons, a customer wants to be sure that a database line > cannot be modified after its initial insertion (or unmodified without > being visible, with proof that the line has been modified). Including > by technicians who can

Re: [sqlite] JOIN vs. INTERSECT speed and efficiency

On Thu, Sep 07, 2017 at 09:51:07PM +0200, R Smith wrote: > INTERSECT will happily match however many columns you desire (and specify), > there is no need to match full records or single keys specifically. But the two queries on either side of the set operator must have the same number of columns

Re: [sqlite] JOIN vs. INTERSECT speed and efficiency

On Wed, Sep 06, 2017 at 07:43:07PM -0600, Keith Medcalf wrote: > Try the same test using 147 columns in each table. > > 1 column is rather trivial. Even a kindergarten kid could do it in no > time using crayons and the wall. > > [...] > > In other words except in very trivial cases (like

Re: [sqlite] JOIN vs. INTERSECT speed and efficiency

On Wed, Sep 06, 2017 at 11:54:35PM +0200, R Smith wrote: > It's still remarkable that in both tests 5 and 6 I've used the very same PK > setup, yet Test 6 was significantly faster with the added ORDER BY clause. > In tests 1 through 4 I did not use a PK at all, just plain INT data field, > but

Re: [sqlite] JOIN vs. INTERSECT speed and efficiency

On Wed, Sep 06, 2017 at 10:57:41PM +0200, R Smith wrote: > On 2017/09/06 8:26 PM, Nico Williams wrote: > >On Wed, Sep 06, 2017 at 08:05:57PM +0200, R Smith wrote: > >>-- Another interesting thing to note: The INTERSECT test produces ORDERED > >>-- output, which suggest

Re: [sqlite] JOIN vs. INTERSECT speed and efficiency

On Wed, Sep 06, 2017 at 08:05:57PM +0200, R Smith wrote: > -- Another interesting thing to note: The INTERSECT test produces ORDERED > -- output, which suggests that an ORDER-BY addition to the query would > -- favour the INTERSECT method. Nothing about INTERSECT requires it to produce ordered

Re: [sqlite] Enforcing uniqueness from multiple indexes

On Sun, Aug 20, 2017 at 05:17:16PM +, Wout Mertens wrote: > Oh wow, I didn't know about ON CONFLICT, thanks! > > Unfortunately the behavior on UPDATE (actually INSERT OR REPLACE in my > case) is not the desired behavior, it removes the row with the same k but > different id. PostgreSQL has

Re: [sqlite] Enforcing uniqueness from multiple indexes

An INSERT OR UPDATE sure would be nice. What i often do in cases like this is: UPDATE .. WHERE; INSERT .. SELECT .. WHERE NOT EXISTS (SELECT ..);. That's two statements -- if that's a problem, then you should use D. R. Hipp's trigger-based solution. ___

Re: [sqlite] Packing integer primary key with field bits

On Thu, Aug 10, 2017 at 03:36:31PM +0200, Clemens Ladisch wrote: > x wrote: > > I’m thinking about this more from the gain in speed rather than saving > > space. > > Database performance is usually limited by I/O, i.e., you gain speed by > saving space. Then proper compression (e.g., with zlib)

Re: [sqlite] Mailinglist question

On Wed, Aug 09, 2017 at 03:48:34PM -0600, Keith Medcalf wrote: > There is a solution since about 1984 ... it is called NNTP (Usenet > News). Google Groups is basically Usenet News with a (so some people > thing -- but not I -- I detest so-called web-forums) purty front end > to the news reader.

Re: [sqlite] Tutorials, books, video about SQLite

By far the best generic SQL book, IMO, is the O'Reilly "SQL" Pocket Guide. It's very small and yet fairly comprehensive. It covers Oracle, DB2, SQL Server, and MySQL. But it's very general and brief, and everything it has to teach you is generally applicable to PostgreSQL and SQLite3. Nico --

Re: [sqlite] Mailinglist question

On Wed, Aug 09, 2017 at 12:38:42PM -0700, Jens Alfke wrote: > > On Aug 9, 2017, at 12:31 PM, Nico Williams <n...@cryptonector.com> wrote: > > (It'd be great to have mostly-read-only public IMAP servers serving > > mailing list archives. I say mostly-read-only because one

Re: [sqlite] Mailinglist question

On Wed, Aug 09, 2017 at 09:10:58PM +0200, Lars Frederiksen wrote: > Is it possible to read the mails directly on a server instead of receiving > the mails constantly? There are several archives online, and if you're subscribed you can use your list subscription password to use the mailman

Re: [sqlite] calculated-value indexes are not covering?

On Wed, Aug 09, 2017 at 06:59:18PM +, Wout Mertens wrote: > but… index s is covering and only includes the field s? I thought a > covering index was one where all the data needed to satisfy the query is in > index? I would say that all the indexes here conform to that definition? No,

Re: [sqlite] calculated-value indexes are not covering?

On Wed, Aug 09, 2017 at 06:48:51PM +, Wout Mertens wrote: > sqlite> create table t(j json, s string); > sqlite> create index s on t(s); > sqlite> create index j on t(json_extract(j, '$.foo')); > sqlite> create index l on t(length(s)); In order for any of these indices to be covering indices

Re: [sqlite] LSM1 extension

On Wed, Aug 09, 2017 at 08:26:51AM -0500, Charles Leifer wrote: > sqlite4's future is uncertain. It's not, as I understand, meant to replace > sqlite3 any time soon. I think it was more of a place to try out new ideas > for implementations. I must say though, SQLite4 is *brilliant*. I do hope

Re: [sqlite] Thinking about a way to extend the number of writers in WAL mode

On Tue, Aug 08, 2017 at 10:30:33AM -0700, Jens Alfke wrote: > > On Aug 4, 2017, at 11:28 AM, Nico Williams <n...@cryptonector.com> wrote: > > Imagine a mode where there is only a WAL, and to checkpoint is to write > > a new WAL with only live contents and... rename(2) into

Re: [sqlite] hex and char functions

On Mon, Aug 07, 2017 at 11:45:50AM -0400, Richard Hipp wrote: > On 8/7/17, Nico Williams <n...@cryptonector.com> wrote: > > Internally SQLite3 uses UTF-8. The SQLite3 API lets you deal with > > UTF-16, but this just transcodes to/from UTF-8 internally. > > That is

Re: [sqlite] hex and char functions

On Mon, Aug 07, 2017 at 03:29:41PM +, x wrote: > Apologies, I should have said I was using c++ builder Berlin on > windows 10 and that UnicodeString was UTF16. > > I thought I had learned enough about this string lunacy to get by but > finding out that the UTF8 code for the UTF16 code \u0085

Re: [sqlite] sequencer

On Fri, Aug 04, 2017 at 10:09:04PM +0200, Sylvain Pointeau wrote: > On Fri, Aug 4, 2017 at 10:01 PM, Nico Williams <n...@cryptonector.com> > wrote: > > The main issue is that you can't tell when a transaction has begun or > > ended, so you can't tell when curr_val() should

Re: [sqlite] sequencer

On Fri, Aug 04, 2017 at 09:55:03PM +0200, Sylvain Pointeau wrote: > On Fri, Aug 4, 2017 at 9:16 PM, Nico Williams <n...@cryptonector.com> wrote: > > In general I would say: pthread_once() (Unix) or InitOnceExecuteOnce() > > (WIN32). But here, a global in combination w

Re: [sqlite] sequencer

On Fri, Aug 04, 2017 at 07:20:00PM +, Hick Gunter wrote: > A "temp table" would only be visible in the session that creates it > and would not live past the end of the session. Sequences should be > persistent... This is about the H2 curr_val() semantics -- that it only works if you've

Re: [sqlite] sequencer

On Fri, Aug 04, 2017 at 09:09:10PM +0200, Sylvain Pointeau wrote: > I programmed the currval using a temp table, but the performance dropped > slightly > > sqlite> WITH T(i) AS (SELECT (1)i UNION ALL SELECT i+1 FROM T WHERE > i<100) INSERT INTO seq_test(seq_num) SELECT seq_nextval('s > eq1')

Re: [sqlite] Thinking about a way to extend the number of writers in WAL mode

On Fri, Aug 04, 2017 at 07:05:06PM +, Peter Da Silva wrote: > Step 2 seems rather expensive, even if you’re filtering out dead blocks in > the process. It's no more expensive than WAL checkpointing is today. You could always do what LMDB does to reuse free blocks in a DB and avoid having to

Re: [sqlite] Thinking about a way to extend the number of writers in WAL mode

On Fri, Aug 04, 2017 at 01:59:07PM -0500, Nico Williams wrote: > The checkpoint process would look like this: > > - make a new file in the same directory > - copy the DB to the new file The copy would basically be copying all the live data as a single transaction on the new

Re: [sqlite] Thinking about a way to extend the number of writers in WAL mode

On Fri, Aug 04, 2017 at 06:49:42PM +, Peter Da Silva wrote: > On 8/4/17, 1:45 PM, "sqlite-users on behalf of Nico Williams" > <sqlite-users-boun...@mailinglists.sqlite.org on behalf of > n...@cryptonector.com> wrote: > > SQLite3's WAL is already log-stru

Re: [sqlite] Thinking about a way to extend the number of writers in WAL mode

On Fri, Aug 04, 2017 at 06:43:08PM +, Peter Da Silva wrote: > On 8/4/17, 1:28 PM, "sqlite-users on behalf of Nico Williams" wrote: > > [...] > > A log-structured database, like a log-structured file system? Yes. Filesystems and databases are each other's dual. SQL

Re: [sqlite] Thinking about a way to extend the number of writers in WAL mode

On Fri, Aug 04, 2017 at 11:53:05AM -0500, Nico Williams wrote: > WAL mode still ends up having no read concurrency when it's time to > checkpoint the WAL. The same would happen with this concept. I don't > think this works well. Speaking of which... and I know I've mentioned this bef

Re: [sqlite] sequencer

On Fri, Aug 04, 2017 at 08:04:38PM +0200, Sylvain Pointeau wrote: > On Fri, Aug 4, 2017 at 6:24 PM, Nico Williams <n...@cryptonector.com> wrote: > > On Fri, Aug 04, 2017 at 06:06:55PM +0200, Sylvain Pointeau wrote: > > > Now I would like to implement the seq_currval:

Re: [sqlite] Feature request: check for 'lib' prefix for load_extension()

On Fri, Aug 04, 2017 at 10:55:57AM -0500, Bob Friesenhahn wrote: > Sqlite does not really have a way to know if a module in the current > directory (the directory which just happened to be current when the request > was made) should be trusted. To be secure, sqlite should insist that the > load

Re: [sqlite] Thinking about a way to extend the number of writers in WAL mode

On Fri, Aug 04, 2017 at 08:50:05AM +0200, Luc DAVID wrote: > sqlite has WAL mode for better concurrency and this could maybe be used to > extend the number of writters: > > Do you think it would be possible to create a > MyDb.WAL001...MyDb.WAL.002...MyDb.WAL.nnn when a write operation is >

Re: [sqlite] Thinking about a way to extend the number of writers in WAL mode

On Fri, Aug 04, 2017 at 12:58:43PM +0100, Simon Slavin wrote: > The problem you’re trying to fix is one of the big problems with > distributed databases. Nobody has found a good solution for it yet. It's impossible to solve for the eventually-consistent types. You just have to explicitly

Re: [sqlite] Thinking about a way to extend the number of writers in WAL mode

On Fri, Aug 04, 2017 at 09:33:31AM +0200, Eric Grange wrote: > The main problem with multiple writers would be in conflict resolution, > locking and deadlocks. There is plenty in the literature about this. You have to code more defensively, you may need things like "FOR UPDATE", etc. > Imagine

Re: [sqlite] sequencer

On Fri, Aug 04, 2017 at 06:06:55PM +0200, Sylvain Pointeau wrote: > Please find below the last source code, I removed the check on the table > (NOT NULL on both sql_val and seq_inc) Yeah, I saw. I think this is another argument for SQLite3 needing a strict-type mode! (I very much prefer strong

Re: [sqlite] Feature request: check for 'lib' prefix for load_extension()

On Fri, Aug 04, 2017 at 08:20:10AM -0700, J Decker wrote: > On Fri, Aug 4, 2017 at 8:11 AM, Nico Williams <n...@cryptonector.com> wrote: > > No, see, the ".so"/".dll" suffix is used in all cases, and it varies by > > platform, so it's best if SQLit

Re: [sqlite] Feature request: check for 'lib' prefix for load_extension()

On Fri, Aug 04, 2017 at 10:17:33AM +0200, Dominique Devienne wrote: > On Fri, Aug 4, 2017 at 2:46 AM, Nico Williams <n...@cryptonector.com> wrote: > > You're mistaken. > > > > lib.so is NOT "the default naming scheme on many *nix platforms". > > &g

Re: [sqlite] Feature request: check for 'lib' prefix for load_extension()

On Fri, Aug 04, 2017 at 06:05:53AM +, Hick Gunter wrote: > >Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im > >Auftrag von Nico Williams > >But loadable modules are almost never meant to be used that way. > >They're usually meant to be

Re: [sqlite] sequencer

On Fri, Aug 04, 2017 at 03:35:36AM +, Sylvain Pointeau wrote: > Le ven. 4 août 2017 à 02:42, Nico Williams <n...@cryptonector.com> a écrit : > > > sqlite3_prepare_v2(db, "insert or replace into sp_sequence (seq_name, > > > seq_val, seq_inc) values (?,

Re: [sqlite] sequencer

On Thu, Aug 03, 2017 at 07:42:12PM -0500, Nico Williams wrote: > On Fri, Aug 04, 2017 at 12:35:56AM +0200, Sylvain Pointeau wrote: > I think some type checking should be done. > > You could just take the argv[] values and bind them directly to the > insert below, and use CH

Re: [sqlite] Feature request: check for 'lib' prefix for load_extension()

On Tue, Aug 01, 2017 at 10:56:47AM -0700, Matt Chambers wrote: > load_extension() has the very sensible behavior of: > > So for example, if "samplelib" cannot be loaded, then names like > > "samplelib.so" or "samplelib.dylib" or "samplelib.dll" might be tried > > also. > > I would like to see

Re: [sqlite] sequencer

On Fri, Aug 04, 2017 at 12:35:56AM +0200, Sylvain Pointeau wrote: > void sp_seq_init(sqlite3_context *context, int argc, sqlite3_value **argv) { > int rc = 0; > sqlite3_stmt *stmt; > sqlite3 *db = sqlite3_context_db_handle(context); If you use sqlite3_create_function*() with nArg == -1 then

Re: [sqlite] Problem with the new pointer-passing interface

On Thu, Aug 03, 2017 at 08:33:31PM +0200, Ulrich Telle wrote: > > Rhetorical: Why not use the pointer value itself then instead of the > > contents of the string? After all, the string should just be a .text > > section constant string... > > The SQLite developer team chose a string

Re: [sqlite] sequencer

On Thu, Aug 03, 2017 at 06:59:44PM +0300, Alek Paunov wrote: > On 2017-08-02 20:24, Nico Williams wrote: > >I've implemented "inheritance" with triggers to map DMLs on "derived" > >tables onto "base" tables. That works and is much more general. If yo

Re: [sqlite] Problem with the new pointer-passing interface

On Thu, Aug 03, 2017 at 12:33:05PM +0300, Paul wrote: > To me it seems like that string is very tightly coupled with the > actual pointer being bound. I think it's a good idea, in case you > cannot make it a literal or static, to keep it with an object whose > pointer you bind. Rhetorical: Why

Re: [sqlite] sequencer

On Thu, Aug 03, 2017 at 12:25:00PM +0200, Sylvain Pointeau wrote: > On Thu, 3 Aug 2017 at 08:04, Hick Gunter wrote: > > A sequence is very easily implemented as a virtual table that keeps the > > current values in a separate table my_sequences (name text primary key, > > initial

Re: [sqlite] sqlite3_prepare16_v3 and prepFlags

On Wed, Aug 02, 2017 at 11:01:07PM +0100, Bart Smissaert wrote: > Using 3.20.0 now on Windows and wonder when exactly I should use the > SQLITE_PREPARE_PERSISTENT > > flag > instead of a zero. I have tried both

Re: [sqlite] Pointers in 3.20.0 are great! Dreaming of better

Another thing is that with serializers the shell could automatically serialize on output if desired. Also, the callbacks could be attached to a "type", and perhaps that way you could preserve the APIs you already added for pointer values. ___

Re: [sqlite] sequencer

On Wed, Aug 02, 2017 at 06:46:55PM +, Sylvain Pointeau wrote: > if I do > > insert into mytable(f) > select nextval("myseq") from T > > is it really possible to make an update into that nextval function? I don't > think so since only one statement can be run at the same time if sqlite is >

Re: [sqlite] sequencer

On Wed, Aug 02, 2017 at 06:10:52PM +, Sylvain Pointeau wrote: > for a general case, I would need to persist the counter into a table (for a > specified sequencer) and doing the nextval inside a mutex lock > > Is it possible to insert/ select from a UDF if the statements are > serialized? or

Re: [sqlite] sequencer

On Wed, Aug 02, 2017 at 05:41:38PM +0100, Simon Slavin wrote: > On 2 Aug 2017, at 5:35pm, Nico Williams <n...@cryptonector.com> wrote: > > On Wed, Aug 02, 2017 at 04:56:34PM +0100, Simon Slavin wrote: > >> Can someone explain ? > > > > They make it easy

Re: [sqlite] sequencer

On Wed, Aug 02, 2017 at 07:48:52PM +0300, Alek Paunov wrote: > On 2017-08-02 18:23, Sylvain Pointeau wrote: > ... > > > >CREATE SEQUENCE IF NOT EXISTS SEQ_1 START WITH 12123; > > > >insert into MYTABLE(SPECIFIED_NUMBER, OTHERINFO) values (seq_1.nextval, > >'other info') > > > > BTW, your request

Re: [sqlite] sequencer

On Wed, Aug 02, 2017 at 04:56:34PM +0100, Simon Slavin wrote: > On 2 Aug 2017, at 4:54pm, Peter Da Silva > wrote: > > Can’t you do the same basic logic then use (SELECT value FROM > > super_sequences WHERE id=’SEQ_1’) instead of SEQ_1.nextval? > > Actually, I

[sqlite] Pointers in 3.20.0 are great! Dreaming of better

http://sqlite.org/bindptr.html Great stuff. This will make it possible to, e.g., have JSON functions that keep parsed JSON texts... parsed for as long as possible. The user's SQL will have to invoke an encoder function to encode parsed JSON texts back to actual text, sadly, but that's not a

Re: [sqlite] Datatype for prices (1,500)

On Wed, Nov 30, 2016 at 02:22:42PM -0600, John McKown wrote: > On Wed, Nov 30, 2016 at 9:46 AM, Chris Locke wrote: > > Storing as integers is the way to go. > > At present, I think that is the best way. Of course, we could argue that > every RDMS "should" implement Decimal

Re: [sqlite] Datatype for prices (1,500)

On Wed, Nov 30, 2016 at 04:39:25PM +0100, Werner Kleiner wrote: > yes thats right, but it would be nice to have same behavior between > MySQL and sqlite SQLite3 has very few types. Adding types is very difficult, so I doubt new types will be added anytime soon. Price data and datetime data are

Re: [sqlite] Read-only access which does not block writers

On Mon, Nov 28, 2016 at 07:45:16PM +, Howard Chu wrote: > Nico Williams wrote: > >One of the problems with LMDB's locks is that readers need write > >permission to the lock file, IIRC :( but at least it's a separate file. > > We developed an alternate locking protocol

Re: [sqlite] Read-only access which does not block writers

On Fri, Nov 25, 2016 at 09:08:37AM +0100, Florian Weimer wrote: > I think you misunderstood what I was asking (see the start of the thread). > I need to support Byzantine readers which do not follow the locking > protocol. Based on the documentation, LMDB uses locks to implement MVCC and >

Re: [sqlite] Query Planner fails to recognise efficient strategy when '=' condition gives a strong hint

On Thu, Nov 17, 2016 at 04:48:20PM +0200, Paul wrote: > Replacing JOIN does not help either: > > sqlite> EXPLAIN QUERY PLAN SELECT foo.id FROM bar JOIN foo ON bar.foo = > foo.id ORDER BY id DESC LIMIT 0, 40; > selectidorder fromdetail >

Re: [sqlite] Query Planner fails to recognise efficient strategy when '=' condition gives a strong hint

On Thu, Nov 17, 2016 at 09:54:01AM -0500, Richard Hipp wrote: > Standard SQL requires an implied NOT NULL on all PRIMARY KEY columns. > But due to a coding error, early versions of SQLite did not enforce > that, and so we have taken care not to enforce it on all subsequent > versions of SQLite to

Re: [sqlite] Changing ID's to UUID

On Wed, Nov 16, 2016 at 02:51:35PM -0600, Jeffrey Mattox wrote: > I think this discussion is about apples and oranges. UUID stands for > universally UNIQUE identifier, so there won't be any collisions. It > looks random, but it never repeats. [...] No, DRH is right that this depends on how

Re: [sqlite] Uninitialized memory reads (not likely false positives)

On Wed, Nov 16, 2016 at 12:06:39PM -0600, Nico Williams wrote: > On Tue, Nov 15, 2016 at 09:38:11PM -0200, Bernardo Sulzbach wrote: > > if( s1>7 && s2>7 ){ > > res = s1 - s2; > > }else{ > > if( s1==s2 ){ > > // Accesses to aLen

Re: [sqlite] Uninitialized memory reads (not likely false positives)

On Wed, Nov 16, 2016 at 10:52:13PM +0700, Dan Kennedy wrote: > On 11/16/2016 05:53 AM, Nico Williams wrote: > > [...] > > > > Anyways, the analysis from here is non-trivial, and I can't convince > > myself that sNC.pNext will not be dereferenced. > > T

Re: [sqlite] Uninitialized memory reads (not likely false positives)

On Tue, Nov 15, 2016 at 09:38:11PM -0200, Bernardo Sulzbach wrote: > if( s1>7 && s2>7 ){ > res = s1 - s2; > }else{ > if( s1==s2 ){ > // Accesses to aLen as mentioned above > > If s1 > 7 && s2 > 7 is false, then at least one of s1 and s2 is not above 7. > If they are equal, then

[sqlite] Uninitialized memory reads (not likely false positives)

I don't normally pay attention to warnings when compiling SQLite3, nor to Coverity or other static analysis tools' output either, as I'm quite aware that most of these are false positives and thus unwelcome noise here. However, I do sample them occasionally, and though usually such reports are

[sqlite] Diff two tables as fast as diff(1) (of sorted files)

On Wed, Nov 18, 2015 at 11:22:51PM -0700, Keith Medcalf wrote: > On Wednesday, 18 November, 2015 20:36, Nico Williams cryptonector.com> said: > > On Thu, Nov 19, 2015 at 12:39:41AM +, Simon Slavin wrote: > > > On 19 Nov 2015, at 12:26am, Nico Williams > > >

[sqlite] Diff two tables as fast as diff(1) (of sorted files)

On Wed, Nov 18, 2015 at 09:11:13PM -0500, Richard Hipp wrote: > On 11/18/15, Nico Williams wrote: > > > > Now, diff(1) (i.e., the Unix command) can do this very efficiently > > because it just moves a "cursor" in both files looking for different > >

[sqlite] Diff two tables as fast as diff(1) (of sorted files)

On Thu, Nov 19, 2015 at 12:39:41AM +, Simon Slavin wrote: > On 19 Nov 2015, at 12:26am, Nico Williams wrote: > > two concurrent scans of the same table should be able to go faster > > than the same two scans in series. > > SQLite is not processor-bound, it's file-b

[sqlite] Diff two tables as fast as diff(1) (of sorted files)

On Thu, Nov 19, 2015 at 12:43:18AM +, Stadin, Benjamin wrote: > Sqlitediff diffs a same db (one db changed over time). If you are > looking for diffing data using the same db scheme that has been > generated at different times (producing different instances of the db > scheme, eg you import

[sqlite] Diff two tables as fast as diff(1) (of sorted files)

On Wed, Nov 18, 2015 at 11:56:50PM +, Simon Slavin wrote: > On 18 Nov 2015, at 10:58pm, Nico Williams wrote: > > Can SQLite3 run the sub-queries of a UNION ALL with any degree of > > concurrency? E.g., with its co-routines? > > SQLite always has the bottleneck of acces

[sqlite] Diff two tables as fast as diff(1) (of sorted files)

On Wed, Nov 18, 2015 at 11:52:14PM +, Simon Slavin wrote: > On 18 Nov 2015, at 8:11pm, Nico Williams wrote: > > SELECT 'added., a.* FROM > > (SELECT a.* FROM a EXCEPT SELECT b.* FROM b) a > > UNION ALL > > SELECT 'deleted', d.* FROM > > (SELECT b.* F

[sqlite] Diff two tables as fast as diff(1) (of sorted files)

Can SQLite3 run the sub-queries of a UNION ALL with any degree of concurrency? E.g., with its co-routines? That might help three union'ed scans of the same table go pretty fast, maybe not much slower than one scan. In which case this might compare quite well to diff(1). Nico --

[sqlite] Diff two tables as fast as diff(1) (of sorted files)

On Wed, Nov 18, 2015 at 11:10:47PM +0100, E.Pasma wrote: > op 18-11-2015 21:23 schreef Richard Hipp op drh at sqlite.org: > > The "sqldiff.exe" command-line utility has an (undocumented and > > unsupported) "--debug 2" option that will show you the SQL that it > > uses to do a diff between two

[sqlite] Diff two tables as fast as diff(1) (of sorted files)

On Wed, Nov 18, 2015 at 03:23:33PM -0500, Richard Hipp wrote: > If it's so common, why are you the first to request it :-) And, yeah, > according to the WWPD principal, if Postgres doesn't do it, we > probably won't be in a big hurry to do it either... I've written this sort of query many times.

[sqlite] Diff two tables as fast as diff(1) (of sorted files)

Consider two tables with the same columns and primary keys, and then consider this query: SELECT 'added., a.* FROM (SELECT a.* FROM a EXCEPT SELECT b.* FROM b) a UNION ALL SELECT 'deleted', d.* FROM (SELECT b.* FROM b EXCEPT SELECT a.* FROM a) d; In other words, the SQL analog of a

[sqlite] SQLite, fork(), and exec()

On Mon, Aug 10, 2015 at 11:02:04AM -0400, Richard Hipp wrote: > On 8/10/15, Felipe Gasper wrote: > > Does SQLite need to have nothing at all to do with fork()? Even when > > the fork() immediately precedes an exec(), and the exec()ed command has > > nothing to do with SQLite? > > > > Or

[sqlite] sqlite error code 1034

On Tue, Jun 23, 2015 at 09:12:31PM +0100, Simon Slavin wrote: > On 23 Jun 2015, at 8:49pm, Mayank Kumar (mayankum) > wrote: > > Thanks all for the useful inputs. One more question I have is can > > the 1034 error code ever mean that disk is full or nvram is full and > > hence it cannot fsync ? >

[sqlite] UDTs

On Fri, Jun 05, 2015 at 09:36:09PM +0100, Simon Slavin wrote: > One advantage I can think of of having a DateTime type -- enforcement > of storing the correct thing in the correct column -- won't work in > SQLite anyway, because rather than enforce column types is uses only > affinities. > > How

[sqlite] User-defined types

On Thu, Jun 04, 2015 at 03:36:34PM -0700, Darko Volaric wrote: > I now regret using JSON as an example since everyone wants me to convert > [...] So you don't like the SQL language, but if you're after UDTs and your first stop is to design a different language (or merely make it easier for you to

[sqlite] User-defined types

On Thu, Jun 04, 2015 at 02:16:22PM -0700, Darko Volaric wrote: > { > operation: "insert" > table: "blah" > columns: ["a", "b", "c"] > values: [1.3, 2.0, 3.1] > on-conflict: "replace" > } I do this all the time. It's trivial enough to generate SQL from that sort of thing. If you have

[sqlite] User-defined types

On Thu, Jun 04, 2015 at 11:45:28AM -0700, Darko Volaric wrote: > Which sort of leads me to my next feature, which is bypassing the SQL > language. [...] I like SQL, but sure, if the compiler worked by first parsing into an AST, and if the AST were enough of an interface (versioned, though not

[sqlite] User-defined types

On Thu, Jun 04, 2015 at 10:54:16AM +0200, Dominique Devienne wrote: > On Thu, Jun 4, 2015 at 10:20 AM, Christopher Vance > wrote: > > If you really want your own types, you could always bundle with ASN.1 and > > store the result as a blob. FYI, Heimdal has a very nice, small, simple, featureful,

[sqlite] User-defined types

On Wed, Jun 03, 2015 at 06:04:29PM -0700, Darko Volaric wrote: > Yep, references a another one. Just like the functions, you have to join on > the user type information, add it to constraints, etc. Once you're extending SQLite3 proper the referential integrity problem goes away (being no

[sqlite] User-defined types

On Wed, Jun 03, 2015 at 03:55:04PM -0700, Darko Volaric wrote: > I've tackled this problem from a couple of different angles. My goal was to > allow arbitrary user defined types, based on the builtin types (essentially > subtypes of the existing types), with a minimum of work and minimum >

[sqlite] unreached code in sqlite3.c?

On Thu, Feb 12, 2015 at 8:58 AM, Doug Currie wrote: > Well, if TERM_VNULL is 0, then the code is truly unreachable, so I wouldn't > call it a compiler bug. Agreed. But since this usage is so common, the compiler ought to have a switch to turn off warnings for this particular sort of case, which

Re: [sqlite] Whish List for 2015

On Tue, Dec 23, 2014 at 05:26:00PM +0200, Tony Papadimitriou wrote: > Proposal: > > Minimal stored procedure functionality -- or, maybe call it stored It's already there. It's called TRIGGER. I use triggers to hold "stored procedures", and WHERE conditions on DMLs in the trigger body as

Re: [sqlite] ALTER TABLE .. ADD COLUMN .. IF NOT EXISTS ..?

On Tue, Dec 16, 2014 at 11:40:22PM +, Simon Slavin wrote: > If 'ALTER TABLE ... ADD COLUMN ...' fails it fails harmlessly, with But it doesn't fail so harmlessly: $ sqlite3 db 'alter table toy add column foo text; select 5;' || echo fail SQL Error: duplicate column name: foo fail $ Note

[sqlite] ALTER TABLE .. ADD COLUMN .. IF NOT EXISTS ..?

I have a habit of putting schema definitions in a file that's always safe to read and execute against a DB connection. This means that I DROP some things IF EXISTS and CREATE all things IF NOT EXISTS. But if I have to ALTER TABLE... there's no IF NOT EXISTS .. equivalent for ALTER TABLE. Funny

Re: [sqlite] Query Planner for Virtual Tables: link table evaluation & transitive property of constraints not used

On Mon, Dec 15, 2014 at 06:23:31PM +0700, Dan Kennedy wrote: > It's tricky. As you say, xBestIndex() will currently be invoked > twice - once with no constraints usable and once with both "b.id=?" > and "b.linkid=?" usable. I guess the reason it is not invoked in the > other ways you suggest is

Re: [sqlite] sqlite bugreport : unique index causes valid updates to fail

On Mon, Dec 8, 2014 at 9:01 AM, Dan Kennedy wrote: > You could hack SQLite to do enforce unique constraints the same way as FKs. > When adding an entry to a UNIQUE index b-tree, you check for a duplicate. If > one exists, increment a counter. Do the opposite when removing

Re: [sqlite] sqlite bugreport : unique index causes valid updates to fail

Ideally there would be something like DEFERRED foreign key checking for uniqueness constraints... You can get something like that by using non-unique indexes (but there would also go your primary keys) and then check that there are no duplicates before you COMMIT. (Doing this reliably would

Re: [sqlite] Using Sqlite3 as a Change Time Recording Data Store in Glusterfs

BTW, the experience with dedup is that doing things off-line means never catching up, while doing them online means going slow. You might cache as much as you can in memory then go slow when you miss the cache... In practice I think it's best to separate data and metadata devices so that you can

Re: [sqlite] Using Sqlite3 as a Change Time Recording Data Store in Glusterfs

My advice is to borrow from other clustered filesystems' experience. If you want to adhere to POSIX semantics then st_mtime and st_size visibility will be a particular headache, especially since you don't know when it's OK to lie (i.e., which callers of stat() are using st_mtime/st_size for

Re: [sqlite] Using Sqlite3 as a Change Time Recording Data Store in Glusterfs

On Sun, Nov 23, 2014 at 4:26 PM, James K. Lowden wrote: > Metadata updates to Posix filesystems are seen as so costly that > fsync(2) on the datafile descriptor doesn't update them. A separate > sync on the directory is required. Compared to an in-memory update > (of

Re: [sqlite] SQLite as a meta database

IIRC there's a GNOME interface to databases, libgda, that works this way. The fundamental problem with using SQLite3 virtual tables to access remote/high-latency resources is that the SQLite3 VM and the generated bytecode are very serial, therefore the VM can't make progress on other parts of a

Re: [sqlite] Ordering of group_concat values using subselect in 3.8.7

On Tue, Oct 28, 2014 at 2:33 PM, James Earl wrote: > After upgrading from SQLite 3.8.6 to 3.8.7, I am no longer able to > order group_concat values by using a subselect. For example the > following query with 3.8.6 will give me an ordered string of > items.image values

Re: [sqlite] quasi-bug related to locking, and attached databases....

On Mon, Oct 27, 2014 at 5:12 PM, Simon Slavin <slav...@bigfraud.org> wrote: > On 27 Oct 2014, at 9:49pm, Nico Williams <n...@cryptonector.com> wrote: >> If it's not too much to ask for then SQLite3 ought to: a) check for >> duplicates by canonicalized path (but

  1   2   3   4   5   >