SQL Provider Issues e.g. for PostgreSQL

[idseefeld]

Which Umbraco version are you using?

dev/17

Bug summary

I am working on a custom SQL provider for PostgreSQL and found several issues in core package which I could not resolve in a package:

1. There are a few naming issue because PostgreSQL is case sensitive. These should be easy to fix and are not critical and would NOT be breaking changes (I think).
2. There are also hard code SQL statements which are not using the SqlSyntax provider. These should be easy to fix and are not critical and would NOT be breaking changes (I think) too.
3. After fixing these my all usual tables and data are created in the database.

But the tables for the OpenId implementation build with Entity Framework are missing. Fixing these would require more complex fixes in core projects. I have found so far:

1. The required migrations can not be created or at least I could not figure out how. This is also true for the two core projects Umbraco.Cms.Persistence.EFCore.SqlServer and Umbraco.Cms.Persistence.EFCore.Sqlite.
2. Another issue is caused by static extensions method public static void UseDatabaseProvider(this DbContextOptionsBuilder ... which is not extendable in a package without a custom implementation of DbContextOptionsBuilder. But this might by possible...

Specifics

I am working in branches https://github.com/idseefeld/Umbraco-CMS/tree/v17/EFCore-PostgreSQL-Package and https://github.com/idseefeld/Umbraco-CMS/tree/v17/EFCore-PostgreSQL-Package-Core-Changes.
Branch https://github.com/idseefeld/Umbraco-CMS/tree/v17/EFCore-PostgreSQL-Package-Core-Changes is based on the first and contains only changes in core projects.

Steps to reproduce

PostgreSQL Provider for Umbraco

Install PostggreSQL Database

1. Download and install PostgreSQL from the official website or especially for Windwos.
2. Create a new database for Umbraco using the PostgreSQL command line or a GUI tool like pgAdmin. Follow my tutorial video.
3. Start debugging this solution [F5]

Expected result / actual result

Obviously new persistence providers like PostgreSQL should be possible via packages.
Currently this is not possible because of an inconsistent persistence layer😥

[github-actions]

Hi there @idseefeld!

Firstly, a big thank you for raising this issue. Every piece of feedback we receive helps us to make Umbraco better.

We really appreciate your patience while we wait for our team to have a look at this but we wanted to let you know that we see this and share with you the plan for what comes next.

• We'll assess whether this issue relates to something that has already been fixed in a later version of the release that it has been raised for.
• If it's a bug, is it related to a release that we are actively supporting or is it related to a release that's in the end-of-life or security-only phase?
• We'll replicate the issue to ensure that the problem is as described.
• We'll decide whether the behavior is an issue or if the behavior is intended.

We wish we could work with everyone directly and assess your issue immediately but we're in the fortunate position of having lots of contributions to work with and only a few humans who are able to do it. We are making progress though and in the meantime, we will keep you in the loop and let you know when we have any questions.

Thanks, from your friendly Umbraco GitHub bot 🤖 🙂

[idseefeld]

I have created another video which shows the installation issues. The video shows a different behaviour between manual and unattended installs. Which is probably another issue.

[idseefeld]

I decided that first of all the sql syntax issues have to be addressed and opened #19897.