Da Oracle a Postgresql.

Oracle è un ottimo DB si installa e ci si dimentica che c’è (beh magari una controllatina allo spazio disco ogni tanto bisogna darla). Unica controindicazione… costa! E mica poco!!!

Così per venire in aiuto alle piccole realtà, nell’azienda dove lavoro, si è deciso di fare un porting dell’applicativo su un altra base dati… Doveva essere un database il più possibile stabile e performante e magari simile ad Oracle e la scelta è ricaduta su Postgresql.
La migrazione non è stata indolore si è dovuto modificare (o meglio ringiovanire) pesantemente le query per renderle il più possibile compatibili con entrambe le base dati in maniera da avere query diverse solo in pochi casi (gestite poi con delle direttive di compilazione).
Questo è un eleco delle problematiche riscontrate e delle soluzioni adottate. Ovviamente nessuna regola è scritta nella pietra e probabilmente ci sono soluzioni migliori, ma queste hanno funzionato.

Fase 1 – Rendere standard l’SQL.

L’applicativo originale nasceva su piattaforma Oracle 8, nel 1999. Per cui si usava l’sql di Oracle che allora non rispettava lo standard ANSI. Il primo lavorone è stato rendere ANSI le query.

A) Outer Join
In Oracle un outer join può essere indicata usando la sintassi (+) nella sezione where della query. Questo implica che di seguito al campo della tabella in outer
join venga indicato il simbolo (+). Ad esempio:

Select a.campo1, b.campo2
from tabella1 a, tabella2 b
where b.campo3(+) = a.tabella2_campo3;

Usando l’SQL standard invece si scriverà:

Select a.campo1, b.campo2
from tabella1 a left join tabella2 b on b.campo3 = a.tabella2_campo3;

B) NVL
La funzione NVL in Oracle viene usata per sostituire un valore NULL in un campo con un altro valore. La sua sintassi:

NVL( string1, replace_with )

Ad esempio se non si vuole far vedere vuoto un campo si può decidere di sostituirlo con il messaggio “non selezionato” e allora viene comodo usare NVL. In sql
standard invece esiste la funzione COALESCE che restituisce la prima espressione non-NULL presente tra i suoi argomenti… per cui va benissimo per sostituire la
NVL che di parametri ne aveva solo 2.

C) DECODE
Avete bisogno di inserire un IF nel vostro SQL? In Oracle avreste usato una decode. La sua sintassi:

decode( expression , search , result [, search , result]... [, default] )

vi permette di avere un risulatato particolare a seconda del valore dell’espressione. Il limite della decode è che gestisce solo i confronti per ugualianza. Con l’sql standard si potrà invece usare il case, che ha la seguente sintassi:

SELECT CASE ("nome_di_colonna")
 WHEN "condizionale_1" THEN "risultato_1"
 WHEN "condizionale_2" THEN "risultato_2"
 ...
 [ELSE "risultato_N"]
 END
FROM "nome_della_tabella"

D) Cast dei tipi.
Si possono avere dei problemi di cast tra i tipi di dato. Oracle e Postgresql gestiscono i tipi in maniera diversa. Ad esempio concatenare dei tipi varchar in Oracle genera un tipo varchar, in postgres il capo risultante è un text (tipo di dato CLOB). Per cui una sql si questo tipo:

Select rtrim(COGNOME) || '' '' || rtrim(NOME) from tabella1

In Oracle restituirà un varchar2 in Postgresql un text per renderli compatibili basterà fare un cast per entrambi con il codice seguente:

Select cast(rtrim(COGNOME) || '' '' || rtrim(NOME) as varchar(100)) from tabella1

Fase 2 – In altri casi si è dovuto ricorrere a degli stratagemmi perché non esisteva una soluzione SQL comune ad entrambe le basi dati. In questi casi rientrano:

A) Sequence.

In Oracle per avere il valore successivo di una sequence (Usata magari per dare un volere univoco in una tabella come ad esempio una chiave primaria) si utilizza
la sintassi:

nomesequence.nextval

In postgresql bisognerebbe scrivere invece:

nextval(‘nomesequence’);

Lo stratagemma è stato quello di creare una funzione comune ad entrambi i database che restituisca il valore richiesto. Purtroppo non si può creare una funzione nextval in oracle perché la parola è riservata. Percui si è creata su entrambi i database la funzione seq_nextval(‘nomesequence’) che dato il nome della sequence, ne restituisce il valore abbinato.
Il codice PL/SQL per Oracle sarà

create or replace function seq_nextval(NomeSeq String) return binary_integer is
 Result binary_integer;
begin
 EXECUTE IMMEDIATE 'Select ' || NomeSeq || '.nextval from dual' into Result;
 return(result);
end seq_nextval;

Per Postgresql invece la funzione sarà:

create or replace function seq_nextval(NomeSeq TEXT) returns integer AS $$
declare
 Result integer;

begin
 EXECUTE 'Select nextval(''' || NomeSeq || ''')' into Result;
 return(result);
end;
$$ LANGUAGE plpgsql;

Come si può vedere le differenze nelle funzioni sono minime.

B) Data di sistema.

Lo stesso stratagemma usato per le sequence è stato usato anche per ottenere la data di sistema. In Oracle si usa la sysdate in Postgresql la now(). E’ stata quindi creata la funzione get_sysdate

create or replace function get_sysdate return date is
 Result date;
begin
 Select sysdate into Result from dual;
 return(result);
end get_sysdate;

Attenzione: modificata, come spiegato qui.

Per Postgresql la funzione invece sarà:

create or replace function get_sysdate() returns timestamp AS $$
declare
 Result timestamp;

begin
 Select now() into Result;
 return(result);
end;
$$ LANGUAGE plpgsql;

E nelle varie sql al posto di sysdate o now() si userà sempre get_sysdate(). In Oracle si potrebbe anche scrivere solo get_sysdate, ma per avere la stessa sintassi su entrambi i database bisogna utilizzarla con le parentesi aperta-chiusa.

C) Tabella DUAL.
In Oracle quando si vuole fare un’elaborazione che non ha a che fare con i dati, ad esempio ottenere la data di sistema, si scrive una select che estrae dalla tabella DUAL. Ad esempio:

Select sysdate from dual

Dual è una tabella con una sola riga e un solo campo. Il campo si chiama dummy di tipo varchar2 e il suo contenuto è una ‘X’. In Postgresql basta scrivere:

Select now()

per avere lo stesso risultato senza avere alcuna tabella di comodo. Per ovviare al problema basta creare una tabella per compatibilità chiamata Dual anche su postgresql. Quindi ce la caviamo con questo semplice SQL:

CREATE TABLE dual
(
 dummy character varying(1)
);

insert into dual values ('X');

Riguardo alle performance… non ho fatto test particolari, ma usando il “famoso” metodo occhiometrico non c’è una differenza sostanziale di tempi nell’esecuzione delle query anche sulla tabella più grossa che contiene oltre 100 milioni di record.

Insomma non siamo ancora in questa situazione:

oracle_cat_bounce

ma Postgresql è sicuramente un ottimo prodotto con caratteristiche molto, molto avanzate.

Posta un commento o usa questo indirizzo per il trackback.

Commenti

Trackback

  • Errata Corrige | Ok, panico su 27 febbraio 2013 alle 18:30

    […] si, mi sono sbagliato. Nel mio articolo dove descrivevo la migrazione da Oracle a Postgres ho sbagliato il sorgente della funzione get_sysdate. Nella definizione della funzione non andavano […]

Rispondi

Inserisci i tuoi dati qui sotto o clicca su un'icona per effettuare l'accesso:

Logo di WordPress.com

Stai commentando usando il tuo account WordPress.com. Chiudi sessione /  Modifica )

Google photo

Stai commentando usando il tuo account Google. Chiudi sessione /  Modifica )

Foto Twitter

Stai commentando usando il tuo account Twitter. Chiudi sessione /  Modifica )

Foto di Facebook

Stai commentando usando il tuo account Facebook. Chiudi sessione /  Modifica )

Connessione a %s...

Questo sito utilizza Akismet per ridurre lo spam. Scopri come vengono elaborati i dati derivati dai commenti.

%d blogger hanno fatto clic su Mi Piace per questo: