Re: [sqlite] Custom functions, variable parameters, and SELECT triggers

On Sat, 2015-01-31 at 00:04 -0500, Igor Tandetnik wrote: > On 1/30/2015 10:44 PM, David Barrett wrote: > > Is it possible to create a trigger that calls a custom function and passes > > in NEW.*? > > Not literally NEW.* . You'll have to spell out individual columns as > parameters. > > > 2) I'm

Re: [sqlite] What is the best alternative to this RIGHT OUTER JOIN ?

On Sun, 2014-11-09 at 15:04 +0200, RSmith wrote: > On 2014/11/09 14:11, Tristan Van Berkom wrote: > >> A good SQL rule of thumb: if you can think of a way, so can the DBMS. "... > >> no opportunity to make a good guess" is not true. In > >> some sense

Re: [sqlite] What is the best alternative to this RIGHT OUTER JOIN ?

On Sat, 2014-11-08 at 14:27 -0500, James K. Lowden wrote: > On Sun, 09 Nov 2014 00:45:16 +0900 > Tristan Van Berkom <tris...@upstairslabs.com> wrote: > > > While I do understand SQL as a functional language, most functional > > programming I've done still has rather

Re: [sqlite] What is the best alternative to this RIGHT OUTER JOIN ?

On Sat, 2014-11-08 at 09:46 -0700, Keith Medcalf wrote: > On Saturday, 8 November, 2014 06:56, Tristan Van Berkom > <tris...@upstairslabs.com> said: > > >On Sat, 2014-11-08 at 06:23 -0700, Keith Medcalf wrote: > >> How about the direct approach: > >&g

Re: [sqlite] What is the best alternative to this RIGHT OUTER JOIN ?

On Sat, 2014-11-08 at 10:23 -0500, James K. Lowden wrote: > On Sat, 08 Nov 2014 22:55:46 +0900 > Tristan Van Berkom <tris...@upstairslabs.com> wrote: > > > So I would have to say, the "right way to do it" is the most efficient > > way, the one which prov

Re: [sqlite] What is the best alternative to this RIGHT OUTER JOIN ?

On Sat, 2014-11-08 at 06:23 -0700, Keith Medcalf wrote: > How about the direct approach: > > SELECT uid > FROM resource > WHERE uid NOT IN (SELECT resource_uid > FROM event_participant, event > WHERE event_participant.event_uid = event.uid >

[sqlite] What is the best alternative to this RIGHT OUTER JOIN ?

Hi all, Today I've stumbled on a situation where I think I really need to use a RIGHT OUTER JOIN, and looking at all the examples on the internet I could find so far, I'm not finding a way to simulate it properly using LEFT OUTER JOINs. So I thought, before I commit to an inefficient alternative

[sqlite] Circular References [Was: Re: [RFE bug] Improve error reporting for foreign keys]

[Changing subject line as this strays away from the original topic] On Mon, 2014-10-27 at 08:22 +, Simon Slavin wrote: > On 27 Oct 2014, at 8:17am, Clemens Ladisch <clem...@ladisch.de> wrote: > > > Tristan Van Berkom wrote: > >> locateFKeyIndex() function iss

Re: [sqlite] [RFE bug] Improve error reporting for foreign keys

On Mon, 2014-10-27 at 09:17 +0100, Clemens Ladisch wrote: > Tristan Van Berkom wrote: > > locateFKeyIndex() function issuing the not-so-informative > > message "foreign key mismatch" [...] > > > > o When foreign keys are enabled at CREATE TABLE t

Re: [sqlite] [RFE bug] Improve error reporting for foreign keys

On Mon, 2014-10-27 at 15:49 +0900, Tristan Van Berkom wrote: > This is just a request-for-enhancement bug report, I've went to the > trouble or reproducing this problem in a simple test case and while > I probably wont be able to immediately benefit from an upstream fix > for

[sqlite] [RFE bug] Improve error reporting for foreign keys

This is just a request-for-enhancement bug report, I've went to the trouble or reproducing this problem in a simple test case and while I probably wont be able to immediately benefit from an upstream fix for this, I hope that this bug report will be perceived as helpful and entered into your

Re: [sqlite] Keeping a history of structured data

On Thu, 2014-07-17 at 21:43 +0100, Simon Slavin wrote: On 17 Jul 2014, at 8:43pm, Tristan Van Berkom <tris...@upstairslabs.com> wrote: > The objective is to keep a revisioned history of 'E' whenever 'E' has > changed, or any of it's 'P' counterparts have changed, ideally witho

[sqlite] Keeping a history of structured data

Hi all, We've been pondering how to keep history of structured records without storing the entirety of the structured record for each new revision, we have some ideas on how to achieve this but I wanted to consult this list in case we have overlooked some other strategies, and also just to see

Re: [sqlite] Using a customized collate function to mimic integer array type.

On Fri, 2014-03-21 at 09:35 -0500, Ben Peng wrote: > Dear sqlite experts, I'm far from an "sqlite expert", others should be able to provide a more authoritive answer I think what you want is rather to simply define your own custom function to implement a custom match. I think using COLLATE is

[sqlite] Connection pooling, sharedcache mode, concurrent write transactions

Hello all. I have a pretty basic question about threads and SQLite, here is the short version: When opening multiple connections to the same database using SQLITE_OPEN_SHAREDCACHE, will SQLite automatically serialize write transactions to the same DB while multiple connections are attempting to

Re: [sqlite] Need advice on using nested selects in JOIN statements as a logical 'AND'

On Sun, 2013-12-01 at 00:40 +, Simon Slavin wrote: > On 30 Nov 2013, at 5:40pm, Tristan Van Berkom <trista...@openismus.com> wrote: > > > So, is there a way that I can tell SQLite forcibly to > > prioritize the index on email_list.value when making > > a pref

Re: [sqlite] Need advice on using nested selects in JOIN statements as a logical 'AND'

On Thu, 2013-11-28 at 00:20 -0500, Igor Tandetnik wrote: > On 11/27/2013 11:52 PM, Tristan Van Berkom wrote: > > > > SELECT DISTINCT summary.uid, summary.vcard FROM 'folder_id' AS summary > > LEFT OUTER JOIN 'folder_id_phon

Re: [sqlite] Need advice on using nested selects in JOIN statements as a logical 'AND'

On Thu, 2013-11-28 at 12:19 +, Simon Slavin wrote: > On 28 Nov 2013, at 11:22am, Tristan Van Berkom <trista...@openismus.com> > wrote: > > > Yes, I definitely agree that on a conceptual level, I should not > > have to consider the pre-optimization of my own

Re: [sqlite] Need advice on using nested selects in JOIN statements as a logical 'AND'

On Thu, 2013-11-28 at 12:11 +0100, Clemens Ladisch wrote: > Tristan Van Berkom wrote: > > Are the JOIN statements equal to the logical AND statements, > > Yes. > Thank you. > > for all practical purposes ? > > If you drop all those superfluous LEFT OUTER and IS

Re: [sqlite] Need advice on using nested selects in JOIN statements as a logical 'AND'

On Thu, 2013-11-28 at 09:43 +0100, Clemens Ladisch wrote: > Tristan Van Berkom wrote: > > When using an INNER join, the engine does something like this: > > > > o Create a data set that is table_1 * table_2 * table_3 rows > > large > > > > o Run th

Re: [sqlite] Need advice on using nested selects in JOIN statements as a logical 'AND'

On Thu, 2013-11-28 at 00:20 -0500, Igor Tandetnik wrote: > On 11/27/2013 11:52 PM, Tristan Van Berkom wrote: > > > > SELECT DISTINCT summary.uid, summary.vcard FROM 'folder_id' AS summary > > LEFT OUTER JOIN 'folder_id_phon

Re: [sqlite] Need advice on using nested selects in JOIN statements as a logical 'AND'

On Thu, 2013-11-28 at 00:20 -0500, Igor Tandetnik wrote: > On 11/27/2013 11:52 PM, Tristan Van Berkom wrote: > > > > SELECT DISTINCT summary.uid, summary.vcard FROM 'folder_id' AS summary > > LEFT OUTER JOIN 'folder_id_phon

[sqlite] Need advice on using nested selects in JOIN statements as a logical 'AND'

Hi, I don't have many years experience with the SQL language and I've cooked up some pretty complex stuff which will run in production environments, I just want to confirm with you that the assumptions I've made are true (I do have a lot of unit tests which confirm that my code works as far as

Re: [sqlite] Proper handling of SQLITE_BUSY

On Wed, 2013-11-20 at 13:23 +0200, RSmith wrote: > Hi Tristan, > > Do you honestly have a use-case where you do not know whether a transaction > is going to be writing to the DB or not? > > I would imagine the only way this is possible is that you are doing some form > of select query, and

Re: [sqlite] Proper handling of SQLITE_BUSY

On Mon, 2013-11-18 at 22:54 -0700, Keith Medcalf wrote: > >Since I run all of these statements withing transactions (between > >"BEGIN" statements and "COMMIT" / "ROLLBACK" statements"), my > >expectation is that SQLITE_BUSY will only ever be returned for > >the leading "BEGIN" statement. > >

[sqlite] Proper handling of SQLITE_BUSY

Hi, The C code that I use with SQLite is pretty well tested and known to work well so far, but I've ran into some documentation which leads me to suspect there is a problem with my existing code, or a problem with the documentation. >From the documentation:

Re: [sqlite] curious: Why no "shared library" or DLL?

On Fri, 2013-08-30 at 08:11 -0500, John McKown wrote: > I know that sqlite is designed as an "embedded" SQL engine. But I am > curious if there is a specific reason to _not_ have a DLL/shared object. > The main reason I ask is that a DLL is required in order to create a sqlite > Java JDBC

Re: [sqlite] DB change notification hooks across process barriers

On 11/27/2012 01:40 PM, Simon Slavin wrote: On 27 Nov 2012, at 3:58am, Yongil Jang wrote: What I'd done to solve this problem is reading change counter in db file header. Or watch the modification date of the database file. Right, except what I need is to know

[sqlite] DB change notification hooks across process barriers

Hi all, I just wanted to verify this detail. As I read here, http://www.sqlite.org/c3ref/update_hook.html, the function sqlite3_update_hook() can be used to watch for row insert/update/delete changes to a DB. Now, the documentation does not mention anything about this function not working

[sqlite] escape sequences for GLOB

Hi all, I've been around and around the documentation, run a sequence of test cases and still haven't figured this out. What is the proper default escape sequence to be used for GLOB pattern matching in SQLite ? I've already read in this other thread: