Petar Karan

[mysql] Creare tabella pivot dinamica in MySQL (per esordienti totali)

Con il MySQL è possibile generare tabelle pivot dinamiche, ovvero che contengano nomi di colonne automatiche, in base ad una tabella di riferimento.

Quello che vogliamo ottenere sarà un risultato come il seguente:

Prima di cominciare creiamo un database con un po’ di dati in modo da poterci lavorare.

1. Creazione dati di partenza

Creiamo 3 tabelle: prodotti, agenti e vendite. Di seguito riporto le query per creare le singole tabelle ed inserire i dati di esempio.

PRODOTTI

Per creare la tabella prodotti eseguiamo la seguente query.

CREATE TABLE `prodotti` (
  `id` int(255) NOT NULL,
  `nome` varchar(1024) NOT NULL,
  `prezzo_unitario` double NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE `prodotti` (

`id` int(255) NOT NULL,

`nome` varchar(1024) NOT NULL,

`prezzo_unitario` double NOT NULL

) ENGINE=InnoDB DEFAULT CHARSET=latin1;

Inseriamo i dati nel modo seguente:

INSERT INTO `prodotti` (`id`, `nome`, `prezzo_unitario`) VALUES
(1, 'Mele', 600),
(2, 'Pere', 900),
(3, 'Banane', 750),
(4, 'Pesche', 1100),
(5, 'Kiwi', 1200);

INSERT INTO `prodotti` (`id`, `nome`, `prezzo_unitario`) VALUES

(1, 'Mele', 600),

(2, 'Pere', 900),

(3, 'Banane', 750),

(4, 'Pesche', 1100),

(5, 'Kiwi', 1200);

AGENTI

Andiamo adesso a creare la tabella degli agenti, in modo analogo:

CREATE TABLE `agenti` (
  `id` int(255) NOT NULL,
  `nome` varchar(100) NOT NULL,
  `cognome` varchar(100) NOT NULL,
  `stato` int(10) NOT NULL DEFAULT '1'
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE `agenti` (

`id` int(255) NOT NULL,

`nome` varchar(100) NOT NULL,

`cognome` varchar(100) NOT NULL,

`stato` int(10) NOT NULL DEFAULT '1'

) ENGINE=InnoDB DEFAULT CHARSET=latin1;

Ed inseriamo i dati:

INSERT INTO `agenti` (`id`, `nome`, `cognome`, `stato`) VALUES
(1, 'Mario', 'Rossi', 1),
(2, 'Luigi', 'Verdi', 1),
(3, 'Luisa', 'Neri', 1),
(4, 'Chiara', 'Celesti', 1),
(5, 'Paolo', 'Bianchi', 1),
(6, 'Anna', 'Rossi', 1);

INSERT INTO `agenti` (`id`, `nome`, `cognome`, `stato`) VALUES

(1, 'Mario', 'Rossi', 1),

(2, 'Luigi', 'Verdi', 1),

(3, 'Luisa', 'Neri', 1),

(4, 'Chiara', 'Celesti', 1),

(5, 'Paolo', 'Bianchi', 1),

(6, 'Anna', 'Rossi', 1);

VENDITE

Per le vendite andremo a creare la tabella in questo modo:

CREATE TABLE `vendite` (
  `id` int(255) NOT NULL,
  `idAgente` int(255) NOT NULL,
  `idProdotto` int(255) NOT NULL,
  `data` int(11) NOT NULL,
  `qta` int(255) NOT NULL DEFAULT '0',
  `importo` double NOT NULL,
  `stato` int(10) NOT NULL DEFAULT '1'
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE `vendite` (

`id` int(255) NOT NULL,

`idAgente` int(255) NOT NULL,

`idProdotto` int(255) NOT NULL,

`data` int(11) NOT NULL,

`qta` int(255) NOT NULL DEFAULT '0',

`importo` double NOT NULL,

`stato` int(10) NOT NULL DEFAULT '1'

) ENGINE=InnoDB DEFAULT CHARSET=latin1;

A differenza delle precedenti due, qui voglio inserire dei dati casuali. Per inserire una riga di dati casuali, pescati dalle precedenti due tabelle, potremmo scrivere:

INSERT INTO `vendite`(`idAgente`, `idProdotto`, `data`, `importo`, `stato`) 
VALUES (ROUND(1+RAND()*4,0),ROUND(1+RAND()*4,0),UNIX_TIMESTAMP()-ROUND(RAND()*365*3600*24,0),ROUND(RAND()*10000,2),1);

1 2	INSERT INTO `vendite`(`idAgente`, `idProdotto`, `data`, `importo`, `stato`) VALUES (ROUND(1+RAND()4,0),ROUND(1+RAND()4,0),UNIX_TIMESTAMP()-ROUND(RAND()365360024,0),ROUND(RAND()10000,2),1);

Faccio notare che con l’istruzione ROUND(1+RAND()*4,0) inseriamo un valore intero casuale tra 1 e 5. Se volessimo generalizzare il procedimento potremmo scrivere ROUND(n+RAND()*(m-n),0) dove n è il minimo, mentre m è il massimo.

Con n = 3 e m = 8 avremmo infatti le seguenti operazioni:

RAND() genera un valore casuale decimale da 0 a 1
Moltiplicando per (8-3) il valore casuale sarà tra 0 e 5.
Sommano il risultato a 3 il valore casuale sarà un decimale tra 3 e 8. Potrebbe essere per esempio 6,4.
Usando ROUND() arrotondiamo a 0 cifre decimali ottenendo quindi un intero.

Se volessimo inserire più di una riga in una singola operazione, possiamo farlo scrivendo una procedura.

Dichiariamo quindi una procedura nel modo seguente, che inserisca 1000 valore casuali per volta.

DELIMITER //

CREATE PROCEDURE inserimenti_casuali()
BEGIN
	DECLARE tot INT UNSIGNED DEFAULT 0;
    WHILE tot <= 1000 DO
		INSERT INTO `vendite`(`idAgente`, `idProdotto`, `data`, `importo`, `stato`) 
                VALUES (ROUND(1+RAND()*4,0),ROUND(1+RAND()*4,0),UNIX_TIMESTAMP()-ROUND(RAND()*365*3600*24,0),ROUND(RAND()*10000,2),1);
        SET tot = tot + 1;
	END WHILE;
END//

DELIMITER ;

DELIMITER //

CREATE PROCEDURE inserimenti_casuali()

BEGIN

DECLARE tot INT UNSIGNED DEFAULT 0;

WHILE tot <= 1000 DO

INSERT INTO `vendite`(`idAgente`, `idProdotto`, `data`, `importo`, `stato`)

VALUES (ROUND(1+RAND()*4,0),ROUND(1+RAND()*4,0),UNIX_TIMESTAMP()-ROUND(RAND()*365*3600*24,0),ROUND(RAND()*10000,2),1);

SET tot = tot + 1;

END WHILE;

END//

DELIMITER ;

Chiamiamo la procedura digitando:

CALL inserimenti_casuali();

1	CALL inserimenti_casuali();

2. Creazione tabella pivot

Per creare una tabella pivot manualmente utilizziamo CASE WHEN ... END. A mano potremmo scrivere una query come quella di seguito:

SELECT 
	a.nome, a.cognome,
	ROUND(SUM(v.importo),2) AS totale,
	ROUND(SUM(
		CASE
			WHEN v.idProdotto = 1
			THEN v.importo
			ELSE 0
		END
	),2) AS mele,
	ROUND(SUM(
		CASE
			WHEN v.idProdotto = 2
			THEN v.importo
			ELSE 0
		END
	),2) AS pere
FROM
	agenti a LEFT JOIN vendite v ON v.idAgente = a.id
GROUP BY
	a.id

SELECT

a.nome, a.cognome,

ROUND(SUM(v.importo),2) AS totale,

ROUND(SUM(

CASE

WHEN v.idProdotto = 1

THEN v.importo

ELSE 0

END

),2) AS mele,

ROUND(SUM(

CASE

WHEN v.idProdotto = 2

THEN v.importo

ELSE 0

END

),2) AS pere

FROM

agenti a LEFT JOIN vendite v ON v.idAgente = a.id

GROUP BY

a.id

Ogni colonna della pivot sarà generata da:

ROUND(SUM(
	CASE
		WHEN v.idProdotto = 1
		THEN v.importo
		ELSE 0
	END
),2) AS mele,

ROUND(SUM(

CASE

WHEN v.idProdotto = 1

THEN v.importo

ELSE 0

END

),2) AS mele,

In questo caso noi abbiamo una tabella dei prodotti, dove sappiamo esserci all’id = 1 il prodotto chiamato mele.

Con SUM() sommiamo i valori (avremmo potuto usare COUNT(), AVG(), MAX(), MIN() ecc.) e con ROUND() arrotondiamo. Quest’ultima funzione in particolare non sarebbe necessaria, ma la utilizzo solo perché, visto che si parla di importi in euro, non ha senso calcolare un risultato al di sotto dei centesimi.

Quello che vogliamo fare ora è poter scrivere, quel pezzo di query, in modo iterativo, ripetendola per CIASCUN valore della tabella prodotti.

In particolare potremmo eseguire una query come la seguente:

SELECT
	GROUP_CONCAT(DISTINCT
		CONCAT(
        	'ROUND(SUM(
                CASE
                    WHEN v.idProdotto = ', p.id,'
                    THEN v.importo
                    ELSE 0
                END
            ),2) AS `', p.nome,'` '
        )
    )
FROM
	prodotti p;

SELECT

GROUP_CONCAT(DISTINCT

CONCAT(

'ROUND(SUM(

CASE

WHEN v.idProdotto = ', p.id,'

THEN v.importo

ELSE 0

END

),2) AS `', p.nome,'` '

)

FROM

prodotti p;

Il risultato di questa query sarebbe un singolo campo contenente i seguenti valori:

ROUND(SUM(
   CASE
	   WHEN v.idProdotto = 1
	   THEN v.importo
	   ELSE 0
   END
),2) AS `Mele` ,ROUND(SUM(
   CASE
	   WHEN v.idProdotto = 2
	   THEN v.importo
	   ELSE 0
   END
),2) AS `Pere` ,ROUND(SUM(
   CASE
	   WHEN v.idProdotto = 3
	   THEN v.importo
	   ELSE 0
   END
),2) AS `Banane` ,ROUND(SUM(
   CASE
	   WHEN v.idProdotto = 4
	   THEN v.importo
	   ELSE 0
   END
),2) AS `Pesche` ,ROUND(SUM(
   CASE
	   WHEN v.idProdotto = 5
	   THEN v.importo
	   ELSE 0
   END
),2) AS `Kiwi`

ROUND(SUM(

CASE

WHEN v.idProdotto = 1

THEN v.importo

ELSE 0

END

),2) AS `Mele` ,ROUND(SUM(

CASE

WHEN v.idProdotto = 2

THEN v.importo

ELSE 0

END

),2) AS `Pere` ,ROUND(SUM(

CASE

WHEN v.idProdotto = 3

THEN v.importo

ELSE 0

END

),2) AS `Banane` ,ROUND(SUM(

CASE

WHEN v.idProdotto = 4

THEN v.importo

ELSE 0

END

),2) AS `Pesche` ,ROUND(SUM(

CASE

WHEN v.idProdotto = 5

THEN v.importo

ELSE 0

END

),2) AS `Kiwi`

Faccio notare che grazie a GROUP_CONCAT() vengono inserite le virgole tra le singole righe.

Andiamo a creare quindi una procedura come la seguente:

DELIMITER //

CREATE PROCEDURE pivot_vendite_casuali()
BEGIN
	SET @sql = NULL;
	SELECT
		GROUP_CONCAT(DISTINCT
			CONCAT(
				'ROUND(SUM(
					CASE
						WHEN v.idProdotto = ', p.id,'
						THEN v.importo
						ELSE 0
					END
				),2) AS `', p.nome,'` '
			)
		)
	INTO @sql
	FROM
		prodotti p;
		
	SET @sql = CONCAT('
		SELECT 
			a.nome, a.cognome, ROUND(SUM(v.importo),2) AS totale, 
			', @sql, '
		FROM
			agenti a LEFT JOIN vendite v ON v.idAgente = a.id
		GROUP BY
			a.id
	');

	PREPARE esecuzione FROM @sql;
	EXECUTE esecuzione;
	DEALLOCATE PREPARE esecuzione;
END//

DELIMITER ;

DELIMITER //

CREATE PROCEDURE pivot_vendite_casuali()

BEGIN

SET @sql = NULL;

SELECT

GROUP_CONCAT(DISTINCT

CONCAT(

'ROUND(SUM(

CASE

WHEN v.idProdotto = ', p.id,'

THEN v.importo

ELSE 0

END

),2) AS `', p.nome,'` '

)

INTO @sql

FROM

prodotti p;

SET @sql = CONCAT('

SELECT

a.nome, a.cognome, ROUND(SUM(v.importo),2) AS totale,

', @sql, '

FROM

agenti a LEFT JOIN vendite v ON v.idAgente = a.id

GROUP BY

a.id

');

PREPARE esecuzione FROM @sql;

EXECUTE esecuzione;

DEALLOCATE PREPARE esecuzione;

END//

DELIMITER ;

Eseguiamo, come prima, la procedura:

CALL pivot_vendite_casuali();

1	CALL pivot_vendite_casuali();

Otterremo il risultato cercato.

Dobbiamo usare una procedura per evitare di incorrere in una serie di incongruenze legate all’interrogazione del database.

DIfatti, usando il PHPMyAdmin e lanciando solamente l’istruzione:

SET @sql = NULL;
SELECT
	GROUP_CONCAT(DISTINCT
		CONCAT(
			'ROUND(SUM(
				CASE
					WHEN v.idProdotto = ', p.id,'
					THEN v.importo
					ELSE 0
				END
			),2) AS `', p.nome,'` '
		)
	)
INTO @sql
FROM
	prodotti p;
	
SET @sql = CONCAT('
	SELECT 
		a.nome, a.cognome, ROUND(SUM(v.importo),2) AS totale, 
		', @sql, '
	FROM
		agenti a LEFT JOIN vendite v ON v.idAgente = a.id
	GROUP BY
		a.id
');

PREPARE esecuzione FROM @sql;
EXECUTE esecuzione;
DEALLOCATE PREPARE esecuzione;

SET @sql = NULL;

SELECT

GROUP_CONCAT(DISTINCT

CONCAT(

'ROUND(SUM(

CASE

WHEN v.idProdotto = ', p.id,'

THEN v.importo

ELSE 0

END

),2) AS `', p.nome,'` '

)

INTO @sql

FROM

prodotti p;

SET @sql = CONCAT('

SELECT

a.nome, a.cognome, ROUND(SUM(v.importo),2) AS totale,

', @sql, '

FROM

agenti a LEFT JOIN vendite v ON v.idAgente = a.id

GROUP BY

a.id

');

PREPARE esecuzione FROM @sql;

EXECUTE esecuzione;

DEALLOCATE PREPARE esecuzione;

Si incorrere nel seguente errore:

Fatal error: Uncaught Error: Call to a member function getClauses() on null in D:\xampp\phpMyAdmin\vendor\phpmyadmin\sql-parser\src\Utils\Query.php:564 Stack trace: #0 D:\xampp\phpMyAdmin\vendor\phpmyadmin\sql-parser\src\Utils\Query.php(681): PhpMyAdmin\SqlParser\Utils\Query::getClause(NULL, NULL, 'ORDER BY', -1, false) #1 D:\xampp\phpMyAdmin\libraries\DisplayResults.php(1385): PhpMyAdmin\SqlParser\Utils\Query::replaceClause(NULL, NULL, 'ORDER BY', '') #2 D:\xampp\phpMyAdmin\libraries\DisplayResults.php(4376): PMA\libraries\DisplayResults->_getUnsortedSqlAndSortByKeyDropDown(Array, '') #3 D:\xampp\phpMyAdmin\libraries\sql.lib.php(1689): PMA\libraries\DisplayResults->getTable(Object(mysqli_result), Array, Array, false) #4 D:\xampp\phpMyAdmin\libraries\sql.lib.php(1980): PMA_getHtmlForSqlQueryResultsTable(Object(PMA\libraries\DisplayResults), './themes/pmahom...', NULL, Array, false, 6, 6, NULL, Object(mysqli_result), Array) #5 D:\xampp\phpMyAdmin\libraries\sql.lib.php(2199): PMA_getQueryResponseForResultsReturned(Object(my in D:\xampp\phpMyAdmin\vendor\phpmyadmin\sql-parser\src\Utils\Query.php on line 564

3. Utilizzo della tabella nel PHP

Leggiamo la tabella tramite il PHP. Per farlo utilizzerò la classe MySQLdb come in questo esercizio sul MySQL Cluster.

Creiamo nella root del nostro sito un file MySQLdb.php contenente il seguente codice:

<?php defined("IN_ESEMPIO") or die("Hacking attempt!");


class MySQLdb {

	private $mysql = NULL;

	function __construct($cfg = NULL) {
	
		if( $cfg != NULL ) {
			
			$this->config($cfg['dbname'],$cfg['dbhost'],$cfg['dbuser'],$cfg['dbport'],$cfg['dbpass']);
			
			$this->connect_db();
			
		}
	
	}
	
	function __destruct() {
	
		$this->close_db();
	
	}
	
	function config( $dbname, $dbhost, $dbuser, $dbport, $dbpass ) {
	
		$this->dbname = $dbname;
		$this->dbhost = $dbhost;
		$this->dbuser = $dbuser;
		$this->dbport = $dbport;
		$this->dbpass = $dbpass;
	
	}
	
	function select_db($dbname = NULL) {
	
		$this->mysql->select_db($dbname ? $dbname : $this->dbname);
	
	}
	
	function connect_db() {
	
		if( $this->mysql == NULL ) {
		
			$this->mysql = new mysqli($this->dbhost, $this->dbuser, $this->dbpass);
			$this->select_db();
			$this->errorHandler();
		
		}
	
	}
	
	function close_db() {
	
		if( $this->mysql != NULL ) {
		
			$this->mysql->close();
			$this->mysql = NULL;
		
		}
	
	}
	
	function errorHandler() {
	
		if( $this->mysql->connect_errno ) {
		
			printf( "Connect failed: %s\n", $this->mysql->connect_error );
			die();
			
		}
	
	}
	
	function clean( $val ) {
		return $this->mysql->real_escape_string($val);
	}
	
	function in( $query ) {
	
		$status = $this->mysql->query( $query );
		
		if( $this->mysql->insert_id ) 
			return $this->mysql->insert_id;
		else 
			return $status;
	
	}
	
	function out( $query , $autoReduce = true ) {
	
		$result = $this->mysql->query( $query );
			
		if( !is_object($result) )
			return array();
		
		while ($linea = $result->fetch_assoc()) {
		
			$i = $i !== NULL ? $i + 1 : 0;
			
			foreach( $linea as $key => $value ) {
				$dati[$i][$key] = $value;
			}
			
		}
		
		$result->close();
		
		if( count($dati) == 1 && $autoReduce )
			return $dati[0];
		
		return $dati;
	
	}


}

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

117

118

<?php defined("IN_ESEMPIO") or die("Hacking attempt!");

class MySQLdb {

private $mysql = NULL;

function __construct($cfg = NULL) {

if( $cfg != NULL ) {

$this->config($cfg['dbname'],$cfg['dbhost'],$cfg['dbuser'],$cfg['dbport'],$cfg['dbpass']);

$this->connect_db();

}

function __destruct() {

$this->close_db();

}

function config( $dbname, $dbhost, $dbuser, $dbport, $dbpass ) {

$this->dbname = $dbname;

$this->dbhost = $dbhost;

$this->dbuser = $dbuser;

$this->dbport = $dbport;

$this->dbpass = $dbpass;

}

function select_db($dbname = NULL) {

$this->mysql->select_db($dbname ? $dbname : $this->dbname);

}

function connect_db() {

if( $this->mysql == NULL ) {

$this->mysql = new mysqli($this->dbhost, $this->dbuser, $this->dbpass);

$this->select_db();

$this->errorHandler();

}

function close_db() {

if( $this->mysql != NULL ) {

$this->mysql->close();

$this->mysql = NULL;

}

function errorHandler() {

if( $this->mysql->connect_errno ) {

printf( "Connect failed: %s\n", $this->mysql->connect_error );

die();

}

function clean( $val ) {

return $this->mysql->real_escape_string($val);

}

function in( $query ) {

$status = $this->mysql->query( $query );

if( $this->mysql->insert_id )

return $this->mysql->insert_id;

else

return $status;

}

function out( $query , $autoReduce = true ) {

$result = $this->mysql->query( $query );

if( !is_object($result) )

return array();

while ($linea = $result->fetch_assoc()) {

$i = $i !== NULL ? $i + 1 : 0;

foreach( $linea as $key => $value ) {

$dati[$i][$key] = $value;

}

$result->close();

if( count($dati) == 1 && $autoReduce )

return $dati[0];

return $dati;

}

Creiamo adesso un file index.php, nella stessa posizione, contenente il seguente codice:

<?php

define("IN_ESEMPIO",true);

include("MySQLdb.php");

$mysql = new MySQLdb(array(
	"dbname" => "esempio_pivot",
	"dbuser" => "root",
	"dbhost" => "127.0.0.1",
	"dbpass" => "",
	"dbport" => 3306
));

$prodotti = $mysql->out("SELECT * FROM prodotti");
$dati = $mysql->out("CALL pivot_vendite_casuali()");

?>
<table>
	<thead>
		<tr>
			<th>Nome</th>
			<th>Cognome</th>
			<th>Totale</th>
			<?php foreach( $prodotti as $p ) { ?>
			<th><?php echo $p['nome'] ?></th>
			<?php } ?>
		</tr>
	</thead>
	<tbody>
		<?php foreach( $dati as $d ) { ?>
		<tr>
			<td><?php echo $d['nome'] ?></td>
			<td><?php echo $d['cognome'] ?></td>
			<td><?php echo $d['totale'] ?></td>
			<?php foreach( $prodotti as $p ) { ?>
			<td><?php echo $d[$p['nome']] ?></td>
			<?php } ?>
		</tr>
		<?php } ?>
	</tbody>
</table>

<?php

define("IN_ESEMPIO",true);

include("MySQLdb.php");

$mysql = new MySQLdb(array(

"dbname" => "esempio_pivot",

"dbuser" => "root",

"dbhost" => "127.0.0.1",

"dbpass" => "",

"dbport" => 3306

));

$prodotti = $mysql->out("SELECT * FROM prodotti");

$dati = $mysql->out("CALL pivot_vendite_casuali()");

<table>

<thead>

<tr>

<th>Cognome</th>

<th>Totale</th>

<?php foreach( $prodotti as $p ) { ?>

<?php } ?>

</tr>

</thead>

<tbody>

<?php foreach( $dati as $d ) { ?>

<tr>

<?php foreach( $prodotti as $p ) { ?>

<?php } ?>

</tr>

<?php } ?>

</tbody>

</table>

Vediamo in particolare come nel vettore $prodotti abbiamo l’elenco di tutti i prodotti e quindi delle colonne della pivot, che si trovano in $dati. Il risultato che otterremo sarà simile a questo:

Google Drive, upload bloccato su “meno di un minuto rimanente”

Problema: quando si cerca di caricare su Google Drive un documento di dimensioni consistenti (nel mio caso il problema si presentava con file di appena qualche MB) l’upload non riesce ad andare a buon fine e si blocca su un caricamento simile a quello nell’immagine, dando per tempo stimato “Meno di un minuto rimanente”

Soluzione: il problema può dipendere da Kaspersky Internet Security. Dalle Impostazioni di rete disattivare Scansione delle connessioni crittografate.

Più nel dettaglio ho notato che questo problema affligge tutti gli upload via web. Anche tentando il caricamento altrove, non solo su Google Drive, ed utilizzando qualsiasi browser (ho trovato indistintamente il medesimo problema sia su Chrome, Firefox che Edge).

In generale il problema si presenta come l’impossibilità di stimare il tempo di upload necessario, per cui qualunque barra di upload arriva subito alla fine e si blocca lì fino al completamente del medesimo.

Il problema dipende da Kaspersky Internet Security, nel mio caso ho verificato il problema sulla versione 20.0.14.1085 (j) con Windows 10 x64 Build 18362.

Per risolvere il problema aprire Kaspersky Internet Security > Impostazioni > Avanzate > Rete (Impostazioni di rete) > Scansione delle connessioni crittografate

Selezionare Non esaminare le connessioni crittografate.

Come utilizzare su Linux l’SFTP per trasferire e gestire i file da riga di commando

L’SFTP (SSH File Transfer Protocol) è un protocollo simile al FTP, utilizzato però per trasferire e manipolare file mediante SSH.

I comandi utilizzati sono simili a quelli del FTP. E’ molto utile specialmente quando si devono trasferire i file da un server all’altro.

1. Collegarsi in SFTP

Per collegarci al server remoto in SFTP digitiamo:

sftp nome_utente@indirizzo_server

1	sftp nome_utente@indirizzo_server

Per collegarci al server remoto su una porta diversa dalla 22

sftp -oPort=porta nome_utente@indirizzo_server

1	sftp -oPort=porta nome_utente@indirizzo_server

Una volta collegati ci verrà richiesta la password.

2. Comandi disponibili in SFTP

Per ottenere l’elenco di tutti i comandi disponibili, digitiamo:

help

help

L’output sarà simile al seguente:

bye	Quit sftp
cd path	Change remote directory to ‘path’
chgrp grp path	Change group of file ‘path’ to ‘grp’
chmod mode path	Change permissions of file ‘path’ to ‘mode’
chown own path	Change owner of file ‘path’ to ‘own’
df [-hi] [path]	Display statistics for current directory or filesystem containing ‘path’
exit	Quit sftp
get [-Ppr] remote [local]	Download file
reget remote [local]	Resume download file
help	Display this help text
lcd path	Change local directory to ‘path’
lls [ls-options [path]]	Display local directory listing
lmkdir path	Create local directory
ln [-s] oldpath newpath	Link remote file (-s for symlink)
lpwd	Print local working directory
ls [-1afhlnrSt] [path]	Display remote directory listing
lumask umask	Set local umask to ‘umask’
mkdir path	Create remote directory
progress	Toggle display of progress meter
put [-Ppr] local [remote]	Upload file
pwd	Display remote working directory
quit	Quit sftp
rename oldpath newpath	Rename remote file
rm path	Delete remote file
rmdir path	Remove remote directory
symlink oldpath newpath	Symlink remote file
version	Show SFTP version
!command	Execute ‘command’ in local shell
!	Escape to local shell
?	Synonym for help

3. Scaricare file e cartelle dal SFTP

Per scaricare un singolo file, nella posizione nella quale abbiamo aperto l’SFTP, digitiamo:

get nome_file.ext

1	get nome_file.ext

Per scaricare un file rinominandolo oppure scaricandolo in una posizione locale diversa:

get nome_file.ext nuovo_nome.ext

1	get nome_file.ext nuovo_nome.ext

Per scaricare una cartella digitiamo:

get -r cartella_remota

1	get -r cartella_remota

Per scaricare tutti i file della cartella corrente remota nella cartella locale attuale:

get -r *

get -r *

4. Caricare file e cartelle sul SFTP

Per caricare un file locale nella posizione remota, digitiamo:

put nome_file.ext

1	put nome_file.ext

Per caricare una cartella locale sul server remoto digitiamo:

put -r cartella_locale

1	put -r cartella_locale

Per caricare tutti i file della cartella locale sul server remoto:

put -r *

put -r *

5. Manipolare file e cartelle sul SFTP

Vedere tutto il contenuto di una cartella:

ls

Creare una cartella:

mkdir nome_cartella

1	mkdir nome_cartella

Rinominare un file:

rename vecchio_nome nuovo_nome

1	rename vecchio_nome nuovo_nome

Rimuovere un file:

rm nome_file.ext

1	rm nome_file.ext

Rimuovere una cartella:

rmdir cartella

1	rmdir cartella

[vba] Classe per leggere e scrivere su un database MySQL

A seguito del precedente articolo [excel] Aggiungere all’origine dati delle tabelle da un database MySQL ho deciso di integrare l’esercizio creando una classe per leggere e scrivere tramite VBA su un database MySQL.

Affinché la classe funzioni ricordiamoci di installare il connettore recuperabile sul sito ufficiale (Connector/ODBC 8.0.19). Inoltre ricordiamoci anche che per poter eseguire la connessione ADO abbiamo bisogno di importare l’opportuna libreria dai riferimenti. In questo modo:

Detto tutto questo possiamo creare una classe MySQL con le seguenti istruzioni all’interno:

Private cs As String

Private Sub Class_Initialize()

    cs = "DRIVER={MySQL ODBC 8.0 ANSI Driver}"

End Sub

Public Sub setConnectionString( _
                                ByVal driver As String, _
                                Optional ByVal server As String = "localhost", _
                                Optional ByVal database As String = "", _
                                Optional ByVal user As String = "", _
                                Optional ByVal pass As String = "")
                                
    cs = "DRIVER={" & driver & "}"
    
    If server <> "" Then
        cs = cs & ";SERVER=" & server
    End If
    
    If database <> "" Then
        cs = cs & ";DATABASE=" & database
    End If
    
    If user <> "" Then
        cs = cs & ";USER=" & user
    End If
    
    If pass <> "" Then
        cs = cs & ";PASSWORD=" & pass
    End If

End Sub

Public Function dbout(ByVal query As String) As Variant

    Dim conn As ADODB.Connection
    Dim rs As ADODB.Recordset
    
    Dim risultato() As Variant
    
    Set conn = CreateObject("ADODB.Connection")
    Set rs = CreateObject("ADODB.Recordset")
    
    conn.Open cs
    
    rs.Open query, conn
    
    ' mi calcolo le dimensioni del recordset
    righe = 0
    colonne = 0
    
    If Not rs.EOF Then
        Do
            righe = righe + 1
            colonne = rs.Fields.Count
            rs.MoveNext
        Loop Until rs.EOF
    End If
    
    ' mi riposiziono all'inizio
    rs.MoveFirst
    
    ReDim risultato(righe, colonne)
    
    ' leggo i dati nel risultato
    righe = 0
    If Not rs.EOF Then
        Do
            For j = 0 To rs.Fields.Count - 1
                risultato(righe, j) = rs(j)
            Next j
            righe = righe + 1
            rs.MoveNext
        Loop Until rs.EOF
    End If
    
    rs.Close
    
    conn.Close
    
    dbout = risultato

End Function

Public Function dbin(ByVal query As String)

    Dim conn As ADODB.Connection
    
    Set conn = CreateObject("ADODB.Connection")
    
    conn.Open cs
    
    conn.Execute query
    
    conn.Close

End Function

Public Function clear(ByVal testo As String) As String

    testo = Replace(testo, "\", "\\")
    testo = Replace(testo, "'", "\'")
    
    clear = testo

End Function

100

101

102

103

104

105

106

107

108

Private cs As String

Private Sub Class_Initialize()

cs = "DRIVER={MySQL ODBC 8.0 ANSI Driver}"

End Sub

Public Sub setConnectionString( _

ByVal driver As String, _

Optional ByVal server As String = "localhost", _

Optional ByVal database As String = "", _

Optional ByVal user As String = "", _

Optional ByVal pass As String = "")

cs = "DRIVER={" & driver & "}"

If server <> "" Then

cs = cs & ";SERVER=" & server

End If

If database <> "" Then

cs = cs & ";DATABASE=" & database

End If

If user <> "" Then

cs = cs & ";USER=" & user

End If

If pass <> "" Then

cs = cs & ";PASSWORD=" & pass

End If

End Sub

Public Function dbout(ByVal query As String) As Variant

Dim conn As ADODB.Connection

Dim rs As ADODB.Recordset

Dim risultato() As Variant

Set conn = CreateObject("ADODB.Connection")

Set rs = CreateObject("ADODB.Recordset")

conn.Open cs

rs.Open query, conn

' mi calcolo le dimensioni del recordset

righe = 0

colonne = 0

If Not rs.EOF Then

righe = righe + 1

colonne = rs.Fields.Count

rs.MoveNext

Loop Until rs.EOF

End If

' mi riposiziono all'inizio

rs.MoveFirst

ReDim risultato(righe, colonne)

' leggo i dati nel risultato

righe = 0

If Not rs.EOF Then

For j = 0 To rs.Fields.Count - 1

risultato(righe, j) = rs(j)

Next j

righe = righe + 1

rs.MoveNext

Loop Until rs.EOF

End If

rs.Close

conn.Close

dbout = risultato

End Function

Public Function dbin(ByVal query As String)

Dim conn As ADODB.Connection

Set conn = CreateObject("ADODB.Connection")

conn.Open cs

conn.Execute query

conn.Close

End Function

Public Function clear(ByVal testo As String) As String

testo = Replace(testo, "\", "\\")

testo = Replace(testo, "'", "\'")

clear = testo

End Function

Possiamo usare la classe nel modo seguente:

Sub MostraTotale()

    Dim db As New MySQL
    db.setConnectionString driver:="MySQL ODBC 8.0 ANSI Driver", server:="localhost", database:="miodb", user:="root"
    
    Dim righe As Variant
    
    ' inseriamo dei valori
    nome = "Mario"
    cognome = "D'Angelo"
    indirizzo = "Via delle prove"
    
    db.dbin "INSERT INTO `clienti`(`nome`, `cognome`, `indirizzo`) VALUES ('" & db.clear(nome) & "','" & db.clear(cognome) & "','" & db.clear(indirizzo) & "')"
    
    ' leggiamo diverse righe
    righe = db.dbout("SELECT * FROM clienti")
    
    For i = 0 To UBound(righe, 1) - 1
        Debug.Print righe(i, 0) & " - " & righe(i, 1)
    Next i
    
    ' leggiamo una singola riga
    righe = db.dbout("SELECT COUNT(*) FROM clienti")
    
    Debug.Print righe(0, 0)

End Sub

Sub MostraTotale()

Dim db As New MySQL

db.setConnectionString driver:="MySQL ODBC 8.0 ANSI Driver", server:="localhost", database:="miodb", user:="root"

Dim righe As Variant

' inseriamo dei valori

nome = "Mario"

cognome = "D'Angelo"

indirizzo = "Via delle prove"

db.dbin "INSERT INTO `clienti`(`nome`, `cognome`, `indirizzo`) VALUES ('" & db.clear(nome) & "','" & db.clear(cognome) & "','" & db.clear(indirizzo) & "')"

' leggiamo diverse righe

righe = db.dbout("SELECT * FROM clienti")

For i = 0 To UBound(righe, 1) - 1

Debug.Print righe(i, 0) & " - " & righe(i, 1)

Next i

' leggiamo una singola riga

righe = db.dbout("SELECT COUNT(*) FROM clienti")

Debug.Print righe(0, 0)

End Sub

Faccio notare che con il metodo clear possiamo aggiungere gli slash agli apici, presenti nel cognome.

[excel] Aggiungere all’origine dati delle tabelle da un database MySQL

Vediamo come collegare su Excel una tabella da un database MySQL. Per il mio esempio utilizzerò un database MySQL installato in locale grazie a XAMPP.

Chiameremo il database in questione torregatti e lo struttureremo nel modo seguente, con due tabelle: clienti e agenti

Il file di creazione del database si trova al seguente link: torregatti.zip

A questo punto dobbiamo procurarci l’opportuno Driver ODBC, se non lo abbiamo già installato. In particolare per il MySQL il driver è recuperabile sul sito ufficiale (Connector/ODBC 8.0.19).

Una volta installato e scaricato andiamo a verificare la stringa di connessione.

Premiamo il tasto WIN + R. Nella finestra Esegui digitiamo %systemdrive%\Windows\SysWoW64\odbcad32

In questo modo apriremo l’Amministrazione origine dati ODBC (32 bit). Nel caso si debba aprire quella a 64 bit sarà sufficiente digitare odbcad32 oppure %systemdrive%\Windows\System32\odbcad32

Nella scheda Driver individuiamo il driver che ci interessa, in questo modo:

Nel mio caso è il MySQL ODBC 8.0 ANSI Driver

Fatto questo apriamo Excel e andiamo Dati > Recupera dati > Da altre origini > Da ODBC

Nella finestra Da ODBC selezioniamo Nome origine dati (DNS) come Nessuno e apriamo le Opzioni avanzate. Alla Stringa di connessione inseriamo la seguente stringa: DRIVER={MySQL ODBC 8.0 ANSI Driver};SERVER=localhost;

Qualora il nostro database non si trovasse in locale sostituiamo localhost con l’indirizzo del database. Fatto tutto questo premiamo su OK. A questo punto, se è la prima volta che effettuiamo la connessione, ci verranno chiesto nome utente e password. Selezionare la voce Database e scegliere come username root e lasciare il campo password vuoto. Queste impostazioni dipendono, nel mio caso, dal fatto che sto utilizzando XAMPP. In altre circostanze dovrò inserire nome utente e password appropriati.

Fatto questo possiamo selezionare la tabella alla quale ci vogliamo connettere.

Nel mio caso voglio prelevare la tabella agenti:

Una volta selezionata la tabella che ci interessa premere su Carica.

Possiamo caricare anche delle viste in MySQL, come nel mio caso la vista clienti_agenti. Se tutto è andato bene vedremo un risultato simile a questo:

[VBA] Tema musicale di Tetris eseguito da una macro in VBA su Excel

Ispirato da un articolo che ho trovato online sull’argomento (Tetris Theme Song Using Processing) ho deciso di riprodurre il medesimo risultato in VBA su Excel, riproducendo quindi il tema musicale di Tetris tramite una macro per Excel.

Anzitutto importiamo dal kernel32 di Windows la funzione appropriata per eseguire un Beep, riproducendo quindi un suono, nel modo seguente:

Public Declare PtrSafe Function Beep Lib "kernel32" _
   (ByVal dwFreq As Long, _
    ByVal dwDuration As Long) As Long

Public Declare PtrSafe Function Beep Lib "kernel32" _

(ByVal dwFreq As Long, _

ByVal dwDuration As Long) As Long

La funzione accetta due argomenti: frequenza in Hz e durata in ms. Quindi per eseguire un 1 beep a 800Hz di 0,5 secondi, dovremo scrivere: Beep 800, 500

Detto questo voglio anzitutto riconvertire le funzioni dell’esempio nel link in VBA, creando un vettore di frequenze ed uno dei tempi, nel modo seguente:

Sub Gioca()

    Dim frequenze As Variant
    frequenze = Array(659.25511, 493.8833, 523.25113, 587.32954, 523.25113, 493.8833, 440#, 440#, _
    523.25113, 659.25511, 587.32954, 523.25113, 493.8833, 523.25113, 587.32954, _
    659.25511, 523.25113, 440#, 440#, 440#, 493.8833, 523.25113, 587.32954, _
    698.45646, 880#, 783.99087, 698.45646, 659.25511, 523.25113, 659.25511, _
    587.32954, 523.25113, 493.8833, 493.8833, 523.25113, 587.32954, 659.25511, _
    523.25113, 440#, 440#)
    
    Dim durata As Variant
    durata = Array(406.25, 203.125, 203.125, 406.25, 203.125, 203.125, 406.25, 203.125, _
    203.125, 406.25, 203.125, 203.125, 609.375, 203.125, 406.25, 406.25, _
    406.25, 406.25, 203.125, 203.125, 203.125, 203.125, 609.375, 203.125, _
    406.25, 203.125, 203.125, 609.375, 203.125, 406.25, 203.125, 203.125, _
    406.25, 203.125, 203.125, 406.25, 406.25, 406.25, 406.25, 406.25)
    
    For i = LBound(frequenze) To UBound(frequenze)
        Beep frequenze(i), durata(i)
    Next i

End Sub

Sub Gioca()

Dim frequenze As Variant

frequenze = Array(659.25511, 493.8833, 523.25113, 587.32954, 523.25113, 493.8833, 440#, 440#, _

523.25113, 659.25511, 587.32954, 523.25113, 493.8833, 523.25113, 587.32954, _

659.25511, 523.25113, 440#, 440#, 440#, 493.8833, 523.25113, 587.32954, _

698.45646, 880#, 783.99087, 698.45646, 659.25511, 523.25113, 659.25511, _

587.32954, 523.25113, 493.8833, 493.8833, 523.25113, 587.32954, 659.25511, _

523.25113, 440#, 440#)

Dim durata As Variant

durata = Array(406.25, 203.125, 203.125, 406.25, 203.125, 203.125, 406.25, 203.125, _

203.125, 406.25, 203.125, 203.125, 609.375, 203.125, 406.25, 406.25, _

406.25, 406.25, 203.125, 203.125, 203.125, 203.125, 609.375, 203.125, _

406.25, 203.125, 203.125, 609.375, 203.125, 406.25, 203.125, 203.125, _

406.25, 203.125, 203.125, 406.25, 406.25, 406.25, 406.25, 406.25)

For i = LBound(frequenze) To UBound(frequenze)

Beep frequenze(i), durata(i)

Next i

End Sub

Faccio notare che per la conversione dello spartito (che si può trovare al seguente link) è stata usata una tabella di conversione simile a questa.

Detto questo voglio riportare la medesima tabella di conversione in Excel, in un foglio che chiameremo NOTE. Il risultato che otterremo sarà simile a questo:

A questo punto voglio creare un altro foglio, chiamato SPARTITO, dove creeremo tre colonne, una nella quale scegliere le note e l’altra dove inserire i tempi come ottavi di nota.

A questo punto associamo al pulsante Suona la seguente macro:

Sub Suona()

    Dim ottavo As Double
    ottavo = 1000 * 13 / 8 / 8 ' 1000 * 13 secondi / 8 / 8 = 203,125 ms

    Dim frequenze As Variant
    frequenze = Worksheets("SPARTITO").Range("FREQUENZE").Value
    
    Dim tempi As Variant
    tempi = Worksheets("SPARTITO").Range("TEMPI").Value
    
    For i = LBound(frequenze) To UBound(frequenze)
        Beep CDbl(frequenze(i, 1)), CDbl(tempi(i, 1) * ottavo)
    Next i

End Sub

Sub Suona()

Dim ottavo As Double

ottavo = 1000 * 13 / 8 / 8 ' 1000 * 13 secondi / 8 / 8 = 203,125 ms

Dim frequenze As Variant

frequenze = Worksheets("SPARTITO").Range("FREQUENZE").Value

Dim tempi As Variant

tempi = Worksheets("SPARTITO").Range("TEMPI").Value

For i = LBound(frequenze) To UBound(frequenze)

Beep CDbl(frequenze(i, 1)), CDbl(tempi(i, 1) * ottavo)

Next i

End Sub

Le frequenze vengono recuperate dal foglio NOTE.

Da qui si può scaricare il file excel comprensivo di macro: excel VBA – note musicali – tema tetris

Codici sala controllo della serra, Resident Evil 2 Remake [Leon 2nd run]

Nella sala di controllo della serra si devono inserire due codici per sbloccare rispettivamente la botola nella serra e il miscelatore di soluzioni.

Per il miscelatore il codice è il seguente:

Mentre per aprire la botola bisognerà inserire questo:

Impostazione percorso della metropolitana in Resident Evil 3 Remake

Per risolvere l’enigma della metropolitana, in Resident Evil 3 Remake, bisogna anzitutto riattivare la corrente elettrica (questo passaggio è abbastanza semplice, basta abbattere i ragni che infestano la centrale elettrica).

Una volta riattivati i generatori per l’energia elettrica della centrale, possiamo tornare alla sala di controllo della metropolitana di Raccoon City. Qui dovremo impostare il percorso, seguendo la mappa delle stazioni possibili. La combinazione corretta è quella dell’immagine di seguito:

La combinazione in particolare è: FA 02 – RA 03 – SA 02 – FO 01

Le sigle rappresentano le iniziali delle varie stazioni sul percorso. Una volta attivate correttamente verrà data conferma del percorso sul pannello di controllo frontale.