MariaDB Cookbook (2014)
Chapter 11. NoSQL with HandlerSocket
In this chapter, we will cover the following recipes:
· Installing and configuring HandlerSocket
· Installing the libhsclient library
· Installing the HandlerSocket PERL client libraries
· Reading data using HandlerSocket and PERL
· Inserting data using HandlerSocket and PERL
· Updating and deleting data using HandlerSocket and PERL
· Installing the HandlerSocket Python client libraries
· Reading data using HandlerSocket and Python
· Inserting data using HandlerSocket and Python
· Updating and deleting data using HandlerSocket and Python
· Installing the HandlerSocket Ruby client libraries
· Reading data using HandlerSocket and Ruby
· Inserting data using HandlerSocket and Ruby
· Updating and deleting data using HandlerSocket and Ruby
· Using HandlerSocket directly with Telnet
Introduction
This chapter is all about installing, configuring, and most importantly, using HandlerSocket, a NoSQL interface for MariaDB. We'll start with installing and configuring the HandlerSocket plugin for MariaDB and compiling and installing the libhsclient library that other languages use to talk directly to our MariaDB databases through HandlerSocket.
We then go through the same basic recipes for three popular scripting languages: PERL, Python, and Ruby. For each of these three languages, we first install the client library, and then go through reading, inserting, updating, and deleting data. After installing the HandlerSocket plugin, feel free to jump directly to a preferred language. To finish off the chapter, we have a recipe on interacting with HandlerSocket directly using telnet.
Installing and configuring HandlerSocket
The HandlerSocket plugin is included with MariaDB, but like other optional plugins, it is not enabled or configured by default.
How to do it...
1. Launch the mysql command-line client and connect to our MariaDB database.
2. Install the HandlerSocket plugin using the following command:
3. INSTALL SONAME 'handlersocket';
4. Open our my.cnf or my.ini file and add the following command to the [mysqld] section:
5. #
6. # * HandlerSocket
7. #
8. handlersocket_address="127.0.0.1"
9. handlersocket_port="9998"
10.handlersocket_port_wr="9999"
11. Stop and restart MariaDB.
12. Reconnect to MariaDB using the mysql command-line client and see the HandlerSocket worker threads using the following statement:
13.SHOW PROCESSLIST;
How it works...
The HandlerSocket plugin allows us to completely bypass the SQL layer of MariaDB. This offers an incredible speed for simple operations. The downside is that HandlerSocket only handles simple operations. It cannot handle anything beyond the basic SELECT,INSERT, UPDATE, and DELETE statements, and it can only search on a primary or other indexed key.
The HandlerSocket plugin is included with MariaDB, but like other optional plugins, it is not activated by default. To activate it, we use the INSTALL SONAME command. This is a one-time operation.
After running the INSTALL command, there are a few settings we need to add to our local my.cnf file in order for HandlerSocket to work properly. So, after installing the plugin, we edit our main configuration file and add it.
The handlersocket_address setting is the IP address that the plugin will listen on for requests. The handlersocket_port variable sets the port number we connect to for read-only requests. The handlersocket_port_wr variable defines the port number we connect to for inserts, updates, and other operations that require write access.
Tip
When installing and configuring HandlerSocket, we need to make sure that we run the INSTALL SONAME command before adding the settings to our my.cnf file. The HandlerSocket configuration options are only valid if the plugin is installed. They will cause an error and prevent MariaDB from starting if they are present in our configuration file when the HandlerSocket plugin is not installed.
When HandlerSocket is configured and running, it will spawn several worker threads. These threads handle requests from our applications. While HandlerSocket is running, looking for the worker threads is a good way to verify that we have installed and configured HandlerSocket correctly. When HandlerSocket is running, the output of the SHOW PROCESSLIST; command will look similar to the following screenshot:
To remove the HandlerSocket plugin, we use the UNINSTALL SONAME command. If we choose to UNINSTALL the plugin, we must also remove the configuration settings we added after installing the plugin.
There's more...
By default, the HandlerSocket plugin will launch 16 read threads and 1 write thread. Both of them can be controlled by adding the following variables to the [mysqld] section of our main my.cnf file:
# HandlerSocket Read Threads:
handlersocket_threads = 16
# HandlerSocket Write Threads:
handlersocket_threads_wr = 1
The maximum permissible value for each of them is 3000, but it's not recommended to ever set them that high. In fact, the developers recommend leaving the number of write threads set to 1. For the read threads, they recommend setting it to double the number of CPU cores on the server.
We can also set plain text passwords for clients to use when connecting with HandlerSocket. Along with completely bypassing the SQL layer of MariaDB, HandlerSocket also bypasses MariaDB's security layer. This adds back a measure of security that is otherwise missing when using this plugin. We can set separate passwords for both the read-only and write ports. The two variables are as follows:
handlersocket_plain_secret = 'readSocketPassword'
handlersocket_plain_secret_wr = 'writeSocketPassword'
See also
· The full documentation of the HandlerSocket plugin can be found at https://mariadb.com/kb/en/handlersocket/
· The various HandlerSocket configuration options are documented at https://mariadb.com/kb/en/handlersocket-configuration-options/
· The HandlerSocket protocol is documented at https://github.com/DeNA/HandlerSocket-Plugin-for-MySQL/blob/master/docs-en/protocol.en.txt
Installing the libhsclient library
The libhsclient library is what client libraries use to talk to HandlerSocket. This library is not included with MariaDB; so, we need to either install it from our Linux distribution's package repositories or build and install it ourselves.
Getting ready
Install and configure the HandlerSocket plugin as described in the Installing and configuring HandlerSocket recipe earlier in this chapter. In order to build the libhsclient library, we need to have some development tools and packages installed.
On Red Hat, Fedora, or CentOS, run the following command to install the necessary tools:
sudo yum install make gcc-c++
On Debian or Ubuntu, run the following command to install the necessary tools:
sudo apt-get install make g++
We are now ready to build and install the libhsclient libraries.
How to do it...
1. Download the latest HandlerSocket source file as follows:
2. wget \
3. https://github.com/DeNA/HandlerSocket-Plugin-for-MySQL/archive/master.tar.gz
4. Change to the /usr/local/src directory and untar the file we just downloaded as follows:
5. cd /usr/local/src/
6. sudo tar -zxvf /path/to/master*
7. Change to the libhsclient/ directory and rename Makefile.plain to Makefile as follows:
8. cd HandlerSocket-Plugin-for-MySQL-master/libhsclient
9. sudo mv -vi Makefile.plain Makefile
10. Compile libhsclient and install it as follows:
11.sudo make
12.sudo mkdir -vp /usr/local/include/handlersocket
13.sudo install -vm 644 *.hpp /usr/local/include/handlersocket
14.
15.sudo install -vm 644 libhsclient.a /usr/local/lib
16.
17.cd /usr/local/include/
18.sudo ln -vs handlersocket/*.hpp ./
How it works...
Compiling and installing the libhsclient library is much like compiling and installing other software from a source on Linux. The main difference is that because it is part of the larger HandlerSocket plugin source package and because we don't need the MariaDB plugin parts (they are already in MariaDB), we don't use the usual ./configure && make && sudo make install three-step dance that is so common with the other source code. Instead, we navigate directly to the libhsclient/ subdirectory, put the default Makefile in place, and use the make process.
The library is not very large, so the make process does not take long. When the process is completed, we will have several files that end in .hpp in the libhsclient/ directory. These files need to be moved to a location from where our client libraries can see them, so that's what we do in the last few commands we run in step 4.
Installing the HandlerSocket PERL client libraries
The HandlerSocket client libraries for PERL are included with the HandlerSocket source code. In this recipe, we'll compile and install them.
Getting ready
In order to build the HandlerSocket PERL client library, we need to install some development tools and packages. First, we need to install the libhsclient library as described in the Installing the libhsclient library recipe earlier in this chapter. Then, we need to install some PERL development tools.
On Red Hat, Fedora, or CentOS, run the following command:
sudo yum install perl-devel perl-Test-Simple
On Debian or Ubuntu, run the following command to install the necessary packages:
sudo apt-get install libperl-dev
We are now ready to install the HandlerSocket PERL client library.
How to do it...
1. Open a command-line window.
2. Change to the perl-Net-HandlerSocket directory in the HandlerSocket source directory as follows:
3. cd /usr/local/src/HandlerSocket-Plugin-for-MySQL-master/
4. cd perl-Net-HandlerSocket/
5. Run the following commands to install the PERL HandlerSocket client libraries:
6. sudo perl Makefile.PL
7. sudo make
8. sudo make test
9. sudo make install
How it works...
The HandlerSocket plugin source code includes a PERL client library, but this library is not included with MariaDB binaries, so we have to install it manually. We already had the HandlerSocket source code from the recipe we followed to install the libhsclient library, so we don't have to download it again.
Actually, compiling and installing the library is extremely easy. When the make test portion is run, the following text should be the last line of the output:
Result: PASS
Reading data using HandlerSocket and PERL
In this recipe, we will create a simple PERL script that reads data using HandlerSocket.
Getting ready
Install the HandlerSocket PERL client libraries as described in the Installing the HandlerSocket PERL client libraries recipe earlier in this chapter.
Launch the mysql command-line client and run the following commands to create a test table with some data:
CREATE DATABASE IF NOT EXISTS test;
USE test;
DROP TABLE IF EXISTS hs_test;
CREATE TABLE hs_test (
id SERIAL PRIMARY KEY,
givenname varchar(64),
surname varchar(64)
);
INSERT INTO hs_test VALUES
(1,"William","Hartnell"), (2,"Patrick","Troughton"),
(3,"John","Pertwee"), (4,"Tom","Baker"),
(5,"Peter","Davison"), (6,"Colin","Baker");
This sample data is also in the hs_sample_data.sql file available on the book's website.
How to do it...
1. Create a file named hs_read_test.pl with the following content:
2. #!/usr/bin/perl
3. use strict;
4. use warnings;
5.
6. use Net::HandlerSocket;
7. my $read_args = { host => 'localhost', port => 9998 };
8. my $hs = new Net::HandlerSocket($read_args);
9.
10.my $res = $hs->open_index(0, 'test', 'hs_test', 'PRIMARY', 'id,givenname,surname');
11. die $hs->get_error() if $res != 0;
12.
13.my $pk = 1;
14.
15.$res = $hs->execute_single(0, '=', [ "$pk" ], 10, 0);
16. die $hs->get_error() if $res->[0] != 0;
17.shift(@$res);
18.
19.while ( $res->[0] ) {
20. printf("%s\t%s\t%s\n",$res->[0],$res->[1],$res->[2]);
21. $pk++;
22. $res = $hs->execute_single(0, '=', [ "$pk" ], 20, 0);
23. die $hs->get_error() if $res->[0] != 0;
24. shift(@$res);
25.}
26.
27.$hs->close();
28. Run the file with the following command:
29.perl hs_read_test.pl
Tip
This file is also available on the book's website.
How it works...
To read data using the HandlerSocket PERL library, we first need to create a Net::HandlerSocket object. This is done in our recipe with the use Net::HandlerSocket line and the two lines following it, where we supply the host and port information.
With the object now created, we can open a connection to a specific table using the open_index method. When doing so, we give our connection a number (0 in our recipe) and then specify the database we want to access (test), the table (hs_test), the index we want to open (we use PRIMARY in our recipe, which is a key word that means the primary key), and finally, a comma-separated list of the columns we want to read (id,givenname,surname).
To execute a single read, we use the execute_single method. This method takes five arguments. The first is the connection number (0) we set when calling the open_index method. The second is the search operator (= in our recipe). Supported operators include =, >=,<=, >, and <. The third argument is an array of the key we want to find. The array must have the same number of elements as the number of columns the key we are searching has. As we are searching the primary key in our recipe and it only has a single column, our array only has one element. The fourth and fifth arguments are the maximum number of records to retrieve and the number of columns to skip before retrieving anything, respectively (10 and 0 in our recipe).
The result we get back is an array with elements corresponding to the columns we defined with our open_index call. In our recipe, we use printf to print them to our terminal.
To cycle through all of the records in our test database, we use a while loop to increment our $pk variable until it doesn't find a record.
Lastly, we call the close method to close our connection. The output of this script will look like the following screenshot:
There's more...
We can execute multiple queries in a single operation using the execute_multi method. Instead of a single set of arguments, this method accepts an array of arguments. Each entry in the array contains the same five arguments used in the execute_single method. For example, consider the following statements:
my $mres = $hs->execute_multi([
[0, '=', [ "1" ], 1, 0],
[0, '>', [ "3" ], 5, 0],
[0, '>=', [ "5" ], 2, 0],
[0, '<', [ "6" ], 4, 3]
]);
As we are dealing with multiple requests, error handling goes in a for loop as follows:
for my $res (@$mres) {
die $hs->get_error() if $res->[0] != 0;
shift(@$res);
# and etc...
}
See also
· The documentation for the PERL client library is available at https://github.com/DeNA/HandlerSocket-Plugin-for-MySQL/blob/master/docs-en/perl-client.en.txt
Inserting data using HandlerSocket and PERL
To insert data with HandlerSocket, we need to use a different port, but we still use the execute_single command with extra options.
Getting ready
Complete the Reading data using HandlerSocket and PERL recipe before starting this recipe.
How to do it...
1. Create a file named hs_insert_test.pl with the following content (this script is also available on the book's website):
2. #!/usr/bin/perl
3. use strict;
4. use warnings;
5.
6. use Net::HandlerSocket;
7. my $write_args = { host => 'localhost', port => 9999 };
8. my $hsw = new Net::HandlerSocket($write_args);
9.
10.my $resw = $hsw->open_index(1, 'test', 'hs_test', 'PRIMARY', 'id,givenname,surname');
11. die $hsw->get_error() if $resw != 0;
12.
13.$resw = $hsw->execute_single(1, '+', [ '7', 'Sylvester', 'McCoy' ],0,0);
14. die $hsw->get_error() if $resw->[0] != 0;
15.
16.$resw = $hsw->execute_single(1, '+', [ '8', 'Paul', 'McGann' ],0,0);
17. die $hsw->get_error() if $resw->[0] != 0;
18.
19.$hsw->close();
20. Run the file using the following command:
21.perl hs_insert_test.pl
22. Run the hs_read_test.pl script to verify that our inserts worked:
23.perl hs_read_test.pl
How it works...
Creating the Net::HandlerSocket object and opening a connection are very similar to how we open a read-only connection. The main difference is that we specify the write port (9999) instead of the read-only port (9998). Opening an index is also the same as what we do for a read-only connection.
Performing an insert is different. We do use the same execute_single method, but instead of using a comparison operator, like we would for reading data, we use a + operator to indicate that we are adding a new row.
The third argument of the execute_single method is also different. Instead of specifying what we are searching for, we put in the data we are inserting. These values correspond to the columns we specified in the open_index call. The fourth and fifth arguments of theexecute_single method are not used when inserting and so, both can be set to 0.
The hs_insert_test.pl script will not produce any output, so after running it, we can rerun the hs_read_test.pl script we created in the Reading data using HandlerSocket and PERL recipe. Thus, we can confirm the data was entered. The output will look like the following screenshot:
See also
The documentation for the PERL client library is available at https://github.com/DeNA/HandlerSocket-Plugin-for-MySQL/blob/master/docs-en/perl-client.en.txt
Updating and deleting data using HandlerSocket and PERL
In addition to reading and inserting data, updating and deleting data round out the abilities of HandlerSocket.
Getting ready
Complete the Inserting data using HandlerSocket and PERL recipe before starting this recipe.
How to do it...
1. Create a file named hs_update_test.pl with the following content (this script is also available on the book's website):
2. #!/usr/bin/perl
3. use strict;
4. use warnings;
5.
6. use Net::HandlerSocket;
7. my $update_args = { host => 'localhost', port => 9999 };
8. my $hsu = new Net::HandlerSocket($update_args);
9.
10.my $resu = $hsu->open_index(2, 'test', 'hs_test', 'PRIMARY', 'givenname');
11. die $hsu->get_error() if $resu != 0;
12.
13.$resu = $hsu->execute_single(2, '=', [ '3' ],1,0, 'U', [ 'Jon' ]);
14. die $hsu->get_error() if $resu->[0] != 0;
15.printf("Number of Updated Rows:\t%s\n",$resu->[1]);
16.
17.$hsu->close();
18. Create a file called hs_delete_test.pl with the following contents (this script is also available on the book's website):
19.#!/usr/bin/perl
20.use strict;
21.use warnings;
22.
23.use Net::HandlerSocket;
24.my $delete_args = { host => 'localhost', port => 9999 };
25.my $hsd = new Net::HandlerSocket($delete_args);
26.
27.my $resd = $hsd->open_index(3, 'test', 'hs_test', 'PRIMARY', 'id,givenname,surname');
28. die $hsd->get_error() if $resd != 0;
29.
30.$resd = $hsd->execute_single(3, '+', [ '101', 'Junk', 'Entry' ],1,0);
31. die $hsd->get_error() if $resd->[0] != 0;
32.
33.$resd = $hsd->execute_single(3, '+', [ '102', 'Junk', 'Entry' ],1,0);
34. die $hsd->get_error() if $resd->[0] != 0;
35.
36.$resd = $hsd->execute_single(3, '+', [ '103', 'Junk', 'Entry' ],1,0);
37. die $hsd->get_error() if $resd->[0] != 0;
38.
39.$resd = $hsd->execute_single(3, '>', [ '100' ],10,0, 'D');
40. die $hsd->get_error() if $resd->[0] != 0;
41.
42.printf("Number of Deleted Rows:\t%s\n",$resd->[1]);
43.
44.$hsd->close();
45. Run the files with the following commands:
46.perl hs_update_test.pl
47.perl hs_delete_test.pl
48. Run the hs_read_test.pl script to verify that our update and delete commands have worked:
49.perl hs_read_test.pl
How it works...
To update and delete data, we use the execute_single method but with extra arguments. To update data, there are two extra arguments. The first five are just like the ones used for reading a row. The sixth argument is the letter U that stands for update. The seventh argument contains the values we want to update. These must correspond to the columns we specify in the open_index call.
In our recipe, we specified just the givenname column, so that's the only value we need to provide. Our recipe searches for the primary key 3 and then updates the givenname column of the row with that key to Jon, which is how his given name is actually spelled.
Lastly, in our update script, the result we get back from the execute_single method is the number of rows updated. So, to make things more user friendly, we print the value out. The output should be as follows:
Number of Updated Rows: 1
In our delete script, the calls are simpler than what we used to update data, but we first insert some data to give us something to delete (this lets us run the script multiple times, and it will always have those three rows to delete).
After inserting the three junk rows, we call execute_single with D (for delete) and with a comparison operator (>) and a record limit of 10 that lets us delete the records we just added. When we run the statement, the output will be as follows:
Number of Deleted Rows: 3
Finally, we run the hs_read_test.pl script we created in the Reading data using HandlerSocket and PERL recipe, so we can confirm that the given name was correctly updated (the deleted rows, obviously, will not appear in the output unless something went wrong).
The output will look like the following screenshot:
See also
· The documentation for the PERL client library is available at https://github.com/DeNA/HandlerSocket-Plugin-for-MySQL/blob/master/docs-en/perl-client.en.txt
Installing the HandlerSocket Python client libraries
Python is another popular scripting language. This recipe is about installing the Python pyhs client library for HandlerSocket so that we can use it in our scripts.
Getting ready
Install the libhsclient library as described in the Installing the libhsclient library recipe earlier in this chapter.
We need to also install the mercurial and python-setuptools packages so that we can get the latest copy of the pyhs source code and install it.
On Red Hat, Fedora, or CentOS, run the following command:
sudo yum install mercurial python-setuptools
On Debian or Ubuntu, run the following command:
sudo apt-get install mercurial python-setuptools
We are now ready to install the HandlerSocket client libraries for Python.
How to do it...
1. Clone a copy of the pyhs source code as follows:
2. cd /usr/local/src/
3. sudo hg clone http://bitbucket.org/excieve/pyhs
4. Change to the pyhs directory and edit the setup.py file by adding the following line after the four from... and DistutilsPlatformError lines at the top of the file:
5. ext_errors = (CCompilerError, DistutilsExecError, DistutilsPlatformError)
6. Run the setup.py script to install pyhs as follows:
7. sudo python setup.py install
How it works...
There are a couple of different HandlerSocket libraries for the Python language. The one we're installing is called pyhs. Its source code is hosted on bitbucket.org and uses mercurial for version control. The actual process of checking out and installing the library is quite simple. However, there's a bug in the file that requires a quick fix before we can actually complete the install. The bug is described at https://bitbucket.org/excieve/pyhs/issue/11/setuppy-error-ext_errors-not-defined and may actually be fixed in the current version of pyhs, so we should check before applying the fix described here.
If everything goes well, the last line of the output of the install step will be as follows:
Success
See also
· The documentation of the pyhs library is available at http://python-handler-socket.readthedocs.org/en/latest/
Reading data using HandlerSocket and Python
Now that we have installed pyhs, we can start using it to read data from our database.
Getting ready
Install the HandlerSocket PERL client libraries as described in the Installing the HandlerSocket PERL client libraries recipe earlier in this chapter. Launch the mysql command-line client and run the SQL commands from the Getting ready section of the Reading data using HandlerSocket and PERL recipe, described earlier in this chapter, to give us some sample data to read (and if we've already gone through the PERL or Ruby recipes, running the SQL commands again will reset the sample data to its default state).
How to do it...
1. Launch the interactive Python interpreter in a terminal window as follows:
2. python
3. Run the following commands in the interpreter:
4. from pyhs import Manager
5. hs = Manager()
6. data = hs.get('test', 'hs_test', ['id', 'givenname', 'surname'], '5')
7. print dict(data)
8. Then, run the following commands in the interpreter:
9. from pyhs.sockets import ReadSocket
10.hsr = ReadSocket([('inet', '127.0.0.1', 9998)])
11.
12.r_id = hsr.get_index_id('test', 'hs_test', ['id', 'givenname', 'surname'])
13.
14.hsr.find(r_id, '=', ['5'])
15.hsr.find(r_id, '=', ['6'])
16.hsr.find(r_id, '>=', ['1'],20)
17. Press Ctrl + D to exit the interactive Python interpreter.
How it works...
The pyhs libraries provide us with both high- and low-level methods of getting data. In our recipe, we used the high-level method first.
We start by importing the high-level Manager object. We then assign it to a variable and use the get method to search for a row in our database. This method takes four arguments. First, the name of the database, then the table name, an array of the columns we want to get, and lastly, the primary key value we want to search for (5 in our recipe). We assign the result to a variable and then print it out to our screen with print dict(). The output of the print dict(data) line will look like the following command:
{'givenname': 'Peter', 'surname': 'Davison', 'id': '5'}
Next, we import the lower level ReadSocket object. With the Manager object we can simply request what we want and get it all in one step, but we can't do that with ReadSocket. Instead, with ReadSocket, we first open a connection to the HandlerSocket read-only port, then we call the get_index_id method to define the database, table, and columns we are interested in. This method takes three arguments: the database name, the table name, and an array of the columns we are interested in.
We are then able to read data using the find method. This method takes three arguments. First is the variable we used when calling get_index_id. Second is the comparison operator we want to use; the supported operators are =, >, <, >=, and <=. The third argument is the key value we want to search for. There are fourth and fifth arguments that are optional. They set the maximum number of rows to return and the number of records to skip before retrieving records. If these arguments are not specified, the find method will set them both to 0.
The results of the find method are returned as an array. Of the three calls to this method in our recipe, the first two return single rows and the last returns all of the records in our example table. The returned values in the Python interpreter will look like the following command:
[('5', 'Peter', 'Davison')]
[('6', 'Colin', 'Baker')]
[('1', 'William', 'Hartnell'), ('2', 'Patrick', 'Troughton'), ('3', 'John', 'Pertwee'), ('4', 'Tom', 'Baker'), ('5', 'Peter', 'Davison'), ('6', 'Colin', 'Baker')]
The complete output of the recipe will look like the following screenshot:
See also
· The documentation of the pyhs library is available at http://python-handler-socket.readthedocs.org/en/latest/
Inserting data using HandlerSocket and Python
Inserting data using Python is similar to how it is done in other languages, but with a bit of Python flair.
Getting ready
Complete the Reading data using HandlerSocket and Python recipe, described earlier in this chapter, prior to starting this recipe.
How to do it...
1. Launch the interactive Python interpreter in a terminal window as follows:
2. python
3. Then, run the following commands in the Python interpreter:
4. from pyhs import Manager
5. hs = Manager()
6. hs.insert('test', 'hs_test', [('id', '7'), ('givenname', 'Sylvester'), ('surname', 'McCoy')])
7. Finally, run the following commands in the interpreter:
8. from pyhs.sockets import WriteSocket
9. hsw = WriteSocket([('inet', '127.0.0.1', 9999)])
10.w_id = hsw.get_index_id('test', 'hs_test', ['id', 'givenname', 'surname'])
11.
12.hsw.insert(w_id, ['8','Paul','McGann'])
How it works...
Similar to how data was read, when inserting data with pyhs there are two ways to do it. First is at a high level using the Manager object, and the second is at a low-level using the WriteSocket object.
The Manager object's insert method takes three arguments. First, we set the database name and then the table name. Finally, we provide an array that contains the column names and the values we want to insert.
For the WriteSocket object, we first open a connection to the HandlerSocket write port and then call the get_index_id method to define the database, table, and columns we want to insert.
We are then able to insert data using the insert method. This method takes two arguments. First is the variable we used when calling get_index_id (we used w_id in our recipe), and the second is an array of the values we want to insert. After successful insertion, theinsert method will return True.
The WriteSocket object doesn't have a find method, so if we want to read back the data we just entered, we need to use the ReadSocket object as described in the Reading data using HandlerSocket and Python recipe earlier in this chapter.
The output of the commands run in this recipe will look like the following screenshot:
See also
· The documentation of the pyhs library is available at http://python-handler-socket.readthedocs.org/en/latest/
Updating and deleting data using HandlerSocket and Python
Updating and deleting data is similar to but not quite the same as inserting data.
Getting ready
Complete the Inserting data using HandlerSocket and Python recipe prior to starting this recipe.
How to do it...
1. Launch the interactive Python interpreter in a terminal window as follows:
2. python
3. Run the following commands in the Python interpreter:
4. from pyhs.sockets import WriteSocket
5. hsu = WriteSocket([('inet', '127.0.0.1', 9999)])
6. u_id = hsu.get_index_id('test', 'hs_test', ['givenname'])
7. hsu.find_modify(u_id, '=', ['3'],'U',['Jon'],10,0)
8. Then, run the following commands in the Python interpreter to open new read and write connections to our test table:
9. from pyhs.sockets import ReadSocket
10.hsr = ReadSocket([('inet', '127.0.0.1', 9998)])
11.r_id = hsr.get_index_id('test', 'hs_test', ['id', 'givenname', 'surname'])
12.
13.from pyhs.sockets import WriteSocket
14.hsd = WriteSocket([('inet', '127.0.0.1', 9999)])
15.d_id = hsd.get_index_id('test', 'hs_test', ['id', 'givenname', 'surname'])
16. Run the following commands to test the process of deleting data:
17.hsr.find(r_id, '>=', ['1'],20)
18.hsd.insert(d_id, ['101','Junk','Entry'])
19.hsd.insert(d_id, ['102','Junk','Entry'])
20.hsd.insert(d_id, ['103','Junk','Entry'])
21.hsr.find(r_id, '>=', ['1'],20)
22.hsd.find_modify(d_id, '>', ['100'],'D','',10)
23.hsr.find(r_id, '>=', ['1'],20)
How it works...
To update data, we use the WriteSocket object of the pyhs library. First, we open a connection to the HandlerSocket write port, and then we call the get_index_id method to define the database, table, and columns we want to update. In step 2 of our recipe, we're only updating the givenname column, so that's all we define.
We update data using the find_modify method. This method takes seven arguments. First is the variable we used when calling get_index_id (we used u_id in our recipe), and the second is the comparison operator we want to use for finding the values to update; the supported operators are =, >, <, >=, and <=. The third argument is the key value we want to search for (3 in our recipe). The fourth argument is the character U that tells the method that we are performing an update. Fifth, is an array of the values we want to update. This array corresponds to the columns we specified when we called the get_index_id method; just fill the givenname column in our recipe. The sixth and seventh arguments set the maximum number of rows to update and the number of records to skip before searching records, respectively. We set these to 10 and 0 in our recipe, respectively.
On a successful update, the find_modify method will return the number of rows updated as an array. The returned value in the Python interpreter will look like the following command:
[('1',)]
The complete output of the recipe will look like the following screenshot:
The WriteSocket object doesn't have a find method, so if we want to read back the row we just updated, we need to use the ReadSocket object as described in the Reading data using HandlerSocket and Python recipe earlier in this chapter.
To test the process of deleting data in steps 3 and 4 of our recipe, we first import the ReadSocket object in step 3 like we did in the Reading data using HandlerSocket and Python recipe earlier in this chapter, so we can easily read the data to show the before and after states of our table. We then open a new WriteSocket object, this time defining all of the columns in our table instead of just the givenname column.
Then, in step 4 of our recipe, we actually test the code by inserting some data, deleting it, and reading the data in our table before and after each step. When deleting rows in our recipe, we supply the find_modify method with six arguments. First is the variable we used when calling get_index_id (we used d_id in our recipe), and the second is the comparison operator we want to use for finding the rows to delete (> in our recipe). The third argument is the key value we want to search for (100 in our recipe). The fourth argumentis the character D, which tells the method that we are performing a delete operation. The fifth argument of the find_modify method is an array of the values we want to update; this is only used when updating a row, which we aren't doing here, so we supply an empty value. We do this so that we can specify the sixth argument, which is the limit of the number of rows we want to modify. We do this because if this value is not specified, the method will default to only deleting a single row. In our recipe, we want to delete all rows withid greater than 100, so we set this to 10 (we could set it to 3 since there are only three that match, but in cases where we don't know the exact number of rows we are deleting and we want to make sure we delete them all, it's better to set this value to a larger value than we need to). The result we get back will be an array containing a single value equal to the number of rows deleted.
In our recipe, we print out our data in our example table twice; once before the deletion and then again after it. The key output is in between when the find_modify method returns the number of rows deleted. In our recipe, the output will be the following command:
[('3',)]
The complete output of steps 3 and 4 will look like the following screenshot:
See also
· The documentation of the pyhs library is available at http://python-handler-socket.readthedocs.org/en/latest/
Installing the HandlerSocket Ruby client libraries
Ruby is the last language in our trio of scripting languages compatible with HandlerSocket in this chapter. Installing and using this library is easy.
Getting ready
Install the libhsclient library as described in the Installing the libhsclient library recipe earlier in this chapter. In order to build the HandlerSocket Ruby client libraries, we need to install some development tools and packages.
On Red Hat, Fedora, or CentOS, run the following command:
sudo yum install ruby-irb rubygems ruby-rdoc ruby-devel
On Debian or Ubuntu, run the following command:
sudo apt-get install irb rubygems rdoc ruby-dev
We are now ready to install the HandlerSocket Ruby client libraries.
How to do it...
1. Use rubygems to install the handlersocket gem in a terminal window, as follows:
2. sudo gem install 'handlersocket'
3. Launch the irb interactive Ruby interpreter and check that the handlersocket library loads as follows:
4. irb
5. require 'rubygems'
6. require 'handlersocket'
How it works...
The RubyGems package manager makes installing the HandlerSocket Ruby library easy. To test that the library is correctly installed, we just need to launch the irb interactive Ruby interpreter and try to run the require handlersocket command. If the library loads correctly, the interpreter will return the following output:
=> true
There's more...
On some systems, the require 'rubygems' line is not needed. If it is not required, the command will return false and the require 'handlersocket' line will still return true. So, if we want, we can just ignore the harmless false report or omit the require 'rubygems' line altogether. When the require 'rubygems' line is required, irb will complain that it cannot find the handlersocket library. Once we run the require command on the rubygems library, the handlersocket library can be found. The output of this recipe on Ubuntu and Debian will look like the following screenshot:
See also
· The source code of the Ruby handlersocket library, along with examples, can be found at https://github.com/miyucy/handlersocket
Reading data using HandlerSocket and Ruby
In some ways, using the Ruby HandlerSocket client library is very similar to using the Python HandlerSocket client library described earlier in this chapter. This is mainly true in the commands we send to HandlerSocket, but there are differences that can trip us up if we're not careful.
Getting ready
Install the HandlerSocket Ruby client libraries as described in the Installing the HandlerSocket Ruby client libraries recipe earlier in this chapter. Launch the mysql command-line client and run the SQL commands from the Getting ready section of the Reading data using HandlerSocket and PERL recipe earlier in this chapter to give us some sample data to read (and if we've already gone through the PERL or Python recipes, running the SQL commands again will reset the sample data to its default state).
How to do it...
1. Launch the interactive Ruby interpreter in a terminal window as follows:
2. irb
3. Open a connection to our database in the irb interpreter as follows:
4. require 'rubygems'
5. require 'handlersocket'
6. hs = HandlerSocket.new(:host => '127.0.0.1',:port => '9998')
7. hs.open_index(0,'test','hs_test','PRIMARY','id,givenname,surname')
8. Then, read some data in the irb interpreter as follows:
9. p hs.execute_single(0,'=',[1])
10.p hs.execute_single(0,'>',[1],2,2)
11.p hs.execute_single(0,'>=',[1],20)
How it works...
To read data, we first create a connection to the HandlerSocket read-only port using the HandlerSocket.new method. This method takes two arguments: the host, which is the IP address or domain name of the host we are connecting to, and the port. We are connecting to the local host and the read-only port, so we put in 127.0.0.1 and 9998, respectively. Then, we call the .open_index method to define the database, table, and columns we are interested in. This method takes five arguments. First is an identification number, which can be any integer we want; in our recipe we use 0. The second and third arguments are the database name and table name we want to read, respectively. The fourth argument is the name of the key we want to search on. In our example table, the only key is the primary key, so we use the key word PRIMARY. The fifth argument is a comma-separated list of the columns we want to read. In our recipe, we name all of the columns in our example table (id, givenname, and surname).
We are then able to read data using the execute_single method. This method takes three arguments with the optional fourth and fifth arguments. First is the variable we used when calling open_index (we used 0 in our recipe), and the second is the comparison operator we want to use; the supported operators are =, >, <, >=, and <=. The third argument is the key value we want to search for. The optional fourth and fifth arguments set the maximum number of rows to return and the number of records to skip before retrieving records, respectively. If these arguments are not specified, the execute_single method will set them both to 0, which combine to return only the first matching record for our search. The results are returned as an array. Of the three calls to the execute_single method in our recipe, the first returns a single row, the second returns two rows, and the last returns all of the records in our example table. All are output as a multidimensional array containing a subarray of the resulting data. The returned values will look like the following command:
[0, [["1", "William", "Hartnell"]]]
[0, [["4", "Tom", "Baker"], ["5", "Peter", "Davison"]]]
[0, [["1", "William", "Hartnell"], ["2", "Patrick", "Troughton"], ["3", "John", "Pertwee"], ["4", "Tom", "Baker"], ["5", "Peter", "Davison"], ["6", "Colin", "Baker"]]]
The complete output in the irb interpreter will look like the following screenshot:
See also
· The source code of the Ruby handlersocket library, along with examples, can be found at https://github.com/miyucy/handlersocket
Inserting data using HandlerSocket and Ruby
Now that we can read data (as described in the previous recipe), it's time to learn how to insert data using Ruby.
Getting ready
Complete the Reading data using HandlerSocket and Ruby recipe described earlier in this chapter prior to starting this recipe.
How to do it...
1. Launch the interactive Ruby interpreter in a terminal window as follows:
2. irb
3. Open a connection to our database in the irb interpreter as follows:
4. require 'rubygems'
5. require 'handlersocket'
6. hsw = HandlerSocket.new(:host => '127.0.0.1',:port => '9999')
7. hsw.open_index(1,'test','hs_test','PRIMARY','id,givenname,surname')
8. Still in the irb interpreter, insert a couple of new rows using the following statements:
9. p hsw.execute_single(1,'+',[7,'Sylvester','McCoy'])
10.p hsw.execute_single(1,'+',[8,'Paul','McGann'])
11. Then, read the rows we entered using the following statements in the irb interpreter:
12.p hsw.execute_single(1,'=',[7])
13.p hsw.execute_single(1,'=',[8])
14.p hsw.execute_single(1,'>=',[1],20)
How it works...
Similar to reading data, we first open a connection with HandlerSocket.new to the read-write port (9999) when inserting data. We then use the execute_single method to insert data. This method takes three arguments. First is the number we used when calling open_index(we used 1 in our recipe), and the second is the + character, which tells the method we are inserting data. The third argument is a comma-separated list of the values we want to insert. These must correspond to the comma-separated list of columns we defined when calling open_index (we used id, givenname, and surname in our recipe). After successful insertion, the execute_single method will return an array with zeroes, thus specifying true. This will look like the following command in the irb interpreter:
[0, [["0"]]]
The last step in our recipe is to read the rows we entered and then read all of the rows in our table. The returned values will look like the following command:
[0, [["7", "Sylvester", "McCoy"]]]
[0, [["8", "Paul", "McGann"]]]
[0, [["1", "William", "Hartnell"], ["2", "Patrick", "Troughton"], ["3", "John", "Pertwee"], ["4", "Tom", "Baker"], ["5", "Peter", "Davison"], ["6", "Colin", "Baker"], ["7", "Sylvester", "McCoy"], ["8", "Paul", "McGann"]]]
The complete output of this recipe in irb will look like the following screenshot:
See also
· The source code of the Ruby handlersocket library, along with examples, can be found at https://github.com/miyucy/handlersocket
Updating and deleting data using HandlerSocket and Ruby
Updating and deleting data is similar to but not quite the same as inserting data. In this recipe, we will use Ruby and HandlerSocket to update and delete data.
Getting ready
Complete the Inserting data using HandlerSocket and Ruby recipe prior to starting this recipe.
How to do it...
1. Launch the interactive Ruby interpreter in a terminal window as follows:
2. irb
3. Run the following commands in the irb interpreter to open a HandlerSocket connection to our test database and the hs_test table:
4. require 'rubygems'
5. require 'handlersocket'
6. hsu = HandlerSocket.new(:host => '127.0.0.1',:port => '9999')
7. hsu.open_index(2,'test','hs_test','PRIMARY','givenname')
8. Then, update a row in the interpreter using the following statement:
9. p hsu.execute_single(2,'=',[3],1,0,'U',['Jon'])
10. Read out the value of the column we just updated in irb to confirm that the data was updated using the following statement:
11.p hsu.execute_single(2,'=',[3])
12. Open a connection to use for deleting data using the following statements:
13.hsd = HandlerSocket.new(:host => '127.0.0.1',:port => '9999')
14.hsd.open_index(3,'test','hs_test','PRIMARY','id,givenname,surname')
15. Insert some junk data for us to delete using the following statements:
16.p hsd.execute_single(3,'+',[101,'Junk','Entry'])
17.p hsd.execute_single(3,'+',[102,'Junk','Entry'])
18.p hsd.execute_single(3,'+',[103,'Junk','Entry'])
19. Read all of the data in our table, delete the junk data, and then read our table again to confirm the deletion using the following statements:
20.p hsd.execute_single(3,'>=',[1],20)
21.p hsd.execute_single(3,'>',[100],10,0,'D')
22.p hsd.execute_single(3,'>=',[1],20)
How it works...
To update data, we first open a connection the same way we did when inserting data. In our recipe, we're only interested in updating a single column. So, when calling the open_index method, we only specify the givenname column.
We update data in step 3 using the execute_single method but with more arguments than what we use when reading or inserting data. First is the number we used when calling open_index (we used 2 in our recipe). The second is the comparison operator we want to use for finding the values to update; the supported operators are =, >, <, >=, and <=. The third argument is the key value we want to search for (3 in our recipe). The fourth and fifth arguments set the maximum number of rows to update and the number of rows to skip before trying to match records, respectively. In our recipe, we set these to 10 and 0, respectively.
The sixth argument is the character U that tells the method that we are performing an update. Seventh, is an array of the values we want to update the rows we find to. This array corresponds to the columns we specified when we called the open_index method; just fill the givenname column in our recipe. The sixth and seventh arguments set the maximum number of rows to update and the number of records to skip before searching records, respectively. We set these to 10 and 0 in our recipe. After a successful update, theexecute_single method will return a comma-separated list containing a 0 if the command was successful and then the number of rows updated as an array. The output in irb will look like the following command:
[0, [["1"]]]
We then use the .execute_single method in step 4 to read the column we just updated. The output in irb will look like the following command:
[0, [["Jon"]]]
The complete output of steps 1 through 5 of this recipe in irb will look like the following screenshot:
To test the process of deleting data, we begin with step 5 of our recipe to call HandlerSocket.new again, this time defining all of the columns in our table instead of just the givenname column. We also give our connection a new integer identification number, (3), to distinguish it from the number, (2), we assigned to our update connect.
In step 6 of our recipe, we insert some junk data. Then, in step 7, we read our table, delete the rows we entered in step 6, and then read the table again to confirm the deletion.
When deleting rows in our recipe, we supply the execute_single method with six arguments. First is the variable we used when calling open_index (we used 3 in our recipe). Second is the comparison operator we want to use for finding the rows to delete (> in our recipe). The third argument is the key value we want to search for (100 in our recipe). The fourth and fifth arguments set the maximum number of rows to delete and the number of rows to skip before trying to match records, respectively. In our recipe, we set these to10 and 0, respectively. The sixth argument is the character D, which tells the method that we are performing a delete operation. After a successful delete, the execute_single method will return a comma-separated list first containing a 0 if the command was successful and then the number of rows deleted as an array. Since we deleted three rows, the output of the delete operation in irb will look like the following command:
[0, [["3"]]]
The complete output of steps 5 through 7 of this recipe in irb will look like the following screenshot:
See also
· The source code of the Ruby handlersocket library, along with examples, is at https://github.com/miyucy/handlersocket
Using HandlerSocket directly with Telnet
HandlerSocket listens on two ports, 9998 and 9999, for clients to talk to it. This means we can interact with it directly using telnet.
Getting ready
Install and configure the HandlerSocket plugin as described in the Installing and configuring HandlerSocket recipe earlier in this chapter. Launch the mysql command-line client and run the SQL commands from the Getting ready section of the Reading data using HandlerSocket and PERL recipe, described earlier in this chapter, to give us some sample data to read (and if we've already gone through the PERL, Python, or Ruby recipes, running the SQL commands again will reset the sample data to its default state).
We'll also need to install a telnet client. Most Linux distributions should have one either installed by default or easily installable from the system package repositories.
How to do it...
1. Open telnet and connect to the HandlerSocket read port in a command-line window as follows:
2. telnet 127.0.0.1 9998
3. Enter the following commands (what looks like spaces in the following code are all tabs):
4. P 0 test hs_test PRIMARY id,givenname,surname
5. 0 = 1 1
6. 0 > 1 2 2
7. 0 >= 1 1 20
8. Disconnect by typing Ctrl + ] and then quit.
9. Open a new telnet session; this time connect to the write port using the following command:
10.telnet 127.0.0.1 9999
11. Run the following commands (remember to use tabs for all white spaces):
12.P 1 test hs_test PRIMARY id,givenname,surname
13.1 + 3 7 Sylvester McCoy
14.1 + 3 8 Paul McGann
15.1 >= 1 1 20
16. Then, run the following commands in the same telnet session:
17.P 2 test hs_test PRIMARY givenname
18.2 = 1 3 1 0 U Jon
19.1 = 1 3
20. Finally, run the following commands in the same telnet session:
21.P 3 test hs_test PRIMARY id,givenname,surname
22.3 + 3 101 Junk Entry
23.3 + 3 102 Junk E
24.ntry
25.3 + 3 103 Junk Entry
26.1 >= 1 1 20
27.3 > 1 100 10 0 D
28.1 >= 1 1 20
How it works...
To read data, we first open a connection using our telnet client and connect to the read-only port, 9998.
In step 2, we first tell HandlerSocket about the database and table we want to connect to and the columns we are interested in. This command begins with the key letter, P, and then a number that is used to identify the connection. The number can be any positive number; we use 0 for simplicity. We then name the database (test) and the table (hs_test). Next, we specify the key we want to search on. In our example table, the only key is the primary key, so we use the key word, PRIMARY. The sixth argument is a comma-separated list of the columns we want to read. In our recipe, we name all of the columns in our example table (id, givenname, and surname). All of the arguments must be separated by tabs, not spaces.
With a connection defined, we are now able to read data. To do so, the basic form of the command takes at least four arguments, with an optional fifth argument. First is the identification number we chose when defining the connection 0. Second is the comparison operator we want to use; the supported operators are =, >, <, >=, and <=. The third argument is the number of index columns we are going to search. This must be equal to or less than the number of index columns we defined. As we only defined one index column (the primary key using the key word PRIMARY), we put 1 here. The fourth argument is the index value to search for. In the recipe, our first search command retrieves the row where id equals 1. The output will look similar to the following command:
0 3 1 William Hartnell
The first field in the output is 0, which signifies success. The next filed will have each of the records that are being returned. We defined three columns when we defined our connection, so the number here is 3. Finally, there are the three fields of the record. Our search was for all records with id equal to 1, which, of course, only matches one record.
The second and third search commands in step 2 use the optional fifth argument, which sets the limit of the number of records to retrieve. By default, if this argument is not set, HandlerSocket will return only single records, which was fine for our first search. The second search is for the records where id is greater than 2. This could match many records, so we limit it to 27, which means we get records three and four back. The output will look similar to the following command:
0 3 3 John Pertwee 4 Tom Baker
When HandlerSocket returns data, it does so as one long string, so when it has finished giving us one result of the several, it immediately starts giving us the second, and so on. As the second field in the output is 3, we know the first three fields following it are the first result and the next three fields are the second.
The third search grabs up to 20 records where id is greater than or equal to 1. As our table only has six rows, this search has the effect of grabbing all records. At first glance, the output looks a little jumbled, but once we know what to look for, it is easy to parse.
In step 3 of our recipe, we disconnect so that we can reconnect on the write port. The complete input and output of steps 1 through 3 will look like the following screenshot:
In step 4 of this recipe, we reconnect to the read-write port, 9999, this time so that we can insert, update, and delete records. In step 5 we insert some new rows. First, we define a connection, identifying the connection with the number 1 this time.
The command to insert takes three arguments plus the data we want to insert. The first argument is the connection identification number. The second is the + character, which signifies that we are inserting data. The third is the number of fields we specified when defining our connection (3 in our recipe). In our recipe, we insert two rows. After successful insertion, HandlerSocket responds with the following command:
0 1 0
The first value is the error code. A 0 value means there was no error. The second field is the number of columns in the result set. The third field is the actual result. For an insert, the result of a successful insert is a single column with the result of 0.
In step 6, we update a row. When defining our database connection, we only specify the givenname column because that is the column we are updating. The fields in the update statement are, first, the connection identification number and second, a comparison operator. In our recipe, we use = because we are looking for an exact match. Third is the number of columns in our table we are updating. Fourth is the primary key value we are searching for. Fifth is the limit of the number of rows to modify. Sixth is the offset from the first row in the table to begin searching from. Seventh is the key letter, U, to signify we are performing an update. Eighth is the new values, or in our recipe, value. All together the line looks like the following command:
2 = 1 3 1 0 U Jon
The output looks like the following command:
0 1 1
As with inserting data, the first value in the response is the error code, with 0 meaning success. Likewise, the second value is the number of columns in the response. For an update, this will most likely be 1. The third column in an update response is the number of rows modified; 1 in our recipe.
The complete output of steps 4 through 6 will look like the following screenshot:
In step 7, we first insert some junk rows and then delete them. The insertion is the same as before. The syntax of the delete statement is similar to the update syntax, except for the fact that because we are deleting, we don't need to provide the new values at the end. We use D instead of U so that HandlerSocket knows we are performing a delete. The delete statement now looks as follows:
3 > 1 100 10 0 D
The effect of this statement is to search for up to 10 records that have id values greater than 100 and delete them.
The complete output of step 7 will look like the following screenshot:
See also
· The documentation of the HandlerSocket protocol is available at https://github.com/DeNA/HandlerSocket-Plugin-for-MySQL/blob/master/docs-en/protocol.en.txt