Custom Data APIs

[rufuspollock]

Ability for users to create custom data APIs to complement the standard data API response.

Motivating Examples

Concatenating tables

Suppose I'm storing crime data in multiple resources / datastore tables but I want to be able to query across them. I want to join those tables to have a single crime table.

/custom-api/crime?month=...

Joining tables

I want to have query a table created by joining multiple tables

Implementation

Custom API object

{
  id:    # auto-increment
  name:     # nice unique name for urls
  author: .... # FK to user
  organization: ... # FK to organization
  view: # an sql view spec for generating a view that this will be a query onto
}

URL

/api/datastore/custom/{name}        # query
/api/datastore/custom/{name}/{id}  # access to entity with id

Question: should we have org name in custom url name so we get some kind of namespacing?

[rufuspollock]

@rossjones know you are interested here :-)

[rossjones]

Namespacing by organization seems to be the sensible approach.

Would be useful to allow each endpoint to have the sql view you mentioned, with parameter replacement (taking extra care that params are properly escaped). Want end-users to be able to query the datastore without knowing any SQL and/or structure of the tables.

Also...

owner_user == author
owner_org == organization

Think it would make sense for this to be in the datastore extension rather than separate, although it does potentially mean waiting for whenever 2.3 is scheduled.

[adamamyl]

We're seeing some demand for this. But in the user interface, for non-programmers. And being able to plot combined datasets onto one graph (for example).

(I imagine being able to create a combined dataset as a resource would be pretty essential)

[rufuspollock]

@adamamyl combined datasets on one graph would not require the custom API IMO (might help but would not be required in many cases) - see #13 for more on a "data explorer" intended to support multiple plotting etc

[rufuspollock]

@rossjones all suggestions look good and made changes to table design.

do you think using sql views is the way to go? Also could you explain a bit more what you mean by parameter replacement (agree people should not know any sql ...)

[rossjones]

A naive example of parameter replacement, I mean something like.

/api/custom/buses/near?lat=1.3&lng=53.3

mapping to

    select name, lat, lng, routes from resource_ABCD where lat=:lat and lng=:lng

gives

    select name, lat, lng, routes from resource_ABCD where lat=1.3 and lng=53.3

I think this was probably implicit in your origin post.