search

trinodb / trino

Official repository of Trino, the distributed SQL query engine for big data, formerly known as PrestoSQL (https://trino.io)
https://trino.io
Apache License 2.0
10.24k stars 2.95k forks source link

Improve JDBC compatibility of Trino jdbc driver #22846

Open prrvchr opened 2 months ago

prrvchr commented 2 months ago

Hi all,

I manage to use the Trino jdbc driver in LibreOffice Base with the help of the jdbcDriverOOo extension.

For now Base is able to display data from Base queries which are queries recorded in the odb file allowing the connection to Trino (ie: the Trino jdbc driver). These queries can relate to several trino catalogs and therefore several underlying connectors.

From a query, it is possible to write Base forms that allow the display of data from these queries. If the queries are modifiable (i.e. the ResultSet is updatable) then the forms will also allow data entry. These forms are quite simple to design and use.

For now the jdbcDriverOOo driver is able to transform the ResultSets coming from the Trino jdbc driver into ResultSets that can be updated if it only concerns one table (ie: a single connector) and the first column not containing a duplicate will be considered as the primary key of this query.

This mode of operation is not optimal and this is precisely what I am trying to improve.

Unfortunately I do not know Trino well enough to make the right decisions so I am counting on your participation to get there.

And the ultimate goal would be to offer a LibreOffice extension embedding a Trino coordinator with the possibility of adding JDBC workers (oracle, SQLServer, mariaDB, mySQL, HsqlDB...) in order to offer a very easy and small business-oriented deployment of Trino.

huw0 commented 2 months ago

Does jdbcDriverOOo use getPrimaryKeys? Is it this that is not working as expected?

prrvchr commented 2 months ago

Does jdbcDriverOOo use getPrimaryKeys?

To render a forward only ResultSet updatable jdbcDriverOOo needs getPrimayKeys(). Currently, with Trino ResultSet (ie: forward only) to render a ResultSet updatable jdbcDriverOOo searches for the first column of the ResultSet that does not contain duplicates, this implementation cannot be satisfactory.

Is it this that is not working as expected?

This only works correctly if you have taken care to put the primary key in the first column of the ResultSet. And it will never work correctly if the ResultSet covers several tables. This is the precise point that I would like to improve in order to benefit from the advantages offered by Trino in LibreOffice Base.