Handlersocket protocol in detail
Hello everyone. I decided to publish the Russian version of my own article “HandlerSocket protocol explained” published at http://wk-photo.ru/en/events/view/handlersocket-protocol-explained/ .
So you walked and walked and came to the HandlerSocket . Pure honey. This is a devilishly fast voodoo. And the protocol used is really simple, like two kopecks. Well, if we are honest, who cares about the details of the protocol, if any library will be used to take care of everything? If, in spite of everything, you still want to know what kind of neon is there inside, you can google this page . A few hours - and you are an expert. Well, or you want everything in 15 minutes. Then welcome pzhalovat under the cat!
')
It is assumed that you are already at least remotely familiar with Handlersocket, have a MySQL / MariaDB / Percona server with a plug-in enabled. I used MariaDB, there Handlersocket is available by default, you just need to enable the plugin, seasoning my.cnf with a small number of lines to your taste .
For advertising: https://github.com/crocodile2u/zhandlersocket - my PHP extension for working with Handlersocket.
Go. Log in to the MariaDB console and create a table:
Now connect to Handlersocket. Port 9999, I mean the default port for writing: with this connection, I can both read and write. Since usually you still need to read more often than to write, it will be more efficient to think in advance what actions will be needed in the application, and open a connection to the port for reading or reading / writing, respectively. For reference: by default, Handlersocket starts 16 workers for handling read-only connections and one worker for reading / writing connections.
The class. Now try to talk to this thing. The first thing to do when using Handlersocket is to open the index. For any queries that you send to HS to find or change data, you need an index identifier. Let us see what the documentation says:
The " open_index " query has the following syntax:
Well, everything is clear ... Oh, andwhose this pants with a motor what kind of / indexid /? This is the index ID your application should provide. It is not generated by Handlersocket. In fact, an application can send a request consisting of a number of lines to HS at once:
This is possible only if the application ID is assigned by the application, otherwise you would have to first send to HS a request to open the index, get an identifier from the HS, and only then use this identifier in your queries.
Let's continue our exercises in the HS console (telnet). I forgot to say, parts of the request in HS are separated by TABs. In our example, I use the optional / fcolumns / parameter for the first index. We still need it.
What is this? Doca claims that in return we should get “0 1”! A. Well, yes, it's my own fault, I forgot the / indexname / parameter.
Now order. We are going to knock on the table on the primary key, so that / indexname / we will have PRIMARY. Let's open another HS-index, for a MySQL-index named genre:
Oh, you feed you. And this one has opened. Let's now add to the table row? What is there in our documentation?
... and we should get an answer like this:
Let's try ...
Oops. The answer is not quite as in the dock. Additional one. Let's see what happens with the table in the MariaDB console:
It worked! Come on, come on ...
Look again at MariaDB:
Oops! The unknown third digit returned by HS exactly matches the AUTO_INCREMENT field id!
And now we will try to specify the value for id manually ...
OK. “1” in the first position of the answer tells us that an error has occurred. HS, however, is not too verbose. From the additional information, we only have the magic number "121", which means ... God knows, actually, but I think that in such a simple way we are hinted at an error like "DUPLICATE KEY". One more time…
We look at MariaDB, our new entry was added with ID = 4:
Something like this we insert the records into the tables via HS and get the values of the AUTO_INCREMENT field. Documentation of this is silent. Perhaps because, in the old days, HS did not support AUTO_INCREMENT.
Be careful of dynamic columns with default values . Such as "created_at DATETIME DEFAULT CURRENT_TIMESTAMP" or "updated_at DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP". When inserts and updates, you must set their values manually ! Otherwise, instead of the current timestamp, when inserted into the created_at field, you will get "0000-00-00 00:00:00". And if you do not specify a value for updated_at when updating, the previous value will be there.
With INSERTs finished, uh ... Now let's try to choose something from HS. We look in man ...
Well, everything is clear, in full view. Let's go further.
Okay, okay, just kidding. Let's try to find a record by ID. First comes / indexid / - in our case it is 1 (the index that we opened for the primary key). Then / op /: we want to get a record with id = 1, so this will be "=". Then, / vlen /. This is the number of columns in the index. The index may consist of several columns. In our case, however, it is 1. Then comes part / v1 / ... / vn /. These are the values of the columns, in our example one column and the value 1:
Hooray, it worked! The result can be interpreted as:
What if we try another operation (/ op /)? For example, get entries with id> 1:
How so? Only one entry? We had more, I give a tooth! Ook, this is in the documentation: “LIM is is optional. / limit / and / offset / are numbers. When omitted, it works as if 1 and 0 are specified. ” Let's try to specify enough LIMIT to get all the rows that interest us.
All results are returned in one line:
All this will need to be disassembled in the client application (we know the number of columns, we know the order in which we listed them when opening the index, so there are no problems). Maybe you are asking yourself the question: what will happen if there is a tab in our data? Still sprinkle! The protocol describes the transmission of characters from 0x00 to 0x0f. Client libraries must encode these characters using a simple algorithm (the prefix 0x01 is added to them and shifted to 0x40). The client library should take care of this. For simplicity, we will not use symbols that require encoding in our examples.
In addition, we still have an IN query - that is, you can immediately get heaps of records by their keys, in one query! I conducted several experiments. To begin with, I did not know what to do with / op /, / vlen / and / v1 / ... / vn /. After all, we are going to specify all the id that we want to receive in the IN part. Well, let's try the same way as we tried with a simple id search.
The IN condition is "@ 0 1 2", that is, column number 0, one value, and this value is 2.
"Modop". Thanks, HandlerSocket! Now everything is clear! Let's try to change a little ...
Again. What do you want? And what if I specify LIMIT?
That's it! It seems that for IN queries you need to specify LIMIT and OFFSET. / op /, / vlen / and / v1 / ... / vn / also need to be specified, although this only confuses. In the example below, I specify the value 1 instead of 0 in the / vn / - section and get exactly the same answer!
I will try to change / op /:
Where is it from? Well, let's try to get more than one id:
Damn, I forgot about LIMIT. We indicate that we need 2 two entries:
Obviously, our id were ignored, and HS returns records with id> 1. In the end, I found out that in the IN query, we can safely specify “=” as / op /, and 0 as the value / v1 /. You can see that we got the results exactly in the order we indicated them in the IN condition. However, I do not know if it is a coincidence or not. I would not rely on it.
Let's go to the filters? What if we take our last query and ask HS to apply the filter by genre = 'Sci-Fi'? Remember when we opened the index, we specified / fcolumns /? Now they will come in handy to apply additional filters. If we did not specify / fcolumns /, any query with filters would return “2 1 filterfld”.
It worked! OK, this is probably all about getting records from HS. Now we go to UPDATE'am. What about the documentation?
The first part looks familiar. We only saw all this when we looked at FIND queries, and it looks like these are WHERE conditions for our UDPATE. Update the row with id = 1 and set view_count = 100:
modop. So glad. How clear! What if you specify LIMIT?
Taak. Let's take a look at MariaDB:
Let's try something more complicated. Increase the view_count counter for all comedies by 10.
It looks like the rules! Let's take a look at MariaDB:
No, it did not work. At least it did not work well. The counter has indeed increased by 10, but all other columns have also increased. Since id is numeric and we said to increment it by 0, it remained untouched. I will return everything back:
I can say right away that specifying a value for the “genre” and “title” columns in an increment request does not work. They will get the value "0", just as happened at our first attempt. In order for the increment to work as it should, you need to open a separate index, which will contain only the columns "id" and "view_count":
Well, another thing:
That's all. Happy handlers!
So you walked and walked and came to the HandlerSocket . Pure honey. This is a devilishly fast voodoo. And the protocol used is really simple, like two kopecks. Well, if we are honest, who cares about the details of the protocol, if any library will be used to take care of everything? If, in spite of everything, you still want to know what kind of neon is there inside, you can google this page . A few hours - and you are an expert. Well, or you want everything in 15 minutes. Then welcome pzhalovat under the cat!
')
It is assumed that you are already at least remotely familiar with Handlersocket, have a MySQL / MariaDB / Percona server with a plug-in enabled. I used MariaDB, there Handlersocket is available by default, you just need to enable the plugin, seasoning my.cnf with a small number of lines to your taste .
For advertising: https://github.com/crocodile2u/zhandlersocket - my PHP extension for working with Handlersocket.
Go. Log in to the MariaDB console and create a table:
CREATE TABLE movie ( id int not null auto_increment primary key, genre varchar(20) not null, title varchar(100) not null, view_count int default 0, key(genre) ) engine innodb; Now connect to Handlersocket. Port 9999, I mean the default port for writing: with this connection, I can both read and write. Since usually you still need to read more often than to write, it will be more efficient to think in advance what actions will be needed in the application, and open a connection to the port for reading or reading / writing, respectively. For reference: by default, Handlersocket starts 16 workers for handling read-only connections and one worker for reading / writing connections.
~/ telnet localhost 9999 Trying 127.0.0.1... Connected to localhost. Escape character is '^]'. The class. Now try to talk to this thing. The first thing to do when using Handlersocket is to open the index. For any queries that you send to HS to find or change data, you need an index identifier. Let us see what the documentation says:
The " open_index " query has the following syntax:
P /indexid/ /dbname/ /tablename/ /indexname/ /columns/ [/fcolumns/]
- / indexid / is a number in decimal.
- / dbname /, / tablename /, and / indexname / are strings. To open the primary key, use PRIMARY as / indexname /.
- / columns / is a comma-list of column names.
- / fcolumns / is a list of column names. This parameter is optional.
Well, everything is clear ... Oh, and
Open index 1 Find row(s) in index 1 Find/Modify row(s) in index 1 ... This is possible only if the application ID is assigned by the application, otherwise you would have to first send to HS a request to open the index, get an identifier from the HS, and only then use this identifier in your queries.
Let's continue our exercises in the HS console (telnet). I forgot to say, parts of the request in HS are separated by TABs. In our example, I use the optional / fcolumns / parameter for the first index. We still need it.
~/ telnet localhost 9999 ... P 1 test movie id,genre,title,view_count genre 2 1 idxnum What is this? Doca claims that in return we should get “0 1”! A. Well, yes, it's my own fault, I forgot the / indexname / parameter.
P 1 test movie PRIMARY id,genre,title,view_count genre 0 1 Now order. We are going to knock on the table on the primary key, so that / indexname / we will have PRIMARY. Let's open another HS-index, for a MySQL-index named genre:
P 2 test movie genre id,genre,title,view_count 0 1 Oh, you feed you. And this one has opened. Let's now add to the table row? What is there in our documentation?
'Insert' request has the following syntax./indexid/ + /vlen/ /v1/ ... /vn/
- / vlen / indicates the length of the trailing parameters / v1 / ... / vn /. This is the number of columns / columns / specified by the corresponding open_index request.
- / v1 / ... / vn / specify the column values to set. For each column are not set.
... and we should get an answer like this:
If 'insert' is succeeded, HanderSocket returns a line of the following syntax.
0 1
Let's try ...
1 + 3 0 Sci-Fi Star wars 0 1 1 Oops. The answer is not quite as in the dock. Additional one. Let's see what happens with the table in the MariaDB console:
SELECT * FROM movie; +----+--------+-----------+ | id | genre | title | +----+--------+-----------+ | 1 | Sci-Fi | Star wars | +----+--------+-----------+ It worked! Come on, come on ...
1 + 3 0 Comedy Dumb & Dumber 0 1 2 1 + 3 0 Thriller The Silence of the Lambs 0 1 3 Look again at MariaDB:
SELECT * FROM movie; +----+----------+--------------------------+ | id | genre | title | +----+----------+--------------------------+ | 1 | Sci-Fi | Star wars | | 2 | Comedy | Dumb & Dumber | | 3 | Thriller | The Silence of the Lambs | +----+----------+--------------------------+ Oops! The unknown third digit returned by HS exactly matches the AUTO_INCREMENT field id!
And now we will try to specify the value for id manually ...
1 + 3 1 Sci-Fi Star Trek 1 1 121 OK. “1” in the first position of the answer tells us that an error has occurred. HS, however, is not too verbose. From the additional information, we only have the magic number "121", which means ... God knows, actually, but I think that in such a simple way we are hinted at an error like "DUPLICATE KEY". One more time…
1 + 3 4 Sci-Fi Star Trek 0 1 0 We look at MariaDB, our new entry was added with ID = 4:
SELECT * FROM movie; +----+----------+--------------------------+ | id | genre | title | +----+----------+--------------------------+ | ... | | 4 | Sci-Fi | Star Trek | +----+----------+--------------------------+ Something like this we insert the records into the tables via HS and get the values of the AUTO_INCREMENT field. Documentation of this is silent. Perhaps because, in the old days, HS did not support AUTO_INCREMENT.
Be careful of dynamic columns with default values . Such as "created_at DATETIME DEFAULT CURRENT_TIMESTAMP" or "updated_at DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP". When inserts and updates, you must set their values manually ! Otherwise, instead of the current timestamp, when inserted into the created_at field, you will get "0000-00-00 00:00:00". And if you do not specify a value for updated_at when updating, the previous value will be there.
With INSERTs finished, uh ... Now let's try to choose something from HS. We look in man ...
The 'find' request has the following syntax.
/ indexid / / op / / vlen / / v1 / ... / vn / [LIM] [IN] [FILTER ...]
LIM is a sequence of the following parameters.
/ limit / / offset /
IN is a sequence of the following parameters.@ /icol/ /ivlen/ /iv1/ ... /ivn/
FILETER is a sequence of the following parameters./ftyp/ /fop/ /fcol/ /fval/
- / indexid / is a number. This is the number that should be indicated.
- / op / specifies the comparison operation to use. The current version of HandlerSocket supports '=', '/', '/ =', '/', and '/ ='.
- / vlen / indicates the length of the trailing parameters / v1 / ... / vn /. This is the number of the corresponding 'open_index' request.
- / v1 / ... / vn / specify the column column values to fetch.
- LIM is optional. / limit / and / offset / are numbers. When omitted, it works as if 1 and 0 are specified. These parameter works like LIMIT of SQL. Skipped by a filter.
- IN is optional. It works like WHERE ... IN syntax of SQL. / icol / must be the corresponding index of the corresponding open_index request. If this is the request, the / icol / -th parameter value of / v1 / ... / vn / is ignored.
- FILTERs are optional. A FILTER specifies a filter. / ftyp / is either 'F' (filter) or 'W' (while). / fop / specifies the comparison operation to use. / fcol / must be smaller than the number of the corresponding 'open_index' request. AND. It is simply the skins of the record, and the 'F'
Well, everything is clear, in full view. Let's go further.
Okay, okay, just kidding. Let's try to find a record by ID. First comes / indexid / - in our case it is 1 (the index that we opened for the primary key). Then / op /: we want to get a record with id = 1, so this will be "=". Then, / vlen /. This is the number of columns in the index. The index may consist of several columns. In our case, however, it is 1. Then comes part / v1 / ... / vn /. These are the values of the columns, in our example one column and the value 1:
1 = 1 1 0 3 1 Sci-Fi Star wars Hooray, it worked! The result can be interpreted as:
0 - no errors; 3 - number of columns [1 Sci-Fi Star wars] - the row that we've got from InnoDB. What if we try another operation (/ op /)? For example, get entries with id> 1:
1 > 1 1 0 3 2 Comedy Dumb & Dumber How so? Only one entry? We had more, I give a tooth! Ook, this is in the documentation: “LIM is is optional. / limit / and / offset / are numbers. When omitted, it works as if 1 and 0 are specified. ” Let's try to specify enough LIMIT to get all the rows that interest us.
1 > 1 1 10 0 0 3 2 Comedy Dumb & Dumber 3 Thriller The Silence of the Lambs 6 Sci-Fi Star Trek All results are returned in one line:
0 - no errors; 3 - number of columns [2 Comedy Dumb & Dumber] - row 1 [3 Thriller The Silence of the Lambs] - row 2 [6 Sci-Fi Star Trek] - row 3 All this will need to be disassembled in the client application (we know the number of columns, we know the order in which we listed them when opening the index, so there are no problems). Maybe you are asking yourself the question: what will happen if there is a tab in our data? Still sprinkle! The protocol describes the transmission of characters from 0x00 to 0x0f. Client libraries must encode these characters using a simple algorithm (the prefix 0x01 is added to them and shifted to 0x40). The client library should take care of this. For simplicity, we will not use symbols that require encoding in our examples.
In addition, we still have an IN query - that is, you can immediately get heaps of records by their keys, in one query! I conducted several experiments. To begin with, I did not know what to do with / op /, / vlen / and / v1 / ... / vn /. After all, we are going to specify all the id that we want to receive in the IN part. Well, let's try the same way as we tried with a simple id search.
The IN condition is "@ 0 1 2", that is, column number 0, one value, and this value is 2.
1 = 1 1 @ 0 1 2 2 1 modop "Modop". Thanks, HandlerSocket! Now everything is clear! Let's try to change a little ...
1 = 1 0 @ 0 1 2 2 1 modop Again. What do you want? And what if I specify LIMIT?
1 = 1 0 1 0 @ 0 1 2 0 3 2 Comedy Dumb & Dumber That's it! It seems that for IN queries you need to specify LIMIT and OFFSET. / op /, / vlen / and / v1 / ... / vn / also need to be specified, although this only confuses. In the example below, I specify the value 1 instead of 0 in the / vn / - section and get exactly the same answer!
1 = 1 1 1 0 @ 0 1 2 0 3 2 Comedy Dumb & Dumber I will try to change / op /:
1 > 1 1 1 0 @ 0 1 2 0 3 3 Thriller The Silence of the Lambs Where is it from? Well, let's try to get more than one id:
1 > 1 1 1 0 @ 0 2 2 3 0 3 3 Thriller The Silence of the Lambs Damn, I forgot about LIMIT. We indicate that we need 2 two entries:
1 > 1 1 2 0 @ 0 2 2 3 0 3 3 Thriller The Silence of the Lambs 6 Sci-Fi Star Trek Obviously, our id were ignored, and HS returns records with id> 1. In the end, I found out that in the IN query, we can safely specify “=” as / op /, and 0 as the value / v1 /. You can see that we got the results exactly in the order we indicated them in the IN condition. However, I do not know if it is a coincidence or not. I would not rely on it.
Let's go to the filters? What if we take our last query and ask HS to apply the filter by genre = 'Sci-Fi'? Remember when we opened the index, we specified / fcolumns /? Now they will come in handy to apply additional filters. If we did not specify / fcolumns /, any query with filters would return “2 1 filterfld”.
1 = 1 0 3 0 @ 0 3 2 3 1 F = 0 Sci-Fi 0 3 1 Sci-Fi Star wars It worked! OK, this is probably all about getting records from HS. Now we go to UPDATE'am. What about the documentation?
The 'find_modify' request has the following syntax./indexid/ /op/ /vlen/ /v1/ ... /vn/ [LIM] [IN] [FILTER ...] MOD
MOD is a sequence of the following parameters./mop/ /m1/ ... /mk/
- / mop / is' U '(update),' + '(increment),' - '(decrement),' D '(delete),' U? ',' +? ',' -? ', or' D ? '. If the '?' If you want to find a record before the number of modified records.
- / m1 / ... / mk / specifies the column values to set. The length of / columns / specified by the corresponding 'open_index' request. If / mop / is 'D', these parameters are ignored. If / mop / is '+' or '-', values must be numeric. If you want to make a difference, it’s not.
The first part looks familiar. We only saw all this when we looked at FIND queries, and it looks like these are WHERE conditions for our UDPATE. Update the row with id = 1 and set view_count = 100:
1 = 1 1 U 1 Sci-Fi Star Wars 100 2 1 modop modop. So glad. How clear! What if you specify LIMIT?
1 = 1 1 1 0 U 1 Sci-Fi Star Wars 100 0 1 1 Taak. Let's take a look at MariaDB:
SELECT * FROM movie WHERE id = 1; +----+--------+-----------+------------+ | id | genre | title | view_count | +----+--------+-----------+------------+ | 1 | Sci-Fi | Star Wars | 100 | +----+--------+-----------+------------+ Let's try something more complicated. Increase the view_count counter for all comedies by 10.
1 / 1 0 1000 0 F = 0 Comedy + 0 0 0 10 0 1 1 It looks like the rules! Let's take a look at MariaDB:
SELECT * FROM movie; +----+----------+--------------------------+------------+ | id | genre | title | view_count | +----+----------+--------------------------+------------+ | 1 | Sci-Fi | Star Wars | 100 | | 2 | 0 | 0 | 10 | | 3 | Thriller | The Silence of the Lambs | 0 | | 6 | Sci-Fi | Star Trek | 0 | +----+----------+--------------------------+------------+ No, it did not work. At least it did not work well. The counter has indeed increased by 10, but all other columns have also increased. Since id is numeric and we said to increment it by 0, it remained untouched. I will return everything back:
1 = 1 2 1 0 U 2 Comedy Dumb & Dumber 10 I can say right away that specifying a value for the “genre” and “title” columns in an increment request does not work. They will get the value "0", just as happened at our first attempt. In order for the increment to work as it should, you need to open a separate index, which will contain only the columns "id" and "view_count":
P 3 test movie PRIMARY id,view_count genre 0 1 3 / 1 0 1000 0 F = 0 Comedy + 0 10 0 1 1 Well, another thing:
SELECT * FROM movie; +----+----------+--------------------------+------------+ | id | genre | title | view_count | +----+----------+--------------------------+------------+ | 1 | Sci-Fi | Star Wars | 100 | | 2 | Comedy | Dumb & Dumber | 30 | | 3 | Thriller | The Silence of the Lambs | 0 | | 6 | Sci-Fi | Star Trek | 0 | +----+----------+--------------------------+------------+ That's all. Happy handlers!
Source: https://habr.com/ru/post/278037/
All Articles