Handlersocket Part III / examples / Perl

(originally posted 07/18/12)

other handlersocket entries:

part I – introduction to handlersocket

part II – handlersocket syntax Perl/PHP

—————————————

Inserting Records

Single record

$res = $hs->execute_single(0, '+', ['mike','mike@101.com','2011-05-03']
, 1, 0);

 die $hs->get_error() if $res != 0;

Multiple records

let’s insert four more records as a ‘batch’

$res = $hs->execute_multi([
 [0, '+', ['102','bob','bob@102.com','2011-12-29'], 1, 0],
 [0, '+', ['103','john','john@103.com','2011-07-18'], 1, 0],
 [0, '+', ['104','jane','jane@104.com','2011-06-23'], 1, 0],
 [0, '+', ['105','dave','dave@105.com','2011-04-12'], 1, 0]
 ]);

for my $res (@$res) {
 die $hs->get_error() if $res->[0] != 0;
 shift(@$res);

note: Limit and offset do not affect the insert.

Reading Records

For reading records, you’ll want to additionally do something with the resulting array reference.

Something like this will work,

for (my $row = 0; $row < $ele_returned; $row+= $count_cols) {
   if($res->[$row]){
      my $user_name= $res->[$row + 0];
      my $user_email= $res->[$row + 1];
      my $created= $res->[$row + 2];
      print "$user_name | $user_email | $created\n";
   }
}

Exact values

a) Selecting on an exact single value

$res = $hs->execute_single(0, '=', ['102'], 1, 0);

b) selecting on two or more exact values (where col1=a AND col2=b)

$res = $hs->execute_single(0, '=', ['102', ‘mike’], 1, 0);

note: select on two keys must have the multiple index to support it

c) selecting on two or more exact values (where col1=a or col2=b)

$res = $hs->execute_multi(
[0, '=', ['102'], 1, 0],
[1, '=', ['mike'], 1, 0]
);

note the second array needed to use a different index
notes:

handlersocket needs indexes to find results and like mysql, a multi-column index requires the leftmost prefix and index. Again, handlesocket does not do table scans, and so without it, you will not get any results at all.

d) Selecting on one or more values with (where col1 in (a, b, c….))

You can do this one of two ways,

$res = $hs->execute_multi(
 [0, '=', ['101'], 1, 0],
 [0, '=', ['102'], 1, 0],
 [0, '=', ['103'], 1, 0],
 [0, '=', ['105'], 1, 0]
);

$res = $hs->execute_single(0, '=',[''], 1, 0,
undef,undef,undef,0,['101','102','103','105']);

note with the second query, we can do more with it later on such as filtering while in the first query the arrays are independent of one another..

Limits and Offsets

These are very straight forward, we’ll start with a limit of 10,more than enough for our 5 records. (where col1=a limit x,y)

$res = $hs->execute_single(0, '>=', ['101'], 10, 0);

$res = $hs->execute_single(0, '>=', ['101'], 3, 0);

$res = $hs->execute_single(0, '>=', ['101'], 10, 2);

Range Searches

handlersocket is a little odd in that only one boundary of a range search can be specified in the initial operator cell

$res = $hs->execute_single(0, '>', ['102'], 10, 0);

two bounded range can indeed be done, it can be done with an additional filter function, (W for a stop, F for a filter of those records)

$res = $hs->execute_single(0, '>',['102'], 10, 0, undef, undef,
[['W','execute_single(0, '>',['102'], 10, 0, undef, undef,
[['F','!=', '0','104']]);

103 | john | john@103.com
105 | dave | dave@105.com

the filter can be a different column as well,

$res = $hs->execute_single(0, '>',['102'], 10, 0, undef, undef,
[['F','=', '1','dave']]);

105 | dave | dave@105.com

as you can see with the ability to adjust operations, filters and columns, there are lots of combinations

updating records

similar to the selects, the key difference being an update ‘U’ flag and array of columns are specified.

a single key

$res = $hs->execute_single(0, '=', ['103'], 1, 0, 'U',
['2015-12-12 00:00:00']);

a range

$res = $hs->execute_single(0, '>=', ['103'], 1, 0, 'U',
['2015-12-12 00:00:00']);

two keys

$res = $hs->execute_single(0, '=', ['102', 'mike'], 1, 0, 'U',
['2015-12-12 00:00:00']);

a range and filter

$res = $hs->execute_single(0, '>',['102'], 10, 0, 'U',
['2015-12-12 00:00:00'], ['F','=', '1','dave']);

This is not working correctly.

an ‘IN’ and filter

$res = $hs->execute_single(0, '=',[''], 10, 0, 'U',
['2015-12-12 00:00:00'],[['F','!=', 'mike']],0,
['101','102','103','104']);

it’s important the columns in the open_index call are exactly the same as in your update statement

Deleting Records

Single record

$res = $hs->execute_single(0, '=', ['103'], 1 , 0, 'D');

+———+———–+————–+———————+
| user_id | user_name | user_email | created |
+———+———–+————–+———————+
| 101 | mike | mike@101.com | 2011-05-03 00:00:00 |
| 102 | bob | bob@102.com | 2011-12-29 00:00:00 |
| 104 | jane | jane@104.com | 2015-12-12 00:00:00 |
| 105 | dave | dave@105.com | 2015-12-12 00:00:00 |
+———+———–+————–+———————+

multiple records

$res = $hs->execute_single(0, '>=', ['103'],10 , 0, 'D');

+———+———–+————–+———————+
| user_id | user_name | user_email | created |
+———+———–+————–+———————+
| 101 | mike | mike@101.com | 2011-05-03 00:00:00 |
| 102 | bob | bob@102.com | 2011-12-29 00:00:00 |
+———+———–+————–+———————+

$res = $hs->execute_single(0, '>=', ['103'], 2, 2 'D');

+———+———–+————–+———————+
| user_id | user_name | user_email | created |
+———+———–+————–+———————+
| 101 | mike | mike@101.com | 2011-05-03 00:00:00 |
| 104 | jane | jane@104.com | 2011-06-23 00:00:00 |
| 105 | dave | dave@105.com | 2011-04-12 00:00:00 |

You can not seem to use or even accept ‘!=’

$res = $hs->execute_single(0, '!=', ['103'], 10, 0, 'D');

however the filter can be made to do the same thing

$res = $hs->execute_single(0, '>=',['101'], 10, 0, 'D',
undef,[['F','!=', '1','bob']]);

| 102 | bob | bob@102.com | 2011-12-29 00:00:00 |

filters and IN

$res = $hs->execute_single(0, '=',[''], $rows_returned, $offset,
'D', undef,[['F','!=', '1','bob']],0,['101','102','103','104']);

| 102 | bob | bob@102.com | 2011-12-29 00:00:00 |
| 105 | dave | dave@105.com | 2011-04-12 00:00:00 |

bugs (tested around 02-15-12)

a recent fix was implemented for filtering and ‘IN’ in combination, however,

you CAN NOT seem to use a range, filter and ‘IN’ statement at the same time, such as,

$res = $hs->execute_single(0, '>',['101'], 10, 0, undef, undef,
[['F','!=', '1','bob']],0,['101','102','103','104']);

I had problems with the date field as well with just filtering and IN,

$res = $hs->execute_single(0, '=',[''], 10, 0, undef, undef,
[['F','>', '2','2011-07-18 00:00:00']],0,['101','102','103','104']);

as with the selects, all three (range, filters and IN) do not all work together

| 101 | mike | mike@101.com | 2011-05-03 00:00:00 |
| 102 | bob | bob@102.com | 2011-12-29 00:00:00 |

(missing 105 from the IN exclusion)

putting it all together

here is a sample script in perl

#!/usr/bin/perl

### this script is far from optimized, particularly for many returned rows, however you probably shouldn’t be using handlersocket for a lot of return data anyways,

use strict;

use warnings;

use Net::HandlerSocket;

#1. establishing a connection

####### port 9999 to write, 9998 to read

#my $args = { host => ‘127.0.0.1’, port => 9998 };

my $args = { host => ‘127.0.0.1’, port => 9999 };

my $hs = new Net::HandlerSocket($args);

#2. initializing an index so that we can use in main logics.

# MySQL tables will be opened here (if not opened)

my $res = $hs->auth(‘secretwr’);

die $hs->get_error() if $res != 0;

## no spaces or it will error

my $cols = “user_name,user_email,created”;

### used to get the count of the columns above dynamically, need for below

my @count_cols = split(/,/,$cols);

my $count_cols = scalar(@count_cols);

$res = $hs->open_index(1, ‘test’, ‘user’, ‘user_name’, $cols);

die $hs->get_error() if $res != 0;

#3. main logic my $rows_returned = 3;

my $offset = 0;

my $ele_returned = $rows_returned * $count_cols;

#select signs can be – ‘=’, ‘>’,'<‘,'<=’,’>=’, ‘!=’

$res = $hs->execute_single(1, ‘>=’, [‘101’], $rows_returned, $offset);

die $hs->get_error() if $res->[0] != 0; shift(@$res);

########### multi-select

#$res = $hs->execute_multi([ #[1, ‘=’, [‘mike’], 1, 0], [1, ‘=’, [‘jane’], 1, 0] #]);

## this begins the exec for a multi-statement

#for my $res (@$res) {

# die $hs->get_error() if $res->[0] != 0;

# shift(@$res); my $count_elements = scalar(@$res);

my $count_rows = $count_elements / $count_cols;

for (my $row = 0; $row < $ele_returned; $row+= $count_cols) {

if($res->[$row]){ my $user_name= $res->[$row + 0];

my $user_email= $res->[$row + 1];

my $created= $res->[$row + 2];

print “$user_name | $user_email | $created\n”;

}

print”elements: $count_elements\n”;

print “rows: $count_rows\n\n”;

### this is the closing bracket for a multi-statment

# }

#4. closing the connection

$hs->close();

Leave a Reply Cancel reply