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.
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.
