Custom DBMS support

[Upipa]

Description

I don't know if it's already supported. Looking through the documentation doesn't seem so. I think it would be very useful to allow the connection to every kind of database. The support for postgres is great, in this way the survey can be completely free. But we are already paying for a SQL Server and would be nice to have the responses directly in our database, without needeing to creating another one even for free.

[jhelvy]

Under the hood, we're just using SQL queries to send data to the postgres database, so it might still work with another SQL Server. If you look at sd_db_connect(), we make a pool connection, and then return a list object with the pool connection and the table name stored as a string, like this:

db <- list(db = pool, table = table)

So if you can make a pool connection to your SQL database and then make a similar db object like this, then it should work the same way to just pass that object in sd_server(db).

[Upipa]

Maybe it works, but may I suggest that with a little tweak it maybe possible to add support for a wide variety of DBMS. I didn't explore all the codebase but i saw that the pool connection is built in with try_db_connection and the driver is fixed to RPostgres

surveydown/R/util.R

Line 1492 in f5c8c60

drv = RPostgres::Postgres(),

It might be interesting to let RPostgres as a default but allow to set a different driver in the params argument, like odbc for example. In this way, expecially if the creation of a table and the appending of data is done with DBI (dbCreateTable, dbAppendTable) there's automatically a wide support for a lot of DBMS with an already fair translation to the correct SQL if necessary, done via the driver. I will try your suggestion since it's very simple, but you might consider my suggestion too.

[jhelvy]

It's a great idea! And simple enough to support. As you suggested, try_db_connection could be extended to support a parameterized approach where we add a new argument to sd_db_connect() like driver = 'postgres' as a default but also support odbc and pass that argument on to try_db_connection. Is that something you'd be interested in trying to put together and send as a PR?

[Upipa]

I will try, hope to find a little of time to work on it. It seems simple, but i have to check a little. I'm happy to help if i can anyway

[jhelvy]

I have several other larger things I'm trying to add support for over the next week or so, so this will be a lesser priority for me, but I'm happy to review a PR if you try and figure it out. It's also much easier to test and debug if you've already got a working SQL database set up, which I don't, so that's another reason why I figured it might be easier if you want to try and sort out a solution.

[jhelvy]

Tagged this as an enhancement request in #252