[ENH] ODBC support for SQL Table

[ajdapretnar]

To address all database issues at once, we would have to implement ODBC support. This would enable loading any database and reroute the specifics of handling each DB instance to an external framework.

[KOLANICH]

Could you rename this issue? SQLite support is not about ODBC.

[ajdapretnar]

You are right, it is not directly related, but there seem to be SQLite drivers available for ODBC. I am afraid that SQLite on its own has no chance of making it into Orange, unless someone wants to contribute a pull request with the appropriate support.

[KOLANICH]

BTW, https://github.com/baztian/jaydebeapi may be useful too.

[fabianegli]

Using SQLAlchemy might be a solution to this problem. It allows to interface with various SQL dialects - including PyODBC and pymssql, the latter is currently used in the widget. I see the Core as the most valuable part of SQLAlchemy for this issue.

I believe valuable insight can be gained from studying the db-to-sqlite tool by Simon Willison in which he makes use of the Database URLs.

Applying this to Orange3 would only require a smart way to create the Database URL from the input in the SQL Table widget, which should be straight forward since the Database URLs have a well-defined format and the use of SQLAlchemy as in the db-to-sqlite tool.

[janezd]

Issue moved to #4090.

[animator]

Hi,

I had implemented a prototype of connecting to the Database via pyodbc.

You can view it in action in this video (first 40 seconds)
Building In-database Decision Tree Model using Orange Data Mining

The idea was to use 2 widgets:

1. ODBC Connection widget -
   
   This widget allowed users to select the ODBC connection name as available on his/her computer. The idea of having a separate widget (not connected to the flow) was a design requirement as all other widgets were executed in-database, so it was acting like a global connection setting and the user was not allowed to connect to multiple databases in the same workflow.

2. Table widget -
   
   A very simple way of implementing it is to put 2 empty text fields (Database and Table).
   But, that is inconvenient and from UI/UX perspective it is preferred that we allow users to select the Database name and Table name using drop-down fields (in the video above you can see the same).

Table widget is the tricky part, as:

• There is no standard SQL command to fetch the list of databases and tables. For each database connection type (postgres, mysql, teradata, oracle, etc.), you need to fire a separate query to get these lists.
• Obtaining this list is less than half the job done, as you need to restrict the list to only those databases and tables for which the user has SELECT permissions.

In respect to implementing this feature in Orange.

• Do not handle the user permission complexity. The database will return appropriate error message on selecting a table for which the user has no permission.
• Do not have a separate widget as ODBC Connection widget shown above as it is goes against the principle of flow programming.
• I am assuming the user wants to create an in-memory copy of the table. As Orange is built for in-memory analytics, we can assume that the user wants to import all data from the database.
• JDBC is an alternative more "painful" to get up and running especially in windows environment.

The SQL Table widget is not UI/UX friendly currently as the user has to go through a lot of steps to make it work. But, I think it is a good starting point and we can make the following changes to it:

• Replace the following fields - Database type, Host name, Username, Password with a single drop-down field called ODBC connection name. This drop-down will list all the ODBC connections currently available for the user. A user can install the ODBC driver of his choice and set up a new connection via "ODBC Data Source Administrator" toolkit on windows and "unixODBC" on Linux. This new connection will get reflected in the widget drop-down field.
• We should keep the field for Database name, and Table name as it is currently in the SQL Table widget.

@ajdapretnar @janezd Please let me know your thoughts in regards to the idea I have suggested above. If you can suggest any improvements it would be great. Since it is a core widget and not an extension, i can start some work on it and send a pull request once we decide the final design of the widget.

[ajdapretnar]

I think the best people to discuss this are @lanzagar and @VesnaT. Along with @janezd, of course.

Overall, I'd prefer a single widget. It should be implemented in Prototypes add-on, where we can test it. When the widget becomes stable, we usually move it to core Orange.

[irgolic]

@animator, Feel free to join our new Discord server and engage with us in the #development channel, should you prefer to communicate over chat.

https://discord.gg/FWrfeXV

[pjaol]

Is there an update on this?

[animator]

@pjaol I had raised the issue (way back 30 Oct 2019) in the discord server.
Never heard back from the core team members in regards to my proposal, so I moved on to other projects.

[pjaol]

It looks like someone started on #5079
@PrimozGodec what's needed to complete this?

[PrimozGodec]

Yep, it is waiting since I have currently some other projects which need to be done first. This PR has pretty much a working solution (which has some minor issues discovered while writing tests). I need to fix them and test a bit more with databases. Besides MSSQL and Postgress (which are already supported), widget now supports SQLite and MySQL. I also hope to add Oracle support soon.

If anyone is interested in using and testing the implementation be welcome to check out the pull request.