Trino database

[PeterSchmidt23]

Is it possible to include the Trino JDBC driver?
More infos about Trino .
The problem is, that the Trino driver requires a forward only resultset.

[prrvchr]

Hi PeterSchmidt23,

Is it possible to include the Trino JDBC driver?

Why not, where can we find a jar archive?

The problem is, that the Trino driver requires a forward only resultset.

I am working on the new version which uses javax.sql.rowset.CachedRowSet and therefore allows me to overcome such limitations

[PeterSchmidt23]

Thank you.
you can find the current version in Maven.

[prrvchr]

Ok and how to set up a test environment quickly?

[PeterSchmidt23]

Do you have Docker installed?
It is the easiest way. You can use this example with MySQL.

[prrvchr]

Do you have Docker installed?

yes, I already use it.

You can use this example with MySQL.

Is it possible to do the same thing with MariaDB, because I already have MariaDB installed but not MySQL?

[PeterSchmidt23]

You can do it with you current MariaDB instance.
You can delete the MySQL service from the example docker-compose and change the connection string from the MySQL catalog to your MariaDB. BTW. you can have as many catalogs as you like to almost any database. Trino is amazing.

Trino MariaDB
Or you can just add a new catalog. E.g. mariadb.properties
`
connector.name=mariadb

connection-url=jdbc:mariadb://example.net:3306

connection-user=root

connection-password=secret
`

[prrvchr]

Thanks for this information, I will try to set this up.

I didn't know about Trino but effectively it looks amazing...

[prrvchr]

The problem is, that the Trino driver requires a forward only resultset.

I will be able to override this limitation however I cannot know if the java.sql.ResultSet support update and insert.
And not knowing either Docker or Trino well, I can't configure both sets. Where do we put the etc folder with the config?

[PeterSchmidt23]

The problem is, that the Trino driver requires a forward only resultset.

I will be able to override this limitation however I cannot know if the java.sql.ResultSet support update and insert. And not knowing either Docker or Trino well, I can't configure both sets. Where do we put the etc folder with the config?

Overriding the ResultSet limitations sounds good.
Do you need more informations how to setup Trino?

[prrvchr]

Overriding the ResultSet limitations sounds good.

I can easily make an TYPE_FORWARD_ONLY ResultSet as an TYPE_SCROLL_SENSITIVE at the cost of reloading the ResultSet when necessary.
On the other hand, does Trino offer ResultSet allowing updates and insertions?

Do you need more informations how to setup Trino?

Yes, I would like to find an How To on installing Trino and MariaDB with docker

[PeterSchmidt23]

Overriding the ResultSet limitations sounds good.

I can easily make an TYPE_FORWARD_ONLY ResultSet as an TYPE_SCROLL_SENSITIVE at the cost of reloading the ResultSet when necessary. On the other hand, does Trino offer ResultSet allowing updates and insertions?

Yes it does.
Do you need more informations how to setup Trino?

Yes, I would like to find an How To on installing Trino and MariaDB with docker

I will make you a complete setup, which you can clone and work on.

[prrvchr]

I will make you a complete setup, which you can clone and work on.

Thanks, I've made a little progress. I manage to connect to a Trino instance that I launch this way.

docker run --name trino -d -p 8080:8080 trinodb/trino

but if I try to connect to an instance with a MariaDB catalog I can't.

docker run --name trino -d -p 8080:8080 --volume /home/prrvchr/github/trino-mariadb.docker/etc:/etc/trino trinodb/trino

or

docker run --name trino -d -p 8080:8080 --volume /home/prrvchr/github/trino-mariadb.docker/etc/catalog:/etc/trino/catalog trinodb/trino

[PeterSchmidt23]

Sorry, for my latge answer.

I made you a repository to get started.
Just clone or download , adjust mariadb-ext.properties to match your already running MariaDB. You can also connect to the dockerized MariaDB.

After that, just run docker compose up or docker compose up -d to put things in the background and stop it with docker compose down.

(Untested)
docker run --name trino -d -p 8080:8080 --volume /home/prrvchr/github/trino-mariadb.docker/etc/catalog:/etc/trino/catalog trinodb/trino and put your mariadb.properties inside your local catalog folder.

[prrvchr]

I made you a repository to get started.

Thank you very much, this will save me a lot of time.
I'll put it back quickly and keep you informed.

[prrvchr]

Thanks for the docker/Trino/MariaDB configuration, it works perfectly...

I have just published the latest version 1.3.3 of jdbcDriverOOo which integrates the Trino driver version 448.

On the other hand, for the moment not much is working correctly, and it will take a little time to get everything working.

I have already opened two issues which pose a problem when creating tables and as I prefer to have the least driver specific code, I will wait to see if these first two problems can be fixed by Trino.

One of the problems which prevents the creation of tables can be work around by a change of parameter in the LibreOffice configuration. Just go to the menu: Tools -> Options... -> LibreOffice -> Advanced -> OpenExpertConfiguration and search for SupportTransaction and set it to false.

Once this is done it is possible to create tables but an error message will state that the table could not be created. You must ignore this message and simply reload the tables to see that it has been created.

Another problem seems to be that primary keys are not recognized by Base.

[prrvchr]

The second issue can be worked around by only creating lowercase table names...

[PeterSchmidt23]

Perfect. I will give it a try 👍🏻

[prrvchr]

Well I finally managed to publish version 1.3.3.

Trino poses problems when editing tables in Bases and this will only work when the issue #22306 is resolved.

I hope it will be quick...

[PeterSchmidt23]

I am currently evaluating the extension. Do you know if it's possible to change the generated SQL syntax.
Inner joins working fine. But left joins dont.
This is the generated syntax.

SELECT "gnr_20211"."q_id" FROM { oj "hive"."gnr_text"."gnr_text" AS "gnr_text" LEFT OUTER JOIN "hive"."gnr"."gnr_20211" AS "gnr_20211" ON "gnr_text"."q_id" = "gnr_20211"."q_id" }

Problematic is the { oj syntax.

[prrvchr]

I am currently evaluating the extension. Do you know if it's possible to change the generated SQL syntax.
Inner joins working fine. But left joins dont.

How do you get such a join? Is it LibreOffice Base that generates such a request?

I just re-updated version 1.4.0. This time we are able to edit tables in Base with Trino...

This is not optimal because I am obliged (see issue #22408) to search for the first column that does not contain a duplicate and consider that it is the primary key. Therefore, it is preferable to put the primary key in the first column to avoid any confusion.

[PeterSchmidt23]

I am currently evaluating the extension. Do you know if it's possible to change the generated SQL syntax.
Inner joins working fine. But left joins dont.

How do you get such a join? Is it LibreOffice Base that generates such a request?

I just re-updated version 1.4.0. This time we are able to edit tables in Base with Trino...

This is not optimal because I am obliged (see issue #22408) to search for the first column that does not contain a duplicate and consider that it is the primary key. Therefore, it is preferable to put the primary key in the first column to avoid any confusion.

Thank you for 1.4.0!
I used the build in Query Builder. Does the build in Query builder probably use different syntax for different type of databases?

[prrvchr]

I used the build in Query Builder. Does the build in Query builder probably use different syntax for different type of databases?

If it's Base then there must be settings for this. I need to look in the LibreOffice code...

Maybe you know what is used by Trino users to administer a Trino server (creation of tables, data entry, etc.).

[PeterSchmidt23]

Thanks for looking. Trino uses standard ANSI SQL. Perhaps it is possible to change to MySQL/MariaDB syntax, if possible.

[PeterSchmidt23]

I used the build in Query Builder. Does the build in Query builder probably use different syntax for different type of databases?

If it's Base then there must be settings for this. I need to look in the LibreOffice code...

Maybe you know what is used by Trino users to administer a Trino server (creation of tables, data entry, etc.).

I found the settings in LibreOffice.

But you only get this dialog, if you create a new database with the default JDBC wizard of LibreOffice. So there must be a difference, when setting up a new database.

[prrvchr]

But you only get this dialog, if you create a new database with the default JDBC wizard of LibreOffice.

Ok, I need to enable this setting for the jdbcDriverOOo driver because at the moment it is not supported. I'm looking at what's going on. Thank you for the info...

[PeterSchmidt23]

Thank you.

[prrvchr]

Well all these settings are now available in jdbcDriverOOo version 1.4.1.

You must recreate the odb files to see them appear. I have not tested the creation of queries.