[SQL]La query del giorno

Immaginiamo di avere a disposizione una tabella che contenga le vostre vendite annuali, dei campi che contengono per ciascuna vendita l'importo lordo e ivato e che tutte le date siano espresse nel formato unix timestamp (nel caso non sappiate cosa sia, fate riferimento a questo sito che ve lo spiega e vi fornisce un'applicazioncina web per fare la conversione di formato).

Si vuole produrre una query che dia come risultato dei record i cui campi della tabella sono:

• anno di riferimento per le spese;
• Totale vendite per quell'anno;
• Totale fatturato lordo;
• Totale fatturato ivato.

Ovviamente si potrebbe fare una semplice query del sito select * from vendite where [conditions...] e gestire tutto via PHP, ma costringerebbe ad impiegare varie variabili per il conteggio dei vari dati per il calcolo del fatturato lordo e di quello ivato, dato derivato.

Però volevo che facesse tutto MySQL di modo da semplificare al massimo la gestione del risultato con PHP. Quindi ecco la query finale:

select count(*) as totFatture,
    @totLordo := (select truncate(sum(fatturatoLordo),2) from table
                  where [conditions...]
                  and data between UNIX_TIMESTAMP('2007/01/01') and
                  unix_timestamp('2007/12/31')) as totLordo,
    @totIva := @totLordo + (select truncate(sum(fatturatoIvato),2) as totIva from
                            table where [conditions...]
                            and data between UNIX_TIMESTAMP('2007/01/01')
                            and unix_timestamp('2007/12/31')) as totIva,
    @first := 2007 as anno
    from table where [conditions...] and data
    between UNIX_TIMESTAMP('2007/01/01') and unix_timestamp('2007/12/31')
    group by anno
UNION
select count(*) as totFatture,
    @totLordo := (select truncate(sum(fatturatoLordo),2) from table
                  where [conditions...] between
                  UNIX_TIMESTAMP('2008/01/01') and unix_timestamp('2008/12/31'))     
                  as totLordo,
    @totIva := @totLordo + (select truncate(sum(fatturatoIvato),2) from fatture
                            where [conditions...] and data
                            between UNIX_TIMESTAMP('2008/01/01') and
                            unix_timestamp('2008/12/31')) as totIva,
    @second := 2008 as anno
    from tablewhere [conditions...] and data between
    UNIX_TIMESTAMP('2008/01/01') and unix_timestamp('2008/12/31')
    group by anno

Se vi vengono in mente ottimizzazioni, non esitate a riferirle, per quei pochi dati di cui disponete :) A me è stato utile per imparare l'uso delle variabili in SQL ,della funzione unix_timestamp per il calcolo delle spese per anno e di truncate, che semplicemente tronca un numero alla i-esima cifra decimale specificata come secondo argomento, sebbene non arrotondi, ma semplicemente tronchi il numero (poco male alla fine).

[SQL] MySQL tips

Connessione e dumping

1. cambiare la password di un utente:
   

     
   update user set password=PASSWORD("pippo") where user="root";
   flush privileges;    
   

   
   
2. esportare solo le istruzioni DDL dal database:
   

     mysqldump db_name --no-data > script_file.sql
   

   Aggiungere --databases se si vuole creare anche
   il database al pari di altri oggetti.
   


3. avviare MySQL senza far scattare i beep per gli errori:
   

     mysql -uroot --no-beep -p
   

Query

1. Selezionare l'ultimo elemento della tabella:
   

    select field1,field2, ...
   from table [where=...]
   order by field1 DESC limit 1;
   
   -- oppure...
   select field from table
   where id=last_insert_id(); -- dove id è di tipo auto_increment
   

Date ed orari

1. Selezionare l'ora corrente:
   

    select now();  -- 2008-05-26 12:46:08
   -- oppure...
   select current_timestamp;
   

2. Selezionare solo la data corrente senza l'ora:
   

    select CAST(now() as date); -- 2008-05-26
   -- oppure...
   select current_date;
   

3. Selezionare solo l'ora corrente senza la data:
   

    select CAST(now() as time); -- 12:47:34
   -- oppure...
   select current_time;
   

4. Selezionare lo unix timestamp a partire da una data:
   

    select unix_timestamp('2007/03/26 16:21:56')
   -- è uguale a mktime(16,21,56,3,26,2007,-1) in PHP

Win-get: uso e creazione di un repository

Introduzione

Win-get è un packet manager a linea di comando per windows che sfrutta le funzionalità di wget,un porting per Windows del programma omonimo per Linux.

Presenta una lista di programmi via Web (quelli accessibili anche dall'applicazione) e se ci si registra si ha il vantaggio di poter cliccare su dei "+" a fianco dei nomi dell'applicazione e associare l'applicazione stessa al nostro account. In questa maniera sfrutando l'opzione "restore" di win-get,è possibile installare tutti i programmi associati al nostro profilo (specificando le credenziali di autenticazione o nel file di configurazione o come opzioni da linea di comando).
Esiste anche un ' estensione per Firefox (non ancora provata quindi non vi so dire nulla in termini di utilità e compatibilità tra le varie versioni) che permette di segnalare un'applicazione. Fate attenzione che il server non è configurato per le estensioni firefox quindi vi verrà chiesto di scaricarla. Dovrete poi installare tale estensione con "Apri con" e scegliendo Firefox o da linea di comando "firefox estensione.xpi".

Trovate un'altra recensione di questo software su newstechnology.

Funzionamento tecnico

Questo è un pogetto open source,quindi sono disponibili i sorgenti per poterci creare il nostro repository, ma NON l'interfaccia amministrativa e "perlustrativa" del sito ufficiale (in attesa di chiarimenti).

Win-get farà delle query come questa ad uno script PHP (notate i parametri inviati via GET) e si otterrà un file XML descrittivo che verrà poi gestito da Win-get e presentato nel prompt dei comandi.

Come illustra questa query e quella nella foto,sembra che questo progetto sia un repository di collegamenti alle applicazioni da scaricare e che sul server Sourceforge non abbiano messo alcun software (molto probabilmente perchè non si può nemmeno fare). Quindi se guadate al file XML ritornato dalla precedente query,l'indirizzo del file zip verrà dato in pasto a wget presumo.

Creazione di un proprio repository

Ho intenzione di creare un repository su Aruba sfruttando i files forniti,ma permettendo anche di scaricare direttamente dal server Aruba i software,dal momento che godo di spazio illimitato :-D (ma non banda illimitata presumo!). Chi è su Aruba sa che si hanno a disposizione solitamente 5 database predefiniti per un hosting da 30 sacchi l'anno (mi pare,a me hanno regalato il sottodominio quindi non ricordo :-D). Quindi l'istruzione CREATE DATABASE `w154283_winget` DEFAULT CHARACTER SET latin1 COLLATE latin1_swedish_ci; non sarà possibile. Possiamo:

• rinominare uno dei database predefiniti,azione possibile dal pannello di controllo selezionado "Operazioni";
• semplicemente modificare USE w154283_winget; con il nome del nostro database e cancellare o commentare la creazione del database.

Prendiamo quindi il dump del database e imbocchiamolo a PHPMyAdmin presente su Aruba. Il database è composto da 5 tabelle:

• applications: contiene i dati generali di un'applicazione e il link dove risiede;
• dependencies: le dipendenze per una determinata applicazione;
• errors: gli errori in riferimento alle applicazioni;
• mirrors: eventuali mirrors per una certa applicazione;
• requests: identica ad applications,mi è ancora sconosciuto il significato della sua ridondante esistenza;

Fatto questo basta caricare i file winget.php e db.php sul proprio server. L'indirizzo del proprio repository sarà quello rappresentato dalla locazione del file winget.php.

A questo punto manca tutta l'interfaccia amministrativa che il sito ufficiale presenta. Ho chiesto a Ryan se è possibile avere tali files,dal momento che su CVS di sourceforge presenta solo i 3 files menzionati.

Nel caso non fosse possibile ne costruirò una mia non appena avrò tempo :)

Creare una funzione plpgsql che prelevi i dati di una select attraverso un ciclo

Mi ci è voluto un attimo per capire come funzionasse ma alla fine eccola qua la funzioncina che preleva i ricoveri di un paziente del mio progetto di basi (che ritornerà ad esser scaricabile non appena avrò passato l'esame):

create or replace function dati_ricovero(varchar) returns setof ricovero as $$
DECLARE 
t ricovero%ROWTYPE;
BEGIN
       FOR t IN
        select r.* 
        from ricovero r,account_pazienti u,paziente p
        where u.codiceweb=p.codiceweb AND u.username=$1
        AND p.ssn=r.ssn
       LOOP
         RETURN NEXT t;
       END LOOP;
END;
$$ LANGUAGE 'plpgsql'; 

PostgreSQL e la funzione sleep

Può capitare che per alcune esigenze di simulazione occorra simulare un ritardo di esecuzione oppure come nel mio caso,facendo una serie di inserimenti dietro l'altro nei quali la chiave primaria è la funzione now(),ci ritroviamo con dei bei errori perchè la funzione now() viene richiamata talmente velocemente da ritornare il medesimo risultatoi per un buon numero di statement.
In questa pagina vien emostrato come costruire una funzione plpgsql che simuli una sleep tramite un ciclo oppure usare la sleep di PERL avendo questo linguaggio a bordo e avendo caricato la libreria con CREATE LANGUAGE.

create or replace function sleep (numeric) returns time as '
    declare
        seconds alias for $1;
        later time;
        thetime time;
    begin
        thetime := timeofday()::timestamp;
        later := thetime + (seconds::text || '' seconds'')::interval;
        loop
            if thetime >= later then
                exit;
            else
                thetime := timeofday()::timestamp;
            end if;
        end loop;

        return null;
    end;
' language plpgsql;

Visual explanation of SQL join

Se avete dei dubbi sull'uso del JOIN in SQL (e le sue varie forme come INNER JOIN,LEFT OUTER JOIN e via dicendo) consiglio di vedere questo bel post di coding horror,che dà una spiegazione insiemistica del loro funzionamento. Sinceramente non ho mai usato tutte le varianti del JOIN,ancora non ne ho avuto bisogno :)

SQL:tutorial e documentazioni

Riassumendo Mashable,un buon punto di partenza sicuramente sono le documentazioni ufficiali offerte dai DBMS. Vediamone una breve lista:

• SQL 2003 standard (zip);
• PostgreSQL 8.2 documentazione;
• IBM DB2 doc;
• MS SQL Server;
• Oracle doc;

Lo standard si sa,è variegatamente implementato nei vari DBMS e qui potete vederne alcune differenze.
Dopo di che è il caso di cercarsi tutorial o leggerse libri al riguardo:

• MySQL Basics;
• MySQL in pillole;
• SQL.org;
• SQL tutorial;
• SQL course;
• SQlzoo;
• W3School;
  

Come seconda cosa andiamo ad indicare editor disponibili: per primo cito quello che non è proprio un editor ma un formattatore di istruzioni SQL,incapsulabili anche in un altro linguaggio quale PHP o Java. Possiamo usare anche PHPMyAdmin (o PGAdmin per PostgreSQL,cmq software analoghi) per imparare il linguaggio: se non ci accontentiamo di usare la grafica bella e pronta ma immettiamo le query testualmente,otterremo gli stessi risultati ma essendo consapevoli di quello che avviene sotto le quinte.
Un altro editor è WWW SQL Designer,che agisce via browser dopo aver settato connessioni al database.

Approfondimenti su SQL Injection

Sul mio sito di appunti,potete trovare un riassuntino abbastanza esaustivo su quest'argomento,comprensivo di svariate fonti,tra le quali spicca come al solito Wikipedia. Infatti l'enciclopedia libera mi ha fornito una gran serie di link. Per chi non avesse voglia di vedere il mio riassuntino,puà vedere le fonti che lo hanno interessato qui sotto:

• Programming:PHP/SQL Injection;
  
• Espressioni regolari in PHP;
  
• Serie di link,pdf e presentazioni su Wikipedia;
  
• MySQL injection: guide to security;
  
• Classe anti-sqlinjection e classe PEAR Validate;