SQL::Composer – mapping SQL from Perl and back

SQL::Composer is yet another SQL mapper. But unlike others it does something very useful. It allows you to not only build an SQL from a Perl structure, but when getting the data from database to map it back to the usable Perl structure. We used that for this horse racing betting guide.

Some times you don’t need an ORM, but you would like to have an SQL builder that hides escaping, allows you to specify joins and returns a usable Perl structure when getting data from the database. SQL::Composer does exactly that.

For several years I have been waiting for a module that would do this, but unfortunately all the modules I have tried lack the join support at least. And I use joins a lot. Also they are tightly coupled with other ORMs or require a lot of manual parsing and stuff. So I have written my own module and several people found it handy. So here it goes.

Let’s start from the most advanced example where the best part of SQL::Composer is shown. For example we have Review -> Book -> Author tables. And we want to fetch a Review with all the related information.

my $expr = SQL::Composer::Select->new(
    from    => 'review',
    columns => ['text'],
    join    => [
        {
            source  => 'book',
            columns => ['title'],
            on      => [id => {-col => 'review.book_id'}],
            join    => [
                {
                    source  => 'author',
                    columns => ['name'],
                    on      => [id => {-col => 'book.author_id'}]
                }
            ]
        }
    ],
    where => [id => 1]
);

Let’s generate SQL:

my $sql = $expr->to_sql;

# SELECT
#     `review`.`text`,
#     `book`.`title`,
#     `author`.`name`
# FROM `review`
# JOIN `book` ON `book`.`id` = `review`.`book_id`
# JOIN `author` ON `author`.`id` = `book`.`author_id`
# WHERE `review`.`id` = ?

And get the bind values:

my @bind = $expr->to_bind;

After getting an ARRAYREF from DBI we will get a correctly mapped HASHREF that can either be used as is or can be converted into an object very easily (simple and tiny Perl value objects without additional functionality).

my $sth = $dbh->prepare($sql);
my $rv  = $sth->execute(@bind);

my $rows = $sth->fetchall_arrayref;
my $row_object = $select->from_rows($rows)->[0];

my $objects = $expr->from_rows($rows);

If $rows is something like:

[['Good', 'Perl programming', 'YAPH']]);

then we will get:

# [
#     {
#         'book' => {
#             'title'  => 'Perl programming',
#             'author' => {
#                 'name' => 'YAPH'
#             }
#         },
#         'text' => 'Good'
#     }
# ];

This structure is intuitive and maps perfecly to hash based Perl objects. If you do not want joins be embedded into other joins, just don’t specify them as that:

my $expr = SQL::Composer::Select->new(
    from    => 'review',
    columns => ['text'],
    join    => [
        {
            source  => 'book',
            columns => ['title'],
            on      => [id => {-col => 'review.book_id'}],
        },
        {
            source  => 'author',
            columns => ['name'],
            on      => [id => {-col => 'book.author_id'}]
        }
    ],
    where => [id => 1]
);

And we will get:

# [
#     {
#         'book' => {
#             'title' => 'Perl programming',
#         },
#         'author' => {
#             'name' => 'YAPH'
#         }
#         'text' => 'Good'
#     }
# ];

Among joins and data mapping SQL::Composer supports all expected expressions you would find in other SQL builders.

[foo => 'bar']              => "`foo` = ?",   ['bar']
[foo => { '!= ' => 'bar' }] => "`foo` != ?",  ['bar']
[foo => {-col => 'bar'}]    => "`foo` = bar", []

Also it supports sometimes hard to build sql expressions like:

[created => {'>' => \['ADD_DATE(NOW(), INTERVAL ? SECOND', 10]}]

# "created >= ADD_DATE(NOW(), INTERVAL ? SECOND)", [10]

I have been using SQL::Composer in ObjectDB for quite a while, and it works perfectly well in production with real world data and problems (of course if you like me try to reduce logic in SQL queries). The main focus is on joins and related objects, since when you have a “good enough” normalization you have a lot of joins. But as I stated earlier some people may use SQL::Composer directly since it is very handly and removes a lot of boilerplate from your Perl code.

One can say that writing raw SQL is more readable. Yes, that is true for very complex queries. But most of the time you need some kind of automation, arguments injections and so on and you end up implementing some kind of an SQL builder yourself. Also it is easier to map data from the database when it is structurely presented rather than parsing SQL which is not very easy and not very portable.

App::chronos – automatically record your computer activities

Often you want to know how much time you spend on a random computer activity during your work/home day. There are lots of apps that allow you to record the time, unfortunately you have to manualy turn them on and off. It can be really frustraiting when you forget to do so. So I have written an app that does that automatically.

chronos listens for X11 window switches and records how much time you have spent on every application. It runs a set of filters that guess the type of the application and its name. Moreover if the application can answer more than a question “what type am I” and additionally can provide like a visited URL, a contact you’re chatting with and so on, then the filter can parse that information and add to the log.

Activity details

As previously said the filters can parse additional information. For example right now if the application is a Firefox or a Chromium browser than the currently visited URL is detected. This is done by parsing current sessions. In case of a Skype or Pidgin, for example, the current contact name is detected.

Output

chronos prints the events to the stdout, so the log can be easily saved to any file you like. The format is simple: a single line in JSON, with UNIX epoch timestamps. For example:

{
   "_end" : 1412750698,
   "_start" : 1412750693,
   "application" : "Chromium",
   "category" : "browser",
   "class" : "\"Chromium\", \"Chromium\"",
   "command" : "",
   "id" : "0x4a00048",
   "name" : "\"reddit: the front page of the internet - Chromium\"",
   "role" : "\"browser\"",
   "url" : "www.reddit.com"
}

The JSON part has several fields. role, class, name and command are the fields recorded from X11 and they are saved as is. The filter program could for example detect what kind of a command line I am running (this time vim) and what kind of a file I am working on.

Reporting

Reading and analyzing the log file isn’t very handy, that is where the report command steps in.

As you already know the event is a JSON object that has various fields. The report tool can search through those events, group the results and sort the results by the time spent on them.

Show top 10 visited URLs:

$ chronos report --fields 'url' --where '$category eq "browser"' --group_by 'url' log_file | head -n 10

00d 00:18:27 url=www.youtube.com
00d 00:05:29 url=github.com
00d 00:01:59 url=twitter.com
00d 00:01:25 url=code.google.com

Here I am showing only url field, searching for category named browser and group by url.

Using --where and --group_by various useful reports can be produced specific to your needs.

--where syntax

If you have noticed option --where has a Perl-like syntax. That is actually eval-ed into a Perl subroutine that is than run on every event. This way the where clause can be as profound as needed.

--from and --to

Timeout

To configure how often you want chronos to sleep before recording any activity use --timeout option.

Idle time

chronos also detects the idle time and stops recording the activity. Idle time is detected by running xprintidle and comparing it to the --idle_timeout option, which is 5 minutes by default. So if you don’t type anything or don’t move your mouse for 5 minutes the previous activity is considered as ended.

Flushing

Various bad things can happen during recording. This could be the power outage or accidental killing of the chronos process. In order to be more robust chronos periodically flushes the activity to the log file. This can be configured by --flush_timeout option. And you won’t loose the event recording when you’ve been working on it for several hours.

Contributing

Different people use different applications. I cannot write the filters for every application out there, so if you use chronos and want an application and its options to be parsed, just write a filter package, it’s as simple as:

package App::Chronos::Application::Skype;

use strict;
use warnings;

use base 'App::Chronos::Application::Base';

sub run {
    my $self = shift;
    my ($info) = @_;

    # It's not a Skype application
    return
      unless $info->{role} =~ m/ConversationsWindow/
      && $info->{class} =~ m/Skype/
      && $info->{name} =~ m/Skype/;

    # Yay, it's Skype, let's parse the contact name
    $info->{application} = 'Skype';
    $info->{category} = 'im';
    ($info->{contact}) = $info->{name} =~ m/^"(?:\[\d+\])?(.*?) - Skype/;

    return 1;
}

1;

Tips & tricks

I personally have a bash script that combines several reports:

#!/bin/sh

LOG_FILE=$1
LIMIT=10
COMMAND="perl -Ilib script/chronos"

echo 'Top categories:'
$COMMAND report --fields 'category' --group_by 'category' $LOG_FILE
echo
echo "Top $LIMIT talks:"
$COMMAND report --fields 'contact' --where '$category eq "im"' --group_by 'contact' $LOG_FILE | head -n $LIMIT
echo
echo "Top $LIMIT URLs:"
$COMMAND report --fields 'url' --where '$category eq "browser"' --group_by 'url' $LOG_FILE | head -n $LIMIT
echo
echo 'Idle time:'
$COMMAND report --where '$idle' $LOG_FILE

And then:

$ ./report.sh log_file | mail -s Activities vti

Mixins in Perl

If you want to use mixins in Perl you don’t have to install anything or play with symbol table yourself. It’s right there, in the core.

Mixins are basically not creatable classes, their roles is to embed methods into your class. It is seen as an alternative to multiple inheritance and is something like Roles–.

To embed methods you can use plain old simple Exporter!

package MyMixin
use parent 'Exporter';

our @EXPORT_OK = qw(log);

sub log {
    my $self = shift;

    # Yes, this works too!
    $self->some_internal_method;

    say @_;
}

package SomeClassElsewhere;

use MyMixin 'log';

sub do_stuff {
    my $self = shift;

    $self->log('it is here!');
}

sub some_internal_method {
    my $self = shift;
}

KISSing is nice!

Publishr — publish everywhere

For http://pragmaticperl.com I needed a tool to post the new issue announcement to several social networks. It ended up supporting Facebook, Twitter, LiveJournal, VK, Email, IRC, Jabber/XMPP, Skype and more.

Where to get it?

http://github.com/vti/publishr.

How to run

From the git repository:

perl -Ilib script/publishr --config publishr.json message.txt

Where message.txt looks like:

Status: This is the short title
Link: http://link-to-the-press-release
Image: /path/to/image.jpg
Tags: perl, pragmaticperl, journal

The
multiline
body

Of course every social network supports different kind of messages. This is handled by the so called channels. For example for Twitter publishr only uses Status, Link and Image.

The publishr.json configuration files looks like:

{
   "access" : [
      {
         "name" : "twitter access #1",
         "options" : {
            "access_token" : "",
            "access_token_secret" : "",
            "consumer_key" : "",
            "consumer_secret" : ""
         },
         "type" : "twitter"
      }
   ],
   "scenarios" : [
      {
         "access" : "twitter access #1",
         "name" : "post to pragmaticperl twitter",
         "options" : {}
      }
   ]
}

access is a list of channel credentials. You name them as you like, provide required options and use in scenarios. This is made so you can use the same access tokens for different scenarios, like posting to different Facebook groups etc.

In scenarios you can provide options with additional options, like an IRC channel etc.

Custom commands

Sometimes you will need to just run a custom cli program. For example this is how sending to Skype is done. In util directory you can find a skype-chat.py Python script which uses Skyp4Py library. In order to call that script you configure cmd scenario:

{
    "name":"skype",
    "access":"cmd",
    "options":{
        "env":{
            "PYTHONPATH":"/path/to/skype4py/"
        },
        "cmd":"./util/skype-chat.py 'Skype Chat' '%status% %link%'"
    }
}

Where %status% and %link% are replaced by values from the message.txt.

Running only specific scenarios or channels

Sometimes you would want to run just a specific scenario or a channel, there are options for this:

perl -Ilib script/publishr --config publishr.json \
    --scenario 'post to twitter' message.txt
perl -Ilib script/publishr --config publishr.json \
    --channel 'facebook' message.txt

Useful web application design patterns

I have collected the most useful web application design pattern that I use in web applications I code at work and also for my personal projects. They are presented in a way of a problem and a solution.

All of the examples are for PSGI/Plack based web applications. But I am sure they can be used in web frameworks as well.

Filter/Middleware oriented design

Many of the web application functionality can be separated into so called filter chains or middleware in terms of Plack. But it can be not just some light request/response altering, but rather a complete subsystem. Since middleware are already decoupled it’s easy to build simple and effective solutions.

Let’s take a look at the most common web application task: request dispatching. We want to take an HTTP-request and map it onto the Perl class, call it and then get a template rendered.

The naive solution might be to do that in a single place like most of the web frameworks do.

sub {
    my $env = shift;

    my $path_info = $env->{PATH_INFO};

    my $controller_class = $path_info

    return [404, [], ['Not found']] unless try_load_class $controller_class;

    my $res = $controller_class->new->run($env);

    return [200, [], $res];
}

It’s all good and nice until you want to use smth like routes here. Then you would want to add authorization, user roles, default templates etc.

You will have to move everything to controller thus making them fat and very hard to test, since you will have to mock the whole environment.

The better and simpler approach is to separate responsibilies of the request phrases into middleware. For example:

enable 'RequestDispatcher';
enable 'ActionDispatcher';
enable 'ViewDisplayer';

Where RequestDispatcher takes the HTTP-request and transforms it into understandable for web application object. For example you can use routes there. Then if the correct route is found ActionDispatcher creates controller and runs it. ViewDisplayer renders the appropriate template.

As a side effect you have a possibility to rendere a template without calling a controller, which is needed in many web applications.

Later on you decide you need an authorization. Just write another middleware:

enable 'RequestDispatcher';
enable 'User';
enable 'ACL';
enable 'ActionDispatcher';
enable 'ViewDisplayer';

Where User middleware loads a user from a cookie for example and ACL checks if a current user has a role for executing this controller.

Controllers are kept thin (there is no need to check user rights everywhere), every middleware follows Single Responsibility Principle and everything is configurable and extendible without effecting other parts of the system.

I die a little inside when I see this:

package MyController;
use base 'WebFramework::Controller';

sub action {
    my $self = shift;

    my $user = User->load($self->req->cookie('user_id'));
    if ($user->role eq 'admin') {
        ...
    }
    else {
        return $self->403_error;
    }
}

When using middleware you move all of this stuff into one place and clean up your controllers and tests for them.

Single method controllers

I have already written about this. But I have to mention it here too.

Many web frameworks create controllers that have several methods. For example, we have a controller called Post and then it has internal CRUD methods like create, update etc. While this may seem like a good idea but a better idea is to keep it simple and create a separate package for every action. This will also help to follow Single Responsibility Principle and make testing a lot easier.

Also this way you can use composition separately. It is easier to apply a Role/Mixin or inherit from a base controller if you have just one method to worry about.

Request scopes

In web applications most of the time you have objects with two life cycles. The first one has to be available all the time and you want to build it once. The second one has to be there just for a request.

When using Plack you can create application objects or as I call them services and env objects that are created during a request.

For example, we have a mail object that can send mail. We want to be available in controllers and don’t want to create and configure it every time. Thus the controller will have just two attributes: services and env.

package MyAppController;

sub new {
    my $class = shift;
    my (%params) = @_;

    my $self = {};
    bless $self, $class;

    $self->{env}      = $params{env};
    $self->{services} = $params{services};
}

sub service { shift->{services}->service(@_) }
sub req     { Plack::Request->new(shift->{env} }

Controller doesn’t have to be any more complex than that.

env is also used for passing objects between middleware, thus you can put here template variables and so on.

Service containers

In the previous chapter I mentioned services. How does one configure them for the whole application? You can write a container, a simple hash or anything holding the services that you pass to other components of the system. Sometimes a simple IoC container is a good choice. This way you have smth like:

package MyApp;
use base 'WebFramework';

sub startup {
    my $self = shift;

    my $services = $self->services;

    $services->register('mail' => MyMailObject->new);

    ...
}

sub to_psgi_app {
...
}

Pull templates

Pull templates or active templates are templates that can call model methods from the templates. This could sound like a crazy idea, but if you do it right it has enourmous benefits.

Say for example you have to know in every template if user is logged in or not. Do you really put user variable in every controller for the template to be happy? Or would you rather call from the template special method to know if the user is logged in?

These special methods are usually called helpers. And if you keep implementing helpers in such a way that they just have a read only access to whatever they have access to, you are going to be allright.

For example an active template can look like:

% if ($helpers->acl->is_user)
<span>Logged in</span>
% } else {
<span>Anonymous</span>
% }

And a helper can look like:

package Helpers::ACL;
use base 'WebFramework::Helper::Base';

sub is_user {
    my $self = shift;

    return $self->env->{'user'} ? 1 : 0;
}

Also when you don’t have a controller and just want to render a template, helpers are already there and registered in a separate middleware.

Implementation

If you liked these patterns, you might want to take a look at http://github.com/vti/tu and a real life web application example http://github.com/vti/threads.

4 Secrets About Technology That You Absolutely Must Know

A fast progressing thing like technology must have its secrets, right? Today we are going to spill some. Do you know any secrets about technology? Do you believe in them? Do you think technology is dangerous? Why do you think there are secrets around it anyway?

How incognito are you

We all used Ctrl + Shift + N at least once. I usually use it when I am not on my computer and I have to log in my e-mail and I don’t want Gmail to remember me or I simply don’t have the time to erase the user after I am done.

But did you know that Incognito mode does absolutely nothing! True, your searches don’t show in your History tab but your IP address is still visible to everyone. So if you are trying to do something in Incognito mode, thinking that nobody can see what you are doing, you might think again.

 

How much do you trust uncle Google

Most of us use Google as our search engine and we have Google Play, Google Maps, Gmail. They even bought YouTube! So every time you accept their policies and you let Google manage your pictures or accounts, you give them the access to all your data. If you just turn your location on on your phone, they immediately know where you are.

 

Google is simply not on your side. If you were ought to do something, Google could simply give them your information and they would find you immediately.

 

Hackers are after you

Have you ever got the alert that someone wanted to hack your e-mail of Facebook? Not only is that easier than ever (especially for a skilled hacker) but sometimes sites themselves will want to access your account but you will think that it was someone else.

 

 

What do you know about monitoring

Do you work in an office? Or do you work from your computer in any way? Did you know that there is a way for your boss or someone in your vicinity to log on to your server or Wi-Fi network and can see everything you are doing on your computer and when you’re not watching they can even snoop around.

The Future of Technology – How Far Can It Go

Just 18 years ago Nokia released their famous indestructible phone and became one of the top competitors in the phone industry. Now, we have so many phone-making companies that we don’t know which one to choose. Technology boomed in the last 25 years. In 2007 Motorola announced their first smartphone, which was a little more than 10 years ago and look at our phones now.

Technology is progressing every day more and it is even taking over our jobs. How long before it takes over our lives completely? We already rely highly on it. What do you think, how far is technology going to go? Here are some predictions:

Robots won’t be just a movie “thing”

If you follow technology news just a bit, you know that every developed country is in a race for creating the most functioned robot and put in on the market. While many believe that the age of robotics will ruin the human race, others think it will help us enormously when it comes to our day to day life.

Privacy is becoming an issue

Lately, people aren’t happy with the privacy policies every company and app is swearing they have. Every time we download something or we want to install something we have to give access to our personal information, pictures, contacts, etc.

Have you ever wondered why that is? Do they really need all of that or is it just a scam?
The rumors that there is a camera in every corner are already out there. What will happen to our data as technology goes further and they get more and more access to our information?

What is the real world

We have all heard about VR before and by the way it’s progressing VR could become people’s real world. There has already been talk about people getting lost in it and confusing what is real and what is not. It’s just a matter of time they create a VR where you won’t have to take it off; what will happen to us then?

Top 3 Tech Events You Would Want To Be a Part of in 2018

Of course this has to be one of the posts on my blog. Every year you have so many events and technology-related conventions that it would just be a shame if we wouldn’t attend them. So if you’re a tech-freak like me and you want to know more about technology or the future it’s promising, make sure you check out the events below and write down the dates, because adventure is coming your way!

1. Network of the Future

Because it is May already and because I know you will be excited to attend as many events as possible I decided to pick one that I think is the most useful one in June 2018. The conference will be in Plano, Texas and it’s a three day trip for getting all the knowledge about different infrastructure that you would need, no matter what industry you’re a part of and no matter what’s your business about. If you want to know the latest things you need for your market, this is the right conference for you.

2. Artificial Intelligence

Don’t let me get started on this one! This is a must for all technology enthusiasts and the one I am most excited for. We will all have to wait a bit for this one, since it’s in September, but I promise it will be worth your while. The conference lasts 2 days in San Francisco and it will spill all the secrets about AI. You will be able to meet developers, scientist and innovators and together you will find out more about AI than ever before! If you want a robot in your home soon, you mustn’t miss this one!

3. Cybersecurity Conference

A month after AI conference there is one in Montreat College’s Center and from the three it’s the most serious one. As we know cyberbullying is becoming bigger every year and the awareness is not even close to protecting its victims. The speakers will help you understand cyberbullying and teach you how to properly protect yourself from it.

Infographic by: TFE Times

Contracts in mop

While trying out mop I decided to implement contracts, or basically a simple type checking system.

The type checking itself is done via Type::Tiny. In mop it is implemented using method roles, which is very very handy. The code looks like:

use mop;
use mopx::contracts;

use Types::Standard -types;

class MyClass {
    has $!foo is expected(Int);

    method add ($add) is expected(Int), ensured(Int) {
        $!foo + $add
    }
}

So mopx::contracts exports expected and ensured. As you can see this works on attributes too. ensured is for checking the return values, thus adding even more robustness to our code.

In case you wonder what happens when the class is inherited, all the types are inherited too, there is no need to repeat them. For example, an abstract class (or interface) can look like:

class MyAbstractClass {
    method do_something is ensured(Int)
}

class MyClass extends MyAbstractClass {
}

And MyClass inherits all the checking.

It is possible to overwrite the type checking now, but I will modify it so you can only overwrite the types without breaking Liskov substitution principle. For example:

class MyAbstractClass {
    method do_something is expected(Int), ensured(Int)
}

class MyClass extends MyAbstractClass {
    method do_something is expected(Number), ensured(PositiveInt)
}

So we can only weaken preconditions and strengthen postconditions. But this is not yet implemented :) (UPDATE: implemented!)

If you’re interested you can follow the project at http://github.com/vti/mopx-contracts.

I want to thank guys who are making mop happen and for their help on the channel, especially doy, who helped with initial implementation.