Use separate database backend at its full potential and performance question

[Sylvain303]

Is your feature request related to a problem? Please describe.

I'm quite new using Tables it as some very interesting features it borrows from database management.

So why not using the database back-end at it's full potential and creating real database and tables in the back-end server?

I mean create a separate database for Tables, and manage real SQL tables mapping Tables. Not storing JSON blob in the back-end.

So you could perform real database feature that is already offered by long-time running SQL back-end. Not reinventing the wheel once again. 😉

Describe the solution you'd like

Creates a new Database in the back-end, example: nxt_tables Move oc_tables_rows content as well defined SQL table in the new database:

Original schema (for storting data), we suppose a table name my_tablename

CREATE TABLE public.oc_tables_rows (
    id integer NOT NULL,
    table_id integer NOT NULL,
    created_by character varying(200) NOT NULL,
    created_at timestamp(0) without time zone NOT NULL,
    last_edit_by character varying(200) NOT NULL,
    last_edit_at timestamp(0) without time zone NOT NULL,
    -- data blob should be replaced by real column ⬇️
    data json
);

could become:

 -- with a choice column for example:

CREATE TYPE choice_my_tablename_mood AS ENUM ('sad', 'ok', 'happy');

CREATE TABLE my_tablename (
    id integer NOT NULL,
    table_id integer NOT NULL,
    created_by character varying(200) NOT NULL,
    created_at timestamp(0) without time zone NOT NULL,
    last_edit_by character varying(200) NOT NULL,
    last_edit_at timestamp(0) without time zone NOT NULL,
    -- real column ⬇️
    col_firstname text,
    col_lastname text,
    col_email text,
    col_current_mood choice_my_tablename_mood
);

etc.

Describe alternatives you've considered

Really, using JSON and partial column set?

nextcloud_database=> SELECT id, data->1->>'value' AS Nom, data->0->>'value' as prénom, data->3->>'value' as status FROM oc_tables_rows WHERE table_id = 4;
  id  |    nom     |  prénom   | status 
------+------------+-----------+--------
 1046 | Boudichon  | Julien    | 
 2031 | Dosgor     | Igor      | 
 2032 | Leterrible | Ivan      | 0
 1524 | Rossignol  | Phil      | 
 1525 | Beauvau    | Radegonde | 
 1821 | Aubert     | Marie     | 
 1889 | Aliker     | Débora    | 
 2019 | Dutest     | Ivan      | 
(8 rows)

Follows 2 rows, with not the same number of columns. 🤔 😨 in JSON ⬇️

nextcloud_database=> SELECT id, data  FROM oc_tables_rows WHERE id = 2032;
  id  |                                                                      data                                                                       
------+-------------------------------------------------------------------------------------------------------------------------------------------------
 2032 | [{"columnId":23,"value":"Ivan"},{"columnId":24,"value":"Leterrible"},{"columnId":25,"value":"ivanoe@terrible.email"},{"columnId":38,"value":0}]
(1 row)

nextcloud_database=> SELECT id, data  FROM oc_tables_rows WHERE id = 2019;
  id  |                                                      data                                                      
------+----------------------------------------------------------------------------------------------------------------
 2019 | [{"columnId":23,"value":"Ivan"},{"columnId":24,"value":"Dutest"},{"columnId":25,"value":"ivan@invalid.email"}]
(1 row)

Additional context

No response