(originally published 07/18/12)
other handlersocket entries:
part I – introduction to handlersocket
part III – handlersocket examples – Perl
————————-
Like the native access, you can use access all handlersocket functionality via HandlerSocket.pm, you can download the entire Handlersocket package at,
https://github.com/ahiguti/HandlerSocket-Plugin-for-MySQL
if you already have Handlersocket via Percona Server installed, in order to get HandlerSocket.pm, you may still need to download the separate installation, then simply go to the perl-Net-HandlerSocket directory within it and build the perl module per the instruction within it.
What perl-Net-HandlerSocket can do:
Like the native functionality it can,
Select / insert /update/ delete on primary and secondary keys
Filter on primary and secondary keys
‘In’ type functionality
queries with more than one parameter
The official documentation is at,
https://github.com/ahiguti/HandlerSocket-Plugin-for-MySQL/blob/master/docs-en/perl-client.en.txt
Initiate a connection
perl
use Net::HandlerSocket;
my $args = { host => '127.0.0.1', port => 9999 };
my $hs = new Net::HandlerSocket($args);
php
$hs = new HandlerSocket($host, $port_wr);
very simple, choose your host, choose your port (normally 9999 or 9998, more below on this).
Error Handling
After executing a call, you can print alerts and stop the program with the following,
perl
die $hs->get_error() if $res != 0;
Or for multiple rows,
for my $res (@$res) {
die $hs->get_error() if $res->[0] != 0;
shift(@$res);
php
if (! Whatever function) {
echo $hs->getError(), PHP_EOL;
die();
}
Authorization
1) edit your my.cnf settings (see configuration settings above)
2) then in your code,
perl
my $res = $hs->auth('your_password');
die $hs->get_error() if $res != 0;
php
if (!$hs->auth('your password')) {
die('Fault auth');
}
It will test both passwords and give appropriate permissions to the password that matches
Open an index
HandlerSocket completely relies on the indexes to get data, it can not get a record without one. (although it can certainly get the non-indexed data within the row you retrieve using the index). You must open at least one index for handlersocket to operate on to do a query.
perl
$res = $hs->open_index(a, 'b', 'c', 'd', ‘e1,e2,e3…’, 'f1,f2,f3…');
describing each position in the set above,
a – the index id, this can be any number, you’ll use it to differentiate all of your open indexes in your calls. Using the same number for a different index will overwrite the former opened index.
b – the database name
c – the table name
d – the name (defined in your table) of the index you are using
e(1,2,3…) – the columns you are using in the index. If it’s a multiple index, like mysql you must include the left most columns of the index (i.e. to use col2, you can not leave out col1 and use col2, you must include both).
f(1,2,3…) – the columns for which you want data returned.
php
$hs->openIndex(a,’b’ , ‘c’, ‘d’, 'f1,f2,f3…')
exactly the same as above EXCEPT no defining partial indexes (no ‘e1, e2,…’)
Data transactions
To see specific examples, go to Handlersocket examples. The general form of the call is the following,
perl
select, insert, update and delete can all be done with the general form below,
$res = $hs->execute_single(a, 'b',['c',’c2’, ‘c3’…], d, e, ‘f’, [g1,g2,g3,…],['h1','h2', 'h3','h4'],i,['j1','j2',’j3','j4']);
Not all are necessarily relevant and depends on the operation you are doing.
a – index id
b – the operation one of (>,=,!=, +)
c – the value for the index to query on OR the values for an insert
d – the limit of rows operated on like limit in mysql
e – the offset of the result like offset in mysql
f – if a delete or update then (‘D’, ‘U’) otherwise undef
g(1,2,3..) – if update, an array of values to use for the update
h – an array with filter values
h1 – ‘F’ (filter those values for =, !=), ‘W’ (stop once value is hit such as =)
h2 – filter operation (>,=,!=)
h3 – index id for the filter
h4 – value to filter on
i – key for IN statement
j(1,2,3..) – values for IN statement
the call itself can be execute_single or execute_multi. The latter will feed it as an array of the series above, there are a couple of examples coming up to demonstrate.
I’ve found many positions not requiring a value may still need a placeholder, where an empty ‘’ will not work for some of them. Use undef in this case.
php
Php is a little more flexible, you can use a similar executeSingle function for everything or use specific functions for insert, update, delete
$retval = $hs->executeSingle(a, '=', array(‘c’), d, e);
$retval = $hs->executeMulti(
array(array(a1, '=', array(‘c1'), d1, e1),
array(a2, '=', array('c2'), d2, e2)));
$hs->executeInsert(a, array('c1', 'c2','c3')
$hs->executeUpdate(a, 'b', array('c1'), array('c2'), d, e)
$hs->executeDelete(a, 'b', array('c'))
Things to keep in mind (optimization, troubleshooting and bugs)
a) execute_multi is supposed to be much faster for many statements than looping through execute_single.
b) Contrary to the documentation, I’ve found if you do not include definitions for the limit and offset, it will error. Using ‘undef’ for each place will work, but throw a ‘Use of uninitialized value in subroutine entry at handlersockettest.pl’ warning.
c) DO NOT leave spaces on either side of the commas in ‘open index’ call for (e) and (f) – it will error. I assume because these are multiple value and each is not enclosed by quotes has something to do with it.
