Esempio di gestione di tabelle master - detail.

Introduzione.

Il modulo SQLEdit è nato per permettere, all'interno di WebGUI, di creare facilmente dei form per la manipolazione dei dati associati a tabelle di database.

Si vede facilmente come sia possibile associare ad una singola tabella di un database, un form web per la visualizzazione, l'inserimento, la modifica e la cancellazione dei dati su questa tabella.

Le cose si complicano nel momento in cui la struttura dei dati da gestire non è associabile ad una rappresentazione mono-tabelare. Un caso molto comune è una struttura di dati rappresentabile sotto forma di più tabelle con una relazione di tipo master-detail.

Cosa significa "tabelle master - detail"?

Questo tipo di relazione si ha in tutti quei casi in cui una entità è relazionata a una o a più entità distinte.

Per essere più chiari, ed introdurre la struttura dei dati oggetto di questo esempio, una struttura che relaziona gli autori di libri con i libri stessi. Ovviamente un autore può aver scritto uno o più libri, e il numero di libri che un autore ha scritto non è determinabile a priori. Non è quindi possibile gestire questa struttura con una unica tabella di database del tipo

in quanto si potrebbero sottostimare o sovrastimare il numero di libri.

Il modo migliore è invece separare le informazioni su due tabelle, una prima, detta tabella master, contiene le informazioni relative al solo autore, la seconda, detta tabella di detail, contiene l'elenco dei libri con un riferimento all'autore.

Una struttura adatta a gestire questo genere di informazioni può essere quindi schematizzata in questo modo

Come si vede la seconda tabella contiene una relazione ben precisa (la colonna author_id) che permette di ottenere da un lato, tutti i libri di un dato autore, e dall'altro, di risalire dato un libro, al suo autore. Una relazione di questo tipo viene definita relazione "1 a molti" in quanto un libro ha sempre un autore e un autore ha uno o più libri (non si tiene conto del caso di libri con più autori).

Con questo tipo di rappresentazione non c'è più alcuna possibilità di sottostimare o sovrastimare il possibile numero di libri che un autore può scrivere in quanto la tabella "books" cresce "verticalmente" a seconda delle nostre esigenze.

Gestire la cosa con SQLEdit.

Sebbene sia possibile con SQLEdit gestire le due tabelle separatamente, vi renderete subito conto che la cosa non risulterebbe però usabile. Per esempio, per inserire un libro nella tabella books dovremmo recuperare, in qualche modo, l'id dell'autore, e quindi dovremmo andare a cercarlo da qualche altra parte prima di poter inserire il libro.

Un'altro problema potrebbe esserci se proviamo a cancellare un autore che ha ancora dei libri associati. Nei database relazionali questa operazione è vietata e produre un errore. Per cancellare l'autore dovremmo prima cancellare uno per uno tutti i libri ad esso associati per poi poter cancellare l'autore stesso.

Si capisce bene come questa soluzione non sia assolutamente usabile nei casi reali. Vediamo quindi come creare una gestione web-based, usabile, per questa struttura dati con SQLEdit costruendo:

• una pagina che elenchi gli autori;

• una pagina che permetta l'aggiunta di nuovi autori...

• ...o la gestione di autori esistenti (e che contenga anche l'elenco dei suoi libri);

• una pagina per l' aggiunta, modifica e cancellazione dei libri;

• una pagina per la cancellazione di un autore (e di tutti i suoi libri).

Requisiti.

Per svolgere questo esercizio useremo ovviamente il modulo"SQLEdit" nella versione 06.00.05, il modulo standard "SQL Report" e una serie di macro di programmazione che ci renderanno la vita più facile. In particolare useremo le macro FormParam, International, SQL, If. Inoltre useremo la mia macro SQLExt per eseguire le query di cancellazione dei dati. Per l'attivazione di queste macro, vi ricordo, è necessario soltanto editare il file WebGUI.conf e aggiungere le seguenti righe nella sezione "macros":

FormParam => FormParam, \
International => International, \
SQL => SQL, \
SQLExt => SQLExt, \
If => If

Creazione delle tabelle d'esempio.

Come prima cosa andiamo a creare le tabelle che utilizzeremo per il nostro esempio, ossia le tabelle "authors" e "books" utilizzando il seguente script SQL

CREATE TABLE authors (
  id int(11) NOT NULL auto_increment,
  name varchar(255) NOT NULL default '',
  nationality varchar(100) NOT NULL default '',
  PRIMARY KEY  (id),
  KEY name (name)
) TYPE=MyISAM ;

CREATE TABLE books (
  title varchar(255) NOT NULL default '',
  author_id int(11) NOT NULL default '0',
  price decimal(8,2) NOT NULL default '0.00',
  PRIMARY KEY  (title,author_id)
) TYPE=MyISAM;

Per semplicità utilizziamo tranquillamente lo stesso database di WebGUI. Scarichiamo quindi il codice SQL e lanciamo il client MySQL dandogli in pasto questo script

# mysql -p WebGUI < create_authors_books.sql

Pagina con l'elenco degli autori.

Iniziamo, creando la pagina che elencherà gli autori presenti nel nostro database e che conterrà i link per la manutenzione dell'archivio degli autori.

Aggiungiamo quindi una pagina a WebGUI, diamole come titolo "Authors List" e come url "authors_list". Impostiamo a zero il caching della pagina.

Aggingiamo ora l'oggetto SQL Report che elencherà gli autori presenti nel database.

Impostiamo il titolo dell'oggetto SQL Report appena creato a "Authors List". Useremo la descrizione di questo oggetto per avere un link per aggiungere nuovi autori. Se state usando l'editor evoluto di Internet Explorer 5.x, cliccate su e passate in modalità codice. Aggiungiamo quindi questo link tramite il seguente codice

<a href="author">Add new author</a>

Nel campo della query SQL da eseguire per visualizzare il report aggiungiamo il seguente codice SQL

select * from authors

Spostandoci ora nella sezione che definisce il layout della pagina, aggiungiamo il codice HTML che incolonnerà i campi della tabella degli autori in una tabella HTML e aggiungerà i link per editare e cancellare l'autore.

Chi ha WebGui 5.x, deve aggiungere questo codice direttamente nel campo "report template"

<table border=0>
    <tr class="tableHeader">
        <td>Id</td>

        <td>Name</td>
        <td>Nationality</td>
        <td></td>

        <td></td>
    </tr>
    ^-;
    <tr class="tableData">
        <td>^0;</td>

        <td>^1;</td>
        <td>^2;</td>
        <td>
            <a href="author?exec=edit&id=^0;">Edit</a>
        </td>
        <td>
            <a href="author_delete?id=^0;">Delete</a>

        </td>
    </tr>
    ^-;
</table>

Chi ha invece WebGUI 6.x, deve creare un nuovo template. Cliccate sul pulsante "modifica" del template, copiate il template di default di SQLReport in un nuovo template che chiamerete "SQLReport authors_list" e modificate questo nuovo template di modo che il contenuto del tag <table> risulti cosi fatto:

<table border=0>
    <tr class="tableHeader">
        <td>Id</td>

        <td>Name</td>
        <td>Nationality</td>
        <td></td>

        <td></td>
    </tr>

    <tmpl_loop row.field_loop>
        <tr class="tableData">
            <td><tmpl_var row.field.id.value></td>

            <td><tmpl_var row.field.name.value></td>
            <td><tmpl_var row.field.nationality.value></td>
            <td>
                <a href="author?exec=edit&id=<tmpl_var row.field.id.value>">Edit</a>
            </td>
            <td>
                <a href="author_delete?id=<tmpl_var row.field.id.value>">Delete</a>

            </td>
    </tmpl_loop>
    </tr>
</table>

Si vada quindi ad impostare questo come template dell'elemento SQLReport.

Quindi passiamo alla pagina dei privilegi e impostiamo il percorso al database dove sono state create le cartelle "authors" e "books".

Le tre schermate dovrebbero, alla fine assomigliare a queste:

Salviamo questo oggetto. Ovviamente non essendoci ancora alcun autore caricato, il risultato sarà una lista vuota.

Andiamo quindi a creare la pagina per aggiungere ed editare gli autori.

Pagina per la gestione degli autori.

Questa pagina conterrà due elementi. Un oggetto SQLEdit che ci permetterà di editare i dati della tabella "authors" e un oggetto SQLReport che ci mostrerà gli eventuali libri dell'autore oggetto della modifica e ci permetterà di aggiungere, editare e cancellare tali libri.

Aggiungiamo quindi una nuova pagina gerarchicamente sotto alla pagina creata in precedenza. Per puro gusto estetico, iniziamo ad utilizzare un po' di macro di WebGUI, per far si che il titolo della pagina sia "Add author" in fase di aggiunta di un nuovo autore e "Edit author: *****" in fase di modifica.

Per fare ciò basta mettere nel campo del titolo della pagina che stiamo per aggiungere, il seguente codice:

^If('^FormParam(id);' ne '', Edit author: ^SQL("select name from authors where id = ^FormParam(id);", ^0;); , Add new author);

Dato che, in fase di modifica di un autore, verrà passata a questa pagina un parametro con cui l'autore è univocamente definito all'interno del nostro database (id), il codice sopra non fa altro che recuperare il nome dell'autore e inserirlo al posto della macro ^SQL se questo parametro "id" è impostato o fissare il titolo a "Add new author" altrimenti. Dato che la proprietà "Menu title" non processa le macro, impostiamola staticamente a "Manage author".

Definiamo l'URL a questa pagina come "author", nascondiamo questa pagina alla navigazione, di modo che non appaia nel menù del sito, e aggiungiamo quindi questa nuova pagina.

Siamo ora pronti ad aggiungere l'elemento SQLEdit per la gestione della tabella degli autori. Aggiungiamo quindi questo modulo impostando il titolo del modulo come il titolo della pagina e la pagina di riferimento "referer page" all'URL della pagina precedentemente creata ossia alla pagina "authors_list" di modo che un "Salva e torna indietro" ci porterà proprio a questa pagina.

Andiamo quindi nella schermata dei privilegi, impostiamo le stesse proprietà del percorso al database usate nell'elemento SQL Report aggiunto sopra e impostiamo come nome della tabella, quello della tabella degli autori "authors".

Questo elemento dovrebbe essere quindi configurato in un modo simile a questo:

e, una volta salvato, dovrebbe apparire un form vuoto con le colonne della tabella "authors".

Prima di aggiungere l'elemento per aggiungere e visualizzare i libri dell'autore, andiamo a nascondere il campo "id" che è inutile mostrare all'utente. Per fare questo, clicchiamo sull'edit della riga corrispondente e impostiamo la proprietà "stato" a "nascosto".

Passiamo ora ad aggiungere l'elemento SQL Report per la visualizzazione dei libri di un autore. La configurazione di questo elemento non è "esteticamente" il massimo in quanto il Wobject SQL Report non possiede un modo "pulito" per renderlo invisibile in alcuni casi. In fatti, quello che vorrei, è che, quando vado ad aggiungere un nuovo autore, l'elemento SQL Report non appaia, anche perchè, non essendo disponibile alcun "id" per l'autore, le query SQL che andremo a mettere dentro a questo ulteriore elemento, produrrebbero un errore. E' solo dopo aver salvato il nuovo autore che l'elemento SQL Report deve eapparire per permetterci di aggiungere nuovi libri all'autore.

Per fare questo, dovremo scrivere del codice, "esteticamente" non molto bello, dentro a questo modulo. Aggiungiamo quindi un modulo SQL Report nella pagina "author" e, come nel caso del titolo di questa pagina e del titolo dell'elemento SQLEdit, scriviamo il seguente codice nel titolo di quest'ultimo elemento

^If('^FormParam(id);' ne '', Books of author: ^SQL("select name from authors where id=^FormParam(id);","^0;"););

Questo codice fa si che se il parametro "id" non è passato alla pagina (e quindi, presumibilmente) stiamo aggiungendo un nuovo autore, il titolo non viene mostrato.

Come nel caso dell'elemento SQL Report della pagina "authors_list", andiamo a mettere nella descrizione il codice per aggiungere un nuovo libro. Solo che in questo caso dobbiamo verificare se stiamo aggiungendo o editando un autore:

^If('^FormParam(id);' ne '',<a href="book?author_id=^FormParam(id);">Add a new book</a> to this author);

Aggiungiamo la query SQL per la ricerca dei libri dell 'autore

select * from books where author_id = ^FormParam(id);

e, a causa della presenza di una macro nel codice SQL, impostiamo il flag per pre-processare le macro nella query. Per fortuna, a meno di non attivare il debug, nessun testo viene stampato se la query produce un errore e quindi, se stiamo aggiungendo un nuovo autore e quindi nessun parametro "id" è passato alla pagina, il titolo e la descrizione non appaiono grazie all' ^If; e il report non appare in quanto la query SQL da errore.

Passando ora al layout di questo elemento andiamo a mettere del codice HTML simile a quello impostato nell'elemento della pagina "authors_list"

Per WebGUI 5.x si usi questo codice

<table border=0>
    <tr class="tableHeader">
        <td>Title</td>

        <td></td>

        <td></td>

    </tr>
    ^-;
    <tr class="tableData">

        <td>^0;</td>
        <td>

            <a href="book?exec=edit&title=^0;&author_id=^1;">Edit</a>

        </td>
        <td>

            <a href="book?exec=delete&title=^0;&author_id=^1;">Delete</a>

        </td>

    </tr>
    ^-;
</table>

Per WebGUI 6.x creare un nuovo template e usare il seguente codice per il contenuto del tag <table>

<table border=0>
    <tr class="tableHeader">
        <td>Title</td>

        <td></td>

        <td></td>

    </tr>

    <tmpl_loop row.field_loop>
        <tr class="tableData">

            <td><tmpl_var row.field.title.value></td>
            <td>

                <a href="book?exec=edit&title=<tmpl_var row.field.title.value>&author_id=<tmpl_var row.field.author_id.value>">Edit</a>

            </td>
            <td>

                <a href="book?exec=delete&title=<tmpl_var row.field.title.value>&author_id=<tmpl_var row.field.author_id.value>">Delete</a>

            </td>

        </tr>

    </tmpl_loop>
</table>

Quindi passiamo alla pagina dei privilegi e impostiamo il solito percorso al database. Questo elemento dovrebbe essere quindi configurato con delle schermate simili a queste:

Una volta salvato questo elemento il risultato dovrebbe essere assolutamente vuoto, in quanto, fintanto che non si è in modifica di un autore, questo elemento dovrà essere invisibile.

Pagina per la gestione degli autori.

Passiamo ora a creare la pagina per gestire i libri. Aggiungiamo quindi una nuova pagina gerarchicamente sotto la pagina "author" e inseriamo il seguente codice come titolo della pagina:

^If('^ParamForm(exec);' eq 'edit',^SQL("SELECT title,name FROM books LEFT JOIN authors on (id=author_id) where title = '^FormParam(title)' and author_id=^FormParam(author_id);","Edit book: ^0; ( ^1;)");,Add new book);

Questa volta, tanto per cambiare, per capire se ci troviamo in fase di aggiunta di un nuovo libro o in fase di modifica di uno esiste, invece di verificare l'esistenza della chiavi primarie della tabella "books" come parametri passati alla pagina, verifichiamo che il parametro "exec" che dice a SQLEdit in che modalità porsi, sia uguale a "edit". Se lo è sto modificando, se non lo è, sto aggiungendo. Dato che la proprietà "Menu title" non processa le macro, impostiamola staticamente a "Manage book".

Definiamo l'URL a questa pagina come "book", nascondiamo questa pagina alla navigazione, di modo che non appaia nel menù del sito, e aggiungiamo quindi questa nuova pagina.

Possiamo quindi aggiungere l'elemento SQLEdit per la gestione della tabella dei libri. Aggiungiamo quindi questo modulo impostando il titolo del modulo come il titolo della pagina e la pagina di riferimento "referer page" all'URL della pagina precedentemente creata passandole l'id dell'autore per ritornare alla visualizzazione dell'autore che ha scritto questo libro ossia:

author?id=^FormParam(author_id);&exec=edit

Andiamo quindi nella schermata dei privilegi, impostiamo il percorso al database e come nome della tabella, quello della tabella dei libri "books". Salviamo questo elemento, spostiamo il campo "author_id" come primo campo e rendiamolo nascosto.

Il risultato dovrebbe essere qualcosa di simile:

Pagine per la cancellazione dei libri.

Per cancellare completamente un autore e tutti i suoi libri andremo ad aggiungere due pagina, una prima che ci chiede conferma dell'operazione di cancellazione e la seconda che effettua materialmente l'operazione.

Aggiungiamo quindi una prima pagina gerarchicamente sotto alla pagina "authors_list", diamole come titolo "Author delete", come url "author_delete" e rendiamo la pagina invisibile nel menu. Salviamo la pagina e aggiungiamole un elemento "Articolo".

Impostiamo il titolo dell'elemento articolo a

^International(23,"SQLEdit");

e diamo al codice HTML della descrizione dell'articolo, il seguente valore

<p>^International(23,"SQLEdit");</p>
<p align="center">
<a href="author_deleteConfirm?id=^FormParam("id");">
^International(44);</a>
-
<a href="javascript: history.move(-1)">
^International(45);</a>
</p>

Il risultato, una volta salvato questo elemento, sarà qualcosa di simile:

Passiamo ora a creare la pagina che effettuerà materialmente la cancellazione. Aggiungiamo quindi una pagina gerarchicamente sotto quella appena creata, diamole come titolo "Author delete confirm", come url "author_deleteConfirm" e rendiamo la pagina invisibile nel menu. Salviamo la pagina e aggiungiamole un elemento "Articolo".

Impostiamo il titolo dell'articolo come:

Author ^SQL("select name from authors where id=^FormParam(id);"); deleted

e diamo al codice HTML della descrizione dell'articolo, il seguente valore

Author <b>^SQL("select name from authors where id=^FormParam(id);");</b>

with id <b>^FormParam(id);</b> and all him books has been deleted.

^SQLExt("delete from books where author_id = ^FormParam(id);");
^SQLExt("delete from authors where id = ^FormParam(id);");

<p><a href="authors_books">Come back</a> to authors list page</p>

Come si vede, la descrizione dell'articolo non contiene solo codice HTML ma anche i comandi SQL per cancellare i libri dell'autore e poi l'autore stesso. Notate che si è dovuto far ricorso, qui, a una macro non standard ^SQLExt; in quanto la macro standard ^SQL; non permette di eseguire operazioni di DELETE, ma solo di SELECT.

Salvando, il risultato dovrebbe essere tutta una serie di errori SQL. Non ci dobbiamo preoccupare, questa pagina dovrà essere richiamata solo e soltanto passandole il parametro "id" dell'autore da cancellare e quindi le query SQL diventeranno, in questo modo, sintatticamente corrette.

Prova della procedura.

Siamo quindi giunti a completare la nostra procedura per gestire gli autori e i libri. Si può provare ad aggiungere autori e libri e vedere il comportamento della procedura.

Analizzando quanto sopra creato, non dovrebbe essere difficile capire come funziona il tutto e adattare questo esempio alle proprie esigenze.