Lista record con numero progressivo

Questa richiesta è arrivata su un forum di programmazione: Come faccio a elencare i risultati di una query con un numero progressivo per ogni record?

Ci sono diverse risposte a questa domanda. La più immediata sarebbe di implementare il conteggio nell’ambito dell’applicazione che riceve i dati. Questa soluzione, però, richiede dettagli diversi in ogni linguaggio e potrebbe non essere quel che si cerca.

Invece, esiste un modo per far contare i record a MySQL, in modo da avere un risultato che sia lo stesso, a prescindere dal linguaggio usato.

Forse non tutti sanno che MySQL consente di usare delle variabili “globali” che sono attive per tutta la durata di una connessione. Per esempio, un uso frequente di questa funzionalità si ha per ovviare alla mancanza di subquery nelle versioni inferiori alla 4.1:


mysql> SELECT @massimo := max(stipendio) from paghe;
mysql> SELECT nome, cognome, stipendio from paghe where stipendio = @massimo;

In maniera simile, possiamo usare una variabile per creare un contatore. Poiché ogni variabile ha valore NULL finché non le viene assegnato esplicitamente qualcosa, è necessario eseguire due query per poter raggiungere lo scopo. Prima, vediamo la query senza contatore.


mysql> SELECT nome,stipendio, genere FROM persone;
+--------+-----------+--------+
| nome | stipendio | genere |
+--------+-----------+--------+
| John | 5000 | m |
| Mario | 6000 | m |
| Frank | 5000 | m |
| Otto | 6000 | m |
| Susan | 5500 | f |
| Martin | 5500 | m |
| Mary | 5500 | f |
| Bill | 5000 | m |
| June | 6000 | f |
+--------+-----------+--------+
9 rows in set (0.01 sec)

Poi, eseguiamo le due query. Innanzitutto inizializziamo il contatore.


mysql> set @N = 0;
Query OK, 0 rows affected (0.27 sec)

Infine, eseguiamo la query includendo il contatore con una formula per incrementarlo.


mysql> SELECT @N := @N +1 AS numero, nome,stipendio,genere FROM persone;
+--------+--------+-----------+--------+
| numero | nome | stipendio | genere |
+--------+--------+-----------+--------+
| 1 | John | 5000 | m |
| 2 | Mario | 6000 | m |
| 3 | Frank | 5000 | m |
| 4 | Otto | 6000 | m |
| 5 | Susan | 5500 | f |
| 6 | Martin | 5500 | m |
| 7 | Mary | 5500 | f |
| 8 | Bill | 5000 | m |
| 9 | June | 6000 | f |
+--------+--------+-----------+--------+
9 rows in set (0.01 sec)

Notate che ogni variabile in una query può essere calcolata una volta sola per ogni record.

Infine, se adottate una soluzione simile, ricordatevi di inizializzare il contatore alla query successiva, altrimenti i vostri record avranno un conteggio in continua crescita!

Trigger su SELECT

I trigger sono aggeggi da usare con cautela. Se abusati, possono portare la vostra applicazione in ginocchio. Tuttavia, sono anche fantastici strumenti per raffinare il controllo in situazioni difficili. Secondo lo standard SQL, i trigger possono essere associati solo alle operazioni di modifica (INSERT, DELETE, UPDATE). E i SELECT, allora? Non sarebbe bello se si potesse instaurare un trigger anche per ogni SELECT?

Supponiamo di avere una tabella books e che vogliamo creare una lista personalizzata degli accessi solo per quella tabella. Ecco come si può fare:

  1. Create una tabella db_counter che conterrà il numero degli accessi, e una tabella db_log che conterrà i dettagli;
  2. Create una funzione che contenga tutte le operazioni che volete eseguire per ogni SELECT. Questa funzione deve restituire un intero positivo;
  3. Create una vista aggiornabile sulla vostra tabella, aggiungendo una clausola WHERE che usa la funzione appena creata.

Andiamo a incominciare:


-- ----------------------------------------------------
-- La tabella originale. Quella per cui vogliamo il log
-- La facciamo semplice per non appesantire l'esempio
-- ----------------------------------------------------
CREATE TABLE books (
book_id INT NOT NULL primary key,
title VARCHAR(40),
pages INT,
price DECIMAL(5,2),
author_id INT NOT NULL,
KEY (author_id)
-- ,FOREIGN KEY (author_id) REFERENCES authors (author_id) ON DELETE CASCADE
) ENGINE = InnoDB;

-- ----------------------------------------------------
-- Il contatore
-- Contiene solo una riga.
-- ----------------------------------------------------
CREATE TABLE db_counter (
id int(11) NOT NULL default '0',
counter bigint(20) default '0',
PRIMARY KEY (id)
) ENGINE=MyISAM;

-- ----------------------------------------------------
-- L'elenco di dettaglio.
-- Ogni riga contiene informazioni sull'orario ,
-- sull'operazione fatta e l'utente.
-- ----------------------------------------------------
CREATE TABLE db_log (
ts timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
user varchar(20) NOT NULL,
operation enum('select','insert','update','delete') default 'select',
KEY user_ts (user,ts),
KEY ts (ts)
) ENGINE=MyISAM;

Queste ultime due tabelle contengono le informazioni che ci servono. Prima di continuare, qualche ritocco per essere sicuri che la tabella del contatore possa contenere solo una riga:


CREATE TRIGGER counter_insert before INSERT on db_counter
for each row set new.id = 1;
CREATE TRIGGER counter_update before UPDATE on db_counter
for each row set new.id = 1;

E ora alcuni trigger per registrare le operazioni di modifica nella tabella books. (Sarebbe buffo che registriamo gli accessi in lettura e trascuriamo quelli in scrittura!)


CREATE TRIGGER book_insert after INSERT on books
for each row set @BOOKCOUNT = get_counter('insert');
CREATE TRIGGER book_delete after DELETE on books
for each row set @BOOKCOUNT = get_counter('delete');
CREATE TRIGGER book_update after UPDATE on books
for each row set @BOOKCOUNT = get_counter('update');

Siamo pronti per creare la funzione:


delimiter //
drop function if exists get_counter //
create function get_counter( oper varchar(10) )
returns int
deterministic
begin
if ( ( select count(*) from db_counter ) > 0 ) then
update db_counter set counter = counter + 1;
else
insert into db_counter (counter) values (1);
end if;
insert into db_log (ts, user, operation) values (NULL, user(), oper);
return (select counter from db_counter where id = 1);
end //
delimiter ;

Infine, creiamo la vista aggiornabile per la tabella books.


CREATE VIEW mybooks as
SELECT * from books WHERE get_counter() > 0;

Ora, usando mybooks invece di books, ogni accesso a books verrà registrato. Vediamo in azione il nostro meccanismo (notate che usiamo sempre la vista, non la tabella).


gmdesk [localhost] {gmax} (test1) > insert into mybooks values (1, 'MySQL', 1000,39.95,1);
Query OK, 1 row affected (0.02 sec)
gmdesk [localhost] {gmax} (test1) > insert into mybooks values (2, 'Programming Perl', 1095,39.95,2);
Query OK, 1 row affected (0.02 sec)


gmdesk [localhost] {gmax} (test1) > select * from db_counter;
+----+---------+
| id | counter |
+----+---------+
| 1 | 2 |
+----+---------+
1 row in set (0.00 sec)


gmdesk [localhost] {gmax} (test1) > select * from db_log;
+---------------------+----------------+-----------+
| ts | user | operation |
+---------------------+----------------+-----------+
| 2005-10-24 11:14:22 | gmax@localhost | insert |
| 2005-10-24 11:14:27 | gmax@localhost | insert |
+---------------------+----------------+-----------+
2 rows in set (0.00 sec)

Dopo due inserimenti, il contatore è a due, e la tabella dei dettagli ha due record. Proviamo con il nostro obiettivo principale, cioè una SELECT:

gmdesk [localhost] {gmax} (test1) > select count(*) from mybooks;
+----------+
| count(*) |
+----------+
| 2 |
+----------+
1 row in set (0.00 sec)

gmdesk [localhost] {gmax} (test1) > select * from db_log;
+---------------------+----------------+-----------+
| ts | user | operation |
+---------------------+----------------+-----------+
| 2005-10-24 11:17:11 | gmax@localhost | insert |
| 2005-10-24 11:17:23 | gmax@localhost | insert |
| 2005-10-24 11:18:08 | gmax@localhost | select |
+---------------------+----------------+-----------+
3 rows in set (0.01 sec)


gmdesk [localhost] {gmax} (test1) > select * from db_counter;
+----+---------+
| id | counter |
+----+---------+
| 1 | 3 |
+----+---------+
1 row in set (0.00 sec)

CVD