giovedì 30 giugno 2011

Gestire le unita' di misura in PostgreSQL

Uno dei problemi di molti, se non tutti, sistemi gestionali e' quello di gestire diverse unita' di misura e le conversioni fra di esse. E' possibile creare nel database una serie di funzioni per la gestione delle unita' di misura e della loro conversione.
Prima di tutto occorre fare alcune assunzioni: (1) le unita' di misura sono legate ad un contesto (o dominio), che identifica a quale settore merciologico (o addirittura per quale articolo) esse hanno validita'. Se e' vero infatti che kg e km hanno sempre quel significato, scatole, pallet, blister, ecc. hanno configurazioni e parametri di conversione differenti a seconda della categoria merceologica e del loro dominio di applicazione.
Un'altra assunzione e' che la conversione fra le unita' di misura sia sempre lineare, ossia basata da un fattore moltiplicativo (o diviso) e da nessuno spiazzamento. Infine si suppone che per ogni dominio venga definita una unita' di misura "root" (principale) alla quale si fara' riferimento per le conversioni; tale unita' puo' cambiare nel tempo, ma ogni categoria ne deve avere una specifica. Tipicamente l'unita' root e' quella di produzione, mentre le altre unita' sono quelle di acquisto/vendita.

Si passa alla definizione di una tabella che conterra' i dati di tutte le unita' di misura definite:

CREATE TABLE units
(
  unitspk serial NOT NULL,
  unit text NOT NULL,
  domain text NOT NULL,
  scaling_factor real NOT NULL DEFAULT 1,
  root_for_domain boolean NOT NULL DEFAULT false,
  references_to_root integer,
  CONSTRAINT units_surrogate_primarykey PRIMARY KEY (unitspk),
  CONSTRAINT unit_to_unit FOREIGN KEY (references_to_root)
      REFERENCES units (unitspk) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION,
  CONSTRAINT unique_units_domain_check UNIQUE (unit, domain)
)

In sostanza per ogni unita' di misura si defnisce il suo codice identificativo (es kg) e il suo dominio, nonché il fattore di conversione verso una root del dominio.
L'idea è quella di poter definire delle unita' di misura mediante delle funzioni come segue:

-- creazione di una unita' di base
select f_create_root_unit( 'pesi', 'g', 0);
-- creazione di altre unita'
select f_add_unit( 'pesi', 'kg', 1000, 'g' );
select f_add_unit( 'pesi', 'hg', 0.1, 'kg' );


Quello che si vede è la creazione dell'unita' base per i pesi: il grammo. In seguito si creano delle unita' facendo riferimento ad altre unita', quindi ad esempio il 'kg' corrisponde a 1000 grammi, mentre il 'hg' corrisponde a 0.1 'kg'.
Infine si possono definire delle funzioni di conversione come segue:

-- conversione verso root
select f_convert_units( 'pesi', 'kg', 30, 'g' );
       -- fornisce in uscita 30000 (30 kg = 30000 g)

-- conversione da root
select f_convert_units( 'pesi', 'g', 5, 'hg' );
       -- fornisce in uscita 0.05 (5 g = 0.05 hg)

-- conversioni fra due unita' non root
select f_convert_units( 'pesi', 'hg', 99, 'kg' );
       -- fornisce in uscita 9.9 (99 hg = 9.9 kg)
select f_convert_units( 'pesi', 'kg',  88, 'hg' );
       -- fornisce in uscita 880 (88 kg = 880 hg)

che molto semplicemente effettuano la conversione di una data quantita', ad esempio 30 kg in grammi.
Il "trucco" dietro alle conversioni e' dato dal contenuto della tabella che presenta i fattori di moltiplicazione/divisione e i riferimenti increciati fra una unita' e la sua root:

select unit, domain, scaling_factor from units;
 unit | domain | scaling_factor
------+--------+----------------
 g    | pesi   |              1
 kg   | pesi   |           1000
 hg   | pesi   |            100
(3 rows)

Come scritto sopra l'unità scelta come root potrebbe cambiare nel tempo, per questo la funzione f_create_root_unit accetta tre parametri: il terzo e' appunto un fattore di aggiustamento dalla root corrente a quella nuova.
Se si vuole modificare la root corrente (grammi) in milligrammi, considerando che un milligrammo corrisponde ad un millesimo di grammi, si avrà:

-- cambio la unita' root da grammi a milligrammi, considerando
-- che un grammo = 1000 milligrammi
select * from f_create_root_unit( 'pesi', 'mg', 1000 );


e il sistema aggiusterà tutti i fattori di conversione:


select unit, domain, scaling_factor from units;
 unit | domain | scaling_factor
------+--------+----------------
 mg   | pesi   |              1
 g    | pesi   |           1000
 kg   | pesi   |          1e+06
 hg   | pesi   |         100000



Come si nota la root ha sempre un fattore di conversione 1 (oppure 0), mentre le altre unita' sono state aggiustate di conseguenza.
Le funzioni di conversione operano nel seguente modo:
- se l'unita' di partenza e' la radice allora si divide la quantia' per lo scaling factor dell'unita' destinazione;
- se l'unita' di destinazione e' la radice allora si moltiplica per lo scaling factor dell'unita' di partenza la quantia';
- se nessuna delle due e' la radice allora si rapportano i due scaling factor per trovare quello definitivo

Analogamente la creazione di una nuova root va prima alla ricerca di root pre-esistenti e aggiusta tutti gli scaling factor di conseguenza.
Di seguito sono presentate le funzioni plpgsql.
Si consideri la funzione di creazione di una unita' radice per un dato dominio:


CREATE OR REPLACE FUNCTION f_create_root_unit(working_domain text, new_unit text, new_scaling real)
  RETURNS boolean AS
$BODY$
DECLARE
    /* dichiarazione variabili */
    old_domain_root_unit        integer;
    old_domain_root_unit_text    text;
    current_root_pk            integer;
    current_unit_pk            integer;
    current_root_scaling_factor    real;
BEGIN

    -- controllo
    /* se si tenta di inserire la root corrente non si fa nulla */
    SELECT unitspk, unit
    INTO   old_domain_root_unit, old_domain_root_unit_text
    FROM   units
    WHERE  root_for_domain = true
    AND    domain          = working_domain;

    IF old_domain_root_unit IS NOT NULL AND old_domain_root_unit_text = new_unit
    THEN
        RAISE INFO 'Impossibile aggiungere questa um come root, esiste!';
        return false;
    END IF;

    -- controllo
    /* potrebbe esserci il caso di agigunta unita' esistente come root */
    SELECT unitspk, scaling_factor
    INTO   current_root_pk, current_root_scaling_factor
    FROM   units
    WHERE  domain          = working_domain
    AND    root_for_domain = false
    AND    unit            = new_unit;

    IF current_root_pk IS NOT NULL
    THEN
        UPDATE units
        SET    root_for_domain    = false,
               scaling_factor     = scaling_factor / current_root_scaling_factor,
               references_to_root = current_root_pk
        WHERE  unitspk <> current_root_pk
        AND    domain = working_domain;

        UPDATE units
        SET    root_for_domain    = true,
               references_to_root = NULL
        WHERE  unitspk = current_root_pk;

        RETURN true;

           
    END IF;
   
   

    /* se esiste il dominio devo trovarne la root */
    SELECT unitspk, unit
    INTO   old_domain_root_unit, old_domain_root_unit_text
    FROM   units
    WHERE  root_for_domain = true
    AND    domain = working_domain;

    RAISE INFO 'Root (precedente) del dominio % = % %', working_domain, old_domain_root_unit, old_domain_root_unit_text ;


   
    /* inserimento dei valori nella tupla */
    INSERT INTO units( domain, unit, root_for_domain)
    VALUES( working_domain, new_unit, true);

    -- ottengo la nuova root
    SELECT unitspk
    INTO   current_root_pk
    FROM   units
    WHERE  domain = working_domain
    AND    unit   = new_unit;

    FOR  current_unit_pk IN     SELECT unitspk
                    FROM units
                    WHERE domain          = working_domain
                    AND   unitspk         <> current_root_pk
                    LOOP

        RAISE INFO 'Aggiustamento entry % alla nuova root %', current_unit_pk, current_root_pk;
        UPDATE units
        SET    scaling_factor     = scaling_factor * new_scaling,
               references_to_root = current_root_pk,
               root_for_domain    = false
        WHERE  domain             = working_domain
        AND    unitspk            = current_unit_pk;
                   
    END LOOP;

    -- aggiusto tutti gli scaling factor per puntare alla nuova root
   

    RAISE INFO 'Aggiustamento nuova root % %', current_root_pk, new_scaling;

    -- tutto ok
    RETURN true;

END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;
ALTER FUNCTION f_create_root_unit(text, text, real) OWNER TO postgres;

Di seguito la piu' semplice funzione di aggiunta di una unita' di misura:


CREATE OR REPLACE FUNCTION f_add_unit(working_domain text, new_unit text, scaling real, references_to_unit text)
  RETURNS boolean AS
$BODY$
DECLARE
    /* dichiarazione variabili */
    current_domain_root_unit    integer;
    current_domain_root_unit_text   text;
    current_domain            text;
    converting_function_name        text;
    function_query                  text;
BEGIN

    /* 1) trovo la root del dominio */
    SELECT unitspk, unit
    INTO   current_domain_root_unit, current_domain_root_unit_text
    FROM   units
    WHERE  root_for_domain = true
    AND    domain = working_domain;


    /* se non mi sto riferendo alla root devo calcolare
       il valore di scaling */
    IF current_domain_root_unit_text <> references_to_unit
    THEN
        SELECT scaling_factor * scaling
        INTO   scaling
        FROM   units
        WHERE  domain = working_domain
        AND    unit   = references_to_unit;
    END IF;
   

    RAISE INFO 'Root del dominio % = % %', working_domain, current_domain_root_unit, current_domain_root_unit_text ;


    /* 2) inserisco valori nella tupla */
    INSERT INTO units( unit, domain, scaling_factor, references_to_root )
    VALUES( new_unit, working_domain, scaling, current_domain_root_unit );

    -- tutto ok
    RETURN true;

END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;
ALTER FUNCTION f_add_unit(text, text, real, text) OWNER TO postgres;


Infine la funzione di conversione fra due unita' di misura:

CREATE OR REPLACE FUNCTION f_convert_units(working_domain text, from_unit text, quantity real, to_unit text)
  RETURNS real AS
$BODY$
DECLARE
    /* dichiarazione variabili */
    from_unit_record    units%rowtype;
    to_unit_record        units%rowtype;
    linear_multiplier    real;
BEGIN

    /* trovo le chiavi delle unita' di partenza e arrivo */
    SELECT *
    INTO   from_unit_record
    FROM   units
    WHERE  unit = from_unit
    AND    domain = working_domain;

    SELECT *
    INTO   to_unit_record
    FROM   units
    WHERE  unit = to_unit
    AND    domain = working_domain;




    -- se l'unita' di destinazion e' root il calcolo lineare e' corretto, altrimenti
    -- lo inverto
    IF to_unit_record.root_for_domain = false AND from_unit_record.root_for_domain = true
    THEN
        SELECT 1 / (to_unit_record.scaling_factor)
        INTO   linear_multiplier;
        RAISE INFO 'Conversione verso root %', linear_multiplier;

   
    ELSIF to_unit_record.root_for_domain = true AND from_unit_record.root_for_domain = false
    THEN
        SELECT 1 * (from_unit_record.scaling_factor )
        INTO   linear_multiplier;
        RAISE INFO 'Conversione da root %', linear_multiplier;

    ELSIF to_unit_record.root_for_domain = false AND from_unit_record.root_for_domain = false
    THEN
        -- conversione fra due unita' intermedie, si puo' fare agevolmente se si riferiscono
        -- alla stessa root
        IF from_unit_record.references_to_root = to_unit_record.references_to_root
        THEN
            SELECT ( (from_unit_record.scaling_factor ) / (to_unit_record.scaling_factor ) )
            INTO   linear_multiplier;
        END IF;
   
    END IF;

    -- tutto fatto
    RETURN quantity  * linear_multiplier;

END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;
ALTER FUNCTION f_convert_units(text, text, real, text) OWNER TO postgres;

Ovviamente le funzioni proposte qui sono a puro scopo didattico/dimostrativo, ulteriori controlli di coerenza dovrebbero essere implementati.
Per le trasformazioni non lineari una possibile soluzione e' quella di creare al volo delle funzioni di conversione specifiche e di usare tali funzioni per ogni conversione.

Nessun commento: