Postgres implementation

[arhimede]

Feature Request

Q	A
New Feature	yes
RFC	no
BC Break	no

Summary

Implement Postgres
and see if something need to be modified in the codebase in order to
work with both Mariadb and Postgres

[alexmerlin]

Important

@arhimede Before moving forward with this, we need to take into account the below when working with PostgreSQL.
We can discuss these at the next Dotkernel TSC meeting, but based on what I found so far, using ENUMs in the code and VARCHAR in the database seems to be the way to go if we want to support both DB engines.

Let's use the AdminRole entity for the below and focus on the $status property.
See below the current implementation:

#[ORM\Column(
    name: 'name',
    type: 'admin_role_enum',
    unique: true,
    enumType: AdminRoleEnum::class,
    options: ['default' => AdminRoleEnum::Admin]
)]
protected AdminRoleEnum $name = AdminRoleEnum::Admin;

---

Diferences between MariaDB/PostgreSQL

MariaDB

ENUMs are native types so Doctrine Migration Diff command produces this syntax:

CREATE TABLE admin_role (name ENUM('admin', 'superuser') DEFAULT 'admin' NOT NULL, ...);

PostgreSQL

ENUMs are native types but they cannot be used directly, but via a custom type that needs to be created beforehand.
So, first we need to create the custom type based on AdminRoleEnum's values:

CREATE TYPE admin_status_enum AS ENUM ('active', 'inactive');

Once the type exists, running Doctrine Migration Diff command produces this syntax:

CREATE TABLE admin_role (name admin_role_enum DEFAULT \'admin\' NOT NULL, ...);

Same rule applies for all ENUMs found in the application: first the type needs to be created, then everything works as expected.

Ordering ENUM values

The ordering of the values in an enum type is the order in which the values were listed when the type was created.

Source

Adding values

We can add a new value to the type by executing:

ALTER TYPE admin_role_enum ADD VALUE 'manager';

We can add a new value between existing values:

ALTER TYPE admin_role_enum ADD VALUE 'manager' BEFORE 'superuser';

but that is purely cosmetic, internaly the new value is appended to the existing values.

Removing values

Existing values cannot be removed from an enum type, nor can the sort ordering of such values be changed, short of dropping and re-creating the enum type.

Source

Practical example

1. Generated a migration file
2. Commented out unnecessary tables not relevant for my test (everything except AdminRole)
3. Migrated AdminRole so the table structure exists in the database
4. Modified AdminRoleEnum by adding a new value to it
5. Cleared Doctrine cache
6. Generated a new migration file
7. The new value from AdminRoleEnum does not appear in the migration file

(Not so) Fun fact

I read somewhere that PostgreSQL stores in tables the indices of enum values not the enum values themselves.
(e.g.: admin_status_enum is an ENUM with these values: 'active', 'inactive' - PostgreSQL will store int 0 for "active" and int 1 for "inactive")
This is the reason why values should be appended to a type instead of adding in-between values or ordering them.
Else, an index of 1 could point to "admin" before the modification and "superuser" afterwards, leading to chaos in the database.

The above is confirmed in their docs:

The pg_enum catalog contains entries showing the values and labels for each enum type.
The internal representation of a given enum value is actually the OID of its associated row in pg_enum.

Source

Also, Googling "Don’t use Postgres Enums" brings up lots of "no-nos".

---

Note

This way we could get rid of the below custom types:

• https://github.com/dotkernel/api/blob/6.0/src/Core/src/Admin/src/DBAL/Types/AdminRoleEnumType.php
• https://github.com/dotkernel/api/blob/6.0/src/Core/src/Admin/src/DBAL/Types/AdminStatusEnumType.php
• https://github.com/dotkernel/api/blob/6.0/src/Core/src/App/src/DBAL/Types/SuccessFailureEnumType.php
• https://github.com/dotkernel/api/blob/6.0/src/Core/src/App/src/DBAL/Types/YesNoEnumType.php
• https://github.com/dotkernel/api/blob/6.0/src/Core/src/Setting/src/DBAL/Types/SettingIdentifierEnumType.php
• https://github.com/dotkernel/api/blob/6.0/src/Core/src/User/src/DBAL/Types/UserResetPasswordStatusEnumType.php
• https://github.com/dotkernel/api/blob/6.0/src/Core/src/User/src/DBAL/Types/UserRoleEnumType.php
• https://github.com/dotkernel/api/blob/6.0/src/Core/src/User/src/DBAL/Types/UserStatusEnumType.php

They would no longer need to be registered in the ConfigProviders:

• src/Core/src/Admin/src/ConfigProvider.php
• src/Core/src/App/src/ConfigProvider.php
• src/Core/src/Setting/src/ConfigProvider.php
• src/Core/src/User/src/ConfigProvider.php

And in the future, ENUMs created in live projects would not need the above extra steps.
Also, ENUM type column definitions would get simpler by avoiding the type: 'admin_role_enum', parameter.

[arhimede]

let's remove ENUM , in order to allow an easy integration of Postgresql , without strange hacks
In order to open a door to implement some sort of GraphQL

[arhimede]

let's remove ENUM , in order to allow an easy integration of Postgresql , without strange hacks In order to open a door to implement some sort of GraphQL

or let's think more about that