New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Introduce a more general query mechanism beyond keys/ranges #45

Open
inexorabletash opened this Issue Oct 7, 2015 · 10 comments

Comments

Projects
None yet
4 participants
@inexorabletash
Member

inexorabletash commented Oct 7, 2015

Provide the primitives and/or higher level syntax to allow more complex queries to be submitted to the database backend, without having to do so much iteration in script.

@inexorabletash

This comment has been minimized.

Show comment
Hide comment
@inexorabletash

inexorabletash Oct 7, 2015

Member

One hand-wavy approach would be to add boolean/set operations as query types, e.g.:

store.get(IDBQuery.and(IDBQuery.is("name", "alice"), IDBQuery.is("salary", IDBKeyRange.bounds(1e5,2e5))))

... with ways of referencing indexes, etc. This could be the target of an ES6 "tagged template string" microsyntax compiler, or we could introduce a blessed query syntax.

Member

inexorabletash commented Oct 7, 2015

One hand-wavy approach would be to add boolean/set operations as query types, e.g.:

store.get(IDBQuery.and(IDBQuery.is("name", "alice"), IDBQuery.is("salary", IDBKeyRange.bounds(1e5,2e5))))

... with ways of referencing indexes, etc. This could be the target of an ES6 "tagged template string" microsyntax compiler, or we could introduce a blessed query syntax.

@sicking

This comment has been minimized.

Show comment
Hide comment
@sicking

sicking Oct 30, 2015

Contributor

When considering this, we really need to think about what pieces we'd rather leave to libraries, and which ones we should build in to the API.

For the first two use cases in the previous comment, I think an SQL database would create a temporary table and insert results in that table.

We could do something similar and add APIs on the transaction object for creating a temporary btree or hash table and then allow reading/writing in that.

Contributor

sicking commented Oct 30, 2015

When considering this, we really need to think about what pieces we'd rather leave to libraries, and which ones we should build in to the API.

For the first two use cases in the previous comment, I think an SQL database would create a temporary table and insert results in that table.

We could do something similar and add APIs on the transaction object for creating a temporary btree or hash table and then allow reading/writing in that.

@kristofdegrave

This comment has been minimized.

Show comment
Hide comment
@kristofdegrave

kristofdegrave Nov 2, 2015

I think you should stick with the NOSQL approach. If I can make a suggestion: make it possible to have multiple filters, or have a callback in which you can filter your data. That is an approach I used in my own lib I wrote. I do all my filtering in a webworker and I give the user the ability to provide a filtering function. (My worker for filtering and ordering the data. https://linq2indexeddb.codeplex.com/SourceControl/latest#Linq2IndexedDB/Linq2IndexedDB/Source/Worker.js and this is a blog post on how to use it http://www.kristofdegrave.be/2012/07/linq2indexeddb-custom-filters.html)

kristofdegrave commented Nov 2, 2015

I think you should stick with the NOSQL approach. If I can make a suggestion: make it possible to have multiple filters, or have a callback in which you can filter your data. That is an approach I used in my own lib I wrote. I do all my filtering in a webworker and I give the user the ability to provide a filtering function. (My worker for filtering and ordering the data. https://linq2indexeddb.codeplex.com/SourceControl/latest#Linq2IndexedDB/Linq2IndexedDB/Source/Worker.js and this is a blog post on how to use it http://www.kristofdegrave.be/2012/07/linq2indexeddb-custom-filters.html)

@nolanlawson

This comment has been minimized.

Show comment
Hide comment
@nolanlawson

nolanlawson Sep 22, 2016

Member

Due to the popularity of MongoDB, CouchDB has implemented a Mongo-inspired query API, cheekily called Mango. (PouchDB's version is called pouchdb-find.)

I suggest we implement something similar to tap into web developer familiarity with MongoDB as "the" canonical NoSQL database.

Sketch:

store.get(new IDBQuery({
  name: 'Nolan'
}))

equivalent to:

store.get(new IDBQuery({
  name: {
    $eq: 'Nolan'
  }
}))

Fancier:

store.getAll(new IDBQuery({
  firstName: 'Nolan',
  age: {
    $lt: 40,
    $gte: 30
  }
}, {
  sort: ['lastName', 'firstName'],
  limit: 20,
  descending: true,
  skip: 1
}))

The second argument would be optional. Default sorting can be primary key. Default limit would be Infinity. descending would default to false. skip would default to 0.

I suggest the engine should automatically determine the index to use for a given API (via some handwavy query planner algorithm). If no index is found, then an inefficient in-memory method should be used (possibly logging a warning). Based on experience with PouchDB/Cloudant/CouchDB, a warning is more useful than throwing an error.

Member

nolanlawson commented Sep 22, 2016

Due to the popularity of MongoDB, CouchDB has implemented a Mongo-inspired query API, cheekily called Mango. (PouchDB's version is called pouchdb-find.)

I suggest we implement something similar to tap into web developer familiarity with MongoDB as "the" canonical NoSQL database.

Sketch:

store.get(new IDBQuery({
  name: 'Nolan'
}))

equivalent to:

store.get(new IDBQuery({
  name: {
    $eq: 'Nolan'
  }
}))

Fancier:

store.getAll(new IDBQuery({
  firstName: 'Nolan',
  age: {
    $lt: 40,
    $gte: 30
  }
}, {
  sort: ['lastName', 'firstName'],
  limit: 20,
  descending: true,
  skip: 1
}))

The second argument would be optional. Default sorting can be primary key. Default limit would be Infinity. descending would default to false. skip would default to 0.

I suggest the engine should automatically determine the index to use for a given API (via some handwavy query planner algorithm). If no index is found, then an inefficient in-memory method should be used (possibly logging a warning). Based on experience with PouchDB/Cloudant/CouchDB, a warning is more useful than throwing an error.

@inexorabletash

This comment has been minimized.

Show comment
Hide comment
@inexorabletash

inexorabletash Sep 22, 2016

Member

If there's no index on a property then this involves decoding every record, which (at least in Chrome) is a process hop and likely jank, akin to creating a new index on existing store. While that's a "quality of implementation" thing I wonder if that'll be common across browsers.

It also might be incentive for browsers to create/maintain automatic indexes; you'd pay the cost on the first query. (I bet there's a fancy database term for that.)

Member

inexorabletash commented Sep 22, 2016

If there's no index on a property then this involves decoding every record, which (at least in Chrome) is a process hop and likely jank, akin to creating a new index on existing store. While that's a "quality of implementation" thing I wonder if that'll be common across browsers.

It also might be incentive for browsers to create/maintain automatic indexes; you'd pay the cost on the first query. (I bet there's a fancy database term for that.)

@inexorabletash

This comment has been minimized.

Show comment
Hide comment
@inexorabletash

inexorabletash Sep 22, 2016

Member

age: { $gte: 30, $lt: 40 } vs. age: IDBKeyRange.bounds(30, 40, false, true) ?

The former is more readable, the latter is more consistent with the API. I suppose both is doable, producing the same internal range type.

Member

inexorabletash commented Sep 22, 2016

age: { $gte: 30, $lt: 40 } vs. age: IDBKeyRange.bounds(30, 40, false, true) ?

The former is more readable, the latter is more consistent with the API. I suppose both is doable, producing the same internal range type.

@inexorabletash

This comment has been minimized.

Show comment
Hide comment
@inexorabletash

inexorabletash Sep 22, 2016

Member

Does this inform solutions for n-dimensional index query need in #40 ?

Member

inexorabletash commented Sep 22, 2016

Does this inform solutions for n-dimensional index query need in #40 ?

@nolanlawson

This comment has been minimized.

Show comment
Hide comment
@nolanlawson

nolanlawson Sep 22, 2016

Member

Yes, this seems to have a lot of overlap with #40. The example in that case could be solved Mango-style with:

{
  x: { $gt: 10, $lt: 50 },
  y: { $gt: 20, $lt: 30 }
}

(Assuming the index is on an array of [x, y].)

Member

nolanlawson commented Sep 22, 2016

Yes, this seems to have a lot of overlap with #40. The example in that case could be solved Mango-style with:

{
  x: { $gt: 10, $lt: 50 },
  y: { $gt: 20, $lt: 30 }
}

(Assuming the index is on an array of [x, y].)

@inexorabletash

This comment has been minimized.

Show comment
Hide comment
@inexorabletash

inexorabletash Sep 30, 2016

Member

Another case: The "join" pattern with multiple cursors comes up a lot. I used it in a single index over in this fulltext search example:

https://gist.github.com/inexorabletash/a279f03ab5610817c0540c83857e4295

I'm noting this here rather than in #92 since it involves a single index; like the examples here over a single store with one or more indexes, the primary keys are implicitly the same so it's simpler to express.

Mango-style, would that be { words: { $all: [ 'x', 'y' ] } } ?

Member

inexorabletash commented Sep 30, 2016

Another case: The "join" pattern with multiple cursors comes up a lot. I used it in a single index over in this fulltext search example:

https://gist.github.com/inexorabletash/a279f03ab5610817c0540c83857e4295

I'm noting this here rather than in #92 since it involves a single index; like the examples here over a single store with one or more indexes, the primary keys are implicitly the same so it's simpler to express.

Mango-style, would that be { words: { $all: [ 'x', 'y' ] } } ?

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment