{"id":19,"date":"2013-11-12T15:16:41","date_gmt":"2013-11-12T20:16:41","guid":{"rendered":"http:\/\/www.scalemysql.com\/blog\/?p=19"},"modified":"2013-11-12T18:07:55","modified_gmt":"2013-11-12T23:07:55","slug":"handlersocket-syntax-examples","status":"publish","type":"post","link":"https:\/\/www.scalemysql.com\/?p=19","title":{"rendered":"Handlersocket Part III \/ examples \/ Perl"},"content":{"rendered":"

(originally posted 07\/18\/12)<\/p>\n

other handlersocket entries:<\/p>\n

part I<\/strong> – introduction to handlersocket<\/a><\/p>\n

part II<\/strong> – handlersocket syntax Perl\/PHP<\/a><\/p>\n

—————————————<\/p>\n

Inserting Records<\/strong><\/p>\n

Single record<\/span><\/span><\/p>\n

$res = $hs->execute_single(0, '+', ['mike','mike@101.com','2011-05-03']\r\n, 1, 0);\r\n\r\n\u00a0die $hs->get_error() if $res != 0;<\/pre>\n
Multiple records<\/span><\/p>\n
let\u2019s insert four more records as a \u2018batch\u2019<\/p>\n
$res = $hs->execute_multi([\r\n [0, '+', ['102','bob','bob@102.com','2011-12-29'], 1, 0],\r\n [0, '+', ['103','john','john@103.com','2011-07-18'], 1, 0],\r\n [0, '+', ['104','jane','jane@104.com','2011-06-23'], 1, 0],\r\n [0, '+', ['105','dave','dave@105.com','2011-04-12'], 1, 0]\r\n ]);<\/pre>\n
for my $res (@$res) {\r\n die $hs->get_error() if $res->[0] != 0;\r\n shift(@$res);<\/pre>\n
note: Limit and offset do not affect the insert.<\/p>\n
Reading Records<\/strong><\/p>\n
For reading records, you\u2019ll want to additionally do something with the resulting array reference.<\/p>\n
Something like this will work,<\/p>\n
for (my $row = 0; $row < $ele_returned; $row+= $count_cols) {\r\n   if($res->[$row]){\r\n      my $user_name= $res->[$row + 0];\r\n      my $user_email= $res->[$row + 1];\r\n      my $created= $res->[$row + 2];\r\n      print \"$user_name | $user_email | $created\\n\";\r\n   }\r\n}<\/pre>\n
Exact values<\/span><\/p>\n
a)<\/strong> Selecting on an exact single value<\/p>\n
$res = $hs->execute_single(0, '=', ['102'], 1, 0);<\/pre>\n
b)<\/strong> selecting on two or more exact values (where col1=a AND col2=b)<\/p>\n
$res = $hs->execute_single(0, '=', ['102', \u2018mike\u2019], 1, 0);<\/pre>\n
note: select on two keys must have the multiple index to support it<\/p>\n
c)<\/strong> selecting on two or more exact values (where col1=a or col2=b)<\/p>\n
$res = $hs->execute_multi(\r\n[0, '=', ['102'], 1, 0],\r\n[1, '=', ['mike'], 1, 0]\r\n);<\/pre>\n
note the second array needed to use a different index
\nnotes:<\/p>\n
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.<\/p>\n
d)<\/strong> Selecting on one or more values with (where col1 in (a, b, c\u2026.))<\/p>\n
You can do this one of two ways,<\/p>\n
$res = $hs->execute_multi(\r\n [0, '=', ['101'], 1, 0],\r\n [0, '=', ['102'], 1, 0],\r\n [0, '=', ['103'], 1, 0],\r\n [0, '=', ['105'], 1, 0]\r\n);<\/pre>\n
or<\/p>\n
$res = $hs->execute_single(0, '=',[''], 1, 0,\r\nundef,undef,undef,0,['101','102','103','105']);<\/pre>\n
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..<\/p>\n
Limits and Offsets<\/span><\/p>\n
These are very straight forward, we\u2019ll start with a limit of 10,more than enough for our 5 records. (where col1=a limit x,y)<\/p>\n
$res = $hs->execute_single(0, '>=', ['101'], 10, 0);<\/pre>\n
101 | mike | mike@101.com
\n102 | bob | bob@102.com
\n103 | john | john@103.com
\n104 | jane | jane@104.com
\n105 | dave | dave@105.com<\/p>\n
$res = $hs->execute_single(0, '>=', ['101'], 3, 0);<\/pre>\n
101 | mike | mike@101.com
\n102 | bob | bob@102.com
\n103 | john | john@103.com<\/p>\n
$res = $hs->execute_single(0, '>=', ['101'], 10, 2);<\/pre>\n
103 | john | john@103.com
\n104 | jane | jane@104.com
\n105 | dave | dave@105.com<\/p>\n
Range Searches<\/span><\/p>\n
handlersocket is a little odd in that only one boundary of a range search can be specified in the initial operator cell<\/p>\n
$res = $hs->execute_single(0, '>', ['102'], 10, 0);<\/pre>\n
103 | john | john@103.com
\n104 | jane | jane@104.com
\n105 | dave | dave@105.com<\/p>\n
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)<\/p>\n
$res = $hs->execute_single(0, '>',['102'], 10, 0, undef, undef,\r\n[['W','execute_single(0, '>',['102'], 10, 0, undef, undef,\r\n[['F','!=', '0','104']]);<\/pre>\n
103 | john | john@103.com
\n105 | dave | dave@105.com<\/p>\n
the filter can be a different column as well,<\/p>\n
$res = $hs->execute_single(0, '>',['102'], 10, 0, undef, undef,\r\n[['F','=', '1','dave']]);<\/pre>\n
105 | dave | dave@105.com<\/p>\n
as you can see with the ability to adjust operations, filters and columns, there are lots of combinations<\/p>\n
updating records<\/strong><\/p>\n
similar to the selects, the key difference being an update \u2018U\u2019 flag and array of columns are specified.<\/p>\n
a single key<\/span><\/p>\n
$res = $hs->execute_single(0, '=', ['103'], 1, 0, 'U',\r\n['2015-12-12 00:00:00']);<\/pre>\n
a range<\/span><\/span><\/p>\n
$res = $hs->execute_single(0, '>=', ['103'], 1, 0, 'U',\r\n['2015-12-12 00:00:00']);<\/pre>\n
two keys<\/span><\/span><\/p>\n
$res = $hs->execute_single(0, '=', ['102', 'mike'], 1, 0, 'U',\r\n['2015-12-12 00:00:00']);<\/pre>\n
a range and filter<\/span><\/p>\n
$res = $hs->execute_single(0, '>',['102'], 10, 0, 'U',\r\n['2015-12-12 00:00:00'], ['F','=', '1','dave']);<\/pre>\n
This is not working correctly.<\/span><\/p>\n
an \u2018IN\u2019 and filter<\/span><\/p>\n
$res = $hs->execute_single(0, '=',[''], 10, 0, 'U',\r\n['2015-12-12 00:00:00'],[['F','!=', 'mike']],0,\r\n['101','102','103','104']);<\/pre>\n
it\u2019s important the columns in the open_index call are exactly the same as in your update statement<\/p>\n
Deleting Records<\/strong><\/p>\n
 <\/p>\n
Single record<\/span><\/p>\n
$res = $hs->execute_single(0, '=', ['103'], 1 , 0, 'D');<\/pre>\n
+———+———–+————–+———————+
\n| user_id | user_name | user_email | created |
\n+———+———–+————–+———————+
\n| 101 | mike | mike@101.com | 2011-05-03 00:00:00 |
\n| 102 | bob | bob@102.com | 2011-12-29 00:00:00 |
\n| 104 | jane | jane@104.com | 2015-12-12 00:00:00 |
\n| 105 | dave | dave@105.com | 2015-12-12 00:00:00 |
\n+———+———–+————–+———————+<\/p>\n
multiple records<\/span><\/p>\n
$res = $hs->execute_single(0, '>=', ['103'],10 , 0, 'D');<\/pre>\n
+———+———–+————–+———————+
\n| user_id | user_name | user_email | created |
\n+———+———–+————–+———————+
\n| 101 | mike | mike@101.com | 2011-05-03 00:00:00 |
\n| 102 | bob | bob@102.com | 2011-12-29 00:00:00 |
\n+———+———–+————–+———————+<\/p>\n
$res = $hs->execute_single(0, '>=', ['103'], 2, 2 'D');<\/pre>\n
+———+———–+————–+———————+
\n| user_id | user_name | user_email | created |
\n+———+———–+————–+———————+
\n| 101 | mike | mike@101.com | 2011-05-03 00:00:00 |
\n| 104 | jane | jane@104.com | 2011-06-23 00:00:00 |
\n| 105 | dave | dave@105.com | 2011-04-12 00:00:00 |<\/p>\n
You can not seem to use or even accept \u2018!=\u2019<\/p>\n
$res = $hs->execute_single(0, '!=', ['103'], 10, 0, 'D');<\/pre>\n
however the filter can be made to do the same thing<\/p>\n
$res = $hs->execute_single(0, '>=',['101'], 10, 0, 'D',\r\nundef,[['F','!=', '1','bob']]);<\/pre>\n
| 102 | bob | bob@102.com | 2011-12-29 00:00:00 |<\/p>\n
filters and IN<\/span><\/p>\n
$res = $hs->execute_single(0, '=',[''], $rows_returned, $offset,\r\n'D', undef,[['F','!=', '1','bob']],0,['101','102','103','104']);<\/pre>\n
| 102 | bob | bob@102.com | 2011-12-29 00:00:00 |
\n| 105 | dave | dave@105.com | 2011-04-12 00:00:00 |<\/p>\n
bugs<\/strong> (tested around 02-15-12)<\/p>\n
a recent fix was implemented for filtering and \u2018IN\u2019 in combination, however,<\/p>\n
you CAN NOT seem to use a range, filter and \u2018IN\u2019 statement at the same time, such as,<\/p>\n
$res = $hs->execute_single(0, '>',['101'], 10, 0, undef, undef,\r\n[['F','!=', '1','bob']],0,['101','102','103','104']);<\/pre>\n
103 | john | john@103.com
\n104 | jane | jane@104.com
\n105 | dave | dave@105.com X<\/span><\/p>\n
I had problems with the date field as well with just filtering and IN,<\/p>\n
$res = $hs->execute_single(0, '=',[''], 10, 0, undef, undef,\r\n[['F','>', '2','2011-07-18 00:00:00']],0,['101','102','103','104']);<\/pre>\n
101 | mike | mike@101.com X<\/span>
\n102 | bob | bob@102.com
\n103 | john | john@103.com X<\/span>
\n104 | jane | jane@104.com X<\/span><\/p>\n
as with the selects, all three (range, filters and IN) do not all work together<\/p>\n
| 101 | mike | mike@101.com | 2011-05-03 00:00:00 |
\n| 102 | bob | bob@102.com | 2011-12-29 00:00:00 |<\/p>\n
(missing 105 from the IN exclusion)<\/p>\n
putting it all together<\/strong><\/p>\n
here is a sample script in perl<\/p>\n
#!\/usr\/bin\/perl<\/span><\/p>\n
### 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,<\/span><\/p>\n
use strict;<\/span><\/p>\n
use warnings;<\/span><\/p>\n
use Net::HandlerSocket; <\/span><\/p>\n
#1. establishing a connection <\/span><\/p>\n
####### port 9999 to write, 9998 to read <\/span><\/p>\n
#my $args = { host => ‘127.0.0.1’, port => 9998 };<\/span><\/p>\n
 my $args = { host => ‘127.0.0.1’, port => 9999 }; <\/span><\/p>\n
my $hs = new Net::HandlerSocket($args); <\/span><\/p>\n
#2. initializing an index so that we can use in main logics.<\/span><\/p>\n
# MySQL tables will be opened here (if not opened) <\/span><\/p>\n
my $res = $hs->auth(‘secretwr’);<\/span><\/p>\n
 die $hs->get_error() if $res != 0; <\/span><\/p>\n
## no spaces or it will error<\/span><\/p>\n
 my $cols = “user_name,user_email,created”; <\/span><\/p>\n
### used to get the count of the columns above dynamically, need for below <\/span><\/p>\n
my @count_cols = split(\/,\/,$cols);<\/span><\/p>\n
my $count_cols = scalar(@count_cols); <\/span><\/p>\n
$res = $hs->open_index(1, ‘test’, ‘user’, ‘user_name’, $cols);<\/span><\/p>\n
die $hs->get_error() if $res != 0; <\/span><\/p>\n
#3. main logic my $rows_returned = 3;<\/span><\/p>\n
my $offset = 0;<\/span><\/p>\n
my $ele_returned = $rows_returned * $count_cols; <\/span><\/p>\n
#select signs can be – ‘=’, ‘>’,'<‘,'<=’,’>=’, ‘!=’ <\/span><\/p>\n
$res = $hs->execute_single(1, ‘>=’, [‘101’], $rows_returned, $offset);<\/span><\/p>\n
die $hs->get_error() if $res->[0] != 0;<\/span>  shift(@$res); <\/span><\/p>\n
########### multi-select <\/span><\/p>\n
#$res = $hs->execute_multi([<\/span>  #[1, ‘=’, [‘mike’], 1, 0], [1, ‘=’, [‘jane’], 1, 0]<\/span>  #]); <\/span><\/p>\n
## this begins the exec for a multi-statement<\/span><\/p>\n
#for my $res (@$res) {<\/span><\/p>\n
 #\u00a0\u00a0\u00a0 die $hs->get_error() if $res->[0] != 0;<\/span><\/p>\n
#\u00a0\u00a0\u00a0 shift(@$res); my $count_elements = scalar(@$res);<\/span><\/p>\n
my $count_rows = $count_elements \/ $count_cols; <\/span><\/p>\n
for (my $row = 0; $row < $ele_returned; $row+= $count_cols) { <\/span><\/p>\n
if($res->[$row]){<\/span>  my $user_name= $res->[$row + 0];<\/span><\/p>\n
 my $user_email= $res->[$row + 1];<\/span><\/p>\n
my $created= $res->[$row + 2];<\/span><\/p>\n
print “$user_name | $user_email | $created\\n”;<\/span><\/p>\n
 }<\/span><\/p>\n
 } <\/span><\/p>\n
print”elements: $count_elements\\n”;<\/span><\/p>\n
 print “rows: $count_rows\\n\\n”; <\/span><\/p>\n
### this is the closing bracket for a multi-statment<\/span><\/p>\n
# } <\/span><\/p>\n
#4. closing the connection<\/span><\/p>\n
$hs->close();<\/span><\/p>\n
 <\/p>\n","protected":false},"excerpt":{"rendered":"
(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); \u00a0die $hs->get_error() if $res != 0; Multiple records let\u2019s insert four more records as a \u2018batch\u2019 $res = $hs->execute_multi([ [0, ‘+’, [‘102′,’bob’,’bob@102.com’,’2011-12-29′], 1, 0], […]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[],"tags":[],"class_list":["post-19","post","type-post","status-publish","format-standard","hentry"],"_links":{"self":[{"href":"https:\/\/www.scalemysql.com\/index.php?rest_route=\/wp\/v2\/posts\/19","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.scalemysql.com\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.scalemysql.com\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.scalemysql.com\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.scalemysql.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=19"}],"version-history":[{"count":40,"href":"https:\/\/www.scalemysql.com\/index.php?rest_route=\/wp\/v2\/posts\/19\/revisions"}],"predecessor-version":[{"id":238,"href":"https:\/\/www.scalemysql.com\/index.php?rest_route=\/wp\/v2\/posts\/19\/revisions\/238"}],"wp:attachment":[{"href":"https:\/\/www.scalemysql.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=19"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.scalemysql.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=19"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.scalemysql.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=19"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}