[sqlite] Version 3.13.0 coming soon
On Tue May 03, 2016 at 08:33:30AM -0400, Richard Hipp wrote: > On 5/2/16, Rolf Ade wrote: > > > > Richard Hipp writes: > >> A change summary for 3.13.0 is at > >> https://www.sqlite.org/draft/releaselog/3_13_0.html > > > > Change the temporary directory search algorithm on Unix to allow > > directories read and execute permission, but without read permission, > > to > > serve as temporary directories. > > > > .. "write and execute permission, but without read permission" ? > > Yes. Apparently that is the new standard for security on unix The way I understood Rolf's comment was that he was pointing out a typo: the text reads "read and execute" permission when it should probably read "write and execute" permission. The way I understood your reply (although informative) was that you missed Rolf's point. Could be however that I've mis-understood both points :-) -- Mark Lawrence
[sqlite] FOREIGN KEY constraint failed
On Tue Apr 05, 2016 at 11:56:53PM +0200, R Smith wrote: > > On 2016/04/05 11:15 PM, Keith Medcalf wrote: > >Are we confusing immediate constraints (checked per statement) with > >DEFERRED constraints (checked at COMMIT time) again? > > > > We might be - though I assume the OP implicated only deferred > constraints - since immediate constraints will fail on contact, and > as such, no mystery surrounds their origins. My assumption might be > wrong. There is plenty of mystery around immediate constraints when triggers are involved. I often have an issue with statements failing with the generic foreign key message where the actual issue is three or four trigger levels deep. So I can only add my +1 to the "this is an issue" position as well as a +1 to "please can we have *some* kind of help." What I usually end up doing is re-executing the statement with foreign keys turned off, and then run the foreign_key_check pragma. But it doesn't always work because if the problem trigger/statement doesn't fail then later statements sometimes mask the original problem. So my suggestion would be a development pragma to request that SQLite does this itself before the transaction gets rolled back, adding the results of the foreign_key_check to the error message. Mark -- Mark Lawrence
[sqlite] EXPLAIN QUERY PLAN for non-SELECT statements?
I wanted to see how SELECT queries within triggers are using indexes (or not) which of course lead me to https://www.sqlite.org/eqp.html: EXPLAIN QUERY PLAN is most useful on a SELECT statement, but may also be appear with other statements that read data from database tables (e.g. UPDATE, DELETE, INSERT INTO ... SELECT). However I don't get any output from EQP on non-SELECT queries: .version -- SQLite 3.9.1 2015-10-16 17:31:12 767c1727fec4ce11b83f25b3f1bfcfe68a2c8b02 create table t( a integer not null, b integer not null ); create index t_a on t(a,b); create trigger t_ai after insert on t for each row begin insert into t(a,b) select a,new.a from t where a < new.a and a = b; end; insert into t(a,b) values(1,1); insert into t(a,b) values(2,2); insert into t(a,b) values(3,3); select * from t order by a,b; -- a b -- -- -- -- 1 1 -- 1 2 -- 1 3 -- 2 2 -- 2 3 -- 3 3 explain query plan insert into t(a,b) values(4,4); -- No output! Is the above lack of output expected? Is there some way other than cutting and pasting and substituing NEW.*/OLD.* values to see what my triggers are doing? Mark -- Mark Lawrence
[sqlite] MIN/MAX query
On Wed Feb 17, 2016 at 06:17:40PM +, David Bicking wrote: > I have a table > I? L? V1 1?? A1 2?? A1 3?? A1 4?? B1 5 ? B1 6?? A1 7?? A2 1 ? C2 2?? C The formatting of this (and your desired results) does not make the question clear enough. Can you resend with each row on a separate line and perhaps the text "NULL" to represent empty values? Mark -- Mark Lawrence
[sqlite] FOREIGN KEY enhancement request
On Wed Nov 25, 2015 at 10:45:07AM +, Simon Slavin wrote:
>
> REFERENCES foreign-table ( column-name [[, column-name]]) ...
>
> It would be useful if rather than having to specify a column name for
> each item one could specify a fixed value (or perhaps an expression,
> though that might be more complicated to handle). This means that,
> if some customers were dealers and others weren't you could ensure
> you gave discounts only to dealers using
>
> REFERENCES customers ('dealer', id)
Which column in the customers table should contain the fixed value or
expression?
--
Mark Lawrence
[sqlite] Database sybchronisation
On Thu Aug 13, 2015 at 10:06:44AM +0200, Dominique Devienne wrote: > > The new RBU [1] extension, coupled with the new sqldiff utility, > might be of interest too.--DD > > [1] http://www.sqlite.org/rbu.html Is there a table of contents or index of SQLite extensions somewhere on the main website? The only page I can find is the /contrib one which doesn't include the above rbu, or FTS3/4, etc. -- Mark Lawrence
[sqlite] Patch that add ".dumpdata" command to shell.c
> This command should behave exactly like ".dump" but without the > database schema. I would occasionally find that command useful. > Here is a small patch that adds ".dumpdata" for "shell.c" in sqlite3. This mailing list does not accept attachments. Perhaps you can post a link? -- Mark Lawrence
[sqlite] Contstant WHERE terms still require table scan?
On Fri Jun 12, 2015 at 09:49:29AM +, Hick Gunter wrote: > Seems the correct code is already generated... Thanks Hick, that shows a bit more detail I didn't think to look for. It seems that this only works for bind values, as the comparison and goto statements aren't present when the term is defined at prepare time: .width 4 10 4 4 4 10 2 10 EXPLAIN SELECT x.id FROM x WHERE 1=0 ; addr opcode p1p2p3p4 p5 comment -- -- -- -- 0 Init0 9 0 00 NULL 1 Ne 2 7 1 51 NULL 2 OpenRead0 2 0 0 00 NULL 3 Rewind 0 7 0 00 NULL 4 Rowid 0 3 0 00 NULL 5 ResultRow 3 1 0 00 NULL 6 Next0 4 0 01 NULL 7 Close 0 0 0 00 NULL 8 Halt0 0 0 00 NULL 9 Transactio 0 0 1 0 01 NULL 10TableLock 0 2 0 x 00 NULL 11Integer 1 1 0 00 NULL 12Integer 0 2 0 00 NULL 13Goto0 1 0 00 NULL That makes me think that for the 1=0 case the scan occurs anyway? -- Mark Lawrence
[sqlite] Contstant WHERE terms still require table scan?
> This would potentially allow me to shortcut some largish UNION > statements. I should clarify: I don't want to have to force my callers to use their own if/then/else statements in order to pick a specific query. I want a single general-purpose query they can call that shortcuts based on a bind value (or WHERE clause to a VIEW). -- Mark Lawrence
