Add support for more databases

[jhelvy]

As mentioned in #251, it is probably a rather straightforward fix to allow other types of databases to be supported. I'm posting this here just to keep it as an item on the todo list.

[Upipa]

Hi, i was looking more deeply at the code. It may be a little more difficult than expected to implement. I found a way to make editable the connection parameters and driver. The problem starts in updating the database as far as i saw. In database_uploading is used an upsert statement, which is not supported by a lot of DBMS. This is not a great problem: we can check if the id_session already exist and change between insert and update accordingly. The real problem is in column creation: the statement
'ALTER TABLE "%s" ADD COLUMN "%s" TEXT'
is not supported by most DBMS cause the type TEXT doesn't exist in most of them. One solution may be to configure the entire table beforehand, so there's no need to alter the table case by case. To have automatic match with the specific DBMS syntax you can use

table_prototype = tibble(
   id_session = character(),
   col_1 = numeric(),
   col_2 = character(),
   col_3 = logical()
)
dbCreateTable(con, table_name, table_prototype)

In this way the driver creates a table with the correct type for each column, and is adapted to the DBMS. The final touch is updating the table with dbBind. In this way it should adapt the statement based on the type of the input. With this workflow we can adapt the package automatically to almost any DBMS, and almost any type instead of relying heavely on the text type. The problem now becomes how to construct the table_prototype. I saw that the table is created with just session_id, time_start e time_end. I'll look for a way to construct a comprehensive table_prototype from the beginning, without requiring to alter the table on every page of the survey

[jhelvy]

Thanks for looking into this further! I'd be happy to make the backend more flexible to support different database types, and that should all be manageable while maintaining reverse compatibility with the way things are done right now with postgres. I'm just imagining some new argument to sd_db_connect(driver = 'postgres') that defaults to the current approach, and otherwise uses a new approach or other systems. If you can find a new approach that works with other databases, it should be able to be added.