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

[Nick Stenning]

On 16 Jul 2011, at 20:50, Friedrich Lindenberg <friedrich.lindenberg at okfn.org> wrote:

> 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.

I agree that hacking together some combination of sqla and pysqlite is probably not a good solution. However, I'm really strongly in favour of keeping sqlite as the backend if at all possible, partially for personal reasons (I do almost all my data hacking with sqlite databases and would love to be able to upload them direct to webstore) but mainly for reasons of isolation.

> (i.e. users could do SELECT * FROM cra.cra2010 cra JOIN "openspending.cofog".cofog cofog1 ON cra.cofog1_id = cofog1.id)

Are you suggesting here that people would be able to SELECT across different webstore databases? If so, I'm strongly in opposition to this idea. I recognise the elegance in principle, but in practice I think evidence is the supposed reuse of data doesn't work ... And I think OpenSpending can testify to that. We should prevent users from building queries that rely on *other people's schemas*. 

So, having told you what I'm not in favour of, here's another suggestion:

There's one very easy way of ensuring read-only queries against an SQLite databases, and that's to connect to them as a unix user with read-only permissions. We can then (presumably) catch any errors that arise as a result of trying to run read-write queries against them and present a nicer error message to the user.

I'm thinking that maybe webstore could spawn a process as a different user, dependent on the authz status of the user making the API request? One user has RW access to the db, one has RO.

There's also some discussion about ensuring that only RO transactions are happening at a higher level by monkeypatching sqla here: http://www.mail-archive.com/sqlalchemy@googlegroups.com/msg21801.html

What are your thoughts?

N
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.okfn.org/pipermail/ckan-dev/attachments/20110717/6f610f29/attachment-0002.html>