Normalisation

Comment dénormaliser les table générique

table de valeurs

Les tables dénormalisées doivent être faites par type de données (colonnes différentes). Dans un premier temps on type les valeurs (date timestamp, date formattée, int float, boolean, text. Les règles de formattage sont dans le fichier de configuration. On ajoute l'uuid de la ligne.

Pour les référentiels on peut garder dans un premier temps l'uuid, le display_fr le display en

On peut rajouter des index sur les timestamps et les uuid, voire une clef étrangère si on a aussi construit les tables des référentiels.

Dans le cas des patterns, on rajoutera le nom de la colonne (discriminant) et pour les noms de colonnes un mixte entre le nom du composant display et du sous composant.

Requête

Il existe des requêtes simplifiées pour remplir une telle table

drop schema if exists monsore_dn cascade;
create schema monsore_dn;
-- génération des tables
create table monsore_dn.pem as (with reference as
                                         (select id,
                                                 hierarchicalkey,
                                                 COALESCE(
                                                         NULLIF(refvalues ->> '__display_fr', ''),
                                                         refvalues ->> '__display_default'
	)                                                                     display_fr,
                                                 COALESCE(
                                                         NULLIF(refvalues ->> '__display_en', ''),
                                                         NULLIF(refvalues ->> '__display_fr', ''),
                                                         refvalues ->> '__display_default'
	) display_en
                                          from monsore.referencevalue)
                                select referencevalue.id,
                                       referencevalue.naturalkey,
                                       referencevalue.hierarchicalkey,
                                       (val.date::composite_date)::timestamp ts_date, (val.date::composite_date) ::text date,
refs.site site,
site.hierarchicalkey site_hk,
site.display_fr site_fr,
site.display_en site_en,
refs.chemin chemin,
chemin.display_fr chemin_fr,
chemin.display_en chemin_en,
refs.projet projet,
projet.hierarchicalkey projet_hk,
projet.display_fr projet_fr,
projet.display_en projet_en,
refs.color_unit color_unit,
color_unit.display_fr color_unit_fr,
color_unit.display_en color_unit_en,
refs.individusNumber_unit individusNumber_unit,
individusNumber_unit.display_fr individusNumber_unit_fr,
individusNumber_unit.display_en individusNumber_unit_en,
refs.espece espece,
espece.display_fr espece_fr,
espece.display_en espece_en,
refs.color_value color_value,
color_value.display_fr color_value_fr,
color_value.display_en color_value_en,
val.plateforme,
val.individusNumbervalue
                                FROM monsore.referencevalue, JSON_TABLE (refvalues, '$' COLUMNS (
                                    date text PATH '$.date', plateforme text PATH '$.plateforme', individusNumbervalue float PATH '$.individusNumbervalue'
                                    )
                                    ) AS val, JSON_TABLE (refslinkedto, '$' COLUMNS (
                                    site uuid PATH '$.sites.site.*.uuids[0]', chemin uuid PATH '$.sites.chemin.*.uuids[0]', projet uuid PATH '$.projet.projet.*.uuids[0]', color_unit uuid PATH '$.unites.color_unit.*.uuids[0]', individusNumber_unit uuid PATH '$.unites.individusNumber_unit.*.uuids[0]', espece uuid PATH '$.especes.espece.*.uuids[0]', color_value uuid PATH '$.valeurs_qualitatives.color_value.*.uuids[0]'
                                    )
                                    ) AS refs
                                    left join reference site
                                on site.id=refs.site
                                    left join reference chemin on chemin.id=refs.chemin
                                    left join reference projet on projet.id=refs.projet
                                    left join reference color_unit on color_unit.id=refs.color_unit
                                    left join reference individusNumber_unit on individusNumber_unit.id=refs.individusNumber_unit
                                    left join reference espece on espece.id=refs.espece
                                    left join reference color_value on color_value.id=refs.color_value

                                where referencetype = 'pem'
);
ALTER TABLE monsore_dn.pem
    ALTER COLUMN id SET NOT NULL;
ALTER TABLE monsore_dn.pem
    ADD CONSTRAINT pk PRIMARY KEY (id);

CREATE INDEX IF NOT EXISTS date_idx
    ON monsore_dn.pem USING brin
    (ts_date timestamp_minmax_multi_ops)
    WITH (pages_per_range=128, autosummarize=False)
    TABLESPACE pg_default;

CREATE INDEX IF NOT EXISTS site_idx
    ON monsore_dn.pem USING btree(site ASC NULLS LAST);

CREATE INDEX IF NOT EXISTS site_hk_idx
    ON monsore_dn.pem USING btree(site_hk ASC NULLS LAST);

CREATE INDEX IF NOT EXISTS projet_idx
    ON monsore_dn.pem USING btree(projet ASC NULLS LAST);

CREATE INDEX IF NOT EXISTS projet_hk_idx
    ON monsore_dn.pem USING btree(projet ASC NULLS LAST);

-- ajout des droits sur la table
GRANT SELECT  ON monsore_dn.pem  TO public ;
ALTER TABLE  monsore_dn.pem  ENABLE ROW LEVEL SECURITY;

DO $$
DECLARE
rec RECORD;
    sql_policy TEXT;
BEGIN
FOR rec IN
SELECT
    id::text AS id,
    application::text || '_mgt_' || SUBSTRING(id::text FROM 1 FOR 8) AS role,
    (authorizations #>> '{pem, timescope}')::tsrange AS ts,
    (authorizations #>> '{pem, authorizationscope,sites,0}') AS site,
    (authorizations #>> '{pem, authorizationscope,projet,0}') AS projet
FROM monsore.oresiauthorization
WHERE authorizations ? 'pem'
    LOOP
        sql_policy := format($fmt$
            CREATE POLICY "pem_%s"
            ON monsore_dn.pem
            AS PERMISSIVE
            TO "%s"
            USING (
                ts_date <@ '%s'::tsrange
                AND site_hk <@ '%s'::ltree
                AND projet_hk <@ '%s'::ltree
            )
        $fmt$, rec.id, rec.role, rec.ts, rec.site, rec.projet);

EXECUTE sql_policy;
END LOOP;
END $$;

A noter que s'il existe des reférentiels many la requête sera plus compliquée puisqu'elle nécessitera un group by.