CometQL - api for working with comets server using MySQL protocol

CometQL is an api for working with saas comets service using the MySQL protocol.

Why is it cool

• Unified api for over 12 programming languages
• Simple and clear type of requests
• In php there are tools to maintain constant connections with MySQL and now they can also be used to work with comet server.

For example, to get information about when the user was online, just execute the following query:

selectid, timefrom users_time whereid = 2;

And here is the request to send a message to the channel:

INSERTINTO pipes_messages (name, event, message)VALUES("pipe_name", "event_in_pipe", "text message");

How to connect and try it yourself

You can connect with the demo data and try it yourself.

# Сервер app.comet-server.ru# Логин 15# Пароль lPXBFPqNg3f661JcegBY0N0dPXqUBdHXqj2cHf04PZgLHxT6z55e20ozojvMRvB8# База данных CometQL_v1# Строка для подключения из консоли
mysql -h app.comet-server.ru -u15 -plPXBFPqNg3f661JcegBY0N0dPXqUBdHXqj2cHf04PZgLHxT6z55e20ozojvMRvB8 -DCometQL_v1

CometQL Overview

CometQL externally is a kind of database. Moreover, when we select the CometQL_v1 database when connecting, this is actually an instruction indicating which version of api we want to interact with.
You need to understand that there is no MySQL server on the backend, requests are parsed and executed directly by the comet server. And the presentation of all possible actions in the form of tables and queries to them is simply convenient and familiar for many webmasters.

Pipes_messages table

The pipes_messages table contains messages sent through pipes. To send a message to the channel, you need to execute the insert request into this table.

mysql> insertinto pipes_messages (name, event, message)values("pipe_name", "event_in_pipe", "text message");
Query OK, 0 rows affected (0.13 sec)

The fields “name” and “event” must correspond to the following regular expression [0-9A-z = + / _]
Requesting a selection from pipes_messages will return the message history in the channel if the history saving function is enabled for this channel (how to enable this function is described below )

mysql> select * from pipes_messages wherename = "p10";
+------+-------+-------+--------------+
| name | index | event | message      |
+------+-------+-------+--------------+
| p10  | 0     | event | msgData      |
| p10  | 1     | event | msgqqrrata   |
| p10  | 2     | evt3  | msgqqrrata   |
+------+-------+-------+--------------+
3 rows in set (0.00 sec)

Clears the channel message history.

mysql> deletefrom pipes_messages wherename = 'p10';
Query OK, 0 rows affected (0.13 sec)

Online example of sending messages

Pipes table

The pipes table contains information about how many people subscribed to messages from channels. The table is read-only.

mysql> select * from pipes wherenamein( "web_admins", "web_php_chat");
+--------------+-------+
| name         | users |
+--------------+-------+
| web_admins   | 3     |
| web_php_chat | 2     |
+--------------+-------+
2 rows in set (0.30 sec)

Online channel subscription example

Table users_in_pipes

The users_in_pipes table contains information about which of the authorized users subscribed to the channel. The table is read-only.

mysql> select * from users_in_pipes wherename = "web_admins";
+------------+---------+
| name       | user_id |
+------------+---------+
| web_admins | 2       |
| web_admins | 4       |
| web_admins | 14      |
| web_admins | 9       |
+------------+---------+
4 row in set (0.32 sec)

It is noteworthy that the users field contains the total number of subscribers, both authorized and unauthorized, while the users_in_pipes table contains a list of only authorized subscribers.

Pipes_settings table

The pipes_settings table contains the channel logging settings. By default, messages passing through a channel are not remembered. But if you enable the logging mechanism for the channel, then the n last messages that passed through this channel will be stored in the comet server.
To enable the logging mechanism in the channel, you must perform the following request.

mysql> insertinto pipes_settings (name, length) values ('p10', 10);
Query OK, 1 row affected (0.00 sec)

Here, the length parameter is how many recent messages will be remembered. It takes values ​​from 0 to 99.
In order to get the values ​​of the channel settings, you need to execute a query from pipes_settings.

mysql> select * from pipes_settings wherename = 'p10';
+------+--------+
| name | length |
+------+--------+
| p10  | 10     |
+------+--------+
1 row in set (0.00 sec)

In order to disable the logging mechanism, you must delete the settings entry from pipes_settings.

mysql> deletefrom  pipes_settings wherename = 'p10';
Query OK, 0 rows affected (0.00 sec)

The mechanism of user authorization on the comet server

In addition to channels where everyone who knows the name of the channel can subscribe to it, there is the possibility of authorizing users on the comet server and sending personal messages to users by their identifiers. User authorization takes place in 2 stages. The first step is to send the user ID on your system and a random hash to the comet server.

mysql> INSERTINTO users_auth (id, hash )VALUES (1, 'auth_hash1');

• Here the line auth_hash1 is the text authorization key . You yourself generate it on your server and send it first to the comets by means of the insert request in the users_auth table, and then pass it to JavaScript to authorize a specific user on the comet server.
• user id on your site, any positive integer of no more than 9 digits.

At the second stage, this information (user ID and hash) must be passed to JavaScript Api

$(document).ready(function()
{
    CometServer().start({dev_id:1, user_key:"auth_hash1", user_id:"Числовой_Идентификатор_пользователя" })
});

Here dev_id is the public identifier of the developer .
And now the user will be authorized on the comet server.

Message delivery for authorized users

When sending messages to authorized users by their identifier (insert a request to the users_messages table), messages are delivered to the user on all devices (up to 16 devices) on which he is currently authorized. This is very convenient if a person visited your site and logged in to it from more than one device (for example, a phone and a computer, or just sitting in two different browsers at the same time).

If the person is currently ofline, then the message is placed in the message queue and will be delivered when the person appears online. Currently, for each user, the maximum queue size is limited.

The main purpose of the message queue is message delivery after a person’s short-term transition to ofline. For example, in those cases when a person refreshes the page of the site on which the connection was opened, he goes to ofline for about 1 second.

Users_messages table

The users_messages table is intended for sending messages to authorized users by their identifier.
For example, to send a message to a user with id = 2 and the message text 'message', you need to execute the following request

mysql> insertinto users_messages (id, event, message)values (2, 'event', 'message');
Query OK, 0 row affected (0.00 sec)

The message is either sent to the user immediately or placed in a queue for sending to the user later.
In order to receive all those messages that have not yet been delivered to the user and are in the queue, you must execute the select request

mysql> select * from users_messages whereid = 2;
+----+-------+-------+---------+
| id | index | event | message |
+----+-------+-------+---------+
| 2  | 0     | evnt1 | message |
| 2  | 1     | evnt2 | messag2 |
+----+-------+-------+---------+
2 rows in set (0.00 sec)

Here you can see that 2 messages are waiting for sending. They will be sent as soon as the user appears online.
The table contains the columns:

• id - user id
• index - Message number in the queue
• event - event name
• message - Message body

Use the delete request to clear the queue.

mysql> deletefrom users_messages whereid = 2;
Query OK, 0 rows affected (0.08 sec)

After the message is delivered to the user, it will automatically be removed from the message queue.

Users_time table

The users_time table contains information about when users were online. The table is read-only. Time data is stored in UNIX-time

mysql> select * from users_time whereidin( 2, 3, 145);
+-----+------------+
| id  | time       |
+-----+------------+
| 2   | 0          |
| 3   | 1438245468 |
| 145 | -1         |
+-----+------------+
3 rows in set (0.31 sec)

Here the user with id = 2 is currently on the site, the user with id = 3 was online on July 30, and for the user with id = 145 there is no data.

Users_auth table

The users_auth table contains data for authorizing users on the comet server.

mysql> insertinto users_auth (id, hash )values (12, 'hash1');
Query OK, 1 row affected (0.13 sec)

mysql> select * from users_auth whereidin(2, 3, 12);
+----+----------------------------+
| id | hash                       |
+----+----------------------------+
| 2  | bjl6knotdb2t1oov958mhuian7 |
| 12 | hash1                      |
+----+----------------------------+
2 rows in set (0.32 sec)

Here for the user with id = 3 there is no data, and for users 2 and 12 the data is present.

It is important to note that in the hash field only lines with a length of not more than 32 characters and matching the regular expression [0-9A-z = + / _] can be transmitted .
To delete user authorization data, use the delete query

deletefrom users_auth whereid = 12;
Query OK, 0 rows affected (0.00 sec)

Tips for those who make their public api

Here, in my opinion, the 3 most useful articles for anyone who is going to make a public api interface:

• habrahabr.ru/post/181988
• habrahabr.ru/post/224929
• habrahabr.ru/company/yandex/blog/237459

More examples of using CometQL in real projects

Examples of using CometQL are in the article how to make chat on the site and in the article about the integration of private chat on the site