PHP: estrarre un ramo da un albero a liste di adiacenza senza ricorsione

Esempio di struttura ad albero
Esempio di struttura ad albero
Supponiamo di avere un archivio con una struttura ad albero di tipo a liste di adiacenza. Ogni record sarà necessariamente caratterizzato da un identificativo univoco e da un attributo che serve a riconoscere il proprio genitore. Per esempio, nella figura accanto, l’elemento 4 avrà id = 4, e parentId = 1, l’elemento 9 avrà id = 9, e parentId = 4, e così via. Questo modello offre alcuni vantaggi, che sono principalmente la semplicità della struttura e la velocità negli inserimenti. D’altro canto, risultano piuttosto onerosi processi come l’estrazione o la cancellazione di interi rami. Le query risultano quindi, complesse e spesso ricorsive.

Sebbene l’utilizzo di una struttura di tipo nested tree model ideata da Joe Celko sia quasi sempre preferibile, alle volte è necessario cimentarsi con gli algoritmi per la manipolazione delle liste di adiacenza.

Se si deve salire da una foglia fino alla root, non è troppo difficile, basta risalire, attraverso il parentId, il genitore finché parentId = NULL. Il procedimento naturalmente ricorsivo, per questo motivo spesso si indica l’attributo parentId anche con il nome di ricorsore.

Il gioco si fa duro quando si deve estrarre un ramo a partire da un certo genitore… ma come disse John Belushi: “When the going gets tough, the toughs get going!”. Vediamo come fare evitando di farci del male con stored procedure, query ricorsive o tabelle temporanee.
La soluzione che ho adottato prende spunto dagli algoritmi di attraversamento dei grafi: Breadth-first search e Depth-first search. Questi sono procedimenti non ricorsivi che utilizzano stack di tipo FIFO e LIFO per l’esplorazione dei nodi.

L’operazione di fetching da un archivio che rappresenta l’albero della figura in alto produrrà un’array di questo tipo:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
$arrTest = array (
                    0 => array ("id"=>1, "parent_id"=>NULL),
                    1 => array ("id"=>2, "parent_id"=>1),
                    2 => array ("id"=>3, "parent_id"=>1),
                    3 => array ("id"=>4, "parent_id"=>1),
                    4 => array ("id"=>5, "parent_id"=>2),
                    5 => array ("id"=>6, "parent_id"=>2),
                    6 => array ("id"=>7, "parent_id"=>3),
                    7 => array ("id"=>8, "parent_id"=>3),
                    8 => array ("id"=>9, "parent_id"=>4),
                    9 => array ("id"=>10, "parent_id"=>4),
                    10 => array ("id"=>11, "parent_id"=>4),
                    11 => array ("id"=>12, "parent_id"=>5),
                    12 => array ("id"=>13, "parent_id"=>5),
                    13 => array ("id"=>14, "parent_id"=>6),
                    14 => array ("id"=>15, "parent_id"=>7),
                    15 => array ("id"=>16, "parent_id"=>7),
                    16 => array ("id"=>17, "parent_id"=>9),
                    17 => array ("id"=>18, "parent_id"=>9),
                    18 => array ("id"=>19, "parent_id"=>10),
                    19 => array ("id"=>20, "parent_id"=>11),
                    20 => array ("id"=>21, "parent_id"=>11)
                  );

Ammettiamo ora di voler estrarre l’intero ramo che ha come radice l’elemento 4. La funzione seguente utilizza un nuovo array che viene costruito utilizzando come chiave gli id e come valori i parent_id. Questo ci permette di utilizzare la funzione PHP: array_keys() per le ricerche dei figli, che è piuttosto efficiente.

Ecco il codice:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
function getBranch ($arr,$pk,$recursor,$idBranch)
{
  $pksArr = array();
  foreach ($arr as $rec) {
    $pksArr[$rec[$pk]]=$rec[$recursor];
  }
  $branchIds = array($idBranch);
  $i=0;
  while ($i<count($branchIds)) {
    $newKeys = array_keys($pksArr,$branchIds[$i]);
    if (!empty($newKeys)) {
      foreach ($newKeys as $newKey){
        array_push($branchIds, $newKey);
      }
    }
    ++$i;
  }
  $res = array();
  foreach ($arr as $child) {
    if (in_array($child[$pk], $branchIds)) {
      $res[] = $child;
    }
  }
  return $res;
}

Certo, il codice di questa funzione non è troppo ottimizzato: utilizza tre array (anche se $pksArr e $branchIds sono monodimensionali e quindi “leggeri”) ed esegue tre cicli. In particolare il ciclo (righe 19-23) è piuttosto lento, ma è necessario se si vuole recuperare tutti gli attributi degli elementi estratti oltre agli indispensabili id già presenti in $branchIds. Dalle prove che ho fatto, per archivi fino a 10-15000 records risulta comunque abbastanza efficiente.

Questo è il risultato dell’estrazione del ramo con radice 4 (si nota che l’andamento è di tipo “breadth-first-search” ricerca in ampiezza, seguendo i colori: rosso-verde-blu-giallo):

Array
(
    [0] => Array
        (
            [id] => 4
            [parent_id] => 1
        )

    [1] => Array
        (
            [id] => 9
            [parent_id] => 4
        )

    [2] => Array
        (
            [id] => 10
            [parent_id] => 4
        )

    [3] => Array
        (
            [id] => 11
            [parent_id] => 4
        )

    [4] => Array
        (
            [id] => 17
            [parent_id] => 9
        )

    [5] => Array
        (
            [id] => 18
            [parent_id] => 9
        )

    [6] => Array
        (
            [id] => 19
            [parent_id] => 10
        )

    [7] => Array
        (
            [id] => 20
            [parent_id] => 11
        )

    [8] => Array
        (
            [id] => 21
            [parent_id] => 11
        )

)
// Tempo di esecuzione: 0.2388 msecs

Conclusioni

Personalmente, ho una certa ritrosia nell’uso di funzioni ricorsive, per questo ho perso del tempo a cercare soluzioni alternative e cicliche, ciò non toglie che, ad esempio per recuperare il percorso di una foglia (risalendo quindi verso la root), la ricorsione sia la soluzione più adatta.

Riferimenti ed approfondimenti:

PHP: un datagrid XHTML valido con paginazione per applicazioni web

Esempio di datagrid
Esempio di datagrid
Questo datagrid è un aggiornamento di quello presentato in questo post. Ho apportato alcune sostanziali modifiche per renderlo più flessibile ed accessibile, pur mantenendo le stesse prerogative che mi ero imposto inizialmente.
La modifica più urgente era quella di implementare un sistema di paginazione. Questo ha comportato inevitabilmente il cambiamento della modalità di passaggio dei dati al costruttore della classe. Mentre prima era sufficiente fornire il riferimento al risultato di una query, adesso è necessario passare il link alla connessione, il nome del database ed infine la query in formato stringa.

Le caratteristiche iniziali, che sono state ulteriormente migliorate, sono:

  • XHTML valido
  • Personalizzabile tramite fogli stile CSS
  • colonna aggiuntiva di link per azioni tipo “modifica”
  • Aspetto gradevole e alternanza di colorazione delle singole righe
  • Compatibile sia con PHP4 che con PHP5

Le nuove caratteristiche:

  • Semplice sistema di paginazione nel <tfoot> della tabella. Il numero di record per pagina da visualizzare vengono impostati come parametro del costruttore, default=0
  • Autoriconoscimento della o delle chiave primarie. Nel caso in cui l’autoriconoscimento fallisca, è possibile impostarle manualmente come parametro del costruttore
  • Il link nella colonna aggiuntiva può contenere una querystring, in questo caso il link alla chiave primaria viene appeso con “&” altrimenti con “?”
  • Possibilità di utilizzare il datagrid in una pagina che già contiene una querystring. In questo caso è necessario passare la querystring esistente come parametro al costruttore.
  • Possibilità di impostare numerose proprietà dei tag HTML

I parametri del costruttore:

  1. $link (obbligatorio) – riferimento alla connessione al db
  2. $db (obbligatorio) – stringa con il nome del database
  3. $query (obbligatorio) – stringa contentente la query di tipo SELECT
  4. $recPerPage=0 – Numero di record per pagina. Default=0 (tutti i record senza paginazione)
  5. $pk = NULL – Nome della chiave primaria (se più di una, separate da virgola) da immettere manualmente, se NULL (default) il riconoscimento è automatico
  6. $qs = NULL – Stringa contenente l’eventuale querystring associata alla pagina in cui viene pubblicato il datagrid.
Elenco dei metodi:
NomeTipoDefaultNote
setPkIsVisible()booleanfalseDetermina se le colonne relative alla chiave primaria sono visibili o no
setLinkPage()stringNULLImposta il link alla pagina da richiamare per le modifiche (Riferimento alla chiave primaria). Se NULL, la colonna aggiunitva non viene visualizzata.
setLinkLabel()string“Edit”Imposta il testo da visualizzare nella colonna dei link. Può contenere codice HTML per caricare un’icona.
setLinkTitle()string“Edit”Imposta il titolo del tag <a> nella colonna dei link.
setPrev()string“prev”Imposta il testo da visualizzare per la paginazione indietro. Può contenere codice HTML per caricare un’icona.
setNext()string“next”Imposta il testo da visualizzare per la paginazione in avanti. Può contenere codice HTML per caricare un’icona.
setSummaryTable()string“Data table”Imposta il testo per la descrizione “summary” della tabella.
setCaptionTable()string“Table”Imposta il testo per l’intestazione della tabella.
makeTable()Stampa la tabella

Un esempio di utilizzo:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
// Database connection params
$host = 'localhost';
$user = 'root';
$password = '';
$database = 'prova';
$query = 'select * from es_libri';
// open the connection to the db server
$link = mysql_connect($host,$user,$password);
include ('datagrid.class.php');
// datagrid class with some options
$mydatagrid = &amp; new datagrid($link,$database,$query,3); // 3 rec per page
$mydatagrid->setLinkPage("test.php");
$mydatagrid->setLinkLabel("<img src='./mod.gif' alt='edit' />");
$mydatagrid->setLinkTitle("edit");
$mydatagrid->setPrev("<img src='./prev.gif' alt='previous' />");
$mydatagrid->setNext("<img src='./next.gif' alt='next' />");
$mydatagrid->setPkIsVisible(true);
$mydatagrid->setSummaryTable("Elenco dei libri in biblioteca del settore informatica");
$mydatagrid->setCaptionTable("Elenco libri informatica");
$mydatagrid->makeTable();

Per vedere l’esempio, cliccare qui.
Questo, invece è il pacchetto contenente la classe, il file di esempio, il foglio stile, le gif e l’sql per la creazione della tabella “es_libri”, (da creare in un db chiamato “prova”).

Note:

Per quanto abbia fatto diverse prove, non posso dire di aver coperto tutti i possibili errori, fate qualche prova prima di metterlo in produzione!.

Conclusioni:

Per il futuro mi riprometto di aggiungere la caratteristica di autoriconoscimento dei tipi dei campi e la formattazione automatica con allineamento a destra per i numeri

Riferimenti ed approfondimenti:

PHP e MySQL: una classe per stampare alberi gerarchici con liste annidate

Lista Modified Preorder Tree TraversalLa soluzione denominata “Modified Preorder Tree Traversal”, che mal si traduce in italiano, è probabilmente la soluzione più efficace per immagazzinare dati di tipo gerarchico in un database. La soluzione tipica alternativa è il modello a liste di adiacenza. Quest’ultimo modello soffre di alcune limitazioni e debolezze, come ad esempio la particolar cura che deve essere presa nella cancellazione di sotto-alberi che può portare a potenziali figli orfani, e la difficoltà di stesura delle query SQL.
In questo articolo mi riferirò quindi esclusivamente al modello Modified Preorder Tree Traversal. Questo modello è molto ben descritto in questo articolo di Mike Hillyer sul sito ufficiale MySQL nel paragrafo denominato: “Nested Set Model”. Il concetto chiave che sta alla base del modello è di non considerare più la struttura gerarchica come un insieme di nodi e di linee, ma come una serie di insiemi e sottoinsiemi annidati. Il concetto sarà più chiaro dopo aver letto l’articolo e la figura esplicativa della struttura, riportata qui sotto.
treeGraphLe varie query necessarie a gestire il database sono state riprese e tradotte in funzioni PHP da Daevid Vincent in questo thread su questo forum. Il lavoro di Daevid si è rivelato davvero prezioso, e mi ha fatto risparmiare davvero molto tempo, però non ero completamente soddisfatto del sistema di stampa HTML dell’albero. Infatti la funzione utilizza “blank space” per la rappresentazione dell’indentazione, mentre sarebbe più corretto ed elegante utilizzare liste annidate di tipo <UL>. Ho fatto quindi alcune modifiche alla funzione per raggiungere questo scopo.

Queste sono le query per creare il database campione e popolarlo con alcuni dati di esempio:

1
2
3
4
5
6
7
8
9
10
11
12
CREATE TABLE nested_category (
 category_id INT AUTO_INCREMENT PRIMARY KEY,
 name VARCHAR(20) NOT NULL,
 lft INT NOT NULL,
 rgt INT NOT NULL
);
 
INSERT INTO nested_category
VALUES(1,'ELECTRONICS',1,20),(2,'TELEVISIONS',2,9),(3,'TUBE',3,4),
(4,'LCD',5,6),(5,'PLASMA',7,8),(6,'PORTABLE ELECTRONICS',10,19),
(7,'MP3 PLAYERS',11,14),(8,'FLASH',12,13),
(9,'CD PLAYERS',15,16),(10,'2 WAY RADIOS',17,18);

E questo il codice della classe modificata:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
class preorderModelTree {
 
  var $dbConn;
 
  function preorderModelTree ($resConn) {
    $this->dbConn = $resConn;
  }
 
  function dbQuery($sql){
    $rs = @mysql_query($sql,$this->dbConn);
    return $rs;
  }
 
  function fetchRow($rs) {
    if ( $row = @mysql_fetch_array($rs,MYSQL_ASSOC) ) {
        return $row;
    } else {
        return false;
    }
  }
 
  function print_nested_tree_list($pageLink = "?id=", $id = 1)
  {
    $sth = $this->dbQuery("SELECT node.category_id as id, node.name AS name,
    (COUNT(parent.name) - 1) as indent
    FROM nested_category AS node, nested_category
    AS parent
    WHERE node.lft BETWEEN parent.lft
    AND parent.rgt  
    GROUP BY node.name
    ORDER BY node.lft");
 
    if ($sth) {
    $oldRowIndent = -1;
 
    while($row = $this->fetchRow($sth))
    {
      if($row['id'] == $id){
        echo("<ul><li>". $row['name']);
      }
      elseif($row['indent']>$oldRowIndent){
        echo("<ul><li><a href='".$pageLink.$row['id']."'>". $row['name'] ."</a>");
      }
      elseif($row['indent']==$oldRowIndent){
        echo("</li><li><a href='".$pageLink.$row['id']."'>". $row['name'] ."</a>");
      }
      else {
        echo("</li>");
        echo str_repeat("</ul></li>", $oldRowIndent-$row['indent']);
        echo("<li><a href='".$pageLink.$row['id']."'>". $row['name'] ."</a>");
      }
      $oldRowIndent = $row['indent'];
    }
    echo("</li>");
    echo str_repeat("</ul></li>", $oldRowIndent);
    echo("</ul>");
   }    
  }  
}

Come si evince dal codice, la chiave di tutto è la query ben fatta. Il campo calcolato indent fornisce la misura dell’indentazione che è rappresentata dal numero di gusci in cui la categoria è compresa. Tramite questo valore è possibile costruire il corretto annidamento di elementi <ul> e <li>.

Il pacchetto contenente la classe, il codice sql e un file di esempio è scaricabile qui.

Un ringraziamento particolare per le “imbeccate” ed i consigli, all’amico e collega Alessandro Scoscia.

Conclusioni:

Un ulteriore miglioramento della rappresentazione dell’albero gerarchico potrebbe venire dall’utilizzo di JQuery. Tramite questa libreria javascript si potrebbe implementare un’azione tipo toggle con la quale mostrare o nascondere i sotto-alberi selezionati.

Riferimenti ed approfondimenti:


evilripper ha scritto:

preziossimo articolo!!
grazie per tutte le informazioni!!
ciao

P4A framework: utilizzare il widget P4A Google Maps per implementare uno store locator

Questo post ha più di 1 anno. Il contenuto potrebbe essere obsoleto, non più completamente accessibile o mancante di alcune informazioni.

P4A esempio di Store LocatorEcco un esempio di utilizzazione del widget P4A Google Maps per creare una mappa di riferimenti (negozi, magazzini, ecc) che siano compresi in un certo raggio di distanza in Km, a partire da un indirizzo prestabilito.
Ho cercato di utilizzare il widget di Alberto Galanti senza effettuare nessuna modifica al codice originale. Ho scelto, quindi di implementare un metodo che fornisce la georeferenza della località che funge da centro, per poi effettuare, attraverso una query, la selezione degli indirizzi compresi nel raggio di distanze prestabilito.
L’esempio utilizzato, per comodità, è lo stesso indicato dalla guida di Google Maps in questo articolo.

I passi principali prima di procedere:

  • Scaricare il widget P4A Google Maps qui
  • Creare un archivio di riferimenti che abbiano un indirizzo nel formato accettato da Google Maps (Es.: “Piazza Venezia, Roma, Italia”)
  • Georeferenziare tutti gli indirizzi (trovare latitudine e longitudine)

Utilizzando MySQL, la struttura dell’archivio suggerita da Google è questa:

1
2
3
4
5
6
7
8
CREATE TABLE `markers` (
  `id` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(60) NOT NULL,
  `address` VARCHAR(80) NOT NULL,
  `lat` FLOAT(10,6) NOT NULL,
  `lng` FLOAT(10,6) NOT NULL,
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1;

Per comodità potete scaricare l’sql completo dei dati di esempio di Google qui

Nel caso non si conoscano già le coordinate dei riferimenti, è possibile utilizzare il metodo suggerito in questo articolo sempre da Google. Questo sistema permette di aggiornare direttamente i dati di un database MySQL con le coordinate fornite da Google Maps per tutti gli indirizzi memorizzati.

La selezione dei record che sono compresi in un reggio di distanza in Km prefissato, avviene tramite una query che utilizza la formula della triangolazione sferica:
cos p = cos a cos b + sen a sen b cos φ,
per maggiori dettagli potete leggere questo post.

Ecco la query in pratica:

1
2
3
4
5
6
7
8
SELECT address, name, lat, lng, 
( 6371 * acos( cos( radians('Center_latitude')) * cos( radians( lat )) 
* cos( radians( lng ) - radians('Center_longitude')) 
+ sin( radians('Center_latitude'))
* sin( radians( lat )))) AS distance
FROM markers
HAVING distance &lt; 'Radius'
ORDER BY distance

dove 'Center_latitude' è la latitudine del centro, 'Center_longitude' è la longitudine del centro e 'Radius' è il raggio in km dal centro.

Ed ecco dunque, il codice della maschera P4A:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
class GoogleMap extends P4A_Mask
{
  function GoogleMap()
  {
    parent::P4A_Mask();
    $this->setTitle("Google Map Store Locator Test");
    // Frame
    $this->build("p4a_frame", "frame");
    $this->frame->setWidth(800);
    //la chiave per localhost è disponibile nel file p4a_map.php
    $coordArray = $this->getLocationCoordinates("Mountain View, CA", 
                  "Inserire_chiave_Google"); 
    $radius = 12;
    // DB Source
    $this->build("p4a_db_source", "source");
    $query = "SELECT address, name, lat, lng, 
     CONCAT_WS('<br />', name, address) AS info,
     (6371 * acos(cos(radians('$coordArray[2]')) * cos(radians(lat))
     * cos( radians(lng) - radians('$coordArray[3]')) 
     + sin( radians('$coordArray[2]'))
     * sin( radians(lat)))) AS distance
     FROM markers
     HAVING distance < '$radius'
     ORDER BY distance";
    $this->source->setQuery($query);
    $this->source->setPk("id");
    $this->source->load();
    // map
    $map =&amp; $this->build("p4a_map","mappa");
    $map->setCenter("Mountain View, CA");
    $map->setZoom(10);
    $map->setWidth(750);
    $map->setHeight(500);
    // markers from datasource
    $map->setSourceAddressField("address");
    //$map->setSourceDescriptionField("distance");
    $map->setSourceDescriptionField("info");
    $map->setSource($this->source);
    // Layout
    $this->frame->anchorCenter($map);
    // Display
    $this->display("main", $this->frame);
   }
 /*
  * Return the coordinates of the location.
  * @param $location		valid Google Maps address of a location.
  * @param $googleMapKey		key for accessing Google Maps.	 
  * @return array  element[0] = status, element[2] = lat, element[3] = long
  */
 function getLocationCoordinates($location, $googleMapKey)
 {
  $base_url = "http://maps.google.com/maps/geo?output=csv&amp;key=".$googleMapKey;
  $request_url = $base_url."&amp;q=".urlencode($location);
  $csv = file_get_contents($request_url);
  if ($csv) {
    $csvSplit = split(",", $csv);
  }
  else{
    $csvSplit = array();
  }
    return $csvSplit;
  }
}

Note:

La query crea la sorgente dati per i markers, che vengono evidenziati automaticamente dalla classe p4a_map.
Al posto delle info, potete visualizzare la distanza di ogni marker dal centro (nell’esempio: “Mountain View, CA”) utilizzando l’istruzione: $map->setSourceDescriptionField("distance");.
Il metodo getLocationCoordinates() recupera le coordinate del centro per poter poi comporre la query.
La chiave per l’accesso a Google Map è quella valida per un server tipo “localhost” e la trovate all’interno del file: p4a_map.php che si trova nella directory libraries. Per avere quella per un dominio pubblico è necessario farla generare a Google Maps qui.
Non ho fatto controlli di errore, ad esempio per quanto riguarda la risposta del metodo getLocationCoordinates() sarebbe opportuno controllare che l’array non sia vuoto e anche lo status della risposta di Google Maps, e si dovrebbe anche limitare il risultato della query al numero di markers massimo gestiti dal widget (17 se non ricordo male…).

Download

Il widget con la maschera sopra descritta è scaricabile qui

Conclusioni:

Il risultato è già soddisfacente, anche se un po’ grezzo, ma credo ci siano molti margini di miglioramento per questo tipo di utilizzo di Google Maps in P4A. Spero di avere tempo per affinare ulteriormente sia questo programma che il widget, per renderlo utilizzabile anche per idee diverse! Suggerimenti e consigli sono molto ben accetti!!

Riferimenti ed approfondimenti:

MySQL: un semplice script batch DOS per il backup

MySQL-logo.jpgUn aspetto importante nella distribuzione di web application per piccole e medie aziende è il backup degli archivi. Nel caso, piuttosto frequente per questo target, che il server MySQL sia locale (all’interno della LAN) è possibile utilizzare l’utility mysqldump.exe distribuita insieme al pacchetto MySQL. Questa piccola applicazione client fornisce un output direttamente in SQL oppure in altri formati standard come ad esempio CSV e XML. Nel caso in cui le tabelle del database siano esclusivamente di tipo MyISAM, si può anche prendere in considerazione l’utilizzo di un’altra utility fornita insieme a MySQL che si chiama mysqlhotcopy che è uno script in PERL decisamente più veloce, ma inadatto a database di tipo innoDB.

Ci sono tre modi di lanciare mysqldump:

mysqldump [options] dbname [tables]
mysqldump [options] --databases db_name1 [db_name2 db_name3]
mysqldump [options] --all-databases

Nel primo caso è possibile specificare quali tabelle di un database processare per il backup, nel secondo è possibile specificare un certo numero di database mentre nell’ultimo caso tutti i database presenti vengono processati per il backup.

I parametri opzionali sono numerosi, quello che mi sembra più utile è --opt che raggruppa insieme una serie di opzioni che sono utilizzate frequentemente:

  • --add-drop-table
  • --add-locks
  • --create-options
  • --disable-keys
  • --extended-insert
  • --lock-tables
  • --quick
  • --set-charset

Nella sezione “User comments” del manuale di MySQL on-line relativa a MySQLDump viene segnalato un interessante script per automatizzare la procedura di backup su macchine dotate di sistema operativo Windows scritta in puro batch script DOS. Questo script è davvero completo e oltre al backup di tutti i database implementa anche la compressione dei file generati e l’invio di una mail all’amministratore del RDBMS

Poiché in molti casi si rende necessario solo il backup di uno o due database e non è necessaria né la compressione né l’invio delle mail, ho semplificato lo script lasciando solo le sue funzioni fondamentali che sono:

  • Creazione dei file sql per ogni db presente nella lista con un prefisso che indica la data e l’ora del backup
  • Creazione di un unico file di log che indica la data e l’ora di inizio e di fine backup e l’elenco dei database processati

Ecco il codice:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
@echo off
:: ---- SETTINGS ----------------------------------------
:: db user name
set dbuser=root
:: db user password
set dbpass=
:: db list (separate each db name with a blank space)
set dblist=magazzino p4a_products_catalogue
:: backup directory 
set bkupdir=C:\dbBackups\files
:: MySQL folder
set mysqldir=C:\wamp\mysql
:: -----------------------------------------------------
:: Do not edit here
for /f %%i in ('doff.exe yyyymmdd_hhmiss') do set fn=%%i
for /f %%i in ('doff.exe dd-mm-yyyy_hh:mi:ss') do set nicedate=%%i
echo MySQLDump start on %nicedate% &gt;&gt; log.txt
for %%f in (%dblist%) do (
echo backing up %%f ...
"%mysqldir%\bin\mysqldump" --user=%dbuser% --password=%dbpass% --databases %%f --opt 
--quote-names --allow-keywords --complete-insert &gt; "%bkupdir%\%fn%_%%f.sql"
echo Done! 
echo backup of %%f done! &gt;&gt; log.txt
)
for /f %%i in ('doff.exe dd-mm-yyyy_hh:mi:ss') do set nicedate=%%i
echo MySQLDump finished on %nicedate% &gt;&gt; log.txt
echo -- &gt;&gt; log.txt
echo All Done!

L’utilizzo è semplicissimo, è sufficiente impostare nella sezione “settings” i parametri che indicano username e password per l’accesso al server MySQL, la lista dei nomi dei database da processare, il percorso della directory di backup e il percorso della directory di installazione di MySQL.
All’interno dello script viene richiamato un programmino (doff.exe) che serve a restituire la data e l’ora di sistema in un formato utilizzabile per generare il suffisso dei files generati.
Infine un’avvertenza: nel codice che ho riportato qui sopra sono stato obbligato a spezzare la linea che contiene il comando di lancio di mysqldump, chi volesse copiare ed incollare questo codice dovrà eliminare il ritorno carrello che spezza la riga! Per semplificare, potete scaricare direttamente lo script insieme all’albero di directory per il backup e all’utility “doff.exe” qui

Chi utilizza server Linux potrà facilmente convertire questo in uno script bash. Inoltre ho trovato (ma non testato…) uno script per linux che funziona in modo simile: Shell script to backup MySql database

Riferimenti ed approfondimenti


Maurizio ha scritto:

Eccellente, coniuga perfettamente semplicità praticità e sopratutto funzionalità . Voto mio 10.
Grazie 1000

Massimiliano ha scritto:

Grande Script.
Ho una domanda.

Se lancio lo script manualmente dalla Prompt dei comandi tutto funziona correttamemte.
Mentre se lo metto in operazioni pianificate di windows il nome del file di backup pianificato non contiene le info su data ed ora.
Ossia invece di chiamarsi
20090907_120000_bk.sql
si chiama
_bk.sql

Da quanto ne capisco non viene eseguito doff.exe
Qualche consiglio?

L’utente utilizzato per schedulare il bat è quello di administrator

Mario Spada (Autore) ha scritto:

Grazie Massimiliano! Il problema è che, lanciando lo script da una posizione differente dalla directory in cui risiede (come avviene nelle operazioni pianificate), il percorso di doff.exe non è corretto. Per risolvere basta anteporre il path completo a doff.exe. Quindi, se la directory dove risiede lo script + doff.exe è C:\dbBackups\:
riga 15 diventa:
for /f %%i in (‘C:\dbBackups\doff.exe yyyymmdd_hhmiss’) do set fn=%%i
riga 16 diventa:
for /f %%i in (‘C:\dbBackups\doff.exe dd-mm-yyyy_hh:mi:ss’) do set nicedate=%%i
e riga 24 diventa:
for /f %%i in (‘C:\dbBackups\doff.exe dd-mm-yyyy_hh:mi:ss’) do set nicedate=%%i
oppure (meglio) crea una variabile con il path nella sezione “SETTINGS”

Bigdump: una utility per importare dump voluminosi in MySQL

screenshot di bigdumpHo trovato questa utility di Alexey Ozerov dopo un vano tentativo di fare l’upload di un voluminoso dump di dati su un server MySQL.
I problemi che si verificano in questi casi sono dovuti ad alcune impostazioni del PHP nel file di configurazione php.ini: upload_max_filesize, memory_limit, post_max_size, o alle impostazioni di timeout per gli script PHP: max_execution_time (default = 30s).
Inoltre, sui server di produzione, e su gran parte dei server in hosting gestiti con Plesk, non si hanno i diritti per cambiare le impostazioni dei file di configurazione per ovvie ragioni di sicurezza. Se ciò non bastasse, questi server solitamente sono impostati in modalità safe, quindi non si ha nemmeno la possibilità di modificare a runtime i valori dei parametri preimpostati. Per completare l’opera frequentemente non si ha nemmeno a disposizione una shell per i comandi MySQL.

Per fortuna quelli di phpMyAdmin lo sanno e quando ci si imbatte in un errore dovuto a queste limitazioni, l’applicazione stessa suggerisce l’utilizzo di Bigdump (ma non garantiscono l’utilizzo, perché lo script è di terze parti).
Questo piccolo ma prodigioso script PHP esegue piccole porzioni del dump, ripartendo in sessioni successive fino al termine della query. In questo modo lo script rimane sempre nei limiti di memoria imposti, anche quelli più restrittivi.

screenshot di bigdumpL’utilizzo è semplicissimo: per prima cosa bisogna aprirlo con un editor ed impostare i parametri di connessione al database, poi si può fare l’upload sul server web e modificargli i permessi (chmod 777 su Linux). Una volta lanciato, la prima schermata richiede di selezionare il file .sql che viene caricato sul server spezzato in chunk di 2Mb, per evitare i limiti di upload_max_filesize.
Si può anche usare FTP per trasferire il dump sul server, in questo caso bisogna metterlo nella stessa directory dove risiede Bigdump. A questo punto nella schermata compare il nome del file caricato ed accanto un link con “start import”, basta fare un click ed aspettare pazientemente!

Per darvi un’idea del tempo che serve, posso dirvi che per un dump di 10 tabelle InnoDB con 12000 records per un totale di circa 2Mb ci sono voluti circa 10′. Con tabelle MyISAM è molto più veloce, ad esempio con un file di 8Mb contenente una tabella con 33500 records ha impiegato pochi secondi.

Questo script utilizza AJAX e javascript per mostrare lo stato di avanzamento dell’operazione in modo asincrono e senza ricaricare la pagina nel browser, dunque javascript deve essere abilitato. Da sottolineare anche che il progetto è in fase beta, dunque non è dichiarato stabile, comunque nelle prove che ho fatto non ho avuto nessun problema. Infine una raccomandazione, è molto importante cancellare Bigdump dal server una volta terminata l’importazione perché si può facilmente intuire la pericolosità dello stesso in mani diverse dal gestore del db!

Download:

Bigdump.zip

Conclusioni:

Senza Bigdump avrei dovuto scrivermi uno script simile a Bigdump, ma perché perdere tempo? Secondo me è utilissimo!

Riferimenti ed approfondimenti: