[ckan-dev] Started work on the 'webstore' (a datastore with web API)

[Friedrich Lindenberg]

Hi all,

I did some hacking on this last night:

Its hard to do this with SQLite and the normal Python drivers,
apparently. While there is a binding that supports the RO flag, this
in turn would not be compatible with SQLAlchemy which helps us keeping
the code clean.

What I would propose is using PostgreSQL as a backend and having all
stores share a single database. Isolation will be done on a schema
level which is optionally not complete (i.e. users could do SELECT *
FROM cra.cra2010 cra JOIN "openspending.cofog".cofog cofog1 ON
cra.cofog1_id = cofog1.id). To do this, we would create schemas on
demand and a read-only user along with them. Since in PG < 9.0 you
can't assign SELECT privileges on a per-schema basis, this requires
some DDL hookery, but see for yourselves: [1]

All of this works when executed on the PSQL prompt but not currently
via SQLA. Also I'm not entirely sure its a good idea (read: not sure
how bad it is as an idea). One thing that David Raznik mentioned is
file system commands like SELECT INTO ..., another concern is simply
having one giant database that could potentially go AWOL.

Happy for any hints, comments, etc.

- Friedrich

[1] broken branch:
https://github.com/okfn/webstore/blob/readonly/webstore/database.py#L148

On Fri, Jul 15, 2011 at 10:23 PM, Nick Stenning <nick at whiteink.com> wrote:
> On Tue, Jul 12, 2011 at 12:14, Francis Irving <francis at scraperwiki.com> wrote:
>>
>> That's a bit timid!
>>
>> Surely the whole benefit of using an SQL database is to allow arbitary
>> SQL calls in URLs, and the ability to call them directly from
>> Javascript?
>
> I'd just like to submit a strong +1 on this. SQL is a well-understood
> standard for a good reason, and in my opinion the only arguments
> against doing this are invalid here (security isn't an issue if we set
> SQLITE_OPEN_READONLY for non-owners, and it we can kill queries that
> take too long to run).
>
> [Aside: you could *even* consider providing no SQL write support at
> all, could you not?]
>
> I'm all in favour of abstracting the simplest single-table features of
> SQL onto a REST API, but where SQL really comes into its own is in
> joining complex relational data. I'd argue that if you allow raw SQL
> queries, there's no need to provide any JOIN facility from the REST
> API whatsoever, which should simplify your lives a little.
>
> I would *really* like to be able to store cleaned data in webstore,
> and specify a (webstore identifier, SQL query) tuple as an
> OpenSpending source.
>
> Regards,
> Nick
>



-- 
Open Knowledge Foundation
Promoting Open Knowledge in a Digital Age
http://www.okfn.org/ - http://blog.okfn.org/

http://twitter.com/pudo
http://pudo.org