Re: [sqlite] Full outer joins

2014-10-21 Thread dave
> -Original Message- > From: sqlite-users-boun...@sqlite.org > [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Richard Hipp > Sent: Tuesday, October 21, 2014 6:59 PM > To: General Discussion of SQLite Database > Subject: Re: [sqlite] Full outer joins > > > On Tue, Oct 21, 2014 at

Re: [sqlite] Full outer joins

2014-10-21 Thread Keith Medcalf
Ooops. This can be further simplified as: select rowid from a where rowid not in b union all select rowid from b where rowid not in a; >>It looks hairy but here's what it's doing. Given tables A,B: > >>1. Do the regular join (all rows with matches in both A and B) >>2. Find rows in A that

Re: [sqlite] Full outer joins

2014-10-21 Thread Keith Medcalf
>It looks hairy but here's what it's doing. Given tables A,B: >1. Do the regular join (all rows with matches in both A and B) >2. Find rows in A that aren't in B >3. Find rows in B that aren't in A >4. Concatenate those 3 queries together with UNION ALL will be the same as A UNION ALL B, which

Re: [sqlite] Full outer joins

2014-10-21 Thread Richard Hipp
On Tue, Oct 21, 2014 at 7:12 PM, Paul Sanderson < sandersonforens...@gmail.com> wrote: > out of interest why are > full out joins not supported? > In 14 years, you are the first person to ask for them. That tells me that probably not many people would use them even if we did put them in. --

Re: [sqlite] Full outer joins

2014-10-21 Thread David King
> I want to find the rows that dont appear in both tables. So I think I > need a full outer join which I understand is not supported by SQLite > I have seen this which gives an alternative, but it seems very complex > http://en.wikipedia.org/wiki/Join_%28SQL%29#Full_outer_join > Is there an easier

[sqlite] Full outer joins

2014-10-21 Thread Paul Sanderson
I have two tables from two versions of the same database each table has an integer id primary key I want to find the rows that dont appear in both tables. So I think I need a full outer join which I understand is not supported by SQLite I have seen this which gives an alternative, but it seems

Re: [sqlite] JSON indexing

2014-10-21 Thread Mike Jarmy
jq looks very interesting. I have a simplistic filtering syntax to work with right now, as part of a third-party API definition that my system needs to support, but it doesn't support querying into nested structures. jq looks like just the ticket for that. I'm sure I'll take a look when the

Re: [sqlite] JSON indexing

2014-10-21 Thread Nico Williams
On Tue, Oct 21, 2014 at 2:13 PM, Mike Jarmy wrote: > I could probably use an Entity-Attribute-Value approach to modeling JSON Yes. > I can sort of work around needing to query embedded data structures like > lists and json objects, so EAV would more or less work for me.

Re: [sqlite] JSON indexing

2014-10-21 Thread Mike Jarmy
It appears that neither Unqlite nor sculejs supported disk-based indexing of arbitrary JSON data, which is the use case I'm looking at here (they both look cool though). I already have something very similar to sculejs, but I currently keep all the JSON documents in RAM, which is not going to

Re: [sqlite] JSON indexing

2014-10-21 Thread Nico Williams
On Tue, Oct 21, 2014 at 10:14 AM, Dominique Devienne wrote: > On Tue, Oct 21, 2014 at 3:38 PM, Mike Jarmy wrote: > >> Has there been any discussion of adding JSON indexing to sqlite, similar to >> the way Full Text Search and R-Tree are available? >> >>

Re: [sqlite] JSON indexing

2014-10-21 Thread Neville Dastur
Originally built for Appcelerator but now covers more than that https://github.com/dan-eyles/sculejs this library might be what you are looking for. Neville — Surgeons Net Education: http://www.surgeons.org.uk Clinical Software Solutions: http://www.clinsoftsolutions.com Find our free and paid

Re: [sqlite] JSON indexing

2014-10-21 Thread Peter Aronson
I've never used it myself, but there is http://www.unqlite.org/, which is an embedded document store database library. I believe it uses JSON as native storage format, and it supports an embedded scripting language to access the contents. The Unqlite forums seem active, so it seems to be

Re: [sqlite] JSON indexing

2014-10-21 Thread Mike Jarmy
Hmm, UnQL looks like just what I was looking for. Seems like the project never really got rolling though. DocumentDB looks very interesting. There is a lot of activity in this space I think -- the Postgres 'jsonb' stuff is really great. An embedded solution is what I need though :-) On Tue,

Re: [sqlite] JSON indexing

2014-10-21 Thread Cory Nelson
On Tue, Oct 21, 2014 at 10:14 AM, Dominique Devienne wrote: > On Tue, Oct 21, 2014 at 3:38 PM, Mike Jarmy wrote: > > > Has there been any discussion of adding JSON indexing to sqlite, similar > to > > the way Full Text Search and R-Tree are available? > >

Re: [sqlite] JSON indexing

2014-10-21 Thread Dominique Devienne
On Tue, Oct 21, 2014 at 3:38 PM, Mike Jarmy wrote: > Has there been any discussion of adding JSON indexing to sqlite, similar to > the way Full Text Search and R-Tree are available? > > Postgres 9.4 beta has a very nice facility for creating indexes on json > column types: > >

[sqlite] JSON indexing

2014-10-21 Thread Mike Jarmy
Has there been any discussion of adding JSON indexing to sqlite, similar to the way Full Text Search and R-Tree are available? Postgres 9.4 beta has a very nice facility for creating indexes on json column types: http://www.postgresql.org/docs/9.4/static/datatype-json.html It would be extremely