Monthly Archives: November 2012

Sending sockets from PostgreSQL triggers with Python

Picture this: We want to notify to one external service each time that one record is inserted in the database. We can find the place where the insert statement is done and create a TCP client there, but: What happens if the application that inserts the data within the database is a legacy application?, or maybe it is too hard to do?. If your database is PostgreSQL it’s pretty straightforward. With the “default” procedural language of PostgreSQL (pgplsql) we cannot do it, but PostgreSQL allows us to use more procedural languages than plpgsql, for example Python. With plpython we can use sockets in the same way than we use it within Python scripts. It’s very simple. Let me show you how to do it.

First we need to create one plpython with our TCP client

CREATE OR REPLACE FUNCTION dummy.sendsocket(msg character varying, host character varying, port integer)
  RETURNS integer AS
$BODY$
  import _socket
  try:
    s = _socket.socket(_socket.AF_INET, _socket.SOCK_STREAM)
    s.connect((host, port))
    s.sendall(msg)
    s.close()
    return 1
  except:
    return 0
$BODY$
  LANGUAGE plpython VOLATILE
  COST 100;
ALTER FUNCTION dummy.sendsocket(character varying, character varying, integer)
  OWNER TO username;

Now we create the trigger that use our socket client.

CREATE OR REPLACE FUNCTION dummy.myTriggerToSendSockets()
RETURNS trigger AS
$BODY$
   import json
   stmt = plpy.prepare("select dummy.sendSocket($1, $2, $3)", ["text", "text", "int"])
   rv = plpy.execute(stmt, [json.dumps(TD), "host", 26200])
$BODY$
LANGUAGE plpython VOLATILE
COST 100;

As you can see in my example we are sending all the record as a JSON string in the socket body.

And finally we attach the trigger to one table (or maybe we need to do it to more than one table)

CREATE TRIGGER myTrigger
  AFTER INSERT OR UPDATE OR DELETE
  ON dummy.myTable
  FOR EACH ROW
  EXECUTE PROCEDURE dummy.myTriggerToSendSockets();

And that’s all. Now we can use one simple TCP socket server to handle those requests. Let me show you different examples of TCP servers with different languages. As we can see all are different implementations of Reactor pattern. We can use, for example:

node.js:

var net = require('net');

var host = 'localhost';
var port = 26200;

var server = net.createServer(function (socket) {
    socket.on('data', function(buffer) {
        // do whatever that we want with buffer
    });
});

server.listen(port, host);

python (with Twisted):

from twisted.internet import reactor, protocol

HOST = 'localhost'
PORT = 26200

class MyServer(protocol.Protocol):
    def dataReceived(self, data):
        # do whatever that we want with data
        pass

class MyServerFactory(protocol.Factory):
    def buildProtocol(self, addr):
        return MyServer()

reactor.listenTCP(PORT, MyServerFactory(), interface=HOST)
reactor.run()

(I know that we can create the Python’s TCP server without Twisted, but if don’t use it maybe someone will angry with me. Probably he is angry right now because I put the node.js example first :))

php (with react):

<?php
include __DIR__ . '/vendor/autoload.php';

$host = 'localhost';
$port = 26200;

$loop   = React\EventLoop\Factory::create();
$socket = new React\Socket\Server($loop);

$socket->on('connection', function ($conn) {
    $conn->on('data', function ($data) {
        // do whatever we want with data
        }
    );
});

$socket->listen($port, $host);
$loop->run();

You also can use xinet.d to handle the TCP inbound connections.

How to call shell programs as functions with PHP

I’m a big fan of Symfony’s Process Component. I’ve used intensively this component within a project and I noticed that I needed a wrapper to avoid to write again and again the same code. Suddenly a cool python library came to my head: sh. With python’s sh we can call any program as if it were a function:

from sh import ifconfig
print(ifconfig("wlan0"))

Outputs:

wlan0   Link encap:Ethernet  HWaddr 00:00:00:00:00:00
        inet addr:192.168.1.100  Bcast:192.168.1.255  Mask:255.255.255.0
        inet6 addr: ffff::ffff:ffff:ffff:fff/64 Scope:Link
        UP BROADCAST RUNNING MULTICAST  MTU:1500  Metric:1
        RX packets:0 errors:0 dropped:0 overruns:0 frame:0
        TX packets:0 errors:0 dropped:0 overruns:0 carrier:0
        collisions:0 txqueuelen:1000
        RX bytes:0 (0 GB)  TX bytes:0 (0 GB)

So I decided to develop something similar in PHP. This library is not exactly the same than python one. Python’s sh allows more cool things such as non-blocking processes, baking, … not available in my PHP one’s, but at least I can call shell programs as functions in a simple way (and that’s was my scope). Let’s start.

One simple example of Process:

use Symfony\Component\Process\Process;

$process = new Process('-latr ~');
$process->setTimeout(3600);
$process->run();

echo $process->getOutput();

With sh library we can do:

use Sh/Sh;

$sh  = new Sh();
echo $sh->ls('-latr ~');

You can check the source code in github, but it’s very simple one. Basically it’s a parser that creates the command line string, and another class that calls to the parser and sends the output to Process component. Whit the magic function __call we can use shell commands as functions.

The command’s arguments can be one string ‘-latr ~’ or one array ['-latr', '~']. You can see more example in the unit tests here

Symfony/Process also allows us to get feedback in real time:

use Symfony\Component\Process\Process;

$process = new Process('ls -lsa');
$process->run(function ($type, $buffer) {
    if ('err' === $type) {
        echo 'ERR > '.$buffer;
    } else {
        echo 'OUT > '.$buffer;
    }
});

Sh uses this feature, so we can do things like that:

$sh->tail('/var/log/messages', function ($buffer)  {
    echo $buffer;
});

We can see more examples here:

<?php
error_reporting(-1);
include __DIR__ . '/../vendor/autoload.php';
use Sh\Sh;

echo Sh::factory()->runCommnad('notify-send', ['-t', 5000, 'title', 'HOLA']);
$sh  = new Sh();
echo $sh->ifconfig("eth0");
echo $sh->ls('-latr ~');
echo $sh->ls(['-latr', '~']);
$sh->tail('-f /var/log/apache2/access.log', function ($buffer)  {
    echo $buffer;
});

As I said before the library is in github and also you can use with composer:

require: "gonzalo123/sh": "dev-master"

Updated!

Now Sh library supports chained arguments (baking)

// chainable commands (baking)
$sh->ssh(array('myserver.com', '-p' => 1393))->whoami();
// executes: ssh myserver.com -p 1393 whoami

$sh->ssh(array('myserver.com', '-p' => 1393))->tail(array("/var/log/dumb_daemon.log", 'n' => 100));
// executes: ssh myserver.com -p 1393 tail /var/log/dumb_daemon.log -n 100
});

Building a FTP client library with PHP

In my daily work I need to connect very often to FTP servers. Put files, read, list and things like that. I normally use the standard PHP functions for Ftp it’s pretty straight forward to use them. Just enable it within our installation (–enable-ftp) and it’s ready to use them. But last Sunday it was raining again and I start with this simple library.

Lets connect to a FTP server, switch to passive mode, put a file from one real file stored in our local filesystem and delete it.

use FtpLib\Ftp,
    FtpLib\File;

list($host, $user, $pass) = include __DIR__ . "/credentials.php";

$ftp = new Ftp($host, $user, $pass);
$ftp->connect();
$ftp->setPasv();

$file = $ftp->putFileFromPath(__DIR__ . '/fixtures/foo');
echo $file->getName();
echo $file->getContent();
$file->delete();

Now the same, but without a real file. We are going to create the file on-the-fly from one string:

use FtpLib\Ftp,
    FtpLib\File;

list($host, $user, $pass) = include __DIR__ . "/credentials.php";

$ftp = new Ftp($host, $user, $pass);
$ftp->connect();
$ftp->setPasv();

$file = $ftp->putFileFromString('bar', 'bla, bla, bla');
echo $file->getName();
echo $file->getContent();
$file->delete();

We also can create directories, change the working directory and delete folders in the FTP server with a fluent interface (I love fluent interfaces, indeed):

$ftp->mkdir('directory')
  ->chdir('directory')
  ->putFileFromString('newFile', 'bla, bla')
  ->delete();

$ftp->rmdir('directory');

And finally we can iterate files in the FTP (I must admit that this feature was the main purpose of the library)

$ftp->getFiles(function (File $file) use ($ftp) {
    switch($file->getName()) {
        case 'file1':
            $file->delete();
            break;
        case 'file2':
            $ftp->mkdir('backup')->chdir('backup')->putFileFromString($file->getName(), $file->getContent());
            break;
    }
});

And that’s all. You can find the library in github and you also can use it with composer.

"gonzalo123/ftplib": "dev-master"

You can also see usage examples within the unit tests

Live changes within node.js scripts without stop/start

Imagine that you are working within a nodejs project. This simple script:

var CONF = ['item1', 'item2', 'item3'];

var last;
setInterval(function () {
    var next = CONF.indexOf(last) + 1;
    last = (CONF[next] == undefined) ? CONF[0] : CONF[next];
    console.log(last);
}, 1000);

If we run this script, we will see in the console one element of CONF each second. Simple, isn’t it?. OK, imagine now we want to add one new element to the list (let’s say item4). We can easily change the script, stop the execution and run again. OK but imagine that we cannot stop/start the script as many times as we want. What can we do?. We can store the CONF data into one external storage (Redis, for example), but today we are going to do something more easy. We are going to modify CONF in execution time. The idea is to open a TCP socket and let change CONF with a simple protocol.

If we change the script to:

var net = require('net');
var CONF_PORT = 9730;
var CONF = ['item1', 'item2', 'item3'];

var last;
setInterval(function () {
    var next = CONF.indexOf(last) + 1;
    last = (CONF[next] == undefined) ? CONF[0] : CONF[next];
    console.log(last);
}, 1000);

var serverConf = net.createServer(function (confSocket) {
    confSocket.on("data", function (data) {
        var dataAsString = data.toString().trim();
        switch (dataAsString.substr(0, 1)) {
            case '+':
                var userVariable = dataAsString.substr(1);
                if (CONF.indexOf(userVariable) < 0) {
                    CONF.push(userVariable);
                    confSocket.write("+ " + userVariable + " added\n");
                } else {
                    confSocket.write("+ " + userVariable + " already added\n");
                }
                break;
            case '-':
                var userVariable = dataAsString.substr(1);
                if (CONF.indexOf(userVariable) >= 0) {
                    CONF.splice(CONF.indexOf(userVariable), 1)
                    confSocket.write("- " + userVariable + " deleted\n");
                } else {
                    confSocket.write("- " + userVariable + " don't exists\n");
                }
                break;
            case '=':
                for (var i in CONF) {
                    confSocket.write(CONF[i] + "\n");
                }
                break;
        }
        confSocket.write("\n");
        confSocket.write("Number of elements: " + CONF.length + "\n");
        confSocket.end("\n");
    });
});
serverConf.listen(CONF_PORT);

You can see the script in action here:

Follow

Get every new post delivered to your Inbox.

Join 992 other followers