2015

12 December 2015
in mason, perl
2 min read

DB.pm Library

Hello, I'm finally back! Yes not much posts lately, but lots of coding - it's a lot considering that I'm working full time and that this is only a side project on my spare time.

After some previous attempts, I decided that I had to write a library to manage queries, to apply filters and searches, to move to the first/last/previous/next record, and to update and insert data.

I finally came out with DB.pm library, and even if it still needs to be improved I'm very proud of how small, elegant and clean it is!

I might rename it to Sentosa::SQL as I might reuse it on other projects as well.

Syntax

You need to define a $columns array reference as the following:

my $columns = [
  { col => 'id', 'pk' => 1},
  { col => 'name'},
  { col => 'surname'}
];

where id is the primary key, and then you have to call selectQuery and get a hash reference like this:

my $q = Sentosa::Db::selectQuery(
  'mytable',
  $columns,
  'ASC',
  undef,
  0, #offset
  10, #number of records
  'SQLite'
);

this will return three queries and three array references like below:

  print $q->{query}, "\n";
  print "(".join(',', @{$q->{query_data}}).")\n";

  print $q->{query_search}, "\n";
  print "(".join(',', @{$q->{query_search_data}}).")\n";

  print $q->{query_limit}, "\n";
  print "(".join(',', @{$q->{query_limit_data}}).")\n";

yes I could just call the same function thrice with different parameters (that would make the library even more elegant), but at the moment I have good reasons to call it once, but I might change my mind in a near future.

query: is the query, with a first level filter
query_search: is the query with a first filter and second level search filter
query_limit: is the query with a first level filter and a second level search filter, and also a limit on the number of rows (useful for pagination)

Filters

There are two levels of filters, a main filter that is applied to all of the queries, and an additional search filter that is applied only to query_search and query_limit.

This could be useful because a main filter can be applied to a table, but the user can search for some records within the already filtered table.

For example, many users could have records in the same table:

ID	User	Song
1	1	AC-DC - Ride On.mp3
2	1	Metallica - Enter Sandman.mp3
3	2	The Sweet - Action.mp3
4	2	Sixx::AM - Life Is Beautiful.mp3
5	2	Metallica - Metal Militia.mp3

We can filter the previous table for each user, then the user can apply an additional filter:

my $columns = [
  { col => 'id', 'pk' => 1},
  { col => 'user', 'filter' => 1},
  { col => 'song', 'search' => 'Metallica', 'searchcriteria' => 'SUB'}
];

09 August 2015
in mason, perl
2 min read

Global variable $dbh

The name of my project is "Sentosa AutoForms", and it is going to be driven by a SQL database. I'm using SQLite but it can be changed at a later time to MySQL or Postgresql.

Let's create my new project:

poet new Sentosa

cd Sentosa

and let's create our SQLite database. The schema goes on the db/schema.sql file:

create table if not exists af_info (
      id integer primary key autoincrement,
      attribute string not null,
      value string not null
    );

insert into af_info (attribute, value) values
('name', 'Sentosa AutoForms'),
('version', '0.01');

and the actual database data goes to data/sentosa.db:

sqlite3 -batch data/sentosa.db < db/schema.sql

This is how I would access this DB with a standard Perl application (using the DBI module):

{% raw %}
#!/usr/bin/perl

use strict;
use warnings;
use DBI;

my $dbh = DBI->connect(          
    'dbi:SQLite:dbname=../data/sentosa.db',
    '',                          
    '',                          
    { RaiseError => 1 },         
) or die $DBI::errstr;

my $sth = $dbh->prepare('SELECT value FROM af_info WHERE attribute="name"');
$sth->execute();

my $name = $sth->fetch();

print @$name;
print "\n";

$sth->finish();
$dbh->disconnect();
{% endraw %}

and this is how I am connecting to it in my web application:

{% raw %}
package Sentosa::Import;
use Poet::Moose;
extends 'Poet::Import';

use DBI;

method provide_var_dbh ($caller) {
  return DBI->connect(          
    'dbi:SQLite:dbname=data/sentosa.db',
    '',                          
    '',                          
    { RaiseError => 1 },         
  ) or die $DBI::errstr;
}
1;
{% endraw %}

and this is how I'm using it on my component index.mc:

{% raw %}
<%class>
use Poet qw($dbh);
</%class>
<% $.title %>
<%init>
my $sth = $dbh->prepare('SELECT value FROM af_info WHERE attribute="name"');
$sth->execute();

my $name = $sth->fetch();

$.title("Welcome to @$name");
</%init>
{% endraw %}

07 August 2015
in mason, perl
2 min read

True Global Variable for Mason in Poet

A few days ago, while I was roaming around Singapore, I offered a bounty (twice) on an already existing question on StackOverflow Global Variable mason2 in Poet where the poster was asking how to use global variables in Mason. And I got a very nice and useful answer :)

Here I am describing how to get a true global and persistent variable. The content of a true global and persistent variable is preserved between requests.

Has your web server 10 different threads that answer HTTP requests? Then you'll end up having 10 different variables. Has your web server 1000 threads? Well... you got the idea!

But what's the proper use of such global variables? Any thread can potentially serve any request, so we cannot predict which server is going to answer which request. Private informations doesn't have to go here.

But most web applications are driven by some datatabase, and connecting to a database has cost in terms of performance. Since the database won't (usually) change between requests why do we have to establish the same database connection over and over again?

Actually, we don't have to: we can define a global variable $dbh whose value is a valid database handler, which will be preserved between requests. This database handler value will born with the thread and will die with the thread!

Oh but we still have to be careful since the handler could have been valid at the time of the creation of the thread, but then something bad could have happened (e.g. a timeout could have occoured, or some evil DBA might had killed your connection, just for the sake of it). Somehow we'll have to manage this.

Okay now the fun part. Please read Poet::Import and the awarded answer which I'm going to use as a reference, and then let's do some coding!

Global Variable Example

# generate app Myapp
poet new Myapp
cd Myapp

add a class Myapp::Import, here's where we have to define our global variables:

vi lib/Myapp/Import.pm

and then let's add some code:

package Myapp::Import;
use Poet::Moose;
extends 'Poet::Import';

# create some variable
has 'mytemp' => (is => 'ro', default => 'my temp value');

method provide_var_mytemp ($caller) {
    return $self->mytemp;
}
1;

then we can test our global variable in our components, let's print its value on comps/index.mc:

<%class>
use Poet qw($mytemp);
</%class>
I got this <% $mytemp %> variable.

Let's see how to store a database handler, on my next post!