[ckan-discuss] RDBMS Data Source

Wed Mar 21 22:38:23 GMT 2012

Hello,

Thanks for your ideas.

> Is there a plugin that supports a relational database as fourth type of
> data source?

Thats an interesting idea.  The only worry is that there is no clear
and safe way of connecting to a database over the internet.  I.e just
exposing a posgresql, mysql, oracle port and host to the world.  The
only safe way is probably to have ssh tunnels.  So the database that
needs to be added would provide a public key.

Grabbing the data from a local source would not be very tricky at all,
i.e we could set up an a restricted read only local user that does not
require login details.  We would probably use sqlalchemy style urls
for this.  Using this method we would also fairly easily be able to
get field types as sqlalchemy does a decent job of reflecting the
underlying schema.

> 1. Support for (partial) SQL dumps.
> This is in the same vein as connecting directly to a database. Except, as
> you pointed out, login information is usually guarded. But a SQL dump file
> can be parsed to learn the schema. We can limit ourselves to 'create
> table' and 'create view' statements as they are sufficient to understand
> the schema.

I think that would be a huge amount of work as all the different rdms
dumps are different and very subtly different too. To parse this data
is pretty painful. I think we would have more success actually
restoring the dumps and analysing the table structure by the above
method.

Thanks

David