Command Packet for PurePerl MySQL API
Developing a PurePerl MySQL API – Part 3
Writing a Perl Module
Foreword: In this part of the series I explain the command packet code for PurePerl MySQL API.
By: Chrysanthus Date Published: 28 Jan 2015
Introduction
A packet is a string of bytes. It begins with a 4-byte header segment followed by a body segment. In this article I talk only about the body segment since I have already talked about the header segment, which is common to all the packets, in the previous series.
The Command Packet
Once the login (authentication phase) is complete, the client begins sending commands to the server using command packets. The body of the command packet is simple: it consists of two fields. The first field is 1 byte which is a decimal code (equivalent) indicating the instruction. For example, to change the database has one number; a normal query has a different number. The second field is called the argument. The argument can be the SQL statement without the ending semicolon. It can even be of zero length. For the compressed packet it is the argument that is compressed. For the uncompressed packet the argument is not compressed. The decimal numbers and their argument descriptions are given in the following table:
| Code numeric value | Argument Description | Command Description |
|---|---|---|
| 0 | No argument. | Never sent by a client. Reserved for internal use. |
| 1 | No argument. | Tells the server to end the session. |
| 2 | A string containing the name of the database. | Tells the server to change the default database for the session to the one specified by the argument. |
| 3 | A string containing the query. | Tells the server to run the query. |
| 4 | A string containing the name of the table. | Tells the server to return a list of fields for the specified table. This is an obsolete command still supported on the server for compatibility with old clients. Newer clients use the SHOW FIELDS query. |
| 5 | A string containing the name of the database | Tells the server to create a database with the specified name. This is an obsolete command still supported on the server for compatibility with old clients. Newer clients use the CREATE DATABASE query. |
| 6 | A string containing the name of the database. | Tells the server to drop the database with the specified name. This is an obsolete command still supported on the server for compatibility with old clients. Newer clients use the DROP DATABASE query. |
| 7 | A byte containing the bit mask of reloading operations. | Tells the server to refresh the table cache, rotate the logs, reread the access control tables, clear the host name lookup cache, reset the status variables to 0, clear the replication master logs, or reset the replication slave depending on the options in the bit mask. Issued by the client API call mysql_refresh( ). |
| 8 | No argument. | Tells the server to shut down. |
| 9 | No argument. | Tells the server to send back a string containing a brief status report. Issued by the client API call status( ). |
| 10 | No argument. | Tells the server to send back a report on the status of all running threads. This is an obsolete command still supported on the server for compatibility with old clients. Newer clients use the SHOW PROCESSLIST query. |
| 11 | No argument. | Never sent by a client. Used for internal purposes. |
| 12 | A 4-byte integer with the low byte first containing the MySQL ID of the thread to be terminated. | Tells the server to terminate the thread identified by the argument. This is an obsolete command still supported on the server for compatibility with old clients. Newer clients use the KILL query. |
| 13 | No argument. | Tells the server to dump some debugging information into its error log. Issued by the client API call mysql_dump_debug_info( ). |
| 14 | No argument. | Tells the server to respond with an OK packet. If the server is alive and reachable, it will. Issued by the client API call ping( ). |
| 15 | No argument. | Never sent by a client. Used for internal purposes. |
| 16 | No argument. | Never sent by a client. Used for internal purposes. |
| 17 | A byte sequence in the following format: zero-terminated user name, encrypted password, zero-terminated default database name. | Tells the server the client wants to change the user associated with this session. |
| 18 | A byte sequence in the following format: 4-byte integer for the offset, 2- byte integer for the flags, 4-byte integer for the slave server ID, and a string for the log name. All integers are formatted with the low byte first. | Tells the server to send a continuous feed of the replication master log events starting at the specified offset in the specified log. Used by the replication slave, and in the mysqlbinlog command-line utility. |
| 19 | A byte sequence in the following format: 1 byte for database name length, database name, 1 byte for table name length, table name. | Tells the server to send the table definition and data to the client in raw format. Used when a replication slave receives a LOAD DATA FROM MASTER query. |
| 20 | No argument. | Never sent by a client. Used for internal purposes. |
| 21 | A byte sequence in the following format: a 4-byte integer for the server ID, then a sequence of 1 byte-length prefixed strings in the following order: slave host name, slave user to connect as, slave user password. Then a 2-byte slave user port, 4-byte replication recovery rank, and another 4-byte field that is currently unused. All integers have the low byte first. | Tells the replication master server to register the slave using the information supplied in the argument. This command is a remnant of the started fail-safe replication project. It was introduced in the early version 4.0, but not much has changed since. It is possible that this command might get removed in the future versions. |
| 22 | A string containing the statement. | Tells the server to prepare the statement specified by the argument. Issued by the client API call mysql_stmt_prepare( ). New in version 4.1. |
| 23 | A byte sequence in the following format: 4-byte statement ID, 1 byte for flags, and 4-byte iteration count. All integers have the low byte first. | Tells the server to execute the statement referenced by the statement ID. Issued by the client API call mysql_stmt_ execute( ). New in version 4.1. |
| 24 | A byte sequence in the following format: 4 byte statement ID, 2 byte parameter number, parameter string. Both integers have the low byte first. | Tells the server the packet contains the data for one bound parameter in a prepared statement. Used to avoid unnecessary copying of a large amount of data when the value of the bound parameter is very long. Issued by the client API call mysql_stmt_send_long_data( ). New in version 4.1. |
| 25 | 4-byte statement ID with the low byte first. | Tells the server to close the prepared statement specified by the statement ID. Issued by the client API call mysql_stmt_close( ). New in version 4.1. |
| 26 | 4-byte statement ID with the low byte first. | Tells the server to discard the current parameter values in the prepared statement specified by the statement ID that may have been set with COM_ LONG_DATA. Issued by the client API call mysql_stmt_reset( ). New in version 4.1. |
| 27 | 2-byte code for the option, low byte first. | Tells the server to enable or disable the option specified by the code. At this point, seems to be used only to enable or disable the support of multiple statements in one query string. Issued by the client API call mysql_set_server_option( ). New in version 4.1. |
| 28 | No argument. | Never sent by a client. Used for internal purposes. |
This version of the API does not implement replication features. It does not implement prepare statement features. It does not implement the support of multiple statements in one query string. It does not also implement the end_session and change_user features. From the table, the tasks to implement are as follows – one function for each task:
- Tell the server to change the default database for the session to the one specified by the argument – Decimal code, 2 – Argument: database name.
- Tell the server to run the query – Decimal code, 3 – Argument: query string without semicolon.
- Tell the server to shut down – Decimal code, 8 – Argument: No argument.
- Tell the server to send back a string containing a brief status report - Decimal code, 9 – Argument: No argument.
- Tells the server to respond with an OK packet. If the server is alive and reachable, it will - Decimal code, 14 – Argument: No argument.
So there are five command functions, for this version. In the next part of the series the command packet package (module) will be developed, and the function names will be given – one function per task.
Chrys
Related Links
Internet Sockets and PerlPerl pack and unpack Functions
Writing MySQL Protocol Packets in PurePerl
Developing a PurePerl MySQL API
Using the PurePerl MySQL API
More Related Links
Perl Mailsend
PurePerl MySQL API
Perl Course - Professional and Advanced
Major in Website Design
Web Development Course
Producing a Pure Perl Library
MySQL Course
BACK NEXT