Category: PManual

So far, you are assumed to run topics for a single database table. For relational database, a search usually requires multiple table joins.

While you can build raw SQL to do this, as described in Section 2.2,  Genelet has a built-in solution which is cleaner and can take advantage of pagination etc. as well. Here is it.

 

Step 1: Add New Attribute current_tables to Model.pm

This array reference current_tables contains all joint tables, each of which is represented by a map having the following key-values:

• name: the table name
• alias: optional, the alias of the table
• type: one of join types – INNER or LEFT
• using: to use USING clause in joining the table
• on: to use ON clause in joining the table

The first item in current_tables is for the main table having only name and alias.

 

Step 2: Re-define topics_pars to be HASH

By default, the searched fields will use their table (or table alias) names as prefixes. You can assign labels to these cumbersome fields, by re-defining topics_pars as a hash.

 

Example

You’d like to get a list of all children in full names from a family table and a children table.

create table family (
  family_id int not null auto_increment,
  last_name varchar(255) not null,
  mother varchar(255),
  father varchar(255),
  primary key (family_id)
);

create table child (
  child_id int not null auto_increment,
  family_id int not null,
  first_name varchar(255) not null,
  primary key (child_id),
  index (family_id)
);

This SQL will produce the correct list:

SELECT f.family_id AS family_id, c.child_id as child_id, f.last_name AS last_name, c.first_name as first_name
FROM family f
INNER JOIN child c ON (f.family_id=c.family_id)

You may override the topics method in Child’s Model.pm to achieve the goal. Then you have to set up own pagination.

Alternatively, you may use:

__PACKAGE__->setup_accessors(
  ...
  current_tables => [
    {name=>"family", alias=>"f"},
    {name=>"child",  alias=>"c", type=>"INNER", on=>"f.family_id=c.family_id"}
  ],
  topics_pars    => {
    "f.family_id" => "family_id",
    "f.last_name" => "last_name",
    "c.child_id"  => "child_id",
    "c.first_name"=> "first_name"
  }
);

which will use the inherited topics to search and do other functions, as if it were a single table.

If rowcount is passed in query or manually added in Filter.pm, Genelet will display searched results in pages (i.e. pagination). Specifically,

1. sending a request with rowcount=20 will turn on pagination
2. if no pageno, then pageno=1 will be taken as the default.
3. if total_force=1 in the model, the total number of rows and the total number of pages, totalno and maxpageno respectively, will be calculated for pageno=1.
4. Use pageno=2 for the second page, and so on.
5. sortby should be specifically defined, since the order of rows returned would depend on how they are sorted.
6. If totalno is expected to change in real-time, you should arrange pagination in such a way that the first page, case 2 above, could be clicked often, so as to have totalno and maxpageno refreshed.

 

Components should inherit from the project. In this example, the component is named Mycomponent:

package Myproject::Mycomponent::Model;

use strict;
use Myproject::Model;
use vars qw(@ISA);

@ISA=('Myproject::Model');

__PACKAGE__->setup_accessors(
  current_table  => string,
  current_key    => string,
  current_id_auto=> string,
  current_insupd => string,

  insert_pars    => array_ref,
  topics_pars    => array_ref,
  edit_pars      => array_ref,
  update_pars    => array_ref,
  empty_pars     => array_ref,
  key_in         => hash
);

1;

The RESTful verbs topics, edit, insert, update, insupd and delete are now ready to use.

You can add new method or override an existing RESTful verb following Section 2.2.

Let project’s Model.pm inherit from Genelet::Model, so you can put project-wide settings, like database type, pagination and common methods, into project’s Model.pm. Let component’s Model.pm inherit from that of project.  So, all the components will share the same project settings.

In this example, the project is named Myproject.

package Myproject::Model;

use strict;
use Genelet::Model;

use vars qw(@ISA);
@ISA = qw(Genelet::Model);

__PACKAGE__->setup_accesors(
  'sortby' => 'sortby',
  'sortreverse' => 'sortreverse',
  'field' => 'field',
  'empties' => 'empties',
  'rowcount' => 'rowcount',
  'pageno' => 'pageno',
  'totalno' => 'totalno',
  'max_pageno' => 'max_pageno',
  'total_force' => 1
);

1;

So the pagination information is setup for the whole project. You can add project-wide methods here so they are available in all inherited classes.

Your application would have more customized actions than the few standard RESTful verbs. So one of your daily works would be to add new methods to model, or to override the existing RESTful methods. When adding new method, please follow the rules:

 

Rule 1: Use the action as the method name.

For example, to add a new action findFriends:

sub findFriends {
  my $self = shift;
  my $extra = shift;
  ...
  return;
}

Note that the following attributes can be taken as granted and are good enough for you to finish any sophisticated method.

• $self->{ARGS}: the input data.
• $self->{DBH}: the standard DBH handler.
• $self->{STORAGE}: the pre-defined caching object.
• $self->{LOGGER}: the logger.

 

Rule 2: return nothing for success, otherwise return the error found.

The last statement in a class method is always

return;

If any error is met, return that error code or an explanation string. The error will be shown in HTML or API.

 

Rule 3: Save main output data into LISTS

The resultant data are always assigned to $self->{LISTS} . Usually you create empty array reference, run a relevant database method as described in this section, which will fill in the array. For example, to search for one’s friends:

sub findFriends {
  my $self = shift;
  $self->{LISTS} = [];
  my $err = $self->select_sql($self->{LISTS},
    "SELECT * FROM tbl_friends WHERE user_id=?",
    $self->{ARGS}->{user_id});
  return $err if $err;

  return;
}

Or even simpler:

sub findFriends {
  my $self = shift;
  $self->{LISTS} = [];
  return $self->select_sql($self->{LISTS},
    "SELECT * FROM tbl_friends WHERE user_id=?",
    $self->{ARGS}->{user_id});
}

In HTML template or API, the data will be returned as the value of key findFriends.

All other type of data can be saved to $self->{OTHER}, using your customized key names. They will be output as multiple key-value pairs in HTML or API.

NAME

Genelet::Model – Genelet Model Class

 

SYNOPSIS

my $dbh = DBI->connect(...);
my $ARGS = {key=>value, key=>value};
my $model = Genelet::Model->new(dbh=>$dbh, ARGS=>$ARGS);
my $extra = {key=>value};
 
my $error = $model->insert($extra);
die $error if $error;
my $lists = $model->lists();
warn Dumper $lists;

$error = $model->topics();
die $error if $error;
$lists = $model->lists();
warn Dumper $lists;

 

DESCRIPTION

This class provides the following functionalities:

1. Unified data structures for use in Controller
2. Built-in RESTful verbs
3. Multiple table searches using JOINS
4. Pagination
5. Trigger: retrieving data from, or passing data to other models

 

CONSTRUCTOR

If you run model in a standalone program, the constructor should be like:

my $model = Genelet::Model->new(dbh=>$dbh, args=>$args);

In Genelet web project, the model is already initialized and the inputs are collected in early phases and ready to use:

• args: input data collected from query and POST body, as a hash reference.
• dbh: a database handler.
• logger: optional, a Genelet::Logger object for logging purpose.
• storage: optional, a read-only caching object in FCGI.

 

OUTPUT

The following attributes are output data. Your program should generate them. Internally, Genelet will deliver them back to Controller for displaying or API:

• lists: the main output data, as an array reference
• other: other data, as a hash reference

 

 

CRUD

Here is the list of attributes for database table information.

• current_table: the table name.
• current_key: the primary key (PK). If it consists of several fields, put them as an array reference.
• current_id_auto: if there exists an auto increment field, put it here.

Here is the list of attributes for CRUD.

• insert_pars: array reference, defining which fields will be inserted. Fields without input value will be ignored.
• topics_pars: an array reference, defining which fields will be searched for multiple items.
• edit_pars: an array reference, defining which fields will be selected for one specific item id.
• update_pars: an array reference, defining which fields will be updated.
• insupd_pars: an array reference, defining unique-key names. When run Genelet’s special verb insupd, you add a new row if the unique keys do not exist, you update the existing record if the unique keys exist.
• empties: an array reference. In UPDATE, fields without input values will be ignored. The fields in this list will be forced to be set NULL in the table.
• key_in: a hash reference like {table=>fk, table=>fk}, simulating foreign key (FK) behavior. If to delete an item using the PK and if this PK still exists in other tables as FK, Genelet will stop the deletion. This hash defines all FKs in other tables for the current PK of this table.

 

PAGINATION

Here is the list of attributes for pagination.

• sortby: when SELECT, you may use a field to sort the output. This is the variable name you use to pass the sorting field in query. The default is sortby.
• sortreverse: used together with sortby, to specify if the data should be reversely sorted. For example, if you have defined it myreverse, then you should pass myreverse=1 for reverse sorting. The default is sortreverse.
• fields: You can specify a subset of topics_pars or edit_pars on-the-fly. Use this variable to pass it in query. The default is fields.
• rowcount: when SELECT, passing rowcount will trigger pagination. This is the variable to pass rows per page. For example, you may define it eachpage and pass eachpage=50 in query. The default is rowcount.
• pageno: in pagination, use it to pass the page number. The default is pageno.
• totalno: variable name for the total rows. For example, if you define it how_many, then you can pass how_many=1234 in query. In some API, it will be calculated and returned as {“how_many”: 1234}. The default is totalno.
• maxpageno: Same as totalno, but for the total pages.
• total_force: default to 0. If it is 1 and pageno=1, totaln0 and maxpageno will be calculated and returned in API.

 

CRUD METHODS

insert

my $err = $self->insert(extra);

Use this method to insert a new row into table, following the definition of $self->{INSERT_PARS} and the input $self->{ARGS}.  The optional argument extra, if exists, will override the key-values pairs in $self->{ARGS}.

If successful, no error will be returned and the inserted row will be put as the first element in $self->{LISTS}. If failed, an error will be returned and the error message will be display in HTML or API.

 

topics

my $err = $self->insert(extra);

Use this method to search all records in the table. The row is saved as the first element in $self->{LISTS}. An error will be returned if something goes wrong.

Optional extra lets you to place extra search conditions. See JOIN and pagination for more sophisticated search conditions.

 

search conditions

Normally, each key-value pair in extra represents a field and its value. In case the value is an array reference, it will be interpreted as an IN condition in WHERE. For example, {firstname => [“John”, “Mary”]} will be interpreted as WHERE firstname IN [“John”, “Mary”].

In case the value is a hash reference, it will be interpreted as operator-value pairs with AND logic in WHERE . For example, {a => { “>” => 0, “<=” => 5}} will be interpreted as WHERE (a>0 AND a<=5).

The special key _gsql will be interpreted as a raw SQL statement. For example, {_gsql => ‘firstname = “John” AND lastname != “Smith”‘} will be interpreted as WHERE firstname=”John” AND lastname != “Smith”.

 

edit

my $err = $self->edit(extra);

Use this method to search one record by current_key. extra lets you to place extra search conditions. For any login role of none admin type, visitor’s login ID is always enforced as a condition. The result is saved as the first element in $self->{LISTS}.

 

update

my $err = $self->update(extra);

Use this method to update a row. extra lets you to place extra update conditions. The updated row is saved as the first element in $self->{LISTS}.

 

insupd

my $err = $self->insupd(extra);

Use this method to insert a row. If the unique key or key combination $self->{CURRENT_INSUPD} is already existing, the old row will be updated instead of inserted. The new or updated row is saved as the first element in $self->{LISTS}. The optional argument, extra will override the values in $self->{ARGS}.

 

delete

my $err = $self->delete(extra);

Use this method to delete a row by the PK. extra lets you place deletion’s conditions. If the PK exists in other tables indicated in $self->{KEY_IN}, the operation will stop and nothing would be changed.

 

METHODS

existing

my $err = $self->existing(field, value, table);

It checks if field of value exists in table. Return nothing if the statement is true, or 1075 if it is wrong.

 

randomid

my $err = $self->randomid(max, trials, field, table);

It generates a random id for field in table, not exceeding max. If after trials times it still could not get one (because all random ids in trials are already existing in the table), it will give up and return 1076.

To restrict the generated random id between a and b, pass [a,b] as the max.

 

METHODS ON OTHER MODEL

call_once

my $err = $self->call_once(page, extra);

This method runs an action on another model, defined by

page = { model=>STRING, action=>STRING }

The other model will be constructed using the same input, logger and database handler as those of the current model. There is an optional extra whose meaning is defined as above.

If successful, the output data from the other model will be saved as the value to key model_action in this model’s $self->{OTHER}. If failed, the error will be returned.

 

call_nextpage

my $err = $self->call_nextpage(page, extra);

This method runs action on the other model for many items in $self->{LISTS}. The other model will be constructed using the same input, logger and database handler as those of the current model. There is an optional extra whose meaning is defined as above.

To run action on the other model, page is defined as

page = {
  model       => STRING,
  action      => STRING,  
  relate_item => {this_field=>other_field, this_field=>other_field, ...},
  manual      => {field=>value, field=>value, ...}
}

Here relate_item links this_field in $self->{LISTS} to the corresponding other_field in the other model. The value of this_field will be used as a search or insert condition.

The manual map allows you to assign manually key-value pairs as search condition.

For example, you have derived customers from topics. You’d like to see each customer’s purchasing history that is handled by another model. So you run call_nextpage in this model, using the other model name and action topics, which will search each customer’s history.

Another example: you just had a new registration to your customer table with insert. You’d like to add the new customer to a sponsorship table, which tracks who has referred the customer.

If successful, the result will be saved as an new key-value pair with key model_action in $self->{LISTS}. For example, if you have a list of customers:

$self->{LISTS} = [
  {customer_id=>1111, name=>"John Kerr", ...},
  {customer_id=>2222, name=>"Peter Pan", ...},
  ...
]

You want to get each customer’s purchase history. You may call:

my $err = $self->call_nextpage({model=>"Project::Purchase::Model", action=>"topics", relate_item=>{customer_id=>"customer_id"}}, {status_id=>1});

will update $self->{LISTS} to have new key Project_Purchase_Model_topics.

$self->{LISTS} = [
  {customer_id=>1111, Project_Purchase_Model_topics=>[...], name=>"John Kerr", ...},
  {customer_id=>2222, Project_Purchase_Model_topics=>[...], name=>"Peter Pan", ...},
  ...
]

Genelet uses call_once and call_nextpage in Triggers.

1. Introduction
2. Model
   1. Genelet::Model
   2. Add Class Method
   3. Project
   4. Component
   5. Pagination
   6. JOIN Tables
   7. Triggers
3. Filter

 

As we mentioned, the web development in Genelet can be seen as simply as to make one Model.pm and one Filter.pm for new components.

In the MVC designing pattern, Model manages data.

Throughout Genelet programming, you are assumed to know about your data model and can write SQL to operate on tables and stored procedures.

 

NAME

Genelet::DBI – Generic Relational Database Class

 

SYNOPSIS

use DBI;
use Genelet::DBI;

my $dbh = DBI->connect(...);
my $dbi = Genelet::DBI->new(dbh=>$dbh);

my $error = $dbi->do_sql(SQL, @bind_values);

my $lists = [];
my $error = $dbi->select_sql($lists, SQL, @bind_values);

my $lists = [];
my $hash = {};
my $outs = [column, column, ...];
my $error = $dbi->select_do_proc($lists, $hash, $outs, PROCEDURE, @bind_values);

 

DESCRIPTION

This class handles database operations using raw SQL statements or stored procedures.

 

CONSTRUCTOR

my $dbi = Genelet::DBI->new(dbh=>$dbh);

where $dbh is a database handler object from DBI->connect(…).

 

MAIN METHODS

 

do_sql

my $error = $dbi->do_sql(SQL, @bind_values);

This is for INSERT, UPDATE and DELETE. Pass binding variables as arguments. It catches error as the return.

select_sql

my $lists = [];
my $error = $dbi->select_sql($lists, SQL, @bind_values);

This is for SELECT. The searched results are saved in $lists as an array reference, in which each row is a hash reference using column names as keys. Catch error as the return.

select_do_proc

my $lists = [];
my $hash = {};
my $outs = [column, column, ...];
my $error = $dbi->select_do_proc($lists, $hash, $outs, PROCEDURE, @bind_values);

A Stored Procedure could output values and produce multiple searches a the same time. After a successful run, the output of variables $outs are saved in $hash, and the searched results in $lists, as an array reference. Each item in $lists is itself an array reference, storing one SELECT data set as in select_sql().

 

OTHER METHODS

 

do_sqls

my $error = $dbi->do_sqls(SQL, \@bind_variables, \@bind_variables, ...);

If you insert multiple records at once, you may use do_sqls (with s) instead of running multiple do_sql. Each argument is one row containing the binding variables as an array reference.

select_sql_label

my $lists = [];
my $labels = [label, label, ...];
 my $error = $dbi->select_sql($lists, SQL, $labels, @bind_values);

It is similar to select_sql but uses given labels as hash keys.

do_proc

my $error = $dbi->do_proc(PROCEDURE, @bind_values);

or

my $outs = [column, column, ...];
my $hash = {};
my $error = $dbi->do_proc($hash, $outs, PROCEDURE, @bind_values);

Runs a Stored Procedure without search. The first form outputs nothing. The second form outputs the values of variables $out in $hash.

select_proc

my $lists = [];
my $error = $dbi->select_proc($lists, PROCEDURE, @bind_values);

Runs a Stored Procedure without output variable. It saves the searched results in $lists, which, like select_do_proc, is an array reference of array references of hashes.

A CGI script will be generated by help.pl. You can manually create it too.

#!/usr/bin/perl

use lib qw(/home/user/tutoperl/lib);

use strict;
use JSON;

use DBI;
use XML::LibXML;
use LWP::UserAgent;

use File::Find;
use Data::Dumper;
use URI;
use URI::Escape();
use Digest::HMAC_SHA1;
use MIME::Base64();
use Template;

use Genelet::Dispatch;

Genelet::Dispatch::run("/home/user/tutoperl/conf/config.json", "/home/user/tutoperl/lib", [qw(Table1 Table2)]);

exit;

The program loads the necessary 3rd-party modules, then calls run on Genelet::Dispatch to launch CGI or FCGI. There are 4 arguments you can pass to run, in the following order:

1. The full path the configuration file.
2. The library path of project’s Perl classes.
3. By default, we assume all the directory names under the project classes are valid components. You can rewrite it by placing specific components here as a array reference.
4. 1 for FCGI and 0 for CGI, which is the default. CGI is recommended for development.
5. The maximal size in bytes for uploading files. The default is 3M bytes.
6. A read-only hash reference, which you can setup once and used for all the FCGI children.

 

NAME

Genelet::Accessor

 

SYNOPSIS

package Foo;
use base qw(Genelet::Accessor);
Foo->setup_accessors( %init_hash );

 

DESCRIPTION

By inheriting your class from Genelet::Accessor, or one of its children such as Genelet::Model and Genelet::Filter, you can generate Getter and Setter for class attributes and initialize their values.

 

FUNCTION

__PACKAGE__->setup_accessors( %hash );

The keys and values in hash are the attributes and their default values. For example,

__PACKAGE__->setup_accessors( exmpl=>"some string" );

You can get it as $self->exmpl() resulting in “some string”. Or set it up as $self->exmp(“other”).

 

MULTI-LEVEL INHERITANCES

Setting up an attribute value via setup_accessors will override that in parents.