Re: [sqlite] Bug

I think attachments are dropped. If the SQL is reasonable size, just post it. Otherwise, you'll need to host the screen shot somewhere and link to it. > On Nov 19, 2014, at 10:00 PM, Josef Handsuch wrote: > > Dear developer, I'd like to thank you for you brilliant

Re: [sqlite] insert or ignore with foreign keys

I think INSERT OR IGNORE is designed to insert a record into a table if a record with its primary key doesn't already exist. It's not an INSERT AND IGNORE ON ANY ERROR. So: INSERT OR IGNORE INTO t2 VALUES (1,1) INSERT OR IGNORE INTO t2 VALUES (1,1) The above would not cause an error where,

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

I am like you, Gwendal, in that I don't like that behavior in SQLite; however, not liking it doesn't make it a bug. The constraint-checking algorithm was defined to work exactly the way it's working. When designed, the fact that your type of insert would fail was known and understood. Hence,

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

: Monday, December 08, 2014 9:18 AM To: General Discussion of SQLite Database Subject: Re: [sqlite] sqlite bugreport : unique index causes valid updates to fail On Mon, Dec 8, 2014 at 8:15 AM, Marc L. Allen <mlal...@outsitenetworks.com> wrote: > I am like you, Gwendal, in that I d

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

Doesn't that code risk being broken in a later version that doesn't update in the order provided by the sub-query? -Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of J T Sent: Monday, December 08, 2014 9:23 AM To:

Re: [sqlite] Select count(*)

I believe that when NULLs are allowed as PKs, they are all distinct. So, you can multiple rows with a NULL value as the PK. -Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Dominique Devienne Sent: Thursday, December 11,

[sqlite] i think this is a bug in sqlite? thanks

I was about the suggest the same thing that was suggest in that thread. Verify the types of those values. -Original Message- From: sqlite-users-bounces at mailinglists.sqlite.org [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On Behalf Of Anthony Damico Sent: Thursday, December

[sqlite] Query takes 8 seconds on one machine but I abort after 40+ minutes on other machines

Sorry.. confused does sqlite allow comparison between NULLs? I mean... LEFT JOIN work ON work.fpath = home.fpath WHERE work.fpath IS NULL Should never match anything... it checks to see if work.fpath IS NULL and if the same work.fpath is equal to something. I get the weird feeling that

[sqlite] Query takes 8 seconds on one machine but I abort after 40+ minutes on other machines

on other machines On Mon, 27 Jul 2015 at 17:34, Marc L. Allen wrote: > Sorry.. confused does sqlite allow comparison between NULLs? No. > LEFT JOIN work ON work.fpath = home.fpath WHERE work.fpath IS NULL > > Should never match anything... it checks to see if work.f

[sqlite] Query takes 8 seconds on one machine but I abort after 40+ minutes on other machines

sqlite.org] On Behalf Of Reinhard Max Sent: Monday, July 27, 2015 12:04 PM To: General Discussion of SQLite Database Subject: Re: [sqlite] Query takes 8 seconds on one machine but I abort after 40+ minutes on other machines On Mon, 27 Jul 2015 at 17:48, Marc L. Allen wrote: > When would that speci

[sqlite] Mozilla wiki 'avoid SQLite'

Perhaps you might want to consider an apology and check your headers next time. I'm pretty sure the comment you quoted from this morning was written by Scott Doctor, not Scott Robinson. -Original Message- From: sqlite-users-bounces at mailinglists.sqlite.org

[sqlite] Mozilla wiki 'avoid SQLite'

I don't know. Back in the day, assembly was low-level because it was directly converted to machine code. C was high level because you could express more complex structures without worrying about the underlying architecture. I still like that distinction. I think people are trying to call C low

[sqlite] Mozilla wiki 'avoid SQLite'

I don't understand all the issues with SQL as it is today. I doubt I'm at the level of most of the posters in this group. However, I do write a mess of it so I thought I'd give my two cents as a programmer. For what it does, SQL does it really well. That is, it takes a couple of sets of

[sqlite] Mozilla wiki 'avoid SQLite'

- A New Database Language - andl.org -Original Message- From: sqlite-users-bounces at mailinglists.sqlite.org [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On Behalf Of Marc L. Allen Sent: Thursday, 18 June 2015 11:51 PM To: General Discussion of SQLite Database Subject: Re: [sqlite

[sqlite] saving :memory:database to disk

Don't you already have to have a DB initialization script to create the in-memory database? Couldn't you use that to create the on-disk database, and then have a separate script to copy all the data over? Or is this for more of a dynamic database whereby the users can add/remove tables and

Re: [sqlite] Bug in division?

Not an error. Int/Int uses integer division and results in an integer number. When one number is a float, the result becomes a float. I don't know about all SQL varieties, but MSSQL is the same. > On Apr 30, 2014, at 8:04 AM, "Gene Connor" wrote: > > > SELECT

Re: [sqlite] select 1 where 1 - 1;

Really? Interesting. So... Select 1 Where 1 < inf; ? Or is it just when taking inf by itself? -Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Richard Hipp Sent: Monday, May 05, 2014 11:32 AM To: General Discussion of

Re: [sqlite] select 1 where 1 - 1;

I think everyone agrees that SQLite does not strictly follow the SQL standards for WHERE clause expressions. The question is... should it? One must ask, "what makes SQLite lite?" I think this kind of simplification is of them. However, I can understand that it might rankle some people.

[sqlite] [sqlite-dev] Proposed new version numbering scheme for SQLite - Feedback requested

Just my $0.02... In the proposed new versioning system: Partial Indexes is clearly something that requires Y to be incremented as Y-1 won't be able to handle a database with partial indexes. However, CTE is a functionality enhancement that, I believe, does not impact the ability of previous

[sqlite] [sqlite-dev] Proposed new version numbering scheme for SQLite - Feedback requested

L. Allen Sent: Thursday, October 08, 2015 11:52 AM To: General Discussion of SQLite Database Subject: Re: [sqlite] [sqlite-dev] Proposed new version numbering scheme for SQLite - Feedback requested Just my $0.02... In the proposed new versioning system: Partial Indexes is clearly something

[sqlite] [sqlite-dev] Proposed new version numbering scheme for SQLite - Feedback requested

of SQLite Database Subject: Re: [sqlite] [sqlite-dev] Proposed new version numbering scheme for SQLite - Feedback requested On 10/8/15, Marc L. Allen wrote: > However, CTE is a functionality enhancement that, I believe, does not > impact the ability of previous SQLite versions t

[sqlite] Simple Math Question

If I recall, he had a printout of the state at each time unit. The state was represented as a set of numbers out to 6 decimal points. He wanted to rerun part of the simulation, so he entered in those super-precise numbers and let them run, but the model quick diverged because those numbers

[sqlite] Simple Math Question

I ram the following code on my ARM processor: double c25 = 0.0; c25 += 9.2; c25 += 7.9; c25 += 0.0; c25 += 4.0; c25 += 2.6; c25 += 1.3; double n25 = 25.0; double c23 = 0.0; c23 += 9.2; c23 += 7.8; c23 += 0.0; c23 += 3.0; c23 += 1.3; c23 += 1.7; double n23 = 23.0;

[sqlite] Simple Math Question

I didn't see this go through, so I'm sending it again. I got an Alexa spam, so maybe that's part of it. I ran the following code on my ARM processor: double c25 = 0.0; c25 += 9.2; c25 += 7.9; c25 += 0.0; c25 += 4.0; c25 += 2.6; c25 += 1.3; double n25 = 25.0; double c23 = 0.0; c23 +=

[sqlite] Alexa has been banished. Was: Mailing list policy change

I save one. I can forward it to you, if you'd like. > On Oct 29, 2015, at 5:37 PM, Nicolas J?ger wrote: > > damn! I wanted a date! > >>> On 10/29/15, Simon Slavin wrote: >>> >>> You don't even need to mess with the genuine list server. Just grab all the >>> addresses it sends to and use

[sqlite] Illegal SQL not rejected

That error is saying that you can't using HAVING on a column unless it's in a group by or it's referenced in an aggregate in the HAVING clause You could say, HAVING SUM(A) <> 0 or something. The query as stated SELECT SUM(A) ... HAVING A<>0 makes no sense because A is not in the select list.

[sqlite] SQLite Pronunciation

That sounds like someone that comes from the land of Sequel. ;) I realize there *is* an official pronunciation, but I will probably forever pronounce it as S-Q-L-light, regardless of what it really is. :) -Original Message- From: sqlite-users-bounces at mailinglists.sqlite.org

[sqlite] SQLite Pronunciation

? To me, it falls off the tongue easier to say Sequel instead of Ess-Queue-Ell. 1- http://acronyms.thefreedictionary.com/RADAR On Wed, Mar 16, 2016 at 2:55 PM, Marc L. Allen wrote: > That sounds like someone that comes from the land of Sequel. ;) > > I realize there *is* an official pron

[sqlite] Article about pointer abuse in SQLite

I had a long response to this, but it occurs to me that you're just being pedantic for fun. Am I wrong? -Original Message- From: sqlite-users-bounces at mailinglists.sqlite.org [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On Behalf Of Keith Medcalf Sent: Friday, March 18, 2016

[sqlite] Article about pointer abuse in SQLite

I don't think compilers "run" your code. When looking for uninitialized variables, it simply looks for a potential path through the code that uses a variable without it being initialized. The fact that the code never actually allows that path to occur is beyond the scope of most compilers,

[sqlite] Article about pointer abuse in SQLite

With everything that routine does, the extra initialization negatively impacts operation? -Original Message- From: sqlite-users-bounces at mailinglists.sqlite.org [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On Behalf Of Richard Hipp Sent: Thursday, March 24, 2016 10:50 AM To:

[sqlite] PERSIST Journal Mode

-- ** * * * * Marc L. Allen * "... so many things are * * * possible just as long as you* * Outsite Networks, Inc. * don't know they're impossible." * * (757) 853

Re: [sqlite] PERSIST Journal Mode

se Subject: Re: [sqlite] PERSIST Journal Mode On 17 Dec 2012, at 8:35pm, "Marc L. Allen" <mlal...@outsitenetworks.com> wrote: > Another item.. when having Journal Mode = PERSIST, DBA (in the example below) > was not being physically updated. DBB was. I can think of a rea

Re: [sqlite] PERSIST Journal Mode

@bigfraud.org> wrote: > > On 18 Dec 2012, at 3:04pm, Marc L. Allen <mlal...@outsitenetworks.com> wrote: > >> I also have no additional information as to why having PERSIST mode on >> prevents the database from being updated/correct. I did check the >> sqlite3

Re: [sqlite] order by "a huge number" does not work, but "random" well why ?

If you literally used "ORDER BY 1234567892" then there's nothing in the record being sorted. I can't recall is SQLite allows order by aliases, but something like.. Select ..., random() as X Order by X Might work, as long as random() is executed for each row. (Sorry.. don't have a quick SQLite

Re: [sqlite] SQL query

If you simply want a list of all files that are present and are not also present in set 0 (I'm not sure how 'duplicated' means anything different...) SELECT f.name, f.set, f.hash FROM files f LEFT OUTER JOIN files f2 ON f2.name = f.name and f2.set = 0 WHERE f.set != 0 and f2.name is null

Re: [sqlite] Running on windows 98

Might I suggest you include his patch so it at least runs? That way, if he's willing to test each new version, he doesn't need to modify the official source to do it. -Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Richard

Re: [sqlite] SQL query

Add a group by name, hash and change the select to be name, min(setid), hash? -Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Paul Sanderson Sent: Thursday, January 31, 2013 4:48 PM To: General Discussion of SQLite Database

Re: [sqlite] SQL query

Actually... with that requirement, I wonder if it's even easier/better to use: Select name, min(setid), hash >From rtable Group by name, hash Having min(setid) > 0 -Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Paul

[sqlite] SQLite 4

-- ** * * * * Marc L. Allen * "... so many things are * * * possible just as long as you* * Outsite Networks, Inc. * don't know they're impos

Re: [sqlite] SQLite 4

of SQLite Database Subject: Re: [sqlite] SQLite 4 On Tue, Feb 12, 2013 at 11:26 AM, Marc L. Allen <mlal...@outsitenetworks.com > wrote: > I'm sorry if this isn't the right group, but an earlier message > mentioned it, and I found some stuff on the SQLite website. > > Althou

Re: [sqlite] Would anyone know how to use Sqlite to calculate the streakedness of data? The definition of streakedness is show below. Thank you for your help

I haven't done SQLite coding in several months, and it's quite rusty, so I'll paraphrase. I haven't tested and if this is bogus, I'm sorry in advance. But maybe it'll give someone the right idea. You might be better off with a custom function, though. It would be something like this: CREATE

Re: [sqlite] Memory DB - Query does not return all records after Update

Are you finalizing the UPDATE statement? -Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of mike.akers Sent: Monday, February 25, 2013 4:48 PM To: sqlite-users@sqlite.org Subject: [sqlite] Memory DB - Query does not return all

Re: [sqlite] Bug on real operations

Yes.. for what it's worth, I've had this very same problem on MS SQL 2008. Comparing floating point values in their raw form is always dangerous. It just works so much more often than not that it's easy to forget until you get that one number that doesn't work. The solution for MS SQL was

Re: [sqlite] SQLite strong-typing [WAS: inner vs. outer join inconsistency]

If I have any doubt, I add .5 (or .05, .005, whatever) before the operation. I know that breaks algebraic rounding, but that's one I live with. -Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of James K. Lowden Sent: Friday,

Re: [sqlite] [SQLite.Net] Right way to install precompiled binaries?

It has around 500 context switches per second.. so I'm thinking MosYield. -Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of markus diersbock Sent: Thursday, April 18, 2013 1:37 PM To: General Discussion of SQLite Database

Re: [sqlite] [SQLite.Net] Right way to install precompiled binaries?

Sorry... replied to the wrong message. :( -Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Marc L. Allen Sent: Thursday, April 18, 2013 1:40 PM To: General Discussion of SQLite Database Subject: Re: [sqlite] [SQLite.Net] Right

Re: [sqlite] Query optimizer bug?

In looking at the draft plan... am I right in assuming that at any 'stop' you can eliminate paths which have consumed the identical set of nodes but are more expensive? For instance, at stop 2, the draft shows: R-N1 (cost: 7.03) N1-R (cost: 7.31) R-N2 (cost: 9.08) N2-R (cost:

Re: [sqlite] Query optimizer bug?

of SQLite Database Subject: Re: [sqlite] Query optimizer bug? On Tue, Apr 30, 2013 at 5:46 PM, Marc L. Allen <mlal...@outsitenetworks.com>wrote: > In looking at the draft plan... am I right in assuming that at any 'stop' > you can eliminate paths which have consumed the identical

Re: [sqlite] Query optimizer bug?

, April 30, 2013 6:20 PM To: General Discussion of SQLite Database Subject: Re: [sqlite] Query optimizer bug? On Tue, Apr 30, 2013 at 5:46 PM, Marc L. Allen <mlal...@outsitenetworks.com>wrote: > In looking at the draft plan... am I right in assuming that at any 'stop' > you can eli

Re: [sqlite] Max of 63 columns for a covering index to work?

I think there might be a disconnect. You can have a covering index on a 300 column table... it just can't cover any column past the 63rd (or 64th?). It's not perfect, but not as bad as not being able to have a covering index at all. At least, that's how I read some of the answers.

Re: [sqlite] Max of 63 columns for a covering index to work?

the table, it was the only way to read from it in a performant fashion. -David -Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Marc L. Allen Sent: Wednesday, May 22, 2013 12:02 PM To: General Discussion of SQLite Database Subject: R

Re: [sqlite] Max of 63 columns for a covering index to work?

I haven't had a table that large, but I have had big ones... the disadvantage is the number of records you can scan in a single disk read, but an advantage is that you don't have to take the time to join tables, especially when you need to do it ALL THE TIME. -Original Message- From:

Re: [sqlite] Potential bug in crash-recovery code: unlink() and friends are not synchronous

Just to throw in my $0.02 as a user Given the SQL stream of... COMMIT Vs. Except in cases where, in the first example, I have time to inform someone about the COMMIT before the power loss, there's no functional difference between the two events. I would hate to think I would ever

Re: [sqlite] Max of 63 columns for a covering index to work?

Dominique Devienne Sent: Thursday, May 23, 2013 8:57 AM To: General Discussion of SQLite Database Subject: Re: [sqlite] Max of 63 columns for a covering index to work? On Wed, May 22, 2013 at 9:11 PM, Marc L. Allen <mlal...@outsitenetworks.com>wrote: > [...]. It makes me think you might

Re: [sqlite] SQLite NULL or 0-Length

No. All SQL functions can safely take NULL as an argument. LENGTH(NULL) returns NULL, so LENGTH(NULL) = 0 is always false. -Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Rob Richardson Sent: Thursday, May 23, 2013 3:34 PM

Re: [sqlite] Is pragma foreign_key_check fast enough to be used in normal application flow?

It's exhaustive in that it absolutely verifies if the key exists or not. However, it doesn't necessarily do a full database scan. I assume it uses available indexes and does a standard lookup on the key. So, it still might be fast enough for what you want (though I missed the beginning of

Re: [sqlite] attaching databases programmatically

, July 22, 2013 10:51 AM To: General Discussion of SQLite Database Subject: Re: [sqlite] attaching databases programmatically Marc L. Allen wrote on Monday, July 22, 2013 10:47 AM > Nelson, Erik wrote: > > I've got an application that allows the user to create an arbitrary > > numb

Re: [sqlite] attaching databases programmatically

Perhaps I misunderstood the question. It sounds like he has the sqlite* objects for the databases, but wants to be able to determine the database/filename associated with them so he can construct an ATTACH statement in another query. So.. the question is.. given an sqlite*, can you determine

Re: [sqlite] name resolutionn in GROUP BY

As does MS SQL 2008 R2 DROP TABLE #Test CREATE TABLE #Test ( Val int ) INSERT INTO [#Test] ([Val]) VALUES (-2), (2) SELECT Val FROM #Test GROUP BY Val SELECT ABS(Val) AS Val FROM #Test GROUP BY Val Val --- -2 2 Val --- 2 2 Your requested test case: Untitled1 m - - 1 x 1

Re: [sqlite] name resolution in GROUP BY

I seem to recall having read that as well. I believe, however, that MySQL does allow it, but I think it defaults to base table when available. Also, a modified form of the test case: DROP TABLE #t1 CREATE TABLE #t1(m VARCHAR(4)); INSERT INTO #t1 VALUES('az'); INSERT INTO #t1 VALUES('by');

Re: [sqlite] name resolution in GROUP BY

Heh... I forgot.. both selects below are identical, as 'lower(m1)' is incorrect. MS SQL does not permit further operations on the derived value. -Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Marc L. Allen Sent: Wednesday

Re: [sqlite] name resolution in GROUP BY

] On Behalf Of Richard Hipp Sent: Wednesday, August 14, 2013 2:26 PM To: General Discussion of SQLite Database Subject: Re: [sqlite] name resolution in GROUP BY On Wed, Aug 14, 2013 at 2:18 PM, Marc L. Allen <mlal...@outsitenetworks.com>wrote: > Heh... I forgot.. both selects below are

Re: [sqlite] name resolution in GROUP BY

resolution in GROUP BY On Wed, Aug 14, 2013 at 2:18 PM, Marc L. Allen <mlal...@outsitenetworks.com>wrote: > Heh... I forgot.. both selects below are identical, as 'lower(m1)' is > incorrect. MS SQL does not permit further operations on the derived value. > I think you also

Re: [sqlite] name resolutionn in GROUP BY

This appears to be how MS SQL handles it... looking at the definitions below, MS SQL uses the base value in GROUP BY and the derived value in ORDER BY. That said, 'lower(m)' referenced the base m, not the derived m in the ORDER BY. I'm afraid I don't understand enough about COLLATE to get

Re: [sqlite] name resolution in GROUP BY

strictly on the derived table.   Peter From: Marc L. Allen <mlal...@outsitenetworks.com> >To: General Discussion of SQLite Database <sqlite-users@sqlite.org> >Sent: Wednesday, August 14, 2013 11:28 AM >Subject: Re: [sqlite] name resolution in GROUP BY > > >I understand.  My

Re: [sqlite] name resolutionn in GROUP BY

I'd actually like a compromise. Allow GROUP BY to accept a derived name if no base name exists. I realize that's against spec, but there's no ambiguity (as it otherwise errors out), and does make it much nicer when the derived column is a hairy expression that I end up needing to replicate

Re: [sqlite] name resolutionn in GROUP BY

, 14 Aug 2013 14:57:19 -0500 "Marc L. Allen" <mlal...@outsitenetworks.com> wrote: > I'd actually like a compromise. Allow GROUP BY to accept a derived > name if no base name exists. I realize that's against spec, but > there's no ambiguity (as it otherwise errors

Re: [sqlite] Different result from experimental query

Looks like that should return one row, yes? I wonder if operator precedence is broken for that query and the OR is binding higher than the AND. Also possible is that the NOT NULL for id in table t is messing up some query optimization with t2.id NOT NULL. -Original Message- From:

Re: [sqlite] segmentation fault with 3.8.0

Silly question.. I looked at the fix. Why ignore indexes with greater than 4 fields? Isn't that a bit risky? Wouldn't it be better to ignore the fields after the 4th one for planning? -Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org]

Re: [sqlite] segmentation fault with 3.8.0

Database Subject: Re: [sqlite] segmentation fault with 3.8.0 On Thu, Aug 29, 2013 at 11:47 AM, Marc L. Allen <mlal...@outsitenetworks.com > wrote: > Silly question.. I looked at the fix. Why ignore indexes with greater > than 4 fields? Isn't that a bit risky? Wouldn't it be better

Re: [sqlite] Inefficient covering index used for Subversion with SQLite 3.8.0

Am I understanding that, in this example, the I_NODES_PARENT is being chosen as the search index because... it's smaller and therefore faster to find initial qualifying rows that you can then use in some sort of ordered lookup in another index/table? I'm always in awe of some of the plans a

Re: [sqlite] Table name length in sqlite affects performance. Why?

Not to mention having to check each new table to see if it's already in the database and the associated physical reads that might be associated with that. -Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Jay A. Kreibich Sent:

Re: [sqlite] Collating sequences. Was: Query problems

The left-most of the first select? Or the second? Maybe I don't understand 'left-most?' -Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Richard Hipp Sent: Thursday, September 05, 2013 9:36 AM To: General Discussion of

Re: [sqlite] UPDATE question

How about... ? UPDATE table SET Sequence = Sequence + 1 WHERE Sequence >= seq_to_insert AND Name = name_to_insert -Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Peter Haworth Sent: Thursday, September 05, 2013 2:21 PM To:

Re: [sqlite] UPDATE question

Oops.. sorry.. I missed the last paragraph. If you're essentially single threaded.. I can do it in two updates... UPDATE table SET Sequence = -(Sequence + 1) WHERE Sequence >= seq_to_insert AND Name = name_to_insert UPDATE table SET Sequence = -Sequence WHERE Sequence < 0 AND Name =

Re: [sqlite] UPDATE question

No one commented on my second thread (written after I actually understood the problem!). But, I proposed a two update sequence to do it. UPDATE table SET Sequence = -(Sequence + 1) WHERE Sequence >= seq_to_insert AND Name = name_to_insert UPDATE table SET Sequence = -Sequence WHERE Sequence

Re: [sqlite] UPDATE question

I don't think it's a bug. I don't believe there's any defined rule for how SQL should behave, is there? The updates are done serially not atomically. If the rows happen to be processed in reverse order, then no constraint is violated. In fact, if there was a way to define the order the

Re: [sqlite] UPDATE question

Nice, but that still requires extra work. 1) Determine if row is already in table. 2) Determine next lower value. 3) Split difference and insert. There's also the possibility that the higher level APP expects the new row to have a sequence number of 3. -Original Message- From:

Re: [sqlite] UPDATE question

MySQL also uses this implementation. They acknowledge that it is not SQL compliant and that (I never thought of this), you cannot delete a record that has a foreign key link to itself. Postgres apparently has the ability to have deferred checking as of V9, but not before then. Please see:

Re: [sqlite] UPDATE question

Yes, thanks. I was mistaken. On Sep 6, 2013, at 9:27 PM, "James K. Lowden" <jklow...@schemamania.org> wrote: > On Fri, 6 Sep 2013 07:56:53 -0500 > "Marc L. Allen" <mlal...@outsitenetworks.com> wrote: > >> I don't think it's a bug. > >

Re: [sqlite] Hints for the query planner

As I was reading this, I said to myself, "what they really need is a confidence value." Then I read the end and, there it was! A confidence value. Ok.. not exactly confidence, but I think you get my meaning. It seems to me that you're allowing the query writer to substitute personal

Re: [sqlite] Bug : Tcl interface + "INSERT OR REPLACE" statement

Yep. What most people want is an INSERT OR UPDATE. -Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Petite Abeille Sent: Tuesday, September 24, 2013 1:48 PM To: General Discussion of SQLite Database Subject: Re: [sqlite]

Re: [sqlite] Bug : Tcl interface + "INSERT OR REPLACE" statement

of SQLite Database Subject: Re: [sqlite] Bug : Tcl interface + "INSERT OR REPLACE" statement On Sep 24, 2013, at 7:54 PM, "Marc L. Allen" <mlal...@outsitenetworks.com> wrote: > Yep. What most people want is an INSERT OR UPDATE. Yep. Which is what one usually calls 'MER

Re: [sqlite] Bug : Tcl interface + "INSERT OR REPLACE" statement

Discussion of SQLite Database Subject: Re: [sqlite] Bug : Tcl interface + "INSERT OR REPLACE" statement On 24 Sep 2013, at 6:58pm, Petite Abeille <petite.abei...@gmail.com> wrote: > On Sep 24, 2013, at 7:54 PM, "Marc L. Allen" <mlal...@outsitenetworks.com> > wr

Re: [sqlite] Bug : Tcl interface + "INSERT OR REPLACE" statement

NSERT OR REPLACE" statement On 24 Sep 2013, at 7:09pm, Marc L. Allen <mlal...@outsitenetworks.com> wrote: > Also, there are times when you do a bulk insert, so you have to structure the > query to not fail on records that are already present. Yeah. Actually I got what I posted wrong.

Re: [sqlite] Bug : Tcl interface + "INSERT OR REPLACE" statement

Sent: Tuesday, September 24, 2013 2:14 PM To: General Discussion of SQLite Database Subject: Re: [sqlite] Bug : Tcl interface + "INSERT OR REPLACE" statement On Sep 24, 2013, at 8:09 PM, Marc L. Allen <mlal...@outsitenetworks.com> wrote: > Not complaining, mind you. MS SQL doesn'

[sqlite] File locking requirements in Single Process/Multi-Threaded system

I'm trying to use sqlite in an embedded, multi-tasking system with a very simple file system. Essentially, it doesn't support having a file open multiple times. That is, only one reader or writer. I have studied the code, but I can't quite tell how sqlite manages the DB files in a single

Re: [sqlite] File locking requirements in Single Process/Multi-Threaded system

Hipp Sent: Tuesday, February 07, 2012 3:09 PM To: General Discussion of SQLite Database Subject: Re: [sqlite] File locking requirements in Single Process/Multi-Threaded system On Tue, Feb 7, 2012 at 2:42 PM, Marc L. Allen <mlal...@outsitenetworks.com>wrote: > I'm trying to u

Re: [sqlite] File locking requirements in Single Process/Multi-Threaded system

> The methods on the VFS open-file > objectthat do reading and > writing specify both the amount and the offset. There are no seeks. Oh, yeah. :( Sorry. I've even implemented those routines. > The open succeeds. xLock() does not interact with any

[sqlite] Using WAL without shared memory, no exclusive, but single file descriptor

operate with WAL turned on, even without exclusive access? Thanks, Marc -- ** * * * * Marc L. Allen * "... so many t

Re: [sqlite] Database locked in multi process scenario

I'm not sure I'm even following how this scenario can happen. Doesn't App1 have a Shared lock on the DB? Doesn't App2 require an Exclusive lock before it can update something? When given the initial scenario, I thought that Step 5 would block waiting for App1 to finalize. > -Original

Re: [sqlite] Database locked in multi process scenario

So, you're assuming the OP actually started a transaction? Because, otherwise, isn't the SELECT in step 2 and the UPDATE in step 5 separate transactions? If there is a BEGIN in there somewhere, we're talking about: App1: BEGIN SELECT UPDATE .. App2BEGIN

Re: [sqlite] Database locked in multi process scenario

2012 9:28 AM > To: General Discussion of SQLite Database > Subject: Re: [sqlite] Database locked in multi process scenario > > On Fri, Feb 10, 2012 at 9:19 AM, Marc L. Allen > <mlal...@outsitenetworks.com>wrote: > > > > > So, you're assuming the OP actually start

Re: [sqlite] Database locked in multi process scenario

February 10, 2012 9:43 AM > To: sqlite-users@sqlite.org > Subject: Re: [sqlite] Database locked in multi process scenario > > Marc L. Allen <mlal...@outsitenetworks.com> wrote: > > I see. So, the implied commit doesn't occur until you finalize? > > Or reset. > >

Re: [sqlite] Database locked in multi process scenario

) Thanks > -Original Message- > From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users- > boun...@sqlite.org] On Behalf Of Marc L. Allen > Sent: Friday, February 10, 2012 9:45 AM > To: General Discussion of SQLite Database > Subject: Re: [sqlite] Database locked in multi p

Re: [sqlite] Database locked in multi process scenario

> -Original Message- > From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users- > boun...@sqlite.org] On Behalf Of Igor Tandetnik > Sent: Friday, February 10, 2012 2:36 PM > To: sqlite-users@sqlite.org > Subject: Re: [sqlite] Database locked in multi process scenario > > On 2/10/2012

Re: [sqlite] Function context

> Actually, my expectation is the other way. I'm continually surprised > by posts on this list that people expect to be able to use SQLite with > no external programming at all. I often see complicated compound JOIN > and sub-SELECT SQL commands here which can be replaced by four lines in > any

Re: [sqlite] Function context

> More generally, I *often* see good C++ programmers pushing application > logic into SQL, and getting it wrong because they don't really know > SQL. As a result, my rule of thumb is to make the separation between > application code and SQL code based on whether the code is filtering > result

[sqlite] Handling multiple result sets in sqllite3_exec()

If I call exec() with a series of SQL that returns multiple result sets, is there an easy way to determine that in the callback? I could always compare column names, but that seems rather inefficient. Thanks, Marc ___ sqlite-users mailing list

  1   2   >