An example of using SQLEdit with master-detail tables.

Introduction.

SQLEdit module is born in order to allow, inside WebGUI, to simply create web-forms to manipulate data binding to dabatabase tables.

One cas simply see as it is possible to associate to one single table of a database, a web-form  for the visualization, the insertion, the modification and the cancellation of the data on this table.

Things become more difficult if the data structure to manage isn't combinable into one-table view. A much common case is one data structure describe into more tables with a relationship called master-detail.

What "master-detail" means?

This type of relation has in all those cases in which an entity is related to one or more distinguished entity.

For being clearer, and introducing the data structure we'll use in this example, a structure that relates the authors of books with their books. Obviously an author can have written one or more books, and the number of books that an author has written is not determinable a priori. Therefore, it's not possible to manage this structure with only one table like this:

becouse you could undervalue or overvalue number of books.

The better way is instead to separate the information on two tables, the formed, called master table, contains the information relatives to the single author, the latter, called detail table, contains the directory of the books with a reference to the author.

A structure adapted to manage this kind of information can therefore be outlined in this way

So, you can see that the second table contains a very precise relation (the column author_id) it allows to obtain all the books of an author, and to go back, given a book, to its author. This kind of relation was defined "1 to many" because a book has always an author and an author has one or more books (does not keep account of the case of books with more authors).

With this type of description there is no possibility to undervalue or to overvalue the possible number of books that an author can write because the table "books" grows "vertically" as needed.

Manage these tables with SQLEdit.

Although it is possible with SQLEdit to manage the two tables separately, you will understand this isn't usable. As an example, in order to insert a book in the table books we would have to recover, in some way, the id of the author, and therefore we would have to go to find it from some other part before being able to insert the book.

Another problem could appear if we try to delete an author who has still some books linked to him. In relationship database this operation is prohibited and produre an error. To delete the author we should delete before his books to be able then to delete the author itself.

It is understood well as this solution is not absolutely usable in the real cases.

We see therefore how to create one usable web-based management, by building for this structure

• one page that lists the authors;

• one page to add new authors...

• ...or edit existing ones (with a list of all his books)

• one page for the visualization, the insertion, the modification and the cancellation of authors book

• one page to delete an author (and all his books)

Requirement .

In order to carry out this exercise we will obviously use the "SQLEdit" module in version 06.00.05, the standard module "SQL Report" and a series of programming macros that will make us life easier. In particular we will use the macros: FormParam, International, SQL and If . Moreover we will use my macro SQLExt in order to delete author and his books. For the activation of these macro, remember, is necessary to edit WebGUI.conf and add the following lines in the section "macros" :

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

Creazione delle tabelle d'esempio.

First of all we go to create the tables that we will use for our example, that is the tables "authors" and "books" using following SQL script

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;

To simplify, we could use the same WebGUI database . We can download SQL the above script and launch the MySQL client with it

# mysql -p WebGUI < create_authors_books.sql

Page with a list of authors.

We begin, creating the page that will list the authors present in our database and that it will contain links for the maintenance of the authors archives.

We add therefore a page to WebGUI, title it "Authors List" and set "authors_list" like URL. We set up to zero the caching of the page.

We add an SQL Report element that will list the authors available in the database.

We set up the title to "Authors List". We will use the description of this object for having a link in order to add new authors. If you are using the Internet Explorer editor, click on icon and change in modality code. We add therefore this link with the following code

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

In the field used to execute SQL query in order to visualize the report, we add following code SQL

select * from authors

Switch now to the section that defines the layout of the page, we add HTML code that draw up the fields of the table of the authors in a HTML table and will add the link for the modification and the cancellation of the author.

If you have WebGui 5.x, enter this code directly in the report template field:

<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>

If you have WebGUI 6.x, you must create a new template. Click on "manage" button of the template, copy default SQLReport template and named "SQLReport authors_list" the new template. Edit this template so that the <table> is as:

<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>

Use this new template as the template for our SQLReport element.

Therefore we pass to the page of privileges and we set up the database link where they have been created the "authors" and "books" tables.

The three view would have, to the end, to be similar these:

We save this object. Obviously not being to us still some loaded author, the result will be one empty list.

We go therefore to create the page in order to add and to edit the authors.

Page to manage authors.

This page will contain two elements. A SQLEdit object that will allow us to modify the data of the table "authors" and a SQLReport object that will show us the books of the author that we are going to modify and will allow us to add, to edit and to delete such books.

We hierarchically add one new page under the previously created one. For pure aesthetic taste, we begin to use a little macro of WebGUI, in order to make the title of the page is "Add author" when adding a new author and "Edit author: *****" during modification.

In order to do it, we add the following code:

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

Since, during modification of an author, a parameter will be send to this page to unique set the author (id), the code above will try to recover the name of the author and to insert it in place of macro the ^SQL if this parameter "id" is set up or to fix the title to "Add new author" otherwise. Since the property "Menu title" don't execute macros, we set up it to "Manage author" statically.

We define the URL to this page like "author", hide this page to navigation, and add therefore this new page.

Now we are ready to add the SQLEdit element to manage the authors table. Therefore we add an SQLEdit element, setting up its title like the title of the page and the "referer page" with the URL of the page previously created that is to the "authors_list" page so that "Save and go back" will go back to this page.

Now we switch to privileges tab, set up the database link as the SQL Report element added above and set up the table name to "authors" table.

This element would have therefore to be configured in a way similar to this:

and, once saved, an empty  form would appear with the columns of the table "authors".

Before adding the element in order to add and to visualize the books of the author, we go to hide the field "id" that it is useless for the customer. In order to make this, click on the edit in the same row and set up the property "type" to "hidden".

Now we can add an SQL Report element  for the visualization of the author books. The configuration of this element is not "aesthetically" the best because SQL Report does not have "a clean" way  to be it invisibile in some cases.  In facts, what I would, it is that when add new author, the SQL Report is invisible, also because, not being available some "id" for the author, the SQL query would produce an error. Only alfet saved new author the SQL Report element must show in order to allow us to add new books to the author.

To do this, we will have to write some code, "aesthetically" not so beautiful. Therefore we add a SQL Report element  in this page  and, like in the case of the title,we write the following code in its title

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

So if the parameter "id" is not passed to the page (and therefore, presumablly) we are adding a new author, title does not show.

Like in the case of SQL Report element of the page "authors_list", we go to put in the description the code in order to add a new book. But, in this case, we must verify if we are adding or editando an author:

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

We add the SQL query for the search of the author books

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

and, because of the presence of a macro in code SQL, we set up the flag in order preprocess the macro in the query.

Fortunately, less than not to activate debug, no text will be printed if the query produces an error and therefore, if we are adding a new author and no parameter "id" is passed to the page, the title and the description do not appear thanks to the ^If; and the report don't show owing to the ^SQL; error.

Now switching to the layout of this element we go to put HTML code similar to that one set up in the element of the page "authors_list".

For WebGUI 5.x use this code for report template field.

<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>

For WebGUI 6.x create a new template and use this code for <table> tag

<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>

Therefore we switch to the privileges tab and set up the usual database link. This element would have therefore to be configured similar to these:

Once saved this element the result would have to be absolutely empty, ibecause, until it is not in editing of an author, this element will have to be invisibile.

Page to manage books

Now, we are ready to create the page to manage the books. So we add one new page hierarchically under the page "author" and insert the following code for its title:

^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);

This time, to understand if we are adding a new book or edit an existing one, instead verifying the existence of the primary keys of the table "books" as parameters pass to the page, we verify that the parameter "exec" which says to SQLEdit in what modality to be, is equal to "edit". If it is we are editing, if not, we are adding. Since the property "Menu title" not proceeded the macro, we set up it to "Manage book" statically. We define the URL to this page as "book", hide this page to navigation, and add therefore this new page.

Now we can add the SQLEdit element for the management of the books table. We add it to this module, setting up its title like the title of the page and the "referer page" to the URL of the page previously created passing it the id of the author in order to return to manage the author who has written this book:

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

We go then in the privileges tab, set up the usually database link and, like name of the table, table "books". We save this element, we move the field "author_id" as first field and set it hidden.

The result would have to be something like this:

Page to delete authors

To completely delete an author and all its books we will go to add two page, the former that asks us confirmation for the operation and the latter that carries out the operation materially.

So we hierarchically add the first page under "authors_list", give them la title like "Author delete", as URL "author_delete" and makes it invisibile in the menu. We save the page and we add it an "Article" element .

We set up its title to

^International(23,"SQLEdit");

and the description of the article as the following value

<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>

The result, once saved this element, is something of similar:

Then we pass to create the page that will carry out the deleting materially. We hierarchically add a page under the one as soon as created, give them a title like "Author delete confirm", as URL "author_deleteConfirm" and makes it invisibile in the menu. We save the page and we add them an "Article" element.

We set up its title to

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

and the description of the article as the following value

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>

As you can see, the description of the article does not contain only HTML code but also SQL statements in order to delete the author books  and then the author . Look that we have had to use a not standard macro ^SQLExt; because standard ^SQL; does not allow to execute DELETE, but only SELECT operations .

Saving, the result would have to be one series of errors SQL. We do not have to be worried, this page will be called only passing them the parameter "id" of the author to delete and therefore the SQL statements will become, in this way, syntactically corrected.

Ppocedure test .

We have so completed our procedure to manage the authors and the books. It can be tried to add authors and books and see the behavior of the procedure.

Analyzing what we have created, it would not have to be difficult to understand how it works and to adapt this example to the own requirements.