Reusing code for Mysql and Mason Application

neilwatson has asked for the wisdom of the Perl Monks concerning the following question:

I'm working on a Mason, Mysql inventory application. For any item, whose information may be in one or more tables, the user will have an option to view, edit or add a new item. I want avoid having three fully separate pieces of code that each fetch and then display. I think it would be easier to share some common code.

My idea was to have three separate components. One to view, one to edit, and one that gets the data (Mysql/DBI) that gives it back to the first two components.

Consider these simple, untested, examples:

View data:

<%perl>
# views currency data

my $data_structure = $m->comp('/comp/get_currency.mas');

print" <h3>Currencies:</h3>

<table cellpadding=2>

<tr id=tableheadcolor>
    <th>Currency</th>
    <th>Cid</th>
</tr>\n";
$row=0;

foreach ??? #what type of data structure should I have {

    $row++; 
    if ($row/3 == int($row/3)){
        print "<tr id=shadetable>\n";
    }else{  
        print "<tr>\n"; 
    }
    print "\t<td>".# data?."</a></td>\n";
    print "\t<td>".# data?."</td>\n";
    print "</tr>\n";
}
print "</table>\n";
</%perl>
[download]

Edit or add data:

<%args>
    $cid => 0
</%args>
<%perl>
    # if a currency to edit was passed to this form then
    # get the data and place in the form.
    # otherwise present a blank form
    my $data_structure = $m->comp('/comp/get_currency',
    cid => $cid);

</%perl>

    <h3>Make a new currency entry</h3>

<form action=comp/insert.mas?form=add_currency method=post>

    <table cellpadding=2>
    <tr>    
        <td align=right>Currency:</td>
        <td><input name=currency value=<& $some_structure &> type=text
+ size=5></td>
    </tr>   
    </table>
    <input type=submit>

</form>

<& /view_currency.html &>
[download]

Get data from database

%perl>
# get data requested and return
my ($statement, $sth, $ref);

$statement = 'SELECT 
cid,
currency
FROM currency ORDER BY currency;';

$sth = $dbh->prepare($statement) or die "Couldn't prepare statement: $
+dbh->errstr";
$sth->execute or die "Couldn't execute statement: $dbh->errstr";

while ($ref = $sth->fetchrow_hashref){
    # store one or more rows of data in a structure
}

return $data_structure
</%perl>
[download]

Am I on the right track? If so, what kind of data structure should I create to pass the table data back and forth?

An even better idea would be to pass the SELECT statement to a generic get_data component that would return the table in some structure. Is that possible?

Neil Watson
watson-wilson.ca

Comment on Reusing code for Mysql and Mason Application Select or Download Code

Replies are listed 'Best First'.
Re: Reusing code for Mysql and Mason Application by Arunbear (Prior) on Aug 06, 2004 at 21:30 UTC
Here's my suggestion: firstly use Class::DBI for data access: `package Currency; use base 'Class::DBI::mysql'; __PACKAGE__->set_db('Main', 'dbi:mysql:dbname', 'user', 'password'); __PACKAGE__->set_up_table("currency"); 1;` [download] Amongst (many) other things, this gives you an easy way to retreive all records in the currency table, or to retreive a particular record given its primary key. Class::DBI has very good documentation. Now you can use it like this in the view component: `<h3>Currencies:</h3> <table cellpadding=2> <tr id=tableheadcolor> <th>Currency</th> <th>Cid</th> </tr> % while (my $currency = $iter->next) { % $row++; % $shadetable = ($row % 3 == 0 ? q{id="shadetable"} : ""); <tr <% $shadetable %>> <td>$currency->currency</td> <td>$currency->cid</td> </tr> % } </table> <%once> use Currency; my $iter = Currency->retrieve_all; </%once> <%init> my $row=0; my $shadetable; </%init>` [download] And like this in the form component: `<h3>Make a new currency entry</h3> <form action="comp/insert.mas?form=add_currency" method="post"> <table cellpadding=2> <tr> <td align=right>Currency:</td> <td><input name="currency" type="text" value="<% $currency_val + %>" size="5"></td> </tr> </table> <input type="submit"> </form> <& /view_currency.html &> <%args> $cid => 0 </%args> <%init> my $currency_val = ""; if($cid ) { $currency_val = Currency->retrieve($cid)->currency; } </%init>` [download] Finally, I'd strongly suggest reading the Mason Book (the whole thing is available to read online).	[reply] [d/l] [select]

Replies are listed 'Best First'.

Re: Reusing code for Mysql and Mason Application
by Arunbear (Prior) on Aug 06, 2004 at 21:30 UTC

Class::DBI

package Currency;
use base 'Class::DBI::mysql';

__PACKAGE__->set_db('Main', 'dbi:mysql:dbname', 'user', 'password');
__PACKAGE__->set_up_table("currency");

1;
[download]

<h3>Currencies:</h3>

<table cellpadding=2>

<tr id=tableheadcolor>
    <th>Currency</th>
    <th>Cid</th>
</tr>

% while (my $currency = $iter->next) {
%    $row++; 
%    $shadetable = ($row % 3 == 0 ? q{id="shadetable"} : "");

     <tr <% $shadetable %>>
       <td>$currency->currency</td>
       <td>$currency->cid</td>
     </tr>
     
% }

</table>

<%once>
use Currency;

my $iter = Currency->retrieve_all;
</%once>

<%init>
my $row=0;
my $shadetable;
</%init>
[download]

<h3>Make a new currency entry</h3>

<form action="comp/insert.mas?form=add_currency" method="post">

    <table cellpadding=2>
    <tr>    
        <td align=right>Currency:</td>
        <td><input name="currency" type="text" value="<% $currency_val
+ %>" size="5"></td>
    </tr>   
    </table>
    <input type="submit">
</form>

<& /view_currency.html &>


<%args>
    $cid => 0
</%args>

<%init>
my $currency_val = "";
if($cid ) {
    $currency_val = Currency->retrieve($cid)->currency;
}
</%init>
[download]

Mason Book

[reply]
[d/l]
[select]