Tipo Boolean in Oracle – Una piccola digressione

Due parole sul tipo Boolean

In questo post andremo ad affrontare un argomento particolare: parleremo di questo tipo Boolean sotto Oracle.

Esiste il tipo Boolean?

Oracle mette a disposizione il tipo Boolean, ma occorre tenere conto di queste precisazioni:

  • NON E’ usabile nella definizione dei campi della tabella.
  • PUO’ essere usato nel PLSQL.

Se vogliamo utilizzarlo nella definizione di una tabella, come suggerito da Tom Kyle, conviene sostituirlo con il tipo CHAR(1), che presenta le stesse caratteristiche, come mostrato di seguito:

flag char(1) check (flag in ( 'Y', 'N' )),

Il risultato è il medesimo.

Una precisazione

Anche se esiste come datatype, non è possibile usarlo ovunque. PL-SQL ha le sue regole e chi lo usa deve mettersi in testa che si devono seguire. Non si può pretendere che, se altri linguaggi (quali ad es. JAVA) lo consentono, PL-SQL si deve adeguare.

 

Conclusione

Abbiamo visto un piccolo tassello, ma di grande importanza. Questo ci ricorda che ogni linguaggio ha le sue regole e i suoi ambiti di applicazione. Dobbiamo tenerne presente sempre.




Esportazione dati via SQLPLUS

Un suggerimento

In questo post, andremo a vedere un piccolo trucchetto che ho usato per poter esportare dei dati, sopratutto quando si tratta di esportare dati di una certa mole.

 

Andiamo nel dettaglio

L’idea è di fare uso di SQLPLUS, che si presta bene a questo genere di lavori. sfruttando le sue capacità. In particolare ci appoggiamo alla possibilità di poter inviare su file il risultato della esecuzione della query. Stiamo parlando del comando: SPOOL;

 

 

Andiamo nel dettaglio e vediamo come fare.

Una volta definita la query, con tutti i dati che sono necessari. Quindi componiamo la query come segue:

Select <campo1> || <campo2> || ..... || <campon> from .....

oppure come

Select <campo1> || <delimitatore> || <campo2> || <delimitatore> ||  ..... || <campon> from .....

a seconda che si voglia avere una estrazione dove i campi sono a lunghezza fissa (primo caso) o con delimitatore (secondo caso).

A questo punto, vediamo come procedere per avere l’estrazione vera e propria.

Attiviamo SQLPLUS, da riga di comando:

sqlplus <utente>/<password>@<dbsid>

Quindi forniamo la sequenza di comandi:

set heading off
set pagesize 0
set linesize 200
set feedback off
Spool on
Spool <file_destinazione>.txt
<scriviamo la Query, mettendo il ';' finale >
Spool off <da lanciare al termine della esecuzione della query>

sqlplus01

La precedente figura da una idea per capire come procedere. Ovviamente mi sono limitato a eseguire una query di esempio per mostrare il funzionamento.

Conclusioni

Abbiamo un sistema molto semplice per eseguire delle estrazioni dati molto semplici. Il metodo è automatizzabile, creando degli script ad hoc, per semplificarci ulteriormente la vita :-).




Semplici statistiche in Confluence

Visualizziamo numeri

In questo post sarà mostrato come poter utilizzare Confluence per realizzare delle semplici statistiche. Si tratta di un semplice esempio di uso dedicato alla realizzazione di dashboard, che possono sempre aiutarci nel nostro lavoro.

Di cosa abbiamo bisogno?

Quello di cui abbiamo bisogno è:

  • Confluence (versione cloud)
  • SQL Play
  • Database MySql, per fare tutte le prove di cui abbiamo bisogno. Per tutte le nostre prove, utilizzeremo db4free, un sito che gratuitamente mette a disposizione database mysql di test, attraverso eseguire dei test.

Andiamo in dettaglio

In questo post andremo a vedere un semplice esempio di come visualizzare alcune informazioni. IN questo caso supponiamo di andare a visualizzare delle semplici statistiche sui miei consumi di benzina 🙂

A tale scopo, ho creato un semplice DB su db4free, dove ho riportato alcuni semplici dati.

Dash00

Sfruttando le potenzialità dell’addon SQL Play, ho creato il contesto verso tale database, quindi ho creato la query per visualizzare i dati.

Dash01

A questo punto creiamo la pagina di prova 🙂

sql10
Ovviamente si tratta di dati di esempio. 🙂

Conclusioni

Abbiamo visto come inserire delle semplici statistiche da un database remoto. Possiamo a questo punto creare delle dashboard che, leggendo le informazioni da un db remoto, visualizzano i risultati direttamente sulle pagine di Confluence.

 




RMAN – first Look

RMAN – First Look

Iniziamo, con questo post, una serie di articoli in cui andiamo a spiegare il funzionamento di questo comando, la sua utilità ed il suo utilizzo.

 

Che cosa è?

RMAN (Oracle Recovery Manager) è un interprete a riga di comando. L’utility è presente in ogni installazione di database, anche per le installazioni XE (express).

 

A cosa serve?

Serve fondamentalmente per eseguire backup e restore di database Oracle. Il backup è inteso per ogni singola ISTANZA di Oracle, non per l’intero database. RMAN può eseguire sia backup FULL o completi che backup incrementali, in base alle esigenze o alle impostazioni eseguite

 

Esempio di uso

Vediamo un primo esempio di utilizzo. Per far ciò mi avvalgo della mia versione di Oracle 11 XE, che ho installato.

RMAN03

Come mostrato dalla figura, per accedere ad RMAN, attiviamo una sessione di CMD e successivamente digitiamo il comando RMAN TARGET /, per accedere alla attuale istanza Oracle. Dato che dispongo di una installazione XE, non avendo a disposizione che una sola istanza Oracle, non devo fare altre operazioni per selezionare dove connettermi. Dalla immagine possiamo vedere che siamo collegati alla istanza XE.

Possiamo subito vedere se abbiamo dei backup, lanciando il comando: LIST BACKUP SUMMARY;

RMAN04

che ci mostra l’elenco dei backup attualmente presenti a sistema.

Per lanciare un backup, lanciare il comando: BACKUP DATABASE; Il risultato sarà simile a quello mostrato ella figura sottostante (dove ho lanciato un comando similare, che ripete il backup ma eseguendo una validazione per verificare se è possibile eseguite l’operazione).

RMAN05

 

Per ripristinare il backup, semplicemente eseguire il comando RESTORE DATABASE

 

Conclusioni

Questo è solo il primo passo, per descrivere questa importantissima utility di Oracle, in cui abbiamo semplicemente presentato alcune delle funzionalità. Nei prossimi post, andremo ad esplorarla meglio e a fare dei semplici esempi di come possiamo utilizzarla.

 

 

Reference

Maggiori informazioni sono reperibili sui vari manuali Oracle, ma una buona reference è reperibile qui

 

 

 




Panoramica sulle tabelle di Confluence – 2

Il viaggio continua

Continuiamo in questo post quanto iniziato nel precedente, dove abbiamo iniziato a esplorare l’oscuro arcano delle tabelle di Confluence. Andiamo avanti ed iniziamo ad entrare meglio nelle varie tabelle.

Prendendo spunto dal data model di Confluence, andiamo ad esaminare alcune delle tabelle dello schema.

 

Autenticazione ed utenti

Per gestire l’autenticazione, sono utilizzate le seguenti tabelle:

  • cwd_user
  • cwd_group
  • cwd_membership

Mappano rispettivamente gli utenti e i gruppi di appartenenza. La tabella cwd_membership fornisce l’associazione tra utenti e gruppi. . Qualora Confluence sia collegato ad un LDAP, le informazioni in queste tabelle sono aggiornate costantemente e la gestione della password viene affidata al sistema LDAP. Abbiamo visto un esempio di come andare a referenziare queste tabelle su questo post.

 

Spaces

Le informazioni sugli space sono relegate alla omonima tabella Space. Questa viene referenziata anche sulle altre tabelle, per indicare l’appartenenza dei contenuti.

 

Contenuti

I contenuti sono riportati nelle seguenti tabelle:

  • Content – Referenzia diverse informazioni, distinte in base al campo CONTENTTYPE. In particolare, in questa tabella sono presenti sia le informazioni delle varie pagine, che metadati dell’utente e degli space. Nel caso delle pagine, sono presenti TUTTE le versioni della pagina. Attraverso questa tabella Confluence riesce a fornire le differenze tra pagine.
  • Bodycontent – Referenzia il contenuto delle pagine. Queste sono riportati con i vari TAG usati per definirire formati ed altro.
  • Content_label – Referenzia le etichette che sono agganciati ai vari contenuti, distinti in base al campo LABELABLETYPE. 

Permissions

Le permission sono sparse su diverse tabelle:

  • Spacepermissions – Referenzia le permission che sono state assegnate ad uno specifico SPACE. Sono riportate sia le permission assegnate a GRUPPI che a singoli utenti. Nel caso dei singoli utenti, prestare attenzione a come sono codificati gli utenti stessi. Fare rifermento al precedente post per vedere come referenziare correttamente il tutto
  • Content_perm_set – Referenzia quale pagina dispone di una restrizione. Il dettaglio è riportato nella tabella successiva.
  • Content_perm – Da il dettaglio delle restrizioni della pagina referenziata nella tabella precedente, indicando quali utenti/gruppi sono soggetti alla restrizione.

 

Attachments

Gli attachments sono gestiti attraverso queste tabelle

  • attachments – Referenzia i metadati che sono agganciati ai dati.
  • attachmentdata – Referenzia gli allegati veri e propri, nel caso in cui la configurazione di Confluence preveda che gli allegati siano riportati su DB e non su File System.

Conclusioni

In questo post viene mostrato come sono organizzati i dati. Nei prossimi post cercheremo di fornire una ulteriore spiegazione di come sono collegate le informazioni e di come referenziarle.

 




Breve panoramica sulle tabelle di JIRA

JIRA e le sue tabelle

In questo post, proseguiamo quanto iniziato su questo post, dove abbiamo fatto una breve panoramica delle tabelle di Confluence.

In questo caso, proviamo ad eseguire la stessa operazione presentata nel post precedente, ma facendo riferimento alle tabelle di JIRA. La seguente immagine riassume alcune delle tabelle di JIRA. Si rimanda alla sezione Reference per tutte le indicazioni sul caso.

 

 

Precauzioni

Si ribadiscono le stesse identiche precauzioni che sono state indicate nel precedente post. Fate sempre un backup dei dati, prima di procedere con qualsiasi operazione. In questo modo potete essere sicuri di poter operare in tutta sicurezza.

 

Iniziamo 🙂

Cerchiamo di impostare un avatar di default differente da quello preimpostato. Partiamo da questa situazione:

profile01

 

Le tabelle che andiamo a referenziate sono le seguenti:

  • cwd_user – Tabella contenente le informazioni degli utenti
  • avatar – contenente gli avatar standard
  • propertyentry – contenente le associazioni da utente ed avatar utilizzato
  • propertynumber – contenente l’associazione con l’avatar collegato all’utente

 

Procediamo con la modifica

Aggiungiamo il nuovo avatar. Il file va caricato nella directory:

<Install-Dir>Atlassian-JiraWEBINFClassesAvatar

Supponiamo di utilizzare sempre l’icona del pinguino 🙂

pinguino48

Punto di attenzione. A differenza di Confluence, JIRA ha la necessità di avere anche una seconda icona, dimensione 24×24, la metà rispetto alla dimensione dell’avatar che si inserisce, ovvero 48×48, che viene referenziato e mostrato in alto a destra. Predisporre quindi un secondo avatar, nome pari a small_<nome_del_file_nuovo_avatar>.png e memorizzarlo nella stessa directory.

Quindi aggiungiamo un nuovo record, alla tabella avatar, con le indicazioni dell’icona nuova

avatar-new

Dalla tabella cwd_user, identifichiamo l’utente in questione:

user

Dalla propertyentry ci ricaviamo l’ID per determinare la proprietà da andare a modificare, cercando per ENTITY_ID = ID USER e PROPERTY_KEY = ‘user.avatar.id’:

avatar-user

Quindi dalla propertynumber, ci ricaviamo l’ID dell’avatar utilizzato.

new-avatar

Inseriamo, nel campo propertyvalue, l’ID del nuovo avatar di default, e questo è il risultato:

nuovo-profilo

 

 

Conclusioni

Con questo sistema riusciamo ad impostare il nuovo avatar di default in maniera semplice. Questo ci consente di poter eseguire una semplice modifica alla installazione, sostituendo gli avatar in modo semplice.

 

Se si vuole aggiungere un avatar non di default?

Fattibile, ma occorre agire da tutt’altra parte. In questo caso si dovrebbe memorizzare il file del nuovo avatar in altra directory, ovvero:

<JIRA-Home-dir>dataavatars

e si memorizzano in vari formati, principalmente il formato 48×48 ed il formato 24×24, che servono principalmente per tutte le funzionalità. La configurazione delle tabelle è la medesima. Il nome del file, come per il precedente esempio, deve essere preceduto dal nuovo ID assegnato alla tabella Avatar. Nei prossimi post vedremo altre informazioni sulle tabelle di JIRA.

 




Accedere ai dati di CONFLUENCE

Scaviamo in profondità

In questo post andremo a vedere, con alcuni esempi, come possiamo accedere ai dati di Confluence e JIRA, lavorando direttamente su DB. In particolare vedremo alcuni esempi di come sono memorizzate le informazioni. In figura vediamo lo schema delle tabelle di Confluence.

 

Perché accedere ai dati?

Prima di iniziare ad esplorare le tabelle dei due sistemi, conviene porsi una domanda fondamentale: Perché dobbiamo accedere direttamente ai dati del DB? Quale è la necessità?

Uno dei motivi più importanti è sicuramente quello di dover accedere ad informazioni a cui non si avrebbe altrimenti accesso, oppure il dover eseguire una operazione massiva. Questo perché non sempre è possibile eseguire una operazione su di una grossa mole di dati, in quanto Confluence/JIRA non la consentono, come ribadito in altri post di questo blog. Fino a quando non saranno disponibili delle funzionalità di un certo tipo, occorre dover agire direttamente da DB. In aggiunta, queste informazioni sono sicuramente utili a coloro che vogliono sviluppare addon per i prodotti Atlassian 🙂

 

Precauzioni

Prima di agire, occorre sempre che siano prese delle semplici precauzioni. Il motivo mi sembra abbastanza semplice: Quando si lavora direttamente su DB, è abbastanza facile arrecare danni. Di conseguenza, sempre meglio avere un backup dei dati/tabelle/DB intero prima di procedere con le modifiche.

Il mio consiglio è sempre quello di avere a disposizione un backup del DB completo + una copia delle tabelle su cui si agisce, prima di procedere con qualsiasi operazione.

 

Un primo esempio

Supponiamo che si voglia modificare gli avatar standard, con un avatar differente (i motivi possono essere qualsiasi: Marketing aziendale,  impostare un avatar di default più carino, etc).

 

profile

 

Al momento, solo gli utenti possono modificare il proprio avatar. A livello di amministrazione, non è possibile eseguire tale operazione. La precedente immagine mostra dove è possibile eseguire tale operazione. Adesso vediamo come è possibile aggirare tale limitazione, semplicemente andando a lavorare su DB.

In prima battuta esaminiamo le tabelle dove sono contenute le informazioni delle utenze. In particolare faremo riferimento a:

  • cwd_user – Tabella contenente le informazioni degli utenti che sono stati configurati in Confluence.
  • cwd_group – Tabella contenente le informazioni dei gruppi definiti su Confluence.
  • os_propertyentry – Tabella contenente anche le informazioni degli avatar.
  • user_mapping – Tabella contenente la corrispondenza uid – nome utente.

La tabella che ci interessa in particolare è l’ultima. Dobbiamo andare a cercare le informazioni degli avatar, utilizzando questa query:

query

Come si vede dalla precedente immagine, quello che dobbiamo andare a cercare è il campo String_val. In questo campo è presente la localizzazione dell’avatar. In questo caso ho assegnato un avatar di default. 🙂 Nel campo Entity_name abbiamo la userid. Come possiamo vedere è abbastanza ….. criptata. Come facciamo a decodificarla? Il giro è il seguente:

  • Dalla tabella os_propertyentry abbiamo le indicazioni del path della immagine
  • Dalla tabella user_mapping abbiamo le indicazioni dell’uid dell’utente
  • Dalla tabella cwd_user abbiamo il nome dell’utente.

Le seguenti immagini chiariscono il tutto: Dall’ID identifichiamo l’utente

query01

 

query02

Di conseguenza, per modificare l’avatar dell’utente admin (in questo caso), possiamo semplicemente modificare il path e andare ad assegnarne uno nuovo. Supponiamo di creare una sottodirectory nel path utilizzato da Confluence per gestire gli avatar di default, ovvero:

<Install_dir_Confluence>confluenceimagesiconsprofilepic

supponiamo di chiamarla Demoprofile e di memorizzare il seguente avatar:

pinguino48

Eseguire il restart del servizio Confluence e, come per magia, il risultato sarà il seguente:

 profiloMod

Conclusioni

Abbiamo visto un esempio di come si può modificare il database per modificare gli avatar degli utenti di Confluence. Questo esempio può essere utilizzato anche per le installazioni per cui gli utenti sono presi da LDAP e non sono solo utenti locali di Confluence.  Nei prossimi post vedremo altri esempi di come si può accedere ai dati e …. vedere altre informazioni.




Creare una funzione split con plsql

Funzione SPLIT in PL-SQL

Riporto un sistema molto semplice per realizzare una funzione SPLIT in PL-SQL. Si tratta di una procedura che ho trovato in questo blog.

Si tratta di una funzione che, data una stringa con separatori, restityuisce l’elemento i-esimo.

create or replace function get_token(
   the_list  varchar2,
   the_index number,
   delim     varchar2:= ','
)
   return varchar2 is
   start_pos number;
   end_pos   number;
begin

   if the_index = 1 then
       start_pos := 1;
   else
       start_pos := instr(the_list, delim, 1, the_index - 1);
       if start_pos = 0 then 
          return null;
       else
           start_pos := start_pos + length(delim);
       end if;
   end if;

   end_pos := instr(the_list, delim, start_pos, 1);

   if end_pos = 0 then
       return substr(the_list, start_pos);
   else
       return substr(the_list, start_pos, end_pos - start_pos);
   end if;

end get_token;
/

Si tratta di una funzione molto semplice. Il risultato è il seguente:

select
   get_token('foo,bar,baz',1), -- 'foo'
   get_token('foo,bar,baz',3), -- 'baz'
   --
   get_token('a,,b',2),        -- '' (null)
   get_token('a,,b',3),        -- 'b'
   --
   get_token('a|b|c',2,'|'),   -- 'b'
   get_token('a|b|c',4,'|')    -- '' (null)
from
   dual

Punti di attenzione

  • L’indice parte da 1 e non da 0
  • Se per l’indice specificato, non è presente alcun valore, allora non viene restituito alcun valore
  • Se si fornisce un indice che non esiste (es. la stringa è formata da 3 elementi e si richiede il 4 elemento), allora anche in questo caso, la funzione non restituisce nulla.

 

Suggerimenti

Potrebbe tornare utile far restituire una stringa ad hoc, qualora non venga fornito un indice non esistente nella stringa, sostituendo il return null; con return ‘-1’; in modo da distinguere la situazione in cui non venga restituito un valore da l’aver richiesto un indice non esistente.

 

 




Campo CLOB via DB-LINK

Vi sarà capitato di ….

…. dover gestire delle tabelle via DB-LINK. Ma a quanti sarà capitato di dover lavorare via db-link con tabelle che presentano dei campi LOB? Magari con una versione un pò più datata di Oracle (Oracle 9, per intenderci)?

Di sicuro ci  si sarà scontrati con l’errore:

LOB

 

semplicemente lanciando una query del tipo:

Select * from tabella@dblink.

Oracle non consente di poter eseguire la select diretta, come riportato nel seguente articolo di AskTom, ma sono possibili altre operazioni molto interessanti. Non potendo leggere da remoto, possiamo portare le informazioni in locale attraverso due possibili strade:

  1. INSERT INTO….. SELECT * FROM TABELLA@dblink
  2. CREATE TABLE ….. AS SELECT * FROM TABELLA@dblink

Il primo sistema presuppone la presenza di una tabella destinazione, mentre con il secondo sistema, andiamo fisicamente a generare la tabella in locale. Una volta portati i dati in locale, possiamo fare qualsiasi operazione. 🙂

 

Controindicazioni?

Ovviamente se la tabella remota presenta un numero di record molto alto, conviene impostare delle condizioni di filtro alla query su tabella remota, in modo da non portare troppi record.

 



Esiste una funzione isNumeric in PLSQL??

Potrebbe sorgere la necessità di dover leggere dei valori solo numerici, memorizzati su di un campo alfanumerico. I motivi per cui si può avere questa necessità possono essere svariati.

Al momento in cui scrivo, una funzione isNumeric o similare non esiste in PLSQL. Come possiamo realizzare questo? Ci viene in aiuto il sito di AskTom, che ci fornisce alcuni spunti.

 

Abbiamo due strade:

 

Funzione Translate….

La funzione Translate può risultare molto utile. Come indicato in questa pagina, la funzione esegue una sostituzione di caratteri basandosi su delle corrispondenze. Di conseguenza, un sistema per ottenere il risultato desiderato, si può usare la seguente istruzione:

replace( translate( <valore>, ‘0123456789’, ‘0000000000’ ), ‘0’, ” )

Spieghiamo il funzionamento: La translate, fondamentalmente, esegue una sostituzione delle cifre numeriche con ‘0’ (zero). La seconda funzione replace, banalmente, elimina lo ‘0’ (zero).

Conseguenza? Se <valore> contiene delle cifre numeriche, queste sono eliminate e viene restituita una stringa vuota. In alternativa, se su <valore> è presente un carattere alfanumerico, questo rimane e viene restituito nella stringa.

.

REGEX – Espressioni Regolari

E’ possibile anche utilizzare le espressioni regolari, per riuscire ad identificare i valori solo numerici. Se la versione di Oracle è la 10g o successive, la funzione REGEXP_LIKE può essere utilizzata. Di seguito un esempio:

 

SELECT col1 FROM t1 WHERE REGEXP_LIKE(col1, ‘[[:digit:]]’);

Basta semplicemente impostare le regole, ed il gioco è fatto.

Conclusioni

Si tratta di due soluzioni più che valide, ma la prima risulta essere molto semplice, veloce, pratica ed usabile in vari contesti. La seconda risulta più pesante.