[ckan-dev] CKAN on top of Existing DBs

Thu Jul 17 18:24:10 UTC 2014

I think I like the idea of a separate service that maps URLs to database queries and returns the data in one of several formats.  Might well also have a wider use.

There was a very old project along these lines at https://github.com/okfn/webstore (in case anybody was wondering where the never used webstore_url field in the default resource schema - https://github.com/ckan/ckan/blob/master/ckan/logic/schema.py#L87 - came from ;) ). I’m not sure if it would be worth using as a starting point, but perhaps…

Ross

On 17 Jul 2014, at 18:13, Alessio Dragoni <alessio.dragoni at gmail.com> wrote:

> Hi Steve,
> despite this is clearly a useful feature I believe it could be exposing to SQL injection.
> My suggestion is to build apart a sort of db table/query dump services using traditional ORM tools and then
> point to the services you have created with the proper type when creating the dataset resource.
> You can create the resource as URL with CSV/JSON/XML. etc. depending how the service is returning the data from your database.
> 
> Exists already several tools that can be used to build quickly similar services.
> There's also the advantage that you can build suck dump/services with your preferred language/framework
> 
> Alessio
> 
> 
> On 07/17/2014 06:56 PM, Rimar, Steve wrote:
>> Ross – the way I would envision it is when adding a dataset there would be a 3rd option which would be Existing Database. You would then enter the database connection info depending on the type of DB similar to how database query tools work. Once the connection is entered you would have the option to browse and select tables or schemas or enter SQL.
>> 
>> For the user who is looking at the dataset in CKAN they would be able to preview a snapshot of the data similar to how the preview works for CSV files.
>> 
>> The data would not be imported into CKAN as this would defeat the purpose. CKAN would just store the connection info and the tables/schemas or custom SQL. 
>> 
>> Do you know if this would be possible to develop? Has anyone been creating plug-in for this?
>> 
>> -Steve
>> 
>> From: Ross Jones <ross at servercode.co.uk>
>> Reply-To: CKAN Development Discussions <ckan-dev at lists.okfn.org>
>> Date: Thursday, July 17, 2014 at 2:58 AM
>> To: CKAN Development Discussions <ckan-dev at lists.okfn.org>
>> Subject: Re: [ckan-dev] CKAN on top of Existing DBs
>> 
>> It’d be really useful to have some user stories for this I think. For instance, what does the user provide instead of a file or URL? Is it an ODBC connection string and a query? How would it safely handle authentication with the DB?
>> 
>> Perhaps it would be better for the user to provide an OData endpoint url when creating a resource?  Or a separate simple flask app that does the DB->CSV conversion for display (and perhaps allows you to specify a query in the preview?).
>> 
>> At the moment, although I understand the high level request, I’m not really sure how you envision it working.  I can’t see why it can’t be built though (other than not being very clear) particularly if you can provide the data via a URL.
>> 
>> Ross
>> 
>> 
>> On 16 Jul 2014, at 19:54, Rimar, Steve <srimar at qualcomm.com> wrote:
>> 
>>> Hi - we're trying to use CKAN for our organization’s data registry and were wondering how we would use this on top of existing datasets that are in databases such as Oracle or SQL Server? When you add a dataset in CKAN you have 3 options: File, Link or API. Looks like there's an option missing for Database or JDBC/ODBC? Is this available as an add-on or plug-in? Can we build this out ourselves if not?
>>> 
>>> Thanks,
>>> Steve
>>> _______________________________________________
>>> ckan-dev mailing list
>>> ckan-dev at lists.okfn.org
>>> https://lists.okfn.org/mailman/listinfo/ckan-dev
>>> Unsubscribe: https://lists.okfn.org/mailman/options/ckan-dev
>> 
>> 
>> 
>> _______________________________________________
>> ckan-dev mailing list
>> ckan-dev at lists.okfn.org
>> https://lists.okfn.org/mailman/listinfo/ckan-dev
>> Unsubscribe: https://lists.okfn.org/mailman/options/ckan-dev
> 
> _______________________________________________
> ckan-dev mailing list
> ckan-dev at lists.okfn.org
> https://lists.okfn.org/mailman/listinfo/ckan-dev
> Unsubscribe: https://lists.okfn.org/mailman/options/ckan-dev

-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.okfn.org/pipermail/ckan-dev/attachments/20140717/fa9718ad/attachment-0003.html>