Add Support for additional Database Dialects

[Kathrin-Huber]

Description

Kitodo currently relies on MySQL/MariaDB. Clients with a fixed Database Infrastructure would highly benefit if Kitodo would natively support there Database of choice. Since hibernate already supports most widely used Database Dialects, this task would probably focus on identifying Dialects which are currently not compatible with Kitodo Production.

We propose to focus on the following Dialects:

• MS SQL Server • PostgreSQL • (Maybe) Oracle

To-do

• [ ] Decide which Databases would have the biggest positive impact if Kitodo Productions could support them
• [ ] Test compatibility with the proposed Databases
• [ ] Review the tests and identify issues which prevent the usage of the proposed Database with Kitodo Production
• [ ] Prioritize these Issues
• [ ] Update Documentation

Estimated Cost and Complexity

This is a mid-range project for about 7-9 PT.

[stweil]

Should this issue be closed? Or be tagged for development fund 2023?

[solth]

@stweil tagged for current round of development fund

[matthias-ronge]

Does this mean that we need to write all SQL migration scripts for each of these databases, or can Flyway “translate” them?

[henning-gerhardt]

Does this mean that we need to write all SQL migration scripts for each of these databases, or can Flyway “translate” them?

So far as I know flyway can translate the migration SQL scripts to specific database languages but I have no experiences in this and I think the translation depends on how good or bad the migration SQL scripts are written.