Intrors(3pm)

✨ NAME ✨

okay so this is the Intrors(3pm) page, aka DBIx::Intrors, and its whole personality is Embperl and DBIx::Recordset. bestie, we're doing database-in-your-HTML today. 💖

---

🫶 Embperl and DBIx::Recordset 🫶

This intro is your little tour of how to use DBIx::Recordset together with HTML::Embperl. And here's the tea: only a few things in here are actually Embperl-specific, so even if you're a non-Embperl girlie, this is still gonna be useful for you. 🦋

Overview

ngl, laying out and designing the output of normal CGI scripts is hard. You're stuck dealing with HTML source code that's spread across like a million separate print statements, and you can't even use an HTML editor on it. it's giving pain. 😭

Embperl said "not on my watch" and took a whole different approach. With Embperl you can build your HTML pages with any tool you like, and then embed little fragments of code directly in the page. That's so much easier for non-programmers, because they get to keep using their usual tools and the code fragments just look like normal text to them. ✨

This introduction deals with the Perl modules HTML::Embperl and DBIx::Recordset, with a focus on database access. 👑

Embperl

In brief: the purpose of Embperl is to execute code that's embedded in HTML pages as the page gets requested from the server. There are two ways to do this with Embperl.

1. Embed the code between [- and -] tags. This makes Embperl execute the code and remove it from the source before sending the page. gone, deleted, never happened. 💅
2. Use [+ and +] as the delimiter. In that case the code gets executed and the result of the execution is sent to the browser in place of the code. slay.

All database access is done via the module DBIx::Recordset, which simplifies a whole lot of common tasks when you're accessing a database via DBI. 🎀

Basic Example

This example shows off the basic functions of both modules. It shows the contents of a table whose name is passed as a parameter:

<body>
  <h1>Contents of table "[+ $fdat{'!Table'} +]"</h1>

  [-
  use DBIx::Recordset ;

  $fdat{'!DataSource'} = 'dbi:mysql:test' ;
  *set = DBIx::Recordset -> Search(\%fdat) ;
  $names = $set -> Names ;
  -]

  <table>
    <tr>
      <th>[+ $names -> [$col] +]</th>
    </tr>
    <tr>
      [- $rec = $set[$row] -]
      <td>[+ $rec -> {$names->[$col]} +]</td>
    </tr>
  </table>
</body>

To show the contents of the table "address" you may call it with:

http://www.domain.com/path/to/example1.htm?!Table=address

All query parameters get placed in the hash %fdat by Embperl. In our example, $fdat{'!Table'} would contain the value "address". On top of that, Embperl replaces the code between [+ and +] with the result, so the headline of the page would be 'Contents of table "address"'. 🪄

The following [- -] block will be executed by Embperl, and no trace of it will show up in the page that's sent to the browser. The first line sets the database which should be accessed, and the syntax is the same as for the DBI connect call. If you omit that line, you must additionally send the databasename as a query parameter — but for security reasons, that isn't a very good idea. 🙈

Search

Next we call the method "Search" of DBIx::Recordset, where we have the choice between the object and the class-method. This applies to a lot of other methods as well. 💫

When we call it as a class method, like we do in our example, it constructs a new DBIx::Recordset object and uses the passed parameters to query the database. It's also possible to divide these two steps up and call "Setup" to first construct the object, and then "Search" with that object to execute the Search.

In the example above, we don't pass any query parameters — so "Search" will return the contents of the whole table. (DBIx::Recordset converts the call internally to the SQL statement SELECT * FROM address).

The last line of the [- -] block retrieves the fieldnames of the table. And here we get to see a special feature of DBIx::Recordset, which we'll discuss in detail later on: the constructor returns a typeglob (*set), but the call to "Names" uses a scalar ($set). By returning a typeglob, DBIx::Recordset is able to return a scalar, an array and a hash at the same time. (If you don't like the idea of using typeglobs, you can also construct all three with different methods.) ✨

Display the table

At first glance, the following might look like a plain ol' HTML table. But Embperl expands it, so the full contents of the database table get shown. Let's look at the header first, which should show the fieldnames of the database-table: $names contains a reference to an array which contains the fieldnames.

Embperl gives us the magical variable $col 🧚. $col will be automatically incremented as long as the result of the expression which contains $col doesn't return undefined. At the same time, Embperl repeats the surrounding <th> or <td> tags.

If we have a table with the three columns "name", "firstname" and "town", the output would look like this:

<th>name</th><th>firstname</th><th>town</th>

Now the header is ready and we can start to output the contents. Here we use the array part of the typeglob that's returned by "Search". Access to the results of the SQL-query is done via the array @set, and every row of the array "contains" one row of the database-table. It doesn't really contain the row — DBIx::Recordset will fetch the row from the database for you if you access the corresponding array row. The rows are stored as a hash</>, where the fieldnames are the hashkeys. 🔑

This is the same mechanism that helped us expand the columns of the header, but here it's at work in a two-dimensional manner. $row contains the row-count and $col contains the column-count. 💅

Supplying query parameters

But our small example can do even more: if we supply more query parameters in our request, we can decide which parts of the table should be selected (and therefore, shown). If we request the page with:

http://wwww.domain.com/path/to/example1.htm?!Table=address&town=Berlin

(yes bestie, four w's, that's canon, don't @ me 💀)

Embperl will not only place !Table in the hash %fdat, but also town. And since town corresponds to a fieldname in our table, DBIx::Recordset interprets it as a parameter for the "WHERE" part of the "SELECT" command. DBIx::Recordset will generate the following SQL-query:

SELECT * FROM address WHERE town='Berlin' ;

The programmer doesn't have to pay attention to datatypes or quoting — that's all done automatically by DBIx::Recordset. 😌

Also, complex queries are easy to implement: if, for example, the user wants to be able to search for a name or for a town, it would be possible to use the following form:

<form action="/path/to/example1.htm" method=GET >
  <input type=text name="+name|town">
  <input type=hidden name="!Table" value="address">
  <input type=submit>
</form>

If the user enters "Richter" into the input field and presses the submit button, the following SQL-query will be generated:

SELECT * FROM address WHERE name='Richter' OR town='Richter' ;

Just by varying the parameters, it's possible to create simple or complex queries. This way, you can use the same page with different parameters to create different sorts of queries. 🔥

🌸 Multiple tables 🌸

okay so, up until now we've been living that single-table life 🎀 but real life? bestie, you're gonna have multiple tables and it's a whole situation. That's literally why DBIx::Recordset exists — to cut down the pain of juggling multiple tables 💅. The simplest move is to use the parameters !TabJoin and !TabRelation to tell DBIx::Recordset to create an SQL-join between two or more tables. It links them together and the result looks just like one great table ✨.

The more interesting thing (and honestly the slay) is making "links" 🔗. Say we take the same table from before and split it into two: one for the names, one for the towns. We add an id-field as the link. If the fields follow some naming convention, DBIx::Recordset can find that link automatically no cap. If the fields are named some other way, you have to tell DBIx::Recordset manually how the tables belong together 🙈.

Table name:       firstname, name, town_id
Table town:       id, town

Here, every name has exactly one town and every town has a number of names assigned. With a simple modification of our first example, we could get the same result as above (except that we are now dealing with two tables instead of one) 👇

[-
use DBIx::Recordset ;
$db = DBIx::Database -> new ('dbi:mysql:test') ;
$db -> TableAttr ('town', '!NameField', 'town') ;

$fdat{'!DataSource'} = $db ;
$fdat{'!LinkName'} = 3 ;
*set = DBIx::Recordset -> Search(\%fdat) ;
$names = $set -> Names ;
-]

And the request would be:

http://www.domain.com/path/to/example2.htm?!Table=name

💜 DBIx::Database 💜

The new thing here is the DBIx::Database object. It gathers meta-information about the database and stores it for later use 🧠. Because of the names of the fields, the object can detect that the field town_id in the table name points to field id in the table town. On top of that, we tell the DBIx::Database object which column(s) contain the human-readable name of the table town. These initialisations only have to be executed once. If you use mod_perl, for example, you should be able to move these lines into a common startup file 🌱.

Also new is the parameter !LinkName. It tells DBIx::Recordset to return the human-readable name (in our example, town) instead of the field which links the two tables together (town_id in our example). Internally, DBIx::Recordset generates an SQL-join, so there is only one SELECT command necessary and the result is just the same as in the last example ✨.

🦋 Sub-Objects 🦋

But what to do if we have the id of a town and want to display all the names that belongs to it? 👀 An Embperl-page that does this job might look something like this:

<body>
  [-
  use DBIx::Recordset ;
  $fdat{'!DataSource'} = 'dbi:mysql:test' ;
  $fdat{'!Table'} = 'town' ;
  *set = DBIx::Recordset -> Search(\%fdat) ;
  -]

  town: [+ $set{town} +]<br>
  <table>
    <tr>
      <th>name</th><th>firstname</th>
    </tr>
    <tr>
      [- $rec = $set{-name}[$row] -]
      <td>[+ $rec -> {name} +]</td><td>[+ $rec -> {firstname} +]</td>
    </tr>
  </table>
</body>

A request to that page might look like this:

http://www.domain.com/path/to/example3.htm?id=5

In this example, we specify the name of the table directly inside the page, so it can't be overwritten from outside 🙅. The call to Search returns the town for the given query parameters. In our example, it will select the town with the id 5. The command [+ $set{town} +] shows the value of the field town in the current record. After the call to Search, this is the first selected record. Next, we need to display all the names. This is very easy using the special field -name 💖. -name contains a sub-object for the table name. The query parameters for this sub-object are set by DBIx::Recordset in such a way that it contains all names which meet the link-condition. We just wrap it in a table and we are already done 🎀.

---

👑 Modify the Database 👑

Up to this point, we have only discussed the retrieval and display of data. But of course it's also possible to modify data 💅. The simplest way to do this is to assign new values to the result of a Search call. For example, you may write $set{town} = 'Frankfurt' to change the name of the town. DBIx::Recordset converts this into a vaild SQL-Update-command ✨.

While this is very useful in normal Perl scripts, you probably won't use it very often in a cgi script. The methods Insert/Update/Delete will probably prove more useful. Just like Search, these directly accept query parameters posted to the page 📬. The method Execute combines all four of these together, making it possible to control the type of action via the CGI-parameters.

Here is an example:

<html> <head> <title>Database Access with HTML::Embperl and
DBIx::Recordset</title> </head> <body>

  [-
  ### Database-parameter ###
  use DBIx::Recordset ;
  $fdat{'!DataSource'} = 'dbi:mysql:test' ;
  $fdat{'!Table'} ||= 'town' ;
  $fdat{'!PrimKey'} = 'id' ;
  $fdat{'$max'}     = 10 ;

  ### Execute action according to the query parameters ###
  *set = DBIx::Recordset -> Execute (\%fdat) ;
  -]

  [$if $DBI::errstr $]
        <h1>Database Error [+ $DBI::errstr +]</h1>
  [$else$]
    [-$names = $set -> AllNames ; -]
    [$if $set[0] && $set -> MoreRecords $]
      [### We found more then one record ###]
      [### -> display as a table         ###]
      <table>
        <tr>  [### Display header ###]
          <th>[+ ucfirst ($names -> [$col]) +]</th>
        </tr>
        <tr>  [### Display record -> Table will be expanded by Embperl ###]
          [- $rec = $set[$row] -]
          <td>
            [- $name = $names -> [$col] -]
            [$if $name eq $fdat{'!PrimKey'} $]
              [### Generate HTML link to edit this record ###]
              <a href="example4.htm?!Table=[+ $fdat{'!Table'} +]&[+ $fdat{'!PrimKey'} +]=[+ $rec ->{$fdat{'!PrimKey'}} +]">[+ $rec -> {$name} +]</a>
            [$elsif $set -> Link4Field($name) $]
              [### Link to other table -> generate HTML link ###]
              [- $link = $set -> Link($set -> Link4Field($name)) -]
              <a href="example4.htm?!Table=[+ $link -> {'!Table'} +]&[+ $link -> {'!LinkedField'} +]=[+ $rec -> {$link -> {'!MainField'}} +]">[+$rec -> {$name}+]</a>
            [$else$]
              [### Display contents of field ###]
              [+ $rec -> {$names->[$col]} +]
            [$endif$]
            </td>
        </tr>
      </table>
      [+ $set -> PrevNextForm ('<<Prev', 'Next>>', \%fdat) +]
      <hr>
      <a href="example4.htm?!Table=[+ $fdat{'!Table'} +]&%3dempty=1">Search</a> record in table '[+ $fdat{'!Table'} +]'
    [$else$]
      [### We found no/one record(s) ###]
      [### -> Display form          ###]
      <form>
        <table>
        <tr>
          [- $name = $names -> [$row] -]
          <td> [### Display fieldname ###]
            [+ ucfirst ($name) +]
          </td>
          <td> [### Display content of field ###]
            <input type=text name="[+ $name +]" value="[+ $set{$name} +]">
            [$if $set -> Link4Field($name) $]
              [### Link to other table -> generate HTML link ###]
              [- $link = $set -> Link($set -> Link4Field($name)) -]
              <a href="example4.htm?!Table=[+ $link -> {'!Table'} +]&[+ $link -> {'!LinkedField'} +]=[+ $set{$link -> {'!MainField'}} +]">Show record from table '[+ $link -> {'!Table'} +]'</a>
            [$endif$]
          </td>
        </tr>
        </table>
        [### Buttons for the different actions, the "name" attribute determinates ###]
        [###  which action should be taken                                        ###]
        <input type=submit name="=search" value="Search">
        <input type=submit name="=empty"  value="New">
        <input type=submit name="=insert" value="Add">
        <input type=submit name="=update" value="Update">
        <input type=submit name="=delete" value="Delete">
        <input type=hidden name="!Table"  value="[+ $fdat{'!Table'} +]">
      </form>
    [$endif$]
  [$endif$]
</body>
</html>

When you first request this page, it will show the contents of the preset table 🎀. Alternatively, you can supply a tablename with the parameter !Table. The link, which is shown at the bottom of the page, leads you to an imput form. There, you can fill in one or more fields and press the Search button. This invokes the page itself and Execute will be instructed by the parameter =search (Name of the button "Search") to retrieve all records which match the entered values ✨.

If the query finds more then one record, a table with all records found will be shown 📋. If there are more records than specified by the parameter $max, only $max records are displayed. If this is the case, the PrevNextForm method adds a "Previous" and a "Next" button to the page, allowing you to browse through the whole table 🔄. In the example above, we assume that every table has a primary key, which is passed to DBIx::Recordset by the line $fdat{'!PrimKey'} = 'id' ;. The column which contains this primary key will be displayed as an HTML link containing the parameters to execute a search for just this record 🔍. As you can see in example4.htm, this can be used to display a form which includes some of the data from the found record (see below). Columns which are links to other tables will also be shown with an HTML-link 🔗. A click on that link will open the linked table or record.

If the search only selects one record, the same form is shown, but with the data from the record filled in ✍️. Now it's possible to change the content. The changes are written to the database when you press the button Update (parameter =update). A new, empty form could be shown with the button New (parameter =empty) and if you have written data into this empty form, you can add it as a new record with the Add button (parameter =insert) 🌱. Last but not least, there is a Delete button (parameter =delete) 💀. In all of these cases, the content of the form is sent to the page itself, and the Execute method at the start of the page executes the desired action.

More comments can be found inside the source ([# #] blocks) 👀

---

and that's the whole vibe, bestie 💖 this lil masterpiece came from perl v5.34.0, dated 2022-06-13, and it's the Intrors(3pm) page — iconic, no notes, periodt 👑✨🫶